123 lines
3.7 KiB
SQL
Executable File
123 lines
3.7 KiB
SQL
Executable File
--CREATE EXTENSION IF NOT EXISTS postgis;
|
|
CREATE EXTENSION IF NOT EXISTS hstore;
|
|
|
|
CREATE DATABASE pager_data;
|
|
CREATE USER admin WITH ENCRYPTED PASSWORD '1234';
|
|
GRANT ALL PRIVILEGES ON DATABASE pager_data TO admin;
|
|
|
|
-- === Devices ===
|
|
CREATE TABLE Devices (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
euid VARCHAR(16) NOT NULL UNIQUE, -- LoRaWAN Extended Unique ID (hex, 8 bytes)
|
|
name VARCHAR(64) NOT NULL, -- ! Not Unique
|
|
|
|
fw_version VARCHAR(24) NOT NULL,
|
|
|
|
charge NUMERIC(5,2) CHECK (charge >= 0 AND charge <= 100), -- battery in %
|
|
last_online TIMESTAMPTZ, -- ! NULL
|
|
|
|
-- Location fields:
|
|
latitude DOUBLE PRECISION CHECK (latitude >= -90 AND latitude <= 90),
|
|
longitude DOUBLE PRECISION CHECK (longitude >= -180 AND longitude <= 180),
|
|
altitude DOUBLE PRECISION
|
|
);
|
|
|
|
CREATE TABLE Messages (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
sender_id INT NOT NULL REFERENCES Devices(id) ON DELETE SET NULL,
|
|
receiver_id INT REFERENCES Devices(id) ON DELETE SET NULL, -- ! NULL
|
|
payload VARCHAR(256)
|
|
);
|
|
|
|
CREATE TABLE Statuses (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
device_id INT NOT NULL REFERENCES Devices(id) ON DELETE CASCADE,
|
|
payload VARCHAR(256)
|
|
);
|
|
|
|
-- === Roles ===
|
|
CREATE TABLE Roles (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
name VARCHAR(64) NOT NULL UNIQUE,
|
|
description VARCHAR(128) NOT NULL DEFAULT 'Role description'
|
|
);
|
|
|
|
INSERT INTO Roles (name, description) VALUES ('Admin', 'Should do everything.'); -- Admin role
|
|
|
|
CREATE TABLE Permissions (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
name VARCHAR(64) NOT NULL UNIQUE,
|
|
description VARCHAR(128) NOT NULL DEFAULT 'Permission description'
|
|
);
|
|
|
|
INSERT INTO Permissions (name, description) VALUES ('Everything', 'Can do everything.'); -- Admin permission
|
|
|
|
-- Join table for Roles & Permissions
|
|
CREATE TABLE RolesPermissions (
|
|
role_id INT NOT NULL REFERENCES Roles(id),
|
|
permission_id INT NOT NULL REFERENCES Permissions(id)
|
|
);
|
|
|
|
INSERT INTO RolesPermissions (role_id, permission_id) VALUES (1, 1); -- Tie together role & permission
|
|
|
|
-- === Users ===
|
|
CREATE TABLE Users (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
name VARCHAR(64) UNIQUE NOT NULL,
|
|
password VARCHAR(64) NOT NULL,
|
|
role_id INT DEFAULT 2 REFERENCES Roles(id) ON DELETE SET DEFAULT, -- default low privilleges
|
|
last_online TIMESTAMPTZ -- ! NULL
|
|
);
|
|
|
|
INSERT INTO Users (name, password, role_id) VALUES ('Admin', 'admin', 1); -- Admin user
|
|
|
|
CREATE TABLE NFC_Cards (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
user_id INT REFERENCES Users(id) ON DELETE SET NULL, -- ! NULL
|
|
device_id INT REFERENCES Devices(id) ON DELETE SET NULL, -- ! NULL
|
|
uid VARCHAR(7) NOT NULL UNIQUE,
|
|
CONSTRAINT uid_length CHECK (length(uid) = 4 OR length(uid) = 7) -- Check for valid UUID
|
|
);
|
|
|
|
-- === Logs ===
|
|
CREATE TABLE Logs (
|
|
id SERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
db_user TEXT DEFAULT current_user
|
|
);
|
|
|
|
CREATE TABLE LogsUsers (
|
|
log_id INT REFERENCES Logs(id) ON DELETE CASCADE,
|
|
user_id INT REFERENCES Users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE LogsDevices (
|
|
log_id INT REFERENCES Logs(id) ON DELETE CASCADE,
|
|
device_id INT REFERENCES Devices(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE Changes (
|
|
id SERIAL PRIMARY KEY,
|
|
log_id INT NOT NULL REFERENCES Logs(id) ON DELETE CASCADE,
|
|
table_name TEXT NOT NULL,
|
|
operation TEXT NOT NULL,
|
|
old_value hstore, -- ! NULL
|
|
new_value hstore, -- ! NULL
|
|
CONSTRAINT change_diff CHECK (old_value != new_value)
|
|
);
|