365 lines
15 KiB
SQL
365 lines
15 KiB
SQL
-- MySQL Script generated by MySQL Workbench
|
||
-- Út 26. leden 2016, 22:44:34 CET
|
||
-- Model: New Model Version: 1.0
|
||
-- MySQL Workbench Forward Engineering
|
||
|
||
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
|
||
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
|
||
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
|
||
|
||
-- -----------------------------------------------------
|
||
-- Schema minicms
|
||
-- -----------------------------------------------------
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `users`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `users` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`email` VARCHAR(64) NOT NULL COMMENT 'E-mail is used as a contact and also as a username',
|
||
`username` VARCHAR(64) NOT NULL COMMENT 'Username can be used also for login. Default value is users email.',
|
||
`password` VARCHAR(128) NOT NULL COMMENT 'Password hash',
|
||
`role` ENUM('visitor', 'editor', 'admin') NOT NULL DEFAULT 'visitor' COMMENT 'User\'s role determines his permissions in the system.',
|
||
`remember_token` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Token used for \"remember me\" feature',
|
||
`name` VARCHAR(128) NULL DEFAULT NULL,
|
||
`status` ENUM('deleted', 'blocked', 'active') NOT NULL DEFAULT 'active',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE INDEX `email_UNIQUE` (`email` ASC))
|
||
ENGINE = InnoDB
|
||
COMMENT = 'Table with users who are allowed to log into system';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `modules`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `modules` (
|
||
`handler` VARCHAR(255) NOT NULL,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`name` VARCHAR(64) NOT NULL,
|
||
`description` TEXT NULL DEFAULT NULL,
|
||
PRIMARY KEY (`handler`))
|
||
ENGINE = InnoDB;
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `files`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `files` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`user_id` INT NOT NULL COMMENT 'User, who uploaded this file',
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`filename` VARCHAR(128) NOT NULL,
|
||
`sha256_hash` VARCHAR(64) NOT NULL COMMENT 'SHA hash of file content.',
|
||
`mime_type` VARCHAR(64) NOT NULL COMMENT 'MIME type of uploaded file',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `fk_file_user1_idx` (`user_id` ASC),
|
||
CONSTRAINT `fk_file_user1`
|
||
FOREIGN KEY (`user_id`)
|
||
REFERENCES `users` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION)
|
||
ENGINE = InnoDB
|
||
COMMENT = 'Table containing files which were uploaded into system.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `contents`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `contents` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`user_id` INT NOT NULL,
|
||
`module_handler` VARCHAR(255) NOT NULL,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`published_from` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`published_to` TIMESTAMP NULL DEFAULT NULL,
|
||
`title_photo` INT NULL DEFAULT NULL,
|
||
`title` VARCHAR(64) NOT NULL,
|
||
`url` VARCHAR(64) NOT NULL,
|
||
`meta_keywords` VARCHAR(128) NULL DEFAULT NULL,
|
||
`meta_description` MEDIUMTEXT NULL DEFAULT NULL,
|
||
`content` TEXT NULL DEFAULT NULL,
|
||
`module_settings` TEXT NULL COMMENT 'JSON serialized object passed as settings to the module.',
|
||
`status` ENUM('deleted', 'draft', 'protected', 'public') NULL DEFAULT 'draft' COMMENT 'This photo is supposed to be displayed e.g. on list of all pages.',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `fk_content_user1_idx` (`user_id` ASC),
|
||
INDEX `url` (`url` ASC),
|
||
FULLTEXT INDEX `content_fulltext` (`content` ASC),
|
||
UNIQUE INDEX `url_UNIQUE` (`url` ASC),
|
||
INDEX `fk_content_module1_idx` (`module_handler` ASC),
|
||
INDEX `fk_content_file1_idx` (`title_photo` ASC),
|
||
CONSTRAINT `fk_content_user1`
|
||
FOREIGN KEY (`user_id`)
|
||
REFERENCES `users` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION,
|
||
CONSTRAINT `fk_content_module1`
|
||
FOREIGN KEY (`module_handler`)
|
||
REFERENCES `modules` (`handler`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION,
|
||
CONSTRAINT `fk_content_file1`
|
||
FOREIGN KEY (`title_photo`)
|
||
REFERENCES `files` (`id`)
|
||
ON DELETE SET NULL
|
||
ON UPDATE CASCADE)
|
||
ENGINE = InnoDB
|
||
COMMENT = 'Table with all content pages.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `content_history`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `content_history` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`user_id` INT NOT NULL,
|
||
`content_id` INT NOT NULL,
|
||
`changed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`edit_batch` VARCHAR(16) NOT NULL COMMENT 'This field holds identical values for all historical entries edited as one update.',
|
||
`column` VARCHAR(64) NOT NULL,
|
||
`new_value` TEXT NULL DEFAULT NULL,
|
||
`old_value` TEXT NULL DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
INDEX `edit_batch` (`edit_batch` ASC),
|
||
INDEX `column` (`column` ASC),
|
||
INDEX `fk_content_history_users_idx` (`user_id` ASC),
|
||
INDEX `fk_content_history_content1_idx` (`content_id` ASC),
|
||
CONSTRAINT `fk_content_history_users`
|
||
FOREIGN KEY (`user_id`)
|
||
REFERENCES `users` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION,
|
||
CONSTRAINT `fk_content_history_content1`
|
||
FOREIGN KEY (`content_id`)
|
||
REFERENCES `contents` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION)
|
||
ENGINE = InnoDB
|
||
COMMENT = 'Table for holding history of changes on some content.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `widget_types`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `widget_types` (
|
||
`handler` VARCHAR(255) NOT NULL COMMENT 'Class wich handles everything about this widget type',
|
||
`name` VARCHAR(64) NOT NULL COMMENT 'Name is used only in admin for better readability.',
|
||
`description` TEXT NULL DEFAULT NULL COMMENT 'This field also improves readability.',
|
||
PRIMARY KEY (`handler`))
|
||
ENGINE = InnoDB
|
||
COMMENT = 'Widget type table contains information about all installed widgets. This is something like \"class\" to widgets.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `widgets`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `widgets` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`widget_type_handler` VARCHAR(255) NOT NULL,
|
||
`user_id` INT NOT NULL COMMENT 'User ID who created this widget',
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`name` VARCHAR(64) NOT NULL,
|
||
`settings` TEXT NOT NULL COMMENT 'JSON serialized values used for initializing given widget.',
|
||
`description` MEDIUMTEXT NULL,
|
||
PRIMARY KEY (`id`),
|
||
INDEX `fk_widget_user1_idx` (`user_id` ASC),
|
||
INDEX `fk_widget_widget_type1_idx` (`widget_type_handler` ASC),
|
||
CONSTRAINT `fk_widget_user1`
|
||
FOREIGN KEY (`user_id`)
|
||
REFERENCES `users` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION,
|
||
CONSTRAINT `fk_widget_widget_type1`
|
||
FOREIGN KEY (`widget_type_handler`)
|
||
REFERENCES `widget_types` (`handler`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION)
|
||
ENGINE = InnoDB
|
||
COMMENT = 'Widget is a small piece of content, which can be inserted into widget area. Example of widgets: menu (with links), banner, some static html code. This table contains real \"instances\" of widgets.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `widget_areas`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `widget_areas` (
|
||
`id` INT NOT NULL,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`name` VARCHAR(64) NOT NULL COMMENT 'Used for user overview.',
|
||
`code` VARCHAR(32) NOT NULL COMMENT 'Value of this field is used for replacing placeholder text in templates with content of real widget area.',
|
||
`description` TEXT NULL DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE INDEX `code_UNIQUE` (`code` ASC))
|
||
ENGINE = InnoDB
|
||
COMMENT = 'Widget area is container for widgets. This container is inserted into templates/content and populated by widgets.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `widget_in_widget_area`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `widget_in_widget_area` (
|
||
`widget_id` INT NOT NULL,
|
||
`widget_area_id` INT NOT NULL,
|
||
`position` INT NOT NULL COMMENT 'Which position should the widget take?',
|
||
PRIMARY KEY (`widget_id`, `widget_area_id`),
|
||
INDEX `fk_widget_has_widget_area_widget_area1_idx` (`widget_area_id` ASC),
|
||
INDEX `fk_widget_has_widget_area_widget1_idx` (`widget_id` ASC),
|
||
INDEX `position` (`position` ASC),
|
||
CONSTRAINT `fk_widget_has_widget_area_widget1`
|
||
FOREIGN KEY (`widget_id`)
|
||
REFERENCES `widgets` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION,
|
||
CONSTRAINT `fk_widget_has_widget_area_widget_area1`
|
||
FOREIGN KEY (`widget_area_id`)
|
||
REFERENCES `widget_areas` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION)
|
||
ENGINE = InnoDB;
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `custom_field_types`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `custom_field_types` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(64) NOT NULL COMMENT 'Name of custom field. Given as a translation string (e.g. as dir/section.translation)',
|
||
`handler` VARCHAR(255) NOT NULL COMMENT 'Class for handling this type of custom field',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE INDEX `name_UNIQUE` (`name` ASC),
|
||
UNIQUE INDEX `handler_UNIQUE` (`handler` ASC))
|
||
ENGINE = InnoDB
|
||
COMMENT = 'List of all custom field types.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `custom_field_resource`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `custom_field_resource` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(64) NOT NULL COMMENT 'Name of resource for custom fields. Saved as translation string.',
|
||
PRIMARY KEY (`id`))
|
||
ENGINE = InnoDB;
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `custom_fields`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `custom_fields` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(128) NOT NULL,
|
||
`type_id` INT NOT NULL COMMENT 'Foreign key to custom_field_types table',
|
||
`resource_id` INT NOT NULL,
|
||
`description` MEDIUMTEXT NULL DEFAULT NULL COMMENT 'Description used for detailed information about meaning of given custom field',
|
||
`default_value` TEXT NULL DEFAULT NULL,
|
||
`possible_values` TEXT NULL DEFAULT NULL COMMENT 'Possible values separated with pipeline',
|
||
`regexp` MEDIUMTEXT NULL DEFAULT NULL,
|
||
`min_length` INT(11) NULL DEFAULT NULL,
|
||
`max_length` INT(11) NULL DEFAULT NULL,
|
||
`required` TINYINT(1) NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (`id`),
|
||
INDEX `fk_custom_fields_custom_field_types1_idx` (`type_id` ASC),
|
||
INDEX `fk_custom_fields_custom_field_resource1_idx` (`resource_id` ASC),
|
||
CONSTRAINT `fk_custom_fields_custom_field_types1`
|
||
FOREIGN KEY (`type_id`)
|
||
REFERENCES `custom_field_types` (`id`)
|
||
ON DELETE CASCADE
|
||
ON UPDATE CASCADE,
|
||
CONSTRAINT `fk_custom_fields_custom_field_resource1`
|
||
FOREIGN KEY (`resource_id`)
|
||
REFERENCES `custom_field_resource` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION)
|
||
ENGINE = InnoDB
|
||
COMMENT = 'List of all custom fields which may be used for extending some part of content on webpage';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `user_has_custom_field`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `user_has_custom_field` (
|
||
`user_id` INT NOT NULL,
|
||
`custom_field_id` INT NOT NULL,
|
||
`value` TEXT NOT NULL,
|
||
PRIMARY KEY (`user_id`, `custom_field_id`),
|
||
INDEX `fk_user_has_custom_field_custom_field1_idx` (`custom_field_id` ASC),
|
||
INDEX `fk_user_has_custom_field_user1_idx` (`user_id` ASC),
|
||
CONSTRAINT `fk_user_has_custom_field_user1`
|
||
FOREIGN KEY (`user_id`)
|
||
REFERENCES `users` (`id`)
|
||
ON DELETE CASCADE
|
||
ON UPDATE CASCADE,
|
||
CONSTRAINT `fk_user_has_custom_field_custom_field1`
|
||
FOREIGN KEY (`custom_field_id`)
|
||
REFERENCES `custom_fields` (`id`)
|
||
ON DELETE CASCADE
|
||
ON UPDATE CASCADE)
|
||
ENGINE = InnoDB;
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `action_log_types`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `action_log_types` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(128) NOT NULL,
|
||
`value` VARCHAR(128) NOT NULL COMMENT 'Log content - represented as translation string.',
|
||
PRIMARY KEY (`id`))
|
||
ENGINE = InnoDB
|
||
COMMENT = 'List of all types which can be logged.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `action_log`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `action_log` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`type_id` INT NOT NULL,
|
||
`user_id` INT NOT NULL,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`parameters` MEDIUMTEXT NULL DEFAULT NULL COMMENT 'JSON serialized array/object passed into translation.',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `fk_action_log_action_log_type1_idx` (`type_id` ASC),
|
||
INDEX `fk_action_log_user1_idx` (`user_id` ASC),
|
||
CONSTRAINT `fk_action_log_action_log_type1`
|
||
FOREIGN KEY (`type_id`)
|
||
REFERENCES `action_log_types` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION,
|
||
CONSTRAINT `fk_action_log_user1`
|
||
FOREIGN KEY (`user_id`)
|
||
REFERENCES `users` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION)
|
||
ENGINE = InnoDB
|
||
COMMENT = 'This table contains log of all actions, which happened in administration.';
|
||
|
||
|
||
-- -----------------------------------------------------
|
||
-- Table `content_has_custom_fields`
|
||
-- -----------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS `content_has_custom_fields` (
|
||
`contents_id` INT NOT NULL,
|
||
`custom_field_id` INT NOT NULL,
|
||
`value` TEXT NULL,
|
||
PRIMARY KEY (`contents_id`, `custom_field_id`),
|
||
INDEX `fk_contents_has_custom_fields_custom_fields1_idx` (`custom_field_id` ASC),
|
||
INDEX `fk_contents_has_custom_fields_contents1_idx` (`contents_id` ASC),
|
||
CONSTRAINT `fk_contents_has_custom_fields_contents1`
|
||
FOREIGN KEY (`contents_id`)
|
||
REFERENCES `contents` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION,
|
||
CONSTRAINT `fk_contents_has_custom_fields_custom_fields1`
|
||
FOREIGN KEY (`custom_field_id`)
|
||
REFERENCES `custom_fields` (`id`)
|
||
ON DELETE NO ACTION
|
||
ON UPDATE NO ACTION)
|
||
ENGINE = InnoDB;
|
||
|
||
|
||
SET SQL_MODE=@OLD_SQL_MODE;
|
||
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
|
||
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
|