/ Check-in [4e366996]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Avoid creating a temp table in the user database in the sqlite3_expert code.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: 4e366996434e63f06cc451d2011f1f1464360f03430b8260e48f78a612b4e9d6
User & Date: dan 2017-04-20 17:03:32
Context
2017-04-20
17:35
Merge latest trunk changes into this branch. check-in: b1533bc4 user: dan tags: schemalint
17:03
Avoid creating a temp table in the user database in the sqlite3_expert code. check-in: 4e366996 user: dan tags: schemalint
16:43
Speed this branch up a bit by filtering before the virtual table layer when sampling user data. Closed-Leaf check-in: 8e57c313 user: dan tags: schemalint-failure
16:18
Avoid adding INTEGER PRIMARY KEY columns to candidate indexes. check-in: 4577fea5 user: dan tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/expert/sqlite3expert.c.

   277    277     if( pNew ){
   278    278       pNew->zColl = (char*)&pNew[1];
   279    279       memcpy(pNew->zColl, zColl, nColl+1);
   280    280     }
   281    281     return pNew;
   282    282   }
   283    283   
          284  +/*
          285  +** An error associated with database handle db has just occurred. Pass
          286  +** the error message to callback function xOut.
          287  +*/
          288  +static void idxDatabaseError(
          289  +  sqlite3 *db,                    /* Database handle */
          290  +  char **pzErrmsg                 /* Write error here */
          291  +){
          292  +  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          293  +}
          294  +
          295  +/*
          296  +** Prepare an SQL statement.
          297  +*/
          298  +static int idxPrepareStmt(
          299  +  sqlite3 *db,                    /* Database handle to compile against */
          300  +  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
          301  +  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
          302  +  const char *zSql                /* SQL statement to compile */
          303  +){
          304  +  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
          305  +  if( rc!=SQLITE_OK ){
          306  +    *ppStmt = 0;
          307  +    idxDatabaseError(db, pzErrmsg);
          308  +  }
          309  +  return rc;
          310  +}
          311  +
          312  +/*
          313  +** Prepare an SQL statement using the results of a printf() formatting.
          314  +*/
          315  +static int idxPrintfPrepareStmt(
          316  +  sqlite3 *db,                    /* Database handle to compile against */
          317  +  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
          318  +  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
          319  +  const char *zFmt,               /* printf() format of SQL statement */
          320  +  ...                             /* Trailing printf() arguments */
          321  +){
          322  +  va_list ap;
          323  +  int rc;
          324  +  char *zSql;
          325  +  va_start(ap, zFmt);
          326  +  zSql = sqlite3_vmprintf(zFmt, ap);
          327  +  if( zSql==0 ){
          328  +    rc = SQLITE_NOMEM;
          329  +  }else{
          330  +    rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql);
          331  +    sqlite3_free(zSql);
          332  +  }
          333  +  va_end(ap);
          334  +  return rc;
          335  +}
          336  +
   284    337   
   285    338   /*************************************************************************
   286    339   ** Beginning of virtual table implementation.
   287    340   */
   288    341   typedef struct ExpertVtab ExpertVtab;
   289    342   struct ExpertVtab {
   290    343     sqlite3_vtab base;
   291    344     IdxTable *pTab;
   292    345     sqlite3expert *pExpert;
   293    346   };
          347  +
          348  +typedef struct ExpertCsr ExpertCsr;
          349  +struct ExpertCsr {
          350  +  sqlite3_vtab_cursor base;
          351  +  sqlite3_stmt *pData;
          352  +};
   294    353   
   295    354   static char *expertDequote(const char *zIn){
   296    355     int n = strlen(zIn);
   297    356     char *zRet = sqlite3_malloc(n);
   298    357   
   299    358     assert( zIn[0]=='\'' );
   300    359     assert( zIn[n-1]=='\'' );
................................................................................
   438    497     sqlite3_vtab *pVtab, 
   439    498     int nData, 
   440    499     sqlite3_value **azData, 
   441    500     sqlite_int64 *pRowid
   442    501   ){
   443    502     return SQLITE_OK;
   444    503   }
          504  +
          505  +/* 
          506  +** Virtual table module xOpen method.
          507  +*/
          508  +static int expertOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          509  +  int rc = SQLITE_OK;
          510  +  ExpertCsr *pCsr;
          511  +  pCsr = idxMalloc(&rc, sizeof(ExpertCsr));
          512  +  *ppCursor = (sqlite3_vtab_cursor*)pCsr;
          513  +  return rc;
          514  +}
          515  +
          516  +/* 
          517  +** Virtual table module xClose method.
          518  +*/
          519  +static int expertClose(sqlite3_vtab_cursor *cur){
          520  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          521  +  sqlite3_finalize(pCsr->pData);
          522  +  sqlite3_free(pCsr);
          523  +  return SQLITE_OK;
          524  +}
          525  +
          526  +/*
          527  +** Virtual table module xEof method.
          528  +**
          529  +** Return non-zero if the cursor does not currently point to a valid 
          530  +** record (i.e if the scan has finished), or zero otherwise.
          531  +*/
          532  +static int expertEof(sqlite3_vtab_cursor *cur){
          533  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          534  +  return pCsr->pData==0;
          535  +}
          536  +
          537  +/* 
          538  +** Virtual table module xNext method.
          539  +*/
          540  +static int expertNext(sqlite3_vtab_cursor *cur){
          541  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          542  +  int rc = SQLITE_OK;
          543  +
          544  +  assert( pCsr->pData );
          545  +  rc = sqlite3_step(pCsr->pData);
          546  +  if( rc!=SQLITE_ROW ){
          547  +    rc = sqlite3_finalize(pCsr->pData);
          548  +    pCsr->pData = 0;
          549  +  }else{
          550  +    rc = SQLITE_OK;
          551  +  }
          552  +
          553  +  return rc;
          554  +}
          555  +
          556  +/* 
          557  +** Virtual table module xRowid method.
          558  +*/
          559  +static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          560  +  *pRowid = 0;
          561  +  return SQLITE_OK;
          562  +}
          563  +
          564  +/* 
          565  +** Virtual table module xColumn method.
          566  +*/
          567  +static int expertColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          568  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          569  +  sqlite3_value *pVal;
          570  +  pVal = sqlite3_column_value(pCsr->pData, i);
          571  +  if( pVal ){
          572  +    sqlite3_result_value(ctx, pVal);
          573  +  }
          574  +  return SQLITE_OK;
          575  +}
          576  +
          577  +/* 
          578  +** Virtual table module xFilter method.
          579  +*/
          580  +static int expertFilter(
          581  +  sqlite3_vtab_cursor *cur, 
          582  +  int idxNum, const char *idxStr,
          583  +  int argc, sqlite3_value **argv
          584  +){
          585  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          586  +  ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab);
          587  +  sqlite3expert *pExpert = pVtab->pExpert;
          588  +  int rc;
          589  +
          590  +  rc = sqlite3_finalize(pCsr->pData);
          591  +  pCsr->pData = 0;
          592  +  if( rc==SQLITE_OK ){
          593  +    rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg,
          594  +        "SELECT * FROM main.%Q WHERE sample()", pVtab->pTab->zName
          595  +    );
          596  +  }
          597  +
          598  +  if( rc==SQLITE_OK ){
          599  +    rc = expertNext(cur);
          600  +  }
          601  +  return rc;
          602  +}
   445    603   
   446    604   static int idxRegisterVtab(sqlite3expert *p){
   447    605     static sqlite3_module expertModule = {
   448    606       2,                            /* iVersion */
   449    607       expertConnect,                /* xCreate - create a table */
   450    608       expertConnect,                /* xConnect - connect to an existing table */
   451    609       expertBestIndex,              /* xBestIndex - Determine search strategy */
   452    610       expertDisconnect,             /* xDisconnect - Disconnect from a table */
   453    611       expertDisconnect,             /* xDestroy - Drop a table */
   454         -    0,                            /* xOpen - open a cursor */
   455         -    0,                            /* xClose - close a cursor */
   456         -    0,                            /* xFilter - configure scan constraints */
   457         -    0,                            /* xNext - advance a cursor */
   458         -    0,                            /* xEof */
   459         -    0,                            /* xColumn - read data */
   460         -    0,                            /* xRowid - read data */
          612  +    expertOpen,                   /* xOpen - open a cursor */
          613  +    expertClose,                  /* xClose - close a cursor */
          614  +    expertFilter,                 /* xFilter - configure scan constraints */
          615  +    expertNext,                   /* xNext - advance a cursor */
          616  +    expertEof,                    /* xEof */
          617  +    expertColumn,                 /* xColumn - read data */
          618  +    expertRowid,                  /* xRowid - read data */
   461    619       expertUpdate,                 /* xUpdate - write data */
   462    620       0,                            /* xBegin - begin transaction */
   463    621       0,                            /* xSync - sync transaction */
   464    622       0,                            /* xCommit - commit transaction */
   465    623       0,                            /* xRollback - rollback transaction */
   466    624       0,                            /* xFindFunction - function overloading */
   467    625       0,                            /* xRename - rename the table */
................................................................................
   471    629     };
   472    630   
   473    631     return sqlite3_create_module(p->dbv, "expert", &expertModule, (void*)p);
   474    632   }
   475    633   /*
   476    634   ** End of virtual table implementation.
   477    635   *************************************************************************/
   478         -
   479         -/*
   480         -** An error associated with database handle db has just occurred. Pass
   481         -** the error message to callback function xOut.
   482         -*/
   483         -static void idxDatabaseError(
   484         -  sqlite3 *db,                    /* Database handle */
   485         -  char **pzErrmsg                 /* Write error here */
   486         -){
   487         -  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
   488         -}
   489         -
   490         -/*
   491         -** Prepare an SQL statement.
   492         -*/
   493         -static int idxPrepareStmt(
   494         -  sqlite3 *db,                    /* Database handle to compile against */
   495         -  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
   496         -  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
   497         -  const char *zSql                /* SQL statement to compile */
   498         -){
   499         -  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
   500         -  if( rc!=SQLITE_OK ){
   501         -    *ppStmt = 0;
   502         -    idxDatabaseError(db, pzErrmsg);
   503         -  }
   504         -  return rc;
   505         -}
   506         -
   507         -/*
   508         -** Prepare an SQL statement using the results of a printf() formatting.
   509         -*/
   510         -static int idxPrintfPrepareStmt(
   511         -  sqlite3 *db,                    /* Database handle to compile against */
   512         -  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
   513         -  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
   514         -  const char *zFmt,               /* printf() format of SQL statement */
   515         -  ...                             /* Trailing printf() arguments */
   516         -){
   517         -  va_list ap;
   518         -  int rc;
   519         -  char *zSql;
   520         -  va_start(ap, zFmt);
   521         -  zSql = sqlite3_vmprintf(zFmt, ap);
   522         -  if( zSql==0 ){
   523         -    rc = SQLITE_NOMEM;
   524         -  }else{
   525         -    rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql);
   526         -    sqlite3_free(zSql);
   527         -  }
   528         -  va_end(ap);
   529         -  return rc;
   530         -}
   531         -
   532    636   /*
   533    637   ** Finalize SQL statement pStmt. If (*pRc) is SQLITE_OK when this function
   534    638   ** is called, set it to the return value of sqlite3_finalize() before
   535    639   ** returning. Otherwise, discard the sqlite3_finalize() return value.
   536    640   */
   537    641   static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){
   538    642     int rc = sqlite3_finalize(pStmt);
................................................................................
  1425   1529       }
  1426   1530     }
  1427   1531     sqlite3_free(zCols);
  1428   1532     sqlite3_free(zOrder);
  1429   1533   
  1430   1534     /* Formulate the query text */
  1431   1535     if( rc==SQLITE_OK ){
  1432         -    rc = idxPrepareStmt(p->db, &pQuery, pzErr, zQuery);
         1536  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
         1537  +    rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery);
  1433   1538     }
  1434   1539     sqlite3_free(zQuery);
  1435   1540   
  1436   1541     if( rc==SQLITE_OK ){
  1437   1542       aStat = (int*)idxMalloc(&rc, sizeof(int)*(nCol+1));
  1438   1543     }
  1439   1544     if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
