Something went wrong. Try again.
An ATProto Powered Reputation Network
Something went wrong. Try again.
17 kB · 341 lines
SQL
at master
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342-- name: Join :execINSERT INTO participants(did) VALUES (?) ON CONFLICT(did) DO NOTHING;
-- name: ReplaySequence :oneSELECT i.id, CAST(COALESCE(r.sequence, 0) AS INTEGER) AS sequence FROM identities iLEFT JOIN replay_accounts r ON r.author = i.id AND r.source = @sourceWHERE i.did = CAST(@author AS TEXT);
-- name: AdvanceReplayAccount :execINSERT INTO replay_accounts(source, author, sequence) VALUES (@source, @author, @sequence)ON CONFLICT(source, author) DO UPDATE SET sequence = excluded.sequence;
-- name: Participant :oneSELECT * FROM participants WHERE did = ?;
-- name: Participants :manySELECT * FROM participants ORDER BY synced_at, did LIMIT ?;
-- name: SetThreshold :execrowsUPDATE participants SET threshold = ? WHERE did = ?;
-- name: SetSynced :execUPDATE participants SET synced_at = ? WHERE did = ?;
-- name: SetBootstrap :execUPDATE participants SET bootstrap_us = ?, account_us = ?, active = 1, synced_at = ? WHERE did = ?;
-- name: SetAccount :execUPDATE participants SET active = ?, account_us = ?, synced_at = ? WHERE did = ?;
-- name: SetDeleted :execUPDATE participants SET active = 0, account_us = ?, bootstrap_us = ?, synced_at = ? WHERE did = ?;
-- name: ClearVouches :execDELETE FROM vouches WHERE author = ?;
-- name: InsertVouch :execINSERT INTO vouches(author, subject, tag, score, created_at, record_key, comment) VALUES (?, ?, ?, ?, ?, ?, ?);
-- name: AuthorRecords :manySELECT * FROM vouches WHERE author = ?;
-- name: ReplaceVouch :execINSERT INTO vouches(author, subject, tag, score, created_at, record_key, comment) VALUES (?, ?, ?, ?, ?, ?, ?)ON CONFLICT(author, subject, tag) DO UPDATE SET score = excluded.score, created_at = excluded.created_at, record_key = excluded.record_key, comment = excluded.commentWHERE score != excluded.score OR created_at != excluded.created_at OR record_key != excluded.record_key OR comment != excluded.comment;
-- name: Incoming :manySELECT vouches.* FROM vouches JOIN participants ON participants.did = vouches.authorWHERE subject = ?1 AND participants.active = 1AND NOT EXISTS (SELECT 1 FROM participants target WHERE target.did = vouches.subject AND target.active = 0)AND (tag = ?2 OR (CAST(?3 AS INTEGER) = 1 AND tag = ''))ORDER BY author, tag LIMIT ?4;
-- name: GraphRevision :oneSELECT revision FROM graph_revision WHERE singleton = 1;
-- name: BumpGraphRevision :execUPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;
-- name: DeferTrustInvalidation :execUPDATE cache_policy SET deferred = 1 WHERE singleton = 1;
-- name: ResumeTrustInvalidation :execUPDATE cache_policy SET deferred = 0 WHERE singleton = 1;
-- name: ClearTrust :execDELETE FROM trust_cache;
-- name: CachedTrust :oneSELECT status, score FROM trust_cache WHERE cache_key = ? AND expires_us > ?;
-- name: TrustCachePath :manySELECT did FROM trust_cache_paths WHERE cache_key = ? ORDER BY position;
-- name: TrustCacheScores :manySELECT did, assertion, source, list_uri, score, comment FROM trust_cache_scores WHERE cache_key = ? ORDER BY position;
-- name: DeleteTrust :execDELETE FROM trust_cache WHERE cache_key = ?;
-- name: SaveTrust :execINSERT INTO trust_cache(cache_key, status, score, expires_us) VALUES (?, ?, ?, ?);
-- name: SaveTrustPath :execINSERT INTO trust_cache_paths(cache_key, position, did) VALUES (?, ?, ?);
-- name: SaveTrustScore :execINSERT INTO trust_cache_scores(cache_key, position, did, assertion, source, list_uri, score, comment) VALUES (?, ?, ?, ?, ?, ?, ?, ?);
-- name: SaveDependency :execINSERT OR IGNORE INTO trust_dependencies(cache_key, author, source, record_key)VALUES (@cache_key, (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key);
-- name: PutIdentity :execINSERT INTO identities(did) VALUES (?) ON CONFLICT(did) DO NOTHING;
-- name: ReplayIndexState :oneSELECT source, deferred FROM replay_indexes WHERE singleton = 1;
-- name: DeferReplayIndexes :execUPDATE replay_indexes SET source = ?, deferred = 1 WHERE singleton = 1;
-- name: FinishReplayIndexes :execUPDATE replay_indexes SET deferred = 0 WHERE singleton = 1;
-- name: SoftGraph :many-- The caller sorts complete results. Avoid a global DID sort before the edge limit.SELECT s.* FROM soft_edges sWHERE (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = s.author AND p.active = 1))AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = s.author AND v.subject = s.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))LIMIT @max_rows;
-- name: SoftIncoming :manySELECT s.* FROM soft_edges sWHERE s.subject = @subjectAND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = s.author AND p.active = 1))AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = s.author AND v.subject = s.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))ORDER BY s.author LIMIT @max_rows;
-- name: SoftAccount :oneSELECT i.did, a.active, a.account_us, a.bootstrap_us FROM soft_accounts a JOIN identities i ON i.id = a.did WHERE i.did = ?;
-- name: SoftReplayState :oneSELECT a.bootstrap_us, v.time_us FROM (SELECT CAST(@author AS INTEGER) AS id) iLEFT JOIN soft_accounts a ON a.did = i.idLEFT JOIN soft_versions v ON v.author = i.id AND v.source = @source AND v.record_key = @record_key;
-- name: JoinReplaySoft :execINSERT INTO soft_accounts(did) VALUES (?) ON CONFLICT(did) DO NOTHING;
-- name: SetReplaySoftVersion :execINSERT INTO soft_versions(author, source, record_key, time_us) VALUES (@author, @source, @record_key, @time_us)ON CONFLICT(author, source, record_key) DO UPDATE SET time_us = excluded.time_us;
-- name: ReplaceReplaySoft :execINSERT INTO soft_vouches(author, subject, source, record_key, score, created_at)VALUES (@author, (SELECT id FROM identities WHERE did = CAST(@subject AS TEXT)), @source, @record_key, @score, @created_at)ON CONFLICT(author, source, record_key) DO UPDATE SET subject = excluded.subject, score = excluded.score, created_at = excluded.created_atWHERE subject != excluded.subject OR score != excluded.score OR created_at != excluded.created_at;
-- name: DeleteReplaySoft :execDELETE FROM soft_vouches WHERE author = @author AND source = @source AND record_key = @record_key;
-- name: JoinSoft :execINSERT INTO soft_accounts(did) SELECT id FROM identities WHERE did = CAST(@did AS TEXT) ON CONFLICT(did) DO NOTHING;
-- name: SoftAccountState :execUPDATE soft_accounts SET active = @active, account_us = @account_us WHERE did = (SELECT id FROM identities WHERE did = CAST(@did AS TEXT));
-- name: SoftBootstrap :execUPDATE soft_accounts SET active = 1, account_us = @account_us, bootstrap_us = @bootstrap_us WHERE did = (SELECT id FROM identities WHERE did = CAST(@did AS TEXT));
-- name: SoftDeleted :execUPDATE soft_accounts SET active = 0, account_us = @account_us, bootstrap_us = @bootstrap_us WHERE did = (SELECT id FROM identities WHERE did = CAST(@did AS TEXT));
-- name: SoftRecords :manySELECT * FROM soft_vouches WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));
-- name: SoftVersion :oneSELECT time_us FROM soft_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;
-- name: SetSoftVersion :execINSERT INTO soft_versions(author, source, record_key, time_us) VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key, @time_us)ON CONFLICT(author, source, record_key) DO UPDATE SET time_us = excluded.time_us;
-- name: ClearSoftVersions :execDELETE FROM soft_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));
-- name: DeleteSoft :execDELETE FROM soft_vouches WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;
-- name: ClearSoft :execDELETE FROM soft_vouches WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));
-- name: ReplaceSoft :execINSERT INTO soft_vouches(author, subject, source, record_key, score, created_at)VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), (SELECT id FROM identities WHERE did = CAST(@subject AS TEXT)), @source, @record_key, @score, @created_at)ON CONFLICT(author, source, record_key) DO UPDATE SET subject = excluded.subject, score = excluded.score, created_at = excluded.created_atWHERE subject != excluded.subject OR score != excluded.score OR created_at != excluded.created_at;
-- name: ListGraph :manySELECT e.* FROM list_edges eWHERE (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = e.author AND p.active = 1))AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = e.author AND v.subject = e.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))LIMIT @max_rows;
-- name: ListIncoming :manySELECT e.* FROM list_edges eWHERE e.subject = @subjectAND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = e.author AND p.active = 1))AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = e.author AND v.subject = e.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))ORDER BY e.author, e.list_uri LIMIT @max_rows;
-- name: ListRecord :oneSELECT a.did AS author, r.source, r.record_key, r.list_uri, COALESCE(s.did, '') AS subject, r.purpose, r.created_atFROM list_records r JOIN identities a ON a.id = r.author LEFT JOIN identities s ON s.id = r.subjectWHERE a.did = CAST(@author AS TEXT) AND r.source = @source AND r.record_key = @record_key;
-- name: ListAuthorRecords :manySELECT a.did AS author, r.source, r.record_key, r.list_uri, COALESCE(s.did, '') AS subject, r.purpose, r.created_atFROM list_records r JOIN identities a ON a.id = r.author LEFT JOIN identities s ON s.id = r.subjectWHERE a.did = CAST(@author AS TEXT) AND r.source = @source;
-- name: ListURIRecords :manySELECT a.did AS author, r.source, r.record_key, r.list_uri, COALESCE(s.did, '') AS subject, r.purpose, r.created_atFROM list_records r JOIN identities a ON a.id = r.author LEFT JOIN identities s ON s.id = r.subjectWHERE r.list_uri = ? AND r.source != 'app.bsky.graph.listblock';
-- name: PutListRecord :execINSERT INTO list_records(author, source, record_key, list_uri, subject, purpose, created_at)VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key, @list_uri, (SELECT id FROM identities WHERE did = NULLIF(CAST(@subject AS TEXT), '')), @purpose, @created_at)ON CONFLICT(author, source, record_key) DO UPDATE SET list_uri = excluded.list_uri, subject = excluded.subject, purpose = excluded.purpose, created_at = excluded.created_atWHERE list_uri != excluded.list_uri OR subject IS NOT excluded.subject OR purpose != excluded.purpose OR created_at != excluded.created_at;
-- name: DeleteListRecord :execDELETE FROM list_records WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;
-- name: ClearListAuthor :execDELETE FROM list_records WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));
-- name: ReplayClearLists :execDELETE FROM list_records WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT))AND NOT EXISTS (SELECT 1 FROM list_snapshots s WHERE s.list_uri = list_records.list_uri AND s.bootstrap_us >= @time_us)AND (source != 'app.bsky.graph.listblock' OR NOT EXISTS (SELECT 1 FROM list_bootstraps b WHERE b.author = list_records.author AND b.bootstrap_us >= @time_us));
-- name: ClearListVersions :execDELETE FROM list_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));
-- name: ListVersion :oneSELECT time_us FROM list_versions WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT)) AND source = @source AND record_key = @record_key;
-- name: PutListVersion :execINSERT INTO list_versions(author, source, record_key, time_us) VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @source, @record_key, @time_us)ON CONFLICT(author, source, record_key) DO UPDATE SET time_us = excluded.time_us;
-- name: ListWatermark :oneSELECT bootstrap_us FROM list_snapshots WHERE list_uri = ?;
-- name: PutListWatermark :execINSERT INTO list_snapshots(list_uri, owner, bootstrap_us) VALUES (@list_uri, (SELECT id FROM identities WHERE did = CAST(@owner AS TEXT)), @bootstrap_us)ON CONFLICT(list_uri) DO UPDATE SET bootstrap_us = excluded.bootstrap_us;
-- name: ListBootstrap :oneSELECT bootstrap_us FROM list_bootstraps WHERE author = (SELECT id FROM identities WHERE did = CAST(@author AS TEXT));
-- name: PutListBootstrap :execINSERT INTO list_bootstraps(author, bootstrap_us) VALUES ((SELECT id FROM identities WHERE did = CAST(@author AS TEXT)), @bootstrap_us)ON CONFLICT(author) DO UPDATE SET bootstrap_us = excluded.bootstrap_us;
-- name: WantsListOwner :oneSELECT EXISTS(SELECT 1 FROM list_snapshots WHERE owner = (SELECT id FROM identities WHERE did = CAST(@owner AS TEXT))) AS wanted;
-- name: WantsListURI :oneSELECT EXISTS(SELECT 1 FROM list_snapshots WHERE list_uri = ?) AS wanted;
-- name: Graph :manySELECT vouches.* FROM vouches JOIN participants ON participants.did = vouches.authorWHERE participants.active = 1AND NOT EXISTS (SELECT 1 FROM participants target WHERE target.did = vouches.subject AND target.active = 0)AND (tag = sqlc.arg(tag) OR (CAST(sqlc.arg(fallback) AS INTEGER) = 1 AND tag = ''))ORDER BY author, subject, tag LIMIT sqlc.arg(max_rows);
-- Outgoing reads preserve per-pair selection and use author indexes rather than a global graph scan.-- name: Outgoing :manySELECT v.* FROM vouches v JOIN participants p ON p.did = v.authorWHERE v.author = @author AND p.active = 1AND NOT EXISTS (SELECT 1 FROM participants target WHERE target.did = v.subject AND target.active = 0)AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = ''))ORDER BY v.subject, v.tag LIMIT @max_rows;
-- name: SoftOutgoing :manySELECT s.* FROM soft_edges sWHERE s.author = @authorAND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = s.author AND p.active = 1))AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = s.author AND v.subject = s.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))LIMIT @max_rows;
-- name: ListOutgoing :manySELECT e.* FROM list_edges eWHERE e.author = @authorAND (CAST(@all_authors AS INTEGER) = 1 OR EXISTS (SELECT 1 FROM participants p WHERE p.did = e.author AND p.active = 1))AND NOT EXISTS (SELECT 1 FROM vouches v WHERE v.author = e.author AND v.subject = e.subject AND (v.tag = @tag OR (CAST(@fallback AS INTEGER) = 1 AND v.tag = '')))LIMIT @max_rows;
-- name: OwnVouches :manySELECT vouches.* FROM vouches JOIN participants ON participants.did = vouches.authorWHERE participants.active = 1 AND author = ? ORDER BY subject, tag LIMIT ? OFFSET ?;
-- name: Tags :manySELECT DISTINCT tag FROM vouches JOIN participants ON participants.did = vouches.authorWHERE participants.active = 1 AND tag != '' AND substr(tag, 1, length(CAST(sqlc.arg(prefix) AS TEXT))) = CAST(sqlc.arg(prefix) AS TEXT)ORDER BY tag LIMIT sqlc.arg(max_rows);
-- name: DeleteRecord :execDELETE FROM vouches WHERE author = ? AND record_key = ?;
-- name: RecordVersion :oneSELECT time_us FROM record_versions WHERE author = ? AND record_key = ?;
-- name: SetRecordVersion :execINSERT INTO record_versions(author, record_key, time_us) VALUES (?, ?, ?)ON CONFLICT(author, record_key) DO UPDATE SET time_us = excluded.time_us;
-- name: ClearVersions :execDELETE FROM record_versions WHERE author = ?;
-- name: AdvanceCursor :execINSERT INTO stream_cursors(source, time_us) VALUES (?, ?)ON CONFLICT(source) DO UPDATE SET time_us = max(stream_cursors.time_us, excluded.time_us);
-- name: InitCursor :execINSERT INTO stream_cursors(source, time_us) VALUES (?, ?) ON CONFLICT(source) DO NOTHING;
-- name: Cursor :oneSELECT time_us FROM stream_cursors WHERE source = ?;
-- name: LegacyKeys :manySELECT author, subject, tag FROM vouches WHERE record_key = '';
-- name: SetLegacyKey :execUPDATE vouches SET record_key = ? WHERE author = ? AND subject = ? AND tag = ?;
-- name: PutSecret :execINSERT INTO secrets(kind, id, value, expires) VALUES (?, ?, ?, ?)ON CONFLICT(kind, id) DO UPDATE SET value = excluded.value, expires = excluded.expires;
-- name: AddSecret :execINSERT INTO secrets(kind, id, value, expires) VALUES (?, ?, ?, ?);
-- name: GetSecret :oneSELECT value FROM secrets WHERE kind = ? AND id = ? AND expires > ?;
-- name: RemoveSecret :execDELETE FROM secrets WHERE kind = ? AND id = ?;
-- name: ConsumeSecret :oneDELETE FROM secrets WHERE kind = ? AND id = ? AND expires > ? RETURNING value;
-- name: PruneSecrets :execDELETE FROM secrets WHERE expires <= ?;