invoices: text rows, duplicate and payment link (schema v12); smtp address checksv0.1.62

1 files changed, 53 insertions, 1 deletions

diff --git a/src/db.c b/src/db.c
index 4618a73..200fff8 100644
--- a/src/db.c
+++ b/src/db.c
@@ -351,6 +351,8 @@ static const char SCHEMA_V1[] =
     "                  CHECK (status IN ('issued','credited')),"
     "  document_id   INTEGER,"
     "  voucher_id    INTEGER,"
+    "  paid_date     TEXT NOT NULL DEFAULT '',"
+    "  payment_voucher_id INTEGER,"
     "  last_sent_at  TEXT,"
     "  last_sent_to  TEXT,"
     "  created_at    TEXT NOT NULL,"
@@ -359,7 +361,9 @@ static const char SCHEMA_V1[] =
     "  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)"
+    "  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),"
+    "  FOREIGN KEY (org_id, payment_voucher_id)"
+    "    REFERENCES vouchers(org_id, id)"
     ") STRICT;\n"
 
     "CREATE TABLE invoice_rows ("
@@ -377,6 +381,7 @@ static const char SCHEMA_V1[] =
     "  vat_code       TEXT NOT NULL DEFAULT '25'"
     "                   CHECK (vat_code IN ('25','12','6','0','rc','eu')),"
     "  account        TEXT NOT NULL DEFAULT '',"
+    "  is_text        INTEGER NOT NULL DEFAULT 0,"
     "  UNIQUE (org_id, id),"
     "  UNIQUE (org_id, invoice_id, line_no),"
     "  FOREIGN KEY (org_id, invoice_id) REFERENCES invoices(org_id, id)"
@@ -911,6 +916,49 @@ static int db_upgrade_v11(sqlite3 *db, char **err)
                    err);
 }
 
+static int db_column_exists(sqlite3 *db, const char *table, const char *col,
+                            char **err)
+{
+    sqlite3_stmt *st = NULL;
+    char sql[160];
+    snprintf(sql, sizeof sql,
+             "SELECT count(*) FROM pragma_table_info('%s') WHERE name='%s'",
+             table, col);
+    if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) != SQLITE_OK) {
+        set_err(err, "database error");
+        return -1;
+    }
+    int have = sqlite3_step(st) == SQLITE_ROW && sqlite3_column_int(st, 0) > 0;
+    sqlite3_finalize(st);
+    return have;
+}
+
+static int db_add_column(sqlite3 *db, const char *table, const char *col,
+                         const char *decl, char **err)
+{
+    int have = db_column_exists(db, table, col, err);
+    if (have != 0)
+        return have < 0 ? -1 : 0;
+    char sql[384];
+    snprintf(sql, sizeof sql, "ALTER TABLE %s ADD COLUMN %s %s", table, col,
+             decl);
+    return db_exec(db, sql, err);
+}
+
+/* v12: invoice text rows (is_text) and the payment link (paid_date,
+   payment_voucher_id). Fresh databases already carry the columns. */
+static int db_upgrade_v12(sqlite3 *db, char **err)
+{
+    if (db_add_column(db, "invoice_rows", "is_text",
+                      "INTEGER NOT NULL DEFAULT 0", err) != 0)
+        return -1;
+    if (db_add_column(db, "invoices", "paid_date",
+                      "TEXT NOT NULL DEFAULT ''", err) != 0)
+        return -1;
+    return db_add_column(db, "invoices", "payment_voucher_id", "INTEGER",
+                         err);
+}
+
 static int db_upgrade(sqlite3 *db, int from, char **err)
 {
     if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
@@ -955,6 +1003,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
         db_exec(db, "ROLLBACK", NULL);
         return -1;
     }
+    if (from < 12 && db_upgrade_v12(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);