ircd.sql 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537
  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: Feb 20, 2014 at 02:10 AM
  7. -- Server version: 5.6.11
  8. -- PHP Version: 5.5.3
  9. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  10. SET AUTOCOMMIT = 0;
  11. START TRANSACTION;
  12. SET time_zone = "+00:00";
  13. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  14. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  15. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  16. /*!40101 SET NAMES utf8 */;
  17. --
  18. -- Database: `ircd`
  19. --
  20. CREATE DATABASE IF NOT EXISTS `ircd` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
  21. USE `ircd`;
  22. -- --------------------------------------------------------
  23. --
  24. -- Stand-in structure for view `children_v`
  25. --
  26. DROP VIEW IF EXISTS `children_v`;
  27. CREATE TABLE IF NOT EXISTS `children_v` (
  28. `user_id` int(100)
  29. ,`parent_id` int(100)
  30. ,`child_id` int(100)
  31. );
  32. -- --------------------------------------------------------
  33. --
  34. -- Table structure for table `configuration`
  35. --
  36. -- Creation: Feb 19, 2014 at 10:35 PM
  37. --
  38. DROP TABLE IF EXISTS `configuration`;
  39. CREATE TABLE IF NOT EXISTS `configuration` (
  40. `key` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  41. `description` varchar(100) NOT NULL,
  42. `value` varchar(4000) DEFAULT NULL,
  43. `type` varchar(100) NOT NULL,
  44. PRIMARY KEY (`key`),
  45. UNIQUE KEY `key` (`key`),
  46. KEY `key_2` (`key`)
  47. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  48. --
  49. -- Dumping data for table `configuration`
  50. --
  51. INSERT INTO `configuration` (`key`, `description`, `value`, `type`) VALUES
  52. ('2-factor-method', '2-Factor Method', 'none', 'list'),
  53. ('authy-api-key', 'Authy API Key', NULL, 'string'),
  54. ('authy-endpoint', 'Authy Endpoint', 'http://sandbox-api.authy.com', 'list'),
  55. ('irc-port', 'Main Server Port', '6667', 'number'),
  56. ('irc-server', 'Main Server', NULL, 'lookup'),
  57. ('mibbit-password', 'Mibbit Password', NULL, 'string'),
  58. ('ops-channel', 'Opers Channel', '#opers', 'string'),
  59. ('persona-audience', 'Persona Audience', NULL, 'string'),
  60. ('persona-endpoint', 'Persona Endpoint', 'none', 'list'),
  61. ('rehash-pass', 'RehashServ Password', NULL, 'string'),
  62. ('rehash-host', 'RehashServ Host', NULL, 'string'),
  63. ('server-pass', 'Server-to-Server Password', NULL, 'string'),
  64. ('services-server', 'Services Server', NULL, 'lookup'),
  65. ('stats-server', 'Stats Server', NULL, 'lookup'),
  66. ('xmlrpc-path', 'XMLRPC Path', '/xmlrpc', 'string'),
  67. ('xmlrpc-port', 'XMLRPC Port', '9900', 'number'),
  68. ('xmlrpc-server', 'XMLRPC Server', NULL, 'lookup'),
  69. ('smf-path', 'SMF Installation Path', NULL, 'string');
  70. -- --------------------------------------------------------
  71. --
  72. -- Table structure for table `configuration_lists`
  73. --
  74. -- Creation: Feb 19, 2014 at 10:35 PM
  75. --
  76. DROP TABLE IF EXISTS `configuration_lists`;
  77. CREATE TABLE IF NOT EXISTS `configuration_lists` (
  78. `id` int(100) NOT NULL AUTO_INCREMENT,
  79. `key` varchar(100) COLLATE latin1_general_ci NOT NULL,
  80. `value` varchar(4000) COLLATE latin1_general_ci NOT NULL,
  81. `label` varchar(100) COLLATE latin1_general_ci NOT NULL,
  82. PRIMARY KEY (`id`),
  83. KEY `key` (`key`)
  84. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8 ;
  85. --
  86. -- RELATIONS FOR TABLE `configuration_lists`:
  87. -- `key`
  88. -- `configuration` -> `key`
  89. --
  90. --
  91. -- Dumping data for table `configuration_lists`
  92. --
  93. INSERT INTO `configuration_lists` (`id`, `key`, `value`, `label`) VALUES
  94. (1, '2-factor-method', 'none', '(none)'),
  95. (2, '2-factor-method', 'google-authenticator', 'Google Authenticator'),
  96. (3, '2-factor-method', 'authy', 'Authy'),
  97. (4, 'authy-endpoint', 'https://api.authy.com', 'Production'),
  98. (5, 'authy-endpoint', 'http://sandbox-api.authy.com', 'Sandbox'),
  99. (6, 'persona-endpoint', 'none', '(none)'),
  100. (7, 'persona-endpoint', 'https://verifier.login.persona.org/verify', 'Production');
  101. -- --------------------------------------------------------
  102. --
  103. -- Table structure for table `configuration_lookups`
  104. --
  105. -- Creation: Feb 19, 2014 at 10:35 PM
  106. --
  107. DROP TABLE IF EXISTS `configuration_lookups`;
  108. CREATE TABLE IF NOT EXISTS `configuration_lookups` (
  109. `id` int(100) NOT NULL AUTO_INCREMENT,
  110. `key` varchar(100) COLLATE latin1_general_ci NOT NULL,
  111. `table` varchar(100) COLLATE latin1_general_ci NOT NULL,
  112. `column` varchar(100) COLLATE latin1_general_ci NOT NULL,
  113. `label_column` varchar(100) COLLATE latin1_general_ci NOT NULL,
  114. `enabled_column` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
  115. PRIMARY KEY (`id`),
  116. KEY `configuration_key` (`key`)
  117. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;
  118. --
  119. -- RELATIONS FOR TABLE `configuration_lookups`:
  120. -- `key`
  121. -- `configuration` -> `key`
  122. --
  123. --
  124. -- Dumping data for table `configuration_lookups`
  125. --
  126. INSERT INTO `configuration_lookups` (`id`, `key`, `table`, `column`, `label_column`, `enabled_column`) VALUES
  127. (1, 'irc-server', 'servers', 'ip', 'name', '!uline'),
  128. (2, 'services-server', 'servers', 'host', 'name', 'uline'),
  129. (3, 'stats-server', 'servers', 'host', 'name', 'uline'),
  130. (4, 'xmlrpc-server', 'servers', 'ip', 'name', 'uline');
  131. -- --------------------------------------------------------
  132. --
  133. -- Table structure for table `emails`
  134. --
  135. -- Creation: Feb 19, 2014 at 10:35 PM
  136. --
  137. DROP TABLE IF EXISTS `emails`;
  138. CREATE TABLE IF NOT EXISTS `emails` (
  139. `id` int(100) NOT NULL AUTO_INCREMENT,
  140. `user_id` int(100) NOT NULL,
  141. `email` varchar(100) COLLATE latin1_general_ci NOT NULL,
  142. PRIMARY KEY (`id`),
  143. UNIQUE KEY `email` (`email`),
  144. KEY `user_id` (`user_id`)
  145. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=16 ;
  146. --
  147. -- RELATIONS FOR TABLE `emails`:
  148. -- `user_id`
  149. -- `users` -> `id`
  150. --
  151. -- --------------------------------------------------------
  152. --
  153. -- Table structure for table `hosts`
  154. --
  155. -- Creation: Feb 19, 2014 at 10:35 PM
  156. --
  157. DROP TABLE IF EXISTS `hosts`;
  158. CREATE TABLE IF NOT EXISTS `hosts` (
  159. `id` int(100) NOT NULL AUTO_INCREMENT,
  160. `oper_id` int(100) NOT NULL,
  161. `host` varchar(100) NOT NULL,
  162. PRIMARY KEY (`id`),
  163. KEY `oper_id` (`oper_id`)
  164. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;
  165. --
  166. -- RELATIONS FOR TABLE `hosts`:
  167. -- `oper_id`
  168. -- `opers` -> `id`
  169. --
  170. -- --------------------------------------------------------
  171. --
  172. -- Table structure for table `opers`
  173. --
  174. -- Creation: Feb 19, 2014 at 10:35 PM
  175. --
  176. DROP TABLE IF EXISTS `opers`;
  177. CREATE TABLE IF NOT EXISTS `opers` (
  178. `id` int(100) NOT NULL AUTO_INCREMENT,
  179. `nick` varchar(20) NOT NULL,
  180. `role_id` int(11) NOT NULL,
  181. `user_id` int(100) NOT NULL,
  182. `manager_id` int(100) NOT NULL,
  183. `server_id` int(100) DEFAULT NULL,
  184. `password` varchar(100) NOT NULL,
  185. `password_type_id` int(100) NOT NULL,
  186. `swhois` varchar(100) DEFAULT NULL,
  187. PRIMARY KEY (`id`),
  188. UNIQUE KEY `id` (`id`),
  189. UNIQUE KEY `nick` (`nick`),
  190. KEY `id_2` (`id`),
  191. KEY `nick_2` (`nick`),
  192. KEY `role_id` (`role_id`),
  193. KEY `password_type_id` (`password_type_id`),
  194. KEY `user_id` (`manager_id`),
  195. KEY `server_id` (`server_id`),
  196. KEY `user_id_2` (`user_id`)
  197. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
  198. --
  199. -- RELATIONS FOR TABLE `opers`:
  200. -- `password_type_id`
  201. -- `password_type` -> `id`
  202. -- `server_id`
  203. -- `servers` -> `id`
  204. -- `role_id`
  205. -- `oper_roles` -> `id`
  206. -- `manager_id`
  207. -- `users` -> `id`
  208. --
  209. -- --------------------------------------------------------
  210. --
  211. -- Stand-in structure for view `opers_v`
  212. --
  213. DROP VIEW IF EXISTS `opers_v`;
  214. CREATE TABLE IF NOT EXISTS `opers_v` (
  215. `id` int(100)
  216. ,`user_id` int(100)
  217. ,`manager_id` int(100)
  218. ,`server_id` int(100)
  219. ,`nick` varchar(20)
  220. ,`password` varchar(100)
  221. ,`password_type` varchar(100)
  222. ,`swhois` varchar(100)
  223. ,`flags` varchar(4000)
  224. ,`role` varchar(20)
  225. );
  226. -- --------------------------------------------------------
  227. --
  228. -- Table structure for table `oper_roles`
  229. --
  230. -- Creation: Feb 19, 2014 at 10:35 PM
  231. --
  232. DROP TABLE IF EXISTS `oper_roles`;
  233. CREATE TABLE IF NOT EXISTS `oper_roles` (
  234. `id` int(100) NOT NULL AUTO_INCREMENT,
  235. `name` varchar(20) NOT NULL,
  236. `flags` varchar(4000) NOT NULL,
  237. PRIMARY KEY (`id`),
  238. UNIQUE KEY `name` (`name`)
  239. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
  240. --
  241. -- Dumping data for table `oper_roles`
  242. --
  243. INSERT INTO `oper_roles` (`id`, `name`, `flags`) VALUES
  244. (1, 'netadmin', 'netadmin;\ncan_restart;\ncan_die;\ncan_gkline;\ncan_zline;\ncan_gzline;\ncan_override;\ncan_addline;\nget_host;'),
  245. (2, 'global', 'global;\ncan_override;\ncan_setq;\ncan_addline;\ncan_dccdeny;\nget_host;'),
  246. (3, 'servicesadmin', 'services-admin;\r\ncan_override;\r\ncan_setq;\r\ncan_addline;\r\ncan_dccdeny;\r\nget_host;');
  247. -- --------------------------------------------------------
  248. --
  249. -- Table structure for table `password_type`
  250. --
  251. -- Creation: Feb 19, 2014 at 10:35 PM
  252. --
  253. DROP TABLE IF EXISTS `password_type`;
  254. CREATE TABLE IF NOT EXISTS `password_type` (
  255. `id` int(100) NOT NULL AUTO_INCREMENT,
  256. `name` varchar(100) NOT NULL,
  257. PRIMARY KEY (`id`),
  258. UNIQUE KEY `name` (`name`)
  259. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
  260. --
  261. -- Dumping data for table `password_type`
  262. --
  263. INSERT INTO `password_type` (`id`, `name`) VALUES
  264. (1, 'md5'),
  265. (2, 'sha1');
  266. -- --------------------------------------------------------
  267. --
  268. -- Table structure for table `servers`
  269. --
  270. -- Creation: Feb 19, 2014 at 10:35 PM
  271. --
  272. DROP TABLE IF EXISTS `servers`;
  273. CREATE TABLE IF NOT EXISTS `servers` (
  274. `id` int(100) NOT NULL AUTO_INCREMENT,
  275. `name` varchar(100) NOT NULL,
  276. `host` varchar(100) NOT NULL,
  277. `description` varchar(4000) NOT NULL,
  278. `parent_id` int(100) DEFAULT NULL,
  279. `user_id` int(100) NOT NULL,
  280. `ip` varchar(15) NOT NULL,
  281. `uline` tinyint(1) NOT NULL DEFAULT '0',
  282. PRIMARY KEY (`id`),
  283. UNIQUE KEY `name` (`name`,`host`),
  284. KEY `parent_id` (`parent_id`),
  285. KEY `user_id` (`user_id`)
  286. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
  287. --
  288. -- RELATIONS FOR TABLE `servers`:
  289. -- `parent_id`
  290. -- `servers` -> `id`
  291. -- `user_id`
  292. -- `users` -> `id`
  293. --
  294. -- --------------------------------------------------------
  295. --
  296. -- Stand-in structure for view `ulines_v`
  297. --
  298. DROP VIEW IF EXISTS `ulines_v`;
  299. CREATE TABLE IF NOT EXISTS `ulines_v` (
  300. `id` int(100)
  301. ,`host` varchar(100)
  302. );
  303. -- --------------------------------------------------------
  304. --
  305. -- Table structure for table `users`
  306. --
  307. -- Creation: Feb 19, 2014 at 10:35 PM
  308. --
  309. DROP TABLE IF EXISTS `users`;
  310. CREATE TABLE IF NOT EXISTS `users` (
  311. `id` int(100) NOT NULL AUTO_INCREMENT,
  312. `api_key` varchar(24) NOT NULL,
  313. `secret_key` varchar(100) DEFAULT NULL,
  314. `password` varchar(40) NOT NULL,
  315. `real_name` varchar(50) NOT NULL,
  316. `nick` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  317. `email` varchar(100) NOT NULL,
  318. PRIMARY KEY (`id`),
  319. UNIQUE KEY `api_key` (`api_key`),
  320. UNIQUE KEY `nick` (`nick`),
  321. KEY `authy_id` (`secret_key`)
  322. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
  323. --
  324. -- Dumping data for table `users`
  325. --
  326. INSERT INTO `users` (`id`, `api_key`, `secret_key`, `password`, `real_name`, `nick`, `email`) VALUES
  327. (7, '1', NULL, '$Dj94pkis$Fs5kyCo4ocTT7zh8asWNJwIelP0=', 'root', 'root', '');
  328. -- --------------------------------------------------------
  329. --
  330. -- Table structure for table `user_roles`
  331. --
  332. -- Creation: Feb 19, 2014 at 10:35 PM
  333. --
  334. DROP TABLE IF EXISTS `user_roles`;
  335. CREATE TABLE IF NOT EXISTS `user_roles` (
  336. `id` int(100) NOT NULL AUTO_INCREMENT,
  337. `user_id` int(100) NOT NULL,
  338. `user_role_id` int(100) NOT NULL,
  339. PRIMARY KEY (`id`),
  340. KEY `user_id` (`user_id`,`user_role_id`),
  341. KEY `user_role_id` (`user_role_id`)
  342. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
  343. --
  344. -- RELATIONS FOR TABLE `user_roles`:
  345. -- `user_id`
  346. -- `users` -> `id`
  347. -- `user_role_id`
  348. -- `user_role_types` -> `id`
  349. --
  350. --
  351. -- Dumping data for table `user_roles`
  352. --
  353. INSERT INTO `user_roles` (`id`, `user_id`, `user_role_id`) VALUES
  354. (13, 7, 4);
  355. -- --------------------------------------------------------
  356. --
  357. -- Table structure for table `user_role_types`
  358. --
  359. -- Creation: Feb 19, 2014 at 10:35 PM
  360. --
  361. DROP TABLE IF EXISTS `user_role_types`;
  362. CREATE TABLE IF NOT EXISTS `user_role_types` (
  363. `id` int(100) NOT NULL AUTO_INCREMENT,
  364. `name` varchar(100) NOT NULL,
  365. `description` varchar(100) NOT NULL,
  366. `flags` varchar(3) NOT NULL DEFAULT 'o',
  367. PRIMARY KEY (`id`),
  368. UNIQUE KEY `name` (`name`),
  369. UNIQUE KEY `description` (`description`)
  370. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
  371. --
  372. -- Dumping data for table `user_role_types`
  373. --
  374. INSERT INTO `user_role_types` (`id`, `name`, `description`, `flags`) VALUES
  375. (1, 'oper', 'Oper', 'o'),
  376. (2, 'admin', 'Server Manager', 'n'),
  377. (3, 'netadmin', 'Network Admin', 'on'),
  378. (4, 'globaladmin', 'Global Admin', 'nao');
  379. DROP TABLE IF EXISTS `smf_groups`;
  380. CREATE TABLE IF NOT EXISTS `smf_groups` (
  381. `irc_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  382. `id_group` smallint(5) NOT NULL,
  383. PRIMARY KEY (`irc_name`),
  384. UNIQUE KEY `irc_name` (`irc_name`,`id_group`),
  385. KEY `id_group` (`id_group`)
  386. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
  387. -- --------------------------------------------------------
  388. --
  389. -- Structure for view `children_v`
  390. --
  391. DROP TABLE IF EXISTS `children_v`;
  392. CREATE ALGORITHM=UNDEFINED DEFINER=`ircd`@`localhost` SQL SECURITY DEFINER VIEW `children_v` AS select `p`.`user_id` AS `user_id`,`p`.`id` AS `parent_id`,`c`.`id` AS `child_id` from (`servers` `c` left join `servers` `p` on((`p`.`id` = `c`.`parent_id`))) where ((`p`.`user_id` is not null) and (`c`.`user_id` is not null) and (`c`.`parent_id` is not null) and (`c`.`uline` = 0));
  393. -- --------------------------------------------------------
  394. --
  395. -- Structure for view `opers_v`
  396. --
  397. DROP TABLE IF EXISTS `opers_v`;
  398. CREATE ALGORITHM=UNDEFINED DEFINER=`ircd`@`localhost` SQL SECURITY DEFINER VIEW `opers_v` AS select `o`.`id` AS `id`,`o`.`user_id` AS `user_id`,`o`.`manager_id` AS `manager_id`,`o`.`server_id` AS `server_id`,`o`.`nick` AS `nick`,`o`.`password` AS `password`,`p`.`name` AS `password_type`,`o`.`swhois` AS `swhois`,`r`.`flags` AS `flags`,`r`.`name` AS `role` from ((`opers` `o` join `oper_roles` `r` on((`r`.`id` = `o`.`role_id`))) join `password_type` `p` on((`p`.`id` = `o`.`password_type_id`)));
  399. -- --------------------------------------------------------
  400. --
  401. -- Structure for view `ulines_v`
  402. --
  403. DROP TABLE IF EXISTS `ulines_v`;
  404. CREATE ALGORITHM=UNDEFINED DEFINER=`ircd`@`localhost` SQL SECURITY DEFINER VIEW `ulines_v` AS select `s`.`id` AS `id`,`s`.`host` AS `host` from `servers` `s` where (`s`.`uline` = 1);
  405. --
  406. -- Constraints for dumped tables
  407. --
  408. --
  409. -- Constraints for table `configuration_lists`
  410. --
  411. ALTER TABLE `configuration_lists`
  412. ADD CONSTRAINT `configuration_lists_ibfk_1` FOREIGN KEY (`key`) REFERENCES `configuration` (`key`) ON DELETE CASCADE ON UPDATE CASCADE;
  413. --
  414. -- Constraints for table `configuration_lookups`
  415. --
  416. ALTER TABLE `configuration_lookups`
  417. ADD CONSTRAINT `configuration_lookups_ibfk_1` FOREIGN KEY (`key`) REFERENCES `configuration` (`key`) ON DELETE CASCADE ON UPDATE CASCADE;
  418. --
  419. -- Constraints for table `emails`
  420. --
  421. ALTER TABLE `emails`
  422. ADD CONSTRAINT `emails_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  423. --
  424. -- Constraints for table `hosts`
  425. --
  426. ALTER TABLE `hosts`
  427. ADD CONSTRAINT `hosts_ibfk_1` FOREIGN KEY (`oper_id`) REFERENCES `opers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  428. --
  429. -- Constraints for table `opers`
  430. --
  431. ALTER TABLE `opers`
  432. ADD CONSTRAINT `opers_ibfk_2` FOREIGN KEY (`password_type_id`) REFERENCES `password_type` (`id`),
  433. ADD CONSTRAINT `opers_ibfk_4` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  434. ADD CONSTRAINT `opers_ibfk_5` FOREIGN KEY (`role_id`) REFERENCES `oper_roles` (`id`),
  435. ADD CONSTRAINT `opers_ibfk_6` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`);
  436. --
  437. -- Constraints for table `servers`
  438. --
  439. ALTER TABLE `servers`
  440. ADD CONSTRAINT `servers_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  441. ADD CONSTRAINT `servers_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  442. --
  443. -- Constraints for table `user_roles`
  444. --
  445. ALTER TABLE `user_roles`
  446. ADD CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  447. ADD CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`user_role_id`) REFERENCES `user_role_types` (`id`) ON UPDATE CASCADE;
  448. COMMIT;
  449. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  450. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  451. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;