Database Architecture

CHESSDatabase persists everything built with CHESSCore to a SQLite database. create_db(path) builds a new schema at path, and connect_SQLite(path) opens the connection that every other function in the package uses:

julia> path = joinpath(mktempdir(), "lab.db");

julia> create_db(path);

julia> connect_SQLite(path)

create_db also enables foreign-key enforcement, so the database rejects any write that would leave a reference pointing at a row that does not exist. The relationships described below are enforced by the database.

Each table below is listed as Name(column, column, ...).

Core identity

• Ledger(ID, SequenceID, Time) numbers every event in order, and the history in every other table refers to it. See The Ledger.
• LocationTypes(Name) and Locations(ID, Name, Type) hold one row for every committed Location, whatever its Julia type.
• Barcodes(Barcode, LocationID, Name) maps a physical barcode string to a Location.

Components and chemistry

Components(ID, Type), Reagents(ComponentID, Name, Type, MolecularWeight, Density, CID), Species(ID, Name, Charge, Formula, MolecularWeight), Dissolutions(ID, ReagentComponentID, SpeciesID, Coefficient), and Organisms(ID, ComponentID, Genus, Species, Strain) persist the Reagent, Species, Organism, and Dissolution types of CHESSCore. A species with an elemental formula stores the formula as a Hill-order string and no molecular weight, since the weight derives from the formula. A species without a formula stores its molecular weight.

A database created before the Chemicals and CompositionRules tables were renamed Species and Dissolutions is converted by migrate_chemistry!.

Environment

Attributes(Attribute, BaseUnit) is the registry of attribute kinds, and get_all_attributes returns it as a DataFrame. EnvironmentAttributes(ID, LedgerID, LocationID, Attribute, Value, Unit, Time, InstrumentID, InstrumentTime) has one row for every set_attribute! call.

Operations

Every mutating CHESSCore operation has a matching append-only table: Transfers, Movements, Reads, Locks, Activity, and InstrumentSettings. Each has a LedgerID, which places the row in the history, and an InstrumentID and InstrumentTime, which record the instrument that performed the operation, if any. The InstrumentID columns are indexed. See Instrument Interfaces.

Caching

A family of Cached* tables stores snapshots of derived state, so that reconstructing a location does not always replay its entire history. The tables are CachedAncestors, CachedDescendants, CachedEnvironments, CachedContents, and CachedLockActivity. The backing tables CachedChildSets, CachedAttributeSets, and CachedStocks store each shared child set, attribute set, and stock once. See Caching & Repair.

Experiments, runs, protocols, and encumbrances

The tables Experiments, Runs, Protocols, ProtocolEnforcement, Encumbrances, and EncumbranceCompletion, and a matching Encumbered* family of operation tables, group and reserve future work for an experiment. See Encumbrances for protocols and encumbrances.

Running SQL

query_db runs a statement that returns rows and gives a DataFrame. execute_db runs a statement that changes the database, such as INSERT, UPDATE, or CREATE. Both take a vector of parameters for the ? placeholders of the statement. Parameters are safer than building the SQL with string interpolation. Both raise an error if no database is connected.

sql_transaction runs a function in a transaction. If the function throws, every write that it made is rolled back, and otherwise it returns the value of the function. upload and update use it, so an operation and its database record succeed or fail together. sql_commit and sql_rollback commit and roll back a named savepoint, and most code uses sql_transaction instead:

julia> execute_db("CREATE TABLE Scratch (Name TEXT)");

julia> execute_db("INSERT INTO Scratch (Name) VALUES (?)", ["first"]);

julia> try
           sql_transaction() do
               execute_db("INSERT INTO Scratch (Name) VALUES (?)", ["second"])
               error("fail after the insert")
           end
       catch
       end

julia> query_db("SELECT Name FROM Scratch")
1×1 DataFrame
 Row │ Name
     │ String
─────┼────────
   1 │ first