macrame-db 0.7.0

A Bitemporal Graph Ledger on libSQL · Embedded knowledge database
Documentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
use std::future::Future;
use std::pin::Pin;

use crate::error::{DbError, Result};
use crate::schema::ddl::*;

/// Schema version this build understands, stored in SQLite's `user_version`.
///
/// The baseline is **2**, not 1, on purpose. Builds before 0.5.4 stamped
/// `user_version = 1` over the pre-canonical schema — no `CHECK` constraints,
/// second-precision timestamps, the narrow sentinel. Had the canonical baseline
/// kept the number 1, one of those files would open silently and every
/// guarantee D-029 buys would be void on it while `user_version` insisted all
/// was well. Reserving 1 as a value this build refuses by name is what makes
/// "no legacy support" an enforced property instead of a README sentence.
pub const SCHEMA_VERSION: u32 = 7;

type StepFuture<'a> = Pin<Box<dyn Future<Output = Result<()>> + Send + 'a>>;

/// One rung of the ladder: takes a database at `from` and leaves it at `to`.
struct Step {
    from: u32,
    to: u32,
    name: &'static str,
    apply: for<'a> fn(&'a libsql::Connection) -> StepFuture<'a>,
}

/// The ladder, in no particular order — `run` walks it by matching `from`.
///
/// The rung out of 0 lays the whole schema; the rung out of 2 adds only what
/// v3 introduced. There is deliberately still no rung out of 1: that is the
/// pre-canonical schema D-032 refuses by name, and v2 is not the same case —
/// it was written by this same 0.5.4 line with canonical timestamps and every
/// CHECK in place, so it is missing a derivative table and nothing else.
const STEPS: &[Step] = &[
    Step {
        from: 0,
        to: SCHEMA_VERSION,
        name: "baseline-0.5.4",
        apply: |conn| Box::pin(baseline(conn)),
    },
    Step {
        from: 2,
        to: 3,
        name: "analytics-annotations",
        apply: |conn| Box::pin(add_analytics_annotations(conn)),
    },
    Step {
        from: 3,
        to: 4,
        name: "traversal-covering-index",
        apply: |conn| Box::pin(add_traversal_cover(conn)),
    },
    Step {
        from: 4,
        to: 5,
        name: "concepts-fts",
        apply: |conn| Box::pin(add_concepts_fts(conn)),
    },
    Step {
        from: 5,
        to: 6,
        name: "single-open-interval-index",
        apply: |conn| Box::pin(add_open_interval_index(conn)),
    },
    Step {
        from: 6,
        to: 7,
        name: "links-weight-check",
        apply: |conn| Box::pin(add_weight_check(conn)),
    },
];

/// Bring `conn`'s database up to [`SCHEMA_VERSION`], or fail explaining why not.
///
/// Reading `user_version` before writing is the whole point. The previous
/// implementation re-ran every `CREATE … IF NOT EXISTS` unconditionally and then
/// stamped the version it had never read, which meant it could not distinguish a
/// fresh file from a foreign one from a database written by a future build — it
/// simply asserted the schema it wanted and hoped. `IF NOT EXISTS` hides exactly
/// the case that matters: an object that exists with a *different* definition is
/// silently kept, so a legacy table would survive with none of its constraints
/// while the stamp claimed otherwise.
/// What [`run`] did, so a caller can react to the schema having moved.
///
/// The one caller that must is `Database::open`: a `SCHEMA_VERSION` bump
/// invalidates every snapshot on disk (D-043), and until Wave 4.4 nothing
/// noticed — the first `reconstruct` after an upgrade skipped every snapshot as
/// incompatible and folded from genesis, correctly and expensively, with the
/// only trace a `warn!` per skipped file.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct MigrationOutcome {
    /// The version the file carried on the way in.
    pub from: u32,
    /// [`SCHEMA_VERSION`], always — `run` either reaches it or fails.
    pub to: u32,
}

