Introduction

ROMEDataBase class provides Relational Database Management System (RDBMS) independent reading and writing functions. Database dependent code is implemented in ROMENoDataBase, ROMEXMLDataBase and ROMESQLDataBase. ROMESQLDataBase can access MySQL, MariaDB, SQLite and PostgreSQL.

SQL databases which have the same structure work equivalently in ROME. Users can choose the database type with configuration XML file. Therefore, for example, one can easily switch from MySQL to SQLite3 without modifying the source code.

There is an example of accessing databases in the ROME package (examples/dbexample).

There are three ways to access the database from the ROME-based applications. You can choose a method as you like from,

Using SQL query.
Using ROME SQL-path.
1. Write a reading/writing code by hand
2. Add a path in the folder definition XML, and let ROME read values automatically.

With the first method, you can use the standard SQL query to access databases. If you are familiar with writing SQL query, you may choose this method. Please see examples/dbexample/src/tasks/RDBTMakeQuery.cpp as an example.

For using the second method, ROME has a special definition of path to represent the SQL relation. By using the system, you can access SQL databases without knowing the SQL query. On the other hand, the database should be designed to suit the ROME path system. The path system can be used either in the C++ code or in the definition of the folders. If you use it in the definition XML of folders, ROME automatically generates code to read the database and fill the values in the fields in folders. The values are automatically read before starting analysis.

Using ROME database path

How to add database access to your folders

In order to add database access to your folders, you need not to modify your source code. Framework will access database and get information at the beginning of runs automatically. At first you need to activate the database-access flag by <DataBaseAccess> tag in definition XML file. And you need to set the database names and paths to each fields. If you set different names to the fields, you can have different database connections for field by field.

  <Folder>
    <FolderName>Calib</FolderName>
    <DataBaseAccess>true</DataBaseAccess>
    <ArraySize>228</ArraySize>
    <Field>
      <FieldName>PMTQE</FieldName>
      <FieldType>Float_t</FieldType>
      <FieldComment>PMT quantum efficiencies</FieldComment>
      <DataBaseName>database_name</DataBaseName>
      <DataBasePath>""/path/to/qe_field""</DataBasePath>
    </Field>
    <Field>
      <FieldName>ADCPed</FieldName>
      <FieldType>Float_t</FieldType>
      <FieldComment>ADC Pedestal value</FieldComment>
      <DataBaseName>database_name</DataBaseName>
      <DataBasePath>""/path/to/pedestal_field""</DataBasePath>
    </Field>
  </Folder>

After executing the ROMEBuilder again, please add following lines in your configuration xml file(normally romeConfig.xml).

  <DataBases>
    <DataBase>
      <Name>database_name</Name>
      <Type>sql</Type>
      <Connection>mysql://username:passwd@hostname.com/database_name</Connection>
    </DataBase>
  </DataBases>

For more details of the database configuration, please search "DataBasesDesc" in link.

You can overwrite the database name, type and connection of each field at the run-time with modifying the configuration XML instead of recompiling the software. (Please search " FieldDesc/FieldName" in link for details)

How to access databases using ROME DB Path and C++ code

If you prefer to write C++ code for accessing databases instead of the automatic reading method mentioned in the previous section, you can use following functions to read and write databases.

ROMEDataBase::Read(values,path,run_number);
ROMEDataBase::Write(values,path,run_number);

,where values is a pointer to ROMEStr2DArray (two dimensional array of TString), path is a pointer of char, and run_number is an int.

In the following example code, the gain of ten photo-multipliers are read. It is assumed that the database is already connected by the framework. Users can call the handler with GetDataBase method.

How to read

   Int_t iDB;
   for(iDB=0;iDB<gAnalyzer->GetNumberOfDataBases();iDB++){
      if(!strcmp(gAnalyzer->GetDataBase(iDB)->GetName(),"your_database_name")){
         ROMEStr2DArray* values = new ROMEStr2DArray(10,1);
         char path[] = "/PMTGain[0,9](id=#)/gain";
         gAnalyzer->GetDataBase(iDB)->Read(values,path,gAnalyzer->GetCurrentRunNumber());
         for(i=0;i<10;i++){
            gAnalyzer->GetPMTAt(i)->SetGain(atof(values->At(i,0).Data()));
         }
         delete values;
         break;
      }
   }

How to write

   Int_t iDB;
   for(iDB=0;iDB<gAnalyzer->GetNumberOfDataBases();iDB++){
      if(!strcmp(gAnalyzer->GetDataBase(iDB)->GetName(),"your_database_name")){
         ROMEStr2DArray* values = new ROMEStr2DArray(10,1);
         char path[] = "/PMTGain[0,9](id=#)/gain";
         TString value;
         for(i=0;i<10;i++){
            value = gAnalyzer->GetPMTAt(i)->GetGain();
            values->SetAt(value,i,0);
         }
         gAnalyzer->GetDataBase(iDB)->Write(values,path,gAnalyzer->GetCurrentRunNumber());
         delete values;
         break;
      }
   }

You can also see examples/dbexample/src/tasks/RDBTWriteFolders.cpp as an example.

ROME path definition

In this section, the ROME DB path definition and the suited DB structure are explained.

The simplest database example

Database sample

This example shows how the database structure can be in a real experiment for storing gain of several PMTs. id in Calibration represents the history and idx represents the channels. The path for accessing the gain values of the ten PMTs can be written as "/RunCatalog[id=#]/Calibration(0,9)/gain".

