Index: openacs-4/packages/xotcl-core/tcl/01-debug-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/xotcl-core/tcl/01-debug-procs.tcl,v
diff -u -r1.12 -r1.13
--- openacs-4/packages/xotcl-core/tcl/01-debug-procs.tcl	22 May 2007 12:45:37 -0000	1.12
+++ openacs-4/packages/xotcl-core/tcl/01-debug-procs.tcl	24 May 2007 08:56:33 -0000	1.13
@@ -139,13 +139,6 @@
     }
   }
 
-  #
-  # a simple calback for cleanup of per connection objects
-  #
-  ::xotcl::Object instproc destroy_on_cleanup {} {
-    set ::xotcl_cleanup([self]) [list [self] destroy]
-  }
-
 }
 
 # ::xotcl::Class instproc import {class pattern} {
@@ -188,14 +181,28 @@
   #
   if {[catch {set registered [ns_ictl gettraces freeconn]}]} {
     ns_log notice "*** you should really upgrade to Aolserver 4.5"
-    ns_ictl oncleanup ::xo::cleanup
+    # "ns_ictl oncleanup" is called after variables are deleted
+    # ns_ictl oncleanup "ns_log notice --oncleanup"
+
+    ::xotcl::Object instproc destroy_on_cleanup {} {
+      #my log "--cleanup adding ::xotcl_cleanup([self]) [list [self] destroy]"
+      if {![array exists ::xotcl_cleanup]} {
+	ns_atclose ::xo::cleanup
+      }
+      set ::xotcl_cleanup([self]) [list [self] destroy]
+
+    }
   } else {
+    ::xotcl::Object instproc destroy_on_cleanup {} {
+      set ::xotcl_cleanup([self]) [list [self] destroy]
+    }
+    # register only once
     if {[lsearch $registered ::xo::cleanup] == -1} {
       ns_ictl trace freeconn ::xo::cleanup
     }
   }
   proc cleanup {} {
-    ns_log notice "*** start of cleanup"
+    #ns_log notice "*** start of cleanup ([array get ::xotcl_cleanup])"
     set at_end ""
     foreach {name cmd} [array get ::xotcl_cleanup] {
       if {![::xotcl::Object isobject $name]} {
@@ -221,7 +228,7 @@
     if {[catch {eval $at_end} errorMsg]} {
       ns_log notice "Error during ::xo::cleanup: $errorMsg $::errorInfo"
     }
-    ns_log notice "*** end of cleanup"
+    #ns_log notice "*** end of cleanup (at_end $at_end)"
   }
 }
 
Index: openacs-4/packages/xotcl-core/tcl/05-db-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/xotcl-core/tcl/05-db-procs.tcl,v
diff -u -r1.21 -r1.22
--- openacs-4/packages/xotcl-core/tcl/05-db-procs.tcl	23 May 2007 07:05:11 -0000	1.21
+++ openacs-4/packages/xotcl-core/tcl/05-db-procs.tcl	24 May 2007 08:56:33 -0000	1.22
@@ -215,7 +215,7 @@
   } else { ;# Oracle
     proc map_sql_datatype {type} {
       switch $type {
-        text {set type varchar(64000)}
+        text {set type varchar2(4000)}
       }
       return $type
     }
@@ -231,21 +231,22 @@
       {-orderby ""}
       {-map_function_names false}
     } {
+      # "-start" not used so far
       set order_clause [expr {$orderby ne "" ? "ORDER BY $orderby" : ""}]
       set group_clause [expr {$groupby ne "" ? "GROUP BY $groupby" : ""}]
-      if {$map_function_calls} {set vars [::xo::db::function_name $vars]}
-      set sql "SELECT $vars FROM $from $start WHERE $where $group_clause"
+      if {$map_function_names} {set vars [::xo::db::function_name $vars]}
+      set sql "SELECT $vars FROM $from WHERE $where $group_clause"
       if {$limit ne "" || $offset ne ""} {
         if {$offset eq ""} {
           set limit_clause "ROWNUM <= $limit"
-        } else {$limit eq ""} {
+        } elseif {$limit eq ""} {
           set limit_clause "ROWNUM >= $offset"
         } else {
           set limit_clause "ROWNUM BETWEEN $offset and [expr {$offset+$limit}]"
         }
         # for pagination, we will need an "inner" sort, such as 
         # SELECT * FROM (SELECT ...., ROW_NUMBER() OVER (ORDER BY ...) R FROM table) WHERE R BETWEEN 0 and 100 
-        set sql "SELECT * FROM (SELECT $sql) WHERE ROWNUM <= $limit_clause $order_clause"
+        set sql "SELECT * FROM ($sql) WHERE $limit_clause $order_clause"
       } else {
         append sql " " $order_clause
       }
Index: openacs-4/packages/xotcl-core/tcl/generic-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/xotcl-core/tcl/generic-procs.tcl,v
diff -u -r1.67 -r1.68
--- openacs-4/packages/xotcl-core/tcl/generic-procs.tcl	23 May 2007 07:05:11 -0000	1.67
+++ openacs-4/packages/xotcl-core/tcl/generic-procs.tcl	24 May 2007 08:56:33 -0000	1.68
@@ -72,16 +72,14 @@
     #
     # Postgres
     #
-    CrClass instproc object_types {
+    CrClass instproc object_types_query {
       {-subtypes_first:boolean false}
     } {
       my instvar object_type_key
       set order_clause [expr {$subtypes_first ? "order by tree_sortkey desc":""}]
-      return [db_list [my qn get_object_types] "
-        select object_type from acs_object_types where 
-        tree_sortkey between :object_type_key and tree_right(:object_type_key)
-        $order_clause
-      "]
+      return "select object_type from acs_object_types where 
+        tree_sortkey between '$object_type_key' and tree_right('$object_type_key')
+        $order_clause"
     }
     CrClass instproc init_type_hierarchy {} {
       my instvar object_type
@@ -91,11 +89,14 @@
       }]
     }
     CrClass instproc type_selection {-with_subtypes:boolean} {
-      my instvar object_type_key
+      my instvar object_type_key object_type
       if {$with_subtypes} {
-        return [list "" "acs_object_types.tree_sortkey between '$object_type_key' and tree_right('$object_type_key')"]
+        #return "acs_object_types.tree_sortkey between '$object_type_key' and tree_right('$object_type_key')"
+        #return "ci.content_type in ('[join [my object_types] ',']')"
+        return "ci.content_type in ([my object_types_query])"
       } else {
-        return [list "" "acs_object_types.tree_sortkey = '$object_type_key'"]
+        return "ci.content_type = '$object_type'"
+        #return "acs_object_types.tree_sortkey = '$object_type_key'"
       }
     }
     set pg_version [db_string qn.null.get_version {
@@ -111,26 +112,24 @@
     #
     # Oracle
     #
-    CrClass instproc object_types {
+    CrClass instproc object_types_query {
       {-subtypes_first:boolean false}
     } {
       my instvar object_type
-      set order_clause [expr {$subtypes_first ? "order by level desc":""}]
-      return [db_list [my qn get_object_types] "
-        select object_type from acs_object_types 
-        start with object_type = :object_type
-        connect by prior supertype = object_type $order_clause
-      "]
+      set order_clause [expr {$subtypes_first ? "order by LEVEL desc":""}]
+      return "select object_type from acs_object_types 
+        start with object_type = '$object_type' 
+        connect by prior object_type = supertype $order_clause"
     }
     CrClass instproc init_type_hierarchy {} {
       my set object_type_key {}
     }
     CrClass instproc type_selection {-with_subtypes:boolean} {
       my instvar object_type
       if {$with_subtypes} {
-        return [list "start with object_type = :object_type connect by prior supertype = object_type" ""]
+        return "acs_objects.object_type in ([my object_types_query])"
       } else {
-        return [list "" "acs_object_types.object_type = :object_type"]
+        return "acs_objects.object_type = '$object_type'"
       }
     }
   }
@@ -465,6 +464,13 @@
     ::xo::db::content_item delete -item_id $item_id
   }
 
+  CrClass instproc object_types {
+    {-subtypes_first:boolean false}
+   } {
+     return [db_list [my qn get_object_types] \
+		 [my object_types_query -subtypes_first $subtypes_first]]
+  }
+
   CrClass ad_instproc instance_select_query {
     {-select_attributes ""}
     {-orderby ""}
@@ -495,7 +501,8 @@
       if {$a eq "title"} {set a cr.title}
       lappend attributes $a
     }
-    foreach {start_clause type_selection} [my type_selection -with_subtypes $with_subtypes] break
+    set type_selection [my type_selection -with_subtypes $with_subtypes]
+    #my log "type_selection -with_subtypes $with_subtypes returns $type_selection"
     if {$count} {
       set attribute_selection "count(*)"
       set orderby ""      ;# no need to order when we count
@@ -508,8 +515,7 @@
     if {$type_selection ne ""} {lappend cond $type_selection}
     if {$where_clause   ne ""} {lappend cond $where_clause}
     if {[info exists publish_status]} {lappend cond "ci.publish_status eq '$publish_status'"}
-    lappend cond "acs_object_types.object_type = ci.content_type
-        and coalesce(ci.live_revision,ci.latest_revision) = cr.revision_id 
+    lappend cond "coalesce(ci.live_revision,ci.latest_revision) = cr.revision_id 
         and parent_id = $folder_id and acs_objects.object_id = cr.revision_id"
 
     if {$page_number ne ""} {
@@ -522,10 +528,9 @@
 
     set sql [::xo::db::sql select \
                 -vars $attribute_selection \
-                -from "acs_object_types, acs_objects, cr_items ci, cr_revisions cr $from_clause" \
+                -from "acs_objects, cr_items ci, cr_revisions cr $from_clause" \
                 -where [join $cond " and "] \
                 -orderby $orderby \
-                -start $start_clause \
                 -limit $limit -offset $offset]
     #my log "--sql=$sql"
     return $sql