/ Check-in [4a6c416f]
Login
Home Timeline Branches Tags Tickets Wiki More...

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: 4a6c416fa025a34116ea30923a673cbb108b251b2676734ec8f603600e38e50e
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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');

This page was generated in about 0.007s by Fossil 2.10 [776ce9e645] 2019-07-18 17:45:11