impl MigrationOutcome {
    /// Whether an **existing** database moved between versions.
    ///
    /// A fresh file (`from == 0`) is deliberately not an upgrade. It has no
    /// snapshots to invalidate, so there is nothing to re-anchor — and treating
    /// it as one made `Database::open` write a snapshot on every first open,
    /// which broke two contracts the suite already pins: an idle database is
    /// never anchored, and a handle opened with no cadence writes nothing until
    /// `close()`. Both are worth keeping. `open()` touching the disk when it was
    /// not asked to is surprising in its own right.
    pub fn upgraded(&self) -> bool {
        self.from != 0 && self.from != self.to
    }
}

pub async fn run(conn: &libsql::Connection) -> Result<MigrationOutcome> {
    let found = read_user_version(conn).await?;

    if found > SCHEMA_VERSION {
        return Err(DbError::Migration {
            to: SCHEMA_VERSION,
            reason: format!(
                "database is at schema v{found}; this build understands v{SCHEMA_VERSION} \
                 and will not operate on a schema it does not know. Upgrade macrame \
                 rather than opening the file with an older build."
            ),
        });
    }

    if found == 0 {
        refuse_if_occupied(conn).await?;
    }

    let mut current = found;
    while current != SCHEMA_VERSION {
        let step = STEPS
            .iter()
            .find(|s| s.from == current)
            .ok_or_else(|| no_path_from(current))?;
        apply_step(conn, step).await?;
        current = step.to;
    }

    verify(conn).await?;
    Ok(MigrationOutcome {
        from: found,
        to: SCHEMA_VERSION,
    })
}

/// Version this build stamps on databases it creates.
pub fn current_version() -> u32 {
    SCHEMA_VERSION
}

/// Refuse to lay the baseline over a database that already holds something.
///
/// `user_version` defaults to 0, so an unrelated SQLite file is indistinguishable
/// from a fresh one by version alone. Without this check, pointing macrame at the
/// wrong path would quietly add four tables and nine triggers to somebody else's
/// database — including delete guards that abort writes the owner never asked to
/// have guarded.
async fn refuse_if_occupied(conn: &libsql::Connection) -> Result<()> {
    let mut rows = conn
        .query(
            "SELECT COUNT(*) FROM sqlite_master WHERE name NOT LIKE 'sqlite_%'",
            (),
        )
        .await?;
    let objects: i64 = match rows.next().await? {
        Some(row) => row.get(0)?,
        None => 0,
    };

    if objects > 0 {
        return Err(DbError::Migration {
            to: SCHEMA_VERSION,
            reason: format!(
                "database carries no macrame schema version but already holds {objects} \
                 object(s); refusing to lay the baseline over an unrelated database. \
                 Point at a new file, or delete this one deliberately."
            ),
        });
    }
    Ok(())
}

/// Explain a version with no rung leading out of it.
fn no_path_from(current: u32) -> DbError {
    let reason = if current < SCHEMA_VERSION {
        format!(
            "database is at schema v{current}, written by a pre-0.5.4 build: its \
             timestamps are second-precision and its tables carry none of the \
             canonical-form CHECK constraints (D-029). This build provides no \
             migration path — create a new database."
        )
    } else {
        format!("no migration step leads out of schema v{current}")
    };
    DbError::Migration {
        to: SCHEMA_VERSION,
        reason,
    }
}

/// Run one rung inside a single transaction, stamp included.
///
/// `user_version` is a database-header field and its write is journalled like
/// any other, so stamping inside the transaction makes "the schema exists" and
/// "the schema is declared to exist" the same commit. A crash mid-step therefore
/// leaves a database that is still honestly at its old version, rather than one
/// stamped for a schema it only partly has.
async fn apply_step(conn: &libsql::Connection, step: &Step) -> Result<()> {
    let tx = conn
        .transaction_with_behavior(libsql::TransactionBehavior::Immediate)
        .await?;

    let res: Result<()> = async {
        (step.apply)(&tx).await?;
        // PRAGMA takes no bind parameters; `to` is a u32 read from a const.
        tx.execute(&format!("PRAGMA user_version = {}", step.to), ())
            .await?;
        Ok(())
    }
    .await;

    match res {
        Ok(()) => {
            tx.commit().await?;
            Ok(())
        }
        Err(e) => {
            let _ = tx.rollback().await;
            Err(DbError::Migration {
                to: step.to,
                reason: format!("step {:?}: {e}", step.name),
            })
        }
    }
}

