NexusLIMS database

Last updated: June 13, 2022

In order to accurately know an Experimental session has occurred (and a record needs to be built) NexusLIMS relies on an external database that is stored as a file accessible to the back-end. Due to its simple design and requirements, the database is implemented using a single SQLite file stored in a location specified by the nexusLIMS_db_path environment variable. This database is created using a custom SQL Schema Definition (documented below) and can be easily backed up by simply copying the database file to a new location. The contents and structure of the database can be inspected using a number of open source tools, including the cross-platform software DB Browser for SQLite.

This database fulfills two primary purposes (in its current implementation). First, it serves as a location entries related to when a user has started and finished an Experiment on an instrument, as well as when the back-end has attempted (and completed) building a record based on that Experiment (these entries are made by either the deprecated Session Logger app, or one of the more modern harvester modules, such as py:mod:~nexusLIMS.harvesters.nemo. The second purpose is to contain authoritative information about the instruments in the facility, such as the instruments’ names, their reservation URLs, where a given instrument stores its data, etc. Having this information centrally located facilitates maintenance in the event the configuration changes in the future. These two sources of data are represented as two tables within the database named session_log and instruments, respectively. Specific documentation of each table and their data columns are provided below.

The session_log table

As described above, the session_log table is where the instruments (and the NexusLIMS back-end) store information that is used to determine what records need to be built and which files should be included in a given record (see the record building documentation for more details). Each row of this table represents a single timestamped log of a certain type of event. If the deprecated Session Logger App is in use, users (perhaps without realizing it) write to this table when they start the on an instrument at the beginning of their session, and again when they click the “End Session” button or close the application at the end of their experiment. Otherwise, the reservation and usage event harvester code (such as Session Logger App) parses the API response from the reservation system and adds rows to the session_log table as required to represent an experiment on a particular instrument.

Together, these “START” and “END” logs (linked by a session_identifier) represent a unit of time on a given instrument, and indicate to the NexusLIMS back-end that a record needs to be built for that instrument, containing files created between the starting and ending timestamps. The back-end periodically polls this database table for any logs with a status of “TO_BE_BUILT”, and fires off the record building process if any are found. Upon completion of record building, the back-end updates the record_status of these logs as needed so that duplicate records are not created. The back-end then continues polling the database indefinitely for any new sessions that need to be built.

The following is a detailed description of the columns contained in the session_log table, their data types, and how they are used/constraints placed on their values:

Column	Data type	Description
id_session_log	INTEGER	The auto-incrementing primary key identifier for this table (just a generic number). Checks: must not be NULL
session_identifier	VARCHAR(36)	A unique string (could be a UUID) that is consistent among a single record’s “START”, “END”, and “RECORD_GENERATION” events. Checks: must not be NULL
instrument	VARCHAR(100)	The instrument PID associated with this session (this value is a foreign key reference to the instruments table). Checks: value must be one of those from the instrument_pid column of the instruments table.
timestamp	DATETIME	The date and time of the logged event in ISO timestamp format. Default: strftime('%Y-%m-%dT%H:%M:%f', 'now', 'localtime') Checks: must not be NULL
event_type	TEXT	The type of log for this session. Checks: must be one of “START”, “END”, or “RECORD_GENERATION”.
record_status	TEXT	The status of the record associated with this session. This value will be updated after a record is built for a given session. Default: “WAITING_FOR_END” Checks: must be one of “WAITING_FOR_END” (session has a start event, but no end event), “TO_BE_BUILT” (session has ended, but record not yet built), “COMPLETED” (record has been built successfully), “ERROR” (some error happened during record generation), or “NO_FILES_FOUND” (record generation occurred, but no files matched time span)
user	VARCHAR(50)	A username associated with this session (if known) – this value is not currently used by the back-end since it is not reliable across different instruments.

The instruments table

This table serves as the authoritative data source for the NexusLIMS back-end regarding information about the instruments in the Nexus Facility. By locating this information in an external database, changes to instrument configuration (or addition of a new instrument) requires making adjustments to just one location, simplifying maintenance of the system. For example, when the SharePoint calendar system version was transitioned from 2010 to 2016, the calendar URLs changed, but after a simple update to the entries in this table, the existing back-end code continued working with no other changes needed.

Likewise, when the Nexus facility migrated from the SharePoint-based reservation system to the NEMO-based one, reconfiguring the instruments was as simple as adding a duplicate row for each instrument with a different harvester value and the new API endpoint location for that instrument.

Back-end implementation details

When the nexusLIMS module is imported, one of the “setup” tasks performed is to perform a basic object-relational mapping between rows of the instruments table from the database into Instrument objects. These objects are stored in a dictionary attribute named nexusLIMS.instruments.instrument_db. This is done by querying the database specified in the environment variable nexusLIMS_db_path and creating a dictionary of Instrument objects that contain information about all of the instruments specified in the database. These objects are used widely throughout the code so that the database is only queried once at initial import, rather than every time information is needed.

Column	Data type	Description
instrument_pid	VARCHAR(100)	The unique identifier for an instrument in the facility, currently (but not required to be) built from the make, model, and type of instrument, plus a unique numeric code (e.g. FEI-Titan-TEM-635816 )
api_url	TEXT	The calendar API endpoint url for this instrument’s scheduler
calendar_name	TEXT	The “user-friendly” name of the calendar for this instrument as displayed on the reservation system resource (e.g. “FEI Titan TEM”)
calendar_url	TEXT	The URL to this instrument’s web-accessible calendar on the SharePoint resource (if using)
location	VARCHAR(100)	The physical location of this instrument (building and room number)
schema_name	TEXT	The human-readable name of instrument as defined in the Nexus Microscopy schema and displayed in the records
property_tag	VARCHAR(20)	A unique numeric identifier for this instrument (not used by NexusLIMS, but for reference and potential future use)
filestore_path	TEXT	The path (relative to central storage location specified in mmfnexus_path) where this instrument stores its data (e.g. ./Titan)
computer_name	TEXT	The hostname of the support PC connected to this instrument that runs the Session Logger App. If this is incorrect (or not included), the logger application will fail when attempting to start a session from the microscope (only relevant if using the Session Logger App)
computer_ip	VARCHAR(15)	The IP address of the support PC connected to this instrument (not currently utilized)
computer_mount	TEXT	The full path where the central file storage is mounted and files are saved on the ‘support PC’ for the instrument (e.g. ‘M:/’; only relevant if using the Session Logger App)
harvester	TEXT	The specific submodule within nexusLIMS.harvesters that should be used to harvest reservation information for this instrument. At the time of writing, the only possible values are nemo or sharepoint_calendar.
timezone	TEXT	The timezone in which this instrument is located, in the format of the IANA timezone database (e.g. America/New_York). This is used to properly localize dates and times when communicating with the harvester APIs.