Fossil

Check-in [6e2f9edc]
Login

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

Overview
Comment:Added strftime support to the th1 query API.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | th1-query-api
Files: files | file ages | folders
SHA1: 6e2f9edc29c86c04ae70bd441a1b65a85b95bc5a
User & Date: stephan 2012-07-14 22:38:40
Context
2012-07-15
00:39
Added flags arg to Th_Render to allow us to eventually customize its output a bit. check-in: 3ab06e89 user: stephan tags: th1-query-api
2012-07-14
22:38
Added strftime support to the th1 query API. check-in: 6e2f9edc user: stephan tags: th1-query-api
21:36
Fixed short-form flag handling for th1 argv command in CLI mode. It now handles GET/POST params. check-in: 413a33f2 user: stephan tags: th1-query-api
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/th_main.c.

776
777
778
779
780
781
782



783
784
785
786
787
788
789
....
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197

1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209

1210
1211
1212
1213
1214
1215
1216
1217
1218
















































































1219
1220
1221
1222
1223
1224
1225
....
1391
1392
1393
1394
1395
1396
1397

1398
1399
1400
1401
1402
1403
1404
....
1411
1412
1413
1414
1415
1416
1417

1418
1419
1420
1421

1422
1423
1424
1425
1426
1427
1428
  Th_register_commands( interp, aCommand );
}

#endif
/* end TH_USE_ARGV */

#ifdef TH_USE_SQLITE




/*
** TH Syntax:
**
** query_prepare SQL
**
** Returns an opaque statement identifier.
................................................................................
  void *p, 
  int argc, 
  const char **argv, 
  int *argl
){
  sqlite3_stmt * pStmt = NULL;
  char const * val;
  int index;
  int rc = 0;
  int valLen;

  if( argc!=3 ){
    return Th_WrongNumArgs2(interp,
                            argv[0], argl[0],
                            "StmtHandle Index");
  }
  pStmt = queryStmtHandle(interp, argv[1], argl[1], &rc);
  if( rc < 1 ){
    return TH_ERROR;
  }
  if( 0 != Th_ToInt( interp, argv[2], argl[2], &index ) ){
    return TH_ERROR;
  }

  val = sqlite3_column_name( pStmt, index );
  if(NULL==val){
    Th_ErrorMessage(interp, "Column index out of bounds(?):", argv[2], -1);
    return TH_ERROR;
  }else{
    Th_SetResult( interp, val, strlen( val ) );
    return TH_OK;
  }
}

















































































/*
** TH Syntax:
**
** query_bind_null stmtId Index
**
** Binds a value to the given 1-based parameter index.
................................................................................
  static Th_SubCommand aSub[] = {
    {"count",   queryColCountCmd},
    {"is_null", queryColIsNullCmd},
    {"name",    queryColNameCmd},
    {"double",  queryColDoubleCmd},
    {"int",     queryColIntCmd},
    {"string",  queryColStringCmd},

    {"type",    queryColTypeCmd},
    {0, 0}
  };
  Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
}


................................................................................
){
  static Th_SubCommand aSub[] = {
    {"bind",        queryBindTopLevelCmd},
    {"col",         queryColTopLevelCmd},
    {"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 );







>
>
>







 







<

<
>





|
|
|
<
<


>









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







 







>







 







>




>







776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
....
1191
1192
1193
1194
1195
1196
1197

1198

1199
1200
1201
1202
1203
1204
1205
1206
1207


1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
....
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
....
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
  Th_register_commands( interp, aCommand );
}

#endif
/* end TH_USE_ARGV */

#ifdef TH_USE_SQLITE
#ifndef INTERFACE
#include "blob.h"
#endif

