-- -----------------------------------------------------
-- Table `user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NOT NULL UNIQUE,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `group` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NOT NULL UNIQUE,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `world`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `world` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(128) NOT NULL UNIQUE,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `region`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region` (
`id` VARCHAR(128) NOT NULL ,
`world_id` INT UNSIGNED NOT NULL ,
`type` ENUM('cuboid','poly2d','global') NOT NULL ,
`priority` SMALLINT NOT NULL DEFAULT 0 ,
`parent` VARCHAR(128) NULL ,
PRIMARY KEY (`id`, `world_id`) ,
INDEX `parent` (`parent` ASC) ,
INDEX `fk_region_world` (`world_id` ASC) ,
CONSTRAINT `parent`
FOREIGN KEY (`parent` )
REFERENCES `region` (`id` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_region_world1`
FOREIGN KEY (`world_id` )
REFERENCES `world` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `region_flag`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region_flag` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`region_id` VARCHAR(128) NOT NULL ,
`flag` VARCHAR(64) NOT NULL ,
`value` VARCHAR(256) NOT NULL ,
INDEX `fk_flags_region` (`region_id` ASC) ,
PRIMARY KEY (`id`) ,
CONSTRAINT `fk_flags_region1`
FOREIGN KEY (`region_id` )
REFERENCES `region` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `region_cuboid`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region_cuboid` (
`region_id` VARCHAR(128) NOT NULL ,
`min_z` BIGINT NOT NULL ,
`min_y` BIGINT NOT NULL ,
`min_x` BIGINT NOT NULL ,
`max_z` BIGINT NOT NULL ,
`max_y` BIGINT NOT NULL ,
`max_x` BIGINT NOT NULL ,
PRIMARY KEY (`region_id`) ,
INDEX `fk_region_cuboid_region` (`region_id` ASC) ,
CONSTRAINT `fk_region_cuboid_region`
FOREIGN KEY (`region_id` )
REFERENCES `region` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `region_poly2d`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region_poly2d` (
`region_id` VARCHAR(128) NOT NULL ,
`max_y` INT NOT NULL ,
`min_y` INT NOT NULL ,
PRIMARY KEY (`region_id`) ,
INDEX `fk_region_poly2d_region` (`region_id` ASC) ,
CONSTRAINT `fk_region_poly2d_region`
FOREIGN KEY (`region_id` )
REFERENCES `region` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `region_players`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region_players` (
`region_id` VARCHAR(128) NOT NULL ,
`user_id` INT UNSIGNED NOT NULL ,
`owner` TINYINT(1) NOT NULL ,
PRIMARY KEY (`region_id`, `user_id`) ,
INDEX `fk_region_players_region` (`region_id` ASC) ,
INDEX `fk_region_players_user` (`user_id` ASC) ,
CONSTRAINT `fk_region_players_region`
FOREIGN KEY (`region_id` )
REFERENCES `region` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_region_players_user`
FOREIGN KEY (`user_id` )
REFERENCES `user` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `region_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region_groups` (
`region_id` VARCHAR(128) NOT NULL ,
`group_id` INT UNSIGNED NOT NULL ,
`owner` TINYINT(1) NOT NULL ,
PRIMARY KEY (`region_id`, `group_id`) ,
INDEX `fk_region_groups_region` (`region_id` ASC) ,
INDEX `fk_region_groups_group` (`group_id` ASC) ,
CONSTRAINT `fk_region_groups_region`
FOREIGN KEY (`region_id` )
REFERENCES `region` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_region_groups_group`
FOREIGN KEY (`group_id` )
REFERENCES `group` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `region_poly2d_point`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region_poly2d_point` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`region_id` VARCHAR(128) NOT NULL ,
`z` BIGINT NOT NULL ,
`x` BIGINT NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_region_poly2d_point_region_poly2d` (`region_id` ASC) ,
CONSTRAINT `fk_region_poly2d_point_region_poly2d`
FOREIGN KEY (`region_id` )
REFERENCES `region_poly2d` (`region_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
эксперементируй