Tables

The SQL database tables used in dbprocessing, and their columns, are summarized here. getEntry() will, in most cases, return a record from any table.

code	Executable data-producing codes
file	A single data file
filecodelink	Code used to create an output file
filefilelink	Input files used to create an output file
inspector	Codes that link files to products
instrument	Instrument (for grouping related products)
instrumentproductlink	Connect instruments to products
logging	Log of ProcessQueue.py runs.
logging_file	Unused
mission	Directories for mission codes, files, etc.
process	Process that converts inputs to output
processqueue	Files to be processed
product	Generalization of file types
productprocesslink	Relates processes to their input products
release	Record of files in a release
satellite	Satellite (for grouping related products)
unixtime	Unix start/stop time for files

code

Table describing a single executable script, used for creating output files from input files (see Codes). Note each version of a code has its own entry (with no explicit connection between them), and a given script may be referred to/used by more than one code entry.

code_id

Auto-incremented ID for this code, mostly for cross-referencing. (Integer, PK, NOT NULL)

filename

Filename (only) of executable; this is passed as part of the command line. May include substitution strings. (String, NOT NULL)

relative_path

Directory containing filename, relative to mission.codedir. (String, NOT NULL)

code_start_date

Code is valid for files dated on or after this date; matching is by utc_file_date. (Date, NOT NULL)

code_stop_date

Code is valid for files dated on or after before date; matching is by utc_file_date. (Date, NOT NULL)

code_description

Human-readable description of a code. May be considered a name but usually longer than a name and usually not used for lookup. (Text, NOT NULL)

process_id

The process implemented by this code. (Integer, NOT NULL, FK process.process_id)

interface_version

Version of the code. Full version is interface.quality.revision. (SmallInteger, NOT NULL)

quality_version

Version of the code. (SmallInteger, NOT NULL)

revision_version

Version of the code. (SmallInteger, NOT NULL)

output_interface_version

Interface (i.e. major) version of the output product of this code. (SmallInteger, NOT NULL)

active_code

Whether the code is active; inactive codes are not used for processing files. (Boolean, NOT NULL)

date_written

Date code was written; meant for human information only. (Date, NOT NULL)

shasum

SHA-1 checksum of the code; meant for validation but not currently used. (String)

newest_version

Whether this is the newest version of a particular code. This may somewhat conflict with having multiple versions of a code that are selected based on code_start_date and code_stop_date; in practice, to date only one version of a code has been marked newest_version and it is also usually the only one marked active_code. (Boolean, NOT NULL)

arguments

Command line arguments for building the code. The full command line is built from mission.codedir, relative_path, filename, process.extra_params, arguments, the input files, and then output files (in that order). (Text)

ram

A relative measure of how much memory this code consumes. Purely relative and used in calculating how many codes are currently running for purposes of ProcessQueue.py -n. Nominally 1, so e.g. making 2 indicates a process that takes up twice as much RAM as “typical”, and 0.5 indicates half as much as typical. (Float)

cpu

Analagous to ram, a relative measure of how much processor power it takes to run this code. More concretely, this should usually be set to the number of threads a code uses (thus being integral); a long-running single-threaded process should still be set to 1. (SmallInteger)

file

A single data file; conceptually maps to a single file on disk. Related, but not identical, to Diskfile and DBfile. Much of this information is populated by the inspector. See also addFile().

file_id

Auto-incremented ID for this file, mostly for cross-referencing. (Integer, PK, NOT NULL)

filename

Name of the file, without path. The pathing is determined from product.relative_path. (String, NOT NULL)

utc_file_date

Single data “characterizing” the data within the file. For a file of a product on a DAILY timebase, this usually maps to the date of every timestamp within the file. However, a daily file may, due to conversions, include a small amount of data from the previous and following date, thus this is distinct from utc_start_time and utc_stop_time. Semantics on other timebases are not yet defined. (Date)

utc_start_time

Timestamp of the first record in this file. The interpretation of this timestamp is not defined by dbprocessing. (DateTime)

utc_stop_time

Timestamp of the last record in this file. The interpretation of this timestamp is not defined by dbprocessing. (DateTime)

data_level

