[Amavisadmin-svn] SF.net SVN: amavisadmin: [47] amavisadmin/trunk/db/postgres
Status: Beta
Brought to you by:
streindl
|
From: <str...@us...> - 2007-01-24 21:32:16
|
Revision: 47
http://amavisadmin.svn.sourceforge.net/amavisadmin/?rev=47&view=rev
Author: streindl
Date: 2007-01-24 13:32:13 -0800 (Wed, 24 Jan 2007)
Log Message:
-----------
DB initial load scripts
Added Paths:
-----------
amavisadmin/trunk/db/postgres/create_tables.sql
amavisadmin/trunk/db/postgres/initial_load.sql
Added: amavisadmin/trunk/db/postgres/create_tables.sql
===================================================================
--- amavisadmin/trunk/db/postgres/create_tables.sql (rev 0)
+++ amavisadmin/trunk/db/postgres/create_tables.sql 2007-01-24 21:32:13 UTC (rev 47)
@@ -0,0 +1,156 @@
+-- This file is part of AmavisAdmin
+--
+-- Copyright (C) 2007 Stephen Reindl
+--
+-- create initial data model
+--
+-- Table: users
+CREATE TABLE users
+(
+ id serial NOT NULL,
+ priority int4 NOT NULL DEFAULT 7,
+ policy_id int4 NOT NULL DEFAULT 1,
+ email varchar(255) NOT NULL,
+ fullname varchar(255),
+ "local" char(1),
+ CONSTRAINT users_pkey PRIMARY KEY (id),
+ CONSTRAINT users_email_key UNIQUE (email)
+);
+
+-- Table: wblist
+CREATE TABLE wblist
+(
+ rid int4 NOT NULL,
+ sid int4 NOT NULL,
+ wb varchar(10) NOT NULL,
+ CONSTRAINT wblist_pkey PRIMARY KEY (rid, sid)
+);
+
+-- Table: maddr
+CREATE TABLE maddr
+(
+ id serial NOT NULL,
+ email varchar(255) NOT NULL,
+ "domain" varchar(255) NOT NULL,
+ CONSTRAINT maddr_pkey PRIMARY KEY (id),
+ CONSTRAINT maddr_email_ux UNIQUE (email)
+);
+
+-- Table: mailaddr
+CREATE TABLE mailaddr
+(
+ id serial NOT NULL,
+ priority int4 NOT NULL DEFAULT 7,
+ email varchar(255) NOT NULL,
+ CONSTRAINT mailaddr_pkey PRIMARY KEY (id),
+ CONSTRAINT mailaddr_email_ux UNIQUE (email)
+);
+
+-- Table: policy
+CREATE TABLE policy
+(
+ id serial NOT NULL,
+ policy_name varchar(32),
+ virus_lover char(1),
+ spam_lover char(1),
+ banned_files_lover char(1),
+ bad_header_lover char(1),
+ bypass_virus_checks char(1),
+ bypass_spam_checks char(1),
+ bypass_banned_checks char(1),
+ bypass_header_checks char(1),
+ spam_modifies_subj char(1),
+ virus_quarantine_to varchar(64),
+ spam_quarantine_to varchar(64),
+ banned_quarantine_to varchar(64),
+ bad_header_quarantine_to varchar(64),
+ clean_quarantine_to varchar(64),
+ other_quarantine_to varchar(64),
+ spam_tag_level float8,
+ spam_tag2_level float8,
+ spam_kill_level float8,
+ spam_dsn_cutoff_level float8,
+ spam_quarantine_cutoff_level float8,
+ addr_extension_virus varchar(64),
+ addr_extension_spam varchar(64),
+ addr_extension_banned varchar(64),
+ addr_extension_bad_header varchar(64),
+ warnvirusrecip char(1),
+ warnbannedrecip char(1),
+ warnbadhrecip char(1),
+ newvirus_admin varchar(64),
+ virus_admin varchar(64),
+ banned_admin varchar(64),
+ bad_header_admin varchar(64),
+ spam_admin varchar(64),
+ spam_subject_tag varchar(64),
+ spam_subject_tag2 varchar(64),
+ message_size_limit int4,
+ banned_rulenames varchar(64),
+ CONSTRAINT policy_pkey PRIMARY KEY (id)
+);
+
+-- Table: msgs
+CREATE TABLE msgs
+(
+ mail_id varchar(12) NOT NULL,
+ secret_id varchar(12) DEFAULT '',
+ am_id varchar(20) NOT NULL,
+ time_num int4 NOT NULL,
+ time_iso timestamptz NOT NULL,
+ sid int4 NOT NULL,
+ policy varchar(255) DEFAULT '',
+ client_addr varchar(255) DEFAULT '',
+ size int4 NOT NULL,
+ content char(1),
+ quar_type char(1),
+ quar_loc varchar(255) DEFAULT '';
+ dsn_sent char(1),
+ spam_level float8,
+ message_id varchar(255) DEFAULT '',
+ from_addr varchar(255) DEFAULT '',
+ subject varchar(255) DEFAULT '',
+ host varchar(255) NOT NULL,
+ CONSTRAINT msgs_pkey PRIMARY KEY (mail_id),
+ CONSTRAINT msgs_sid_fkey FOREIGN KEY (sid)
+ REFERENCES maddr (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE RESTRICT
+);
+
+-- Index: msgs_idx_sid
+CREATE INDEX msgs_idx_sid ON msgs USING btree (sid);
+
+-- Index: msgs_idx_time_iso
+CREATE INDEX msgs_idx_time_iso ON msgs USING btree (time_iso);
+
+-- Table: quarantine
+CREATE TABLE quarantine
+(
+ mail_id varchar(12) NOT NULL,
+ chunk_ind int4 NOT NULL,
+ mail_text bytea NOT NULL,
+ CONSTRAINT quarantine_pkey PRIMARY KEY (mail_id, chunk_ind),
+ CONSTRAINT quarantine_mail_id_fkey FOREIGN KEY (mail_id)
+ REFERENCES msgs (mail_id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE CASCADE
+);
+
+-- Table: msgrcpt
+CREATE TABLE msgrcpt
+(
+ mail_id varchar(12) NOT NULL,
+ rid int4 NOT NULL,
+ ds char(1) NOT NULL,
+ rs char(1) NOT NULL,
+ bl char(1) DEFAULT ' '::bpchar,
+ wl char(1) DEFAULT ' '::bpchar,
+ bspam_level float8,
+ smtp_resp varchar(255) DEFAULT '',
+ CONSTRAINT msgrcpt_pkey PRIMARY KEY (mail_id, rid),
+ CONSTRAINT msgrcpt_mail_id_fkey FOREIGN KEY (mail_id)
+ REFERENCES msgs (mail_id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE CASCADE,
+ CONSTRAINT msgrcpt_rid_fkey FOREIGN KEY (rid)
+ REFERENCES maddr (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE RESTRICT
+);
\ No newline at end of file
Added: amavisadmin/trunk/db/postgres/initial_load.sql
===================================================================
--- amavisadmin/trunk/db/postgres/initial_load.sql (rev 0)
+++ amavisadmin/trunk/db/postgres/initial_load.sql 2007-01-24 21:32:13 UTC (rev 47)
@@ -0,0 +1,80 @@
+-- This file is part of AmavisAdmin
+--
+-- Copyright (C) 2007 Stephen Reindl
+--
+-- Perform an intial load for an empty database
+
+-- insert standard policies.
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('Non-paying', 'N','N','N','N', 'Y','Y','Y','N', 'Y', 3.0, 7, 10);
+
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('Uncensored', 'Y','Y','Y','Y', 'N','N','N','N', 'N', 3.0, 999, 999);
+
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('Wants all spam','N','Y','N','N', 'N','N','N','N', 'Y', 3.0, 999, 999);
+
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('Wants viruses', 'Y','N','Y','Y', 'N','N','N','N', 'Y', 3.0, 6.9, 6.9);
+
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('Normal', 'N','N','N','N', 'N','N','N','N', 'Y', 3.0, 6.9, 6.9);
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('Trigger happy', 'N','N','N','N', 'N','N','N','N', 'Y', 3.0, 5, 5);
+
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('Permissive', 'N','N','N','Y', 'N','N','N','N', 'Y', 3.0, 10, 20);
+
+INSERT
+ INTO policy
+ (policy_name, virus_lover, spam_lover, banned_files_lover,
+ bad_header_lover, bypass_virus_checks, bypass_spam_checks,
+ bypass_banned_checks, bypass_header_checks, spam_modifies_subj,
+ spam_tag_level, spam_tag2_level, spam_kill_level)
+VALUES ('6.5/7.8', 'N','N','N','N', 'N','N','N','N', 'N', 3.0, 6.5, 7.8);
+
+-- insert a catchall user that will be used to
+-- define the profile in case no other match
+-- was done.
+INSERT
+ INTO users
+ (priority, policy_id, email, local)
+ SELECT id, 5, '@.', 'N'
+ FROM policy
+ WHERE policy_name = 'Normal';
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|