From 2db415ce42fb96059b6d7fce4cf4ce078a62680e Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 27 Jan 2023 17:50:14 +0300 Subject: baseline non-production databases to 1.36 --- src/main/resources/schema-sqlite.sql | 311 +++++++++++++++++++++++++++++++++++ 1 file changed, 311 insertions(+) create mode 100644 src/main/resources/schema-sqlite.sql (limited to 'src/main/resources/schema-sqlite.sql') diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql new file mode 100644 index 00000000..7ec33395 --- /dev/null +++ b/src/main/resources/schema-sqlite.sql @@ -0,0 +1,311 @@ +CREATE TABLE IF NOT EXISTS "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', + 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 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 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 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 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 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) +); +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_secret varchar(200) DEFAULT NULL, + client_secret_expires_at timestamp DEFAULT NULL, + client_name varchar(200) NOT NULL, + client_authentication_methods varchar(1000) NOT NULL, + authorization_grant_types varchar(1000) NOT NULL, + redirect_uris varchar(1000) DEFAULT NULL, + scopes varchar(1000) NOT NULL, + client_settings varchar(2000) NOT NULL, + token_settings varchar(2000) NOT NULL, + PRIMARY KEY (id) +); -- cgit v1.2.3