From b5349b52e757399d8ddb6325120a58e7d9e6e1f6 Mon Sep 17 00:00:00 2001 From: Anders Betts Date: Sun, 20 Sep 2026 09:51:28 +0200 Subject: db: snapshot the database before forward migrations When db_open finds an older schema version, write a consistent VACUUM INTO copy to /pre-migration-v-.db before the first migration statement. A taken name gets a numeric suffix; if the snapshot fails, the open and the migration abort. --- docs/SCHEMA.md | 7 +- src/db.c | 76 ++++++++++++++++++++ tests/test_core.c | 212 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 293 insertions(+), 2 deletions(-) diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md index 2100990..89dd0b1 100644 --- a/docs/SCHEMA.md +++ b/docs/SCHEMA.md @@ -484,8 +484,11 @@ another voucher is posted in between) — clients must not persist it. and `created_at`. Current version: **3** (v3 replaces the seeded moms rules with the corrected mapping; v2 adds the two template tables). - Migrations are forward-only, applied automatically at daemon start, each in - one transaction, and require an automatic `VACUUM INTO` snapshot next to the - database before starting (`bokfd.db.pre-migration-`). + one transaction. Before the first migration statement a consistent + `VACUUM INTO` snapshot is written to + `/pre-migration-v-.db` (a numeric suffix is + added when the name is taken); if the snapshot cannot be taken the upgrade + is aborted and the database is left at its old version. - `audit.verify` must pass before and after any migration; migrations never rewrite ledger rows. diff --git a/src/db.c b/src/db.c index d3bd15f..627efbc 100644 --- a/src/db.c +++ b/src/db.c @@ -1,9 +1,13 @@ #include "db.h" +#include #include #include #include #include +#include +#include +#include #include "auth.h" #include "config.h" @@ -469,6 +473,74 @@ static int db_upgrade(sqlite3 *db, int from, char **err) return 0; } +static int mkdir_p(const char *path, mode_t mode) +{ + char tmp[4096]; + if (!path || strlen(path) >= sizeof tmp) + return -1; + strcpy(tmp, path); + for (char *p = tmp + 1; *p; p++) { + if (*p == '/') { + *p = '\0'; + if (mkdir(tmp, mode) != 0 && errno != EEXIST) + return -1; + *p = '/'; + } + } + if (mkdir(tmp, mode) != 0 && errno != EEXIST) + return -1; + return 0; +} + +static int pre_migration_snapshot(sqlite3 *db, int old_version, char **err) +{ + if (mkdir_p(g_cfg.backup_dir, 0700) != 0) { + set_err(err, "pre-migration snapshot: cannot create backup directory %s", + g_cfg.backup_dir); + return -1; + } + char stamp[32]; + time_t t = (time_t)util_now(); + struct tm tm; + gmtime_r(&t, &tm); + strftime(stamp, sizeof stamp, "%Y%m%dT%H%M%SZ", &tm); + char path[4096]; + for (int n = 0;; n++) { + int len = n == 0 + ? snprintf(path, sizeof path, + "%s/pre-migration-v%d-%s.db", + g_cfg.backup_dir, old_version, stamp) + : snprintf(path, sizeof path, + "%s/pre-migration-v%d-%s-%d.db", + g_cfg.backup_dir, old_version, stamp, n); + if (len < 0 || (size_t)len >= sizeof path) { + set_err(err, "pre-migration snapshot path is too long"); + return -1; + } + if (access(path, F_OK) != 0) + break; + if (n >= 1000) { + set_err(err, "cannot find a free pre-migration snapshot name in %s", + g_cfg.backup_dir); + return -1; + } + } + char *sql = sqlite3_mprintf("VACUUM INTO %Q", path); + if (!sql) { + set_err(err, "out of memory"); + return -1; + } + int rc = db_exec(db, sql, err); + sqlite3_free(sql); + if (rc != 0) { + set_err(err, "pre-migration snapshot to %s failed: %s", path, + err && *err ? *err : sqlite3_errmsg(db)); + return -1; + } + log_info("pre-migration snapshot: %s", path); + return 0; +} + int db_open(const char *path, sqlite3 **out, char **err) { sqlite3 *db = NULL; @@ -508,6 +580,10 @@ int db_open(const char *path, sqlite3 **out, char **err) return -1; } if (version < BOKF_SCHEMA_VERSION) { + if (pre_migration_snapshot(db, version, err) != 0) { + sqlite3_close(db); + return -1; + } if (db_upgrade(db, version, err) != 0) { sqlite3_close(db); return -1; diff --git a/tests/test_core.c b/tests/test_core.c index a8d8b54..ee0d3b9 100644 --- a/tests/test_core.c +++ b/tests/test_core.c @@ -1,4 +1,5 @@ #include +#include #include #include #include @@ -9,6 +10,7 @@ #include #include #include +#include #include #include "client.h" @@ -321,6 +323,214 @@ static void test_transport(void) } } +static int count_snapshots(const char *dir) +{ + DIR *d = opendir(dir); + if (!d) + return -1; + int n = 0; + struct dirent *e; + while ((e = readdir(d)) != NULL) { + if (strncmp(e->d_name, "pre-migration-v3-", 17) == 0) + n++; + } + closedir(d); + return n; +} + +static char *db_text(sqlite3 *db, const char *sql) +{ + sqlite3_stmt *st = NULL; + char *value = NULL; + if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) == SQLITE_OK && + sqlite3_step(st) == SQLITE_ROW) { + const unsigned char *v = sqlite3_column_text(st, 0); + if (v) + value = xstrdup((const char *)v); + } + sqlite3_finalize(st); + return value; +} + +static int find_snapshot(const char *dir, char *out, size_t n) +{ + DIR *d = opendir(dir); + if (!d) + return -1; + int found = -1; + struct dirent *e; + while ((e = readdir(d)) != NULL) { + if (strncmp(e->d_name, "pre-migration-v3-", 17) != 0) + continue; + char p[1500]; + snprintf(p, sizeof p, "%s/%s", dir, e->d_name); + sqlite3 *sdb = NULL; + if (sqlite3_open_v2(p, &sdb, SQLITE_OPEN_READONLY, NULL) != SQLITE_OK) { + sqlite3_close(sdb); + continue; + } + char *v = db_text(sdb, + "SELECT value FROM meta WHERE key='schema_version'"); + if (v && strcmp(v, "3") == 0) { + snprintf(out, n, "%s", p); + found = 0; + } + free(v); + sqlite3_close(sdb); + if (found == 0) + break; + } + closedir(d); + return found; +} + +static void test_pre_migration_snapshot(const char *tmpdir, + const char *backupdir) +{ + char dbpath[600]; + snprintf(dbpath, sizeof dbpath, "%s/migrate.db", tmpdir); + char *err = NULL; + sqlite3 *db = NULL; + + CHECK(db_open(dbpath, &db, &err) == 0); + free(err); + err = NULL; + CHECK(count_snapshots(backupdir) == 0); + if (!db) + return; + + const char *downgrade[] = { + "ALTER TABLE fiscal_years DROP COLUMN dividend_ore", + "ALTER TABLE fiscal_years DROP COLUMN events", + "ALTER TABLE fiscal_years DROP COLUMN agm_date", + "ALTER TABLE fiscal_years DROP COLUMN dividend_date", + "ALTER TABLE fiscal_years DROP COLUMN employees", + "ALTER TABLE fiscal_years DROP COLUMN notes", + "ALTER TABLE orgs DROP COLUMN description", + "ALTER TABLE orgs DROP COLUMN shares", + "DROP TABLE board_members", + "UPDATE meta SET value='3' WHERE key='schema_version'", + }; + for (size_t i = 0; i < sizeof downgrade / sizeof downgrade[0]; i++) + CHECK(db_exec(db, downgrade[i], &err) == 0); + free(err); + err = NULL; + sqlite3_close(db); + db = NULL; + + /* a failed snapshot must abort before the first migration statement */ + char blocker[600], baddir[700]; + snprintf(blocker, sizeof blocker, "%s/blocker", tmpdir); + snprintf(baddir, sizeof baddir, "%s/backup", blocker); + FILE *f = fopen(blocker, "wb"); + if (f) + fclose(f); + CHECK(f != NULL); + free(g_cfg.backup_dir); + g_cfg.backup_dir = xstrdup(baddir); + CHECK(db_open(dbpath, &db, &err) != 0); + CHECK(db == NULL); + CHECK(err && strstr(err, "pre-migration snapshot") != NULL); + free(err); + err = NULL; + free(g_cfg.backup_dir); + g_cfg.backup_dir = xstrdup(backupdir); + + sqlite3 *raw = NULL; + CHECK(sqlite3_open_v2(dbpath, &raw, SQLITE_OPEN_READONLY, NULL) == + SQLITE_OK); + char *v = + db_text(raw, "SELECT value FROM meta WHERE key='schema_version'"); + CHECK(v && strcmp(v, "3") == 0); + free(v); + sqlite3_close(raw); + + /* a timestamp collision must not clobber the existing file */ + time_t t = time(NULL); + while (time(NULL) == t) + ; + t = (time_t)util_now(); + struct tm tm; + gmtime_r(&t, &tm); + char stamp[32]; + strftime(stamp, sizeof stamp, "%Y%m%dT%H%M%SZ", &tm); + char occupied[1400]; + snprintf(occupied, sizeof occupied, "%s/pre-migration-v3-%s.db", backupdir, + stamp); + f = fopen(occupied, "wb"); + if (f) { + fputs("occupied", f); + fclose(f); + } + CHECK(f != NULL); + + CHECK(db_open(dbpath, &db, &err) == 0); + if (!db) { + fprintf(stderr, "migration db_open: %s\n", err ? err : "?"); + free(err); + unlink(occupied); + unlink(dbpath); + return; + } + CHECK(count_snapshots(backupdir) == 2); + + time_t t2 = (time_t)util_now(); + gmtime_r(&t2, &tm); + char stamp2[32]; + strftime(stamp2, sizeof stamp2, "%Y%m%dT%H%M%SZ", &tm); + if (strcmp(stamp, stamp2) == 0) { + char expect[1500]; + snprintf(expect, sizeof expect, "%s/pre-migration-v3-%s-1.db", + backupdir, stamp); + CHECK(access(expect, F_OK) == 0); + } + + char buf[32] = ""; + f = fopen(occupied, "rb"); + if (f) { + size_t got = fread(buf, 1, sizeof buf - 1, f); + buf[got] = '\0'; + fclose(f); + } + CHECK(strcmp(buf, "occupied") == 0); + + /* the snapshot is a valid database holding the pre-migration state */ + char snap[1500] = ""; + CHECK(find_snapshot(backupdir, snap, sizeof snap) == 0); + sqlite3 *sdb = NULL; + CHECK(sqlite3_open_v2(snap, &sdb, SQLITE_OPEN_READONLY, NULL) == SQLITE_OK); + v = db_text(sdb, "SELECT value FROM meta WHERE key='schema_version'"); + CHECK(v && strcmp(v, "3") == 0); + free(v); + v = db_text(sdb, "PRAGMA quick_check"); + CHECK(v && strcmp(v, "ok") == 0); + free(v); + sqlite3_close(sdb); + + /* the reopened database is at the current version and usable */ + v = db_text(db, "SELECT value FROM meta WHERE key='schema_version'"); + CHECK(v && strcmp(v, "6") == 0); + free(v); + int64_t user_id = 0; + CHECK(db_create_user(db, "migrated", "Migrated", "secret123", 0, &user_id, + &err) == 0); + free(err); + err = NULL; + CHECK(user_id > 0); + sqlite3_close(db); + db = NULL; + + unlink(occupied); + if (snap[0]) + unlink(snap); + unlink(dbpath); + snprintf(dbpath, sizeof dbpath, "%s/migrate.db-wal", tmpdir); + unlink(dbpath); + snprintf(dbpath, sizeof dbpath, "%s/migrate.db-shm", tmpdir); + unlink(dbpath); + unlink(blocker); +} + int main(void) { char tmpdir[] = "/tmp/bokf-test-XXXXXX"; @@ -2102,6 +2312,8 @@ int main(void) test_transport(); + test_pre_migration_snapshot(tmpdir, backupdir); + /* rate limiting must stay last: it blocks the login key */ for (int i = 0; i < 5; i++) CHECK(!login("admin", "wrong")); -- cgit v1.3