Mercurial > hg > orthanc
comparison OrthancServer/PrepareDatabase.sql @ 203:9283552c25df
db refactoring done
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Tue, 27 Nov 2012 17:50:24 +0100 |
parents | OrthancServer/PrepareDatabase2.sql@530a25320461 |
children | 4453a010d0db |
comparison
equal
deleted
inserted
replaced
202:1650557bd81a | 203:9283552c25df |
---|---|
1 CREATE TABLE GlobalProperties( | |
2 name TEXT 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 Metadata( | |
22 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
23 type INTEGER, | |
24 value TEXT, | |
25 PRIMARY KEY(id, type) | |
26 ); | |
27 | |
28 CREATE TABLE AttachedFiles( | |
29 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
30 fileType INTEGER, | |
31 uuid TEXT, | |
32 compressedSize INTEGER, | |
33 uncompressedSize INTEGER, | |
34 compressionType INTEGER, | |
35 PRIMARY KEY(id, fileType) | |
36 ); | |
37 | |
38 CREATE TABLE Changes( | |
39 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
40 changeType INTEGER, | |
41 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
42 resourceType INTEGER, | |
43 date TEXT | |
44 ); | |
45 | |
46 CREATE TABLE ExportedInstances( | |
47 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
48 remoteModality TEXT, | |
49 publicId TEXT, | |
50 patientId TEXT, | |
51 studyInstanceUid TEXT, | |
52 seriesInstanceUid TEXT, | |
53 sopInstanceUid TEXT, | |
54 date TEXT | |
55 ); | |
56 | |
57 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
58 CREATE INDEX PublicIndex ON Resources(publicId); | |
59 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
60 | |
61 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); | |
62 CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); | |
63 CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); | |
64 | |
65 CREATE INDEX ChangesIndex ON Changes(internalId); | |
66 | |
67 CREATE TRIGGER AttachedFileDeleted | |
68 AFTER DELETE ON AttachedFiles | |
69 BEGIN | |
70 SELECT SignalFileDeleted(old.uuid); | |
71 END; | |
72 | |
73 CREATE TRIGGER ResourceDeleted | |
74 AFTER DELETE ON Resources | |
75 BEGIN | |
76 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) | |
77 FROM Resources AS parent WHERE internalId = old.parentId; | |
78 END; | |
79 | |
80 -- Delete a parent resource when its unique child is deleted | |
81 CREATE TRIGGER ResourceDeletedParentCleaning | |
82 AFTER DELETE ON Resources | |
83 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
84 BEGIN | |
85 DELETE FROM Resources WHERE internalId = old.parentId; | |
86 END; |