Mercurial > hg > orthanc-databases

annotate Odbc/Plugins/PrepareIndex.sql @ 370:d2b5d9c92214 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
PG: test feature: configurable transaction isolation level
author Alain Mazy <am@osimis.io>
date Wed, 22 Feb 2023 16:52:04 +0100
parents b5fb8b77ce4d
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
329
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 CREATE TABLE GlobalProperties(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2 property INTEGER PRIMARY KEY,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 value ${LONGTEXT}
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 CREATE TABLE ServerProperties(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 server VARCHAR(64) NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 property INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 value ${LONGTEXT},
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10 PRIMARY KEY(server, property)
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13 CREATE TABLE Resources(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14 internalId ${AUTOINCREMENT_TYPE},
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15 resourceType INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16 publicId VARCHAR(64) NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 parentId BIGINT
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 CREATE TABLE MainDicomTags(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21 id BIGINT NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22 tagGroup INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 tagElement INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 value VARCHAR(255),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 PRIMARY KEY(id, tagGroup, tagElement),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26 CONSTRAINT MainDicomTags1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29 CREATE TABLE DicomIdentifiers(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30 id BIGINT NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31 tagGroup INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32 tagElement INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 value VARCHAR(255),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
34 PRIMARY KEY(id, tagGroup, tagElement),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
35 CONSTRAINT DicomIdentifiers1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
36 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38 CREATE TABLE Metadata(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39 id BIGINT NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40 type INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
41 value ${LONGTEXT},
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
42 revision INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
43 PRIMARY KEY(id, type),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
44 CONSTRAINT Metadata1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
45 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
46
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
47 CREATE TABLE AttachedFiles(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
48 id BIGINT NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
49 fileType INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
50 uuid VARCHAR(64) NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
51 compressedSize BIGINT,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
52 uncompressedSize BIGINT,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
53 compressionType INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
54 uncompressedHash VARCHAR(40),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
55 compressedHash VARCHAR(40),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56 revision INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
57 PRIMARY KEY(id, fileType),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
58 CONSTRAINT AttachedFiles1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
59 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
60
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
61 CREATE TABLE Changes(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
62 seq ${AUTOINCREMENT_TYPE},
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
63 changeType INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
64 internalId BIGINT NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
65 resourceType INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
66 date VARCHAR(64),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
67 CONSTRAINT Changes1 FOREIGN KEY (internalId) REFERENCES Resources(internalId) ON DELETE CASCADE
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
68 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
69
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
70 CREATE TABLE ExportedResources(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
71 seq ${AUTOINCREMENT_TYPE},
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
72 resourceType INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
73 publicId VARCHAR(64),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
74 remoteModality VARCHAR(64),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
75 patientId VARCHAR(64),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
76 studyInstanceUid VARCHAR(128),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
77 seriesInstanceUid VARCHAR(128),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
78 sopInstanceUid VARCHAR(128),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
79 date VARCHAR(64)
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
80 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
81
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
82 CREATE TABLE PatientRecyclingOrder(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
83 seq ${AUTOINCREMENT_TYPE},
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
84 patientId BIGINT NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
85 CONSTRAINT PatientRecyclingOrder1 FOREIGN KEY (patientId) REFERENCES Resources(internalId) ON DELETE CASCADE
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
86 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
87
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
88 CREATE INDEX ChildrenIndex ON Resources(parentId);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
89 CREATE INDEX PublicIndex ON Resources(publicId);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
90 CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
91 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
92
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
93 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
94 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
95 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
96 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
97
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
98 CREATE INDEX ChangesIndex ON Changes(internalId);
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
99
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
100
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
101
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
102 -- New tables wrt. Orthanc core
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
103 CREATE TABLE DeletedFiles( -- Same structure as AttachedFiles
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
104 id BIGINT NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
105 fileType INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
106 uuid VARCHAR(64) NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
107 compressedSize BIGINT,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
108 uncompressedSize BIGINT,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
109 compressionType INTEGER,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
110 uncompressedHash VARCHAR(40),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
111 compressedHash VARCHAR(40),
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
112 revision INTEGER
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
113 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
114
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
115 CREATE TABLE DeletedResources(
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
116 internalId BIGINT NOT NULL PRIMARY KEY,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
117 resourceType INTEGER NOT NULL,
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
118 publicId VARCHAR(64) NOT NULL
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
119 );
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
120
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
121
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
122
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
123 -- Set version of database to 6
b5fb8b77ce4d initial commit of ODBC framework
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
124 INSERT INTO GlobalProperties VALUES(1, '6');