-- Adminer 4.0.2 MySQL dump
SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = '+04:00';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DELIMITER ;;
DROP PROCEDURE IF EXISTS `refresh_gallery_album_meta`;;
CREATE PROCEDURE `refresh_gallery_album_meta`(IN `albumId` int unsigned)
UPDATE `v_gallery_albums` SET `total_photos` = (SELECT COUNT(*) FROM `v_gallery_photos` WHERE `album_id` = `albumId`), `updated_at` = (SELECT IFNULL(MAX(uploaded_at), "0000-00-00 00:00:00") FROM `v_gallery_photos` WHERE `album_id` = `albumId`) WHERE `id` = `albumId`;;
DELIMITER ;
DROP TABLE IF EXISTS `v_auth_assignments`;
CREATE TABLE `v_auth_assignments` (
`itemname` varchar(255) NOT NULL,
`userid` int(10) unsigned NOT NULL,
`bizrule` text,
`data` text,
PRIMARY KEY (`itemname`,`userid`),
KEY `userid` (`userid`),
CONSTRAINT `assignment_user` FOREIGN KEY (`userid`) REFERENCES `v_users` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_auth_assignments_ibfk_3` FOREIGN KEY (`itemname`) REFERENCES `v_auth_items` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_auth_items`;
CREATE TABLE `v_auth_items` (
`name` varchar(255) NOT NULL,
`type` enum('0','1','2') NOT NULL,
`description` text,
`bizrule` text,
`data` text,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `v_auth_items` (`name`, `type`, `description`, `bizrule`, `data`) VALUES
('administrator', '2', NULL, NULL, 'a:1:{s:5:\"color\";s:9:\"important\";}'),
('author', '2', NULL, 'return !Yii::app()->user->isGuest && $params[\"author\"] == Yii::app()->user->record->id;', 'N;'),
('forums-moderator', '2', NULL, NULL, 'a:1:{s:5:\"color\";s:9:\"important\";}'),
('forums.forum.createForum', '0', NULL, NULL, 'N;'),
('forums.forum.deleteForum', '0', NULL, NULL, 'N;'),
('forums.forum.orderForum', '0', NULL, NULL, 'N;'),
('forums.forum.updateForum', '0', NULL, NULL, 'N;'),
('forums.post.createPost', '0', NULL, NULL, 'N;'),
('forums.post.deletePost', '0', NULL, NULL, 'N;'),
('forums.post.seeHistory', '0', NULL, NULL, 'N;'),
('forums.post.updatePost', '0', NULL, NULL, 'N;'),
('forums.section.createSection', '0', NULL, NULL, 'N;'),
('forums.section.deleteSection', '0', NULL, NULL, 'N;'),
('forums.section.updateSection', '0', NULL, NULL, 'N;'),
('forums.topic.createTopic', '0', NULL, NULL, 'N;'),
('forums.topic.deleteTopic', '0', NULL, NULL, 'N;'),
('forums.topic.updateTopic', '0', NULL, NULL, 'N;'),
('gallery-moderator', '2', NULL, NULL, 'a:1:{s:5:\"color\";s:9:\"important\";}'),
('gallery.photo.deletePhoto', '0', NULL, NULL, 'N;'),
('gallery.photo.updatePhoto', '0', NULL, NULL, 'N;'),
('groups-moderator', '2', NULL, NULL, 'a:1:{s:5:\"color\";s:9:\"important\";}'),
('groups.group.deleteGroup', '0', NULL, NULL, 'N;'),
('groups.group.updateGroup', '0', NULL, NULL, 'N;'),
('groups.post.createPost', '0', NULL, NULL, 'N;'),
('groups.post.deletePost', '0', NULL, NULL, 'N;'),
('groups.post.updatePost', '0', NULL, NULL, 'N;'),
('groups.topic.deleteTopic', '0', NULL, NULL, 'N;'),
('groups.topic.updateTopic', '0', NULL, NULL, 'N;'),
('major-moderator', '2', NULL, NULL, 'a:1:{s:5:\"color\";s:9:\"important\";}'),
('publics-moderator', '2', NULL, NULL, 'a:1:{s:5:\"color\";s:9:\"important\";}'),
('publics.post.createPost', '0', NULL, NULL, 'N;'),
('publics.post.deletePost', '0', NULL, NULL, 'N;'),
('publics.post.updatePost', '0', NULL, NULL, 'N;'),
('publics.public.deletePublic', '0', NULL, NULL, 'N;'),
('publics.public.updatePublic', '0', NULL, NULL, 'N;'),
('user', '2', NULL, 'return !Yii::app()->user->isGuest;', 'N;');
DROP TABLE IF EXISTS `v_auth_items_relationships`;
CREATE TABLE `v_auth_items_relationships` (
`parent` varchar(255) NOT NULL,
`child` varchar(255) NOT NULL,
PRIMARY KEY (`parent`,`child`),
KEY `child` (`child`),
CONSTRAINT `relationship_child` FOREIGN KEY (`child`) REFERENCES `v_auth_items` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `relationship_parent` FOREIGN KEY (`parent`) REFERENCES `v_auth_items` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `v_auth_items_relationships` (`parent`, `child`) VALUES
('major-moderator', 'forums-moderator'),
('forums-moderator', 'forums.forum.createForum'),
('forums-moderator', 'forums.forum.deleteForum'),
('forums-moderator', 'forums.forum.orderForum'),
('forums-moderator', 'forums.forum.updateForum'),
('user', 'forums.post.createPost'),
('forums-moderator', 'forums.post.deletePost'),
('forums-moderator', 'forums.post.seeHistory'),
('forums-moderator', 'forums.post.updatePost'),
('forums-moderator', 'forums.section.createSection'),
('forums-moderator', 'forums.section.deleteSection'),
('forums-moderator', 'forums.section.updateSection'),
('user', 'forums.topic.createTopic'),
('forums-moderator', 'forums.topic.deleteTopic'),
('forums-moderator', 'forums.topic.updateTopic'),
('major-moderator', 'gallery-moderator'),
('gallery-moderator', 'gallery.photo.deletePhoto'),
('gallery-moderator', 'gallery.photo.updatePhoto'),
('major-moderator', 'groups-moderator'),
('groups-moderator', 'groups.group.deleteGroup'),
('groups-moderator', 'groups.group.updateGroup'),
('groups-moderator', 'groups.post.createPost'),
('groups-moderator', 'groups.post.deletePost'),
('groups-moderator', 'groups.post.updatePost'),
('groups-moderator', 'groups.topic.deleteTopic'),
('groups-moderator', 'groups.topic.updateTopic'),
('administrator', 'major-moderator'),
('major-moderator', 'publics-moderator'),
('publics-moderator', 'publics.post.createPost'),
('publics-moderator', 'publics.post.deletePost'),
('publics-moderator', 'publics.post.updatePost'),
('publics-moderator', 'publics.public.deletePublic'),
('publics-moderator', 'publics.public.updatePublic');
DROP TABLE IF EXISTS `v_forums`;
CREATE TABLE `v_forums` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`order` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_forum_sections`;
CREATE TABLE `v_forum_sections` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`forum_id` int(10) unsigned NOT NULL,
`total_topics` smallint(5) unsigned NOT NULL COMMENT 'fills automatically by triggers',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'fills automatically by triggers',
PRIMARY KEY (`id`),
KEY `forum_id` (`forum_id`),
KEY `total_post` (`total_topics`),
CONSTRAINT `v_forum_sections_ibfk_1` FOREIGN KEY (`forum_id`) REFERENCES `v_forums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_forum_section_topics`;
CREATE TABLE `v_forum_section_topics` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`initiated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`section_id` int(10) unsigned NOT NULL,
`initiator_id` int(10) unsigned NOT NULL,
`opponent_id` int(10) unsigned DEFAULT NULL,
`total_posts` smallint(5) unsigned NOT NULL COMMENT 'fills automatically by triggers',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'fills automatically by triggers',
PRIMARY KEY (`id`),
KEY `section_id` (`section_id`),
KEY `initiator_id` (`initiator_id`),
KEY `opponent_id` (`opponent_id`),
KEY `last_post_datetime` (`updated_at`),
CONSTRAINT `v_forum_section_topics_ibfk_2` FOREIGN KEY (`section_id`) REFERENCES `v_forum_sections` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_forum_section_topics_ibfk_5` FOREIGN KEY (`opponent_id`) REFERENCES `v_users` (`id`),
CONSTRAINT `v_forum_section_topics_ibfk_6` FOREIGN KEY (`initiator_id`) REFERENCES `v_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER `forum_sections_topics_after_insert` AFTER INSERT ON `v_forum_section_topics` FOR EACH ROW
BEGIN
UPDATE `v_forum_sections` SET `total_topics` = `total_topics` + 1 WHERE `id` = NEW.`section_id`;
END;;
CREATE TRIGGER `forum_section_topics_after_update` AFTER UPDATE ON `v_forum_section_topics` FOR EACH ROW
BEGIN
IF (NOT(NEW.`updated_at` <=> OLD.`updated_at`)) THEN
UPDATE `v_forum_sections` SET `updated_at` = (SELECT MAX(last_post_datetime) FROM `v_forum_section_topics` WHERE `section_id` = NEW.`section_id`) WHERE `id` = NEW.`section_id`;
END IF;
END;;
CREATE TRIGGER `forum_section_topics_after_delete` AFTER DELETE ON `v_forum_section_topics` FOR EACH ROW
BEGIN
UPDATE `v_forum_sections` SET `total_topics` = `total_topics` - 1 WHERE `id` = OLD.`section_id`;
END;;
DELIMITER ;
DROP TABLE IF EXISTS `v_forum_section_topic_posts`;
CREATE TABLE `v_forum_section_topic_posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`author_id` int(10) unsigned NOT NULL,
`topic_id` int(10) unsigned NOT NULL,
`text` text NOT NULL,
`update_datetime` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `author_id` (`author_id`),
KEY `topic_id` (`topic_id`),
KEY `creation_datetime` (`created_at`),
CONSTRAINT `v_forum_section_topic_posts_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `v_users` (`id`),
CONSTRAINT `v_forum_section_topic_posts_ibfk_4` FOREIGN KEY (`topic_id`) REFERENCES `v_forum_section_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER `forum_section_topic_posts_after_insert` AFTER INSERT ON `v_forum_section_topic_posts` FOR EACH ROW
BEGIN
UPDATE `v_forum_section_topics` SET `total_posts` = `total_posts` + 1, `updated_at` = NEW.`created_at` WHERE `id` = NEW.`topic_id`;
END;;
CREATE TRIGGER `forum_section_topic_posts_after_delete` AFTER DELETE ON `v_forum_section_topic_posts` FOR EACH ROW
BEGIN
#if this post is the last one in section
IF ((SELECT `updated_at` FROM `v_forum_section_topics` WHERE `id` = OLD.`topic_id`) = OLD.`created_at`) THEN
UPDATE `v_forum_section_topics`
SET
`total_posts` = `total_posts` - 1 ,
`updated_at` = (SELECT MAX(created_at) FROM `v_forum_section_topic_posts` WHERE `topic_id` = OLD.`topic_id`)
WHERE `id` = OLD.`topic_id`;
ELSE
UPDATE `v_forum_section_topics`
SET
`total_posts` = `total_posts` - 1
WHERE `id` = OLD.`topic_id`;
END IF;
END;;
DELIMITER ;
DROP TABLE IF EXISTS `v_forum_section_topic_post_versions`;
CREATE TABLE `v_forum_section_topic_post_versions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(10) unsigned NOT NULL,
`author_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`diff` text NOT NULL,
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`),
KEY `author_id` (`author_id`),
KEY `creation_datetime` (`created_at`),
CONSTRAINT `v_forum_section_topic_post_versions_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `v_forum_section_topic_posts` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_forum_section_topic_post_versions_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `v_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_gallery_albums`;
CREATE TABLE `v_gallery_albums` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`total_photos` smallint(5) unsigned NOT NULL COMMENT 'fills automatically by triggers',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'fills automatically by triggers',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `last_photo_datetime` (`updated_at`),
CONSTRAINT `album_user` FOREIGN KEY (`user_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_gallery_photos`;
CREATE TABLE `v_gallery_photos` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`album_id` int(10) unsigned DEFAULT NULL,
`uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`hash` varchar(32) NOT NULL COMMENT 'Do not change manually!',
`views_count` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `album_id` (`album_id`),
KEY `creation_datetime` (`uploaded_at`),
CONSTRAINT `photo_user` FOREIGN KEY (`user_id`) REFERENCES `v_users` (`id`),
CONSTRAINT `v_gallery_photos_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `v_gallery_albums` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER `gallery_photos_insert` AFTER INSERT ON `v_gallery_photos` FOR EACH ROW
BEGIN
IF NEW.`album_id` IS NOT NULL THEN
CALL refresh_gallery_album_meta(NEW.`album_id`);
END IF;
END;;
CREATE TRIGGER `gallery_photos_update` AFTER UPDATE ON `v_gallery_photos` FOR EACH ROW
BEGIN
IF !(NEW.`album_id` <=> OLD.`album_id`) THEN
IF OLD.`album_id` IS NOT NULL THEN
CALL refresh_gallery_album_meta(OLD.`album_id`);
END IF;
IF NEW.`album_id` IS NOT NULL THEN
CALL refresh_gallery_album_meta(NEW.`album_id`);
END IF;
END IF;
END;;
CREATE TRIGGER `gallery_photos_delete` AFTER DELETE ON `v_gallery_photos` FOR EACH ROW
BEGIN
IF OLD.`album_id` IS NOT NULL THEN
CALL refresh_gallery_album_meta(OLD.`album_id`);
END IF;
END;;
DELIMITER ;
DROP TABLE IF EXISTS `v_groups`;
CREATE TABLE `v_groups` (
`id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`creator_id` int(10) unsigned NOT NULL,
`total_members` mediumint(8) unsigned NOT NULL COMMENT 'fills automatically by triggers',
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `creator_id` (`creator_id`),
CONSTRAINT `v_groups_ibfk_2` FOREIGN KEY (`id`) REFERENCES `v_profiles` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_groups_ibfk_4` FOREIGN KEY (`creator_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_group_members`;
CREATE TABLE `v_group_members` (
`group_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`role` enum('member','moderator','administrator') NOT NULL,
`approved` enum('0','1') NOT NULL,
PRIMARY KEY (`group_id`,`user_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `v_group_members_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_group_members_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `v_groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER `members_after_insert` AFTER INSERT ON `v_group_members` FOR EACH ROW
BEGIN
UPDATE `v_groups` SET `total_members` = `total_members` + 1 WHERE `id` = NEW.`group_id`;
END;;
CREATE TRIGGER `members_after_delete` AFTER DELETE ON `v_group_members` FOR EACH ROW
BEGIN
UPDATE `v_groups` SET `total_members` = `total_members` - 1 WHERE `id` = OLD.`group_id`;
END;;
DELIMITER ;
DROP TABLE IF EXISTS `v_group_topics`;
CREATE TABLE `v_group_topics` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`group_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`initiated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`initiator_id` int(10) unsigned NOT NULL,
`opponent_id` int(10) unsigned DEFAULT NULL,
`total_posts` smallint(5) unsigned NOT NULL COMMENT 'fills automatically by triggers',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'fills automatically by triggers',
PRIMARY KEY (`id`),
KEY `initiator_id` (`initiator_id`),
KEY `opponent_id` (`opponent_id`),
KEY `last_post_datetime` (`updated_at`),
KEY `group_id` (`group_id`),
CONSTRAINT `v_group_topics_ibfk_4` FOREIGN KEY (`initiator_id`) REFERENCES `v_users` (`id`),
CONSTRAINT `v_group_topics_ibfk_5` FOREIGN KEY (`opponent_id`) REFERENCES `v_users` (`id`),
CONSTRAINT `v_group_topics_ibfk_6` FOREIGN KEY (`group_id`) REFERENCES `v_groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_group_topic_posts`;
CREATE TABLE `v_group_topic_posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`author_id` int(10) unsigned NOT NULL,
`topic_id` int(10) unsigned NOT NULL,
`text` text NOT NULL,
`update_datetime` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `author_id` (`author_id`),
KEY `topic_id` (`topic_id`),
KEY `creation_datetime` (`created_at`),
CONSTRAINT `v_group_topic_posts_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `v_users` (`id`),
CONSTRAINT `v_group_topic_posts_ibfk_5` FOREIGN KEY (`topic_id`) REFERENCES `v_group_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER `v_group_topic_posts_ai` AFTER INSERT ON `v_group_topic_posts` FOR EACH ROW
BEGIN
UPDATE `v_group_topics` SET `total_posts` = `total_posts` + 1, `updated_at` = NEW.`created_at` WHERE `id` = NEW.`topic_id`;
END;;
CREATE TRIGGER `v_group_topic_posts_ad` AFTER DELETE ON `v_group_topic_posts` FOR EACH ROW
BEGIN
#if this post is the last one in section
IF ((SELECT COUNT(*) FROM `v_group_topics` WHERE `id` = OLD.`topic_id`) = 1) THEN
UPDATE `v_group_topics`
SET
`total_posts` = `total_posts` - 1,
`updated_at` = (SELECT MAX(`created_at`) FROM `v_group_topic_posts` WHERE `topic_id` = OLD.`topic_id`)
WHERE `id` = OLD.`topic_id`;
ELSE
UPDATE `v_group_topics`
SET
`total_posts` = `total_posts` - 1
WHERE `id` = OLD.`topic_id`;
END IF;
END;;
DELIMITER ;
DROP TABLE IF EXISTS `v_profiles`;
CREATE TABLE `v_profiles` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` enum('user','group','public') NOT NULL,
`avatar_id` int(10) unsigned DEFAULT NULL,
`alias` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `avatar_id` (`avatar_id`),
KEY `alias` (`alias`),
CONSTRAINT `v_profiles_ibfk_1` FOREIGN KEY (`avatar_id`) REFERENCES `v_gallery_photos` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_publics`;
CREATE TABLE `v_publics` (
`id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`creator_id` int(10) unsigned NOT NULL,
`total_readers` mediumint(8) unsigned NOT NULL COMMENT 'fills automatically by triggers',
PRIMARY KEY (`id`),
KEY `creator_id` (`creator_id`),
CONSTRAINT `v_publics_ibfk_1` FOREIGN KEY (`id`) REFERENCES `v_profiles` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_publics_ibfk_2` FOREIGN KEY (`creator_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_public_posts`;
CREATE TABLE `v_public_posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`public_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT '0000-00-00 00:00:00',
`author_id` int(10) unsigned NOT NULL,
`editor_id` int(10) unsigned DEFAULT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`),
KEY `author_id` (`author_id`),
KEY `creation_datetime` (`created_at`),
KEY `public_id` (`public_id`),
KEY `editor_id` (`editor_id`),
CONSTRAINT `v_public_posts_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_public_posts_ibfk_3` FOREIGN KEY (`public_id`) REFERENCES `v_publics` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_public_posts_ibfk_4` FOREIGN KEY (`editor_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_public_post_comments`;
CREATE TABLE `v_public_post_comments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(10) unsigned NOT NULL,
`created_at` datetime NOT NULL,
`author_id` int(10) unsigned NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`),
KEY `author_id` (`author_id`),
CONSTRAINT `v_public_post_comments_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `v_public_posts` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_public_post_comments_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_public_readers`;
CREATE TABLE `v_public_readers` (
`public_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`role` enum('reader','moderator','writer','administrator') NOT NULL,
PRIMARY KEY (`public_id`,`user_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `v_public_readers_ibfk_1` FOREIGN KEY (`public_id`) REFERENCES `v_publics` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_public_readers_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER `readers_after_insert` AFTER INSERT ON `v_public_readers` FOR EACH ROW
BEGIN
UPDATE `v_publics` SET `total_readers` = `total_readers` + 1 WHERE `id` = NEW.`public_id`;
END;;
CREATE TRIGGER `readers_after_delete` AFTER DELETE ON `v_public_readers` FOR EACH ROW
BEGIN
UPDATE `v_publics` SET `total_readers` = `total_readers` - 1 WHERE `id` = OLD.`public_id`;
END;;
DELIMITER ;
DROP TABLE IF EXISTS `v_settings`;
CREATE TABLE `v_settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`value` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `category_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_users`;
CREATE TABLE `v_users` (
`id` int(10) unsigned NOT NULL,
`email` varchar(255) NOT NULL,
`password_hash` varchar(64) NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`patronymic` varchar(255) DEFAULT NULL,
`gender` enum('male','female') NOT NULL,
`birth_date` date DEFAULT NULL,
`registrated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`visited_at` timestamp NULL DEFAULT NULL,
`timezone` varchar(50) DEFAULT NULL,
`language` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `gender` (`gender`),
KEY `birth_date` (`birth_date`),
KEY `last_activity_datetime` (`visited_at`),
CONSTRAINT `v_users_ibfk_1` FOREIGN KEY (`id`) REFERENCES `v_profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_user_friends`;
CREATE TABLE `v_user_friends` (
`user_id` int(10) unsigned NOT NULL,
`friend_id` int(10) unsigned NOT NULL,
`approved` enum('0','1') NOT NULL,
`message` text NOT NULL,
PRIMARY KEY (`user_id`,`friend_id`),
KEY `friend_id` (`friend_id`),
CONSTRAINT `v_user_friends_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_user_friends_ibfk_2` FOREIGN KEY (`friend_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_user_messages`;
CREATE TABLE `v_user_messages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`receiver_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`text` text NOT NULL,
`new` enum('0','1') NOT NULL,
PRIMARY KEY (`id`),
KEY `receiver_id` (`receiver_id`),
KEY `user_id_receiver_id` (`user_id`,`receiver_id`),
CONSTRAINT `v_user_messages_ibfk_1` FOREIGN KEY (`receiver_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE,
CONSTRAINT `v_user_messages_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `v_user_notifications`;
CREATE TABLE `v_user_notifications` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`text` text NOT NULL,
`type` enum('success','danger','warning','info') NOT NULL,
`new` enum('0','1') NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `creation_datetime` (`created_at`),
KEY `new` (`new`),
CONSTRAINT `v_user_notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `v_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 2014-04-14 03:00:20