cyberia/research/mimi/sql/migrations/000001_telegram.sql

sql
ฯ€ 0.0% 190 words ยท 1 min 
-- +goose Up
-- +goose StatementBegin
CREATE TABLE IF NOT EXISTS telegram_peer (
    id bigint PRIMARY KEY,
    chat_name text NOT NULL,
    description text,
    enabled boolean NOT NULL DEFAULT TRUE
);

COMMENT ON TABLE telegram_peer IS 'Contains information about telegram groups and channels';

COMMENT ON COLUMN telegram_peer.enabled IS 'Set to false if given peer should not be used';

CREATE TABLE IF NOT EXISTS telegram_topic (
    id int,
    peer_id bigint NOT NULL REFERENCES telegram_peer(id) ON DELETE CASCADE,
    title text NOT NULL,
    description text NOT NULL,
    PRIMARY KEY (id, peer_id)
);

COMMENT ON TABLE telegram_topic IS 'Megagroups in telegram i.e. forums contain different topics';

CREATE TABLE IF NOT EXISTS telegram_message (
    id int,
    peer_id bigint NOT NULL,
    topic_id int,
    message text NOT NULL,
    created_at timestamp WITH time zone DEFAULT NOW(),
    PRIMARY KEY (id, peer_id),
    FOREIGN KEY (topic_id, peer_id) REFERENCES telegram_topic(id, peer_id) ON DELETE CASCADE,
    FOREIGN KEY (peer_id) REFERENCES telegram_peer(id) ON DELETE CASCADE
);

COMMENT ON TABLE telegram_message IS 'Each telegram peer contains multiple messages';
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS telegram_message;

DROP TABLE IF EXISTS telegram_topic;

DROP TABLE IF EXISTS telegram_peer;
-- +goose StatementEnd

Graph