aboutsummaryrefslogtreecommitdiff
path: root/src/main/resources/db/specific/sqlite
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-01-27 17:50:14 +0300
committerGravatar Vitaly Takmazov2023-01-27 19:05:25 +0300
commit2db415ce42fb96059b6d7fce4cf4ce078a62680e (patch)
tree06d21a8483e437ad4d62f337b70e7e93dba18cc6 /src/main/resources/db/specific/sqlite
parentb3a169844b43d64aba4c1f7c583073b5dc54cf49 (diff)
baseline non-production databases to 1.36
Diffstat (limited to 'src/main/resources/db/specific/sqlite')
-rw-r--r--src/main/resources/db/specific/sqlite/V1.22__schema.sql377
1 files changed, 0 insertions, 377 deletions
diff --git a/src/main/resources/db/specific/sqlite/V1.22__schema.sql b/src/main/resources/db/specific/sqlite/V1.22__schema.sql
deleted file mode 100644
index 072b2a1a..00000000
--- a/src/main/resources/db/specific/sqlite/V1.22__schema.sql
+++ /dev/null
@@ -1,377 +0,0 @@
-CREATE TABLE android (
- user_id INTEGER NOT NULL,
- regid character varying(1024) 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)
-);