package store import ( "context" "database/sql" "errors" "strings" "testing" "time" "pageswiki/internal/db" ) func searchQueryPlan(t *testing.T, database *sql.DB, query string, args ...any) []string { t.Helper() rows, err := database.Query("EXPLAIN QUERY PLAN "+query, args...) if err != nil { t.Fatal(err) } defer rows.Close() var plan []string for rows.Next() { var id, parent, unused int var detail string if err := rows.Scan(&id, &parent, &unused, &detail); err != nil { t.Fatal(err) } plan = append(plan, detail) } if err := rows.Err(); err != nil { t.Fatal(err) } return plan } func TestSearchExcerptDeadlineIsOptionalButParentCancellationIsNot(t *testing.T) { database, err := db.Open(t.TempDir() + "/excerpt-deadline.db") if err != nil { t.Fatal(err) } defer database.Close() if err := db.Migrate(database); err != nil { t.Fatal(err) } New(database) tx, err := database.BeginTx(context.Background(), &sql.TxOptions{ReadOnly: true}) if err != nil { t.Fatal(err) } defer tx.Rollback() excerpts, err := loadSearchBodyExcerpts(context.Background(), tx, `"needle"*`, []any{1}, -time.Second) if err != nil || len(excerpts) != 0 { t.Fatalf("optional excerpt deadline discarded search: excerpts=%v err=%v", excerpts, err) } var one int if err := tx.QueryRow(`SELECT 1`).Scan(&one); err != nil || one != 1 { t.Fatalf("excerpt timeout left search transaction unusable: %d %v", one, err) } ctx, cancel := context.WithCancel(context.Background()) cancel() if _, err := loadSearchBodyExcerpts(ctx, tx, `"needle"*`, []any{1}, time.Second); !errors.Is(err, context.Canceled) { t.Fatalf("parent cancellation was swallowed: %v", err) } if _, err := loadSearchBodyExcerpts(context.Background(), tx, `NEAR(`, []any{1}, time.Second); err == nil { t.Fatal("non-timeout SQL failure was swallowed") } } func TestSearchFTSPlanScansMatchesOnce(t *testing.T) { database, err := db.Open(t.TempDir() + "/search-plan.db") if err != nil { t.Fatal(err) } defer database.Close() if err := db.Migrate(database); err != nil { t.Fatal(err) } New(database) for _, field := range []string{"body", "filename"} { plan := searchQueryPlan(t, database, searchFTSCandidatesSQL, field+` : ("needle"*)`, "domain", "/", "/", "0", SearchCandidateLimit) if len(plan) != 3 || !strings.Contains(plan[0], "SCAN revision_search VIRTUAL TABLE") || !strings.Contains(plan[1], "revision_uuid=?") || !strings.Contains(plan[2], "object_uuid=? AND domain_uuid=?") { t.Fatalf("%s search must scan FTS once, then use indexed Revision/Path seeks:\n%s", field, strings.Join(plan, "\n")) } } plan := searchQueryPlan(t, database, searchBodyExcerptsSQL(2), `body : ("needle"*)`, 1, 2) if len(plan) != 1 || !strings.Contains(plan[0], "VIRTUAL TABLE INDEX 0:=M4") { t.Fatalf("snippets must use both rowid and MATCH constraints:\n%s", strings.Join(plan, "\n")) } } func TestSearchFilteredFTSPlanKeepsMatchFirstAndIndexedSeeks(t *testing.T) { database, err := db.Open(t.TempDir() + "/search-filter-plan.db") if err != nil { t.Fatal(err) } defer database.Close() if err := db.Migrate(database); err != nil { t.Fatal(err) } New(database) from, before := int64(1000), int64(2000) for _, options := range []SearchOptions{ {FileType: "code"}, {Extension: "py"}, {FromMS: &from, BeforeMS: &before}, {DateField: "created", FromMS: &from, BeforeMS: &before, FileType: "code", Extension: "py"}, } { predicate, filterArgs := searchOptionsPredicate(options) args := []any{`body : ("needle"*)`, "domain", "/", "/", "0"} args = append(args, filterArgs...) args = append(args, SearchCandidateLimit) plan := searchQueryPlan(t, database, searchFTSCandidatesBaseSQL+predicate+` LIMIT ?`, args...) if len(plan) < 3 || !strings.Contains(plan[0], "SCAN revision_search VIRTUAL TABLE") || !strings.Contains(plan[1], "revision_uuid=?") || !strings.Contains(plan[2], "object_uuid=? AND domain_uuid=?") { t.Fatalf("filtered FTS must scan MATCH once, then indexed Revision/Path seeks: %+v\n%s", options, strings.Join(plan, "\n")) } if options.DateField == "created" { if len(plan) != 5 || !strings.Contains(plan[4], "SEARCH o USING INDEX") || !strings.Contains(plan[4], "uuid=?") { t.Fatalf("Object date needs one indexed PK lookup, not a scan: %s", strings.Join(plan, "\n")) } } args = []any{"domain", "/", "/", "0"} args = append(args, filterArgs...) args = append(args, SearchCandidateLimit) plan = searchQueryPlan(t, database, searchPathCandidatesBaseSQL+predicate+` ORDER BY p.path LIMIT ?`, args...) joinedPlan := strings.Join(plan, "\n") if len(plan) < 2 || !strings.Contains(plan[0], "SEARCH p USING INDEX") || !strings.Contains(plan[0], "domain_uuid=?") || !strings.Contains(joinedPlan, "SEARCH r USING INDEX") || !strings.Contains(joinedPlan, "object_uuid=?") || strings.Contains(joinedPlan, "TEMP B-TREE") { t.Fatalf("filter-only search must keep indexed Domain order and bounded candidate memory: %s", strings.Join(plan, "\n")) } } }