diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/main/resources/application-postgres.properties | 1 | ||||
| -rw-r--r-- | src/main/resources/application.properties | 2 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.22__schema.sql (renamed from src/test/resources/db/specific/postgresql/V1.22__schema.sql) | 0 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.24__fix_lastmessage.sql (renamed from src/main/resources/db/specific/postgresql/V1.24__fix_lastmessage.sql) | 0 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.25__update_tokens_size.sql (renamed from src/main/resources/db/specific/h2/V1.25__update_tokens_size.sql) | 0 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.26__case_insensitive_nick.sql (renamed from src/main/resources/db/specific/postgresql/V1.26__case_insensitive_nick.sql) | 0 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.27__followers_unique.sql (renamed from src/main/resources/db/specific/postgresql/V1.27__followers_unique.sql) | 0 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.35__case_insensitive_email_jid.sql (renamed from src/main/resources/db/specific/postgresql/V1.35__case_insensitive_email_jid.sql) | 0 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.36__web_tokens.sql (renamed from src/main/resources/db/specific/postgresql/V1.36__web_tokens.sql) | 0 | ||||
| -rw-r--r-- | src/main/resources/db/specific/h2/V1.24__fix_lastmessage.sql | 2 | ||||
| -rw-r--r-- | src/main/resources/db/specific/h2/V1.26__case_insensitive_nick.sql | 1 | ||||
| -rw-r--r-- | src/main/resources/db/specific/h2/V1.27__followers_unique.sql | 1 | ||||
| -rw-r--r-- | src/main/resources/db/specific/h2/V1.35__case_insensitive_email_jid.sql | 2 | ||||
| -rw-r--r-- | src/main/resources/db/specific/mariadb/V1.24__fix_lastmessage.sql | 1 | ||||
| -rw-r--r-- | src/main/resources/db/specific/mariadb/V1.25__update_tokens_size.sql | 3 | ||||
| -rw-r--r-- | src/main/resources/db/specific/mariadb/V1.27__followers_unique.sql | 1 | ||||
| -rw-r--r-- | src/main/resources/db/specific/postgresql/V1.25__update_tokens_size.sql | 3 | ||||
| -rw-r--r-- | src/main/resources/schema-h2.sql (renamed from src/main/resources/db/specific/h2/V1.22__schema.sql) | 167 | ||||
| -rw-r--r-- | src/main/resources/schema-mysql.sql (renamed from src/test/resources/db/specific/mariadb/V1.22__schema.sql) | 286 | ||||
| -rw-r--r-- | src/main/resources/schema-sqlite.sql (renamed from src/main/resources/db/specific/sqlite/V1.22__schema.sql) | 100 | ||||
| -rw-r--r-- | src/test/resources/data-mysql.sql (renamed from src/test/resources/db/specific/mariadb/V1.999__data.sql) | 0 | ||||
| -rw-r--r-- | src/test/resources/data-postgresql.sql (renamed from src/test/resources/db/specific/postgresql/V1.999__data.sql) | 0 |
22 files changed, 199 insertions, 371 deletions
diff --git a/src/main/resources/application-postgres.properties b/src/main/resources/application-postgres.properties index 05dbc12d..c3697a7c 100644 --- a/src/main/resources/application-postgres.properties +++ b/src/main/resources/application-postgres.properties @@ -1 +1,2 @@ spring.sql.init.platform=postgresql +spring.flyway.enabled=true
\ No newline at end of file diff --git a/src/main/resources/application.properties b/src/main/resources/application.properties index ecea6a79..989e1c63 100644 --- a/src/main/resources/application.properties +++ b/src/main/resources/application.properties @@ -5,7 +5,7 @@ spring.jackson.serialization.write-dates-as-timestamps=false spring.jackson.serialization.write-empty-json-arrays=true spring.h2.console.enabled=true spring.datasource.generate-unique-name=false -spring.flyway.locations=classpath:db/migration,classpath:db/specific/{vendor} +spring.flyway.enabled=false spring.sql.init.platform=h2 spring.cache.type=simple spring.cache.cache-names=help, usernames, users_by_name, discover, discussions, messages, replies, popular_tags, profiles, twitter_user diff --git a/src/test/resources/db/specific/postgresql/V1.22__schema.sql b/src/main/resources/db/migration/V1.22__schema.sql index aab050a6..aab050a6 100644 --- a/src/test/resources/db/specific/postgresql/V1.22__schema.sql +++ b/src/main/resources/db/migration/V1.22__schema.sql diff --git a/src/main/resources/db/specific/postgresql/V1.24__fix_lastmessage.sql b/src/main/resources/db/migration/V1.24__fix_lastmessage.sql index ba3eee8c..ba3eee8c 100644 --- a/src/main/resources/db/specific/postgresql/V1.24__fix_lastmessage.sql +++ b/src/main/resources/db/migration/V1.24__fix_lastmessage.sql diff --git a/src/main/resources/db/specific/h2/V1.25__update_tokens_size.sql b/src/main/resources/db/migration/V1.25__update_tokens_size.sql index a1c7bbac..a1c7bbac 100644 --- a/src/main/resources/db/specific/h2/V1.25__update_tokens_size.sql +++ b/src/main/resources/db/migration/V1.25__update_tokens_size.sql diff --git a/src/main/resources/db/specific/postgresql/V1.26__case_insensitive_nick.sql b/src/main/resources/db/migration/V1.26__case_insensitive_nick.sql index a68c15f3..a68c15f3 100644 --- a/src/main/resources/db/specific/postgresql/V1.26__case_insensitive_nick.sql +++ b/src/main/resources/db/migration/V1.26__case_insensitive_nick.sql diff --git a/src/main/resources/db/specific/postgresql/V1.27__followers_unique.sql b/src/main/resources/db/migration/V1.27__followers_unique.sql index 5a15d9ae..5a15d9ae 100644 --- a/src/main/resources/db/specific/postgresql/V1.27__followers_unique.sql +++ b/src/main/resources/db/migration/V1.27__followers_unique.sql diff --git a/src/main/resources/db/specific/postgresql/V1.35__case_insensitive_email_jid.sql b/src/main/resources/db/migration/V1.35__case_insensitive_email_jid.sql index 93a987b8..93a987b8 100644 --- a/src/main/resources/db/specific/postgresql/V1.35__case_insensitive_email_jid.sql +++ b/src/main/resources/db/migration/V1.35__case_insensitive_email_jid.sql diff --git a/src/main/resources/db/specific/postgresql/V1.36__web_tokens.sql b/src/main/resources/db/migration/V1.36__web_tokens.sql index ccdefad2..ccdefad2 100644 --- a/src/main/resources/db/specific/postgresql/V1.36__web_tokens.sql +++ b/src/main/resources/db/migration/V1.36__web_tokens.sql diff --git a/src/main/resources/db/specific/h2/V1.24__fix_lastmessage.sql b/src/main/resources/db/specific/h2/V1.24__fix_lastmessage.sql deleted file mode 100644 index bcca2b50..00000000 --- a/src/main/resources/db/specific/h2/V1.24__fix_lastmessage.sql +++ /dev/null @@ -1,2 +0,0 @@ -ALTER TABLE users ALTER COLUMN lastmessage SET NULL - diff --git a/src/main/resources/db/specific/h2/V1.26__case_insensitive_nick.sql b/src/main/resources/db/specific/h2/V1.26__case_insensitive_nick.sql deleted file mode 100644 index 3b17aa00..00000000 --- a/src/main/resources/db/specific/h2/V1.26__case_insensitive_nick.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE users ALTER COLUMN nick SET DATA TYPE VARCHAR_IGNORECASE(64) diff --git a/src/main/resources/db/specific/h2/V1.27__followers_unique.sql b/src/main/resources/db/specific/h2/V1.27__followers_unique.sql deleted file mode 100644 index e103d1a0..00000000 --- a/src/main/resources/db/specific/h2/V1.27__followers_unique.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE followers ADD UNIQUE (user_id, acct)
\ No newline at end of file diff --git a/src/main/resources/db/specific/h2/V1.35__case_insensitive_email_jid.sql b/src/main/resources/db/specific/h2/V1.35__case_insensitive_email_jid.sql deleted file mode 100644 index a20debf6..00000000 --- a/src/main/resources/db/specific/h2/V1.35__case_insensitive_email_jid.sql +++ /dev/null @@ -1,2 +0,0 @@ -ALTER TABLE jids ALTER COLUMN jid SET DATA TYPE VARCHAR_IGNORECASE(255); -ALTER TABLE emails ALTER COLUMN email SET DATA TYPE VARCHAR_IGNORECASE(255) diff --git a/src/main/resources/db/specific/mariadb/V1.24__fix_lastmessage.sql b/src/main/resources/db/specific/mariadb/V1.24__fix_lastmessage.sql deleted file mode 100644 index 399abf01..00000000 --- a/src/main/resources/db/specific/mariadb/V1.24__fix_lastmessage.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE users MODIFY COLUMN lastmessage TIMESTAMP(6) NULL
\ No newline at end of file diff --git a/src/main/resources/db/specific/mariadb/V1.25__update_tokens_size.sql b/src/main/resources/db/specific/mariadb/V1.25__update_tokens_size.sql deleted file mode 100644 index 05eefe24..00000000 --- a/src/main/resources/db/specific/mariadb/V1.25__update_tokens_size.sql +++ /dev/null @@ -1,3 +0,0 @@ -ALTER TABLE vk MODIFY COLUMN access_token VARCHAR(255); -ALTER TABLE twitter MODIFY COLUMN access_token VARCHAR(255); -ALTER TABLE facebook MODIFY COLUMN access_token VARCHAR(255);
\ No newline at end of file diff --git a/src/main/resources/db/specific/mariadb/V1.27__followers_unique.sql b/src/main/resources/db/specific/mariadb/V1.27__followers_unique.sql deleted file mode 100644 index e103d1a0..00000000 --- a/src/main/resources/db/specific/mariadb/V1.27__followers_unique.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE followers ADD UNIQUE (user_id, acct)
\ No newline at end of file diff --git a/src/main/resources/db/specific/postgresql/V1.25__update_tokens_size.sql b/src/main/resources/db/specific/postgresql/V1.25__update_tokens_size.sql deleted file mode 100644 index a1c7bbac..00000000 --- a/src/main/resources/db/specific/postgresql/V1.25__update_tokens_size.sql +++ /dev/null @@ -1,3 +0,0 @@ -ALTER TABLE vk ALTER COLUMN access_token TYPE character varying(255); -ALTER TABLE twitter ALTER COLUMN access_token TYPE character varying(255); -ALTER TABLE facebook ALTER COLUMN access_token TYPE character varying(255);
\ No newline at end of file diff --git a/src/main/resources/db/specific/h2/V1.22__schema.sql b/src/main/resources/schema-h2.sql index e616c943..43320fb6 100644 --- a/src/main/resources/db/specific/h2/V1.22__schema.sql +++ b/src/main/resources/schema-h2.sql @@ -1,31 +1,21 @@ -- H2 2.1.214; -SET DB_CLOSE_DELAY -1; - -CREATE MEMORY TABLE "PUBLIC"."ANDROID"( - "USER_ID" INTEGER NOT NULL, - "REGID" VARCHAR(1024) NOT NULL, +CREATE MEMORY TABLE "PUBLIC"."JIDS"( + "USER_ID" INTEGER DEFAULT NULL, + "JID" VARCHAR_IGNORECASE(255) NOT NULL, + "ACTIVE" TINYINT DEFAULT '1' NOT NULL, + "LOGINHASH" CHARACTER(36) DEFAULT NULL, "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.ANDROID; -CREATE MEMORY TABLE "PUBLIC"."MAIL"( - "USER_ID" INTEGER NOT NULL, - "HASH" CHARACTER(16) NOT NULL, - "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL -); -ALTER TABLE "PUBLIC"."MAIL" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_23" PRIMARY KEY("USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MAIL; CREATE MEMORY TABLE "PUBLIC"."BL_TAGS"( "USER_ID" INTEGER NOT NULL, "TAG_ID" INTEGER NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.BL_TAGS; CREATE MEMORY TABLE "PUBLIC"."BL_USERS"( "USER_ID" INTEGER NOT NULL, "BL_USER_ID" INTEGER NOT NULL, "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ); ALTER TABLE "PUBLIC"."BL_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_2" PRIMARY KEY("USER_ID", "BL_USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.BL_USERS; CREATE MEMORY TABLE "PUBLIC"."REACTIONS"( "LIKE_ID" INTEGER NOT NULL, "DESCRIPTION" CHARACTER VARYING(100) NOT NULL @@ -35,9 +25,8 @@ CREATE MEMORY TABLE "PUBLIC"."FAVORITES"( "MESSAGE_ID" INTEGER NOT NULL, "TS" TIMESTAMP NOT NULL, "LIKE_ID" INTEGER, - "USER_URI" VARCHAR(255) DEFAULT '' NOT NULL + "USER_URI" CHARACTER VARYING(255) DEFAULT '' NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.FAVORITES; CREATE MEMORY TABLE "PUBLIC"."AUTH"( "USER_ID" INTEGER, "PROTOCOL" ENUM('xmpp', 'email', 'sms') NOT NULL, @@ -45,7 +34,6 @@ CREATE MEMORY TABLE "PUBLIC"."AUTH"( "AUTHCODE" CHARACTER(8) NOT NULL, "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.AUTH; CREATE MEMORY TABLE "PUBLIC"."IMAGES"( "MID" INTEGER NOT NULL, "RID" INTEGER NOT NULL, @@ -56,14 +44,12 @@ CREATE MEMORY TABLE "PUBLIC"."IMAGES"( "WIDTH" INTEGER NOT NULL ); ALTER TABLE "PUBLIC"."IMAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_8" PRIMARY KEY("MID", "RID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.IMAGES; CREATE MEMORY TABLE "PUBLIC"."MESSAGES_PROPERTIES"( "MESSAGE_ID" INTEGER NOT NULL, "REPLY_ID" SMALLINT NOT NULL, "PROPERTY_KEY" CHARACTER VARYING(255) NOT NULL, "PROPERTY_VALUE" CHARACTER VARYING NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSAGES_PROPERTIES; CREATE MEMORY TABLE "PUBLIC"."MEON"( "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL, "USER_ID" INTEGER NOT NULL, @@ -72,51 +58,43 @@ CREATE MEMORY TABLE "PUBLIC"."MEON"( "ICO" SMALLINT DEFAULT NULL ); ALTER TABLE "PUBLIC"."MEON" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_24" PRIMARY KEY("ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MEON; CREATE MEMORY TABLE "PUBLIC"."MESSAGES_ACCESS"( "MESSAGE_ID" INTEGER NOT NULL, "USER_ID" INTEGER NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSAGES_ACCESS; CREATE MEMORY TABLE "PUBLIC"."MESSAGES_TAGS"( "MESSAGE_ID" INTEGER NOT NULL, "TAG_ID" INTEGER NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSAGES_TAGS; -CREATE MEMORY TABLE "PUBLIC"."USEROPTIONS"( - "USER_ID" INTEGER NOT NULL, - "JNOTIFY" TINYINT DEFAULT '1' NOT NULL, - "SUBSCR_ACTIVE" TINYINT DEFAULT '1' NOT NULL, - "XMPPXHTML" TINYINT DEFAULT '0' NOT NULL, - "SUBSCR_NOTIFY" TINYINT DEFAULT '1' NOT NULL, - "OFF_TS" TIMESTAMP, - "RECOMMENDATIONS" TINYINT DEFAULT '1' NOT NULL, - "PRIVACY_VIEW" TINYINT DEFAULT '1' NOT NULL, - "PRIVACY_REPLY" TINYINT DEFAULT '1' NOT NULL, - "PRIVACY_PM" TINYINT DEFAULT '1' NOT NULL, - "REPLIESVIEW" TINYINT DEFAULT '0' NOT NULL -); -ALTER TABLE "PUBLIC"."USEROPTIONS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_C0" PRIMARY KEY("USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.USEROPTIONS; +CREATE MEMORY TABLE "PUBLIC"."USERS"( + "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0 RESTART WITH 6 MINVALUE 0) DEFAULT ON NULL NOT NULL, + "NICK" VARCHAR_IGNORECASE(64) NOT NULL, + "PASSW" CHARACTER VARYING(32) NOT NULL, + "LANG" ENUM('en', 'ru', 'fr', 'fa', '__') DEFAULT '__' NOT NULL, + "BANNED" TINYINT DEFAULT '0' NOT NULL, + "LASTMESSAGE" TIMESTAMP(6), + "LASTPM" INTEGER DEFAULT '0' NOT NULL, + "LASTPHOTO" INTEGER DEFAULT '0' NOT NULL, + "KARMA" SMALLINT DEFAULT '0' NOT NULL, + "LAST_SEEN" TIMESTAMP(6) +); +ALTER TABLE "PUBLIC"."USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_4" PRIMARY KEY("ID"); CREATE MEMORY TABLE "PUBLIC"."PM"( "USER_ID" INTEGER NOT NULL, "USER_ID_TO" INTEGER NOT NULL, "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL, "TXT" CHARACTER VARYING NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.PM; CREATE MEMORY TABLE "PUBLIC"."FOLLOWERS"( "USER_ID" INTEGER DEFAULT NULL, "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, - "ACCT" VARCHAR(64) NOT NULL + "ACCT" CHARACTER VARYING(64) NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.FOLLOWERS; CREATE MEMORY TABLE "PUBLIC"."SUBSCR_USERS"( "USER_ID" INTEGER NOT NULL, "SUSER_ID" INTEGER NOT NULL, "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.SUBSCR_USERS; CREATE MEMORY TABLE "PUBLIC"."MESSENGER"( "USER_ID" INTEGER DEFAULT NULL, "SENDER_ID" BIGINT NOT NULL, @@ -124,7 +102,6 @@ CREATE MEMORY TABLE "PUBLIC"."MESSENGER"( "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, "LOGINHASH" CHARACTER(36) DEFAULT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSENGER; CREATE MEMORY TABLE "PUBLIC"."PLACES"( "PLACE_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL, "LAT" DECIMAL(10, 7) NOT NULL, @@ -136,12 +113,10 @@ CREATE MEMORY TABLE "PUBLIC"."PLACES"( "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ); ALTER TABLE "PUBLIC"."PLACES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_8C" PRIMARY KEY("PLACE_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.PLACES; CREATE MEMORY TABLE "PUBLIC"."PLACES_TAGS"( "PLACE_ID" INTEGER NOT NULL, "TAG_ID" INTEGER NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.PLACES_TAGS; CREATE MEMORY TABLE "PUBLIC"."REPLIES"( "MESSAGE_ID" INTEGER NOT NULL, "REPLY_ID" SMALLINT NOT NULL, @@ -151,11 +126,10 @@ CREATE MEMORY TABLE "PUBLIC"."REPLIES"( "ATTACH" CHARACTER(3), "TXT" CHARACTER VARYING NOT NULL, "UPDATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL, - "USER_URI" VARCHAR(255) DEFAULT NULL, - "REPLY_URI" VARCHAR(255) DEFAULT NULL, + "USER_URI" CHARACTER VARYING(255) DEFAULT NULL, + "REPLY_URI" CHARACTER VARYING(255) DEFAULT NULL, "HTML" TINYINT DEFAULT '0' NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.REPLIES; CREATE INDEX "PUBLIC"."REPLY_URI_INDEX" ON "PUBLIC"."REPLIES"("REPLY_URI" NULLS FIRST); CREATE MEMORY TABLE "PUBLIC"."MESSAGES_TXT"( "MESSAGE_ID" INTEGER NOT NULL, @@ -164,7 +138,6 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES_TXT"( "UPDATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL ); ALTER TABLE "PUBLIC"."MESSAGES_TXT" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_3E" PRIMARY KEY("MESSAGE_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSAGES_TXT; CREATE MEMORY TABLE "PUBLIC"."MESSAGES"( "MESSAGE_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL, "USER_ID" INTEGER NOT NULL, @@ -184,42 +157,40 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES"( "UPDATED" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL ); ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_13" PRIMARY KEY("MESSAGE_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.MESSAGES; CREATE MEMORY TABLE "PUBLIC"."VK"( "USER_ID" INTEGER DEFAULT NULL, "VK_ID" BIGINT NOT NULL, "LOGINHASH" CHARACTER(36) DEFAULT NULL, - "ACCESS_TOKEN" CHARACTER VARYING(64), + "ACCESS_TOKEN" CHARACTER VARYING(255), "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, "VK_NAME" CHARACTER(64) NOT NULL, "VK_LINK" CHARACTER(64) NOT NULL, "CROSSPOST" SMALLINT DEFAULT '1' NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.VK; CREATE MEMORY TABLE "PUBLIC"."SUBSCR_MESSAGES"( "MESSAGE_ID" INTEGER NOT NULL, "SUSER_ID" INTEGER NOT NULL, "LAST_READ_RID" SMALLINT DEFAULT '0' NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.SUBSCR_MESSAGES; CREATE MEMORY TABLE "PUBLIC"."SUBSCR_TAGS"( "TAG_ID" INTEGER NOT NULL, "SUSER_ID" INTEGER NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.SUBSCR_TAGS; -CREATE MEMORY TABLE "PUBLIC"."USERS"( - "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0 RESTART WITH 6 MINVALUE 0) DEFAULT ON NULL NOT NULL, - "NICK" VARCHAR(64) NOT NULL, - "PASSW" VARCHAR(32) NOT NULL, - "LANG" ENUM('en', 'ru', 'fr', 'fa', '__') DEFAULT '__' NOT NULL, - "BANNED" TINYINT DEFAULT '0' NOT NULL, - "LASTMESSAGE" TIMESTAMP(6) NOT NULL, - "LASTPM" INTEGER DEFAULT '0' NOT NULL, - "LASTPHOTO" INTEGER DEFAULT '0' NOT NULL, - "KARMA" SMALLINT DEFAULT '0' NOT NULL, - "LAST_SEEN" TIMESTAMP(6) -); -ALTER TABLE "PUBLIC"."USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_4" PRIMARY KEY("ID"); +CREATE MEMORY TABLE "PUBLIC"."OAUTH2_REGISTERED_CLIENT"( + "ID" CHARACTER VARYING(100) NOT NULL, + "CLIENT_ID" CHARACTER VARYING(100) NOT NULL, + "CLIENT_ID_ISSUED_AT" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + "CLIENT_SECRET" CHARACTER VARYING(200) DEFAULT NULL, + "CLIENT_SECRET_EXPIRES_AT" TIMESTAMP DEFAULT NULL, + "CLIENT_NAME" CHARACTER VARYING(200) NOT NULL, + "CLIENT_AUTHENTICATION_METHODS" CHARACTER VARYING(1000) NOT NULL, + "AUTHORIZATION_GRANT_TYPES" CHARACTER VARYING(1000) NOT NULL, + "REDIRECT_URIS" CHARACTER VARYING(1000) DEFAULT NULL, + "SCOPES" CHARACTER VARYING(1000) NOT NULL, + "CLIENT_SETTINGS" CHARACTER VARYING(2000) NOT NULL, + "TOKEN_SETTINGS" CHARACTER VARYING(2000) NOT NULL +); +ALTER TABLE "PUBLIC"."OAUTH2_REGISTERED_CLIENT" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_83" PRIMARY KEY("ID"); 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, @@ -233,12 +204,10 @@ ALTER TABLE "PUBLIC"."TAGS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_27" PRIMARY KEY( CREATE MEMORY TABLE "PUBLIC"."TAGS_IGNORE"( "TAG_ID" INTEGER NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.TAGS_IGNORE; CREATE MEMORY TABLE "PUBLIC"."TAGS_SYNONYMS"( "NAME" CHARACTER(64) NOT NULL, "CHANGETO" CHARACTER(64) NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.TAGS_SYNONYMS; CREATE MEMORY TABLE "PUBLIC"."TELEGRAM"( "USER_ID" INTEGER DEFAULT NULL, "TG_ID" BIGINT NOT NULL, @@ -246,127 +215,91 @@ CREATE MEMORY TABLE "PUBLIC"."TELEGRAM"( "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, "LOGINHASH" CHARACTER(36) DEFAULT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.TELEGRAM; CREATE MEMORY TABLE "PUBLIC"."TOP_IGNORE_MESSAGES"( "MESSAGE_ID" INTEGER NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.TOP_IGNORE_MESSAGES; CREATE MEMORY TABLE "PUBLIC"."TOP_IGNORE_TAGS"( "TAG_ID" INTEGER NOT NULL ); ALTER TABLE "PUBLIC"."TOP_IGNORE_TAGS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_7" PRIMARY KEY("TAG_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.TOP_IGNORE_TAGS; CREATE MEMORY TABLE "PUBLIC"."TOP_IGNORE_USERS"( "USER_ID" INTEGER NOT NULL ); ALTER TABLE "PUBLIC"."TOP_IGNORE_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_B2" PRIMARY KEY("USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.TOP_IGNORE_USERS; CREATE MEMORY TABLE "PUBLIC"."FACEBOOK"( "USER_ID" INTEGER DEFAULT NULL, "FB_ID" BIGINT, "LOGINHASH" CHARACTER(36) DEFAULT NULL, - "ACCESS_TOKEN" CHARACTER VARYING(64), + "ACCESS_TOKEN" CHARACTER VARYING(255), "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, "FB_NAME" CHARACTER(64), "FB_LINK" CHARACTER(255), "CROSSPOST" TINYINT DEFAULT '1' NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.FACEBOOK; CREATE MEMORY TABLE "PUBLIC"."TWITTER"( "USER_ID" INTEGER NOT NULL, - "ACCESS_TOKEN" CHARACTER VARYING(64), + "ACCESS_TOKEN" CHARACTER VARYING(255), "ACCESS_TOKEN_SECRET" CHARACTER(64) NOT NULL, "UNAME" CHARACTER(64) NOT NULL, "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, "CROSSPOST" TINYINT DEFAULT '1' NOT NULL ); ALTER TABLE "PUBLIC"."TWITTER" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_F" PRIMARY KEY("USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.TWITTER; CREATE MEMORY TABLE "PUBLIC"."USERS_SUBSCR"( "USER_ID" INTEGER NOT NULL, "CNT" SMALLINT DEFAULT '0' NOT NULL ); ALTER TABLE "PUBLIC"."USERS_SUBSCR" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_BB" PRIMARY KEY("USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.USERS_SUBSCR; CREATE MEMORY TABLE "PUBLIC"."USERSINFO"( "USER_ID" INTEGER NOT NULL, "JID" CHARACTER(32) DEFAULT NULL, - "FULLNAME" VARCHAR(32) DEFAULT NULL, - "COUNTRY" VARCHAR(32) DEFAULT NULL, + "FULLNAME" CHARACTER VARYING(32) DEFAULT NULL, + "COUNTRY" CHARACTER VARYING(32) DEFAULT NULL, "URL" CHARACTER(64) DEFAULT NULL, "GENDER" CHARACTER(32) DEFAULT NULL, "BDAY" CHARACTER(10) DEFAULT NULL, "DESCR" CHARACTER VARYING(255) DEFAULT NULL ); ALTER TABLE "PUBLIC"."USERSINFO" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_BB6" PRIMARY KEY("USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.USERSINFO; CREATE MEMORY TABLE "PUBLIC"."EMAILS"( "USER_ID" INTEGER NOT NULL, - "EMAIL" VARCHAR(64) NOT NULL, + "EMAIL" VARCHAR_IGNORECASE(255) NOT NULL, "SUBSCR_HOUR" TINYINT DEFAULT NULL ); ALTER TABLE "PUBLIC"."EMAILS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_7A" PRIMARY KEY("EMAIL"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.EMAILS; -CREATE MEMORY TABLE "PUBLIC"."IOS"( - "USER_ID" INTEGER NOT NULL, - "TOKEN" VARCHAR(64) NOT NULL, - "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL -); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.IOS; -CREATE MEMORY TABLE "PUBLIC"."JIDS"( - "USER_ID" INTEGER DEFAULT NULL, - "JID" CHARACTER(64) NOT NULL, - "ACTIVE" TINYINT DEFAULT '1' NOT NULL, - "LOGINHASH" CHARACTER(36) DEFAULT NULL, - "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL -); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.JIDS; CREATE MEMORY TABLE "PUBLIC"."LOGINS"( "USER_ID" INTEGER NOT NULL, "HASH" CHARACTER(16) NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.LOGINS; -CREATE MEMORY TABLE "PUBLIC"."PM_INROSTER"( - "USER_ID" INTEGER NOT NULL, - "JID" CHARACTER(64) NOT NULL -); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.PM_INROSTER; CREATE MEMORY TABLE "PUBLIC"."VERSION"( "VERSION" BIGINT NOT NULL ); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.VERSION; -CREATE MEMORY TABLE "PUBLIC"."WINPHONE"( - "USER_ID" INTEGER NOT NULL, - "URL" CHARACTER(255) NOT NULL, - "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL -); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.WINPHONE; CREATE MEMORY TABLE "PUBLIC"."WL_USERS"( "USER_ID" INTEGER NOT NULL, "WL_USER_ID" INTEGER NOT NULL ); ALTER TABLE "PUBLIC"."WL_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_A8" PRIMARY KEY("USER_ID", "WL_USER_ID"); --- 0 +/- SELECT COUNT(*) FROM PUBLIC.WL_USERS; +CREATE MEMORY TABLE "PUBLIC"."USER_SERVICES"( + "USER_ID" INTEGER NOT NULL, + "REGID" CHARACTER VARYING(1024) NOT NULL, + "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + "SERVICE_TYPE" CHARACTER VARYING(255) DEFAULT 'fcm' NOT NULL +); ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_131" CHECK("ATTACH" IN('jpg', 'mp4', 'png')) NOCHECK; ALTER TABLE "PUBLIC"."REPLIES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_6C" CHECK("ATTACH" IN('jpg', 'mp4', 'png')) NOCHECK; ALTER TABLE "PUBLIC"."LOGINS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_86" UNIQUE("USER_ID"); -ALTER TABLE "PUBLIC"."PM_INROSTER" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_5" UNIQUE("USER_ID", "JID"); ALTER TABLE "PUBLIC"."REACTIONS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_6" UNIQUE("LIKE_ID"); -ALTER TABLE "PUBLIC"."IOS" ADD CONSTRAINT "PUBLIC"."TOKEN" UNIQUE("TOKEN"); ALTER TABLE "PUBLIC"."SUBSCR_TAGS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_B" UNIQUE("TAG_ID", "SUSER_ID"); +ALTER TABLE "PUBLIC"."FOLLOWERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_D3" UNIQUE("USER_ID", "ACCT"); ALTER TABLE "PUBLIC"."SUBSCR_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_C" UNIQUE("USER_ID", "SUSER_ID"); ALTER TABLE "PUBLIC"."MESSAGES_PROPERTIES" ADD CONSTRAINT "PUBLIC"."MESSAGE_KEY" UNIQUE("MESSAGE_ID", "REPLY_ID", "PROPERTY_KEY"); ALTER TABLE "PUBLIC"."USERS" ADD CONSTRAINT "PUBLIC"."NICK" UNIQUE("NICK"); ALTER TABLE "PUBLIC"."MESSAGES_TAGS" ADD CONSTRAINT "PUBLIC"."MESSAGE_ID_2" UNIQUE("MESSAGE_ID", "TAG_ID"); ALTER TABLE "PUBLIC"."SUBSCR_MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_6D" UNIQUE("MESSAGE_ID", "SUSER_ID"); ALTER TABLE "PUBLIC"."JIDS" ADD CONSTRAINT "PUBLIC"."JID" UNIQUE("JID"); -ALTER TABLE "PUBLIC"."WINPHONE" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_EC" UNIQUE("URL"); ALTER TABLE "PUBLIC"."VK" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_A" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; ALTER TABLE "PUBLIC"."FAVORITES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_3" FOREIGN KEY("LIKE_ID") REFERENCES "PUBLIC"."REACTIONS"("LIKE_ID") NOCHECK; -ALTER TABLE "PUBLIC"."IOS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_1" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_131A" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; ALTER TABLE "PUBLIC"."FOLLOWERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_D" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; ALTER TABLE "PUBLIC"."JIDS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_22" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; ALTER TABLE "PUBLIC"."EMAILS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_7A1" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; -ALTER TABLE "PUBLIC"."PM_INROSTER" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_54" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; -ALTER TABLE "PUBLIC"."WINPHONE" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_ECD" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK; diff --git a/src/test/resources/db/specific/mariadb/V1.22__schema.sql b/src/main/resources/schema-mysql.sql index 9d5e2ff0..5ffaba3c 100644 --- a/src/test/resources/db/specific/mariadb/V1.22__schema.sql +++ b/src/main/resources/schema-mysql.sql @@ -1,8 +1,8 @@ --- MariaDB dump 10.19 Distrib 10.5.15-MariaDB, for debian-linux-gnu (x86_64) +-- MariaDB dump 10.19 Distrib 10.5.18-MariaDB, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: juick -- ------------------------------------------------------ --- Server version 10.5.15-MariaDB-0+deb11u1 +-- Server version 10.5.18-MariaDB-0+deb11u1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -16,22 +16,6 @@ /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- --- Table structure for table `android` --- - -DROP TABLE IF EXISTS `android`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `android` ( - `user_id` int(10) unsigned NOT NULL, - `regid` text NOT NULL, - `ts` timestamp NOT NULL DEFAULT current_timestamp(), - UNIQUE KEY `regid` (`regid`), - KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- -- Table structure for table `auth` -- @@ -41,10 +25,10 @@ DROP TABLE IF EXISTS `auth`; CREATE TABLE `auth` ( `user_id` int(10) unsigned DEFAULT NULL, `protocol` enum('xmpp','email','sms') NOT NULL, - `account` char(64) NOT NULL, + `account` char(128) NOT NULL, `authcode` char(8) NOT NULL, `ts` timestamp(6) NOT NULL DEFAULT current_timestamp(6) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -59,7 +43,7 @@ CREATE TABLE `bl_tags` ( `tag_id` int(10) unsigned NOT NULL, KEY `tag_id` (`tag_id`), KEY `user_id` (`user_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -74,7 +58,7 @@ CREATE TABLE `bl_users` ( `bl_user_id` int(10) unsigned NOT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`user_id`,`bl_user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -86,10 +70,10 @@ DROP TABLE IF EXISTS `emails`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `emails` ( `user_id` int(10) unsigned NOT NULL, - `email` char(64) NOT NULL, + `email` char(128) NOT NULL, `subscr_hour` tinyint(4) DEFAULT NULL, KEY `email` (`email`) USING HASH -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -103,13 +87,13 @@ CREATE TABLE `facebook` ( `user_id` int(10) unsigned DEFAULT NULL, `fb_id` bigint(20) unsigned DEFAULT NULL, `loginhash` char(36) DEFAULT NULL, - `access_token` char(255) DEFAULT NULL, + `access_token` varchar(255) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp(), `fb_name` char(64) DEFAULT NULL, `fb_link` char(255) DEFAULT NULL, `crosspost` tinyint(1) unsigned NOT NULL DEFAULT 1, KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -125,10 +109,10 @@ CREATE TABLE `favorites` ( `ts` datetime NOT NULL, `like_id` int(10) unsigned NOT NULL DEFAULT 1, `user_uri` char(255) NOT NULL DEFAULT '', - KEY `user_id` (`user_id`), - KEY `message_id` (`message_id`), - KEY `like_id` (`like_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; + KEY `like_id` (`like_id`), + KEY `fav_index` (`user_id`,`message_id`), + KEY `fav_message_index` (`message_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -139,12 +123,12 @@ DROP TABLE IF EXISTS `followers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `followers` ( - `user_id` int(10) unsigned DEFAULT NULL, + `user_id` int(10) unsigned NOT NULL DEFAULT 0, `ts` timestamp NOT NULL DEFAULT current_timestamp(), `acct` char(64) NOT NULL, - KEY `user_id` (`user_id`), - CONSTRAINT `followers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + UNIQUE KEY `user_id_2` (`user_id`,`acct`), + KEY `user_id` (`user_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -167,22 +151,6 @@ CREATE TABLE `images` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `ios` --- - -DROP TABLE IF EXISTS `ios`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `ios` ( - `user_id` int(10) unsigned NOT NULL, - `token` char(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `ts` timestamp NOT NULL DEFAULT current_timestamp(), - UNIQUE KEY `token` (`token`), - KEY `user_id` (`user_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - --- -- Table structure for table `jids` -- @@ -197,7 +165,7 @@ CREATE TABLE `jids` ( `ts` timestamp NOT NULL DEFAULT current_timestamp(), UNIQUE KEY `jid` (`jid`), KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -211,22 +179,7 @@ CREATE TABLE `logins` ( `user_id` int(10) unsigned NOT NULL, `hash` char(16) NOT NULL, UNIQUE KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `mail` --- - -DROP TABLE IF EXISTS `mail`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `mail` ( - `user_id` int(10) unsigned NOT NULL, - `hash` char(16) NOT NULL, - `ts` timestamp(6) NOT NULL DEFAULT current_timestamp(6), - PRIMARY KEY (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -243,7 +196,7 @@ CREATE TABLE `meon` ( `name` char(32) NOT NULL, `ico` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -278,7 +231,7 @@ CREATE TABLE `messages` ( KEY `popular` (`popular`), KEY `hidden` (`hidden`), KEY `updated_indx` (`updated`,`message_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -292,7 +245,7 @@ CREATE TABLE `messages_access` ( `message_id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, KEY `message_id` (`message_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -308,7 +261,7 @@ CREATE TABLE `messages_properties` ( `property_key` varchar(255) NOT NULL, `property_value` mediumtext NOT NULL, UNIQUE KEY `message_key` (`message_id`,`reply_id`,`property_key`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -324,7 +277,7 @@ CREATE TABLE `messages_tags` ( UNIQUE KEY `message_id_2` (`message_id`,`tag_id`), KEY `message_id` (`message_id`), KEY `tag_id` (`tag_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -336,8 +289,8 @@ DROP TABLE IF EXISTS `messages_txt`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `messages_txt` ( `message_id` int(10) unsigned NOT NULL, - `repliesby` varchar(96) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `txt` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `repliesby` varchar(96) DEFAULT NULL, + `txt` mediumtext NOT NULL, `updated_at` timestamp(6) NOT NULL DEFAULT current_timestamp(6), PRIMARY KEY (`message_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -356,7 +309,31 @@ CREATE TABLE `messenger` ( `display_name` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp(), `loginhash` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `oauth2_registered_client` +-- + +DROP TABLE IF EXISTS `oauth2_registered_client`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `oauth2_registered_client` ( + `id` varchar(100) NOT NULL, + `client_id` varchar(100) NOT NULL, + `client_id_issued_at` timestamp NOT NULL DEFAULT current_timestamp(), + `client_secret` varchar(200) DEFAULT NULL, + `client_secret_expires_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -376,7 +353,7 @@ CREATE TABLE `places` ( `user_id` int(10) unsigned NOT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`place_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -389,7 +366,7 @@ DROP TABLE IF EXISTS `places_tags`; CREATE TABLE `places_tags` ( `place_id` int(10) unsigned NOT NULL, `tag_id` int(10) unsigned NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -403,23 +380,11 @@ CREATE TABLE `pm` ( `user_id` int(10) unsigned NOT NULL, `user_id_to` int(10) unsigned NOT NULL, `ts` timestamp(6) NOT NULL DEFAULT current_timestamp(6), - `txt` text NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `pm_inroster` --- - -DROP TABLE IF EXISTS `pm_inroster`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `pm_inroster` ( - `user_id` int(10) unsigned NOT NULL, - `jid` char(64) NOT NULL, - UNIQUE KEY `user_id_2` (`user_id`,`jid`), - KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; + `txt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, + KEY `user_indx` (`user_id`), + KEY `user_to_indx` (`user_id_to`), + KEY `ts_indx` (`ts`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -432,7 +397,7 @@ DROP TABLE IF EXISTS `reactions`; CREATE TABLE `reactions` ( `like_id` int(10) unsigned NOT NULL, `description` varchar(100) NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -448,11 +413,11 @@ CREATE TABLE `replies` ( `user_id` int(10) unsigned NOT NULL, `replyto` smallint(5) unsigned NOT NULL DEFAULT 0, `ts` timestamp(6) NOT NULL DEFAULT current_timestamp(6), - `attach` enum('jpg','mp4','png') COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `txt` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, + `attach` enum('jpg','mp4','png') DEFAULT NULL, + `txt` mediumtext NOT NULL, `updated_at` timestamp(6) NOT NULL DEFAULT current_timestamp(6), - `user_uri` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `reply_uri` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `user_uri` char(255) DEFAULT NULL, + `reply_uri` char(255) DEFAULT NULL, `html` tinyint(4) NOT NULL DEFAULT 0, KEY `ts` (`ts`), KEY `message_id` (`message_id`), @@ -463,6 +428,36 @@ CREATE TABLE `replies` ( /*!40101 SET character_set_client = @saved_cs_client */; -- +-- Table structure for table `search` +-- + +DROP TABLE IF EXISTS `search`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `search` ( + `id` bigint(20) unsigned NOT NULL, + `weight` int(11) NOT NULL, + `query` varchar(3072) NOT NULL, + `group_id` int(11) DEFAULT NULL, + KEY `query` (`query`(768)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CONNECTION='sphinx://127.0.0.1:3312/messages'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `sphinx` +-- + +DROP TABLE IF EXISTS `sphinx`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sphinx` ( + `counter_id` tinyint(3) unsigned NOT NULL, + `max_id` int(10) unsigned NOT NULL, + PRIMARY KEY (`counter_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- -- Table structure for table `subscr_messages` -- @@ -472,10 +467,11 @@ DROP TABLE IF EXISTS `subscr_messages`; CREATE TABLE `subscr_messages` ( `message_id` int(10) unsigned NOT NULL, `suser_id` int(10) unsigned NOT NULL, - `last_read_rid` smallint(5) NOT NULL DEFAULT 0, + `last_read_rid` smallint(5) unsigned NOT NULL DEFAULT 0, UNIQUE KEY `message_id` (`message_id`,`suser_id`), - KEY `last_read_indx` (`suser_id`,`last_read_rid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; + KEY `last_read_indx` (`suser_id`,`last_read_rid`), + KEY `s_indx` (`suser_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -489,7 +485,7 @@ CREATE TABLE `subscr_tags` ( `tag_id` int(10) unsigned NOT NULL, `suser_id` int(10) unsigned NOT NULL, UNIQUE KEY `tag_id` (`tag_id`,`suser_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -505,7 +501,7 @@ CREATE TABLE `subscr_users` ( `ts` timestamp NOT NULL DEFAULT current_timestamp(), UNIQUE KEY `user_id` (`user_id`,`suser_id`), KEY `suser_id` (`suser_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -518,15 +514,16 @@ DROP TABLE IF EXISTS `tags`; 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 DEFAULT NULL, + `name` char(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `top` 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, PRIMARY KEY (`tag_id`), + UNIQUE KEY `tag_name` (`name`), KEY `synonym_id` (`synonym_id`), KEY `stat_msg_indx` (`name`,`stat_messages`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -538,7 +535,7 @@ DROP TABLE IF EXISTS `tags_ignore`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `tags_ignore` ( `tag_id` int(10) unsigned NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -551,7 +548,7 @@ DROP TABLE IF EXISTS `tags_synonyms`; CREATE TABLE `tags_synonyms` ( `name` char(64) NOT NULL, `changeto` char(64) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -564,9 +561,9 @@ DROP TABLE IF EXISTS `telegram`; CREATE TABLE `telegram` ( `user_id` int(10) unsigned DEFAULT NULL, `tg_id` bigint(20) NOT NULL, - `tg_name` char(64) COLLATE utf8mb4_unicode_ci NOT NULL, + `tg_name` char(64) NOT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp(), - `loginhash` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL + `loginhash` char(36) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -579,7 +576,7 @@ DROP TABLE IF EXISTS `top_ignore_messages`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `top_ignore_messages` ( `message_id` int(10) unsigned NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -592,7 +589,7 @@ DROP TABLE IF EXISTS `top_ignore_tags`; CREATE TABLE `top_ignore_tags` ( `tag_id` int(10) unsigned NOT NULL, PRIMARY KEY (`tag_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -605,7 +602,7 @@ DROP TABLE IF EXISTS `top_ignore_users`; CREATE TABLE `top_ignore_users` ( `user_id` int(10) unsigned NOT NULL, PRIMARY KEY (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -617,37 +614,30 @@ DROP TABLE IF EXISTS `twitter`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `twitter` ( `user_id` int(10) unsigned NOT NULL, - `access_token` char(64) NOT NULL, + `access_token` varchar(255) DEFAULT NULL, `access_token_secret` char(64) NOT NULL, `uname` char(64) NOT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp(), `crosspost` tinyint(1) unsigned NOT NULL DEFAULT 1, PRIMARY KEY (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `useroptions` +-- Table structure for table `user_services` -- -DROP TABLE IF EXISTS `useroptions`; +DROP TABLE IF EXISTS `user_services`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; -CREATE TABLE `useroptions` ( +CREATE TABLE `user_services` ( `user_id` int(10) unsigned NOT NULL, - `jnotify` tinyint(1) NOT NULL DEFAULT 1, - `subscr_active` tinyint(1) NOT NULL DEFAULT 1, - `off_ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `xmppxhtml` tinyint(1) NOT NULL DEFAULT 0, - `subscr_notify` tinyint(1) NOT NULL DEFAULT 1, - `recommendations` tinyint(1) NOT NULL DEFAULT 1, - `privacy_view` tinyint(1) NOT NULL DEFAULT 1, - `privacy_reply` tinyint(1) NOT NULL DEFAULT 1, - `privacy_pm` tinyint(1) NOT NULL DEFAULT 1, - `repliesview` tinyint(1) NOT NULL DEFAULT 0, - PRIMARY KEY (`user_id`), - KEY `recommendations` (`recommendations`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; + `regid` char(255) NOT NULL, + `ts` timestamp NOT NULL DEFAULT current_timestamp(), + `service_type` varchar(255) NOT NULL DEFAULT 'fcm', + UNIQUE KEY `regid` (`regid`), + KEY `user_id` (`user_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -663,14 +653,14 @@ CREATE TABLE `users` ( `passw` char(32) NOT NULL, `lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__', `banned` tinyint(3) unsigned NOT NULL DEFAULT 0, - `lastmessage` timestamp(6) NOT NULL DEFAULT current_timestamp(6), + `lastmessage` timestamp(6) NULL DEFAULT NULL, `lastpm` int(11) NOT NULL DEFAULT 0, `lastphoto` int(11) NOT NULL DEFAULT 0, `karma` smallint(6) NOT NULL DEFAULT 0, `last_seen` timestamp(6) NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `nick` (`nick`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -684,7 +674,7 @@ CREATE TABLE `users_subscr` ( `user_id` int(10) unsigned NOT NULL, `cnt` smallint(5) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -696,7 +686,7 @@ DROP TABLE IF EXISTS `usersinfo`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `usersinfo` ( `user_id` int(10) unsigned NOT NULL, - `jid` char(32) DEFAULT NULL, + `jid2` char(32) DEFAULT NULL, `fullname` char(32) DEFAULT NULL, `country` char(32) DEFAULT NULL, `url` char(64) DEFAULT NULL, @@ -704,7 +694,7 @@ CREATE TABLE `usersinfo` ( `bday` char(10) DEFAULT NULL, `descr` varchar(255) DEFAULT NULL, PRIMARY KEY (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -730,29 +720,13 @@ CREATE TABLE `vk` ( `user_id` int(10) unsigned DEFAULT NULL, `vk_id` bigint(20) unsigned DEFAULT NULL, `loginhash` char(36) DEFAULT NULL, - `access_token` char(128) DEFAULT NULL, + `access_token` varchar(255) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp(), `vk_name` char(64) DEFAULT NULL, `vk_link` char(64) NOT NULL, `crosspost` tinyint(3) unsigned NOT NULL DEFAULT 1, KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `winphone` --- - -DROP TABLE IF EXISTS `winphone`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `winphone` ( - `user_id` int(10) unsigned NOT NULL, - `url` char(255) NOT NULL, - `ts` timestamp NOT NULL DEFAULT current_timestamp(), - UNIQUE KEY `url` (`url`), - KEY `user_id` (`user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -766,7 +740,7 @@ CREATE TABLE `wl_users` ( `user_id` int(10) unsigned NOT NULL, `wl_user_id` int(10) unsigned NOT NULL, PRIMARY KEY (`user_id`,`wl_user_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -778,4 +752,4 @@ CREATE TABLE `wl_users` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2022-12-16 16:03:20 +-- Dump completed on 2023-01-27 17:47:26 diff --git a/src/main/resources/db/specific/sqlite/V1.22__schema.sql b/src/main/resources/schema-sqlite.sql index 072b2a1a..7ec33395 100644 --- a/src/main/resources/db/specific/sqlite/V1.22__schema.sql +++ b/src/main/resources/schema-sqlite.sql @@ -1,11 +1,10 @@ -CREATE TABLE android ( +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, + 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, @@ -14,14 +13,12 @@ CREATE TABLE auth ( 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, @@ -30,14 +27,12 @@ CREATE TABLE bl_users ( 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, @@ -49,7 +44,6 @@ CREATE TABLE facebook ( 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, @@ -58,7 +52,6 @@ CREATE TABLE favorites ( 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, @@ -66,7 +59,6 @@ CREATE TABLE followers ( PRIMARY KEY (user_id) FOREIGN KEY (user_id) REFERENCES users(id) ); - CREATE TABLE images ( mid bigint NOT NULL, rid bigint NOT NULL, @@ -77,15 +69,6 @@ CREATE TABLE images ( 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, @@ -94,20 +77,11 @@ CREATE TABLE jids ( 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, @@ -116,7 +90,6 @@ CREATE TABLE meon ( ico smallint, FOREIGN KEY (user_id) REFERENCES users(id) ); - CREATE TABLE messages ( message_id INTEGER PRIMARY KEY NOT NULL, user_id bigint NOT NULL, @@ -136,14 +109,12 @@ CREATE TABLE messages ( 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, @@ -152,13 +123,11 @@ CREATE TABLE messages_properties ( 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, @@ -166,7 +135,6 @@ CREATE TABLE messages_txt ( 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, @@ -177,13 +145,11 @@ CREATE TABLE places ( 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, @@ -192,18 +158,10 @@ CREATE TABLE pm ( 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, @@ -219,7 +177,6 @@ CREATE TABLE replies ( 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, @@ -227,14 +184,12 @@ CREATE TABLE subscr_messages ( 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, @@ -242,7 +197,6 @@ CREATE TABLE subscr_users ( 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, @@ -253,17 +207,14 @@ CREATE TABLE tags ( 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, @@ -272,22 +223,18 @@ CREATE TABLE telegram ( 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, @@ -297,22 +244,6 @@ CREATE TABLE twitter ( 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, @@ -326,13 +257,11 @@ CREATE TABLE users ( 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), @@ -344,11 +273,9 @@ CREATE TABLE usersinfo ( 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, @@ -360,14 +287,6 @@ CREATE TABLE vk ( 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, @@ -375,3 +294,18 @@ CREATE TABLE wl_users ( 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) +); diff --git a/src/test/resources/db/specific/mariadb/V1.999__data.sql b/src/test/resources/data-mysql.sql index a5db173a..a5db173a 100644 --- a/src/test/resources/db/specific/mariadb/V1.999__data.sql +++ b/src/test/resources/data-mysql.sql diff --git a/src/test/resources/db/specific/postgresql/V1.999__data.sql b/src/test/resources/data-postgresql.sql index 673b24d2..673b24d2 100644 --- a/src/test/resources/db/specific/postgresql/V1.999__data.sql +++ b/src/test/resources/data-postgresql.sql |