diff options
Diffstat (limited to 'juick-server-jdbc/src/main/resources/schema.sql')
| -rw-r--r-- | juick-server-jdbc/src/main/resources/schema.sql | 151 |
1 files changed, 66 insertions, 85 deletions
diff --git a/juick-server-jdbc/src/main/resources/schema.sql b/juick-server-jdbc/src/main/resources/schema.sql index 13fe1413..296fd486 100644 --- a/juick-server-jdbc/src/main/resources/schema.sql +++ b/juick-server-jdbc/src/main/resources/schema.sql @@ -1,4 +1,3 @@ -SET MODE MYSQL; SET DB_CLOSE_ON_EXIT TRUE; CREATE TABLE IF NOT EXISTS `android` ( @@ -22,12 +21,6 @@ CREATE TABLE IF NOT EXISTS `bl_users` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`,`bl_user_id`) ); -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 IF NOT EXISTS `facebook` ( `user_id` int(10) unsigned DEFAULT NULL, `fb_id` bigint(20) unsigned NOT NULL, @@ -36,8 +29,7 @@ CREATE TABLE IF NOT EXISTS `facebook` ( `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', - KEY `user_id` (`user_id`) + `crosspost` tinyint(1) unsigned NOT NULL DEFAULT '1' ); CREATE TABLE IF NOT EXISTS `reactions` ( @@ -69,29 +61,6 @@ CREATE TABLE IF NOT EXISTS `images` ( `width` int(10) unsigned NOT NULL, PRIMARY KEY (`mid`,`rid`) ); -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`), - KEY (`user_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`), - KEY (`user_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 `mail` ( `user_id` int(10) unsigned NOT NULL, @@ -108,45 +77,17 @@ CREATE TABLE IF NOT EXISTS `meon` ( PRIMARY KEY (`id`) ); -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(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, - PRIMARY KEY (`message_id`), - KEY (`user_id`), - KEY (`ts`), - KEY (`attach`), - KEY (`place_id`), - KEY (`popular`), - KEY (`hidden`), - KEY (`updated`,`message_id`) -); + CREATE TABLE IF NOT EXISTS `messages_access` ( `message_id` int(10) unsigned NOT NULL, - `user_id` int(10) unsigned NOT NULL, - KEY (`message_id`) + `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`), - KEY (`message_id`), - KEY (`tag_id`) + UNIQUE KEY `message_id_2` (`message_id`,`tag_id`) ); CREATE TABLE IF NOT EXISTS `messages_txt` ( @@ -196,13 +137,6 @@ CREATE TABLE IF NOT EXISTS `pm` ( `txt` text NOT NULL ); -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 IF NOT EXISTS `pm_streams` ( `user_id` int(10) unsigned NOT NULL, `user_id_to` int(10) unsigned NOT NULL, @@ -226,10 +160,7 @@ CREATE TABLE IF NOT EXISTS `replies` ( `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, - KEY (`message_id`), - KEY (`user_id`), - KEY (`ts`) + `txt` mediumtext NOT NULL ); CREATE TABLE IF NOT EXISTS `subscr_messages` ( @@ -251,8 +182,7 @@ CREATE TABLE IF NOT EXISTS `subscr_users` ( `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`) + UNIQUE KEY (`user_id`,`suser_id`) ); CREATE TABLE IF NOT EXISTS `tags` ( @@ -263,8 +193,7 @@ CREATE TABLE IF NOT EXISTS `tags` ( `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`) + PRIMARY KEY (`tag_id`) ); CREATE TABLE IF NOT EXISTS `tags_ignore` ( @@ -279,7 +208,7 @@ CREATE TABLE IF NOT EXISTS `tags_synonyms` ( 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, + `tg_name` char(64) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `loginhash` char(36) DEFAULT NULL ); @@ -325,8 +254,7 @@ CREATE TABLE IF NOT EXISTS `useroptions` ( `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`) + PRIMARY KEY (`user_id`) ); CREATE TABLE IF NOT EXISTS `users` ( @@ -345,8 +273,7 @@ CREATE TABLE IF NOT EXISTS `users` ( CREATE TABLE IF NOT EXISTS `users_refs` ( `user_id` int(10) unsigned NOT NULL, - `ref` int(10) unsigned NOT NULL, - KEY `ref` (`ref`) + `ref` int(10) unsigned NOT NULL ); CREATE TABLE IF NOT EXISTS `users_subscr` ( @@ -366,6 +293,60 @@ CREATE TABLE IF NOT EXISTS `usersinfo` ( `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 @@ -380,7 +361,7 @@ CREATE TABLE IF NOT EXISTS `vk` ( `vk_name` char(64) NOT NULL, `vk_link` char(64) NOT NULL, `crosspost` bit(1) unsigned NOT NULL DEFAULT FALSE, - KEY (`user_id`) + FOREIGN KEY (`user_id`) references users(id) ); CREATE TABLE IF NOT EXISTS `winphone` ( @@ -388,7 +369,7 @@ CREATE TABLE IF NOT EXISTS `winphone` ( `url` char(255) NOT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY (`url`), - KEY (`user_id`) + FOREIGN KEY (`user_id`) references users(id) ); CREATE TABLE IF NOT EXISTS `wl_users` ( |