Major Upgrade: decoupling devices and sensors - geocryology/sensorDb GitHub Wiki
We need to handle cases where the device is moved to a different sensor (either because the sensors die, or for convenience instead of changing batteries in the field)
Treat each devices.id
as a unique combination of device + sensor. Create a new devices entry whenever the device + sensor combination is changed, and "move" the old device (with identical devices.serial_number
) to a new location
("device nirvana") with record_observations=FALSE
.
- Create
Device Nirvana
location
- When swapping loggers in the field, must record which loggers were swapped (e.g. replaced E50xxx with E50yyy at site z on 'YYYYMMDD HHMMSS')
- Provide a way to 'swap' devices if not done in PermafrostDB
- In
DbApiMemory
andDbApiJdbc
modifygetDeviceFromSerialNumber
to returnList<Device>
or make a new method. Remove checks that throw exceptions when multiple devices are returned -
SensorFileImporter.importFile()
needs some changes:- First get all devices with the serial number
- then for each observation: a. find out where each of those devices was b. get the device D_0 for one that was "active" (i.e. not in "device nirvana") c. make sure that one is unique d. make a HashMap(Label,Sensor) for that device
- This all has to get moved inside
insertSample
and its a mess.
- Probably none
Add a new table (devices_sensors
) that links the devices and sensors tables with a timestamp, similar to the behaviour of devices_locations
- Create new table
devices_sensors
with the following fields:
name | type |
---|---|
id |
UUID |
timestamp |
TIMESTAMP(6) WITH TIME ZONE |
device_id |
UUID |
sensor_id |
UUID |
notes |
text |
- Populate
devices_sensors
, linking all existing sensor-device combinations usingdevices.acquired on
as the timestamp - For all "human observation" sensors, use arbitrary early date as timestamp
- Ensure there are no other devices that are missing timestamp
- Remove
sensors.device_id
foreign key? - Update SQL statements used to create database to reflect changes
- Create "No Device" device (for orphaned sensors who have had their sensor removed)
- First, close connections to database (block port through openstack)
- stop sensordb
- Then, make a backup copy of the database
CREATE DATABASE observations_legacy WITH TEMPLATE observations OWNER postgres;
or if errors:
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'observations' AND pid <> pg_backend_pid();
- then connect to observations and:
/* # create new table # */
CREATE TABLE public.devices_sensors(
id uuid NOT NULL DEFAULT uuid_generate_v4(),
timestamp timestamp WITH TIME ZONE NOT NULL,
device_id uuid NOT NULL,
sensor_id uuid NOT NULL,
notes text,
CONSTRAINT device_sensor_pk PRIMARY KEY (id)
);
ALTER TABLE public.devices_sensors OWNER TO observations_admin;
ALTER TABLE public.devices_sensors ADD CONSTRAINT device_sensor_fk_device FOREIGN KEY (device_id)
REFERENCES public.devices (id) MATCH FULL
ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE public.devices_sensors ADD CONSTRAINT device_sensor_fk_sensor FOREIGN KEY (sensor_id)
REFERENCES public.sensors (id) MATCH FULL
ON DELETE NO ACTION ON UPDATE NO ACTION;
GRANT SELECT on devices_sensors to observations_read;
GRANT SELECT, INSERT, UPDATE, DELETE on devices_sensors to observations_write;
/* copy data */
INSERT INTO devices_sensors (timestamp, device_id, sensor_id, notes)
SELECT devices.acquired_on as timestamp,
devices.id as device_id,
sensors.id as sensor_id,
'Interpreted from old devices.aquired_on' as notes
FROM devices
INNER JOIN sensors ON sensors.device_id = devices.id;
/* Create 'No Device' device */
INSERT INTO devices (device_type, manufacturer, acquired_on, notes)
VALUES (
'no device',
'generic device',
make_timestamptz(1900, 1, 1, 00, 00, 00, 'UTC'),
'This devices links to sensors that have been disconnected from a device.'
)
RETURNING id;
/* */
/* == Later == # */
/* delete constraints */
ALTER TABLE public.sensors DROP CONSTRAINT fk_device_id;
/* delete field sensors.device_id */
ALTER TABLE public.sensors DROP COLUMN device_id;
/* Checks */
/* All sensors linked to a device */
SELECT sensors.id, sensors.label, devices_sensors.id
FROM sensors
LEFT JOIN devices_sensors
ON sensors.id = devices_sensors.sensor_id
WHERE devices_sensors.id IS NULL;
/* can select manual obs */
SELECT sensors.label
FROM sensors
INNER JOIN devices_sensors ON sensors.id = devices_sensors.sensor_id
INNER JOIN devices ON devices.id = devices_sensors.device_id
WHERE devices.device_type = 'human observation';
/* can select sensor */
SELECT sensors.label,
devices.manufacturer_device_name,
devices_sensors.timestamp as added_to_device_on
FROM sensors
INNER JOIN devices_sensors ON sensors.id = devices_sensors.sensor_id
INNER JOIN devices ON devices.id = devices_sensors.device_id
WHERE devices.id = 'cd28efd8-2852-4c79-82db-f5169d37901d'
AND devices_sensors.timestamp > make_timestamptz(1900, 1, 1, 00, 00, 00, 'UTC');
- Re-open connections to database (through openstack)
- No changes to
sensorDb-command
- Modify
Sensor
object- Remove any Java representation of the
sensors.device_id
field- Deal with any calls to
sensor.setDeviceId()
- Remove attribute
deviceId
- Delete or deprecate methods
sensor.setDeviceId()
andsensor.getDeviceId()
- any
resultSet
returned from the database when creating a sensor needs indices incremented
- Deal with any calls to
- Remove any Java representation of the
- Create new
DeviceSensor
object that looks likeDeviceLocation
- Modify
DbApiJdbc
object (and alsoDbApiMemory()
for testing purposes and also in theDbAPI
interface)- Create new method
createDeviceSensor
- Create new method
getSensorFromSensorId
- Create new method
getDeviceSensor
- Create new method
getDeviceSensorFromDeviceId
- Create new method
getSensorFromDeviceSensor
- Modify
createSensor
- do not include
device_id
(will need to link it to a device elsewhere) - update indices of resultset and database statement values
- do not include
- Modify
createDevice
: don'tsetDeviceId
for created sensors. Instead, create a new record indevices_sensors
usingcreateDeviceSensor
(what happens if this crashes: orphaned sensors? what needs to be checked? can we access theid
of both the sensor (yes, fromcreateSensor
) and device (yes - fromresultSet
) as they're being created?)
- Create new method
- Changes to
DbServletActions.java
- Create method
addDeviceSensor
- Create method
buildDeviceSensorJson
- Create method
getDeviceSensors
- Modify
getSensorsFromDeviceId
or creategetSensorsFromDeviceIdAtTimestamp
to only get sensors at particular time - Modify
buildSensorJson
to not include
- Create method
- Changes to
sensorDb-upload-observations/*
- Create class
DeviceSensorHistory
analogous toDeviceLocator
- create method
getSensorsFromTimestamp
(public List<Sensor> getSensorsFromTimestamp(Date timestamp)
)
- create method
- Create class
- Changes to
SensorFileImporter
(I think this is where things get tough)- Currently, the class gets all sensors through a call to
dbAPI.getSensorsFromDeviceId(device_id)
. Then checks to make sure each label points to a single sensor and that all columns (labels have a sensor) (a bijection?). We will start to return duplicate sensors-per-label. The methodinsertSample
takes theSensor
object and aDeviceLocator
which uses the timestamp to get the proper location. There would be an analagous solution where we pass a label and aDeviceSensorInventory
. But we should still check that there aren't duplicate/missing sensor:label relations for that particular timestep. - create new class
DeviceSensorHistory(List<deviceSensor> devicesSensors, List<Sensor> Sensors)
with methodList<Sensor> sensorsFromTimestamp(Date correctedTime)
- Create new class
SensorSelector(List<Sensor> sensors, List<String> labels) with method
Sensor sensorFromLabel(String label). This creates the
HashMap<String,Sensor>that is currently done in
SensorFileImporter.importFileand performs the necessary checks. The
SensorSelectoris passed to
insertSamplealong with the
String sensorLabel` for a particular observation. - New overloaded method
insertSample
that doesn't yet know which sensor to take (given a label instead) - but how and when to check that all required labels exist?
- Currently, the class gets all sensors through a call to
- Deal with the creation of new sensor-device combinations from a template
- we'll have to also make the timestamped linking record
- traces back to
getSensorsFromDeviceId
- Testing
- Review and modify existing tests where the tests use sensor.deviceId
- Create new tests (following examples from analogous classes and their tests)
- New major version
- Traceability
- Create new interface like
ImportReport
.. maybeDeviceSwapReport
? - Implement
DeviceSwapReportMemory
- [ ]
- Create new interface like
- Observations API
- No changes (
devices
don't come into the picture)
- No changes (