Something went wrong. Try again.
search and/or read your saved and liked bluesky posts
wails go svelte sqlite desktop bluesky
Something went wrong. Try again.
2.3 kB · 62 lines
SQL
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263-- Combined initial schema for bsky-browser-desktop-- Includes all migrations: auth, posts, FTS5, OAuth fields, and facets
-- Auth table with all OAuth fieldsCREATE TABLE IF NOT EXISTS auth ( did TEXT PRIMARY KEY, handle TEXT NOT NULL, access_jwt TEXT NOT NULL, refresh_jwt TEXT NOT NULL, pds_url TEXT NOT NULL, session_id TEXT, auth_server_url TEXT, auth_server_token_endpoint TEXT, auth_server_revocation_endpoint TEXT, dpop_auth_nonce TEXT, dpop_host_nonce TEXT, dpop_private_key TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP);
-- Posts table with facets supportCREATE TABLE IF NOT EXISTS posts ( uri TEXT PRIMARY KEY, cid TEXT NOT NULL, author_did TEXT NOT NULL, author_handle TEXT NOT NULL, text TEXT NOT NULL DEFAULT '', created_at DATETIME NOT NULL, like_count INTEGER DEFAULT 0, repost_count INTEGER DEFAULT 0, reply_count INTEGER DEFAULT 0, source TEXT NOT NULL CHECK(source IN ('saved', 'liked')), facets TEXT, indexed_at DATETIME DEFAULT CURRENT_TIMESTAMP);
-- FTS5 virtual table for full-text searchCREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5( text, author_handle, content='posts', content_rowid='rowid', tokenize='unicode61');
-- Triggers to keep FTS5 index in sync with posts tableCREATE TRIGGER IF NOT EXISTS posts_ai AFTER INSERT ON posts BEGIN INSERT INTO posts_fts(rowid, text, author_handle) VALUES (new.rowid, new.text, new.author_handle);END;
CREATE TRIGGER IF NOT EXISTS posts_ad AFTER DELETE ON posts BEGIN INSERT INTO posts_fts(posts_fts, rowid, text, author_handle) VALUES ('delete', old.rowid, old.text, old.author_handle);END;
CREATE TRIGGER IF NOT EXISTS posts_au AFTER UPDATE ON posts BEGIN INSERT INTO posts_fts(posts_fts, rowid, text, author_handle) VALUES ('delete', old.rowid, old.text, old.author_handle); INSERT INTO posts_fts(rowid, text, author_handle) VALUES (new.rowid, new.text, new.author_handle);END;