Artifact [ad275ce02f]

Artifact ad275ce02f71196d57fd8d524bee2ae7d958c4c9aa3c2a06dcf9a9001d83690d:

• File internal/index/plan_test.go — part of check-in [450a8ad0f3] at 2026-08-17 20:42:49 on branch trunk — index: denormalize the timeline into an event table

  The timeline assembled itself at read time from a five-way UNION ALL, ending in USE TEMP B-TREE FOR ORDER BY — every request sorted every event in the repository to return fifty. tk cost 536ms; fossil serves the same page in 3-4ms whatever the repository size.

  This is fossil's shape: one event table with a kind column and an index on date, so the read is an index walk that stops at the limit. Triggers maintain it, like search_fts, so it cannot drift from its sources. isleaf and the ticket label are resolved for the returned page only.

  tk 536ms -> 4ms sqlite 210ms -> 5ms fossil 163ms -> 3ms pikchr 22ms -> 1ms home 399ms -> 3ms (it asked each repo for its newest event)

  plan_test asserts the plan, not the types: nothing about the old query was ill-typed, and the defect was only ever visible in EXPLAIN.

  Also fixes limit being ignored on every wildcard route: the router's second decode pass re-applied defaults to fields the path did not name, so ?limit=200 became 50. OverlayValues sets only what it names. (user: dmestas, size: 3242) [annotate] [blame] [check-ins using]

package index

import (
	"strings"
	"testing"
)

// The timeline once assembled itself at read time from a five-way UNION
// ALL and ended in USE TEMP B-TREE FOR ORDER BY — SQLite materializing
// and sorting every event in the repository to return fifty. It cost
// 536ms on tk where fossil serves the same page in 3-4ms.
//
// Nothing about that query was ill-typed; a code generator would have
// emitted it happily. The defect was only ever visible in the PLAN,
// which is why the plan is what this asserts.
func TestTimelineWalksAnIndex(t *testing.T) {
	ix := openIndex(t)

	// EXPLAIN QUERY PLAN reports the plan for the statement as written,
	// so this must be the same SQL Timeline issues. Kept beside it: if
	// the query changes shape and this is not updated, the test starts
	// describing a query that no longer exists — so assert on markers
	// general enough to survive cosmetic edits and specific enough to
	// catch a return to scanning.
	const q = `
		SELECT e.kind, e.hash, e.ref, e.date, e.user,
			CASE WHEN e.kind = 't'
				THEN 'Ticket [' || substr(e.ref, 1, 10) || '] ' || COALESCE(d.label, '')
				ELSE e.comment END,
			e.branch,
			CASE WHEN e.kind = 'ci'
				THEN NOT EXISTS (SELECT 1 FROM checkin_parent lp WHERE lp.parent = e.hash)
				ELSE 0 END
		FROM event e
		LEFT JOIN search_doc d ON e.kind = 't' AND d.hash = 'ticket:' || e.ref
		ORDER BY e.date DESC, e.hash DESC LIMIT ?`

	plan := queryPlan(t, ix, q, 50)

	// The sort is the expensive part and the one that scales with the
	// repository rather than the page.
	if strings.Contains(plan, "TEMP B-TREE") {
		t.Errorf("timeline sorts in a temp b-tree — it is materializing the whole repository:\n%s", plan)
	}
	// A plain SCAN of event means the ordering index was not used.
	if strings.Contains(plan, "SCAN event") && !strings.Contains(plan, "USING INDEX") {
		t.Errorf("timeline scans event instead of walking an index:\n%s", plan)
	}
	if !strings.Contains(plan, "event_by_date") {
		t.Errorf("timeline did not use event_by_date:\n%s", plan)
	}
}

// The branch-filtered timeline is a different plan and a different
// index; a fix that only covered the unfiltered page would leave the
// branch view scanning.
func TestBranchTimelineWalksAnIndex(t *testing.T) {
	ix := openIndex(t)
	const q = `
		SELECT e.kind, e.hash, e.ref, e.date, e.user, e.comment, e.branch, 0
		FROM event e
		WHERE e.branch = ? AND e.kind = ?
		ORDER BY e.date DESC, e.hash DESC LIMIT ?`

	plan := queryPlan(t, ix, q, "trunk", "ci", 50)
	if strings.Contains(plan, "TEMP B-TREE") {
		t.Errorf("branch timeline sorts in a temp b-tree:\n%s", plan)
	}
	if !strings.Contains(plan, "USING INDEX") {
		t.Errorf("branch timeline did not use an index:\n%s", plan)
	}
}

func queryPlan(t *testing.T, ix *Index, query string, args ...any) string {
	t.Helper()
	rows, err := ix.db.Query("EXPLAIN QUERY PLAN "+query, args...)
	if err != nil {
		t.Fatal(err)
	}
	defer rows.Close()
	var b strings.Builder
	for rows.Next() {
		var id, parent, notused int
		var detail string
		if err := rows.Scan(&id, &parent, &notused, &detail); err != nil {
			t.Fatal(err)
		}
		b.WriteString(detail)
		b.WriteByte('\n')
	}
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	return b.String()
}