A.4. Database schema.

Note. The database scheme is not considered to be a stable interface in the sense that whle the APIs documented will be maintained and backward compatible with future work, the developers reserve the right to modify the actual database schema. This documentation allows you to know how to do operations that may not be supported by the APIs but there's no gaurantee anything you do with schema knowledge will work in perpetuity. If the API is missing something you want then by all means let us know and we'll consider adding it or working with you to describe how to meet your need within the current APIs.

Database schema are divided into tables. Each table usually has a field called its primary key. The value of the primary key is a unique identifier for records in the table. It is normally very quick to look up a record by its primary key.

In a relational databae, such as sqlite3, it is normal for records in one table to refer to records in another table. This reference is expressed by having fields that contain foreign keys to the other table. A foreign key is a field whose values are primary keys in another table.

Foreign keys express a belonging/ownership relationship. The ownership, in turn may express a 'has many' or a 'has one' relationship depending on the multiplicity of the relationship. This multiplicity is defined by the logic that populates the database.

This appendix will describe the set of tables defined by a daq database and their relationships.

The top level table is the save_sets table. It contains one entry for each save set in the database. All other tables are owned either directly or indirectly by a row in the save_set table. The schema of this table is:

    CREATE TABLE IF NOT EXISTS  save_sets 
            (id  INTEGER PRIMARY KEY,
             name TEXT UNIQUE,
             timestamp INTEGER)
                

The fields have the following meaning:

The primary purpose of save sets is to store the analysis configuration of SpecTcl. This consists of parameter/treeparameter definitions, Spectrum definitions, gate definitions, gate applications and tree variables. At present, pseudo parameter definitions are not stored. If there is a need for that, please contact us as these can be added.

Parameter definitions require a single table: parameter_defs. This table contains the base SpecTcl parameter definition as well as any optional metadata supplied by a treeparameter that might wrap it. If a tree parameter is not wrapping the parameter, then the metadata values are NULL.

Here's the table definition for the parameter_defs table:

    CREATE TABLE IF NOT EXISTS parameter_defs
            (id      INTEGER PRIMARY KEY,                    
             save_id INTEGER NOT NULL,  -- foreign key to save_sets.id
             name    TEXT NOT NULL,
             number  INTEGER NOT NULL,
             low     REAL,
             high    REAL,
             bins    INTEGER,
             units   TEXT)
                

As with all tables, by convention, id is the primary key and is an integer. Here are the definitions for the remaining fields of the table:

The next set of tables hold information about spectrum definitions and spectrum contents. These tables are:

Let's look at these tables in detail.

spectrum_defs is the top level table for capturing spectrum definitions and contents. All of the other tables involved in spectrum definitions have foreign keys linking them back to this table. This table, in turn, has a foreign key that links it back to the save set it belongs to.

The defintion of spectrum_defs is:

    CREATE TABLE IF NOT EXISTS spectrum_defs
            (id      INTEGER PRIMARY KEY,
             save_id INTEGER NOT NULL,     -- Foreign key to save_sets.id
             name    TEXT NOT NULL,
             type    TEXT NOT NULL,
             datatype TEXT NOT NULL
            )
                

Besides the id which is the usual primary keh and the save_id which is the foreign key pointing back to the save_sets table, the following fields are defined:

Each spectrum has one or two axes (SpecTcl does not support 3d spectra). These axes are described in the axis_defs table. When fetching from this table, it's important to order the results by the id field. When this is done, the first (or only) axis for a spectrum is the X axis and the second, if it exists is the Y axis definition.

Here is the definitino of the axis+defs table:

    CREATE TABLE IF NOT EXISTS axis_defs
            (
                id           INTEGER PRIMARY KEY,
                spectrum_id  INTEGER NOT NULL,  -- FK to spectrum_defs.id
                low          REAL NOT NULL,
                high         REAL NOT NULL,
                bins         INTEGER NOT NULL
            )
                

As usual, id is the primary key. It also serves to order axis definitions for a spectrum. spectrum_id is a foreign key to the id field of the spectrum_defs table. Its value indicates which spectrum in the spectrum_defs table, a record in this table belongs to.

The remaining fields in this table are:

Spectra are defined on parameters. The values of parameters in an event can increment a spectrum depending on that spectrum's type and whether or not parameters needed are present. The number of parameters a spectrum may have are dependent on may be anywhere from 1 to unbounded depending on the spectrum type (e.g. gamma spectra).

Where parameter ordering is important, ordering the retrieval of parameters from the spectrum_params table recovers that ordering.

The contents of this table consist of the primary key id, and two foreign keys: spectrum_id which ties the record back to the spectrum it belongs to and parameter_id which ties the entry back to the parameter in the parameter_defs table that defines the parameter.

In this way, it is not necessary to actually repeat the parameter definitions. This point is an important concept in database design. This sort of table is also sometimes called a join table because you can use the SQL join operation to marry the spectrum_defs table to the parameter_defs table through this table.

Finlly, If the contents of the spectrum have been saved in a save set there will be several entries in the spectrum_contents table to represent this. With the exception of the special case of a spectrum with no counts, the table will contain one entry for each non-zero channel. If the spectrum is empty, it will contain a single entry for a valid channel in the spectrum containing a value of zero.

