commit ea3b2c1b1c93febf1cb333e22d5f43bd02804676
parent c625578e7d06620ae4d5d63997f7cf66564972f1
Author: triesap <tyson@radroots.org>
Date: Fri, 20 Mar 2026 15:41:05 +0000
db: repair legacy sqlite indexes and wasm parity
Diffstat:
6 files changed, 141 insertions(+), 5 deletions(-)
diff --git a/crates/replica-db/migrations/0019_repair_missing_indexes.down.sql b/crates/replica-db/migrations/0019_repair_missing_indexes.down.sql
@@ -0,0 +1,4 @@
+DROP INDEX IF EXISTS nostr_event_state_kind_idx;
+DROP INDEX IF EXISTS plot_farm_d_tag_idx;
+DROP INDEX IF EXISTS gcs_location_geohash_idx;
+DROP INDEX IF EXISTS farm_pubkey_d_tag_idx;
diff --git a/crates/replica-db/migrations/0019_repair_missing_indexes.up.sql b/crates/replica-db/migrations/0019_repair_missing_indexes.up.sql
@@ -0,0 +1,4 @@
+CREATE UNIQUE INDEX IF NOT EXISTS farm_pubkey_d_tag_idx ON farm(pubkey, d_tag);
+CREATE INDEX IF NOT EXISTS gcs_location_geohash_idx ON gcs_location(geohash);
+CREATE UNIQUE INDEX IF NOT EXISTS plot_farm_d_tag_idx ON plot(farm_id, d_tag);
+CREATE INDEX IF NOT EXISTS nostr_event_state_kind_idx ON nostr_event_state(kind);
diff --git a/crates/replica-db/src/migrations.rs b/crates/replica-db/src/migrations.rs
@@ -98,6 +98,11 @@ pub static MIGRATIONS: &[Migration] = &[
up_sql: include_str!("../migrations/0018_nostr_event_state.up.sql"),
down_sql: include_str!("../migrations/0018_nostr_event_state.down.sql"),
},
+ Migration {
+ name: "0019_repair_missing_indexes",
+ up_sql: include_str!("../migrations/0019_repair_missing_indexes.up.sql"),
+ down_sql: include_str!("../migrations/0019_repair_missing_indexes.down.sql"),
+ },
];
pub fn run_all_up<E>(executor: &E) -> Result<(), SqlError>
diff --git a/crates/replica-db/tests/migration_repairs.rs b/crates/replica-db/tests/migration_repairs.rs
@@ -0,0 +1,86 @@
+use radroots_replica_db::migrations;
+use radroots_sql_core::{SqlExecutor, SqliteExecutor};
+use serde_json::Value;
+
+fn query_rows(exec: &SqliteExecutor, sql: &str) -> Vec<Value> {
+ serde_json::from_str(&exec.query_raw(sql, "[]").expect("query should succeed"))
+ .expect("query should decode")
+}
+
+fn create_legacy_schema_without_secondary_indexes(exec: &SqliteExecutor) {
+ let schema = [
+ "CREATE TABLE __migrations (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, applied_at TEXT NOT NULL DEFAULT (datetime('now')))",
+ "CREATE TABLE farm (id CHAR(36) PRIMARY KEY NOT NULL UNIQUE CHECK(length(id) = 36), created_at DATETIME NOT NULL CHECK(length(created_at) = 24), updated_at DATETIME NOT NULL CHECK(length(updated_at) = 24), d_tag TEXT NOT NULL, pubkey TEXT NOT NULL, name TEXT NOT NULL, about TEXT, website TEXT, picture TEXT, banner TEXT, location_primary TEXT, location_city TEXT, location_region TEXT, location_country TEXT)",
+ "CREATE TABLE gcs_location (id CHAR(36) PRIMARY KEY NOT NULL UNIQUE CHECK(length(id) = 36), created_at DATETIME NOT NULL CHECK(length(created_at) = 24), updated_at DATETIME NOT NULL CHECK(length(updated_at) = 24), d_tag TEXT NOT NULL, lat REAL NOT NULL, lng REAL NOT NULL, geohash TEXT NOT NULL, point TEXT NOT NULL, polygon TEXT NOT NULL, accuracy REAL, altitude REAL, tag_0 TEXT, label TEXT, area REAL, elevation INTEGER, soil TEXT, climate TEXT, gc_id TEXT, gc_name TEXT, gc_admin1_id TEXT, gc_admin1_name TEXT, gc_country_id TEXT, gc_country_name TEXT)",
+ "CREATE TABLE plot (id CHAR(36) PRIMARY KEY NOT NULL UNIQUE CHECK(length(id) = 36), created_at DATETIME NOT NULL CHECK(length(created_at) = 24), updated_at DATETIME NOT NULL CHECK(length(updated_at) = 24), d_tag TEXT NOT NULL, farm_id CHAR(36) NOT NULL, name TEXT NOT NULL, about TEXT, location_primary TEXT, location_city TEXT, location_region TEXT, location_country TEXT, FOREIGN KEY (farm_id) REFERENCES farm(id) ON DELETE CASCADE)",
+ "CREATE TABLE nostr_event_state (id CHAR(36) PRIMARY KEY NOT NULL UNIQUE CHECK(length(id) = 36), created_at DATETIME NOT NULL CHECK(length(created_at) = 24), updated_at DATETIME NOT NULL CHECK(length(updated_at) = 24), key TEXT NOT NULL UNIQUE, kind INTEGER NOT NULL, pubkey CHAR(64) NOT NULL CHECK(length(pubkey) = 64), d_tag TEXT NOT NULL, last_event_id CHAR(64) NOT NULL CHECK(length(last_event_id) = 64), last_created_at INTEGER NOT NULL, content_hash TEXT NOT NULL)",
+ ];
+
+ for sql in schema {
+ exec.exec(sql, "[]")
+ .expect("schema statement should succeed");
+ }
+
+ for name in [
+ "0000_init",
+ "0001_log_error",
+ "0002_farm",
+ "0003_gcs_location",
+ "0004_trade_product",
+ "0005_nostr_profile",
+ "0006_nostr_relay",
+ "0007_media_image",
+ "0008_farm_gcs_location",
+ "0009_nostr_profile_relay",
+ "0010_trade_product_location",
+ "0011_trade_product_media",
+ "0012_plot",
+ "0013_plot_gcs_location",
+ "0014_farm_tag",
+ "0015_plot_tag",
+ "0016_farm_member",
+ "0017_farm_member_claim",
+ "0018_nostr_event_state",
+ ] {
+ let sql = format!("INSERT INTO __migrations(name) VALUES ('{name}')");
+ exec.exec(&sql, "[]")
+ .expect("legacy migration marker should succeed");
+ }
+}
+
+#[test]
+fn run_all_up_repairs_missing_indexes_in_legacy_sqlite_dbs() {
+ let exec = SqliteExecutor::open_memory().expect("open sqlite memory");
+ create_legacy_schema_without_secondary_indexes(&exec);
+
+ let before = query_rows(
+ &exec,
+ "SELECT name FROM sqlite_master WHERE type = 'index' AND name IN ('farm_pubkey_d_tag_idx', 'gcs_location_geohash_idx', 'plot_farm_d_tag_idx', 'nostr_event_state_kind_idx') ORDER BY name",
+ );
+ assert!(before.is_empty());
+
+ migrations::run_all_up(&exec).expect("repair migration should succeed");
+
+ let after = query_rows(
+ &exec,
+ "SELECT name FROM sqlite_master WHERE type = 'index' AND name IN ('farm_pubkey_d_tag_idx', 'gcs_location_geohash_idx', 'plot_farm_d_tag_idx', 'nostr_event_state_kind_idx') ORDER BY name",
+ );
+ let names = after
+ .iter()
+ .map(|row| {
+ row.get("name")
+ .and_then(Value::as_str)
+ .expect("index name should exist")
+ .to_string()
+ })
+ .collect::<Vec<_>>();
+ assert_eq!(
+ names,
+ vec![
+ "farm_pubkey_d_tag_idx".to_string(),
+ "gcs_location_geohash_idx".to_string(),
+ "nostr_event_state_kind_idx".to_string(),
+ "plot_farm_d_tag_idx".to_string(),
+ ]
+ );
+}
diff --git a/crates/sql-core/tests/coverage.rs b/crates/sql-core/tests/coverage.rs
@@ -1,3 +1,5 @@
+#[cfg(feature = "native")]
+use radroots_sql_core::SqliteExecutor;
use radroots_sql_core::error::SqlError;
use radroots_sql_core::migrations::{Migration, migrations_run_all_down, migrations_run_all_up};
use radroots_sql_core::utils::{
@@ -6,8 +8,6 @@ use radroots_sql_core::utils::{
to_partial_object_map, uuidv4, with_transaction,
};
use radroots_sql_core::{ExecOutcome, SqlExecutor};
-#[cfg(feature = "native")]
-use radroots_sql_core::SqliteExecutor;
use serde::ser::{SerializeMap, SerializeSeq};
use serde::{Deserialize, Serialize, Serializer};
use serde_json::{Map, Value, json};
diff --git a/crates/sql-wasm-core/src/embedded.rs b/crates/sql-wasm-core/src/embedded.rs
@@ -4,7 +4,7 @@ use std::sync::Mutex;
use radroots_sql_core::sqlite_util;
use radroots_sql_core::{ExecOutcome, SqlError, SqlExecutor};
-use rusqlite::{Connection, DatabaseName, params_from_iter};
+use rusqlite::{Connection, MAIN_DB, params_from_iter};
use serde_json::Value;
const SAVEPOINT_BEGIN: &str = "savepoint radroots_schema_tx";
@@ -84,7 +84,7 @@ fn serialize_main(conn: &Connection) -> Result<Vec<u8>, rusqlite::Error> {
if failpoints::take(failpoints::Point::ExportSerialize) {
return Err(forced_error());
}
- conn.serialize(DatabaseName::Main).map(|data| data.to_vec())
+ conn.serialize(MAIN_DB).map(|data| data.to_vec())
}
fn map_row(row: &rusqlite::Row<'_>) -> rusqlite::Result<Value> {
@@ -132,6 +132,16 @@ impl EmbeddedSqlEngine {
pub fn exec(&self, sql: &str, params_json: &str) -> Result<ExecOutcome, SqlError> {
let binds = sqlite_util::parse_params(params_json)?;
let conn = self.conn.lock().map_err(|_| SqlError::Internal)?;
+ if binds.is_empty() {
+ let total_changes_before = conn.total_changes();
+ conn.execute_batch(sql).map_err(map_rusqlite)?;
+ let total_changes_after = conn.total_changes();
+ let last_insert_id = conn.last_insert_rowid();
+ return Ok(ExecOutcome {
+ changes: (total_changes_after - total_changes_before) as i64,
+ last_insert_id,
+ });
+ }
let changes = conn
.execute(sql, params_from_iter(binds.into_iter()))
.map_err(map_rusqlite)?;
@@ -217,7 +227,7 @@ pub fn coverage_branch_probe(input: bool) -> &'static str {
#[cfg(all(test, feature = "embedded"))]
mod tests {
use super::{EmbeddedSqlEngine, coverage_branch_probe, failpoints};
- use radroots_sql_core::{SqlError, SqlExecutor};
+ use radroots_sql_core::SqlExecutor;
const CREATE_TABLE_SQL: &str = "CREATE TABLE test_items (id INTEGER PRIMARY KEY, name TEXT)";
@@ -271,6 +281,33 @@ mod tests {
}
#[test]
+ fn exec_runs_multi_statement_batches_without_params() {
+ let engine = EmbeddedSqlEngine::new().unwrap();
+
+ let outcome = engine
+ .exec(
+ "CREATE TABLE demo (id INTEGER PRIMARY KEY, name TEXT NOT NULL);\
+\nCREATE UNIQUE INDEX demo_name_idx ON demo(name);",
+ "[]",
+ )
+ .unwrap();
+ assert_eq!(outcome.changes, 0);
+
+ let insert = engine
+ .exec("INSERT INTO demo (name) VALUES ('alpha')", "[]")
+ .unwrap();
+ assert_eq!(insert.changes, 1);
+
+ let rows = engine
+ .query_rows(
+ "SELECT name FROM sqlite_master WHERE type = 'index' AND name = 'demo_name_idx'",
+ "[]",
+ )
+ .unwrap();
+ assert_eq!(rows.len(), 1);
+ }
+
+ #[test]
fn export_bytes_non_empty() {
let engine = EmbeddedSqlEngine::new().unwrap();
engine.exec(CREATE_TABLE_SQL, "[]").unwrap();
