181
|
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 name TEXT,
|
|
31 uuid TEXT,
|
183
|
32 compressedSize INTEGER,
|
181
|
33 uncompressedSize INTEGER,
|
|
34 compressionType INTEGER,
|
|
35 PRIMARY KEY(id, name)
|
|
36 );
|
|
37
|
183
|
38 CREATE TABLE Changes(
|
|
39 seq INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
40 changeType INTEGER,
|
|
41 publicId TEXT,
|
|
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
|
181
|
57 CREATE INDEX ChildrenIndex ON Resources(parentId);
|
|
58 CREATE INDEX PublicIndex ON Resources(publicId);
|
|
59
|
183
|
60 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id);
|
|
61 CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement);
|
|
62 CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY);
|
|
63
|
|
64 CREATE INDEX ChangesIndex ON Changes(publicId);
|
181
|
65
|
|
66 CREATE TRIGGER AttachedFileDeleted
|
|
67 AFTER DELETE ON AttachedFiles
|
|
68 BEGIN
|
|
69 SELECT SignalFileDeleted(old.uuid);
|
|
70 END;
|
|
71
|
|
72 CREATE TRIGGER ResourceDeleted
|
|
73 AFTER DELETE ON Resources
|
|
74 BEGIN
|
183
|
75 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType)
|
|
76 FROM Resources AS parent WHERE internalId = old.parentId;
|
181
|
77 END;
|
|
78
|
183
|
79 -- Delete a parent resource when its unique child is deleted
|
|
80 CREATE TRIGGER ResourceDeletedParentCleaning
|
|
81 AFTER DELETE ON Resources
|
|
82 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0
|
|
83 BEGIN
|
|
84 DELETE FROM Resources WHERE internalId = old.parentId;
|
|
85 END;
|