diff options
Diffstat (limited to 'juick-server/src/main/resources/schema.sql')
| -rw-r--r-- | juick-server/src/main/resources/schema.sql | 379 |
1 files changed, 379 insertions, 0 deletions
diff --git a/juick-server/src/main/resources/schema.sql b/juick-server/src/main/resources/schema.sql new file mode 100644 index 00000000..296fd486 --- /dev/null +++ b/juick-server/src/main/resources/schema.sql @@ -0,0 +1,379 @@ +SET DB_CLOSE_ON_EXIT TRUE; + +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 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 IF NOT EXISTS `bl_tags` ( + `user_id` int(10) unsigned NOT NULL, + `tag_id` int(10) unsigned NOT NULL, +); +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 IF NOT EXISTS `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(255) NOT NULL, + `crosspost` tinyint(1) unsigned NOT NULL DEFAULT '1' +); + +CREATE TABLE IF NOT EXISTS `reactions` ( + `like_id` int(10) unsigned NOT NULL, + `description` varchar (100) NOT NULL +); +CREATE TABLE IF NOT EXISTS `favorites` ( + `user_id` int(10) unsigned NOT NULL, + `message_id` int(10) unsigned NOT NULL, + `ts` datetime NOT NULL, + `like_id` int(10), + foreign key (like_id) references reactions(like_id) +); + + + +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 IF NOT EXISTS `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 IF NOT EXISTS `mail` ( + `user_id` int(10) unsigned NOT NULL, + `hash` char(16) NOT NULL, + PRIMARY KEY (`user_id`) +); + +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, + `name` char(32) NOT NULL, + `ico` smallint(5) unsigned DEFAULT NULL, + PRIMARY KEY (`id`) +); + + + +CREATE TABLE IF NOT EXISTS `messages_access` ( + `message_id` int(10) unsigned NOT NULL, + `user_id` int(10) unsigned NOT NULL +); + +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`) +); + +CREATE TABLE IF NOT EXISTS `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 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 IF NOT EXISTS `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 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, + `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 IF NOT EXISTS `places_tags` ( + `place_id` int(10) unsigned NOT NULL, + `tag_id` int(10) unsigned NOT NULL +); + +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 IF NOT EXISTS `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 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 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(9) NOT NULL DEFAULT CURRENT_TIMESTAMP, + `attach` nchar(3) check (attach in ('jpg', 'mp4', 'png')), + `txt` mediumtext NOT NULL +); + +CREATE TABLE IF NOT EXISTS `subscr_messages` ( + `message_id` int(10) unsigned NOT NULL, + `suser_id` int(10) unsigned NOT NULL, + `last_read_rid` smallint(5) unsigned NOT NULL DEFAULT '0', + UNIQUE KEY (`message_id`,`suser_id`) +); + +CREATE TABLE IF NOT EXISTS `subscr_tags` ( + `tag_id` int(10) unsigned NOT NULL, + `suser_id` int(10) unsigned NOT NULL, + UNIQUE KEY (`tag_id`,`suser_id`) +); + +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, + `active` bit(1) NOT NULL DEFAULT TRUE, + `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE KEY (`user_id`,`suser_id`) +); + +CREATE TABLE IF NOT EXISTS `tags` ( + `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `synonym_id` int(10) unsigned 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', + `stat_users` smallint(5) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`tag_id`) +); + +CREATE TABLE IF NOT EXISTS `tags_ignore` ( + `tag_id` int(10) unsigned NOT NULL +); + +CREATE TABLE IF NOT EXISTS `tags_synonyms` ( + `name` char(64) NOT NULL, + `changeto` char(64) NOT NULL +); + +CREATE TABLE IF NOT EXISTS `telegram` ( + `user_id` int(10) unsigned DEFAULT NULL, + `tg_id` bigint(20) NOT NULL, + `tg_name` char(64) DEFAULT NULL, + `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `loginhash` char(36) DEFAULT NULL +); + +CREATE TABLE IF NOT EXISTS `telegram_chats` ( + `chat_id` bigint(20) DEFAULT NULL, + UNIQUE KEY `chat_id` (`chat_id`) +); + +CREATE TABLE IF NOT EXISTS `top_ignore_messages` ( + `message_id` int(10) unsigned NOT NULL +); + +CREATE TABLE IF NOT EXISTS `top_ignore_tags` ( + `tag_id` int(10) unsigned NOT NULL, + PRIMARY KEY (`tag_id`) +); + +CREATE TABLE IF NOT EXISTS `top_ignore_users` ( + `user_id` int(10) unsigned NOT NULL, + PRIMARY KEY (`user_id`) +); + +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, + `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 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 CURRENT_TIMESTAMP, + `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`) +); + +CREATE TABLE IF NOT EXISTS `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` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP, + `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 IF NOT EXISTS `users_refs` ( + `user_id` int(10) unsigned NOT NULL, + `ref` int(10) unsigned NOT NULL +); + +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 IF NOT EXISTS `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 IF NOT EXISTS `emails` ( + `user_id` int(10) unsigned NOT NULL, + `email` char(64) NOT NULL PRIMARY KEY, + `subscr_hour` tinyint(4) DEFAULT NULL, + foreign key (user_id) references users(id) +); +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, + UNIQUE KEY `token` (`token`), + foreign key (user_id) references users(id) +); + +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', + `loginhash` char(36) DEFAULT NULL, + `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE KEY `jid` (`jid`), + foreign key (user_id) references users(id) +); + +CREATE TABLE IF NOT EXISTS `logins` ( + `user_id` int(10) unsigned NOT NULL, + `hash` char(16) NOT NULL, + UNIQUE KEY (`user_id`) +); +CREATE TABLE IF NOT EXISTS `messages` ( + `message_id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, + `user_id` int(10) unsigned NOT NULL, + `lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__', + `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` 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(9) NOT NULL DEFAULT CURRENT_TIMESTAMP, + FOREIGN KEY (`user_id`) references users(id) +); +CREATE TABLE IF NOT EXISTS `pm_inroster` ( + `user_id` int(10) unsigned NOT NULL, + `jid` char(64) NOT NULL, + UNIQUE KEY (`user_id`,`jid`), + FOREIGN KEY (`user_id`) references users(id) +); + +CREATE TABLE IF NOT EXISTS `version` ( + `version` bigint(20) NOT NULL +); + +CREATE TABLE IF NOT EXISTS `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, + FOREIGN KEY (`user_id`) references users(id) +); + +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, + UNIQUE KEY (`url`), + FOREIGN KEY (`user_id`) references users(id) +); + +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`) +);
\ No newline at end of file |