# DATABASE

This is the server side component for the robot hands system.

This is going to be split into a few parts:

1. The database, which stores the data
2. The receiver/responder, which receives data from the base stations and puts it into the database and responds with settings if appropriate
3. The frond-end, which reads from the database and serves the front-end (in another repo)

The idea is to make this in node quickly and then transfer it into go later.

## Database stuff

In the database there are two unifying concepts: The node and the temperature site. Data is received from nodes and can be retrieved by node id. Some nodes are assigned temperature sites, with each temperature site having at most one node, data can be retrieed by temperature site.

### Tables

#### Users table

This table is probably going to be handled by django, if not we need to figure out what else we have to add here.

- `name` the username
- `creation_time` when the entry was added, this is automatically set.

```
CREATE TABLE IF NOT EXISTS Users (
  name TEXT UNIQUE NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

#### Nodes table

This table lists all of the nodes and names for the nodes.
This does not give any type to the nodes, that is all done using groups.
`author` is a foreign key to the users table.

- `hardware_id` the node id, currently we use the mac address
- `friendly_name` a human readable name
- `creation_time` when the entry was added, this is automatically set.
- `author` the user that added the entry, must match an entry in the Users table

```
CREATE TABLE IF NOT EXISTS Nodes (
  hardware_id VARCHAR(255) UNIQUE NOT NULL,
  friendly_name TEXT NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  author TEXT NOT NULL,
  CONSTRAINT FOREIGN KEY (author) REFERENCES Users(name)
);
```

#### Groups table

This table defines different groups that exist

- `name` the group name
- `creation_time` when the entry was added, this is automatically set.
- `author` the user that added the entry, must match an entry in the Users table

```
CREATE TABLE IF NOT EXISTS Groups (
  name TEXT UNIQUE NOT NULL,
  author TEXT NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT FOREIGN KEY (author) REFERENCES Users(name)
);
```

#### Node2Group table

This table associates each node with its groups

- `node` is the hardware id of the node, it must match an entry in the `Nodes` table.
- `group` is the group name, it must match an entry is the `Groups` table.
- `author` the user that added the entry, must match an entry in the Users table
- `creation_time` when the entry was added, this is automatically set.

```
CREATE TABLE IF NOT EXISTS Node2Group (
  node VARCHAR(255) NOT NULL,
  node_group TEXT NOT NULL,
  author TEXT NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_pairing UNIQUE(node, node_group),
  CONSTRAINT FOREIGN KEY (author) REFERENCES Users(name),
  CONSTRAINT FOREIGN KEY (node) REFERENCES Nodes(hardware_id),
  CONSTRAINT FOREIGN KEY (node_group) REFERENCES Groups(name)
);
```

#### GroupType table

This defines the different group types

- `name` the group type name
- `author` the user that added the entry, must match an entry in the Users table
- `creation_time` when the entry was added, this is automatically set.

```
CREATE TABLE IF NOT EXISTS GroupType (
  name VARCHAR(255) UNIQUE NOT NULL,
  author TEXT NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT FOREIGN KEY (author) REFERENCES Users(name)
);
```

#### GroupParent table

This gives groups a hierarachy.

Make sure that we enforce that `parent != child`

- `child` the child group, must match an entry in the `Groups` table
- `parent` the parent group, must match an entry in the `Groups' table
- `author` the user that added the entry, must match an entry in the Users table
- `creation_time` when the entry was added, this is automatically set.

```
CREATE TABLE IF NOT EXISTS GroupParent (
  child TEXT NOT NULL,
  parent TEXT NOT NULL,
  author TEXT NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_relation UNIQUE(child, parent),
  CONSTRAINT FOREIGN KEY (author) REFERENCES Users(name),
  CONSTRAINT FOREIGN KEY (child) REFERENCES Nodes(hardware_id),
  CONSTRAINT FOREIGN KEY (parent) REFERENCES Nodes(hardware_id)
);
```

#### Group2Type table

This associates each group with a type

