Initial SQLite schema (work in progress)

2 files changed, 379 insertions, 2 deletions

diff --git a/src/main/resources/db/specific/h2/V1.22__schema.sql b/src/main/resources/db/specific/h2/V1.22__schema.sql
index dcb92374..de6946e8 100644
--- a/src/main/resources/db/specific/h2/V1.22__schema.sql
+++ b/src/main/resources/db/specific/h2/V1.22__schema.sql
@@ -169,7 +169,7 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES"(
     "MESSAGE_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL,
     "USER_ID" INTEGER NOT NULL,
     "LANG" ENUM('en', 'ru', 'fr', 'fa', '__') DEFAULT '__' NOT NULL,
-    "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    "TS" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     "REPLIES" SMALLINT DEFAULT '0' NOT NULL,
     "MAXREPLYID" SMALLINT DEFAULT '0' NOT NULL,
     "PRIVACY" TINYINT DEFAULT '1' NOT NULL,
@@ -181,7 +181,7 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES"(
     "POPULAR" TINYINT DEFAULT '0' NOT NULL,
     "HIDDEN" TINYINT DEFAULT '0' NOT NULL,
     "LIKES" SMALLINT DEFAULT '0' NOT NULL,
-    "UPDATED" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
+    "UPDATED" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
 );
 ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_13" PRIMARY KEY("MESSAGE_ID");
 -- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSAGES;
diff --git a/src/main/resources/db/specific/sqlite/V1.22__schema.sql b/src/main/resources/db/specific/sqlite/V1.22__schema.sql
new file mode 100644
index 00000000..8685cbb7
--- /dev/null
+++ b/src/main/resources/db/specific/sqlite/V1.22__schema.sql
@@ -0,0 +1,377 @@
+CREATE TABLE android (
+    user_id INTEGER NOT NULL,
+    regid character varying(255) NOT NULL,
+    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id),
+    UNIQUE (regid)
+);
+
+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,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE bl_tags (
+    user_id bigint 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,
+    bl_user_id bigint NOT NULL,
+    ts timestamp with time zone 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 TABLE emails (
+    user_id bigint NOT NULL,
+    email character varying(128) NOT NULL,
+    subscr_hour smallint,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE facebook (
+    user_id bigint,
+    fb_id numeric,
+    loginhash character varying(36),
+    access_token character varying(255),
+    ts timestamp with time zone 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 TABLE favorites (
+    user_id bigint 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,
+    acct character varying(64) NOT NULL,
+    PRIMARY KEY (user_id)
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE images (
+    mid bigint NOT NULL,
+    rid bigint NOT NULL,
+    thumb bigint NOT NULL,
+    small bigint NOT NULL,
+    medium bigint NOT NULL,
+    height bigint NOT NULL,
+    width bigint NOT NULL,
+    PRIMARY KEY (mid, rid)
+);
+
+CREATE TABLE ios (
+    user_id bigint NOT NULL,
+    token character varying(64) NOT NULL,
+    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id),
+    UNIQUE (token)
+);
+
+CREATE TABLE jids (
+    user_id bigint,
+    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,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE logins (
+    user_id bigint NOT NULL,
+    hash character varying(16) NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE mail (
+    user_id INTEGER NOT NULL,
+    hash character varying(16) NOT NULL,
+    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE meon (
+    id INTEGER NOT NULL,
+    user_id bigint 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,
+    lang TEXT DEFAULT '__' NOT NULL,
+    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    replies smallint DEFAULT (0) NOT NULL,
+    maxreplyid smallint DEFAULT (0) NOT NULL,
+    privacy smallint DEFAULT (1) NOT NULL,
+    readonly boolean DEFAULT false NOT NULL,
+    attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')),
+    place_id bigint,
+    lat numeric(10,7),
+    lon numeric(10,7),
+    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,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE messages_access (
+    message_id INTEGER NOT NULL,
+    user_id bigint 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,
+    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,
+    FOREIGN KEY (message_id) REFERENCES messages(message_id)
+);
+
+CREATE TABLE places (
+    place_id INTEGER PRIMARY KEY NOT NULL,
+    lat numeric(10,7) NOT NULL,
+    lon numeric(10,7) NOT NULL,
+    name character varying(64) NOT NULL,
+    descr character varying(255),
+    url character varying(128),
+    user_id bigint NOT NULL,
+    ts timestamp with time zone 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,
+    txt text NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id),
+    FOREIGN KEY (user_id_to) REFERENCES users(id)
+);
+
+CREATE TABLE pm_inroster (
+    user_id bigint NOT NULL,
+    jid character varying(64) NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+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,
+    replyto smallint DEFAULT (0) NOT NULL,
+    ts timestamp with time zone 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,
+    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,
+    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,
+    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,
+    FOREIGN KEY (user_id) REFERENCES users(id),
+    FOREIGN KEY (suser_id) REFERENCES users(id)
+);
+
+CREATE TABLE tags (
+    tag_id INTEGER PRIMARY KEY NOT NULL,
+    synonym_id bigint,
+    name character varying(70) COLLATE NOCASE,
+    top boolean DEFAULT false NOT NULL,
+    noindex boolean DEFAULT false NOT NULL,
+    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,
+    tg_id numeric NOT NULL,
+    tg_name character varying(64) NOT NULL,
+    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    loginhash character varying(36),
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+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,
+    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,
+    crosspost boolean DEFAULT true NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE useroptions (
+    user_id INTEGER NOT NULL,
+    jnotify smallint DEFAULT 1 NOT NULL,
+    subscr_active smallint DEFAULT 1 NOT NULL,
+    off_ts timestamp with time zone,
+    xmppxhtml smallint DEFAULT 0 NOT NULL,
+    subscr_notify smallint DEFAULT 1 NOT NULL,
+    recommendations smallint DEFAULT 1 NOT NULL,
+    privacy_view smallint DEFAULT 1 NOT NULL,
+    privacy_reply smallint DEFAULT 1 NOT NULL,
+    privacy_pm smallint DEFAULT 1 NOT NULL,
+    repliesview smallint DEFAULT 0 NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE users (
+    id bigint NOT NULL,
+    nick character varying(64) NOT NULL COLLATE NOCASE,
+    passw character varying(32) NOT NULL,
+    lang users_lang DEFAULT '__' NOT NULL,
+    banned smallint DEFAULT (0) NOT NULL,
+    lastmessage timestamp with time zone,
+    lastpm bigint DEFAULT (0) NOT NULL,
+    lastphoto bigint DEFAULT (0) NOT NULL,
+    karma smallint DEFAULT (0) NOT NULL,
+    last_seen timestamp with time zone,
+    PRIMARY KEY (id)
+);
+
+CREATE TABLE users_subscr (
+    user_id bigint NOT NULL,
+    cnt smallint DEFAULT (0) NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE usersinfo (
+    user_id bigint NOT NULL,
+    jid character varying(64),
+    fullname character varying(64),
+    country character varying(32),
+    url character varying(128),
+    gender character varying(32),
+    bday character varying(32),
+    descr text,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE version (
+    version numeric NOT NULL
+);
+
+CREATE TABLE vk (
+    user_id bigint,
+    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,
+    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 TABLE winphone (
+    user_id bigint NOT NULL,
+    url character varying(255) NOT NULL,
+    ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+
+CREATE TABLE wl_users (
+    user_id bigint 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)
+);