[nsdbi] dbi_rows: add -columns option to list column names of re...

[NaviServer C. <nav...@li...>]

user:      Stephen Deasey <sd...@gm...>
date:      Sun Jul 27 21:48:46 2008 +0100
changeset: 212:4badc4883891
details:   http://naviserver.sourceforge.net/hgnsdbi/rev/4badc4883891

dbi_rows: add -columns option to list column names of result

Thanks to Daniel Stasinski for a working implementation.

diffstat:

4 files changed, 78 insertions(+), 16 deletions(-)
TODO                   |    7 ------
doc/src/mann/nsdbi.man |   19 +++++++++++++++++
tclcmds.c              |   52 +++++++++++++++++++++++++++++++++++++++---------
tests/dbi.test         |   16 ++++++++++++++

diffs (160 lines):

diff -r 618e3f203cbd -r 4badc4883891 TODO
--- a/TODO	Tue Jun 10 14:31:03 2008 +0100
+++ b/TODO	Sun Jul 27 21:48:46 2008 +0100
@@ -45,13 +45,6 @@
 
 ** Add Url2Pool callbacks (See url2file) to determine which handle to use
    when?  e.g. round-robin requests between 3 databases?
-
-
-** Column meta-data?
-
-     set rows [dbi_rows -columns columns {select a, b, c from ...}]
-     puts $columns
-     {a b c}
 
 
 ** Add higher level Tcl commands 'update', 'insert' etc.
diff -r 618e3f203cbd -r 4badc4883891 doc/src/mann/nsdbi.man
--- a/doc/src/mann/nsdbi.man	Tue Jun 10 14:31:03 2008 +0100
+++ b/doc/src/mann/nsdbi.man	Sun Jul 27 21:48:46 2008 +0100
@@ -67,6 +67,7 @@
 
 [call [cmd dbi_rows] \
       [vset standard_options] \
+      [opt [option "-columns [arg varname]"]] \
       [opt [option "-max [arg nrows]"]] \
       [opt [option -append]] \
       [opt [arg --]] \
@@ -155,6 +156,24 @@
 [list_end]
 
 [list_begin options]
+
+[opt_def -columns [arg varname]]
+
+If the [option -columns] option is given then it is the name of a Tcl variable
+which will be created to hold a list of the column names of the result.
+
+[para]
+This is useful in queries of the form "select * from ..." (but don't do that),
+and for generic procedures which handle a variety of queries, such as formatting
+queries as an HTML table.
+
+[example_begin]
+% dbi_rows [option "-columns [arg cols]"] {select first, last from peeps}
+{{Dirty Harry} {Jolly Roger}}
+
+% set cols
+{first last}
+[example_end]
 
 [opt_def -max [arg nrows]]
 
diff -r 618e3f203cbd -r 4badc4883891 tclcmds.c
--- a/tclcmds.c	Tue Jun 10 14:31:03 2008 +0100
+++ b/tclcmds.c	Sun Jul 27 21:48:46 2008 +0100
@@ -527,19 +527,21 @@
     InterpData   *idataPtr = arg;
     Dbi_Handle   *handle;
     unsigned int  colIdx, numCols;
-    Tcl_Obj      *resObj, *valueObj, *queryObj;
-    Tcl_Obj      *poolObj = NULL, *valuesObj = NULL;
+    CONST char   *colName;
+    Tcl_Obj      *resObj, *valueObj, *colListObj, *queryObj;
+    Tcl_Obj      *poolObj = NULL, *valuesObj = NULL, *colsNameObj = NULL;
     Tcl_Obj      *templateObj = NULL, *defaultObj = NULL;
     Ns_Time      *timeoutPtr = NULL;
     int           end, status, maxRows = -1, adp = 0;
 
     Ns_ObjvSpec opts[] = {
-        {"-db",        Ns_ObjvObj,    &poolObj,    NULL},
-        {"-timeout",   Ns_ObjvTime,   &timeoutPtr, NULL},
-        {"-bind",      Ns_ObjvObj,    &valuesObj,  NULL},
-        {"-max",       Ns_ObjvInt,    &maxRows,    NULL},
-        {"-append",    Ns_ObjvBool,   &adp,        (void *) NS_TRUE},
-        {"--",         Ns_ObjvBreak,  NULL,        NULL},
+        {"-db",        Ns_ObjvObj,    &poolObj,     NULL},
+        {"-timeout",   Ns_ObjvTime,   &timeoutPtr,  NULL},
+        {"-bind",      Ns_ObjvObj,    &valuesObj,   NULL},
+        {"-columns",   Ns_ObjvObj,    &colsNameObj, NULL},
+        {"-max",       Ns_ObjvInt,    &maxRows,     NULL},
+        {"-append",    Ns_ObjvBool,   &adp,         (void *) NS_TRUE},
+        {"--",         Ns_ObjvBreak,  NULL,         NULL},
         {NULL, NULL, NULL, NULL}
     };
     Ns_ObjvSpec args[] = {
@@ -575,7 +577,7 @@
 
         while ((status = NextRow(interp, handle, &end)) == TCL_OK && !end) {
 
-            for (colIdx = 0; colIdx < numCols; colIdx ++) {
+            for (colIdx = 0; colIdx < numCols; colIdx++) {
                 if ((status = ColumnValue(interp, handle, colIdx, &valueObj))
                         != TCL_OK) {
                     goto done;
@@ -587,6 +589,38 @@
                 }
             }
         }
+    }
+
+    /*
+     * Report the column names of the result set.
+     */
+
+    if (colsNameObj != NULL) {
+
+        status = TCL_ERROR;
+
+        colListObj = Tcl_ObjSetVar2(interp, colsNameObj, NULL,
+                                    Tcl_NewListObj(0, NULL), TCL_LEAVE_ERR_MSG);
+        if (colListObj == NULL) {
+            goto done;
+        }
+
+        numCols = Dbi_NumColumns(handle);
+        for (colIdx = 0; colIdx < numCols; colIdx++) {
+
+            if (Dbi_ColumnName(handle, colIdx, &colName) != NS_OK) {
+                Dbi_TclErrorResult(interp, handle);
+                goto done;
+            }
+            valueObj = Tcl_NewStringObj(colName, -1);
+            if (Tcl_ListObjAppendElement(interp, colListObj, valueObj)
+                    != TCL_OK) {
+                Tcl_DecrRefCount(valueObj);
+                goto done;
+            }
+        }
+
+        status = TCL_OK;
     }
 
  done:
diff -r 618e3f203cbd -r 4badc4883891 tests/dbi.test
--- a/tests/dbi.test	Tue Jun 10 14:31:03 2008 +0100
+++ b/tests/dbi.test	Sun Jul 27 21:48:46 2008 +0100
@@ -282,6 +282,22 @@
 test pool-global {query from non-default global pool} -body {
     dbi_rows -db global1 {ROWS 1 1 v}
 } -result v
+
+
+
+test columns-1 {1 column name} -body {
+    dbi_rows -columns cols {ROWS 1 1}
+    set cols
+} -cleanup {
+    unset -nocomplain cols
+} -result 0
+
+test columns-2 {multiple column names} -body {
+    dbi_rows -columns cols {ROWS 5 1}
+    set cols
+} -cleanup {
+    unset -nocomplain cols
+} -result {0 1 2 3 4}