road_defect.sql

Java的框架

USE PRRM;

## ----------------- road defect  ------------------------------------------------
CREATE TABLE IF NOT EXISTS `ROADDEFECT` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `DEFECTTYPE` VARCHAR(25) NOT NULL DEFAULT '',
  `DEFECTDETAIL` VARCHAR(250) NOT NULL DEFAULT '',
  `ROADID` BIGINT(20) NOT NULL DEFAULT '0',
  `LOCATION` VARCHAR(250) NOT NULL DEFAULT '',
  `SEVERITY` VARCHAR(25) NOT NULL DEFAULT '',
  `CREATIONTIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `CLOSEDDATE` DATETIME DEFAULT '0000-00-00 00:00:00',
  `LASTMODIFIEDTIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `STATUS` VARCHAR(25) NOT NULL DEFAULT '',
  `CONTACTID` BIGINT(20) NOT NULL DEFAULT '1',
  `COORDINATES` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY  (`ID`)
) ENGINE=INNODB;

## ----------------- contact  ------------------------------------------------
CREATE TABLE IF NOT EXISTS `CONTACT` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `BLOCK` VARCHAR(8) NOT NULL DEFAULT 'NIL',
  `COUNTRY` VARCHAR(50) NOT NULL DEFAULT 'NIL',
  `EMAIL` VARCHAR(30) NOT NULL DEFAULT 'NIL',
  `FIRSTNAME` VARCHAR(20) NOT NULL DEFAULT '',
  `FLOOR` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `LASTMODIFIEDTIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LASTNAME` VARCHAR(20) NOT NULL DEFAULT '',
  `POSTCODE` VARCHAR(10) NOT NULL DEFAULT '0',
  `ROADNAME` VARCHAR(50) NOT NULL DEFAULT 'NIL',
  `TELEPHONE` VARCHAR(20) NOT NULL DEFAULT 'NIL',
  `UNIT` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`ID`)
) ENGINE=INNODB;

## ----------------- road inspection  ------------------------------------------------

CREATE TABLE IF NOT EXISTS `ROADINSPECTION` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ROADDEFECTID` BIGINT(20) NOT NULL DEFAULT '0',
  `TASKID` BIGINT(20) NOT NULL DEFAULT '0',
  `DEFECTTYPE` VARCHAR(25) NOT NULL DEFAULT '',
  `DEFECTDETAIL` VARCHAR(100) NOT NULL DEFAULT '',
  `ROADNAME` VARCHAR(50) NOT NULL DEFAULT '',
  `LOCATION` VARCHAR(250) NOT NULL DEFAULT '',
  `SEVERITY` VARCHAR(25) NOT NULL DEFAULT '',
  `SUMMARY` MEDIUMTEXT NOT NULL,
  `SOLUTION` MEDIUMTEXT NOT NULL,
  `REPAIRREQUIRED` TINYINT(1) NOT NULL DEFAULT '0',
  `CREATIONTIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LASTMODIFIEDTIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`ID`),
  KEY `ROADDEFECT_ID_FK` (`ROADDEFECTID`)
) ENGINE=INNODB;


CREATE TABLE IF NOT EXISTS `ROADINSPECTION_SEQ` (
  `ID` BIGINT(20) NOT NULL DEFAULT '0'
) ENGINE=INNODB;

INSERT INTO `ROADINSPECTION_SEQ` (`ID`) VALUES (0);

## ----------------- task ------------------------------------------------

CREATE TABLE IF NOT EXISTS `TASK` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CATEGORY`  VARCHAR(25) NOT NULL DEFAULT '',
  `CREATIONTIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LASTMODIFIEDTIME` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PRIORITY` VARCHAR(25) NOT NULL DEFAULT '',
  `DUEDATE` DATE NOT NULL DEFAULT '0000-00-00',
  `STATUS`  VARCHAR(25) NOT NULL DEFAULT '',
  `REMARKS` VARCHAR(100) NOT NULL DEFAULT '',
  `ROADDEFECTID` BIGINT(20) NOT NULL DEFAULT '0',
  `ASSIGNEDUSERID` VARCHAR(100) NOT NULL DEFAULT '',
  PRIMARY KEY  (`ID`),
  KEY `ROADDEFECT_ID_FK` (`ROADDEFECTID`)
) ENGINE=INNODB;


CREATE TABLE IF NOT EXISTS `TASK_SEQ` (
  `ID` BIGINT(20) NOT NULL DEFAULT '0'
) ENGINE=INNODB;


INSERT INTO `TASK_SEQ` (`ID`) VALUES (0);