12 KiB

Raw Permalink Blame History

DATABASE

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

This is going to be split into a few parts:

The database, which stores the data
The receiver/responder, which receives data from the base stations and puts it into the database and responds with settings if appropriate
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)
);

12 KiB Raw Permalink Blame History