Q: I am completely new to databases, what should I do first?
A: Please, read this FAQ list, and database API documentation :
Database documentation
Then, please read How to request new db tables
Don't forget to log in, most of the information is STAR-specific and is protected; If our documentation pages are missing some information (that's possible), please as questions at db-devel-maillist.
Q: I think, I've encountered database-related bug, how can I report it?
A: Please report it using STAR RT system (create ticket), or send your observations to db-devel maillist. Don't hesitate to send ANY db-related questions to db-devel maillist, please!
Q: I am subsystem manager, and I have questions about possible database structure for my subsystem. Whom should I talk to discuss this?
A: Dmitry Arkhipkin is current STAR database administrator. You can contact him via email, phone, or just stop by his office at BNL:
Phone: (631)-344-4922
Email: arkhipkin@bnl.gov
Office: 1-182
Q: why do I need API at all, if I can access database directly?
A: There are a few moments to consider :
a) we need consistent data set conversion from storage format to C++ and Fortran;
b) our data formats change with time, we add new structures, modify old structures;
b) direct queries are less efficient than API calls: no caching,no load balancing;
c) direct queries mean more copy-paste code, which generally means more human errors;
We need API to enable: schema evolution, data conversion, caching, load balancing.
Q: Why do we need all those databases?
A: STAR has lots of data, and it's volume is growing rapidly. To operate efficiently, we must use proven solution, suitable for large data warehousing projects – that's why we have such setup, there's simply no subpart we can ignore safely (without overall performance penalty).
Q: It is so complex and hard to use, I'd stay with plain text files...
A: We have clean, well-defined API for both Offline and FileCatalog databases, so you don't have to worry about internal db activity. Most db usage examples are only a few lines long, so really, it is easy to use. Documentation directory (Drupal) is being improved constantly.
Q: I need to insert some data to database, how can I get write access enabled?
A: Please send an email with your rcas login and desired database domain (e.g. "Calibrations/emc/[tablename]") to arkhipkin@bnl.gov (or current database administrator). Write access is not for everyone, though - make sure that you are either subsystem coordinator, or have proper permission for such data upload.
Q: How can I read some data from database? I need simple code example!
A: Please read this page : DB read example
Q: How can I write something to database? I need simple code example!
A: Please read this page : DB write example
Q: I'm trying to set '001122' timestamp, but I cannot get records from db, what's wrong?
A: In C++, numbers starting with '0' are octals, so 001122 is really translated to 594! So, if you need to use '001122' timestamp (any timestamp with leading zeros), it should be written as simply '1122', omitting all leading zeros.
Q: What time zone is used for a database timestamps? I see EDT and GMT being used in RunLog...
A: All STAR databases are using GMT timestamps, or UNIX time (seconds since epoch, no timezone). If you need to specify a date/time for db request, please use GMT timestamp.
Q: It is said that we need to document our subsystem's tables. I don't have privilege to create new pages (or, our group has another person responsible for Drupal pages), what should I do?
A: Please create blog page with documentation - every STAR user has this ability by default. It is possible to add blog page to subsystem documentation pages later (webmaster can do that).
Q: Which file(s) is used by Load Balancer to locate databases, and what is the order of precedence for those files (if many available)?
A: Files being searched by LB are :
1. $DB_SERVER_LOCAL_CONFIG env var, should point to new LB version schema xml file (set by default);
2. $DB_SERVER_GLOBAL_CONFIG env. var, should point to new LB version schema xml file (not set by default);
3. $STAR/StDb/servers/dbLoadBalancerGlobalConfig.xml : fallback for LB, new schema expected;
if no usable LB configurations found yet, following files are being used :
1. $STDB_SERVERS/dbServers.xml - old schema expected;
2. $HOME/dbServers.xml - old schema expected;
3. $STAR/StDb/servers/dbServers.xml - old schema expected;
// this will be in chain
St_db_Maker *dbMk=new St_db_Maker("db", "MySQL:StarDb", "$STAR/StarDb");
dbMk->SetDebug();
dbMk->SetDateTime(20090301,0); // event or run start time
dbMk->SetFlavor("ofl"); // pick up offline calibrations
// dbMk->SetFlavor("simu"); // use simulations calibration set
dbMk->Init();
dbMk->Make();
// this is done inside ::Make method
TDataSet *DB = 0;
// "dbMk->" will NOT be needed, if done inside your [subsys]DbMaker.
// Simply use DB = GetInputDb("Calibrations/[subsystem_name]")
DB = dbMk->GetInputDB("Calibrations/[subsystem_name]");
if (!DB) { std::cout << "ERROR: no db maker?" << std::endl; }
St_[subsystem_name][table_name] *[table_name] = 0;
[table_name] = (St_[subsystem_name][table_name]*) DB->Find("[subsystem_name][table_name]");
// fetch data and place it to appropriate structure
if ([table_name]) {
[subsystem_name][table_name]_st *table = [table_name]->GetTable();
std::cout << table->param1 << std::endl;
std::cout << table->paramN << std::endl;
}
// Initialize db manager
StDbManager* mgr = StDbManager::Instance();
StDbConfigNode* node = mgr->initConfig("Calibrations_[subsystem_name]");
StDbTable* table = node->addDbTable("[subsystem_name][table_name]");
TString storeTime = "2009-01-02 00:00:00"; // calibration timestamp
mgr->setStoreTime(storeTime.Data());
// create your c-struct and fill it with data
[subsystem_name][table_name]_st [tablename];
[table_name].param1 = [some_value];
[table_name].paramN = [some_other_value];
// store data in the table
table->SetTable((char*)&[table_name]);
// store table in dBase
mgr->storeDbTable(table);
There are three timestamps used in STAR databases;
| beginTime | This is STAR user timestamp and it defines ia validity range |
| entryTime | insertion into the database |
| deactive | either a 0 or a UNIX timestamp - used for turning off a row of data |
EntryTime and deactive are essential for 'reproducibility' and 'stability' in production.
The beginTime is the STAR user timestamp. One manifistation of this, is the time recorded by daq at the beginning of a run. It is valid until the the beginning of the next run. So, the end of validity is the next beginTime. In this example it the time range will contain many event times which are also defined by the daq system.
The beginTime can also be use in calibration/geometry to define a range of valid values.
EXAMPLE: (et = entryTime) The beginTime represents a 'running' timeline that marks changes in db records w/r to daq's event timestamp. In this example, say at some time, et1, I put in an initial record in the db with daqtime=bt1. This data will now be used for all daqTimes later than bt1. Now, I add a second record at et2 (time I write to the db) with beginTime=bt2 > bt1. At this point the 1st record is valid from bt1 to bt2 and the second is valid for bt2 to infinity. Now I add a 3rd record on et3 with bt3 < bt1 so that
1st valid bt1-to-bt2, 2nd valid bt2-to-infinity, 3rd is valid bt3-to-bt1.
Let's say that after we put in the 1st record but before we put in the second one, Lydia runs a tagged production that we'll want to 'use' forever. Later I want to reproduce some of this production (e.g. embedding...) but the database has changed (we've added 2nd and 3rd entries). I need to view the db as it existed prior to et2. To do this, whenever we run production, we defined a productionTimestamp at that production time, pt1 (which is in this example < et2). pt1 is passed to the StDbLib code and the code requests only data that was entered before pt1. This is how production in 'reproducible'.
The mechanism also provides 'stability'. Suppose at time et2 the production was still running. Use of pt1 is a barrier to the production from 'seeing' the later db entries.
Now let's assume that the 1st production is over, we have all 3 entries, and we want to run a new production. However, we decide that the 1st entry is no good and the 3rd entry should be used instead. We could delete the 1st entry so that 3rd entry is valid from bt3-to-bt2 but then we could not reproduce the original production. So what we do is 'deactivate' the 1st entry with a timestamp, d1. And run the new production at pt2 > d1. The sql is written so that the 1st entry is ignored as long as pt2 > d1. But I can still run a production with pt1 < d1 which means the 1st entry was valid at time pt1, so it IS used.
One word of caution, you should not deactivate data without training!
email your request to the database expert.
In essence the API will request data as following:
'entryTime <productionTime<deactive || entryTime< productionTime & deactive==0.'
To put this to use with the BFC a user must use the dbv switch. For example, a chain that includes dbv20020802 will return values from the database as if today were August 2, 2002. In other words, the switch provides a user with a snapshot of the database from the requested time (which of coarse includes valid values older than that time). This ensures the reproducability of production.
If you do not specify this tag (or directly pass a prodTime to StDbLib) then you'll get the latest (non-deactivated) DB records.
Below is an example of the actual queries executed by the API:
select beginTime + 0 as mendDateTime, unix_timestamp(beginTime) as mendTime from eemcDbADCconf Where nodeID=16 AND flavor In('ofl') AND (deactive=0 OR deactive > =1068768000) AND unix_timestamp(entryTime) < =1068768000 AND beginTime > from_unixtime(1054276488) And elementID In(1) Order by beginTime limit 1
select unix_timestamp(beginTime) as bTime,eemcDbADCconf.* from eemcDbADCconf Where nodeID=16 AND flavor In('ofl') AND (deactive=0 OR deactive>=1068768000) AND unix_timestamp(entryTime) < =1068768000 AND beginTime < =from_unixtime(1054276488) AND elementID In(1) Order by beginTime desc limit 1