database.sql 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361
  1. SET NAMES utf8;
  2. SET foreign_key_checks = 0;
  3. SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
  4. DROP TABLE IF EXISTS `user`;
  5. CREATE TABLE `user` (
  6. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  7. `username` varchar(32) DEFAULT NULL,
  8. `email` varchar(64) DEFAULT NULL,
  9. `name` varchar(32) NOT NULL,
  10. `password` char(40) DEFAULT NULL,
  11. `salt` char(32) DEFAULT NULL,
  12. `reset_token` CHAR(96) NULL,
  13. `role` enum('user','admin','group') NOT NULL DEFAULT 'user',
  14. `rank` tinyint(1) unsigned NOT NULL DEFAULT '0',
  15. `task_color` char(6) DEFAULT NULL,
  16. `theme` varchar(64) DEFAULT NULL,
  17. `language` varchar(5) DEFAULT NULL,
  18. `avatar_filename` varchar(64) DEFAULT NULL,
  19. `options` blob NULL,
  20. `api_key` varchar(40) NULL,
  21. `api_visible` tinyint(1) unsigned NOT NULL DEFAULT '1',
  22. `created_date` datetime NOT NULL,
  23. `deleted_date` datetime DEFAULT NULL,
  24. PRIMARY KEY (`id`),
  25. UNIQUE KEY `username` (`username`),
  26. UNIQUE KEY `email` (`email`)
  27. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  28. DROP TABLE IF EXISTS `user_group`;
  29. CREATE TABLE `user_group` (
  30. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  31. `user_id` int(10) unsigned NOT NULL,
  32. `group_id` int(10) unsigned NOT NULL,
  33. `manager` tinyint(1) NOT NULL DEFAULT '0',
  34. PRIMARY KEY (`id`),
  35. KEY `group_id` (`group_id`),
  36. KEY `group_user_id` (`user_id`)
  37. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  38. DROP TABLE IF EXISTS `issue`;
  39. CREATE TABLE `issue` (
  40. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  41. `status` int(10) unsigned NOT NULL DEFAULT '1',
  42. `type_id` int(11) unsigned NOT NULL DEFAULT '1',
  43. `name` varchar(255) NOT NULL,
  44. `size_estimate` VARCHAR(20) NULL,
  45. `description` text NOT NULL,
  46. `parent_id` int(11) unsigned DEFAULT NULL,
  47. `author_id` int(11) unsigned NOT NULL,
  48. `owner_id` int(11) unsigned DEFAULT NULL,
  49. `priority` int(11) NOT NULL DEFAULT '0',
  50. `hours_total` double unsigned DEFAULT NULL,
  51. `hours_remaining` double unsigned DEFAULT NULL,
  52. `hours_spent` double unsigned DEFAULT NULL,
  53. `created_date` datetime NOT NULL,
  54. `closed_date` datetime DEFAULT NULL,
  55. `deleted_date` datetime DEFAULT NULL,
  56. `start_date` date DEFAULT NULL,
  57. `due_date` date DEFAULT NULL,
  58. `repeat_cycle` varchar(10) NULL,
  59. `sprint_id` int(10) unsigned DEFAULT NULL,
  60. `due_date_sprint` tinyint(1) unsigned DEFAULT 0 NOT NULL,
  61. PRIMARY KEY (`id`),
  62. KEY `sprint_id` (`sprint_id`),
  63. KEY `repeat_cycle` (`repeat_cycle`),
  64. KEY `due_date` (`due_date`),
  65. KEY `type_id` (`type_id`),
  66. KEY `parent_id` (`parent_id`),
  67. CONSTRAINT `issue_type_id` FOREIGN KEY (`type_id`) REFERENCES `issue_type`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
  68. CONSTRAINT `issue_sprint_id` FOREIGN KEY (`sprint_id`) REFERENCES `sprint`(`id`) ON UPDATE CASCADE ON DELETE SET NULL,
  69. CONSTRAINT `issue_owner_id` FOREIGN KEY (`owner_id`) REFERENCES `user`(`id`) ON UPDATE CASCADE ON DELETE SET NULL,
  70. CONSTRAINT `issue_status` FOREIGN KEY (`status`) REFERENCES `issue_status`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
  71. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  72. DROP TABLE IF EXISTS `issue_backlog`;
  73. CREATE TABLE `issue_backlog` (
  74. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  75. `sprint_id` int(10) unsigned DEFAULT NULL,
  76. `issues` blob NOT NULL,
  77. PRIMARY KEY (`id`),
  78. UNIQUE KEY `issue_backlog_sprint_id` (`sprint_id`),
  79. CONSTRAINT `issue_backlog_sprint_id` FOREIGN KEY (`sprint_id`) REFERENCES `sprint` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  80. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  81. DROP TABLE IF EXISTS `issue_comment`;
  82. CREATE TABLE `issue_comment` (
  83. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  84. `issue_id` int(10) unsigned NOT NULL,
  85. `user_id` int(10) unsigned NOT NULL,
  86. `text` text NOT NULL,
  87. `file_id` int(10) unsigned NULL,
  88. `created_date` datetime NOT NULL,
  89. PRIMARY KEY (`id`),
  90. KEY `issue_id` (`issue_id`),
  91. KEY `user` (`user_id`),
  92. CONSTRAINT `comment_issue` FOREIGN KEY (`issue_id`) REFERENCES `issue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  93. CONSTRAINT `comment_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
  94. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  95. DROP TABLE IF EXISTS `issue_file`;
  96. CREATE TABLE `issue_file` (
  97. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  98. `issue_id` int(10) unsigned NOT NULL,
  99. `filename` varchar(255) NOT NULL DEFAULT '',
  100. `disk_filename` varchar(255) NOT NULL DEFAULT '',
  101. `disk_directory` varchar(255) DEFAULT NULL,
  102. `filesize` int(11) NOT NULL DEFAULT '0',
  103. `content_type` varchar(255) DEFAULT '',
  104. `digest` varchar(40) NOT NULL,
  105. `downloads` int(11) NOT NULL DEFAULT '0',
  106. `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  107. `created_date` datetime NOT NULL,
  108. `deleted_date` datetime DEFAULT NULL,
  109. PRIMARY KEY (`id`),
  110. KEY `index_issue_id` (`issue_id`),
  111. KEY `index_user_id` (`user_id`),
  112. KEY `index_created_on` (`created_date`)
  113. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  114. DROP TABLE IF EXISTS `issue_priority`;
  115. CREATE TABLE `issue_priority` (
  116. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  117. `value` int(10) NOT NULL,
  118. `name` varchar(64) NOT NULL,
  119. PRIMARY KEY (`id`)
  120. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  121. INSERT INTO `issue_priority` (`id`, `value`, `name`) VALUES
  122. (1, 0, 'Normal'),
  123. (2, 1, 'High'),
  124. (3, -1, 'Low');
  125. DROP TABLE IF EXISTS `issue_status`;
  126. CREATE TABLE `issue_status` (
  127. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  128. `name` varchar(32) NOT NULL,
  129. `closed` tinyint(1) NOT NULL DEFAULT '0',
  130. `taskboard` tinyint(1) NOT NULL DEFAULT '1',
  131. `taskboard_sort` INT UNSIGNED NULL,
  132. PRIMARY KEY (`id`)
  133. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  134. INSERT INTO `issue_status` (`id`, `name`, `closed`, `taskboard`, `taskboard_sort`) VALUES
  135. (1, 'New', 0, 2, 1),
  136. (2, 'Active', 0, 2, 2),
  137. (3, 'Completed', 1, 2, 3),
  138. (4, 'On Hold', 0, 1, 4);
  139. DROP TABLE IF EXISTS `issue_type`;
  140. CREATE TABLE `issue_type` (
  141. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  142. `name` varchar(32) NOT NULL,
  143. `role` ENUM('task','project','bug') DEFAULT 'task' NOT NULL,
  144. PRIMARY KEY (`id`),
  145. KEY `issue_type_role` (`role`)
  146. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  147. INSERT INTO `issue_type` (`id`, `name`, `role`) VALUES
  148. (1, 'Task', 'task'),
  149. (2, 'Project', 'project'),
  150. (3, 'Bug', 'bug');
  151. DROP TABLE IF EXISTS `issue_update`;
  152. CREATE TABLE `issue_update` (
  153. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  154. `issue_id` int(10) unsigned NOT NULL,
  155. `user_id` int(10) unsigned NOT NULL,
  156. `created_date` datetime NOT NULL,
  157. `comment_id` int(10) unsigned DEFAULT NULL,
  158. `notify` TINYINT(1) UNSIGNED NULL,
  159. PRIMARY KEY (`id`),
  160. KEY `issue` (`issue_id`),
  161. KEY `user` (`user_id`),
  162. CONSTRAINT `update_issue` FOREIGN KEY (`issue_id`) REFERENCES `issue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  163. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  164. DROP TABLE IF EXISTS `issue_update_field`;
  165. CREATE TABLE `issue_update_field` (
  166. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  167. `issue_update_id` int(10) unsigned NOT NULL,
  168. `field` varchar(64) NOT NULL,
  169. `old_value` text NOT NULL,
  170. `new_value` text NOT NULL,
  171. PRIMARY KEY (`id`),
  172. KEY `issue_update_field_update_id` (`issue_update_id`),
  173. CONSTRAINT `issue_update_field_update` FOREIGN KEY (`issue_update_id`) REFERENCES `issue_update` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  174. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  175. DROP TABLE IF EXISTS `issue_watcher`;
  176. CREATE TABLE `issue_watcher` (
  177. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  178. `issue_id` int(10) unsigned NOT NULL,
  179. `user_id` int(10) unsigned NOT NULL,
  180. PRIMARY KEY (`id`),
  181. UNIQUE KEY `unique_watch` (`issue_id`,`user_id`)
  182. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  183. DROP TABLE IF EXISTS `issue_tag`;
  184. CREATE TABLE `issue_tag`(
  185. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  186. `tag` VARCHAR(60) NOT NULL,
  187. `issue_id` INT UNSIGNED NOT NULL,
  188. PRIMARY KEY (`id`),
  189. INDEX `issue_tag_tag` (`tag`, `issue_id`),
  190. CONSTRAINT `issue_tag_issue` FOREIGN KEY (`issue_id`) REFERENCES `issue`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
  191. ) ENGINE=INNODB CHARSET=utf8;
  192. DROP TABLE IF EXISTS `issue_dependency`;
  193. CREATE TABLE `issue_dependency` (
  194. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  195. `issue_id` int(10) unsigned NOT NULL,
  196. `dependency_id` int(11) unsigned NOT NULL,
  197. `dependency_type` char(2) COLLATE utf8_unicode_ci NOT NULL,
  198. PRIMARY KEY (`id`),
  199. UNIQUE KEY `issue_id_dependency_id` (`issue_id`,`dependency_id`),
  200. KEY `dependency_id` (`dependency_id`),
  201. CONSTRAINT `issue_dependency_ibfk_2` FOREIGN KEY (`issue_id`) REFERENCES `issue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  202. CONSTRAINT `issue_dependency_ibfk_3` FOREIGN KEY (`dependency_id`) REFERENCES `issue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  203. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  204. DROP TABLE IF EXISTS `sprint`;
  205. CREATE TABLE `sprint` (
  206. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  207. `name` varchar(60) NOT NULL,
  208. `start_date` date NOT NULL,
  209. `end_date` date NOT NULL,
  210. PRIMARY KEY (`id`)
  211. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  212. DROP VIEW IF EXISTS `user_group_user`;
  213. CREATE VIEW `user_group_user` AS (select `g`.`id` AS `id`,`g`.`group_id` AS `group_id`,`g`.`user_id` AS `user_id`,`u`.`username` AS `user_username`,`u`.`email` AS `user_email`,`u`.`name` AS `user_name`,`u`.`role` AS `user_role`,`u`.`task_color` AS `user_task_color`,`u`.`deleted_date` AS `deleted_date`,`g`.`manager` AS `manager` from (`user_group` `g` join `user` `u` on((`g`.`user_id` = `u`.`id`))));
  214. DROP VIEW IF EXISTS `issue_comment_user`;
  215. CREATE VIEW `issue_comment_user` AS (select `c`.`id` AS `id`,`c`.`issue_id` AS `issue_id`,`c`.`user_id` AS `user_id`,`c`.`text` AS `text`, `c`.`file_id` as `file_id`, `c`.`created_date` AS `created_date`,`u`.`username` AS `user_username`,`u`.`email` AS `user_email`,`u`.`name` AS `user_name`,`u`.`role` AS `user_role`,`u`.`task_color` AS `user_task_color` from (`issue_comment` `c` join `user` `u` on((`c`.`user_id` = `u`.`id`))));
  216. DROP VIEW IF EXISTS `issue_comment_detail`;
  217. CREATE VIEW `issue_comment_detail` AS (select `c`.`id` AS `id`, `c`.`issue_id` AS `issue_id`, `c`.`user_id` AS `user_id`, `c`.`text` AS `text`, `c`.`file_id` AS `file_id`, `c`.`created_date` AS `created_date`, `u`.`username` AS `user_username`, `u`.`email` AS `user_email`, `u`.`name` AS `user_name`, `u`.`role` AS `user_role`, `u`.`task_color` AS `user_task_color`, `f`.`filename` AS `file_filename`, `f`.`filesize` AS `file_filesize`, `f`.`content_type` AS `file_content_type`, `f`.`downloads` AS `file_downloads`, `f`.`created_date` AS `file_created_date`, `f`.`deleted_date` AS `file_deleted_date`, `i`.`deleted_date` AS `issue_deleted_date` from `issue_comment` `c` join `user` `u` on `c`.`user_id` = `u`.`id` left join `issue_file` `f` on `c`.`file_id` = `f`.`id` JOIN `issue` `i` ON `i`.`id` = `c`.`issue_id`);
  218. DROP VIEW IF EXISTS `issue_detail`;
  219. CREATE VIEW `issue_detail` AS
  220. SELECT
  221. `issue`.`id` AS `id`,
  222. `issue`.`status` AS `status`,
  223. `issue`.`type_id` AS `type_id`,
  224. `issue`.`name` AS `name`,
  225. `issue`.`size_estimate` AS `size_estimate`,
  226. `issue`.`description` AS `description`,
  227. `issue`.`parent_id` AS `parent_id`,
  228. `issue`.`author_id` AS `author_id`,
  229. `issue`.`owner_id` AS `owner_id`,
  230. `issue`.`priority` AS `priority`,
  231. `issue`.`hours_total` AS `hours_total`,
  232. `issue`.`hours_remaining` AS `hours_remaining`,
  233. `issue`.`hours_spent` AS `hours_spent`,
  234. `issue`.`created_date` AS `created_date`,
  235. `issue`.`closed_date` AS `closed_date`,
  236. `issue`.`deleted_date` AS `deleted_date`,
  237. `issue`.`start_date` AS `start_date`,
  238. `issue`.`due_date` AS `due_date`,
  239. ISNULL(`issue`.`due_date`) AS `has_due_date`,
  240. `issue`.`repeat_cycle` AS `repeat_cycle`,
  241. `issue`.`sprint_id` AS `sprint_id`,
  242. `issue`.`due_date_sprint` AS `due_date_sprint`,
  243. `sprint`.`name` AS `sprint_name`,
  244. `sprint`.`start_date` AS `sprint_start_date`,
  245. `sprint`.`end_date` AS `sprint_end_date`,
  246. `type`.`name` AS `type_name`,
  247. `status`.`name` AS `status_name`,
  248. `status`.`closed` AS `status_closed`,
  249. `priority`.`id` AS `priority_id`,
  250. `priority`.`name` AS `priority_name`,
  251. `author`.`username` AS `author_username`,
  252. `author`.`name` AS `author_name`,
  253. `author`.`email` AS `author_email`,
  254. `author`.`task_color` AS `author_task_color`,
  255. `owner`.`username` AS `owner_username`,
  256. `owner`.`name` AS `owner_name`,
  257. `owner`.`email` AS `owner_email`,
  258. `owner`.`task_color` AS `owner_task_color`,
  259. `parent`.`name` AS `parent_name`
  260. FROM `issue`
  261. LEFT JOIN `user` `author` on`issue`.`author_id` = `author`.`id`
  262. LEFT JOIN `user` `owner` on`issue`.`owner_id` = `owner`.`id`
  263. LEFT JOIN `issue_status` `status` on`issue`.`status` = `status`.`id`
  264. LEFT JOIN `issue_priority` `priority` on`issue`.`priority` = `priority`.`value`
  265. LEFT JOIN `issue_type` `type` on`issue`.`type_id` = `type`.`id`
  266. LEFT JOIN `sprint` on`issue`.`sprint_id` = `sprint`.`id`
  267. LEFT JOIN `issue` `parent` ON `issue`.`parent_id` = `parent`.`id`;
  268. DROP VIEW IF EXISTS `issue_file_detail`;
  269. CREATE VIEW `issue_file_detail` AS (select `f`.`id` AS `id`, `f`.`issue_id` AS `issue_id`, `f`.`filename` AS `filename`, `f`.`disk_filename` AS `disk_filename`, `f`.`disk_directory` AS `disk_directory`, `f`.`filesize` AS `filesize`, `f`.`content_type` AS `content_type`, `f`.`digest` AS `digest`, `f`.`downloads` AS `downloads`, `f`.`user_id` AS `user_id`, `f`.`created_date` AS `created_date`, `f`.`deleted_date` AS `deleted_date`, `u`.`username` AS `user_username`, `u`.`email` AS `user_email`, `u`.`name` AS `user_name`, `u`.`task_color` AS `user_task_color` from (`issue_file` `f` join `user` `u` on ((`f`.`user_id` = `u`.`id`))));
  270. DROP VIEW IF EXISTS `issue_update_detail`;
  271. CREATE VIEW `issue_update_detail` AS (select `i`.`id` AS `id`, `i`.`issue_id` AS `issue_id`, `i`.`user_id` AS `user_id`, `i`.`created_date` AS `created_date`, `u`.`username` AS `user_username`, `u`.`name` AS `user_name`, `u`.`email` AS `user_email`, `i`.`comment_id` AS `comment_id`, `c`.`text` AS `comment_text` from ((`issue_update` `i` join `user` `u` on ((`i`.`user_id` = `u`.`id`))) left join `issue_comment` `c` on ((`i`.`comment_id` = `c`.`id`))));
  272. DROP VIEW IF EXISTS `issue_watcher_user`;
  273. CREATE VIEW `issue_watcher_user` AS (select `w`.`id` AS `watcher_id`,`w`.`issue_id` AS `issue_id`,`u`.`id` AS `id`,`u`.`username` AS `username`,`u`.`email` AS `email`,`u`.`name` AS `name`,`u`.`password` AS `password`,`u`.`role` AS `role`,`u`.`task_color` AS `task_color`,`u`.`created_date` AS `created_date`,`u`.`deleted_date` AS `deleted_date` from (`issue_watcher` `w` join `user` `u` on((`w`.`user_id` = `u`.`id`))));
  274. DROP TABLE IF EXISTS `attribute`;
  275. CREATE TABLE `attribute` (
  276. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  277. `name` varchar(64) NOT NULL,
  278. `type` enum('text','numeric','datetime','bool','list') NOT NULL DEFAULT 'text',
  279. `default` text,
  280. PRIMARY KEY (`id`)
  281. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  282. DROP TABLE IF EXISTS `attribute_issue_type`;
  283. CREATE TABLE `attribute_issue_type` (
  284. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  285. `attribute_id` int(10) unsigned NOT NULL,
  286. `issue_type_id` int(10) unsigned NOT NULL,
  287. PRIMARY KEY (`id`),
  288. KEY `issue_type` (`issue_type_id`),
  289. KEY `attribute_issue_type_attribute_id` (`attribute_id`),
  290. CONSTRAINT `attribute_issue_type_attribute_id` FOREIGN KEY (`attribute_id`) REFERENCES `attribute` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  291. CONSTRAINT `attribute_issue_type_issue_type_id` FOREIGN KEY (`issue_type_id`) REFERENCES `issue_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  292. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  293. DROP TABLE IF EXISTS `attribute_value`;
  294. CREATE TABLE `attribute_value` (
  295. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  296. `attribute_id` int(10) unsigned NOT NULL,
  297. `issue_id` int(10) unsigned NOT NULL,
  298. `value` text NOT NULL,
  299. PRIMARY KEY (`id`),
  300. KEY `object` (`attribute_id`,`issue_id`),
  301. CONSTRAINT `attribute_value_attribute_id` FOREIGN KEY (`attribute_id`) REFERENCES `attribute` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  302. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  303. DROP VIEW IF EXISTS `attribute_value_detail`;
  304. CREATE VIEW `attribute_value_detail` AS (select `v`.`id` AS `id`,`v`.`attribute_id` AS `attribute_id`,`v`.`issue_id` AS `issue_id`,`v`.`value` AS `value`,`a`.`name` AS `name`,`a`.`type` AS `type`,`a`.`default` AS `default` from (`attribute_value` `v` join `attribute` `a` on((`v`.`attribute_id` = `a`.`id`))));
  305. DROP TABLE IF EXISTS `session`;
  306. CREATE TABLE `session`(
  307. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  308. `token` VARBINARY(64) NOT NULL,
  309. `ip` VARBINARY(39) NOT NULL,
  310. `user_id` INT UNSIGNED NOT NULL,
  311. `created` DATETIME NOT NULL,
  312. PRIMARY KEY (`id`),
  313. UNIQUE KEY `session_token` (`token`, `ip`),
  314. KEY `session_user_id` (`user_id`),
  315. CONSTRAINT `session_user_id` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
  316. ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_unicode_ci;
  317. DROP TABLE IF EXISTS `config`;
  318. CREATE TABLE `config` (
  319. `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  320. `attribute` varchar(255) COLLATE 'utf8_general_ci' NULL,
  321. `value` varchar(255) COLLATE 'utf8_general_ci' NULL,
  322. UNIQUE KEY `attribute` (`attribute`)
  323. );
  324. -- TODO: Set all default config values from config-base.ini in DB
  325. -- This will allow us to deprecate and remove the file
  326. INSERT INTO `config` (`attribute`,`value`) VALUES ('security.reset_ttl', '86400');
  327. INSERT INTO `config` (`attribute`, `value`) VALUES ('version', '17.03.23');