- `group` is the group name, it must match an entry is the `Groups` table.
- `type` the group type, it must match an entry in the `GroupType` table
- `author` the user that added the entry, must match an entry in the Users table
- `creation_time` when the entry was added, this is automatically set.

```
CREATE TABLE IF NOT EXISTS Group2Type (
  group_name TEXT NOT NULL,
  type TEXT NOT NULL,
  author TEXT NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_grouptype UNIQUE(group_name, type),
  CONSTRAINT FOREIGN KEY (author) REFERENCES Users(name),
  CONSTRAINT FOREIGN KEY (group_name) REFERENCES Groups(name),
  CONSTRAINT FOREIGN KEY (type) REFERENCES GroupType(name)
);
```

#### Measurement table

This stores all of the measurement data

- `source_node` the hardware id of the node that took the measurement, must match an entry in the `Nodes` table
- `reporting_node` the hardware id of the node that sent the data to the server, generally a basestation, must match an entry in the `Nodes` table
- `associated_group` the group associated with the node, generally a temperature site, must match an entry in the `Groups` table
- `collection_time` the time that the node took the measurement as reported by the node
- `server_received_time` the time that the server received the measurement data
- `temperature_18_inch` the temperature at the 18 inch probe in degrees C
- `temperature_36_inch` the temperature at the 36 inch probe in degrees C
- `device_temperature` the internal temperature of the controller on the node, in degrees C
- `ambient_temperature` the ambient temperature as measured by the on-board sensor (SHT40 or BME280 for now). This isn't really ambient temperature if the probe is in sunlight due to the case heating up.
- `relative_humidity` the relative humidity as measured by the on-board sensor. This is humidity inside the case, not necessarily the external humidity
- `barometric_pressure` the measured barometric pressure in hpa, measured using a BME280 at the base station
- `accelerometer_x` the measured acceleration along the x axis
  - on the probe the x axis is along the pipe, the point is in the +x direction and the brain case is -x
  - on the base station the x axis is left and right with +x pointing to the right and -x pointing to the left
- `accelerometer_y` the measured acceleration along the y axis
  - on the probe the y axis is left and right from the board, +y is to the right, -y is to the left
  - on the base station y is front to back (pitch), with +y at the back and -y at the front
- `accelerometer_z` the measured acceleration along the z axis 
  - on the probe the +z axis points out of the front of the board, -z out the back
  - on the base station +z is toward the top, -z is down
- `battery_charge_percent` is the measured remaining battery change is %, where 100% is full charge and 0% is our defined lowest acceptable charge
- `battery_voltage` is the measured battery voltage
- `remaining_battery_capacity` is the estimated remaining battery capacity is mAh

```
CREATE TABLE IF NOT EXISTS Measurement (
  source_node VARCHAR(255) NOT NULL,
  reporting_node VARCHAR(255) NOT NULL,
  associated_group TEXT NOT NULL,
  collection_time TIMESTAMP NOT NULL,
  server_received_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  temperature_18_inch FLOAT,
  temperature_36_inch FLOAT,
  device_temperature FLOAT NOT NULL,
  ambient_temperature FLOAT NOT NULL,
  relative_humidity FLOAT,
  barometric_pressure FLOAT,
  accelerometer_x FLOAT NOT NULL,
  accelerometer_y FLOAT NOT NULL,
  accelerometer_z FLOAT NOT NULL,
  battery_charge_percent FLOAT NOT NULL,
  battery_voltage FLOAT NOT NULL,
  remaining_battery_capacity FLOAT NOT NULL,
  CONSTRAINT device_measurement UNIQUE(source_node, collection_time),
  CONSTRAINT FOREIGN KEY (source_node) REFERENCES Nodes(hardware_id),
  CONSTRAINT FOREIGN KEY (reporting_node) REFERENCES Nodes(hardware_id),
  CONSTRAINT FOREIGN KEY (associated_group) REFERENCES Groups(name)
);
```

#### NodeRssiRecord table

This stores RSSI data

