Mercurial > hg > orthanc

comparison OrthancServer/PrepareDatabase2.sql @ 181:2dece1526c06

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
simplifying db schema
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 09 Nov 2012 18:09:19 +0100
parents
children baada606da3c
comparison
equal deleted inserted replaced
180:626777d01dc4 181:2dece1526c06
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,
32 uncompressedSize INTEGER,
33 compressionType INTEGER,
34 PRIMARY KEY(id, name)
35 );
36
37 CREATE INDEX ChildrenIndex ON Resources(parentId);
38 CREATE INDEX PublicIndex ON Resources(publicId);
39
40
41 CREATE TRIGGER AttachedFileDeleted
42 AFTER DELETE ON AttachedFiles
43 BEGIN
44 SELECT SignalFileDeleted(old.uuid);
45 END;
46
47 CREATE TRIGGER ResourceDeleted
48 AFTER DELETE ON Resources
49 BEGIN
50 SELECT SignalResourceDeleted(old.resourceType, old.parentId);
51 END;
52
53
54 -- -- Delete a resource when its unique child is deleted TODO TODO
55 -- CREATE TRIGGER ResourceRemovedUpward
56 -- AFTER DELETE ON Resources
57 -- FOR EACH ROW
58 -- WHEN (SELECT COUNT(*) FROM ParentRelationship WHERE parent = old.
59 -- END;