Index: openacs-4/packages/xowiki/tcl/xowiki-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/xowiki/tcl/xowiki-procs.tcl,v
diff -u -r1.472.2.38 -r1.472.2.39
--- openacs-4/packages/xowiki/tcl/xowiki-procs.tcl	3 Mar 2015 16:08:22 -0000	1.472.2.38
+++ openacs-4/packages/xowiki/tcl/xowiki-procs.tcl	23 Mar 2015 11:54:42 -0000	1.472.2.39
@@ -142,9 +142,10 @@
             -spec "hidden" 
         ::xo::db::CrAttribute create state -default ""
       }
-
-  # create various extra tables, indices and views
+  
   #
+  # Create various extra tables, indices and views
+  #
   ::xo::db::require index -table xowiki_form_page -col assignee
   ::xo::db::require index -table xowiki_page_instance -col page_template
 
@@ -190,6 +191,10 @@
   ::xo::db::require index -table xowiki_page -col page_order \
       -using [expr {[::xo::dc has_ltree] ? "gist" : ""}]
 
+  #
+  # view: xowiki_page_live_revision
+  #
+  
   set sortkeys [expr {[db_driverkey ""] eq "oracle" ? "" : ", ci.tree_sortkey, ci.max_child_sortkey"}]
   ::xo::db::require view xowiki_page_live_revision \
       "select p.*, cr.*,ci.parent_id, ci.name, ci.locale, ci.live_revision, \
@@ -200,14 +205,207 @@
             and p.page_id = cr.revision_id  \
             and ci.publish_status <> 'production'"
 
+  #
+  # xowiki_form_instance_item_index:
+  #
+  #   A materialized table of xowiki formpage instances, containing
+  #   just the item information, but combined with other attributes
+  #   frequently used for indexing (like page_id, paren_id, ... hkey).
+  #
+  #   Rationale: The quality of indices on cr_revisions tend to
+  #   decrease when there are many revisions stored in the database,
+  #   since the number of duplicates increases due to non-live
+  #   revisions. This table can be used for indexing just the live
+  #   revision on the item level. Example query combining package_id
+  #   and page_template:
+  #
+  #      select count(*) from xowiki_form_instance_item_index
+  #      where package_id = 18255683
+  #         and page_template = 20260757
+  #         and publish_status='ready'; 
+  #
+  #   In order to get rid of this helper table (may be to regenerate it
+  #   on the next load) use
+  #
+  #       drop table xowiki_form_instance_item_index cascade;
+  #
+  #
+  #ns_logctl severity Debug(sql) on
 
