Other sop/dbread

Table Of Contents

Previous topic

DB Table Writing

Next topic

Debugging unexpected parameters

This Page

Daya Bay Links

Content Skeleton

DB Table Reading

DB Reading with DybDbi

DybDbi exposes most DBI functionality to python. Details in doc:5642. An example of using DybDbi to make a DBI query from ipython

Default Context Reading

A DbiResultPtr<GCalibPmtSpec> is constructed under the covers with a default context that is created from a string serialization obtained from the .spec file.

In [1]: from DybDbi import GCalibPmtSpec, TimeStamp
In [2]: r = GCalibPmtSpec.Rpt()         ## result pointer

On requesting the length the DB is queried and GCalibPmtSpec instances are created corresponding to the payload rows obtained by the query.

In [4]: len(r)             ## DB is accessed here
Out[4]: 208

Warning

When zero results are returned it means that the context does not match entries in the DB

Payload instances are accessible by list-like access on the result pointer.

In [19]: print(r[0].asdict)
{'Status': 1, 'PmtId': 536936705, 'Describ': 'SABAD1-ring01-column01', 'PrePulseProb': 0.0, 'SigmaSpeHigh': 11.3568, 'AfterPulseProb': 0.0, 'Efficiency': 1.0, 'DarkRate': 0.0, 'SpeLow': 2.0545199, 'TimeOffset': 0.0, 'TimeSpread': 0.0, 'SpeHigh': 41.0905}
In [6]: r[0]        # r[-1] array/slice access to T* Row objs
In [7]: r[0].spehigh
Out[5]: 20.0

Examine Default Read Context

In [4]: r.ctx     ## representation of default DbiCtx in use
Out[4]:
{   'CtorMask': 2097278,
    'DetectorId': 0,
    'Mask': 2097278,
    'SimFlag': 1,
    'Site': 127,
    'SubSite': 0,
    'TableName': 'CalibPmtSpec',
    'Task': 0,
    'TimeStamp': Tue, 12 Apr 2011 14:35:49 +0000 (GMT) +564713000 nsec,
    'UpdateMask': 0}

In [5]: GCalibPmtSpec.MetaRctx   ## the default DbiCtx supplied in the .spec file
Out[5]: 'Site.kAll,SimFlag.kData,TimeStamp.kNOW,DetectorId.kUnknown,SubSite.kDefaultSubSite,Task.kDefaultTask,TableName.kCalibPmtSpec'

Change Read Context

Under the covers changing the read context results in a new DbiResultPtr<T> being instanciated, and the old one being cleaned up.

In [6]: r.ctx(timestamp=TimeStamp(2010,8,10,18,30,0))  ## anything back then ?
Out[6]:
{   'CtorMask': 2097278,
    'DetectorId': 0,
    'Mask': 2097278,
    'SimFlag': 1,
    'Site': 127,
    'SubSite': 0,
    'TableName': 'CalibPmtSpec',
    'Task': 0,
    'TimeStamp': Tue, 10 Aug 2010 18:30:00 +0000 (GMT) +        0 nsec,
    'UpdateMask': 16}

In [7]: len(r)
DbiRpt<GCalibPmtSpec>::Delete
DbiRpt<GCalibPmtSpec>::MakeResultPtr tablename variant of standard ctor, tablename: CalibPmtSpec
Caching new results: ResultKey: Table: row: No vrecs
DbiCtx::RegisterCreation [DbiRpt<GCalibPmtSpec>] mask:2097278 Site,SimFlag,DetectorId,TimeStamp,SubSite,Task,TableName
Out[7]: 0     ## nope

In [8]: r.ctx(timestamp=TimeStamp())   ## default timestamp is now
Out[8]:
{   'CtorMask': 2097278,
    'DetectorId': 0,
    'Mask': 2097278,
    'SimFlag': 1,
    'Site': 127,
    'SubSite': 0,
    'TableName': 'CalibPmtSpec',
    'Task': 0,
    'TimeStamp': Tue, 12 Apr 2011 14:37:29 +0000 (GMT) +443074000 nsec,
    'UpdateMask': 16}

