postgres.up.psql

/*
 Copyright 2022 The jackal Authors

 Licensed under the Apache License, Version 2.0 (the "License");
 you may not use this file except in compliance with the License.
 You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing, software
 distributed under the License is distributed on an "AS IS" BASIS,
 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 See the License for the specific language governing permissions and
 limitations under the License.
*/

-- Functions to manage updated_at timestamps

CREATE OR REPLACE FUNCTION enable_updated_at(_tbl regclass) RETURNS VOID AS $$
BEGIN
    EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s
                    FOR EACH ROW EXECUTE PROCEDURE set_updated_at()', _tbl);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
    IF (
        NEW IS DISTINCT FROM OLD AND
        NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at
    ) THEN
        NEW.updated_at := current_timestamp;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- users

CREATE TABLE IF NOT EXISTS users (
    username         VARCHAR(1023) PRIMARY KEY,
    h_sha_1          TEXT NOT NULL,
    h_sha_256        TEXT NOT NULL,
    h_sha_512        TEXT NOT NULL,
    h_sha3_512       TEXT NOT NULL,
    salt             TEXT NOT NULL,
    iteration_count  INT NOT NULL,
    pepper_id        VARCHAR(1023) NOT NULL,
    updated_at       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

SELECT enable_updated_at('users');

-- last

CREATE TABLE IF NOT EXISTS last (
    username   VARCHAR(1023) PRIMARY KEY,
    status     TEXT NOT NULl,
    seconds    BIGINT NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

SELECT enable_updated_at('last');

-- capabilities

CREATE TABLE IF NOT EXISTS capabilities (
    node       VARCHAR(1023) NOT NULL,
    ver        VARCHAR(1023) NOT NULL,
    features   TEXT ARRAY,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    PRIMARY KEY (node, ver)
);

SELECT enable_updated_at('capabilities');

-- offline_messages

CREATE TABLE IF NOT EXISTS offline_messages (
    id         SERIAL PRIMARY KEY,
    username   VARCHAR(1023) NOT NULL,
    message    BYTEA NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS i_offline_messages_username ON offline_messages(username);

-- blocklist_items

CREATE TABLE IF NOT EXISTS blocklist_items (
    username   VARCHAR(1023) NOT NULL,
    jid        TEXT NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    PRIMARY KEY(username, jid)
);

SELECT enable_updated_at('blocklist_items');

-- private_storage

CREATE TABLE IF NOT EXISTS private_storage (
    username        VARCHAR(1023) NOT NULL,
    namespace       VARCHAR(512) NOT NULL,
    data            BYTEA NOT NULL,
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    PRIMARY KEY (username, namespace)
);

SELECT enable_updated_at('private_storage');

-- roster_notifications

CREATE TABLE IF NOT EXISTS roster_notifications (
    contact     VARCHAR(1023) NOT NULL,
    jid         TEXT NOT NULL,
    presence    BYTEA NOT NULL,
    updated_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    PRIMARY KEY (contact, jid)
);

SELECT enable_updated_at('roster_notifications');

-- roster_items

CREATE TABLE IF NOT EXISTS roster_items (
    username        VARCHAR(1023) NOT NULL,
    jid             TEXT NOT NULL,
    name            TEXT NOT NULL,
    subscription    TEXT NOT NULL,
    groups          TEXT ARRAY,
    ask             BOOL NOT NULL,
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    PRIMARY KEY (username, jid)
);

SELECT enable_updated_at('roster_items');

-- roster_versions

CREATE TABLE IF NOT EXISTS roster_versions (
    username   VARCHAR(1023) NOT NULL,
    ver        INT NOT NULL DEFAULT 1,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

    PRIMARY KEY (username)
);

SELECT enable_updated_at('roster_versions');

-- vcards

CREATE TABLE IF NOT EXISTS vcards (
    username        VARCHAR(1023) PRIMARY KEY,
    vcard           BYTEA NOT NULL,
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

SELECT enable_updated_at('vcards');

-- archives

CREATE TABLE IF NOT EXISTS archives (
    serial     SERIAL PRIMARY KEY,
    archive_id VARCHAR(1023),
    id         VARCHAR(255) NOT NULL,
    "from"     TEXT NOT NULL,
    from_bare  TEXT NOT NULL,
    "to"       TEXT NOT NULL,
    to_bare    TEXT NOT NULL,
    message    BYTEA NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS i_archives_archive_id ON archives(archive_id);
CREATE INDEX IF NOT EXISTS i_archives_id ON archives(id);
CREATE INDEX IF NOT EXISTS i_archives_to ON archives("to");
CREATE INDEX IF NOT EXISTS i_archives_to_bare ON archives(to_bare);
CREATE INDEX IF NOT EXISTS i_archives_from ON archives("from");
CREATE INDEX IF NOT EXISTS i_archives_from_bare ON archives(from_bare);
CREATE INDEX IF NOT EXISTS i_archives_created_at ON archives(created_at);