pd.if.org Git - newsd/blob - 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 );