· 8 years ago · Jun 12, 2018, 02:34 PM
1BEGIN TRANSACTION;
2CREATE TABLE IF NOT EXISTS `migrations` (
3 `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
4 `migration` varchar NOT NULL,
5 `batch` integer NOT NULL
6);
7INSERT INTO `migrations` VALUES (1,'2018_06_08_163535_create_Configurations_table',1);
8INSERT INTO `migrations` VALUES (2,'2018_06_08_163535_create_Departments_table',1);
9INSERT INTO `migrations` VALUES (3,'2018_06_08_163536_create_Organizations_table',1);
10INSERT INTO `migrations` VALUES (4,'2018_06_08_163536_create_devices_table',1);
11INSERT INTO `migrations` VALUES (5,'2018_06_08_163537_create_PoolsInDepartments_table',1);
12INSERT INTO `migrations` VALUES (6,'2018_06_08_163537_create_Pools_table',1);
13CREATE TABLE IF NOT EXISTS `devices` (
14 `id` integer NOT NULL,
15 `serial_number` varchar,
16 `organizations_id` integer NOT NULL,
17 FOREIGN KEY(`organizations_id`) REFERENCES `Organizations`(`id`) on delete NO ACTION on update NO ACTION,
18 PRIMARY KEY(`id`,`organizations_id`)
19);
20CREATE TABLE IF NOT EXISTS `PoolsInDepartments` (
21 `pools_id` integer NOT NULL,
22 `departaments_id` integer NOT NULL,
23 FOREIGN KEY(`departaments_id`) REFERENCES `Departments`(`id`) on delete NO ACTION on update NO ACTION,
24 PRIMARY KEY(`pools_id`,`departaments_id`),
25 FOREIGN KEY(`pools_id`) REFERENCES `Pools`(`id`) on delete NO ACTION on update NO ACTION
26);
27CREATE TABLE IF NOT EXISTS `Pools` (
28 `id` integer NOT NULL,
29 `configurations_id` integer NOT NULL,
30 `owner_organizations_id` integer NOT NULL,
31 `name` TEXT,
32 FOREIGN KEY(`owner_organizations_id`) REFERENCES `Organizations`(`id`) on delete NO ACTION on update NO ACTION,
33 FOREIGN KEY(`configurations_id`) REFERENCES `Configurations`(`id`) on delete NO ACTION on update NO ACTION,
34 PRIMARY KEY(`id`,`configurations_id`,`owner_organizations_id`)
35);
36CREATE TABLE IF NOT EXISTS `Organizations` (
37 `id` integer NOT NULL,
38 `name` TEXT,
39 PRIMARY KEY(`id`)
40);
41CREATE TABLE IF NOT EXISTS `Departments` (
42 `id` integer NOT NULL,
43 `organizations_id` integer NOT NULL,
44 `default_departament` integer,
45 `name` TEXT,
46 FOREIGN KEY(`organizations_id`) REFERENCES `Organizations`(`id`) on delete NO ACTION on update NO ACTION,
47 PRIMARY KEY(`id`,`organizations_id`)
48);
49CREATE TABLE IF NOT EXISTS `Configurations` (
50 `id` integer NOT NULL,
51 `name` TEXT,
52 `options` TEXT,
53 PRIMARY KEY(`id`)
54);
55INSERT INTO `Configurations` VALUES (1,NULL,NULL);
56INSERT INTO `Configurations` VALUES (2,NULL,NULL);
57CREATE INDEX IF NOT EXISTS `fk_devices_Organizations1_idx` ON `devices` (
58 `organizations_id`
59);
60CREATE INDEX IF NOT EXISTS `fk_Pools_Organizations1_idx` ON `Pools` (
61 `owner_organizations_id`
62);
63CREATE INDEX IF NOT EXISTS `fk_Pools_Configurations1_idx` ON `Pools` (
64 `configurations_id`
65);
66CREATE INDEX IF NOT EXISTS `fk_PoolsInDepartament_Pools` ON `PoolsInDepartments` (
67 `pools_id`
68);
69CREATE INDEX IF NOT EXISTS `fk_PoolsInDepartament_Departament` ON `PoolsInDepartments` (
70 `departaments_id`
71);
72CREATE INDEX IF NOT EXISTS `fk_Departments_Organizations1_idx` ON `Departments` (
73 `organizations_id`
74);
75COMMIT;