Index: openacs-4/packages/bulk-mail/sql/postgresql/upgrade/upgrade-0.1a-0.2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/bulk-mail/sql/postgresql/upgrade/Attic/upgrade-0.1a-0.2.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/bulk-mail/sql/postgresql/upgrade/upgrade-0.1a-0.2.sql 28 Aug 2003 09:41:52 -0000 1.2 +++ openacs-4/packages/bulk-mail/sql/postgresql/upgrade/upgrade-0.1a-0.2.sql 30 Sep 2003 12:10:50 -0000 1.3 @@ -1,13 +1,72 @@ --- create the new query column as type 'text' -alter table bulk_mail_messages add column query2 text; +-- Change query column from varchar(4000) to text +-- +-- NOTE: Since PG 7.2 doesn't support dropping of columns or +-- adding of not null constraints, we recreate the table. +-- +-- @author Peter Marklund -alter table bulk_mail_messages alter column query2 set not null; +-- Temp table. Don't use constraints to avoid naming conflicts +create table bulk_mail_messages_tmp ( + bulk_mail_id integer, + package_id integer, + send_date timestamptz, + sent_p boolean, + from_addr varchar(4000), + subject varchar(4000), + reply_to varchar(4000), + extra_headers varchar(4000), + message text, + query text, + status varchar(100) +); --- update the rows -update bulk_mail_messages set query2 = query; +-- Save data to temp table +insert into bulk_mail_messages_tmp select * from bulk_mail_messages; --- drop the old column -alter table bulk_mail_messages drop column query cascade; +-- Get rid of old table +drop table bulk_mail_messages; --- rename the new column ot the correct name -alter table bulk_mail_messages rename column query2 to query; +-- Create new table with text datatype +create table bulk_mail_messages ( + bulk_mail_id integer + constraint bm_messages_bulk_mail_id_fk + references acs_objects (object_id) + constraint bm_messages_bulk_mail_id_pk + primary key, + package_id integer + constraint bm_messages_package_id_fk + references apm_packages (package_id) + constraint bm_messages_package_id_nn + not null, + send_date date + default now() + constraint bm_messages_send_date_nn + not null, + sent_p boolean + default 'f' + constraint bm_messages_sent_p_nn + not null, + from_addr varchar(4000) + constraint bm_messages_from_addr_nn + not null, + subject varchar(4000), + reply_to varchar(4000), + extra_headers varchar(4000), + message text + constraint bm_messages_message_nn + not null, + query text + constraint bm_messages_query_nn + not null, + status varchar(100) + default 'pending' + constraint bm_messages_status_nn + not null + +); + +-- Copy the data back +insert into bulk_mail_messages select * from bulk_mail_messages_tmp; + +-- Get rid of temp table +drop table bulk_mail_messages_tmp; Index: openacs-4/packages/bulk-mail/sql/postgresql/upgrade/upgrade-0.2-0.3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/bulk-mail/sql/postgresql/upgrade/Attic/upgrade-0.2-0.3.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/bulk-mail/sql/postgresql/upgrade/upgrade-0.2-0.3.sql 23 Sep 2003 23:30:36 -0000 1.3 +++ openacs-4/packages/bulk-mail/sql/postgresql/upgrade/upgrade-0.2-0.3.sql 30 Sep 2003 12:10:50 -0000 1.4 @@ -1,12 +1,72 @@ --- send_date needs to be of type timestampz +-- Change send_date column from timestamp to timestamptz +-- +-- NOTE: Since PG 7.2 doesn't support dropping of columns or +-- adding of not null constraints, we recreate the table. +-- +-- @author Peter Marklund -alter table bulk_mail_messages alter column send_date drop not null; -alter table bulk_mail_messages add column new_send_date timestamptz; -alter table bulk_mail_messages alter column new_send_date set default now(); -update bulk_mail_messages set new_send_date = send_date; -alter table bulk_mail_messages alter column new_send_date set not null; -alter table bulk_mail_messages drop column send_date; -alter table bulk_mail_messages rename column new_send_date to send_date; +-- Temp table. Don't use constraints to avoid naming conflicts +create table bulk_mail_messages_tmp ( + bulk_mail_id integer, + package_id integer, + send_date timestamptz, + sent_p boolean, + from_addr varchar(4000), + subject varchar(4000), + reply_to varchar(4000), + extra_headers varchar(4000), + message text, + query text, + status varchar(100) +); -select define_function_args('bulk_mail__new','bulk_mail_id,package_id,send_date,date_format,status;pending,from_addr,subject,reply_to,extra_headers,message,query,creation_date;now(),creation_user,creation_ip,context_id'); +-- Save data to temp table +insert into bulk_mail_messages_tmp select * from bulk_mail_messages; +-- Get rid of old table +drop table bulk_mail_messages; + +-- Create new table with timestamptz datatype +create table bulk_mail_messages ( + bulk_mail_id integer + constraint bm_messages_bulk_mail_id_fk + references acs_objects (object_id) + constraint bm_messages_bulk_mail_id_pk + primary key, + package_id integer + constraint bm_messages_package_id_fk + references apm_packages (package_id) + constraint bm_messages_package_id_nn + not null, + send_date timestamptz + default now() + constraint bm_messages_send_date_nn + not null, + sent_p boolean + default 'f' + constraint bm_messages_sent_p_nn + not null, + from_addr varchar(4000) + constraint bm_messages_from_addr_nn + not null, + subject varchar(4000), + reply_to varchar(4000), + extra_headers varchar(4000), + message text + constraint bm_messages_message_nn + not null, + query text + constraint bm_messages_query_nn + not null, + status varchar(100) + default 'pending' + constraint bm_messages_status_nn + not null + +); + +-- Copy the data back +insert into bulk_mail_messages select * from bulk_mail_messages_tmp; + +-- Get rid of temp table +drop table bulk_mail_messages_tmp;