Version 9 (modified by blyth, 14 years ago)

--

  1. DbiTables
  2. Overview of Issues
    1. varchar/strings in DBI tables may prevent L2Cache
    2. CalibPmtSpec + SimPmtSpec : varchar PMTDESCRIB
    3. FeeCableMap : varchar CHANNELDESC, CHANHRDWDESC, SENSORDESC
    4. DaqRawDataFileInfo : rethink needed
    5. DaqRawInfo : varchars runType, partitionName
  3. Example of genDBI
    1. SimPmtSpec .spec file
      1. gendbi generated documentation
      2. gendbi generated sql
      3. gendbi generated header

DbiTables

Overview of Issues

Minor issues common to almost all tables

  • arbitrary name/case changes between SQL field names and C++ data members

Potentially major issue of varchar usage in 5 tables

  • CalibPmtSpec, DaqRawDataFileInfo, DaqRunInfo, FeeCableMap, SimPmtSpec

Philosopy mismatch issue (pointed out by Brett)

  • DaqRawDataFileInfo : changes transferState
    • DBI should not do that

varchar/strings in DBI tables may prevent L2Cache

T varchar fields hdr
CalibFeeSpec dybsvn:/dybgaudi/trunk/Database/DbiDataSvc/DbiDataSvc/CalibFeeSpec.h
CalibPmtSpec PMTDESCRIB dybsvn:/dybgaudi/trunk/Database/DbiDataSvc/DbiDataSvc/CalibPmtSpec.h
DaqCalibRunInfo dybsvn:/dybgaudi/trunk/Database/DaqRunInfoSvc/DaqRunInfoSvc/DaqCalibRunInfo.h
DaqRawDataFileInfo streamType, stream, fileState, checksum , transferState dybsvn:/dybgaudi/trunk/Database/DbiRawDataFileSvc/DbiRawDataFileSvc/DaqRawDataFileInfo.h
DaqRunInfo runType, partitionName dybsvn:/dybgaudi/trunk/Database/DaqRunInfoSvc/DaqRunInfoSvc/DaqRunInfo.h
DcsAdTemp dybsvn:/dybgaudi/trunk/Database/DcsDataSvc/DcsDataSvc/DcsAdTemp.h
DcsPmtHv dybsvn:/dybgaudi/trunk/Database/DcsDataSvc/DcsDataSvc/DcsPmtHv.h
FeeCableMap CHANNELDESC, CHANHRDWDESC, SENSORDESC dybsvn:/dybgaudi/trunk/Database/DbiDataSvc/DbiDataSvc/FeeCableMap.h
SimPmtSpec PMTDESCRIB dybsvn:/dybgaudi/trunk/Database/DbiDataSvc/DbiDataSvc/SimPmtSpec.h

CalibPmtSpec + SimPmtSpec : varchar PMTDESCRIB

Varchar Descriptions do not belong in hot DB tables ,

  • more efficient + useful approach : store integer codes (enums ) into DB
    • derive description from them by adding methods to Data Object classes
    • use of strings when enums are more appropriate is profligate 

mysql> select distinct(PMTDESCRIB) from CalibPmtSpec ;
+------------------------+
| PMTDESCRIB             |
+------------------------+
| SABAD1-ring01-column01 | 
| SABAD1-ring02-column01 | 
| SABAD1-ring03-column01 |
...
| SABAD1-ring00-column14 | 
| SABAD1-ring00-column15 | 
| SABAD1-ring00-column16 | 
+------------------------+
208 rows in set (0.00 sec)
  • inefficient storage and usage ... split into 2 or 3 fields