/// The 0.5.4 schema, applied to an empty database.
async fn baseline(conn: &libsql::Connection) -> Result<()> {
    // concepts first: links declares a foreign key into it.
    conn.execute(CREATE_CONCEPTS_TABLE, ()).await?;
    conn.execute(CREATE_LINKS_TABLE, ()).await?;
    conn.execute(CREATE_LINKS_CURRENT_TABLE, ()).await?;
    conn.execute(CREATE_TRANSACTION_LOG_TABLE, ()).await?;
    // Derivative, and last: every index in CREATE_INDICES must have its table.
    conn.execute(CREATE_ANALYTICS_ANNOTATIONS_TABLE, ()).await?;
    // Before the triggers, not after: `trg_concepts_fts_*` name this table, and
    // SQLite resolves a trigger body's tables at CREATE TRIGGER time.
    conn.execute(CREATE_CONCEPTS_FTS, ()).await?;

    for index_ddl in CREATE_INDICES {
        conn.execute(index_ddl, ()).await?;
    }

    for trigger_ddl in CREATE_TRIGGERS {
        conn.execute(trigger_ddl, ()).await?;
    }

    Ok(())
}

/// v4 → v5: add the FTS5 index over concept text (§5.9, D-051).
///
/// Derivative and additive, so D-036 permits it — an FTS index over `concepts`
/// is Doctrine VI's second category, disposable and reconstructible. The two
/// triggers land on `concepts`, which *is* a frozen ledger table, but a trigger
/// changes neither its columns nor its rows; the compat contract freezes the
/// table's shape, and that is untouched.
///
/// Unlike the v2 → v3 rung this one **does** backfill, and can: the index is a
/// pure function of text the ledger already holds, so `'rebuild'` reconstructs
/// exactly what the triggers would have written had they always existed. That is
/// the difference between this and D-041's annotations, where the old data was
/// destroyed and no recovery existed.
async fn add_concepts_fts(conn: &libsql::Connection) -> Result<()> {
    conn.execute(CREATE_CONCEPTS_FTS, ()).await?;
    for trigger_ddl in CREATE_TRIGGERS {
        conn.execute(trigger_ddl, ()).await?;
    }
    conn.execute(REBUILD_CONCEPTS_FTS, ()).await?;
    Ok(())
}

/// v2 → v3: add the derivative analytics table (D-041).
///
/// Purely additive, and additive on the *periphery* — `analytics_annotations`
/// is Doctrine VI's second category, so D-036's freeze on the ledger tables is
/// not in play. Nothing is backfilled: annotations written before v3 went into
/// `concepts.content`, which is the defect, and there is no way to tell a label
/// that landed there from the document text it replaced. Recomputing is the
/// recovery, and recomputing is what this table exists to make cheap.
async fn add_analytics_annotations(conn: &libsql::Connection) -> Result<()> {
    conn.execute(CREATE_ANALYTICS_ANNOTATIONS_TABLE, ()).await?;
    for index_ddl in CREATE_INDICES {
        conn.execute(index_ddl, ()).await?;
    }
    Ok(())
}

/// v5 → v6: index the single-open-interval probe (D-059).
///
/// Index-only and on a derivative table, so D-036 permits it on the same two
/// grounds the v3 → v4 rung stood on. Nothing is dropped this time: the new
/// index and `idx_lc_traversal_cover` serve different shapes — one needs three
/// equality columns bound, the other leads on `source_id` alone — so neither
/// subsumes the other and keeping both is the point rather than an oversight.
///
/// **This is the largest measured win in the tree and it sat proven and
/// unshipped for a full cycle**, on the stated ground that an index is a schema
/// change wanting its own rung. That was a description of the work rather than
/// an objection to it. See [`CREATE_INDICES`] for the numbers.
///
/// Nothing is backfilled because an index has nothing to backfill; `CREATE
/// INDEX` populates it from the table. That makes this the cheapest rung on the
/// ladder and the only one whose cost is a function of existing row count alone.
async fn add_open_interval_index(conn: &libsql::Connection) -> Result<()> {
    for index_ddl in CREATE_INDICES {
        conn.execute(index_ddl, ()).await?;
    }
    Ok(())
}

