create_tables.sql from Amavisd-new Maintenance at Krugle

Show create_tables.sql syntax highlighted

-- 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
);
See more files for this project here