diff options
Diffstat (limited to 'src/db.c')
| -rw-r--r-- | src/db.c | 81 |
1 files changed, 81 insertions, 0 deletions
@@ -271,6 +271,39 @@ static const char SCHEMA_V1[] = " key TEXT NOT NULL," " value TEXT NOT NULL," " PRIMARY KEY (org_id, key)" + ") STRICT;\n" + + "CREATE TABLE bank_transactions (" + " org_id INTEGER NOT NULL REFERENCES orgs(id)," + " id INTEGER PRIMARY KEY," + " account TEXT NOT NULL," + " booked_at TEXT NOT NULL," + " value_date TEXT NOT NULL," + " text TEXT NOT NULL," + " type TEXT NOT NULL," + " amount_ore INTEGER NOT NULL," + " balance_ore INTEGER," + " source TEXT NOT NULL," + " source_hash BLOB NOT NULL CHECK (length(source_hash) = 32)," + " imported_at TEXT NOT NULL," + " imported_by INTEGER NOT NULL REFERENCES users(id)," + " UNIQUE (org_id, id)," + " UNIQUE (org_id, source_hash)" + ") STRICT;\n" + "CREATE INDEX idx_bank_tx_date ON bank_transactions(org_id, booked_at);\n" + + "CREATE TABLE bank_matches (" + " org_id INTEGER NOT NULL," + " transaction_id INTEGER NOT NULL," + " voucher_id INTEGER NOT NULL," + " matched_at TEXT NOT NULL," + " matched_by INTEGER NOT NULL REFERENCES users(id)," + " kind TEXT NOT NULL DEFAULT 'manual'" + " CHECK (kind IN ('manual','auto'))," + " PRIMARY KEY (org_id, transaction_id, voucher_id)," + " FOREIGN KEY (org_id, transaction_id)" + " REFERENCES bank_transactions(org_id, id)," + " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)" ") STRICT;\n"; static const char SCHEMA_V2[] = @@ -458,6 +491,50 @@ static int db_upgrade_v7(sqlite3 *db, char **err) err); } +/* v8: bank reconciliation — imported statement evidence and the mutable + transaction/voucher links (phase 1 never books anything). */ +static int db_upgrade_v8(sqlite3 *db, char **err) +{ + if (db_exec(db, + "CREATE TABLE IF NOT EXISTS bank_transactions (" + " org_id INTEGER NOT NULL REFERENCES orgs(id)," + " id INTEGER PRIMARY KEY," + " account TEXT NOT NULL," + " booked_at TEXT NOT NULL," + " value_date TEXT NOT NULL," + " text TEXT NOT NULL," + " type TEXT NOT NULL," + " amount_ore INTEGER NOT NULL," + " balance_ore INTEGER," + " source TEXT NOT NULL," + " source_hash BLOB NOT NULL CHECK (length(source_hash) = 32)," + " imported_at TEXT NOT NULL," + " imported_by INTEGER NOT NULL REFERENCES users(id)," + " UNIQUE (org_id, id)," + " UNIQUE (org_id, source_hash)" + ") STRICT;" + "CREATE INDEX IF NOT EXISTS idx_bank_tx_date" + " ON bank_transactions(org_id, booked_at);", + err) != 0) + return -1; + return db_exec(db, + "CREATE TABLE IF NOT EXISTS bank_matches (" + " org_id INTEGER NOT NULL," + " transaction_id INTEGER NOT NULL," + " voucher_id INTEGER NOT NULL," + " matched_at TEXT NOT NULL," + " matched_by INTEGER NOT NULL REFERENCES users(id)," + " kind TEXT NOT NULL DEFAULT 'manual'" + " CHECK (kind IN ('manual','auto'))," + " PRIMARY KEY (org_id, transaction_id, voucher_id)," + " FOREIGN KEY (org_id, transaction_id)" + " REFERENCES bank_transactions(org_id, id)," + " FOREIGN KEY (org_id, voucher_id)" + " REFERENCES vouchers(org_id, id)" + ") STRICT", + err); +} + static int db_upgrade(sqlite3 *db, int from, char **err) { if (db_exec(db, "BEGIN IMMEDIATE", err) != 0) @@ -486,6 +563,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err) db_exec(db, "ROLLBACK", NULL); return -1; } + if (from < 8 && db_upgrade_v8(db, err) != 0) { + db_exec(db, "ROLLBACK", NULL); + return -1; + } char *sql = sqlite3_mprintf( "UPDATE meta SET value='%d' WHERE key='schema_version'", BOKF_SCHEMA_VERSION); |