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: |
6e2f9edc29c86c04ae70bd441a1b65a8 |
| 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
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 < [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 < [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>
|