nusearch_doc.txt

CREATE DATABASE chatty;

---------------------------------------------------------------

CREATE TABLE indexer (
   next_low_id    INTEGER  NOT NULL,
   next_high_id   INTEGER  NOT NULL
);
COMMENT ON TABLE indexer IS '2';

CREATE TABLE thread (
   id          INTEGER     PRIMARY KEY,
   date        TIMESTAMP   NOT NULL,
   bump_date   TIMESTAMP   NOT NULL,
);

CREATE TABLE post (
   id          INTEGER     PRIMARY KEY,
   thread_id   INTEGER     NOT NULL REFERENCES thread ON DELETE CASCADE,
   parent_id   INTEGER     NULL,
   author      TEXT        NOT NULL,
   category    INTEGER     NOT NULL,
   date        TIMESTAMP   NOT NULL,
   body        TEXT        NOT NULL,
   author_c    TEXT        NOT NULL,
   body_c      TEXT        NOT NULL
);

CREATE TABLE post_lols (
   post_id INTEGER NOT NULL,
   tag TEXT NOT NULL,
   count INTEGER NOT NULL,
   PRIMARY KEY (post_id, tag)
);
CREATE INDEX idx_post_lols_post_id ON post_lols (post_id);

CREATE TABLE post_index (
   id          INTEGER     NOT NULL REFERENCES post ON DELETE CASCADE,
   body_c_ts   tsvector    NOT NULL,
   PRIMARY KEY (id)
);

CREATE TABLE nuked_post (
   id          INTEGER     PRIMARY KEY,
   reattempts  INTEGER     NOT NULL,
   last_date   TIMESTAMP   NOT NULL,
   error       TEXT        NOT NULL
);

CREATE TABLE shacker (
   id                   SERIAL   PRIMARY KEY,
   username             TEXT     NOT NULL UNIQUE,
   signup_date          TIMESTAMP WITH TIME ZONE NULL,
   filter_nws           BOOLEAN  NOT NULL,
   filter_stupid        BOOLEAN  NOT NULL,
   filter_political     BOOLEAN  NOT NULL,
   filter_tangent       BOOLEAN  NOT NULL,
   filter_informative   BOOLEAN  NOT NULL
);

CREATE TABLE shacker_marked_post (
   shacker_id           INTEGER NOT NULL REFERENCES shacker ON DELETE CASCADE,
   post_id              INTEGER NOT NULL REFERENCES post ON DELETE CASCADE,
   mark_type            INTEGER NOT NULL,
   PRIMARY KEY (shacker_id, post_id)
);

CREATE TABLE private_client_data (
   id          SERIAL   PRIMARY KEY,
   shacker_id  INTEGER  NOT NULL REFERENCES shacker ON DELETE CASCADE,
   client_code TEXT     NOT NULL,
   data        TEXT
);

CREATE TABLE client_session (
   token TEXT PRIMARY KEY,
   username TEXT NOT NULL,
   client_code TEXT NOT NULL,
   client_version TEXT NOT NULL,
   expire_date TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE event (
   id SERIAL PRIMARY KEY,
   date TIMESTAMP WITH TIME ZONE NOT NULL,
   type TEXT NOT NULL,
   data TEXT NOT NULL
);

CREATE TABLE reindex_request (
   id SERIAL PRIMARY KEY,
   post_id INTEGER NOT NULL
);

CREATE TABLE new_post_queue (
   id SERIAL PRIMARY KEY,
   username TEXT NOT NULL,
   parent_id INTEGER NOT NULL,
   body TEXT NOT NULL
);

INSERT INTO indexer (next_low_id, next_high_id) VALUES (31156792, 31156793);
INSERT INTO thread (id, bump_date) VALUES (0, '2013-11-29');
INSERT INTO post (id, thread_id, parent_id, author, category, date, body, author_c, body_c) VALUES (0, 0, NULL, '', 1, '2013-11-29', '', '', '');

CREATE INDEX idx_thread_date ON thread (date);
CREATE INDEX idx_post_index_body_c_ts ON post_index USING gin(body_c_ts);
CREATE INDEX idx_post_author_c ON post (author_c);
CREATE INDEX idx_post_category ON post (category);
CREATE INDEX idx_post_thread_id ON post (thread_id);
CREATE INDEX idx_post_date ON post (date);
CREATE INDEX idx_nuked_post_reattempts ON nuked_post (reattempts);
CREATE INDEX idx_private_client_data_shacker_id ON private_client_data (shacker_id);
CREATE INDEX idx_shacker_marked_post_shacker_id ON shacker_marked_post (shacker_id);

---------------------------------------------------------------


Moderation flag values
1 ON_TOPIC,
2 NOT_WORK_SAFE,
3 STUPID,
4 POLITICAL_RELIGIOUS,
5 TANGENT,
6 INFORMATIVE
7 NUKED