# Host: localhost  (Version: 5.6.21)
# Date: 2015-07-03 11:17:44
# Generator: MySQL-Front 5.3  (Build 4.121)

/*!40101 SET NAMES utf8 */;

#
# Structure for table "privilege"
#

DROP TABLE IF EXISTS `privilege`;
CREATE TABLE `privilege` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `resource` varchar(50) NOT NULL DEFAULT '',
  `url` varchar(150) NOT NULL DEFAULT '',
  `is_display` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `Privilege_parent` (`parent_id`),
  CONSTRAINT `Privilege_parent` FOREIGN KEY (`parent_id`) REFERENCES `privilege` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;

#
# Data for table "privilege"
#

INSERT INTO `privilege` VALUES (27,'权限管理',NULL,NULL,'admin:user.list','/admin/*',b'1'),(28,'用户管理',NULL,27,'admin:user.*','/admin/user/*',b'1'),(29,'角色管理',NULL,27,'admin:role.*','/admin/role/*',b'1'),(32,'菜单管理',NULL,27,'admin:privilege.*','/admin/privilege/*',b'1'),(33,'文章管理',NULL,NULL,'','',b'0'),(34,'删除文章',NULL,33,'article:*','article/*',b'1'),(36,'添加用户',NULL,28,'admin:user.add','/admin/user/add',b'1'),(37,'删除用户',NULL,28,'admin:user.delte','/admin/user/delete',b'1'),(38,'更新用户',NULL,28,'admin:user.update','/admin/user/update',b'1'),(39,'添加角色',NULL,29,'admin:role.add','/admin/role/add',b'1'),(40,'删除角色',NULL,29,'admin:role.delete','/admin/role/delete',b'1'),(41,'更新角色',NULL,29,'admin:role.update','/admin/role/update',b'1'),(42,'查询用户',NULL,28,'admin:user.list','/admin/user/list',b'1'),(43,'查询角色',NULL,29,'admin:role.list','/admin/role/list',b'1'),(44,'根据ID查找用户',NULL,28,'admin:user.unique','/admin/user/unique',b'1'),(45,'查询权限',NULL,32,'admin:privilege.list','/admin/privilege/list',b'1'),(46,'添加权限',NULL,32,'admin:privilege.add','/admin/privilege/add',b'1'),(47,'删除权限',NULL,32,'admin:privilege.delete','/admin/privilege/delete',b'1');

#
# Structure for table "role"
#

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

#
# Data for table "role"
#

INSERT INTO `role` VALUES (32,'超级管理员','超管后台~~~'),(33,'文章管理员','管理系统文章~~~');

#
# Structure for table "role_privilege"
#

DROP TABLE IF EXISTS `role_privilege`;
CREATE TABLE `role_privilege` (
  `role_id` bigint(20) NOT NULL DEFAULT '0',
  `privilege_id` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`role_id`,`privilege_id`),
  KEY `RP_Privilege` (`privilege_id`),
  CONSTRAINT `RP_Privilege` FOREIGN KEY (`privilege_id`) REFERENCES `privilege` (`id`),
  CONSTRAINT `RP_Role` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table "role_privilege"
#

INSERT INTO `role_privilege` VALUES (32,27),(32,28),(32,29),(32,32),(32,36),(32,37),(32,39),(32,40),(32,42),(32,43),(32,44),(32,45),(32,46),(32,47);

#
# Structure for table "user"
#

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `password` varchar(200) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `image` varchar(200) DEFAULT NULL,
  `is_admin` bit(1) NOT NULL,
  `create_at` double NOT NULL,
  `telephone` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8;

#
# Data for table "user"
#

INSERT INTO `user` VALUES (47,'Mxxim','e10adc3949ba59abbe56e057f20f883e','376877431@qq.com','http://t11.baidu.com/it/u=2252020396,3421242360&fm=56',b'1',1435737984.27799,'13417139666'),(48,'mcc','e10adc3949ba59abbe56e057f20f883e','213454@Gmail.com','http://t11.baidu.com/it/u=2252020396,3421242360&fm=56',b'0',1435737984.27799,'123456');

#
# Structure for table "blog"
#

DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `content` longtext,
  `create_at` double NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Blog_Owner_is` (`user_id`),
  CONSTRAINT `Blog_Owner_is` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table "blog"
#


#
# Structure for table "comment"
#

DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `content` longtext,
  `user_id` bigint(20) NOT NULL,
  `blog_id` bigint(20) NOT NULL,
  `create_at` double NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Comment_Where_is` (`blog_id`),
  KEY `Comment_Owner_is` (`user_id`),
  CONSTRAINT `Comment_Owner_is` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Comment_Where_is` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table "comment"
#


#
# Structure for table "user_role"
#

DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `role_id` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `UR_Role` (`role_id`),
  CONSTRAINT `UR_Role` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
  CONSTRAINT `UR_User` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table "user_role"
#

INSERT INTO `user_role` VALUES (47,32),(48,32),(48,33);

DROP VIEW IF EXISTS `user_role_privilege`;
CREATE VIEW `user_role_privilege` AS 
  (select `u`.`id` AS `user_id`,`u`.`name` AS `user_name`,`r`.`id` AS `role_id`,`r`.`name` AS `role_name`,`p`.`id` AS `privilege_id`,`p`.`name` AS `privilege_name`,`p`.`parent_id` AS `privilege_parent_id`,`p`.`resource` AS `privilege_resource`,`p`.`url` AS `privilege_url`,`p`.`is_display` AS `privilege_is_display` from ((((`webblog`.`user` `u` join `webblog`.`user_role` `ur` on((`ur`.`user_id` = `u`.`id`))) join `webblog`.`role` `r` on((`r`.`id` = `ur`.`role_id`))) join `webblog`.`role_privilege` `rp` on((`r`.`id` = `rp`.`role_id`))) join `webblog`.`privilege` `p` on((`p`.`id` = `rp`.`privilege_id`))));