jimandClaude Opus 5
05ec465f74
Both columns carry the meaning Blogs.IsActive has: 0 = removed by another tool, anything else (including NULL) = live. Neither exists in the live TL.db yet, and both are added from outside this crawler, so the code has to work on databases either side of the change - naming a missing column is a hard SQLite error. HasIsActiveColumn asks PRAGMA table_info once per table per database path and caches it; AndIsActive/WhereIsActive return "COALESCE(IsActive, 1) = 1" or an empty string. Every read that selects posts or notes now carries the filter: GetPosts (both branches, including the per-blog count subquery), GetReplies, GetRepliesWithMissingText, GetRepliesWithFilledText, GetAllPostTextColumns, GetAllPostsForBlog, GetPost, GetPostByIdAnyBlog, and the engagement queries that count or join Notes - GetBlogs, GetBlogsAll and both note-joining variants of GetBlogsForLikes. The LEFT JOIN Notes in GetPosts is left alone on purpose: nothing is selected from it and it can neither add nor remove a row. LegacyPostsDbImporter is left alone too - it reads a foreign legacy schema. Writes were already safe and are documented rather than changed: no INSERT column list names IsActive, no UPDATE sets it, MapPrefixToColumn cannot map to it, and there is no INSERT OR REPLACE on Posts or Notes for a column default to be reset by. Re-crawling a removed row refreshes its content and leaves the flag at 0. As with Blogs, exclusion belongs at selection, so the update paths stay keyed on rows the caller already chose. Verified against three synthetic databases - no IsActive columns, columns present with a removed post and its notes, and columns present but NULL - by running every affected reader: the queries are valid in all three, the removed rows drop out only where the columns exist, NULL reads as live, and AddPost/AddNote/UpsertPostFromTextFile/UpdatePostContentFields leave an IsActive = 0 row at 0. Co-Authored-By: Claude Opus 5 <[email protected]>
203 lines
12 KiB
SQL
203 lines
12 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;'),
|
|
|
|
-- Notes (base columns: manual review if missing)
|
|
('Notes','RootBlogName', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Notes','PostID', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Notes','NoteBlogName', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Notes','TimeStamp', 'MANUAL REVIEW - base/PK column missing'),
|
|
('Notes','Type', 'MANUAL REVIEW - base/PK column missing'),
|
|
('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)
|
|
('Notes','replyText', 'ALTER TABLE Notes ADD COLUMN replyText TEXT DEFAULT ''.'';'),
|
|
|
|
-- 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 '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'),('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'),
|
|
('Notes','RootBlogName'),('Notes','PostID'),('Notes','NoteBlogName'),('Notes','TimeStamp'),
|
|
('Notes','Type'),('Notes','DatetimeCrawled'),('Notes','DateModified'),('Notes','DateCreated'),
|
|
('Notes','replyText'),('Notes','IsActive'),
|
|
('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 '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;
|
|
|
|
|
|
-- ============================================================================
|
|
-- 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.
|
|
-- ============================================================================
|
|
|
|
-- 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 DEFAULT '.';
|