- `node` is the hardware id of the node measuring the rssi, must match an entry in the `Nodes` table
- `neighbour` is the hardware id of the node that sent the message used to get the rssi, must match an entry in the `Nodes` table
- `rssi` is the rssi value
- `collection_time` the time that the node took the measurement as reported by the node
- `server_received_time` the time that the server received the measurement data

```
CREATE TABLE IF NOT EXISTS NodeRssiRecord (
  node VARCHAR(255) NOT NULL,
  neighbor VARCHAR(255) NOT NULL,
  rssi INT NOT NULL,
  collection_time TIMESTAMP NOT NULL,
  server_received_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_thing UNIQUE(node, neighbor, collection_time),
  CONSTRAINT FOREIGN KEY (node) REFERENCES Nodes(hardware_id),
  CONSTRAINT FOREIGN KEY (neighbor) REFERENCES Nodes(hardware_id)
);
```

#### NodeFileManifest table

This holds the file manifests for each node

- `node` is the hardware id of the reporting node, must match an entry in the `Nodes` table
- `collection_time` the time that the node took the measurement as reported by the node
- `server_received_time` the time that the server received the measurement data
- `program_id` the id of the file
- `program_version` the file version

```
CREATE TABLE IF NOT EXISTS NodeFileManifest (
  node VARCHAR(255) NOT NULL,
  collection_time TIMESTAMP NOT NULL,
  server_received_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  program_id INT NOT NULL,
  program_version INT NOT NULL,
  CONSTRAINT unique_version UNIQUE(node, collection_time, program_id),
  CONSTRAINT FOREIGN KEY (node) REFERENCES Nodes(hardware_id)
);
```

#### NodeReportedStatus table

This holds the reported status from nodes.

- `node` is the hardware id of the reporting node, must match an entry in the `Nodes` table
- `collection_time` the time that the node sent the data, as reported by the node
- `server_received_time` the time that the server received the data
- `measurement_interval_minutes` the time between measurements in minutes
- `wake_window_length_minutes` the time that the node stays awake (basestation only)
- `offset_from_midnight_minutes` the time offset from midnight for that measurement times are aligned to
- `sleep_duration_minutes` the amonut of time that the node is set to sleep for
- `number_saved_measurements` the number of measurements saved in the nodes local memory
- `when_time_was_last_updated` the time when the node last received a time update

```
CREATE TABLE IF NOT EXISTS NodeReportedStatus (
  node VARCHAR(255) NOT NULL,
  collection_time TIMESTAMP NOT NULL,
  server_received_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  measurement_interval_minutes INT NOT NULL,
  wake_window_length_minutes INT NOT NULL,
  offset_from_midnight_minutes INT NOT NULL,
  sleep_duration_minutes INT NOT NULL,
  number_saved_measurements INT NOT NULL,
  when_time_was_last_updated TIMESTAMP NOT NULL,
  CONSTRAINT unique_status UNIQUE(node, collection_time),
  CONSTRAINT FOREIGN KEY (node) REFERENCES Nodes(hardware_id)
);
```

#### NodeConfiguration table

This holds the configuration data for the nodes. This sets the configuration, it doesn't report it.

- `node` is the hardware id of the node the settings are for, must match an entry in the `Nodes` table
- `author` the user that added the entry, must match an entry in the `Users` table
- `creation_time` the time when the entry was added
- `measurement_interval_minutes` the time the node should wait between measurements
- `offset_from_midnight_minutes` the offset from midnight that the measurements are aligned to
- `wake_window_length_minutes` how long the node stays awake, only relevant for basestations

```
CREATE TABLE IF NOT EXISTS NodeConfiguration (
  node VARCHAR(255) NOT NULL,
  author TEXT NOT NULL,
  creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  measurement_interval_minutes INT NOT NULL,
  offset_from_midnight_minutes INT NOT NULL,
  wake_window_length_minutes INT NOT NULL,
  CONSTRAINT FOREIGN KEY (author) REFERENCES Users(name),
  CONSTRAINT FOREIGN KEY (node) REFERENCES Nodes(hardware_id)
);
```