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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
4a6c416fa025a34116ea30923a673cbb |
| User & Date: | dan 2017-07-18 19:51:11.061 |
Context
|
2017-07-18
| ||
| 20:03 | Improve the readability of the unionvtab code. (check-in: 5bcf0f86ea 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: 4a6c416fa0 user: dan tags: trunk) | |
| 18:50 | Add test cases to unionvtab.test. (check-in: 6c9128e1a5 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 |
/*
** 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.
*/
| > | 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
/*
** 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.
*/
|
| ︙ | ︙ | |||
454 455 456 457 458 459 460 |
}
/* 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, ', ')"
| | > | < < | | | < > | 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 |
}
/* 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 ){
|
| ︙ | ︙ | |||
682 683 684 685 686 687 688 689 690 691 692 693 694 695 |
** 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];
| > | | 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 |
** 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');
|
| ︙ | ︙ |