| Version 11 (modified by blyth, 14 years ago) |
|---|
DbiTables
Overview of Issues
Minor issues
- too many databases ... name change testdb to offline_db
- 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
Too many databases : still
mysql> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | archive_db | | dayabayrpc | | dybdb1-relay-bin | | my_test | | mysql | | mysql-bin | | offline_db | | offline_db_ihep | | testdb | | testdb_ihep | +--------------------+ 11 rows in set (0.00 sec)
DcsPmtHv : too many entries
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
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