The table contains the usual id primary key column. It also has a spectrum_id foreign key into the spectrum_defs table that ties each record back to a spectrum definition which, in turn, ties the entry back to a save set.

The remaining fields are:

SpecTcl provides a very rich set of gate types. For database definition purposes, these actually fall into three categories:

1. Parameter/point; these gates depend on parameters and points in parameter space.

2. Parameter/mask; these gates depend on parameters and a bitmask.

3. Compound; these gates depend on other gates.

There are also the special cases of True and False gates that depend on nothing.

This richness in gate types and dependecies leads to a root gate_defs table and quite a few subordinate tables some of which are used and other not used for any given gate type:

Let's starg going over the contents of each of these tables one at a time. The gate_defs table, as expected, has the id primary key and saveset_id foreign key fields, uniquely defining the entry and which save set the gate definition belongs to.

Additionally, each record of this table has the following fields:

For Parameter/Points gates, the gate_points table defines a region of interest in parameter space in which the gate is true. These gates will also have entries in the gate_parameters table. Where point ordering is important (e.g contours and bands), ordering the retrieval by id recovers that order.

In addition to the id primary key, this table contains a gate_id field which is a foreign key into the gate_defs table identifying the gate each point belongs to.

Real x and possibly null y fields identify the point coordinates. One dimensional regions of interest (e.g. slices) have null y values.

Compound gates require a list of dependent gates. These are stored in the join table component_gates. In addition to the id primary key and parent_gate foreign key back to the gate_defs table indicating the gate each record belongs to, a child_gate foreign key back to the gate_defs table points to the dependent gate.

Compound gates need not have ordering, however for some gate types (and, or gates), the ordering could be chosen to improve the efficiency of gate computation if the frequency with which a dependent gate is satisfied is known. This s because these gates do short-circuit evaluation, where possible to avoid checking all dependent gates. Once more retrieving from this table ordered by id will recover the original order of dependent gates.

Parameter/point and parameter/mask gates require entrie(s) in the gate_params table. This is a join table with primary key id and parent_gate pointing to the owning gate in the gate_defs table and parameter_id pointing to the parameter in the parameter_defs table.

As usual, retrieving the data from this table ordered by primary key will recover any required ordering of these parameters (for example band aand contour gates require the first parameter be the X parameter and second the Y).

Finally, the gate_masks table stores the mask values for gates Parameter/Mask gate types. This table, in addition to the primary key id, and foreign key parent_gate that refers back to the gate_defs table has an integer mask parameter that contains the mask value.

Defined gates can be applied to a spectrum in SpecTcl. When applied, that gate acts as a condition that is checked, event by event, and must be true for the spectrum to be incremented. Actually all spectra have gates applied to them. When a spectrum is created it has a True gate applied to it so that it is always incremented.

Gate applications are captured in a join table called, gate_applications in addition to the primary key id, this table has two foreign keys: spectrum_id that indicates which spectrum is being conditionalized by the gate that is indicated by gate_id.

The final analysis cofiguration item we need to describe in the database are tree variables. These map to Tcl variables and CTreeVariable objects that are used to steer computations performed by the user's event analysis pipeline.

Tree variable definitions are captured in the treevariables table:

    CREATE TABLE IF NOT EXISTS treevariables (
                id             INTEGER PRIMARY KEY,
                save_id        INTEGER NOT NULL,
                name           TEXT NOT NULL,
                value          DOUBLE NOT NULL,
                units          TEXT
            )
                

In addition to the id primary key and save_id foreign key into the save_sets table, the following fields are present in this table:

            CREATE TABLE IF NOT EXISTS runs (    -- Runs that were saved.
                id         INTEGER PRIMARY KEY,
                config_id  INTEGER,              -- Configuration at begin of run.
                run_number INTEGER UNIQUE NOT NULL,
                title      TEXT NOT NULL,
                start_time INTEGER,
                stop_time  INTEGER              -- End of run time
            )
                    

    CREATE TABLE IF NOT EXISTS events (
        id          INTEGER PRIMARY KEY,
        run_id      INTEGER NOT NULL,
        event_number INTEGER_NOT NULL,
        parameter_id INTEGER NOT NULL,
        parameter_value REAL NOT NULL
    )
                    

    CREATE TABLE IF NOT EXISTS events (
                id         INTEGER PRIMARY KEY,
                run_id     INTEGER NOT NULL,   -- fk to runs(id).
                event_number INTEGER NOT NULL, -- Event number in the run.
                parameter_count INTEGER NOT NULL, -- Hints at the event blob size.
                event_data  BLOB NOT NULL
            )
                    

            CREATE TABLE IF NOT EXISTS scaler_readouts (
                id            INTEGER PRIMARY KEY,
                run_id        INTEGER NOT NULL,      -- fk for runs.
                source_id     INTEGER NOT NULL,      -- Event builder source.
                start_offset  INTEGER NOT NULL,
                stop_offset   INTEGER NOT NULL,
                divisor       INTEGER NOT NULL,
                clock_time    INTEGER NOT NULL
            )