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. This must be unique

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

location - TEXT. Where the device is located

active - INTEGER. Is the device in use, 1 yes 2 no.

The following SQL command will create the table

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

id_<device>_details

This table, where <device> is the name of the device as set in devlist and id is the device id, 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 "id_<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));

id_<device>

This table, where <device> is the name of the device as set in devlist and id is the device id, 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

id_<sens_id> - INTEGER, the id 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 "id_<device>"("id" INTEGER NOT NULL, timestamp Time, id_<sens_id> INTEGER, PRIMARY KEY("id" AUTOINCREMENT))

Setting up an Inator

To set up an inator into the database the following method should be used.

This assumes that the database has been already set up and that the devlist table exists.

1 Add the Inator name to the devlist table

2 Create the \<device\>_details table for the Inator

3 Populate the \<device\>_details table with the sensor information for the Inator. This should be as full as possible.

4 Create the \<device\> table with a column for each sensor that is attached to the Inator

These steps can be chained together into one database transaction

Querying Database

The Database can now be queried to find out what devices are reporting here and what sensors are connected.