From 556d10c400add4b69d8832a40fb704fda4333080 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Sat, 28 Jan 2023 01:46:13 +0300
Subject: Improve SQL server compatibility

---
 src/main/resources/data-sqlserver.sql   |  4 ++++
 src/main/resources/schema-sqlserver.sql | 17 +++++++++++------
 2 files changed, 15 insertions(+), 6 deletions(-)

(limited to 'src/main/resources')

diff --git a/src/main/resources/data-sqlserver.sql b/src/main/resources/data-sqlserver.sql
index bb1203a4..f5844a52 100644
--- a/src/main/resources/data-sqlserver.sql
+++ b/src/main/resources/data-sqlserver.sql
@@ -1,7 +1,11 @@
+set identity_insert users ON;
 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');
+set identity_insert users OFF;
+set identity_insert tags ON;
 INSERT INTO tags(tag_id, name) VALUES(2, 'juick');
+set identity_insert tags OFF;
 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');
diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql
index 7fb2d4e0..5fbe9569 100644
--- a/src/main/resources/schema-sqlserver.sql
+++ b/src/main/resources/schema-sqlserver.sql
@@ -1,5 +1,5 @@
 CREATE TABLE users (
-    id bigint NOT NULL,
+    id bigint IDENTITY(1,1) 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,
@@ -27,7 +27,7 @@ CREATE TABLE auth (
     FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE TABLE tags (
-    tag_id INTEGER PRIMARY KEY NOT NULL,
+    tag_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL,
     synonym_id integer,
     name character varying(70),
     [top] bit DEFAULT 0 NOT NULL,
@@ -118,7 +118,7 @@ CREATE TABLE meon (
     FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE TABLE messages (
-    message_id INTEGER PRIMARY KEY NOT NULL,
+    message_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL,
     user_id bigint NOT NULL,
     lang TEXT DEFAULT '__' NOT NULL,
     ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
@@ -146,7 +146,7 @@ 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,
+    property_value character varying(255) NOT NULL,
     UNIQUE (message_id, reply_id, property_key),
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
 );
@@ -157,8 +157,8 @@ CREATE TABLE messages_tags (
 );
 CREATE TABLE messages_txt (
     message_id INTEGER NOT NULL,
-    repliesby text,
-    txt text NOT NULL,
+    repliesby varchar(max),
+    txt varchar(max) NOT NULL,
     updated_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
 );
@@ -220,6 +220,10 @@ CREATE TABLE subscr_users (
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
 );
+CREATE TABLE users_subscr (
+    user_id bigint NOT NULL,
+    cnt INTEGER NOT NULL DEFAULT 0
+);
 CREATE TABLE tags_ignore (
     tag_id integer NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
@@ -267,6 +271,7 @@ CREATE TABLE usersinfo (
     gender character varying(32),
     bday character varying(32),
     descr text,
+    UNIQUE(user_id),
     FOREIGN KEY (user_id) REFERENCES users(id)
 );
 CREATE TABLE vk (
-- 
cgit v1.2.3