::xo::db::DB-oracle ::xo::db::DB-oracle ::xo::db::DB ::xo::db::DB transaction ::xo::db::DB-oracle->::xo::db::DB ::xo::db::oracle ::xo::db::oracle ::xo::db::DB-oracle->::xo::db::oracle ::xo::db::DB-postgresql ::xo::db::DB-postgresql 0or1row 1row dml foreach generate_psql get_value insert-view-operation list list_of_lists multirow row_lock sets ::xo::db::DB-postgresql->::xo::db::DB ::xo::db::postgresql ::xo::db::postgresql ::xo::db::DB-postgresql->::xo::db::postgresql ::xo::db::DBI-postgresql ::xo::db::DBI-postgresql ::xo::db::DBI-postgresql->::xo::db::postgresql ::xo::db::DBI ::xo::db::DBI ::xo::db::DBI-postgresql->::xo::db::DBI ::xo::db::Driver ::xo::db::Driver ::xo::db::DB->::xo::db::Driver ::xotcl::Object ::xotcl::Object ::xo::db::Driver->::xotcl::Object

Class ::xo::db::DB-postgresql

::xo::db::DB-postgresql[i] create ...

Defined in

Class Relations

  • class: ::xotcl::Class[i]
  • superclass: ::xo::db::DB[i], ::xo::db::postgresql[i]
::xotcl::Class create ::xo::db::DB-postgresql \
     -superclass {::xo::db::DB ::xo::db::postgresql}

