Advertisement
Guest User

Untitled

a guest
Aug 10th, 2016
80
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- MySQL Script generated by MySQL Workbench
  2. -- 08/10/16 13:54:47
  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`.`vacationUser`
  22. -- -----------------------------------------------------
  23. DROP TABLE IF EXISTS `vacationplaner`.`vacationUser` ;
  24.  
  25. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUser` (
  26.   `userID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  27.   `userLogin` VARCHAR(45) NOT NULL COMMENT '',
  28.   `userPassword` VARCHAR(60) NULL COMMENT '',
  29.   `userNameFirst` VARCHAR(45) NOT NULL COMMENT '',
  30.   `userNameLast` VARCHAR(45) NOT NULL COMMENT '',
  31.   `userMailAddress` VARCHAR(45) NOT NULL COMMENT '',
  32.   `userVacationDaysTotal` INT NOT NULL COMMENT '',
  33.   PRIMARY KEY (`userID`)  COMMENT '')
  34. ENGINE = InnoDB;
  35.  
  36. CREATE UNIQUE INDEX `userID_UNIQUE` ON `vacationplaner`.`vacationUser` (`userID` ASC)  COMMENT '';
  37.  
  38.  
  39. -- -----------------------------------------------------
  40. -- Table `vacationplaner`.`vacationRequest`
  41. -- -----------------------------------------------------
  42. DROP TABLE IF EXISTS `vacationplaner`.`vacationRequest` ;
  43.  
  44. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationRequest` (
  45.   `vacationRequestID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  46.   `userID` INT NOT NULL COMMENT '',
  47.   `vacationRequestDateFrom` DATE NOT NULL COMMENT '',
  48.   `vacationRequestDateTo` DATE NOT NULL COMMENT '',
  49.   `vacationRequestTotalDays` INT NOT NULL COMMENT '',
  50.   `vacationRequestDescription` VARCHAR(45) NULL COMMENT '',
  51.   `vacationRequestStatus` VARCHAR(45) NOT NULL COMMENT '',
  52.   `vacationRequestRequestDate` DATETIME NOT NULL COMMENT '',
  53.   `vacationRequestStatusUpdateDate` DATE NULL COMMENT '',
  54.   `vacationRequestStatusUpdateReason` VARCHAR(255) NULL COMMENT '',
  55.   PRIMARY KEY (`vacationRequestID`)  COMMENT '',
  56.   CONSTRAINT `fk_request_user`
  57.     FOREIGN KEY (`userID`)
  58.     REFERENCES `vacationplaner`.`vacationUser` (`userID`)
  59.     ON DELETE CASCADE
  60.     ON UPDATE CASCADE)
  61. ENGINE = InnoDB;
  62.  
  63. CREATE INDEX `x_idx` ON `vacationplaner`.`vacationRequest` (`userID` ASC)  COMMENT '';
  64.  
  65.  
  66. -- -----------------------------------------------------
  67. -- Table `vacationplaner`.`vacationGroup`
  68. -- -----------------------------------------------------
  69. DROP TABLE IF EXISTS `vacationplaner`.`vacationGroup` ;
  70.  
  71. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationGroup` (
  72.   `groupID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  73.   `groupName` VARCHAR(45) NULL COMMENT '',
  74.   PRIMARY KEY (`groupID`)  COMMENT '')
  75. ENGINE = InnoDB;
  76.  
  77.  
  78. -- -----------------------------------------------------
  79. -- Table `vacationplaner`.`vacationDepartment`
  80. -- -----------------------------------------------------
  81. DROP TABLE IF EXISTS `vacationplaner`.`vacationDepartment` ;
  82.  
  83. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationDepartment` (
  84.   `departmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  85.   `departmentName` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL COMMENT '',
  86.   `departmentColor` VARCHAR(45) NOT NULL COMMENT '',
  87.   `departmentMinStock` INT NOT NULL COMMENT '',
  88.   PRIMARY KEY (`departmentID`)  COMMENT '')
  89. ENGINE = InnoDB;
  90.  
  91.  
  92. -- -----------------------------------------------------
  93. -- Table `vacationplaner`.`statutoryHolidays`
  94. -- -----------------------------------------------------
  95. DROP TABLE IF EXISTS `vacationplaner`.`statutoryHolidays` ;
  96.  
  97. CREATE TABLE IF NOT EXISTS `vacationplaner`.`statutoryHolidays` (
  98.   `statutoryHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  99.   `statutoryHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
  100.   `statutoryHolidaysDate` DATE NOT NULL COMMENT '',
  101.   PRIMARY KEY (`statutoryHolidaysID`)  COMMENT '')
  102. ENGINE = InnoDB;
  103.  
  104. CREATE UNIQUE INDEX `statutoryHolidaysName_UNIQUE` ON `vacationplaner`.`statutoryHolidays` (`statutoryHolidaysName` ASC)  COMMENT '';
  105.  
  106.  
  107. -- -----------------------------------------------------
  108. -- Table `vacationplaner`.`schoolHolidays`
  109. -- -----------------------------------------------------
  110. DROP TABLE IF EXISTS `vacationplaner`.`schoolHolidays` ;
  111.  
  112. CREATE TABLE IF NOT EXISTS `vacationplaner`.`schoolHolidays` (
  113.   `schoolHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  114.   `schoolHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
  115.   `schoolHolidaysDateFrom` DATE NOT NULL COMMENT '',
  116.   `schoolHolidaysDateTo` DATE NOT NULL COMMENT '',
  117.   PRIMARY KEY (`schoolHolidaysID`)  COMMENT '')
  118. ENGINE = InnoDB;
  119.  
  120.  
  121. -- -----------------------------------------------------
  122. -- Table `vacationplaner`.`vacationUserAccountHistory`
  123. -- -----------------------------------------------------
  124. DROP TABLE IF EXISTS `vacationplaner`.`vacationUserAccountHistory` ;
  125.  
  126. CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUserAccountHistory` (
  127.   `userAccountHistoryID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  128.   `userAccountID` INT NULL COMMENT '',
  129.   `userID` INT NOT NULL COMMENT '',
  130.   `userVacationDaysLeft` INT NOT NULL COMMENT '',
  131.   `userVacationDaysUnconfirmed` INT NOT NULL COMMENT '',
  132.   `userVacationLastUpdated` DATETIME NOT NULL COMMENT '',
  133.   PRIMARY KEY (`userAccountHistoryID`)  COMMENT '')
  134. ENGINE = InnoDB;
  135.  
  136.  
  137. -- -----------------------------------------------------
  138. -- Table `vacationplaner`.`deputyOfDepartment`
  139. -- -----------------------------------------------------
  140. DROP TABLE IF EXISTS `vacationplaner`.`deputyOfDepartment` ;
  141.  
  142. CREATE TABLE IF NOT EXISTS `vacationplaner`.`deputyOfDepartment` (
  143.   `deputyOfDepartmentID` INT NOT NULL COMMENT '',
  144.   PRIMARY KEY (`deputyOfDepartmentID`)  COMMENT '')
  145. ENGINE = InnoDB;
  146.  
  147.  
  148. -- -----------------------------------------------------
  149. -- Table `vacationplaner`.`userAccount`
  150. -- -----------------------------------------------------
  151. DROP TABLE IF EXISTS `vacationplaner`.`userAccount` ;
  152.  
  153. CREATE TABLE IF NOT EXISTS `vacationplaner`.`userAccount` (
  154.   `userAccountID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  155.   `userAccountDaysLeft` INT NOT NULL COMMENT '',
  156.   `userAccountDaysUnconfirmed` INT NOT NULL COMMENT '',
  157.   `userAccountLastUpdated` DATETIME NOT NULL COMMENT '',
  158.   PRIMARY KEY (`userAccountID`)  COMMENT '')
  159. ENGINE = InnoDB;
  160.  
  161. CREATE UNIQUE INDEX `userAccountID_UNIQUE` ON `vacationplaner`.`userAccount` (`userAccountID` ASC)  COMMENT '';
  162.  
  163.  
  164. -- -----------------------------------------------------
  165. -- Table `vacationplaner`.`responsibleOfDepartment`
  166. -- -----------------------------------------------------
  167. DROP TABLE IF EXISTS `vacationplaner`.`responsibleOfDepartment` ;
  168.  
  169. CREATE TABLE IF NOT EXISTS `vacationplaner`.`responsibleOfDepartment` (
  170.   `responsibleOfDepartmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  171.   PRIMARY KEY (`responsibleOfDepartmentID`)  COMMENT '')
  172. ENGINE = InnoDB;
  173.  
  174.  
  175. -- -----------------------------------------------------
  176. -- Table `vacationplaner`.`users`
  177. -- -----------------------------------------------------
  178. DROP TABLE IF EXISTS `vacationplaner`.`users` ;
  179.  
  180. CREATE TABLE IF NOT EXISTS `vacationplaner`.`users` (
  181.   `usersID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  182.   `userID` INT NOT NULL COMMENT '',
  183.   `departmentID` INT NOT NULL COMMENT '',
  184.   `groupID` INT NOT NULL COMMENT '',
  185.   `deputyOfDepartmentID` INT NULL COMMENT '',
  186.   `responsibleOfDepartmentID` INT NULL COMMENT '',
  187.   `userAccountID` INT NOT NULL COMMENT '',
  188.   PRIMARY KEY (`usersID`)  COMMENT '',
  189.   CONSTRAINT `fk_users_user`
  190.     FOREIGN KEY (`userID`)
  191.     REFERENCES `vacationplaner`.`vacationUser` (`userID`)
  192.     ON DELETE CASCADE
  193.     ON UPDATE CASCADE,
  194.   CONSTRAINT `fk_users_department`
  195.     FOREIGN KEY (`departmentID`)
  196.     REFERENCES `vacationplaner`.`vacationDepartment` (`departmentID`)
  197.     ON DELETE CASCADE
  198.     ON UPDATE CASCADE,
  199.   CONSTRAINT `fk_users_group`
  200.     FOREIGN KEY (`groupID`)
  201.     REFERENCES `vacationplaner`.`vacationGroup` (`groupID`)
  202.     ON DELETE CASCADE
  203.     ON UPDATE CASCADE,
  204.   CONSTRAINT `fk_users_deputy`
  205.     FOREIGN KEY (`deputyOfDepartmentID`)
  206.     REFERENCES `vacationplaner`.`deputyOfDepartment` (`deputyOfDepartmentID`)
  207.     ON DELETE CASCADE
  208.     ON UPDATE CASCADE,
  209.   CONSTRAINT `fk_users_responsible`
  210.     FOREIGN KEY (`responsibleOfDepartmentID`)
  211.     REFERENCES `vacationplaner`.`responsibleOfDepartment` (`responsibleOfDepartmentID`)
  212.     ON DELETE CASCADE
  213.     ON UPDATE CASCADE,
  214.   CONSTRAINT `fk_users_userAccount1`
  215.     FOREIGN KEY (`userAccountID`)
  216.     REFERENCES `vacationplaner`.`userAccount` (`userAccountID`)
  217.     ON DELETE CASCADE
  218.     ON UPDATE CASCADE)
  219. ENGINE = InnoDB;
  220.  
  221. CREATE INDEX `fk_users_user_idx` ON `vacationplaner`.`users` (`userID` ASC)  COMMENT '';
  222.  
  223. CREATE INDEX `fk_users_department_idx` ON `vacationplaner`.`users` (`departmentID` ASC)  COMMENT '';
  224.  
  225. CREATE INDEX `fk_users_group_idx` ON `vacationplaner`.`users` (`groupID` ASC)  COMMENT '';
  226.  
  227. CREATE INDEX `fk_users_deputy_idx` ON `vacationplaner`.`users` (`deputyOfDepartmentID` ASC)  COMMENT '';
  228.  
  229. CREATE UNIQUE INDEX `userID_UNIQUE` ON `vacationplaner`.`users` (`userID` ASC)  COMMENT '';
  230.  
  231. CREATE INDEX `fk_users_responsible_idx` ON `vacationplaner`.`users` (`responsibleOfDepartmentID` ASC)  COMMENT '';
  232.  
  233. CREATE UNIQUE INDEX `uc_userID_departmentID_groupID` ON `vacationplaner`.`users` (`userID` ASC, `departmentID` ASC, `groupID` ASC)  COMMENT '';
  234.  
  235. CREATE INDEX `fk_users_userAccount1_idx` ON `vacationplaner`.`users` (`userAccountID` ASC)  COMMENT '';
  236.  
  237. USE `vacationplaner`;
  238.  
  239. DELIMITER $$
  240.  
  241. USE `vacationplaner`$$
  242. DROP TRIGGER IF EXISTS `vacationplaner`.`vacationUser_BEFORE_UPDATE` $$
  243. USE `vacationplaner`$$
  244. CREATE DEFINER = CURRENT_USER TRIGGER `vacationplaner`.`vacationUser_BEFORE_UPDATE` BEFORE UPDATE ON `vacationUser` FOR EACH ROW
  245. BEGIN
  246.     INSERT INTO vacationUserHistory SELECT * FROM vacationUser WHERE userID = NEW.userID;
  247. END
  248. ;$$
  249.  
  250.  
  251. DELIMITER ;
  252.  
  253. -- -----------------------------------------------------
  254. -- Data for table `vacationplaner`.`vacationGroup`
  255. -- -----------------------------------------------------
  256. START TRANSACTION;
  257. USE `vacationplaner`;
  258. INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (1, 'Admin');
  259. INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (2, 'Urlaubs_Verantwortlicher');
  260. INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (3, '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;
  296.  
  297.  
  298. SET SQL_MODE=@OLD_SQL_MODE;
  299. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  300. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Advertisement
RAW Paste Data Copied
Advertisement