summaryrefslogtreecommitdiff
path: root/src/db.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/db.c')
-rw-r--r--src/db.c247
1 files changed, 246 insertions, 1 deletions
diff --git a/src/db.c b/src/db.c
index 7e23cb0..57e1efd 100644
--- a/src/db.c
+++ b/src/db.c
@@ -144,7 +144,8 @@ static const char SCHEMA_V1[] =
" date TEXT NOT NULL CHECK (date LIKE '____-__-__'),"
" description TEXT NOT NULL CHECK (length(description) > 0),"
" source TEXT NOT NULL DEFAULT 'manual'"
- " CHECK (source IN ('manual','agent','sie_import','system','ib')),"
+ " CHECK (source IN ('manual','agent','sie_import','system','ib',"
+ " 'invoice')),"
" client_ref TEXT,"
" corrects_voucher_id INTEGER,"
" created_at TEXT NOT NULL,"
@@ -304,6 +305,81 @@ static const char SCHEMA_V1[] =
" 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"
+
+ "CREATE TABLE customers ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " name TEXT NOT NULL,"
+ " address TEXT NOT NULL DEFAULT '',"
+ " postal_code TEXT NOT NULL DEFAULT '',"
+ " city TEXT NOT NULL DEFAULT '',"
+ " country TEXT NOT NULL DEFAULT 'SE',"
+ " vat_nr TEXT NOT NULL DEFAULT '',"
+ " email TEXT NOT NULL DEFAULT '',"
+ " your_ref TEXT NOT NULL DEFAULT '',"
+ " payment_days INTEGER NOT NULL DEFAULT 30 CHECK (payment_days >= 0),"
+ " notes TEXT NOT NULL DEFAULT '',"
+ " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),"
+ " created_at TEXT NOT NULL,"
+ " updated_at TEXT,"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, name)"
+ ") STRICT;\n"
+
+ "CREATE TABLE invoice_sequence ("
+ " org_id INTEGER NOT NULL PRIMARY KEY REFERENCES orgs(id),"
+ " next_number INTEGER NOT NULL CHECK (next_number > 0)"
+ ") STRICT;\n"
+
+ "CREATE TABLE invoices ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " customer_id INTEGER NOT NULL,"
+ " number INTEGER NOT NULL CHECK (number > 0),"
+ " ocr TEXT NOT NULL,"
+ " invoice_date TEXT NOT NULL,"
+ " due_date TEXT NOT NULL,"
+ " delivery_date TEXT NOT NULL DEFAULT '',"
+ " your_ref TEXT NOT NULL DEFAULT '',"
+ " our_ref TEXT NOT NULL DEFAULT '',"
+ " notes TEXT NOT NULL DEFAULT '',"
+ " net_ore INTEGER NOT NULL,"
+ " vat_ore INTEGER NOT NULL,"
+ " total_ore INTEGER NOT NULL,"
+ " status TEXT NOT NULL DEFAULT 'issued'"
+ " CHECK (status IN ('issued','credited')),"
+ " document_id INTEGER,"
+ " voucher_id INTEGER,"
+ " last_sent_at TEXT,"
+ " last_sent_to TEXT,"
+ " created_at TEXT NOT NULL,"
+ " created_by INTEGER NOT NULL REFERENCES users(id),"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, number),"
+ " FOREIGN KEY (org_id, customer_id) REFERENCES customers(org_id, id),"
+ " FOREIGN KEY (org_id, document_id) REFERENCES attachments(org_id, id),"
+ " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)"
+ ") STRICT;\n"
+
+ "CREATE TABLE invoice_rows ("
+ " org_id INTEGER NOT NULL,"
+ " id INTEGER PRIMARY KEY,"
+ " invoice_id INTEGER NOT NULL,"
+ " line_no INTEGER NOT NULL,"
+ " article_no TEXT NOT NULL DEFAULT '',"
+ " description TEXT NOT NULL,"
+ " quantity_milli INTEGER NOT NULL CHECK (quantity_milli > 0),"
+ " unit TEXT NOT NULL DEFAULT 'st',"
+ " unit_price_ore INTEGER NOT NULL,"
+ " amount_ore INTEGER NOT NULL,"
+ " note TEXT NOT NULL DEFAULT '',"
+ " vat_code TEXT NOT NULL DEFAULT '25'"
+ " CHECK (vat_code IN ('25','12','6','0','rc','eu')),"
+ " account TEXT NOT NULL DEFAULT '',"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, invoice_id, line_no),"
+ " FOREIGN KEY (org_id, invoice_id) REFERENCES invoices(org_id, id)"
") STRICT;\n";
static const char SCHEMA_V2[] =
@@ -535,6 +611,137 @@ static int db_upgrade_v8(sqlite3 *db, char **err)
err);
}
+/* v9: invoicing — customers, the global invoice number series, invoices and
+ their rows, plus a widened voucher source. A CHECK cannot be altered in
+ place, so the vouchers table is rebuilt; db_open turns foreign keys off
+ for the migration and runs PRAGMA foreign_key_check afterwards. */
+static int db_upgrade_v9(sqlite3 *db, char **err)
+{
+ if (db_exec(db,
+ "CREATE TABLE IF NOT EXISTS customers ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " name TEXT NOT NULL,"
+ " address TEXT NOT NULL DEFAULT '',"
+ " postal_code TEXT NOT NULL DEFAULT '',"
+ " city TEXT NOT NULL DEFAULT '',"
+ " country TEXT NOT NULL DEFAULT 'SE',"
+ " vat_nr TEXT NOT NULL DEFAULT '',"
+ " email TEXT NOT NULL DEFAULT '',"
+ " your_ref TEXT NOT NULL DEFAULT '',"
+ " payment_days INTEGER NOT NULL DEFAULT 30"
+ " CHECK (payment_days >= 0),"
+ " notes TEXT NOT NULL DEFAULT '',"
+ " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),"
+ " created_at TEXT NOT NULL,"
+ " updated_at TEXT,"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, name)"
+ ") STRICT;"
+ "CREATE TABLE IF NOT EXISTS invoice_sequence ("
+ " org_id INTEGER NOT NULL PRIMARY KEY REFERENCES orgs(id),"
+ " next_number INTEGER NOT NULL CHECK (next_number > 0)"
+ ") STRICT;"
+ "CREATE TABLE IF NOT EXISTS invoices ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " customer_id INTEGER NOT NULL,"
+ " number INTEGER NOT NULL CHECK (number > 0),"
+ " ocr TEXT NOT NULL,"
+ " invoice_date TEXT NOT NULL,"
+ " due_date TEXT NOT NULL,"
+ " delivery_date TEXT NOT NULL DEFAULT '',"
+ " your_ref TEXT NOT NULL DEFAULT '',"
+ " our_ref TEXT NOT NULL DEFAULT '',"
+ " notes TEXT NOT NULL DEFAULT '',"
+ " net_ore INTEGER NOT NULL,"
+ " vat_ore INTEGER NOT NULL,"
+ " total_ore INTEGER NOT NULL,"
+ " status TEXT NOT NULL DEFAULT 'issued'"
+ " CHECK (status IN ('issued','credited')),"
+ " document_id INTEGER,"
+ " voucher_id INTEGER,"
+ " last_sent_at TEXT,"
+ " last_sent_to TEXT,"
+ " created_at TEXT NOT NULL,"
+ " created_by INTEGER NOT NULL REFERENCES users(id),"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, number),"
+ " FOREIGN KEY (org_id, customer_id)"
+ " REFERENCES customers(org_id, id),"
+ " FOREIGN KEY (org_id, document_id)"
+ " REFERENCES attachments(org_id, id),"
+ " FOREIGN KEY (org_id, voucher_id)"
+ " REFERENCES vouchers(org_id, id)"
+ ") STRICT;"
+ "CREATE TABLE IF NOT EXISTS invoice_rows ("
+ " org_id INTEGER NOT NULL,"
+ " id INTEGER PRIMARY KEY,"
+ " invoice_id INTEGER NOT NULL,"
+ " line_no INTEGER NOT NULL,"
+ " article_no TEXT NOT NULL DEFAULT '',"
+ " description TEXT NOT NULL,"
+ " quantity_milli INTEGER NOT NULL CHECK (quantity_milli > 0),"
+ " unit TEXT NOT NULL DEFAULT 'st',"
+ " unit_price_ore INTEGER NOT NULL,"
+ " amount_ore INTEGER NOT NULL,"
+ " note TEXT NOT NULL DEFAULT '',"
+ " vat_code TEXT NOT NULL DEFAULT '25'"
+ " CHECK (vat_code IN ('25','12','6','0','rc','eu')),"
+ " account TEXT NOT NULL DEFAULT '',"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, invoice_id, line_no),"
+ " FOREIGN KEY (org_id, invoice_id)"
+ " REFERENCES invoices(org_id, id)"
+ ") STRICT",
+ err) != 0)
+ return -1;
+ return db_exec(
+ db,
+ "CREATE TABLE vouchers_new ("
+ " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+ " id INTEGER PRIMARY KEY,"
+ " fiscal_year_id INTEGER NOT NULL,"
+ " series TEXT NOT NULL,"
+ " number INTEGER NOT NULL CHECK (number > 0),"
+ " date TEXT NOT NULL CHECK (date LIKE '____-__-__'),"
+ " description TEXT NOT NULL CHECK (length(description) > 0),"
+ " source TEXT NOT NULL DEFAULT 'manual'"
+ " CHECK (source IN ('manual','agent','sie_import','system','ib',"
+ " 'invoice')),"
+ " client_ref TEXT,"
+ " corrects_voucher_id INTEGER,"
+ " created_at TEXT NOT NULL,"
+ " created_by_user INTEGER NOT NULL REFERENCES users(id),"
+ " created_by_token INTEGER REFERENCES api_tokens(id),"
+ " hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32),"
+ " hash BLOB NOT NULL CHECK (length(hash) = 32),"
+ " UNIQUE (org_id, id),"
+ " UNIQUE (org_id, fiscal_year_id, series, number),"
+ " UNIQUE (org_id, client_ref),"
+ " FOREIGN KEY (org_id, fiscal_year_id)"
+ " REFERENCES fiscal_years(org_id, id),"
+ " FOREIGN KEY (org_id, corrects_voucher_id)"
+ " REFERENCES vouchers(org_id, id)"
+ ") STRICT;"
+ "INSERT INTO vouchers_new(org_id,id,fiscal_year_id,series,number,date,"
+ " description,source,client_ref,corrects_voucher_id,created_at,"
+ " created_by_user,created_by_token,hash_prev,hash)"
+ " SELECT org_id,id,fiscal_year_id,series,number,date,description,source,"
+ " client_ref,corrects_voucher_id,created_at,created_by_user,"
+ " created_by_token,hash_prev,hash FROM vouchers;"
+ "DROP TABLE vouchers;"
+ "ALTER TABLE vouchers_new RENAME TO vouchers;"
+ "CREATE INDEX idx_vouchers_date ON vouchers(org_id, date);"
+ "CREATE INDEX idx_vouchers_fy"
+ " ON vouchers(org_id, fiscal_year_id, series, number);"
+ "CREATE TRIGGER vouchers_no_update BEFORE UPDATE ON vouchers"
+ " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;"
+ "CREATE TRIGGER vouchers_no_delete BEFORE DELETE ON vouchers"
+ " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;",
+ err);
+}
+
static int db_upgrade(sqlite3 *db, int from, char **err)
{
if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
@@ -567,6 +774,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
db_exec(db, "ROLLBACK", NULL);
return -1;
}
+ if (from < 9 && db_upgrade_v9(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);
@@ -651,6 +862,26 @@ static int pre_migration_snapshot(sqlite3 *db, int old_version, char **err)
return 0;
}
+static int db_foreign_key_check(sqlite3 *db, char **err)
+{
+ sqlite3_stmt *st = NULL;
+ if (sqlite3_prepare_v2(db, "PRAGMA foreign_key_check", -1, &st, NULL) !=
+ SQLITE_OK) {
+ set_err(err, "database error");
+ return -1;
+ }
+ if (sqlite3_step(st) == SQLITE_ROW) {
+ const char *table = (const char *)sqlite3_column_text(st, 0);
+ int64_t rowid = sqlite3_column_int64(st, 1);
+ set_err(err, "foreign key violation in %s row %lld after migration",
+ table ? table : "?", (long long)rowid);
+ sqlite3_finalize(st);
+ return -1;
+ }
+ sqlite3_finalize(st);
+ return 0;
+}
+
int db_open(const char *path, sqlite3 **out, char **err)
{
sqlite3 *db = NULL;
@@ -694,10 +925,24 @@ int db_open(const char *path, sqlite3 **out, char **err)
sqlite3_close(db);
return -1;
}
+ /* v9 rebuilds the vouchers table; foreign keys are re-checked
+ right after the migration and re-enabled for normal use */
+ if (db_exec(db, "PRAGMA foreign_keys=OFF", err) != 0) {
+ sqlite3_close(db);
+ return -1;
+ }
if (db_upgrade(db, version, err) != 0) {
sqlite3_close(db);
return -1;
}
+ if (db_foreign_key_check(db, err) != 0) {
+ sqlite3_close(db);
+ return -1;
+ }
+ if (db_exec(db, "PRAGMA foreign_keys=ON", err) != 0) {
+ sqlite3_close(db);
+ return -1;
+ }
}
} else {
sqlite3_finalize(st);
generated by cgit v1.3 (git 2.53.0) at 2026-09-21 22:02:29 +0000