[[PageOutline]] = DbiTables = = Overview of Issues = Minor issues * most table/classes exhibit arbitrary name/case changes between SQL field names and C++ data members * DcsPmtHv : 10 million entries already 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 * == {{{ Last login: Wed Jul 21 15:59:49 2010 from 221.2.162.118 ... [lxslc21] /afs/ihep.ac.cn/users/b/blyth > [lxslc21] /afs/ihep.ac.cn/users/b/blyth > cd $DYB [lxslc21] /ihepbatch/dyb/dybsw/NuWa > dyb-- dybpython invoking "dyb dybgaudi" invoking "dyb dybpython" [lxslc21] /home/dyb/dybsw/NuWa/NuWa-1.6.2/dybgaudi/DybPython > cd python/DybPython/ [lxslc21] /home/dyb/dybsw/NuWa/NuWa-1.6.2/dybgaudi/DybPython/python/DybPython > ./db.py connecting to "testdb" and listing table counts dbconf : reading config from section "testdb" obtained from ['/etc/my.cnf', '/home/dyb/dybsw/NuWa/NuWa-1.6.2/../.my.cnf', '/afs/ihep.ac.cn/users/b/blyth/.my.cnf'] (last one wins) of available sections ['mysqld_safe', 'testdb', 'client', 'mysqld', 'mysql.server'] dbconf : connecting to {'passwd': '***', 'host': 'dybdb1.ihep.ac.cn', 'db': 'testdb', 'user': 'dayabay'} {'VERSION()': '5.0.45-community-log'} CalibFeeSpec : 1984 CalibFeeSpecVld : 111 CalibPmtSpec : 1040 CalibPmtSpecVld : 5 DaqCalibRunInfo : 2491 DaqCalibRunInfoVld : 2491 DaqRawDataFileInfo : 11306 DaqRawDataFileInfoVld : 11306 DaqRunConfig : 24850 DaqRunInfo : 4099 DaqRunInfoVld : 4099 DcsAdTemp : 58765 DcsAdTempVld : 58766 DcsPmtHv : 10640448 DcsPmtHvVld : 55419 FeeCableMap : 2864 FeeCableMapVld : 2 LOCALSEQNO : 3 SimPmtSpec : 2546 SimPmtSpecVld : 1 [lxslc21] /home/dyb/dybsw/NuWa/NuWa-1.6.2/dybgaudi/DybPython/python/DybPython > }}} == varchar/strings in DBI tables may prevent L2Cache == * dybsvn:ticket:524 || '''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 === {{{ #!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 === {{{ #!cpp #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 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 }}}