1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
|
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE uri (
id INTEGER PRIMARY KEY,
value VARCHAR UNIQUE
);
INSERT INTO "uri" VALUES(1,'file:///tmp');
INSERT INTO "uri" VALUES(2,'http://www.google.de');
INSERT INTO "uri" VALUES(3,'belly');
INSERT INTO "uri" VALUES(5,'file:///tmp/foo.txt');
INSERT INTO "uri" VALUES(6,'big bang');
CREATE TABLE interpretation (
id INTEGER PRIMARY KEY,
value VARCHAR UNIQUE
);
INSERT INTO "interpretation" VALUES(1,'stfu:OpenEvent');
INSERT INTO "interpretation" VALUES(2,'stfu:Document');
INSERT INTO "interpretation" VALUES(3,'stfu:ShalalalalaEvent');
INSERT INTO "interpretation" VALUES(4,'stfu:Image');
INSERT INTO "interpretation" VALUES(5,'stfu:FoobarEvent');
INSERT INTO "interpretation" VALUES(6,'stfu:Test');
CREATE TABLE manifestation (
id INTEGER PRIMARY KEY,
value VARCHAR UNIQUE
);
INSERT INTO "manifestation" VALUES(1,'stfu:YourActivity');
INSERT INTO "manifestation" VALUES(2,'http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#RemoteDataObject');
INSERT INTO "manifestation" VALUES(3,'stfu:BooActivity');
INSERT INTO "manifestation" VALUES(4,'stfu:Ethereal');
INSERT INTO "manifestation" VALUES(5,'stfu:SomeActivity');
CREATE TABLE mimetype (
id INTEGER PRIMARY KEY,
value VARCHAR UNIQUE
);
INSERT INTO "mimetype" VALUES(1,'meat/raw');
INSERT INTO "mimetype" VALUES(2,'text/plain');
CREATE TABLE actor (
id INTEGER PRIMARY KEY,
value VARCHAR UNIQUE
);
INSERT INTO "actor" VALUES(1,'firefox');
INSERT INTO "actor" VALUES(2,'geany');
INSERT INTO "actor" VALUES(3,'gedit');
CREATE TABLE text (
id INTEGER PRIMARY KEY,
value VARCHAR UNIQUE
);
INSERT INTO "text" VALUES(1,'this item has not text... rly!');
CREATE TABLE payload
(id INTEGER PRIMARY KEY, value BLOB);
CREATE TABLE storage (
id INTEGER PRIMARY KEY,
value VARCHAR UNIQUE,
state INTEGER,
icon VARCHAR,
display_name VARCHAR
);
INSERT INTO "storage" VALUES(1,'net',1,'stock_internet','Internet');
INSERT INTO "storage" VALUES(2,'368c991f-8b59-4018-8130-3ce0ec944157',NULL,NULL,NULL);
CREATE TABLE event (
id INTEGER,
timestamp INTEGER,
interpretation INTEGER,
manifestation INTEGER,
actor INTEGER,
payload INTEGER,
subj_id INTEGER,
subj_interpretation INTEGER,
subj_manifestation INTEGER,
subj_origin INTEGER,
subj_mimetype INTEGER,
subj_text INTEGER,
subj_storage INTEGER,
origin INTEGER,
subj_id_current INTEGER,
CONSTRAINT interpretation_fk
FOREIGN KEY(interpretation)
REFERENCES interpretation(id)
ON DELETE CASCADE,
CONSTRAINT manifestation_fk
FOREIGN KEY(manifestation)
REFERENCES manifestation(id)
ON DELETE CASCADE,
CONSTRAINT actor_fk
FOREIGN KEY(actor)
REFERENCES actor(id)
ON DELETE CASCADE,
CONSTRAINT origin_fk
FOREIGN KEY(origin)
REFERENCES uri(id)
ON DELETE CASCADE,
CONSTRAINT payload_fk
FOREIGN KEY(payload)
REFERENCES payload(id)
ON DELETE CASCADE,
CONSTRAINT subj_id_fk
FOREIGN KEY(subj_id)
REFERENCES uri(id)
ON DELETE CASCADE,
CONSTRAINT subj_id_current_fk
FOREIGN KEY(subj_id_current)
REFERENCES uri(id)
ON DELETE CASCADE,
CONSTRAINT subj_interpretation_fk
FOREIGN KEY(subj_interpretation)
REFERENCES interpretation(id)
ON DELETE CASCADE,
CONSTRAINT subj_manifestation_fk
FOREIGN KEY(subj_manifestation)
REFERENCES manifestation(id)
ON DELETE CASCADE,
CONSTRAINT subj_origin_fk
FOREIGN KEY(subj_origin)
REFERENCES uri(id)
ON DELETE CASCADE,
CONSTRAINT subj_mimetype_fk
FOREIGN KEY(subj_mimetype)
REFERENCES mimetype(id)
ON DELETE CASCADE,
CONSTRAINT subj_text_fk
FOREIGN KEY(subj_text)
REFERENCES text(id)
ON DELETE CASCADE,
CONSTRAINT subj_storage_fk
FOREIGN KEY(subj_storage)
REFERENCES storage(id)
ON DELETE CASCADE,
CONSTRAINT unique_event UNIQUE (timestamp, interpretation,
manifestation, actor, subj_id)
);
INSERT INTO "event" VALUES(1,1347652042579,1,1,1,0,2,2,2,1,1,1,2,NULL,2);
INSERT INTO "event" VALUES(2,143,3,3,2,0,5,4,4,1,2,1,2,3,5);
INSERT INTO "event" VALUES(3,133,5,5,3,0,2,6,2,1,2,1,2,6,2);
CREATE TABLE extensions_conf (
extension VARCHAR,
key VARCHAR,
value BLOB,
CONSTRAINT unique_extension UNIQUE (extension, key)
);
CREATE TABLE schema_version (
schema VARCHAR PRIMARY KEY ON CONFLICT REPLACE,
version INT
);
INSERT INTO "schema_version" VALUES('core',5);
CREATE UNIQUE INDEX uri_value ON uri(value);
CREATE UNIQUE INDEX interpretation_value
ON interpretation(value);
CREATE UNIQUE INDEX manifestation_value
ON manifestation(value);
CREATE UNIQUE INDEX mimetype_value
ON mimetype(value);
CREATE UNIQUE INDEX actor_value
ON actor(value);
CREATE UNIQUE INDEX text_value
ON text(value);
CREATE UNIQUE INDEX storage_value
ON storage(value);
CREATE INDEX event_id
ON event(id);
CREATE INDEX event_timestamp
ON event(timestamp);
CREATE INDEX event_interpretation
ON event(interpretation);
CREATE INDEX event_manifestation
ON event(manifestation);
CREATE INDEX event_actor
ON event(actor);
CREATE INDEX event_origin
ON event(origin);
CREATE INDEX event_subj_id
ON event(subj_id);
CREATE INDEX event_subj_id_current
ON event(subj_id_current);
CREATE INDEX event_subj_interpretation
ON event(subj_interpretation);
CREATE INDEX event_subj_manifestation
ON event(subj_manifestation);
CREATE INDEX event_subj_origin
ON event(subj_origin);
CREATE INDEX event_subj_mimetype
ON event(subj_mimetype);
CREATE INDEX event_subj_text
ON event(subj_text);
CREATE INDEX event_subj_storage
ON event(subj_storage);
CREATE UNIQUE INDEX extensions_conf_key
ON extensions_conf (extension, key);
CREATE VIEW event_view AS
SELECT event.id,
event.timestamp,
event.interpretation,
event.manifestation,
event.actor,
(SELECT value FROM payload
WHERE payload.id=event.payload)
AS payload,
(SELECT value FROM uri
WHERE uri.id=event.subj_id)
AS subj_uri,
event.subj_id, --//this points to an id in the uri table
event.subj_interpretation,
event.subj_manifestation,
event.subj_origin,
(SELECT value FROM uri
WHERE uri.id=event.subj_origin)
AS subj_origin_uri,
event.subj_mimetype,
(SELECT value FROM text
WHERE text.id = event.subj_text)
AS subj_text,
(SELECT value FROM storage
WHERE storage.id=event.subj_storage)
AS subj_storage,
(SELECT state FROM storage
WHERE storage.id=event.subj_storage)
AS subj_storage_state,
event.origin,
(SELECT value FROM uri
WHERE uri.id=event.origin)
AS event_origin_uri,
(SELECT value FROM uri
WHERE uri.id=event.subj_id_current)
AS subj_current_uri,
event.subj_id_current,
event.subj_text AS subj_text_id,
event.subj_storage AS subj_storage_id,
(SELECT value FROM actor
WHERE actor.id=event.actor)
AS actor_uri
FROM event
;
COMMIT;
|
