about summary refs log tree commit diff
diff options
context:
space:
mode:
authorPjotr Prins <pjotr.public01@thebird.nl>2026-11-14 03:31:13 +0000
committerPjotr Prins <pjotr.public01@thebird.nl>2026-11-14 03:31:13 +0000
commit5d00a06d8c7eeacc0a1058217e9c9e5676657605 (patch)
tree54f4f24453233477644696599f3fcd928181ece9
parent19f10528b3aed1e08f492865820850c308697539 (diff)
downloadai-memory-5d00a06d8c7eeacc0a1058217e9c9e5676657605.tar.gz
migrate by inspecting the schema, not by trusting user_version
A store can carry a newer column under an older version number: the
genenetwork4 store reported user_version = 3 while already having
reasoning.gist, so the v3->v4 ALTER failed with "duplicate column name:
gist" and every command on that store died in open_db -- search included.

Each step now adds its column only if it is missing, inspects pragma
table_info, and writes the version at the end. Backfills still run only
when a column was actually added.
-rw-r--r--db.odin90
1 files changed, 61 insertions, 29 deletions
diff --git a/db.odin b/db.odin
index a5622e4..31ce583 100644
--- a/db.odin
+++ b/db.odin
@@ -156,53 +156,85 @@ stmt_col_i64 :: proc(s: ^Stmt, idx: int) -> i64 {
 // db_migrate creates the schema and upgrades older stores in place.
 db_migrate :: proc(db: ^Db) -> bool {
 	// SCHEMA's `create table if not exists' never alters an existing table, and
-	// it creates indexes over the columns added since version 1.  So the column
+	// it creates indexes over the columns added since version 1, so the column
 	// additions have to happen before SCHEMA runs.  A fresh store is version 0
 	// and simply gets everything from SCHEMA.
-	// Version 2 added the basis and supersedes columns to reasoning.
-	if db_user_version(db) == 1 {
-		if !db_exec(db, "alter table reasoning add column basis text") {
-			return false
-		}
-		if !db_exec(db, "alter table reasoning add column supersedes integer references reasoning(id)") {
+	//
+	// Each step tests the schema rather than trusting user_version: a store can
+	// carry a newer column under an older number (another writer, a hand ALTER),
+	// and an unconditional ALTER then fails with "duplicate column name" -- which
+	// reads as corruption when it is only version skew.
+	version := db_user_version(db)
+
+	// Version 2: basis and supersedes on reasoning.
+	if version == 1 {
+		if !db_add_column(db, "reasoning", "basis", "text") {
 			return false
 		}
-		if !db_exec(db, "pragma user_version = 2") {
+		if !db_add_column(db, "reasoning", "supersedes",
+		                  "integer references reasoning(id)") {
 			return false
 		}
+		version = 2
 	}
 
-	// Version 3 added symbols.basename so that an unqualified identifier is an
-	// index seek rather than a LIKE scan.
-	if db_user_version(db) == 2 {
-		if !db_exec(db, "alter table symbols add column basename text") {
-			return false
-		}
-		if !db_backfill_basename(db) {
-			return false
-		}
-		if !db_exec(db, "pragma user_version = 3") {
-			return false
+	// Version 3: symbols.basename, so an unqualified identifier is an index seek
+	// rather than a LIKE scan.
+	if version == 2 {
+		if db_add_column(db, "symbols", "basename", "text") {
+			if !db_backfill_basename(db) {
+				return false
+			}
 		}
+		version = 3
 	}
 
-	// Version 4 added reasoning.gist: the one-line claim, so listings need no
-	// truncation.  Existing entries get a first-sentence gist backfilled.
-	if db_user_version(db) == 3 {
-		if !db_exec(db, "alter table reasoning add column gist text") {
-			return false
-		}
-		if !db_backfill_gist(db) {
-			return false
+	// Version 4: reasoning.gist, the one-line claim.  Entries written before the
+	// column existed get a first-sentence gist.
+	if version == 3 {
+		if db_add_column(db, "reasoning", "gist", "text") {
+			if !db_backfill_gist(db) {
+				return false
+			}
 		}
-		if !db_exec(db, "pragma user_version = 4") {
+		version = 4
+	}
+
+	if version > 0 {
+		if !db_exec(db, fmt.tprintf("pragma user_version = %d", version)) {
 			return false
 		}
 	}
-
 	return db_exec(db, SCHEMA)
 }
 
+// db_add_column adds a column unless it is already there, and reports whether
+// the table now has it.
+db_add_column :: proc(db: ^Db, table, column, decl: string) -> bool {
+	if db_has_column(db, table, column) {
+		return true
+	}
+	if !db_exec(db, fmt.tprintf("alter table %s add column %s %s", table, column, decl)) {
+		return false
+	}
+	return true
+}
+
+// db_has_column reports whether TABLE already has COLUMN.
+db_has_column :: proc(db: ^Db, table, column: string) -> bool {
+	s, ok := stmt(db, fmt.tprintf("pragma table_info(%s)", table))
+	if !ok {
+		return false
+	}
+	defer stmt_close(&s)
+	for stmt_step(&s) == .Row {
+		if stmt_col_text(&s, 1) == column {
+			return true
+		}
+	}
+	return false
+}
+
 // db_backfill_basename fills symbols.basename for stores created before
 // version 3.  The value is the identifier after the last dot of the qualified
 // name; SQLite has no right-to-left string functions, so this walks the rows.