Index: openacs-4/packages/acs-lang/sql/postgresql/upgrade/upgrade-5.0.0b4-5.0.0b5.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-lang/sql/postgresql/upgrade/upgrade-5.0.0b4-5.0.0b5.sql,v
diff -u -r1.1.2.1 -r1.1.2.2
--- openacs-4/packages/acs-lang/sql/postgresql/upgrade/upgrade-5.0.0b4-5.0.0b5.sql	9 Dec 2003 13:41:52 -0000	1.1.2.1
+++ openacs-4/packages/acs-lang/sql/postgresql/upgrade/upgrade-5.0.0b4-5.0.0b5.sql	11 Dec 2003 12:40:17 -0000	1.1.2.2
@@ -3,8 +3,8 @@
 --
 -- @author Peter Marklund
 
--- The lang_messages_keys.upgrade_status column carry any information over the corresponding
--- en_US row in the lang_messages table and were not being kept in sync.
+-- Changes to lang_message_keys table
+-- Column not needed as en_US row in lang_messages table has same info
 alter table lang_message_keys drop column upgrade_status;
 
 -- Add new columns to the lang_messages table 
@@ -25,3 +25,33 @@
                                        constraint lang_messages_us_ck
                                        check (upgrade_status in ('no_upgrade', 'added', 'deleted', 'updated'));
 update lang_messages_audit set deleted_p = 'f', conflict_p = 'f', upgrade_status = 'no_upgrade';
+
+-- Missing this primary key made some queries below very slow
+alter table lang_messages_audit add constraint lang_messages_audit_pk primary key (package_key, message_key, locale, overwrite_date);
+
+-- Make sure sync_time is not null for the first message imported. If we don't
+-- do this we are missing a set of base messages to merge against on the next 
+-- catalog import.
+-- Messages with only one revision
+
+-- Messages with only one revision
+-- This query is slow. Not sure how to speed it up.
+update lang_messages lm
+       set lm.sync_time = current_timestamp
+       where not exists (select 1
+                               from lang_messages_audit lma
+                               where lma.package_key = lm.package_key
+                                 and lma.message_key = lm.message_key
+                                 and lma.locale = lm.locale
+                              );
+
+-- Messages with multiple revisions
+-- This query is slow as well.
+ update lang_messages_audit lma1
+        set lma1.sync_time = current_timestamp
+        where lma1.overwrite_date = (select min(lma2.overwrite_date)
+                                    from lang_messages_audit lma2
+                                    where lma2.package_key = lma1.package_key
+                                      and lma2.message_key = lma1.message_key
+                                      and lma2.locale      = lma1.locale
+                                    );
