Database Schemas

Here we will provide a list of schemas that can and should be used to log environmental data

Various databases are available, we have used SQLite3 for all our work

Sensor data from Inator Devices

The database will have the following schema:

devlist

This table lists the devices/inators which are connected to the database. The columns are:

id - INTEGER NOT NULL, PRIMARY KEY & AUTOINCREMENT. This is the id number for the device name - TEXT. This is the name of the sensor, this is what will be used for naming device based tables num_sensors - INTEGER. How many sensors are attached to the device info - TEXT. Any comments or info on the device, such as where it is to be found type - TEXT. What type of data. For example is it a time series or individual files

The following SQL command will create the table

CREATE TABLE "devlist" ("id" INTEGER NOT NULL,"name" TEXT,"num_sensors" INTEGER,"info" TEXT, "type" TEXT, PRIMARY KEY("id" AUTOINCREMENT))

<device>_details

This table, where <device> is the name of the device as set in devlist, contains information on the sensors connected to the device, with a row per sensor. The columns are:

sens_id - INTEGER NOT NULL, PRIMARY KEY & AUTOINCREMENT. This is the id of the sensor attached to the device sens_name - TEXT, the name of the sensor connected to the device. measures - TEXT, description of what the sensor is measuring returns - TEXT, what the sensor returns, e.g. a voltage or the actual value calib - TEXT, any calibration that needs applying range - TEXT, the range the measurement can cover info - TEXT, info on the sensor. For example, make, serial number comments - TEXT, any comments about the sensor, for example position

The following SQL command will create the table

CREATE TABLE "<device>_details"("sens_id" INTEGER NOT NULL, "sens_name" TEXT, "measures" TEXT, "returns" TEXT, "calib" TEXT, "range" TEXT, "info" TEXT, "comments" TEXT, PRIMARY KEY("sens_id" AUTOINCREMENT));

<device>

This table, where <device> is the name of the device as set in devlist, where there is a row for each data entry. The columns are:

id - INTEGER NOT NULL, PRIMARY KEY & AUTOINCREMENT. The id of the data entry timestamp - TIME, the time that the data entry was recorded <sens_name> - FLOAT, the name of the sensor (as given in _details). There is one column for each sensor on the device

The following SQL command will create the table, adding a <sens_name> FLOAT for each sensor

CREATE TABLE "<device>"("id" INTEGER NOT NULL, timestamp Time, <sens_name> FLOAT, PRIMARY KEY("id" AUTOINCREMENT))

Querying Database