Fossil

Check-in [bd98f0f4]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Refactored th1 query API to use (query foo) instead of query_foo. Added th1_query.wiki doc page.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | th1-query-api
Files: files | file ages | folders
SHA1: bd98f0f4304fd49a0d0bf2c0f0d8055073d3892f
User & Date: stephan 2012-07-14 20:45:24.076
Context
2012-07-14
20:59
Refactored th1 query API col_xxx and bind_xxx to (col xxx) and (bind xxx). ... (check-in: 8260fdc9 user: stephan tags: th1-query-api)
20:45
Refactored th1 query API to use (query foo) instead of query_foo. Added th1_query.wiki doc page. ... (check-in: bd98f0f4 user: stephan tags: th1-query-api)
20:11
Added th1_ob.wiki embedded doc. ... (check-in: 905f67d0 user: stephan tags: th1-query-api)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/th.h.
221
222
223
224
225
226
227
228




229
230
231
232
233
234
235
** the current command's (name,length), i.e. (argv[0],argl[0]).
*/
int Th_WrongNumArgs2(Th_Interp *interp, const char *zCmdName,
                     int zCmdLen, const char *zMsg);

typedef struct Th_SubCommand {char *zName; Th_CommandProc xProc;} Th_SubCommand;
int Th_CallSubCommand(Th_Interp*,void*,int,const char**,int*,Th_SubCommand*);





/*
** Sends the given data through vTab->out.f() if vTab->out.enabled is
** true, otherwise this is a no-op. Returns 0 or higher on success, *
** a negative value if vTab->out.f is NULL.
*/
int Th_Vtab_output( Th_Vtab *vTab, char const * zData, int len );








|
>
>
>
>







221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
** the current command's (name,length), i.e. (argv[0],argl[0]).
*/
int Th_WrongNumArgs2(Th_Interp *interp, const char *zCmdName,
                     int zCmdLen, const char *zMsg);

typedef struct Th_SubCommand {char *zName; Th_CommandProc xProc;} Th_SubCommand;
int Th_CallSubCommand(Th_Interp*,void*,int,const char**,int*,Th_SubCommand*);
/*
** Works similarly to Th_CallSubCommand() but adjusts argc/argv/argl
** by 1 before passing on the call to the subcommand.
*/
int Th_CallSubCommand2(Th_Interp *interp, void *ctx, int argc, const char **argv, int *argl, Th_SubCommand *aSub);
/*
** Sends the given data through vTab->out.f() if vTab->out.enabled is
** true, otherwise this is a no-op. Returns 0 or higher on success, *
** a negative value if vTab->out.f is NULL.
*/
int Th_Vtab_output( Th_Vtab *vTab, char const * zData, int len );

Changes to src/th_lang.c.
879
880
881
882
883
884
885




















886
887
888
889
890
891
892
      return aSub[i].xProc(interp, ctx, argc, argv, argl);
    }
  }

  Th_ErrorMessage(interp, "Expected sub-command, got:", argv[1], argl[1]);
  return TH_ERROR;
}





















