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.18 -r1.19 --- openacs-4/packages/xotcl-core/tcl/05-db-procs.tcl 14 May 2007 13:23:31 -0000 1.18 +++ openacs-4/packages/xotcl-core/tcl/05-db-procs.tcl 15 May 2007 10:47:41 -0000 1.19 @@ -193,22 +193,58 @@ ::xotcl::Object create sql if {[db_driverkey ""] eq "postgresql"} { - sql proc limit_select {-limit:required -sql:required {-order ""}} { - set order_clause [expr {$order ne "" ? "ORDER BY $order" : ""}] - return "SELECT $sql $order_clause LIMIT $limit" + sql proc select { + -vars:required + -from:required + -where:required + {-groupby ""} + {-limit ""} + {-offset ""} + {-start ""} + {-orderby ""} + } { + set offset_clause [expr {$offset ne "" ? "OFFSET $offset" : ""}] + set limit_clause [expr {$limit ne "" ? "LIMIT $limit" : ""}] + set order_clause [expr {$orderby ne "" ? "ORDER BY $orderby" : ""}] + set group_clause [expr {$groupby ne "" ? "GROUP BY $groupby" : ""}] + return "SELECT $vars FROM $from WHERE $where $group_clause $order_clause $limit_clause" } } else { ;# Oracle - sql proc limit_select {-limit:required -sql:required {-order ""}} { - if {$order ne ""} { - set sql "SELECT * FROM (SELECT $sql) WHERE ROWNUM <= $limit ORDER BY $order" + sql proc select { + -vars:required + -from:required + -where:required + {-groupby ""} + {-limit ""} + {-offset ""} + {-start ""} + {-orderby ""} + } { + set order_clause [expr {$orderby ne "" ? "ORDER BY $orderby" : ""}] + set group_clause [expr {$groupby ne "" ? "GROUP BY $groupby" : ""}] + set sql "SELECT $vars FROM $from $start WHERE $where $group_clause" + if {$limit ne "" || $offset ne ""} { + if {$offset eq ""} { + set limit_clause "ROWNUM <= $limit" + } else {$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" } else { - set sql "SELECT * FROM (SELECT $sql) WHERE ROWNUM <= $limit" + append sql " " $order_clause } my log "--returned sql = $sql" return $sql } } - + sql proc since_interval_condition {var interval} { + set since [clock format [clock scan "-$interval"] -format "%Y-%m-%d %T"] + return "$var > TO_TIMESTAMP('$since','YYYY-MM-DD HH24:MI:SS')" + } ::xotcl::Object create require require set postgresql_table_exists {select 1 from pg_tables where tablename = '$name'} 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.61 -r1.62 --- openacs-4/packages/xotcl-core/tcl/generic-procs.tcl 14 May 2007 13:23:31 -0000 1.61 +++ openacs-4/packages/xotcl-core/tcl/generic-procs.tcl 15 May 2007 10:47:41 -0000 1.62 @@ -92,10 +92,11 @@ } CrClass instproc type_selection {-with_subtypes:boolean} { my instvar object_type_key - return [expr {$with_subtypes ? - "where acs_object_types.tree_sortkey between \ - '$object_type_key' and tree_right('$object_type_key') and" : - "where acs_object_types.tree_sortkey = '$object_type_key' and"}] + if {$with_subtypes} { + return [list "" "acs_object_types.tree_sortkey between '$object_type_key' and tree_right('$object_type_key')"] + } else { + return [list "" "acs_object_types.tree_sortkey = '$object_type_key'"] + } } set pg_version [db_string qn.null.get_version { select substring(version() from 'PostgreSQL #"[0-9]+.[0-9+]#".%' for '#') }] @@ -118,18 +119,19 @@ 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 + connect by prior supertype = object_type $order_clause "] } CrClass instproc init_type_hierarchy {} { my set object_type_key {} } CrClass instproc type_selection {-with_subtypes:boolean} { my instvar object_type - return [expr {$with_subtypes ? - "start with object_type = :object_type - connect by supertype = prior object_type where" : - "where acs_object_types.object_type = :object_type and"}] + if {$with_subtypes} { + return [list "start with object_type = :object_type connect by prior supertype = object_type" ""] + } else { + return [list "" "acs_object_types.object_type = :object_type"] + } } } @@ -485,7 +487,7 @@ CrClass ad_instproc instance_select_query { {-select_attributes ""} - {-order_clause ""} + {-orderby ""} {-where_clause ""} {-from_clause ""} {-with_subtypes:boolean true} @@ -498,7 +500,7 @@ returns the SQL-query to select the CrItems of the specified object_type @select_attributes attributes for the sql query to be retrieved, in addion to ci.item_id acs_objects.object_type, which are always returned - @param order_clause clause for ordering the solution set + @param orderby for ordering the solution set @param where_clause clause for restricting the answer set @param with_subtypes return subtypes as well @param count return the query for counting the solutions @@ -513,36 +515,43 @@ if {$a eq "title"} {set a cr.title} lappend attributes $a } - set type_selection [my type_selection -with_subtypes $with_subtypes] + foreach {start_clause type_selection} [my type_selection -with_subtypes $with_subtypes] break if {$count} { set attribute_selection "count(*)" - set order_clause "" ;# no need to order when we count + set orderby "" ;# no need to order when we count set page_number "" ;# no pagination when count is used } else { set attribute_selection [join $attributes ,] } + + set cond [list] + 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 + and parent_id = $folder_id and acs_objects.object_id = cr.revision_id" - if {$where_clause ne ""} { - set where_clause "and $where_clause" - } if {$page_number ne ""} { - set pagination "offset [expr {$page_size*($page_number-1)}] limit $page_size" + set limit $page_size + set offset [expr {$page_size*($page_number-1)}] } else { - set pagination "" + set limit "" + set offset "" } - set publish_clause \ - [expr {[info exists publish_status] ? " and ci.publish_status eq '$publish_status'" : ""}] - return "select $attribute_selection - from acs_object_types, acs_objects, cr_items ci, cr_revisions cr $from_clause - $type_selection acs_object_types.object_type = ci.content_type - and coalesce(ci.live_revision,ci.latest_revision) = cr.revision_id - and parent_id = $folder_id and acs_objects.object_id = cr.revision_id \ - $where_clause $order_clause $publish_clause $pagination" + + return [::xo::db::sql select \ + -vars $attribute_selection \ + -from "acs_object_types, acs_objects, cr_items ci, cr_revisions cr $from_clause" \ + -where [join $cond " and "] \ + -orderby $orderby \ + -start $start_clause \ + -limit $limit -offset $offset] } CrClass ad_instproc instantiate_all { {-select_attributes ""} - {-order_clause ""} + {-orderby ""} {-where_clause ""} {-with_subtypes:boolean true} {-folder_id} @@ -568,7 +577,7 @@ -select_attributes $select_attributes \ -with_subtypes $with_subtypes \ -where_clause $where_clause \ - -order_clause $order_clause \ + -orderby $orderby \ -page_size $page_size -page_number $page_number] { set __o [$object_type create ${__result}::$item_id] $__result add $__o @@ -1274,14 +1283,14 @@ append new_data { category::map_object -remove_old -object_id $item_id $category_ids #ns_log notice "-- new data category::map_object -remove_old -object_id $item_id $category_ids" - db_dml [my qn insert_asc_named_object] \ - "insert into acs_named_objects (object_id,object_name,package_id) \ - values (:item_id, :name, :package_id)" + #db_dml [my qn insert_asc_named_object] \ + # "insert into acs_named_objects (object_id,object_name,package_id) \ + # values (:item_id, :name, :package_id)" } append edit_data { - db_dml [my qn update_asc_named_object] \ - "update acs_named_objects set object_name = :name, \ - package_id = :package_id where object_id = :item_id" + #db_dml [my qn update_asc_named_object] \ + # "update acs_named_objects set object_name = :name, \ + # package_id = :package_id where object_id = :item_id" #ns_log notice "-- edit data category::map_object -remove_old -object_id $item_id $category_ids" category::map_object -remove_old -object_id $item_id $category_ids } @@ -1407,7 +1416,7 @@ List ad_instproc generate { - -order_by + {-orderby ""} -template } { the method generate is used to actually generate the list template @@ -1418,7 +1427,6 @@ } { my instvar object_type with_subtypes - set order_clause [expr {[info exists order_by] ? "order by $order_by":""}] if {![info exists template]} { set template [my name] } @@ -1445,7 +1453,7 @@ -folder_id [my folder_id] \ -select_attributes $select_attributes \ -with_subtypes $with_subtypes \ - -order_clause $order_clause] { + -orderby $orderby] { set view_url [export_vars -base [my view_link] {item_id}] set edit_url [export_vars -base [my edit_link] {item_id}] set delete_url [export_vars -base [my delete_link] {item_id}]