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, ¬used, &detail); err != nil {
t.Fatal(err)
}
b.WriteString(detail)
b.WriteByte('\n')
}
if err := rows.Err(); err != nil {
t.Fatal(err)
}
return b.String()
}
// The ticket list had the timeline's disease: GROUP BY over every change
// row, two correlated subqueries per ticket — each with its own temp
// b-tree — and a HAVING that filtered after grouping, so paging saved
// nothing. 200ms on tk, with no zit call involved.
func TestTicketListWalksAnIndex(t *testing.T) {
ix := openIndex(t)
const q = `
SELECT id, created, updated, title, status FROM ticket
WHERE ?1 = '' OR updated < ?1 OR (updated = ?1 AND id > ?2)
ORDER BY updated DESC, id
LIMIT ?3`
plan := queryPlan(t, ix, q, "", "", 50)
if strings.Contains(plan, "TEMP B-TREE") {
t.Errorf("ticket list sorts in a temp b-tree:\n%s", plan)
}
if strings.Contains(plan, "CORRELATED") {
t.Errorf("ticket list runs a correlated subquery per row:\n%s", plan)
}
if !strings.Contains(plan, "ticket_by_updated") {
t.Errorf("ticket list did not use ticket_by_updated:\n%s", plan)
}
}
// A person's timeline (/timeline?u=, and the dashboard's "mine") once
// searched event_by_user, which knows nobody's order, and sorted every
// event the person ever made in a temp b-tree to return one page: 42ms
// for pkgsrc's most prolific committer, growing with their history. The
// same statements Timeline issues, with and without a cursor and a kind.
func TestUserTimelineWalksAnIndex(t *testing.T) {
ix := openIndex(t)
const sel = `
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,
0,
CASE WHEN e.kind = 'e' THEN COALESCE(substr(n.body, 1, 400), '') ELSE '' END
FROM event e
LEFT JOIN search_doc d ON e.kind = 't' AND d.hash = 'ticket:' || e.ref
LEFT JOIN technote n ON e.kind = 'e' AND n.hash = e.hash
`
const order = ` ORDER BY e.date DESC, e.hash DESC LIMIT ?`
for _, c := range []struct {
name string
where string
args []any
}{
{"user", `WHERE e.user = ? AND e.kind <> 'lc'`, []any{"dan", 61}},
{"user and cursor", `WHERE (e.date < ? OR (e.date = ? AND e.hash < ?)) AND e.user = ? AND e.kind <> 'lc'`,
[]any{"2026-01-01", "2026-01-01", "abc", "dan", 61}},
{"user and kind", `WHERE e.user = ? AND e.kind = ?`, []any{"dan", "ci", 61}},
} {
plan := queryPlan(t, ix, sel+c.where+order, c.args...)
if strings.Contains(plan, "TEMP B-TREE") {
t.Errorf("%s: sorts every event the person made in a temp b-tree:\n%s", c.name, plan)
}
if !strings.Contains(plan, "USING INDEX") {
t.Errorf("%s: did not use an index:\n%s", c.name, plan)
}
}
}