Something went wrong. Try again.
An AT Protocol Personal Data Server written in JavaScript pdsjs.dev
pds atproto
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155// Blob listing and facet SQL shared by the SQLite-backed storage adapters// (better-sqlite3 and Durable Object SQLite speak the same dialect). The// schema is each adapter's own; these builders only read `blobs` and// `record_blobs`.
/** @typedef {import('./ports.js').BlobListOptions} BlobListOptions */
// The four named media kinds; anything else buckets as `other`.const NAMED_KINDS = ['image', 'video', 'audio', 'text'];
const KIND_CASE = `CASE WHEN mime_type LIKE 'image/%' THEN 'image' WHEN mime_type LIKE 'video/%' THEN 'video' WHEN mime_type LIKE 'audio/%' THEN 'audio' WHEN mime_type LIKE 'text/%' THEN 'text' ELSE 'other' END`;
/** * The collection-NSID authority (`app.bsky.feed.post` -> `app.bsky`) of each * blob's referencing records, one row per distinct blob/authority pair, so a * blob two apps reference counts once in each and repeat references within an * app count once. Derived by string position: a record URI is always * `at://<did>/<collection>/<rkey>`, and an NSID's authority is its first two * dot-segments. */const BLOB_AUTHORITIES = `SELECT DISTINCT blob_cid, CASE WHEN instr(coll, '.') = 0 THEN coll WHEN instr(substr(coll, instr(coll, '.') + 1), '.') = 0 THEN coll ELSE substr(coll, 1, instr(coll, '.') + instr(substr(coll, instr(coll, '.') + 1), '.') - 1) END AS authority FROM ( SELECT blob_cid, substr(c1, 1, instr(c1, '/') - 1) AS coll FROM ( SELECT blob_cid, substr(substr(record_uri, 6), instr(substr(record_uri, 6), '/') + 1) AS c1 FROM record_blobs ) )`;
export const BLOB_FACETS_TOTAL_SQL = 'SELECT COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes FROM blobs';
export const BLOB_FACETS_UNUSED_SQL = `SELECT COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes FROM blobs b WHERE NOT EXISTS (SELECT 1 FROM record_blobs rb WHERE rb.blob_cid = b.cid)`;
export const BLOB_FACETS_KINDS_SQL = `SELECT ${KIND_CASE} AS kind, COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes FROM blobs GROUP BY kind ORDER BY bytes DESC`;
export const BLOB_FACETS_APPS_SQL = `SELECT authority, COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes FROM (${BLOB_AUTHORITIES}) x JOIN blobs b ON b.cid = x.blob_cid GROUP BY authority ORDER BY bytes DESC`;
// A record minted by a normal write carries a TID rkey: 13 base32-sortable// chars encoding (creation micros << 10 | clock id). The rkey is the URI's// last path segment, so a TID rkey is exactly the URI's last 13 chars.const TID_CHARS = '234567abcdefghijklmnopqrstuvwxyz';// Shape check as instr() per char, not GLOB: Durable Object SQLite rejects a// 13-class GLOB pattern as "too complex", though better-sqlite3 accepts it.const TID_SHAPE = [ `substr(rb.record_uri, -14, 1) = '/'`, ...Array.from( { length: 13 }, (_, i) => `instr('${TID_CHARS}', substr(rb.record_uri, ${i - 13}, 1)) > 0`, ),].join(' AND ');const TID_MS = `(${Array.from( { length: 13 }, (_, i) => `(instr('${TID_CHARS}', substr(rb.record_uri, ${i - 13}, 1)) - 1) * ${(32n ** BigInt(12 - i)).toString()}`,).join(' + ')}) / 1024000`;
/** * When a blob's content entered the account, as epoch ms: the earliest * referencing record's own stored timestamp, else that record's TID time, * else when the blob was stored here. The distinction matters for migrated * repos, where every blob's stored-here time is the migration date but the * records still carry the original timestamps. */const LINK_TIME = `COALESCE(rb.record_time, CASE WHEN ${TID_SHAPE} THEN ${TID_MS} END)`;const BLOB_TIME = `COALESCE(MIN(${LINK_TIME}), b.created_at)`;const BLOB_TIME_KNOWN = `(MIN(${LINK_TIME}) IS NOT NULL)`;
/** * The filtered, ordered blob listing with each blob's referencing record URIs * concatenated (comma-separated; neither DIDs, NSIDs, nor rkeys may contain a * comma). Keyset pagination: the cursor is `<sortValue>:<cid>`, matching the * chosen order, so a page boundary holds still while blobs come and go. * @param {string|null} cursor * @param {number} limit * @param {BlobListOptions} [options] * @returns {{sql: string, params: (string|number)[]}} */export function buildBlobListQuery(cursor, limit, options = {}) { const { kind, appAuthority, unused, sort } = options; const conds = []; /** @type {(string|number)[]} */ const params = [];
if (kind && NAMED_KINDS.includes(kind)) { conds.push('b.mime_type LIKE ?'); params.push(`${kind}/%`); } else if (kind === 'other') { conds.push( `(b.mime_type IS NULL OR (${NAMED_KINDS.map(() => 'b.mime_type NOT LIKE ?').join(' AND ')}))`, ); params.push(...NAMED_KINDS.map((named) => `${named}/%`)); }
if (appAuthority) { conds.push( 'EXISTS (SELECT 1 FROM record_blobs rb2 WHERE rb2.blob_cid = b.cid AND rb2.record_uri LIKE ?)', ); params.push(`at://%/${appAuthority}.%`); }
if (unused) { conds.push( 'NOT EXISTS (SELECT 1 FROM record_blobs rb2 WHERE rb2.blob_cid = b.cid)', ); }
// The sort key is an aggregate for `newest`, so the cursor bound lives in // HAVING (evaluated after grouping) rather than WHERE. const having = []; if (cursor) { const split = cursor.indexOf(':'); const key = Number(cursor.slice(0, split)); const cid = cursor.slice(split + 1); having.push('(sort_key < ? OR (sort_key = ? AND b.cid < ?))'); params.push(key, key, cid); }
const keyExpr = sort === 'largest' ? 'b.size' : BLOB_TIME; const where = conds.length ? `WHERE ${conds.join(' AND ')}` : ''; params.push(limit); return { sql: `SELECT b.cid, b.mime_type, b.size, b.created_at, GROUP_CONCAT(rb.record_uri) AS uris, ${BLOB_TIME} AS time_ms, ${BLOB_TIME_KNOWN} AS time_known, ${keyExpr} AS sort_key FROM blobs b LEFT JOIN record_blobs rb ON b.cid = rb.blob_cid ${where} GROUP BY b.cid ${having.length ? `HAVING ${having.join(' AND ')}` : ''} ORDER BY sort_key DESC, b.cid DESC LIMIT ?`, params, };}
/** * The cursor naming the last blob of a page, under the same options the page * was listed with. * @param {import('./ports.js').BlobDetails} blob * @param {BlobListOptions} [options] * @returns {string} */export function blobListCursor(blob, options = {}) { const key = options.sort === 'largest' ? blob.size : blob.time; return `${key}:${blob.cid}`;}