Make date and time handling usable with both julianday and datetime.

Use datetime instead of julianday when not performance sensitive.

Keep julianday for logins.

2 files changed, 46 insertions, 35 deletions

diff --git a/schema.sql b/schema.sql
index b0d7b39..49d195a 100644
--- a/schema.sql
+++ b/schema.sql
@@ -16,7 +16,7 @@ create table if not exists titles (
 create table if not exists logins (
 	sid integer primary key,
 	user_id integer,
-	expires real
+	expires real -- julianday
 );
 
 create table if not exists users (
@@ -25,7 +25,7 @@ create table if not exists users (
 	mail text unique collate nocase,
 	notify boolean default 0,
 	is_banned boolean default 0,
-	ctime real default (julianday()),
+	ctime datetime default current_timestamp,
 	password text,
 	salt text,
 	about text
@@ -38,19 +38,19 @@ insert or ignore into
 
 create table if not exists user_last_seen (
 	user_id integer primary key,
-	atime real
+	atime datetime
 );
 
 create table if not exists tokens (
 	user_id integer primary key,
 	token text,
-	time real
+	time datetime
 );
 
 create table if not exists last_notified (
 	game_id integer,
 	user_id integer,
-	time real,
+	time datetime,
 	primary key (game_id, user_id)
 ) without rowid;
 
@@ -161,7 +161,7 @@ create table if not exists messages (
 	is_deleted_from_outbox boolean default 0,
 	from_id integer,
 	to_id integer,
-	time real default (julianday()),
+	time datetime default current_timestamp,
 	is_read boolean default 0,
 	subject text,
 	body text
@@ -208,8 +208,8 @@ create table if not exists posts (
 	post_id integer primary key,
 	thread_id integer,
 	author_id integer,
-	ctime real default (julianday()),
-	mtime real default (julianday()),
+	ctime datetime default current_timestamp,
+	mtime datetime default current_timestamp,
 	body text
 );
 
@@ -274,7 +274,7 @@ create table if not exists games (
 	scenario text,
 	options text,
 	owner_id integer,
-	ctime real default (julianday()),
+	ctime datetime default current_timestamp,
 	is_private boolean default 0,
 	is_random boolean default 0,
 	description text,
@@ -287,7 +287,7 @@ create index if not exists games_status_idx on games(status);
 
 create table if not exists game_state (
 	game_id integer primary key,
-	mtime real,
+	mtime datetime,
 	active text,
 	state text
 );
@@ -295,7 +295,7 @@ create table if not exists game_state (
 create table if not exists game_chat (
 	chat_id integer primary key,
 	game_id integer,
-	time real default (julianday()),
+	time datetime default current_timestamp,
 	user_id integer,
 	message text
 );
@@ -417,7 +417,7 @@ create view your_turn_reminder as
 		and active in ('All', 'Both', role)
 		and is_solo = 0
 		and notify = 1
-		and julianday() > mtime + 0.04
+		and julianday() > julianday(mtime, '+1 hour')
 	;
 
 drop view if exists your_turn;
@@ -481,6 +481,7 @@ end;
 
 drop trigger if exists trigger_mark_threads_as_unread2;
 create trigger trigger_mark_threads_as_unread2 after update on posts
+	when new.body != old.body
 begin
 	delete from read_threads where user_id != new.author_id and thread_id = new.thread_id;
 end;
@@ -489,7 +490,7 @@ create table if not exists deleted_users (
 	user_id integer,
 	name text collate nocase,
 	mail text collate nocase,
-	time real default (julianday())
+	time datetime default current_timestamp
 );
 
 drop trigger if exists trigger_log_deleted_users;
diff --git a/server.js b/server.js
index ef62317..a3bb61d 100644
--- a/server.js
+++ b/server.js
@@ -19,10 +19,6 @@ const HTTP_PORT = process.env.HTTP_PORT || 8080
 const SITE_NAME = process.env.SITE_NAME || "Localhost"
 const SITE_URL = process.env.SITE_URL || "http://" + HTTP_HOST + ":" + HTTP_PORT
 
-// Time intervals in julian days
-const HOURS = 1 / 24
-const MINUTES = 1 / (24 * 60)
-
 function LOG_STATS() {
 	// Count clients connected to join page events
 	let num_joins = 0
@@ -50,8 +46,8 @@ setInterval(LOG_STATS, 60 * 1000)
 let db = new sqlite3(process.env.DATABASE || "./db")
 db.pragma("synchronous = NORMAL")
 
-db.exec("delete from logins where julianday() > expires")
-db.exec("delete from tokens where julianday() > time + 1")
+db.exec("delete from logins where julianday() > julianday(expires)")
+db.exec("delete from tokens where julianday() > julianday(time, '+1 days')")
 
 function SQL(s) {
 	return db.prepare(s)
@@ -181,12 +177,24 @@ function random_seed() {
 	return crypto.randomInt(1, 2**35-31)
 }
 
-function from_julianday(jd) {
-	return (jd - 2440587.5) * 86400000
+function epoch_from_julianday(x) {
+	return (x - 2440587.5) * 86400000
+}
+
+function julianday_from_epoch(x) {
+	return x / 86400000 + 2440587.5
+}
+
+function epoch_from_time(x) {
+	if (typeof x === "string")
+		return Date.parse(x)
+	return epoch_from_julianday(x)
 }
 
-function to_julianday(t) {
-	return t / 86400000 + 2440587.5
+function julianday_from_time(x) {
+	if (typeof x === "string")
+		return julianday_from_epoch(Date.parse(x))
+	return x
 }
 
 function SLOG(socket, ...msg) {
@@ -202,9 +210,11 @@ function SLOG(socket, ...msg) {
 }
 
 function human_date(date) {
+	if (typeof date === 'string')
+		date = julianday_from_epoch(Date.parse(date))
 	if (typeof date !== 'number')
 		return "never"
-	var days = to_julianday(Date.now()) - date
+	var days = julianday_from_epoch(Date.now()) - date
 	var seconds = days * 86400
 	if (days < 1) {
 		if (seconds < 60) return "now"
@@ -216,7 +226,7 @@ function human_date(date) {
 	if (days < 2) return "yesterday"
 	if (days < 14) return Math.floor(days) + " days ago"
 	if (days < 31) return Math.floor(days / 7) + " weeks ago"
-	return new Date(from_julianday(date)).toISOString().substring(0,10)
+	return new Date(epoch_from_julianday(date)).toISOString().substring(0,10)
 }
 
 function is_email(email) {
@@ -269,7 +279,7 @@ const SQL_SELECT_USER_DYNAMIC = SQL("select * from user_dynamic_view where user_
 const SQL_SELECT_USER_NAME = SQL("SELECT name FROM users WHERE user_id=?").pluck()
 const SQL_SELECT_USER_ID = SQL("SELECT user_id FROM users WHERE name=?").pluck()
 
-const SQL_OFFLINE_USER = SQL("SELECT * FROM user_view NATURAL JOIN user_last_seen WHERE user_id=? AND julianday() > atime + ?")
+const SQL_OFFLINE_USER = SQL("SELECT * FROM user_view NATURAL JOIN user_last_seen WHERE user_id=? AND julianday() > julianday(atime, ?)")
 
 const SQL_SELECT_USER_NOTIFY = SQL("SELECT notify FROM users WHERE user_id=?").pluck()
 const SQL_UPDATE_USER_NOTIFY = SQL("UPDATE users SET notify=? WHERE user_id=?")
@@ -286,9 +296,9 @@ const SQL_UPDATE_WEBHOOK = SQL("INSERT OR REPLACE INTO webhooks (user_id, url, f
 const SQL_UPDATE_WEBHOOK_ERROR = SQL("UPDATE webhooks SET error=? WHERE user_id=?")
 const SQL_DELETE_WEBHOOK = SQL("DELETE FROM webhooks WHERE user_id=?")
 
-const SQL_FIND_TOKEN = SQL("SELECT token FROM tokens WHERE user_id=? AND julianday() < time + 0.004").pluck()
+const SQL_FIND_TOKEN = SQL("SELECT token FROM tokens WHERE user_id=? AND julianday('now') < julianday(time, '+5 minutes')").pluck()
 const SQL_CREATE_TOKEN = SQL("INSERT OR REPLACE INTO tokens (user_id,token,time) VALUES (?, lower(hex(randomblob(16))), julianday()) RETURNING token").pluck()
-const SQL_VERIFY_TOKEN = SQL("SELECT EXISTS ( SELECT 1 FROM tokens WHERE user_id=? AND julianday() < time + 0.020 AND token=? )").pluck()
+const SQL_VERIFY_TOKEN = SQL("SELECT EXISTS ( SELECT 1 FROM tokens WHERE user_id=? AND julianday('now') < julianday(time, '+20 minutes') AND token=? )").pluck()
 
 function is_blacklisted(mail) {
 	if (SQL_BLACKLIST_MAIL.get(mail) === 1)
@@ -818,7 +828,7 @@ app.post('/message/send', must_be_logged_in, function (req, res) {
 })
 
 function quote_body(message) {
-	let when = new Date(from_julianday(message.time)).toDateString()
+	let when = new Date(epoch_from_time(message.time)).toDateString()
 	let who = message.from_name
 	let what = message.body.split("\n").join("\n> ")
 	return "\n\n" + "On " + when + " " + who + " wrote:\n> " + what + "\n"
@@ -1211,7 +1221,7 @@ const QUERY_LIST_ACTIVE_GAMES_OF_USER = SQL(`
 	where
 		( owner_id=$user_id or game_id in ( select game_id from players where players.user_id=$user_id ) )
 		and
-		( status < 2 or mtime > julianday() - 7 )
+		( status < 2 or julianday(mtime) > julianday('now', '-7 days') )
 	order by status asc, mtime desc
 	`)
 
@@ -1837,7 +1847,7 @@ function webhook_your_turn(user, game_id) {
 const MAIL_FROM = process.env.MAIL_FROM || "user@localhost"
 const MAIL_FOOTER = "\n--\nYou can unsubscribe from notifications on your profile page:\n" + SITE_URL + "/profile\n"
 
-const SQL_SELECT_NOTIFIED = SQL("SELECT julianday() < time + ? FROM last_notified WHERE game_id=? AND user_id=?").pluck()
+const SQL_SELECT_NOTIFIED = SQL("SELECT julianday() < julianday(time, ?) FROM last_notified WHERE game_id=? AND user_id=?").pluck()
 const SQL_INSERT_NOTIFIED = SQL("INSERT OR REPLACE INTO last_notified (game_id,user_id,time) VALUES (?,?,julianday())")
 const SQL_DELETE_NOTIFIED = SQL("DELETE FROM last_notified WHERE game_id=? AND user_id=?")
 const SQL_DELETE_NOTIFIED_ALL = SQL("DELETE FROM last_notified WHERE game_id=?")
@@ -1969,7 +1979,7 @@ function mail_your_turn_notification_to_offline_users(game_id, old_active, activ
 					reset_your_turn_notification(p, game_id)
 			} else {
 				if (p.notify)
-					mail_your_turn_notification(p, game_id, 15 * MINUTES)
+					mail_your_turn_notification(p, game_id, '+15 minutes')
 				webhook_your_turn(p, game_id)
 			}
 		} else {
@@ -2003,7 +2013,7 @@ function mail_game_over_notification_to_offline_users(game_id, result, victory)
 
 function notify_your_turn_reminder() {
 	for (let item of QUERY_LIST_YOUR_TURN.all()) {
-		mail_your_turn_notification(item, item.game_id, 25 * HOURS)
+		mail_your_turn_notification(item, item.game_id, '+25 hours')
 	}
 }
 
@@ -2011,10 +2021,10 @@ function notify_ready_to_start_reminder() {
 	for (let game of SQL_SELECT_OPEN_GAMES.all()) {
 		let players = SQL_SELECT_PLAYERS.all(game.game_id)
 		if (is_game_ready(game.title_id, game.scenario, game.options, players)) {
-			let owner = SQL_OFFLINE_USER.get(game.owner_id, 3 * MINUTES)
+			let owner = SQL_OFFLINE_USER.get(game.owner_id, '+3 minutes')
 			if (owner) {
 				if (owner.notify)
-					mail_ready_to_start_notification(owner, game.game_id, 25 * HOURS)
+					mail_ready_to_start_notification(owner, game.game_id, '+25 hours')
 			}
 		}
 	}