From 57222853c90b0ffc4b800adeeddc289d58ee0a25 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Wed, 1 Feb 2023 20:22:05 +0300
Subject: Store timestamp in milliseconds when using SQLite

---
 src/main/resources/schema-sqlite.sql | 174 ++++++++++++-----------------------
 1 file changed, 57 insertions(+), 117 deletions(-)

(limited to 'src/main/resources')

diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index 8e496069..39895b2e 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -1,71 +1,51 @@
-PRAGMA journal_mode=WAL;;
+PRAGMA journal_mode=WAL;
 CREATE TABLE user_services (
     user_id INTEGER NOT NULL,
     regid character varying(1024) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     service_type varchar(255) not null default 'fcm',
     FOREIGN KEY (user_id) REFERENCES users(id),
     UNIQUE (regid)
-);;
-CREATE TRIGGER INSERT_user_services AFTER INSERT ON user_services
-    BEGIN
-        UPDATE user_services SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-        WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE auth (
     user_id INTEGER,
     protocol TEXT CHECK (protocol IN ('xmpp', 'email', 'sms')) NOT NULL,
     account character varying(128) NOT NULL,
     authcode character varying(8) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_auth AFTER INSERT ON auth
-    BEGIN
-       UPDATE auth SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE bl_tags (
     user_id INTEGER NOT NULL,
     tag_id bigint NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
-);;
+);
 CREATE TABLE bl_users (
     user_id INTEGER NOT NULL,
     bl_user_id bigint NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     PRIMARY KEY (user_id, bl_user_id),
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (bl_user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_bl_users AFTER INSERT ON bl_users
-    BEGIN
-       UPDATE bl_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE emails (
     user_id INTEGER NOT NULL,
     email character varying(128) NOT NULL,
     subscr_hour smallint,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE facebook (
     user_id INTEGER,
     fb_id numeric,
     loginhash character varying(36),
     access_token character varying(255),
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     fb_name character varying(64),
     fb_link character varying(255) NOT NULL,
     crosspost boolean DEFAULT true NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_facebook AFTER INSERT ON facebook
-    BEGIN
-       UPDATE facebook SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE favorites (
     user_id INTEGER NOT NULL,
     message_id bigint NOT NULL,
@@ -73,19 +53,14 @@ CREATE TABLE favorites (
     like_id smallint DEFAULT 1 NOT NULL,
     user_uri character varying(255) NOT NULL DEFAULT '',
     FOREIGN KEY (like_id) REFERENCES reactions(like_id)
-);;
+);
 CREATE TABLE followers (
     user_id INTEGER,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     acct character varying(64) NOT NULL,
     PRIMARY KEY (user_id)
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_followers AFTER INSERT ON followers
-    BEGIN
-       UPDATE followers SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE images (
     mid bigint NOT NULL,
     rid bigint NOT NULL,
@@ -95,25 +70,20 @@ CREATE TABLE images (
     height bigint NOT NULL,
     width bigint NOT NULL,
     PRIMARY KEY (mid, rid)
-);;
+);
 CREATE TABLE jids (
     user_id INTEGER,
     jid character varying(64) NOT NULL,
     active smallint DEFAULT 0 NOT NULL,
     loginhash character varying(36),
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_jids AFTER INSERT ON jids
-    BEGIN
-       UPDATE jids SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE logins (
     user_id INTEGER NOT NULL,
     hash character varying(16) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE meon (
     id INTEGER NOT NULL,
     user_id INTEGER NOT NULL,
@@ -121,12 +91,12 @@ CREATE TABLE meon (
     name character varying(32) NOT NULL,
     ico smallint,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE messages (
     message_id INTEGER PRIMARY KEY NOT NULL,
     user_id INTEGER NOT NULL,
     lang TEXT DEFAULT '__' NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     replies smallint DEFAULT (0) NOT NULL,
     maxreplyid smallint DEFAULT (0) NOT NULL,
     privacy smallint DEFAULT (1) NOT NULL,
@@ -138,15 +108,15 @@ CREATE TABLE messages (
     popular smallint DEFAULT (0) NOT NULL,
     hidden smallint DEFAULT (0) NOT NULL,
     likes smallint DEFAULT (0) NOT NULL,
-    updated DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE messages_access (
     message_id INTEGER NOT NULL,
     user_id INTEGER NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE messages_properties (
     message_id INTEGER NOT NULL,
     reply_id smallint NOT NULL,
@@ -154,19 +124,19 @@ CREATE TABLE messages_properties (
     property_value text NOT NULL,
     UNIQUE (message_id, reply_id, property_key),
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE messages_tags (
     message_id INTEGER NOT NULL,
     tag_id bigint NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE messages_txt (
     message_id INTEGER NOT NULL,
     repliesby text,
     txt text NOT NULL,
-    updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated_at DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE places (
     place_id INTEGER PRIMARY KEY NOT NULL,
     lat numeric(10,7) NOT NULL,
@@ -175,36 +145,31 @@ CREATE TABLE places (
     descr character varying(255),
     url character varying(128),
     user_id bigint NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL
-);;
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL
+);
 CREATE TABLE places_tags (
     place_id INTEGER NOT NULL,
     tag_id bigint NOT NULL,
     FOREIGN KEY (place_id) REFERENCES places(place_id)
-);;
+);
 CREATE TABLE pm (
     user_id INTEGER NOT NULL,
     user_id_to INTEGER NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     txt text NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (user_id_to) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_pm AFTER INSERT ON pm
-    BEGIN
-       UPDATE pm SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE reactions (
     like_id INTEGER PRIMARY KEY NOT NULL,
     description character varying(100) NOT NULL
-);;
+);
 CREATE TABLE replies (
     message_id bigint NOT NULL,
     reply_id smallint NOT NULL,
     user_id INTEGER NOT NULL,
     replyto smallint DEFAULT (0) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')),
     txt text NOT NULL,
     updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL,
@@ -213,32 +178,27 @@ CREATE TABLE replies (
     html smallint DEFAULT '0' NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id),
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE subscr_messages (
     message_id bigint NOT NULL,
     suser_id INTEGER NOT NULL,
     last_read_rid smallint DEFAULT 0 NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE subscr_tags (
     tag_id bigint NOT NULL,
     suser_id INTEGER NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE subscr_users (
     user_id INTEGER NOT NULL,
     suser_id INTEGER NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_subscr_users AFTER INSERT ON subscr_users
-    BEGIN
-       UPDATE subscr_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE tags (
     tag_id INTEGER PRIMARY KEY NOT NULL,
     synonym_id bigint,
@@ -248,54 +208,44 @@ CREATE TABLE tags (
     stat_messages bigint DEFAULT (0) NOT NULL,
     stat_users smallint DEFAULT (0) NOT NULL,
     FOREIGN KEY (synonym_id) REFERENCES tags(tag_id)
-);;
+);
 CREATE TABLE tags_ignore (
     tag_id bigint NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
-);;
+);
 CREATE TABLE tags_synonyms (
     name character varying(64) NOT NULL,
     changeto character varying(64) NOT NULL
-);;
+);
 CREATE TABLE telegram (
     user_id INTEGER,
     tg_id numeric NOT NULL,
     tg_name character varying(64) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     loginhash character varying(36),
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_telegram AFTER INSERT ON telegram
-    BEGIN
-       UPDATE telegram SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE top_ignore_messages (
     message_id bigint NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE top_ignore_tags (
     tag_id NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tag(tag_id)
-);;
+);
 CREATE TABLE top_ignore_users (
     user_id INTEGER NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE twitter (
     user_id INTEGER NOT NULL,
     access_token character varying(64) NOT NULL,
     access_token_secret character varying(64) NOT NULL,
     uname character varying(64) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     crosspost boolean DEFAULT true NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_twitter AFTER INSERT ON twitter
-    BEGIN
-       UPDATE twitter SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE users (
     id INTEGER PRIMARY KEY,
     nick character varying(64) NOT NULL COLLATE NOCASE,
@@ -308,12 +258,12 @@ CREATE TABLE users (
     karma smallint DEFAULT (0) NOT NULL,
     last_seen timestamp with time zone,
     UNIQUE(nick)
-);;
+);
 CREATE TABLE users_subscr (
     user_id INTEGER NOT NULL,
     cnt smallint DEFAULT (0) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE usersinfo (
     user_id INTEGER NOT NULL,
     jid character varying(64),
@@ -325,34 +275,29 @@ CREATE TABLE usersinfo (
     descr text,
     UNIQUE(user_id),
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE vk (
     user_id INTEGER,
     vk_id numeric NULL,
     loginhash character varying(36),
     access_token character varying(128) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     vk_name character varying(64) NOT NULL,
     vk_link character varying(64) NOT NULL,
     crosspost smallint DEFAULT (1) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_vk AFTER INSERT ON vk
-    BEGIN
-       UPDATE vk SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE wl_users (
     user_id INTEGER NOT NULL,
     wl_user_id bigint NOT NULL,
     PRIMARY KEY (user_id, wl_user_id),
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (wl_user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE oauth2_registered_client (
     id varchar(100) NOT NULL,
     client_id varchar(100) NOT NULL,
-    client_id_issued_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    client_id_issued_at timestamp DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     client_secret varchar(200) DEFAULT NULL,
     client_secret_expires_at timestamp DEFAULT NULL,
     client_name varchar(200) NOT NULL,
@@ -363,9 +308,4 @@ CREATE TABLE oauth2_registered_client (
     client_settings varchar(2000) NOT NULL,
     token_settings varchar(2000) NOT NULL,
     PRIMARY KEY (id)
-);;
-CREATE TRIGGER INSERT_oauth2_registered_client AFTER INSERT ON oauth2_registered_client
-    BEGIN
-       UPDATE oauth2_registered_client SET client_id_issued_at = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END
+);
-- 
cgit v1.2.3