/ Check-in [4a6c416f]
Login

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/unionvtab.c.

    83     83   
    84     84   /*
    85     85   ** Virtual table  type for union vtab.
    86     86   */
    87     87   struct UnionTab {
    88     88     sqlite3_vtab base;              /* Base class - must be first */
    89     89     sqlite3 *db;                    /* Database handle */
           90  +  int iPK;                        /* INTEGER PRIMARY KEY column, or -1 */
    90     91     int nSrc;                       /* Number of elements in the aSrc[] array */
    91     92     UnionSrc *aSrc;                 /* Array of source tables, sorted by rowid */
    92     93   };
    93     94   
    94     95   /*
    95     96   ** Virtual table cursor type for union vtab.
    96     97   */
................................................................................
   454    455       }
   455    456   
   456    457       /* Compose a CREATE TABLE statement and pass it to declare_vtab() */
   457    458       if( rc==SQLITE_OK ){
   458    459         zSql = sqlite3_mprintf("SELECT "
   459    460             "'CREATE TABLE xyz('"
   460    461             "    || group_concat(quote(name) || ' ' || type, ', ')"
   461         -          "    || ')'"
          462  +          "    || ')',"
          463  +          "max((cid+1) * (type='INTEGER' COLLATE nocase AND pk=1))-1 "
   462    464             "FROM pragma_table_info(%Q, ?)", 
   463         -          pTab->aSrc[0].zTab
          465  +          pTab->aSrc[0].zTab, pTab->aSrc[0].zDb
   464    466         );
   465    467         if( zSql==0 ) rc = SQLITE_NOMEM;
   466    468       }
   467    469       pStmt = unionPrepare(&rc, db, zSql, pzErr);
   468         -    if( rc==SQLITE_OK ){
   469         -      sqlite3_bind_text(pStmt, 1, pTab->aSrc[0].zDb, -1, SQLITE_STATIC);
   470         -      if( SQLITE_ROW==sqlite3_step(pStmt) ){
   471         -        const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0);
   472         -        rc = sqlite3_declare_vtab(db, zDecl);
   473         -      }
          470  +    if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
          471  +      const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0);
          472  +      rc = sqlite3_declare_vtab(db, zDecl);
          473  +      pTab->iPK = sqlite3_column_int(pStmt, 1);
   474    474       }
   475    475   
   476    476       unionFinalize(&rc, pStmt);
   477    477       sqlite3_free(zSql);
   478    478     }
   479    479   
   480    480     if( rc!=SQLITE_OK ){
................................................................................
   682    682   ** is passed as either the first or second argument to xFilter, depending
   683    683   ** on whether or not there is also a LT|LE constraint.
   684    684   */
   685    685   static int unionBestIndex(
   686    686     sqlite3_vtab *tab,
   687    687     sqlite3_index_info *pIdxInfo
   688    688   ){
          689  +  UnionTab *pTab = (UnionTab*)tab;
   689    690     int iEq = -1;
   690    691     int iLt = -1;
   691    692     int iGt = -1;
   692    693     int i;
   693    694   
   694    695     for(i=0; i<pIdxInfo->nConstraint; i++){
   695    696       struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
   696         -    if( p->usable && p->iColumn<0 ){
          697  +    if( p->usable && (p->iColumn<0 || p->iColumn==pTab->iPK) ){
   697    698         switch( p->op ){
   698    699           case SQLITE_INDEX_CONSTRAINT_EQ:
   699    700             if( iEq<0 ) iEq = i;
   700    701             break;
   701    702           case SQLITE_INDEX_CONSTRAINT_LE:
   702    703           case SQLITE_INDEX_CONSTRAINT_LT:
   703    704             if( iLt<0 ) iLt = i;

Changes to test/unionvtab.test.

   246    246   do_execsql_test 3.8.3 { SELECT count(*) FROM uu WHERE rowid <= $S } {0}
   247    247   do_execsql_test 3.8.4 { SELECT count(*) FROM uu WHERE rowid <  $S } {0}
   248    248   
   249    249   do_execsql_test 3.9.1 { SELECT count(*) FROM uu WHERE rowid >= $L } {0}
   250    250   do_execsql_test 3.9.2 { SELECT count(*) FROM uu WHERE rowid >  $L } {0}
   251    251   do_execsql_test 3.9.3 { SELECT count(*) FROM uu WHERE rowid <= $L } {300}
   252    252   do_execsql_test 3.9.4 { SELECT count(*) FROM uu WHERE rowid <  $L } {300}
          253  +
          254  +do_execsql_test 3.10.1 { SELECT count(*) FROM uu WHERE a < 25 } {24}
          255  +do_execsql_test 3.10.2 { SELECT count(*) FROM uu WHERE a < 26 } {100}
          256  +do_execsql_test 3.10.3 { SELECT count(*) FROM uu WHERE a < 27 } {126}
          257  +do_execsql_test 3.10.4 { SELECT count(*) FROM uu WHERE a < 73 } {172}
          258  +do_execsql_test 3.10.5 { SELECT count(*) FROM uu WHERE a < 74 } {173}
          259  +do_execsql_test 3.10.6 { SELECT count(*) FROM uu WHERE a < 75 } {200}
          260  +do_execsql_test 3.10.7 { SELECT count(*) FROM uu WHERE a < 76 } {275}
          261  +do_execsql_test 3.10.8 { SELECT count(*) FROM uu WHERE a < 99 } {298}
          262  +do_execsql_test 3.10.9 { SELECT count(*) FROM uu WHERE a < 100 } {299}
          263  +do_execsql_test 3.10.10 { SELECT count(*) FROM uu WHERE a < 101 } {300}
          264  +
   253    265   
   254    266   #-------------------------------------------------------------------------
   255    267   #
   256    268   do_execsql_test 4.0 {
   257    269     CREATE TABLE s1(k INTEGER PRIMARY KEY, v);
   258    270     INSERT INTO s1 VALUES($S, 'one');
   259    271     INSERT INTO s1 VALUES($S+1, 'two');