sylph-searcher 1.2.0-10 / usr / share / sylph-searcher / sql / create.sql

DROP TABLE IF EXISTS msginfo;
CREATE TABLE msginfo (
	msg_sid BIGSERIAL PRIMARY KEY,
	file_size INTEGER,
	file_mtime TIMESTAMP,
	msg_date TIMESTAMP,
	flags INTEGER,
	hdr_from TEXT,
	hdr_to TEXT,
	hdr_cc TEXT,
	hdr_newsgroups TEXT,
	hdr_subject TEXT,
	hdr_msgid TEXT UNIQUE NOT NULL,
	hdr_inreplyto TEXT,
	hdr_references TEXT,
	body_text TEXT,
	body_index TSVECTOR,
	attach_num INTEGER
);

CREATE INDEX msginfo_msg_date_index ON msginfo (msg_date);
CREATE INDEX msginfo_body_index ON msginfo USING gin (body_index);

-- CREATE TRIGGER msginfo_update BEFORE UPDATE OR INSERT ON msginfo
-- FOR EACH ROW EXECUTE PROCEDURE tsearch(body_index, wakachi, body_text);

DROP TABLE IF EXISTS msg_folderinfo;
CREATE TABLE msg_folderinfo (
	msg_sid BIGINT NOT NULL,
	folder_id TEXT,
	msgnum INTEGER,
	PRIMARY KEY (folder_id, msgnum)
);

CREATE INDEX msg_sid_index ON msg_folderinfo (msg_sid);