minicms/core
1
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
Files
master
core/database/schema.sql
T

713 lines
37 KiB
SQL
Raw Permalink Blame History

-- MySQL dump 10.16 Distrib 10.1.17-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: minicms
-- ------------------------------------------------------
-- Server version 10.1.17-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `action_log`
--
DROP TABLE IF EXISTS `action_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`ip` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'User''s IP Address',
`user_agent` mediumtext COLLATE utf8_unicode_ci NOT NULL COMMENT 'Browser string of client, who performed some action.',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`parameters` mediumtext COLLATE utf8_unicode_ci COMMENT 'JSON serialized array/object passed into translation.',
PRIMARY KEY (`id`),
KEY `fk_action_log_action_log_type1_idx` (`type_id`),
KEY `fk_action_log_user1_idx` (`user_id`),
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 AUTO_INCREMENT=284 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='This table contains log of all actions, which happened in administration.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `action_log_types`
--
DROP TABLE IF EXISTS `action_log_types`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action_log_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`string_id` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'String ID used for better readability in loggers',
`name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`value` varchar(128) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Log content - represented as translation string.',
PRIMARY KEY (`id`),
UNIQUE KEY `action_log_types_string_id_unique` (`string_id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='List of all types which can be logged.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `categories`
--
DROP TABLE IF EXISTS `categories`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `categories` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
`position` int(10) unsigned NOT NULL,
`real_depth` int(10) unsigned NOT NULL,
`user_id` int(11) NOT NULL COMMENT 'Author of this category',
`module_id` int(11) NOT NULL COMMENT 'ID of module, where this category is used',
`name` varchar(255) NOT NULL COMMENT 'Human readable name of category',
`url` varchar(255) NOT NULL COMMENT 'URL slug of category',
`description` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `categories_url_module_id_unique` (`url`,`module_id`),
KEY `categories_url_index` (`url`),
KEY `categories_parent_id_foreign` (`parent_id`),
KEY `categories_user_id_foreign` (`user_id`),
KEY `categories_module_id_foreign` (`module_id`),
CONSTRAINT `categories_module_id_foreign` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
CONSTRAINT `categories_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `category_closure`
--
DROP TABLE IF EXISTS `category_closure`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `category_closure` (
`closure_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ancestor` int(10) unsigned NOT NULL,
`descendant` int(10) unsigned NOT NULL,
`depth` int(10) unsigned NOT NULL,
PRIMARY KEY (`closure_id`),
KEY `category_closure_ancestor_foreign` (`ancestor`),
KEY `category_closure_descendant_foreign` (`descendant`),
CONSTRAINT `category_closure_ancestor_foreign` FOREIGN KEY (`ancestor`) REFERENCES `categories` (`id`) ON DELETE CASCADE,
CONSTRAINT `category_closure_descendant_foreign` FOREIGN KEY (`descendant`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `comments`
--
DROP TABLE IF EXISTS `comments`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `comments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`user_id` int(11) DEFAULT NULL COMMENT 'When comment was inserted by logged user, save his ID here',
`content_id` int(11) NOT NULL,
`email` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`content` text NOT NULL,
`approved` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `comments_content_id_foreign` (`content_id`),
KEY `comments_user_id_foreign` (`user_id`),
CONSTRAINT `comments_content_id_foreign` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `comments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `content_has_custom_fields`
--
DROP TABLE IF EXISTS `content_has_custom_fields`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `content_has_custom_fields` (
`contents_id` int(11) NOT NULL,
`custom_field_id` int(11) NOT NULL,
`value` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`contents_id`,`custom_field_id`),
KEY `fk_contents_has_custom_fields_custom_fields1_idx` (`custom_field_id`),
KEY `fk_contents_has_custom_fields_contents1_idx` (`contents_id`),
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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `content_history`
--
DROP TABLE IF EXISTS `content_history`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `content_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`content_id` int(11) NOT NULL,
`changed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`edit_batch` varchar(16) COLLATE utf8_unicode_ci NOT NULL COMMENT 'This field holds identical values for all historical entries edited as one update.',
`column` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`new_value` text COLLATE utf8_unicode_ci,
`old_value` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
KEY `edit_batch` (`edit_batch`),
KEY `column` (`column`),
KEY `fk_content_history_users_idx` (`user_id`),
KEY `fk_content_history_content1_idx` (`content_id`),
CONSTRAINT `fk_content_history_content1` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_content_history_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table for holding history of changes on some content.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `contents`
--
DROP TABLE IF EXISTS `contents`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contents` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL COMMENT 'ID of parent page',
`user_id` int(11) NOT NULL,
`module_handler` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`category_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to table categories',
`template` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Template used for rendering this content page.',
`language` varchar(8) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'cs',
`is_homepage` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is this content page the homepage?',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`viewed` int(11) NOT NULL COMMENT 'How many times has been this content page displayed?',
`published_from` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`published_to` timestamp NULL DEFAULT NULL,
`title_photo` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'This photo is supposed to be displayed e.g. on list of all pages.',
`title` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`url` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`meta_keywords` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` mediumtext COLLATE utf8_unicode_ci,
`content` text COLLATE utf8_unicode_ci,
`module_settings` text COLLATE utf8_unicode_ci COMMENT 'JSON serialized object passed as settings to the module.',
`status` enum('draft','protected','public','deleted') COLLATE utf8_unicode_ci DEFAULT 'draft' COMMENT 'This photo is supposed to be displayed e.g. on list of all pages.',
PRIMARY KEY (`id`),
UNIQUE KEY `contents_language_url_unique` (`language`,`url`),
KEY `fk_content_user1_idx` (`user_id`),
KEY `url` (`url`),
KEY `fk_content_module1_idx` (`module_handler`),
KEY `fk_content_file1_idx` (`title_photo`),
KEY `contents_is_homepage_index` (`is_homepage`),
KEY `contents_template_index` (`template`),
KEY `contents_parent_id_index` (`parent_id`),
KEY `contents_language_index` (`language`),
KEY `contents_category_id_foreign` (`category_id`),
CONSTRAINT `contents_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `contents_language_foreign` FOREIGN KEY (`language`) REFERENCES `languages` (`code`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `contents_module_handler_foreign` FOREIGN KEY (`module_handler`) REFERENCES `modules` (`handler`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `contents_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `contents_template_foreign` FOREIGN KEY (`template`) REFERENCES `templates` (`template_name`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `contents_title_photo_foreign` FOREIGN KEY (`title_photo`) REFERENCES `files` (`filename`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_content_user1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table with all content pages.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `contents_has_files`
--
DROP TABLE IF EXISTS `contents_has_files`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contents_has_files` (
`content_id` int(11) NOT NULL,
`file_id` int(11) NOT NULL,
PRIMARY KEY (`content_id`,`file_id`),
KEY `contents_has_files_file_id_foreign` (`file_id`),
CONSTRAINT `contents_has_files_content_id_foreign` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `contents_has_files_file_id_foreign` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `custom_field_resource`
--
DROP TABLE IF EXISTS `custom_field_resource`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `custom_field_resource` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of resource for custom fields. Saved as translation string.',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `custom_field_resources`
--
DROP TABLE IF EXISTS `custom_field_resources`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `custom_field_resources` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of resource for custom fields. Saved as translation string.',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `custom_field_types`
--
DROP TABLE IF EXISTS `custom_field_types`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `custom_field_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of custom field. Given as a translation string (e.g. as dir/section.translation)',
`handler` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Class for handling this type of custom field',
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`),
UNIQUE KEY `handler_UNIQUE` (`handler`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='List of all custom field types.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `custom_fields`
--
DROP TABLE IF EXISTS `custom_fields`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `custom_fields` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`type_id` int(11) NOT NULL COMMENT 'Foreign key to custom_field_types table',
`resource_id` int(11) NOT NULL,
`description` mediumtext COLLATE utf8_unicode_ci COMMENT 'Description used for detailed information about meaning of given custom field',
`default_value` text COLLATE utf8_unicode_ci,
`possible_values` text COLLATE utf8_unicode_ci COMMENT 'Possible values separated with pipeline',
`regexp` mediumtext COLLATE utf8_unicode_ci,
`min_length` int(11) DEFAULT NULL,
`max_length` int(11) DEFAULT NULL,
`required` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_custom_fields_custom_field_types1_idx` (`type_id`),
KEY `fk_custom_fields_custom_field_resource1_idx` (`resource_id`),
CONSTRAINT `fk_custom_fields_custom_field_resource1` FOREIGN KEY (`resource_id`) REFERENCES `custom_field_resources` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_custom_fields_custom_field_types1` FOREIGN KEY (`type_id`) REFERENCES `custom_field_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='List of all custom fields which may be used for extending some part of content on webpage';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `directories`
--
DROP TABLE IF EXISTS `directories`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `directories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`user_id` int(11) DEFAULT NULL COMMENT 'ID of user, who created this directory.',
`parent_id` int(11) DEFAULT NULL COMMENT 'ID of parent directory',
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Directory name used for better navigation',
`description` mediumtext COLLATE utf8_unicode_ci COMMENT 'Description of directory',
`hidden` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Hide this directory from view of all directories?',
PRIMARY KEY (`id`),
UNIQUE KEY `directories_parent_id_name_unique` (`parent_id`,`name`),
KEY `directories_user_id_index` (`user_id`),
KEY `directories_parent_id_index` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `files`
--
DROP TABLE IF EXISTS `files`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `files` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT 'User, who uploaded this file',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
`filename` varchar(128) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Filename of localy stored file',
`directory_id` int(11) DEFAULT NULL COMMENT 'FK to directories table',
`original_filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Original filename of uploaded file',
`file_hash` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'SHA hash of file content.',
`hash_algorithm` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`mime_type` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'MIME type of uploaded file',
`file_size` int(11) NOT NULL COMMENT 'Filesize given in bytes',
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Some human readable name of file. May be used in different ways',
`description` text COLLATE utf8_unicode_ci,
`keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_file_user1_idx` (`user_id`),
KEY `filename` (`filename`),
KEY `files_filename_index` (`filename`),
KEY `keywords_idx` (`keywords`),
KEY `files_directory_id_index` (`directory_id`),
CONSTRAINT `files_directory_id_foreign` FOREIGN KEY (`directory_id`) REFERENCES `directories` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_file_user1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table containing files which were uploaded into system.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `languages`
--
DROP TABLE IF EXISTS `languages`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `languages` (
`code` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Translated name for given language',
`script` varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Latn' COMMENT 'ISO code for locale script (Latn, Arab, ...)',
`native` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Native name for language',
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `migrations`
--
DROP TABLE IF EXISTS `migrations`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `migrations` (
`migration` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`batch` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `modules`
--
DROP TABLE IF EXISTS `modules`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `modules` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL COMMENT 'Parent module ID (when module is submodule)',
`handler` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`description` text COLLATE utf8_unicode_ci,
`alias` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` tinyint(1) NOT NULL COMMENT 'Is this module in use?',
`module_settings` text COLLATE utf8_unicode_ci COMMENT 'JSON serialized object with configuration of given module.',
`manages_content` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Does this module support content management?',
`support_categories` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Does this module support categories?',
`has_submodules` tinyint(1) NOT NULL DEFAULT '0',
`is_template` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Templates can be treated as modules.',
PRIMARY KEY (`id`),
UNIQUE KEY `modules_handler_unique` (`handler`),
KEY `modules_enabled_index` (`enabled`),
KEY `modules_parent_id_foreign` (`parent_id`),
CONSTRAINT `modules_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `password_resets`
--
DROP TABLE IF EXISTS `password_resets`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `password_resets` (
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `password_resets_email_index` (`email`),
KEY `password_resets_token_index` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `settings`
--
DROP TABLE IF EXISTS `settings`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`language` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`key` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'User readable key for accessing value of this config. Config will be available under this key also in views.',
`value` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Value of config.',
`load_into_views` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Use in views directly?',
PRIMARY KEY (`id`),
UNIQUE KEY `settings_key_language_unique` (`key`,`language`),
KEY `settings_load_into_views_index` (`load_into_views`),
KEY `settings_language_foreign` (`language`),
CONSTRAINT `settings_language_foreign` FOREIGN KEY (`language`) REFERENCES `languages` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `template_paths`
--
DROP TABLE IF EXISTS `template_paths`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `template_paths` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL COMMENT 'ID of parent directory',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`path` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Path on filesystem',
`namespace` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Namespace used for all templates in this path',
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Human readable name',
PRIMARY KEY (`id`),
KEY `template_paths_parent_id_index` (`parent_id`),
CONSTRAINT `template_paths_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `template_paths` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `templates`
--
DROP TABLE IF EXISTS `templates`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `templates` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`module_id` int(11) DEFAULT NULL COMMENT 'Template belongs to module specified with this ID.',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`template_name` varchar(128) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Template name used for rendering page. Use namespace::dir.template format.',
`path` int(11) NOT NULL COMMENT 'ID of template directory',
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Human readable name of given template',
`description` mediumtext COLLATE utf8_unicode_ci NOT NULL COMMENT 'Description of this template.',
`editable` tinyint(1) NOT NULL COMMENT 'Can this template be edited?',
PRIMARY KEY (`id`),
KEY `templates_template_name_index` (`template_name`),
KEY `templates_path_index` (`path`),
KEY `templates_module_id_index` (`module_id`),
CONSTRAINT `templates_module_id_foreign` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `templates_path_foreign` FOREIGN KEY (`path`) REFERENCES `template_paths` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_has_custom_field`
--
DROP TABLE IF EXISTS `user_has_custom_field`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_has_custom_field` (
`user_id` int(11) NOT NULL,
`custom_field_id` int(11) NOT NULL,
`value` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`user_id`,`custom_field_id`),
KEY `fk_user_has_custom_field_custom_field1_idx` (`custom_field_id`),
KEY `fk_user_has_custom_field_user1_idx` (`user_id`),
CONSTRAINT `fk_user_has_custom_field_custom_field1` FOREIGN KEY (`custom_field_id`) REFERENCES `custom_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_user_has_custom_field_user1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `users`
--
DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'E-mail is used as a contact and also as a username',
`username` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Username can be used also for login. Default value is users email.',
`password` varchar(128) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Password hash',
`role` enum('visitor','editor','admin') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'visitor' COMMENT 'User''s role determines his permissions in the system.',
`remember_token` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Token used for "remember me" feature',
`name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`status` enum('deleted','blocked','active') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
PRIMARY KEY (`id`),
UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table with users who are allowed to log into system';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `widget_areas`
--
DROP TABLE IF EXISTS `widget_areas`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `widget_areas` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Used for user overview.',
`code` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Value of this field is used for replacing placeholder text in templates with content of real widget area.',
`description` text COLLATE utf8_unicode_ci,
`deletable` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Can this widget area be deleted?',
PRIMARY KEY (`id`),
UNIQUE KEY `code_UNIQUE` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Widget area is container for widgets. This container is inserted into templates/content and populated by widgets.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `widget_in_widget_area`
--
DROP TABLE IF EXISTS `widget_in_widget_area`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `widget_in_widget_area` (
`widget_id` int(11) NOT NULL,
`widget_area_id` int(11) NOT NULL,
`position` int(11) NOT NULL COMMENT 'Which position should the widget take?',
PRIMARY KEY (`widget_id`,`widget_area_id`),
KEY `fk_widget_has_widget_area_widget_area1_idx` (`widget_area_id`),
KEY `fk_widget_has_widget_area_widget1_idx` (`widget_id`),
KEY `position` (`position`),
CONSTRAINT `fk_widget_has_widget_area_widget1` FOREIGN KEY (`widget_id`) REFERENCES `widgets` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `widget_in_widget_area_widget_area_id_foreign` FOREIGN KEY (`widget_area_id`) REFERENCES `widget_areas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `widget_types`
--
DROP TABLE IF EXISTS `widget_types`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `widget_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`handler` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Class wich handles everything about this widget type',
`template_id` int(11) DEFAULT NULL COMMENT 'Commes this widget with template?',
`module_id` int(11) DEFAULT NULL COMMENT 'Commes this widget with some module?',
`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name is used only in admin for better readability.',
`description` text COLLATE utf8_unicode_ci COMMENT 'This field also improves readability.',
PRIMARY KEY (`id`),
UNIQUE KEY `widget_types_handler_unique` (`handler`),
KEY `widget_types_template_id_foreign` (`template_id`),
KEY `widget_types_module_id_foreign` (`module_id`),
CONSTRAINT `widget_types_module_id_foreign` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `widget_types_template_id_foreign` FOREIGN KEY (`template_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Widget type table contains information about all installed widgets. This is something like "class" to widgets.';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `widgets`
--
DROP TABLE IF EXISTS `widgets`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `widgets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`widget_type_handler` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`language` varchar(8) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'cs',
`user_id` int(11) 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) COLLATE utf8_unicode_ci NOT NULL,
`settings` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'JSON serialized values used for initializing given widget.',
`description` mediumtext COLLATE utf8_unicode_ci,
`deletable` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Can this widget be deleted?',
PRIMARY KEY (`id`),
KEY `fk_widget_user1_idx` (`user_id`),
KEY `fk_widget_widget_type1_idx` (`widget_type_handler`),
KEY `widgets_language_foreign` (`language`),
CONSTRAINT `fk_widget_user1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `widgets_language_foreign` FOREIGN KEY (`language`) REFERENCES `languages` (`code`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `widgets_widget_type_handler_foreign` FOREIGN KEY (`widget_type_handler`) REFERENCES `widget_types` (`handler`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 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.';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
INSERT INTO `languages` (`code`, `name`, `script`, `native`) VALUES
('cs', 'common.lang.czech', 'Latn', 'Cestina'),
('en', 'common.lang.english', 'Latn', 'English');
INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2014_10_12_100000_create_password_resets_table', 1),
('2016_01_28_213204_AddStringIdForActionTypes', 2),
('2016_02_08_105722_AddExtendedFileInfo', 3),
('2016_02_08_134354_AddFileSizeTofiles', 4),
('2016_02_08_234544_FixAutoIncrementForWidgetAreas', 5),
('2016_02_14_142507_AddIdToWidgetTypes', 6),
('2016_02_15_155126_AddHomepageMark', 7),
('2016_02_17_131804_AddTemplateSupport', 8),
('2016_02_18_100743_CreateTemplateDirectoriesTable', 9),
('2016_02_18_122645_RemoveModuleIdFromTemplates', 10),
('2016_02_18_132248_AddNameAndParentIdToTemplatePaths', 11),
('2016_02_18_141703_AddNamespaceToTemplatePaths', 12),
('2016_03_08_100656_AddModuleIdToTemplatesAgain', 13),
('2016_03_13_131109_AddFileFirectoriesSupport', 14),
('2016_03_13_225224_CreateSettingsTable', 15),
('2016_03_14_000228_ExtendModulesInfo', 16),
('2016_03_14_102343_AddModuleSettings', 17),
('2016_03_31_175822_AddSubpages', 18),
('2016_03_31_192238_ChangeContentTitleImageIdForFilename', 19),
('2016_03_31_214730_MakeUserIdNotMandatoryInDirectories', 20),
('2016_03_31_225203_AddNameForFiles', 21),
('2016_04_02_233316_AddIpAndUserAgentIntoLog', 22),
('2016_07_21_205626_MakeLangEnumerations', 23),
('2016_07_21_221547_AddFieldsIntoLanguages', 24),
('2016_07_22_095923_AddLanguageToContentPages', 25),
('2016_07_22_130324_AddLanguageIntoWidgets', 26),
('2016_07_22_145402_AddLanguageIntoSettings', 27),
('2016_07_28_192556_assignFilesToPages', 28),
('2016_08_01_211830_ChangeCollationOfTables', 29),
('2016_08_08_204055_AddFKToContenctAndTemplates', 30),
('2016_08_08_223700_MarkContentManagementModules', 31),
('2016_08_08_225613_MarkCategorizableModules', 31),
('2016_08_10_204045_create_categories_table', 32),
('2016_08_10_204046_create_category_closures_table', 32),
('2016_08_13_150322_AddCategorySupport', 33),
('2016_08_14_165211_MakeModuleHandlerNullable', 34),
('2016_08_15_183157_AddParentModuleIdToModules', 35),
('2016_09_05_085644_create_comments', 36),
('2016_09_19_192132_SaveTemplatesIntoModulesTable', 37),
('2016_09_19_225527_AddNumberOfViewsOfPage', 38),
('2016_09_20_113806_AddTemplateSupportToWidgets', 39),
('2016_09_20_114930_MakeWidgetsAndWidgetAreasNotDeletable', 40),
('2016_09_20_122700_AddTemplateIdIntoTemplates', 41);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-09-20 21:58:09
Reference in New Issue View Git Blame Copy Permalink