Data model

This page describeds the data model of the database which stores device events (e.g. observations, actuations, etc.).

Expected queries

• Get last observations by system id
• Get observations by system id and the start and end date time
• Get last command results performed by a device with the given system id
• Get command results performed by a device with the given system id and filtered by the start and end date time

Things to store

Observations

Observation:

• Sensor ID (extracted from ssn:observedBy)
• System ID (extracted from the topic name)
• XSD Date Time (ssn:observationResultTime)
• URI of the observed property (ssn:observedProperty)
• URI of the feature of interest (ssn:featureOfInterest)
• Observation value

Commands & Command Results

Command Result:

• System ID
• XSD Date Time when command was performed
• value:
  • Command

Command:

• System ID
• Parameters:
  • URI of the parameter (semiot:forParameter)
  • Value of the parameter

Queries

CREATE KEYSPACE IF NOT EXISTS semiot WITH replication = {
  'class': 'SimpleStrategy', 
  'replication_factor': 1
} ;

Observations

CREATE TABLE IF NOT EXISTS observation (
  sensor_id text,
  system_id text,
  event_time timestamp,
  property text,
  feature_of_interest text,
  observation_value text,
  PRIMARY KEY ((system_id,sensor_id),event_time)
)
WITH CLUSTERING ORDER BY (event_time DESC);

Commands & Command Results

CREATE TYPE IF NOT EXISTS command_parameter (
  for_parameter text,
  value text
);

CREATE TYPE IF NOT EXISTS command_property (
  property text,
  value text,
  datatype text
);

CREATE TABLE IF NOT EXISTS commandresult (
  system_id text,
  process_id text,
  event_time timestamp,
  command_type text,
  command_properties list<frozen <command_property>>,
  command_parameters list<frozen <command_parameter>>,
  PRIMARY KEY((system_id, process_id), event_time)
)
WITH CLUSTERING ORDER BY (event_time DESC);