This is merely a historical archive of years 2008-2021, before the migration to mailman3.
A maintained and still updated list archive can be found at https://lists.osmocom.org/hyperkitty/list/gerrit-log@lists.osmocom.org/.
Neels Hofmeyr gerrit-no-reply at lists.osmocom.orgReview at https://gerrit.osmocom.org/4438 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 --- M sql/hlr.sql M src/Makefile.am M src/db.c A src/db_bootstrap.sed M tests/Makefile.am M tests/db/Makefile.am 6 files changed, 81 insertions(+), 7 deletions(-) git pull ssh://gerrit.osmocom.org:29418/osmo-hlr refs/changes/38/4438/1 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 @@ 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 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,7 +10,13 @@ EXTRA_DIST = \ populate_hlr_db.pl \ + db_bootstrap.sed \ $(NULL) + +BUILT_SOURCES = \ + db_bootstrap.h \ + $(NULL) +CLEANFILES = $(BUILT_SOURCES) noinst_HEADERS = \ auc.h \ @@ -24,6 +30,7 @@ ctrl.h \ hlr_vty.h \ hlr_vty_subscr.h \ + db_bootstrap.h \ $(NULL) bin_PROGRAMS = \ @@ -73,3 +80,14 @@ $(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 @@ 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 @@ 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..60b8243 --- /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", <--note the comma here +# "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 ';' in \2 +s/^\(.*[^;]\)\(;\|\)$/"\1\\n"\2/ +# Replace trailing ';' as ',' +s/;$/,/ diff --git a/tests/Makefile.am b/tests/Makefile.am index 2dba6a1..f1cc710 100644 --- a/tests/Makefile.am +++ b/tests/Makefile.am @@ -51,7 +51,6 @@ # make vty-test U=-u vty-test: -rm -f $(VTY_TEST_DB) - sqlite3 $(VTY_TEST_DB) < $(top_srcdir)/sql/hlr.sql osmo_verify_transcript_vty.py -v \ -n OsmoHLR -p 4258 \ -r "$(top_builddir)/src/osmo-hlr -c $(top_srcdir)/doc/examples/osmo-hlr.cfg -l $(VTY_TEST_DB)" \ 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) \ -- To view, visit https://gerrit.osmocom.org/4438 To unsubscribe, visit https://gerrit.osmocom.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: If77dbbfe1af3e66aaec91cb6295b687f37678636 Gerrit-PatchSet: 1 Gerrit-Project: osmo-hlr Gerrit-Branch: master Gerrit-Owner: Neels Hofmeyr <nhofmeyr at sysmocom.de>