Something went wrong. Try again.
An ATProto Powered Reputation Network
Something went wrong. Try again.
4.0 kB · 69 lines
SQL
at master
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970-- Authors, subjects, and owners use the same identity dictionary as soft records.CREATE TABLE IF NOT EXISTS list_records ( author INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE, source TEXT NOT NULL CHECK(source IN ('app.bsky.graph.list', 'app.bsky.graph.listitem', 'app.bsky.graph.listblock')), record_key TEXT NOT NULL, list_uri TEXT NOT NULL, subject INTEGER REFERENCES identities(id), purpose TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, PRIMARY KEY(author, source, record_key)) WITHOUT ROWID;CREATE INDEX IF NOT EXISTS list_records_uri ON list_records(list_uri, source, subject, author);CREATE INDEX IF NOT EXISTS list_records_subject ON list_records(subject, source, author);
CREATE TABLE IF NOT EXISTS list_versions ( author INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE, source TEXT NOT NULL, record_key TEXT NOT NULL, time_us INTEGER NOT NULL, PRIMARY KEY(author, source, record_key)) WITHOUT ROWID;CREATE TABLE IF NOT EXISTS list_snapshots ( list_uri TEXT PRIMARY KEY, owner INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE, bootstrap_us INTEGER NOT NULL);CREATE INDEX IF NOT EXISTS list_snapshots_owner ON list_snapshots(owner);CREATE TABLE IF NOT EXISTS list_bootstraps ( author INTEGER PRIMARY KEY REFERENCES soft_accounts(did) ON DELETE CASCADE, bootstrap_us INTEGER NOT NULL);
CREATE VIEW IF NOT EXISTS list_edges ASSELECT author.did AS author, subject.did AS subject, b.source, b.record_key, b.created_at, b.list_uri, owner_did.did AS list_author, l.record_key AS list_key, i.record_key AS item_keyFROM list_records bJOIN list_records l ON l.list_uri = b.list_uri AND l.source = 'app.bsky.graph.list' AND l.purpose = 'app.bsky.graph.defs#modlist'JOIN list_records i ON i.list_uri = l.list_uri AND i.author = l.author AND i.source = 'app.bsky.graph.listitem'JOIN soft_accounts subscriber ON subscriber.did = b.author AND subscriber.active = 1JOIN soft_accounts owner ON owner.did = l.author AND owner.active = 1JOIN identities author ON author.id = b.authorJOIN identities subject ON subject.id = i.subjectJOIN identities owner_did ON owner_did.id = l.authorWHERE b.source = 'app.bsky.graph.listblock'AND NOT EXISTS (SELECT 1 FROM participants p WHERE p.did = subject.did AND p.active = 0)AND NOT EXISTS (SELECT 1 FROM soft_accounts p WHERE p.did = i.subject AND p.active = 0)AND NOT EXISTS (SELECT 1 FROM list_records x WHERE x.author = b.author AND x.source = b.source AND x.list_uri = b.list_uri AND x.record_key < b.record_key)AND NOT EXISTS (SELECT 1 FROM list_records x WHERE x.author = i.author AND x.source = i.source AND x.list_uri = i.list_uri AND x.subject = i.subject AND x.record_key < i.record_key);
-- A selected list edge depends on its subscription, list definition, and membership.CREATE TRIGGER IF NOT EXISTS trust_list_delete BEFORE DELETE ON list_records WHEN (SELECT deferred FROM cache_policy WHERE singleton = 1) = 0 BEGIN DELETE FROM trust_cache WHERE cache_key IN ( SELECT cache_key FROM trust_dependencies WHERE author = OLD.author AND source = OLD.source AND record_key = OLD.record_key );END;CREATE TRIGGER IF NOT EXISTS trust_list_update BEFORE UPDATE ON list_records WHEN (SELECT deferred FROM cache_policy WHERE singleton = 1) = 0 BEGIN DELETE FROM trust_cache WHERE cache_key IN ( SELECT cache_key FROM trust_dependencies WHERE author = OLD.author AND source = OLD.source AND record_key = OLD.record_key );END;CREATE TRIGGER IF NOT EXISTS graph_list_insert AFTER INSERT ON list_records BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_list_delete AFTER DELETE ON list_records BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_list_update AFTER UPDATE ON list_records BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;