symfony_rbac_202011160954.sql

--
-- Script was generated by Devart dbForge Studio 2020 for MySQL, Version 9.0.338.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 11/16/2020 9:54:20 AM
-- Server version: 5.7.31
-- Client version: 4.1
--

-- 
-- Disable foreign keys
-- 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

-- 
-- Set SQL mode
-- 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- 
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE symfony-rbac;

--
-- Drop table `auth_assignment`
--
DROP TABLE IF EXISTS auth_assignment;

--
-- Drop table `auth_item_child`
--
DROP TABLE IF EXISTS auth_item_child;

--
-- Drop table `auth_item`
--
DROP TABLE IF EXISTS auth_item;

--
-- Drop table `auth_rule`
--
DROP TABLE IF EXISTS auth_rule;

--
-- Set default database
--
USE oa_rbac;

--
-- Create table `auth_rule`
--
CREATE TABLE auth_rule (
  name varchar(64) NOT NULL COMMENT 'Rule name',
  data text DEFAULT NULL COMMENT 'a serialized object instance of a class that implements the rbacrule interface',
  created_at int(11) DEFAULT NULL COMMENT 'Creation time',
  updated_at int(11) DEFAULT NULL COMMENT 'Update time',
  PRIMARY KEY (name)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8,
COLLATE utf8_general_ci,
COMMENT = 'rule table of Role(Permission)';

--
-- Create index `created_at` on table `auth_rule`
--
ALTER TABLE auth_rule
ADD INDEX created_at (created_at);

--
-- Create index `name` on table `auth_rule`
--
ALTER TABLE auth_rule
ADD INDEX name (name);

--
-- Create index `updated_at` on table `auth_rule`
--
ALTER TABLE auth_rule
ADD INDEX updated_at (updated_at);

--
-- Create table `auth_item`
--
CREATE TABLE auth_item (
  name varchar(64) NOT NULL COMMENT 'Role (Permission) unique identification',
  alias varchar(64) DEFAULT NULL COMMENT 'Role (permission) name',
  type int(11) NOT NULL COMMENT 'type:1 For Poles; 2 for Permissions',
  category int(11) DEFAULT NULL COMMENT 'classification',
  description text DEFAULT NULL COMMENT 'description',
  rule_name varchar(64) DEFAULT NULL COMMENT 'name of rule',
  data text DEFAULT NULL COMMENT 'extra data',
  status int(2) NOT NULL DEFAULT 1 COMMENT 'Enable 1 disable 0',
  created_at int(11) DEFAULT NULL COMMENT 'Creation time',
  updated_at int(11) DEFAULT NULL COMMENT 'Update time',
  PRIMARY KEY (name)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 585,
CHARACTER SET utf8,
COLLATE utf8_general_ci,
COMMENT = 'table of Role(Permission)';

--
-- Create index `type` on table `auth_item`
--
ALTER TABLE auth_item
ADD INDEX type (type);

--
-- Create foreign key
--
ALTER TABLE auth_item
ADD CONSTRAINT auth_item_ibfk_1 FOREIGN KEY (rule_name)
REFERENCES auth_rule (name) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Create table `auth_item_child`
--
CREATE TABLE auth_item_child (
  parent varchar(64) NOT NULL COMMENT 'name of Role(Permission)',
  child varchar(64) NOT NULL COMMENT 'name of Role(Permission)',
  PRIMARY KEY (parent, child)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 585,
CHARACTER SET utf8,
COLLATE utf8_general_ci,
COMMENT = 'relation table of Role(Permission)';

--
-- Create foreign key
--
ALTER TABLE auth_item_child
ADD CONSTRAINT auth_item_child_ibfk_1 FOREIGN KEY (parent)
REFERENCES auth_item (name) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Create foreign key
--
ALTER TABLE auth_item_child
ADD CONSTRAINT auth_item_child_ibfk_2 FOREIGN KEY (child)
REFERENCES auth_item (name) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Create table `auth_assignment`
--
CREATE TABLE auth_assignment (
  item_name varchar(64) NOT NULL COMMENT 'name of Role(Permission)',
  user_id varchar(64) NOT NULL COMMENT 'user’s id',
  created_at int(11) DEFAULT NULL COMMENT 'Creation time',
  PRIMARY KEY (item_name, user_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8,
COLLATE utf8_general_ci,
COMMENT = 'relation table of User-Role(Permission)';

--
-- Create foreign key
--
ALTER TABLE auth_assignment
ADD CONSTRAINT auth_assignment_ibfk_1 FOREIGN KEY (item_name)
REFERENCES auth_item (name) ON DELETE CASCADE ON UPDATE CASCADE;

-- 
-- Restore previous SQL mode
-- 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

-- 
-- Enable foreign keys
-- 
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;