sandspiel.postgres

CREATE TABLE creations (
    id char(20) CONSTRAINT firstkey PRIMARY KEY NOT NULL UNIQUE,
    ip inet,
    data_id char(32) NOT NULL,
    title varchar(220) NOT NULL,
    score integer NOT NULL,
    children integer,
    user_id char(32),
    parent_id char(32),
    timestamp timestamp NOT NULL DEFAULT NOW()
);

ALTER TABLE creations ADD COLUMN parent_id char(32);
  
-- https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/
ALTER TABLE creations ADD COLUMN tsv tsvector;

CREATE INDEX tsv_idx ON creations USING gin(tsv);
UPDATE creations SET tsv = to_tsvector(coalesce(title,''));

-- UPDATE creations SET tsv = to_tsvector(coalesce(title,'')) WHERE timestamp > NOW() - INTERVAL '1 days';



CREATE FUNCTION creations_search_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
    to_tsvector(coalesce(new.title,''));
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON creations FOR EACH ROW EXECUTE PROCEDURE creations_search_trigger();


CREATE INDEX ON creations (timestamp);

CREATE INDEX ON creations (score);

CREATE INDEX ON creations (id);

CREATE INDEX ON creations (ip);
CREATE INDEX ON creations (user_id);
CREATE INDEX ON creations (data_id);
CREATE INDEX ON creations (parent_id);

CREATE TABLE votes (
    id char(20),
    uid text
);

CREATE UNIQUE INDEX ON votes (id, uid);


CREATE TABLE reports (
    id char(20) REFERENCES creations(id),
    ip inet NOT NULL,
    timestamp timestamp NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX ON reports (id, ip);
CREATE INDEX ON reports (id);



CREATE TABLE bans (
    user_id char(32),
    timestamp timestamp NOT NULL DEFAULT NOW()
);
CREATE INDEX ON bans (user_id);

CREATE TABLE rulings (
    id char(20) REFERENCES creations(id),
    bad BOOLEAN  NOT NULL
);


CREATE UNIQUE INDEX ON rulings (id);



-- SELECT d.date, count(se.id)
-- FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
--       FROM generate_series(0, 365, 1) AS offs
--      ) d LEFT OUTER JOIN
--      creations se 
--      ON d.date = to_char(date_trunc('day', se.timestamp), 'YYYY-MM-DD')  
-- GROUP BY d.date;