90 lines
2.3 KiB
SQL
90 lines
2.3 KiB
SQL
BEGIN TRANSACTION;
|
|
DROP TABLE IF EXISTS "posreport";
|
|
CREATE TABLE IF NOT EXISTS "posreport" (
|
|
"id" INTEGER NOT NULL,
|
|
"mmsi" INTEGER NOT NULL,
|
|
"navstatus" INTEGER,
|
|
"rot" INTEGER,
|
|
"cog" INTEGER,
|
|
"sog" INTEGER,
|
|
"accuracy" INTEGER,
|
|
"longitude" INTEGER,
|
|
"latitude" INTEGER,
|
|
"heading" INTEGER,
|
|
"timestamp" TEXT,
|
|
"raim" INTEGER,
|
|
"commstate" INTEGER,
|
|
PRIMARY KEY("id" AUTOINCREMENT)
|
|
);
|
|
DROP TABLE IF EXISTS "staticdata";
|
|
CREATE TABLE IF NOT EXISTS "staticdata" (
|
|
"version" INTEGER,
|
|
"imo" INTEGER,
|
|
"callsign" TEXT,
|
|
"name" TEXT,
|
|
"shiptype" INTEGER,
|
|
"typeofdevice" INTEGER,
|
|
"maxpresetstaticdraught" INTEGER,
|
|
"destination" TEXT,
|
|
"dte" INTEGER,
|
|
"mmsi" INTEGER NOT NULL,
|
|
"classb" INTEGER,
|
|
"breadth" INTEGER,
|
|
"length" INTEGER,
|
|
"eta" TEXT,
|
|
"changed" TEXT,
|
|
"updatecount" INTEGER DEFAULT 0,
|
|
"created" TEXT DEFAULT current_timestamp,
|
|
PRIMARY KEY("mmsi")
|
|
);
|
|
DROP TABLE IF EXISTS "monitor_group";
|
|
CREATE TABLE IF NOT EXISTS "monitor_group" (
|
|
"id" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
PRIMARY KEY("id" AUTOINCREMENT)
|
|
);
|
|
DROP TABLE IF EXISTS "monitor_zone";
|
|
CREATE TABLE IF NOT EXISTS "monitor_zone" (
|
|
"id" INTEGER NOT NULL,
|
|
"name" TEXT,
|
|
"active" INTEGER,
|
|
"monitor_group_id" INTEGER NOT NULL,
|
|
"sequence" INTEGER,
|
|
FOREIGN KEY("monitor_group_id") REFERENCES "monitor_group"("id")
|
|
);
|
|
DROP TABLE IF EXISTS "zone_vertex";
|
|
CREATE TABLE IF NOT EXISTS "zone_vertex" (
|
|
"Id" INTEGER NOT NULL,
|
|
"monitor_zone_id" INTEGER,
|
|
"latitude" NUMERIC,
|
|
"longitude" NUMERIC,
|
|
FOREIGN KEY("monitor_zone_id") REFERENCES "monitor_zone"("id"),
|
|
PRIMARY KEY("Id" AUTOINCREMENT)
|
|
);
|
|
DROP TABLE IF EXISTS "zone_assignment";
|
|
CREATE TABLE IF NOT EXISTS "zone_assignment" (
|
|
"id" INTEGER NOT NULL,
|
|
"mmsi" INTEGER NOT NULL,
|
|
"monitor_zone_id" INTEGER NOT NULL,
|
|
"type" INTEGER,
|
|
FOREIGN KEY("monitor_zone_id") REFERENCES "monitor_zone"("id"),
|
|
PRIMARY KEY("id" AUTOINCREMENT)
|
|
);
|
|
DROP TABLE IF EXISTS "alarm";
|
|
CREATE TABLE IF NOT EXISTS "alarm" (
|
|
"id" INTEGER NOT NULL,
|
|
"zone_assignment_id" INTEGER NOT NULL,
|
|
"timestamp" TEXT,
|
|
"type" TEXT,
|
|
"acknowledged" INTEGER,
|
|
FOREIGN KEY("zone_assignment_id") REFERENCES "zone_assignment"("id"),
|
|
PRIMARY KEY("id" AUTOINCREMENT)
|
|
);
|
|
DROP TRIGGER IF EXISTS "static_update_cnt";
|
|
CREATE TRIGGER static_update_cnt AFTER UPDATE ON staticdata
|
|
BEGIN
|
|
UPDATE staticdata SET updatecount = OLD.updatecount + 1;
|
|
UPDATE staticdata SET changed = CURRENT_TIMESTAMP;
|
|
END;
|
|
COMMIT;
|