Other sop/dbspec

Table Of Contents

Previous topic

Debugging unexpected parameters

Next topic

DB Validation

This Page

Daya Bay Links

Content Skeleton

DB Table Creation

Workflow Outline for Adding Tables

The Row classes needed to interact with the database and the Database table descriptions are generated from specification files (.spec) stored in dybgaudi:Database/DybDbi/spec. The generation is done when the CMT DybDbi package is built.

  1. create the .spec
  2. generate the code and table descriptions by building dybgaudi:Database/DybDbi
  3. create the test tables in a copy of offline_db
  4. populate the table with some dummy data using DybDbi
  5. make queries against the table using DybDbi and services

These last 2 steps in python can then be rearranged into a nosetest.

Design Tables

When considering how to divide parameters into tables bear in mind:

  • Quantities that are not updated together should not be stored together in the same table
  • Joins between tables are not supported by DBI; simplicity is mandatory

Things to avoid in tables:

  • duplication, for example integer codes accompanied by a human readable string might seem nice for users but in the long run is a bug magnet
  • strings where integer codes are more appropriate, integer columns are easier and more efficient to query against
  • varchar when other types can be used, especially in frequently accessed tables

Prepare .spec File

Spec files need to be created in dybgaudi:Database/DybDbi/spec and named after the table name prefixed with a G. An example of a spec file dybgaudi:Database/DybDbi/spec/GCalibFeeSpec.spec:

"""
docstring
"""
;
 table                         | meta                          | legacy                        | CanL2Cache                    | class
 CalibFeeSpec                  | 1                             | CalibFeeSpec                  | kFALSE                        | GCalibFeeSpec
;
 meta                          | rctx
 2                             | Site.kAll,SimFlag.kData,TimeStamp.kNOW,DetectorId.kUnknown,SubSite.kDefaultSubSite,Task.kDefaultTask,TableName.kCalibFeeSpec
;
 meta                          | wctx
 3                             | SiteMask.kAll,SimMask.kData,TimeStart.kBOT,TimeEnd.kEOT,AggNo.k-1,SubSite.kDefaultSubSite,Task.kDefaultTask,TableName.kCalibFeeSpec
;
 name                 | codetype              | dbtype           | legacy            | memb                   | code2db           | description
 ChannelId            | DayaBay::FeeChannelId | int(10) unsigned | channelId         | m_channelId            | .fullPackedData() | Electronics channel ID number
 Status               | int                   | int(10) unsigned | status            | m_status               |                   | Channel status
 AdcPedestalHigh      | double                | double           | pedestalHigh      | m_adcPedestalHigh      |                   | Measured high-gain Pedestal ADC value
 AdcPedestalHighSigma | double                | double           | sigmaPedestalHigh | m_adcPedestalHighSigma |                   | high-gain Pedestal ADC sigma
 AdcPedestalLow       | double                | double           | pedestalLow       | m_adcPedestalLow       |                   | Measured low-gain Pedestal ADC value
 AdcPedestalLowSigma  | double                | double           | sigmaPedestalLow  | m_adcPedestalLowSigma  |                   | low-gain Pedestal ADC sigma
 AdcThresholdHigh     | double                | double           | thresholdHigh     | m_adcThresholdHigh     |                   | Channel threshold, as measured in ~ADC counts
 AdcThresholdLow      | double                | double           | thresholdLow      | m_adcThresholdLow      |                   | Channel threshold, as measured in ~ADC counts

Spec files are structured into sections divided by semicolons in the first column of otherwise blank lines. The sections comprise:

  1. documentation string in triple quotes : which is propagated thru the C++ to the python commandline and used in generated documentation oum:genDbi/GCalibFeeSpec/
  2. class level quantities (identified by the presence of the meta key)
  3. row level quantities (without the meta key)

Within each section a vertical bar delimited format is used that is parsed into python dicts and lists of dicts by oum:api/dybdbipre/. These objects are made available within the context used to fill the django templates dybgaudi:Database/DybDbi/templates for the various derived files : classes, headers, documentation , sql descriptions. Further details are in the API docs linked above.