Numerical level of this file; somewhat redundant with product.level. (Float, NOT NULL)

interface_version

Version of the file. Full version is interface.quality.revision. (SmallInteger, NOT NULL)

quality_version

Version of the file. (SmallInteger, NOT NULL)

revision_version

Version of the file. (SmallInteger, NOT NULL)

verbose_provenance

Full command line which was used to build this file; in theory if the same codes and input files are in place, executing this command line will recreate the file. (Text)

check_date

Date the file was quality checked; unused. (Was meant to support the QA loop). (DateTime)

quality_comment

Comment from the quality check; unused. (Text)

caveats

Caveats on use of the file; unused. (Text)

file_create_date

Date/time the file was created. (DateTime, NOT NULL)

met_start_time

Mission Elapsed Time (MET) of first record in file, meant to correspond to utc_start_time. Not used by dbprocessing logic and interpretation is not defined by dbprocessing. (Float)

met_stop_time

MET of last record in file, corresponding to utc_stop_time. (Float)

exists_on_disk

Whether the file is believed to exist on disk, or is a historical record of a deleted file. (Boolean, NOT NULL)

quality_checked

Whether quality has been checked; part of the unused QA loop. (Boolean)

product_id

This file is considered an instance of this product. (Integer, NOT NULL, FK product.product_id)

shasum

SHA-1 checksum of the file, calculated when ingested. (String)

process_keywords

Warning

This explanation may not be completely correct; this is not commonly used.

When a product has keyword substitutions in the filename format that are not directly calculatable by dbprocessing (not, e.g. date or version), the values of those keywords for this file are stored, allowing calculation of the filename. (Text)

filecodelink

Connects a single data file to the (single) code used to create it. A many-to-one relationship: many files are made from a single code.

resulting_file

ID of the file created. (Integer, NOT NULL, FK file.file_id)

source_code

ID of the code used to create resulting_file. (Integer, NOT NULL, FK code.code_id)

filefilelink

Connects a single data file to the (potentially many) input files used to create it. A many-to-many relationship: each file may serve as input to multiple output files, and each output file may be created from multiple inputs. This table is expressed as pairs: each row links one output file to one of its input files.

source_file

ID of the source (input) file for a particular pairing. (Integer, NOT NULL, FK file.file_id)

resulting_file

ID of the resulting (output) file for a particular pairing. (Integer, NOT NULL, FK file.file_id)

inspector

An inspector is a small piece of code which examines (inspects) a file to determine its product and various metadata for dbprocessing; this table describes the codes.

See also

inspector

inspector_id

Auto-incremented ID for this inspector, mostly for cross-referencing. (Integer, PK, NOT NULL)

filename

Filename (only) of inspector module. (String, NOT NULL)

relative_path

Directory containing filename, relative to mission.inspectordir. (String, NOT NULL)

description

Human-readable description of an inspector. May be considered a name but usually longer than a name and usually not used for lookup. (Text, NOT NULL)

interface_version

Version of the inspector. Full version is interface.quality.revision. (SmallInteger, NOT NULL)

quality_version

Version of the inspector. (SmallInteger, NOT NULL)

revision_version

Version of the inspector. (SmallInteger, NOT NULL)

output_interface_version

Warning

The purpose of this column is unclear.

Usually 1. May exist simply from copying the code definition. (SmallInteger, NOT NULL)

active_code

Whether this inspector is active, i.e. actually executed to determine potential matches between files and products. (Boolean, NOT NULL)

date_written

Date inspector was written; meant for human information only. (Date, NOT NULL)

shasum

SHA-1 checksum of the inspector file; meant for validation but not currently used. (String)

newest_version

Warning

This does not appear to be used, so its purpose is unclear.

Whether this is the newest version of a particular inspector. (Boolean, NOT NULL)

arguments

Space-separated list of key=value pairs, passed as keyword arguments to inspect. This allows the same file to be used as an inspector for multiple products, by using different arguments. (Text)

product

ID of the product which this inspector identifies. Every inspector can identify on, and only one, product. (Integer, NOT NULL, FK product.product_id)

instrument

