Logbook Schema

NSCL DAQ Software Documentation
Prev		Next

Synopsis

CREATE TABLE IF NOT EXISTS kvstore (           
    id   INTEGER PRIMARY KEY,                   
    key  TEXT,                                  
    value TEXT                                  
);    
CREATE TABLE IF NOT EXISTS person  (                
    id         INTEGER PRIMARY KEY,                  
    lastname   TEXT,                                  
    firstname  TEXT,                                  
    salutation TEXT                                  
);
CREATE TABLE IF NOT EXISTS shift (                 
    id    INTEGER PRIMARY KEY,                      
    name TEXT                                       
);
CREATE TABLE IF NOT EXISTS shift_members (        
      shift_id   INTEGER,                            
      person_id  INTEGER                             
);
CREATE TABLE IF NOT EXISTS current_shift (  
    shift_id  INTEGER                              
);
CREATE TABLE IF NOT EXISTS run (             
    id   INTEGER PRIMARY KEY,                 
    number INTEGER,                           
    title TEXT                                
);
CREATE TABLE IF NOT EXISTS run_transitions ( 
    id   INTEGER PRIMARY KEY,                  
    run_id INTEGER,                            
    transition_type INTEGER,                   
    time_stamp      INTEGER,                   
    shift_id        INTEGER,                   
    short_comment   TEXT                      
);
CREATE TABLE IF NOT EXISTS valid_transitions ( 
    id      INTEGER PRIMARY KEY,              
    type    TEXT                              
);
CREATE TABLE IF NOT EXISTS  valid_state_transitions (
    from_id   INTEGER,                                   
    to_id     INTEGER                                    
);
CREATE TABLE IF NOT EXISTS current_run (id INTEGER);
CREATE TABLE IF NOT EXISTS note (                 
   id INTEGER PRIMARY  KEY,                        
   run_id INTEGER,                                 
   author_id INTEGER NOT NULL,                     
   note_time INTEGER NOT NULL,                    
   note   TEXT NOT NULL                           
);
CREATE TABLE IF NOT EXISTS note_image (           
   id          INTEGER PRIMARY KEY,                
   note_id     INTEGER NOT NULL,                   
   note_offset INTEGER NOT NULL,                   
   original_filename TEXT NOT NULL,                
   image       BLOB NOT NULL                      
);

DESCRIPTION

If you are reading this and you are not either an NSCLDAQ programming/maintainer or formulating a where clause for one of the API functions that accepts one, you've come to the wrong place. Instead you should be looking at the API or language bindings for Python or Tcl. If you program directly to the database, you may not get some of the business logic of the logbook correct.

The synpopsis shows the Sqlite3 commands that create the logbook databas schema. This manpage will describe the contents of the tables and how they interrelate. The tables fall into the following broad categories of interrelated tables with some relationships carried across these categories:

Key value store: The kvstore table provides a generic key value store. This is initially stocked when the database is created, however the APIs in all languages support manipulation of this store.
Personel: The person, shift shift_members and current_shift tables provide the twin concepts of people and shifts that are collections of people that work together during data taking.
Runs: The run, run_transitions, valid_transitions, valid_state_transitions and current_run provide support for logging segments of data taking commonly called runs. This set of tables crosses over into the Personel set of tables because each run state transition is documented to have taken place during a shift.
Notes: The note, and note_image tables provide support for experimenters to log arbitrary rich text artifacts. These artifacts cross over in to both Personel (notes are written by an author who is a person on the experiment), and optinoally Runs (notes can be associated with a run).

All tables have an auto incrementing integer primary key called id. By convention, foreign keys will have field names that end in _id.

The remaining sections of this reference will describe the tables for each category.

Key value store

A key value store simply associates arbitrary text content (a value) with an arbitrary text name by which the value can be retrieved (key). A single table, kvstore provides support for the key value store. This has the follwing fields:

INTEGER id: Primary key.
TEXT key: The retrieval key.
TEXT value: The value associated with the key.

When a logbook database is created, several key/value pairs are stored:

experiment: The experiment id the log book is for.
spokesperson: The free text name of the spokesperson. Note that the presence of this key does not imply the spokesperson is a a logbook person in the Personel sense.
purpose: A brief statement of the purpose of the experiment.
version: The database version. Currently this has the value 1.0.

Personel

This subset of data base tables provides definitions of the people that are working on the experiment (person table), groups of people that are on-duty concurrently during data taking (shift and shift_members tables). In addition while the experiment is running ther is a concept of a current_shift. This indicates the shift that is on-duty currently. The current_shift table is used by the API to determine which shift a run state transition should be linked to.

Here are the fields in each of these tables:

person Table

INTEGER id: Primary key.
TEXT lastname: Surname of the person in a record of the table.
TEXT firstname: Given name of the person in a record of the table.
TEXTsalutation: Salutation by which a person is known (e.g. Mr., Ms. or Prof.).

shift Table

INTEGER id: Primary key.
TEXT name: Name of the shift.

