From 49bf2e2971d734ed8d1e5fdc6aab284a51fa5203 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Wed, 28 Mar 2018 14:16:08 +0300
Subject: h2 schema and test fixes

---
 juick-server-jdbc/src/main/resources/schema.sql | 109 ++++++++++++------------
 1 file changed, 55 insertions(+), 54 deletions(-)

(limited to 'juick-server-jdbc/src/main')

diff --git a/juick-server-jdbc/src/main/resources/schema.sql b/juick-server-jdbc/src/main/resources/schema.sql
index 65166da4..c4b1026e 100644
--- a/juick-server-jdbc/src/main/resources/schema.sql
+++ b/juick-server-jdbc/src/main/resources/schema.sql
@@ -1,33 +1,34 @@
 SET MODE MYSQL;
+SET DB_CLOSE_ON_EXIT TRUE;
 
-CREATE TABLE `android` (
+CREATE TABLE IF NOT EXISTS `android` (
   `user_id` int(10) unsigned NOT NULL,
   `regid` char(255) NOT NULL,
   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 );
-CREATE TABLE `auth` (
+CREATE TABLE IF NOT EXISTS `auth` (
   `user_id` int(10) unsigned NOT NULL,
   `protocol` enum('xmpp','email','sms') NOT NULL,
   `account` char(64) NOT NULL,
   `authcode` char(8) NOT NULL
 );
-CREATE TABLE `bl_tags` (
+CREATE TABLE IF NOT EXISTS `bl_tags` (
   `user_id` int(10) unsigned NOT NULL,
   `tag_id` int(10) unsigned NOT NULL,
 );
-CREATE TABLE `bl_users` (
+CREATE TABLE IF NOT EXISTS `bl_users` (
   `user_id` int(10) unsigned NOT NULL,
   `bl_user_id` int(10) unsigned NOT NULL,
   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`user_id`,`bl_user_id`)
 );
-CREATE TABLE `emails` (
+CREATE TABLE IF NOT EXISTS `emails` (
   `user_id` int(10) unsigned NOT NULL,
   `email` char(64) NOT NULL,
   `subscr_hour` tinyint(4) DEFAULT NULL,
   KEY `email` (`email`)
 );
-CREATE TABLE `facebook` (
+CREATE TABLE IF NOT EXISTS `facebook` (
   `user_id` int(10) unsigned DEFAULT NULL,
   `fb_id` bigint(20) unsigned NOT NULL,
   `loginhash` char(36) DEFAULT NULL,
@@ -38,19 +39,19 @@ CREATE TABLE `facebook` (
   `crosspost` tinyint(1) unsigned NOT NULL DEFAULT '1',
   KEY `user_id` (`user_id`)
 );
-CREATE TABLE `favorites` (
+CREATE TABLE IF NOT EXISTS `favorites` (
   `user_id` int(10) unsigned NOT NULL,
   `message_id` int(10) unsigned NOT NULL,
   `ts` datetime NOT NULL,
   UNIQUE KEY `user_id_2` (`user_id`,`message_id`),
   KEY `message_id` (`message_id`)
 );
-CREATE TABLE `friends_facebook` (
+CREATE TABLE IF NOT EXISTS `friends_facebook` (
   `user_id` int(10) unsigned NOT NULL,
   `friend_id` bigint(20) unsigned NOT NULL,
   UNIQUE KEY `user_id` (`user_id`,`friend_id`)
 );
-CREATE TABLE `images` (
+CREATE TABLE IF NOT EXISTS `images` (
   `mid` int(10) unsigned NOT NULL,
   `rid` int(10) unsigned NOT NULL,
   `thumb` int(10) unsigned NOT NULL,
@@ -60,7 +61,7 @@ CREATE TABLE `images` (
   `width` int(10) unsigned NOT NULL,
   PRIMARY KEY (`mid`,`rid`)
 );
-CREATE TABLE `ios` (
+CREATE TABLE IF NOT EXISTS `ios` (
   `user_id` int(10) unsigned NOT NULL,
   `token` char(64) NOT NULL,
   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
@@ -68,7 +69,7 @@ CREATE TABLE `ios` (
   KEY (`user_id`)
 );
 
-CREATE TABLE `jids` (
+CREATE TABLE IF NOT EXISTS `jids` (
   `user_id` int(10) unsigned DEFAULT NULL,
   `jid` char(64) NOT NULL,
   `active` tinyint(1) NOT NULL DEFAULT '1',
@@ -78,19 +79,19 @@ CREATE TABLE `jids` (
   KEY (`user_id`)
 );
 
-CREATE TABLE `logins` (
+CREATE TABLE IF NOT EXISTS `logins` (
   `user_id` int(10) unsigned NOT NULL,
   `hash` char(16) NOT NULL,
   UNIQUE KEY (`user_id`)
 );
 
-CREATE TABLE `mail` (
+CREATE TABLE IF NOT EXISTS `mail` (
   `user_id` int(10) unsigned NOT NULL,
   `hash` char(16) NOT NULL,
   PRIMARY KEY (`user_id`)
 );
 
-CREATE TABLE `meon` (
+CREATE TABLE IF NOT EXISTS `meon` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `user_id` int(10) unsigned NOT NULL,
   `link` char(255) NOT NULL,
@@ -99,23 +100,23 @@ CREATE TABLE `meon` (
   PRIMARY KEY (`id`)
 );
 
-CREATE TABLE `messages` (
+CREATE TABLE IF NOT EXISTS `messages` (
   `message_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `user_id` int(10) unsigned NOT NULL,
   `lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__',
-  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `ts` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `replies` smallint(5) unsigned NOT NULL DEFAULT '0',
   `maxreplyid` smallint(5) unsigned NOT NULL DEFAULT '0',
   `privacy` tinyint(4) NOT NULL DEFAULT '1',
   `readonly` tinyint(1) NOT NULL DEFAULT '0',
-  `attach` enum('jpg','mp4','png') DEFAULT NULL,
+  `attach` nchar(3) check (attach in ('jpg', 'mp4', 'png')),
   `place_id` int(10) unsigned DEFAULT NULL,
   `lat` decimal(10,7) DEFAULT NULL,
   `lon` decimal(10,7) DEFAULT NULL,
   `popular` tinyint(4) NOT NULL DEFAULT '0',
   `hidden` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `likes` smallint(6) NOT NULL DEFAULT '0',
-  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `updated` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`message_id`),
   KEY (`user_id`),
   KEY (`ts`),
@@ -126,13 +127,13 @@ CREATE TABLE `messages` (
   KEY (`updated`,`message_id`)
 );
 
-CREATE TABLE `messages_access` (
+CREATE TABLE IF NOT EXISTS `messages_access` (
   `message_id` int(10) unsigned NOT NULL,
   `user_id` int(10) unsigned NOT NULL,
   KEY (`message_id`)
 );
 
-CREATE TABLE `messages_tags` (
+CREATE TABLE IF NOT EXISTS `messages_tags` (
   `message_id` int(10) unsigned NOT NULL,
   `tag_id` int(10) unsigned NOT NULL,
   UNIQUE KEY `message_id_2` (`message_id`,`tag_id`),
@@ -140,7 +141,7 @@ CREATE TABLE `messages_tags` (
   KEY (`tag_id`)
 );
 
-CREATE TABLE `messages_txt` (
+CREATE TABLE IF NOT EXISTS `messages_txt` (
   `message_id` int(10) unsigned NOT NULL,
   `tags` varchar(255) DEFAULT NULL,
   `repliesby` varchar(96) DEFAULT NULL,
@@ -148,14 +149,14 @@ CREATE TABLE `messages_txt` (
   PRIMARY KEY (`message_id`)
 );
 
-CREATE TABLE `messages_votes` (
+CREATE TABLE IF NOT EXISTS `messages_votes` (
   `message_id` int(10) unsigned NOT NULL,
   `user_id` int(10) unsigned NOT NULL,
   `vote` tinyint(4) NOT NULL DEFAULT '1',
   UNIQUE KEY `message_id` (`message_id`,`user_id`)
 );
 
-CREATE TABLE `messenger` (
+CREATE TABLE IF NOT EXISTS `messenger` (
   `user_id` int(10) unsigned DEFAULT NULL,
   `sender_id` bigint(20) NOT NULL,
   `display_name` char(64) NOT NULL,
@@ -163,7 +164,7 @@ CREATE TABLE `messenger` (
   `loginhash` char(36) DEFAULT NULL
 );
 
-CREATE TABLE `places` (
+CREATE TABLE IF NOT EXISTS `places` (
   `place_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `lat` decimal(10,7) NOT NULL,
   `lon` decimal(10,7) NOT NULL,
@@ -175,26 +176,26 @@ CREATE TABLE `places` (
   PRIMARY KEY (`place_id`)
 );
 
-CREATE TABLE `places_tags` (
+CREATE TABLE IF NOT EXISTS `places_tags` (
   `place_id` int(10) unsigned NOT NULL,
   `tag_id` int(10) unsigned NOT NULL
 );
 
-CREATE TABLE `pm` (
+CREATE TABLE IF NOT EXISTS `pm` (
   `user_id` int(10) unsigned NOT NULL,
   `user_id_to` int(10) unsigned NOT NULL,
   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `txt` text NOT NULL
 );
 
-CREATE TABLE `pm_inroster` (
+CREATE TABLE IF NOT EXISTS `pm_inroster` (
   `user_id` int(10) unsigned NOT NULL,
   `jid` char(64) NOT NULL,
   UNIQUE KEY (`user_id`,`jid`),
   KEY (`user_id`)
 );
 
-CREATE TABLE `pm_streams` (
+CREATE TABLE IF NOT EXISTS `pm_streams` (
   `user_id` int(10) unsigned NOT NULL,
   `user_id_to` int(10) unsigned NOT NULL,
   `lastmessage` datetime NOT NULL,
@@ -203,19 +204,19 @@ CREATE TABLE `pm_streams` (
   UNIQUE KEY (`user_id`,`user_id_to`)
 );
 
-CREATE TABLE `presence` (
+CREATE TABLE IF NOT EXISTS `presence` (
   `user_id` int(10) unsigned NOT NULL,
   `jid` char(64) DEFAULT NULL,
   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   UNIQUE KEY (`jid`)
 );
 
-CREATE TABLE `replies` (
+CREATE TABLE IF NOT EXISTS `replies` (
   `message_id` int(10) unsigned NOT NULL,
   `reply_id` smallint(5) unsigned NOT NULL,
   `user_id` int(10) unsigned NOT NULL,
   `replyto` smallint(5) unsigned NOT NULL DEFAULT '0',
-  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `ts` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `attach` enum('jpg','mp4','png') DEFAULT NULL,
   `txt` mediumtext NOT NULL,
   KEY (`message_id`),
@@ -223,13 +224,13 @@ CREATE TABLE `replies` (
   KEY (`ts`)
 );
 
-CREATE TABLE `subscr_messages` (
+CREATE TABLE IF NOT EXISTS `subscr_messages` (
   `message_id` int(10) unsigned NOT NULL,
   `suser_id` int(10) unsigned NOT NULL,
   UNIQUE KEY (`message_id`,`suser_id`)
 );
 
-CREATE TABLE `subscr_tags` (
+CREATE TABLE IF NOT EXISTS `subscr_tags` (
   `tag_id` int(10) unsigned NOT NULL,
   `suser_id` int(10) unsigned NOT NULL,
   `jid` char(64) NOT NULL,
@@ -237,7 +238,7 @@ CREATE TABLE `subscr_tags` (
   UNIQUE KEY (`tag_id`,`suser_id`)
 );
 
-CREATE TABLE `subscr_users` (
+CREATE TABLE IF NOT EXISTS `subscr_users` (
   `user_id` int(10) unsigned NOT NULL,
   `suser_id` int(10) unsigned NOT NULL,
   `jid` char(64) DEFAULT NULL,
@@ -247,10 +248,10 @@ CREATE TABLE `subscr_users` (
   KEY (`suser_id`)
 );
 
-CREATE TABLE `tags` (
+CREATE TABLE IF NOT EXISTS `tags` (
   `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `synonym_id` int(10) unsigned DEFAULT NULL,
-  `name` char(70) DEFAULT NULL,
+  `name` varchar_ignorecase(70) 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',
@@ -259,16 +260,16 @@ CREATE TABLE `tags` (
   KEY (`synonym_id`)
 );
 
-CREATE TABLE `tags_ignore` (
+CREATE TABLE IF NOT EXISTS `tags_ignore` (
   `tag_id` int(10) unsigned NOT NULL
 );
 
-CREATE TABLE `tags_synonyms` (
+CREATE TABLE IF NOT EXISTS `tags_synonyms` (
   `name` char(64) NOT NULL,
   `changeto` char(64) NOT NULL
 );
 
-CREATE TABLE `telegram` (
+CREATE TABLE IF NOT EXISTS `telegram` (
   `user_id` int(10) unsigned DEFAULT NULL,
   `tg_id` bigint(20) NOT NULL,
   `tg_name` char(64) NOT NULL,
@@ -276,26 +277,26 @@ CREATE TABLE `telegram` (
   `loginhash` char(36) DEFAULT NULL
 );
 
-CREATE TABLE `telegram_chats` (
+CREATE TABLE IF NOT EXISTS `telegram_chats` (
   `chat_id` bigint(20) DEFAULT NULL,
   UNIQUE KEY `chat_id` (`chat_id`)
 );
 
-CREATE TABLE `top_ignore_messages` (
+CREATE TABLE IF NOT EXISTS `top_ignore_messages` (
   `message_id` int(10) unsigned NOT NULL
 );
 
-CREATE TABLE `top_ignore_tags` (
+CREATE TABLE IF NOT EXISTS `top_ignore_tags` (
   `tag_id` int(10) unsigned NOT NULL,
   PRIMARY KEY (`tag_id`)
 );
 
-CREATE TABLE `top_ignore_users` (
+CREATE TABLE IF NOT EXISTS `top_ignore_users` (
   `user_id` int(10) unsigned NOT NULL,
   PRIMARY KEY (`user_id`)
 );
 
-CREATE TABLE `twitter` (
+CREATE TABLE IF NOT EXISTS `twitter` (
   `user_id` int(10) unsigned NOT NULL,
   `access_token` char(64) NOT NULL,
   `access_token_secret` char(64) NOT NULL,
@@ -305,11 +306,11 @@ CREATE TABLE `twitter` (
   PRIMARY KEY (`user_id`)
 );
 
-CREATE TABLE `useroptions` (
+CREATE TABLE IF NOT EXISTS `useroptions` (
   `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',
+  `off_ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `xmppxhtml` tinyint(1) NOT NULL DEFAULT '0',
   `subscr_notify` tinyint(1) NOT NULL DEFAULT '1',
   `recommendations` tinyint(1) NOT NULL DEFAULT '1',
@@ -321,7 +322,7 @@ CREATE TABLE `useroptions` (
   KEY `recommendations` (`recommendations`)
 );
 
-CREATE TABLE `users` (
+CREATE TABLE IF NOT EXISTS `users` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `nick` char(64) NOT NULL,
   `passw` char(32) NOT NULL,
@@ -335,19 +336,19 @@ CREATE TABLE `users` (
   UNIQUE KEY `nick` (`nick`)
 );
 
-CREATE TABLE `users_refs` (
+CREATE TABLE IF NOT EXISTS `users_refs` (
   `user_id` int(10) unsigned NOT NULL,
   `ref` int(10) unsigned NOT NULL,
   KEY `ref` (`ref`)
 );
 
-CREATE TABLE `users_subscr` (
+CREATE TABLE IF NOT EXISTS `users_subscr` (
   `user_id` int(10) unsigned NOT NULL,
   `cnt` smallint(5) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`user_id`)
 );
 
-CREATE TABLE `usersinfo` (
+CREATE TABLE IF NOT EXISTS `usersinfo` (
   `user_id` int(10) unsigned NOT NULL,
   `jid` char(32) DEFAULT NULL,
   `fullname` char(32) DEFAULT NULL,
@@ -359,11 +360,11 @@ CREATE TABLE `usersinfo` (
   PRIMARY KEY (`user_id`)
 );
 
-CREATE TABLE `version` (
+CREATE TABLE IF NOT EXISTS `version` (
   `version` bigint(20) NOT NULL
 );
 
-CREATE TABLE `vk` (
+CREATE TABLE IF NOT EXISTS `vk` (
   `user_id` int(10) unsigned DEFAULT NULL,
   `vk_id` bigint(20) NOT NULL,
   `loginhash` char(36) DEFAULT NULL,
@@ -375,7 +376,7 @@ CREATE TABLE `vk` (
   KEY (`user_id`)
 );
 
-CREATE TABLE `winphone` (
+CREATE TABLE IF NOT EXISTS `winphone` (
   `user_id` int(10) unsigned NOT NULL,
   `url` char(255) NOT NULL,
   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
@@ -383,7 +384,7 @@ CREATE TABLE `winphone` (
   KEY (`user_id`)
 );
 
-CREATE TABLE `wl_users` (
+CREATE TABLE IF NOT EXISTS `wl_users` (
   `user_id` int(10) unsigned NOT NULL,
   `wl_user_id` int(10) unsigned NOT NULL,
   PRIMARY KEY (`user_id`,`wl_user_id`)
-- 
cgit v1.2.3