Mercurial > hg > orthanc

comparison OrthancServer/PrepareDatabase.sql @ 203:9283552c25df

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