FeeCableMap : varchar CHANNELDESC, CHANHRDWDESC, SENSORDESC

 mysql> select * from FeeCableMap limit 10 ;
 +-------+-------------+-----------+--------------------------------+---------------+--------------+----------+----------------------------+---------------+-------------+
 | SEQNO | ROW_COUNTER | CHANNELID | CHANNELDESC                    | FEEHARDWAREID | CHANHRDWDESC | SENSORID | SENSORDESC                 | PMTHARDWAREID | PMTHRDWDESC |
 +-------+-------------+-----------+--------------------------------+---------------+--------------+----------+----------------------------+---------------+-------------+
 |     1 |           1 |  16843009 | DayaBayAD1-board01-connector01 |      67109121 | Fee-1        | 16843009 | DayaBayAD1-ring01-column01 |      16777217 | Pmt8inch-1  |
 |     1 |           2 |  16843010 | DayaBayAD1-board01-connector02 |      67109122 | Fee-1        | 16843010 | DayaBayAD1-ring01-column02 |      16777218 | Pmt8inch-2  |
 |     1 |           3 |  16843011 | DayaBayAD1-board01-connector03 |      67109123 | Fee-1        | 16843011 | DayaBayAD1-ring01-column03 |      16777219 | Pmt8inch-3  |
 |     1 |           4 |  16843012 | DayaBayAD1-board01-connector04 |      67109124 | Fee-1        | 16843012 | DayaBayAD1-ring01-column04 |      16777220 | Pmt8inch-4  |
 |     1 |           5 |  16843013 | DayaBayAD1-board01-connector05 |      67109125 | Fee-1        | 16843013 | DayaBayAD1-ring01-column05 |      16777221 | Pmt8inch-5  |
 |     1 |           6 |  16843014 | DayaBayAD1-board01-connector06 |      67109126 | Fee-1        | 16843014 | DayaBayAD1-ring01-column06 |      16777222 | Pmt8inch-6  |
 |     1 |           7 |  16843015 | DayaBayAD1-board01-connector07 |      67109127 | Fee-1        | 16843015 | DayaBayAD1-ring01-column07 |      16777223 | Pmt8inch-7  |
 |     1 |           8 |  16843016 | DayaBayAD1-board01-connector08 |      67109128 | Fee-1        | 16843016 | DayaBayAD1-ring01-column08 |      16777224 | Pmt8inch-8  |
 |     1 |           9 |  16843017 | DayaBayAD1-board01-connector09 |      67109129 | Fee-1        | 16843017 | DayaBayAD1-ring01-column09 |      16777225 | Pmt8inch-9  |
 |     1 |          10 |  16843018 | DayaBayAD1-board01-connector10 |      67109130 | Fee-1        | 16843018 | DayaBayAD1-ring01-column10 |      16777226 | Pmt8inch-10 |
 +-------+-------------+-----------+--------------------------------+---------------+--------------+----------+----------------------------+---------------+-------------+
 10 rows in set (0.00 sec)

DaqRawDataFileInfo : rethink needed

  • transferState/fileState ... changes (does not fit with DBI philosophy)
  • varchar : streamType, stream, fileState, checksum , transferState
    • none need to be using varchar varchar BUT not a hot object so does not matter ?
  • although not a hot table, use of enums/int is still preferable for greater usability 
