Mercurial > hg > orthanc

comparison OrthancServer/Sources/Database/PrepareDatabase.sql @ 4044:d25f4c0fa160 framework

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
splitting code into OrthancFramework and OrthancServer
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 10 Jun 2020 20:30:34 +0200
parents OrthancServer/Database/PrepareDatabase.sql@61da3c9b4121
children 28944db5318b
comparison
equal deleted inserted replaced
4043:6c6239aec462 4044:d25f4c0fa160
1 CREATE TABLE GlobalProperties(
2 property INTEGER PRIMARY KEY,
3 value TEXT
4 );
5
6 CREATE TABLE Resources(
7 internalId INTEGER PRIMARY KEY AUTOINCREMENT,
8 resourceType INTEGER,
9 publicId TEXT,
10 parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE
11 );
12
13 CREATE TABLE MainDicomTags(
14 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
15 tagGroup INTEGER,
16 tagElement INTEGER,
17 value TEXT,
18 PRIMARY KEY(id, tagGroup, tagElement)
19 );
20
21 -- The following table was added in Orthanc 0.8.5 (database v5)
22 CREATE TABLE DicomIdentifiers(
23 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
24 tagGroup INTEGER,
25 tagElement INTEGER,
26 value TEXT,
27 PRIMARY KEY(id, tagGroup, tagElement)
28 );
29
30 CREATE TABLE Metadata(
31 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
32 type INTEGER,
33 value TEXT,
34 PRIMARY KEY(id, type)
35 );
36
37 CREATE TABLE AttachedFiles(
38 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
39 fileType INTEGER,
40 uuid TEXT,
41 compressedSize INTEGER,
42 uncompressedSize INTEGER,
43 compressionType INTEGER,
44 uncompressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4)
45 compressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4)
46 PRIMARY KEY(id, fileType)
47 );
48
49 CREATE TABLE Changes(
50 seq INTEGER PRIMARY KEY AUTOINCREMENT,
51 changeType INTEGER,
52 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
53 resourceType INTEGER,
54 date TEXT
55 );
56
57 CREATE TABLE ExportedResources(
58 seq INTEGER PRIMARY KEY AUTOINCREMENT,
59 resourceType INTEGER,
60 publicId TEXT,
61 remoteModality TEXT,
62 patientId TEXT,
63 studyInstanceUid TEXT,
64 seriesInstanceUid TEXT,
65 sopInstanceUid TEXT,
66 date TEXT
67 );
68
69 CREATE TABLE PatientRecyclingOrder(
70 seq INTEGER PRIMARY KEY AUTOINCREMENT,
71 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE
72 );
73
74 CREATE INDEX ChildrenIndex ON Resources(parentId);
75 CREATE INDEX PublicIndex ON Resources(publicId);
76 CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
77 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
78
79 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id);
80 -- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up
81 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement);
82 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY);
83
84 -- The 3 following indexes were added in Orthanc 0.8.5 (database v5)
85 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
86 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
87 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY);
88
89 CREATE INDEX ChangesIndex ON Changes(internalId);
90
91 CREATE TRIGGER AttachedFileDeleted
92 AFTER DELETE ON AttachedFiles
93 BEGIN
94 SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize,
95 old.compressionType, old.compressedSize,
96 -- These 2 arguments are new in Orthanc 0.7.3 (database v4)
97 old.uncompressedMD5, old.compressedMD5);
98 END;
99
100 CREATE TRIGGER ResourceDeleted
101 AFTER DELETE ON Resources
102 BEGIN
103 SELECT SignalResourceDeleted(old.publicId, old.resourceType); -- New in Orthanc 0.8.5 (db v5)
104 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType)
105 FROM Resources AS parent WHERE internalId = old.parentId;
106 END;
107
108 -- Delete a parent resource when its unique child is deleted
109 CREATE TRIGGER ResourceDeletedParentCleaning
110 AFTER DELETE ON Resources
111 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0
112 BEGIN
113 DELETE FROM Resources WHERE internalId = old.parentId;
114 END;
115
116 CREATE TRIGGER PatientAdded
117 AFTER INSERT ON Resources
118 FOR EACH ROW WHEN new.resourceType = 1 -- "1" corresponds to "ResourceType_Patient" in C++
119 BEGIN
120 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId);
121 END;
122
123
124 -- Set the version of the database schema
125 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration
126 INSERT INTO GlobalProperties VALUES (1, "6");