SET client_encoding = 'UTF-8';

CREATE TABLE referrers(
    name text
        PRIMARY KEY,
    uri text);

CREATE TABLE posts(                                                     
    uri text 
        PRIMARY KEY,                                                                       
    headline text 
        NOT NULL,                                                         
    postedat timestamp with time zone 
        NOT NULL
        UNIQUE          -- N.B. complains on multiple-row insert using 
        DEFAULT now(),  -- default timestamp [now() is same for all rows].                                      
    article text,
    via text 
        REFERENCES referrers (name)
        ON UPDATE CASCADE
        ON DELETE RESTRICT);

CREATE TABLE tags(                                                      
    uri text 
        NOT NULL 
        REFERENCES posts (uri) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE,   
    tag text 
        NOT NULL 
        DEFAULT 'notagyet', 
    PRIMARY KEY (uri,tag));



CREATE ROLE oddreader NOINHERIT LOGIN PASSWORD oddreader;
GRANT USAGE ON SCHEMA oddry TO oddreader;
GRANT SELECT ON posts,referrers,tags TO oddreader;
GRANT SELECT, INSERT, DELETE, UPDATE ON posts, tags TO oddwriter;
GRANT SELECT, INSERT, UPDATE ON referrers TO oddwriter;