mysql> describe DaqRawDataFileInfo ;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| SEQNO         | int(11)          | NO   | PRI |         |                | 
| ROW_COUNTER   | int(11)          | NO   | PRI | NULL    | auto_increment | 
| runNo         | int(10) unsigned | YES  |     | NULL    |                | 
| fileNo        | int(10) unsigned | YES  |     | NULL    |                | 
| fileName      | tinytext         | YES  |     | NULL    |                | 
| streamType    | varchar(32)      | YES  |     | NULL    |                | 
| stream        | varchar(32)      | YES  |     | NULL    |                | 
| fileState     | varchar(32)      | YES  |     | NULL    |                | 
| fileSize      | int(11)          | YES  |     | NULL    |                | 
| checksum      | varchar(64)      | YES  |     | NULL    |                | 
| transferState | varchar(32)      | YES  |     | NULL    |                | 
+---------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)


 mysql> select * from DaqRawDataFileInfo limit 10 ;
 +-------+-------------+-------+--------+-----------------------------------------------------+------------+---------+-----------+-----------+----------+---------------+
 | SEQNO | ROW_COUNTER | runNo | fileNo | fileName                                            | streamType | stream  | fileState | fileSize  | checksum | transferState |
 +-------+-------------+-------+--------+-----------------------------------------------------+------------+---------+-----------+-----------+----------+---------------+
 |     1 |           1 |     6 |      1 | daq.NoTag.0000006.Pedestal.TST-AD1.SFO-1._0001.data | Pedestal   | TST-AD1 | CLOSED    | 233513220 | 4C98F617 | TRANSFERRED   |
 |     2 |           1 |     7 |      1 | daq.NoTag.0000007.FEEDiag.TST-AD1.SFO-1._0001.data  | FEEDiag    | TST-AD1 | CLOSED    | 131074920 | 3994B4E8 | TRANSFERRED   |
 |     3 |           1 |     9 |      1 | daq.NoTag.0000009.FEEDiag.SAB-AD1.SFO-1._0001.data  | FEEDiag    | SAB-AD1 | CLOSED    |  11360220 | 6D83A03B | TRANSFERRED   |
 |     4 |           1 |    10 |      1 | daq.NoTag.0000010.Pedestal.SAB-AD1.SFO-1._0001.data | Pedestal   | SAB-AD1 | CLOSED    |  11200220 | 42DD5FD5 | TRANSFERRED   |
 |     5 |           1 |    11 |      1 | daq.NoTag.0000011.Physics.SAB-AD1.SFO-1._0001.data  | Physics    | SAB-AD1 | CLOSED    | 473592276 | 70065B17 | TRANSFERRED   |
 |     6 |           1 |    12 |      1 | daq.NoTag.0000012.Physics.SAB-AD1.SFO-1._0001.data  | Physics    | SAB-AD1 | CLOSED    | 108200292 | FEB524EC | TRANSFERRED   |
 |     7 |           1 |    13 |      1 | daq.NoTag.0000013.Physics.SAB-AD1.SFO-1._0001.data  | Physics    | SAB-AD1 | CLOSED    | 128677660 | 539F47D1 | TRANSFERRED   |
 |     8 |           1 |    14 |      1 | daq.NoTag.0000014.Physics.SAB-AD1.SFO-1._0001.data  | Physics    | SAB-AD1 | CLOSED    | 248492412 | 6CB379D1 | TRANSFERRED   |
 |     9 |           1 |    15 |      1 | daq.NoTag.0000015.Physics.SAB-AD1.SFO-1._0001.data  | Physics    | SAB-AD1 | CLOSED    |  47866220 | 6B3C78EA | TRANSFERRED   |
 |    10 |           1 |    16 |      1 | daq.NoTag.0000016.Physics.SAB-AD1.SFO-1._0001.data  | Physics    | SAB-AD1 | CLOSED    |  11077284 | 80CF0F36 | TRANSFERRED   |
 +-------+-------------+-------+--------+-----------------------------------------------------+------------+---------+-----------+-----------+----------+---------------+
 10 rows in set (0.01 sec)

DaqRawInfo : varchars runType, partitionName

  • although not a hot table, I think use of enums/int is still preferable for greater usability 
mysql> describe DaqRunInfo ;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| SEQNO         | int(11)      | NO   | PRI |         |                | 
| ROW_COUNTER   | int(11)      | NO   | PRI | NULL    | auto_increment | 
| runNo         | int(11)      | YES  |     | NULL    |                | 
| triggerType   | bigint(20)   | YES  |     | NULL    |                | 
| runType       | varchar(32)  | YES  |     | NULL    |                | 
| detectorMask  | int(11)      | YES  |     | NULL    |                | 
| partitionName | varchar(255) | YES  |     | NULL    |                | 
| schemaVersion | int(11)      | YES  |     | NULL    |                | 
| dataVersion   | int(11)      | YES  |     | NULL    |                | 
| baseVersion   | int(11)      | YES  |     | NULL    |                | 
+---------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> select distinct(runType) from DaqRunInfo ;
+----------+
| runType  |
+----------+
| Pedestal | 
| FEEDiag  | 
| Physics  | 
| ADCalib  | 
+----------+
4 rows in set (0.01 sec)