In [9]: len(r)
DbiRpt<GCalibPmtSpec>::Delete
DbiRpt<GCalibPmtSpec>::MakeResultPtr tablename variant of standard ctor, tablename: CalibPmtSpec
Caching new results: ResultKey: Table:CalibPmtSpec row:GCalibPmtSpec.  1 vrec (seqno;versiondate): 26;2011-01-22 08:15:17
DbiTimer:CalibPmtSpec: Query done. 208rows,   19.1Kb Cpu   0.0 , elapse   0.0
DbiCtx::RegisterCreation [DbiRpt<GCalibPmtSpec>] mask:2097278 Site,SimFlag,DetectorId,TimeStamp,SubSite,Task,TableName
Out[9]: 208

Using mysql client

Note

Interactive examination of the Database is an invaluable first step to validating updates.

By virtue of the client section, in the configuration ~/.my.cnf, which is read by the client, the mysql command with no arguments starts an interactive command line interface allowing you to query your configured database (this is not the server, that runs as mysqld).

See Configuring DB Access for an example of the client section, which will typically correspond to the tmp_offline_db section.

Example mysql client session:

[blyth@belle7 DybPython]$ mysql                    ## reads from client section
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32808
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> status            ##  verify are connected with expected DB and identity
--------------
mysql  Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (i686) using  EditLine wrapper

Connection id:          32808
Current database:       tmp_offline_db
Current user:           noddy@belle7.nuu.edu.tw
...


mysql> show tables ;
+--------------------------+
| Tables_in_tmp_offline_db |
+--------------------------+
| CalibFeeSpec             |
| CalibFeeSpecVld          |
| CalibPmtSpec             |
| CalibPmtSpecVld          |
| DaqRunInfo               |
..

mysql> select * from CalibPmtSpecVld ;          ## examine changes made
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
| SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
|    26 | 2011-01-22 08:15:17 | 2020-12-30 16:00:00 |      127 |       1 |       0 |    0 |          -1 | 2011-01-22 08:15:17 | 2011-02-25 08:10:15 |
|    18 | 2010-06-21 07:49:24 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-21 15:50:24 | 2010-07-19 12:49:29 |
|    27 | 2011-01-22 08:15:17 | 2020-12-30 16:00:00 |      127 |       2 |       0 |    0 |          -1 | 2011-01-22 08:15:17 | 2011-02-25 08:28:04 |
|    28 | 2011-01-22 08:15:17 | 2038-01-19 03:14:07 |        1 |       2 |       1 |    0 |          -1 | 2011-01-22 08:15:17 | 2011-02-28 10:47:15 |
|    29 | 2011-01-22 08:15:17 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-21 15:56:24 | 2011-02-28 15:02:13 |
|    23 | 2010-09-16 06:31:34 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-21 15:55:24 | 2010-10-13 14:23:35 |
|    24 | 2010-09-21 05:48:57 | 2038-01-19 03:14:07 |       32 |       1 |       2 |    0 |          -1 | 2010-06-21 15:56:24 | 2010-10-13 14:24:44 |
|    25 | 2010-09-22 04:26:59 | 2038-01-19 03:14:07 |       32 |       1 |       2 |    0 |          -1 | 2010-06-21 15:57:24 | 2010-10-13 14:25:32 |
|    30 | 2010-09-22 12:26:59 | 2038-01-19 03:14:07 |      127 |       3 |       0 |    0 |          -1 | 2010-09-22 12:26:59 | 2011-03-24 10:11:28 |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
9 rows in set (0.00 sec)

