dm_sql Module

Predefined SQL statements as Fortran parameter strings.


Uses

  • module~~dm_sql~~UsesGraph module~dm_sql dm_sql module~dm_ascii dm_ascii module~dm_sql->module~dm_ascii

Used by

  • module~~dm_sql~~UsedByGraph module~dm_sql dm_sql module~dm_db dm_db module~dm_db->module~dm_sql module~dm_db_table dm_db_table module~dm_db_table->module~dm_sql module~dm_db_table->module~dm_db module~dmpack dmpack module~dmpack->module~dm_sql module~dmpack->module~dm_db module~dmpack->module~dm_db_table

Variables

Type Visibility Attributes Name Initial
integer, public, parameter :: SQL_TABLE_NODES = 1

Nodes table.
integer, public, parameter :: SQL_TABLE_SENSORS = 2

Sensors table.
integer, public, parameter :: SQL_TABLE_TARGETS = 3

Targets table.
integer, public, parameter :: SQL_TABLE_OBSERVS = 4

Observations table.
integer, public, parameter :: SQL_TABLE_RECEIVERS = 5

Receivers table.
integer, public, parameter :: SQL_TABLE_REQUESTS = 6

Requests table.
integer, public, parameter :: SQL_TABLE_RESPONSES = 7

Responses table.
integer, public, parameter :: SQL_TABLE_LOGS = 8

Logs table.
integer, public, parameter :: SQL_TABLE_IMAGES = 9

Images table.
integer, public, parameter :: SQL_TABLE_BEATS = 10

Heartbeats table.
integer, public, parameter :: SQL_TABLE_SYNC_NODES = 11

Sync nodes table.
integer, public, parameter :: SQL_TABLE_SYNC_SENSORS = 12

Sync sensors table.
integer, public, parameter :: SQL_TABLE_SYNC_TARGETS = 13

Sync targets table.
integer, public, parameter :: SQL_TABLE_SYNC_OBSERVS = 14

Sync observations table.
integer, public, parameter :: SQL_TABLE_SYNC_LOGS = 15

Sync logs table.
integer, public, parameter :: SQL_TABLE_SYNC_IMAGES = 16

Sync images table.
integer, public, parameter :: SQL_TABLE_LAST = 16

Never use this.
integer, public, parameter :: SQL_TABLE_NAME_LEN = 12

Max. length of table names.
character(len=*), public, parameter :: SQL_TABLE_NAMES(SQL_TABLE_LAST) = [character(len=SQL_TABLE_NAME_LEN)::'nodes', 'sensors', 'targets', 'observs', 'receivers', 'requests', 'responses', 'logs', 'images', 'beats', 'sync_nodes', 'sync_sensors', 'sync_targets', 'sync_observs', 'sync_logs', 'sync_images']

