rbac-2011.sql

/*
SQLyog Community v8.3 
MySQL - 5.1.36-community-log : Database - x5
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `application` */

CREATE TABLE `application` (
  `application_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `description` text CHARACTER SET latin1,
  PRIMARY KEY (`application_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Application / Modules.';

/*Table structure for table `permission` */

CREATE TABLE `permission` (
  `permission_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `application_id` int(10) unsigned NOT NULL,
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Permission with in an Application - Read, Write, etc.';

/*Table structure for table `role` */

CREATE TABLE `role` (
  `role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `description` text CHARACTER SET latin1,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci COMMENT='Roles / Groups';

/*Table structure for table `role_permission` */

CREATE TABLE `role_permission` (
  `role_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Role permissions to applications - True only.';

/*Table structure for table `user` */

CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET latin1 NOT NULL,
  `password` char(128) CHARACTER SET latin1 NOT NULL,
  `email` varchar(255) CHARACTER SET latin1 NOT NULL,
  `twitter` bigint(20) unsigned DEFAULT NULL,
  `facebook` bigint(20) unsigned DEFAULT NULL,
  `google` bigint(20) unsigned DEFAULT NULL,
  `msn` bigint(20) unsigned DEFAULT NULL,
  `aol` bigint(20) unsigned DEFAULT NULL,
  `openid` bigint(20) unsigned DEFAULT NULL,
  `registered` int(10) unsigned NOT NULL DEFAULT '0',
  `last_login` int(10) unsigned NOT NULL DEFAULT '0',
  `hash1` char(16) CHARACTER SET latin1 NOT NULL,
  `confirmed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User''s table - Login information.';

/*Table structure for table `user_password_reset` */

CREATE TABLE `user_password_reset` (
  `reset_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `date_time` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`reset_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Password resets for user - system generated.';

/*Table structure for table `user_permission` */

CREATE TABLE `user_permission` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `permission_id` int(10) unsigned NOT NULL,
  `access` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`,`permission_id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Permissions to applications - True/False';

/*Table structure for table `user_permission_compiled` */

CREATE TABLE `user_permission_compiled` (
  `user_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User permissions to applications - quick look up - true only';

/*Table structure for table `user_profile` */

CREATE TABLE `user_profile` (
  `user_id` int(10) unsigned NOT NULL,
  `gender` enum('m','f','u') CHARACTER SET latin1 DEFAULT NULL,
  `prefix` enum('Mr.','Mrs.','Ms.','Prof.','Ph.D.','Capt.','Lt.','D.D.S.','Dr.','Rev.') CHARACTER SET latin1 DEFAULT NULL,
  `first_name` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `middle_name` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `last_name` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `suffix` enum('Jr.','Sr.','I','III','IV','V','Ph. D.','Dr.','Md.','D.D.S.','D.V.M.','Jd.') CHARACTER SET latin1 DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `aim` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `icq` bigint(20) DEFAULT NULL,
  `yim` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `msn` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User''s profile';

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;