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]>
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]>
Removed outdated binary files and the `run_500_times.bat` script, which automated repetitive runs of `URLNotesGrabberCORE`. The batch script is no longer needed or has been replaced.
Added `verify-db-schema.sql`, a new script to verify and align the SQLite database schema (`TL.db`) with the application's expected schema. The script includes:
- A verification section to identify missing/extra columns or tables.
- An optional fix section with `ALTER TABLE` statements to add missing columns.
The SQL script ensures database compatibility while preserving data integrity and avoiding destructive operations.
jimandClaude Opus 5