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. Actually also create initial tables in it.

Note: it might 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, and possibly also CSV import. For that, it is also desirable to
bootstrap a usable database, which is the core reason for this patch.

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:
* Add 'IF NOT EXISTS' to 'CREATE TABLE', so that the bootstrap
  sequence can be run on an already bootstrapped db. This is the same as
  osmo-nitb does it.
* 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

5 files changed, 81 insertions, 6 deletions

diff --git a/sql/hlr.sql b/sql/hlr.sql
index 5fbc712..696cf1c 100644
--- a/sql/hlr.sql
+++ b/sql/hlr.sql
@@ -1,6 +1,6 @@
 --modelled roughly after TS 23.008 version 13.3.0
 
-CREATE TABLE subscriber (
+CREATE TABLE IF NOT EXISTS subscriber (
 	id		INTEGER PRIMARY KEY,
 	-- Chapter 2.1.1.1
 	imsi		VARCHAR(15) UNIQUE NOT NULL,
@@ -40,24 +40,24 @@ CREATE TABLE subscriber (
 	ms_purged_ps	BOOLEAN NOT NULL DEFAULT 0
 );
 
-CREATE TABLE subscriber_apn (
+CREATE TABLE IF NOT EXISTS subscriber_apn (
 	subscriber_id	INTEGER,		-- subscriber.id
 	apn		VARCHAR(256) NOT NULL
 );
 
 -- Chapter 2.1.3
-CREATE TABLE subscriber_multi_msisdn (
+CREATE TABLE IF NOT EXISTS subscriber_multi_msisdn (
 	subscriber_id	INTEGER,		-- subscriber.id
 	msisdn		VARCHAR(15) NOT NULL
 );
 
-CREATE TABLE auc_2g (
+CREATE TABLE IF NOT EXISTS auc_2g (
 	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
 	algo_id_2g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
 	ki		VARCHAR(32) NOT NULL	-- hex string: subscriber's secret key (128bit)
 );
 
-CREATE TABLE auc_3g (
+CREATE TABLE IF NOT EXISTS auc_3g (
 	subscriber_id	INTEGER PRIMARY KEY,	-- subscriber.id
 	algo_id_3g	INTEGER NOT NULL,	-- enum osmo_auth_algo value
 	k		VARCHAR(32) NOT NULL,	-- hex string: subscriber's secret key (128bit)
@@ -68,4 +68,3 @@ CREATE TABLE auc_3g (
 );
 
 CREATE UNIQUE INDEX IF NOT EXISTS idx_subscr_imsi ON subscriber (imsi);
--- SELECT algo_id_2g, ki, algo_id_3g, k, op, opc, sqn FROM subscriber LEFT JOIN auc_2g ON auc_2g.subscriber_id = subscriber.id LEFT JOIN auc_3g ON auc_3g.subscriber_id = subscriber.id WHERE imsi = ?
diff --git a/src/Makefile.am b/src/Makefile.am
index fc7c653..3b09b7b 100644
--- a/src/Makefile.am
+++ b/src/Makefile.am
@@ -10,8 +10,14 @@ AM_CFLAGS = \
 
 EXTRA_DIST = \
 	populate_hlr_db.pl \
+	db_bootstrap.sed \
 	$(NULL)
 
+BUILT_SOURCES = \
+	db_bootstrap.h \
+	$(NULL)
+CLEANFILES = $(BUILT_SOURCES)
+
 noinst_HEADERS = \
 	auc.h \
 	db.h \
@@ -24,6 +30,7 @@ noinst_HEADERS = \
 	ctrl.h \
 	hlr_vty.h \
 	hlr_vty_subscr.h \
+	db_bootstrap.h \
 	$(NULL)
 
 bin_PROGRAMS = \
@@ -73,3 +80,14 @@ db_test_LDADD = \
 	$(LIBOSMOGSM_LIBS) \
 	$(SQLITE3_LIBS) \
 	$(NULL)
+
+BOOTSTRAP_SQL = $(top_srcdir)/sql/hlr.sql
+
+db_bootstrap.h: $(BOOTSTRAP_SQL) $(srcdir)/db_bootstrap.sed
+	echo "/* DO NOT EDIT THIS FILE. It is generated from osmo-hlr.git/sql/hlr.sql */" > "$@"
+	echo "#pragma once" >> "$@"
+	echo "static const char *stmt_bootstrap_sql[] = {" >> "$@"
+	cat "$(BOOTSTRAP_SQL)" \
+		| sed -f "$(srcdir)/db_bootstrap.sed" \
+		>> "$@"
+	echo "};" >> "$@"
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,
diff --git a/src/db_bootstrap.sed b/src/db_bootstrap.sed
new file mode 100644
index 0000000..8c34532
--- /dev/null
+++ b/src/db_bootstrap.sed
@@ -0,0 +1,25 @@
+# Input to this is sql/hlr.sql.
+#
+# We want each SQL statement line wrapped in "...\n", and each end (";") to
+# become a comma:
+#
+#   SOME SQL COMMAND (
+#     that may span )
+#   MULTIPLE LINES;
+#   MORE;
+#
+# -->
+#
+#   "SOME SQL COMMAND (\n"
+#   "  that may span )\n"
+#   "MULTIPLE LINES\n",
+#   "MORE\n",
+#
+# just replacing ';' with '\n,' won't work, since sed is bad in printing
+# multiple lines. Also, how to input newlines to sed is not portable across
+# platforms.
+
+# Match excluding a trailing ';' as \1, keep any trailing ';'
+s/^\(.*[^;]\)\(;\|\)$/"\1\\n"\2/
+# Replace trailing ';' as ','
+s/;$/,/
diff --git a/tests/db/Makefile.am b/tests/db/Makefile.am
index b706bec..55b1655 100644
--- a/tests/db/Makefile.am
+++ b/tests/db/Makefile.am
@@ -1,6 +1,7 @@
 AM_CFLAGS = \
 	$(all_includes) \
 	-I$(top_srcdir)/src \
+	-I$(top_builddir)/src \
 	-Wall \
 	-ggdb3 \
 	$(LIBOSMOCORE_CFLAGS) \