Schema of configuration files

Configuration files are saved by rustogramer in Sqlite3 databases. Sqlite3 data bases are a single file relational database. SpecTcl and Rustogramer are both able to recover the analysis configuration from these database files.

This section documents the database schema of those files. Note that SpecTcl schema additions describes the section of the scheme that is only used by SpecTcl.

This section assumes that you have a basic understanding of relational databases.

In this database schema table primary keys are an integer field named id. The values of these primary keys are used as foreign keys to link tables together.

Save Sets

The database format provides support for storing several configurations. This is not curruently used by rustogramer or SpecTcl. Each configuration is called a save set and the top level table for a configuration is the save_set table which is generated using the following database definition language (DDL)

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

The table has the following fields:

  • id - the primary key of the row. Used to associated rows in other tables with specific save sets.
  • name - name of the save set. When the rustogramer GUI is used to save/restore the configuration the save-set created is called rustogramer_gui.
  • timestamp - Is the system timestamp that identifies when the table was created. For savesets generated by the Rustogramer GUI, this is given the value from the Python time.time() function.

Parameter definitions

Parameter definitions are relatively simple and require only a single table that is created using the following DDL

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)

The fields in this table are:

  • id - Primary key. Tables which refer to parameter definitions will use this as the foreign key.
  • *save_id - Foreign key to the save_sets table. This is the value of the primary key of the row in that table that identifies which save set this parmaeter definition belongs to.
  • name - Name of the parameter.
  • number - Parameter id used internally to SpecTcl and Rustogramer's histograming engines.
  • low - Suggested low axis limit
  • high - Suggested high axis limit.
  • bins - Suggested number of axis bins.
  • units - Units of measure of the parameter.

Clearly there will be one row in this table for each parameter definition.

Spectrum definitions.

Several tables are required for each spectrum definition. This is because each table has several parameters and may have several axes

The root table for spectrum defintions is spectrum_defs which is defined as follows:

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
            )
  • id - is the row's primary key. Parts of the definition in other tables that refer to this spectrum will have the row's primary key as a foreign key.
  • save_id - is a foreign key into the save_sets table. This identifies which save set this definition belongs to.
  • name - is the name of the spectrum being defined.
  • type - is the textual type of the spectrum being defined.
  • datatype - is the bin data-type of the spectrum being defined. Note that when recovering configuration written by e.g. Rustogramer in SpecTcl (or the other way around), the restoration code may not honor this datatype as the set of bin datatypes supported by the two programs is disjoint (f64 for rustogramer, and long, short, byte for SpecTcl).

axis_defs

This table contains axis defintions. In restoring a spectrum from the configuration, the assumpption is made that the primary keys are chronologically monotonic, in that case, with the X axis saved first then the Y axis, fetching the axis definitions sorted by primary key allows us to distinguish between the X and Y axis definitions. The axis_defs table is defined using the following DDL:

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
            )
  • id - is the primary key of the row.
  • spectrum_id is a foreign key into the spectrum_defs table indicating which spectrum this axis belongs to. In a spectrum with two axes, as desdribed above, the one with the smaller value for id will be the X axis.
  • low - Axis low limit.
  • high - Axis high limit.
  • bins number of bins on the axis.

Spectrum parameters.

The set of tables that describe the spectrum parameters reflect the evolution of spectrum types. For the most part the spectrum_params table should not be used, in favor of the spectrum_x_params and *spectrum_y_params. Even so, capturing the parameters required by a gamma summary spectrum is not clear and probably additional scheme will need to be added to adequatly handle this. All three tables, have the same scheme, so we'll only show the spectrum_params table definition:

 CREATE TABLE IF NOT EXISTS spectrum_params   
            (   id          INTEGER PRIMARY KEY,          
                spectrum_id INTEGER NOT NULL,             
                parameter_id INTEGER NOT NULL             
            )
  • id - is the primary key of a row in this table.
  • spectrum_id is a foreign key from spectrum_defs wich indicates the spectrum this parameter belongs to.
  • parameter_id is a foreign key from parameter_defs indicating the parameter.

To give an idea of how this all hangs together, here's SQL that can grab the names of the X parameters required by the spectrum:

SELECT spectrum_defs.name, parameter_defs.name FROM spectrum_x_params
    INNER JOIN parameter_defs ON spectrum_x_params.parameter_id = parameter_defs.id
    INNER JOIN spectrum_defs ON  spectrum_x_params.spectrum_id  = spectrum_defs.id
    WHERE spectrum_defs.save_id = :saveset  
        AND spectrum_defs.name  =  :spectrum

Where

  • :saveset - is a saved query parameter that is the save set id.
  • :spectrum -is a saved query parameter that is the name of a specturm.

Note how the joins are used to link the rows in the spectrum_x_params tables to the spectrum_defs and parameter_defs tables via the foreign keys in spectrum_x_params

Condition definitions

Condition (gate) definitions are the most complex schema in this database. However, in general for a given condition type, only a very small subset of the schema is required.

The top level, or root table for condition definitions is gate_defs:

CREATE TABLE IF NOT EXISTS gate_defs       
                (   id          INTEGER PRIMARY KEY,   
                    saveset_id  INTEGER NOT NULL,      
                    name        TEXT NOT NULL,         
                    type        TEXT NOT NULL          
                )
  • id - is the gate definition primary key. This is a foreign key in all of the remaining tables in the Gate schema, tying rows back to the specific condition they describe.
  • saveset_id - Foreign key from save_sets indicating the save set this definition belongw to.
  • name - name of the condition.
  • type - condition type code.