mysql> select * from CalibPmtSpec where seqno = 30  ;           ## seqno provides the link between payload and validity tables
+-------+-------------+-----------+------------------------+-----------+------------+-----------------+-----------+------------+------------+----------+-------------+---------------+-------------+
| SEQNO | ROW_COUNTER | PMTID     | PMTDESCRIB             | PMTSTATUS | PMTSPEHIGH | PMTSIGMASPEHIGH | PMTSPELOW | PMTTOFFSET | PMTTSPREAD | PMTEFFIC | PMTPREPULSE | PMTAFTERPULSE | PMTDARKRATE |
+-------+-------------+-----------+------------------------+-----------+------------+-----------------+-----------+------------+------------+----------+-------------+---------------+-------------+
|    30 |           1 | 536936705 | SABAD1-ring01-column01 |         1 |    41.0905 |         11.3568 |   2.05452 |          0 |          0 |        1 |           0 |             0 |           0 |
|    30 |           2 | 536936705 | SABAD1-ring01-column01 |         1 |    41.0905 |         11.3568 |   2.05452 |          0 |          0 |        1 |           0 |             0 |         100 |
|    30 |           3 | 536936705 | SABAD1-ring01-column01 |         1 |    41.0905 |         11.3568 |   2.05452 |          0 |          0 |        1 |           0 |             0 |         101 |
+-------+-------------+-----------+------------------------+-----------+------------+-----------------+-----------+------------+------------+----------+-------------+---------------+-------------+
3 rows in set (0.00 sec)

Hands On Exercise 5 : Read from DB with varying context

Note

this can be performed either on a copy tmp_offline_db or on offline_db

Follow the examples of the previous two sections to perform, DIY steps:

  1. Use mysql client to query the Vld table, eg select * from CalibPmtSpecVld ;
  2. Perform queries with varying contexts : with timestamps to distinguish between sets of parameters
  3. Contrast row counts obtained with expectations from mysql client selects

Hint, the vrec DbiValidityRec attribute on a cls.Rpt() provides access to the SEQNO of the query which allows a payload query using a where clause to select payload rows corresponding to a particular validity.

In [12]: r.vrec
Out[12]:
DbiValidityRec
{   'AggregateNo': -1,
    'ContextRange': |site 0x007f|sim 0x007f
        2011-01-22 08:15:17.000000000Z
        2020-12-30 16:00:00.000000000Z,
    'DatabaseLayout': 'NULL',
    'DbNo': 0L,
    'InsertDate': Fri, 25 Feb 2011 08:10:15 +0000 (GMT) +        0 nsec,
    'L2CacheName': '26_2011-01-22_08:15:17',
    'SeqNo': 26L,
    'SubSite': 0,
    'Task': 0,
    'VersionDate': Sat, 22 Jan 2011 08:15:17 +0000 (GMT) +        0 nsec}

In [13]: r.vrec.seqno
Out[13]: 26L

Hands On Exercise 6 : Read run timestart/timeend from DaqRunInfo table

Note

this can be performed either on a copy tmp_offline_db or on offline_db

Default Context Query

Default context will probably yield no results:

In [27]: import os

In [28]: os.environ['DBCONF'] = "tmp_offline_db"

In [30]: from DybDbi import GDaqRunInfo

In [31]: rpt = GDaqRunInfo.Rpt()

In [32]: len(rpt)
DbiRpt<GDaqRunInfo>::MakeResultPtr tablename variant of standard ctor, tablename: DaqRunInfo
Caching new results: ResultKey: Table: row: No vrecs
DbiCtx::RegisterCreation [DbiRpt<GDaqRunInfo>] mask:2097278 Site,SimFlag,DetectorId,TimeStamp,SubSite,Task,TableName
Out[32]: 0

Modify to use wideopen validity context

Use exceedingly low level technique to access the DaqRunInfo row for a particular run number:

In [32]: run = 5647

In [33]: rpt.ctx( sqlcontext="1=1" , task=-1 , subsite=-1 )

In [34]: len(rpt)

Fixing this page if it breaks

On building the docs some of the ipython sessions listed above are actually performed, making live DB queries etc... This leaves the possibility of failure, to debug this just build a single page with eg:

sphinx-build -b dirhtml -d _build/doctrees   . _build/dirhtml sop/dbread.rst