Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Have unionvtab support constraints on the column that corresponds to the INTEGER PRIMARY KEY field of the underlying source tables in the same way as rowid. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
4a6c416fa025a34116ea30923a673cbb |
| User & Date: | dan 2017-07-18 19:51:11 |
Context
|
2017-07-18
| ||
| 20:03 | Improve the readability of the unionvtab code. (check-in: 5bcf0f86 user: dan tags: trunk) | |
| 19:51 | Have unionvtab support constraints on the column that corresponds to the INTEGER PRIMARY KEY field of the underlying source tables in the same way as rowid. (check-in: 4a6c416f user: dan tags: trunk) | |
| 18:50 | Add test cases to unionvtab.test. (check-in: 6c9128e1 user: dan tags: trunk) | |
Changes
Changes to ext/misc/unionvtab.c.
83 84 85 86 87 88 89 90 91 92 93 94 95 96 ... 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 ... 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 |
/*
** Virtual table type for union vtab.
*/
struct UnionTab {
sqlite3_vtab base; /* Base class - must be first */
sqlite3 *db; /* Database handle */
int nSrc; /* Number of elements in the aSrc[] array */
UnionSrc *aSrc; /* Array of source tables, sorted by rowid */
};
/*
** Virtual table cursor type for union vtab.
*/
................................................................................
}
/* Compose a CREATE TABLE statement and pass it to declare_vtab() */
if( rc==SQLITE_OK ){
zSql = sqlite3_mprintf("SELECT "
"'CREATE TABLE xyz('"
" || group_concat(quote(name) || ' ' || type, ', ')"
" || ')'"
"FROM pragma_table_info(%Q, ?)",
pTab->aSrc[0].zTab
);
if( zSql==0 ) rc = SQLITE_NOMEM;
}
pStmt = unionPrepare(&rc, db, zSql, pzErr);
if( rc==SQLITE_OK ){
sqlite3_bind_text(pStmt, 1, pTab->aSrc[0].zDb, -1, SQLITE_STATIC);
if( SQLITE_ROW==sqlite3_step(pStmt) ){
const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0);
rc = sqlite3_declare_vtab(db, zDecl);
}
}
unionFinalize(&rc, pStmt);
sqlite3_free(zSql);
}
if( rc!=SQLITE_OK ){
................................................................................
** is passed as either the first or second argument to xFilter, depending
** on whether or not there is also a LT|LE constraint.
*/
static int unionBestIndex(
sqlite3_vtab *tab,
sqlite3_index_info *pIdxInfo
){
int iEq = -1;
int iLt = -1;
int iGt = -1;
int i;
for(i=0; i<pIdxInfo->nConstraint; i++){
struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
if( p->usable && p->iColumn<0 ){
switch( p->op ){
case SQLITE_INDEX_CONSTRAINT_EQ:
if( iEq<0 ) iEq = i;
break;
case SQLITE_INDEX_CONSTRAINT_LE:
case SQLITE_INDEX_CONSTRAINT_LT:
if( iLt<0 ) iLt = i;
|
> | > | < < | | | < > > | |
83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 ... 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 ... 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 |
/*
** Virtual table type for union vtab.
*/
struct UnionTab {
sqlite3_vtab base; /* Base class - must be first */
sqlite3 *db; /* Database handle */
int iPK; /* INTEGER PRIMARY KEY column, or -1 */
int nSrc; /* Number of elements in the aSrc[] array */
UnionSrc *aSrc; /* Array of source tables, sorted by rowid */
};
/*
** Virtual table cursor type for union vtab.
*/
................................................................................
}
/* Compose a CREATE TABLE statement and pass it to declare_vtab() */
if( rc==SQLITE_OK ){
zSql = sqlite3_mprintf("SELECT "
"'CREATE TABLE xyz('"
" || group_concat(quote(name) || ' ' || type, ', ')"
" || ')',"
"max((cid+1) * (type='INTEGER' COLLATE nocase AND pk=1))-1 "
"FROM pragma_table_info(%Q, ?)",
pTab->aSrc[0].zTab, pTab->aSrc[0].zDb
);
if( zSql==0 ) rc = SQLITE_NOMEM;
}
pStmt = unionPrepare(&rc, db, zSql, pzErr);
if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0);
rc = sqlite3_declare_vtab(db, zDecl);
pTab->iPK = sqlite3_column_int(pStmt, 1);
}
unionFinalize(&rc, pStmt);
sqlite3_free(zSql);
}
if( rc!=SQLITE_OK ){
................................................................................
** is passed as either the first or second argument to xFilter, depending
** on whether or not there is also a LT|LE constraint.
*/
static int unionBestIndex(
sqlite3_vtab *tab,
sqlite3_index_info *pIdxInfo
){
UnionTab *pTab = (UnionTab*)tab;
int iEq = -1;
int iLt = -1;
int iGt = -1;
int i;
for(i=0; i<pIdxInfo->nConstraint; i++){
struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
if( p->usable && (p->iColumn<0 || p->iColumn==pTab->iPK) ){
switch( p->op ){
case SQLITE_INDEX_CONSTRAINT_EQ:
if( iEq<0 ) iEq = i;
break;
case SQLITE_INDEX_CONSTRAINT_LE:
case SQLITE_INDEX_CONSTRAINT_LT:
if( iLt<0 ) iLt = i;
|
Changes to test/unionvtab.test.
246 247 248 249 250 251 252 253 254 255 256 257 258 259 |
do_execsql_test 3.8.3 { SELECT count(*) FROM uu WHERE rowid <= $S } {0}
do_execsql_test 3.8.4 { SELECT count(*) FROM uu WHERE rowid < $S } {0}
do_execsql_test 3.9.1 { SELECT count(*) FROM uu WHERE rowid >= $L } {0}
do_execsql_test 3.9.2 { SELECT count(*) FROM uu WHERE rowid > $L } {0}
do_execsql_test 3.9.3 { SELECT count(*) FROM uu WHERE rowid <= $L } {300}
do_execsql_test 3.9.4 { SELECT count(*) FROM uu WHERE rowid < $L } {300}
#-------------------------------------------------------------------------
#
do_execsql_test 4.0 {
CREATE TABLE s1(k INTEGER PRIMARY KEY, v);
INSERT INTO s1 VALUES($S, 'one');
INSERT INTO s1 VALUES($S+1, 'two');
|
> > > > > > > > > > > > |
246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 |
do_execsql_test 3.8.3 { SELECT count(*) FROM uu WHERE rowid <= $S } {0}
do_execsql_test 3.8.4 { SELECT count(*) FROM uu WHERE rowid < $S } {0}
do_execsql_test 3.9.1 { SELECT count(*) FROM uu WHERE rowid >= $L } {0}
do_execsql_test 3.9.2 { SELECT count(*) FROM uu WHERE rowid > $L } {0}
do_execsql_test 3.9.3 { SELECT count(*) FROM uu WHERE rowid <= $L } {300}
do_execsql_test 3.9.4 { SELECT count(*) FROM uu WHERE rowid < $L } {300}
do_execsql_test 3.10.1 { SELECT count(*) FROM uu WHERE a < 25 } {24}
do_execsql_test 3.10.2 { SELECT count(*) FROM uu WHERE a < 26 } {100}
do_execsql_test 3.10.3 { SELECT count(*) FROM uu WHERE a < 27 } {126}
do_execsql_test 3.10.4 { SELECT count(*) FROM uu WHERE a < 73 } {172}
do_execsql_test 3.10.5 { SELECT count(*) FROM uu WHERE a < 74 } {173}
do_execsql_test 3.10.6 { SELECT count(*) FROM uu WHERE a < 75 } {200}
do_execsql_test 3.10.7 { SELECT count(*) FROM uu WHERE a < 76 } {275}
do_execsql_test 3.10.8 { SELECT count(*) FROM uu WHERE a < 99 } {298}
do_execsql_test 3.10.9 { SELECT count(*) FROM uu WHERE a < 100 } {299}
do_execsql_test 3.10.10 { SELECT count(*) FROM uu WHERE a < 101 } {300}
#-------------------------------------------------------------------------
#
do_execsql_test 4.0 {
CREATE TABLE s1(k INTEGER PRIMARY KEY, v);
INSERT INTO s1 VALUES($S, 'one');
INSERT INTO s1 VALUES($S+1, 'two');
|