Methods (to be applied on instances)

  • 0or1row (scripted, public)

     <instance of xo::db::DB-postgresql[i]> 0or1row [ -dbn dbn ] \
        [ -bind bind ] [ -prepare prepare ] qn sql

    Run a query that returns at most one row. Set caller variables from the columns if a row is present.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Query bind values.
    -prepare (optional)
    Optional prepared-statement argument types.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.
    Returns:
    1 if a row was found, or 0 otherwise.

    Testcases:
    test_xo_db_object, test_cr_items, xowiki_test_cases
    if {$sql eq ""} {set sql [:get_sql $qn]}
    set prepOpt [expr {[info exists prepare] ? [list -prepare $prepare] : ""}]
    set answers [uplevel [list [self] exec_0or1row -dbn $dbn {*}$prepOpt -bind $bind $sql]]
    if {$answers ne ""} {
      foreach {att val} [ns_set array $answers] { uplevel [list set $att $val] }
      ns_set free $answers
      return 1
    }
    return 0
  • 1row (scripted, public)

     <instance of xo::db::DB-postgresql[i]> 1row [ -dbn dbn ] \
        [ -bind bind ] [ -prepare prepare ] qn sql

    Run a query that must return exactly one row and set its columns as variables in the caller. Raise an error if there is no row.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Bind values for the query.
    -prepare (optional)
    Optional argument types for a prepared statement.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.
    Returns:
    1 on success.

    Testcases:
    xotcl_core_tutorial_3, test_xo_db_object, test_cr_items, test_prepared_statements
    if {$sql eq ""} {set sql [:get_sql $qn]}
    set prepOpt [expr {[info exists prepare] ? [list -prepare $prepare] : ""}]
    set answers [uplevel [list [self] exec_0or1row -dbn $dbn {*}$prepOpt -bind $bind $sql]]
    if {$answers ne ""} {
      foreach {att val} [ns_set array $answers] { uplevel [list set $att $val] }
      ns_set free $answers
      return 1
    }
    error "query $sql did not return an answer"
  • dml (scripted, public)

     <instance of xo::db::DB-postgresql[i]> dml [ -dbn dbn ] \
        [ -bind bind ] [ -prepare prepare ] qn sql

    Execute a data modification statement.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Query bind values.
    -prepare (optional)
    Optional prepared-statement argument types.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.
    Returns:
    The number of affected rows.

    Testcases:
    xotcl_core_tutorial_2, xotcl_core_tutorial_4, test_cr_items, xowiki_test_cases, slot_interactions
    if {$sql eq ""} {set sql [:get_sql $qn]}
    if {$bind ne ""} {set bindOpt [list -bind $bind]} {set bindOpt ""}
    set bind $bindOpt
    db_with_handle -dbn [:map_default_dbn $dbn] db {
      if {[info exists prepare]} {set sql [:prepare -handle $db -argtypes $prepare $sql]}
      ::db_exec dml $db [uplevel [list [self] qn $qn]] $sql 2
    }
    return [db_resultrows]
  • foreach (scripted, public)

     <instance of xo::db::DB-postgresql[i]> foreach [ -dbn dbn ] \
        [ -bind bind ] [ -prepare prepare ] qn sql body

    Run a query and execute the body in the caller for each row, with the column values available as local variables.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Bind values for the query.
    -prepare (optional)
    Optional argument types for a prepared statement.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.
    body (required)
    Tcl script to run for each row.

    Testcases:
    test_prepared_statements, test_db_out_of_pools
    set prepare [expr {[info exists prepare] ? [list -prepare $prepare] : ""}]
    set rows [uplevel 1 [list ::xo::dc list_of_lists -with_headers true -dbn $dbn -bind $bind {*}$prepare $qn $sql]]
    set headers [lindex $rows 0]
    foreach row [lrange $rows 1 end] {
      foreach att $headers value $row {
        uplevel 1 [list set $att $value]
      }
    
      try {
    
        uplevel 1 $body
    
      } on error {errMsg} {
    
        error $errMsg $::errorInfo $::errorCode
    
      } on return {} {
    
        error "Cannot return from inside a ::xo::dc foreach loop"
    
      } on break {} {
    
        break
    
      } on continue {} {
    
        # Just ignore and continue looping.
    
      }
    }
  • generate_psql (scripted, public)

     <instance of xo::db::DB-postgresql[i]> generate_psql package_name \
        object_name

    Generate argument metadata and a Tcl wrapper body for calling a PostgreSQL function through the database API.

    Parameters:
    package_name (required)
    SQL package name.
    object_name (required)
    Function name within the package.
    Returns:
    The argument metadata dictionary with a body entry.

    Testcases:
    xotcl_core_tutorial_2
    set function_args [:get_function_args $package_name $object_name]
    set function_args [:fix_function_args $function_args $package_name $object_name]
    set sql_info [:sql_arg_info $function_args $package_name $object_name]
    #ns_log notice "-- select ${package_name}__${object_name} ($psql_args)"
    set sql_suffix [:psql_statement_suffix ${package_name} ${object_name}]
    set sql [subst {
      select ${package_name}__${object_name}([dict get $sql_info psql_args]) $sql_suffix
    }]
    set sql_cmd {ns_set value [ns_pg_bind 0or1row $db $sql] 0}
    dict set sql_info body [subst {
      #function_args: $function_args
      foreach var \[list [dict get $sql_info arg_order]\]  {
        set varname \[string tolower \$var\]
        if {\[info exists \$varname\]} {
          set \$var \[set \$varname\]
          set _\$var :\$var
        } else {
          set _\$var null
        }
      }
      set sql "$sql"
      db_with_handle -dbn \$dbn db {
        #ns_log notice "--sql=\$sql"
        return \[ $sql_cmd \]
      }
    }]
    return $sql_info
  • get_value (scripted, public)

     <instance of xo::db::DB-postgresql[i]> get_value [ -dbn dbn ] \
        [ -bind bind ] [ -prepare prepare ] qn sql [ default ]

    Return the first column of a single-row query, or the default if there is no row.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Bind values for the query.
    -prepare (optional)
    Optional argument types for a prepared statement.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.
    default (optional)
    Value to return when the query produces no rows.

    Testcases:
    xotcl_core_tutorial_1, xotcl_core_tutorial_2, xotcl_core_tutorial_4, test_xo_db_object, test_cr_items, test_prepared_statements, xowiki_test_cases, path_resolve
    if {$sql eq ""} {set sql [:get_sql $qn]}
    set prepOpt [expr {[info exists prepare] ? [list -prepare $prepare] : ""}]
    set answers [uplevel [list [self] exec_0or1row -dbn $dbn {*}$prepOpt -bind $bind $sql]]
    if {$answers ne ""} {
      set result [ns_set value $answers 0]
      ns_set free $answers
      return $result
    }
    return $default
  • insert-view-operation (scripted, public)

     <instance of xo::db::DB-postgresql[i]> insert-view-operation

    Return the database operation used to insert through a PostgreSQL view.

    Returns:
    0or1row.

    Testcases:
    xotcl_core_tutorial_4, test_cr_items
    return 0or1row
  • list (scripted, public)

     <instance of xo::db::DB-postgresql[i]> list [ -dbn dbn ] \
        [ -bind bind ] [ -prepare prepare ] qn sql

    Return the first column of each query result row as a list.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Query bind values.
    -prepare (optional)
    Optional prepared-statement argument types.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.

    Testcases:
    xotcl_core_tutorial_2, xowiki_test_cases
    if {$sql eq ""} {set sql [:get_sql $qn]}
    if {$bind ne ""} {set bindOpt [list -bind $bind]} {set bindOpt ""}
    db_with_handle -dbn [:map_default_dbn $dbn] db {
      if {[info exists prepare]} {set sql [:prepare -handle $db -argtypes $prepare $sql]}
      set result {}
      set answers [uplevel [list ns_pg_bind select $db {*}$bindOpt $sql]]
      while { [::db_getrow $db $answers] } {
        lappend result [ns_set value $answers 0]
      }
      ns_set free $answers
    }
    return $result
  • list_of_lists (scripted, public)

     <instance of xo::db::DB-postgresql[i]> list_of_lists [ -dbn dbn ] \
        [ -bind bind ] [ -with_headers with_headers ] [ -prepare prepare ] \
        qn sql

    Return each query result row as a list of column values.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Query bind values.
    -with_headers (optional, defaults to "false")
    Include a first list containing the column names.
    -prepare (optional)
    Optional prepared-statement argument types.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.

    Testcases:
    xotcl_core_tutorial_2, xotcl_core_tutorial_3
    if {$sql eq ""} {set sql [:get_sql $qn]}
    if {$bind ne ""} {set bindOpt [list -bind $bind]} {set bindOpt ""}
    db_with_handle -dbn [:map_default_dbn $dbn] db {
      if {[info exists prepare]} {set sql [:prepare -handle $db -argtypes $prepare $sql]}
      set result {}
      set answers [uplevel [list ns_pg_bind select $db {*}$bindOpt $sql]]
      if {$with_headers} {
        if {[acs::icanuse "ns_set keys"]} {
          set headers [ns_set keys $answers]
        } else {
          set headers [dict keys [ns_set array $answers]]
        }
        set result [list $headers]
      }
      while { [db_getrow $db $answers] } {
        set row [list]
        foreach {att value} [ns_set array $answers] {lappend row $value}
        lappend result $row
      }
      ns_set free $answers
    }
    return $result
  • multirow (scripted, public)

     <instance of xo::db::DB-postgresql[i]> multirow [ -dbn dbn ] \
        [ -bind bind ] [ -local local ] [ -upvar_level upvar_level ] \
        [ -extend extend ] [ -prepare prepare ] var_name qn sql [ body ]

    Append query results to a template multirow, creating it if needed.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Bind values for the query.
    -local (optional, defaults to "false")
    Keep the multirow local to the selected caller frame.
    -upvar_level (optional, defaults to "1")
    Caller level at which to store the multirow.
    -extend (optional)
    Additional column names to populate in the row body.
    -prepare (optional)
    Optional argument types for a prepared statement.
    Parameters:
    var_name (required)
    Template multirow name.
    qn (required)
    Query name.
    sql (required)
    SQL statement.
    body (optional)
    Optional Tcl script to run for each row.

    Testcases:
    test_prepared_statements, test_multirow, test_db_out_of_pools
    set prepare [expr {[info exists prepare] ? [list -prepare $prepare] : ""}]
    set rows [uplevel 1 [list ::xo::dc list_of_lists -with_headers true -dbn $dbn -bind $bind {*}$prepare $qn $sql]]
    set headers [lindex $rows 0]
    
    if { $local } {
      set level_up [expr {$upvar_level + 1}]
    } else {
      set level_up \#[::template::adp_level]
    }
    
    set cols [concat $headers $extend]
    if {[::template::multirow -local -ulevel $level_up exists $var_name]} {
      #
      # We enforce here, that appending to an existing multirow
      # can only happen when we are extracting the same columns.
      #
      set existing_cols [::template::multirow -local -ulevel $level_up columns $var_name]
      if {$cols ne $existing_cols} {
        error "Cannot append to a multirow with different columns"
      }
    } else {
      ::template::multirow -local -ulevel $level_up create $var_name {*}$cols
    }
    
    foreach values [lrange $rows 1 end] {
      if {[string length $body] > 0} {
        #
        # We have a code to execute. Bring all of the multirow
        # variables in scope.
        #
    
        #
        # Vars from the query
        #
        foreach att $headers value $values {
          uplevel 1 [list set $att $value]
        }
    
        #
        # Extended variables, initialized to empty.
        #
        foreach att $extend {
          uplevel 1 [list set $att ""]
        }
    
        #
        # Run the code and trap any exception.
        #
        try {
    
          uplevel 1 $body
    
        } on error {errMsg} {
    
          error $errMsg $::errorInfo $::errorCode
    
        } on return {} {
    
          error "Cannot return from inside a ::xo::dc multirow loop"
    
        } on break {} {
    
          break
    
        } on continue {} {
    
          continue
    
        }
    
        #
        # Collect the values after the code has been executed.
        #
        set values [lmap att $cols {
          if {[uplevel 1 [list info exists $att]]} {
            uplevel 1 [list set $att]
          }
        }]
      } else {
        #
        # No code to execute. We can just bulk append the values
        # from the row.
        #
      }
      ::template::multirow -local -ulevel $level_up append $var_name {*}$values
    }
  • row_lock (scripted, public)

     <instance of xo::db::DB-postgresql[i]> row_lock [ -dbn dbn ] \
        [ -bind bind ] [ -for for ] [ -prepare prepare ] qn sql

    Append a locking clause to a query and return the first column of each row.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Query bind values.
    -for (optional, defaults to "UPDATE")
    Locking clause after FOR, defaulting to UPDATE.
    -prepare (optional)
    Optional prepared-statement argument types.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.

    Testcases:
    xotcl_core_tutorial_4, test_cr_items
    set prepareOpt [expr {[info exists prepare] ? [list -prepare $prepare] : ""}]
    :uplevel [list ::xo::dc list -dbn $dbn -bind $bind {*}$prepareOpt $qn "$sql FOR $for"]
  • sets (scripted, public)

     <instance of xo::db::DB-postgresql[i]> sets [ -dbn dbn ] \
        [ -bind bind ] [ -prepare prepare ] qn sql

    Return a list of ns_set handles, one per query result row.

    Switches:
    -dbn (optional)
    Database name, or empty for the default.
    -bind (optional)
    Query bind values.
    -prepare (optional)
    Optional prepared-statement argument types.
    Parameters:
    qn (required)
    Query name.
    sql (required)
    SQL statement.

    Testcases:
    xotcl_core_tutorial_2
    if {$sql eq ""} {set sql [:get_sql $qn]}
    if {$bind ne ""} {set bindOpt [list -bind $bind]} {set bindOpt ""}
    
    db_with_handle -dbn $dbn db {
      if {[info exists prepare]} {set sql [:prepare -handle $db -argtypes $prepare $sql]}
      set result [list]
    
      set answers [uplevel [list ns_pg_bind select $db {*}$bindOpt $sql]]
      while { [::db_getrow $db $answers] } {
        lappend result [ns_set copy $answers]
      }
      ns_set free $answers
    }
    return $result