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)

Stephan's Solution

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.

Database changes

Create Device Nirvana location

PermafrostDB (R-package) changes

Behavioural Changes

When swapping loggers in the field, must record which loggers were swapped (e.g. replaced E50xxx with E50yyy at site z on 'YYYYMMDD HHMMSS')

SensorDB Changes

Provide a way to 'swap' devices if not done in PermafrostDB
In DbApiMemory and DbApiJdbc modify getDeviceFromSerialNumber to return List<Device> or make a new method. Remove checks that throw exceptions when multiple devices are returned
SensorFileImporter.importFile() needs some changes:
1. First get all devices with the serial number
2. 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
3. This all has to get moved inside insertSample and its a mess.

Microservices (PermafrostNet) changes

Probably none

Link table Solution

Add a new table (devices_sensors) that links the devices and sensors tables with a timestamp, similar to the behaviour of devices_locations

Database changes

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 using devices.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)

Migration code

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');

Major Upgrade: decoupling devices and sensors - geocryology/sensorDb GitHub Wiki

Stephan's Solution

Database changes

PermafrostDB (R-package) changes

Behavioural Changes

SensorDB Changes

Microservices (PermafrostNet) changes

Link table Solution

Database changes

Migration code

PermafrostDB (R-package) changes

Behavioural Changes

SensorDB Changes

Microservices (PermafrostNet) changes

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️