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 -N -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;