+  #
+  # $populate is a dml statement to populate the materialized index
+  # xowiki_form_instance_item_index, when it does not exist.
+  #
+  set popuplate {
+    insert into xowiki_form_instance_item_index (
+           item_id, name, package_id, parent_id, publish_status,
+           page_template, assignee, state )
+    select ci.item_id, ci.name, o.package_id, ci.parent_id, ci.publish_status,
+           xpi.page_template, xfp.assignee, xfp.state
+    from cr_items ci
+    join xowiki_page_instance xpi on (ci.live_revision = xpi.page_instance_id)
+    join xowiki_form_page xfp on (ci.live_revision = xfp.xowiki_form_page_id)
+    join acs_objects o on (o.object_id = ci.item_id)
+  }
+  
+  if {[::xo::dc has_hstore]} {
+    ::xo::db::require table xowiki_form_instance_item_index {
+      item_id             {integer references cr_items(item_id) on delete cascade}
+      name                {character varying(400)}
+      package_id          {integer}
+      parent_id           {integer references cr_items(item_id) on delete cascade}
+      publish_status      {text}
+      page_template       {integer references cr_items(item_id) on delete cascade}
+      hkey                {hstore}
+      assignee            {integer references parties(party_id) on delete cascade}
+      state               {text}
+    } $popuplate
+    ::xo::db::require index -table xowiki_form_instance_item_index -col hkey -using gist
+    set hkey_in_view "xi.hkey,"
+  } else {
+    ::xo::db::require table xowiki_form_instance_item_index {
+      item_id             {integer references cr_items(item_id) on delete cascade}
+      name                {character varying(400)}
+      package_id          {integer}
+      parent_id           {integer references cr_items(item_id) on delete cascade}
+      publish_status      {text}
+      page_template       {integer references cr_items(item_id) on delete cascade}
+      assignee            {integer references parties(party_id) on delete cascade}
+      state               {text}
+    } $popuplate
+    set hkey_in_view ""
+  }
+ 
+  ::xo::db::require index -table xowiki_form_instance_item_index -col item_id -unique true
+  ::xo::db::require index -table xowiki_form_instance_item_index -col parent_id,name -unique true
+  ::xo::db::require index -table xowiki_form_instance_item_index -col page_template
+  ::xo::db::require index -table xowiki_form_instance_item_index -col page_template,package_id
+  ::xo::db::require index -table xowiki_form_instance_item_index -col parent_id
+  ::xo::db::require index -table xowiki_form_instance_item_index -col parent_id,page_template
+
+  #
+  # Define helper views in connection with the form_instance_item_index:
+  #
+  # - xowiki_form_instance_item_view
+  # - xowiki_form_instance_children
+  # - xowiki_form_instance_attributes
+  
+  #
+  # - xowiki_form_instance_item_view:
+  #
+  #   A view similar to xowiki_form_pagei, but containing already
+  #   often extra-joined attributes like parent_id. This view returns
+  #   only the values of the live revisions, and uses the
+  #   form_instance_item_index for quick lookup. Example query to
+  #   obtain all attributes necessary for object creation based on
+  #   package_id and page_template.
+  #
+  #      select * from xowiki_form_instance_item_view
+  #      where package_id = 18255683
+  #         and page_template = 20260757
+  #         and publish_status='ready';
+  #   
+  ::xo::db::require view xowiki_form_instance_item_view [subst {
+      SELECT
+         xi.package_id, xi.parent_id, xi.name,
+         $hkey_in_view xi.publish_status, xi.assignee, xi.state, xi.page_template, xi.item_id,
+         o.object_id, o.object_type, o.title AS object_title, o.context_id,
+         o.security_inherit_p, o.creation_user, o.creation_date, o.creation_ip,
+         o.last_modified, o.modifying_user, o.modifying_ip, o.tree_sortkey, o.max_child_sortkey,
+         cr.revision_id, cr.title, content_revision__get_content(cr.revision_id) AS data,
+         cr_text.text_data AS text, cr.description, cr.publish_date, cr.mime_type, cr.nls_language,
+         xowiki_form_page.xowiki_form_page_id,
+         xowiki_page_instance.page_instance_id,
+         xowiki_page_instance.instance_attributes,
+         xowiki_page.page_id,
+         xowiki_page.page_order,
+         xowiki_page.creator
+      FROM cr_text,
+         xowiki_form_instance_item_index xi
+         left join cr_items ci on (ci.item_id = xi.item_id)
+         left join cr_revisions cr on (cr.revision_id = ci.live_revision)
+         left join acs_objects o on (o.object_id = ci.live_revision)
+         left join xowiki_page on (o.object_id = xowiki_page.page_id)
+         left join xowiki_page_instance on (o.object_id = xowiki_page_instance.page_instance_id)
+         left join xowiki_form_page on (o.object_id = xowiki_form_page.xowiki_form_page_id)
+  }]
+
+  # xowiki_form_instance_children:
+  #
+  #   Return the root_item_id and all attributes of the
+  #   form_instance_item_index of all child items under the tree based
+  #   on parent_ids. Use a query like the following to count the
+  #   children of an item having a certain page_template (e.g.
+  #   find all the folders/links/... having the the specified item
+  #   as parent):
+  #
+  #      select count(*) from xowiki_form_instance_children
+  #      where root_item_id = 18255779
+  #         and page_template = 20260757
+  #         and publish_status='ready'; 
+  #
+  #   Note: this query needs an oracle counter-part
+  
+  ::xo::db::require view xowiki_form_instance_children {
+      With RECURSIVE child_items AS (
+        select item_id as root_item_id, * from xowiki_form_instance_item_index
+      UNION ALL
+        select child_items.root_item_id, xi.* from xowiki_form_instance_item_index xi, child_items
+	where xi.parent_id = child_items.item_id 
+      )
+      select * from child_items
+  }
+
+  # xowiki_form_instance_attributes
+  #
+  #   Return for a given item_id the full set of attributes like the
+  #   one returned from xowiki_form_instance_item_view. The idea is to
+  #   make it convenient to obtain from a query all attributes
+  #   necessary for creating instances. The same view can be used to
+  #   complete either values from the xowiki_form_instance_item_index
+  #
+  #      select * from xowiki_form_instance_item_index xi
+  #      left join xowiki_form_instance_attributes xa on xi.item_id = xa.item_id;
+  #
+  #   or from xowiki_form_instance_children
+  #
+  #      select * from xowiki_form_instance_children ch
+  #      left join xowiki_form_instance_attributes xa on ch.item_id = xa.item_id;
+  #
+  #   
+  ::xo::db::require view xowiki_form_instance_attributes {
+      SELECT
+    ci.item_id,
+         o.package_id,
+         o.object_id, o.object_type, o.title AS object_title, o.context_id,
+         o.security_inherit_p, o.creation_user, o.creation_date, o.creation_ip,
+         o.last_modified, o.modifying_user, o.modifying_ip, o.tree_sortkey, o.max_child_sortkey,
+         cr.revision_id, cr.title, content_revision__get_content(cr.revision_id) AS data,
+         cr_text.text_data AS text, cr.description, cr.publish_date, cr.mime_type, cr.nls_language,
+         xowiki_form_page.xowiki_form_page_id,
+         xowiki_page_instance.page_instance_id,
+         xowiki_page_instance.instance_attributes,
+         xowiki_page.page_id,
+         xowiki_page.page_order,
+         xowiki_page.creator
+      FROM cr_text, cr_items ci 
+         left join cr_revisions cr on (cr.revision_id = ci.live_revision)
+         left join acs_objects o on (o.object_id = ci.live_revision)
+         left join xowiki_page on (o.object_id = xowiki_page.page_id)
+         left join xowiki_page_instance on (o.object_id = xowiki_page_instance.page_instance_id)
+         left join xowiki_form_page on (o.object_id = xowiki_form_page.xowiki_form_page_id)   
+  }
+  
+  #ns_logctl severity Debug(sql) off
+  
+  
   #############################
   #
   # A simple autoname handler
   #
   # The autoname handler has the purpose to generate new names based
   # on a stem and a parent_id. Typically this is used for the
