DCLaravelBase/tableSchema.sql

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 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' */;

-- 創建資料庫
CREATE DATABASE sampleProject;
USE sampleProject;

-- 導出  表 sampleProject.functions 結構
CREATE TABLE IF NOT EXISTS `functions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FunName` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `FunLink` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `FunDesc` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Valid` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `Oid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 正在導出表  sampleProject.functions 的資料：~0 rows (大約)
/*!40000 ALTER TABLE `functions` DISABLE KEYS */;
INSERT INTO `functions` (`id`, `FunName`, `FunLink`, `FunDesc`, `Valid`, `created_at`, `updated_at`, `Oid`) VALUES
	(1, 'Change Password', 'Password', 'Change Password', 1, '2016-02-23 16:18:15', '2016-02-23 16:18:16', 0),
	(2, 'Manage Groups', 'Usergroups', 'Manage Groups', 1, '2016-02-23 16:18:37', '2016-02-23 16:18:38', 0),
	(3, 'Manage Users', 'Users', 'Manage Users', 1, '2016-02-23 16:18:58', '2016-02-23 16:18:59', 0),
	(5, 'Manage Functions', 'Funmenus', 'Manage Functions', 1, '2016-03-08 03:38:32', '2016-03-08 07:10:51', 1);
/*!40000 ALTER TABLE `functions` ENABLE KEYS */;

-- 導出  表 sampleProject.funmenu 結構
CREATE TABLE IF NOT EXISTS `funmenu` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `MenuName` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Valid` int(11) NOT NULL,
  `Corder` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `Oid` int(11) NOT NULL,
  `icon` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 正在導出表  sampleProject.funmenu 的資料：~0 rows (大約)
/*!40000 ALTER TABLE `funmenu` DISABLE KEYS */;
INSERT INTO `funmenu` (`id`, `MenuName`, `Valid`, `Corder`, `created_at`, `updated_at`, `Oid`, `icon`) VALUES
	(1, 'System Management', 1, 1, '2016-02-23 16:19:34', '2016-03-08 07:16:40', 1, 'fa fa-cog');
/*!40000 ALTER TABLE `funmenu` ENABLE KEYS */;

-- 導出  表 sampleProject.funmenudetail 結構
CREATE TABLE IF NOT EXISTS `funmenudetail` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FunMenuId` int(11) NOT NULL,
  `FunId` int(11) NOT NULL,
  `Valid` int(11) NOT NULL,
  `Corder` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `Oid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 正在導出表  sampleProject.funmenudetail 的資料：~0 rows (大約)
/*!40000 ALTER TABLE `funmenudetail` DISABLE KEYS */;
INSERT INTO `funmenudetail` (`id`, `FunMenuId`, `FunId`, `Valid`, `Corder`, `created_at`, `updated_at`, `Oid`) VALUES
	(1, 1, 1, 1, 10, '2016-02-23 16:20:00', '2016-02-23 16:20:01', 0),
	(2, 1, 2, 1, 20, '2016-02-23 16:20:10', '2016-02-23 16:20:11', 0),
	(3, 1, 3, 1, 30, '2016-02-23 16:20:21', '2016-02-23 16:20:22', 0),
	(6, 1, 5, 1, 40, '2016-03-08 03:38:32', '2016-03-08 07:10:51', 1);
/*!40000 ALTER TABLE `funmenudetail` ENABLE KEYS */;

-- 導出  表 sampleProject.funusergroups 結構
CREATE TABLE IF NOT EXISTS `funusergroups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `FunList` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UsrList` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Valid` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `Oid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 正在導出表  sampleProject.funusergroups 的資料：~0 rows (大約)
/*!40000 ALTER TABLE `funusergroups` DISABLE KEYS */;
INSERT INTO `funusergroups` (`id`, `Name`, `FunList`, `UsrList`, `Valid`, `created_at`, `updated_at`, `Oid`) VALUES
	(1, '管理群組', '1,2,3,5,', '1,', 1, '2017-03-20 15:38:06', '2017-03-20 15:38:07', 0);
/*!40000 ALTER TABLE `funusergroups` ENABLE KEYS */;

-- 導出  表 sampleProject.password_resets 結構
CREATE TABLE IF NOT EXISTS `password_resets` (
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  KEY `password_resets_email_index` (`email`),
  KEY `password_resets_token_index` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 正在導出表  sampleProject.password_resets 的資料：~0 rows (大約)
/*!40000 ALTER TABLE `password_resets` DISABLE KEYS */;
/*!40000 ALTER TABLE `password_resets` ENABLE KEYS */;

-- 導出  表 sampleProject.users 結構
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 正在導出表  sampleProject.users 的資料：~1 rows (大約)
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` (`id`, `name`, `email`, `password`, `remember_token`, `created_at`, `updated_at`) VALUES
	(1, 'admin', 'admin@example.com', '$2y$10$hqcHCoANcpUwgZ.4cSLEMO7NMOrxgOAp9cofFTPer17WYyZvTQXy2', 'ojdNzeonOoPRtOATx4JFDii8ODgSuIzTq2EsKJGsg57gScHs5Ksh9FmTJOyv', '2017-03-20 07:25:52', '2017-03-20 07:25:52');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;

-- 導出  視圖 sampleProject.v_leftmenu 結構
-- 創建臨時表以解決視圖依賴性錯誤
CREATE TABLE `v_leftmenu` (
	`icon` VARCHAR(100) NULL COLLATE 'utf8mb4_unicode_ci',
	`MenuName` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`FunMenuCorder` INT(11) NOT NULL,
	`FunMenuId` INT(11) NOT NULL,
	`FunId` INT(11) NOT NULL,
	`FunName` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`FunLink` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`FunDesc` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`Valid` INT(11) NOT NULL,
	`created_at` TIMESTAMP NULL,
	`updated_at` TIMESTAMP NULL,
	`Oid` INT(11) NOT NULL,
	`FunMenuDetailCOrder` INT(11) NOT NULL
) ENGINE=MyISAM;

-- 導出  視圖 sampleProject.v_leftmenu 結構
-- 移除臨時表並創建最終視圖結構
DROP TABLE IF EXISTS `v_leftmenu`;
CREATE VIEW `v_leftmenu` AS select `funmenu`.`icon` AS `icon`,`funmenu`.`MenuName` AS `MenuName`,`funmenu`.`Corder` AS `FunMenuCorder`,`funmenudetail`.`FunMenuId` AS `FunMenuId`,`funmenudetail`.`FunId` AS `FunId`,`functions`.`FunName` AS `FunName`,`functions`.`FunLink` AS `FunLink`,`functions`.`FunDesc` AS `FunDesc`,`functions`.`Valid` AS `Valid`,`functions`.`created_at` AS `created_at`,`functions`.`updated_at` AS `updated_at`,`functions`.`Oid` AS `Oid`,`funmenudetail`.`Corder` AS `FunMenuDetailCOrder` from ((`funmenu` join `funmenudetail` on((`funmenu`.`id` = `funmenudetail`.`FunMenuId`))) join `functions` on((`funmenudetail`.`FunId` = `functions`.`id`))) where `functions`.`id` in (select `Functions_1`.`id` from `functions` `Functions_1` where (locate((convert(concat(',',`Functions_1`.`id`,',') using utf8) collate utf8_unicode_ci),convert(concat(',',(select `funusergroups`.`FunList` from `funusergroups` where (`funusergroups`.`id` = 1)),',') using utf8)) > 0)) order by `funmenu`.`Corder`,`funmenudetail`.`Corder` limit 0,1000;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;