diff options
author | Neels Hofmeyr <neels@hofmeyr.de> | 2017-10-24 23:26:27 +0200 |
---|---|---|
committer | Harald Welte <laforge@gnumonks.org> | 2017-10-28 16:49:33 +0000 |
commit | 7750d2cedc5403cd1d756d1d30a8e08fccd12f74 (patch) | |
tree | d6c4036ad34832f2f59746237b2d80e949f860cf /sql | |
parent | cd7fa4502cc2e003ae1d6520f7a49fdaef490a3c (diff) |
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
Diffstat (limited to 'sql')
-rw-r--r-- | sql/hlr.sql | 11 |
1 files changed, 5 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 = ? |