[Amavisadmin-svn] SF.net SVN: amavisadmin: [47] amavisadmin/trunk/db/postgres

[<str...@us...>]

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.