浪子
105 lines
2.8 KiB
SQL
105 lines
2.8 KiB
SQL
-- Account fields, status enrichment, and federation state for v0.2 -> v0.3.
|
|
|
|
ALTER TABLE statuses ADD COLUMN summary TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE statuses ADD COLUMN sensitive INTEGER NOT NULL DEFAULT 0;
|
|
ALTER TABLE statuses ADD COLUMN language TEXT NOT NULL DEFAULT 'en';
|
|
|
|
CREATE TABLE IF NOT EXISTS deleted_statuses (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
object_id TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
deleted_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
actor TEXT NOT NULL,
|
|
status_id TEXT,
|
|
read INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_time ON notifications(user_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS favourites (
|
|
id TEXT PRIMARY KEY,
|
|
status_id TEXT NOT NULL,
|
|
actor TEXT NOT NULL,
|
|
activity_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE(status_id, actor)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_favourites_status ON favourites(status_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS reblogs (
|
|
id TEXT PRIMARY KEY,
|
|
status_id TEXT NOT NULL,
|
|
actor TEXT NOT NULL,
|
|
activity_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE(status_id, actor)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reblogs_status ON reblogs(status_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS mentions (
|
|
status_id TEXT NOT NULL,
|
|
actor TEXT NOT NULL,
|
|
acct TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
PRIMARY KEY(status_id, actor)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_mentions_status ON mentions(status_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS hashtags (
|
|
status_id TEXT NOT NULL,
|
|
tag TEXT NOT NULL,
|
|
PRIMARY KEY(status_id, tag)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_hashtags_tag ON hashtags(tag);
|
|
|
|
CREATE TABLE IF NOT EXISTS actor_cache (
|
|
id TEXT PRIMARY KEY,
|
|
inbox TEXT NOT NULL,
|
|
shared_inbox TEXT,
|
|
preferred_username TEXT,
|
|
name TEXT,
|
|
summary TEXT,
|
|
icon_url TEXT,
|
|
public_key_id TEXT,
|
|
public_key_pem TEXT,
|
|
fetched_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_actor_cache_key_id ON actor_cache(public_key_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS outgoing_follows (
|
|
id TEXT PRIMARY KEY,
|
|
local_user_id TEXT NOT NULL,
|
|
target_actor TEXT NOT NULL,
|
|
target_inbox TEXT NOT NULL,
|
|
activity_id TEXT NOT NULL,
|
|
accepted INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE(local_user_id, target_actor)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_outgoing_follows_user ON outgoing_follows(local_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_profile_fields (
|
|
user_id TEXT NOT NULL,
|
|
position INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
PRIMARY KEY(user_id, position)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_oauth_codes_expires ON oauth_codes(expires_at);
|
|
CREATE INDEX IF NOT EXISTS idx_statuses_user_time ON statuses(user_id, created_at DESC);
|