update_v0_v1.sql from Amavisd-new Maintenance at Krugle

Show update_v0_v1.sql syntax highlighted

-- Database update script for amavisadmin 
-- version 0.1
-- db-version 1

-- new tables

-- #1638972
create table configuration (
	"key" varchar(64) not null, 
	job_ref varchar(64) not null, 
	version int4 default 0 not null, 
	"value" varchar(255) not null, 
        primary key ("key", job_ref)
);

-- #1640285 
create table job (
	job_name varchar(64) not null, 
	version int4 default 0 not null, 
	job_description varchar(255) not null, 
	job_cron_settings varchar(30) not null, 
	job_class varchar(255) not null, 
        job_special bool not null default false,
	job_enabled bool not null default false, 
	job_last_run_date timestamp, 
	primary key (job_name)
);

-- postponed to 0.2
--create table job_status (
--        job_name varchar(64) not null,
--        job_run_date timestamp with time zone 
--                     not null,
--        version int4 default 0 not null, 
--        job_status character(1) default ' ' 
--                                not null
--                                check (job_status in (' ', 'S', 'E')),
--        job_status_message text not null,
--        primary key (job_name, job_run_date)
--);

create table mail_queue (
	id int4 not null, 
	version int4 default 0 not null, 
	sender varchar(255) not null, 
	mail_status char(1) default ' ' not null 
                            check (mail_status in (' ', 'I', 'S', 'E')), 
	send_date timestamp, 
	subject varchar(255) not null, 
	encoding varchar(128) not null, 
	mail_text text not null, 
	error varchar(255), 
	primary key (id)
);

create sequence mail_queue_id_seq;


create table mail_queue_rcpt (
	id int4 not null, 
	version int4 default 0 not null, 
	rcpt_type char(1) check (rcpt_type IN ('T', 'C', 'B')), 
	receipient varchar(255) not null, 
	mail_queue_id int4 not null, 
	primary key (id)
);

create sequence mail_queue_rcpt_id_seq;

-- #1640285 
create table request (
	"key" varchar(255) not null, 
	version int4 default 0 not null, 
	handler_Type varchar(255) not null, 
	status char(1) not null, 
	expiration_date timestamp not null, 
	param1 varchar(255), 
	param2 varchar(255), 
	primary key ("key")
);

-- 2. changes to existing tables

-- 2.1 Adding version fields
alter table msgrcpt add column version int4 default 0 not null;
alter table maddr add column version int4 default 0 not null;
alter table policy add column version int4 default 0 not null;
alter table quarantine add column version int4 default 0 not null;
alter table users add column version int4 default 0 not null;
alter table msgs add column version int4 default 0 not null;
alter table wblist add column version int4 default 0 not null;

-- 2.2 Business related fields

-- 2.2.1 MADDR.USER_ID
--   The field user id contains a link to the user which owns this entry. 
--   This can be used to handle more than one mail address per user.
alter table maddr add column user_id int4;

-- 2.2.2 MSGRCPT.REQUEST_USER
--  This field contains the user-id of the user which requested the release 
--  of the corresponding mail recipient entry.
alter table msgrcpt add column request_user int4;

-- 2.2.3 USER.PASSWORD
--  Obviously the password of the user. The password is stored encrypted by 
--  using a hasing function.
alter table users add column password varchar(255);

-- 2.2.4 USER.ADMIN
--   If this field is true, the user is an admin user. This field is temporary 
--   only until roles are implemented.
alter table users add column admin bool default false;

-- 2.2.5 USER.LAST_LOGIN
--  This field stores the timstamp when the user has logged in the last time.
alter table users add column last_login timestamp with time zone;

-- 2.2.6 USER.LOCKED
--  If this field is true, the user is locked, i.e. he cannot login.
alter table users add column locked bool default false;

-- 2.2.6 USER.NEED_PASSWORD_RESET
--  If this field is true, the user is locked, i.e. he cannot login.
alter table users add column need_password_reset bool default false;

-- 3. constraints
-- basically new references to new fields and renaming of existing 
-- constraints with internal names ($1...).

alter table maddr add constraint maddr_fk_1 foreign key (user_id) references users;

alter table configuration add constraint constraint_fk_1 
        foreign key (job_ref) references job;

alter table mail_queue_rcpt add constraint mail_queue_rcpt_fk_1 
        foreign key (mail_queue_id) references mail_queue;

alter table msgrcpt drop constraint "$1";
alter table msgrcpt drop constraint "$2";
alter table msgrcpt add 
        constraint msgrcpt_fk_1 foreign key (request_user) references users;
alter table msgrcpt add constraint msgrcpt_fk_2 foreign key (rid) references maddr;
alter table msgrcpt add constraint msgrcpt_fk_3 foreign key (mail_id) references msgs;

alter table msgs drop constraint "$1";
alter table msgs add constraint msgs_fk_1 foreign key (sid) references maddr;

alter table quarantine drop constraint "$1";
alter table quarantine add constraint quarantine_fk_1 foreign key (mail_id) references msgs;

-- 4. Load data
-- 4.1 Migration Job
insert into job (job_name, job_description, job_cron_settings,
                 job_class, job_enabled, job_special) 
       values ('$$Migrate V0 to V1', 'Migration', 'dummy', 
               'de.sreindl.amavisadmin.migration.MigrateV0toV1', true, true);

-- 4.2 Dummy job for global variable references
insert into job (job_name, job_description, job_cron_settings,
                 job_class, job_enabled, job_special) 
       values ('$$Global', 'Global Variables', 'dummy', 
               'de.sreindl.amavisadmin.jobs.BaseJob', false, true);
    

-- 4. Configuration version update
-- #1640315
insert into configuration ("key", job_ref, "value") 
       values ('amavisadmin.db_version', '$$Global', '1');
See more files for this project here