From 7750d2cedc5403cd1d756d1d30a8e08fccd12f74 Mon Sep 17 00:00:00 2001
From: Neels Hofmeyr <neels@hofmeyr.de>
Date: Tue, 24 Oct 2017 23:26:27 +0200
Subject: automatically create db tables on osmo-hlr invocation

If a database file is missing, osmo-hlr creates it, as is the default sqlite3
API behavior -- before this patch, that db file is created, but lacks useful
tables. Actually also create initial tables in it, as osmo-nitb did.

In effect, the 'vty-test' target in tests/Makefile.am no longer needs to create
a database manually. (The 'ctrl-test' still does, because it also wants to add
subscriber data on top of the bare tables.)

Note: it could be desirable to bail if the desired database file does not
exist. That is however a different semantic from this patch; this is not
changing the fact that a db file is created, this just creates a usable one.

Note: I am about to add osmo-hlr-db-tool to do database migration from
osmo-nitb. For that, it is desirable to bootstrap a usable database, which is
the core reason for this patch.

Don't plainly duplicate hlr.sql to .c, but create db_bootstrap.h as a
BUILT_SOURCE from reading in sql/hlr.sql and mangling via sed to a list of SQL
statement strings. On each db_open(), run this bootstrap sequence.

In sql/hlr.sql, these tweaks are necessary:
* Add 'IF NOT EXISTS' to 'CREATE TABLE', so that the bootstrap sequence can be
  run on an already bootstrapped db.
* Drop the final comment at the bottom, which ended up being an empty SQL
  statement and causing sqlite3 API errors, seemed to have no purpose anyway.

Note: by composing the statement strings as multiline and including the SQL
comments, sqlite3 actually retains the comments contained in table definitions
and prints them back during 'sqlite3 hlr.db .dump'.

Change-Id: If77dbbfe1af3e66aaec91cb6295b687f37678636
---
 src/db.c | 32 ++++++++++++++++++++++++++++++++
 1 file changed, 32 insertions(+)

(limited to 'src/db.c')

diff --git a/src/db.c b/src/db.c
index fbf5c76..8733cf5 100644
--- a/src/db.c
+++ b/src/db.c
@@ -25,6 +25,7 @@
 
 #include "logging.h"
 #include "db.h"
+#include "db_bootstrap.h"
 
 #define SEL_COLUMNS \
 	"id," \
@@ -179,6 +180,35 @@ void db_close(struct db_context *dbc)
 	talloc_free(dbc);
 }
 
+static int db_bootstrap(struct db_context *dbc)
+{
+	int i;
+	for (i = 0; i < ARRAY_SIZE(stmt_bootstrap_sql); i++) {
+		int rc;
+		sqlite3_stmt *stmt;
+
+		rc = sqlite3_prepare_v2(dbc->db, stmt_bootstrap_sql[i], -1,
+					&stmt, NULL);
+		if (rc != SQLITE_OK) {
+			LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n",
+			     stmt_bootstrap_sql[i]);
+			return -1;
+		}
+
+		/* execute the statement */
+		rc = sqlite3_step(stmt);
+		db_remove_reset(stmt);
+		if (rc != SQLITE_DONE) {
+			LOGP(DDB, LOGL_ERROR, "Cannot bootstrap database: SQL error: (%d) %s,"
+			     " during stmt '%s'",
+			     rc, sqlite3_errmsg(dbc->db),
+			     stmt_bootstrap_sql[i]);
+			return -1;
+		}
+	}
+	return 0;
+}
+
 struct db_context *db_open(void *ctx, const char *fname)
 {
 	struct db_context *dbc = talloc_zero(ctx, struct db_context);
@@ -231,6 +261,8 @@ struct db_context *db_open(void *ctx, const char *fname)
 		LOGP(DDB, LOGL_ERROR, "Unable to set Write-Ahead Logging: %s\n",
 			err_msg);
 
+	db_bootstrap(dbc);
+
 	/* prepare all SQL statements */
 	for (i = 0; i < ARRAY_SIZE(dbc->stmt); i++) {
 		rc = sqlite3_prepare_v2(dbc->db, stmt_sql[i], -1,
-- 
cgit v1.2.3