/// The v7 shape of `links`, pinned as text (T2.1, D-083).
///
/// **Deliberately not `ddl::CREATE_LINKS_TABLE`.** Every other rung on this
/// ladder reuses the DDL constants, and for those it is right — they create an
/// index or a derivative table, and getting today's definition is the point. A
/// *table rebuild* is different: it produces whatever shape the constant names
/// at the moment it runs, so the day `links` gains a v8 column, this rung would
/// silently take a v6 database straight to the v8 shape and stamp it v7. The
/// ladder would then have two databases both stamped v7 with different columns,
/// and the v7 → v8 rung would run against a table that already had its change.
///
/// A migration rung is a statement about the past. Pinning the text is what
/// makes it one.
const LINKS_V7: &str = r#"
CREATE TABLE links_v7 (
    source_id   TEXT NOT NULL REFERENCES concepts(id),
    target_id   TEXT NOT NULL REFERENCES concepts(id),
    edge_type   TEXT NOT NULL,
    valid_from  TEXT NOT NULL,
    recorded_at TEXT NOT NULL,
    valid_to    TEXT NOT NULL DEFAULT '9999-12-31T23:59:59.999999Z',
    weight      REAL NOT NULL DEFAULT 1.0,
    properties  TEXT NOT NULL DEFAULT '{}',
    PRIMARY KEY (source_id, target_id, edge_type, valid_from, recorded_at),
    CHECK (weight >= 0.0 AND weight < 9e999 AND typeof(weight) = 'real'),
    -- (the timestamp CHECK, spelled out for the same pinning reason)
    CHECK (valid_from GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]Z' AND valid_to GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]Z' AND recorded_at GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]Z' AND 1)
)
"#;

/// v6 → v7: constrain `links.weight` (§4.7, T2.1, D-083).
///
/// # The only rung that rewrites a ledger table, and what that costs
///
/// SQLite has no `ADD CONSTRAINT`, so this is a full rebuild of `links` — the
/// largest table in the schema — inside [`apply_step`]'s single transaction:
/// create, copy, drop, rename, recreate triggers. Cost is O(rows) in time and
/// roughly 2× `links` in peak disk. Every other rung on this ladder is index
/// work or an additive table; this one is not, and a caller upgrading a large
/// database should expect it to take a while and to need the space.
///
/// It is taken **pre-1.0 on purpose**. D-032 makes this a baseline re-issue
/// today, which is cheap; after 1.0 the compat contract (D-036) freezes the
/// ledger tables and the same change becomes an unmigration.
///
/// # Doctrine III is not violated, and the case where it would be is refused
///
/// A rebuild that *altered* an assertion would be exactly what Doctrine III
/// forbids. This one copies every row verbatim — no clamping, no rounding, no
/// dropping. Which means a database already holding a weight the new constraint
/// rejects cannot be migrated at all, and this refuses **before** touching
/// anything, with a count and an example, rather than failing halfway through a
/// copy with a bare `CHECK constraint failed`.
///
/// Such rows are reachable: until this rung, `assert_edge(weight = -1.0)` was
/// accepted by the write API and refused only at load time (§4.7). That was the
/// gap. An operator who has them must decide what those assertions meant, and
/// that is not a decision a migration can take for them.
///
/// # Order, and the trap it avoids
///
/// `DROP TABLE links` first, then rename. Dropping the table takes its four
/// triggers with it, so the rename does not reparse a schema containing trigger
/// bodies that name a table which no longer exists — the failure T1.2 hit from
/// the other direction. All triggers are `IF NOT EXISTS`, so re-running the
/// whole array afterwards recreates the four on `links` and no-ops the rest.
/// No index is defined on `links`, so there is none to rebuild.
async fn add_weight_check(conn: &libsql::Connection) -> Result<()> {
    let offending: i64 = conn
        .query(
            "SELECT COUNT(*) FROM links WHERE NOT (weight >= 0.0 AND weight < 9e999 AND typeof(weight) = 'real')",
            (),
        )
        .await?
        .next()
        .await?
        .and_then(|r| r.get(0).ok())
        .unwrap_or(0);

    if offending > 0 {
        let example: Option<String> = conn
            .query(
                "SELECT source_id || ' -> ' || target_id || ' (' || edge_type || \
                 ') weight=' || CAST(weight AS TEXT) FROM links \
                 WHERE NOT (weight >= 0.0 AND weight < 9e999 AND typeof(weight) = 'real') LIMIT 1",
                (),
            )
            .await?
            .next()
            .await?
            .and_then(|r| r.get(0).ok());

        return Err(DbError::Migration {
            to: 7,
            reason: format!(
                "{offending} row(s) in `links` hold a weight the v7 constraint \
                 rejects, e.g. {}. Copying them verbatim is impossible and \
                 altering them would violate Doctrine III, so this migration \
                 refuses rather than choosing on your behalf. These rows were \
                 writable through `assert_edge` before v7 (§4.7) — decide what \
                 they were meant to assert, archive them, and retry.",
                example.as_deref().unwrap_or("<unreadable>")
            ),
        });
    }

    conn.execute(LINKS_V7, ()).await?;
    conn.execute(
        "INSERT INTO links_v7 (source_id, target_id, edge_type, valid_from, \
         recorded_at, valid_to, weight, properties) \
         SELECT source_id, target_id, edge_type, valid_from, recorded_at, \
                valid_to, weight, properties FROM links",
        (),
    )
    .await?;
    conn.execute("DROP TABLE links", ()).await?;
    conn.execute("ALTER TABLE links_v7 RENAME TO links", ())
        .await?;

    for trigger_ddl in CREATE_TRIGGERS {
        conn.execute(trigger_ddl, ()).await?;
    }

    Ok(())
}