Condition points

Conditions that are geometric in 1-d or 2-d use this table to store the coordinates of their points. The points are ordered by the primary key assigned to each point row. The table is defined as:

CREATE TABLE IF NOT EXISTS gate_points  
    (   id          INTEGER PRIMARY KEY,   
        gate_id     INTEGER NOT NULL,      
        x           REAL,                  
        y           REAL                   
    )
  • id - is the primary key of the point.
  • gate_id is a foreign key that contains the primary key of the row in the gate_defs table of the gate this point belongs to.
  • x, y - are the coordinates of a point. In the case of a 1-d gate (e.g. a slice), only the x coordinate is used and the first point is the low limit, the second the high limit of the acceptance region.

Condition parameters

Conditions that depend on parameters, store their parameters here:

CREATE TABLE IF NOT EXISTS gate_parameters 
    (   id   INTEGER PRIMARY KEY,           
        parent_gate INTEGER NOT NULL,       
        parameter_id INTEGER NOT NULL       
    )
  • id the primary key of the row.
  • parent_gate - A foreign key that identifies which gate in gate_defs this parameter belongs to.
  • *parameter_id - A foreign key that identifies which parameter in parameter_defs this parameter identifies.

Condition dependent gates

Compound conditions, depend on other previously defined condtions. This table provides the conditions a condition dpeends on:

 CREATE TABLE IF NOT EXISTS component_gates       
    (                                            
        id          INTEGER PRIMARY KEY,         
        parent_gate INTEGER NOT NULL,           
        child_gate  INTEGER NOT NULL            
    )
  • id - the row's primary key.
  • parent_gate - A foreign key into gate_defs identifying which condition, this condition is a component of.
  • child_gate - A foreign key into gate_defs identifying a condition the condition indicated by parent_gate depends on.

Condition bit masks

Bit mask conditions, supported by SpecTcl require storage of their bitmask:

CREATE TABLE IF NOT EXISTS gate_masks    
    (   id          INTEGER PRIMARY KEY,     
        parent_gate INTEGER NOT NULL,        
        mask        INTEGER NOT NULL         
    )
  • id - primary key of the row.
  • parent_gate - foreign key in gate_defs identifying which condition this mask belongs to.
  • mask - The bit mask itself.

Gate applications

Conditions can be applied to spectra at which point they become a gate to that spectrum. This is captured as shown below:

CREATE TABLE IF NOT EXISTS gate_applications 
    (
        id                INTEGER PRIMARY KEY,  
        spectrum_id       INTEGER NOT NULL,     
        gate_id           INTEGER NOT NULL      
    )

Where

  • id - is the row primary key.
  • spectrum_id is a foreign key into spectrum_defs indicating the spectrum that is being gated.
  • gate_id is a foreign key into gate_defs indicating which condition is the gate.

SpecTcl Schema Additions

SpecTcl implements treevariables which are not implemented, nor needed by Rustogramer. To support this, the schema also has the following table which is empty when a configuration is saved by rustogramer:

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                   
    )
  • id - is the row primary key.
  • save_id is a foreign key to save_sets which indicates the save set that this definition belongs to.
  • name - is the name of a tree variable.
  • value - nIs the value of the variable.
  • units - is the units of measure of the variable.

Bindset Schema additions

The python gui will create and populate a schema to save and restore the bindsets it knows about. SpecTcl, directly does not do that. The Python GUI load operations can detect the lack of the tables descsribed below and operate accordingly.

Two tables are required to save bindsets. The first one, stores the name and description. The second the spectra in each bindset:

CREATE TABLE IF NOT EXISTS binding_sets (
    id            INTEGER PRIMARY KEY,
    save_id       INTEGER NOT NULL,    -- FK save set id.
    name          TEXT NOT NULL,
    description   TEXT DEFAULT NULL
)

Where:

  • id is the row's primary key and is used as a foreign key to identify the bindset.
  • save_id is the id of the save set this binding set belongs to.
  • name is the name of the binding set.
  • description is a longer description of the binding set.

Spectra in a binding set are stored in:

CREATE TABLE IF NOT EXISTS bound_spectra (
    id    INTEGER PRIMARY KEY,
    bindset_id INTEGER NOT NULL, -- FK to binding_sets which set.
    spectrum_id INTEGER NOT NULL -- FK to spectrum_defs spectrum in binding set.
)
  • id is the primary key of the bound spectrum.
  • bindset_id is a foreign key containing the primary key of the binding_set in which the spectrum lives (from binding_sets).
  • spectrum_id is a foreign key from spectrum_defs containing the primary key of the spectrum that is a member of the binding set.

For exsample, given a binding set name, the following query returns the names of all spectra that are in that binding set:

SELECT spectrum_defs.name FROM spectrum_defs
    INNER JOIN bound_spectra on spectrum_defs.id = bound_spectra.spectrum_id
    INNER JOIN binding_sets on binding_sets.id = bound_spectra.bindset_id
    WHERE binding_sets.name = :bset_name

In the query above, :bset_name is a bound parameter of the query that will be substituted at run-time.