Something went wrong. Try again.
An entry for the streamplace vod showcase
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261/** * Database connection and queries using Turso/LibSQL * * Works in Deno/edge environments via HTTP */
import { createClient, type Client } from "@libsql/client/web"
// Secrets from at-rundeclare const Deno: { env: { get(key: string): string | undefined }}
let client: Client | null = null
function getDb(): Client { if (!client) { const url = Deno.env.get("TURSO_URL") const authToken = Deno.env.get("TURSO_AUTH_TOKEN")
if (!url) { throw new Error("TURSO_URL secret not configured") }
client = createClient({ url, authToken, }) } return client}
/** * Initialize database schema (run once on setup) */export async function initSchema(): Promise<void> { const db = getDb()
await db.batch([ `CREATE TABLE IF NOT EXISTS creators ( did TEXT PRIMARY KEY, added_at TEXT DEFAULT (datetime('now')), last_synced_at TEXT )`, `CREATE TABLE IF NOT EXISTS videos ( uri TEXT PRIMARY KEY, creator_did TEXT NOT NULL, title TEXT NOT NULL, duration INTEGER NOT NULL, created_at TEXT NOT NULL, indexed_at TEXT DEFAULT (datetime('now')) )`, `CREATE INDEX IF NOT EXISTS idx_videos_creator ON videos(creator_did)`, `CREATE INDEX IF NOT EXISTS idx_videos_created_at ON videos(created_at DESC)`, ])}
// ============================================================================// Creator queries// ============================================================================
export interface Creator { did: string addedAt: Date lastSyncedAt: Date | null}
export async function getCreator(did: string): Promise<Creator | null> { const db = getDb() const result = await db.execute({ sql: "SELECT did, added_at, last_synced_at FROM creators WHERE did = ?", args: [did], })
if (result.rows.length === 0) return null const row = result.rows[0] return { did: row.did as string, addedAt: new Date(row.added_at as string), lastSyncedAt: row.last_synced_at ? new Date(row.last_synced_at as string) : null, }}
export async function listCreators(): Promise<Creator[]> { const db = getDb() const result = await db.execute( "SELECT did, added_at, last_synced_at FROM creators ORDER BY added_at DESC" )
return result.rows.map((row) => ({ did: row.did as string, addedAt: new Date(row.added_at as string), lastSyncedAt: row.last_synced_at ? new Date(row.last_synced_at as string) : null, }))}
export async function addCreator(did: string): Promise<Creator> { const db = getDb() await db.execute({ sql: `INSERT INTO creators (did) VALUES (?) ON CONFLICT (did) DO UPDATE SET did = excluded.did`, args: [did], })
const creator = await getCreator(did) if (!creator) throw new Error("Failed to add creator") return creator}
export async function updateCreatorSyncTime(did: string): Promise<void> { const db = getDb() await db.execute({ sql: "UPDATE creators SET last_synced_at = datetime('now') WHERE did = ?", args: [did], })}
export async function removeCreator(did: string): Promise<void> { const db = getDb() await db.batch([ { sql: "DELETE FROM videos WHERE creator_did = ?", args: [did] }, { sql: "DELETE FROM creators WHERE did = ?", args: [did] }, ])}
// ============================================================================// Video queries// ============================================================================
export interface Video { uri: string creatorDid: string title: string duration: number createdAt: Date indexedAt: Date}
export interface VideoInput { uri: string creatorDid: string title: string duration: number createdAt: Date}
export async function upsertVideo(video: VideoInput): Promise<void> { const db = getDb() await db.execute({ sql: `INSERT INTO videos (uri, creator_did, title, duration, created_at) VALUES (?, ?, ?, ?, ?) ON CONFLICT (uri) DO UPDATE SET title = excluded.title, duration = excluded.duration, indexed_at = datetime('now')`, args: [video.uri, video.creatorDid, video.title, video.duration, video.createdAt.toISOString()], })}
export async function upsertVideos(videos: VideoInput[]): Promise<void> { if (videos.length === 0) return const db = getDb()
// Batch insert for efficiency const statements = videos.map((v) => ({ sql: `INSERT INTO videos (uri, creator_did, title, duration, created_at) VALUES (?, ?, ?, ?, ?) ON CONFLICT (uri) DO UPDATE SET title = excluded.title, duration = excluded.duration, indexed_at = datetime('now')`, args: [v.uri, v.creatorDid, v.title, v.duration, v.createdAt.toISOString()], }))
// LibSQL batch has a limit, chunk if needed const BATCH_SIZE = 100 for (let i = 0; i < statements.length; i += BATCH_SIZE) { await db.batch(statements.slice(i, i + BATCH_SIZE)) }}
export async function deleteVideosForCreator(creatorDid: string): Promise<void> { const db = getDb() await db.execute({ sql: "DELETE FROM videos WHERE creator_did = ?", args: [creatorDid], })}
export async function listVideos(options?: { limit?: number cursor?: string creatorDid?: string}): Promise<{ videos: Video[]; cursor?: string }> { const db = getDb() const limit = options?.limit ?? 50
let sql: string let args: (string | number)[]
if (options?.creatorDid && options?.cursor) { sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at FROM videos WHERE creator_did = ? AND created_at < ? ORDER BY created_at DESC LIMIT ?` args = [options.creatorDid, options.cursor, limit + 1] } else if (options?.creatorDid) { sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at FROM videos WHERE creator_did = ? ORDER BY created_at DESC LIMIT ?` args = [options.creatorDid, limit + 1] } else if (options?.cursor) { sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at FROM videos WHERE created_at < ? ORDER BY created_at DESC LIMIT ?` args = [options.cursor, limit + 1] } else { sql = `SELECT uri, creator_did, title, duration, created_at, indexed_at FROM videos ORDER BY created_at DESC LIMIT ?` args = [limit + 1] }
const result = await db.execute({ sql, args })
const hasMore = result.rows.length > limit const rows = result.rows.slice(0, limit)
const videos = rows.map((row) => ({ uri: row.uri as string, creatorDid: row.creator_did as string, title: row.title as string, duration: Number(row.duration), createdAt: new Date(row.created_at as string), indexedAt: new Date(row.indexed_at as string), }))
return { videos, cursor: hasMore ? videos[videos.length - 1]?.createdAt.toISOString() : undefined, }}
export async function getVideoCount(): Promise<number> { const db = getDb() const result = await db.execute("SELECT COUNT(*) as count FROM videos") return Number(result.rows[0].count)}
export async function getCreatorCount(): Promise<number> { const db = getDb() const result = await db.execute("SELECT COUNT(*) as count FROM creators") return Number(result.rows[0].count)}