From c5e6a65323a7558ca0322ec810a9dff847968730 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 6 Jan 2023 17:06:43 +0300 Subject: Initial SQLite schema (work in progress) --- src/main/resources/data-sqlite.sql | 11 + .../resources/db/specific/h2/V1.22__schema.sql | 4 +- .../resources/db/specific/sqlite/V1.22__schema.sql | 377 +++++++++++++++++++++ 3 files changed, 390 insertions(+), 2 deletions(-) create mode 100644 src/main/resources/data-sqlite.sql create mode 100644 src/main/resources/db/specific/sqlite/V1.22__schema.sql (limited to 'src/main/resources') diff --git a/src/main/resources/data-sqlite.sql b/src/main/resources/data-sqlite.sql new file mode 100644 index 00000000..bb1203a4 --- /dev/null +++ b/src/main/resources/data-sqlite.sql @@ -0,0 +1,11 @@ +INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password'); +INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password'); +INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password'); +INSERT INTO tags(tag_id, name) VALUES(2, 'juick'); +INSERT INTO reactions (like_id, description) VALUES (1, 'like'); +INSERT INTO reactions (like_id, description) VALUES (2, 'love'); +INSERT INTO reactions (like_id, description) VALUES (3, 'lol'); +INSERT INTO reactions (like_id, description) VALUES (4, 'hmm'); +INSERT INTO reactions (like_id, description) VALUES (5, 'angry'); +INSERT INTO reactions (like_id, description) VALUES (6, 'uhblya'); +INSERT INTO reactions (like_id, description) VALUES (7, 'ugh'); 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) +); -- cgit v1.2.3