Describes an instrument. An instrument is primarily a means of connecting related products for convenience (e.g. in queries and reprocessing); generally speaking it corresponds to a physical instrument. The hierarchy of association is instrument, satellite, mission, where each relation is many-to-one.

instrument_id

Auto-incremented ID for this instrument, mostly for cross-referencing. (Integer, PK, NOT NULL)

instrument_name

Name of the instrument, normally short to make it easy to use in command line queries. (String, NOT NULL)

satellite_id

ID of the satellite of which this instrument is part. (Integer, NOT NULL, FK satellite.satellite_id)

instrumentproductlink

Connects each instrument to the products it is associated with. This is a many-to-many link: an instrument may have its data in several products, and a product may draw from several instruments. Most commonly a product is associated with only one instrument; having multiple instruments per product is not heavily used or tested.

This table is expressed as pairs: each row links one product with one instrument.

Warning

The existence of product.instrument_id suggests a different approach than this.

instrument_id

ID of the instrument in a pairing. (Integer, NOT NULL, FK instrument.instrument_id)

product_id

ID of the product associated with the instrument in the same record. (Integer, NOT NULL, FK product.product_id)

logging

Log of the state of ProcessQueue.py invocations. Every run creates a single record in this table, recording the state of processing and how it terminated.

logging_id

Auto-incremented ID for each log entry, to maintain unique rows. (Integer, PK, NOT NULL)

currently_processing

Is this instance of ProcessQueue.py still running. There should only be one instance running at a time, so this is used as a lock (currentlyProcessing()). (Boolean, NOT NULL)

pid

Process ID of ProcessQueue.py. (Integer)

processing_start_time

When this instance of ProcessQueue.py started. (DateTime, NOT NULL)

processing_end_time

When this instance of ProcessQueue.py completed. (DateTime)

comment

How ProcessQueue.py exited. In the event the processing flag was cleared manually with clearProcessingFlag.py, this includes the message. (Text)

mission_id

ID of the mission on which this is executing. (Integer, NOT NULL, FK mission.mission_id)

user

Username running the ProcessQueue.py process. (String, NOT NULL)

hostname

Name of the host on which ProcessQueue.py is running. (String, NOT NULL)

logging_file

Warning

This table appears to be unused.

Likely intended to provide some sort of dbprocessing-level support for logging from data processing codes, but not used.

logging_file_id

(Integer, PK, NOT NULL)

logging_id

(Integer, NOT NULL, FK logging.logging_id)

file_id

(Integer, NOT NULL, FK file.file_id)

code_id

(Integer, NOT NULL, FK code.code_id)

comments

(Text)

mission

The mission for the data held in this database. A mission may pertain to multiple satellites, e.g. the Van Allen Probes mission had RBSP-A and RBSP-B. The hierarchy of association is instrument, satellite, mission, where each relation is many-to-one.

This is the top-level table determining where dbprocessing looks for files and codes.

Warning

In theory a single database can contain multiple missions; in practice, this has always been a one-to-one, and many parts of the codebase assume just one mission. In particular, most command line arguments to specify “mission” really specify the database.

A mission has many relevant directories; in older versions of the database, these were not all explicitly specified, and in newer versions they may often be null. In these cases a default is used; see getDirectory().

Note

Where specified in this table, directories are assumed to be absolute. If relative, they are relative to current directory, not any particular mission directory.

mission_id

Auto-incremented ID for this code, mostly for cross-referencing. (Integer, PK, NOT NULL)

mission_name

Human-readable name of this mission, should be short for easy use in command line queries. (String, NOT NULL)

rootdir

All data paths are specified relative to this directory. Code and related paths are not. (String, NOT NULL)

See also

getMissionDirectory()

incoming_dir

Directory from which new files are ingested for this mission. (String, NOT NULL)

See also

getIncomingPath()

codedir

Data processing codes are specified relative to this directory. (String)

See also

getCodeDirectory()

inspectordir

Inspector module paths are specified relative to this directory. (String)

See also

getInspectorDirectory()

errordir

Outputs of failed data processing codes, both file outputs and stdout, are placed in this directory, by default 'errors' in codedir. (String)

See also

getErrorPath()

process