The shift_members table is what is called a join table. It's purpose is to join records in the person table and records in the shift table in a many to many relationship. That is a person can be in many shifts and a shift can have many members. This table has no primary key, just two foreign keys:

shift_members Join Table

INTEGER shift_id: Primary key of a shift. This entry will describe a member of that shift.
INTEGER person_id: Primary key of a person. The person that has that primary key is a member of the shift with the primary key shift_id.

Finally, the current_shift table only ever has zero or one entry. It has a single field INTEGER shift_id. If there is an entry, the value of this field is the primary key of a shift. That shift is considered to be the current, or on-duty shift.

Runs

Runs are a segment of data taking. Runs have information that describes them and state transitions. State transitions are associated with a shift. Futhermore, as we will see, Runs can have notes associated with them.

The database tables in this section are a bit involved. run and run_transitions provide tables to record each run and its sequence of transitions. The valid_transitions table provides textual names to go along with each transition type and valid_state_transitions provides the directed graph of valid transitions for each state (the result of a transition). Finally, when a run is active (it has been started but not yet ended), current_run indicates this.

Under normal circumstances, a run is created and has an initial BEGIN transition indicating it has started. There may be other transitions but its final transition, if all goes well is an END transition indicating the run ended normally. Data acquisition system failures can prevent a good END from being logged for the current run. A transition called EMERGENCY_END is, therefore defined that operates identically to END but indicates the run ended improperly.

Here are the fields for each of the tables in this section of the database:

run Table

INTEGER id: Primary key.
INTEGER number: This is a unique number for he run assigned by the experimenters as opposed to id which is assigned by the database itself.
TEXT title: The title of the run. Again assigned by the experimenters. This is just some text that describes the run.

run_transitions Table

INTEGER id: Primary key.
INTEGER run_id: Primary key of the entry in run that describes the run for which this is a transition.
INTEGER transition_type: Primary key in the valid_transitions table that indicates the state transition this transition logs.
INTEGER time_stamp: The time_t at which the run transition was logged. This is gotten from time(2).
INTEGER shift_id: Primary key of an entry in the shift table that indicates which shift was on-duty when the transition was logged.
TEXT short_comment: Short textual comment that is associated with the transition. The API often refers to this as a remark.

The valid_transitions table provides a correspondence between the numeric transitions in transition.transition_type and a human readable transition name. In addition to the INTEGER id primary key referenced by transition.transition_type, this table has a TEXT type field. When the database is created/initialized this table is stocked with the following values:

Table 1. State transition values

s_transition value	s_transitionName value	Transition type
1	BEGIN	Begin run. This is always the first transition
2	END	End of run. This or `EMERGENCY_END` are always the last transition.
3	PAUSE	Pause run. Data taking is temporarly paused.
4	RESUME	Resume run. Indicates a paused run has resumed taking data.
5	EMERGENCY_END	Abnormal end of run.

Finally, not all transitions are legal. The valid_state_transitions table joins entries in valid_transitions to the entries the are valid next transitions. It contains: INTEGER from_id, the prior transition and INTEGER to_id, a legal next transition. This table is also stocked at database initialization time as follows:

Table 2. valid_state_transitions table contents

`from_id`	`to_id`	Meaning.
1	2	BEGIN -> END
1	3	BEGIN -> PAUSE
1	5	BEGIN -> EMERGENCY_END
3	4	PAUSE -> RESUME
3	2	PAUSE -gt; END
3	5	PAUSE -gt; EMERGENCY_END

Finally, current_run has zero or 1 entries. If there is a run that has been begun but not ended (either through END or EMERGENCY_END) this table's id field will contain the primary key of an entry in run.id, which is the active run.

Note tables

Notes are items containing arbitrary rich text. The text is formatted using the Markdown formatting language. Markdown supports references to images that are in the filesystem. The notes themselves are in the note table. The note_image table will store data in image files that are referenced by notes.

Rendering a note requires transforming the text stored in the database or raw text by first exporting the image files it references back out into the file system and then fixing up references to images in the original raw text to properly reference the exported images. API calls support doing that.

Here are the table contents for the tables in this section of the database.

note Table contents

INTEGER id: Primary key.
INTEGER run_id: If not null, this is the primary key in the run table of the run this note is associated with. If this is null, no run associated with this note.
INTEGER author_id: Primary key of an entry in ther person table that designates the author of this note.
INTEGER note_time: time(2) value of when the note was entered in the database by the API.
TEXT note: Raw text of the note. See the discussion at the beginning of this section about needing to transform this text before it can be rendered. There are API calls to do that.

Here are the fields in the note_image table, which contains images that were referenced by the note.

note_image table fields.

INTEGER id: Primary key of the image entry
INTEGER note_id: Primary key of the note in the note table which references this image.
INTEGER note_offset: Byte offset into the note raw text at which the image link to this image begins. Markdown image links look like ![some text](/the/file/name). The value is the offset to the !.
TEXT original_filename: This is the name of the file prior to it having been loaded into this table.
BLOBimage: This is the raw contents of the image.

Prev	Home	Next
Readout GUI Remote control protocol	Up	Event Builder REST