-- sql/example.sql
-- This file is part of Decimail; see http://decimail.org
-- (C) 2004-7 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.
 
-- Example database setup - change this to suit your needs

begin;

-- Create users and specify IMAP passwords
-- (some form of obfuscation would help here)
-- insert into users (username, passwd) values ('john_smith', 'superman');
-- insert into users (username, passwd) values ('bill', 'foobar');

-- NOTE: take care before recreating the users table using the
-- preceding lines.  Some tables refer to users as a foreign key; if
-- you delete a user - even to immediately recreate it - rows in those
-- other tables may be deleted or altered.  This may not be what you
-- wanted.  It is probably best to add or change users manually rather
-- than by re-running this script.
-- The main "messages" table is one table that could / should have a
-- foreign key to the users table.  If this key were present, deleting
-- a user would cause all of their messages to be deleted, which is
-- probably what one would want in a production system.  However for
-- the time being I think that this has more potential for disaster
-- than actual benefits, so this foreign key is not implemented.

-- The remaining tables modified here do not have any foreign keys or
-- other constraints to worry about.  This script first deletes the
-- content of the tables (using truncate) before repopulating them.
-- Beware if you make any changes to these tables manually as these
-- changes will be lost if you re-run this.

-- For each user, specify email address patterns for incoming messages
-- that belong to them.
-- john_smith only gets email to the single address john_smith@domain.dom,
-- but bill gets all messages to bill.com as well bill@domain.dom.
-- (% is the SQL wildcard character.)
truncate incoming_addresses;
insert into incoming_addresses (username, address_pat)
		values ('john_smith', 'john_smith@domain.dom');
insert into incoming_addresses (username, address_pat)
		values ('bill', 'bill@domain.dom');
insert into incoming_addresses (username, address_pat)
		values ('bill', '%@bill.com');


-- Each user may choose to use anti-spam outgoing address modification.
-- john_smith doesn't use this.  bill sets up his email client to set
-- the sender to auto@bill.com; this is replaced by a random address of
-- the form bill_####@bill.com where the #s are replaced by random
-- letters.
truncate auto_users;
insert into auto_users values ('bill', 'auto@bill.com', 'bill_####@bill.com');


-- Domains that get custom From: addresses.
-- Whenever bill sends a message to any address ending "books.com",
-- his from: address is set to "spam_from_books_com@bill.com".
truncate custom_domains;
copy custom_domains (username, domain, custom_addr)
	from stdin with delimiter ',';
bill,books.com,spam_from_books_com@bill.com
\.