-  # autonaming of FormPages. The goal is to generate "nice" names,
+  # auto-naming of FormPages. The goal is to generate "nice" names,
   # i.e. with rather small numbers.
   #
   # Instead of using the table below, another option would be to use
@@ -1065,7 +1263,49 @@
     return [my exists_property form_constraints]
   }
 
+  FormPage instproc hstore_attributes {} {
+    # Per default, we save all instance attributes in hstore, but a
+    # subclass/object might have different requirements.
+    return [my instance_attributes]
+  }
+  
   #
+  # Update helper for xowiki_form_instance_item_index (called from
+  # cr_procs, whenever a live-revision becomes updated).
+  # 
+  FormPage ad_instproc update_item_index {} {
+  } {
+    my instvar item_id package_id parent_id publish_status \
+        page_template instance_attributes assignee state
+
+    set rows [xo::dc dml update_xowiki_form_instance_item_index {
+      update xowiki_form_instance_item_index
+      set item_id = :item_id, name = :name, package_id = :package_id,
+          parent_id = :parent_id, publish_status = :publish_status,
+          page_template = :page_template, assignee = :assignee,
+          state = :state
+      where item_id = :item_id
+    }]
+    if {$rows ne "" && $rows < 1} {
+      ::xo::dc dml insert_xowiki_form_instance_item_index {
+        insert into xowiki_form_instance_item_index (
+            item_id, name, package_id, parent_id, publish_status,
+            page_template, assignee, state
+            ) values (
+            :item_id, :name, :package_id, :parent_id, :publish_status,
+            :page_template, :assignee, :state
+            )
+      }
+    }
+    if {[$package_id get_parameter use_hstore 0]} {
+      set hkey [::xowiki::hstore::dict_as_hkey [my hstore_attributes]]
+      xo::dc dml update_hstore "update xowiki_form_instance_item_index \
+                set hkey = '$hkey' \
+                where item_id = :item_id"
+    }
+  }
+
+  #
   # helper for nls and lang
   #
 
@@ -3079,13 +3319,6 @@
     return 0
   }
 
