-- sql/create_db.sql
-- This file is part of Decimail; see http://decimail.org
-- (C) 2004 Philip Endecott
 
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 
drop table messages cascade;
drop table recipients cascade;
drop sequence msg_ids;


begin;

create table messages (
msg_id			integer	not null	primary key,
owner			text,
subject			text,
subject_tsidx		tsvector,
msgdate			timestamp with time zone,
from_addr		text,
rfc822_messageid	text,
headers			bytea,
body			bytea
);

create trigger update_subject_tsidx 
    before update or insert on messages 
    for each row
        execute procedure tsearch2(subject_tsidx, subject);


create table recipients (
recip_type  text     not null,
addr        text,
msg_id      integer  not null    references messages
                                     on delete cascade
                                     on update cascade
);


create sequence msg_ids start 20000;


end;