Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1 | /* Simple HLR/VLR database backend using sqlite3 */ |
Jan Luebbe | faaa49c | 2008-12-27 01:07:07 +0000 | [diff] [blame] | 2 | /* (C) 2008 by Jan Luebbe <jluebbe@debian.org> |
Holger Freyther | 12aa50d | 2009-01-01 18:02:05 +0000 | [diff] [blame] | 3 | * (C) 2009 by Holger Hans Peter Freyther <zecke@selfish.org> |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 4 | * (C) 2009,2022 by Harald Welte <laforge@gnumonks.org> |
Jan Luebbe | faaa49c | 2008-12-27 01:07:07 +0000 | [diff] [blame] | 5 | * All Rights Reserved |
| 6 | * |
| 7 | * This program is free software; you can redistribute it and/or modify |
Harald Welte | 9af6ddf | 2011-01-01 15:25:50 +0100 | [diff] [blame] | 8 | * it under the terms of the GNU Affero General Public License as published by |
| 9 | * the Free Software Foundation; either version 3 of the License, or |
Jan Luebbe | faaa49c | 2008-12-27 01:07:07 +0000 | [diff] [blame] | 10 | * (at your option) any later version. |
| 11 | * |
| 12 | * This program is distributed in the hope that it will be useful, |
| 13 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 14 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
Harald Welte | 9af6ddf | 2011-01-01 15:25:50 +0100 | [diff] [blame] | 15 | * GNU Affero General Public License for more details. |
Jan Luebbe | faaa49c | 2008-12-27 01:07:07 +0000 | [diff] [blame] | 16 | * |
Harald Welte | 9af6ddf | 2011-01-01 15:25:50 +0100 | [diff] [blame] | 17 | * You should have received a copy of the GNU Affero General Public License |
| 18 | * along with this program. If not, see <http://www.gnu.org/licenses/>. |
Jan Luebbe | faaa49c | 2008-12-27 01:07:07 +0000 | [diff] [blame] | 19 | * |
| 20 | */ |
| 21 | |
Harald Welte | f2b4cd7 | 2010-05-13 11:45:07 +0200 | [diff] [blame] | 22 | #include <stdint.h> |
| 23 | #include <inttypes.h> |
Holger Freyther | bde3610 | 2008-12-28 22:51:39 +0000 | [diff] [blame] | 24 | #include <libgen.h> |
Jan Luebbe | 7398eb9 | 2008-12-27 00:45:41 +0000 | [diff] [blame] | 25 | #include <stdio.h> |
Max | e6052c4 | 2016-06-30 10:25:49 +0200 | [diff] [blame] | 26 | #include <stdbool.h> |
Jan Luebbe | 5c15c85 | 2008-12-27 15:59:25 +0000 | [diff] [blame] | 27 | #include <stdlib.h> |
| 28 | #include <string.h> |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 29 | #include <errno.h> |
Stefan Sperling | d494162 | 2018-01-18 17:36:28 +0100 | [diff] [blame] | 30 | #include <time.h> |
Harald Welte | 467fc57 | 2022-05-13 19:48:08 +0200 | [diff] [blame] | 31 | #include <sqlite3.h> |
Jan Luebbe | 7398eb9 | 2008-12-27 00:45:41 +0000 | [diff] [blame] | 32 | |
Neels Hofmeyr | 9084396 | 2017-09-04 15:04:35 +0200 | [diff] [blame] | 33 | #include <osmocom/msc/gsm_data.h> |
| 34 | #include <osmocom/msc/gsm_subscriber.h> |
| 35 | #include <osmocom/msc/gsm_04_11.h> |
| 36 | #include <osmocom/msc/db.h> |
| 37 | #include <osmocom/msc/debug.h> |
| 38 | #include <osmocom/msc/vlr.h> |
Holger Hans Peter Freyther | c5faf66 | 2010-12-22 18:16:01 +0100 | [diff] [blame] | 39 | |
Harald Welte | d3fa84d | 2016-04-20 17:50:17 +0200 | [diff] [blame] | 40 | #include <osmocom/gsm/protocol/gsm_23_003.h> |
Pablo Neira Ayuso | 136f453 | 2011-03-22 16:47:59 +0100 | [diff] [blame] | 41 | #include <osmocom/core/talloc.h> |
| 42 | #include <osmocom/core/statistics.h> |
| 43 | #include <osmocom/core/rate_ctr.h> |
Neels Hofmeyr | 93bafb6 | 2017-01-13 03:12:08 +0100 | [diff] [blame] | 44 | #include <osmocom/core/utils.h> |
Harald Welte | f2b4cd7 | 2010-05-13 11:45:07 +0200 | [diff] [blame] | 45 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 46 | enum stmt_idx { |
| 47 | DB_STMT_SMS_STORE, |
| 48 | DB_STMT_SMS_GET, |
| 49 | DB_STMT_SMS_GET_NEXT_UNSENT, |
| 50 | DB_STMT_SMS_GET_UNSENT_FOR_SUBSCR, |
| 51 | DB_STMT_SMS_GET_NEXT_UNSENT_RR_MSISDN, |
| 52 | DB_STMT_SMS_MARK_DELIVERED, |
| 53 | DB_STMT_SMS_INC_DELIVER_ATTEMPTS, |
| 54 | DB_STMT_SMS_DEL_BY_MSISDN, |
| 55 | DB_STMT_SMS_DEL_BY_ID, |
| 56 | DB_STMT_SMS_DEL_EXPIRED, |
| 57 | DB_STMT_SMS_GET_VALID_UNTIL_BY_ID, |
| 58 | DB_STMT_SMS_GET_OLDEST_EXPIRED, |
| 59 | _NUM_DB_STMT |
| 60 | }; |
| 61 | |
| 62 | struct db_context { |
| 63 | char *fname; |
| 64 | sqlite3 *db; |
| 65 | sqlite3_stmt *stmt[_NUM_DB_STMT]; |
| 66 | }; |
| 67 | |
| 68 | static struct db_context *g_dbc; |
| 69 | |
| 70 | |
| 71 | /*********************************************************************** |
| 72 | * DATABASE SCHEMA AND MIGRATION |
| 73 | ***********************************************************************/ |
Jan Luebbe | 7398eb9 | 2008-12-27 00:45:41 +0000 | [diff] [blame] | 74 | |
Keith Whyte | ea62986 | 2022-06-18 02:51:51 +0100 | [diff] [blame] | 75 | #define SCHEMA_REVISION "6" |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 76 | |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 77 | enum { |
| 78 | SCHEMA_META, |
| 79 | INSERT_META, |
| 80 | SCHEMA_SUBSCRIBER, |
| 81 | SCHEMA_AUTH, |
| 82 | SCHEMA_EQUIPMENT, |
| 83 | SCHEMA_EQUIPMENT_WATCH, |
| 84 | SCHEMA_SMS, |
| 85 | SCHEMA_VLR, |
| 86 | SCHEMA_APDU, |
| 87 | SCHEMA_COUNTERS, |
| 88 | SCHEMA_RATE, |
| 89 | SCHEMA_AUTHKEY, |
| 90 | SCHEMA_AUTHLAST, |
| 91 | }; |
| 92 | |
| 93 | static const char *create_stmts[] = { |
| 94 | [SCHEMA_META] = "CREATE TABLE IF NOT EXISTS Meta (" |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 95 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 96 | "key TEXT UNIQUE NOT NULL, " |
| 97 | "value TEXT NOT NULL" |
| 98 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 99 | [INSERT_META] = "INSERT OR IGNORE INTO Meta " |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 100 | "(key, value) " |
| 101 | "VALUES " |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 102 | "('revision', " SCHEMA_REVISION ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 103 | [SCHEMA_SUBSCRIBER] = "CREATE TABLE IF NOT EXISTS Subscriber (" |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 104 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 105 | "created TIMESTAMP NOT NULL, " |
| 106 | "updated TIMESTAMP NOT NULL, " |
| 107 | "imsi NUMERIC UNIQUE NOT NULL, " |
| 108 | "name TEXT, " |
| 109 | "extension TEXT UNIQUE, " |
| 110 | "authorized INTEGER NOT NULL DEFAULT 0, " |
| 111 | "tmsi TEXT UNIQUE, " |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 112 | "lac INTEGER NOT NULL DEFAULT 0, " |
| 113 | "expire_lu TIMESTAMP DEFAULT NULL" |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 114 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 115 | [SCHEMA_AUTH] = "CREATE TABLE IF NOT EXISTS AuthToken (" |
Jan Luebbe | 31bef49 | 2009-08-12 14:31:14 +0200 | [diff] [blame] | 116 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 117 | "subscriber_id INTEGER UNIQUE NOT NULL, " |
| 118 | "created TIMESTAMP NOT NULL, " |
| 119 | "token TEXT UNIQUE NOT NULL" |
| 120 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 121 | [SCHEMA_EQUIPMENT] = "CREATE TABLE IF NOT EXISTS Equipment (" |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 122 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 123 | "created TIMESTAMP NOT NULL, " |
| 124 | "updated TIMESTAMP NOT NULL, " |
| 125 | "name TEXT, " |
Harald Welte | c2e302d | 2009-07-05 14:08:13 +0200 | [diff] [blame] | 126 | "classmark1 NUMERIC, " |
| 127 | "classmark2 BLOB, " |
| 128 | "classmark3 BLOB, " |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 129 | "imei NUMERIC UNIQUE NOT NULL" |
| 130 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 131 | [SCHEMA_EQUIPMENT_WATCH] = "CREATE TABLE IF NOT EXISTS EquipmentWatch (" |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 132 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 133 | "created TIMESTAMP NOT NULL, " |
| 134 | "updated TIMESTAMP NOT NULL, " |
| 135 | "subscriber_id NUMERIC NOT NULL, " |
| 136 | "equipment_id NUMERIC NOT NULL, " |
| 137 | "UNIQUE (subscriber_id, equipment_id) " |
| 138 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 139 | [SCHEMA_SMS] = "CREATE TABLE IF NOT EXISTS SMS (" |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 140 | /* metadata, not part of sms */ |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 141 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 142 | "created TIMESTAMP NOT NULL, " |
| 143 | "sent TIMESTAMP, " |
Harald Welte (local) | db552c5 | 2009-08-15 20:15:14 +0200 | [diff] [blame] | 144 | "deliver_attempts INTEGER NOT NULL DEFAULT 0, " |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 145 | /* data directly copied/derived from SMS */ |
Harald Welte | f3efc59 | 2009-07-27 20:11:35 +0200 | [diff] [blame] | 146 | "valid_until TIMESTAMP, " |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 147 | "reply_path_req INTEGER NOT NULL, " |
| 148 | "status_rep_req INTEGER NOT NULL, " |
Pablo Neira Ayuso | 9891dae | 2017-08-07 14:01:33 +0100 | [diff] [blame] | 149 | "is_report INTEGER NOT NULL, " |
| 150 | "msg_ref INTEGER NOT NULL, " |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 151 | "protocol_id INTEGER NOT NULL, " |
| 152 | "data_coding_scheme INTEGER NOT NULL, " |
Harald Welte | d0b7b77 | 2009-08-09 19:03:42 +0200 | [diff] [blame] | 153 | "ud_hdr_ind INTEGER NOT NULL, " |
Holger Hans Peter Freyther | ca3c256 | 2013-10-08 03:17:30 +0200 | [diff] [blame] | 154 | "src_addr TEXT NOT NULL, " |
| 155 | "src_ton INTEGER NOT NULL, " |
| 156 | "src_npi INTEGER NOT NULL, " |
| 157 | "dest_addr TEXT NOT NULL, " |
| 158 | "dest_ton INTEGER NOT NULL, " |
| 159 | "dest_npi INTEGER NOT NULL, " |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 160 | "user_data BLOB, " /* TP-UD */ |
| 161 | /* additional data, interpreted from SMS */ |
| 162 | "header BLOB, " /* UD Header */ |
| 163 | "text TEXT " /* decoded UD after UDH */ |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 164 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 165 | [SCHEMA_VLR] = "CREATE TABLE IF NOT EXISTS VLR (" |
Holger Freyther | c2995ea | 2009-04-19 06:35:23 +0000 | [diff] [blame] | 166 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 167 | "created TIMESTAMP NOT NULL, " |
| 168 | "updated TIMESTAMP NOT NULL, " |
| 169 | "subscriber_id NUMERIC UNIQUE NOT NULL, " |
| 170 | "last_bts NUMERIC NOT NULL " |
| 171 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 172 | [SCHEMA_APDU] = "CREATE TABLE IF NOT EXISTS ApduBlobs (" |
Harald Welte (local) | 026531e | 2009-08-16 10:40:10 +0200 | [diff] [blame] | 173 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 174 | "created TIMESTAMP NOT NULL, " |
| 175 | "apdu_id_flags INTEGER NOT NULL, " |
| 176 | "subscriber_id INTEGER NOT NULL, " |
| 177 | "apdu BLOB " |
| 178 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 179 | [SCHEMA_COUNTERS] = "CREATE TABLE IF NOT EXISTS Counters (" |
Harald Welte | ffa55a4 | 2009-12-22 19:07:32 +0100 | [diff] [blame] | 180 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 181 | "timestamp TIMESTAMP NOT NULL, " |
Harald Welte | f9a43c4 | 2009-12-22 21:40:42 +0100 | [diff] [blame] | 182 | "value INTEGER NOT NULL, " |
| 183 | "name TEXT NOT NULL " |
Harald Welte | 09f7ad0 | 2009-12-24 09:42:07 +0100 | [diff] [blame] | 184 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 185 | [SCHEMA_RATE] = "CREATE TABLE IF NOT EXISTS RateCounters (" |
Harald Welte | c191986 | 2010-05-13 12:55:20 +0200 | [diff] [blame] | 186 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 187 | "timestamp TIMESTAMP NOT NULL, " |
| 188 | "value INTEGER NOT NULL, " |
| 189 | "name TEXT NOT NULL, " |
Harald Welte | d94d6a0 | 2010-05-14 17:38:47 +0200 | [diff] [blame] | 190 | "idx INTEGER NOT NULL " |
Harald Welte | c191986 | 2010-05-13 12:55:20 +0200 | [diff] [blame] | 191 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 192 | [SCHEMA_AUTHKEY] = "CREATE TABLE IF NOT EXISTS AuthKeys (" |
Sylvain Munaut | 10bf812 | 2010-06-09 11:31:32 +0200 | [diff] [blame] | 193 | "subscriber_id INTEGER PRIMARY KEY, " |
Sylvain Munaut | 77d334a | 2009-12-27 19:26:12 +0100 | [diff] [blame] | 194 | "algorithm_id INTEGER NOT NULL, " |
Harald Welte | 3606cc5 | 2009-12-05 15:13:22 +0530 | [diff] [blame] | 195 | "a3a8_ki BLOB " |
| 196 | ")", |
Holger Hans Peter Freyther | e7cc9aa | 2014-03-07 18:17:22 +0100 | [diff] [blame] | 197 | [SCHEMA_AUTHLAST] = "CREATE TABLE IF NOT EXISTS AuthLastTuples (" |
Sylvain Munaut | 10bf812 | 2010-06-09 11:31:32 +0200 | [diff] [blame] | 198 | "subscriber_id INTEGER PRIMARY KEY, " |
Sylvain Munaut | 70881b7 | 2009-12-27 15:41:59 +0100 | [diff] [blame] | 199 | "issued TIMESTAMP NOT NULL, " |
| 200 | "use_count INTEGER NOT NULL DEFAULT 0, " |
| 201 | "key_seq INTEGER NOT NULL, " |
| 202 | "rand BLOB NOT NULL, " |
| 203 | "sres BLOB NOT NULL, " |
| 204 | "kc BLOB NOT NULL " |
Harald Welte | ffa55a4 | 2009-12-22 19:07:32 +0100 | [diff] [blame] | 205 | ")", |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 206 | }; |
| 207 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 208 | /*********************************************************************** |
| 209 | * PREPARED STATEMENTS |
| 210 | ***********************************************************************/ |
Neels Hofmeyr | db82729 | 2017-12-10 14:45:15 +0100 | [diff] [blame] | 211 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 212 | /* don't change this order as the code assumes this ordering when dereferencing |
| 213 | * database query results! */ |
| 214 | #define SEL_COLUMNS \ |
| 215 | "id," \ |
| 216 | "strftime('%s',created)," \ |
| 217 | "sent," \ |
| 218 | "deliver_attempts," \ |
| 219 | "strftime('%s', valid_until)," \ |
| 220 | "reply_path_req," \ |
| 221 | "status_rep_req," \ |
| 222 | "is_report," \ |
| 223 | "msg_ref," \ |
| 224 | "protocol_id," \ |
| 225 | "data_coding_scheme," \ |
| 226 | "ud_hdr_ind," \ |
| 227 | "src_addr," \ |
| 228 | "src_ton," \ |
| 229 | "src_npi," \ |
| 230 | "dest_addr," \ |
| 231 | "dest_ton," \ |
| 232 | "dest_npi," \ |
| 233 | "user_data," \ |
| 234 | "header," \ |
| 235 | "text" |
| 236 | |
| 237 | enum db_sms_column_idx { |
| 238 | COL_ID, |
| 239 | COL_CREATED, |
| 240 | COL_SENT, |
| 241 | COL_DELIVER_ATTEMPTS, |
| 242 | COL_VALID_UNTIL, |
| 243 | COL_REPLY_PATH_REQ, |
| 244 | COL_STATUS_REP_REQ, |
| 245 | COL_IS_REPORT, |
| 246 | COL_MSG_REF, |
| 247 | COL_PROTOCOL_ID, |
| 248 | COL_DATA_CODING_SCHEME, |
| 249 | COL_UD_HDR_IND, |
| 250 | COL_SRC_ADDR, |
| 251 | COL_SRC_TON, |
| 252 | COL_SRC_NPI, |
| 253 | COL_DEST_ADDR, |
| 254 | COL_DEST_TON, |
| 255 | COL_DEST_NPI, |
| 256 | COL_USER_DATA, |
| 257 | COL_HEADER, |
| 258 | COL_TEXT, |
| 259 | }; |
| 260 | |
| 261 | static const char *stmt_sql[] = { |
| 262 | [DB_STMT_SMS_STORE] = |
| 263 | "INSERT INTO SMS " |
| 264 | "(created, valid_until, reply_path_req, status_rep_req, is_report, " |
| 265 | " msg_ref, protocol_id, data_coding_scheme, ud_hdr_ind, user_data, text, " |
| 266 | " dest_addr, dest_ton, dest_npi, src_addr, src_ton, src_npi) " |
| 267 | "VALUES " |
| 268 | "(datetime($created, 'unixepoch'), datetime($valid_until, 'unixepoch'), " |
| 269 | "$reply_path_req, $status_rep_req, $is_report, " |
| 270 | "$msg_ref, $protocol_id, $data_coding_scheme, $ud_hdr_ind, $user_data, $text, " |
| 271 | "$dest_addr, $dest_ton, $dest_npi, $src_addr, $src_ton, $src_npi)", |
| 272 | [DB_STMT_SMS_GET] = "SELECT " SEL_COLUMNS " FROM SMS WHERE SMS.id = $id", |
| 273 | [DB_STMT_SMS_GET_NEXT_UNSENT] = |
| 274 | "SELECT " SEL_COLUMNS " FROM SMS" |
| 275 | " WHERE sent IS NULL" |
| 276 | " AND id >= $id" |
| 277 | " AND deliver_attempts <= $attempts" |
| 278 | " ORDER BY id LIMIT 1", |
| 279 | [DB_STMT_SMS_GET_UNSENT_FOR_SUBSCR] = |
| 280 | "SELECT " SEL_COLUMNS " FROM SMS" |
| 281 | " WHERE sent IS NULL" |
| 282 | " AND dest_addr = $dest_addr" |
| 283 | " AND deliver_attempts <= $attempts" |
| 284 | " ORDER BY id LIMIT 1", |
| 285 | [DB_STMT_SMS_GET_NEXT_UNSENT_RR_MSISDN] = |
| 286 | "SELECT " SEL_COLUMNS " FROM SMS" |
| 287 | " WHERE sent IS NULL" |
| 288 | " AND dest_addr > $dest_addr" |
| 289 | " AND deliver_attempts <= $attempts" |
| 290 | " ORDER BY dest_addr, id LIMIT 1", |
| 291 | [DB_STMT_SMS_MARK_DELIVERED] = |
| 292 | "UPDATE SMS " |
| 293 | " SET sent = datetime('now') " |
| 294 | " WHERE id = $id", |
| 295 | [DB_STMT_SMS_INC_DELIVER_ATTEMPTS] = |
| 296 | "UPDATE SMS " |
| 297 | " SET deliver_attempts = deliver_attempts + 1 " |
| 298 | " WHERE id = $id", |
| 299 | [DB_STMT_SMS_DEL_BY_MSISDN] = |
| 300 | "DELETE FROM SMS WHERE src_addr=$src_addr OR dest_addr=$dest_addr", |
| 301 | [DB_STMT_SMS_DEL_BY_ID] = |
| 302 | "DELETE FROM SMS WHERE id = $id AND sent is NOT NULL", |
| 303 | [DB_STMT_SMS_DEL_EXPIRED] = |
| 304 | "DELETE FROM SMS WHERE id = $id", |
| 305 | [DB_STMT_SMS_GET_VALID_UNTIL_BY_ID] = |
| 306 | "SELECT strftime('%s', valid_until) FROM SMS WHERE id = $id", |
| 307 | [DB_STMT_SMS_GET_OLDEST_EXPIRED] = |
| 308 | "SELECT id, strftime('%s', valid_until) FROM SMS ORDER BY valid_until LIMIT 1", |
| 309 | }; |
| 310 | |
| 311 | /*********************************************************************** |
| 312 | * libsqlite3 helpers |
| 313 | ***********************************************************************/ |
| 314 | |
| 315 | /* libsqlite3 call-back for error logging */ |
| 316 | static void sql3_error_log_cb(void *arg, int err_code, const char *msg) |
Harald Welte | 0b906d0 | 2009-12-24 11:21:42 +0100 | [diff] [blame] | 317 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 318 | LOGP(DDB, LOGL_ERROR, "SQLITE3: (%d) %s\n", err_code, msg); |
Harald Welte | c7548a1 | 2014-07-10 20:18:15 +0200 | [diff] [blame] | 319 | osmo_log_backtrace(DDB, LOGL_ERROR); |
Jan Luebbe | 7398eb9 | 2008-12-27 00:45:41 +0000 | [diff] [blame] | 320 | } |
| 321 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 322 | /* libsqlite3 call-back for normal logging */ |
| 323 | static void sql3_sql_log_cb(void *arg, sqlite3 *s3, const char *stmt, int type) |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 324 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 325 | switch (type) { |
| 326 | case 0: |
| 327 | LOGP(DDB, LOGL_DEBUG, "Opened database\n"); |
| 328 | break; |
| 329 | case 1: |
| 330 | LOGP(DDB, LOGL_DEBUG, "%s\n", stmt); |
| 331 | break; |
| 332 | case 2: |
| 333 | LOGP(DDB, LOGL_DEBUG, "Closed database\n"); |
| 334 | break; |
| 335 | default: |
| 336 | LOGP(DDB, LOGL_DEBUG, "Unknown %d\n", type); |
| 337 | break; |
| 338 | } |
| 339 | } |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 340 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 341 | /* remove statement bindings and reset statement to be re-executed */ |
| 342 | static void db_remove_reset(sqlite3_stmt *stmt) |
| 343 | { |
| 344 | sqlite3_clear_bindings(stmt); |
| 345 | /* sqlite3_reset() just repeats an error code already evaluated during sqlite3_step(). */ |
| 346 | /* coverity[CHECKED_RETURN] */ |
| 347 | sqlite3_reset(stmt); |
| 348 | } |
| 349 | |
| 350 | /** bind blob arg and do proper cleanup in case of failure. If param_name is |
| 351 | * NULL, bind to the first parameter (useful for SQL statements that have only |
| 352 | * one parameter). */ |
| 353 | static bool db_bind_blob(sqlite3_stmt *stmt, const char *param_name, |
| 354 | const uint8_t *blob, size_t blob_len) |
| 355 | { |
| 356 | int rc; |
| 357 | int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; |
| 358 | if (idx < 1) { |
| 359 | LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", |
| 360 | param_name); |
| 361 | return false; |
| 362 | } |
| 363 | rc = sqlite3_bind_blob(stmt, idx, blob, blob_len, SQLITE_STATIC); |
| 364 | if (rc != SQLITE_OK) { |
| 365 | LOGP(DDB, LOGL_ERROR, "Error binding blob to SQL parameter %s: %d\n", |
| 366 | param_name ? param_name : "#1", rc); |
| 367 | db_remove_reset(stmt); |
| 368 | return false; |
| 369 | } |
| 370 | return true; |
| 371 | } |
| 372 | |
| 373 | /** bind text arg and do proper cleanup in case of failure. If param_name is |
| 374 | * NULL, bind to the first parameter (useful for SQL statements that have only |
| 375 | * one parameter). */ |
| 376 | static bool db_bind_text(sqlite3_stmt *stmt, const char *param_name, const char *text) |
| 377 | { |
| 378 | int rc; |
| 379 | int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; |
| 380 | if (idx < 1) { |
| 381 | LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", |
| 382 | param_name); |
| 383 | return false; |
| 384 | } |
| 385 | rc = sqlite3_bind_text(stmt, idx, text, -1, SQLITE_STATIC); |
| 386 | if (rc != SQLITE_OK) { |
| 387 | LOGP(DDB, LOGL_ERROR, "Error binding text to SQL parameter %s: %d\n", |
| 388 | param_name ? param_name : "#1", rc); |
| 389 | db_remove_reset(stmt); |
| 390 | return false; |
| 391 | } |
| 392 | return true; |
| 393 | } |
| 394 | |
| 395 | /** bind int arg and do proper cleanup in case of failure. If param_name is |
| 396 | * NULL, bind to the first parameter (useful for SQL statements that have only |
| 397 | * one parameter). */ |
| 398 | static bool db_bind_int(sqlite3_stmt *stmt, const char *param_name, int nr) |
| 399 | { |
| 400 | int rc; |
| 401 | int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; |
| 402 | if (idx < 1) { |
| 403 | LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", |
| 404 | param_name); |
| 405 | return false; |
| 406 | } |
| 407 | rc = sqlite3_bind_int(stmt, idx, nr); |
| 408 | if (rc != SQLITE_OK) { |
| 409 | LOGP(DDB, LOGL_ERROR, "Error binding int64 to SQL parameter %s: %d\n", |
| 410 | param_name ? param_name : "#1", rc); |
| 411 | db_remove_reset(stmt); |
| 412 | return false; |
| 413 | } |
| 414 | return true; |
| 415 | } |
| 416 | |
| 417 | /** bind int64 arg and do proper cleanup in case of failure. If param_name is |
| 418 | * NULL, bind to the first parameter (useful for SQL statements that have only |
| 419 | * one parameter). */ |
| 420 | static bool db_bind_int64(sqlite3_stmt *stmt, const char *param_name, int64_t nr) |
| 421 | { |
| 422 | int rc; |
| 423 | int idx = param_name ? sqlite3_bind_parameter_index(stmt, param_name) : 1; |
| 424 | if (idx < 1) { |
| 425 | LOGP(DDB, LOGL_ERROR, "Error composing SQL, cannot bind parameter '%s'\n", |
| 426 | param_name); |
| 427 | return false; |
| 428 | } |
| 429 | rc = sqlite3_bind_int64(stmt, idx, nr); |
| 430 | if (rc != SQLITE_OK) { |
| 431 | LOGP(DDB, LOGL_ERROR, "Error binding int64 to SQL parameter %s: %d\n", |
| 432 | param_name ? param_name : "#1", rc); |
| 433 | db_remove_reset(stmt); |
| 434 | return false; |
| 435 | } |
| 436 | return true; |
| 437 | } |
| 438 | |
| 439 | /* callback for sqlite3_exec() below */ |
| 440 | static int db_rev_exec_cb(void *priv, int num_cols, char **vals, char **names) |
| 441 | { |
| 442 | char **rev_s = priv; |
| 443 | OSMO_ASSERT(!strcmp(names[0], "value")); |
| 444 | *rev_s = talloc_strdup(NULL, vals[0]); |
| 445 | return 0; |
| 446 | } |
| 447 | |
| 448 | static int check_db_revision(struct db_context *dbc) |
| 449 | { |
| 450 | char *errstr = NULL; |
| 451 | char *rev_s; |
| 452 | int db_rev = 0; |
| 453 | int rc; |
| 454 | |
| 455 | /* Make a query */ |
| 456 | rc = sqlite3_exec(dbc->db, "SELECT value FROM Meta WHERE key = 'revision'", |
| 457 | db_rev_exec_cb, &rev_s, &errstr); |
| 458 | if (rc != SQLITE_OK) { |
| 459 | LOGP(DDB, LOGL_ERROR, "Cannot execute SELECT value from META: %s\n", errstr); |
| 460 | sqlite3_free(errstr); |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 461 | return -EINVAL; |
| 462 | } |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 463 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 464 | if (!strcmp(rev_s, SCHEMA_REVISION)) { |
| 465 | /* Everything is fine */ |
| 466 | talloc_free(rev_s); |
| 467 | return 0; |
| 468 | } |
| 469 | |
| 470 | LOGP(DDB, LOGL_NOTICE, "Detected DB Revision %s, expected %s\n", rev_s, SCHEMA_REVISION); |
| 471 | |
| 472 | db_rev = atoi(rev_s); |
| 473 | talloc_free(rev_s); |
| 474 | |
| 475 | /* Incremental migration waterfall */ |
| 476 | switch (db_rev) { |
| 477 | case 2: |
| 478 | case 3: |
| 479 | case 4: |
| 480 | LOGP(DDB, LOGL_FATAL, "You must use osmo-msc 1.1.0 to 1.8.0 to upgrade database " |
| 481 | "schema from '%u' to '5', sorry\n", db_rev); |
| 482 | break; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 483 | case 5: |
Keith Whyte | ea62986 | 2022-06-18 02:51:51 +0100 | [diff] [blame] | 484 | LOGP(DDB, LOGL_FATAL, "The storage format of BINARY data in the database " |
| 485 | "has changed. In order to deliver any pending SMS in your database, " |
| 486 | "you must manually convert your database from " |
| 487 | "'%u' to '6'. Alternatively you can use a fresh, blank database " |
| 488 | "with this version of osmo-msc, sorry.\n", db_rev); |
| 489 | return -1; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 490 | break; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 491 | default: |
| 492 | LOGP(DDB, LOGL_FATAL, "Invalid database schema revision '%d'.\n", db_rev); |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 493 | return -EINVAL; |
| 494 | } |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 495 | |
| 496 | return 0; |
| 497 | |
| 498 | //error: |
| 499 | LOGP(DDB, LOGL_FATAL, "Failed to update database from schema revision '%d'.\n", db_rev); |
| 500 | talloc_free(rev_s); |
| 501 | |
| 502 | return -EINVAL; |
| 503 | } |
| 504 | |
| 505 | /*********************************************************************** |
| 506 | * USER API |
| 507 | ***********************************************************************/ |
| 508 | |
| 509 | int db_init(void *ctx, const char *fname, bool enable_sqlite_logging) |
| 510 | { |
| 511 | unsigned int i; |
| 512 | int rc; |
| 513 | bool has_sqlite_config_sqllog = false; |
| 514 | |
| 515 | g_dbc = talloc_zero(ctx, struct db_context); |
| 516 | OSMO_ASSERT(g_dbc); |
| 517 | |
| 518 | /* we are a single-threaded program; we want to avoid all the mutex/etc. overhead */ |
| 519 | sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); |
| 520 | |
| 521 | LOGP(DDB, LOGL_NOTICE, "Init database connection to '%s' using SQLite3 lib version %s\n", |
| 522 | fname, sqlite3_libversion()); |
| 523 | |
| 524 | g_dbc->fname = talloc_strdup(g_dbc, fname); |
| 525 | |
| 526 | for (i = 0; i < 0xfffff; i++) { |
| 527 | const char *o = sqlite3_compileoption_get(i); |
| 528 | if (!o) |
| 529 | break; |
| 530 | LOGP(DDB, LOGL_DEBUG, "SQLite3 compiled with '%s'\n", o); |
| 531 | if (!strcmp(o, "ENABLE_SQLLOG")) |
| 532 | has_sqlite_config_sqllog = true; |
| 533 | } |
| 534 | |
| 535 | if (enable_sqlite_logging) { |
| 536 | rc = sqlite3_config(SQLITE_CONFIG_LOG, sql3_error_log_cb, NULL); |
| 537 | if (rc != SQLITE_OK) |
| 538 | LOGP(DDB, LOGL_NOTICE, "Unable to set SQLite3 error log callback\n"); |
| 539 | } |
| 540 | |
| 541 | if (has_sqlite_config_sqllog) { |
| 542 | rc = sqlite3_config(SQLITE_CONFIG_SQLLOG, sql3_sql_log_cb, NULL); |
| 543 | if (rc != SQLITE_OK) |
| 544 | LOGP(DDB, LOGL_NOTICE, "Unable to set SQLite3 SQL log callback\n"); |
| 545 | } else { |
| 546 | LOGP(DDB, LOGL_DEBUG, "Not setting SQL log callback:" |
| 547 | " SQLite3 compiled without support for it\n"); |
| 548 | } |
| 549 | |
| 550 | rc = sqlite3_open(g_dbc->fname, &g_dbc->db); |
| 551 | if (rc != SQLITE_OK) { |
| 552 | LOGP(DDB, LOGL_ERROR, "Unable to open DB; rc =%d\n", rc); |
| 553 | talloc_free(g_dbc); |
| 554 | return -1; |
| 555 | } |
| 556 | |
| 557 | /* enable extended result codes */ |
| 558 | rc = sqlite3_extended_result_codes(g_dbc->db, 1); |
| 559 | if (rc != SQLITE_OK) { |
| 560 | LOGP(DDB, LOGL_ERROR, "Unable to enable SQLite3 extended result codes\n"); |
| 561 | /* non-fatal */ |
| 562 | } |
| 563 | |
| 564 | char *err_msg; |
Harald Welte | 1d72e30 | 2022-05-17 11:26:06 +0200 | [diff] [blame] | 565 | rc = sqlite3_exec(g_dbc->db, "PRAGMA journal_mode=WAL; PRAGMA synchronous = NORMAL;", 0, 0, &err_msg); |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 566 | if (rc != SQLITE_OK) { |
| 567 | LOGP(DDB, LOGL_ERROR, "Unable to set Write-Ahead Logging: %s\n", err_msg); |
| 568 | sqlite3_free(err_msg); |
| 569 | /* non-fatal */ |
| 570 | } |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 571 | |
Keith Whyte | 99bd0f3 | 2022-06-17 20:39:53 +0100 | [diff] [blame] | 572 | rc = sqlite3_exec(g_dbc->db, "PRAGMA secure_delete=0;", 0, 0, &err_msg); |
| 573 | if (rc != SQLITE_OK) { |
| 574 | LOGP(DDB, LOGL_ERROR, "Unable to disable SECURE_DELETE: %s\n", err_msg); |
| 575 | sqlite3_free(err_msg); |
| 576 | /* non-fatal */ |
| 577 | } |
| 578 | |
Jan Luebbe | bfbdeec | 2012-12-27 00:27:16 +0100 | [diff] [blame] | 579 | return 0; |
| 580 | } |
| 581 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 582 | int db_fini(void) |
| 583 | { |
| 584 | unsigned int i; |
| 585 | int rc; |
| 586 | |
| 587 | if (!g_dbc) |
| 588 | return 0; |
| 589 | |
| 590 | for (i = 0; i < ARRAY_SIZE(g_dbc->stmt); i++) { |
| 591 | /* it is ok to call finalize on NULL */ |
| 592 | sqlite3_finalize(g_dbc->stmt[i]); |
| 593 | } |
| 594 | |
| 595 | /* Ask sqlite3 to close DB */ |
| 596 | rc = sqlite3_close(g_dbc->db); |
| 597 | if (rc != SQLITE_OK) { /* Make sure it's actually closed! */ |
| 598 | LOGP(DDB, LOGL_ERROR, "Couldn't close database: (rc=%d) %s\n", |
| 599 | rc, sqlite3_errmsg(g_dbc->db)); |
| 600 | } |
| 601 | |
| 602 | talloc_free(g_dbc); |
| 603 | g_dbc = NULL; |
| 604 | |
| 605 | return 0; |
| 606 | } |
| 607 | |
| 608 | /* run (execute) a series of SQL statements */ |
| 609 | static int db_run_statements(struct db_context *dbc, const char **statements, size_t statements_count) |
| 610 | { |
| 611 | int i; |
| 612 | for (i = 0; i < statements_count; i++) { |
| 613 | const char *stmt_str = statements[i]; |
| 614 | char *errmsg = NULL; |
| 615 | int rc; |
| 616 | |
| 617 | rc = sqlite3_exec(dbc->db, stmt_str, NULL, NULL, &errmsg); |
| 618 | if (rc != SQLITE_OK) { |
| 619 | LOGP(DDB, LOGL_ERROR, "SQL error during SQL statement '%s': %s\n", stmt_str, errmsg); |
| 620 | sqlite3_free(errmsg); |
| 621 | return -1; |
| 622 | } |
| 623 | } |
| 624 | return 0; |
| 625 | } |
| 626 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 627 | int db_prepare(void) |
| 628 | { |
| 629 | unsigned int i; |
| 630 | int rc; |
| 631 | |
| 632 | OSMO_ASSERT(g_dbc); |
| 633 | rc = db_run_statements(g_dbc, create_stmts, ARRAY_SIZE(create_stmts)); |
| 634 | if (rc < 0) { |
| 635 | LOGP(DDB, LOGL_ERROR, "Failed to create some table.\n"); |
| 636 | return 1; |
| 637 | } |
| 638 | |
| 639 | if (check_db_revision(g_dbc) < 0) { |
| 640 | LOGP(DDB, LOGL_FATAL, "Database schema revision invalid, " |
| 641 | "please update your database schema\n"); |
| 642 | return -1; |
| 643 | } |
| 644 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 645 | /* prepare all SQL statements */ |
| 646 | for (i = 0; i < ARRAY_SIZE(g_dbc->stmt); i++) { |
| 647 | rc = sqlite3_prepare_v2(g_dbc->db, stmt_sql[i], -1, |
| 648 | &g_dbc->stmt[i], NULL); |
| 649 | if (rc != SQLITE_OK) { |
| 650 | LOGP(DDB, LOGL_ERROR, "Unable to prepare SQL statement '%s'\n", stmt_sql[i]); |
| 651 | return -1; |
| 652 | } |
| 653 | } |
| 654 | |
| 655 | return 0; |
| 656 | } |
| 657 | |
| 658 | /* store an [unsent] SMS to the database */ |
| 659 | int db_sms_store(struct gsm_sms *sms) |
| 660 | { |
| 661 | OSMO_ASSERT(g_dbc); |
| 662 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_STORE]; |
| 663 | time_t now, validity_timestamp; |
| 664 | int rc; |
| 665 | |
| 666 | now = time(NULL); |
| 667 | validity_timestamp = now + sms->validity_minutes * 60; |
| 668 | |
| 669 | db_bind_int64(stmt, "$created", (int64_t) now); |
| 670 | db_bind_int64(stmt, "$valid_until", (int64_t) validity_timestamp); |
| 671 | db_bind_int(stmt, "$reply_path_req", sms->reply_path_req); |
| 672 | db_bind_int(stmt, "$status_rep_req", sms->status_rep_req); |
| 673 | db_bind_int(stmt, "$is_report", sms->is_report); |
| 674 | db_bind_int(stmt, "$msg_ref", sms->msg_ref); |
| 675 | db_bind_int(stmt, "$protocol_id", sms->protocol_id); |
| 676 | db_bind_int(stmt, "$data_coding_scheme", sms->data_coding_scheme); |
| 677 | db_bind_int(stmt, "$ud_hdr_ind", sms->ud_hdr_ind); |
| 678 | /* FIXME: do we need to use legacy DBI compatible quoting of sms->user_data? */ |
| 679 | db_bind_blob(stmt, "$user_data", sms->user_data, sms->user_data_len); |
| 680 | db_bind_text(stmt, "$text", (char *)sms->text); |
| 681 | db_bind_text(stmt, "$dest_addr", (char *)sms->dst.addr); |
| 682 | db_bind_int(stmt, "$dest_ton", sms->dst.ton); |
| 683 | db_bind_int(stmt, "$dest_npi", sms->dst.npi); |
| 684 | db_bind_text(stmt, "$src_addr", (char *)sms->src.addr); |
| 685 | db_bind_int(stmt, "$src_ton", sms->src.ton); |
| 686 | db_bind_int(stmt, "$src_npi", sms->src.npi); |
| 687 | |
| 688 | /* execute statement */ |
| 689 | rc = sqlite3_step(stmt); |
| 690 | db_remove_reset(stmt); |
| 691 | if (rc != SQLITE_DONE) { |
| 692 | LOGP(DDB, LOGL_ERROR, "Cannot create SMS: SQL error: (%d) %s\n", rc, sqlite3_errmsg(g_dbc->db)); |
| 693 | return -EIO; |
| 694 | } |
| 695 | |
| 696 | sms->id = sqlite3_last_insert_rowid(g_dbc->db); |
| 697 | |
| 698 | LOGP(DLSMS, LOGL_INFO, "Stored SMS id=%llu in DB\n", sms->id); |
| 699 | |
| 700 | return 0; |
| 701 | } |
| 702 | |
| 703 | static void parse_tp_ud_from_result(struct gsm_sms *sms, sqlite3_stmt *stmt) |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 704 | { |
| 705 | const unsigned char *user_data; |
| 706 | unsigned int user_data_len; |
Vadim Yanitskiy | 0d13e83 | 2019-04-02 18:25:08 +0700 | [diff] [blame] | 707 | unsigned int text_len; |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 708 | const char *text; |
| 709 | |
| 710 | /* Retrieve TP-UDL (User-Data-Length) in octets (regardless of DCS) */ |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 711 | user_data_len = sqlite3_column_bytes(stmt, COL_USER_DATA); |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 712 | if (user_data_len > sizeof(sms->user_data)) { |
| 713 | LOGP(DDB, LOGL_ERROR, |
| 714 | "SMS TP-UD length %u is too big, truncating to %zu\n", |
| 715 | user_data_len, sizeof(sms->user_data)); |
| 716 | user_data_len = (uint8_t) sizeof(sms->user_data); |
| 717 | } |
| 718 | sms->user_data_len = user_data_len; |
| 719 | |
| 720 | /* Retrieve the TP-UD (User-Data) itself */ |
| 721 | if (user_data_len > 0) { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 722 | user_data = sqlite3_column_blob(stmt, COL_USER_DATA); |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 723 | memcpy(sms->user_data, user_data, user_data_len); |
| 724 | } |
| 725 | |
Vadim Yanitskiy | 0d13e83 | 2019-04-02 18:25:08 +0700 | [diff] [blame] | 726 | /* Retrieve the text length (excluding '\0') */ |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 727 | text_len = sqlite3_column_bytes(stmt, COL_TEXT); |
Vadim Yanitskiy | 0d13e83 | 2019-04-02 18:25:08 +0700 | [diff] [blame] | 728 | if (text_len >= sizeof(sms->text)) { |
| 729 | LOGP(DDB, LOGL_ERROR, |
| 730 | "SMS text length %u is too big, truncating to %zu\n", |
| 731 | text_len, sizeof(sms->text) - 1); |
| 732 | /* OSMO_STRLCPY_ARRAY() does truncation for us */ |
| 733 | } |
| 734 | |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 735 | /* Retrieve the text parsed from TP-UD (User-Data) */ |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 736 | text = (const char *)sqlite3_column_text(stmt, COL_TEXT); |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 737 | if (text) |
| 738 | OSMO_STRLCPY_ARRAY(sms->text, text); |
| 739 | } |
| 740 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 741 | static struct gsm_sms *sms_from_result(struct gsm_network *net, sqlite3_stmt *stmt) |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 742 | { |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 743 | struct gsm_sms *sms = sms_alloc(); |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 744 | const char *daddr, *saddr; |
Stefan Sperling | d494162 | 2018-01-18 17:36:28 +0100 | [diff] [blame] | 745 | time_t validity_timestamp; |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 746 | |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 747 | if (!sms) |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 748 | return NULL; |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 749 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 750 | sms->id = sqlite3_column_int64(stmt, COL_ID); |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 751 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 752 | sms->created = sqlite3_column_int64(stmt, COL_CREATED); |
| 753 | validity_timestamp = sqlite3_column_int64(stmt, COL_VALID_UNTIL); |
| 754 | |
Stefan Sperling | d494162 | 2018-01-18 17:36:28 +0100 | [diff] [blame] | 755 | sms->validity_minutes = (validity_timestamp - sms->created) / 60; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 756 | sms->reply_path_req = sqlite3_column_int(stmt, COL_REPLY_PATH_REQ); |
| 757 | sms->status_rep_req = sqlite3_column_int(stmt, COL_STATUS_REP_REQ); |
| 758 | sms->is_report = sqlite3_column_int(stmt, COL_IS_REPORT); |
| 759 | sms->msg_ref = sqlite3_column_int(stmt, COL_MSG_REF); |
| 760 | sms->ud_hdr_ind = sqlite3_column_int(stmt, COL_UD_HDR_IND); |
| 761 | sms->protocol_id = sqlite3_column_int(stmt, COL_PROTOCOL_ID); |
| 762 | sms->data_coding_scheme = sqlite3_column_int(stmt, COL_DATA_CODING_SCHEME); |
Harald Welte | f3efc59 | 2009-07-27 20:11:35 +0200 | [diff] [blame] | 763 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 764 | sms->dst.npi = sqlite3_column_int(stmt, COL_DEST_NPI); |
| 765 | sms->dst.ton = sqlite3_column_int(stmt, COL_DEST_TON); |
| 766 | daddr = (const char *)sqlite3_column_text(stmt, COL_DEST_ADDR); |
Neels Hofmeyr | 93bafb6 | 2017-01-13 03:12:08 +0100 | [diff] [blame] | 767 | if (daddr) |
Max | 98f7467 | 2018-02-05 12:57:06 +0100 | [diff] [blame] | 768 | OSMO_STRLCPY_ARRAY(sms->dst.addr, daddr); |
Vadim Yanitskiy | e1e7247 | 2019-04-09 16:55:44 +0700 | [diff] [blame] | 769 | |
| 770 | if (net != NULL) /* db_sms_test passes NULL, so we need to be tolerant */ |
| 771 | sms->receiver = vlr_subscr_find_by_msisdn(net->vlr, sms->dst.addr, |
| 772 | VSUB_USE_SMS_RECEIVER); |
Harald Welte | 7604218 | 2009-08-08 16:03:15 +0200 | [diff] [blame] | 773 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 774 | sms->src.npi = sqlite3_column_int(stmt, COL_SRC_NPI); |
| 775 | sms->src.ton = sqlite3_column_int(stmt, COL_SRC_TON); |
| 776 | saddr = (const char *)sqlite3_column_text(stmt, COL_SRC_ADDR); |
Neels Hofmeyr | 93bafb6 | 2017-01-13 03:12:08 +0100 | [diff] [blame] | 777 | if (saddr) |
Max | 98f7467 | 2018-02-05 12:57:06 +0100 | [diff] [blame] | 778 | OSMO_STRLCPY_ARRAY(sms->src.addr, saddr); |
Holger Hans Peter Freyther | ca3c256 | 2013-10-08 03:17:30 +0200 | [diff] [blame] | 779 | |
Vadim Yanitskiy | ea24bb5 | 2019-04-02 17:52:08 +0700 | [diff] [blame] | 780 | /* Parse TP-UD, TP-UDL and decoded text */ |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 781 | parse_tp_ud_from_result(sms, stmt); |
Harald Welte | be3e378 | 2009-07-05 14:06:41 +0200 | [diff] [blame] | 782 | |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 783 | return sms; |
| 784 | } |
| 785 | |
Holger Hans Peter Freyther | 812dad0 | 2010-12-24 23:18:31 +0100 | [diff] [blame] | 786 | struct gsm_sms *db_sms_get(struct gsm_network *net, unsigned long long id) |
| 787 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 788 | OSMO_ASSERT(g_dbc); |
| 789 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET]; |
Holger Hans Peter Freyther | 812dad0 | 2010-12-24 23:18:31 +0100 | [diff] [blame] | 790 | struct gsm_sms *sms; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 791 | int rc; |
Holger Hans Peter Freyther | 812dad0 | 2010-12-24 23:18:31 +0100 | [diff] [blame] | 792 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 793 | db_bind_int64(stmt, "$id", id); |
Holger Hans Peter Freyther | 812dad0 | 2010-12-24 23:18:31 +0100 | [diff] [blame] | 794 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 795 | rc = sqlite3_step(stmt); |
| 796 | if (rc != SQLITE_ROW) { |
| 797 | db_remove_reset(stmt); |
Holger Hans Peter Freyther | 812dad0 | 2010-12-24 23:18:31 +0100 | [diff] [blame] | 798 | return NULL; |
| 799 | } |
| 800 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 801 | sms = sms_from_result(net, stmt); |
Holger Hans Peter Freyther | 812dad0 | 2010-12-24 23:18:31 +0100 | [diff] [blame] | 802 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 803 | db_remove_reset(stmt); |
Holger Hans Peter Freyther | 812dad0 | 2010-12-24 23:18:31 +0100 | [diff] [blame] | 804 | return sms; |
| 805 | } |
| 806 | |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 807 | struct gsm_sms *db_sms_get_next_unsent(struct gsm_network *net, |
| 808 | unsigned long long min_sms_id, |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 809 | int max_failed) |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 810 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 811 | OSMO_ASSERT(g_dbc); |
| 812 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_NEXT_UNSENT]; |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 813 | struct gsm_sms *sms; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 814 | int rc; |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 815 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 816 | db_bind_int64(stmt, "$id", min_sms_id); |
| 817 | db_bind_int(stmt, "$attempts", max_failed); |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 818 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 819 | rc = sqlite3_step(stmt); |
| 820 | if (rc != SQLITE_ROW) { |
| 821 | db_remove_reset(stmt); |
Sylvain Munaut | ff1f19e | 2009-12-22 13:22:29 +0100 | [diff] [blame] | 822 | return NULL; |
| 823 | } |
| 824 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 825 | sms = sms_from_result(net, stmt); |
Sylvain Munaut | ff1f19e | 2009-12-22 13:22:29 +0100 | [diff] [blame] | 826 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 827 | db_remove_reset(stmt); |
Sylvain Munaut | ff1f19e | 2009-12-22 13:22:29 +0100 | [diff] [blame] | 828 | return sms; |
| 829 | } |
| 830 | |
Sylvain Munaut | d5778fc | 2009-12-21 01:09:57 +0100 | [diff] [blame] | 831 | /* retrieve the next unsent SMS for a given subscriber */ |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 832 | struct gsm_sms *db_sms_get_unsent_for_subscr(struct vlr_subscr *vsub, |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 833 | int max_failed) |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 834 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 835 | OSMO_ASSERT(g_dbc); |
| 836 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_UNSENT_FOR_SUBSCR]; |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 837 | struct gsm_network *net = vsub->vlr->user_ctx; |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 838 | struct gsm_sms *sms; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 839 | int rc; |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 840 | |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 841 | if (!vsub->lu_complete) |
| 842 | return NULL; |
| 843 | |
Neels Hofmeyr | f6704f1 | 2017-12-05 12:34:44 +0100 | [diff] [blame] | 844 | /* A subscriber having no phone number cannot possibly receive SMS. */ |
| 845 | if (*vsub->msisdn == '\0') |
| 846 | return NULL; |
| 847 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 848 | db_bind_text(stmt, "$dest_addr", vsub->msisdn); |
| 849 | db_bind_int(stmt, "$attempts", max_failed); |
Neels Hofmeyr | 50d0900 | 2017-12-05 12:39:00 +0100 | [diff] [blame] | 850 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 851 | rc = sqlite3_step(stmt); |
| 852 | if (rc != SQLITE_ROW) { |
| 853 | db_remove_reset(stmt); |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 854 | return NULL; |
| 855 | } |
| 856 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 857 | sms = sms_from_result(net, stmt); |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 858 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 859 | db_remove_reset(stmt); |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 860 | return sms; |
| 861 | } |
| 862 | |
| 863 | struct gsm_sms *db_sms_get_next_unsent_rr_msisdn(struct gsm_network *net, |
| 864 | const char *last_msisdn, |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 865 | int max_failed) |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 866 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 867 | OSMO_ASSERT(g_dbc); |
| 868 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_NEXT_UNSENT_RR_MSISDN]; |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 869 | struct gsm_sms *sms; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 870 | int rc; |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 871 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 872 | db_bind_text(stmt, "$dest_addr", last_msisdn); |
| 873 | db_bind_int(stmt, "$attempts", max_failed); |
Neels Hofmeyr | 50d0900 | 2017-12-05 12:39:00 +0100 | [diff] [blame] | 874 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 875 | rc = sqlite3_step(stmt); |
| 876 | if (rc != SQLITE_ROW) { |
| 877 | db_remove_reset(stmt); |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 878 | return NULL; |
| 879 | } |
| 880 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 881 | sms = sms_from_result(net, stmt); |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 882 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 883 | db_remove_reset(stmt); |
Harald Welte | 2ebabca | 2009-08-09 19:05:21 +0200 | [diff] [blame] | 884 | |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 885 | return sms; |
| 886 | } |
| 887 | |
Alexander Chemeris | 1e77e3d | 2014-03-08 21:27:37 +0100 | [diff] [blame] | 888 | /* mark a given SMS as delivered */ |
| 889 | int db_sms_mark_delivered(struct gsm_sms *sms) |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 890 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 891 | sqlite3_stmt *stmt; |
| 892 | int rc; |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 893 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 894 | /* this only happens in unit tests that don't db_init() */ |
| 895 | if (!g_dbc) |
| 896 | return 0; |
| 897 | |
| 898 | stmt = g_dbc->stmt[DB_STMT_SMS_MARK_DELIVERED]; |
| 899 | db_bind_int64(stmt, "$id", sms->id); |
| 900 | |
| 901 | rc = sqlite3_step(stmt); |
| 902 | if (rc != SQLITE_DONE) { |
| 903 | db_remove_reset(stmt); |
Harald Welte | ae1f159 | 2009-12-24 11:39:14 +0100 | [diff] [blame] | 904 | LOGP(DDB, LOGL_ERROR, "Failed to mark SMS %llu as sent.\n", sms->id); |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 905 | return 1; |
| 906 | } |
| 907 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 908 | db_remove_reset(stmt); |
Harald Welte | 7e310b1 | 2009-03-30 20:56:32 +0000 | [diff] [blame] | 909 | return 0; |
| 910 | } |
Harald Welte (local) | db552c5 | 2009-08-15 20:15:14 +0200 | [diff] [blame] | 911 | |
| 912 | /* increase the number of attempted deliveries */ |
| 913 | int db_sms_inc_deliver_attempts(struct gsm_sms *sms) |
| 914 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 915 | sqlite3_stmt *stmt; |
| 916 | int rc; |
Harald Welte (local) | db552c5 | 2009-08-15 20:15:14 +0200 | [diff] [blame] | 917 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 918 | /* this only happens in unit tests that don't db_init() */ |
| 919 | if (!g_dbc) |
| 920 | return 0; |
| 921 | |
| 922 | stmt = g_dbc->stmt[DB_STMT_SMS_INC_DELIVER_ATTEMPTS]; |
| 923 | db_bind_int64(stmt, "$id", sms->id); |
| 924 | |
| 925 | rc = sqlite3_step(stmt); |
| 926 | if (rc != SQLITE_DONE) { |
| 927 | db_remove_reset(stmt); |
| 928 | LOGP(DDB, LOGL_ERROR, "Failed to inc deliver attempts for SMS %llu.\n", sms->id); |
Harald Welte (local) | db552c5 | 2009-08-15 20:15:14 +0200 | [diff] [blame] | 929 | return 1; |
| 930 | } |
| 931 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 932 | db_remove_reset(stmt); |
Harald Welte (local) | db552c5 | 2009-08-15 20:15:14 +0200 | [diff] [blame] | 933 | return 0; |
| 934 | } |
Harald Welte (local) | 026531e | 2009-08-16 10:40:10 +0200 | [diff] [blame] | 935 | |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 936 | /* Drop all pending SMS to or from the given extension */ |
| 937 | int db_sms_delete_by_msisdn(const char *msisdn) |
Harald Welte (local) | 026531e | 2009-08-16 10:40:10 +0200 | [diff] [blame] | 938 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 939 | OSMO_ASSERT(g_dbc); |
| 940 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_DEL_BY_MSISDN]; |
| 941 | int rc; |
| 942 | |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 943 | if (!msisdn || !*msisdn) |
| 944 | return 0; |
Neels Hofmeyr | 50d0900 | 2017-12-05 12:39:00 +0100 | [diff] [blame] | 945 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 946 | db_bind_text(stmt, "$src_addr", msisdn); |
| 947 | db_bind_text(stmt, "$dest_addr", msisdn); |
Neels Hofmeyr | 50d0900 | 2017-12-05 12:39:00 +0100 | [diff] [blame] | 948 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 949 | rc = sqlite3_step(stmt); |
| 950 | if (rc != SQLITE_DONE) { |
| 951 | db_remove_reset(stmt); |
| 952 | LOGP(DDB, LOGL_ERROR, "Failed to delete SMS for %s\n", msisdn); |
Harald Welte | 2483f1b | 2016-06-19 18:06:02 +0200 | [diff] [blame] | 953 | return -1; |
| 954 | } |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 955 | |
| 956 | db_remove_reset(stmt); |
Harald Welte (local) | 026531e | 2009-08-16 10:40:10 +0200 | [diff] [blame] | 957 | return 0; |
| 958 | } |
Harald Welte | ffa55a4 | 2009-12-22 19:07:32 +0100 | [diff] [blame] | 959 | |
Stefan Sperling | 6ba2d5a | 2018-01-18 18:55:26 +0100 | [diff] [blame] | 960 | int db_sms_delete_sent_message_by_id(unsigned long long sms_id) |
| 961 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 962 | OSMO_ASSERT(g_dbc); |
| 963 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_DEL_BY_ID]; |
| 964 | int rc; |
Stefan Sperling | 6ba2d5a | 2018-01-18 18:55:26 +0100 | [diff] [blame] | 965 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 966 | db_bind_int64(stmt, "$id", sms_id); |
| 967 | |
| 968 | rc = sqlite3_step(stmt); |
| 969 | if (rc != SQLITE_DONE) { |
| 970 | db_remove_reset(stmt); |
Stefan Sperling | 6ba2d5a | 2018-01-18 18:55:26 +0100 | [diff] [blame] | 971 | LOGP(DDB, LOGL_ERROR, "Failed to delete SMS %llu.\n", sms_id); |
| 972 | return 1; |
| 973 | } |
| 974 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 975 | db_remove_reset(stmt); |
Stefan Sperling | 6ba2d5a | 2018-01-18 18:55:26 +0100 | [diff] [blame] | 976 | return 0; |
| 977 | } |
| 978 | |
Vadim Yanitskiy | 570b4c8 | 2019-04-14 16:39:12 +0700 | [diff] [blame] | 979 | static int delete_expired_sms(unsigned long long sms_id, time_t validity_timestamp) |
Stefan Sperling | 87cba1f | 2018-01-22 17:05:37 +0100 | [diff] [blame] | 980 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 981 | OSMO_ASSERT(g_dbc); |
| 982 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_DEL_EXPIRED]; |
Vadim Yanitskiy | 570b4c8 | 2019-04-14 16:39:12 +0700 | [diff] [blame] | 983 | time_t now; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 984 | int rc; |
Stefan Sperling | 87cba1f | 2018-01-22 17:05:37 +0100 | [diff] [blame] | 985 | |
| 986 | now = time(NULL); |
Stefan Sperling | 87cba1f | 2018-01-22 17:05:37 +0100 | [diff] [blame] | 987 | |
Vadim Yanitskiy | 570b4c8 | 2019-04-14 16:39:12 +0700 | [diff] [blame] | 988 | /* Net yet expired */ |
| 989 | if (validity_timestamp > now) |
Stefan Sperling | 87cba1f | 2018-01-22 17:05:37 +0100 | [diff] [blame] | 990 | return -1; |
| 991 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 992 | db_bind_int64(stmt, "$id", sms_id); |
| 993 | |
| 994 | rc = sqlite3_step(stmt); |
| 995 | if (rc != SQLITE_DONE) { |
| 996 | db_remove_reset(stmt); |
Stefan Sperling | 87cba1f | 2018-01-22 17:05:37 +0100 | [diff] [blame] | 997 | LOGP(DDB, LOGL_ERROR, "Failed to delete SMS %llu.\n", sms_id); |
| 998 | return -1; |
| 999 | } |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1000 | |
| 1001 | db_remove_reset(stmt); |
Stefan Sperling | 87cba1f | 2018-01-22 17:05:37 +0100 | [diff] [blame] | 1002 | return 0; |
| 1003 | } |
| 1004 | |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1005 | int db_sms_delete_expired_message_by_id(unsigned long long sms_id) |
| 1006 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1007 | OSMO_ASSERT(g_dbc); |
| 1008 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_VALID_UNTIL_BY_ID]; |
Vadim Yanitskiy | 570b4c8 | 2019-04-14 16:39:12 +0700 | [diff] [blame] | 1009 | time_t validity_timestamp; |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1010 | int rc; |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1011 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1012 | db_bind_int64(stmt, "$id", sms_id); |
| 1013 | |
| 1014 | rc = sqlite3_step(stmt); |
| 1015 | if (rc != SQLITE_ROW) { |
| 1016 | db_remove_reset(stmt); |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1017 | return -1; |
| 1018 | } |
| 1019 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1020 | validity_timestamp = sqlite3_column_int64(stmt, 0); |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1021 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1022 | db_remove_reset(stmt); |
Vadim Yanitskiy | 570b4c8 | 2019-04-14 16:39:12 +0700 | [diff] [blame] | 1023 | return delete_expired_sms(sms_id, validity_timestamp); |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1024 | } |
| 1025 | |
| 1026 | void db_sms_delete_oldest_expired_message(void) |
| 1027 | { |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1028 | OSMO_ASSERT(g_dbc); |
| 1029 | sqlite3_stmt *stmt = g_dbc->stmt[DB_STMT_SMS_GET_OLDEST_EXPIRED]; |
| 1030 | int rc; |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1031 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1032 | rc = sqlite3_step(stmt); |
| 1033 | if (rc == SQLITE_ROW) { |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1034 | unsigned long long sms_id; |
Vadim Yanitskiy | 570b4c8 | 2019-04-14 16:39:12 +0700 | [diff] [blame] | 1035 | time_t validity_timestamp; |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1036 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1037 | sms_id = sqlite3_column_int64(stmt, 0); |
| 1038 | validity_timestamp = sqlite3_column_int64(stmt, 1); |
Vadim Yanitskiy | 570b4c8 | 2019-04-14 16:39:12 +0700 | [diff] [blame] | 1039 | delete_expired_sms(sms_id, validity_timestamp); |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1040 | } |
| 1041 | |
Harald Welte | d43c22e | 2022-05-14 15:35:49 +0200 | [diff] [blame] | 1042 | db_remove_reset(stmt); |
Stefan Sperling | 14e0517 | 2018-01-22 17:31:20 +0100 | [diff] [blame] | 1043 | } |