Specified quantities:

class level qty notes
table Name of the table in Database, by convention without the G prefix
meta Simply used to identify class level properties, values are meaningless
legacy Name of table again, can be used for migrations but in typical usage use the same string as the table qty
CanL2Cache leave as kFALSE, enabling L2Cache is not recommended
class Name of the generated class, use table name prefixed with a G
rctx Default DBI read context, make sure the TableName.kName is correct
wctx Default DBI write contextrange, make sure the TableName.kName is correct

Default DBI Read/Write Contexts

The default context qtys use a comma delimited string representation of DBI context and contextrange based on enum value labels. While these are conveniences that can easily be subsequently changed, it is important to ensure that the NAME in TableName.kNAME corresponds to the name of the database table.

Row level quantities are mostly self explanatory, and are detailed in oum:api/dybdbipre/.

The ones that might be confusing are:

row level qty notes
name column name as used in the C++ Getter and Setter methods
legacy name of the field in the database table
memb name of the C++ instance variable

When creating new specifications that do not need to conform to existing tables, using the same string for all the above three quantities is recommended.

Todo

plant internal reference targets to genDbi documentation

Generate Row Classes from .spec

On building the CMT package dybgaudi:Database/DybDbi the classes corresponding to the .spec are generated in the Database/DybDbi/genDbi directory. Typically the build will fail with compilation errors in the event of problems.

Ensuring Consistency When Changing Spec

DatabaseInterface and DybDbi packages make strong use of templates and generated code. Because of this the 1st thing to try when meeting crashes such as segv is to ensure full consistency by cleaning all generated files and rebuilding from scratch.

Deep cleaning can be done by:

#DBI
echo rm -rf $CMTCONFIG            ## check
echo rm -rf $CMTCONFIG  | sh      ## do

##DybDbi
echo rm -rf genDbi genDict $CMTCONFIG
echo rm -rf genDbi genDict $CMTCONFIG   | sh

Rebuild DatabaseInterface and then DybDbi

Create New Tables in tmp_offline_db

Configure the DB to connect to with the DBCONF envvar, see Configuring DB Access

[blyth@belle7 DybDbi]$ DBCONF=tmp_offline_db ipython
Python 2.7 (r27:82500, Feb 16 2011, 11:40:18)
IPython 0.9.1 -- An enhanced Interactive Python.
...
In [1]: from DybDbi import gDbi, GPhysAd
In [2]: gDbi.Status()
DybDbi activating DbiTableProxyRegistry
Using DBConf.Export to prime environment with : from DybPython import DBConf ; DBConf.Export('tmp_offline_db') ;
dbconf:export_to_env from $SITEROOT/../.my.cnf:~/.my.cnf section tmp_offline_db
Successfully opened connection to: mysql://belle7.nuu.edu.tw/tmp_offline_db
This client, and MySQL server (MySQL 5.0.77-log) does support prepared statements.
DbiCascader Status:-
Status   URL

Closed         0 mysql://belle7.nuu.edu.tw/tmp_offline_db

In [3]: GPhysAd().CreateDatabaseTables(0,"PhysAd")   ## dbno in cascade and tablename withou the G prefix
Out[3]: 1
Notes:
  • DBCONF=tmp_offline_db ipython sets the configuration for the ipython session
  • The call to gDbi.Status() is used to verify are talking to the intended Database !

Only for new tables

As CreateDatabaseTables uses create table if not exists a pre-existing table must be manually dropped (loosing all entries) before this will work.

Populate New Table With Dummy Data

Get into ipython again, with DBCONF=tmp_offline_db ipython and add some dummy entries:

In [1]: from DybDbi import gDbi, GPhysAd

In [2]: GPhysAd?     ## lookup attribute names

