Advertisement
Guest User

Untitled

a guest
Aug 10th, 2016
129
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 12.40 KB | None
  1. -- MySQL Script generated by MySQL Workbench
  2. -- 08/10/16 16:31:17
  3. -- Model: New Model    Version: 1.0
  4. -- MySQL Workbench Forward Engineering
  5.  
  6. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  7. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  8. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  9.  
  10. -- -----------------------------------------------------
  11. -- Schema vacationplaner
  12. -- -----------------------------------------------------
  13.  
  14. -- -----------------------------------------------------
  15. -- Schema vacationplaner
  16. -- -----------------------------------------------------
  17. CREATE SCHEMA IF NOT EXISTS `vacationplaner` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
  18. USE `vacationplaner` ;
  19.  
  20. -- -----------------------------------------------------
  21. -- Table `vacationplaner`.`vacationGroup`
  22. -- -----------------------------------------------------
  23. DROP TABLE IF EXISTS `vacationplaner`.`vacationGroup` ;
  24.  
  25. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationGroup` (
  26.   `groupID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  27.   `groupName` VARCHAR(45) NULL COMMENT '',
  28.   PRIMARY KEY (`groupID`)  COMMENT '')
  29. ENGINE = InnoDB;
  30.  
  31.  
  32. -- -----------------------------------------------------
  33. -- Table `vacationplaner`.`vacationUser`
  34. -- -----------------------------------------------------
  35. DROP TABLE IF EXISTS `vacationplaner`.`vacationUser` ;
  36.  
  37. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUser` (
  38.   `userID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  39.   `userLogin` VARCHAR(45) NOT NULL COMMENT '',
  40.   `userPassword` VARCHAR(60) NULL COMMENT '',
  41.   `userNameFirst` VARCHAR(45) NOT NULL COMMENT '',
  42.   `userNameLast` VARCHAR(45) NOT NULL COMMENT '',
  43.   `userMailAddress` VARCHAR(45) NOT NULL COMMENT '',
  44.   `userVacationDaysTotal` INT NOT NULL COMMENT '',
  45.   `groupID` INT NOT NULL COMMENT '',
  46.   PRIMARY KEY (`userID`)  COMMENT '',
  47.   CONSTRAINT `fk_user_group`
  48.     FOREIGN KEY (`groupID`)
  49.     REFERENCES `vacationplaner`.`vacationGroup` (`groupID`)
  50.     ON DELETE NO ACTION
  51.     ON UPDATE NO ACTION)
  52. ENGINE = InnoDB;
  53.  
  54. CREATE UNIQUE INDEX `userID_UNIQUE` ON `vacationplaner`.`vacationUser` (`userID` ASC)  COMMENT '';
  55.  
  56. CREATE INDEX `fk_user_group_idx` ON `vacationplaner`.`vacationUser` (`groupID` ASC)  COMMENT '';
  57.  
  58.  
  59. -- -----------------------------------------------------
  60. -- Table `vacationplaner`.`vacationRequest`
  61. -- -----------------------------------------------------
  62. DROP TABLE IF EXISTS `vacationplaner`.`vacationRequest` ;
  63.  
  64. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationRequest` (
  65.   `vacationRequestID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  66.   `userID` INT NOT NULL COMMENT '',
  67.   `vacationRequestDateFrom` DATE NOT NULL COMMENT '',
  68.   `vacationRequestDateTo` DATE NOT NULL COMMENT '',
  69.   `vacationRequestTotalDays` INT NOT NULL COMMENT '',
  70.   `vacationRequestDescription` VARCHAR(45) NULL COMMENT '',
  71.   `vacationRequestStatus` VARCHAR(45) NOT NULL COMMENT '',
  72.   `vacationRequestRequestDate` DATETIME NOT NULL COMMENT '',
  73.   `vacationRequestStatusUpdateDate` DATE NULL COMMENT '',
  74.   `vacationRequestStatusUpdateReason` VARCHAR(255) NULL COMMENT '',
  75.   PRIMARY KEY (`vacationRequestID`)  COMMENT '',
  76.   CONSTRAINT `fk_request_user`
  77.     FOREIGN KEY (`userID`)
  78.     REFERENCES `vacationplaner`.`vacationUser` (`userID`)
  79.     ON DELETE CASCADE
  80.     ON UPDATE CASCADE)
  81. ENGINE = InnoDB;
  82.  
  83. CREATE INDEX `x_idx` ON `vacationplaner`.`vacationRequest` (`userID` ASC)  COMMENT '';
  84.  
  85.  
  86. -- -----------------------------------------------------
  87. -- Table `vacationplaner`.`vacationDepartment`
  88. -- -----------------------------------------------------
  89. DROP TABLE IF EXISTS `vacationplaner`.`vacationDepartment` ;
  90.  
  91. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationDepartment` (
  92.   `departmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  93.   `departmentName` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL COMMENT '',
  94.   `departmentColor` VARCHAR(45) NOT NULL COMMENT '',
  95.   `departmentMinStock` INT NOT NULL COMMENT '',
  96.   PRIMARY KEY (`departmentID`)  COMMENT '')
  97. ENGINE = InnoDB;
  98.  
  99.  
  100. -- -----------------------------------------------------
  101. -- Table `vacationplaner`.`statutoryHolidays`
  102. -- -----------------------------------------------------
  103. DROP TABLE IF EXISTS `vacationplaner`.`statutoryHolidays` ;
  104.  
  105. CREATE TABLE IF NOT EXISTS `vacationplaner`.`statutoryHolidays` (
  106.   `statutoryHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  107.   `statutoryHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
  108.   `statutoryHolidaysDate` DATE NOT NULL COMMENT '',
  109.   PRIMARY KEY (`statutoryHolidaysID`)  COMMENT '')
  110. ENGINE = InnoDB;
  111.  
  112. CREATE UNIQUE INDEX `statutoryHolidaysName_UNIQUE` ON `vacationplaner`.`statutoryHolidays` (`statutoryHolidaysName` ASC)  COMMENT '';
  113.  
  114.  
  115. -- -----------------------------------------------------
  116. -- Table `vacationplaner`.`schoolHolidays`
  117. -- -----------------------------------------------------
  118. DROP TABLE IF EXISTS `vacationplaner`.`schoolHolidays` ;
  119.  
  120. CREATE TABLE IF NOT EXISTS `vacationplaner`.`schoolHolidays` (
  121.   `schoolHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  122.   `schoolHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
  123.   `schoolHolidaysDateFrom` DATE NOT NULL COMMENT '',
  124.   `schoolHolidaysDateTo` DATE NOT NULL COMMENT '',
  125.   PRIMARY KEY (`schoolHolidaysID`)  COMMENT '')
  126. ENGINE = InnoDB;
  127.  
  128.  
  129. -- -----------------------------------------------------
  130. -- Table `vacationplaner`.`vacationUserAccountHistory`
  131. -- -----------------------------------------------------
  132. DROP TABLE IF EXISTS `vacationplaner`.`vacationUserAccountHistory` ;
  133.  
  134. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUserAccountHistory` (
  135.   `userAccountHistoryID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  136.   `userAccountID` INT NULL COMMENT '',
  137.   `userID` INT NOT NULL COMMENT '',
  138.   `userVacationDaysLeft` INT NOT NULL COMMENT '',
  139.   `userVacationDaysUnconfirmed` INT NOT NULL COMMENT '',
  140.   `userVacationLastUpdated` DATETIME NOT NULL COMMENT '',
  141.   PRIMARY KEY (`userAccountHistoryID`)  COMMENT '')
  142. ENGINE = InnoDB;
  143.  
  144.  
  145. -- -----------------------------------------------------
  146. -- Table `vacationplaner`.`deputyOfDepartment`
  147. -- -----------------------------------------------------
  148. DROP TABLE IF EXISTS `vacationplaner`.`deputyOfDepartment` ;
  149.  
  150. CREATE TABLE IF NOT EXISTS `vacationplaner`.`deputyOfDepartment` (
  151.   `deputyOfDepartmentID` INT NOT NULL COMMENT '',
  152.   PRIMARY KEY (`deputyOfDepartmentID`)  COMMENT '')
  153. ENGINE = InnoDB;
  154.  
  155.  
  156. -- -----------------------------------------------------
  157. -- Table `vacationplaner`.`userAccount`
  158. -- -----------------------------------------------------
  159. DROP TABLE IF EXISTS `vacationplaner`.`userAccount` ;
  160.  
  161. CREATE TABLE IF NOT EXISTS `vacationplaner`.`userAccount` (
  162.   `userAccountID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  163.   `userAccountDaysLeft` INT NOT NULL COMMENT '',
  164.   `userAccountDaysUnconfirmed` INT NOT NULL COMMENT '',
  165.   `userAccountLastUpdated` DATETIME NOT NULL COMMENT '',
  166.   `userID` INT NOT NULL COMMENT '',
  167.   PRIMARY KEY (`userAccountID`)  COMMENT '',
  168.   CONSTRAINT `fk_userAccount_vacationUser1`
  169.     FOREIGN KEY (`userID`)
  170.     REFERENCES `vacationplaner`.`vacationUser` (`userID`)
  171.     ON DELETE NO ACTION
  172.     ON UPDATE NO ACTION)
  173. ENGINE = InnoDB;
  174.  
  175. CREATE UNIQUE INDEX `userAccountID_UNIQUE` ON `vacationplaner`.`userAccount` (`userAccountID` ASC)  COMMENT '';
  176.  
  177. CREATE INDEX `fk_userAccount_vacationUser1_idx` ON `vacationplaner`.`userAccount` (`userID` ASC)  COMMENT '';
  178.  
  179.  
  180. -- -----------------------------------------------------
  181. -- Table `vacationplaner`.`responsibleOfDepartment`
  182. -- -----------------------------------------------------
  183. DROP TABLE IF EXISTS `vacationplaner`.`responsibleOfDepartment` ;
  184.  
  185. CREATE TABLE IF NOT EXISTS `vacationplaner`.`responsibleOfDepartment` (
  186.   `responsibleOfDepartmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  187.   PRIMARY KEY (`responsibleOfDepartmentID`)  COMMENT '')
  188. ENGINE = InnoDB;
  189.  
  190.  
  191. -- -----------------------------------------------------
  192. -- Table `vacationplaner`.`userDepartment`
  193. -- -----------------------------------------------------
  194. DROP TABLE IF EXISTS `vacationplaner`.`userDepartment` ;
  195.  
  196. CREATE TABLE IF NOT EXISTS `vacationplaner`.`userDepartment` (
  197.   `userDepartmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  198.   `departmentID` INT NOT NULL COMMENT '',
  199.   `responsibleOfDepartmentID` INT NULL COMMENT '',
  200.   `deputyOfDepartment` INT NULL COMMENT '',
  201.   `userID` INT NOT NULL COMMENT '',
  202.   PRIMARY KEY (`userDepartmentID`)  COMMENT '',
  203.   CONSTRAINT `fk_userDepartment_vacationUser1`
  204.     FOREIGN KEY (`userID`)
  205.     REFERENCES `vacationplaner`.`vacationUser` (`userID`)
  206.     ON DELETE NO ACTION
  207.     ON UPDATE NO ACTION,
  208.   CONSTRAINT `fk_userDepartment_department`
  209.     FOREIGN KEY (`departmentID`)
  210.     REFERENCES `vacationplaner`.`vacationDepartment` (`departmentID`)
  211.     ON DELETE NO ACTION
  212.     ON UPDATE NO ACTION,
  213.   CONSTRAINT `fk_userDepartment_deputy`
  214.     FOREIGN KEY (`deputyOfDepartment`)
  215.     REFERENCES `vacationplaner`.`deputyOfDepartment` (`deputyOfDepartmentID`)
  216.     ON DELETE NO ACTION
  217.     ON UPDATE NO ACTION,
  218.   CONSTRAINT `fk_userDepartment_responsible`
  219.     FOREIGN KEY (`responsibleOfDepartmentID`)
  220.     REFERENCES `vacationplaner`.`responsibleOfDepartment` (`responsibleOfDepartmentID`)
  221.     ON DELETE NO ACTION
  222.     ON UPDATE NO ACTION)
  223. ENGINE = InnoDB;
  224.  
  225. CREATE UNIQUE INDEX `departmentID_UNIQUE` ON `vacationplaner`.`userDepartment` (`departmentID` ASC)  COMMENT '';
  226.  
  227. CREATE INDEX `fk_userDepartment_vacationUser1_idx` ON `vacationplaner`.`userDepartment` (`userID` ASC)  COMMENT '';
  228.  
  229. CREATE INDEX `fk_userDepartment_deputy_idx` ON `vacationplaner`.`userDepartment` (`deputyOfDepartment` ASC)  COMMENT '';
  230.  
  231. CREATE INDEX `fk_userDepartment_responsible_idx` ON `vacationplaner`.`userDepartment` (`responsibleOfDepartmentID` ASC)  COMMENT '';
  232.  
  233. USE `vacationplaner`;
  234.  
  235. DELIMITER $$
  236.  
  237. USE `vacationplaner`$$
  238. DROP TRIGGER IF EXISTS `vacationplaner`.`vacationUser_BEFORE_UPDATE` $$
  239. USE `vacationplaner`$$
  240. CREATE DEFINER = CURRENT_USER TRIGGER `vacationplaner`.`vacationUser_BEFORE_UPDATE` BEFORE UPDATE ON `vacationUser` FOR EACH ROW
  241. BEGIN
  242.     INSERT INTO vacationUserHistory SELECT * FROM vacationUser WHERE userID = NEW.userID;
  243. END
  244. ;$$
  245.  
  246.  
  247. DELIMITER ;
  248.  
  249. SET SQL_MODE=@OLD_SQL_MODE;
  250. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  251. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  252.  
  253. -- -----------------------------------------------------
  254. -- Data for table `vacationplaner`.`vacationGroup`
  255. -- -----------------------------------------------------
  256. START TRANSACTION;
  257. USE `vacationplaner`;
  258. INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (DEFAULT, 'Admin');
  259. INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (DEFAULT, 'Urlaubs_Verantwortlicher');
  260. INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (DEFAULT, 'User');
  261.  
  262. COMMIT;
  263.  
  264.  
  265. -- -----------------------------------------------------
  266. -- Data for table `vacationplaner`.`vacationDepartment`
  267. -- -----------------------------------------------------
  268. START TRANSACTION;
  269. USE `vacationplaner`;
  270. INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (1, 'Geschäftsführung', '#1AE5E9', 1);
  271. INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (2, 'Service', '#EE0BC4', 3);
  272. INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (3, 'Vertrieb', '#E9F01D', 1);
  273. INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (4, 'Entwicklung', '#2213F5', 0);
  274.  
  275. COMMIT;
  276.  
  277.  
  278. -- -----------------------------------------------------
  279. -- Data for table `vacationplaner`.`statutoryHolidays`
  280. -- -----------------------------------------------------
  281. START TRANSACTION;
  282. USE `vacationplaner`;
  283. INSERT INTO `vacationplaner`.`statutoryHolidays` (`statutoryHolidaysID`, `statutoryHolidaysName`, `statutoryHolidaysDate`) VALUES (1, 'Neujahrstag', '2016-01-01');
  284.  
  285. COMMIT;
  286.  
  287.  
  288. -- -----------------------------------------------------
  289. -- Data for table `vacationplaner`.`schoolHolidays`
  290. -- -----------------------------------------------------
  291. START TRANSACTION;
  292. USE `vacationplaner`;
  293. INSERT INTO `vacationplaner`.`schoolHolidays` (`schoolHolidaysID`, `schoolHolidaysName`, `schoolHolidaysDateFrom`, `schoolHolidaysDateTo`) VALUES (1, 'Summer', '2016-07-11', '2016-08-23');
  294.  
  295. COMMIT;
Advertisement
RAW Paste Data Copied
Advertisement