/* Simple HLR/VLR database backend using sqlite3 */ /* (C) 2008 by Jan Luebbe * (C) 2009 by Holger Hans Peter Freyther * (C) 2009,2022 by Harald Welte * All Rights Reserved * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as published by * the Free Software Foundation; either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Affero General Public License for more details. * * You should have received a copy of the GNU Affero General Public License * along with this program. If not, see . * */ #include #include #include #include #include #include #include #include #include #include #include #include #include #include #include #include #include #include #include #include #include enum stmt_idx { DB_STMT_SMS_STORE, DB_STMT_SMS_GET, DB_STMT_SMS_GET_NEXT_UNSENT, DB_STMT_SMS_GET_UNSENT_FOR_SUBSCR, DB_STMT_SMS_GET_NEXT_UNSENT_RR_MSISDN, DB_STMT_SMS_MARK_DELIVERED, DB_STMT_SMS_INC_DELIVER_ATTEMPTS, DB_STMT_SMS_DEL_BY_MSISDN, DB_STMT_SMS_DEL_BY_ID, DB_STMT_SMS_DEL_EXPIRED, DB_STMT_SMS_GET_VALID_UNTIL_BY_ID, DB_STMT_SMS_GET_OLDEST_EXPIRED, _NUM_DB_STMT }; struct db_context { char *fname; sqlite3 *db; sqlite3_stmt *stmt[_NUM_DB_STMT]; }; static struct db_context *g_dbc; /*********************************************************************** * DATABASE SCHEMA AND MIGRATION ***********************************************************************/ #define SCHEMA_REVISION "5" 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 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_META] = "INSERT OR IGNORE INTO Meta " "(key, value) " "VALUES " "('revision', " SCHEMA_REVISION ")", [SCHEMA_SUBSCRIBER] = "CREATE TABLE IF NOT EXISTS Subscriber (" "id INTEGER PRIMARY KEY AUTOINCREMENT, " "created TIMESTAMP NOT NULL, " "updated TIMESTAMP NOT NULL, " "imsi NUMERIC UNIQUE NOT NULL, " "name TEXT, " "extension TEXT UNIQUE, " "authorized INTEGER NOT NULL DEFAULT 0, " "tmsi TEXT UNIQUE, " "lac INTEGER NOT NULL DEFAULT 0, " "expire_lu TIMESTAMP DEFAULT NULL" ")", [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" ")", [SCHEMA_EQUIPMENT] = "CREATE TABLE IF NOT EXISTS Equipment (" "id INTEGER PRIMARY KEY AUTOINCREMENT, " "created TIMESTAMP NOT NULL, " "updated TIMESTAMP NOT NULL, " "name TEXT, " "classmark1 NUMERIC, " "classmark2 BLOB, " "classmark3 BLOB, " "imei NUMERIC UNIQUE NOT NULL" ")", [SCHEMA_EQUIPMENT_WATCH] = "CREATE TABLE IF NOT EXISTS EquipmentWatch (" "id INTEGER PRIMARY KEY AUTOINCREMENT, " "created TIMESTAMP NOT NULL, " "updated TIMESTAMP NOT NULL, " "subscriber_id NUMERIC NOT NULL, " "equipment_id NUMERIC NOT NULL, " "UNIQUE (subscriber_id, equipment_id) " ")", [SCHEMA_SMS] = "CREATE TABLE IF NOT EXISTS SMS (" /* metadata, not part of sms */ "id INTEGER PRIMARY KEY AUTOINCREMENT, " "created TIMESTAMP NOT NULL, " "sent TIMESTAMP, " "deliver_attempts INTEGER NOT NULL DEFAULT 0, " /* data directly copied/derived from SMS */ "valid_until TIMESTAMP, " "reply_path_req INTEGER NOT NULL, " "status_rep_req INTEGER NOT NULL, " "is_report INTEGER NOT NULL, " "msg_ref INTEGER NOT NULL, " "protocol_id INTEGER NOT NULL, " "data_coding_scheme INTEGER NOT NULL, " "ud_hdr_ind INTEGER NOT NULL, " "src_addr TEXT NOT NULL, " "src_ton INTEGER NOT NULL, " "src_npi INTEGER NOT NULL, " "dest_addr TEXT NOT NULL, " "dest_ton INTEGER NOT NULL, " "dest_npi INTEGER NOT NULL, " "user_data BLOB, " /* TP-UD */ /* additional data, interpreted from SMS */ "header BLOB, " /* UD Header */ "text TEXT " /* decoded UD after UDH */ ")", [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 " ")", [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 " ")", [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 " ")", [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 " ")", [SCHEMA_AUTHKEY] = "CREATE TABLE IF NOT EXISTS AuthKeys (" "subscriber_id INTEGER PRIMARY KEY, " "algorithm_id INTEGER NOT NULL, " "a3a8_ki BLOB " ")", [SCHEMA_AUTHLAST] = "CREATE TABLE IF NOT EXISTS AuthLastTuples (" "subscriber_id INTEGER PRIMARY KEY, " "issued TIMESTAMP NOT NULL, " "use_count INTEGER NOT NULL DEFAULT 0, " "key_seq INTEGER NOT NULL, " "rand BLOB NOT NULL, " "sres BLOB NOT NULL, " "kc BLOB NOT NULL " ")", }; #if 0 static void parse_tp_ud_from_result(struct gsm_sms *sms, sqlite3_stmtp *stmt); static int update_db_revision_2(void) { dbi_result result; result = dbi_conn_query(conn, "ALTER TABLE Subscriber " "ADD COLUMN expire_lu " "TIMESTAMP DEFAULT NULL"); if (!result) { LOGP(DDB, LOGL_ERROR, "Failed to alter table Subscriber (upgrade from rev 2).\n"); return -EINVAL; } dbi_result_free(result); result = dbi_conn_query(conn, "UPDATE Meta " "SET value = '3' " "WHERE key = 'revision'"); if (!result) { LOGP(DDB, LOGL_ERROR, "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; const char *daddr; char buf[32]; char *quoted; dbi_result result2; const char *extension; if (!sms) return NULL; sms->id = dbi_result_get_ulonglong(result, "id"); /* find extension by id, assuming that the subscriber still exists in * the db */ sender_id = dbi_result_get_ulonglong(result, "sender_id"); snprintf(buf, sizeof(buf), "%llu", sender_id); dbi_conn_quote_string_copy(conn, buf, "ed); result2 = dbi_conn_queryf(conn, "SELECT extension FROM Subscriber " "WHERE id = %s ", quoted); free(quoted); extension = dbi_result_get_string(result2, "extension"); if (extension) OSMO_STRLCPY_ARRAY(sms->src.addr, extension); dbi_result_free(result2); /* got the extension */ sms->reply_path_req = dbi_result_get_ulonglong(result, "reply_path_req"); sms->status_rep_req = dbi_result_get_ulonglong(result, "status_rep_req"); sms->ud_hdr_ind = dbi_result_get_ulonglong(result, "ud_hdr_ind"); sms->protocol_id = dbi_result_get_ulonglong(result, "protocol_id"); sms->data_coding_scheme = dbi_result_get_ulonglong(result, "data_coding_scheme"); daddr = dbi_result_get_string(result, "dest_addr"); if (daddr) OSMO_STRLCPY_ARRAY(sms->dst.addr, daddr); /* Parse TP-UD, TP-UDL and decoded text */ parse_tp_ud_from_result(sms, result); return sms; } static int update_db_revision_3(void) { dbi_result result; struct gsm_sms *sms; LOGP(DDB, LOGL_NOTICE, "Going to migrate from revision 3\n"); 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_query(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 (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; } else { dbi_result_free(result); } /* 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; } /* Just like v3, but there is a new message reference field for status reports, * that is set to zero for existing entries since there is no way we can infer * this. */ static struct gsm_sms *sms_from_result_v4(dbi_result result) { struct gsm_sms *sms = sms_alloc(); const char *addr; if (!sms) return NULL; sms->id = dbi_result_get_ulonglong(result, "id"); sms->reply_path_req = dbi_result_get_ulonglong(result, "reply_path_req"); sms->status_rep_req = dbi_result_get_ulonglong(result, "status_rep_req"); sms->ud_hdr_ind = dbi_result_get_ulonglong(result, "ud_hdr_ind"); sms->protocol_id = dbi_result_get_ulonglong(result, "protocol_id"); sms->data_coding_scheme = dbi_result_get_ulonglong(result, "data_coding_scheme"); addr = dbi_result_get_string(result, "src_addr"); OSMO_STRLCPY_ARRAY(sms->src.addr, addr); sms->src.ton = dbi_result_get_ulonglong(result, "src_ton"); sms->src.npi = dbi_result_get_ulonglong(result, "src_npi"); addr = dbi_result_get_string(result, "dest_addr"); OSMO_STRLCPY_ARRAY(sms->dst.addr, addr); sms->dst.ton = dbi_result_get_ulonglong(result, "dest_ton"); sms->dst.npi = dbi_result_get_ulonglong(result, "dest_npi"); /* Parse TP-UD, TP-UDL and decoded text */ parse_tp_ud_from_result(sms, result); return sms; } static int update_db_revision_4(void) { dbi_result result; struct gsm_sms *sms; LOGP(DDB, LOGL_NOTICE, "Going to migrate from revision 4\n"); result = dbi_conn_query(conn, "BEGIN EXCLUSIVE TRANSACTION"); if (!result) { LOGP(DDB, LOGL_ERROR, "Failed to begin transaction (upgrade from rev 4)\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_4"); if (!result) { LOGP(DDB, LOGL_ERROR, "Failed to rename the old SMS table (upgrade from rev 4).\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 4).\n"); goto rollback; } dbi_result_free(result); /* Cycle through old messages and convert them to the new format */ result = dbi_conn_query(conn, "SELECT * FROM SMS_4"); if (!result) { LOGP(DDB, LOGL_ERROR, "Failed fetch messages from the old SMS table (upgrade from rev 4).\n"); goto rollback; } while (next_row(result)) { sms = sms_from_result_v4(result); if (db_sms_store(sms) != 0) { LOGP(DDB, LOGL_ERROR, "Failed to store message to the new SMS table(upgrade from rev 4).\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_4"); if (!result) { LOGP(DDB, LOGL_ERROR, "Failed to drop the old SMS table (upgrade from rev 4).\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 = '5' " "WHERE key = 'revision'"); if (!result) { LOGP(DDB, LOGL_ERROR, "Failed to update DB schema revision (upgrade from rev 4).\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 4)\n"); return -EINVAL; } else { dbi_result_free(result); } /* Shrink DB file size by actually wiping out SMS_4 table data */ result = dbi_conn_query(conn, "VACUUM"); if (!result) LOGP(DDB, LOGL_ERROR, "VACUUM failed. Ignoring it (upgrade from rev 4).\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 4).\n"); else dbi_result_free(result); return -EINVAL; } #endif /*********************************************************************** * PREPARED STATEMENTS ***********************************************************************/ /* don't change this order as the code assumes this ordering when dereferencing * database query results! */ #define SEL_COLUMNS \ "id," \ "strftime('%s',created)," \ "sent," \ "deliver_attempts," \ "strftime('%s',valid_until)," \ "reply_path_req," \ "status_rep_req," \ "is_report," \ "msg_ref," \ "protocol_id," \ "data_coding_scheme," \ "ud_hdr_ind," \ "src_addr," \ "src_ton," \ "src_npi," \ "dest_addr," \ "dest_ton," \ "dest_npi," \ "user_data," \ "header," \ "text" enum db_sms_column_idx { COL_ID, COL_CREATED, COL_SENT, COL_DELIVER_ATTEMPTS, COL_VALID_UNTIL, COL_REPLY_PATH_REQ, COL_STATUS_REP_REQ, COL_IS_REPORT, COL_MSG_REF, COL_PROTOCOL_ID, COL_DATA_CODING_SCHEME, COL_UD_HDR_IND, COL_SRC_ADDR, COL_SRC_TON, COL_SRC_NPI, COL_DEST_ADDR, COL_DEST_TON, COL_DEST_NPI, COL_USER_DATA, COL_HEADER, COL_TEXT, }; static const char *stmt_sql[] = { [DB_STMT_SMS_STORE] = "INSERT INTO SMS " "(created, valid_until, reply_path_req, status_rep_req, is_report, " " msg_ref, protocol_id, data_coding_scheme, ud_hdr_ind, user_data, text, " " dest_addr, dest_ton, dest_npi, src_addr, src_ton, src_npi) " "VALUES " "(datetime($created, 'unixepoch'), datetime($valid_until, 'unixepoch'), " "$reply_path_req, $status_rep_req, $is_report, " "$msg_ref, $protocol_id, $data_coding_scheme, $ud_hdr_ind, $user_data, $text, " "$dest_addr, $dest_ton, $dest_npi, $src_addr, $src_ton, $src_npi)", [DB_STMT_SMS_GET] = "SELECT " SEL_COLUMNS " FROM SMS WHERE SMS.id = $id", [DB_STMT_SMS_GET_NEXT_UNSENT] = "SELECT " SEL_COLUMNS " FROM SMS" " WHERE sent IS NULL" " AND id >= $id" " AND deliver_attempts <= $attempts" " ORDER BY id LIMIT 1", [DB_STMT_SMS_GET_UNSENT_FOR_SUBSCR] = "SELECT " SEL_COLUMNS " FROM SMS" " WHERE sent IS NULL" " AND dest_addr = $dest_addr" " AND deliver_attempts <= $attempts" " ORDER BY id LIMIT 1", [DB_STMT_SMS_GET_NEXT_UNSENT_RR_MSISDN] = "SELECT " SEL_COLUMNS " FROM SMS" " WHERE sent IS NULL" " AND dest_addr > $dest_addr" " AND deliver_attempts <= $attempts" " ORDER BY dest_addr, id LIMIT 1", [DB_STMT_SMS_MARK_DELIVERED] = "UPDATE SMS " " SET sent = datetime('now') " " WHERE id = $id", [DB_STMT_SMS_INC_DELIVER_ATTEMPTS] = "UPDATE SMS " " SET deliver_attempts = deliver_attempts + 1 " " WHERE id = $id", [DB_STMT_SMS_DEL_BY_MSISDN] = "DELETE FROM SMS WHERE src_addr=$src_addr OR dest_addr=$dest_addr", [DB_STMT_SMS_DEL_BY_ID] = "DELETE FROM SMS WHERE id = $id AND sent is NOT NULL", [DB_STMT_SMS_DEL_EXPIRED] = "DELETE FROM SMS WHERE id = $id", [DB_STMT_SMS_GET_VALID_UNTIL_BY_ID] = "SELECT strftime('%s',valid_until) FROM SMS WHERE id = $id", [DB_STMT_SMS_GET_OLDEST_EXPIRED] = "SELECT id, strftime('%s',valid_until) FROM SMS ORDER BY valid_until LIMIT 1", }; /*********************************************************************** * libsqlite3 helpers ***********************************************************************/ /* libsqlite3 call-back for error logging */ static void sql3_error_log_cb(void *arg, int err_code, const char *msg) { LOGP(DDB, LOGL_ERROR, "SQLITE3: (%d) %s\n", err_code, msg); osmo_log_backtrace(DDB, LOGL_ERROR); } /* libsqlite3 call-back for normal logging */ static void sql3_sql_log_cb(void *arg, sqlite3 *s3, const char *stmt, int type) { switch (type) { case 0: LOGP(DDB, LOGL_DEBUG, "Opened database\n"); break; case 1: LOGP(DDB, LOGL_DEBUG, "%s\n", stmt); break; case 2: LOGP(DDB, LOGL_DEBUG, "Closed database\n"); break; default: LOGP(DDB, LOGL_DEBUG, "Unknown %d\n", type); break; } } /* remove statement bindings and reset statement to be re-executed */ static void db_remove_reset(sqlite3_stmt *stmt) { sqlite3_clear_bindings(stmt); /* sqlite3_reset() just repeats an error code already evaluated during sqlite3_step(). */ /* coverity[CHECKED_RETURN] */ sqlite3_reset(stmt); } /** bind blob arg and do proper cleanup in case of failure. If param_name is * NULL, bind to the first parameter (useful for SQL statements that have only * one parameter). */ static bool db_bind_blob(sqlite3_stmt *stmt, const char *param_name, const uint8_t *blob, size_t blob_len) { int rc; int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; if (idx < 1) { LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", param_name); return false; } rc = sqlite3_bind_blob(stmt, idx, blob, blob_len, SQLITE_STATIC); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Error binding blob to SQL parameter %s: %d\n", param_name ? param_name : "#1", rc); db_remove_reset(stmt); return false; } return true; } /** bind text arg and do proper cleanup in case of failure. If param_name is * NULL, bind to the first parameter (useful for SQL statements that have only * one parameter). */ static bool db_bind_text(sqlite3_stmt *stmt, const char *param_name, const char *text) { int rc; int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; if (idx < 1) { LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", param_name); return false; } rc = sqlite3_bind_text(stmt, idx, text, -1, SQLITE_STATIC); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Error binding text to SQL parameter %s: %d\n", param_name ? param_name : "#1", rc); db_remove_reset(stmt); return false; } return true; } /** bind int arg and do proper cleanup in case of failure. If param_name is * NULL, bind to the first parameter (useful for SQL statements that have only * one parameter). */ static bool db_bind_int(sqlite3_stmt *stmt, const char *param_name, int nr) { int rc; int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; if (idx < 1) { LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", param_name); return false; } rc = sqlite3_bind_int(stmt, idx, nr); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Error binding int64 to SQL parameter %s: %d\n", param_name ? param_name : "#1", rc); db_remove_reset(stmt); return false; } return true; } /** bind int64 arg and do proper cleanup in case of failure. If param_name is * NULL, bind to the first parameter (useful for SQL statements that have only * one parameter). */ static bool db_bind_int64(sqlite3_stmt *stmt, const char *param_name, int64_t nr) { int rc; int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; if (idx < 1) { LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", param_name); return false; } rc = sqlite3_bind_int64(stmt, idx, nr); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Error binding int64 to SQL parameter %s: %d\n", param_name ? param_name : "#1", rc); db_remove_reset(stmt); return false; } return true; } /* callback for sqlite3_exec() below */ static int db_rev_exec_cb(void *priv, int num_cols, char **vals, char **names) { char **rev_s = priv; OSMO_ASSERT(!strcmp(names[0], "value")); *rev_s = talloc_strdup(NULL, vals[0]); return 0; } static int check_db_revision(struct db_context *dbc) { char *errstr = NULL; char *rev_s; //int db_rev = 0; int rc; /* Make a query */ rc = sqlite3_exec(dbc->db, "SELECT value FROM Meta WHERE key = 'revision'", db_rev_exec_cb, &rev_s, &errstr); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Cannot execute SELECT value from META: %s\n", errstr); sqlite3_free(errstr); return -EINVAL; } if (!strcmp(rev_s, SCHEMA_REVISION)) { /* Everything is fine */ talloc_free(rev_s); return 0; } LOGP(DDB, LOGL_NOTICE, "Detected DB Revision %s, expected %s\n", rev_s, SCHEMA_REVISION); OSMO_ASSERT(!strcmp(rev_s, SCHEMA_REVISION)); #if 0 db_rev = atoi(rev_s); dbi_result_free(result); /* Incremental migration waterfall */ switch (db_rev) { case 2: if (update_db_revision_2()) goto error; /* fall through */ case 3: if (update_db_revision_3()) goto error; /* fall through */ case 4: if (update_db_revision_4()) goto error; /* The end of waterfall */ break; default: LOGP(DDB, LOGL_FATAL, "Invalid database schema revision '%d'.\n", db_rev); return -EINVAL; } return 0; error: LOGP(DDB, LOGL_FATAL, "Failed to update database " "from schema revision '%d'.\n", db_rev); #endif talloc_free(rev_s); return -EINVAL; } /*********************************************************************** * USER API ***********************************************************************/ int db_init(void *ctx, const char *fname, bool enable_sqlite_logging) { unsigned int i; int rc; bool has_sqlite_config_sqllog = false; g_dbc = talloc_zero(ctx, struct db_context); OSMO_ASSERT(g_dbc); /* we are a single-threaded program; we want to avoid all the mutex/etc. overhead */ sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); LOGP(DDB, LOGL_NOTICE, "Init database connection to '%s' using SQLite3 lib version %s\n", fname, sqlite3_libversion()); g_dbc->fname = talloc_strdup(g_dbc, fname); for (i = 0; i < 0xfffff; i++) { const char *o = sqlite3_compileoption_get(i); if (!o) break; LOGP(DDB, LOGL_DEBUG, "SQLite3 compiled with '%s'\n", o); if (!strcmp(o, "ENABLE_SQLLOG")) has_sqlite_config_sqllog = true; } if (enable_sqlite_logging) { rc = sqlite3_config(SQLITE_CONFIG_LOG, sql3_error_log_cb, NULL); if (rc != SQLITE_OK) LOGP(DDB, LOGL_NOTICE, "Unable to set SQLite3 error log callback\n"); } if (has_sqlite_config_sqllog) { rc = sqlite3_config(SQLITE_CONFIG_SQLLOG, sql3_sql_log_cb, NULL); if (rc != SQLITE_OK) LOGP(DDB, LOGL_NOTICE, "Unable to set SQLite3 SQL log callback\n"); } else { LOGP(DDB, LOGL_DEBUG, "Not setting SQL log callback:" " SQLite3 compiled without support for it\n"); } rc = sqlite3_open(g_dbc->fname, &g_dbc->db); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Unable to open DB; rc =%d\n", rc); talloc_free(g_dbc); return -1; } /* enable extended result codes */ rc = sqlite3_extended_result_codes(g_dbc->db, 1); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Unable to enable SQLite3 extended result codes\n"); /* non-fatal */ } char *err_msg; rc = sqlite3_exec(g_dbc->db, "PRAGMA journal_mode=WAL; PRAGMA synchonous = NORMAL;", 0, 0, &err_msg); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Unable to set Write-Ahead Logging: %s\n", err_msg); sqlite3_free(err_msg); /* non-fatal */ } return 0; } int db_fini(void) { unsigned int i; int rc; if (!g_dbc) return 0; for (i = 0; i < ARRAY_SIZE(g_dbc->stmt); i++) { /* it is ok to call finalize on NULL */ sqlite3_finalize(g_dbc->stmt[i]); } /* Ask sqlite3 to close DB */ rc = sqlite3_close(g_dbc->db); if (rc != SQLITE_OK) { /* Make sure it's actually closed! */ LOGP(DDB, LOGL_ERROR, "Couldn't close database: (rc=%d) %s\n", rc, sqlite3_errmsg(g_dbc->db)); } talloc_free(g_dbc); g_dbc = NULL; return 0; } /* run (execute) a series of SQL statements */ static int db_run_statements(struct db_context *dbc, const char **statements, size_t statements_count) { int rc = 0; int i; for (i = 0; i < statements_count; i++) { const char *stmt_str = statements[i]; sqlite3_stmt *stmt; rc = sqlite3_prepare_v2(dbc->db, stmt_str, -1, &stmt, NULL); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", stmt_str); return rc; } rc = sqlite3_step(stmt); db_remove_reset(stmt); sqlite3_finalize(stmt); if (rc != SQLITE_DONE && rc != SQLITE_ROW) { LOGP(DDB, LOGL_ERROR, "SQL error: (%d) %s, during stmt '%s'", rc, sqlite3_errmsg(dbc->db), stmt_str); return rc; } } return rc; } static int db_configure(struct db_context *dbc) { const char *sync_stmts[] = { "PRAGMA synchronous = FULL" }; return db_run_statements(dbc, sync_stmts, ARRAY_SIZE(sync_stmts)); } int db_prepare(void) { unsigned int i; int rc; OSMO_ASSERT(g_dbc); rc = db_run_statements(g_dbc, create_stmts, ARRAY_SIZE(create_stmts)); if (rc != SQLITE_DONE) { LOGP(DDB, LOGL_ERROR, "Failed to create some table.\n"); return 1; } if (check_db_revision(g_dbc) < 0) { LOGP(DDB, LOGL_FATAL, "Database schema revision invalid, " "please update your database schema\n"); return -1; } db_configure(g_dbc); /* prepare all SQL statements */ for (i = 0; i < ARRAY_SIZE(g_dbc->stmt); i++) { rc = sqlite3_prepare_v2(g_dbc->db, stmt_sql[i], -1, &g_dbc->stmt[i], NULL); if (rc != SQLITE_OK) { LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", stmt_sql[i]); return -1; } } return 0; } /* store an [unsent] SMS to the database */ int db_sms_store(struct gsm_sms *sms) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_STORE]; time_t now, validity_timestamp; int rc; now = time(NULL); validity_timestamp = now + sms->validity_minutes * 60; db_bind_int64(stmt, "$created", (int64_t) now); db_bind_int64(stmt, "$valid_until", (int64_t) validity_timestamp); db_bind_int(stmt, "$reply_path_req", sms->reply_path_req); db_bind_int(stmt, "$status_rep_req", sms->status_rep_req); db_bind_int(stmt, "$is_report", sms->is_report); db_bind_int(stmt, "$msg_ref", sms->msg_ref); db_bind_int(stmt, "$protocol_id", sms->protocol_id); db_bind_int(stmt, "$data_coding_scheme", sms->data_coding_scheme); db_bind_int(stmt, "$ud_hdr_ind", sms->ud_hdr_ind); /* FIXME: do we need to use legacy DBI compatible quoting of sms->user_data? */ db_bind_blob(stmt, "$user_data", sms->user_data, sms->user_data_len); db_bind_text(stmt, "$text", (char *)sms->text); db_bind_text(stmt, "$dest_addr", (char *)sms->dst.addr); db_bind_int(stmt, "$dest_ton", sms->dst.ton); db_bind_int(stmt, "$dest_npi", sms->dst.npi); db_bind_text(stmt, "$src_addr", (char *)sms->src.addr); db_bind_int(stmt, "$src_ton", sms->src.ton); db_bind_int(stmt, "$src_npi", sms->src.npi); /* execute statement */ rc = sqlite3_step(stmt); db_remove_reset(stmt); if (rc != SQLITE_DONE) { LOGP(DDB, LOGL_ERROR, "Cannot create SMS: SQL error: (%d) %s\n", rc, sqlite3_errmsg(g_dbc->db)); return -EIO; } sms->id = sqlite3_last_insert_rowid(g_dbc->db); LOGP(DLSMS, LOGL_INFO, "Stored SMS id=%llu in DB\n", sms->id); return 0; } static void parse_tp_ud_from_result(struct gsm_sms *sms, sqlite3_stmt *stmt) { const unsigned char *user_data; unsigned int user_data_len; unsigned int text_len; const char *text; /* Retrieve TP-UDL (User-Data-Length) in octets (regardless of DCS) */ user_data_len = sqlite3_column_bytes(stmt, COL_USER_DATA); if (user_data_len > sizeof(sms->user_data)) { LOGP(DDB, LOGL_ERROR, "SMS TP-UD length %u is too big, truncating to %zu\n", user_data_len, sizeof(sms->user_data)); user_data_len = (uint8_t) sizeof(sms->user_data); } sms->user_data_len = user_data_len; /* Retrieve the TP-UD (User-Data) itself */ if (user_data_len > 0) { user_data = sqlite3_column_blob(stmt, COL_USER_DATA); memcpy(sms->user_data, user_data, user_data_len); } /* Retrieve the text length (excluding '\0') */ text_len = sqlite3_column_bytes(stmt, COL_TEXT); if (text_len >= sizeof(sms->text)) { LOGP(DDB, LOGL_ERROR, "SMS text length %u is too big, truncating to %zu\n", text_len, sizeof(sms->text) - 1); /* OSMO_STRLCPY_ARRAY() does truncation for us */ } /* Retrieve the text parsed from TP-UD (User-Data) */ text = (const char *)sqlite3_column_text(stmt, COL_TEXT); if (text) OSMO_STRLCPY_ARRAY(sms->text, text); } static struct gsm_sms *sms_from_result(struct gsm_network *net, sqlite3_stmt *stmt) { struct gsm_sms *sms = sms_alloc(); const char *daddr, *saddr; time_t validity_timestamp; if (!sms) return NULL; sms->id = sqlite3_column_int64(stmt, COL_ID); sms->created = sqlite3_column_int64(stmt, COL_CREATED); validity_timestamp = sqlite3_column_int64(stmt, COL_VALID_UNTIL); sms->validity_minutes = (validity_timestamp - sms->created) / 60; sms->reply_path_req = sqlite3_column_int(stmt, COL_REPLY_PATH_REQ); sms->status_rep_req = sqlite3_column_int(stmt, COL_STATUS_REP_REQ); sms->is_report = sqlite3_column_int(stmt, COL_IS_REPORT); sms->msg_ref = sqlite3_column_int(stmt, COL_MSG_REF); sms->ud_hdr_ind = sqlite3_column_int(stmt, COL_UD_HDR_IND); sms->protocol_id = sqlite3_column_int(stmt, COL_PROTOCOL_ID); sms->data_coding_scheme = sqlite3_column_int(stmt, COL_DATA_CODING_SCHEME); sms->dst.npi = sqlite3_column_int(stmt, COL_DEST_NPI); sms->dst.ton = sqlite3_column_int(stmt, COL_DEST_TON); daddr = (const char *)sqlite3_column_text(stmt, COL_DEST_ADDR); if (daddr) OSMO_STRLCPY_ARRAY(sms->dst.addr, daddr); if (net != NULL) /* db_sms_test passes NULL, so we need to be tolerant */ sms->receiver = vlr_subscr_find_by_msisdn(net->vlr, sms->dst.addr, VSUB_USE_SMS_RECEIVER); sms->src.npi = sqlite3_column_int(stmt, COL_SRC_NPI); sms->src.ton = sqlite3_column_int(stmt, COL_SRC_TON); saddr = (const char *)sqlite3_column_text(stmt, COL_SRC_ADDR); if (saddr) OSMO_STRLCPY_ARRAY(sms->src.addr, saddr); /* Parse TP-UD, TP-UDL and decoded text */ parse_tp_ud_from_result(sms, stmt); return sms; } struct gsm_sms *db_sms_get(struct gsm_network *net, unsigned long long id) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET]; struct gsm_sms *sms; int rc; db_bind_int64(stmt, "$id", id); rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { db_remove_reset(stmt); return NULL; } sms = sms_from_result(net, stmt); db_remove_reset(stmt); return sms; } struct gsm_sms *db_sms_get_next_unsent(struct gsm_network *net, unsigned long long min_sms_id, unsigned int max_failed) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_NEXT_UNSENT]; struct gsm_sms *sms; int rc; db_bind_int64(stmt, "$id", min_sms_id); db_bind_int(stmt, "$attempts", max_failed); rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { db_remove_reset(stmt); return NULL; } sms = sms_from_result(net, stmt); db_remove_reset(stmt); return sms; } /* retrieve the next unsent SMS for a given subscriber */ struct gsm_sms *db_sms_get_unsent_for_subscr(struct vlr_subscr *vsub, unsigned int max_failed) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_UNSENT_FOR_SUBSCR]; struct gsm_network *net = vsub->vlr->user_ctx; struct gsm_sms *sms; int rc; if (!vsub->lu_complete) return NULL; /* A subscriber having no phone number cannot possibly receive SMS. */ if (*vsub->msisdn == '\0') return NULL; db_bind_text(stmt, "$dest_addr", vsub->msisdn); db_bind_int(stmt, "$attempts", max_failed); rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { db_remove_reset(stmt); return NULL; } sms = sms_from_result(net, stmt); db_remove_reset(stmt); return sms; } struct gsm_sms *db_sms_get_next_unsent_rr_msisdn(struct gsm_network *net, const char *last_msisdn, unsigned int max_failed) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_NEXT_UNSENT_RR_MSISDN]; struct gsm_sms *sms; int rc; db_bind_text(stmt, "$dest_addr", last_msisdn); db_bind_int(stmt, "$attempts", max_failed); rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { db_remove_reset(stmt); return NULL; } sms = sms_from_result(net, stmt); db_remove_reset(stmt); return sms; } /* mark a given SMS as delivered */ int db_sms_mark_delivered(struct gsm_sms *sms) { sqlite3_stmt *stmt; int rc; /* this only happens in unit tests that don't db_init() */ if (!g_dbc) return 0; stmt = g_dbc->stmt[DB_STMT_SMS_MARK_DELIVERED]; db_bind_int64(stmt, "$id", sms->id); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { db_remove_reset(stmt); LOGP(DDB, LOGL_ERROR, "Failed to mark SMS %llu as sent.\n", sms->id); return 1; } db_remove_reset(stmt); return 0; } /* increase the number of attempted deliveries */ int db_sms_inc_deliver_attempts(struct gsm_sms *sms) { sqlite3_stmt *stmt; int rc; /* this only happens in unit tests that don't db_init() */ if (!g_dbc) return 0; stmt = g_dbc->stmt[DB_STMT_SMS_INC_DELIVER_ATTEMPTS]; db_bind_int64(stmt, "$id", sms->id); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { db_remove_reset(stmt); LOGP(DDB, LOGL_ERROR, "Failed to inc deliver attempts for SMS %llu.\n", sms->id); return 1; } db_remove_reset(stmt); return 0; } /* Drop all pending SMS to or from the given extension */ int db_sms_delete_by_msisdn(const char *msisdn) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_DEL_BY_MSISDN]; int rc; if (!msisdn || !*msisdn) return 0; db_bind_text(stmt, "$src_addr", msisdn); db_bind_text(stmt, "$dest_addr", msisdn); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { db_remove_reset(stmt); LOGP(DDB, LOGL_ERROR, "Failed to delete SMS for %s\n", msisdn); return -1; } db_remove_reset(stmt); return 0; } int db_sms_delete_sent_message_by_id(unsigned long long sms_id) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_DEL_BY_ID]; int rc; db_bind_int64(stmt, "$id", sms_id); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { db_remove_reset(stmt); LOGP(DDB, LOGL_ERROR, "Failed to delete SMS %llu.\n", sms_id); return 1; } db_remove_reset(stmt); return 0; } static int delete_expired_sms(unsigned long long sms_id, time_t validity_timestamp) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_DEL_EXPIRED]; time_t now; int rc; now = time(NULL); /* Net yet expired */ if (validity_timestamp > now) return -1; db_bind_int64(stmt, "$id", sms_id); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { db_remove_reset(stmt); LOGP(DDB, LOGL_ERROR, "Failed to delete SMS %llu.\n", sms_id); return -1; } db_remove_reset(stmt); return 0; } int db_sms_delete_expired_message_by_id(unsigned long long sms_id) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_VALID_UNTIL_BY_ID]; time_t validity_timestamp; int rc; db_bind_int64(stmt, "$id", sms_id); rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { db_remove_reset(stmt); return -1; } validity_timestamp = sqlite3_column_int64(stmt, 0); db_remove_reset(stmt); return delete_expired_sms(sms_id, validity_timestamp); } void db_sms_delete_oldest_expired_message(void) { OSMO_ASSERT(g_dbc); sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_OLDEST_EXPIRED]; int rc; rc = sqlite3_step(stmt); if (rc == SQLITE_ROW) { unsigned long long sms_id; time_t validity_timestamp; sms_id = sqlite3_column_int64(stmt, 0); validity_timestamp = sqlite3_column_int64(stmt, 1); delete_expired_sms(sms_id, validity_timestamp); } db_remove_reset(stmt); }