-
Notifications
You must be signed in to change notification settings - Fork 0
/
brblog.sql
194 lines (173 loc) · 5.4 KB
/
brblog.sql
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
--
-- TODO: postDate should be called entryData
---
drop view if exists PostFull;
drop view if exists CommentFull;
drop table if exists Comment;
drop table if exists Post;
drop table if exists Entry;
-- Don't forget to
-- pragma foreign_keys = ON;
create table Entry (
eId integer primary key autoincrement,
title text,
body text not null,
postDate datetime not null,
isVisible boolean not null
);
-- No autoincrement for pId because it's ok reuse the id's of the post that
-- had been deleted. Not so with eId's.
create table Post (
eId integer,
pId integer primary key,
foreign key (eId) references Entry (eId) on delete cascade
);
create table Comment (
eId integer,
pId integer,
cId integer,
author text,
mark text,
primary key (pId, cId),
foreign key (eId) references Entry (eId) on delete cascade,
foreign key (pId) references Post (pId) on delete cascade
);
create view PostFull as
select pId, title, body, postDate, isVisible
from Post, Entry
where Entry.eId = Post.eId;
create view CommentFull as
select pId, cId, title, body, postDate, isVisible, author, mark
from Comment, Entry
where Entry.eId = Comment.eId;
-- create trigger AdjustEntryTime
-- after insert on Entry
-- for each row
-- begin
-- update Entry
-- set postDate = datetime(New.postDate, 'localtime')
-- where eId = New.eId;
-- end;
create trigger CommentFullDelete
instead of delete on CommentFull
for each row
begin
delete from Comment
where Comment.pId = Old.pId
and Comment.cId = Old.cId;
end;
create trigger PostFullDelete
instead of delete on PostFull
for each row
begin
delete from Post
where Post.pId = Old.pId;
end;
create trigger CommentFullInsert
instead of insert on CommentFull
for each row
begin
insert into Entry values (
null,
New.title,
New.body,
New.postDate,
New.isVisible
);
insert into Comment values (
(select max(eId) from Entry),
New.pId,
New.cId,
New.author,
New.mark
);
end;
create trigger PostFullInsert
instead of insert on PostFull
for each row
begin
insert into Entry values (
null,
New.title,
New.body,
New.postDate,
New.isVisible
);
insert into Post values (
(select max(eId) from Entry),
New.pId
);
end;
create trigger CommentFullUpdate
instead of update on CommentFull
for each row
begin
update Entry
set title = New.title,
body = New.body,
postDate = New.postDate,
isVisible = New.isVisible
where eId = (select eId from Comment where pId = New.pId
and cId = New.cId);
update Comment
set pId = New.pId,
cId = New.cId,
author = New.author,
mark = New.mark
where eId = (select eId from Comment where pId = New.pId
and cId = New.cId);
end;
create trigger PostFullUpdate
instead of update on PostFull
for each row
begin
update Entry
set title = New.title,
body = New.body,
postDate = New.postDate,
isVisible = New.isVisible
where eId = (select eId from Post where pId = New.pId);
update Post
set pId = New.pId
where pId = New.pId;
end;
-- Reference tree looks like this:
-- eId 1 2
-- ^ ^
-- | |
-- pId 1 |
-- ^ |
-- \ /
-- cId 1
--
-- So when a post is deleted, all comments that reference it get deleted
-- automatically as well ('on delete cascade'); but we also have to manually
-- delete entries that are referenced *by* the post and by all the comments we
-- have deleted. This sort of referential integrity is done by the triggers
-- below. Hola! SQL rules.
create trigger CleanCommentEntries
after delete on Comment
for each row
begin
delete from Entry where eId = old.eId;
end;
create trigger CleanPostEntries
after delete on Post
for each row
begin
delete from Entry where eId = old.eId;
end;
-- insert into Entry values (null, 'first post', 'hello, world!', datetime('now'), 1);
-- insert into Post values ((select max(eId) from Entry), null);
-- insert into Entry values (null, 'comment on the first post', 'nice posting...', datetime('now'), 1);
-- insert into Comment values (
-- (select max(eId) from Entry),
-- (select max(pId) from Post),
-- ifnull((select max(cId) from Comment) + 1, 1),
-- 'xio',
-- null
-- );
-- insert into PostFull values (null, 'second post!', 'hi again', datetime('now'), 0);
-- insert into PostFull values (null, 'already the third', 'so hej,<br>using some tags this time', datetime('now'), 1);
-- insert into CommentFull values (2, 1, '2nd comment on the second posting', 'hola mate', datetime('now'), 0, 'stranger', null);
-- insert into CommentFull values (2, 2, 'actual second comment', 'Vote for Edinaya Rossia!', datetime('now'), 0, 'Rossijanin', 'idiot');