1 drop schema if exists nntp cascade;
4 set search_path to nntp,public;
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 $$
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');
17 $$ language 'plpgsql';
19 create or replace function wildmat_regex(wm text) returns text as $$
21 skip_init_neg boolean;
28 skip_init_neg := true;
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);
37 sql := sql || '|' || repat;
39 sql := regexp_replace(sql, E'^\\|', '');
40 sql := '(^(?!' || repat || ')(' || sql || ')$)';
44 sql := regexp_replace(sql, E'^\\|', '');
46 sql := '^(' || sql || ')$';
51 $$ language 'plpgsql';
53 -- TODO put the rest of the mandatory overview fields here?
54 create table articles (
57 body text, -- not null?
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,
64 lines integer, -- actual lines, see the overview table for Lines hdr
66 control boolean not null default 'f'
68 -- check id like '<%' and id like '%>'
70 create table moderated_posts (
73 received timestamp with time zone default CURRENT_TIMESTAMP
76 create or replace function multiline(content text) returns text as $$
77 select 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
84 create or replace function demultiline(content text) returns text as $$
85 select regexp_replace(
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
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
98 unique(article,header)
101 create table header_order (
102 header text primary key,
103 ord integer unique not null
106 insert into header_order (values
117 create view articleover as
118 select id, array_to_string(array_agg(regexp_replace(value,E'\t',' ', 'g')),E'\t') as overview from (
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
129 -- TODO trigger to make an insert an update if the unique constraint
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',
159 high integer default 0,
160 low integer default 1,
161 created timestamp default CURRENT_TIMESTAMP,
163 active boolean not null default true,
164 local boolean not null default false
167 create or replace function newsgroups_update_trig() returns trigger as $$
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);
174 if TG_OP = 'INSERT' and NEW.active = true then
175 insert into log (message) values ('newgroup ' || NEW.newsgroup);
179 $$ language 'plpgsql';
181 create trigger newsgroups_maintenance_log_trigger after update or insert on newsgroups
182 for each row execute procedure newsgroups_update_trig();
184 create table expiration (
185 wildmat text primary key,
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'
195 article text references articles on delete cascade
196 deferrable initially deferred,
197 -- deferrable lets us do the xposts first on new
199 newsgroup text references newsgroups on delete cascade,
201 unique(newsgroup,number)
203 -- the following index makes calculating average cross-posting much
205 create index xpost_article_index on xpost(article);
208 select article, array_to_string(array_agg(newsgroup||':'||number),' ') as xref
209 from xpost group by article
212 create or replace function headers(article text)
213 returns table (header text, value text) as $$
215 substring(h from E'^([^:]+):'),
216 substring(h from E'(?n)^[^:]+: (.+?)$')
218 regexp_split_to_table(
219 regexp_replace($1, E'\r?\n$', ''),
220 E'\r?\n(?!\\s)') as h
222 $$ language 'sql' immutable strict;
224 create or replace function strip_header(headers text, strip text)
226 select array_to_string(array_agg(header || ': ' || value),E'\r\n') || E'\r\n'
228 select * from headers($1)
229 where upper(header) != upper($2)
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;
237 create or replace function header_value(headers text, want text)
239 select value from headers($1) where lower(header) = lower($2);
242 create or replace function header_add(headers text, h text, v text) returns text as $$
243 select $1 || $2 || ': ' || $3 || E'\r\n';
246 create or replace function header_replace(headers text, hdr text, content text)
248 select array_to_string(array_agg(header || ': ' || value),E'\r\n') || E'\r\n' from (
250 case when lower(header) = lower($2) then $2 else header end,
251 case when lower(header) = lower($2) then $3 else value end
257 create or replace function rfc_date(d text) returns timestamptz as $$
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})';
265 match := regexp_matches(d, simple);
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;
274 raise NOTICE 'date regex mismatch %', d;
278 $$ language 'plpgsql';
280 create or replace function article_post() returns trigger as $$
288 xrefhdr := header_value(NEW.header, 'Path');
289 host := substring(xrefhdr from E'^\\s*([^!]+)');
291 id := header_value(NEW.header, 'Message-ID');
292 ng := header_value(NEW.header, 'Newsgroups');
293 xrefcur := header_value(NEW.header, 'Xref');
299 insert into xpost (article, newsgroup)
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)
306 xrefhdr := (select xref from xref where article = id);
308 if xrefcur is not null then
310 header_replace(NEW.header, 'Xref', host || ' ' ||xrefhdr);
313 header_add(NEW.header, 'Xref', host || ' ' ||xrefhdr);
316 if NEW.header is null then
317 raise exception 'null header H';
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);
327 if NEW.header is null then
328 raise exception 'null header a';
332 -- TODO improve this regular expression
333 NEW.expires = regexp_replace(header_value(header, 'Expires'),E'\\([^\\)]*\\)', ' ')::timestamptz;
335 when OTHERS then NEW.expires = NULL;
338 if NEW.header is null then
339 raise exception 'null header b';
343 -- TODO just do all headers? what about dups?
344 insert into overview (article, header, value)
345 select NEW.id, HO.header, H.value
346 from headers(NEW.header) H
348 header_order HO on upper(HO.header) = upper(H.header)
350 values (NEW.id, ':bytes', NEW.bytes::text),
351 (NEW.id, ':lines', NEW.lines::text)
353 -- qw(Subject From Date Message-ID References :bytes :lines Xref);
357 $$ language 'plpgsql';
359 create trigger article_post_trigger before insert on articles
360 for each row execute procedure article_post();
362 create or replace function xpost_trigger() returns trigger as $$
366 select high + 1 from newsgroups where newsgroup = NEW.newsgroup
367 for update into hiwater;
368 NEW.number := hiwater;
369 update newsgroups set high = high + 1 where newsgroup = NEW.newsgroup;
372 $$ language 'plpgsql';
373 create trigger highwater before insert on xpost
374 for each row execute procedure xpost_trigger();
376 create or replace function highwater_trigger() returns trigger as $$
378 update newsgroups set high = NEW.number where newsgroup = NEW.newsgroup;
383 -- TODO this function breaks when the last article is deleted
384 -- need to look up rfc to determine proper behavior
385 create or replace function lowwater_trigger() returns trigger as $$
387 update newsgroups set low = (select coalesce(min(number),0) from xpost where newsgroup = OLD.newsgroup) where newsgroup = OLD.newsgroup;
392 --create trigger highwater after insert on xpost
393 -- for each row execute procedure highwater_trigger();
394 create trigger lowwater after delete on xpost
395 for each row execute procedure lowwater_trigger();
397 -- who do i forward articles to
399 name text primary key,
401 port integer default 119,
402 enabled boolean not null default true,
403 stream boolean not null default false, -- use CHECK/TAKETHIS
404 groups text default '*', -- wildmat
405 wildmat text default '*',
406 wildmatre text default '.', -- parsed wildmat
411 -- TODO feed moderated/unmoderated
412 -- see http://www.faqs.org/docs/linux_network/x18341.html
413 maxsize integer, -- maximum article size to feed
414 localonly boolean default false,
415 path text, -- regular expression default host?
416 frequency interval default '1 hour'::interval, -- int secs?
417 feedtime timestamp with time zone
419 -- todo check(port >= 0 and port <= 65535)
421 create table sessions (
425 incoming boolean not null default true,
426 connected timestamp with time zone,
427 closed timestamp with time zone default clock_timestamp(),
428 received integer, -- 239, 235
429 refused integer, -- 435
430 rejected integer, -- 439, 437
431 postponed integer -- 436
433 -- offered == all those together
434 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;
438 peer text references feeds on delete cascade,
439 article text references articles on delete cascade,
443 create function feedto(newsgroups text, host text) returns boolean as $$
447 any (select regexp_split_to_table(newsgroups, E',')
451 any (select regexp_split_to_table(newsgroups, E',')
458 $$ language 'plpgsql';
460 create or replace function feedout() returns trigger as $$
463 select coalesce(F.host, F.name), NEW.id
465 where true = any (select regexp_split_to_table(NEW.newsgroups, E',')
467 -- TODO add in noxposts
468 and coalesce(F.host,F.name) != NEW.peer
476 $$ language 'plpgsql';
478 create trigger feedpeer_trigger after insert on articles
479 for each row execute procedure feedout();
482 ts timestamp with time zone default CURRENT_TIMESTAMP,