::xotcl::Object ::xo::db::sql

::xotcl::Object create ::xo::db::sql

Methods

• proc datatype_constraint

  ::xo::db::sql proc datatype_constraint {type table att} {
        set constraint ""
        switch -- $type {
          boolean {
            set cname [::xo::db::mk_sql_constraint_name $table $att _ck]
            set constraint "constraint $cname check ($att in ('t','f'))"}
        }
        return $constraint
      }

• proc date_trunc

  ::xo::db::sql proc date_trunc {field date} {
        return "to_char(trunc($date,'$field'), 'YYYY-MM-DD HH24:MI:SS')"
      }

• proc date_trunc_expression

  ::xo::db::sql proc date_trunc_expression {field date date_string} {
        return "trunc($date,'$field') = trunc(to_date('$date_string','YYYY-MM-DD'),'$field')"
      }

• proc map_datatype

  ::xo::db::sql proc map_datatype type {
        switch -- $type {
          string    { set type varchar2(1000) }
          text      { set type varchar2(4000) }
          long_text { set type clob }
          boolean   { set type char(1) }
          ltree     { set type varchar2(1000) }
        }
        return $type
      }

• proc map_function_name

  ::xo::db::sql proc map_function_name sql {
        return [string map [list "__" .] $sql]
      }

• proc select

  ::xo::db::sql proc select {-vars:required -from:required {-where {}} {-groupby {}} {-limit {}} {-offset {}} {-start {}} {-orderby {}} {-map_function_names false}} {
        # "-start" not used so far
        set where_clause [expr {$where   ne "" ? "WHERE $where" : ""}]
        set order_clause [expr {$orderby ne "" ? "ORDER BY $orderby" : ""}]
        set group_clause [expr {$groupby ne "" ? "GROUP BY $groupby" : ""}]
        if {$map_function_names} {set vars [my map_function_name $vars]}
        set sql "SELECT $vars FROM $from $where_clause $group_clause"
        if {$limit ne "" || $offset ne ""} {
          if {$offset eq ""} {
            set limit_clause "ROWNUM <= $limit"
          } 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 ($sql $order_clause) WHERE $limit_clause"
        } else {
          append sql " " $order_clause
        }
        my log "--returned sql = $sql"
        return $sql
      }

• proc since_interval_condition

  ::xo::db::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')"
    }