mysql> select distinct(partitionName) from DaqRunInfo ;
+---------------+
| partitionName |
+---------------+
| part_tst-ad1  | 
| part_sab-ad1  | 
+---------------+
2 rows in set (0.00 sec)

Example of genDBI

SimPmtSpec .spec file

  • Holds all info needed to generate .h,.cxx,.sql,.tex ... 
  meta          ,  class         ,  table       ,  CanL2Cache 
  1             ,  SimPmtSpec    ,  SimPmtSpec  ,  kTRUE
; 
  name           , codetype                 , dbtype       , description                          , code2db 
  pmtId          , DayaBay::DetectorSensor  , int(11)      , PMT sensor ID                        , .sensorId()
  describ        , std::string              , varchar(27)  , String of decribing PMT position     ,
  gain           , double                   , float        , Relative gain for pmt with mean = 1  ,
  sigmaGain      , double                   , float        , 1-sigma spread of S.P.E. response    ,
  timeOffset     , double                   , float        , Relative transit time offset         ,
  timeSpread     , double                   , float        , Transit time spread                  ,
  efficiency     , double                   , float        , Absolute efficiency                  ,
  prePulseProb   , double                   , float        , Probability of prepulsing            ,
  afterPulseProb , double                   , float        , Probability of afterpulsing          ,
  darkRate       , double                   , float        , Dark Rate                            ,

gendbi generated documentation

name dbtype codetype description code2db
pmtId int(11) DayaBay::DetectorSensor PMT sensor ID .sensorId()
describ varchar(27) std::string String of decribing PMT position
gain float double Relative gain for pmt with mean = 1
sigmaGain float double 1-sigma spread of S.P.E. response
timeOffset float double Relative transit time offset
timeSpread float double Transit time spread
efficiency float double Absolute efficiency
prePulseProb float double Probability of prepulsing
afterPulseProb float double Probability of afterpulsing
darkRate float double Dark Rate

gendbi generated sql