SQL table names.
character(len=*), public, parameter :: SQL_DROP_TABLE = "DROP TABLE IF EXISTS ?"
character(len=*), public, parameter :: SQL_SELECT_TABLES = "SELECT "//"(SELECT COUNT(*) FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'), "//"name "//"FROM sqlite_schema "//"WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
character(len=*), public, parameter :: SQL_SELECT_TABLE = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?"
character(len=*), public, parameter :: SQL_CREATE_BEATS = "CREATE TABLE IF NOT EXISTS beats("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"node_id   TEXT    NOT NULL UNIQUE,"//NL//"address   TEXT,"//NL//"client    TEXT,"//NL//"time_sent TEXT    NOT NULL DEFAULT '1970-01-01T00:00:00.000000+00:00',"//NL//"time_recv TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"error     INTEGER NOT NULL DEFAULT 0,"//NL//"interval  INTEGER NOT NULL DEFAULT 0,"//NL//"uptime    INTEGER NOT NULL DEFAULT 0) STRICT"
character(len=*), public, parameter :: SQL_CREATE_LOGS = "CREATE TABLE IF NOT EXISTS logs("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"id        TEXT    NOT NULL UNIQUE,"//NL//"level     INTEGER NOT NULL DEFAULT 0,"//NL//"error     INTEGER NOT NULL DEFAULT 0,"//NL//"timestamp TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"node_id   TEXT,"//NL//"sensor_id TEXT,"//NL//"target_id TEXT,"//NL//"observ_id TEXT,"//NL//"source    TEXT,"//NL//"message   TEXT) STRICT"
character(len=*), public, parameter :: SQL_CREATE_NODES = "CREATE TABLE IF NOT EXISTS nodes("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"id        TEXT NOT NULL UNIQUE,"//NL//"name      TEXT NOT NULL,"//NL//"meta      TEXT,"//NL//"x         REAL NOT NULL DEFAULT 0.0,"//NL//"y         REAL NOT NULL DEFAULT 0.0,"//NL//"z         REAL NOT NULL DEFAULT 0.0,"//NL//"longitude REAL NOT NULL DEFAULT 0.0,"//NL//"latitude  REAL NOT NULL DEFAULT 0.0,"//NL//"elevation REAL NOT NULL DEFAULT 0.0) STRICT"
character(len=*), public, parameter :: SQL_CREATE_SENSORS = "CREATE TABLE IF NOT EXISTS sensors("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"node_id   INTEGER NOT NULL,"//NL//"id        TEXT    NOT NULL UNIQUE,"//NL//"type      INTEGER NOT NULL DEFAULT 0,"//NL//"name      TEXT    NOT NULL,"//NL//"sn        TEXT,"//NL//"meta      TEXT,"//NL//"x         REAL    NOT NULL DEFAULT 0.0,"//NL//"y         REAL    NOT NULL DEFAULT 0.0,"//NL//"z         REAL    NOT NULL DEFAULT 0.0,"//NL//"longitude REAL    NOT NULL DEFAULT 0.0,"//NL//"latitude  REAL    NOT NULL DEFAULT 0.0,"//NL//"elevation REAL    NOT NULL DEFAULT 0.0,"//NL//"FOREIGN KEY (node_id) REFERENCES nodes(row_id)) STRICT"
character(len=*), public, parameter :: SQL_CREATE_TARGETS = "CREATE TABLE IF NOT EXISTS targets("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"id        TEXT    NOT NULL UNIQUE,"//NL//"name      TEXT,"//NL//"meta      TEXT,"//NL//"state     INTEGER NOT NULL DEFAULT 0,"//NL//"x         REAL    NOT NULL DEFAULT 0.0,"//NL//"y         REAL    NOT NULL DEFAULT 0.0,"//NL//"z         REAL    NOT NULL DEFAULT 0.0,"//NL//"longitude REAL    NOT NULL DEFAULT 0.0,"//NL//"latitude  REAL    NOT NULL DEFAULT 0.0,"//NL//"elevation REAL    NOT NULL DEFAULT 0.0) STRICT"
character(len=*), public, parameter :: SQL_CREATE_OBSERVS = "CREATE TABLE IF NOT EXISTS observs("//NL//"row_id     INTEGER PRIMARY KEY,"//NL//"node_id    INTEGER NOT NULL,"//NL//"sensor_id  INTEGER NOT NULL,"//NL//"target_id  INTEGER NOT NULL,"//NL//"id         TEXT    NOT NULL UNIQUE,"//NL//"name       TEXT    NOT NULL,"//NL//"timestamp  TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"source     TEXT,"//NL//"device     TEXT,"//NL//"priority   INTEGER NOT NULL DEFAULT 0,"//NL//"error      INTEGER NOT NULL DEFAULT 0,"//NL//"next       INTEGER NOT NULL DEFAULT 0,"//NL//"nreceivers INTEGER NOT NULL DEFAULT 0,"//NL//"nrequests  INTEGER NOT NULL DEFAULT 0,"//NL//"FOREIGN KEY (node_id)   REFERENCES nodes(row_id),"//NL//"FOREIGN KEY (sensor_id) REFERENCES sensors(row_id),"//NL//"FOREIGN KEY (target_id) REFERENCES targets(row_id)) STRICT"
character(len=*), public, parameter :: SQL_CREATE_RECEIVERS = "CREATE TABLE IF NOT EXISTS receivers("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"observ_id INTEGER NOT NULL,"//NL//"idx       INTEGER NOT NULL,"//NL//"name      TEXT    NOT NULL,"//NL//"FOREIGN KEY (observ_id) REFERENCES observs(row_id),"//NL//"UNIQUE      (observ_id, idx) ON CONFLICT REPLACE) STRICT"
character(len=*), public, parameter :: SQL_CREATE_REQUESTS = "CREATE TABLE IF NOT EXISTS requests("//NL//"row_id     INTEGER PRIMARY KEY,"//NL//"observ_id  INTEGER NOT NULL,"//NL//"idx        INTEGER NOT NULL,"//NL//"name       TEXT    NOT NULL,"//NL//"timestamp  TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"request    TEXT,"//NL//"response   TEXT,"//NL//"delimiter  TEXT,"//NL//"pattern    TEXT,"//NL//"delay      INTEGER NOT NULL DEFAULT 0,"//NL//"error      INTEGER NOT NULL DEFAULT 0,"//NL//"mode       INTEGER NOT NULL DEFAULT 0,"//NL//"retries    INTEGER NOT NULL DEFAULT 0,"//NL//"state      INTEGER NOT NULL DEFAULT 0,"//NL//"timeout    INTEGER NOT NULL DEFAULT 0,"//NL//"nresponses INTEGER NOT NULL DEFAULT 0,"//NL//"FOREIGN KEY (observ_id) REFERENCES observs(row_id),"//NL//"UNIQUE      (observ_id, idx) ON CONFLICT REPLACE) STRICT"
character(len=*), public, parameter :: SQL_CREATE_RESPONSES = "CREATE TABLE IF NOT EXISTS responses("//NL//"row_id     INTEGER PRIMARY KEY,"//NL//"request_id INTEGER NOT NULL,"//NL//"idx        INTEGER NOT NULL,"//NL//"name       TEXT,"//NL//"unit       TEXT,"//NL//"type       INTEGER NOT NULL DEFAULT 0,"//NL//"error      INTEGER NOT NULL DEFAULT 0,"//NL//"value      REAL    NOT NULL DEFAULT 0.0,"//NL//"FOREIGN KEY (request_id) REFERENCES requests(row_id),"//NL//"UNIQUE      (request_id, idx) ON CONFLICT REPLACE) STRICT"
character(len=*), public, parameter :: SQL_CREATE_SYNC_LOGS = "CREATE TABLE IF NOT EXISTS sync_logs("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"log_id    INTEGER NOT NULL UNIQUE,"//NL//"timestamp TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"code      INTEGER NOT NULL DEFAULT 0,"//NL//"attempts  INTEGER NOT NULL DEFAULT 0,"//NL//"FOREIGN KEY (log_id) REFERENCES logs(row_id)) STRICT"
character(len=*), public, parameter :: SQL_CREATE_SYNC_NODES = "CREATE TABLE IF NOT EXISTS sync_nodes("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"node_id   INTEGER NOT NULL UNIQUE,"//NL//"timestamp TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"code      INTEGER NOT NULL DEFAULT 0,"//NL//"attempts  INTEGER NOT NULL DEFAULT 0,"//NL//"FOREIGN KEY (node_id) REFERENCES nodes(row_id)) STRICT"
character(len=*), public, parameter :: SQL_CREATE_SYNC_OBSERVS = "CREATE TABLE IF NOT EXISTS sync_observs("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"observ_id INTEGER NOT NULL UNIQUE,"//NL//"timestamp TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"code      INTEGER NOT NULL DEFAULT 0,"//NL//"attempts  INTEGER NOT NULL DEFAULT 0,"//NL//"FOREIGN KEY (observ_id) REFERENCES observs(row_id)) STRICT"
character(len=*), public, parameter :: SQL_CREATE_SYNC_SENSORS = "CREATE TABLE IF NOT EXISTS sync_sensors("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"sensor_id INTEGER NOT NULL UNIQUE,"//NL//"timestamp TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"code      INTEGER NOT NULL DEFAULT 0,"//NL//"attempts  INTEGER NOT NULL DEFAULT 0,"//NL//"FOREIGN KEY (sensor_id) REFERENCES sensors(row_id)) STRICT"
character(len=*), public, parameter :: SQL_CREATE_SYNC_TARGETS = "CREATE TABLE IF NOT EXISTS sync_targets("//NL//"row_id    INTEGER PRIMARY KEY,"//NL//"target_id INTEGER NOT NULL UNIQUE,"//NL//"timestamp TEXT    NOT NULL DEFAULT (strftime('%FT%R:%f000+00:00')),"//NL//"code      INTEGER NOT NULL DEFAULT 0,"//NL//"attempts  INTEGER NOT NULL DEFAULT 0,"//NL//"FOREIGN KEY (target_id) REFERENCES targets(row_id)) STRICT"
character(len=*), public, parameter :: SQL_CREATE_BEATS_INDICES(1) = [character(len=64)::"CREATE INDEX IF NOT EXISTS idx_node_id ON beats(node_id)"]
character(len=*), public, parameter :: SQL_CREATE_LOGS_INDICES(8) = [character(len=64)::"CREATE INDEX IF NOT EXISTS idx_timestamp ON logs(timestamp)", "CREATE INDEX IF NOT EXISTS idx_level     ON logs(level)", "CREATE INDEX IF NOT EXISTS idx_error     ON logs(error)", "CREATE INDEX IF NOT EXISTS idx_node_id   ON logs(node_id)", "CREATE INDEX IF NOT EXISTS idx_sensor_id ON logs(sensor_id)", "CREATE INDEX IF NOT EXISTS idx_target_id ON logs(target_id)", "CREATE INDEX IF NOT EXISTS idx_observ_id ON logs(observ_id)", "CREATE INDEX IF NOT EXISTS idx_source    ON logs(source)"]
character(len=*), public, parameter :: SQL_CREATE_OBSERVS_INDICES(12) = [character(len=128)::"CREATE INDEX IF NOT EXISTS idx_nodes_id             ON nodes(id)", "CREATE INDEX IF NOT EXISTS idx_sensors_id           ON sensors(id)", "CREATE INDEX IF NOT EXISTS idx_targets_id           ON targets(id)", "CREATE INDEX IF NOT EXISTS idx_observs              ON observs(name, timestamp, error)", "CREATE INDEX IF NOT EXISTS idx_observs_timestamp    ON observs(timestamp)", "CREATE INDEX IF NOT EXISTS idx_receivers_idx        ON receivers(idx)", "CREATE INDEX IF NOT EXISTS idx_requests_idx         ON requests(idx)", "CREATE INDEX IF NOT EXISTS idx_requests_name        ON requests(name)", "CREATE INDEX IF NOT EXISTS idx_requests_timestamp   ON requests(timestamp)", "CREATE INDEX IF NOT EXISTS idx_responses            ON responses(request_id, idx, name, unit, type, error, value)", "CREATE INDEX IF NOT EXISTS idx_responses_request_id ON responses(request_id)", "CREATE INDEX IF NOT EXISTS idx_responses_name       ON responses(name)"]
character(len=*), public, parameter :: SQL_DELETE_OBSERV_TRIGGER = "CREATE TRIGGER IF NOT EXISTS delete_observ_trigger"//NL//"    BEFORE DELETE"//NL//"    ON observs"//NL//"BEGIN"//NL//"    DELETE FROM receivers WHERE observ_id = old.row_id;"//NL//"    DELETE FROM responses WHERE request_id IN"//NL//"        ("//NL//"            SELECT"//NL//"                requests.row_id"//NL//"            FROM"//NL//"                requests"//NL//"            INNER JOIN observs ON observs.row_id = requests.observ_id"//NL//"            WHERE observs.row_id = old.row_id"//NL//"        );"//NL//"    DELETE FROM requests WHERE observ_id = old.row_id;"//NL//"END"
character(len=*), public, parameter :: SQL_DELETE_BEAT = "DELETE FROM beats WHERE node_id = ?"
character(len=*), public, parameter :: SQL_DELETE_LOG = "DELETE FROM logs WHERE id = ?"
character(len=*), public, parameter :: SQL_DELETE_NODE = "DELETE FROM nodes WHERE id = ?"
character(len=*), public, parameter :: SQL_DELETE_SENSOR = "DELETE FROM sensors WHERE id = ?"
character(len=*), public, parameter :: SQL_DELETE_TARGET = "DELETE FROM targets WHERE id = ?"
character(len=*), public, parameter :: SQL_DELETE_OBSERV = "DELETE FROM observs WHERE id = ?"
character(len=*), public, parameter :: SQL_DELETE_RECEIVERS = "DELETE FROM receivers WHERE observ_id IN (SELECT row_id FROM observs WHERE id = ?)"
character(len=*), public, parameter :: SQL_DELETE_REQUESTS = "DELETE FROM requests WHERE observ_id IN (SELECT row_id FROM observs WHERE id = ?)"
character(len=*), public, parameter :: SQL_DELETE_REQUEST_RESPONSES = "DELETE FROM responses WHERE request_id IN "//"(SELECT row_id FROM requests "//"INNER JOIN observs ON observs.row_id = requests.observ_id "//"WHERE observs.id = ? AND requests.idx = ?)"
character(len=*), public, parameter :: SQL_DELETE_OBSERV_RESPONSES = "DELETE FROM responses WHERE request_id IN "//"(SELECT request_id FROM requests "//"INNER JOIN observs ON observs.row_id = requests.observ_id "//"WHERE observs.id = ?)"
character(len=*), public, parameter :: SQL_INSERT_BEAT = "INSERT INTO "//"beats(node_id, address, client, time_sent, time_recv, error, interval, uptime) "//"VALUES (?, ?, ?, ?, ?, ?, ?, ?) "//"ON CONFLICT DO UPDATE SET "//"node_id = excluded.node_id, "//"address = excluded.address, "//"client = excluded.client, "//"time_sent = excluded.time_sent, "//"time_recv = excluded.time_recv, "//"error = excluded.error, "//"interval = excluded.interval, "//"uptime = excluded.uptime"
character(len=*), public, parameter :: SQL_INSERT_LOG = "INSERT OR FAIL INTO "//"logs(id, level, error, timestamp, node_id, sensor_id, target_id, observ_id, source, message) "//"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
character(len=*), public, parameter :: SQL_INSERT_NODE = "INSERT OR FAIL INTO "//"nodes(id, name, meta, x, y, z, longitude, latitude, elevation) "//"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
character(len=*), public, parameter :: SQL_INSERT_SENSOR = "INSERT OR FAIL INTO "//"sensors(id, node_id, type, name, sn, meta, x, y, z, longitude, latitude, elevation) "//"VALUES (?, (SELECT row_id FROM nodes WHERE id = ?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
character(len=*), public, parameter :: SQL_INSERT_TARGET = "INSERT OR FAIL INTO "//"targets(id, name, meta, state, x, y, z, longitude, latitude, elevation) "//"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
character(len=*), public, parameter :: SQL_INSERT_OBSERV = "INSERT OR FAIL INTO "//"observs(id, node_id, sensor_id, target_id, name, timestamp, source, device, "//"priority, error, next, nreceivers, nrequests) "//"VALUES (?, "//"(SELECT row_id FROM nodes WHERE id = ?), "//"(SELECT row_id FROM sensors WHERE id = ?), "//"(SELECT row_id FROM targets WHERE id = ?), "//"?, ?, ?, ?, ?, ?, ?, ?, ?)"
character(len=*), public, parameter :: SQL_INSERT_RECEIVER = "INSERT OR FAIL INTO "//"receivers(observ_id, idx, name) "//"VALUES ((SELECT row_id FROM observs WHERE id = ?), ?, ?)"
character(len=*), public, parameter :: SQL_INSERT_REQUEST = "INSERT OR FAIL INTO "//"requests(observ_id, idx, name, timestamp, request, response, delimiter, pattern, "//"delay, error, mode, retries, state, timeout, nresponses) "//"VALUES ((SELECT row_id FROM observs WHERE id = ?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
character(len=*), public, parameter :: SQL_INSERT_RESPONSE = "INSERT OR FAIL INTO "//"responses(request_id, idx, name, unit, type, error, value) "//"VALUES ((SELECT requests.row_id FROM requests INNER JOIN observs ON observs.row_id = requests.observ_id WHERE observs.id = ? AND requests.idx = ?), "//"?, ?, ?, ?, ?, ?)"
character(len=*), public, parameter :: SQL_UPDATE_NODE = "UPDATE OR FAIL nodes SET name = ?, meta = ?, x = ?, y = ?, z = ?, longitude = ?, latitude = ?, elevation = ? WHERE id = ?"
character(len=*), public, parameter :: SQL_UPDATE_SENSOR = "UPDATE OR FAIL sensors SET node_id = (SELECT row_id FROM nodes WHERE id = ?), "//"type = ?, name = ?, sn = ?, meta = ?, x = ?, y = ?, z = ?, longitude = ?, latitude = ?, elevation = ? WHERE id = ?"
character(len=*), public, parameter :: SQL_UPDATE_TARGET = "UPDATE OR FAIL targets SET name = ?, meta = ?, state = ?, x = ?, y = ?, z = ?, "//"longitude = ?, latitude = ?, elevation = ? WHERE id = ?"
character(len=*), public, parameter :: SQL_HAS_LOG = "SELECT EXISTS(SELECT 1 FROM logs WHERE logs.id = ? LIMIT 1)"
character(len=*), public, parameter :: SQL_HAS_NODE = "SELECT EXISTS(SELECT 1 FROM nodes WHERE nodes.id = ? LIMIT 1)"
character(len=*), public, parameter :: SQL_HAS_OBSERV = "SELECT EXISTS(SELECT 1 FROM observs WHERE observs.id = ? LIMIT 1)"
character(len=*), public, parameter :: SQL_HAS_SENSOR = "SELECT EXISTS(SELECT 1 FROM sensors WHERE sensors.id = ? LIMIT 1)"
character(len=*), public, parameter :: SQL_HAS_TARGET = "SELECT EXISTS(SELECT 1 FROM targets WHERE targets.id = ? LIMIT 1)"
character(len=*), public, parameter :: SQL_SELECT_NBEATS = "SELECT COUNT(row_id) FROM beats"
character(len=*), public, parameter :: SQL_SELECT_NDATA_POINTS = "SELECT COUNT(observs.row_id) FROM observs "//"INNER JOIN nodes ON nodes.row_id = observs.node_id "//"INNER JOIN sensors ON sensors.row_id = observs.sensor_id "//"INNER JOIN targets ON targets.row_id = observs.target_id "//"INNER JOIN requests ON requests.observ_id = observs.row_id "//"INNER JOIN responses ON responses.request_id = requests.row_id"
character(len=*), public, parameter :: SQL_SELECT_NLOGS = "SELECT COUNT(row_id) FROM logs"
character(len=*), public, parameter :: SQL_SELECT_NOBSERVS = "SELECT COUNT(observs.row_id) FROM observs "//"INNER JOIN nodes ON nodes.row_id = observs.node_id "//"INNER JOIN sensors ON sensors.row_id = observs.sensor_id "//"INNER JOIN targets ON targets.row_id = observs.target_id"
character(len=*), public, parameter :: SQL_SELECT_NOBSERV_VIEWS = "SELECT COUNT(observs.row_id) FROM observs "//"INNER JOIN nodes ON nodes.row_id = observs.node_id "//"INNER JOIN sensors ON sensors.row_id = observs.sensor_id "//"INNER JOIN targets ON targets.row_id = observs.target_id "//"INNER JOIN requests ON requests.observ_id = observs.row_id "//"INNER JOIN responses ON responses.request_id = requests.row_id"
character(len=*), public, parameter :: SQL_SELECT_NSENSORS = "SELECT COUNT(sensors.row_id) FROM sensors "//"INNER JOIN nodes ON nodes.row_id = sensors.node_id"
character(len=*), public, parameter :: SQL_SELECT_BEATS = "SELECT "//"node_id, "//"address, "//"client, "//"time_sent, "//"time_recv, "//"error, "//"interval, "//"uptime "//"FROM beats"
character(len=*), public, parameter :: SQL_SELECT_DATA_POINTS = "SELECT "//"requests.timestamp, "//"responses.value "//"FROM observs "//"INNER JOIN nodes ON nodes.row_id = observs.node_id "//"INNER JOIN sensors ON sensors.row_id = observs.sensor_id "//"INNER JOIN targets ON targets.row_id = observs.target_id "//"INNER JOIN requests ON requests.observ_id = observs.row_id "//"INNER JOIN responses ON responses.request_id = requests.row_id"
character(len=*), public, parameter :: SQL_SELECT_LOGS = "SELECT "//"id, "//"level, "//"error, "//"timestamp, "//"node_id, "//"sensor_id, "//"target_id, "//"observ_id, "//"source, "//"message "//"FROM logs"
character(len=*), public, parameter :: SQL_SELECT_NODES = "SELECT "//"nodes.id, "//"nodes.name, "//"nodes.meta, "//"nodes.x, "//"nodes.y, "//"nodes.z, "//"nodes.longitude, "//"nodes.latitude, "//"nodes.elevation "//"FROM nodes"
character(len=*), public, parameter :: SQL_SELECT_OBSERV_IDS = "SELECT observs.id FROM observs "//"INNER JOIN nodes ON nodes.row_id = observs.node_id "//"INNER JOIN sensors ON sensors.row_id = observs.sensor_id "//"INNER JOIN targets ON targets.row_id = observs.target_id"
character(len=*), public, parameter :: SQL_SELECT_OBSERVS = "SELECT "//"observs.id, "//"nodes.id, "//"sensors.id, "//"targets.id, "//"observs.name, "//"observs.timestamp, "//"observs.source, "//"observs.device, "//"observs.priority, "//"observs.error, "//"observs.next, "//"observs.nreceivers, "//"observs.nrequests "//"FROM observs "//"INNER JOIN nodes ON nodes.row_id = observs.node_id "//"INNER JOIN sensors ON sensors.row_id = observs.sensor_id "//"INNER JOIN targets ON targets.row_id = observs.target_id"
character(len=*), public, parameter :: SQL_SELECT_OBSERV_VIEWS = "SELECT "//"observs.id, "//"nodes.id, "//"sensors.id, "//"targets.id, "//"observs.name, "//"observs.error, "//"requests.name, "//"requests.timestamp, "//"requests.error, "//"responses.name, "//"responses.unit, "//"responses.type, "//"responses.error, "//"responses.value "//"FROM observs "//"INNER JOIN nodes ON nodes.row_id = observs.node_id "//"INNER JOIN sensors ON sensors.row_id = observs.sensor_id "//"INNER JOIN targets ON targets.row_id = observs.target_id "//"INNER JOIN requests ON requests.observ_id = observs.row_id "//"INNER JOIN responses ON responses.request_id = requests.row_id"
character(len=*), public, parameter :: SQL_SELECT_RECEIVER = "SELECT receivers.name FROM receivers "//"INNER JOIN observs ON receivers.observ_id = observs.row_id "//"WHERE observs.id = ? AND receivers.idx = ?"
character(len=*), public, parameter :: SQL_SELECT_RECEIVERS = "SELECT receivers.name FROM receivers "//"INNER JOIN observs ON receivers.observ_id = observs.row_id "//"WHERE observs.id = ? ORDER BY receivers.idx ASC"
character(len=*), public, parameter :: SQL_SELECT_REQUEST = "SELECT "//"requests.name, "//"requests.timestamp, "//"requests.request, "//"requests.response, "//"requests.delimiter, "//"requests.pattern, "//"requests.delay, "//"requests.error, "//"requests.mode, "//"requests.retries, "//"requests.state, "//"requests.timeout, "//"requests.nresponses "//"FROM requests "//"INNER JOIN observs ON observs.row_id = requests.observ_id "//"WHERE observs.id = ? AND requests.idx = ?"
character(len=*), public, parameter :: SQL_SELECT_REQUESTS = "SELECT "//"requests.name, "//"requests.timestamp, "//"requests.request, "//"requests.response, "//"requests.delimiter, "//"requests.pattern, "//"requests.delay, "//"requests.error, "//"requests.mode, "//"requests.retries, "//"requests.state, "//"requests.timeout, "//"requests.nresponses "//"FROM requests "//"INNER JOIN observs ON observs.row_id = requests.observ_id "//"WHERE observs.id = ? ORDER BY requests.idx ASC"
character(len=*), public, parameter :: SQL_SELECT_RESPONSE = "SELECT "//"responses.name, "//"responses.unit, "//"responses.type, "//"responses.error, "//"responses.value "//"FROM responses "//"INNER JOIN requests ON requests.row_id = responses.request_id "//"INNER JOIN observs ON observs.row_id = requests.observ_id "//"WHERE observs.id = ? AND requests.idx = ? AND responses.idx = ?"
character(len=*), public, parameter :: SQL_SELECT_RESPONSES = "SELECT "//"responses.name, "//"responses.unit, "//"responses.type, "//"responses.error, "//"responses.value  "//"FROM responses "//"INNER JOIN requests ON requests.row_id = responses.request_id "//"INNER JOIN observs ON observs.row_id = requests.observ_id "//"WHERE observs.id = ? AND requests.idx = ? "//"ORDER BY responses.idx ASC"
character(len=*), public, parameter :: SQL_SELECT_SENSORS = "SELECT "//"sensors.id, "//"nodes.id, "//"sensors.type, "//"sensors.name, "//"sensors.sn, "//"sensors.meta, "//"sensors.x, "//"sensors.y, "//"sensors.z, "//"sensors.longitude, "//"sensors.latitude, "//"sensors.elevation "//"FROM sensors "//"INNER JOIN nodes ON nodes.row_id = sensors.node_id"
character(len=*), public, parameter :: SQL_SELECT_TARGETS = "SELECT "//"targets.id, "//"targets.name, "//"targets.meta, "//"targets.state, "//"targets.x, "//"targets.y, "//"targets.z, "//"targets.longitude, "//"targets.latitude, "//"targets.elevation "//"FROM targets"
character(len=*), public, parameter :: SQL_INSERT_SYNC_LOG = "INSERT INTO sync_logs(log_id, timestamp, code, attempts) "//"VALUES ((SELECT row_id FROM logs WHERE id = ?), ?, ?, ?) "//"ON CONFLICT DO UPDATE SET "//"log_id = excluded.log_id, "//"timestamp = excluded.timestamp, "//"code = excluded.code, "//"attempts = excluded.attempts"
character(len=*), public, parameter :: SQL_INSERT_SYNC_NODE = "INSERT INTO sync_nodes(node_id, timestamp, code, attempts) "//"VALUES ((SELECT row_id FROM nodes WHERE id = ?), ?, ?, ?) "//"ON CONFLICT DO UPDATE SET "//"node_id = excluded.node_id, "//"timestamp = excluded.timestamp, "//"code = excluded.code, "//"attempts = excluded.attempts"
character(len=*), public, parameter :: SQL_INSERT_SYNC_OBSERV = "INSERT INTO sync_observs(observ_id, timestamp, code, attempts) "//"VALUES ((SELECT row_id FROM observs WHERE id = ?), ?, ?, ?) "//"ON CONFLICT DO UPDATE SET "//"observ_id = excluded.observ_id, "//"timestamp = excluded.timestamp, "//"code = excluded.code, "//"attempts = excluded.attempts"
character(len=*), public, parameter :: SQL_INSERT_SYNC_SENSOR = "INSERT INTO sync_sensors(sensor_id, timestamp, code, attempts) "//"VALUES ((SELECT row_id FROM sensors WHERE id = ?), ?, ?, ?) "//"ON CONFLICT DO UPDATE SET "//"sensor_id = excluded.sensor_id, "//"timestamp = excluded.timestamp, "//"code = excluded.code, "//"attempts = excluded.attempts"
character(len=*), public, parameter :: SQL_INSERT_SYNC_TARGET = "INSERT INTO sync_targets(target_id, timestamp, code, attempts) "//"VALUES ((SELECT row_id FROM targets WHERE id = ?), ?, ?, ?) "//"ON CONFLICT DO UPDATE SET "//"target_id = excluded.target_id, "//"timestamp = excluded.timestamp, "//"code = excluded.code, "//"attempts = excluded.attempts"
character(len=*), public, parameter :: SQL_SELECT_NSYNC_LOGS = "SELECT COUNT(logs.row_id) FROM logs "//"LEFT JOIN sync_logs ON sync_logs.log_id = logs.row_id "//"WHERE sync_logs.log_id IS NULL OR sync_logs.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_NSYNC_NODES = "SELECT COUNT(nodes.row_id) FROM nodes "//"LEFT JOIN sync_nodes ON sync_nodes.node_id = nodes.row_id "//"WHERE sync_nodes.node_id IS NULL OR sync_nodes.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_NSYNC_OBSERVS = "SELECT COUNT(observs.row_id) FROM observs "//"LEFT JOIN sync_observs ON sync_observs.observ_id = observs.row_id "//"WHERE sync_observs.observ_id IS NULL OR sync_observs.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_NSYNC_SENSORS = "SELECT COUNT(sensors.row_id) FROM sensors "//"LEFT JOIN sync_sensors ON sync_sensors.sensor_id = sensors.row_id "//"WHERE sync_sensors.sensor_id IS NULL OR sync_sensors.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_NSYNC_TARGETS = "SELECT COUNT(targets.row_id) FROM targets "//"LEFT JOIN sync_targets ON sync_targets.target_id = targets.row_id "//"WHERE sync_targets.target_id IS NULL OR sync_targets.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_SYNC_LOGS = "SELECT "//"logs.id, "//"sync_logs.timestamp, "//"sync_logs.code, "//"sync_logs.attempts "//"FROM logs "//"LEFT JOIN sync_logs ON sync_logs.log_id = logs.row_id "//"WHERE sync_logs.log_id IS NULL OR sync_logs.code NOT IN (201, 409) "//"ORDER BY logs.timestamp ASC"
character(len=*), public, parameter :: SQL_SELECT_SYNC_NODES = "SELECT "//"nodes.id, "//"sync_nodes.timestamp, "//"sync_nodes.code, "//"sync_nodes.attempts "//"FROM nodes "//"LEFT JOIN sync_nodes ON sync_nodes.node_id = nodes.row_id "//"WHERE sync_nodes.node_id IS NULL OR sync_nodes.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_SYNC_OBSERVS = "SELECT "//"observs.id, "//"sync_observs.timestamp, "//"sync_observs.code, "//"sync_observs.attempts "//"FROM observs "//"LEFT JOIN sync_observs ON sync_observs.observ_id = observs.row_id "//"WHERE sync_observs.observ_id IS NULL OR sync_observs.code NOT IN (201, 409) "//"ORDER BY observs.timestamp ASC"
character(len=*), public, parameter :: SQL_SELECT_SYNC_SENSORS = "SELECT "//"sensors.id, "//"sync_sensors.timestamp, "//"sync_sensors.code, "//"sync_sensors.attempts "//"FROM sensors "//"LEFT JOIN sync_sensors ON sync_sensors.sensor_id = sensors.row_id "//"WHERE sync_sensors.sensor_id IS NULL OR sync_sensors.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_SYNC_TARGETS = "SELECT "//"targets.id, "//"sync_targets.timestamp, "//"sync_targets.code, "//"sync_targets.attempts "//"FROM targets "//"LEFT JOIN sync_targets ON sync_targets.target_id = targets.row_id "//"WHERE sync_targets.target_id IS NULL OR sync_targets.code NOT IN (201, 409)"
character(len=*), public, parameter :: SQL_SELECT_JSON_BEATS = "SELECT "//"json_object('node_id', node_id, 'address', address, 'client', client, "//"'time_sent', time_sent, 'time_recv', time_recv, 'error', error, "//"'interval', interval, 'uptime', uptime) "//"FROM beats"
character(len=*), public, parameter :: SQL_SELECT_JSON_LOGS = "SELECT "//"json_object('id', id, 'level', level, 'error', error, 'timestamp', timestamp, "//"'node_id', node_id, 'sensor_id', sensor_id, 'target_id', target_id, 'observ_id', observ_id, "//"'source', source, 'message', message) "//"FROM logs"
character(len=*), public, parameter :: SQL_SELECT_JSON_NODES = "SELECT "//"json_object('id', id, 'name', name, 'meta', meta, 'x', x, 'y', y, 'z', z, "//"'longitude', longitude, 'latitude', latitude, 'elevation', elevation) "//"FROM nodes"
character(len=*), public, parameter :: SQL_SELECT_JSON_SENSORS = "SELECT "//"json_object('id', sensors.id, 'node_id', nodes.id, 'type', sensors.type, 'name', 'sensors.name, "//"'sn', sensors.sn, 'meta', sensors.meta, 'x', sensors.x, 'y', sensors.y, 'z', sensors.z, "//"'longitude', sensors.longitude, 'latitude', sensors.latitude, 'elevation', sensors.elevation) "//"FROM sensors "//"INNER JOIN nodes ON nodes.row_id = sensors.node_id"
character(len=*), public, parameter :: SQL_SELECT_JSON_TARGETS = "SELECT "//"json_object('id', id, 'name', name, 'meta', meta, 'state', state, 'x', x, 'y', y, 'z', z, "//"'longitude', longitude, 'latitude', latitude, 'elevation', elevation) "//"FROM targets"