Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Made the query col argument ordering more flexible/forgiving. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | th1-query-api |
| Files: | files | file ages | folders |
| SHA1: |
a561c43919e549d05e840ce358efb29f |
| User & Date: | stephan 2012-07-15 14:54:21 |
Context
|
2012-07-15
| ||
| 15:28 | Added query reset, refactored bind commands to accept their indexes in the same way as the col commands do (and expanded the remaining col commands which did not do so). check-in: f2ee33d4 user: stephan tags: th1-query-api | |
| 14:54 | Made the query col argument ordering more flexible/forgiving. check-in: a561c439 user: stephan tags: th1-query-api | |
| 14:31 | th1 query API now accepts the statement ID as the first arg after the query command or later on after the subcommand, for all subcommands where this makes sense. check-in: 09d7df15 user: stephan tags: th1-query-api | |
Changes
Changes to src/th_main.c.
872 873 874 875 876 877 878 879 880 881 882 883 884 885 .... 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 .... 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 .... 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 .... 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 .... 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 .... 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 .... 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 .... 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 |
*/
static sqlite3_stmt * Th_GetStmt(Th_Interp *interp, int stmtId);
struct Th_Sqlite {
sqlite3_stmt ** aStmt;
int nStmt;
};
#define Th_Sqlite_KEY "Th_Sqlite"
typedef struct Th_Sqlite Th_Sqlite;
static Th_Sqlite * Th_sqlite_manager( Th_Interp * interp ){
void * p = Th_Data_Get( interp, Th_Sqlite_KEY );
return p ? (Th_Sqlite*)p : NULL;
................................................................................
}
/*
** TH Syntax:
**
** query col string stmtId Index
** query stmtId col string Index
**
** Returns the result column value at the given 0-based index.
*/
static int queryColStringCmd(
Th_Interp *interp,
void *p,
int argc,
................................................................................
}
/*
** TH Syntax:
**
** query col int stmtId Index
** query stmtId col int Index
**
** Returns the result column value at the given 0-based index.
*/
static int queryColIntCmd(
Th_Interp *interp,
void *p,
int argc,
................................................................................
}
/*
** TH Syntax:
**
** query col double stmtId Index
** query stmtId col double Index
**
** Returns the result column value at the given 0-based index.
*/
static int queryColDoubleCmd(
Th_Interp *interp,
void *p,
int argc,
................................................................................
Th_SetResultDouble( interp, sqlite3_column_double( pStmt, index ) );
return TH_OK;
}
/*
** TH Syntax:
**
** query col is_null stmtId Index
** query stmtId col is_null Index
**
** Returns non-0 if the given 0-based result column index contains
** an SQL NULL value, else returns 0.
*/
static int queryColIsNullCmd(
Th_Interp *interp,
void *p,
int argc,
const char **argv,
int *argl
){
sqlite3_stmt * pStmt = (sqlite3_stmt*)p;
int requireArgc = pStmt ? 2 : 3;
double rc = 0;
int index = -1;
if( argc!=requireArgc ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
}else{
Th_ToInt(interp, argv[1], argl[1], &index);
}
if(index < 0){
return TH_ERROR;
}
Th_SetResultInt( interp,
SQLITE_NULL==sqlite3_column_type( pStmt, index )
................................................................................
}
/*
** TH Syntax:
**
** query col type stmtId Index
** query stmtId col type Index
**
** Returns the sqlite type identifier for the given 0-based result
** column index. The values are available in TH as $SQLITE_NULL,
** $SQLITE_INTEGER, etc.
*/
static int queryColTypeCmd(
Th_Interp *interp,
void *p,
int argc,
const char **argv,
int *argl
){
sqlite3_stmt * pStmt = (sqlite3_stmt*)p;
int requireArgc = pStmt ? 2 : 3;
double rc = 0;
int index = -1;
if( argc!=requireArgc ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
}else{
Th_ToInt( interp, argv[1], argl[1], &index );
}
if(index < 0){
return TH_ERROR;
}
Th_SetResultInt( interp, sqlite3_column_type( pStmt, index ) );
return TH_OK;
................................................................................
}
/*
** TH Syntax:
**
** query col name stmtId Index
** query stmtId col name Index
**
** Returns the result column name at the given 0-based index.
*/
static int queryColNameCmd(
Th_Interp *interp,
void *p,
int argc,
const char **argv,
int *argl
){
sqlite3_stmt * pStmt = (sqlite3_stmt*)p;
int requireArgc = pStmt ? 2 : 3;
char const * val;
int rc = 0;
int index = -1;
if( argc!=requireArgc ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
}else{
Th_ToInt( interp, argv[1], argl[1], &index );
}
if(index < 0){
return TH_ERROR;
}
assert(NULL!=pStmt);
val = sqlite3_column_name( pStmt, index );
................................................................................
}
/*
** TH Syntax:
**
** query col time stmtId Index format
** query stmtId col name Index format
**
** Returns the result column name at the given 0-based index.
*/
static int queryColTimeCmd(
Th_Interp *interp,
void *ctx,
int argc,
const char **argv,
int *argl
){
sqlite3_stmt * pStmt = (sqlite3_stmt*)ctx;
int minArgs = pStmt ? 3 : 4;
int argPos;
char const * val;
char * fval;
int i, rc = 0;
int index = -1;
char const * fmt;
Blob sql = empty_blob;
if( argc<minArgs ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index Format");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
argPos = 3;
}else{
Th_ToInt( interp, argv[1], argl[1], &index );
argPos = 2;
}
if(index < 0){
return TH_ERROR;
}
val = sqlite3_column_text( pStmt, index );
fmt = argv[argPos++];
assert(NULL!=pStmt);
................................................................................
static int queryColTopLevelCmd(
Th_Interp *interp,
void *ctx,
int argc,
const char **argv,
int *argl
){
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 int queryTopLevelCmd(
Th_Interp *interp,
void *ctx,
int argc,
|
> > > > | > > > > < | > > > > < | > > > | | > > > < > | > > > | > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > | > |
872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 .... 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 .... 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 .... 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 .... 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 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 .... 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 .... 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 .... 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 .... 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 |
*/
static sqlite3_stmt * Th_GetStmt(Th_Interp *interp, int stmtId);
struct Th_Sqlite {
sqlite3_stmt ** aStmt;
int nStmt;
int colCmdIndex;
};
#define Th_Sqlite_KEY "Th_Sqlite"
typedef struct Th_Sqlite Th_Sqlite;
static Th_Sqlite * Th_sqlite_manager( Th_Interp * interp ){
void * p = Th_Data_Get( interp, Th_Sqlite_KEY );
return p ? (Th_Sqlite*)p : NULL;
................................................................................
}
/*
** TH Syntax:
**
** query col string stmtId Index
** query stmtId col string Index
** query stmtId col Index string
**
** Returns the result column value at the given 0-based index.
*/
static int queryColStringCmd(
Th_Interp *interp,
void *p,
int argc,
................................................................................
}
/*
** TH Syntax:
**
** query col int stmtId Index
** query stmtId col int Index
** query stmtId col Index int
**
** Returns the result column value at the given 0-based index.
*/
static int queryColIntCmd(
Th_Interp *interp,
void *p,
int argc,
................................................................................
}
/*
** TH Syntax:
**
** query col double stmtId Index
** query stmtId col double Index
** query stmtId col Index double
**
** Returns the result column value at the given 0-based index.
*/
static int queryColDoubleCmd(
Th_Interp *interp,
void *p,
int argc,
................................................................................
Th_SetResultDouble( interp, sqlite3_column_double( pStmt, index ) );
return TH_OK;
}
/*
** TH Syntax:
**
** query col isnull stmtId Index
** query stmtId col is_null Index
** query stmtId col Index isnull
**
** Returns non-0 if the given 0-based result column index contains
** an SQL NULL value, else returns 0.
*/
static int queryColIsNullCmd(
Th_Interp *interp,
void *p,
int argc,
const char **argv,
int *argl
){
Th_Sqlite * sq = Th_sqlite_manager(interp);
int index = sq->colCmdIndex;
sqlite3_stmt * pStmt = (sqlite3_stmt*)p;
int requireArgc = pStmt ? 2 : 3;
if( index >= 0 ) --requireArgc;
double rc = 0;
if( argc!=requireArgc ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
}else if(index<0){
Th_ToInt(interp, argv[1], argl[1], &index);
}
if(index < 0){
return TH_ERROR;
}
Th_SetResultInt( interp,
SQLITE_NULL==sqlite3_column_type( pStmt, index )
................................................................................
}
/*
** TH Syntax:
**
** query col type stmtId Index
** query stmtId col type Index
** query stmtId col Index type
**
** Returns the sqlite type identifier for the given 0-based result
** column index. The values are available in TH as $SQLITE_NULL,
** $SQLITE_INTEGER, etc.
*/
static int queryColTypeCmd(
Th_Interp *interp,
void *p,
int argc,
const char **argv,
int *argl
){
Th_Sqlite * sq = Th_sqlite_manager(interp);
int index = sq->colCmdIndex;
sqlite3_stmt * pStmt = (sqlite3_stmt*)p;
int requireArgc = pStmt ? 2 : 3;
if( index >= 0 ) --requireArgc;
double rc = 0;
if( argc!=requireArgc ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
}else if(index<0){
Th_ToInt( interp, argv[1], argl[1], &index );
}
if(index < 0){
return TH_ERROR;
}
Th_SetResultInt( interp, sqlite3_column_type( pStmt, index ) );
return TH_OK;
................................................................................
}
/*
** TH Syntax:
**
** query col name stmtId Index
** query stmtId col name Index
** query stmtId col Index name
**
** Returns the result column name at the given 0-based index.
*/
static int queryColNameCmd(
Th_Interp *interp,
void *p,
int argc,
const char **argv,
int *argl
){
Th_Sqlite * sq = Th_sqlite_manager(interp);
int index = sq->colCmdIndex;
sqlite3_stmt * pStmt = (sqlite3_stmt*)p;
int requireArgc = pStmt ? 2 : 3;
char const * val;
int rc = 0;
if( index >= 0 ) --requireArgc;
if( argc!=requireArgc ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
}else if(index<0){
Th_ToInt( interp, argv[1], argl[1], &index );
}
if(index < 0){
return TH_ERROR;
}
assert(NULL!=pStmt);
val = sqlite3_column_name( pStmt, index );
................................................................................
}
/*
** TH Syntax:
**
** query col time stmtId Index format
** query stmtId col name Index format
** query stmtId col Index name format
**
** Returns the result column name at the given 0-based index.
*/
static int queryColTimeCmd(
Th_Interp *interp,
void *ctx,
int argc,
const char **argv,
int *argl
){
Th_Sqlite * sq = Th_sqlite_manager(interp);
int index = sq->colCmdIndex;
sqlite3_stmt * pStmt = (sqlite3_stmt*)ctx;
int minArgs = pStmt ? 3 : 4;
int argPos;
char const * val;
char * fval;
int i, rc = 0;
char const * fmt;
Blob sql = empty_blob;
if( index >= 0 ) --minArgs;
if( argc<minArgs ){
return Th_WrongNumArgs2(interp,
argv[0], argl[0],
"StmtHandle Index Format");
}
if(!pStmt){
queryStmtIndexArgs(interp, argc, argv, argl, &pStmt, &index);
argPos = 3;
}else if(index<0){
Th_ToInt( interp, argv[1], argl[1], &index );
argPos = 2;
}else{
argPos = 1;
}
if(index < 0){
return TH_ERROR;
}
val = sqlite3_column_text( pStmt, index );
fmt = argv[argPos++];
assert(NULL!=pStmt);
................................................................................
static int queryColTopLevelCmd(
Th_Interp *interp,
void *ctx,
int argc,
const char **argv,
int *argl
){
int colIndex = -1;
static Th_SubCommand aSub[] = {
{"count", queryColCountCmd},
{"is_null", queryColIsNullCmd},
{"isnull", queryColIsNullCmd},
{"name", queryColNameCmd},
{"double", queryColDoubleCmd},
{"int", queryColIntCmd},
{"string", queryColStringCmd},
{"time", queryColTimeCmd},
{"type", queryColTypeCmd},
{0, 0}
};
static Th_SubCommand aSubWithIndex[] = {
{"is_null", queryColIsNullCmd},
{"isnull", queryColIsNullCmd},
{"name", queryColNameCmd},
{"double", queryColDoubleCmd},
{"int", queryColIntCmd},
{"string", queryColStringCmd},
{"time", queryColTimeCmd},
{"type", queryColTypeCmd},
{0, 0}
};
Th_Sqlite * sq = Th_sqlite_manager(interp);
assert(NULL != sq);
if( 1 == argc ){
Th_WrongNumArgs2( interp, argv[0], argl[0],
"subcommand");
return TH_ERROR;
}else if( 0 == Th_TryInt(interp,argv[1], argl[1], &colIndex) ){
if(colIndex <0){
Th_ErrorMessage( interp, "Invalid column index.", NULL, 0);
return TH_ERROR;
}
++argv;
++argl;
--argc;
}
sq->colCmdIndex = colIndex;
/* TODO: accept the index as the first param after "col", and make
subcommands aware of sq->colCmdIndex.
*/
Th_CallSubCommand2( interp, ctx, argc, argv, argl,
(colIndex<0) ? aSub : aSubWithIndex );
}
static int queryTopLevelCmd(
Th_Interp *interp,
void *ctx,
int argc,
|
Changes to www/th1_query.wiki.
94
95
96
97
98
99
100
101
102
103
104
105
106
107
...
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
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.
<nowiki><pre>
set stmt [query prepare "SELECT ... WHERE user=?"]
query $stmt bind int 1 drh
if {0 < [query $stmt step]} {
................................................................................
<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 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:
|
>
>
>
>
>
>
|
>
>
>
>
>
>
>
|
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
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> Note that all of those optionally accept the statement handle directly after the "query" command (before the "col" subcommand). e.g. <tt>query bind null $stmt 1</tt> and <tt>query $stmt bind null 1</tt> are equivalent. Achtung: the bind API uses 1-based indexes, just like SQL does. <nowiki><pre> set stmt [query prepare "SELECT ... WHERE user=?"] query $stmt bind int 1 drh if {0 < [query $stmt step]} { ................................................................................ <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 isnull 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. Note that all of those optionally accept the statement handle directly after the "query" command (before the "col" subcommand). e.g. <tt>query $stmt col count</tt> and <tt>query col count $stmt</tt> are equivalent. They also accept the column index either before or after the type name, e.g. <tt>query $stmt col 1 string</tt> and <tt>query $stmt col string 1</tt> are equivalent. 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: |