In [3]: r = GPhysAd.Create( AdSerial=1,PhysAdId=10,Describ="red" )
In [4]: g = GPhysAd.Create( AdSerial=2,PhysAdId=20,Describ="green" )
In [5]: b = GPhysAd.Create( AdSerial=3,PhysAdId=30,Describ="blue" )

In [6]: wrt = GPhysAd.Wrt()

In [5]: wrt.Write( r )
DbiWrt<GPhysAd>::MakeWriter standard ctor, contextrange: |site 0x007f|sim 0x007f
        1970-01-01 00:00:00.000000000Z
        2038-01-19 03:14:07.000000000Z
Using DBConf.Export to prime environment with : from DybPython import DBConf ; DBConf.Export('tmp_offline_db') ;
dbconf:export_to_env from $SITEROOT/../.my.cnf:~/.my.cnf section tmp_offline_db
Successfully opened connection to: mysql://belle7.nuu.edu.tw/tmp_offline_db
This client, and MySQL server (MySQL 5.0.77-log) does support prepared statements.
DbiCascader Status:-
Status   URL

Closed         0 mysql://belle7.nuu.edu.tw/tmp_offline_db

DbiCtx::RegisterCreation [DbiWrt<GPhysAd>] mask:2128992 SubSite,Task,TimeStart,TimeEnd,SiteMask,SimMask,AggNo,TableName
DbiWrt<GPhysAd>::Write

In [5]: wrt.Write( g )
In [6]: wrt.Write( b )

In [7]: wrt.Close()    ## DB is written to here
DbiWrt<GPhysAd>::Close
Out[8]: 1

In [8]: rpt = GPhysAd.Rpt()

In [9]: len(rpt)
DbiRpt<GPhysAd>::MakeResultPtr tablename variant of standard ctor, tablename: PhysAd
Caching new results: ResultKey: Table:PhysAd row:GPhysAd.  1 vrec (seqno;versiondate): 1;1970-01-01 00:00:00
DbiTimer:PhysAd: Query done. 3rows,    0.0Kb Cpu   0.0 , elapse   0.0
DbiCtx::RegisterCreation [DbiRpt<GPhysAd>] mask:2097278 Site,SimFlag,DetectorId,TimeStamp,SubSite,Task,TableName
Out[9]: 3

In [10]: rpt[0].asdict
Out[10]: {'AdSerial': 1, 'Describ': 'red', 'PhysAdId': 10}

Get Real

More realistic testing would modify the writers context range and readers context from their defaults.

Verify tables using the mysql client

After adding tables check them with the mysql client. Use the status command to check are connected to the expected database, see Configuring DB Access if not.

Example mysql shell session:

mysql> status

    mysql> show tables ;
    +--------------------------+
    | Tables_in_tmp_offline_db |
    +--------------------------+
    | CalibFeeSpec             |
    | CalibFeeSpecVld          |
    | CalibPmtSpec             |
    | CalibPmtSpecVld          |
    | DaqRunInfo               |
    | DaqRunInfoVld            |
    | FeeCableMap              |
    | FeeCableMapVld           |
    | LOCALSEQNO               |
    | PhysAd                   |
    | PhysAdVld                |
    | SimPmtSpec               |
    | SimPmtSpecVld            |
    +--------------------------+
    13 rows in set (0.00 sec)

    mysql> select * from PhysAd ;
    +-------+-------------+----------+----------+------------+
    | SEQNO | ROW_COUNTER | ADSERIAL | PHYSADID | DESCRIB    |
    +-------+-------------+----------+----------+------------+
    |     1 |           1 |        1 |       10 | red        |
    |     1 |           2 |        2 |       20 | green      |
    |     1 |           3 |        3 |       30 | blue       |
    +-------+-------------+----------+----------+------------+
    2 rows in set (0.00 sec)

    mysql> select * from PhysAdVld ;
    +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
    | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
    +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
    |     1 | 1970-01-01 00:00:00 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 1970-01-01 00:00:00 | 2011-04-06 10:48:21 |
    +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
    1 row in set (0.00 sec)