From: Holger Hans Peter Freyther <holger@moiji-mobile.com>
This is mostly based on Alexander's migration code. The code
adds transaction handling and some sanity checks and cleanups
to the code. We made the decision to fork the sms_from_result
method and freeze it to that version. This way sms_from_result
can move forward without having to deal with legacy.
---
openbsc/src/libbsc/gsm_subscriber_base.c | 6 +
openbsc/src/libmsc/db.c | 209 ++++++++++++++++++++++++++++---
2 files changed, 199 insertions(+), 16 deletions(-)
diff --git a/openbsc/src/libbsc/gsm_subscriber_base.c b/openbsc/src/libbsc/gsm_subscriber_base.c
index 5755687..5e00443 100644
--- a/openbsc/src/libbsc/gsm_subscriber_base.c
+++ b/openbsc/src/libbsc/gsm_subscriber_base.c
@@ -72,6 +72,12 @@ static void subscr_free(struct gsm_subscriber *subscr)
talloc_free(subscr);
}
+void subscr_direct_free(struct gsm_subscriber *subscr)
+{
+ OSMO_ASSERT(subscr->use_count == 1);
+ subscr_free(subscr);
+}
+
struct gsm_subscriber *subscr_get(struct gsm_subscriber *subscr)
{
subscr->use_count++;
diff --git a/openbsc/src/libmsc/db.c b/openbsc/src/libmsc/db.c
index 2351485..3b92691 100644
--- a/openbsc/src/libmsc/db.c
+++ b/openbsc/src/libmsc/db.c
@@ -38,23 +38,42 @@
#include <osmocom/core/statistics.h>
#include <osmocom/core/rate_ctr.h>
+/* Semi-Private-Interface (SPI) for the subscriber code */
+void subscr_direct_free(struct gsm_subscriber *subscr);
+
static char *db_basename = NULL;
static char *db_dirname = NULL;
static dbi_conn conn;
-#define SCHEMA_REVISION "3"
+#define SCHEMA_REVISION "4"
+
+enum {
+ SCHEMA_META,
+ INSERT_META,
+ SCHEMA_SUBSCRIBER,
+ SCHEMA_AUTH,
+ SCHEMA_EQUIPMENT,
+ SCHEMA_EQUIPMENT_WATCH,
+ SCHEMA_SMS,
+ SCHEMA_VLR,
+ SCHEMA_APDU,
+ SCHEMA_COUNTERS,
+ SCHEMA_RATE,
+ SCHEMA_AUTHKEY,
+ SCHEMA_AUTHLAST,
+};
-static char *create_stmts[] = {
- "CREATE TABLE IF NOT EXISTS Meta ("
+static const char *create_stmts[] = {
+ [SCHEMA_META] = "CREATE TABLE IF NOT EXISTS Meta ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"key TEXT UNIQUE NOT NULL, "
"value TEXT NOT NULL"
")",
- "INSERT OR IGNORE INTO Meta "
+ [INSERT_META] = "INSERT OR IGNORE INTO Meta "
"(key, value) "
"VALUES "
"('revision', " SCHEMA_REVISION ")",
- "CREATE TABLE IF NOT EXISTS Subscriber ("
+ [SCHEMA_SUBSCRIBER] = "CREATE TABLE IF NOT EXISTS Subscriber ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"created TIMESTAMP NOT NULL, "
"updated TIMESTAMP NOT NULL, "
@@ -66,13 +85,13 @@ static char *create_stmts[] = {
"lac INTEGER NOT NULL DEFAULT 0, "
"expire_lu TIMESTAMP DEFAULT NULL"
")",
- "CREATE TABLE IF NOT EXISTS AuthToken ("
+ [SCHEMA_AUTH] = "CREATE TABLE IF NOT EXISTS AuthToken ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"subscriber_id INTEGER UNIQUE NOT NULL, "
"created TIMESTAMP NOT NULL, "
"token TEXT UNIQUE NOT NULL"
")",
- "CREATE TABLE IF NOT EXISTS Equipment ("
+ [SCHEMA_EQUIPMENT] = "CREATE TABLE IF NOT EXISTS Equipment ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"created TIMESTAMP NOT NULL, "
"updated TIMESTAMP NOT NULL, "
@@ -82,7 +101,7 @@ static char *create_stmts[] = {
"classmark3 BLOB, "
"imei NUMERIC UNIQUE NOT NULL"
")",
- "CREATE TABLE IF NOT EXISTS EquipmentWatch ("
+ [SCHEMA_EQUIPMENT_WATCH] = "CREATE TABLE IF NOT EXISTS EquipmentWatch ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"created TIMESTAMP NOT NULL, "
"updated TIMESTAMP NOT NULL, "
@@ -90,7 +109,7 @@ static char *create_stmts[] = {
"equipment_id NUMERIC NOT NULL, "
"UNIQUE (subscriber_id, equipment_id) "
")",
- "CREATE TABLE IF NOT EXISTS SMS ("
+ [SCHEMA_SMS] = "CREATE TABLE IF NOT EXISTS SMS ("
/* metadata, not part of sms */
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"created TIMESTAMP NOT NULL, "
@@ -114,39 +133,39 @@ static char *create_stmts[] = {
"header BLOB, " /* UD Header */
"text TEXT " /* decoded UD after UDH */
")",
- "CREATE TABLE IF NOT EXISTS VLR ("
+ [SCHEMA_VLR] = "CREATE TABLE IF NOT EXISTS VLR ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"created TIMESTAMP NOT NULL, "
"updated TIMESTAMP NOT NULL, "
"subscriber_id NUMERIC UNIQUE NOT NULL, "
"last_bts NUMERIC NOT NULL "
")",
- "CREATE TABLE IF NOT EXISTS ApduBlobs ("
+ [SCHEMA_APDU] = "CREATE TABLE IF NOT EXISTS ApduBlobs ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"created TIMESTAMP NOT NULL, "
"apdu_id_flags INTEGER NOT NULL, "
"subscriber_id INTEGER NOT NULL, "
"apdu BLOB "
")",
- "CREATE TABLE IF NOT EXISTS Counters ("
+ [SCHEMA_COUNTERS] = "CREATE TABLE IF NOT EXISTS Counters ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"timestamp TIMESTAMP NOT NULL, "
"value INTEGER NOT NULL, "
"name TEXT NOT NULL "
")",
- "CREATE TABLE IF NOT EXISTS RateCounters ("
+ [SCHEMA_RATE] = "CREATE TABLE IF NOT EXISTS RateCounters ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"timestamp TIMESTAMP NOT NULL, "
"value INTEGER NOT NULL, "
"name TEXT NOT NULL, "
"idx INTEGER NOT NULL "
")",
- "CREATE TABLE IF NOT EXISTS AuthKeys ("
+ [SCHEMA_AUTHKEY] = "CREATE TABLE IF NOT EXISTS AuthKeys ("
"subscriber_id INTEGER PRIMARY KEY, "
"algorithm_id INTEGER NOT NULL, "
"a3a8_ki BLOB "
")",
- "CREATE TABLE IF NOT EXISTS AuthLastTuples ("
+ [SCHEMA_AUTHLAST] = "CREATE TABLE IF NOT EXISTS AuthLastTuples ("
"subscriber_id INTEGER PRIMARY KEY, "
"issued TIMESTAMP NOT NULL, "
"use_count INTEGER NOT NULL DEFAULT 0, "
@@ -185,12 +204,164 @@ static int update_db_revision_2(void)
"WHERE key = 'revision'");
if (!result) {
LOGP(DDB, LOGL_ERROR,
- "Failed set new revision (upgrade vom rev 2).\n");
+ "Failed to update DB schema revision (upgrade from rev 2).\n");
+ return -EINVAL;
+ }
+ dbi_result_free(result);
+
+ return 0;
+}
+
+/**
+ * Copied from the normal sms_from_result_v3 to avoid having
+ * to make sure that the real routine will remain backward
+ * compatible.
+ */
+static struct gsm_sms *sms_from_result_v3(dbi_result result)
+{
+ struct gsm_sms *sms = sms_alloc();
+ long long unsigned int sender_id;
+ struct gsm_subscriber *sender;
+ const char *text, *daddr;
+ const unsigned char *user_data;
+ char buf[32];
+
+ if (!sms)
+ return NULL;
+
+ sms->id = dbi_result_get_ulonglong(result, "id");
+
+ sender_id = dbi_result_get_ulonglong(result, "sender_id");
+ snprintf(buf, sizeof(buf), "%llu", sender_id);
+ sender = db_get_subscriber(GSM_SUBSCRIBER_ID, buf);
+ OSMO_ASSERT(sender);
+ strncpy(sms->src.addr, sender->extension, sizeof(sms->src.addr)-1);
+ subscr_direct_free(sender);
+ sender = NULL;
+
+ sms->reply_path_req = dbi_result_get_uint(result, "reply_path_req");
+ sms->status_rep_req = dbi_result_get_uint(result, "status_rep_req");
+ sms->ud_hdr_ind = dbi_result_get_uint(result, "ud_hdr_ind");
+ sms->protocol_id = dbi_result_get_uint(result, "protocol_id");
+ sms->data_coding_scheme = dbi_result_get_uint(result,
+ "data_coding_scheme");
+
+ daddr = dbi_result_get_string(result, "dest_addr");
+ if (daddr) {
+ strncpy(sms->dst.addr, daddr, sizeof(sms->dst.addr));
+ sms->dst.addr[sizeof(sms->dst.addr)-1] = '\0';
+ }
+
+ sms->user_data_len = dbi_result_get_field_length(result, "user_data");
+ user_data = dbi_result_get_binary(result, "user_data");
+ if (sms->user_data_len > sizeof(sms->user_data))
+ sms->user_data_len = (uint8_t) sizeof(sms->user_data);
+ memcpy(sms->user_data, user_data, sms->user_data_len);
+
+ text = dbi_result_get_string(result, "text");
+ if (text) {
+ strncpy(sms->text, text, sizeof(sms->text));
+ sms->text[sizeof(sms->text)-1] = '\0';
+ }
+ return sms;
+}
+
+static int update_db_revision_3(void)
+{
+ dbi_result result;
+ struct gsm_sms *sms;
+
+ result = dbi_conn_query(conn, "BEGIN EXCLUSIVE TRANSACTION");
+ if (!result) {
+ LOGP(DDB, LOGL_ERROR,
+ "Failed to begin transaction (upgrade from rev 3)\n");
return -EINVAL;
}
dbi_result_free(result);
+ /* Rename old SMS table to be able create a new one */
+ result = dbi_conn_query(conn, "ALTER TABLE SMS RENAME TO SMS_3");
+ if (!result) {
+ LOGP(DDB, LOGL_ERROR,
+ "Failed to rename the old SMS table (upgrade from rev 3).\n");
+ goto rollback;
+ }
+ dbi_result_free(result);
+
+ /* Create new SMS table with all the bells and whistles! */
+ result = dbi_conn_query(conn, create_stmts[SCHEMA_SMS]);
+ if (!result) {
+ LOGP(DDB, LOGL_ERROR,
+ "Failed to create a new SMS table (upgrade from rev 3).\n");
+ goto rollback;
+ }
+ dbi_result_free(result);
+
+ /* Cycle through old messages and convert them to the new format */
+ result = dbi_conn_queryf(conn, "SELECT * FROM SMS_3");
+ if (!result) {
+ LOGP(DDB, LOGL_ERROR,
+ "Failed fetch messages from the old SMS table (upgrade from rev 3).\n");
+ goto rollback;
+ }
+ while (dbi_result_next_row(result)) {
+ sms = sms_from_result_v3(result);
+ if (db_sms_store(sms) != 0) {
+ LOGP(DDB, LOGL_ERROR, "Failed to store message to the new SMS table(upgrade from rev 3).\n");
+ sms_free(sms);
+ dbi_result_free(result);
+ goto rollback;
+ }
+ sms_free(sms);
+ }
+ dbi_result_free(result);
+
+ /* Remove the temporary table */
+ result = dbi_conn_query(conn, "DROP TABLE SMS_3");
+ if (!result) {
+ LOGP(DDB, LOGL_ERROR,
+ "Failed to drop the old SMS table (upgrade from rev 3).\n");
+ goto rollback;
+ }
+ dbi_result_free(result);
+
+ /* We're done. Bump DB Meta revision to 4 */
+ result = dbi_conn_query(conn,
+ "UPDATE Meta "
+ "SET value = '4' "
+ "WHERE key = 'revision'");
+ if (!result) {
+ LOGP(DDB, LOGL_ERROR,
+ "Failed to update DB schema revision (upgrade from rev 3).\n");
+ goto rollback;
+ }
+ dbi_result_free(result);
+
+ result = dbi_conn_query(conn, "COMMIT TRANSACTION");
+ if (!result) {
+ LOGP(DDB, LOGL_ERROR,
+ "Failed to commit the transaction (upgrade from rev 3)\n");
+ return -EINVAL;
+ }
+
+ /* Shrink DB file size by actually wiping out SMS_3 table data */
+ result = dbi_conn_query(conn, "VACUUM");
+ if (!result)
+ LOGP(DDB, LOGL_ERROR,
+ "VACUUM failed. Ignoring it (upgrade from rev 3).\n");
+ else
+ dbi_result_free(result);
+
return 0;
+
+rollback:
+ result = dbi_conn_query(conn, "ROLLBACK TRANSACTION");
+ if (!result)
+ LOGP(DDB, LOGL_ERROR,
+ "Rollback failed (upgrade from rev 3).\n");
+ else
+ dbi_result_free(result);
+ return -EINVAL;
}
static int check_db_revision(void)
@@ -218,6 +389,12 @@ static int check_db_revision(void)
dbi_result_free(result);
return -EINVAL;
}
+ } else if (!strcmp(rev_s, "3")) {
+ if (update_db_revision_3()) {
+ LOGP(DDB, LOGL_FATAL, "Failed to update database from schema revision '%s'.\n", rev_s);
+ dbi_result_free(result);
+ return -EINVAL;
+ }
} else if (!strcmp(rev_s, SCHEMA_REVISION)) {
/* everything is fine */
} else {
--
1.9.0