1 statements = """--"
2 -- about NULL values and foreign keys
3 -- ==================================
4 --
5 -- we never use NULL values because they have a special behaviour with respect
6 -- to comparisons (they are not even equal to themselves)
7 --
8 -- the only case where this special behaviour may be useful to us is w.r.t.
9 -- foreign keys, which are not bound to hold for NULL values.
10 -- however, since foreign keys are not checked by Sqlite, we use empty strings
11 -- instead of NULL values, and declare foreign keys even if they are to be
12 -- violated by empty strings
13
14 CREATE TABLE Version (
15 version TEXT PRIMARY KEY
16 -- will contain a single line with the backend version used to create this file
17 );--cut
18
19 CREATE TABLE Packages (
20 id TEXT PRIMARY KEY,
21 uri TEXT NOT NULL,
22 url TEXT NOT NULL
23 -- column url is set when binding a pattern, to remember with which URL the
24 -- package was open
25 );--cut
26
27 CREATE TABLE Elements (
28 package TEXT NOT NULL,
29 id TEXT NOT NULL,
30 typ TEXT NOT NULL,
31 PRIMARY KEY (package, id)
32 FOREIGN KEY (package) references Packages (id)
33 -- type must be m,a,r,t,l,q,v,R,i
34 );--cut
35
36 CREATE TABLE Meta (
37 package TEXT NOT NULL,
38 element TEXT NOT NULL,
39 key TEXT NOT NULL,
40 value TEXT NOT NULL, -- text value
41 value_p TEXT NOT NULL, -- element value (prefix)
42 value_i TEXT NOT NULL, -- element value (identifier)
43 PRIMARY KEY (package, element, key),
44 FOREIGN KEY (package) references Packages (id)
45 -- if column element is not an empty string,
46 -- then it must reference Elements (package, id)
47 -- else, the metadata is about the package itself
48 --
49 -- either value or value_i must be empty
50 );--cut
51
52 CREATE TABLE Contents (
53 package TEXT NOT NULL,
54 element TEXT NOT NULL,
55 mimetype TEXT NOT NULL,
56 model_p TEXT NOT NULL,
57 model_i TEXT NOT NULL,
58 url TEXT NOT NULL,
59 data BLOB NOT NULL,
60 PRIMARY KEY (package, element),
61 FOREIGN KEY (package, element) references Elements (package, id),
62 -- the following foreign key may be violated by empty strings in model_p
63 FOREIGN KEY (package, model_p) references Imports (package, id)
64 -- only elements with a content should be referenced by element
65 -- if not empty, model_i must identify an own or directly imported (from
66 -- model_p) resource
67 );--cut
68
69 CREATE TABLE Medias (
70 package TEXT NOT NULL,
71 id TEXT NOT NULL,
72 url TEXT NOT NULL,
73 foref TEXT NOT NULL, -- Frame Of REFerence
74 PRIMARY KEY (package, id),
75 FOREIGN KEY (package, id) references Elements (package, id)
76 -- typ of the referenced element must me 'm'
77 );--cut
78
79 CREATE TABLE Annotations (
80 package TEXT NOT NULL,
81 id TEXT NOT NULL,
82 media_p TEXT NOT NULL,
83 media_i TEXT NOT NULL,
84 fbegin INT NOT NULL,
85 fend INT NOT NULL,
86 PRIMARY KEY (package, id),
87 FOREIGN KEY (package, id) references Elements (package, id),
88 -- the following foreign key may be violated by empty strings in media_p
89 FOREIGN KEY (package, media_p) references Imports (package, id)
90 -- typ of the referenced element must me 'a'
91 -- Annotations must have a content
92 -- media_i must be the id of an own or directly imported (from media_p) media
93 );--cut
94
95 CREATE TABLE RelationMembers (
96 package TEXT NOT NULL,
97 relation TEXT NOT NULL,
98 ord INT NOT NULL,
99 member_p TEXT NOT NULL,
100 member_i TEXT NOT NULL,
101 PRIMARY KEY (package, relation, ord),
102 FOREIGN KEY (package, relation) REFERENCES Relations (package, id),
103 -- the following foreign key may be violated by empty strings in member_p
104 FOREIGN KEY (package, member_p) REFERENCES Imports (package, id)
105 -- typ of the referenced element must me 'r'
106 -- for each relation, ord should be a consecutive sequence starting from 0
107 -- member_i must be the id of an own or directly imported (from member_p)
108 -- annotation
109 );--cut
110
111 CREATE TABLE ListItems (
112 package TEXT NOT NULL,
113 list TEXT NOT NULL,
114 ord INT NOT NULL,
115 item_p TEXT NOT NULL,
116 item_i TEXT NOT NULL,
117 PRIMARY KEY (package, list, ord),
118 FOREIGN KEY (package, list) references Elements (package, id),
119 -- the following foreign key may be violated by empty strings in item_p
120 FOREIGN KEY (package, item_p) references Imports (package, id)
121 -- typ of the referenced element must me 'l'
122 -- for each list, ord must be a consecutive sequence starting from 0
123 -- item_i must be the id of an own or directly imported (from item_p)
124 -- element
125 );--cut
126
127 CREATE TABLE Imports (
128 package TEXT NOT NULL,
129 id TEXT NOT NULL,
130 url TEXT NOT NULL,
131 uri TEXT NOT NULL,
132 PRIMARY KEY (package, id),
133 FOREIGN KEY (package, id) references Elements (package, id)
134 -- typ of the referenced element must me 'i'
135 -- if not empty, uri should be the uri of the directly imported package
136 );--cut
137
138 CREATE TABLE Tagged (
139 package TEXT NOT NULL,
140 element_p TEXT NOT NULL,
141 element_i TEXT NOT NULL,
142 tag_p TEXT NOT NULL,
143 tag_i TEXT NOT NULL,
144 PRIMARY KEY (package, element_p, element_i, tag_p, tag_i),
145 -- the following foreign key may be violated by empty strings in *_p
146 FOREIGN KEY (package, element_p) references Imports (package, id),
147 FOREIGN KEY (package, tag_p) references Imports (package, id)
148 -- element_i must be the id of an own or directly imported (from
149 -- element_p) element
150 -- tag_i must be the id of an own or directly imported (from tag_p) tag
151 );--cut
152
153 CREATE VIEW UriBases AS
154 SELECT id as package, "" AS prefix,
155 CASE uri WHEN "" THEN url ELSE uri END AS uri_base
156 FROM Packages
157 UNION
158 SELECT package, id AS prefix,
159 CASE uri WHEN "" THEN url ELSE uri END AS uri_base
160 FROM Imports
161 ;--cut""".split(";--cut")[:-1]
162