SQL Storage

[graywolf336]

Get the SQL storage working.

The following is the sql to create the jails tables, if it doesn't exist.

CREATE TABLE IF NOT EXISTS `database`.`prefix_jails` (
  `name` VARCHAR(250) NOT NULL,
  `world` VARCHAR(250) NOT NULL COMMENT 'The world for the top, bottom, and teleport in.',
  `top.x` INT NOT NULL COMMENT 'The top coordinate x.',
  `top.y` INT NOT NULL COMMENT 'The top coordinate y.',
  `top.z` INT NOT NULL COMMENT 'The top coordinate z.',
  `bottom.x` INT NOT NULL COMMENT 'The bottom coordinate x.',
  `bottom.y` INT NOT NULL COMMENT 'The bottom coordinate y.',
  `bottom.z` INT NOT NULL COMMENT 'The bottom coordinate z.',
  `tps.in.x` DOUBLE NOT NULL COMMENT 'The teleport in x coordinate.',
  `tps.in.y` DOUBLE NOT NULL COMMENT 'The teleport in y coordinate.',
  `tps.in.z` DOUBLE NOT NULL COMMENT 'The teleport in z coordinate.',
  `tps.in.yaw` DOUBLE NOT NULL COMMENT 'The teleport in yaw.',
  `tps.in.pitch` DOUBLE NOT NULL COMMENT 'The teleport in pitch.',
  `tps.free.world` VARCHAR(250) NOT NULL COMMENT 'The teleport for being free world.',
  `tps.free.x` DOUBLE NOT NULL COMMENT 'The teleport for being free x coordinate.',
  `tps.free.y` DOUBLE NOT NULL COMMENT 'The teleport for being free y coordinate.',
  `tps.free.z` DOUBLE NOT NULL COMMENT 'The teleport for being free z coordinate.',
  `tps.free.yaw` DOUBLE NOT NULL COMMENT 'The teleport for being free yaw.',
  `tps.free.pitch` DOUBLE NOT NULL COMMENT 'The teleport for being free pitch.',
  PRIMARY KEY (`name`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
COMMENT = 'Holds all the jails for the Bukkit Jail plugin.';

The following is the sql to create the cells table, if it doesn't exist.

CREATE TABLE IF NOT EXISTS `database`.`prefix_cells` (
  `cellid` INT NOT NULL AUTO_INCREMENT COMMENT 'The cellid for the database.',
  `name` VARCHAR(250) NOT NULL COMMENT 'The name of the cell.',
  `jail` VARCHAR(250) NOT NULL COMMENT 'The name of the jail the cell is in.',
  `tp.x` DOUBLE NOT NULL COMMENT 'The teleport in x coordinate.',
  `tp.y` DOUBLE NOT NULL COMMENT 'The teleport in y coordinate.',
  `tp.z` DOUBLE NOT NULL COMMENT 'The teleport in z coordinate.',
  `tp.yaw` DOUBLE NOT NULL COMMENT 'The teleport in yaw.',
  `tp.pitch` DOUBLE NOT NULL COMMENT 'The teleport in pitch.',
  `chest.x` INT NULL COMMENT 'The chest x coordinate.',
  `chest.y` INT NULL COMMENT 'The chest y coordinate.',
  `chest.z` INT NULL COMMENT 'The chest z coordinate.',
  `signs` VARCHAR(250) NULL COMMENT 'A string containing the signs.',
  PRIMARY KEY (`cellid`),
  UNIQUE INDEX `cellid_UNIQUE` (`cellid` ASC))
COMMENT = 'Contains all the cells for the jails.';

The following is the sql to create the prisoner table, if it doesn't exist.

CREATE TABLE IF NOT EXISTS `database`.`prefix_prisoners` (
  `name` VARCHAR(16) NOT NULL COMMENT 'The name of the prisoner.',
  `jail` VARCHAR(250) NOT NULL COMMENT 'The jail the prisoner is in.',
  `cell` VARCHAR(250) NULL COMMENT 'The cell the prisoner is in.',
  `muted` TINYINT NOT NULL COMMENT 'Whether the player is muted or not.',
  `time` INT NOT NULL COMMENT 'The remaining time the prisoner has.',
  `offlinePending` TINYINT NOT NULL COMMENT 'Whether the prisoner has something happened to them while they were offline.',
  `toBeTransferred` TINYINT NOT NULL COMMENT 'Whether the prisoner is to be transferred.',
  `jailer` VARCHAR(250) NOT NULL COMMENT 'The name of the person who jailed them.',
  `reason` VARCHAR(250) NOT NULL COMMENT 'The reason they are jailed.',
  `inventory` BLOB NULL COMMENT 'Their inventory in base64.',
  `armor` BLOB NULL COMMENT 'The armor in base64.',
  `previousLocation` VARCHAR(250) NULL COMMENT 'A string of their previous location.',
  `previousGameMode` VARCHAR(16) NULL COMMENT 'Their previous gamemode before they were jailed.',
  PRIMARY KEY (`name`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
COMMENT = 'Contains all the prisoners, in cells and jails.';

The following is the sql to create the profile table, if it doesn't exist.

CREATE TABLE IF NOT EXISTS `database`.`prefix_profiles` (
  `profileid` INT NOT NULL AUTO_INCREMENT COMMENT 'Auto generated number for the profiles database.',
  `username` VARCHAR(16) NOT NULL COMMENT 'The username of the prisoner.',
  `jailer` VARCHAR(250) NOT NULL COMMENT 'The name of the person who jailed the prisoner.',
  `date` VARCHAR(32) NOT NULL COMMENT 'A string of the date.',
  `time` INT NOT NULL COMMENT 'The milliseconds they were jailed for.',
  `reason` VARCHAR(250) NOT NULL COMMENT 'The reason they were jailed for.',
  PRIMARY KEY (`profileid`),
  UNIQUE INDEX `profileid_UNIQUE` (`profileid` ASC))
COMMENT = 'Holds a history of all the times prisoners have been jailed.';