· 6 years ago · Oct 14, 2019, 05:52 PM
1PROPERTY SQL:
2CREATE TABLE IF NOT EXISTS `file` (
3 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
4
5 `name` VARCHAR(255) NOT NULL,
6 `path` VARCHAR(255) NOT NULL,
7 `mime` VARCHAR(255) NOT NULL,
8 `type` ENUM ('file', 'image', 'video') DEFAULT 'file' NOT NULL,
9 `category` ENUM ('panorama', 'floor_plan', '360', '3d', 'none') DEFAULT 'none' NOT NULL,
10 `primary` TINYINT(1) DEFAULT 0 NOT NULL,
11 `size` INT(11) NOT NULL,
12 `width` INT(11) DEFAULT NULL,
13 `height` INT(11) DEFAULT NULL,
14 `description` TEXT DEFAULT NULL,
15
16 `client_id` INT(10) UNSIGNED DEFAULT NULL,
17 `offer_id` INT(10) UNSIGNED DEFAULT NULL,
18 `demand_id` INT(10) UNSIGNED DEFAULT NULL,
19 `user_id` INT(10) UNSIGNED DEFAULT NULL,
20 `task_id` INT(10) UNSIGNED DEFAULT NULL,
21 `company_id` INT(10) UNSIGNED DEFAULT NULL,
22 `office_id` INT(10) UNSIGNED DEFAULT NULL,
23 `note_id` INT(10) UNSIGNED DEFAULT NULL,
24 `attribute_id` INT(10) UNSIGNED DEFAULT NULL,
25 `document_id` INT(10) UNSIGNED DEFAULT NULL,
26
27 `created` DATETIME DEFAULT CURRENT_TIMESTAMP() NOT NULL,
28 `created_by` INT(10) UNSIGNED NOT NULL,
29 `saved_ts` TIMESTAMP NOT NULL,
30 `active` TINYINT(2) DEFAULT 1 NOT NULL,
31
32 CONSTRAINT `file_client_fk`
33 FOREIGN KEY (`client_id`) REFERENCES `client`(`id`),
34 CONSTRAINT `file_offer_fk`
35 FOREIGN KEY (`offer_id`) REFERENCES `offer`(`id`),
36 CONSTRAINT `file_demand_fk`
37 FOREIGN KEY (`demand_id`) REFERENCES `demand`(`id`),
38 CONSTRAINT `file_user_fk`
39 FOREIGN KEY (`user_id`) REFERENCES `user`(`id`),
40 CONSTRAINT `file_task_fk`
41 FOREIGN KEY (`task_id`) REFERENCES `task`(`id`),
42 CONSTRAINT `file_note_fk`
43 FOREIGN KEY (`note_id`) REFERENCES `note`(`id`),
44 CONSTRAINT `file_attribute_fk`
45 FOREIGN KEY (`attribute_id`) REFERENCES `attribute`(`id`),
46 CONSTRAINT `file_created_by_fk`
47 FOREIGN KEY (`created_by`) REFERENCES `user`(`id`),
48 CONSTRAINT `file_company_fk`
49 FOREIGN KEY (`company_id`) REFERENCES `company`(`id`),
50 CONSTRAINT `file_office_fk`
51 FOREIGN KEY (`office_id`) REFERENCES `office`(`id`),
52 CONSTRAINT `file_document_fk`
53 FOREIGN KEY (`document_id`) REFERENCES `document`(`id`),
54
55 INDEX `file_active_index`(`active`),
56 PRIMARY KEY (`id`)
57)
58 ENGINE = InnoDB
59 CHARACTER SET `utf8mb4`
60 COLLATE `utf8mb4_general_ci`;
61
62ENTITY SQL:
63CREATE TABLE IF NOT EXISTS `document` (
64 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
65 `top_level_id` INT(10) UNSIGNED NOT NULL,
66 `number` INT(10) UNSIGNED NOT NULL,
67 `owner_id` INT(10) UNSIGNED NOT NULL,
68 `note` TEXT DEFAULT NULL,
69
70 `name` VARCHAR(255) UNSIGNED NOT NULL,
71 `description` VARCHAR(255) UNSIGNED NOT NULL,
72
73 `created` DATETIME DEFAULT CURRENT_TIMESTAMP() NOT NULL,
74 `created_by` INT(10) UNSIGNED NOT NULL,
75 `saved_ts` TIMESTAMP NOT NULL,
76 `active` TINYINT(2) DEFAULT 1 NOT NULL,
77
78 CONSTRAINT `document_top_level_id_fk`
79 FOREIGN KEY (`top_level_id`) REFERENCES `user`(`id`),
80 CONSTRAINT `document_owner_id_fk`
81 FOREIGN KEY (`owner_id`) REFERENCES `user`(`id`),
82 CONSTRAINT `document_created_by_fk`
83 FOREIGN KEY (`created_by`) REFERENCES `user`(`id`),
84
85 INDEX `document_active_index`(`active`),
86 PRIMARY KEY (`id`)
87)
88 ENGINE = INNODB
89 CHARACTER SET `utf8mb4`
90 COLLATE `utf8mb4_general_ci`;
91
92ENTITY HHISTORY:
93CREATE TABLE IF NOT EXISTS `document_history`
94 LIKE `document`;
95ALTER TABLE `document_history`
96 DROP `top_level_id`,
97 DROP `owner_id`,
98 DROP `active`,
99 DROP `saved_ts`,
100 DROP INDEX `document_active_index`,
101 ADD `document_id` INT(10) UNSIGNED NOT NULL
102 AFTER `id`,
103 ADD CONSTRAINT `document_history_document_fk`
104 FOREIGN KEY (`document_id`) REFERENCES `document`(`id`),
105 ADD CONSTRAINT `document_history_created_by_fk`
106 FOREIGN KEY (`created_by`) REFERENCES `user`(`id`);