install.sql 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414
  1. -- phpMyAdmin SQL Dump
  2. -- version 4.0.4.1
  3. -- http://www.phpmyadmin.net
  4. --
  5. -- Host: 127.0.0.1
  6. -- Generation Time: Apr 08, 2014 at 05:14 AM
  7. -- Server version: 5.6.11
  8. -- PHP Version: 5.5.3
  9. SET FOREIGN_KEY_CHECKS=0;
  10. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  11. SET AUTOCOMMIT = 0;
  12. START TRANSACTION;
  13. SET time_zone = "+00:00";
  14. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  15. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  16. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  17. /*!40101 SET NAMES utf8 */;
  18. -- --------------------------------------------------------
  19. --
  20. -- Table structure for table `issues`
  21. --
  22. -- Creation: Apr 06, 2014 at 11:26 PM
  23. --
  24. DROP TABLE IF EXISTS `issues`;
  25. CREATE TABLE IF NOT EXISTS `issues` (
  26. `id` int(100) NOT NULL AUTO_INCREMENT,
  27. `u_id` int(100) NOT NULL,
  28. `s_id` int(100) DEFAULT NULL,
  29. `title` varchar(100) NOT NULL,
  30. `description` text NOT NULL,
  31. `pr_id` int(100) DEFAULT NULL,
  32. `st_id` int(100) DEFAULT NULL,
  33. PRIMARY KEY (`id`),
  34. KEY `u_id` (`u_id`),
  35. KEY `u_id_2` (`u_id`),
  36. KEY `s_id` (`s_id`),
  37. KEY `pr_id` (`pr_id`,`st_id`),
  38. KEY `st_id` (`st_id`)
  39. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
  40. --
  41. -- RELATIONS FOR TABLE `issues`:
  42. -- `pr_id`
  43. -- `priorities` -> `id`
  44. -- `st_id`
  45. -- `statuses` -> `id`
  46. -- `u_id`
  47. -- `users` -> `id`
  48. -- `s_id`
  49. -- `scrums` -> `id`
  50. --
  51. -- --------------------------------------------------------
  52. --
  53. -- Table structure for table `messages`
  54. --
  55. -- Creation: Nov 18, 2013 at 07:30 PM
  56. --
  57. DROP TABLE IF EXISTS `messages`;
  58. CREATE TABLE IF NOT EXISTS `messages` (
  59. `id` int(100) NOT NULL AUTO_INCREMENT,
  60. `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  61. `from_id` int(100) NOT NULL,
  62. `to_id` int(100) DEFAULT NULL,
  63. `p_id` int(11) DEFAULT NULL,
  64. `s_id` int(100) DEFAULT NULL,
  65. `i_id` int(100) DEFAULT NULL,
  66. `message` text NOT NULL,
  67. PRIMARY KEY (`id`),
  68. KEY `from_id` (`from_id`,`to_id`,`s_id`,`i_id`),
  69. KEY `to_id` (`to_id`),
  70. KEY `s_id` (`s_id`),
  71. KEY `i_id` (`i_id`),
  72. KEY `p_id` (`p_id`)
  73. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;
  74. --
  75. -- RELATIONS FOR TABLE `messages`:
  76. -- `from_id`
  77. -- `users` -> `id`
  78. -- `to_id`
  79. -- `users` -> `id`
  80. -- `s_id`
  81. -- `scrums` -> `id`
  82. -- `i_id`
  83. -- `issues` -> `id`
  84. -- `p_id`
  85. -- `projects` -> `id`
  86. --
  87. -- --------------------------------------------------------
  88. --
  89. -- Table structure for table `priorities`
  90. --
  91. -- Creation: Apr 06, 2014 at 11:24 PM
  92. --
  93. DROP TABLE IF EXISTS `priorities`;
  94. CREATE TABLE IF NOT EXISTS `priorities` (
  95. `id` int(100) NOT NULL AUTO_INCREMENT,
  96. `name` varchar(100) NOT NULL,
  97. `color` varchar(6) NOT NULL,
  98. PRIMARY KEY (`id`),
  99. UNIQUE KEY `name` (`name`)
  100. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
  101. -- --------------------------------------------------------
  102. --
  103. -- Table structure for table `projects`
  104. --
  105. -- Creation: Oct 24, 2013 at 05:53 PM
  106. --
  107. DROP TABLE IF EXISTS `projects`;
  108. CREATE TABLE IF NOT EXISTS `projects` (
  109. `id` int(100) NOT NULL AUTO_INCREMENT,
  110. `u_id` int(100) NOT NULL,
  111. `title` varchar(100) NOT NULL,
  112. `description` text NOT NULL,
  113. PRIMARY KEY (`id`),
  114. KEY `u_id` (`u_id`)
  115. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
  116. --
  117. -- RELATIONS FOR TABLE `projects`:
  118. -- `u_id`
  119. -- `users` -> `id`
  120. --
  121. -- --------------------------------------------------------
  122. --
  123. -- Table structure for table `rels`
  124. --
  125. -- Creation: Oct 07, 2013 at 08:42 PM
  126. --
  127. DROP TABLE IF EXISTS `rels`;
  128. CREATE TABLE IF NOT EXISTS `rels` (
  129. `id` int(11) NOT NULL AUTO_INCREMENT,
  130. `u_id` int(11) NOT NULL,
  131. `i_id` int(11) DEFAULT NULL,
  132. `s_id` int(11) DEFAULT NULL,
  133. PRIMARY KEY (`id`),
  134. KEY `u_id` (`u_id`,`i_id`,`s_id`),
  135. KEY `i_id` (`i_id`),
  136. KEY `s_id` (`s_id`)
  137. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  138. --
  139. -- RELATIONS FOR TABLE `rels`:
  140. -- `u_id`
  141. -- `users` -> `id`
  142. -- `i_id`
  143. -- `issues` -> `id`
  144. -- `s_id`
  145. -- `scrums` -> `id`
  146. --
  147. -- --------------------------------------------------------
  148. --
  149. -- Table structure for table `scrums`
  150. --
  151. -- Creation: Oct 07, 2013 at 08:42 PM
  152. --
  153. DROP TABLE IF EXISTS `scrums`;
  154. CREATE TABLE IF NOT EXISTS `scrums` (
  155. `id` int(100) NOT NULL AUTO_INCREMENT,
  156. `p_id` int(100) NOT NULL,
  157. `u_id` int(100) NOT NULL,
  158. `title` varchar(100) NOT NULL,
  159. `description` text NOT NULL,
  160. PRIMARY KEY (`id`),
  161. KEY `u_id` (`u_id`),
  162. KEY `p_id` (`p_id`)
  163. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  164. --
  165. -- RELATIONS FOR TABLE `scrums`:
  166. -- `u_id`
  167. -- `users` -> `id`
  168. -- `p_id`
  169. -- `projects` -> `id`
  170. --
  171. -- --------------------------------------------------------
  172. --
  173. -- Table structure for table `statuses`
  174. --
  175. -- Creation: Apr 06, 2014 at 11:22 PM
  176. --
  177. DROP TABLE IF EXISTS `statuses`;
  178. CREATE TABLE IF NOT EXISTS `statuses` (
  179. `id` int(100) NOT NULL AUTO_INCREMENT,
  180. `name` varchar(100) NOT NULL,
  181. `isopen` tinyint(1) NOT NULL DEFAULT '1',
  182. PRIMARY KEY (`id`),
  183. UNIQUE KEY `name` (`name`)
  184. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
  185. -- --------------------------------------------------------
  186. --
  187. -- Table structure for table `users`
  188. --
  189. -- Creation: Nov 25, 2013 at 06:32 PM
  190. --
  191. DROP TABLE IF EXISTS `users`;
  192. CREATE TABLE IF NOT EXISTS `users` (
  193. `id` int(11) NOT NULL AUTO_INCREMENT,
  194. `name` varchar(50) NOT NULL,
  195. `email` varchar(254) NOT NULL,
  196. `password` varchar(128) NOT NULL,
  197. `salt` varchar(25) NOT NULL,
  198. `key` varchar(128) NOT NULL,
  199. `last_pm_check` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  200. PRIMARY KEY (`id`),
  201. UNIQUE KEY `name` (`name`)
  202. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;
  203. --
  204. -- Table structure for table `actions`
  205. --
  206. -- Creation: Apr 13, 2014 at 10:14 PM
  207. --
  208. DROP TABLE IF EXISTS `actions`;
  209. CREATE TABLE IF NOT EXISTS `actions` (
  210. `id` int(100) NOT NULL AUTO_INCREMENT,
  211. `name` varchar(100) NOT NULL,
  212. PRIMARY KEY (`id`),
  213. UNIQUE KEY `name` (`name`)
  214. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
  215. -- --------------------------------------------------------
  216. --
  217. -- Table structure for table `activity`
  218. --
  219. -- Creation: Apr 14, 2014 at 12:47 AM
  220. --
  221. DROP TABLE IF EXISTS `activity`;
  222. CREATE TABLE IF NOT EXISTS `activity` (
  223. `id` int(100) NOT NULL AUTO_INCREMENT,
  224. `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  225. `u_id` int(100) DEFAULT NULL,
  226. `p_id` int(100) DEFAULT NULL,
  227. `s_id` int(100) DEFAULT NULL,
  228. `i_id` int(100) DEFAULT NULL,
  229. `m_id` int(100) DEFAULT NULL,
  230. `a_id` int(100) NOT NULL,
  231. PRIMARY KEY (`id`),
  232. KEY `u_id` (`u_id`,`p_id`,`s_id`,`i_id`,`m_id`,`a_id`),
  233. KEY `u_id_2` (`u_id`),
  234. KEY `p_id` (`p_id`),
  235. KEY `s_id` (`s_id`),
  236. KEY `i_id` (`i_id`),
  237. KEY `m_id` (`m_id`),
  238. KEY `a_id` (`a_id`),
  239. KEY `date` (`date`)
  240. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
  241. --
  242. -- RELATIONS FOR TABLE `activity`:
  243. -- `a_id`
  244. -- `actions` -> `id`
  245. -- `i_id`
  246. -- `issues` -> `id`
  247. -- `m_id`
  248. -- `messages` -> `id`
  249. -- `p_id`
  250. -- `projects` -> `id`
  251. -- `s_id`
  252. -- `scrums` -> `id`
  253. -- `u_id`
  254. -- `users` -> `id`
  255. --
  256. --
  257. -- Table structure for table `emails`
  258. --
  259. -- Creation: Apr 16, 2014 at 05:35 PM
  260. --
  261. DROP TABLE IF EXISTS `emails`;
  262. CREATE TABLE IF NOT EXISTS `emails` (
  263. `id` int(100) NOT NULL AUTO_INCREMENT,
  264. `to_id` int(100) NOT NULL,
  265. `subject` varchar(4000) NOT NULL,
  266. `template` varchar(4000) NOT NULL,
  267. `context` text NOT NULL,
  268. `failed` tinyint(1) NOT NULL DEFAULT '0',
  269. PRIMARY KEY (`id`),
  270. KEY `to_id` (`to_id`)
  271. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
  272. --
  273. -- RELATIONS FOR TABLE `emails`:
  274. -- `to_id`
  275. -- `users` -> `id`
  276. --
  277. --
  278. -- Constraints for dumped tables
  279. --
  280. --
  281. -- Constraints for table `emails`
  282. --
  283. ALTER TABLE `emails`
  284. ADD CONSTRAINT `emails_ibfk_1` FOREIGN KEY (`to_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  285. --
  286. -- Constraints for table `activity`
  287. --
  288. ALTER TABLE `activity`
  289. ADD CONSTRAINT `activity_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  290. ADD CONSTRAINT `activity_ibfk_2` FOREIGN KEY (`p_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  291. ADD CONSTRAINT `activity_ibfk_3` FOREIGN KEY (`s_id`) REFERENCES `scrums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  292. ADD CONSTRAINT `activity_ibfk_4` FOREIGN KEY (`i_id`) REFERENCES `issues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  293. ADD CONSTRAINT `activity_ibfk_5` FOREIGN KEY (`m_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  294. ADD CONSTRAINT `activity_ibfk_6` FOREIGN KEY (`a_id`) REFERENCES `actions` (`id`) ON UPDATE CASCADE;
  295. --
  296. -- Constraints for table `issues`
  297. --
  298. ALTER TABLE `issues`
  299. ADD CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  300. ADD CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`s_id`) REFERENCES `scrums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  301. ADD CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`st_id`) REFERENCES `statuses` (`id`) ON UPDATE CASCADE,
  302. ADD CONSTRAINT `issues_ibfk_4` FOREIGN KEY (`pr_id`) REFERENCES `priorities` (`id`) ON UPDATE CASCADE;
  303. --
  304. -- Constraints for table `messages`
  305. --
  306. ALTER TABLE `messages`
  307. ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`from_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  308. ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`to_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  309. ADD CONSTRAINT `messages_ibfk_3` FOREIGN KEY (`s_id`) REFERENCES `scrums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  310. ADD CONSTRAINT `messages_ibfk_4` FOREIGN KEY (`i_id`) REFERENCES `issues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  311. ADD CONSTRAINT `messages_ibfk_5` FOREIGN KEY (`p_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  312. --
  313. -- Constraints for table `projects`
  314. --
  315. ALTER TABLE `projects`
  316. ADD CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  317. --
  318. -- Constraints for table `rels`
  319. --
  320. ALTER TABLE `rels`
  321. ADD CONSTRAINT `rels_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  322. ADD CONSTRAINT `rels_ibfk_2` FOREIGN KEY (`i_id`) REFERENCES `issues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  323. ADD CONSTRAINT `rels_ibfk_3` FOREIGN KEY (`s_id`) REFERENCES `scrums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  324. --
  325. -- Constraints for table `scrums`
  326. --
  327. ALTER TABLE `scrums`
  328. ADD CONSTRAINT `scrums_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  329. ADD CONSTRAINT `scrums_ibfk_2` FOREIGN KEY (`p_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  330. -- Truncate table before insert `priorities`
  331. --
  332. TRUNCATE TABLE `priorities`;
  333. --
  334. -- Dumping data for table `priorities`
  335. --
  336. INSERT INTO `priorities` (`id`, `name`, `color`) VALUES
  337. (1, 'low', 'green'),
  338. (2, 'medium', 'orange'),
  339. (3, 'high', 'red');
  340. --
  341. -- Truncate table before insert `statuses`
  342. --
  343. TRUNCATE TABLE `statuses`;
  344. --
  345. -- Dumping data for table `statuses`
  346. --
  347. INSERT INTO `statuses` (`id`, `name`) VALUES
  348. (1, 'New'),
  349. (2, 'More Info'),
  350. (3, 'In Progress'),
  351. (4, 'Wont Fix'),
  352. (5, 'Blocked'),
  353. (6, 'Closed');
  354. COMMIT;
  355. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  356. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  357. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;