From 6cd35dbc723a64720c08a331ede9e605df11b8b4 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 28 Jan 2023 19:58:01 +0300 Subject: CI: enable sqlserver job --- src/main/resources/db/migration/V1.39__tags_noindex.sql | 7 +++++++ src/main/resources/schema-h2.sql | 4 ++-- src/main/resources/schema-mysql.sql | 2 +- src/main/resources/schema-sqlite.sql | 4 ++-- src/main/resources/schema-sqlserver.sql | 13 +++++++------ 5 files changed, 19 insertions(+), 11 deletions(-) create mode 100644 src/main/resources/db/migration/V1.39__tags_noindex.sql (limited to 'src/main/resources') diff --git a/src/main/resources/db/migration/V1.39__tags_noindex.sql b/src/main/resources/db/migration/V1.39__tags_noindex.sql new file mode 100644 index 00000000..af37bc66 --- /dev/null +++ b/src/main/resources/db/migration/V1.39__tags_noindex.sql @@ -0,0 +1,7 @@ +alter table tags alter noindex set default null; +alter table tags alter noindex type INTEGER USING CASE WHEN noindex THEN 1 ELSE 0 END; +alter table tags alter noindex set default 0; +alter table tags alter top set default null; +alter table tags alter top type INTEGER USING CASE WHEN top THEN 1 ELSE 0 END; +alter table tags alter top set default 0; +alter table tags rename top to notop \ No newline at end of file diff --git a/src/main/resources/schema-h2.sql b/src/main/resources/schema-h2.sql index 13799b9d..a02804d5 100644 --- a/src/main/resources/schema-h2.sql +++ b/src/main/resources/schema-h2.sql @@ -195,8 +195,8 @@ CREATE MEMORY TABLE "PUBLIC"."TAGS"( "TAG_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 RESTART WITH 3) DEFAULT ON NULL NOT NULL, "SYNONYM_ID" INTEGER DEFAULT NULL, "NAME" VARCHAR_IGNORECASE(70) DEFAULT NULL, - "TOP" BOOLEAN DEFAULT FALSE NOT NULL, - "NOINDEX" BOOLEAN DEFAULT FALSE NOT NULL, + "NOTOP" SMALLINT DEFAULT '0' NOT NULL, + "NOINDEX" SMALLINT DEFAULT '0' NOT NULL, "STAT_MESSAGES" INTEGER DEFAULT '0' NOT NULL, "STAT_USERS" SMALLINT DEFAULT '0' NOT NULL ); diff --git a/src/main/resources/schema-mysql.sql b/src/main/resources/schema-mysql.sql index 2f3f9bfa..5b22bb35 100644 --- a/src/main/resources/schema-mysql.sql +++ b/src/main/resources/schema-mysql.sql @@ -515,7 +515,7 @@ CREATE TABLE `tags` ( `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `synonym_id` int(10) unsigned DEFAULT NULL, `name` char(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, - `top` tinyint(1) unsigned NOT NULL DEFAULT 0, + `notop` tinyint(1) unsigned NOT NULL DEFAULT 0, `noindex` tinyint(1) unsigned NOT NULL DEFAULT 0, `stat_messages` int(10) unsigned NOT NULL DEFAULT 0, `stat_users` smallint(5) unsigned NOT NULL DEFAULT 0, diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql index 3c31381d..b624d67b 100644 --- a/src/main/resources/schema-sqlite.sql +++ b/src/main/resources/schema-sqlite.sql @@ -201,8 +201,8 @@ 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, + notop smallint DEFAULT 0 NOT NULL, + noindex smallint 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) diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql index 5fbe9569..71539577 100644 --- a/src/main/resources/schema-sqlserver.sql +++ b/src/main/resources/schema-sqlserver.sql @@ -9,6 +9,7 @@ CREATE TABLE users ( lastphoto bigint DEFAULT (0) NOT NULL, karma smallint DEFAULT (0) NOT NULL, last_seen datetimeoffset, + UNIQUE(nick), PRIMARY KEY (id) ); CREATE TABLE user_services ( @@ -30,7 +31,7 @@ CREATE TABLE tags ( tag_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL, synonym_id integer, name character varying(70), - [top] bit DEFAULT 0 NOT NULL, + notop 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, @@ -120,7 +121,7 @@ CREATE TABLE meon ( CREATE TABLE messages ( message_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL, user_id bigint NOT NULL, - lang TEXT DEFAULT '__' NOT NULL, + lang varchar(max) DEFAULT '__' NOT NULL, ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, replies smallint DEFAULT (0) NOT NULL, maxreplyid smallint DEFAULT (0) NOT NULL, @@ -143,7 +144,7 @@ CREATE TABLE messages_access ( FOREIGN KEY (message_id) REFERENCES messages(message_id) ); CREATE TABLE messages_properties ( - message_id INTEGER PRIMARY KEY NOT NULL, + message_id INTEGER NOT NULL, reply_id smallint NOT NULL, property_key character varying(255) NOT NULL, property_value character varying(255) NOT NULL, @@ -181,7 +182,7 @@ 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, + txt varchar(max) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (user_id_to) REFERENCES users(id) ); @@ -192,7 +193,7 @@ CREATE TABLE replies ( 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, + txt varchar(max) NOT NULL, updated_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, user_uri character varying(255) DEFAULT NULL, reply_uri character varying(255) DEFAULT NULL, @@ -270,7 +271,7 @@ CREATE TABLE usersinfo ( url character varying(128), gender character varying(32), bday character varying(32), - descr text, + descr varchar(max), UNIQUE(user_id), FOREIGN KEY (user_id) REFERENCES users(id) ); -- cgit v1.2.3