-- -----------------------------------------------------
-- Table `auser_db_test`.`usergroups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auser_db_test`.`usergroups` (
  `userID` INT UNSIGNED NOT NULL ,
  `groupID` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`userID`, `groupID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `auser_db_test`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auser_db_test`.`users` (
  `userID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `parentID` INT UNSIGNED NOT NULL ,
  `login` VARCHAR(200) NOT NULL ,
  `passwd` VARCHAR(200) NOT NULL ,
  `admin` TINYINT(1)  NOT NULL DEFAULT 0 ,
  `backend` TINYINT(1)  NOT NULL DEFAULT 0 ,
  `disabled` TINYINT(1)  NOT NULL DEFAULT 0 ,
  `permsreload` TINYINT(1)  NOT NULL DEFAULT 1 ,
  PRIMARY KEY (`userID`) ,
  UNIQUE INDEX `login_UNIQUE` (`login` ASC) ,
  INDEX `usergroups.userID` (`userID` ASC) ,
  INDEX `users.parentID` (`parentID` ASC) ,
  CONSTRAINT `usergroups.userID`
    FOREIGN KEY (`userID` )
    REFERENCES `auser_db_test`.`usergroups` (`userID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `users.parentID`
    FOREIGN KEY (`parentID` )
    REFERENCES `auser_db_test`.`users` (`userID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `auser_db_test`.`groups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `auser_db_test`.`groups` (
  `groupID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `parentID` INT UNSIGNED NULL ,
  `name` VARCHAR(200) NOT NULL ,
  PRIMARY KEY (`groupID`) ,
  INDEX `groups.parentID` (`parentID` ASC) ,
  INDEX `usergroups.groupID` (`groupID` ASC) ,
  CONSTRAINT `groups.parentID`
    FOREIGN KEY (`parentID` )
    REFERENCES `auser_db_test`.`groups` (`groupID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `usergroups.groupID`
    FOREIGN KEY (`groupID` )
    REFERENCES `auser_db_test`.`usergroups` (`groupID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `auser_db_test`.`groups` (
  `groupID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `parentID` INT UNSIGNED NULL ,
  `name` VARCHAR(200) NOT NULL ,
  PRIMARY KEY (`groupID`) ,
  INDEX `groups.parentID` (`parentID` ASC) ,
  INDEX `usergroups.userID` (`groupID` ASC) ,
  INDEX `usergroups.groupID` (`groupID` ASC) ,
  CONSTRAINT `groups.parentID`
    FOREIGN KEY (`parentID` )
    REFERENCES `auser_db_test`.`groups` (`groupID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `usergroups.groupID`
    FOREIGN KEY (`groupID` )
    REFERENCES `auser_db_test`.`usergroups` (`groupID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;