-  FormPage proc h_double_quote {value} {
-    if {[regexp {[ ,\"\\=>]} $value]} {
-      set value \"[string map [list \" \\\\\" \\ \\\\ ' \\\\'] $value]\"
-    }
-    return $value
-  }
-
   FormPage proc filter_expression {
                                    {-sql true}
                                    input_expr
@@ -3125,7 +3358,7 @@
             lappend tcl_clause "\[my property $lhs\] $tcl_op($op) {$rhs}"
           }
         } else {
-          set hleft [my h_double_quote $lhs]
+          set hleft [::xowiki::hstore::double_quote $lhs]
           lappend vars $lhs ""
           if {$op eq "contains"} {
             #make approximate query
@@ -3144,7 +3377,7 @@
               # TODO: think about a solution for other operators with
               # hstore maybe: extracting it by a query via hstore and
               # compare in plain SQL
-              lappend h_clause "$hleft=>[my h_double_quote $rhs]"
+              lappend h_clause "$hleft=>[::xowiki::hstore::double_quote $rhs]"
             }
           }
         }
@@ -3193,33 +3426,35 @@
     # "-always_queried_attributes *" means to obtain enough attributes
     # to allow a save operatons etc. on the instances.
     #
-    
-    set sql_atts [list ci.parent_id bt.revision_id bt.instance_attributes \
-                      bt.creation_date bt.creation_user bt.last_modified \
-                      "bt.object_package_id as package_id" bt.title \
-                      bt.page_template bt.state bt.assignee 
-                 ]
+
+    set sql_atts {
+      item_id name publish_status object_type
+      parent_id revision_id instance_attributes
+      creation_date creation_user last_modified 
+      package_id title page_template state assignee
+    }
+
     if {$always_queried_attributes eq "*"} {
       lappend sql_atts \
-          bt.object_type bt.object_id \
-          bt.description bt.publish_date bt.mime_type nls_language "bt.data as text" \
-          bt.creator bt.page_order bt.page_id \
-          bt.page_instance_id bt.xowiki_form_page_id
+          object_type object_id \
+          description publish_date mime_type nls_language "data as text" \
+          creator page_order page_id \
+          page_instance_id xowiki_form_page_id
     } else {
       foreach att $always_queried_attributes {
         set name [string range $att 1 end]
-        lappend sql_atts bt.$name
+        lappend sql_atts $name
       }
-    }
+    }    
 
     #
     # Compute the list of field_names from the already covered sql
     # attributes
     #
     set covered_attributes [list _name _publish_status _item_id _object_type]
     foreach att $sql_atts {
-      regexp {[.]([^ ]+)} $att _ name
-      lappend covered_attributes _$name
+      #regexp {[.]([^ ]+)} $att _ name
+      lappend covered_attributes _$att
     }
 
     #
@@ -3234,16 +3469,16 @@
       if {$field_name eq "_text"} {
         lappend sql_atts "bt.data as text"
       } else {
-        lappend sql_atts bt.[$f set __base_field]
+        lappend sql_atts [$f set __base_field]
       }
     }
     #my msg sql_atts=$sql_atts
 
     #
     # Build parts of WHERE clause 
     # 
-    set publish_status_clause [::xowiki::Includelet publish_status_clause -base_table ci $publish_status]
-
+    set publish_status_clause [::xowiki::Includelet publish_status_clause \
+                                   -base_table "" $publish_status]
     #
     # Build filter clause (uses hstore if configured)
     #
@@ -3253,7 +3488,7 @@
                           [$package_id get_parameter use_hstore 0] 
                         }]
     if {$use_hstore && $wc(h) ne ""} {
-      set filter_clause " and '$wc(h)' <@ bt.hkey"
+      set filter_clause " and '$wc(h)' <@ hkey"
     }
     #my msg "exists sql=[info exists wc(sql)]"
     if {$wc(sql) ne "" && $wc(h) ne ""} {
@@ -3267,37 +3502,43 @@
     # Build package clause
     #
     if {$from_package_ids eq ""} {
-      set package_clause "and object_package_id = :package_id"
+      set package_clause "and package_id = :package_id"
     } elseif {$from_package_ids eq "*"} {
       set package_clause ""
     } elseif {[llength $from_package_ids] == 1} {
-      set package_clause "and object_package_id = :from_package_ids"
+      set package_clause "and package_id = :from_package_ids"
     } else {
-      set package_clause "and object_package_id in ([join $from_package_ids ,])"
+      set package_clause "and package_id in ([join $from_package_ids ,])"
     }
 
     if {$parent_id eq "*"} {
       # instance_select_query expects "" for all parents, but for the semantics
       # of this method, "*" looks more appropriate
       set parent_id ""
     }
+
+    if {[llength $base_item_ids] == 0} {
+      error "base_item_ids must not be empty"
+    }
     #
     # transform all into an SQL query
     #
-    set sql  [::xowiki::FormPage instance_select_query \
-                  -select_attributes $sql_atts \
-                  -from_clause "" \
-                  -where_clause " bt.page_template in ([join $base_item_ids ,]) \
+    if {$page_number ne ""} {
+      set limit $page_size
+      set offset [expr {$page_size*($page_number-1)}]
+    } else {
+      set limit ""
+      set offset ""
+    }
+    set sql [::xo::dc select \
+                 -vars [join $sql_atts ", "] \
+                 -from xowiki_form_instance_item_view \
+                 -where " page_template in ([join $base_item_ids ,]) \
             $publish_status_clause $filter_clause $package_clause \
             $extra_where_clause" \
-                  -orderby $orderby \
-                  -with_subtypes false \
-                  -parent_id $parent_id \
-                  -page_size $page_size \
-                  -page_number $page_number \
-                  -base_table xowiki_form_pagei \
-                 ]
-    #my ds $sql
+                 -orderby $orderby \
+                 -limit $limit -offset $offset]
+    #ns_log notice "NEW SQL:\n\n$sql\n"
 
     #
     # When we query all attributes, we return objects named after the