0
|
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 CREATE TABLE DicomIdentifiers(
|
|
22 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
23 tagGroup INTEGER,
|
|
24 tagElement INTEGER,
|
|
25 value TEXT,
|
|
26 PRIMARY KEY(id, tagGroup, tagElement)
|
|
27 );
|
|
28
|
|
29 CREATE TABLE Metadata(
|
|
30 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
31 type INTEGER,
|
|
32 value TEXT,
|
|
33 PRIMARY KEY(id, type)
|
|
34 );
|
|
35
|
|
36 CREATE TABLE AttachedFiles(
|
|
37 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
38 fileType INTEGER,
|
|
39 uuid TEXT,
|
|
40 compressedSize INTEGER,
|
|
41 uncompressedSize INTEGER,
|
|
42 compressionType INTEGER,
|
|
43 uncompressedHash TEXT,
|
|
44 compressedHash TEXT,
|
|
45 PRIMARY KEY(id, fileType)
|
|
46 );
|
|
47
|
|
48 CREATE TABLE Changes(
|
|
49 seq INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
50 changeType INTEGER,
|
|
51 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
52 resourceType INTEGER,
|
|
53 date TEXT
|
|
54 );
|
|
55
|
|
56 CREATE TABLE ExportedResources(
|
|
57 seq INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
58 resourceType INTEGER,
|
|
59 publicId TEXT,
|
|
60 remoteModality TEXT,
|
|
61 patientId TEXT,
|
|
62 studyInstanceUid TEXT,
|
|
63 seriesInstanceUid TEXT,
|
|
64 sopInstanceUid TEXT,
|
|
65 date TEXT
|
|
66 );
|
|
67
|
|
68 CREATE TABLE PatientRecyclingOrder(
|
|
69 seq INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
70 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE
|
|
71 );
|
|
72
|
|
73 CREATE INDEX ChildrenIndex ON Resources(parentId);
|
|
74 CREATE INDEX PublicIndex ON Resources(publicId);
|
|
75 CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
|
|
76 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
|
|
77
|
|
78 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id);
|
|
79 -- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up
|
|
80 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement);
|
|
81 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY);
|
|
82
|
|
83 -- The 3 following indexes were added in Orthanc 0.8.5 (database v5)
|
|
84 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
|
|
85 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
|
|
86 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY);
|
|
87
|
|
88 CREATE INDEX ChangesIndex ON Changes(internalId);
|
|
89
|
|
90
|
|
91
|
|
92 -- New tables wrt. Orthanc core
|
|
93 CREATE TABLE DeletedFiles(
|
|
94 uuid TEXT NOT NULL, -- 0
|
|
95 fileType INTEGER, -- 1
|
|
96 compressedSize INTEGER, -- 2
|
|
97 uncompressedSize INTEGER, -- 3
|
|
98 compressionType INTEGER, -- 4
|
|
99 uncompressedHash TEXT, -- 5
|
|
100 compressedHash TEXT -- 6
|
|
101 );
|
|
102
|
|
103 CREATE TABLE RemainingAncestor(
|
|
104 resourceType INTEGER NOT NULL,
|
|
105 publicId TEXT NOT NULL
|
|
106 );
|
|
107
|
|
108 CREATE TABLE DeletedResources(
|
|
109 resourceType INTEGER NOT NULL,
|
|
110 publicId TEXT NOT NULL
|
|
111 );
|
|
112 -- End of differences
|
|
113
|
|
114
|
|
115
|
|
116 CREATE TRIGGER AttachedFileDeleted
|
|
117 AFTER DELETE ON AttachedFiles
|
|
118 BEGIN
|
|
119 INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize,
|
|
120 old.uncompressedSize, old.compressionType,
|
|
121 old.uncompressedHash, old.compressedHash);
|
|
122 END;
|
|
123
|
|
124
|
|
125 CREATE TRIGGER ResourceDeleted
|
|
126 AFTER DELETE ON Resources
|
|
127 BEGIN
|
|
128 INSERT INTO DeletedResources VALUES(old.resourceType, old.publicId);
|
|
129 END;
|
|
130
|
|
131
|
|
132 -- Delete a parent resource when its unique child gets deleted
|
|
133 CREATE TRIGGER ResourceDeletedParentCleaning
|
|
134 AFTER DELETE ON Resources
|
|
135 FOR EACH ROW WHEN NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId)
|
|
136 BEGIN
|
|
137 DELETE FROM Resources WHERE internalId = old.parentId;
|
|
138 END;
|
|
139
|
|
140 -- Signal that the deleted resource has a remaining parent, if the
|
|
141 -- deleted resource has a sibling resource
|
|
142 CREATE TRIGGER ResourceRemainingAncestorFound
|
|
143 AFTER DELETE ON Resources
|
|
144 FOR EACH ROW WHEN EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId)
|
|
145 BEGIN
|
|
146 INSERT INTO RemainingAncestor(resourceType, publicId)
|
|
147 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId;
|
|
148 END;
|
|
149
|
|
150
|
|
151 CREATE TRIGGER PatientAdded
|
|
152 AFTER INSERT ON Resources
|
|
153 FOR EACH ROW WHEN new.resourceType = 0 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
|
|
154 BEGIN
|
|
155 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId);
|
|
156 END;
|