Fossil SCM

Added strftime support to the th1 query API.

stephan 2012-07-14 22:38 th1-query-api
Commit 6e2f9edc29c86c04ae70bd441a1b65a85b95bc5a
Parent 413a33f20fc0614…
2 files changed +91 -7 +29
~ src/th_main.c ~ www/th1_query.wiki
M src/th_main.c
+91 -7
--- src/th_main.c
+++ src/th_main.c
@@ -778,10 +778,13 @@
778778
779779
 #endif
780780
 /* end TH_USE_ARGV */
781781
782782
 #ifdef TH_USE_SQLITE
783
+#ifndef INTERFACE
784
+#include "blob.h"
785
+#endif
783786
784787
 /*
785788
 ** TH Syntax:
786789
 **
787790
 ** query_prepare SQL
@@ -1190,34 +1193,112 @@
11901193
 const char **argv,
11911194
 int *argl
11921195
 ){
11931196
 sqlite3_stmt * pStmt = NULL;
11941197
 char const * val;
1195
- int index;
11961198
 int rc = 0;
1197
- int valLen;
1199
+ int index = -1;
11981200
 if( argc!=3 ){
11991201
 return Th_WrongNumArgs2(interp,
12001202
 argv[0], argl[0],
12011203
 "StmtHandle Index");
12021204
 }
1203
- pStmt = queryStmtHandle(interp, argv[1], argl[1], &rc);
1204
- if( rc < 1 ){
1205
+ queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
1206
+ if(index < 0){
1207
+ assert(NULL==pStmt);
12051208
 return TH_ERROR;
12061209
 }
1207
- if( 0 != Th_ToInt( interp, argv[2], argl[2], &index ) ){
1208
- return TH_ERROR;
1209
- }
1210
+ assert(NULL!=pStmt);
12101211
 val = sqlite3_column_name( pStmt, index );
12111212
 if(NULL==val){
12121213
 Th_ErrorMessage(interp, "Column index out of bounds(?):", argv[2], -1);
12131214
 return TH_ERROR;
12141215
 }else{
12151216
 Th_SetResult( interp, val, strlen( val ) );
12161217
 return TH_OK;
12171218
 }
12181219
 }
1220
+
1221
+static int queryColTimeCmd(
1222
+ Th_Interp *interp,
1223
+ void *ctx,
1224
+ int argc,
1225
+ const char **argv,
1226
+ int *argl
1227
+){
1228
+ sqlite3_stmt * pStmt = NULL;
1229
+ char const * val;
1230
+ char * fval;
1231
+ int i, rc = 0;
1232
+ int index = -1;
1233
+ char const * fmt;
1234
+ Blob sql = empty_blob;
1235
+ if( argc<4 ){
1236
+ return Th_WrongNumArgs2(interp,
1237
+ argv[0], argl[0],
1238
+ "StmtHandle Index Format");
1239
+ }
1240
+ queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
1241
+ if(index < 0){
1242
+ assert(NULL==pStmt);
1243
+ return TH_ERROR;
1244
+ }
1245
+ val = sqlite3_column_text( pStmt, index );
1246
+ fmt = argv[3];
1247
+ assert(NULL!=pStmt);
1248
+ blob_appendf(&sql,"SELECT strftime(%Q,%Q",
1249
+ fmt, val);
1250
+ if(argc>4){
1251
+ for(i = 4; i < argc; ++i ){
1252
+ blob_appendf(&sql, ",%Q", argv[i]);
1253
+ }
1254
+ }
1255
+ blob_append(&sql, ")", 1);
1256
+ fval = db_text(NULL,"%s", sql.aData);
1257
+
1258
+ blob_reset(&sql);
1259
+ Th_SetResult( interp, fval, fval ? strlen(fval) : 0 );
1260
+ fossil_free(fval);
1261
+ return 0;
1262
+}
1263
+
1264
+static int queryStrftimeCmd(
1265
+ Th_Interp *interp,
1266
+ void *ctx,
1267
+ int argc,
1268
+ const char **argv,
1269
+ int *argl
1270
+){
1271
+ sqlite3_stmt * pStmt = NULL;
1272
+ char const * val;
1273
+ char * fval;
1274
+ int i, rc = 0;
1275
+ int index = -1;
1276
+ char const * fmt;
1277
+ Blob sql = empty_blob;
1278
+ if( argc<3 ){
1279
+ return Th_WrongNumArgs2(interp,
1280
+ argv[0], argl[0],
1281
+ "Format Value ?Modifiers...?");
1282
+ }
1283
+ fmt = argv[1];
1284
+ val = argv[2];
1285
+ blob_appendf(&sql,"SELECT strftime(%Q,%Q",
1286
+ fmt, val);
1287
+ if(argc>3){
1288
+ for(i = 3; i < argc; ++i ){
1289
+ blob_appendf(&sql, ",%Q", argv[i]);
1290
+ }
1291
+ }
1292
+ blob_append(&sql, ")", 1);
1293
+ fval = db_text(NULL,"%s", sql.aData);
1294
+ blob_reset(&sql);
1295
+ Th_SetResult( interp, fval, fval ? strlen(fval) : 0 );
1296
+ fossil_free(fval);
1297
+ return 0;
1298
+}
1299
+
12191300
12201301
 /*
12211302
 ** TH Syntax:
12221303
 **
12231304
 ** query_bind_null stmtId Index
@@ -1393,10 +1474,11 @@
13931474
 {"is_null", queryColIsNullCmd},
13941475
 {"name", queryColNameCmd},
13951476
 {"double", queryColDoubleCmd},
13961477
 {"int", queryColIntCmd},
13971478
 {"string", queryColStringCmd},
1479
+ {"time", queryColTimeCmd},
13981480
 {"type", queryColTypeCmd},
13991481
 {0, 0}
14001482
 };
14011483
 Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
14021484
 }
@@ -1413,14 +1495,16 @@
14131495
 {"bind", queryBindTopLevelCmd},
14141496
 {"col", queryColTopLevelCmd},
14151497
 {"step", queryStepCmd},
14161498
 {"finalize", queryFinalizeCmd},
14171499
 {"prepare", queryPrepareCmd},
1500
+ {"strftime", queryStrftimeCmd},
14181501
 {0, 0}
14191502
 };
14201503
 Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
14211504
 }
1505
+
14221506
14231507
 int th_register_sqlite(Th_Interp *interp){
14241508
 enum { BufLen = 100 };
14251509
 char buf[BufLen];
14261510
 int i, l;
14271511
--- src/th_main.c
+++ src/th_main.c
@@ -778,10 +778,13 @@
778
779 #endif
780 /* end TH_USE_ARGV */
781
782 #ifdef TH_USE_SQLITE
 
 
 