A process, which converts files of input product(s) to a file of an output product.

process_id

Auto-incremented ID for this process, mostly for cross-referencing (Integer, PK, NOT NULL)

process_name

Human-readable name of this process, normally short to make it easy to use in command line queries. (String, NOT NULL)

output_product

ID of the single output product. (Integer, FK product.product_id)

output_timebase

Timebase of output files. (String)

extra_params

Arguments to add to the processing command line. These are added before the code’s arguments. (Text)

processqueue

Queue of files which are to be evaluated as potential inputs to processes see Process Queue. ProcessQueue.py -p will evaluate all products which can be built using these as inputs, and create any which are out of date.

file_id

ID of a file in the queue. (Integer, PK, NOT NULL, FK file.file_id)

version_bump

Requested approach to processing. By default, will only run processes if the outputs are out of date. If this is set, processes are forced to run, and the specified version component of the output is incremented (0 for interface version, 1 for quality, 2 for revision.) (SmallInteger)

See also

reprocessByProduct.py --force

product

A generalization or “type” of a file; every file is an instance of a product.

product_id

Auto-incremented ID for this product, mostly for cross-referencing. (Integer, PK, NOT NULL)

product_name

Human-readable name of the product, normally short to make it easy to use in command line queries. (String, NOT NULL)

instrument_id

The instrument providing data for this product. (Integer, NOT NULL, FK instrument.instrument_id)

Warning

The existence of instrumentproductlink suggests a different approach than this.

relative_path

Location where data files of this product are stored, relative to rootdir. May contain fields to be filled (e.g. {Y} to have a by-year directory). (String, NOT NULL)

level

Numerical level of this product; somewhat redundant with file.data_level. (Float, NOT NULL)

format

Filename template for files of this product. Normally contains fields to be filled (e.g. {Y} to include the year). (Text, NOT NULL)

product_description

Human-readable description of the product. Usually not used for queries. (Text)

productprocesslink

Relates processes to the products that they need as inputs. Each record pairs a process with one of its input products and describes that relationship.

process_id

ID of the process whose input product is described by this record. (Integer, NOT NULL, FK process.process_id)

input_product_id

ID of one input product for the process of this record. (Integer, NOT NULL, FK product.product_id)

optional

Whether input_product_id is an optional product, in which case the process can execute without it, or not. A product will only execute if all its required inputs are available. If all inputs are optional, it will only execute if at least one optional input is available. (Boolean, NOT NULL)

yesterday

Number of days in the past of this product to include as inputs to the process. For instance, if this is 2, then in processing day n, days n-1 and n-2 are also provided as inputs. Behavior is undefined for timebases other than DAILY. (Integer, NOT NULL)

tomorrow

As yesterday, but specifying days in the future. (Integer, NOT NULL)

release

Tracks the files that are present in a public release. For every release, each file in that release has a record in this table. This is a many-to-many relationship: each file may be in multiple releases, which may contain multiple files.

file_id

ID of the file which is included in the release. (Integer, NOT NULL, FK file.file_id)

release_num

Release number of which file_id is a part. (String, NOT NULL)

satellite

Describes an satellite. A satellite is primarily a means of connecting related products for convenience (e.g. in queries and reprocessing); generally speaking it corresponds to a physical instrument. The hierarchy of association is instrument, satellite, mission, where each relation is many-to-one.

satellite_id

Auto-incremented ID for this satellite, mostly for cross-referencing. (Integer, PK, NOT NULL)

satellite_name

Name of the satellite, normally short to make it easy to use in command line queries. (String, NOT NULL)

mission_id

ID of the mission of which this satellite is part. (Integer, NOT NULL, FK mission.mission_id)

unixtime

Stores the start and stop time for each file as a count of seconds since the Unix epoch. This makes certain lookups faster.

file_id

ID of the file for which this record stores the start/stop times. (Integer, PK, NOT NULL, FK file.file_id)

unix_start

utc_start_time for this file expressed as seconds since Unix epoch. (Integer)

unix_stop

utc_stop_time for this file expressed as seconds since Unix epoch. (Integer)

---

Release: 0.1.0 Doc generation date: Feb 10, 2022