ConditionsDbCode

Create a new calibration table

Any user can create a new table in their private code and put data into it using the text file mechanism. Tables that will be created and in the database itself should be designed in consultation with an expert. Only experts with "admin" privilege can create the table in the database.

design considerations

There are many consideration that go into designing a calibration table. First, what data can go into a table? A database table is a set of rows and columns. The rows are usually connected to physical or conceptual units, such a detector components or channels. The columns are usually the data you want associated to each detector component. For example, there might be a table where each row represents a calorimeter crystal and columns are gains and pedestals. An alignment table might have a row for each tracker panel and position offsets for columns. Here are some considerations, starting with the most important.

1. Do the columns have the same row indexing? This is a requirement.
2. Are the values going to be used at the same time? You don't want to have to fetch columns A and B, if you only want A.
3. Are the values going to be updated on a similar schedule? If you have a table with a column that is updated much less often that other data, then you will end up repeating those values, which is inefficient.
4. the largest reading time overhead is probably per-table latency, so you want to have as much data in one table as you can, subject to the above constraints.
5. Have you thought about what else might be needed in the near future? It will add non-trivial work to the database maintenance if you need changes to your table design.

The next major decision is how to index the rows. There must be a column which is unique to the row. Typically this is a channel number, but it can be any integer. For example it might be the sparse index StrawId. There may be 3 major methods:

1. vector access. Access all rows of your table as a vector, loop over the vector, see what the index is for each row, and apply the data to your object based on the index. For example, if there is a row of alignment data for each tracker panel, you could loop over the rows and read the index X and apply the values to the panel X object. In this case you don't need to care about the order of your rows.
2. sequential access. You commit to always keeping your rows in a fixed order 0 to N-1. The index has these values and you always upload them in this order. When you access the table, and you need the row for index X, you simply take position X of the vector, knowing that this corresponds to index X. Once you have the row, you can double-check that the index agrees with the position. Note that if you have a sparse index, such as StrawId, you can keep in your code, apart from the database table code, a conversion between the sparse index and the dense index used to label table rows.
3. map access. You can design your table to keep a map of the rows where the key to the map is the index of your row, and the value is the vector position, then you can do random access for index X. This map could be fixed or created on the fly as the table is filled. A map like this may have some overhead cost if it is large.

Please consider how many significant digits you will need to represent your data and use the minimum. For reproducibility, floats are essentially stored as text, so more digits require more space and time.

Name your table. The first three characters must correspond to the schema in the database. The current schema are "Trk", "Cal", and "Crv", but more can be added. This is important since permissions key off this text. Then there is a string which must be unique within the schema. So if your table contains CRV SiPM gains, the table class might be called CrvSiPMGain. Or if it contained lots of columns concerning the SiPM, maybe call it CrvSiPM or CrvSiPMPars.

Decide if the number of rows in one commit of calibration data is fixed. This would be recommended for any table indexed to a detector with a fixed number of channels. The code can enforce this length as a check. Channels without good data are included with nominal or flag values. An example where the length could not be fixed is variable length list of bad channels or bad runs.

table class code

1. Copy the example code from TstCalib1.hh to the new class name and rename the class inside.
2. change the nested Row class to correspond to the needed row. The unique row index should be the first column.
3. column ordering should be the same everywhere
4. The constructor has three arguments
   1. the code class name (TstCalib1)
   2. the database table name as schema.shortname (tst.calib1). This must be identical the table name in the SQL creation process.
   3. the column names, separated by commas ("channel,flag,dtoe"), this will serve as the basis of an SQL query. The name here must be identical to the column names in the SQL table creation
5. update the size() method so it is an estimate of the memory space used by this class
6. if the table is to have a fixed length, implement it in nrowFix(), otherwise delete this method
7. change addRow() so when it receives a vector of strings, it converts the strings to int and floats in the order you established in the Row class
8. change rowToCsv so that it can convert binary rows to text. Please put in the correct precision.
9. if you chose to not have a map, remove the map from the member data, addRow() and remove the row(channel) accessor.
10. add the class to DbTables/src/DbTableFactory.cc

Once the table class has been created, you can use the table, providing data by the text table interface. You do not need any actual database entries.

create in the database

To create the table in the database, you will need an expert to run an SQL snippet.

CREATE TABLE tst.calib1 
  (cid INTEGER, channel INTEGER, flag INTEGER , dtoe NUMERIC,
   CONSTRAINT tst_calib1_pk PRIMARY KEY (cid,channel) );
GRANT SELECT ON tst.calib1 TO PUBLIC;
GRANT INSERT ON tst.calib1 TO val_role;

1. the table name must be the same as the database name in the class definition
2. you must add the CID column to the first position
3. floats should be represented as NUMERIC, for perfect reproducibility
4. there should be a constraint that the CID and channel number are unique. If two columns form the unique row label, then the constraint would contain both those columns, and the CID.
5. GRANT SELECT to PUBLIC
6. GRANT INSERT (and INSERT only) to the appropriate role. Tables named trk.* are assigned to role trk_role. The tables in the tst (test) schema are assigned the val role to avoid creating another role just for tst.

Finally you will need to add the table to the list of tables in the database:

dbTool commit-table --name TstCalib1 --dbname tst.calib1

This is how the system knows that this table can have calibration data attached to it.