# 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 Code that will create this database and log to it is available at the repository [text](https://gitlab.com/inator-project/inator_mqtt) ## 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. `connection` - TEXT. What is the method of connection - allowed to be (Serial, MQTT, Other). The following SQL command will create the table ```SQL CREATE TABLE "devlist" ("id" INTEGER NOT NULL,"name" TEXT,"num_sensors" INTEGER,"info" TEXT, "type" TEXT, "location" TEXT, "active" INTEGER, "connection" TEXT, PRIMARY KEY("id" AUTOINCREMENT)) ``` ### \_\_details This table, where `` 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 ```SQL CREATE TABLE "__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)); ``` ### \_\ This table, where `` 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_` - 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 FLOAT for each sensor ```SQL CREATE TABLE "_"("id" INTEGER NOT NULL, timestamp Time, 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 `\_details` table for the Inator 3 Populate the `\_details` table with the sensor information for the Inator. This should be as full as possible. 4 Create the `\` table with a column for each sensor that is attached to the Inator These steps can be chained together into one database transaction A interactive method is available using the [dashboard programme](https://gitlab.com/inator-project/Dashboard) if it is run with the -setup cli option then devices can be added ## JSON Schema A JSON Schema has been defined for each inator device when adding to the database ```json { "$schema": "http://json-schema.org/draft-04/schema#", "type": "object", "properties": { "devices": { "type": "array", "items": { "type": "object", "properties": { "device_name": {"type": "string"}, "device_guid": {"type": "string"}, "num_sensors": {"type": "integer"}, "device_info": {"type": "string"}, "device_type": {"type": "string"}, "device_location": {"type": "string"}, "device_active": {"type": "integer"}, "connection": { "type": "string", "enum": ["Serial", "MQTT", "Other"], }, "sensors": { "type": "array", "items": { "type": "object", "properties": { "sens_name": {"type": "string"}, "measures": {"type": "string"}, "returns": {"type": "string"}, "calib": {"type": "string"}, "range": {"type": "string"}, "info": {"type": "string"}, "comments": {"type": "string"}, }, "required": [ "sens_name", "measures", "returns", "calib", "range", "info", "comments", ], }, }, }, "required": [ "device_name", "device_guid", "num_sensors", "device_info", "device_type", "device_location", "device_active", "connection", "sensors", ], }, } }, "required": ["devices"], } ``` ## Querying Database The Database can now be queried to find out what devices are reporting here and what sensors are connected.