diff options
| -rw-r--r-- | database.h | 11 | ||||
| -rw-r--r-- | litterbox.c | 10 | ||||
| -rw-r--r-- | scoop.c | 18 | ||||
| -rw-r--r-- | unscoop.c | 4 |
4 files changed, 27 insertions, 16 deletions
diff --git a/database.h b/database.h index a0cba04..fb59140 100644 --- a/database.h +++ b/database.h @@ -42,7 +42,7 @@ #define DATABASE_PATH "litterbox/litterbox.sqlite" -enum { DatabaseVersion = 3 }; +enum { DatabaseVersion = 4 }; #define ENUM_TYPE \ X(Privmsg, "privmsg") \ @@ -323,7 +323,7 @@ static const char *InitSQL = SQL( UNIQUE (host, port) ); - PRAGMA user_version = 3; + PRAGMA user_version = 4; COMMIT TRANSACTION; ); @@ -363,6 +363,13 @@ static const char *MigrationSQL[] = { CREATE INDEX eventsTime ON events (time); PRAGMA user_version = 3; ), + + SQL( + UPDATE motds SET time = strftime('%s', time); + UPDATE topics SET time = strftime('%s', time); + UPDATE events SET time = strftime('%s', time); + PRAGMA user_version = 4; + ), }; static inline void dbMigrate(void) { diff --git a/litterbox.c b/litterbox.c index a291ad0..70a5185 100644 --- a/litterbox.c +++ b/litterbox.c @@ -231,7 +231,7 @@ static void handleReplyMOTD(struct Message *msg) { static void handleReplyEndOfMOTD(struct Message *msg) { const char *sql = SQL( INSERT OR IGNORE INTO motds (time, network, motd) - VALUES (coalesce(datetime(:time), datetime('now')), :network, :motd); + VALUES (strftime('%s', coalesce(:time, 'now')), :network, :motd); ); sqlite3_stmt *stmt = dbPrepare(sql); dbBindText(stmt, ":time", msg->time); @@ -253,7 +253,7 @@ static void querySearch(struct Message *msg) { WITH results AS ( SELECT contexts.name AS context, - strftime('%Y-%m-%dT%H:%M:%SZ', events.time) AS time, + strftime('%Y-%m-%dT%H:%M:%SZ', events.time, 'unixepoch') AS time, events.type, names.nick, names.user, @@ -377,7 +377,7 @@ static void insertEvent( const char *sql = SQL( INSERT INTO events (time, type, context, name, target, message) SELECT - coalesce(datetime(:time), datetime('now')), + strftime('%s', coalesce(:time, 'now')), :type, context, names.name, :target, :message FROM contexts, names WHERE contexts.network = :network @@ -435,7 +435,7 @@ static void insertTopic( static sqlite3_stmt *stmt; const char *sql = SQL( INSERT OR IGNORE INTO topics (time, context, topic) - SELECT coalesce(datetime(:time), datetime('now')), context, :topic + SELECT strftime('%s', coalesce(:time, 'now')), context, :topic FROM contexts WHERE network = :network AND name = :context; ); dbPersist(&stmt, sql); @@ -546,7 +546,7 @@ static void insertEvents( const char *sql = SQL( INSERT INTO events (time, type, context, name, target, message) SELECT - coalesce(datetime(:time), datetime('now')), + strftime('%s', coalesce(:time, 'now')), :type, context, names.name, :target, :message FROM joins, contexts, names WHERE joins.nick = :nick diff --git a/scoop.c b/scoop.c index 078fc06..fed05ec 100644 --- a/scoop.c +++ b/scoop.c @@ -331,13 +331,12 @@ static char select[4096] = SQL( CASE WHEN :local THEN strftime( coalesce(:format, '%Y-%m-%dT%H:%M:%S'), - events.time, - 'localtime' + events.time, 'unixepoch', 'localtime' ) ELSE strftime( coalesce(:format, '%Y-%m-%dT%H:%M:%SZ'), - events.time + events.time, 'unixepoch' ) END AS time, events.type, @@ -402,8 +401,13 @@ int main(int argc, char *argv[]) { for (int opt; 0 < (opt = getopt(argc, argv, Opts));) { switch (opt) { break; case 'D': { - append(where, SQL(AND events.time >= date(:date))); - append(where, SQL(AND events.time < date(:date, '+1 day'))); + append( + where, + SQL( + AND events.time >= strftime('%s', :date) + AND events.time < strftime('%s', :date, '+1 day') + ) + ); binds[n++] = Bind(":date", optarg, 0); } break; case 'F': { @@ -424,11 +428,11 @@ int main(int argc, char *argv[]) { binds[n++] = Bind(":target", optarg, 0); } break; case 'a': { - append(where, SQL(AND events.time >= datetime(:after))); + append(where, SQL(AND events.time >= strftime('%s', :after))); binds[n++] = Bind(":after", optarg, 0); } break; case 'b': { - append(where, SQL(AND events.time < datetime(:before))); + append(where, SQL(AND events.time < strftime('%s', :before))); binds[n++] = Bind(":before", optarg, 0); } break; case 'c': { diff --git a/unscoop.c b/unscoop.c index 4392816..033c1dd 100644 --- a/unscoop.c +++ b/unscoop.c @@ -263,8 +263,8 @@ static void prepareInsert(void) { SELECT // SQLite expects a colon in the timezine, but ISO8601 does not. CASE WHEN :time LIKE '%Z' - THEN datetime(:time) - ELSE datetime(substr(:time, 1, 22) || ':' || substr(:time, -2)) + THEN strftime('%s', :time) + ELSE strftime('%s', substr(:time, 1, 22) || ':' || substr(:time, -2)) END, :type, context, names.name, :target, :message FROM contexts, names |