[Aplaws-commit] r163 - in ccm-core/trunk/sql/ccm-core: . fixes fixes/formbuilder fixes/messaging

[<mb...@re...>]

Author: mbooth
Date: 2004-12-22 14:58:03 +0100 (Wed, 22 Dec 2004)
New Revision: 163

Added:
   ccm-core/trunk/sql/ccm-core/fixes/
   ccm-core/trunk/sql/ccm-core/fixes/formbuilder/
   ccm-core/trunk/sql/ccm-core/fixes/formbuilder/delete-bogus-form-widgets.sql
   ccm-core/trunk/sql/ccm-core/fixes/messaging/
   ccm-core/trunk/sql/ccm-core/fixes/messaging/update-thread-replies.sql
Log:
A couple of scripts which clean up some dm bogosity.


Added: ccm-core/trunk/sql/ccm-core/fixes/formbuilder/delete-bogus-form-widgets.sql
===================================================================
--- ccm-core/trunk/sql/ccm-core/fixes/formbuilder/delete-bogus-form-widgets.sql	2004-12-22 13:54:02 UTC (rev 162)
+++ ccm-core/trunk/sql/ccm-core/fixes/formbuilder/delete-bogus-form-widgets.sql	2004-12-22 13:58:03 UTC (rev 163)
@@ -0,0 +1,277 @@
+-- Delete bogus entries
+delete from bebop_component_hierarchy h1
+      where not exists(
+                select 1
+                  from acs_objects
+                 where object_id = h1.container_id
+                   and object_type = 'com.arsdigita.formbuilder.FormSection'
+            )
+        and not exists(
+                select 1
+                  from bebop_component_hierarchy h2
+                 where h2.component_id = h1.container_id
+            );
+
+delete from bebop_widgets w
+      where not exists( select 1 from bebop_component_hierarchy h
+                                where h.component_id = w.widget_id
+                                   or h.container_id = w.widget_id );
+
+delete from bebop_listener_map m
+      where not exists( select 1 from bebop_component_hierarchy h
+                                where h.component_id = m.component_id
+                                   or h.container_id = m.component_id );
+
+delete from bebop_listeners l
+      where not exists( select 1 from bebop_listener_map m
+                                where l.listener_id = m.listener_id );
+
+delete from bebop_components c
+      where not exists(
+                select 1
+                  from bebop_component_hierarchy h
+                 where h.component_id = c.component_id
+                    or h.container_id = c.component_id
+            )
+        and not exists (
+                select 1
+                  from acs_objects o
+                 where o.object_id = c.component_id
+                   and o.object_type = 'com.arsdigita.formbuilder.FormSection'
+            );
+
+delete from bebop_options o
+      where not exists( select 1 from bebop_components c
+                                where o.option_id = c.component_id );
+
+-- Delete orphaned Listeners
+
+delete from bebop_component_hierarchy
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Listener'
+                   and not exists(
+                           select 1
+                             from bebop_listeners l
+                            where o.object_id = l.listener_id
+                       )
+            );
+
+delete from bebop_components
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Listener'
+                   and not exists(
+                           select 1
+                             from bebop_listeners l
+                            where o.object_id = l.listener_id
+                       )
+            );
+
+delete from object_context
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Listener'
+                   and not exists(
+                           select 1
+                             from bebop_listeners l
+                            where o.object_id = l.listener_id
+                       )
+            );
+
+delete from object_container_map
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Listener'
+                   and not exists(
+                           select 1
+                             from bebop_listeners l
+                            where o.object_id = l.listener_id
+                       )
+            );
+
+delete from acs_objects o
+      where o.object_type = 'com.arsdigita.formbuilder.Listener'
+        and not exists(
+                select 1
+                  from bebop_listeners l
+                 where o.object_id = l.listener_id
+            );
+
+-- Delete orphaned WidgetLabels
+
+delete from bebop_component_hierarchy
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.WidgetLabel'
+                   and not exists(
+                           select 1
+                             from forms_widget_label l
+                            where o.object_id = l.label_id
+                       )
+            );
+
+delete from bebop_components
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.WidgetLabel'
+                   and not exists(
+                           select 1
+                             from forms_widget_label l
+                            where o.object_id = l.label_id
+                       )
+            );
+
+delete from object_context
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.WidgetLabel'
+                   and not exists(
+                           select 1
+                             from forms_widget_label l
+                            where o.object_id = l.label_id
+                       )
+            );
+
+delete from object_container_map
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.WidgetLabel'
+                   and not exists(
+                           select 1
+                             from forms_widget_label l
+                            where o.object_id = l.label_id
+                       )
+            );
+
+delete from acs_objects o
+      where o.object_type = 'com.arsdigita.formbuilder.WidgetLabel'
+        and not exists(
+                select 1
+                  from forms_widget_label l
+                 where o.object_id = l.label_id
+            );
+
+-- Delete orphaned Options
+
+delete from bebop_component_hierarchy
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Option'
+                   and not exists(
+                           select 1
+                             from bebop_options b
+                            where o.object_id = b.option_id
+                       )
+            );
+
+delete from bebop_components
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Option'
+                   and not exists(
+                           select 1
+                             from bebop_options b
+                            where o.object_id = b.option_id
+                       )
+            );
+
+delete from object_context
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Option'
+                   and not exists(
+                           select 1
+                             from bebop_options b
+                            where o.object_id = b.option_id
+                       )
+            );
+
+delete from object_container_map
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Option'
+                   and not exists(
+                           select 1
+                             from bebop_options b
+                            where o.object_id = b.option_id
+                       )
+            );
+
+delete from acs_objects o
+      where o.object_type = 'com.arsdigita.formbuilder.Option'
+        and not exists(
+                select 1
+                  from bebop_options b
+                 where o.object_id = b.option_id
+            );
+
+-- Delete orphaned Widgets
+
+delete from bebop_component_hierarchy
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Widget'
+                   and not exists(
+                           select 1
+                             from bebop_widgets w
+                            where o.object_id = w.widget_id
+                       )
+            );
+
+delete from bebop_components
+      where component_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Widget'
+                   and not exists(
+                           select 1
+                             from bebop_widgets w
+                            where o.object_id = w.widget_id
+                       )
+            );
+
+delete from object_context
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Widget'
+                   and not exists(
+                           select 1
+                             from bebop_widgets w
+                            where o.object_id = w.widget_id
+                       )
+            );
+
+delete from object_container_map
+      where object_id in (
+                select o.object_id
+                  from acs_objects o
+                 where o.object_type = 'com.arsdigita.formbuilder.Widget'
+                   and not exists(
+                           select 1
+                             from bebop_widgets w
+                            where o.object_id = w.widget_id
+                       )
+            );
+
+delete from acs_objects o
+      where o.object_type = 'com.arsdigita.formbuilder.Widget'
+        and not exists(
+                select 1
+                  from bebop_widgets w
+                 where o.object_id = w.widget_id
+            );

Added: ccm-core/trunk/sql/ccm-core/fixes/messaging/update-thread-replies.sql
===================================================================
--- ccm-core/trunk/sql/ccm-core/fixes/messaging/update-thread-replies.sql	2004-12-22 13:54:02 UTC (rev 162)
+++ ccm-core/trunk/sql/ccm-core/fixes/messaging/update-thread-replies.sql	2004-12-22 13:58:03 UTC (rev 163)
@@ -0,0 +1,6 @@
+  update message_threads t
+     set num_replies = (
+             select count( m.message_id )
+               from messages m
+              where m.root_id = t.root_id
+         );