-
Notifications
You must be signed in to change notification settings - Fork 41
/
create_annotation_tables.sql
75 lines (73 loc) · 3.24 KB
/
create_annotation_tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
CREATE TABLE IF NOT EXISTS `annotations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`appId` varchar(255) DEFAULT NULL,
`sessionId` varchar(255) DEFAULT NULL,
`workerId` varchar(255) DEFAULT NULL,
`itemId` varchar(255) DEFAULT NULL,
`condition` varchar(255) DEFAULT NULL,
`data` mediumtext,
`preview_data` mediumtext,
`progress` float DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`notes` varchar(255) DEFAULT NULL,
`verified` boolean DEFAULT FALSE,
`imported` boolean DEFAULT FALSE,
`code` varchar(255) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`task` varchar(255) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`taskMode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_annotations_on_appId` (`appId`),
KEY `index_annotations_on_sessionId` (`sessionId`),
KEY `index_annotations_on_workerId` (`workerId`),
KEY `index_annotations_on_itemId` (`itemId`),
KEY `index_annotations_on_condition` (`condition`),
KEY `index_annotations_on_progress` (`progress`),
KEY `index_annotations_on_status` (`status`),
KEY `index_annotations_on_imported` (`imported`),
KEY `index_annotations_on_verified` (`verified`),
KEY `index_annotations_on_task` (`task`),
KEY `index_annotations_on_created_at` (`created_at`),
KEY `index_annotations_on_updated_at` (`updated_at`),
KEY `index_annotations_on_type` (`type`)
);
CREATE VIEW current_annotations AS
SELECT anns.id, anns.appId, anns.sessionId, anns.workerId, anns.itemId,
anns.condition, anns.data, anns.preview_data,
anns.progress, anns.status, anns.notes, anns.verified, anns.imported, anns.code,
anns.created_at, anns.updated_at, anns.task, anns.type, anns.taskMode,
x.ids
FROM (
SELECT itemId, max(id) as id, group_concat(id) as ids
FROM annotations group by itemId,task,type
) as x inner join annotations as anns on anns.itemId = x.itemId and anns.id = x.id;
CREATE TABLE IF NOT EXISTS `segment_annotations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`appId` varchar(255) DEFAULT NULL,
`sessionId` varchar(255) DEFAULT NULL,
`workerId` varchar(255) DEFAULT NULL,
`modelId` varchar(255) DEFAULT NULL,
`objectId` varchar(255) DEFAULT NULL,
`segments` mediumtext,
`label` varchar(255) DEFAULT NULL,
`labelType` varchar(255) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`notes` varchar(255) DEFAULT NULL,
`verified` boolean DEFAULT FALSE,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`annId` int(11) DEFAULT NULL,
`condition` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_segment_annotations_on_appId` (`appId`),
KEY `index_segment_annotations_on_sessionId` (`sessionId`),
KEY `index_segment_annotations_on_workerId` (`workerId`),
KEY `index_segment_annotations_on_modelId` (`modelId`),
KEY `index_segment_annotations_on_label` (`label`),
KEY `index_segment_annotations_on_labelType` (`labelType`),
KEY `index_segment_annotations_on_status` (`status`),
KEY `index_segment_annotations_on_condition` (`condition`),
KEY `index_segment_annotations_on_annId` (`annId`)
);