annotate OrthancServer/PrepareDatabase2.sql @ 182:93ff5babcaf8

children public id
author Sebastien Jodogne <s.jodogne@gmail.com>
date Mon, 12 Nov 2012 10:36:58 +0100
parents 2dece1526c06
children baada606da3c
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
181
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 CREATE TABLE GlobalProperties(
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2 name TEXT PRIMARY KEY,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 value TEXT
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4 );
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 CREATE TABLE Resources(
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 internalId INTEGER PRIMARY KEY AUTOINCREMENT,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 resourceType INTEGER,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 publicId TEXT,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10 parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11 );
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13 CREATE TABLE MainDicomTags(
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15 tagGroup INTEGER,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16 tagElement INTEGER,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 value TEXT,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18 PRIMARY KEY(id, tagGroup, tagElement)
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 );
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21 CREATE TABLE Metadata(
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 type INTEGER,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 value TEXT,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 PRIMARY KEY(id, type)
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26 );
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28 CREATE TABLE AttachedFiles(
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30 name TEXT,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31 uuid TEXT,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32 uncompressedSize INTEGER,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 compressionType INTEGER,
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
34 PRIMARY KEY(id, name)
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
35 );
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
36
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37 CREATE INDEX ChildrenIndex ON Resources(parentId);
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38 CREATE INDEX PublicIndex ON Resources(publicId);
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
41 CREATE TRIGGER AttachedFileDeleted
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
42 AFTER DELETE ON AttachedFiles
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
43 BEGIN
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
44 SELECT SignalFileDeleted(old.uuid);
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
45 END;
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
46
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
47 CREATE TRIGGER ResourceDeleted
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
48 AFTER DELETE ON Resources
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
49 BEGIN
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
50 SELECT SignalResourceDeleted(old.resourceType, old.parentId);
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
51 END;
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
52
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
53
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
54 -- -- Delete a resource when its unique child is deleted TODO TODO
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
55 -- CREATE TRIGGER ResourceRemovedUpward
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56 -- AFTER DELETE ON Resources
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
57 -- FOR EACH ROW
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
58 -- WHEN (SELECT COUNT(*) FROM ParentRelationship WHERE parent = old.
2dece1526c06 simplifying db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
59 -- END;