................................................................................
  1481   1586     return rc;
  1482   1587   }
  1483   1588   
  1484   1589   static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){
  1485   1590     int rc;
  1486   1591     char *zSql;
  1487   1592   
  1488         -  rc = sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
         1593  +  rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
  1489   1594     if( rc!=SQLITE_OK ) return rc;
  1490   1595   
  1491   1596     zSql = sqlite3_mprintf(
  1492         -      "CREATE TABLE temp." UNIQUE_TABLE_NAME 
  1493         -      "  AS SELECT * FROM %Q WHERE sample()"
  1494         -      , zTab
         1597  +      "CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab
  1495   1598     );
  1496   1599     if( zSql==0 ) return SQLITE_NOMEM;
  1497         -  rc = sqlite3_exec(p->db, zSql, 0, 0, 0);
         1600  +  rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0);
  1498   1601     sqlite3_free(zSql);
  1499   1602   
  1500   1603     return rc;
  1501   1604   }
  1502   1605   
  1503   1606   /*
  1504   1607   ** This function is called as part of sqlite3_expert_analyze(). Candidate
................................................................................
  1537   1640   
  1538   1641     if( rc==SQLITE_OK ){
  1539   1642       int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax);
  1540   1643       pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte);
  1541   1644     }
  1542   1645   
  1543   1646     if( rc==SQLITE_OK ){
         1647  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
  1544   1648       rc = sqlite3_create_function(
  1545         -        p->db, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
         1649  +        dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
  1546   1650       );
  1547   1651     }
  1548   1652     if( rc==SQLITE_OK ){
  1549   1653       rc = sqlite3_create_function(
  1550   1654           p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
  1551   1655       );
  1552   1656     }
................................................................................
  1573   1677         samplectx.nRet = 0.0;
  1574   1678         rc = idxBuildSampleTable(p, zTab);
  1575   1679         if( rc!=SQLITE_OK ) break;
  1576   1680       }
  1577   1681       rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr);
  1578   1682       iPrev = iRowid;
  1579   1683     }
  1580         -  if( p->iSample<100 ){
  1581         -    rc = sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME,
  1582         -        0,0,0
         1684  +  if( rc==SQLITE_OK && p->iSample<100 ){
         1685  +    rc = sqlite3_exec(p->dbv, 
         1686  +        "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0
  1583   1687       );
  1584   1688     }
  1585   1689   
  1586   1690     idxFinalize(&rc, pAllIndex);
  1587   1691     idxFinalize(&rc, pIndexXInfo);
  1588   1692     idxFinalize(&rc, pWrite);
  1589   1693