/// v3 → v4: swap `idx_lc_src_active` for the traversal covering index (D-042).
///
/// Index-only, and on a derivative table, so D-036 permits it twice over. The
/// drop is the point as much as the create: the new index has the same seek
/// column and strictly more payload, so keeping the old one would cost a second
/// index write on every assertion and buy nothing. Order matters only for peak
/// disk — create first so the traversal is never left without an index at all,
/// even though the whole rung is one transaction.
async fn add_traversal_cover(conn: &libsql::Connection) -> Result<()> {
    for index_ddl in CREATE_INDICES {
        conn.execute(index_ddl, ()).await?;
    }
    conn.execute("DROP INDEX IF EXISTS idx_lc_src_active", ())
        .await?;
    Ok(())
}

/// The tables the baseline declares, by name, for [`verify`].
pub(crate) const BASELINE_TABLES: &[&str] = &[
    "concepts",
    "links",
    "links_current",
    "transaction_log",
    "analytics_annotations",
    "concepts_fts",
];

/// Confirm the database actually holds what the DDL claims to create.
///
/// Cheap insurance against the failure mode `IF NOT EXISTS` is built to hide: a
/// statement that no-ops instead of creating. It also catches the DDL arrays and
/// reality drifting apart — add a trigger to [`CREATE_TRIGGERS`] that fails to
/// compile as written and it is missing here rather than at the first write that
/// needed it.
///
/// **Presence by name, not a count of everything present.** The original
/// counted `sqlite_master` and required exactly four tables, which made
/// verification fail on any database carrying an object the baseline did not
/// create — and this schema now has three legitimate sources of those. A
/// registered embedding model adds `embeddings_<model>` (§4.1); libSQL's vector
/// index adds `libsql_vector_meta_shadow`, a shadow table and a shadow index of
/// its own; and D-036 explicitly permits post-1.0 migrations to add indexes. A
/// count treats all three as corruption and refuses to open a healthy file. What
/// verification is actually for is the absence of something required, so that is
/// what it now checks.
async fn verify(conn: &libsql::Connection) -> Result<()> {
    let mut rows = conn
        .query(
            "SELECT type, name FROM sqlite_master WHERE type IN ('table','trigger','index')",
            (),
        )
        .await?;

    let mut present: Vec<(String, String)> = Vec::new();
    while let Some(row) = rows.next().await? {
        present.push((row.get(0)?, row.get(1)?));
    }
    let has = |kind: &str, name: &str| {
        present
            .iter()
            .any(|(k, n)| k == kind && n.eq_ignore_ascii_case(name))
    };

    let mut missing: Vec<String> = Vec::new();
    for table in BASELINE_TABLES {
        if !has("table", table) {
            missing.push(format!("table {table}"));
        }
    }
    for name in trigger_names() {
        if !has("trigger", &name) {
            missing.push(format!("trigger {name}"));
        }
    }
    for name in index_names() {
        if !has("index", &name) {
            missing.push(format!("index {name}"));
        }
    }

    if !missing.is_empty() {
        return Err(DbError::Migration {
            to: SCHEMA_VERSION,
            reason: format!(
                "schema verification failed: the database is stamped v{SCHEMA_VERSION} \
                 but is missing {}: {}",
                missing.len(),
                missing.join(", ")
            ),
        });
    }
    Ok(())
}