/*
** TH Syntax:
**
** query_prepare SQL
**
** Returns an opaque statement identifier.
................................................................................
  void *p, 
  int argc, 
  const char **argv, 
  int *argl
){
  sqlite3_stmt * pStmt = NULL;
  char const * val;

  int rc = 0;

  int index = -1;
  if( argc!=3 ){
    return Th_WrongNumArgs2(interp,
                            argv[0], argl[0],
                            "StmtHandle Index");
  }
  queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
  if(index < 0){
    assert(NULL==pStmt);


    return TH_ERROR;
  }
  assert(NULL!=pStmt);
  val = sqlite3_column_name( pStmt, index );
  if(NULL==val){
    Th_ErrorMessage(interp, "Column index out of bounds(?):", argv[2], -1);
    return TH_ERROR;
  }else{
    Th_SetResult( interp, val, strlen( val ) );
    return TH_OK;
  }
}

static int queryColTimeCmd(
  Th_Interp *interp,
  void *ctx, 
  int argc, 
  const char **argv, 
  int *argl
){
  sqlite3_stmt * pStmt = NULL;
  char const * val;
  char * fval;
  int i, rc = 0;
  int index = -1;
  char const * fmt;
  Blob sql = empty_blob;
  if( argc<4 ){
    return Th_WrongNumArgs2(interp,
                            argv[0], argl[0],
                            "StmtHandle Index Format");
  }
  queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
  if(index < 0){
    assert(NULL==pStmt);
    return TH_ERROR;
  }
  val = sqlite3_column_text( pStmt, index );
  fmt = argv[3];
  assert(NULL!=pStmt);
  blob_appendf(&sql,"SELECT strftime(%Q,%Q",
               fmt, val);
  if(argc>4){
    for(i = 4; i < argc; ++i ){
      blob_appendf(&sql, ",%Q", argv[i]);
    }
  }
  blob_append(&sql, ")", 1);
  fval = db_text(NULL,"%s", sql.aData);
  
  blob_reset(&sql);
  Th_SetResult( interp, fval, fval ? strlen(fval) : 0 );
  fossil_free(fval);
  return 0;
}

static int queryStrftimeCmd(
  Th_Interp *interp,
  void *ctx, 
  int argc, 
  const char **argv, 
  int *argl
){
  sqlite3_stmt * pStmt = NULL;
  char const * val;
  char * fval;
  int i, rc = 0;
  int index = -1;
  char const * fmt;
  Blob sql = empty_blob;
  if( argc<3 ){
    return Th_WrongNumArgs2(interp,
                            argv[0], argl[0],
                            "Format Value ?Modifiers...?");
  }
  fmt = argv[1];
  val = argv[2];
  blob_appendf(&sql,"SELECT strftime(%Q,%Q",
               fmt, val);
  if(argc>3){
    for(i = 3; i < argc; ++i ){
      blob_appendf(&sql, ",%Q", argv[i]);
    }
  }
  blob_append(&sql, ")", 1);
  fval = db_text(NULL,"%s", sql.aData);
  blob_reset(&sql);
  Th_SetResult( interp, fval, fval ? strlen(fval) : 0 );
  fossil_free(fval);
  return 0;
}


/*
** TH Syntax:
**
** query_bind_null stmtId Index
**
** Binds a value to the given 1-based parameter index.
................................................................................
  static Th_SubCommand aSub[] = {
    {"count",   queryColCountCmd},
    {"is_null", queryColIsNullCmd},
    {"name",    queryColNameCmd},
    {"double",  queryColDoubleCmd},
    {"int",     queryColIntCmd},
    {"string",  queryColStringCmd},
    {"time",    queryColTimeCmd},
    {"type",    queryColTypeCmd},
    {0, 0}
  };
  Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
}


................................................................................
){
  static Th_SubCommand aSub[] = {
    {"bind",        queryBindTopLevelCmd},
    {"col",         queryColTopLevelCmd},
    {"step",        queryStepCmd},
    {"finalize",    queryFinalizeCmd},
    {"prepare",     queryPrepareCmd},
    {"strftime",    queryStrftimeCmd},
    {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 );

Changes to www/th1_query.wiki.

52
53
54
55
56
57
58











59
60
61
62
63
64
65
..
85
86
87
88
89
90
91

92
93
94


















<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>
................................................................................
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.

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
























>
>
>
>
>
>
>
>
>
>
>







 







>



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
..
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

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


<h2>strftime</h2>

This works like <tt>col time</tt> (described below) but takes its
value from an arbitrary source specified by the 3rd argument.

<nowiki><pre>
query strftime %s 1319211587 unixepoch
query strftime {%Y%m%d @ %H:%M:%S} [query col string $stmt 2] {+10 years}]
</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>
................................................................................
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 string StmtId Index Format Modifiers</tt> See below.
   *  <tt>col type StmtId Index</tt> Return value corresponds to one of the <tt>SQLITE_TYPENAME</tt> family of constants.

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

<h3>col time</h3>

This function is a proxy for sqlite3's
<tt>[http://www.sqlite.org/lang_datefunc.html|strftime()]</tt> function. It is used like this:


<nowiki><pre>
query col time $stmt $index {%Y%m%d @ %H:%M:%S}
</pre></nowiki>

Any remaining arguments are treated as "modifiers" and passed as-is to strfmtime. For example:

<nowiki><pre>
query col time $stmt $index {%Y%m%d @ %H:%M:%S} {+5 years}
query col time $stmt $index %s unixepoch
</pre></nowiki>