SQLite support

1 files changed, 147 insertions, 84 deletions

diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index b624d67b..8e496069 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -1,64 +1,91 @@
-CREATE TABLE IF NOT EXISTS "user_services" (
+PRAGMA journal_mode=WAL;;
+CREATE TABLE user_services (
     user_id INTEGER NOT NULL,
     regid character varying(1024) NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, service_type varchar(255) not null default 'fcm',
+    ts DEFAULT CURRENT_TIMESTAMP 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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    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 bigint NOT NULL,
+    user_id INTEGER NOT NULL,
     bl_user_id bigint NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    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 bigint,
+    user_id INTEGER,
     fb_id numeric,
     loginhash character varying(36),
     access_token character varying(255),
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    user_id INTEGER NOT NULL,
     message_id bigint NOT NULL,
     ts timestamp with time zone,
     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 bigint,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    user_id INTEGER,
+    ts DEFAULT CURRENT_TIMESTAMP 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,
@@ -68,33 +95,38 @@ CREATE TABLE images (
     height bigint NOT NULL,
     width bigint NOT NULL,
     PRIMARY KEY (mid, rid)
-);
+);;
 CREATE TABLE jids (
-    user_id bigint,
+    user_id INTEGER,
     jid character varying(64) NOT NULL,
     active smallint DEFAULT 0 NOT NULL,
     loginhash character varying(36),
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    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 bigint NOT NULL,
+    user_id INTEGER NOT NULL,
     link character varying(255) NOT NULL,
     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 bigint NOT NULL,
+    user_id INTEGER NOT NULL,
     lang TEXT DEFAULT '__' NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
     replies smallint DEFAULT (0) NOT NULL,
     maxreplyid smallint DEFAULT (0) NOT NULL,
     privacy smallint DEFAULT (1) NOT NULL,
@@ -106,35 +138,35 @@ CREATE TABLE messages (
     popular smallint DEFAULT (0) NOT NULL,
     hidden smallint DEFAULT (0) NOT NULL,
     likes smallint DEFAULT (0) NOT NULL,
-    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated DEFAULT CURRENT_TIMESTAMP NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);
+);;
 CREATE TABLE messages_access (
     message_id INTEGER NOT NULL,
-    user_id bigint 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 PRIMARY KEY NOT NULL,
+    message_id INTEGER NOT NULL,
     reply_id smallint NOT NULL,
     property_key character varying(255) NOT NULL,
     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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated_at DEFAULT CURRENT_TIMESTAMP 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,
@@ -143,60 +175,70 @@ CREATE TABLE places (
     descr character varying(255),
     url character varying(128),
     user_id bigint NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
-);
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
-    user_id_to bigint NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    user_id INTEGER NOT NULL,
+    user_id_to INTEGER NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    user_id INTEGER NOT NULL,
     replyto smallint DEFAULT (0) NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
     attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')),
     txt text NOT NULL,
-    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL,
     user_uri character varying(255) DEFAULT NULL,
     reply_uri character varying(255) DEFAULT NULL,
     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 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 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 bigint NOT NULL,
-    suser_id bigint NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    user_id INTEGER NOT NULL,
+    suser_id INTEGER NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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,
@@ -206,46 +248,56 @@ 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 bigint,
+    user_id INTEGER,
     tg_id numeric NOT NULL,
     tg_name character varying(64) NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    id INTEGER PRIMARY KEY,
     nick character varying(64) NOT NULL COLLATE NOCASE,
     passw character varying(32) NOT NULL,
     lang users_lang DEFAULT '__' NOT NULL,
@@ -255,15 +307,15 @@ CREATE TABLE users (
     lastphoto bigint DEFAULT (0) NOT NULL,
     karma smallint DEFAULT (0) NOT NULL,
     last_seen timestamp with time zone,
-    PRIMARY KEY (id)
-);
+    UNIQUE(nick)
+);;
 CREATE TABLE users_subscr (
-    user_id bigint NOT NULL,
+    user_id INTEGER NOT NULL,
     cnt smallint DEFAULT (0) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);
+);;
 CREATE TABLE usersinfo (
-    user_id bigint NOT NULL,
+    user_id INTEGER NOT NULL,
     jid character varying(64),
     fullname character varying(64),
     country character varying(32),
@@ -271,26 +323,32 @@ CREATE TABLE usersinfo (
     gender character varying(32),
     bday character varying(32),
     descr text,
+    UNIQUE(user_id),
     FOREIGN KEY (user_id) REFERENCES users(id)
-);
+);;
 CREATE TABLE vk (
-    user_id bigint,
+    user_id INTEGER,
     vk_id numeric NULL,
     loginhash character varying(36),
     access_token character varying(128) NOT NULL,
-    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL,
+    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,
@@ -305,4 +363,9 @@ 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