path: root/src/main/resources/schema.sql

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 NULL,
  `loginhash` char(36) DEFAULT NULL,
  `access_token` char(255) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fb_name` char(64) NULL,
  `fb_link` char(255) 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 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 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`)
);

CREATE CACHED TABLE PUBLIC."flyway_schema_history" (
    "installed_rank" INT NOT NULL,
    "version" VARCHAR(50),
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INT,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TIMESTAMP DEFAULT CURRENT_TIMESTAMP() NOT NULL,
    "execution_time" INT NOT NULL,
    "success" BOOLEAN NOT NULL
);
ALTER TABLE PUBLIC."flyway_schema_history" ADD CONSTRAINT PUBLIC."flyway_schema_history_pk" PRIMARY KEY("installed_rank");
-- 1 +/- SELECT COUNT(*) FROM PUBLIC."flyway_schema_history";
INSERT INTO PUBLIC."flyway_schema_history"("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "installed_on", "execution_time", "success") VALUES
(1, '1', '<< Flyway Baseline >>', 'BASELINE', '<< Flyway Baseline >>', NULL, 'SA', TIMESTAMP '2018-08-14 13:05:13.724', 0, TRUE);