DROP TABLE IF EXISTS `SimPmtSpec`;
CREATE TABLE `SimPmtSpec` (
  `SEQNO` int(11) NOT NULL,
  `ROW_COUNTER` int(11) NOT NULL auto_increment,
  `pmtId` int(11) default NULL COMMENT 'PMT sensor ID',
  `describ` varchar(27) default NULL COMMENT 'String of decribing PMT position',
  `gain` float default NULL COMMENT 'Relative gain for pmt with mean = 1',
  `sigmaGain` float default NULL COMMENT '1-sigma spread of S.P.E. response',
  `timeOffset` float default NULL COMMENT 'Relative transit time offset',
  `timeSpread` float default NULL COMMENT 'Transit time spread',
  `efficiency` float default NULL COMMENT 'Absolute efficiency',
  `prePulseProb` float default NULL COMMENT 'Probability of prepulsing',
  `afterPulseProb` float default NULL COMMENT 'Probability of afterpulsing',
  `darkRate` float default NULL COMMENT 'Dark Rate',
  PRIMARY KEY  (`SEQNO`,`ROW_COUNTER`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

gendbi generated header

#ifndef SIMPMTSPEC_H
#define SIMPMTSPEC_H

////////////////////////////////////////////////////////////////////////
// SimPmtSpec                                                          //
//                                                                    //
// Package: Dbi (Database Interface).                                 //
//                                                                    //
// Concept:  A concrete data type corresponding to a single row in    //
//           the SimPmtSpec  database table of non-aggregated data.  //
//                                                                    //
////////////////////////////////////////////////////////////////////////

#include "Rtypes.h"
#include "DatabaseInterface/DbiTableRow.h"
#include "DatabaseInterface/DbiLog.h"
#include "DatabaseInterface/DbiOutRowStream.h"
#include "DatabaseInterface/DbiResultSet.h"
#include "DatabaseInterface/DbiValidityRec.h"
#include "DatabaseInterface/DbiResultPtr.h"
#include "DataSvc/ICalibDataSvc.h"
#include "Conventions/Detectors.h"
#include <string>

using namespace std;

class DbiValidityRec;

class SimPmtSpec : public DbiTableRow
{
public:
  SimPmtSpec(){}
  SimPmtSpec(const SimPmtSpec& from) : DbiTableRow(from) {  *this = from; }

  SimPmtSpec(
    DayaBay::DetectorSensor pmtId, // PMT sensor ID
    std::string describ, // String of decribing PMT position
    double gain, // Relative gain for pmt with mean = 1
    double sigmaGain, // 1-sigma spread of S.P.E. response
    double timeOffset, // Relative transit time offset
    double timeSpread, // Transit time spread
    double efficiency, // Absolute efficiency
    double prePulseProb, // Probability of prepulsing
    double afterPulseProb, // Probability of afterpulsing
    double darkRate  // Dark Rate
    
              ) 
          {
                  m_pmtId = pmtId;
          m_describ = describ;
          m_gain = gain;
          m_sigmaGain = sigmaGain;
          m_timeOffset = timeOffset;
          m_timeSpread = timeSpread;
          m_efficiency = efficiency;
          m_prePulseProb = prePulseProb;
          m_afterPulseProb = afterPulseProb;
          m_darkRate = darkRate;
          
      }

  virtual ~SimPmtSpec(){};

// State testing member functions
  Bool_t CanL2Cache() const { return kTRUE; }
  Bool_t Compare(const SimPmtSpec& that ) const {
    return m_pmtId == that.m_pmtId
    && m_describ == that.m_describ
    && m_gain == that.m_gain
    && m_sigmaGain == that.m_sigmaGain
    && m_timeOffset == that.m_timeOffset
    && m_timeSpread == that.m_timeSpread
    && m_efficiency == that.m_efficiency
    && m_prePulseProb == that.m_prePulseProb
    && m_afterPulseProb == that.m_afterPulseProb
    && m_darkRate == that.m_darkRate
   
;}

// Getters 
  DayaBay::DetectorSensor GetPmtId() const {return m_pmtId; } 
  std::string GetDescrib() const {return m_describ; } 
  double GetGain() const {return m_gain; } 
  double GetSigmaGain() const {return m_sigmaGain; } 
  double GetTimeOffset() const {return m_timeOffset; } 
  double GetTimeSpread() const {return m_timeSpread; } 
  double GetEfficiency() const {return m_efficiency; } 
  double GetPrePulseProb() const {return m_prePulseProb; } 
  double GetAfterPulseProb() const {return m_afterPulseProb; } 
  double GetDarkRate() const {return m_darkRate; } 
  

  virtual DbiTableRow* CreateTableRow() const { return new SimPmtSpec ; }
// I/O  member functions
  virtual void Fill(DbiResultSet& rs, const DbiValidityRec* vrec);
  virtual void Store(DbiOutRowStream& ors, const DbiValidityRec* vrec) const;

private:  

// Data members
  DayaBay::DetectorSensor m_pmtId; // PMT sensor ID  
  std::string m_describ; // String of decribing PMT position  
  double m_gain; // Relative gain for pmt with mean = 1  
  double m_sigmaGain; // 1-sigma spread of S.P.E. response  
  double m_timeOffset; // Relative transit time offset  
  double m_timeSpread; // Transit time spread  
  double m_efficiency; // Absolute efficiency  
  double m_prePulseProb; // Probability of prepulsing  
  double m_afterPulseProb; // Probability of afterpulsing  
  double m_darkRate; // Dark Rate  
  

};
#endif  // SIMPMTSPEC_H