浪子
74 lines
2.1 KiB
SQL
74 lines
2.1 KiB
SQL
-- Mastodon API compatibility for polls, lists, push subscriptions, and
|
|
-- scheduled statuses.
|
|
|
|
CREATE TABLE IF NOT EXISTS polls (
|
|
id TEXT PRIMARY KEY,
|
|
status_id TEXT NOT NULL UNIQUE,
|
|
user_id TEXT NOT NULL,
|
|
expires_at TEXT,
|
|
multiple INTEGER NOT NULL DEFAULT 0,
|
|
hide_totals INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS poll_options (
|
|
poll_id TEXT NOT NULL,
|
|
position INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
PRIMARY KEY(poll_id, position)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS poll_votes (
|
|
poll_id TEXT NOT NULL,
|
|
position INTEGER NOT NULL,
|
|
voter_actor TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY(poll_id, position, voter_actor)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_polls_status ON polls(status_id);
|
|
CREATE INDEX IF NOT EXISTS idx_poll_votes_poll ON poll_votes(poll_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS lists (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
replies_policy TEXT NOT NULL DEFAULT 'list',
|
|
exclusive INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS list_accounts (
|
|
list_id TEXT NOT NULL,
|
|
account_actor TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY(list_id, account_actor)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_lists_user ON lists(user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_list_accounts_list ON list_accounts(list_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS push_subscriptions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL UNIQUE,
|
|
endpoint TEXT NOT NULL,
|
|
server_key TEXT NOT NULL,
|
|
auth TEXT NOT NULL,
|
|
alerts_json TEXT NOT NULL DEFAULT '{}',
|
|
policy TEXT NOT NULL DEFAULT 'all',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS scheduled_statuses (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
params_json TEXT NOT NULL,
|
|
media_ids_json TEXT NOT NULL DEFAULT '[]',
|
|
scheduled_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scheduled_statuses_user_time ON scheduled_statuses(user_id, scheduled_at ASC);
|
|
CREATE INDEX IF NOT EXISTS idx_scheduled_statuses_due ON scheduled_statuses(scheduled_at ASC);
|