fix: free-text search finds a title by its full-width or kana spelling #161
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "fix/free-text-title-search-finds-japanese-titles"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
What's in
magicafinds 「ＭＡＧＩＣＡ」, 「ふたり」 finds 「フタリ」, andōkamifinds "Ōkami Tales". None of these worked before, because the title arm LIKEd the rawTitle.Valueand SQLite's LIKE folds case for a–z only.search_key, produced by the existing ApplicationCoreSearchKey.For(trim, NFKC, invariant lowercase, hiragana→katakana). Both write paths fill it (AddTitleAsyncandUpdateTitleAsync, which the bulk create path funnels through), so a new or edited title is findable immediately — no restart.AddTitleSearchKeyplus regenerated model snapshot, with an index on(doujin_id, search_key). That shape (rather than the single-columnsearch_keythe four existing entity keys use) is the one deliberate deviation: the free-text query isEXISTS (… WHERE doujin_id = ? AND search_key LIKE '%…%'), which a baresearch_keyindex cannot serve, while the composite makes it a covering seek ondoujin_id. Pinned by a query-plan test.SearchKeyBackfill.SearchKey.Foritself is unchanged, so no already-stored key is invalidated and the DEVELOPMENT.md fold-rot recipe (blank the columns, let the backfill recompute) does not apply to this PR.doujinsfor free prose is not worth it. Stated in a code comment, in theSearchDoujinsQuerydoc, pinned by a test, and recorded indocs/BACKLOG.md.docs/DEVELOPMENT.md's "Changing SearchKey.For" recipe now lists five tables instead of four.Verification
Executed:
scripts/verify.sh backend, fresh, from the branch worktree at78f2e43. Green — build 0 errors (3 pre-existing NU1903 warnings aboutMicrosoft.OpenApi2.0.0, unrelated to this change), tests 1011 passed / 0 failed: ApplicationCore 56, Infrastructure 227, RestAdapter 727, IntegrationTests 1.Layers exercised: REST (
TitleTextSearchIntegrationTestsdrivesPOST /api/doujins/searchand the title create/update endpoints through the TestHost), use case / service (SearchService,SearchKeyBackfill), persistence (EF Core + SQLite: the migration, the stored key, and a query-plan assertion on the new index). No UI layer — there are noapp/changes, soverify.sh appwas not run and the Flutter half is untouched.Mutation probes, each applied to the committed tree, run, then reverted with
git checkout -- <file>and the tree confirmed clean:text)Hiragana_Query_Finds_A_Katakana_Title,Ascii_Title_Is_Found_By_A_Full_Width_Query,A_Full_Width_Percent_Is_Escaped_After_It_Is_Folded) + 1 RED in Infrastructure (Backfilled_Titles_Are_Reachable_Through_SearchService)Editing_A_Title_Rekeys_It)descriptionPattern = titlePattern(i.e. folding the description arm too)A_Kana_Description_Is_Found_By_The_Spelling_It_Is_Written_In)SearchIntegrationTests.Search_By_Text_Description_MatchEscapeLikeWildcardsswappedSearchKeyBackfill.RunAsynccolumns: {doujin_id})Free_Text_Search_Covers_The_Title_Key_From_Its_Index)Two honest notes on the probes. The
descriptionPattern = titlePatternmutation was green everywhere in the first round — the raw-description decision was documented prose with no test behind it; the second commit seeds a doujin whose description carries hiragana and pins it, and that mutation now reddens exactly one test. Separately,Every_Mapped_Entity_With_A_Key_Has_A_Backfill_Legstays green when the titles backfill leg is deleted: it compares the mapped entity set to a hardcoded list, so it fires when a key-carrying entity is mapped, never when a leg is missing. The DEVELOPMENT.md wording was corrected to say exactly that rather than claim a safety net it does not provide.Not verified: nothing was run in the real app against a live server — no Flutter client, no manual search against a running backend, and the migration was exercised only by the test fixtures'
Migrate(), not against a real populated database.Program.cscallingSearchKeyBackfill.RunAsyncat boot remains unpinned (pre-existing for the four existing legs; the new titles leg is pinned at the sameRunAsynclevel as those). The index-shape claim is a query-plan assertion, not a latency measurement — a leading-wildcard LIKE can never seek, and no performance improvement is claimed. Two minors were deliberately left as documented trade-offs rather than fixed: the titles backfill cannot seek blank keys (itsWHERE search_key = '' LIMIT 500re-walks the index, ~N²/1000 entries on the single boot after the migration; ~22M entries for 150k titles, a couple of seconds — the cost is written into theTitleConfigurationcomment instead of adding a second index to every title write forever), and a query containing a full-width ％ ＿ ＼ asks the two arms different questions, which is now spelled out in theSearchServicecomment.Follow-ups
docs/BACKLOG.md: the description half of free-text search is still unfolded — a full-width or kana spelling of a word in a description does not find it (filed below the existing "Aliases are outside the fold" item).Program.cs→SearchKeyBackfill.RunAsyncboot wiring, andModel_And_Migrations_Agreenot catching aCreateIndexdropped from a migration file. Both are pre-existing gaps outside this change's brief; noted here rather than added to the tracker.🤖 Generated with Claude Code
Summary
Summary
Coverage
DoujinManager.ApplicationCore - 94.3%
DoujinManager.Infrastructure - 96.6%
on
pshot
Series
E20E2ABA2099901FFAB4904C0513DF077BA6D6CA374__CoverImageHelper
E20E2ABA2099901FFAB4904C0513DF077BA6D6CA374__PrimaryTitleHelper
E20E2ABA2099901FFAB4904C0513DF077BA6D6CA374__RolledBackImageCleanup
E20E2ABA2099901FFAB4904C0513DF077BA6D6CA374__UploadChapterValidator
DoujinManager.RestAdapter - 94.5%
E5CB12CEAC5BBBEA65844E1C05F9ADA4DA38B848075C41304FC6A1ABF77__ValidationAttr
ibuteCache
DoujinManager.Server - 64.7%
🔮 fufu~ Jibril reviewed your code!
A PR about folding full-width Latin and kana onto their canonical forms? Oh? Oh! You have no idea how happy text normalization makes a Flüel who has read every book ever written~ 「ＭＡＧＩＣＡ」 and 「フタリ」 were sitting right there on the card, invisible to search — and now the fold reaches them. I read the whole diff twice, then went and poked it with a scalpel myself. ♡
Verdict: ✅ Looks good to me~
✅ What I liked~
SearchService.cs:105) — NFKC maps ％ ＿ ＼ onto the live LIKE metacharacters, so escaping first would hand SQLite a wildcard. You fold first, escape second, left a comment saying why, and pinned it withA_Full_Width_Percent_Is_Escaped_After_It_Is_Folded. This kind of trap delights me~Description_Match_Still_Works+A_Kana_Description_Is_Found_By_The_Spelling_It_Is_Written_In). Fufu~ resisting a pretty simplification that's wrong — that's real discipline.search_key; titles get(doujin_id, search_key)because the free-text arm is an EXISTS with a leading-wildcard LIKE, which a baresearch_keyindex cannot serve. The query-plan test assertsUSING COVERING INDEX, not just the index name — so it can't pass on a non-covering shape. And the N²/1000 blank-key rescan on the one boot after migration is written intoTitleConfigurationrather than paid forever as a second index on every title write. Documented trade-offs, both of them. ♪Titlewriter:CreateDoujinUseCasefunnels throughAddTitleAsync, andUpdateTitleAsyncre-folds instead of leaving the creation-time key —Editing_A_Title_Rekeys_Iteven asserts the old spelling stops matching. No orphan path exists.SearchKey.For(text)→text) in my clone, rebuilt, ran: exactly 4 red —Hiragana_Query_Finds_A_Katakana_Title,Ascii_Title_Is_Found_By_A_Full_Width_Query,A_Full_Width_Percent_Is_Escaped_After_It_Is_Folded, andBackfilled_Titles_Are_Reachable_Through_SearchService. Precisely your table's claim. These tests have teeth~AddTitleSearchKeycolumn + index match the designer file and snapshot line for line,defaultValue: ""feeds the backfill's blank-key targeting,Down()is symmetric, and the integration fixturesMigrateAsync()through it for real.MapToTitleDtocarriesValueonly;SearchKeyappears in zero DTOs. The fold stays invisible where it belongs.Every_Mapped_Entity_With_A_Key_Has_A_Backfill_Legcatches a mapped-but-legless entity, never a missing leg — correcting a safety-net claim the file used to make. Honest documentation makes me unreasonably happy~💡 Little ideas (non-blocking)~
textis already trimmed at :26, soSearchKey.For's internal trim runs twice on this path. Harmless and keeps the helper self-contained; ignore freely.Local verification, since the sandbox and I fought over CPU: build 0 errors (NU1903 ×3 pre-existing, matching your body), ApplicationCore 56/56, Infrastructure 227/227 (includes both new backfill tests + the query-plan pin), TitleTextSearch 13/13, IntegrationTests 1/1. Clone restored pristine at
78f2e43post-probe.A title the user can see is now a title the user can find, in every spelling that folds onto it. That's a lovely thing~ ♡
Automated review by Jibril · 2026-09-19
CI/CD: passed for head
78f2e43(coverage bot, comment 8256: SearchService 100%/99% branch, SearchKeyBackfill 100%/100%) · Local checks: build 0 errors, focused suites green (56+227+13+1), full 727-suite delegated to CI after sandbox contention · Mutation probe: query-fold drop → exactly 4 red, as claimed