783
784 /*
785 ** TH Syntax:
786 **
787 ** query_prepare SQL
@@ -1190,34 +1193,112 @@
1190 const char **argv,
1191 int *argl
1192 ){
1193 sqlite3_stmt * pStmt = NULL;
1194 char const * val;
1195 int index;
1196 int rc = 0;
1197 int valLen;
1198 if( argc!=3 ){
1199 return Th_WrongNumArgs2(interp,
1200 argv[0], argl[0],
1201 "StmtHandle Index");
1202 }
1203 pStmt = queryStmtHandle(interp, argv[1], argl[1], &rc);
1204 if( rc < 1 ){
 
1205 return TH_ERROR;
1206 }
1207 if( 0 != Th_ToInt( interp, argv[2], argl[2], &index ) ){
1208 return TH_ERROR;
1209 }
1210 val = sqlite3_column_name( pStmt, index );
1211 if(NULL==val){
1212 Th_ErrorMessage(interp, "Column index out of bounds(?):", argv[2], -1);
1213 return TH_ERROR;
1214 }else{
1215 Th_SetResult( interp, val, strlen( val ) );
1216 return TH_OK;
1217 }
1218 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1219
1220 /*
1221 ** TH Syntax:
1222 **
1223 ** query_bind_null stmtId Index
@@ -1393,10 +1474,11 @@
1393 {"is_null", queryColIsNullCmd},
1394 {"name", queryColNameCmd},
1395 {"double", queryColDoubleCmd},
1396 {"int", queryColIntCmd},
1397 {"string", queryColStringCmd},
 
1398 {"type", queryColTypeCmd},
1399 {0, 0}
1400 };
1401 Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
1402 }
@@ -1413,14 +1495,16 @@
1413 {"bind", queryBindTopLevelCmd},
1414 {"col", queryColTopLevelCmd},
1415 {"step", queryStepCmd},
1416 {"finalize", queryFinalizeCmd},
1417 {"prepare", queryPrepareCmd},
 
1418 {0, 0}
1419 };
1420 Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
1421 }
 
1422
1423 int th_register_sqlite(Th_Interp *interp){
1424 enum { BufLen = 100 };
1425 char buf[BufLen];
1426 int i, l;
1427
--- src/th_main.c
+++ src/th_main.c
@@ -778,10 +778,13 @@
778
779 #endif
780 /* end TH_USE_ARGV */
781
782 #ifdef TH_USE_SQLITE
783 #ifndef INTERFACE
784 #include "blob.h"
785 #endif
786
787 /*
788 ** TH Syntax:
789 **
790 ** query_prepare SQL
@@ -1190,34 +1193,112 @@
1193 const char **argv,
1194 int *argl
1195 ){
1196 sqlite3_stmt * pStmt = NULL;
1197 char const * val;
 
1198 int rc = 0;
1199 int index = -1;
1200 if( argc!=3 ){
1201 return Th_WrongNumArgs2(interp,
1202 argv[0], argl[0],
1203 "StmtHandle Index");
1204 }
1205 queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
1206 if(index < 0){
1207 assert(NULL==pStmt);
1208 return TH_ERROR;
1209 }
1210 assert(NULL!=pStmt);
 
 
1211 val = sqlite3_column_name( pStmt, index );
1212 if(NULL==val){
1213 Th_ErrorMessage(interp, "Column index out of bounds(?):", argv[2], -1);
1214 return TH_ERROR;
1215 }else{
1216 Th_SetResult( interp, val, strlen( val ) );
1217 return TH_OK;
1218 }
1219 }
1220
1221 static int queryColTimeCmd(
1222 Th_Interp *interp,
1223 void *ctx,
1224 int argc,
1225 const char **argv,
1226 int *argl
1227 ){
1228 sqlite3_stmt * pStmt = NULL;
1229 char const * val;
1230 char * fval;
1231 int i, rc = 0;
1232 int index = -1;
1233 char const * fmt;
1234 Blob sql = empty_blob;
1235 if( argc<4 ){
1236 return Th_WrongNumArgs2(interp,
1237 argv[0], argl[0],
1238 "StmtHandle Index Format");
1239 }
1240 queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
1241 if(index < 0){
1242 assert(NULL==pStmt);
1243 return TH_ERROR;
1244 }
1245 val = sqlite3_column_text( pStmt, index );
1246 fmt = argv[3];
1247 assert(NULL!=pStmt);
1248 blob_appendf(&sql,"SELECT strftime(%Q,%Q",
1249 fmt, val);
1250 if(argc>4){
1251 for(i = 4; i < argc; ++i ){
1252 blob_appendf(&sql, ",%Q", argv[i]);
1253 }
1254 }
1255 blob_append(&sql, ")", 1);
1256 fval = db_text(NULL,"%s", sql.aData);
1257
1258 blob_reset(&sql);
1259 Th_SetResult( interp, fval, fval ? strlen(fval) : 0 );
1260 fossil_free(fval);
1261 return 0;
1262 }
1263
1264 static int queryStrftimeCmd(
1265 Th_Interp *interp,
1266 void *ctx,
1267 int argc,
1268 const char **argv,
1269 int *argl
1270 ){
1271 sqlite3_stmt * pStmt = NULL;
1272 char const * val;
1273 char * fval;
1274 int i, rc = 0;
1275 int index = -1;
1276 char const * fmt;
1277 Blob sql = empty_blob;
1278 if( argc<3 ){
1279 return Th_WrongNumArgs2(interp,
1280 argv[0], argl[0],
1281 "Format Value ?Modifiers...?");
1282 }
1283 fmt = argv[1];
1284 val = argv[2];
1285 blob_appendf(&sql,"SELECT strftime(%Q,%Q",
1286 fmt, val);
1287 if(argc>3){
1288 for(i = 3; i < argc; ++i ){
1289 blob_appendf(&sql, ",%Q", argv[i]);
1290 }
1291 }
1292 blob_append(&sql, ")", 1);
1293 fval = db_text(NULL,"%s", sql.aData);
1294 blob_reset(&sql);
1295 Th_SetResult( interp, fval, fval ? strlen(fval) : 0 );
1296 fossil_free(fval);
1297 return 0;
1298 }
1299
1300
1301 /*
1302 ** TH Syntax:
1303 **
1304 ** query_bind_null stmtId Index
@@ -1393,10 +1474,11 @@
1474 {"is_null", queryColIsNullCmd},
1475 {"name", queryColNameCmd},
1476 {"double", queryColDoubleCmd},
1477 {"int", queryColIntCmd},
1478 {"string", queryColStringCmd},
1479 {"time", queryColTimeCmd},
1480 {"type", queryColTypeCmd},
1481 {0, 0}
1482 };
1483 Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
1484 }
@@ -1413,14 +1495,16 @@
1495 {"bind", queryBindTopLevelCmd},
1496 {"col", queryColTopLevelCmd},
1497 {"step", queryStepCmd},
1498 {"finalize", queryFinalizeCmd},
1499 {"prepare", queryPrepareCmd},
1500 {"strftime", queryStrftimeCmd},
1501 {0, 0}
1502 };
1503 Th_CallSubCommand2( interp, ctx, argc, argv, argl, aSub );
1504 }
1505
1506
1507 int th_register_sqlite(Th_Interp *interp){
1508 enum { BufLen = 100 };
1509 char buf[BufLen];
1510 int i, l;
1511
M www/th1_query.wiki
+29
--- www/th1_query.wiki
+++ www/th1_query.wiki
@@ -54,10 +54,21 @@
5454
 for {} {0 &lt; [query step $stmt]} {} {
5555
 puts [query col string $stmt 0] "\n"
5656
 }
5757
 </pre></nowiki>
5858
59
+
60
+<h2>strftime</h2>
61
+
62
+This works like <tt>col time</tt> (described below) but takes its
63
+value from an arbitrary source specified by the 3rd argument.
64
+
65
+<nowiki><pre>
66
+query strftime %s 1319211587 unixepoch
67
+query strftime {%Y%m%d @ %H:%M:%S} [query col string $stmt 2] {+10 years}]
68
+</pre></nowiki>
69
+
5970
6071
 <h2>bind xxx</h2>
6172
6273
 The <tt>bind xxx</tt> family of subcommands attach values to queries
6374
 before stepping through them. The subcommands include:
@@ -87,8 +98,26 @@
8798
 * <tt>col count StmtId</tt> Returns the number of result columns in the statement.
8899
 * <tt>col is_null StmtId Index</tt> Returns non-0 if the given column contains an SQL NULL value.
89100
 * <tt>col double StmtId Index</tt>
90101
 * <tt>col int StmtId Index</tt>
91102
 * <tt>col string StmtId Index</tt>
103
+ * <tt>col string StmtId Index Format Modifiers</tt> See below.
92104
 * <tt>col type StmtId Index</tt> Return value corresponds to one of the <tt>SQLITE_TYPENAME</tt> family of constants.
93105
94106
 Achtung: the col API uses 0-based indexes, just like SQL does.
107
+
108
+<h3>col time</h3>
109
+
110
+This function is a proxy for sqlite3's
111
+<tt>[http://www.sqlite.org/lang_datefunc.html|strftime()]</tt> function. It is used like this:
112
+
113
+
114
+<nowiki><pre>
115
+query col time $stmt $index {%Y%m%d @ %H:%M:%S}
116
+</pre></nowiki>
117
+
118
+Any remaining arguments are treated as "modifiers" and passed as-is to strfmtime. For example:
119
+
120
+<nowiki><pre>
121
+query col time $stmt $index {%Y%m%d @ %H:%M:%S} {+5 years}
122
+query col time $stmt $index %s unixepoch
123
+</pre></nowiki>
95124
--- www/th1_query.wiki
+++ www/th1_query.wiki
@@ -54,10 +54,21 @@
54 for {} {0 &lt; [query step $stmt]} {} {
55 puts [query col string $stmt 0] "\n"
56 }
57 </pre></nowiki>
58
 
 
 
 
 
 
 
 
 
 
 
59
60 <h2>bind xxx</h2>
61
62 The <tt>bind xxx</tt> family of subcommands attach values to queries
63 before stepping through them. The subcommands include:
@@ -87,8 +98,26 @@
87 * <tt>col count StmtId</tt> Returns the number of result columns in the statement.
88 * <tt>col is_null StmtId Index</tt> Returns non-0 if the given column contains an SQL NULL value.
89 * <tt>col double StmtId Index</tt>
90 * <tt>col int StmtId Index</tt>
91 * <tt>col string StmtId Index</tt>
 
92 * <tt>col type StmtId Index</tt> Return value corresponds to one of the <tt>SQLITE_TYPENAME</tt> family of constants.
93
94 Achtung: the col API uses 0-based indexes, just like SQL does.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
95
--- www/th1_query.wiki
+++ www/th1_query.wiki
@@ -54,10 +54,21 @@
54 for {} {0 &lt; [query step $stmt]} {} {
55 puts [query col string $stmt 0] "\n"
56 }
57 </pre></nowiki>
58
59
60 <h2>strftime</h2>
61
62 This works like <tt>col time</tt> (described below) but takes its
63 value from an arbitrary source specified by the 3rd argument.
64
65 <nowiki><pre>
66 query strftime %s 1319211587 unixepoch
67 query strftime {%Y%m%d @ %H:%M:%S} [query col string $stmt 2] {+10 years}]
68 </pre></nowiki>
69
70
71 <h2>bind xxx</h2>
72
73 The <tt>bind xxx</tt> family of subcommands attach values to queries
74 before stepping through them. The subcommands include:
@@ -87,8 +98,26 @@
98 * <tt>col count StmtId</tt> Returns the number of result columns in the statement.
99 * <tt>col is_null StmtId Index</tt> Returns non-0 if the given column contains an SQL NULL value.
100 * <tt>col double StmtId Index</tt>
101 * <tt>col int StmtId Index</tt>
102 * <tt>col string StmtId Index</tt>
103 * <tt>col string StmtId Index Format Modifiers</tt> See below.
104 * <tt>col type StmtId Index</tt> Return value corresponds to one of the <tt>SQLITE_TYPENAME</tt> family of constants.
105
106 Achtung: the col API uses 0-based indexes, just like SQL does.
107
108 <h3>col time</h3>
109
110 This function is a proxy for sqlite3's
111 <tt>[http://www.sqlite.org/lang_datefunc.html|strftime()]</tt> function. It is used like this:
112
113
114 <nowiki><pre>
115 query col time $stmt $index {%Y%m%d @ %H:%M:%S}
116 </pre></nowiki>
117
118 Any remaining arguments are treated as "modifiers" and passed as-is to strfmtime. For example:
119
120 <nowiki><pre>
121 query col time $stmt $index {%Y%m%d @ %H:%M:%S} {+5 years}
122 query col time $stmt $index %s unixepoch
123 </pre></nowiki>
124

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button