keith has uploaded this change for review. ( https://gerrit.osmocom.org/c/osmo-msc/+/28339 )
Change subject: Use unix timestamps in the SMS db ...................................................................... Use unix timestamps in the SMS db There's really no need to convert to/from human readable dateformat (YYYY-MM-DD HH:MM:SS) everytime we access a record in the SMS database. sqlite3 has pseudo column types. In fact this means the column type in the database schema is meaningless and anything will be accepted and stored. See (Flexible Typing) [1] It appears that more processing than might be expected is happening internally on such things as date conversions, as the database engine has to actually do verifications on the data. (Is this INT really an INT etc...) [1] https://www.sqlite.org/quirks.html Change-Id: I599a57666da22adf806b01ff095c8672d523a737 --- M src/libmsc/db.c 1 file changed, 6 insertions(+), 6 deletions(-) git pull ssh://gerrit.osmocom.org:29418/osmo-msc refs/changes/39/28339/1 diff --git a/src/libmsc/db.c b/src/libmsc/db.c index 68cc038..6fe8803 100644 --- a/src/libmsc/db.c +++ b/src/libmsc/db.c @@ -219,10 +219,10 @@ * database query results! */ #define SEL_COLUMNS \ "id," \ - "strftime('%s',created)," \ + "created," \ "sent," \ "deliver_attempts," \ - "strftime('%s', valid_until)," \ + "valid_until," \ "reply_path_req," \ "status_rep_req," \ "is_report," \ @@ -271,7 +271,7 @@ " 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'), " + "($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)", @@ -302,7 +302,7 @@ " ORDER BY dest_addr, id LIMIT 1", [DB_STMT_SMS_MARK_DELIVERED] = "UPDATE SMS " - " SET sent = datetime('now') " + " SET sent = strftime('%s') " " WHERE id = $id", [DB_STMT_SMS_INC_DELIVER_ATTEMPTS] = "UPDATE SMS " @@ -315,9 +315,9 @@ [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", + "SELECT 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", + "SELECT id, valid_until FROM SMS ORDER BY valid_until LIMIT 1", [DB_STMT_VLR_INSERT] = "REPLACE INTO t_vlr (msisdn) VALUES ($msisdn)", }; -- To view, visit https://gerrit.osmocom.org/c/osmo-msc/+/28339 To unsubscribe, or for help writing mail filters, visit https://gerrit.osmocom.org/settings Gerrit-Project: osmo-msc Gerrit-Branch: master Gerrit-Change-Id: I599a57666da22adf806b01ff095c8672d523a737 Gerrit-Change-Number: 28339 Gerrit-PatchSet: 1 Gerrit-Owner: keith <[email protected]> Gerrit-MessageType: newchange
