From c2fe259c1dc558945ea8beb82cdfe5891791564c Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Fri, 27 Jan 2023 19:43:42 +0300
Subject: db: initial SQL Server schema

* only schema is worked
---
 src/main/resources/data-sqlserver.sql   |  11 ++
 src/main/resources/schema-sqlserver.sql | 307 ++++++++++++++++++++++++++++++++
 2 files changed, 318 insertions(+)
 create mode 100644 src/main/resources/data-sqlserver.sql
 create mode 100644 src/main/resources/schema-sqlserver.sql

(limited to 'src/main/resources')

diff --git a/src/main/resources/data-sqlserver.sql b/src/main/resources/data-sqlserver.sql
new file mode 100644
index 00000000..bb1203a4
--- /dev/null
+++ b/src/main/resources/data-sqlserver.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/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql
new file mode 100644
index 00000000..ce6fc4df
--- /dev/null
+++ b/src/main/resources/schema-sqlserver.sql
@@ -0,0 +1,307 @@
+CREATE TABLE users (
+    id bigint NOT NULL,
+    nick character varying(64) NOT NULL,
+    passw character varying(32) NOT NULL,
+    lang VARCHAR(10) CHECK(lang IN ('ru', 'en', '__')) DEFAULT '__' NOT NULL,
+    banned smallint DEFAULT (0) NOT NULL,
+    lastmessage datetimeoffset,
+    lastpm bigint DEFAULT (0) NOT NULL,
+    lastphoto bigint DEFAULT (0) NOT NULL,
+    karma smallint DEFAULT (0) NOT NULL,
+    last_seen datetimeoffset,
+    PRIMARY KEY (id)
+);
+CREATE TABLE user_services (
+    user_id bigint NOT NULL,
+    regid character varying(1024) NOT NULL,
+    ts datetimeoffset 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 bigint,
+    protocol VARCHAR(10) CHECK (protocol IN ('xmpp', 'email', 'sms')) NOT NULL,
+    account character varying(128) NOT NULL,
+    authcode character varying(8) NOT NULL,
+    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+CREATE TABLE tags (
+    tag_id INTEGER PRIMARY KEY NOT NULL,
+    synonym_id integer,
+    name character varying(70),
+    [top] bit DEFAULT 0 NOT NULL,
+    noindex bit DEFAULT 0 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 bl_tags (
+    user_id bigint NOT NULL,
+    tag_id integer 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 datetimeoffset 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 datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    fb_name character varying(64),
+    fb_link character varying(255) NOT NULL,
+    crosspost bit DEFAULT 1 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 favorites (
+    user_id bigint NOT NULL,
+    message_id bigint NOT NULL,
+    ts datetimeoffset,
+    like_id INTEGER 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 datetimeoffset 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 datetimeoffset 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 datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    replies smallint DEFAULT (0) NOT NULL,
+    maxreplyid smallint DEFAULT (0) NOT NULL,
+    privacy smallint DEFAULT (1) NOT NULL,
+    readonly bit DEFAULT 0 NOT NULL,
+    attach VARCHAR(10) 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 datetimeoffset 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 datetimeoffset 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 datetimeoffset 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 datetimeoffset 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 replies (
+    message_id integer NOT NULL,
+    reply_id smallint NOT NULL,
+    user_id bigint NOT NULL,
+    replyto smallint DEFAULT (0) NOT NULL,
+    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    attach VARCHAR(10) CHECK (attach IN ('jpg', 'mp4', 'png')),
+    txt text NOT NULL,
+    updated_at datetimeoffset 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 integer 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 integer 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 datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id),
+    FOREIGN KEY (suser_id) REFERENCES users(id)
+);
+CREATE TABLE tags_ignore (
+    tag_id integer 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 datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    loginhash character varying(36),
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+CREATE TABLE top_ignore_messages (
+    message_id integer NOT NULL,
+    FOREIGN KEY (message_id) REFERENCES messages(message_id)
+);
+CREATE TABLE top_ignore_tags (
+    tag_id integer NOT NULL,
+    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
+);
+CREATE TABLE top_ignore_users (
+    user_id bigint NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
+CREATE TABLE twitter (
+    user_id bigint NOT NULL,
+    access_token character varying(64) NOT NULL,
+    access_token_secret character varying(64) NOT NULL,
+    uname character varying(64) NOT NULL,
+    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    crosspost bit DEFAULT 1 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 datetimeoffset 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 datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    client_secret varchar(200) DEFAULT NULL,
+    client_secret_expires_at datetimeoffset 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