dynamic loaded Zend ACL db model

gistfile1.sql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`users` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`users` (
  `u_id` INT NOT NULL AUTO_INCREMENT ,
  `u_username` VARCHAR(127) NOT NULL ,
  `u_password` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`u_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`roles`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`roles` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`roles` (
  `r_id` INT NOT NULL AUTO_INCREMENT ,
  `r_name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`r_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`role_inheritance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`role_inheritance` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`role_inheritance` (
  `role_id` INT NOT NULL ,
  `parent_role_id` INT NOT NULL ,
  PRIMARY KEY (`role_id`, `parent_role_id`) ,
  INDEX `role` (`role_id` ASC) ,
  INDEX `parent_role` (`parent_role_id` ASC) ,
  CONSTRAINT `role`
    FOREIGN KEY (`role_id` )
    REFERENCES `mydb`.`roles` (`r_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `parent_role`
    FOREIGN KEY (`parent_role_id` )
    REFERENCES `mydb`.`roles` (`r_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`users_roles`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`users_roles` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`users_roles` (
  `user_id` INT NOT NULL ,
  `role_id` INT NOT NULL ,
  PRIMARY KEY (`user_id`, `role_id`) ,
  INDEX `users` (`user_id` ASC) ,
  INDEX `roles` (`role_id` ASC) ,
  CONSTRAINT `users`
    FOREIGN KEY (`user_id` )
    REFERENCES `mydb`.`users` (`u_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `roles`
    FOREIGN KEY (`role_id` )
    REFERENCES `mydb`.`roles` (`r_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`resources`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`resources` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`resources` (
  `r_id` VARCHAR(127) NOT NULL ,
  `parent_resource` VARCHAR(127) NULL ,
  PRIMARY KEY (`r_id`) ,
  INDEX `r_parent` (`parent_resource` ASC) ,
  CONSTRAINT `r_parent`
    FOREIGN KEY (`parent_resource` )
    REFERENCES `mydb`.`resources` (`r_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`role_resource_privilege`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`role_resource_privilege` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`role_resource_privilege` (
  `role_id` INT NOT NULL ,
  `resource_id` VARCHAR(127) NOT NULL ,
  `privilege` VARCHAR(127) NOT NULL ,
  PRIMARY KEY (`role_id`, `resource_id`, `privilege`) ,
  INDEX `p_roles` (`role_id` ASC) ,
  INDEX `p_resources` (`resource_id` ASC) ,
  CONSTRAINT `p_roles`
    FOREIGN KEY (`role_id` )
    REFERENCES `mydb`.`roles` (`r_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `p_resources`
    FOREIGN KEY (`resource_id` )
    REFERENCES `mydb`.`resources` (`r_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`user_resource_privilege`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`user_resource_privilege` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`user_resource_privilege` (
  `user_id` INT NOT NULL ,
  `resource_id` VARCHAR(127) NOT NULL ,
  `privilege` VARCHAR(127) NOT NULL ,
  PRIMARY KEY (`user_id`, `resource_id`, `privilege`) ,
  INDEX `u_users` (`user_id` ASC) ,
  INDEX `u_resources` (`resource_id` ASC) ,
  CONSTRAINT `u_users`
    FOREIGN KEY (`user_id` )
    REFERENCES `mydb`.`users` (`u_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `u_resources`
    FOREIGN KEY (`resource_id` )
    REFERENCES `mydb`.`resources` (`r_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`users`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`users` (`u_id`, `u_username`, `u_password`) VALUES (1, 'admin', 'admin');
INSERT INTO `mydb`.`users` (`u_id`, `u_username`, `u_password`) VALUES (2, 'tester', 'tester');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`roles`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (1, 'guest');
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (2, 'tester');
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (3, 'manager');
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (4, 'admin');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`role_inheritance`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`role_inheritance` (`role_id`, `parent_role_id`) VALUES (2, 1);
INSERT INTO `mydb`.`role_inheritance` (`role_id`, `parent_role_id`) VALUES (3, 2);
INSERT INTO `mydb`.`role_inheritance` (`role_id`, `parent_role_id`) VALUES (4, 3);

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`users_roles`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`users_roles` (`user_id`, `role_id`) VALUES (1, 4);
INSERT INTO `mydb`.`users_roles` (`user_id`, `role_id`) VALUES (2, 2);

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`resources`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('default', NULL);
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('default::login', 'default');
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('default::logout', 'default');
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('special', NULL);

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`role_resource_privilege`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`role_resource_privilege` (`role_id`, `resource_id`, `privilege`) VALUES (1, 'default::login', 'ALL');
INSERT INTO `mydb`.`role_resource_privilege` (`role_id`, `resource_id`, `privilege`) VALUES (2, 'default::logout', 'ALL');
INSERT INTO `mydb`.`role_resource_privilege` (`role_id`, `resource_id`, `privilege`) VALUES (4, 'default', 'ALL');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`user_resource_privilege`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`user_resource_privilege` (`user_id`, `resource_id`, `privilege`) VALUES (1, 'special', 'SPECIAL');

COMMIT;