picc-k8s/deployments/postgres/piccdb.sql

BEGIN;

DROP TABLE IF EXISTS picc_events;
DROP TABLE IF EXISTS picc_locations;

CREATE TABLE picc_locations
(
    id            SERIAL PRIMARY KEY,
    location_name TEXT NOT NULL
    -- TODO: figure out PostGIS
);

INSERT INTO picc_locations (id, location_name)
VALUES (0, 'Unknown');
INSERT INTO picc_locations (id, location_name)
VALUES (1, 'Not Applicable');

DROP TABLE IF EXISTS picc_events;

CREATE TABLE picc_events
(
    id             BIGSERIAL PRIMARY KEY,                                                                 -- ID
    log_time       TIMESTAMP with time zone NOT NULL                           DEFAULT CURRENT_TIMESTAMP, -- Time when event was logged
    event_time     TIMESTAMP with time zone,                                                              -- Optional: time when event was generated, can be different than log_time
    event_location integer                  NOT NULL references picc_locations DEFAULT 0,                 -- Location associated with event
--     sender         text                     NOT NULL, -- Sender is implicit in whoever owns the endpoint
    endpoint       text
        CONSTRAINT valid_endpoint CHECK ( endpoint ~ '^([A-Za-z0-9_-]+\.)*([A-Za-z0-9_-]+)$' ),             -- Event endpoint
    body           jsonb                    NOT NULL                                                      -- Event body content
);

CREATE INDEX idx_picc_events_endpoint ON picc_events (endpoint);

-- DROP TABLE IF EXISTS mqtt;
--
-- CREATE TABLE picc_mqtt
-- (
--     id           BIGSERIAL PRIMARY KEY,
--     receive_time TIMESTAMP,
--     routing_key  TEXT,
--     payload      TEXT
-- );

COMMIT;