]> pd.if.org Git - newsd/blob - schema.sql
Added test script.
[newsd] / schema.sql
1 drop schema if exists nntp cascade;
2 create schema nntp;
3
4 set search_path to nntp,public;
5
6 -- TODO could probably just do a nested replace in straight sql
7 create or replace function wildmat_to_re(wm text) returns text as $$
8 declare
9         re text;
10 begin
11         re := regexp_replace(wm, E'\\.', E'\\.','g'); -- first string will be
12         -- interpreted as a regex, second as just a string
13         re := regexp_replace(re, E'\\?', E'.','g');
14         re := regexp_replace(re, E'\\*', E'.*','g');
15         return re;
16 end;
17 $$ language 'plpgsql';
18
19 create or replace function wildmat_regex(wm text) returns text as $$
20 declare
21         skip_init_neg boolean;
22         negated boolean;
23         repat text;
24         sql     text;
25         pat     text;
26 begin
27         sql := '';
28         skip_init_neg := true;
29
30         for pat in select p from regexp_split_to_table(wm, E',') as p loop
31         if skip_init_neg and pat ~ E'^!' then continue; end if;
32                 skip_init_neg := false;
33                 negated := (pat ~ E'^!');
34                 pat := regexp_replace(pat, E'^!', '');
35                 repat := wildmat_to_re(pat);
36                 if not negated then
37                         sql := sql || '|' || repat;
38                 else
39                         sql := regexp_replace(sql, E'^\\|', '');
40                         sql := '(^(?!' || repat || ')(' || sql || ')$)';
41                 end if;
42         end loop;
43
44         sql := regexp_replace(sql, E'^\\|', '');
45         if not negated then
46                 sql := '^(' || sql || ')$';
47         end if;
48
49         return sql;
50 end;
51 $$ language 'plpgsql';
52
53 -- TODO put the rest of the mandatory overview fields here?
54 create table articles (
55         id      text primary key,
56         header  text not null,
57         body    text, -- not null?
58         peer    text,
59         received        timestamp with time zone default CURRENT_TIMESTAMP,
60         newsgroups      text, -- TODO array?
61         expires timestamp with time zone,
62         "date"  timestamp with time zone,
63         -- hdr headers
64         lines   integer, -- actual lines, see the overview table for Lines hdr
65         bytes   integer,
66         control boolean not null default 'f'
67 );
68 -- check id like '<%' and id like '%>'
69
70 create table moderated_posts (
71         id      text primary key,
72         article text,
73         received        timestamp with time zone default CURRENT_TIMESTAMP
74 );
75
76 create or replace function multiline(content text) returns text as $$
77 select regexp_replace(
78         regexp_replace(
79                 regexp_replace($1, E'^\\.', E'..', 'gn'), -- dot stuff
80                 E'\\r?\\n', E'\r\n', 'g'), -- lines separated by \r\n
81         E'(\\r\\n)?$', E'\r\n\.\r\n'); -- terminated by \r\n.\r\n
82 $$ language 'sql';
83
84 create or replace function demultiline(content text) returns text as $$
85 select regexp_replace(
86         regexp_replace(
87                 regexp_replace($1,
88         E'\\r\\n\\.\\r\\n$', E'\r\n'), -- terminated by \r\n.\r\n
89                 E'\\r\\n', E'\n', 'g'), -- lines separated by \r\n
90                         E'^\\.\\.', E'.', 'gn'); -- undot stuff
91 $$ language 'sql';
92
93 create table overview (
94         article text references articles on delete cascade
95         deferrable initially deferred,
96         header  text, -- or metadata, but it has a unique name
97         value   text,
98         unique(article,header)
99 );
100
101 create table header_order (
102         header  text primary key,
103         ord     integer unique not null
104 );
105
106 insert into header_order (values
107 ('Subject',1),
108 ('From',2),
109 ('Date',3),
110 ('Message-ID',4),
111 ('References',5),
112 (':bytes',6),
113 (':lines',7),
114 ('Xref', 8)
115 );
116
117 create view articleover as
118 select id, array_to_string(array_agg(regexp_replace(value,E'\t',' ', 'g')),E'\t') as overview from (
119 select
120 A.id, ORD.header,
121 case when ord.ord <= 7 then coalesce(O.value,'') else coalesce(ORD.header || ': ' || nullif(O.value, ''), '') end as value
122 from articles A cross join header_order ord
123 left join overview O on O.article = A.id and O.header = ORD.header
124 order by ord.ord
125 ) as foo
126 group by id
127 ;
128
129 -- TODO trigger to make an insert an update if the unique constraint
130 -- would be violated
131
132 -- From
133 -- Date
134 -- Newsgroups
135 -- Subject
136 -- Message-ID
137 -- Path
138
139 -- Optional
140 -- Reply-To
141 -- Sender
142 -- Followup-To
143 -- Expires
144 -- References
145 -- Control
146 -- Distribution
147 -- Organization
148 -- Keywords
149 -- Summary
150 -- Approved
151 -- Lines
152 -- Xref
153
154 create table newsgroups (
155         newsgroup       text primary key,
156         posting char(1) not null default 'y',
157         moderator       text default '%s@moderators.isc.org',
158         description     text,
159         high    integer default 0,
160         low     integer default 1,
161         created timestamp default CURRENT_TIMESTAMP,
162         creator text,
163         active  boolean not null default true,
164         local   boolean not null default false
165 );
166
167 create or replace function newsgroups_update_trig() returns trigger as $$
168 begin
169         if TG_OP = 'UPDATE' and NEW.active = false then
170                if NEW.active != OLD.active then
171                 insert into log (message) values ('rmgroup ' || NEW.newsgroup);
172         end if;
173         end if;
174         if TG_OP = 'INSERT' and NEW.active = true then
175                 insert into log (message) values ('newgroup ' || NEW.newsgroup);
176         end if;
177         return NEW;
178 end;
179 $$ language 'plpgsql';
180
181 create trigger newsgroups_maintenance_log_trigger after update or insert on newsgroups
182 for each row execute procedure newsgroups_update_trig();
183
184 create table expiration (
185         wildmat text primary key,
186         wildmatre text,
187         moderated       boolean, -- true = mod only, false = not mod, null=any
188         crossposts      boolean not null default false, -- also expire all crossposts
189         retention       interval not null default '30 days', -- how long to keep from receipt
190         max     interval not null default '90 days',
191         min     interval not null default '7 days'
192 );
193
194 create table xpost (
195         article text references articles on delete cascade
196                 deferrable initially deferred,
197                 -- deferrable lets us do the xposts first on new
198                 -- article insertion
199         newsgroup       text references newsgroups on delete cascade,
200         number  integer,
201         unique(newsgroup,number)
202 );
203 -- the following index makes calculating average cross-posting much
204 -- faster.
205 create index xpost_article_index on xpost(article);
206
207 create view xref as
208 select article, array_to_string(array_agg(newsgroup||':'||number),' ') as xref
209 from xpost group by article
210 ;
211
212 create or replace function headers(article text)
213 returns table (header text, value text)  as $$
214         select
215         substring(h from E'^([^:]+):'),
216         substring(h from E'(?n)^[^:]+: (.+?)$')
217         from
218         regexp_split_to_table(
219                 regexp_replace($1, E'\r?\n$', ''),
220         E'\r?\n(?!\\s)') as h
221         ;
222 $$ language 'sql' immutable strict;
223
224 create or replace function strip_header(headers text, strip text)
225 returns text as $$
226 select array_to_string(array_agg(header || ': ' || value),E'\r\n') || E'\r\n'
227 from (
228         select * from headers($1)
229         where upper(header) != upper($2)
230 ) as base
231 ;$$ language 'sql';
232
233 create or replace function line_count(content text) returns integer as $$
234 select octet_length(regexp_replace(regexp_replace($1,E'\\n$', ''), E'[^\\n]', '','g'))+1;
235 $$ language 'sql';
236
237 create or replace function header_value(headers text, want text)
238 returns text as $$
239 select value from headers($1) where lower(header) = lower($2);
240 $$ language 'sql';
241
242 create or replace function header_add(headers text, h text, v text) returns text as $$
243 select $1 || $2 || ': ' || $3 || E'\r\n';
244 $$ language 'sql';
245
246 create or replace function header_replace(headers text, hdr text, content text)
247 returns text as $$
248         select array_to_string(array_agg(header || ': ' || value),E'\r\n') || E'\r\n' from (
249         select
250                 case when lower(header) = lower($2) then $2 else header end,
251                 case when lower(header) = lower($2) then $3 else value end
252                 from headers($1)
253         ) as base
254         ;
255 $$ language 'sql';
256
257 create or replace function rfc_date(d text) returns timestamptz as $$
258 declare
259         noct text;
260         simple text;
261         match   text[];
262 begin
263         simple := E'(\\d\\d?)\\s+(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\\s+(\\d\\d(?:\\d\\d)?)\\s+(\\d\\d?:\\d\\d(?::\\d\\d)?)\\s+([+-]\\d\\d\\d\\d|\\w{3})';
264         if d ~ simple then
265                 match := regexp_matches(d, simple);
266                 begin
267                         -- was getting +2400 as time zone
268                 return array_to_string(match, ' ')::timestamptz;
269                 exception when others then
270                         raise NOTICE 'date parse error: % %', SQLSTATE, SQLERRM;
271                         return null;
272                 end;
273         end if;
274         raise NOTICE 'date regex mismatch %', d;
275
276         return null;
277 end;
278 $$ language 'plpgsql';
279
280 create or replace function article_post() returns trigger as $$
281 declare
282         id text;
283         ng text;
284         xrefhdr text;
285         host text;
286         xrefcur text;
287 begin
288         xrefhdr := header_value(NEW.header, 'Path');
289         host := substring(xrefhdr from E'^\\s*([^!]+)');
290
291         id := header_value(NEW.header, 'Message-ID');
292         ng := header_value(NEW.header, 'Newsgroups');
293         xrefcur := header_value(NEW.header, 'Xref');
294
295         -- path
296         -- subject
297         -- date
298
299         insert into xpost (article, newsgroup)
300         select
301         id, GROUPS.newsgroup from
302         regexp_split_to_table(ng, E'\\s*,\\s*') as G
303         inner join newsgroups GROUPS on GROUPS.newsgroup = btrim(G)
304         ;
305
306         xrefhdr := (select xref from xref where article = id);
307
308         if xrefcur is not null then
309                 NEW.header :=
310                 header_replace(NEW.header, 'Xref', host || ' ' ||xrefhdr);
311         else
312                 NEW.header :=
313                 header_add(NEW.header, 'Xref', host || ' ' ||xrefhdr);
314         end if;
315
316         if NEW.header is null then
317                 raise exception 'null header H';
318         end if;
319
320         NEW.newsgroups = ng;
321         -- Two for the logical crlf between the header and body
322         -- There will be three more for the .\r\n at the end
323         -- TODO need to decide on how to store header and body
324         NEW.bytes = octet_length(NEW.header) + octet_length(NEW.body) + 2;
325         NEW.lines = line_count(NEW.body);
326
327         if NEW.header is null then
328                 raise exception 'null header a';
329         end if;
330
331         begin
332                 -- TODO improve this regular expression
333                 NEW.expires = regexp_replace(header_value(NEW.header, 'Expires'),E'\\([^\\)]*\\)', ' ')::timestamptz;
334         exception
335                 when OTHERS then NEW.expires = NULL;
336         end;
337
338         -- TODO if date is null?
339         begin
340                 -- TODO improve this regular expression
341                 NEW.date = regexp_replace(header_value(NEW.header, 'Date'),E'\\([^\\)]*\\)', ' ')::timestamptz;
342         exception
343                 when OTHERS then NEW.date = NULL;
344         end;
345
346         if NEW.header is null then
347                 raise exception 'null header b';
348         end if;
349
350         -- Overview headers
351         -- TODO just do all headers?  what about dups?
352         insert into overview (article, header, value)
353         select NEW.id, HO.header, H.value
354         from headers(NEW.header) H
355         inner join
356         header_order HO on upper(HO.header) = upper(H.header)
357         union
358         values (NEW.id, ':bytes', NEW.bytes::text),
359         (NEW.id, ':lines', NEW.lines::text)
360         ;
361         -- qw(Subject From Date Message-ID References :bytes :lines Xref);
362
363         return NEW;
364 end;
365 $$ language 'plpgsql';
366
367 create trigger article_post_trigger before insert on articles
368 for each row execute procedure article_post();
369
370 create or replace function xpost_trigger() returns trigger as $$
371 declare
372         hiwater integer;
373 begin
374         update newsgroups set high = high + 1
375         where newsgroup = NEW.newsgroup returning high into hiwater;
376
377         NEW.number := hiwater;
378         return NEW;
379 end;
380 $$ language 'plpgsql';
381
382 create trigger highwater before insert on xpost
383         for each row execute procedure xpost_trigger();
384
385 create or replace function highwater_trigger() returns trigger as $$
386 begin
387         update newsgroups set high = NEW.number where newsgroup = NEW.newsgroup;
388         return NEW;
389 end;
390 $$ language plpgsql;
391
392 -- TODO this function breaks when the last article is deleted
393 -- need to look up rfc to determine proper behavior
394 create or replace function lowwater_trigger() returns trigger as $$
395 begin
396         update newsgroups set low = (select coalesce(min(number),0) from xpost where newsgroup = OLD.newsgroup) where newsgroup = OLD.newsgroup;
397         return OLD;
398 end;
399 $$ language plpgsql;
400
401 --create trigger highwater after insert on xpost
402 --      for each row execute procedure highwater_trigger();
403 create trigger lowwater after delete on xpost
404         for each row execute procedure lowwater_trigger();
405
406 -- who do i forward articles to
407 create table feeds (
408         name    text primary key,
409         host    text,
410         port    integer default 119,
411         enabled boolean not null default true,
412         stream  boolean not null default false, -- use CHECK/TAKETHIS
413         groups  text default '*', -- wildmat
414         wildmat text default '*',
415         wildmatre       text default '.', -- parsed wildmat
416         noxposts        text,
417         noxpostsre      text,
418         distribution    text,
419         distributionre  text,
420         -- TODO feed moderated/unmoderated
421         -- see http://www.faqs.org/docs/linux_network/x18341.html
422         maxsize integer, -- maximum article size to feed
423         localonly       boolean default false,
424         path    text, -- regular expression default host?
425         frequency       interval default '1 hour'::interval, -- int secs?
426         feedtime        timestamp with time zone
427 );
428 -- todo check(port >= 0 and port <= 65535)
429
430 create table sessions (
431         peer    text,
432         addr    inet,
433         port    integer,
434         incoming        boolean not null default true,
435         connected       timestamp with time zone,
436         closed  timestamp with time zone default clock_timestamp(),
437         received        integer, -- 239, 235
438         refused         integer, -- 435
439         rejected        integer, -- 439, 437
440         postponed       integer -- 436
441 );
442 -- offered == all those together
443 create view session_summary as select peer, addr, port, connected, sum(received) as received, sum(refused) as refused, sum(rejected) as rejected, sum(postponed) as postponed, coalesce(max(closed), clock_timestamp()) - connected as duration from sessions group by peer, addr, port, connected;
444
445
446 create table feedq (
447         peer    text references feeds on delete cascade,
448         article text references articles on delete cascade,
449         response        integer
450 );
451
452 create function feedto(newsgroups text, host text) returns boolean as $$
453 begin
454         return (select
455                 true =
456                 any (select regexp_split_to_table(newsgroups, E',')
457                         ~ F.wildmatre)
458                 and
459                 not true = 
460                 any (select regexp_split_to_table(newsgroups, E',')
461                         ~ F.noxpostsre)
462                 from feeds F
463                 where F.host = host
464         )
465         ;
466 end;
467 $$ language 'plpgsql';
468
469 create or replace function feedout() returns trigger as $$
470 begin
471         insert into feedq
472         select coalesce(F.host, F.name), NEW.id
473         from feeds F
474         where true = any (select regexp_split_to_table(NEW.newsgroups, E',')
475                         ~ F.wildmatre)
476         -- TODO add in noxposts
477         and coalesce(F.host,F.name) != NEW.peer
478         and F.enabled
479         ;
480         if FOUND then
481                 NOTIFY feedq;
482         end if;
483         return NEW;
484 end;
485 $$ language 'plpgsql';
486
487 create trigger feedpeer_trigger after insert on articles
488 for each row execute procedure feedout();
489
490 create table log (
491         ts      timestamp with time zone default CURRENT_TIMESTAMP,
492         client  inet,
493         port    integer,
494         pid     integer,
495         priority        integer,
496         facility        text,
497         message text
498 );