/// The object names the DDL creates, recovered from the DDL itself.
///
/// Parsed rather than listed separately so that adding a trigger to
/// [`CREATE_TRIGGERS`] extends what `verify` requires, with no second list to
/// remember. A hand-kept list of names beside the statements that create them is
/// the drift D-035 is about.
fn names_after(ddl: &[&str], keyword: &str) -> Vec<String> {
    ddl.iter()
        .filter_map(|stmt| {
            let lower = stmt.to_ascii_lowercase();
            let at = lower.find(keyword)? + keyword.len();
            Some(
                stmt[at..]
                    .split_whitespace()
                    .next()?
                    .trim_matches(|c: char| !c.is_alphanumeric() && c != '_')
                    .to_string(),
            )
        })
        .filter(|n| !n.is_empty())
        .collect()
}

fn trigger_names() -> Vec<String> {
    names_after(CREATE_TRIGGERS, "create trigger if not exists ")
}

fn index_names() -> Vec<String> {
    names_after(CREATE_INDICES, "create index if not exists ")
}

async fn read_user_version(conn: &libsql::Connection) -> Result<u32> {
    // PRAGMA user_version yields a row, so it must go through query(), not
    // execute() -- libsql rejects a statement that returns rows from execute().
    let mut rows = conn.query("PRAGMA user_version", ()).await?;
    match rows.next().await? {
        Some(row) => Ok(row.get::<u32>(0)?),
        None => Ok(0),
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    /// A rung that does not advance the version would spin `run`'s loop forever.
    #[test]
    fn every_step_advances() {
        for step in STEPS {
            assert!(
                step.to > step.from,
                "step {:?} does not advance ({} -> {})",
                step.name,
                step.from,
                step.to
            );
        }
    }

    /// Two rungs out of the same version make the ladder ambiguous: `run` takes
    /// whichever comes first in the array, which is not a decision anyone made.
    #[test]
    fn no_two_steps_share_an_origin() {
        for (i, a) in STEPS.iter().enumerate() {
            for b in &STEPS[i + 1..] {
                assert_ne!(a.from, b.from, "two steps start at v{}", a.from);
            }
        }
    }

    /// The ladder has to actually reach the version this build stamps, or every
    /// fresh open fails with "no migration step leads out of v0".
    #[test]
    fn the_ladder_reaches_the_current_version() {
        let mut current = 0;
        for _ in 0..STEPS.len() {
            match STEPS.iter().find(|s| s.from == current) {
                Some(step) => current = step.to,
                None => break,
            }
        }
        assert_eq!(current, SCHEMA_VERSION);
    }

    /// `verify` requires every name this returns, so a parse that silently
    /// yielded nothing would turn verification into a no-op that passes on an
    /// empty database.
    #[test]
    fn every_trigger_and_index_yields_a_name() {
        let triggers = super::trigger_names();
        assert_eq!(triggers.len(), CREATE_TRIGGERS.len());
        assert!(
            triggers.iter().all(|n| n.starts_with("trg_")),
            "{triggers:?}"
        );

        let indices = super::index_names();
        assert_eq!(indices.len(), CREATE_INDICES.len());
        assert!(indices.iter().all(|n| n.starts_with("idx_")), "{indices:?}");
    }

    /// v1 belongs to the pre-canonical schema and must stay unreachable, or a
    /// 0.5.3 database silently becomes a supported input again.
    #[test]
    fn legacy_v1_has_no_rung() {
        assert!(
            !STEPS.iter().any(|s| s.from == 1),
            "a step out of v1 reintroduces pre-0.5.4 databases as a supported input"
        );
    }
}