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]>
jimandClaude Opus 5
b31d5842cc