[Aplaws-commit] r1162 - in trunk/ccm-cms/sql/ccm-cms: oracle-se/upgrade/6.0.1-6.1.0 postgres/upgrade/6.0.1-6.1.0 upgrade

[<fa...@vh...>]

Author: fabrice
Date: 2006-06-14 12:59:54 +0200 (Wed, 14 Jun 2006)
New Revision: 1162

Added:
   trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql
   trunk/ccm-cms/sql/ccm-cms/postgres/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql
Modified:
   trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/insert-new-privileges.sql
   trunk/ccm-cms/sql/ccm-cms/upgrade/oracle-se-6.0.1-6.1.0.sql
   trunk/ccm-cms/sql/ccm-cms/upgrade/postgres-6.0.1-6.1.0.sql
Log:
speed up upgrade


Added: trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql
===================================================================
--- trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql	2006-06-14 10:47:06 UTC (rev 1161)
+++ trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql	2006-06-14 10:59:54 UTC (rev 1162)
@@ -0,0 +1,45 @@
+--
+-- Copyright (C) 2006 Runtime Collective Ltd. All Rights Reserved.
+--
+-- This library is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU Lesser General Public License
+-- as published by the Free Software Foundation; either version 2.1 of
+-- the License, or (at your option) any later version.
+--
+-- This library is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+-- Lesser General Public License for more details.
+--
+-- You should have received a copy of the GNU Lesser General Public
+-- License along with this library; if not, write to the Free Software
+-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+--
+-- $Id: denormalize-versioning.sql 285 2005-02-22 00:29:02Z sskracic $
+-- $DateTime: 2004/08/17 23:15:09 $
+
+
+
+
+-- this is slightly quicker than the original upgrade script, for large databases.
+-- quicker as in 13 seconds rather than 2+ days
+
+insert into acs_auditing (
+           object_id,
+           creation_user,
+           creation_date,
+           creation_ip,
+           modifying_user,
+           last_modified,
+           modifying_ip
+)
+select tt.itemId, t1.modifying_user, t1.timestamp, t1.modifying_ip, t2.modifying_user, t2.timestamp, t2.modifying_ip
+from vcx_txns t1, vcx_txns t2, (select min(oc.txn_id) as minId, max(oc.txn_id) as maxId, i.item_id as itemId
+                                from vcx_obj_changes oc, cms_items i
+                                where oc.obj_id in (select o.object_type || ';id:1:' || o.object_id 
+                                                    from acs_objects o
+                                                    where o.object_id = i.item_id)
+                                group by i.item_id) tt
+where t1.id = tt.minId
+and t2.id = tt.maxId;
+

Modified: trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/insert-new-privileges.sql
===================================================================
--- trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/insert-new-privileges.sql	2006-06-14 10:47:06 UTC (rev 1161)
+++ trunk/ccm-cms/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/insert-new-privileges.sql	2006-06-14 10:59:54 UTC (rev 1162)
@@ -24,3 +24,5 @@
 ) values (
   'cms_apply_alternate_workflows', 'Apply Alternate Workflows', 68, 'item'
 );
+
+exec dnm_priv_utils.add_privilege('cms_apply_alternate_workflows');

Added: trunk/ccm-cms/sql/ccm-cms/postgres/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql
===================================================================
--- trunk/ccm-cms/sql/ccm-cms/postgres/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql	2006-06-14 10:47:06 UTC (rev 1161)
+++ trunk/ccm-cms/sql/ccm-cms/postgres/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql	2006-06-14 10:59:54 UTC (rev 1162)
@@ -0,0 +1,45 @@
+--
+-- Copyright (C) 2006 Runtime Collective Ltd. All Rights Reserved.
+--
+-- This library is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU Lesser General Public License
+-- as published by the Free Software Foundation; either version 2.1 of
+-- the License, or (at your option) any later version.
+--
+-- This library is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+-- Lesser General Public License for more details.
+--
+-- You should have received a copy of the GNU Lesser General Public
+-- License along with this library; if not, write to the Free Software
+-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+--
+-- $Id: denormalize-versioning.sql 285 2005-02-22 00:29:02Z sskracic $
+-- $DateTime: 2004/08/17 23:15:09 $
+
+
+
+
+-- this is slightly quicker than the original upgrade script, for large databases.
+-- quicker as in 13 seconds rather than 2+ days
+
+insert into acs_auditing (
+           object_id,
+           creation_user,
+           creation_date,
+           creation_ip,
+           modifying_user,
+           last_modified,
+           modifying_ip
+)
+select tt.itemId, t1.modifying_user, t1.timestamp, t1.modifying_ip, t2.modifying_user, t2.timestamp, t2.modifying_ip
+from vcx_txns t1, vcx_txns t2, (select min(oc.txn_id) as minId, max(oc.txn_id) as maxId, i.item_id as itemId
+                                from vcx_obj_changes oc, cms_items i
+                                where oc.obj_id in (select o.object_type || ';id:1:' || o.object_id 
+                                                    from acs_objects o
+                                                    where o.object_id = i.item_id)
+                                group by i.item_id) tt
+where t1.id = tt.minId
+and t2.id = tt.maxId;
+

Modified: trunk/ccm-cms/sql/ccm-cms/upgrade/oracle-se-6.0.1-6.1.0.sql
===================================================================
--- trunk/ccm-cms/sql/ccm-cms/upgrade/oracle-se-6.0.1-6.1.0.sql	2006-06-14 10:47:06 UTC (rev 1161)
+++ trunk/ccm-cms/sql/ccm-cms/upgrade/oracle-se-6.0.1-6.1.0.sql	2006-06-14 10:59:54 UTC (rev 1162)
@@ -20,7 +20,7 @@
 
 PROMPT Red Hat Enterprise CMS 6.0.1 -> 6.1.0 Upgrade Script (Oracle)
 
-@@ ../oracle-se/upgrade/6.0.1-6.1.0/denormalize-versioning.sql
+@@ ../oracle-se/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql
 @@ ../oracle-se/upgrade/6.0.1-6.1.0/add-mpa-page-break.sql
 @@ ../oracle-se/upgrade/6.0.1-6.1.0/alter-table-authoring_steps.sql
 @@ ../oracle-se/upgrade/6.0.1-6.1.0/alter-table-cms_form_item.sql

Modified: trunk/ccm-cms/sql/ccm-cms/upgrade/postgres-6.0.1-6.1.0.sql
===================================================================
--- trunk/ccm-cms/sql/ccm-cms/upgrade/postgres-6.0.1-6.1.0.sql	2006-06-14 10:47:06 UTC (rev 1161)
+++ trunk/ccm-cms/sql/ccm-cms/upgrade/postgres-6.0.1-6.1.0.sql	2006-06-14 10:59:54 UTC (rev 1162)
@@ -24,7 +24,7 @@
 
 alter table content_sections drop content_expiration_digest_id;
 
-\i ../postgres/upgrade/6.0.1-6.1.0/denormalize-versioning.sql
+\i ../postgres/upgrade/6.0.1-6.1.0/denormalize-versioning-quick.sql
 \i ../postgres/upgrade/6.0.1-6.1.0/alter-table-authoring_steps.sql
 \i ../postgres/upgrade/6.0.1-6.1.0/alter-table-cms_form_item.sql
 \i ../postgres/upgrade/6.0.1-6.1.0/insert-new-privileges.sql