SQLize Online / PHPize Online  /  SQLtest Online

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; CREATE TABLE IF NOT EXISTS `file` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `link` VARCHAR(255) NOT NULL, `type` VARCHAR(50) NOT NULL ); CREATE TABLE IF NOT EXISTS `dc_language` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `code` VARCHAR(10) NOT NULL, `locale` VARCHAR(10) NOT NULL, `direction` ENUM ('LTR', 'RTL') DEFAULT 'LTR', UNIQUE KEY `unique_code` (`code`), UNIQUE KEY `unique_locale` (`locale`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS `dc_phrases` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `title` VARCHAR(255) NOT NULL, `content` TEXT NOT NULL, `code` VARCHAR(10) NOT NULL, FOREIGN KEY (`code`) REFERENCES `dc_language` (`code`) ON DELETE CASCADE, UNIQUE KEY `unique_dc_phrase` (`title`, `code`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; INSERT IGNORE INTO dc_language (name, code, locale, direction) VALUES ('English', 'en', 'en_US', 'LTR'), ('Ukrainian', 'uk', 'uk_UA', 'LTR'); INSERT IGNORE INTO dc_phrases (title, content, code) VALUES -- English phrases ('add', 'Add', 'en'), ('remove', 'Remove', 'en'), ('update', 'Update', 'en'), ('cancel', 'Cancel', 'en'), ('save', 'Save', 'en'), -- Ukrainian phrases ('add', 'Додати', 'uk'), ('remove', 'Видалити', 'uk'), ('update', 'Оновити', 'uk'), ('cancel', 'Скасувати', 'uk'), ('save', 'Зберегти', 'uk'); CREATE TABLE IF NOT EXISTS `menu` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL ); CREATE TABLE IF NOT EXISTS `menu_item` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `menu_id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `parent` INT DEFAULT 0, `position` INT DEFAULT 0, `link` VARCHAR(255) NOT NULL, FOREIGN KEY (`menu_id`) REFERENCES `menu` (`id`) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS `dc_modules` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `version` VARCHAR(20) NOT NULL, `is_active` BOOLEAN DEFAULT FALSE, `is_install` BOOLEAN DEFAULT FALSE, `vendor` VARCHAR(255) NOT NULL, `main_controller` VARCHAR(255) NOT NULL ); CREATE TABLE IF NOT EXISTS `dc_settings` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `param` VARCHAR(255) NOT NULL, `value` TEXT ); CREATE TABLE IF NOT EXISTS `dc_user` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `login` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `hash` VARCHAR(255) DEFAULT NULL ); CREATE TABLE IF NOT EXISTS `dc_roles` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL UNIQUE, `permissions` TEXT NOT NULL ); INSERT INTO `dc_roles` (`name`, `permissions`) VALUES ('Unregistered / unconfirmed', 'view_content'), ('Registered', 'view_content,comment'), ( 'Moderating', 'view_content,comment,edit_content,delete_content' ), ( 'Administrative', 'view_content,comment,edit_content,delete_content,manage_settings' ); ALTER TABLE `dc_user` ADD COLUMN `role_id` INT, ADD CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `dc_roles` (`id`); CREATE TABLE IF NOT EXISTS `dc_permissions` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL UNIQUE ); INSERT INTO `dc_permissions` (`name`) VALUES ('view_content'), ('comment'), ('edit_content'), ('delete_content'), ('manage_settings'); CREATE TABLE IF NOT EXISTS `dc_role_permissions` ( `role_id` INT, `permission_id` INT, PRIMARY KEY (`role_id`, `permission_id`), FOREIGN KEY (`role_id`) REFERENCES `dc_roles` (`id`), FOREIGN KEY (`permission_id`) REFERENCES `dc_permissions` (`id`) ); COMMIT;