diff options
author | 2018-03-28 14:16:08 +0300 | |
---|---|---|
committer | 2018-03-28 15:25:02 +0300 | |
commit | 49bf2e2971d734ed8d1e5fdc6aab284a51fa5203 (patch) | |
tree | 0fcd8b55f2c29a82d0aff7be90e6c6caddf0e278 /juick-server-jdbc/src/main/resources/schema.sql | |
parent | 8fa74e5555805acc8ddaa71b362cd684ef1f76c1 (diff) |
h2 schema and test fixes
Diffstat (limited to 'juick-server-jdbc/src/main/resources/schema.sql')
-rw-r--r-- | juick-server-jdbc/src/main/resources/schema.sql | 109 |
1 files changed, 55 insertions, 54 deletions
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`) |