/*
** TH Syntax:
**
**   string compare STR1 STR2
**   string first   NEEDLE HAYSTACK ?STARTINDEX?
**   string is      CLASS STRING







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
      return aSub[i].xProc(interp, ctx, argc, argv, argl);
    }
  }

  Th_ErrorMessage(interp, "Expected sub-command, got:", argv[1], argl[1]);
  return TH_ERROR;
}

int Th_CallSubCommand2(
  Th_Interp *interp, 
  void *ctx,
  int argc,
  const char **argv,
  int *argl,
  Th_SubCommand *aSub
){
  int i;
  for(i=0; aSub[i].zName; i++){
    char const *zName = aSub[i].zName;
    if( th_strlen(zName)==argl[1] && 0==memcmp(zName, argv[1], argl[1]) ){
      return aSub[i].xProc(interp, ctx, argc-1, argv+1, argl+1);
    }
  }
  Th_ErrorMessage(interp, "Expected sub-command, got:", argv[1], argl[1]);
  return TH_ERROR;
}


/*
** TH Syntax:
**
**   string compare STR1 STR2
**   string first   NEEDLE HAYSTACK ?STARTINDEX?
**   string is      CLASS STRING
Changes to src/th_main.c.
1322
1323
1324
1325
1326
1327
1328



























1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346


1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360

1361
1362
1363
1364
1365
1366
1367
  rc = sqlite3_bind_double( pStmt, index, val );
  if(rc){
    return queryReportDbErr( interp );
  }
  Th_SetResultInt( interp, 0 );
  return TH_OK;
}




























int th_register_sqlite(Th_Interp *interp){
  enum { BufLen = 100 };
  char buf[BufLen];
  int i, l;
#define SET(K) l = snprintf(buf, BufLen, "%d", K);      \
  Th_SetVar( interp, #K, strlen(#K), buf, l );
  SET(SQLITE_BLOB);
  SET(SQLITE_DONE);
  SET(SQLITE_ERROR);
  SET(SQLITE_FLOAT);
  SET(SQLITE_INTEGER);
  SET(SQLITE_NULL);
  SET(SQLITE_OK);
  SET(SQLITE_ROW);
  SET(SQLITE_TEXT);
#undef SET
  static Th_Command_Reg aCommand[] = {


    {"query_bind_int",    queryBindIntCmd,   0},
    {"query_bind_double", queryBindDoubleCmd,0},
    {"query_bind_null",   queryBindNullCmd,  0},
    {"query_bind_string", queryBindStringCmd,0},
    {"query_col_count",   queryColCountCmd,  0},
    {"query_col_double",  queryColDoubleCmd, 0},
    {"query_col_int",     queryColIntCmd,    0},
    {"query_col_is_null", queryColIsNullCmd, 0},
    {"query_col_name",    queryColNameCmd,   0},
    {"query_col_string",  queryColStringCmd, 0},
    {"query_col_type",    queryColTypeCmd,   0},
    {"query_finalize",    queryFinalizeCmd,  0},
    {"query_prepare",     queryPrepareCmd,   0},
    {"query_step",        queryStepCmd,      0},

    {0, 0, 0}
  };
  Th_register_commands( interp, aCommand );
}

#endif
/* end TH_USE_SQLITE */







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


















>
>














>







1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
  rc = sqlite3_bind_double( pStmt, index, val );
  if(rc){
    return queryReportDbErr( interp );
  }
  Th_SetResultInt( interp, 0 );
  return TH_OK;
}

static int queryTopLevelCmd(
  Th_Interp *interp,
  void *ctx, 
  int argc, 
  const char **argv, 
  int *argl
){
  static Th_SubCommand aSub[] = {
    {"bind_int",    queryBindIntCmd},
    {"bind_double", queryBindDoubleCmd},
    {"bind_null",   queryBindNullCmd},
    {"bind_string", queryBindStringCmd},
    {"col_count",   queryColCountCmd},
    {"col_double",  queryColDoubleCmd},
    {"col_int",     queryColIntCmd},
    {"col_is_null", queryColIsNullCmd},
    {"col_name",    queryColNameCmd},
    {"col_string",  queryColStringCmd},
    {"col_type",    queryColTypeCmd},
    {"step",        queryStepCmd},
    {"finalize",    queryFinalizeCmd},
    {"prepare",     queryPrepareCmd},
    {0, 0}
  };
  Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
}