RunCatalog.id is the run number. The RunCatalog database and the Calibration database are connected with a relation RunCatalog.Calibration_id=Calibration.id. The records are sorted with the Calibration.idx and returned when you read them.

Database used in the example

In this section, the method to use an example project (examples/dbexample) and the structure of the database are explained.

All the path rules are listed here, but you don't need to understand all, in almost cases, Ex.1 and Ex.2 are enough. And for the two rules, only three tables (AAA, BBB and CCC in the figure) are enough.

Install ROME. (Please see installation page of ROME.)
Go to example directory
```
cd rome/example/dbexample
$ROMESYS/bin/romebuilder.exe [options, e.g. -mysql] RDBframework.xml
```
If you are goint to use MySQL, SQLite, SQLite3 or PostgreSQL, you have to use -mysql, -sqlite, -sqlite3 or -pgsql option, respqctively.
There is a SQL script for MySQL for creating tables. If you are interested in MySQL database. Please make a database and tables with using this script. If you are goint to use SQLite, you can use files under examples/dbexample/sqlDataBase/.

If you are going to use MySQL (or MariaDB)...

Install MySQL server and create users and a database.
Add one line at the top of sqlDataBase/RDBDataBase.mysql.

Run the the script.

% mysql -u username -h hostname -p
mysql> CREATE DATABASE test;
mysql> exit;
% emacs sqlDataBase/RDBDataBase.mysql
USE test;      ( <-- Add this line at the top of the script. )
% mysql -u username -h hostname -p < sqlDataBase/RDBDataBase.sql

Edit DataBase/Connection in mysqlConfig.xml.
Run framework with SQL database.
```
./rdbframework.exe -i mysqlConfig.xml
```

The structure of the example database is shown in the next figure.

Database structure

Ex.1

/AAA[id=#]/BBB/bbb

This example shows the most basic rule. In this path, AAA and BBB are names of tables and bbb is the name of a field in BBB. AAA and BBB are connected with the relation BBB.id = AAA.BBB_id. Additional constraints can be added with [] brackets. You can add several constraints with using AND. For instance, "/AAA[id=#]/BBB[DDD_id=0 and id=AAA.BBB_id]/bbb" will work. In ROME project # is replaced with the run number.

Ex.2

/AAA[id=#]/CCC(0,3)/ccc

Next example shows how to read arrayed data. When you have arrayed data, you need to have idx field in the table. Range of array can be written in () brackets. In this example, ROMEDataBase reads ccc in CCC from idx=0 to idx=3. If you want to have field with other order, you can put other field name after ";" . Following path gives ccc from inverseOrder=0 to 10.

/AAA[id=#]/CCC(0,10)/ccc;CCC/inverseOrder(0,10)

In addition, stride can be specified as the third argument of () brackets. For instance, "/AAA[id=#]/CCC(0,10,2)/ccc" gives ccc of idx=0,2,4,6,8,10.

Ex.3

/AAA[id=#]/BBB/DDD(0,3)/ddd(0,3)

This example shows how to read arrayed fields while previous example showed how to read arrayed records. In ROME, array of records corresponds that of folders, and array of fields does that of fields in a folder. In database, arrayed fields need to have names like basename__number (two underscores !). With using both records and fields array, two dimensional array can be stored.

Ex.4

/AAA[id=#]/BBB[@XXX]/GGG/ggg

If structure of a part of database is supposed to be frequently changed, and you don't want to change path. You can use so called atmark constraints. You can store a part of path in database itself. In this example, XXX in BBB is written as

GGG="/EEE/FFF"
JJJ="/HHH{,idx}/III{,idx}[id=(@@III_id)]"

ROMEDataBase searches destination table name in XXX and insert it in path.

Ex.5

/AAA[id=#]/BBB/HHH(0,3){,idx}[id=BBB.HHH_id]/III{,idx}[id=BBB.III_id]/JJJ[id=BBB.JJJ_id]/jjj

This example shows how do you have two dimensional relations between tables. As you can see in the figure of database structure, HHH and JJJ are connected by idx, and HHH,III and JJJ are connected with BBB by id. For this purpose, ROMEDataBase can follow not only id relation but also idx relation. By default, id relation is followed, and you can enable idx relation with {,} brackets. {,} needs always two arguments, the first argument is keyword for id relation and the second is that of idx relation. When the keyword is not empty, ROMEDataBase follow the relation. Next table shows 4 patterns.

| | ---|---|--- 1 | AAA{id,}/BBB | BBB.id = AAA.BBB_id 2 | AAA{id,idx}/BBB | BBB.id = AAA.BBB_id and BBB.idx = AAA.BBB_idx 3 | AAA{,idx}/BBB | BBB.idx = AAA.BBB_idx 4 | AAA{ii,jj}/BBB | BBB.id = AAA.BBB_ii and BBB.idx = AAA.BBB_jj

Ex.6

/AAA[id=#]/BBB[XXX]/JJJ[id=BBB.JJJ_id]/jjj;HHH/idx(0,3)

You can store the structure of two dimensional relations in atmark constraints. In atmark constraint, @@ will be replaced by the name of table which have the field (In this example, @@ will be replaced with BBB).

Transaction

In ROMESQLDataBase, transaction is disabled by default. If you want to use transaction during writing database, please compile ROMESQLDataBase.cpp with -DUSE_TRANSACTION option. It may accelerate writing.

(Matthias Schneebeli), Ryu Sawada

e-mail: ryu.sawada@cern.ch

Wiki

rome3 / Database