core-legacy/sql/migration_0.sql

-- base migration

CREATE TABLE controllers
(
    id      INTEGER
            PRIMARY KEY
            AUTOINCREMENT,
    uid     BLOB
            NOT NULL
            UNIQUE,
    name        VARCHAR(128),
    ip          VARCHAR(16),
    port        INTEGER,
    relay_count INTEGER,
    active      BOOLEAN
                NOT NULL
);

CREATE TABLE relays
(
    id              INTEGER
                    PRIMARY KEY
                    AUTOINCREMENT,
    name            VARCHAR(128),
    number          INTEGER
                    NOT NULL,
    controller_id   INTEGER
                    NOT NULL
                    REFERENCES controllers (id)
                    ON DELETE CASCADE
);

CREATE TABLE schedules
(
    id      INTEGER
            PRIMARY KEY
            AUTOINCREMENT,
    uid     BLOB
            NOT NULL
            UNIQUE,
    name    VARCHAR(128),
    periods BLOB
);

CREATE TABLE tags
(
    id      INTEGER
            PRIMARY KEY
            AUTOINCREMENT,
    tag     VARCHAR(128)
            NOT NULL
            UNIQUE
);

CREATE TABLE junction_tag
(
    tag_id          INTEGER
                    NOT NULL
                    REFERENCES tags (id)
                    ON DELETE CASCADE,
    relay_id        INTEGER
                    REFERENCES relays (id)
                    ON DELETE CASCADE,
    schedule_id     INTEGER
                    REFERENCES schedules (id)
                    ON DELETE CASCADE
);

CREATE TABLE junction_relay_schedule
(
    weekday         SMALLINT
                    NOT NULL,
    relay_id        INTEGER
                    REFERENCES relays (id)
                    ON DELETE CASCADE,
    schedule_id     INTEGER
                    DEFAULT 1
                    REFERENCES schedules (id)
                    ON DELETE SET DEFAULT
);

INSERT INTO schedules (uid, name, periods) VALUES (x'6f666600000000000000000000000000', 'off', x'00');
INSERT INTO schedules (uid, name, periods) VALUES (x'6f6e0000000000000000000000000000',  'on', x'010000000000');