维基百科

User:Bluedeck/haystack/unblock-zh/db

< User:Bluedeck‎ | haystack/unblock-zh

这是init schema 

-- 用户表中省去和CoreEntropy、FoundationEntropy、OWFPreservationCounter相关的细节。
-- 这个schema不一定是最新的。2019-07-10

BEGIN;

CREATE TABLE events (

    id BIGSERIAL PRIMARY KEY,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events DEFAULT VALUES;  -- event 1: initialization event

CREATE TABLE symbols (

    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(2048) NOT NULL,  -- e.g. if a symbol is a user permission, name="zh.wikipedia read private page"
    type VARCHAR(256) NOT NULL,  -- e.g. if a symbol represents a user permission, then type="permission"
    description VARCHAR(2048) NOT NULL DEFAULT '',  -- comments, do not affect anything
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE        INDEX symbols_by_name ON symbols (name);
CREATE UNIQUE INDEX symbols_by_name_type ON symbols (name, type);
CREATE        INDEX symbols_by_type ON symbols (type);

CREATE TABLE permission_groups (

    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(256) UNIQUE NOT NULL,
    description VARCHAR(2049) NOT NULL,
    permissions VARCHAR(2048) NOT NULL,  -- a list of symbols (id) the permission group has. e.g. "14:415:140:481"
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    deprecated BOOLEAN NOT NULL DEFAULT false,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX permission_groups_by_event_id ON permission_groups (event_id);
CREATE INDEX permission_groups_by_deprecated ON permission_groups (deprecated);

CREATE TABLE users (

    id BIGSERIAL PRIMARY KEY,
    display_name VARCHAR(2048),
    username_normalized VARCHAR(2048) UNIQUE,
    email_primary VARCHAR(2048) UNIQUE,
    settings BYTEA,  -- A protobuf message containing user settings. These settings are meant to affect server behaviour. Must be readable by server.
    lang_code VARCHAR(256),
    blocked BOOLEAN NOT NULL DEFAULT false,
    blocked_till TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    blocked_indef BOOLEAN NOT NULL DEFAULT false,
    locked BOOLEAN NOT NULL DEFAULT false,  -- A locked user cannot login or validate credentials.
    frozen BOOLEAN NOT NULL DEFAULT false,  -- A frozen user cannot be interacted with, e.g. others can't send messages to him.
    bot BOOLEAN NOT NULL DEFAULT false,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX users_by_username_normalized ON users (username_normalized);
CREATE UNIQUE INDEX users_by_email_primary ON users (email_primary);
CREATE        INDEX users_by_event_id ON users (event_id);

INSERT INTO users (display_name, username_normalized)
VALUES ('*** Mr. Test user ***', 'mr. test');  -- other fields need populating

CREATE TABLE sessions (

    id BIGSERIAL PRIMARY KEY,
    session_metadata VARCHAR(8192),  -- e.g. "<meta><session /></meta>", only client needs to understand it
    secret VARCHAR(256) NOT NULL,
    invalidated BOOLEAN NOT NULL DEFAULT false,
    expiry TIMESTAMP WITH TIME ZONE NOT NULL,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX sessions_by_user_id ON sessions (user_id);
CREATE INDEX sessions_by_secret ON sessions (secret);
CREATE INDEX sessions_by_event_id ON sessions (event_id);

CREATE TABLE feeds (

    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    name VARCHAR(2048) NOT NULL,
    namespace BIGINT NOT NULL REFERENCES symbols (id) ON DELETE RESTRICT,
    feed_config VARCHAR(8192),  -- e.g. "<feed-config><display-title v="title" /></feed-config>" meant for client only
    creator_read BOOLEAN NOT NULL DEFAULT true,
    creator_reply BOOLEAN NOT NULL DEFAULT true,
    creator_reply_protected BOOLEAN NOT NULL DEFAULT false,
    creator_edit BOOLEAN NOT NULL DEFAULT false,  -- Editing a feed = delete other people's reply
    creator_delete_feed BOOLEAN NOT NULL DEFAULT false,
    read_permission BIGINT REFERENCES symbols (id) ON DELETE RESTRICT,
    deleted BOOLEAN NOT NULL DEFAULT false,
    protected BOOLEAN NOT NULL DEFAULT false,
    protected_till TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    protected_indef BOOLEAN NOT NULL DEFAULT false,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE        INDEX feeds_by_name ON feeds (name);
CREATE        INDEX feeds_by_namespace ON feeds (namespace);
CREATE UNIQUE INDEX feeds_by_name_namespace ON feeds (name, namespace);
CREATE        INDEX feeds_by_user_id ON feeds (user_id);
CREATE        INDEX feeds_by_event_id ON feeds (event_id);
CREATE        INDEX feeds_by_t_create ON feeds (t_create);
CREATE        INDEX feeds_by_t_update ON feeds (t_update);

CREATE TABLE pages (

    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    name VARCHAR(2048) NOT NULL,
    namespace BIGINT NOT NULL REFERENCES symbols (id) ON DELETE RESTRICT,
    feed_id BIGINT REFERENCES feeds (id) ON DELETE CASCADE,
    current_revision_id BIGINT,
    creator_read BOOLEAN NOT NULL DEFAULT true,
    creator_edit BOOLEAN NOT NULL DEFAULT true,  -- the edit permission also affects tagging.
    creator_edit_protected BOOLEAN NOT NULL DEFAULT false,
    creator_delete BOOLEAN NOT NULL DEFAULT false,
    read_permission BIGINT REFERENCES symbols (id) ON DELETE RESTRICT,  -- If a page belongs to a feed, it's read permission should be overwritten by that of the feed.
    deleted BOOLEAN NOT NULL DEFAULT false,  -- If a page belongs to a feed, and feed is deleted, the page becomes invisible without being marked deleted explicitly.
    -- pages.deleted if set, marks that the page is deleted from the feed.
    protected BOOLEAN NOT NULL DEFAULT false,
    protected_till TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    protected_indef BOOLEAN NOT NULL DEFAULT false,
    content_type VARCHAR(256) NOT NULL DEFAULT 'text/plain',
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE        INDEX pages_by_name ON pages (name);
CREATE        INDEX pages_by_namespace ON pages (namespace);
CREATE UNIQUE INDEX pages_by_name_namespace ON pages(name, namespace);
CREATE        INDEX pages_by_feed_id ON pages (feed_id);
CREATE        INDEX pages_by_user_id ON pages (user_id);
CREATE        INDEX pages_by_event_id ON pages (event_id);
CREATE        INDEX pages_by_t_create ON pages (t_create);
CREATE        INDEX pages_by_t_update ON pages (t_update);

CREATE TABLE tags (

    id BIGSERIAL PRIMARY KEY,
    parent_id BIGINT REFERENCES tags (id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    name VARCHAR(2048) NOT NULL,
    namespace BIGINT NOT NULL REFERENCES symbols (id) ON DELETE RESTRICT,
    desc_page_id BIGINT REFERENCES pages (id) ON DELETE RESTRICT,
    color VARCHAR(256),
    hidden BOOLEAN NOT NULL DEFAULT false,
    deleted BOOLEAN NOT NULL DEFAULT false,
    settings JSONB,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE        INDEX tags_by_name ON tags (name);
CREATE        INDEX tags_by_namespace ON tags (namespace);
CREATE UNIQUE INDEX tags_by_name_namespace ON tags (name, namespace);
CREATE        INDEX tags_by_color ON tags (color);
CREATE        INDEX tags_by_event_id ON tags (event_id);

CREATE TABLE revisions (

    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    page_id BIGINT NOT NULL REFERENCES pages (id) ON DELETE RESTRICT,
    elevated_visibility BIGINT REFERENCES symbols (id) ON DELETE RESTRICT,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX revisions_by_user_id ON revisions (user_id);
CREATE INDEX revisions_by_page_id ON revisions (page_id);
CREATE INDEX revisions_by_user_id_t_create ON revisions (user_id, t_create);
CREATE INDEX revisions_by_page_id_t_create ON revisions (page_id, t_create);
CREATE INDEX revisions_by_event_id ON revisions (event_id);

CREATE TABLE notifications (

    id BIGSERIAL PRIMARY KEY,
    summary VARCHAR(2048),
    revision_id BIGINT NOT NULL REFERENCES revisions (id) ON DELETE RESTRICT,
    sender_deleted BOOLEAN NOT NULL DEFAULT false,
    receiver_deleted BOOLEAN NOT NULL DEFAULT false,
    viewed BOOLEAN NOT NULL DEFAULT false,
    sender_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    receiver_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX notifications_by_receiver_id ON notifications (receiver_id);
CREATE INDEX notifications_by_receiver_id_receiver_deleted ON notifications (receiver_id, receiver_deleted);
CREATE INDEX notifications_by_receiver_id_receiver_deleted_t_create ON notifications (receiver_id, receiver_deleted, t_create);

CREATE INDEX notifications_by_sender_id ON notifications (sender_id);
CREATE INDEX notifications_by_sender_id_sender_deleted ON notifications (sender_id, sender_deleted);
CREATE INDEX notifications_by_sender_id_sender_deleted_t_create ON notifications (sender_id, sender_deleted, t_create);

CREATE INDEX notifications_by_receiver_id_sender_id ON notifications (receiver_id, sender_id);
CREATE INDEX notifications_by_receiver_id_sender_id_receiver_deleted ON notifications (receiver_id, sender_id, receiver_deleted);
CREATE INDEX notifications_by_receiver_id_sender_id_receiver_deleted_t_create ON notifications (receiver_id, sender_id, receiver_deleted, t_create);
CREATE INDEX notifications_by_receiver_id_sender_id_sender_deleted ON notifications (receiver_id, sender_id, sender_deleted);
CREATE INDEX notifications_by_receiver_id_sender_id_sender_deleted_t_create ON notifications (receiver_id, sender_id, sender_deleted, t_create);

CREATE INDEX notifications_by_event_id ON notifications (event_id);
CREATE INDEX notifications_by_t_create ON notifications (t_create);

CREATE TABLE logs (

    id BIGSERIAL PRIMARY KEY,
    config VARCHAR(8192),  -- XML config akin to page config. Only client needs to understand it.
    type BIGINT NOT NULL REFERENCES symbols (id) ON DELETE RESTRICT,
    summary VARCHAR(2048),
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    visibility BIGINT REFERENCES symbols (id) ON DELETE RESTRICT,
    deleted BOOLEAN NOT NULL DEFAULT false,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    t_create TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t_update TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX logs_by_user_id ON logs (user_id);
CREATE INDEX logs_by_type ON logs (type);
CREATE INDEX logs_by_user_id_type ON logs (user_id, type);
CREATE INDEX logs_by_event_id ON logs (event_id);

CREATE TABLE users_permission_groups_pivot (

    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    group_id BIGINT NOT NULL REFERENCES permission_groups (id) ON DELETE RESTRICT,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    PRIMARY KEY (user_id, group_id)
);

CREATE INDEX users_permission_groups_pivot_by_user_id ON users_permission_groups_pivot (user_id);
CREATE INDEX users_permission_groups_pivot_by_group_id ON users_permission_groups_pivot (group_id);
CREATE INDEX users_permission_groups_pivot_by_event_id ON users_permission_groups_pivot (event_id);

CREATE TABLE pages_tags_pivot (

    tag_id BIGINT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    page_id BIGINT NOT NULL REFERENCES pages (id) ON DELETE CASCADE,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    PRIMARY KEY (tag_id, page_id)
);

CREATE INDEX pages_tags_pivot_by_tag_id ON pages_tags_pivot (tag_id);
CREATE INDEX pages_tags_pivot_by_page_id ON pages_tags_pivot (page_id);
CREATE INDEX pages_tags_pivot_by_event_id ON pages_tags_pivot (page_id);

CREATE TABLE feeds_tags_pivot (

    tag_id BIGINT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    feed_id BIGINT NOT NULL REFERENCES feeds (id) ON DELETE CASCADE,
    event_id BIGINT REFERENCES events (id) ON DELETE RESTRICT,
    PRIMARY KEY (tag_id, feed_id)
);

CREATE INDEX feeds_tags_pivot_by_tag_id ON feeds_tags_pivot (tag_id);
CREATE INDEX feeds_tags_pivot_by_feed_id ON feeds_tags_pivot (feed_id);
CREATE INDEX feeds_tags_pivot_by_event_id ON feeds_tags_pivot (event_id);

CREATE TABLE users_feeds_subscription_pivot (

    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    feed_id BIGINT NOT NULL REFERENCES feeds (id) ON DELETE CASCADE,
    last_access TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    event_id BIGINT NOT NULL REFERENCES events (id) ON DELETE RESTRICT,
    PRIMARY KEY (user_id, feed_id)
);

CREATE INDEX users_feeds_subscription_pivot_by_user_id ON users_feeds_subscription_pivot (user_id);
CREATE INDEX users_feeds_subscription_pivot_by_last_access ON users_feeds_subscription_pivot (last_access);

CREATE TABLE users_pages_watch_pivot (

    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    page_id BIGINT NOT NULL REFERENCES feeds (id) ON DELETE CASCADE,
    last_access TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    event_id BIGINT NOT NULL REFERENCES events (id) ON DELETE RESTRICT,
    PRIMARY KEY (user_id, page_id)
);

CREATE INDEX users_pages_watch_pivot_by_id ON users_pages_watch_pivot (user_id);
CREATE INDEX users_pages_watch_pivot_by_last_access ON users_pages_watch_pivot (last_access);

COMMIT;
取自“https://zh.wikipedia.org/w/index.php?title=User:Bluedeck/haystack/unblock-zh/db&oldid=55165402”