Postgres schema for reasonwell.com

create_tables.sql

\set ON_ERROR_STOP


CREATE TABLE Sites (
  id            SERIAL PRIMARY KEY,
  base_url      VARCHAR NOT NULL,
  title         VARCHAR NOT NULL,
  public_read   BOOLEAN NOT NULL DEFAULT FALSE,
  allow_anyone  BOOLEAN NOT NULL DEFAULT FALSE,
  allow_unverified BOOLEAN NOT NULL DEFAULT FALSE,
  allow_email_domain VARCHAR,
  logging       BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE Hostnames (
  host          VARCHAR PRIMARY KEY CHECK (lower(host) = host),
  site_id       INTEGER NOT NULL,
  audience      VARCHAR NOT NULL,
  FOREIGN KEY (site_id) REFERENCES Sites (id)
);

INSERT INTO Sites (id, base_url, title, public_read, allow_anyone) VALUES
  (1, 'http://www.reasonwell.com', 'Public', TRUE, TRUE),
  (2, 'http://test.lvh.me:3000', 'Test', TRUE, TRUE);

INSERT INTO Hostnames (site_id, host, audience) VALUES
  (1, 'www.reasonwell.com', 'http://www.reasonwell.com'),
  (2, 'test.lvh.me', 'http://test.lvh.me:3000');


CREATE TABLE Users (
  id            SERIAL PRIMARY KEY,
  username      VARCHAR(16),
  fullname      VARCHAR(120),
  email         VARCHAR(254),
  email_verified BOOLEAN NOT NULL DEFAULT FALSE,
  allow_login   BOOLEAN NOT NULL DEFAULT FALSE, -- to be removed
  password_hash VARCHAR(60),
  location      VARCHAR(120),
  bio           TEXT,
  website       VARCHAR(120),
  twitter       VARCHAR(16),
  notify_args_challenging BOOLEAN NOT NULL DEFAULT TRUE,
  notify_args_affirming BOOLEAN NOT NULL DEFAULT TRUE,
  notify_edits BOOLEAN NOT NULL DEFAULT TRUE,
  notify_criticisms BOOLEAN NOT NULL DEFAULT TRUE,
  notify_announcements BOOLEAN NOT NULL DEFAULT TRUE,
  terms_accepted BOOLEAN NOT NULL DEFAULT FALSE,
  allow_contributions BOOLEAN NOT NULL DEFAULT FALSE, -- to be removed
  notified_allow_login BOOLEAN NOT NULL DEFAULT FALSE, -- to be removed
  logging       BOOLEAN NOT NULL DEFAULT FALSE,
  create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX ON Users ((lower(username)));
CREATE UNIQUE INDEX users_lower_email_idx ON Users ((lower(email)));


CREATE TABLE Sessions (
  session_key   VARCHAR(255) PRIMARY KEY,
  site_id       INTEGER NOT NULL,
  user_id       INTEGER NOT NULL,
  via_persona   BOOLEAN NOT NULL DEFAULT FALSE,
  create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  FOREIGN KEY (site_id) REFERENCES Sites (id),
  FOREIGN KEY (user_id) REFERENCES Users (id)
);

CREATE INDEX on Sessions (user_id);


CREATE TABLE SiteUsers (
  site_id       INTEGER NOT NULL,
  user_id       INTEGER NOT NULL,
  site_admin    BOOLEAN NOT NULL DEFAULT FALSE,
  allow_login   BOOLEAN NOT NULL DEFAULT FALSE,
  allow_contributions BOOLEAN NOT NULL DEFAULT TRUE,
  notified_allow_login BOOLEAN NOT NULL DEFAULT FALSE,
  notifications_throttled BOOLEAN NOT NULL DEFAULT FALSE,
  create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (site_id, user_id),
  FOREIGN KEY (site_id) REFERENCES Sites (id),
  FOREIGN KEY (user_id) REFERENCES Users (id)
);


CREATE TABLE Immutables (
  id            VARCHAR(64) NOT NULL UNIQUE,
  class_id      CHAR(2) NOT NULL,
  creator_id    INTEGER NOT NULL,
  create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (id, class_id),
  FOREIGN KEY (creator_id) REFERENCES Users (id)
);

CREATE TABLE SiteImmutables (
  site_id       INTEGER NOT NULL,
  id            VARCHAR(64) NOT NULL,
  class_id      CHAR(2) NOT NULL,
  creator_id    INTEGER NOT NULL,
  create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (site_id, id),
  FOREIGN KEY (site_id) REFERENCES Sites (id)
  FOREIGN KEY (id, class_id) REFERENCES Immutables (id, class_id),
  FOREIGN KEY (creator_id) REFERENCES Users (id)
);

CREATE TABLE Claims (
  id            VARCHAR(64) PRIMARY KEY,
  class_id      CHAR(2) NOT NULL DEFAULT 'cl' CHECK(class_id = 'cl'),
  text          TEXT NOT NULL,
  FOREIGN KEY (id, class_id) REFERENCES Immutables (id, class_id)
);

CREATE INDEX ON Claims (lower(text(20)));


CREATE TABLE Arguments (
  id            VARCHAR(64) PRIMARY KEY,
  class_id      CHAR(2) NOT NULL DEFAULT 'ar' CHECK(class_id = 'ar'),
  contention_id VARCHAR(64) NOT NULL,
  supports      BOOLEAN NOT NULL,
  premise_count INTEGER NOT NULL,
  FOREIGN KEY (contention_id) REFERENCES Claims (id),
  FOREIGN KEY (id, class_id) REFERENCES Immutables (id, class_id)
);

CREATE INDEX ON Arguments (contention_id, supports);


CREATE TABLE Premises (
  argument_id   VARCHAR(64) NOT NULL,
  index         INTEGER NOT NULL,
  claim_id      VARCHAR(64) NOT NULL,
  PRIMARY KEY (argument_id, index),
  FOREIGN KEY (argument_id) REFERENCES Arguments (id),
  FOREIGN KEY (claim_id) REFERENCES Claims (id)
);

CREATE INDEX ON Premises (claim_id);


CREATE TABLE Flaws (
  id            VARCHAR(64) PRIMARY KEY,
  class_id      CHAR(2) NOT NULL DEFAULT 'fl' CHECK(class_id = 'fl'),
  argument_id   VARCHAR(64) NOT NULL,
  text          TEXT NOT NULL,
  FOREIGN KEY (argument_id) REFERENCES Arguments (id),
  FOREIGN KEY (id, class_id) REFERENCES Immutables (id, class_id)
);

CREATE INDEX ON Flaws (argument_id);


CREATE TABLE Assumptions (
  id            VARCHAR(64) PRIMARY KEY,
  class_id      CHAR(2) NOT NULL DEFAULT 'as' CHECK(class_id = 'as'),
  argument_id   VARCHAR(64) NOT NULL,
  claim_id      VARCHAR(64) NOT NULL,
  FOREIGN KEY (argument_id) REFERENCES Arguments (id),
  FOREIGN KEY (claim_id) REFERENCES Claims (id),
  FOREIGN KEY (id, class_id) REFERENCES Immutables (id, class_id)
);

CREATE INDEX ON Assumptions (argument_id);


CREATE TABLE Responses (
  id            VARCHAR(64) PRIMARY KEY,
  class_id      CHAR(2) NOT NULL DEFAULT 're' CHECK(class_id = 're'),
  issue_id      VARCHAR(64) NOT NULL,
  proposal_id   VARCHAR(64) NOT NULL,
  FOREIGN KEY (issue_id) REFERENCES Claims (id),
  FOREIGN KEY (proposal_id) REFERENCES Claims (id),
  FOREIGN KEY (id, class_id) REFERENCES Immutables (id, class_id)
);

CREATE INDEX ON Responses (issue_id);


CREATE TABLE Edits (
  id            VARCHAR(64) PRIMARY KEY,
  class_id      CHAR(2) NOT NULL DEFAULT 'ed' CHECK(class_id = 'ed'),
  pred_id       VARCHAR(64) NOT NULL,
  succ_id       VARCHAR(64) NOT NULL,
  FOREIGN KEY (pred_id) REFERENCES Immutables (id),
  FOREIGN KEY (succ_id) REFERENCES Immutables (id),
  FOREIGN KEY (id, class_id) REFERENCES Immutables (id, class_id)
);

CREATE INDEX ON Edits (pred_id);


CREATE TABLE Opinions (
  site_id       INTEGER NOT NULL,
  id            VARCHAR(64) NOT NULL,
  user_id       INTEGER NOT NULL,
  accepts       BOOLEAN,
  attention     BOOLEAN,
  watching      BOOLEAN NOT NULL DEFAULT TRUE,
  watch_edits   BOOLEAN NOT NULL DEFAULT TRUE,
  update_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (site_id, id, user_id),
  FOREIGN KEY (site_id) REFERENCES Sites (id),
  FOREIGN KEY (id) REFERENCES Immutables (id),
  FOREIGN KEY (user_id) REFERENCES Users (id)
);


CREATE TABLE Comments (
  id            SERIAL PRIMARY KEY,
  site_id       INTEGER NOT NULL,
  topic_id      VARCHAR(64) NOT NULL,
  user_id       INTEGER NOT NULL,
  text          TEXT NOT NULL,
  create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  update_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  FOREIGN KEY (site_id) REFERENCES Sites (id)
  FOREIGN KEY (topic_id) REFERENCES Immutables (id),
  FOREIGN KEY (user_id) REFERENCES Users (id)
);

CREATE INDEX Comments_topic_create_time ON Comments (topic_id, create_time);


CREATE TABLE Featured (
  site_id       INTEGER NOT NULL,
  id            VARCHAR(64) NOT NULL,
  score         REAL,
  PRIMARY KEY (site_id, id),
  FOREIGN KEY (site_id) REFERENCES Sites (id),
  FOREIGN KEY (id) REFERENCES Immutables (id)
);


CREATE TABLE Pages (
  site_id       INTEGER NOT NULL,
  path          VARCHAR NOT NULL,
  title         VARCHAR NOT NULL,
  body          TEXT NOT NULL,
  is_public     BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (site_id, path),
  FOREIGN KEY (site_id) REFERENCES Sites (id)
);


CREATE TABLE LogEvents (
  id            SERIAL,
  event_time    TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  site_id       INTEGER,
  user_id       INTEGER,
  status        INTEGER,
  httpmethod    VARCHAR,
  url           VARCHAR,
  body          VARCHAR,
  referer       VARCHAR,
  remoteaddress VARCHAR,
  xforwardedfor VARCHAR,
  useragent     VARCHAR
);


-- http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
-- http://stackoverflow.com/a/10246381/310597
CREATE OR REPLACE FUNCTION update_stamp()
  RETURNS TRIGGER AS $$
BEGIN
  IF ((NEW.accepts != OLD.accepts) IS NOT FALSE) THEN
    NEW.update_time := current_timestamp;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS Opinions_update_stamp ON Opinions;
CREATE TRIGGER Opinions_update_stamp BEFORE UPDATE ON Opinions
    FOR EACH ROW EXECUTE PROCEDURE update_stamp();


-- Returns TRUE if the claim did not previously exist.
CREATE OR REPLACE FUNCTION create_claim(claim_id VARCHAR, claim_text TEXT, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  INSERT INTO Immutables (id, class_id, creator_id) VALUES (claim_id, 'cl', user_id);
  INSERT INTO Claims (id, text) VALUES (claim_id, claim_text);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Returns TRUE if the claim did not previously exist in the site.
CREATE OR REPLACE FUNCTION site_create_claim(site_id INTEGER, claim_id VARCHAR, claim_text TEXT, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  PERFORM create_claim(claim_id, claim_text, user_id);
  INSERT INTO SiteImmutables (site_id, id, class_id, creator_id) VALUES (site_id, claim_id, 'cl', user_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;


-- Returns TRUE if the argument did not previously exist.
CREATE OR REPLACE FUNCTION create_argument(id VARCHAR, contention_id VARCHAR, supports BOOLEAN, premise_count INTEGER, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  INSERT INTO Immutables (id, class_id, creator_id) VALUES (id, 'ar', user_id);
  INSERT INTO Arguments (id, contention_id, supports, premise_count)
                 VALUES (id, contention_id, supports, premise_count);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Returns TRUE if the argument did not previously exist in the site.
CREATE OR REPLACE FUNCTION site_create_argument(site_id INTEGER, id VARCHAR, contention_id VARCHAR, supports BOOLEAN, premise_count INTEGER, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  PERFORM create_argument(id, contention_id, supports, premise_count, user_id);
  INSERT INTO SiteImmutables (site_id, id, class_id, creator_id) VALUES (site_id, id, 'ar', user_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION create_premise(argument_id VARCHAR, index INTEGER, claim_id VARCHAR)
  RETURNS VOID AS $$
BEGIN
  INSERT INTO Premises (argument_id, index, claim_id)
                VALUES (argument_id, index, claim_id);
EXCEPTION WHEN unique_violation THEN
  -- do nothing
END;
$$ LANGUAGE plpgsql;


-- Returns TRUE if the assumption did not previously exist.
CREATE OR REPLACE FUNCTION create_assumption(id VARCHAR, argument_id VARCHAR, claim_id VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  INSERT INTO Immutables (id, class_id, creator_id) VALUES (id, 'as', user_id);
  INSERT INTO Assumptions (id, argument_id, claim_id)
                   VALUES (id, argument_id, claim_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Returns TRUE if the assumption did not previously exist in the site.
CREATE OR REPLACE FUNCTION site_create_assumption(site_id INTEGER, id VARCHAR, argument_id VARCHAR, claim_id VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  PERFORM create_assumption(id, argument_id, claim_id, user_id);
  INSERT INTO SiteImmutables (site_id, id, class_id, creator_id) VALUES (site_id, id, 'as', user_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;


-- Returns TRUE if the flaw did not previously exist.
CREATE OR REPLACE FUNCTION create_flaw(id VARCHAR, argument_id VARCHAR, text VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  INSERT INTO Immutables (id, class_id, creator_id) VALUES (id, 'fl', user_id);
  INSERT INTO Flaws (id, argument_id, text)
             VALUES (id, argument_id, text);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Returns TRUE if the flaw did not previously exist in the site.
CREATE OR REPLACE FUNCTION site_create_flaw(site_id INTEGER, id VARCHAR, argument_id VARCHAR, text VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  PERFORM create_flaw(id, argument_id, text, user_id);
  INSERT INTO SiteImmutables (site_id, id, class_id, creator_id) VALUES (site_id, id, 'fl', user_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;


-- Returns TRUE if the response did not previously exist.
CREATE OR REPLACE FUNCTION create_response(id VARCHAR, issue_id VARCHAR, proposal_id VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  INSERT INTO Immutables (id, class_id, creator_id) VALUES (id, 're', user_id);
  INSERT INTO Responses (id, issue_id, proposal_id)
                 VALUES (id, issue_id, proposal_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Returns TRUE if the response did not previously exist in the site.
CREATE OR REPLACE FUNCTION site_create_response(site_id INTEGER, id VARCHAR, issue_id VARCHAR, proposal_id VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  PERFORM create_response(id, issue_ud, proposal_id, user_id);
  INSERT INTO SiteImmutables (site_id, id, class_id, creator_id) VALUES (site_id, id, 're', user_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;


-- Returns TRUE if the edit did not previously exist.
CREATE OR REPLACE FUNCTION create_edit(id VARCHAR, pred_id VARCHAR, succ_id VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  INSERT INTO Immutables (id, class_id, creator_id) VALUES (id, 'ed', user_id);
  INSERT INTO Edits (id, pred_id, succ_id)
             VALUES (id, pred_id, succ_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Returns TRUE if the edit did not previously exist in the site.
CREATE OR REPLACE FUNCTION site_create_edit(site_id INTEGER, id VARCHAR, pred_id VARCHAR, succ_id VARCHAR, user_id INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  PERFORM create_edit(id, pred_id, succ_id, user_id);
  INSERT INTO SiteImmutables (site_id, id, class_id, creator_id) VALUES (site_id, id, 'ed', user_id);
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_watching(uid INTEGER, obj_id VARCHAR, wat BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET watching = wat WHERE user_id = uid AND id = obj_id;
  IF NOT found THEN
    INSERT INTO Opinions (user_id, id, watching) VALUES (uid, obj_id, wat);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION site_set_watching(sid INTEGER, uid INTEGER, obj_id VARCHAR, wat BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET watching = wat WHERE site_id = sid AND user_id = uid AND id = obj_id;
  IF NOT found THEN
    INSERT INTO Opinions (site_id, user_id, id, watching) VALUES (sid, uid, obj_id, wat);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_watch_edits(uid INTEGER, obj_id VARCHAR, wat BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET watch_edits = wat WHERE user_id = uid AND id = obj_id;
  IF NOT found THEN
    INSERT INTO Opinions (user_id, id, watch_edits) VALUES (uid, obj_id, wat);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION site_set_watch_edits(sid INTEGER, uid INTEGER, obj_id VARCHAR, wat BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET watch_edits = wat WHERE site_id = sid AND user_id = uid AND id = obj_id;
  IF NOT found THEN
    INSERT INTO Opinions (site_id, user_id, id, watch_edits) VALUES (sid, uid, obj_id, wat);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_opinion(uid INTEGER, obj_id VARCHAR, acc BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET accepts = acc WHERE user_id = uid AND id = obj_id;
  IF NOT found THEN
    INSERT INTO Opinions (user_id, id, accepts) VALUES (uid, obj_id, acc);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION site_set_opinion(sid INTEGER, uid INTEGER, obj_id VARCHAR, acc BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET accepts = acc WHERE site_id = sid AND user_id = uid AND id = obj_id;
  IF NOT found THEN
    INSERT INTO Opinions (site_id, user_id, id, accepts) VALUES (sid, uid, obj_id, acc);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION assume_opinion(uid INTEGER, obj_id VARCHAR, acc BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET accepts = acc WHERE user_id = uid AND id = obj_id AND accepts IS NULL;
  IF NOT found THEN
    INSERT INTO Opinions (user_id, id, accepts) VALUES (uid, obj_id, acc);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION site_assume_opinion(sid INTEGER, uid INTEGER, obj_id VARCHAR, acc BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET accepts = acc WHERE site_id = sid AND user_id = uid AND id = obj_id AND accepts IS NULL;
  IF NOT found THEN
    INSERT INTO Opinions (site_id, user_id, id, accepts) VALUES (sid, uid, obj_id, acc);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION site_set_attention(sid INTEGER, uid INTEGER, obj_id VARCHAR, att BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Opinions SET attention = att WHERE site_id = sid AND user_id = uid AND id = obj_id;
  IF NOT found THEN
    INSERT INTO Opinions (site_id, user_id, id, attention) VALUES (sid, uid, obj_id, att);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_session(sid VARCHAR, uid INTEGER, persona BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Sessions SET user_id = uid, via_persona = persona WHERE session_key = sid;
  IF NOT found THEN
    INSERT INTO Sessions (session_key, user_id, via_persona) VALUES (sid, uid, persona);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION site_set_session(sid INTEGER, skey VARCHAR, uid INTEGER, persona BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  UPDATE Sessions SET site_id = sid, user_id = uid, via_persona = persona WHERE session_key = skey;
  IF NOT found THEN
    INSERT INTO Sessions (site_id, session_key, user_id, via_persona) VALUES (sid, skey, uid, persona);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION site_user_rejects_argument(sid INTEGER, uid INTEGER, aid VARCHAR)
  RETURNS BOOLEAN AS $$
BEGIN
  -- A user may reject an argument:
  -- * explicitly, by having an opinion on the argument with accepts = FALSE;
  -- * by rejecting any premise of the argument
  -- * by accepting an assumption and rejecting that assumption's claim.
  RETURN
    0 < (
      SELECT count(*) FROM Opinions AS o
      WHERE o.site_id = sid AND o.user_id = uid AND o.id = aid AND NOT o.accepts
    ) OR 0 < (
      SELECT count(*) FROM Premises AS p JOIN Opinions AS o ON o.id = p.claim_id
      WHERE p.argument_id = aid AND o.site_id = sid AND o.user_id = uid AND NOT o.accepts
    ) OR 0 < (
      SELECT count(*) FROM Assumptions AS ass
      JOIN Opinions AS asso ON ass.id = asso.id
      JOIN Opinions AS assco ON ass.claim_id = assco.id
      WHERE ass.argument_id = aid AND asso.site_id = sid AND asso.user_id = uid AND assco.site_id = sid AND assco.user_id = uid
      AND asso.accepts AND NOT assco.accepts
    );
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION site_user_rejects_assumption(sid INTEGER, uid INTEGER, assid VARCHAR)
  RETURNS BOOLEAN AS $$
BEGIN
  -- A user may reject an assumption:
  -- * explicitly, by having an opinion on the assumption with accepts = FALSE;
  -- * implicitly, by agreeing with the assumption's claim.
  RETURN
    0 < (
      SELECT count(*) FROM Opinions AS o
      WHERE o.site_id = sid AND o.user_id = uid AND o.id = assid AND NOT o.accepts
    ) OR 0 < (
      SELECT count(*) FROM Assumptions AS ass
      JOIN Opinions AS o ON ass.claim_id = o.id
      WHERE ass.id = assid AND o.site_id = sid AND o.user_id = uid AND o.accepts
    );
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_user_email_verified(em VARCHAR)
  RETURNS VOID AS $$
BEGIN
  UPDATE Users SET email_verified = TRUE WHERE lower(email) = lower(em);
  IF NOT found THEN
    INSERT INTO Users (email, email_verified) VALUES (em, TRUE);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_siteuser_email_verified(sid INTEGER, em VARCHAR, login BOOLEAN, contrib BOOLEAN)
  RETURNS VOID AS $$
BEGIN
  PERFORM set_user_email_verified(em);
  INSERT INTO SiteUsers (site_id, user_id, allow_login, allow_contributions)
    VALUES (sid, (SELECT id FROM Users WHERE lower(email) = lower(em)), login, contrib);
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_user(em VARCHAR)
  RETURNS VOID AS $$
BEGIN
  INSERT INTO Users (email) VALUES (em);
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION set_siteuser(sid INTEGER, em VARCHAR, login BOOLEAN, contrib BOOLEAN)
  RETURNS VOID AS $$ 
DECLARE
  uid INTEGER;
BEGIN
  PERFORM set_user(em);
  uid := (SELECT id FROM Users WHERE lower(email) = lower(em));
  UPDATE SiteUsers SET allow_login = login, allow_contributions = contrib
    WHERE site_id = sid AND user_id = uid;
  IF NOT found THEN
    INSERT INTO SiteUsers (site_id, user_id, allow_login, allow_contributions)
      VALUES (sid, uid, login, contrib);
  END IF;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION obj_is_shadowed(sid INTEGER, imm_id VARCHAR)
  RETURNS BOOLEAN AS $$
BEGIN
  RETURN 0 < (SELECT count(*) FROM Edits AS e JOIN SiteImmutables AS si ON e.id = si.id
                WHERE si.site_id = sid AND e.pred_id = imm_id
                AND (0 = (SELECT count(*) FROM Opinions AS o WHERE o.site_id = sid
                  AND o.id = e.id AND NOT o.accepts)));
END;
$$ LANGUAGE plpgsql;