int th_register_sqlite(Th_Interp *interp){
  enum { BufLen = 100 };
  char buf[BufLen];
  int i, l;
#define SET(K) l = snprintf(buf, BufLen, "%d", K);      \
  Th_SetVar( interp, #K, strlen(#K), buf, l );
  SET(SQLITE_BLOB);
  SET(SQLITE_DONE);
  SET(SQLITE_ERROR);
  SET(SQLITE_FLOAT);
  SET(SQLITE_INTEGER);
  SET(SQLITE_NULL);
  SET(SQLITE_OK);
  SET(SQLITE_ROW);
  SET(SQLITE_TEXT);
#undef SET
  static Th_Command_Reg aCommand[] = {
    {"query",             queryTopLevelCmd,  0},
#if 0
    {"query_bind_int",    queryBindIntCmd,   0},
    {"query_bind_double", queryBindDoubleCmd,0},
    {"query_bind_null",   queryBindNullCmd,  0},
    {"query_bind_string", queryBindStringCmd,0},
    {"query_col_count",   queryColCountCmd,  0},
    {"query_col_double",  queryColDoubleCmd, 0},
    {"query_col_int",     queryColIntCmd,    0},
    {"query_col_is_null", queryColIsNullCmd, 0},
    {"query_col_name",    queryColNameCmd,   0},
    {"query_col_string",  queryColStringCmd, 0},
    {"query_col_type",    queryColTypeCmd,   0},
    {"query_finalize",    queryFinalizeCmd,  0},
    {"query_prepare",     queryPrepareCmd,   0},
    {"query_step",        queryStepCmd,      0},
#endif
    {0, 0, 0}
  };
  Th_register_commands( interp, aCommand );
}

#endif
/* end TH_USE_SQLITE */
Changes to test/th1-query-api-1.th1.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

proc xyz {} {
        return 42
}
set a [xyz]
puts "a=${a}" ! \n

set stmt [query_prepare {SELECT login, cap FROM user}]
set colCount [query_col_count $stmt]
puts "query column count: ${colCount}\n"
puts "stmt id=${stmt}\n"

proc noop {} {}
proc incr {name {step 1}} {
    upvar $name x
    set x [expr $x+$step]
}


set sep "    "
set i 0
set colNames(0) 0
for {set i 0} {$i < $colCount} {incr i} {
    set colNames($i) [query_col_name $stmt $i]
    puts "colNames($i)=" $colNames($i) "\n"
}

for {set row 0} {0 < [query_step $stmt]} {incr row} {
    for {set i 0} {$i < $colCount} {incr i} {
        if {$i > 0} {
            puts $sep
        } else {
            puts "#$row: $sep"
        }
        puts $colNames($i) = [query_col_string $stmt $i]
    }
    puts "\n"
}
unset row

query_finalize $stmt


proc query_step_each {{stmt} {callback}} {
    set colNames(0) 0
    set colCount [query_col_count $stmt]
    for {set i 0} {$i < $colCount} {incr i} {
        set colNames($i) [query_col_name $stmt $i]
    }
    upvar cb $callback
    for {set row 0} {0 < [query_step $stmt]} {incr row} {
        #puts "Calling callback: $stmt $colCount colNames\n"
        $callback $stmt $colCount
    }
}

set sql {SELECT uid, login FROM user WHERE uid!=?}
#set sql {SELECT uid, login FROM user WHERE login=?}
#set sql {SELECT tagid, value, null FROM tagxref WHERE value IS ? LIMIT 3}
set stmt [query_prepare $sql]
puts "stmt ID=" $stmt "\n"
query_bind_int $stmt 1 3
#set stmt [query_prepare $sql]
#query_bind_string $stmt 1 stephan
#set stmt [query_prepare $sql]
#query_bind_null $stmt 1
set rc 0
puts "USER LIST:\n"
catch {
    proc my_each {stmt colCount} {
        upvar 2 sep sep
        puts [query_col_int $stmt 0] " (type=" [query_col_type $stmt 0] ")" $sep
        puts [query_col_double $stmt 0] $sep
        puts [query_col_string $stmt 1]  " (type=" [query_col_type $stmt 1] ")" $sep
        puts "isnull 0 ?= " [query_col_is_null $stmt 0] $sep
        puts "isnull 2 ?= " [query_col_is_null $stmt 2]
#        for {set i 0} {$i < $colCount} {incr i} {
#            if {$i > 0} { puts $sep }
#        }
        puts "\n"
#        error "hi!"
    }
    query_step_each $stmt my_each
#    query_step_each $stmt {
#        proc each {stmt cc} { puts hi "\n" }
#    }
} rc
query_finalize $stmt
puts rc = $rc "\n"

set consts [list SQLITE_BLOB SQLITE_DONE SQLITE_ERROR SQLITE_FLOAT SQLITE_INTEGER SQLITE_NULL SQLITE_OK SQLITE_ROW SQLITE_TEXT]
#set consts $SQLITE_CONSTANTS
puts consts = $consts "\n"
for {set i 0} {$i < [llength $consts]} {incr i} {
    set x [lindex $consts $i]







|
|














|



|






|





|




|

|


|








|

|
|
|
|
|





|
|
|
|
|






|
|



|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

proc xyz {} {
        return 42
}
set a [xyz]
puts "a=${a}" ! \n

set stmt [query prepare {SELECT login, cap FROM user}]
set colCount [query col_count $stmt]
puts "query column count: ${colCount}\n"
puts "stmt id=${stmt}\n"

proc noop {} {}
proc incr {name {step 1}} {
    upvar $name x
    set x [expr $x+$step]
}


set sep "    "
set i 0
set colNames(0) 0
for {set i 0} {$i < $colCount} {incr i} {
    set colNames($i) [query col_name $stmt $i]
    puts "colNames($i)=" $colNames($i) "\n"
}

for {set row 0} {0 < [query step $stmt]} {incr row} {
    for {set i 0} {$i < $colCount} {incr i} {
        if {$i > 0} {
            puts $sep
        } else {
            puts "#$row: $sep"
        }
        puts $colNames($i) = [query col_string $stmt $i]
    }
    puts "\n"
}
unset row

query finalize $stmt


proc query_step_each {{stmt} {callback}} {
    set colNames(0) 0
    set colCount [query col_count $stmt]
    for {set i 0} {$i < $colCount} {incr i} {
        set colNames($i) [query col_name $stmt $i]
    }
    upvar cb $callback
    for {set row 0} {0 < [query step $stmt]} {incr row} {
        #puts "Calling callback: $stmt $colCount colNames\n"
        $callback $stmt $colCount
    }
}

set sql {SELECT uid, login FROM user WHERE uid!=?}
#set sql {SELECT uid, login FROM user WHERE login=?}
#set sql {SELECT tagid, value, null FROM tagxref WHERE value IS ? LIMIT 3}
set stmt [query prepare $sql]
puts "stmt ID=" $stmt "\n"
query bind_int $stmt 1 3
#set stmt [query prepare $sql]
#query bind_string $stmt 1 stephan
#set stmt [query prepare $sql]
#query bind_null $stmt 1
set rc 0
puts "USER LIST:\n"
catch {
    proc my_each {stmt colCount} {
        upvar 2 sep sep
        puts [query col_int $stmt 0] " (type=" [query col_type $stmt 0] ")" $sep
        puts [query col_double $stmt 0] $sep
        puts [query col_string $stmt 1]  " (type=" [query col_type $stmt 1] ")" $sep
        puts "isnull 0 ?= " [query col_is_null $stmt 0] $sep
        puts "isnull 2 ?= " [query col_is_null $stmt 2]
#        for {set i 0} {$i < $colCount} {incr i} {
#            if {$i > 0} { puts $sep }
#        }
        puts "\n"
#        error "hi!"
    }
    query step_each $stmt my_each
#    query step_each $stmt {
#        proc each {stmt cc} { puts hi "\n" }
#    }
} rc
query finalize $stmt
puts rc = $rc "\n"

set consts [list SQLITE_BLOB SQLITE_DONE SQLITE_ERROR SQLITE_FLOAT SQLITE_INTEGER SQLITE_NULL SQLITE_OK SQLITE_ROW SQLITE_TEXT]
#set consts $SQLITE_CONSTANTS
puts consts = $consts "\n"
for {set i 0} {$i < [llength $consts]} {incr i} {
    set x [lindex $consts $i]
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
enable_output 1

proc multiStmt {} {
    set max 5
    set i 0
    set s(0) 0
    for {set i 0} {$i < $max} {incr i} {
       set s($i) [query_prepare "SELECT $i"]
       puts "s($i) = $s($i)\n"
    }
    for {set i 0} {$i < $max} {incr i} {
       query_step $s($i)
    }
    for {set i 0} {$i < $max} {incr i} {
       puts "closing stmt $s($i)\n"
       query_finalize $s($i)
    }

    puts "Preparing again\n"

    for {set i 0} {$i < $max} {incr i} {
       set s($i) [query_prepare "SELECT $i"]
       puts "s($i) = $s($i)\n"
    }
    for {set i 0} {$i < $max} {incr i} {
       query_step $s($i)
    }
    puts "Closing again\n"

    for {set i 0} {$i < $max} {incr i} {
       puts "closing stmt $s($i)\n"
       query_finalize $s($i)
    }
}
multiStmt

enable_output 1
puts "If you got this far, you win!\n"
</th1>







|



|



|





|



|





|







137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
enable_output 1

proc multiStmt {} {
    set max 5
    set i 0
    set s(0) 0
    for {set i 0} {$i < $max} {incr i} {
       set s($i) [query prepare "SELECT $i"]
       puts "s($i) = $s($i)\n"
    }
    for {set i 0} {$i < $max} {incr i} {
       query step $s($i)
    }
    for {set i 0} {$i < $max} {incr i} {
       puts "closing stmt $s($i)\n"
       query finalize $s($i)
    }

    puts "Preparing again\n"

    for {set i 0} {$i < $max} {incr i} {
       set s($i) [query prepare "SELECT $i"]
       puts "s($i) = $s($i)\n"
    }
    for {set i 0} {$i < $max} {incr i} {
       query step $s($i)
    }
    puts "Closing again\n"

    for {set i 0} {$i < $max} {incr i} {
       puts "closing stmt $s($i)\n"
       query finalize $s($i)
    }
}
multiStmt

enable_output 1
puts "If you got this far, you win!\n"
</th1>
Added www/th1_query.wiki.




































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
<h1>TH1 "query" API</h1>

The "query" API provides limited access to the fossil database.
It restricts usage to queries which return result columns (i.e.
<tt>SELECT</tt> and friends).
Example usage:

<nowiki><pre>
&lt;th1>
catch {
    set stmt [query prepare "SELECT login, cap FROM user"]
    puts "stmt ID=$stmt\n"
    for {} {0 < [query step $stmt]} {} {
        puts [query col_string $stmt 0] ": " [query col_string $stmt 1] "\n"
    }
    query finalize $stmt
    return 0
} rc
if {0 != $rc} {
    puts "ERROR: $rc\n"
}
&lt;th1>
</pre></nowiki>

The various subcommands are summarized below...

<h2>prepare</h2>

This subcommand prepares a query for execution. It returns a statement handle
ID which must be passed to any other functions using the API.

All prepared statements must be <tt>finalize</tt>d when they have outlived
their usefulness.

<h2>finalize</h2>

Releases all resources associated with the statement. Note that future
calls to <tt>prepare</tt> might re-use the same statement statement
ID.

<nowiki><pre>
set stmt [query prepare "SELECT ..."]
query finalize $stmt
</pre></nowiki>


<h2>step</h2>

This subcommand steps the result set by one row. It returns 0
at the end of the set, a positive value if a new row is available,
and throws for any other condition.

<nowiki><pre>
for {} {0 &lt; [query step $stmt]} {} {
   puts [query col_string $stmt 0] "\n"
}
</pre></nowiki>


<h2>bind_xxx</h2>

The <tt>bind_xxx</tt> family of subcommands attach values to queries
before stepping through them. The subcommands include:

   *  <tt>bind_int StmtId Index Value</tt>
   *  <tt>bind_double StmtId Index Value</tt>
   *  <tt>bind_null StmtId Index</tt>
   *  <tt>bind_string StmtId Index Value</tt>

Achtung: the bind API uses 1-based indexes, just like SQL does.

TODO: split bind_xxx into "bind xxx".

<nowiki><pre>
set stmt [query prepare "SELECT ... WHERE user=?"]
query bind_int $stmt 1 drh
if {0 &lt; [query step $stmt]} {
   puts [query col_string $stmt 0] "\n"
}
query finalize $stmt
</pre></nowiki>


<h2>col_xxx</h2>

The <tt>col_xxx</tt> familys of subcommands are for fetching
values and metadata from result rows.

   *  <tt>col_count StmtId</tt> Returns the number of result columns in the statement.
   *  <tt>col_is_null StmtId Index</tt> Returns non-0 if the given column contains an SQL NULL value.
   *  <tt>col_double StmtId Index</tt>
   *  <tt>col_int StmtId Index</tt>
   *  <tt>col_string StmtId Index</tt>
   *  <tt>col_type StmtId Index</tt> Return value corresponds to one of the <tt>SQLITE_TYPENAME</tt> family of constants.

TODO: split col_xxx into "col xxx".

Achtung: the col API uses 0-based indexes, just like SQL does.