Mercurial > hg > orthanc-databases

comparison Odbc/Plugins/PrepareIndex.sql @ 329:b5fb8b77ce4d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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');