Forum OpenACS Development: Preview: nsdb extensions for NaviServer 5.2

Dear all,

A preview of shared bind-variable support for generic nsdb is now available in NaviServer’s development version, with an implementation in nsdbsqlite and in nsdbpg. These changes should arrive in NaviServer 5.2. OpenACS is for not effected by this, for the time being, OpenACS continues to use ns_pg_bind interface,

This generalizes the approach long available through ns_pg_bind in the nsdb postgres driver, making bind variables available through the existing ns_db execution commands and an optional driver interface.

The new features are:

• Availability across drivers: a shared bind-variable implementation in core nsdb, currently used by SQLite and reusable by other drivers.
• Uniform execution interface: binding options on the existing ns_db exec, dml, select, 1row and 0or1row commands.
• Three value sources: caller variables (-bindvars), an nsset (-bindset), or a Tcl dictionary (-binddict). Dictionary bindings are new; nsdbpg’s explicit -bind source accepts only an nsset.
• Strict binding checks: preserve nsdbpg’s useful behavior of rejecting missing bindings, helping catch misspelled names.
• Explicit empty-value policy: preserve empty text by default (as e.g. in nsdbpg); use -emptynull to map supplied empty values to SQL NULL.
• New collection bindings: -bindtypes supports Tcl lists conversions for IN expressions and rectangular rows for VALUES clauses, without extending the colon syntax. Default scalars as for nsdbpg now.
• New explicit binary bindings: bind bytearrays directly, including embedded NUL bytes, with optional per-column types for rows.
• Transparent caching: cache parsed parameter names and externally encoded SQL in Tcl objects. SQLite also caches external encodings of reusable string values.
• Capability reporting: query driver support through ns_db bindinfo.

The change is fully backward compatible for existing applications. Ordinary ns_db calls retain their behavior, and nsdbpg - including ns_pg_bind - is untouched. The new options are opt-in; they neither replace nor redirect the existing PostgreSQL interface. Existing drivers continue to work without implementing the optional callbacks, and the updated drivers retain conditional compilation for older core headers.

Some examples:

  ns_db dml $handle -bindvars {
      UPDATE items SET title=:title WHERE item_id=:item_id
  }
  ns_db select $handle -bindset $values $sql
  ns_db 1row $handle -binddict [dict create item_id 42] {
      SELECT title FROM items WHERE item_id=:item_id
  }

Exactly one source is required. Bind names use the familiar colon notation and follow nsdbpg’s name-scanning rules, including $ and # as continuation characters.

The -bindtypes option extends scalar binding:

  set ids {42 57 91}
  set rows [ns_db select $handle \
              -bindvars -bindtypes {ids list} {
      SELECT item_id, title FROM items WHERE item_id IN (:ids)
  }]

Supported interpretations are scalar (the default), list, rows and binary. Rows can declare column types:

  ns_db dml $handle \
      -bindvars \
      -bindtypes {values {rows {binary scalar scalar}}} {
          INSERT INTO peers VALUES :values
      }

Collection values remain bound parameters; their contents are never interpolated into SQL text. Binary interpretation is explicit rather than inferred from a Tcl object’s internal representation.

For the new interface, empty text remains empty text by default. -emptynull explicitly maps supplied empty values to SQL NULL. This does not change the empty-value conventions of existing interfaces such as nspgbind.

Driver support can be queried with:

  ns_db bindinfo $handle
  ns_db bindinfo $handle -version

The current implementation supports nsdbpg and SQLite. Adapting this to other ndb drivers is relatively simple (more work for oracle, since it has its own bind variable semantics).

The primary goals of these changes were wider availability, a uniform interface and additional functionality - to make SQL integration safer. Performance also improved in my local benchmarks. For a cached 64 KB SQL statement, converter overhead was approximately 1 µs for the shared implementation versus 2.35 µs for the existing nsdbpg converter. In a separate SQLite benchmark with eight repeatedly bound 64 KB strings, caching externally encoded values reduced complete execution time from approximately 713 to 197 µs. These measure particular workloads, not a general database speedup; fresh values still require conversion, and database execution can dominate overall time. The test just compares ns_pg_bin bind vars with the new ones. Much bigger improvements are for cases, where tcl-based SQL escaping is replaced.

More detailed documentation is on https://naviserver.sourceforge.io/5.2/nsdb/files/ns_db.html

Feedback on the preview - particularly further requirements, the option names, type metadata and driver interface - is welcome.

Best regards,
Gustaf