server: autoconfigured schema for h2

1 files changed, 390 insertions, 0 deletions

diff --git a/juick-server-jdbc/src/main/resources/schema.sql b/juick-server-jdbc/src/main/resources/schema.sql
new file mode 100644
index 00000000..65166da4
--- /dev/null
+++ b/juick-server-jdbc/src/main/resources/schema.sql
@@ -0,0 +1,390 @@
+SET MODE MYSQL;
+
+CREATE TABLE `android` (
+  `user_id` int(10) unsigned NOT NULL,
+  `regid` char(255) NOT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+);
+CREATE TABLE `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` (
+  `user_id` int(10) unsigned NOT NULL,
+  `tag_id` int(10) unsigned NOT NULL,
+);
+CREATE TABLE `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` (
+  `user_id` int(10) unsigned NOT NULL,
+  `email` char(64) NOT NULL,
+  `subscr_hour` tinyint(4) DEFAULT NULL,
+  KEY `email` (`email`)
+);
+CREATE TABLE `facebook` (
+  `user_id` int(10) unsigned DEFAULT NULL,
+  `fb_id` bigint(20) unsigned NOT NULL,
+  `loginhash` char(36) DEFAULT NULL,
+  `access_token` char(255) DEFAULT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `fb_name` char(64) NOT NULL,
+  `fb_link` char(64) NOT NULL,
+  `crosspost` tinyint(1) unsigned NOT NULL DEFAULT '1',
+  KEY `user_id` (`user_id`)
+);
+CREATE TABLE `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` (
+  `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` (
+  `mid` int(10) unsigned NOT NULL,
+  `rid` int(10) unsigned NOT NULL,
+  `thumb` int(10) unsigned NOT NULL,
+  `small` int(10) unsigned NOT NULL,
+  `medium` int(10) unsigned NOT NULL,
+  `height` int(10) unsigned NOT NULL,
+  `width` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`mid`,`rid`)
+);
+CREATE TABLE `ios` (
+  `user_id` int(10) unsigned NOT NULL,
+  `token` char(64) NOT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  UNIQUE KEY `token` (`token`),
+  KEY (`user_id`)
+);
+
+CREATE TABLE `jids` (
+  `user_id` int(10) unsigned DEFAULT NULL,
+  `jid` char(64) NOT NULL,
+  `active` tinyint(1) NOT NULL DEFAULT '1',
+  `loginhash` char(36) DEFAULT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  UNIQUE KEY `jid` (`jid`),
+  KEY (`user_id`)
+);
+
+CREATE TABLE `logins` (
+  `user_id` int(10) unsigned NOT NULL,
+  `hash` char(16) NOT NULL,
+  UNIQUE KEY (`user_id`)
+);
+
+CREATE TABLE `mail` (
+  `user_id` int(10) unsigned NOT NULL,
+  `hash` char(16) NOT NULL,
+  PRIMARY KEY (`user_id`)
+);
+
+CREATE TABLE `meon` (
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `user_id` int(10) unsigned NOT NULL,
+  `link` char(255) NOT NULL,
+  `name` char(32) NOT NULL,
+  `ico` smallint(5) unsigned DEFAULT NULL,
+  PRIMARY KEY (`id`)
+);
+
+CREATE TABLE `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,
+  `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,
+  `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,
+  PRIMARY KEY (`message_id`),
+  KEY (`user_id`),
+  KEY (`ts`),
+  KEY (`attach`),
+  KEY (`place_id`),
+  KEY (`popular`),
+  KEY (`hidden`),
+  KEY (`updated`,`message_id`)
+);
+
+CREATE TABLE `messages_access` (
+  `message_id` int(10) unsigned NOT NULL,
+  `user_id` int(10) unsigned NOT NULL,
+  KEY (`message_id`)
+);
+
+CREATE TABLE `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`),
+  KEY (`message_id`),
+  KEY (`tag_id`)
+);
+
+CREATE TABLE `messages_txt` (
+  `message_id` int(10) unsigned NOT NULL,
+  `tags` varchar(255) DEFAULT NULL,
+  `repliesby` varchar(96) DEFAULT NULL,
+  `txt` mediumtext NOT NULL,
+  PRIMARY KEY (`message_id`)
+);
+
+CREATE TABLE `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` (
+  `user_id` int(10) unsigned DEFAULT NULL,
+  `sender_id` bigint(20) NOT NULL,
+  `display_name` char(64) NOT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `loginhash` char(36) DEFAULT NULL
+);
+
+CREATE TABLE `places` (
+  `place_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `lat` decimal(10,7) NOT NULL,
+  `lon` decimal(10,7) NOT NULL,
+  `name` char(64) NOT NULL,
+  `descr` char(255) DEFAULT NULL,
+  `url` char(128) DEFAULT NULL,
+  `user_id` int(10) unsigned NOT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (`place_id`)
+);
+
+CREATE TABLE `places_tags` (
+  `place_id` int(10) unsigned NOT NULL,
+  `tag_id` int(10) unsigned NOT NULL
+);
+
+CREATE TABLE `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` (
+  `user_id` int(10) unsigned NOT NULL,
+  `jid` char(64) NOT NULL,
+  UNIQUE KEY (`user_id`,`jid`),
+  KEY (`user_id`)
+);
+
+CREATE TABLE `pm_streams` (
+  `user_id` int(10) unsigned NOT NULL,
+  `user_id_to` int(10) unsigned NOT NULL,
+  `lastmessage` datetime NOT NULL,
+  `lastview` datetime DEFAULT NULL,
+  `unread` smallint(5) unsigned NOT NULL DEFAULT '0',
+  UNIQUE KEY (`user_id`,`user_id_to`)
+);
+
+CREATE TABLE `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` (
+  `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,
+  `attach` enum('jpg','mp4','png') DEFAULT NULL,
+  `txt` mediumtext NOT NULL,
+  KEY (`message_id`),
+  KEY (`user_id`),
+  KEY (`ts`)
+);
+
+CREATE TABLE `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` (
+  `tag_id` int(10) unsigned NOT NULL,
+  `suser_id` int(10) unsigned NOT NULL,
+  `jid` char(64) NOT NULL,
+  `active` bit(1) NOT NULL DEFAULT TRUE,
+  UNIQUE KEY (`tag_id`,`suser_id`)
+);
+
+CREATE TABLE `subscr_users` (
+  `user_id` int(10) unsigned NOT NULL,
+  `suser_id` int(10) unsigned NOT NULL,
+  `jid` char(64) DEFAULT NULL,
+  `active` bit(1) NOT NULL DEFAULT TRUE,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  UNIQUE KEY (`user_id`,`suser_id`),
+  KEY (`suser_id`)
+);
+
+CREATE TABLE `tags` (
+  `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `synonym_id` int(10) unsigned DEFAULT NULL,
+  `name` char(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',
+  `stat_users` smallint(5) unsigned NOT NULL DEFAULT '0',
+  PRIMARY KEY (`tag_id`),
+  KEY (`synonym_id`)
+);
+
+CREATE TABLE `tags_ignore` (
+  `tag_id` int(10) unsigned NOT NULL
+);
+
+CREATE TABLE `tags_synonyms` (
+  `name` char(64) NOT NULL,
+  `changeto` char(64) NOT NULL
+);
+
+CREATE TABLE `telegram` (
+  `user_id` int(10) unsigned DEFAULT NULL,
+  `tg_id` bigint(20) NOT NULL,
+  `tg_name` char(64) NOT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `loginhash` char(36) DEFAULT NULL
+);
+
+CREATE TABLE `telegram_chats` (
+  `chat_id` bigint(20) DEFAULT NULL,
+  UNIQUE KEY `chat_id` (`chat_id`)
+);
+
+CREATE TABLE `top_ignore_messages` (
+  `message_id` int(10) unsigned NOT NULL
+);
+
+CREATE TABLE `top_ignore_tags` (
+  `tag_id` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`tag_id`)
+);
+
+CREATE TABLE `top_ignore_users` (
+  `user_id` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`user_id`)
+);
+
+CREATE TABLE `twitter` (
+  `user_id` int(10) unsigned NOT NULL,
+  `access_token` char(64) NOT 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`)
+);
+
+CREATE TABLE `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',
+  `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`)
+);
+
+CREATE TABLE `users` (
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `nick` char(64) NOT NULL,
+  `passw` char(32) NOT NULL,
+  `lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__',
+  `banned` tinyint(3) unsigned NOT NULL DEFAULT '0',
+  `lastmessage` int(11) NOT NULL DEFAULT '0',
+  `lastpm` int(11) NOT NULL DEFAULT '0',
+  `lastphoto` int(11) NOT NULL DEFAULT '0',
+  `karma` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `nick` (`nick`)
+);
+
+CREATE TABLE `users_refs` (
+  `user_id` int(10) unsigned NOT NULL,
+  `ref` int(10) unsigned NOT NULL,
+  KEY `ref` (`ref`)
+);
+
+CREATE TABLE `users_subscr` (
+  `user_id` int(10) unsigned NOT NULL,
+  `cnt` smallint(5) unsigned NOT NULL DEFAULT '0',
+  PRIMARY KEY (`user_id`)
+);
+
+CREATE TABLE `usersinfo` (
+  `user_id` int(10) unsigned NOT NULL,
+  `jid` char(32) DEFAULT NULL,
+  `fullname` char(32) DEFAULT NULL,
+  `country` char(32) DEFAULT NULL,
+  `url` char(64) DEFAULT NULL,
+  `gender` char(32) DEFAULT NULL,
+  `bday` char(10) DEFAULT NULL,
+  `descr` varchar(255) DEFAULT NULL,
+  PRIMARY KEY (`user_id`)
+);
+
+CREATE TABLE `version` (
+  `version` bigint(20) NOT NULL
+);
+
+CREATE TABLE `vk` (
+  `user_id` int(10) unsigned DEFAULT NULL,
+  `vk_id` bigint(20) NOT NULL,
+  `loginhash` char(36) DEFAULT NULL,
+  `access_token` char(128) NOT NULL,
+  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `vk_name` char(64) NOT NULL,
+  `vk_link` char(64) NOT NULL,
+  `crosspost` bit(1) unsigned NOT NULL DEFAULT FALSE,
+  KEY (`user_id`)
+);
+
+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`),
+  KEY (`user_id`)
+);
+
+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`)
+);
\ No newline at end of file