jimandClaude Opus 5
b31d5842cc
Notes.RootBlogName/NoteBlogName/Type became RootBlogId/NoteBlogId/TypeId on 2026-08-07, resolved through the new BlogNames and NoteTypes tables. There is no compatibility view, so every affected statement is a hard cut. All 14 call sites in DataAccess.cs are ported: - Notes->Blogs joins go through Blogs.BlogId in one integer hop; the Notes->Posts join in GetRepliesWithFilledText is the only one that must route through BlogNames, since Posts carries no BlogId - AddNote registers both blog names and the note type with INSERT OR IGNORE before inserting, in one transaction committed before the console sleep. Registering the type matters: an unseen type would resolve to NULL and fail NOT NULL on TypeId, silently losing the note - The LEFT JOIN Notes in GetPosts is dropped rather than translated. It selected nothing, could not remove a row, and its duplicates were collapsed by the query's own GROUP BY - Duplicate-key detection moves to IsNotesDuplicateKey, matching the constraint and table instead of an exact column list. The old literal string is what broke on this rename - EnsureReplyTextColumnExists drops DEFAULT '.', matching the migrated schema: new rows get NULL, not a placeholder nobody wrote verify-db-schema.sql gains BlogNames, NoteTypes, Blogs.BlogId and the new Notes columns, plus query 1d naming a pre-migration file and pointing at normalize-notes.sql. Blogs.BlogId is deliberately not auto-fixable -- an added-but-empty column makes engagement joins return zero rows silently. Verified against the live 148 MB file: query plans hit the intended indexes, and the BlogId join matches an independent name-resolved formulation exactly on all 4,267 GetBlogs and 2,637 GetBlogsForLikes rows. RolodexRepository.cs (16 sites) lives in the Rolodex repo and is not covered here. Co-Authored-By: Claude Opus 5 <[email protected]>
248 lines
15 KiB
SQL
248 lines
15 KiB
SQL
-- ============================================================================
|
|
-- verify-db-schema.sql
|
|
--
|
|
-- Purpose: Verify that a TL.db (e.g. a restored backup) has every column the
|
|
-- current URLNotesGrabberCORE code expects. The app has NO startup
|
|
-- migration: missing columns only get added when specific modes run,
|
|
-- and a referenced-but-missing column causes a "no such column" crash.
|
|
--
|
|
-- How to use (DB Browser for SQLite):
|
|
-- 1. File > Open Database -> pick the restored backup.
|
|
-- 2. Execute SQL tab. Run SECTION 1 (it is read-only).
|
|
-- * Zero rows from every query = schema is fully aligned, you're done.
|
|
-- * Rows in "MISSING COLUMNS" = copy the run_this_to_fix text.
|
|
-- 3. If columns are missing: KEEP A COPY OF THE BACKUP FIRST, then go to
|
|
-- SECTION 2, uncomment ONLY the ALTER lines that match the report, and run.
|
|
-- 4. Re-run SECTION 1 to confirm zero rows.
|
|
--
|
|
-- This script never UPDATEs/DELETEs/DROPs. In particular it deliberately does
|
|
-- NOT replicate the likes-reset that the app's -likes migration performs
|
|
-- (DataAccess.cs:375), so existing likes high-water marks are preserved.
|
|
-- ============================================================================
|
|
|
|
|
|
-- ============================================================================
|
|
-- SECTION 1 -- VERIFICATION (read-only)
|
|
-- ============================================================================
|
|
|
|
-- Expected schema for the current code version.
|
|
-- alter_stmt is a runnable ALTER for additively-fixable columns; for base
|
|
-- columns it is a 'MANUAL REVIEW' note (a missing base column means the backup
|
|
-- predates the table's creation or is damaged -- do not blindly auto-add).
|
|
WITH expected(tbl, col, alter_stmt) AS (
|
|
VALUES
|
|
-- Posts (base columns: manual review if missing)
|
|
('Posts','BlogName', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Posts','PostID', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Posts','HasNotesGathered', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','reblogURL', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','NotFound', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','PostDate', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','NotesGatheredDateTime', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','HasImage', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','PostURL', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Slug', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','ReblogKey', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','ReblogName', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Summary', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Quote', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Body', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Tags', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Link', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','PhotoURL', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','PhotoCaption', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','DownloadedFiles', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','AudioCaption', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Question', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Answer', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','Title', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','ByLikes', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','RootBlogName', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','RootURL', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','DateModified', 'MANUAL REVIEW - base column missing'),
|
|
('Posts','DateCreated', 'MANUAL REVIEW - base column missing'),
|
|
-- Posts (additive migration column, auto-fixable)
|
|
('Posts','PostType', 'ALTER TABLE Posts ADD COLUMN PostType TEXT;'),
|
|
|
|
-- Blogs (base columns: manual review if missing)
|
|
('Blogs','BlogName', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Blogs','HasBeenOutput', 'MANUAL REVIEW - base column missing'),
|
|
('Blogs','IsActive', 'MANUAL REVIEW - base column missing'),
|
|
('Blogs','DateAdded', 'MANUAL REVIEW - base column missing'),
|
|
('Blogs','ByLikes', 'MANUAL REVIEW - base column missing'),
|
|
('Blogs','DateModified', 'MANUAL REVIEW - base column missing'),
|
|
('Blogs','DateCreated', 'MANUAL REVIEW - base column missing'),
|
|
-- Blogs (additive migration columns, auto-fixable)
|
|
('Blogs','LikesPulled', 'ALTER TABLE Blogs ADD COLUMN LikesPulled INTEGER DEFAULT 0;'),
|
|
('Blogs','LikesCursor', 'ALTER TABLE Blogs ADD COLUMN LikesCursor INTEGER DEFAULT 0;'),
|
|
('Blogs','LikesNewestTimestamp', 'ALTER TABLE Blogs ADD COLUMN LikesNewestTimestamp INTEGER DEFAULT 0;'),
|
|
('Blogs','LikesLastRefreshed', 'ALTER TABLE Blogs ADD COLUMN LikesLastRefreshed INTEGER DEFAULT 0;'),
|
|
('Blogs','LikesLastNewCount', 'ALTER TABLE Blogs ADD COLUMN LikesLastNewCount INTEGER DEFAULT 0;'),
|
|
('Blogs','TTFolderPath', 'ALTER TABLE Blogs ADD COLUMN TTFolderPath TEXT;'),
|
|
-- Blogs.BlogId (2026-08-07) is the single-hop join key into Notes. Deliberately NOT
|
|
-- auto-fixable: an added-but-empty BlogId makes every engagement join return zero
|
|
-- rows silently, which is worse than the hard error a missing column gives.
|
|
('Blogs','BlogId', 'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
|
|
|
|
-- Notes (base columns: manual review if missing)
|
|
-- Integer IDs since 2026-08-07. RootBlogName/NoteBlogName/Type are GONE, not renamed
|
|
-- in place -- a backup that still has them needs normalize-notes.sql, not an ALTER.
|
|
-- Query 1d below reports exactly that case.
|
|
('Notes','RootBlogId', 'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
|
|
('Notes','PostID', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Notes','NoteBlogId', 'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
|
|
('Notes','TimeStamp', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Notes','TypeId', 'MANUAL REVIEW - see query 1d: pre-2026-08-07 name schema, or damaged'),
|
|
('Notes','DatetimeCrawled', 'MANUAL REVIEW - base column missing'),
|
|
('Notes','DateModified', 'MANUAL REVIEW - base column missing'),
|
|
('Notes','DateCreated', 'MANUAL REVIEW - base column missing'),
|
|
-- Notes (additive migration column, auto-fixable)
|
|
-- No DEFAULT: the migrated schema dropped it, so new rows get NULL rather than a
|
|
-- placeholder. EnsureReplyTextColumnExists in DataAccess.cs adds it the same way.
|
|
('Notes','replyText', 'ALTER TABLE Notes ADD COLUMN replyText TEXT;'),
|
|
|
|
-- BlogNames / NoteTypes (the lookup tables Notes resolves its IDs through, 2026-08-07).
|
|
-- Not auto-fixable: an empty BlogNames does not mean "add the table", it means the
|
|
-- Notes rows have nothing to resolve against. Rebuild with normalize-notes.sql.
|
|
('BlogNames','BlogId', 'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
|
|
('BlogNames','BlogName', 'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
|
|
('NoteTypes','TypeId', 'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
|
|
('NoteTypes','Type', 'MANUAL REVIEW - see query 1d: run normalize-notes.sql'),
|
|
|
|
-- DailyAPICount (base columns)
|
|
('DailyAPICount','Date', 'MANUAL REVIEW - base/PK column missing'),
|
|
('DailyAPICount','APICount', 'MANUAL REVIEW - base column missing'),
|
|
|
|
-- ApiKeyPoolState (created at runtime by EnsureApiKeyPoolTables; auto-fixable by re-running app, but safe to add)
|
|
('ApiKeyPoolState','KeyName', 'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables'),
|
|
('ApiKeyPoolState','RetryUntil', 'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables'),
|
|
('ApiKeyPoolMeta','Id', 'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables'),
|
|
('ApiKeyPoolMeta','LastIndex', 'MANUAL REVIEW - run app once to auto-create ApiKeyPool tables')
|
|
),
|
|
actual(tbl, col) AS (
|
|
SELECT 'Posts', name FROM pragma_table_info('Posts')
|
|
UNION ALL SELECT 'Blogs', name FROM pragma_table_info('Blogs')
|
|
UNION ALL SELECT 'Notes', name FROM pragma_table_info('Notes')
|
|
UNION ALL SELECT 'BlogNames', name FROM pragma_table_info('BlogNames')
|
|
UNION ALL SELECT 'NoteTypes', name FROM pragma_table_info('NoteTypes')
|
|
UNION ALL SELECT 'DailyAPICount', name FROM pragma_table_info('DailyAPICount')
|
|
UNION ALL SELECT 'ApiKeyPoolState', name FROM pragma_table_info('ApiKeyPoolState')
|
|
UNION ALL SELECT 'ApiKeyPoolMeta', name FROM pragma_table_info('ApiKeyPoolMeta')
|
|
)
|
|
|
|
-- 1a. MISSING COLUMNS: columns the code needs that the DB does not have.
|
|
-- Zero rows = good. Otherwise copy run_this_to_fix into SECTION 2.
|
|
SELECT
|
|
e.tbl AS table_name,
|
|
e.col AS missing_column,
|
|
e.alter_stmt AS run_this_to_fix
|
|
FROM expected e
|
|
LEFT JOIN actual a
|
|
ON a.tbl = e.tbl AND lower(a.col) = lower(e.col)
|
|
WHERE a.col IS NULL
|
|
ORDER BY (e.alter_stmt LIKE 'ALTER%') DESC, e.tbl, e.col;
|
|
|
|
|
|
-- 1b. MISSING TABLES: expected tables that don't exist at all in this DB.
|
|
-- Zero rows = good.
|
|
WITH expected_tables(tbl) AS (
|
|
VALUES ('Posts'),('Blogs'),('Notes'),('BlogNames'),('NoteTypes'),('DailyAPICount'),
|
|
('ApiKeyPoolState'),('ApiKeyPoolMeta')
|
|
)
|
|
SELECT et.tbl AS missing_table
|
|
FROM expected_tables et
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM sqlite_master
|
|
WHERE type = 'table' AND lower(name) = lower(et.tbl)
|
|
)
|
|
ORDER BY et.tbl;
|
|
|
|
|
|
-- 1c. EXTRA / UNEXPECTED COLUMNS: present in the DB but not in the expected
|
|
-- list above. Informational only -- e.g. a NEWER backup, or a column this
|
|
-- script's expected-list hasn't been updated for. Not an error by itself.
|
|
-- Posts.IsActive and Notes.IsActive are listed here and NOT in 1a on
|
|
-- purpose: they are written by other tools, the app only reads them when
|
|
-- present, and it must not be told to add them. See TL.db.md.
|
|
WITH expected(tbl, col) AS (
|
|
VALUES
|
|
('Posts','BlogName'),('Posts','PostID'),('Posts','HasNotesGathered'),('Posts','reblogURL'),
|
|
('Posts','NotFound'),('Posts','PostDate'),('Posts','NotesGatheredDateTime'),('Posts','HasImage'),
|
|
('Posts','PostURL'),('Posts','Slug'),('Posts','ReblogKey'),('Posts','ReblogName'),('Posts','Summary'),
|
|
('Posts','Quote'),('Posts','Body'),('Posts','Tags'),('Posts','Link'),('Posts','PhotoURL'),
|
|
('Posts','PhotoCaption'),('Posts','DownloadedFiles'),('Posts','AudioCaption'),('Posts','Question'),
|
|
('Posts','Answer'),('Posts','Title'),('Posts','ByLikes'),('Posts','RootBlogName'),('Posts','RootURL'),
|
|
('Posts','DateModified'),('Posts','DateCreated'),('Posts','PostType'),('Posts','IsActive'),
|
|
('Blogs','BlogName'),('Blogs','HasBeenOutput'),('Blogs','IsActive'),('Blogs','DateAdded'),
|
|
('Blogs','ByLikes'),('Blogs','DateModified'),('Blogs','DateCreated'),('Blogs','LikesPulled'),
|
|
('Blogs','LikesCursor'),('Blogs','LikesNewestTimestamp'),('Blogs','LikesLastRefreshed'),
|
|
('Blogs','LikesLastNewCount'),('Blogs','TTFolderPath'),('Blogs','BlogId'),
|
|
('Notes','RootBlogId'),('Notes','PostID'),('Notes','NoteBlogId'),('Notes','TimeStamp'),
|
|
('Notes','TypeId'),('Notes','DatetimeCrawled'),('Notes','DateModified'),('Notes','DateCreated'),
|
|
('Notes','replyText'),('Notes','IsActive'),
|
|
('BlogNames','BlogId'),('BlogNames','BlogName'),
|
|
('NoteTypes','TypeId'),('NoteTypes','Type'),
|
|
('DailyAPICount','Date'),('DailyAPICount','APICount'),
|
|
('ApiKeyPoolState','KeyName'),('ApiKeyPoolState','RetryUntil'),
|
|
('ApiKeyPoolMeta','Id'),('ApiKeyPoolMeta','LastIndex')
|
|
),
|
|
actual(tbl, col) AS (
|
|
SELECT 'Posts', name FROM pragma_table_info('Posts')
|
|
UNION ALL SELECT 'Blogs', name FROM pragma_table_info('Blogs')
|
|
UNION ALL SELECT 'Notes', name FROM pragma_table_info('Notes')
|
|
UNION ALL SELECT 'BlogNames', name FROM pragma_table_info('BlogNames')
|
|
UNION ALL SELECT 'NoteTypes', name FROM pragma_table_info('NoteTypes')
|
|
UNION ALL SELECT 'DailyAPICount', name FROM pragma_table_info('DailyAPICount')
|
|
UNION ALL SELECT 'ApiKeyPoolState', name FROM pragma_table_info('ApiKeyPoolState')
|
|
UNION ALL SELECT 'ApiKeyPoolMeta', name FROM pragma_table_info('ApiKeyPoolMeta')
|
|
)
|
|
SELECT a.tbl AS table_name, a.col AS unexpected_column
|
|
FROM actual a
|
|
LEFT JOIN expected e
|
|
ON e.tbl = a.tbl AND lower(e.col) = lower(a.col)
|
|
WHERE e.col IS NULL
|
|
ORDER BY a.tbl, a.col;
|
|
|
|
|
|
-- 1d. PRE-MIGRATION DATABASE: a backup from before 2026-08-07, when Notes still
|
|
-- stored names. Zero rows = good.
|
|
--
|
|
-- This is the one failure SECTION 2 cannot fix. Notes.RootBlogName /
|
|
-- NoteBlogName / Type were replaced by RootBlogId / NoteBlogId / TypeId
|
|
-- resolving through BlogNames and NoteTypes -- a data migration, not an
|
|
-- ADD COLUMN. There is no compatibility view, so the current code fails
|
|
-- outright ("no such column: RootBlogId") against such a file.
|
|
--
|
|
-- Fix: run normalize-notes.sql against a COPY of the backup, then re-run
|
|
-- SECTION 1. Do not hand-add the ID columns: they would be empty, and an
|
|
-- empty NoteBlogId is indistinguishable from a note by blog #0.
|
|
SELECT 'Notes still stores names -- run normalize-notes.sql on a copy' AS pre_migration_schema,
|
|
group_concat(name, ', ') AS legacy_columns_found
|
|
FROM pragma_table_info('Notes')
|
|
WHERE lower(name) IN ('rootblogname','noteblogname','type')
|
|
HAVING COUNT(*) > 0;
|
|
|
|
|
|
-- ============================================================================
|
|
-- SECTION 2 -- FIX (opt-in, additive only)
|
|
--
|
|
-- Run ONLY the lines that query 1a flagged with an ALTER statement.
|
|
-- KEEP A COPY OF THE BACKUP FIRST. SQLite has no "ADD COLUMN IF NOT EXISTS",
|
|
-- so running an ALTER for a column that already exists throws a harmless
|
|
-- "duplicate column name" error and changes nothing -- just run the flagged
|
|
-- subset. These are the 8 additive migration columns and nothing else; the
|
|
-- likes high-water-mark reset is intentionally NOT included.
|
|
--
|
|
-- Nothing here addresses query 1d. The Notes integer schema is a data migration
|
|
-- (normalize-notes.sql) and cannot be reached by adding columns.
|
|
-- ============================================================================
|
|
|
|
-- ALTER TABLE Posts ADD COLUMN PostType TEXT;
|
|
-- ALTER TABLE Blogs ADD COLUMN LikesPulled INTEGER DEFAULT 0;
|
|
-- ALTER TABLE Blogs ADD COLUMN LikesCursor INTEGER DEFAULT 0;
|
|
-- ALTER TABLE Blogs ADD COLUMN LikesNewestTimestamp INTEGER DEFAULT 0;
|
|
-- ALTER TABLE Blogs ADD COLUMN LikesLastRefreshed INTEGER DEFAULT 0;
|
|
-- ALTER TABLE Blogs ADD COLUMN LikesLastNewCount INTEGER DEFAULT 0;
|
|
-- ALTER TABLE Blogs ADD COLUMN TTFolderPath TEXT;
|
|
-- ALTER TABLE Notes ADD COLUMN replyText TEXT;
|