Mercurial > hg > orthanc-databases
comparison Odbc/Plugins/PrepareIndex.sql @ 329:b5fb8b77ce4d
initial commit of ODBC framework
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Tue, 10 Aug 2021 20:08:53 +0200 |
parents | |
children |
comparison
equal
deleted
inserted
replaced
328:6a49c495c940 | 329:b5fb8b77ce4d |
---|---|
1 CREATE TABLE GlobalProperties( | |
2 property INTEGER PRIMARY KEY, | |
3 value ${LONGTEXT} | |
4 ); | |
5 | |
6 CREATE TABLE ServerProperties( | |
7 server VARCHAR(64) NOT NULL, | |
8 property INTEGER NOT NULL, | |
9 value ${LONGTEXT}, | |
10 PRIMARY KEY(server, property) | |
11 ); | |
12 | |
13 CREATE TABLE Resources( | |
14 internalId ${AUTOINCREMENT_TYPE}, | |
15 resourceType INTEGER NOT NULL, | |
16 publicId VARCHAR(64) NOT NULL, | |
17 parentId BIGINT | |
18 ); | |
19 | |
20 CREATE TABLE MainDicomTags( | |
21 id BIGINT NOT NULL, | |
22 tagGroup INTEGER NOT NULL, | |
23 tagElement INTEGER NOT NULL, | |
24 value VARCHAR(255), | |
25 PRIMARY KEY(id, tagGroup, tagElement), | |
26 CONSTRAINT MainDicomTags1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
27 ); | |
28 | |
29 CREATE TABLE DicomIdentifiers( | |
30 id BIGINT NOT NULL, | |
31 tagGroup INTEGER NOT NULL, | |
32 tagElement INTEGER NOT NULL, | |
33 value VARCHAR(255), | |
34 PRIMARY KEY(id, tagGroup, tagElement), | |
35 CONSTRAINT DicomIdentifiers1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
36 ); | |
37 | |
38 CREATE TABLE Metadata( | |
39 id BIGINT NOT NULL, | |
40 type INTEGER NOT NULL, | |
41 value ${LONGTEXT}, | |
42 revision INTEGER, | |
43 PRIMARY KEY(id, type), | |
44 CONSTRAINT Metadata1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
45 ); | |
46 | |
47 CREATE TABLE AttachedFiles( | |
48 id BIGINT NOT NULL, | |
49 fileType INTEGER, | |
50 uuid VARCHAR(64) NOT NULL, | |
51 compressedSize BIGINT, | |
52 uncompressedSize BIGINT, | |
53 compressionType INTEGER, | |
54 uncompressedHash VARCHAR(40), | |
55 compressedHash VARCHAR(40), | |
56 revision INTEGER, | |
57 PRIMARY KEY(id, fileType), | |
58 CONSTRAINT AttachedFiles1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
59 ); | |
60 | |
61 CREATE TABLE Changes( | |
62 seq ${AUTOINCREMENT_TYPE}, | |
63 changeType INTEGER, | |
64 internalId BIGINT NOT NULL, | |
65 resourceType INTEGER, | |
66 date VARCHAR(64), | |
67 CONSTRAINT Changes1 FOREIGN KEY (internalId) REFERENCES Resources(internalId) ON DELETE CASCADE | |
68 ); | |
69 | |
70 CREATE TABLE ExportedResources( | |
71 seq ${AUTOINCREMENT_TYPE}, | |
72 resourceType INTEGER, | |
73 publicId VARCHAR(64), | |
74 remoteModality VARCHAR(64), | |
75 patientId VARCHAR(64), | |
76 studyInstanceUid VARCHAR(128), | |
77 seriesInstanceUid VARCHAR(128), | |
78 sopInstanceUid VARCHAR(128), | |
79 date VARCHAR(64) | |
80 ); | |
81 | |
82 CREATE TABLE PatientRecyclingOrder( | |
83 seq ${AUTOINCREMENT_TYPE}, | |
84 patientId BIGINT NOT NULL, | |
85 CONSTRAINT PatientRecyclingOrder1 FOREIGN KEY (patientId) REFERENCES Resources(internalId) ON DELETE CASCADE | |
86 ); | |
87 | |
88 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
89 CREATE INDEX PublicIndex ON Resources(publicId); | |
90 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
91 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | |
92 | |
93 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id); | |
94 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); | |
95 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); | |
96 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value); | |
97 | |
98 CREATE INDEX ChangesIndex ON Changes(internalId); | |
99 | |
100 | |
101 | |
102 -- New tables wrt. Orthanc core | |
103 CREATE TABLE DeletedFiles( -- Same structure as AttachedFiles | |
104 id BIGINT NOT NULL, | |
105 fileType INTEGER, | |
106 uuid VARCHAR(64) NOT NULL, | |
107 compressedSize BIGINT, | |
108 uncompressedSize BIGINT, | |
109 compressionType INTEGER, | |
110 uncompressedHash VARCHAR(40), | |
111 compressedHash VARCHAR(40), | |
112 revision INTEGER | |
113 ); | |
114 | |
115 CREATE TABLE DeletedResources( | |
116 internalId BIGINT NOT NULL PRIMARY KEY, | |
117 resourceType INTEGER NOT NULL, | |
118 publicId VARCHAR(64) NOT NULL | |
119 ); | |
120 | |
121 | |
122 | |
123 -- Set version of database to 6 | |
124 INSERT INTO GlobalProperties VALUES(1, '6'); |