QwikClock is an employee time tracking app.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

database.sql 4.8KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152
  1. -- MySQL Script generated by MySQL Workbench
  2. -- Wed 30 May 2018 07:09:00 PM MDT
  3. -- Model: New Model Version: 1.0
  4. -- MySQL Workbench Forward Engineering
  5. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  6. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  7. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  8. -- -----------------------------------------------------
  9. -- Schema qwikclock
  10. -- -----------------------------------------------------
  11. DROP SCHEMA IF EXISTS `qwikclock` ;
  12. -- -----------------------------------------------------
  13. -- Schema qwikclock
  14. -- -----------------------------------------------------
  15. CREATE SCHEMA IF NOT EXISTS `qwikclock` DEFAULT CHARACTER SET utf8 ;
  16. USE `qwikclock` ;
  17. -- -----------------------------------------------------
  18. -- Table `qwikclock`.`shifts`
  19. -- -----------------------------------------------------
  20. DROP TABLE IF EXISTS `qwikclock`.`shifts` ;
  21. CREATE TABLE IF NOT EXISTS `qwikclock`.`shifts` (
  22. `shiftid` INT NOT NULL AUTO_INCREMENT,
  23. `shiftname` VARCHAR(255) NOT NULL,
  24. `start` TIME NOT NULL,
  25. `end` TIME NOT NULL,
  26. `days` VARCHAR(14) NOT NULL DEFAULT 'MoTuWeThFr',
  27. PRIMARY KEY (`shiftid`),
  28. UNIQUE INDEX `shiftid_UNIQUE` (`shiftid` ASC))
  29. ENGINE = InnoDB;
  30. -- -----------------------------------------------------
  31. -- Table `qwikclock`.`punches`
  32. -- -----------------------------------------------------
  33. DROP TABLE IF EXISTS `qwikclock`.`punches` ;
  34. CREATE TABLE IF NOT EXISTS `qwikclock`.`punches` (
  35. `punchid` INT NOT NULL AUTO_INCREMENT,
  36. `uid` INT NOT NULL,
  37. `in` DATETIME NOT NULL,
  38. `out` DATETIME NULL,
  39. `notes` TEXT(1000) NULL,
  40. `shiftid` INT NULL,
  41. PRIMARY KEY (`punchid`),
  42. UNIQUE INDEX `punchid_UNIQUE` (`punchid` ASC),
  43. INDEX `fk_punches_shifts1_idx` (`shiftid` ASC),
  44. CONSTRAINT `fk_punches_shifts1`
  45. FOREIGN KEY (`shiftid`)
  46. REFERENCES `qwikclock`.`shifts` (`shiftid`)
  47. ON DELETE NO ACTION
  48. ON UPDATE NO ACTION)
  49. ENGINE = InnoDB;
  50. -- -----------------------------------------------------
  51. -- Table `qwikclock`.`assigned_shifts`
  52. -- -----------------------------------------------------
  53. DROP TABLE IF EXISTS `qwikclock`.`assigned_shifts` ;
  54. CREATE TABLE IF NOT EXISTS `qwikclock`.`assigned_shifts` (
  55. `uid` INT NOT NULL,
  56. `shiftid` INT NOT NULL,
  57. PRIMARY KEY (`uid`, `shiftid`),
  58. INDEX `fk_assigned_shifts_shifts_idx` (`shiftid` ASC),
  59. CONSTRAINT `fk_assigned_shifts_shifts`
  60. FOREIGN KEY (`shiftid`)
  61. REFERENCES `qwikclock`.`shifts` (`shiftid`)
  62. ON DELETE NO ACTION
  63. ON UPDATE NO ACTION)
  64. ENGINE = InnoDB;
  65. -- -----------------------------------------------------
  66. -- Table `qwikclock`.`report_access_codes`
  67. -- -----------------------------------------------------
  68. DROP TABLE IF EXISTS `qwikclock`.`report_access_codes` ;
  69. CREATE TABLE IF NOT EXISTS `qwikclock`.`report_access_codes` (
  70. `id` INT NOT NULL AUTO_INCREMENT,
  71. `code` VARCHAR(45) NULL,
  72. `expires` DATETIME NULL,
  73. `uid` INT NOT NULL DEFAULT -1,
  74. PRIMARY KEY (`id`))
  75. ENGINE = InnoDB;
  76. -- -----------------------------------------------------
  77. -- Table `qwikclock`.`jobs`
  78. -- -----------------------------------------------------
  79. DROP TABLE IF EXISTS `qwikclock`.`jobs` ;
  80. CREATE TABLE IF NOT EXISTS `qwikclock`.`jobs` (
  81. `jobid` INT NOT NULL AUTO_INCREMENT,
  82. `jobname` VARCHAR(200) NOT NULL,
  83. `jobcode` VARCHAR(200) NULL,
  84. `color` VARCHAR(45) NULL,
  85. `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  86. PRIMARY KEY (`jobid`),
  87. UNIQUE INDEX `jobid_UNIQUE` (`jobid` ASC))
  88. ENGINE = InnoDB;
  89. -- -----------------------------------------------------
  90. -- Table `qwikclock`.`job_groups`
  91. -- -----------------------------------------------------
  92. DROP TABLE IF EXISTS `qwikclock`.`job_groups` ;
  93. CREATE TABLE IF NOT EXISTS `qwikclock`.`job_groups` (
  94. `id` INT NOT NULL AUTO_INCREMENT,
  95. `groupid` INT NOT NULL,
  96. `jobid` INT NOT NULL,
  97. PRIMARY KEY (`id`, `groupid`, `jobid`),
  98. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  99. INDEX `fk_job_groups_jobs1_idx` (`jobid` ASC),
  100. CONSTRAINT `fk_job_groups_jobs1`
  101. FOREIGN KEY (`jobid`)
  102. REFERENCES `qwikclock`.`jobs` (`jobid`)
  103. ON DELETE NO ACTION
  104. ON UPDATE NO ACTION)
  105. ENGINE = InnoDB;
  106. -- -----------------------------------------------------
  107. -- Table `qwikclock`.`job_tracking`
  108. -- -----------------------------------------------------
  109. DROP TABLE IF EXISTS `qwikclock`.`job_tracking` ;
  110. CREATE TABLE IF NOT EXISTS `qwikclock`.`job_tracking` (
  111. `id` INT NOT NULL AUTO_INCREMENT,
  112. `uid` INT NOT NULL,
  113. `jobid` INT NOT NULL,
  114. `start` DATETIME NULL,
  115. `end` DATETIME NULL,
  116. PRIMARY KEY (`id`, `uid`, `jobid`),
  117. INDEX `fk_job_tracking_jobs1_idx` (`jobid` ASC),
  118. UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  119. CONSTRAINT `fk_job_tracking_jobs1`
  120. FOREIGN KEY (`jobid`)
  121. REFERENCES `qwikclock`.`jobs` (`jobid`)
  122. ON DELETE NO ACTION
  123. ON UPDATE NO ACTION)
  124. ENGINE = InnoDB;
  125. SET SQL_MODE=@OLD_SQL_MODE;
  126. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  127. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;