Mercurial > hg > orthanc
comparison OrthancServer/PrepareDatabase2.sql @ 181:2dece1526c06
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; |