/ Check-in [c62e3582]
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. Trouble is, this makes sampling for stat1 data much slower.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint-failure
Files: files | file ages | folders
SHA3-256: c62e358243d96cb38a7ce2aa679fc640b62bf46080eab4bd5fc2acf5997d6cd5
User & Date: dan 2017-04-20 16:08:33
Context
2017-04-20
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:08
Avoid creating a temp table in the user database in the sqlite3_expert code. Trouble is, this makes sampling for stat1 data much slower. check-in: c62e3582 user: dan tags: schemalint-failure
09:54
Add an option to generate stat1 data based on a subset of the user database table contents to sqlite3_expert. check-in: c69c3e21 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  +
          353  +  int iTarget;                    /* Target as a percentage */
          354  +  double target;                  /* Target nRet/nRow value */
          355  +  double nRow;                    /* Rows seen */
          356  +  double nRet;                    /* Rows returned */
          357  +};
   294    358   
   295    359   static char *expertDequote(const char *zIn){
   296    360     int n = strlen(zIn);
   297    361     char *zRet = sqlite3_malloc(n);
   298    362   
   299    363     assert( zIn[0]=='\'' );
   300    364     assert( zIn[n-1]=='\'' );
................................................................................
   385    449       pScan->pTab = p->pTab;
   386    450       pScan->pNextScan = p->pExpert->pScan;
   387    451       p->pExpert->pScan = pScan;
   388    452   
   389    453       /* Add the constraints to the IdxScan object */
   390    454       for(i=0; i<pIdxInfo->nConstraint; i++){
   391    455         struct sqlite3_index_constraint *pCons = &pIdxInfo->aConstraint[i];
   392         -      if( pCons->usable && pCons->iColumn>=0 && (pCons->op & opmask) ){
          456  +      if( pCons->usable 
          457  +       && pCons->iColumn>=0 
          458  +       && p->pTab->aCol[pCons->iColumn].iPk==0
          459  +       && (pCons->op & opmask) 
          460  +      ){
   393    461           IdxConstraint *pNew;
   394    462           const char *zColl = sqlite3_vtab_collation(dbv, i);
   395    463           pNew = idxNewConstraint(&rc, zColl);
   396    464           if( pNew ){
   397    465             pNew->iCol = pCons->iColumn;
   398    466             if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){
   399    467               pNew->pNext = pScan->pEq;
................................................................................
   434    502     sqlite3_vtab *pVtab, 
   435    503     int nData, 
   436    504     sqlite3_value **azData, 
   437    505     sqlite_int64 *pRowid
   438    506   ){
   439    507     return SQLITE_OK;
   440    508   }
          509  +
          510  +/* 
          511  +** Virtual table module xOpen method.
          512  +*/
          513  +static int expertOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          514  +  int rc = SQLITE_OK;
          515  +  ExpertCsr *pCsr;
          516  +  pCsr = idxMalloc(&rc, sizeof(ExpertCsr));
          517  +  *ppCursor = (sqlite3_vtab_cursor*)pCsr;
          518  +  return rc;
          519  +}
          520  +
          521  +/* 
          522  +** Virtual table module xClose method.
          523  +*/
          524  +static int expertClose(sqlite3_vtab_cursor *cur){
          525  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          526  +  sqlite3_finalize(pCsr->pData);
          527  +  sqlite3_free(pCsr);
          528  +  return SQLITE_OK;
          529  +}
          530  +
          531  +/*
          532  +** Virtual table module xEof method.
          533  +**
          534  +** Return non-zero if the cursor does not currently point to a valid 
          535  +** record (i.e if the scan has finished), or zero otherwise.
          536  +*/
          537  +static int expertEof(sqlite3_vtab_cursor *cur){
          538  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          539  +  return pCsr->pData==0;
          540  +}
          541  +
          542  +/* 
          543  +** Virtual table module xNext method.
          544  +*/
          545  +static int expertNext(sqlite3_vtab_cursor *cur){
          546  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          547  +  int rc = SQLITE_OK;
          548  +  int bRet;
          549  +  assert( pCsr->pData );
          550  +
          551  +  do {
          552  +    rc = sqlite3_step(pCsr->pData);
          553  +    if( rc!=SQLITE_ROW ){
          554  +      rc = sqlite3_finalize(pCsr->pData);
          555  +      pCsr->pData = 0;
          556  +      bRet = 1;
          557  +    }else{
          558  +      rc = SQLITE_OK;
          559  +      bRet = (pCsr->nRow==0.0 || pCsr->nRow/pCsr->nRet < pCsr->target);
          560  +      if( bRet==0 ){
          561  +        unsigned short rnd;
          562  +        sqlite3_randomness(2, (void*)&rnd);
          563  +        bRet = ((int)rnd % 100) <= pCsr->iTarget;
          564  +      }
          565  +    }
          566  +    pCsr->nRow += 1.0;
          567  +  }while( bRet==0 );
          568  +
          569  +  pCsr->nRet += 1.0;
          570  +  return rc;
          571  +}
          572  +
          573  +/* 
          574  +** Virtual table module xRowid method.
          575  +*/
          576  +static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          577  +  *pRowid = 0;
          578  +  return SQLITE_OK;
          579  +}
          580  +
          581  +/* 
          582  +** Virtual table module xColumn method.
          583  +*/
          584  +static int expertColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          585  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          586  +  sqlite3_value *pVal;
          587  +  pVal = sqlite3_column_value(pCsr->pData, i);
          588  +  if( pVal ){
          589  +    sqlite3_result_value(ctx, pVal);
          590  +  }
          591  +  return SQLITE_OK;
          592  +}
          593  +
          594  +/* 
          595  +** Virtual table module xFilter method.
          596  +*/
          597  +static int expertFilter(
          598  +  sqlite3_vtab_cursor *cur, 
          599  +  int idxNum, const char *idxStr,
          600  +  int argc, sqlite3_value **argv
          601  +){
          602  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          603  +  ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab);
          604  +  sqlite3expert *pExpert = pVtab->pExpert;
          605  +  int rc;
          606  +
          607  +  rc = sqlite3_finalize(pCsr->pData);
          608  +  pCsr->pData = 0;
          609  +  pCsr->nRow = 0.0;
          610  +  pCsr->nRet = 0.0;
          611  +  pCsr->iTarget = pExpert->iSample;
          612  +  pCsr->target = (double)pExpert->iSample / 100.0;
          613  +
          614  +  if( rc==SQLITE_OK ){
          615  +    rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg,
          616  +        "SELECT * FROM main.%Q", pVtab->pTab->zName
          617  +    );
          618  +  }
          619  +
          620  +  if( rc==SQLITE_OK ){
          621  +    rc = expertNext(cur);
          622  +  }
          623  +  return rc;
          624  +}
   441    625   
   442    626   static int idxRegisterVtab(sqlite3expert *p){
   443    627     static sqlite3_module expertModule = {
   444    628       2,                            /* iVersion */
   445    629       expertConnect,                /* xCreate - create a table */
   446    630       expertConnect,                /* xConnect - connect to an existing table */
   447    631       expertBestIndex,              /* xBestIndex - Determine search strategy */
   448    632       expertDisconnect,             /* xDisconnect - Disconnect from a table */
   449    633       expertDisconnect,             /* xDestroy - Drop a table */
   450         -    0,                            /* xOpen - open a cursor */
   451         -    0,                            /* xClose - close a cursor */
   452         -    0,                            /* xFilter - configure scan constraints */
   453         -    0,                            /* xNext - advance a cursor */
   454         -    0,                            /* xEof */
   455         -    0,                            /* xColumn - read data */
   456         -    0,                            /* xRowid - read data */
          634  +    expertOpen,                   /* xOpen - open a cursor */
          635  +    expertClose,                  /* xClose - close a cursor */
          636  +    expertFilter,                 /* xFilter - configure scan constraints */
          637  +    expertNext,                   /* xNext - advance a cursor */
          638  +    expertEof,                    /* xEof */
          639  +    expertColumn,                 /* xColumn - read data */
          640  +    expertRowid,                  /* xRowid - read data */
   457    641       expertUpdate,                 /* xUpdate - write data */
   458    642       0,                            /* xBegin - begin transaction */
   459    643       0,                            /* xSync - sync transaction */
   460    644       0,                            /* xCommit - commit transaction */
   461    645       0,                            /* xRollback - rollback transaction */
   462    646       0,                            /* xFindFunction - function overloading */
   463    647       0,                            /* xRename - rename the table */
................................................................................
   467    651     };
   468    652   
   469    653     return sqlite3_create_module(p->dbv, "expert", &expertModule, (void*)p);
   470    654   }
   471    655   /*
   472    656   ** End of virtual table implementation.
   473    657   *************************************************************************/
   474         -
   475         -/*
   476         -** An error associated with database handle db has just occurred. Pass
   477         -** the error message to callback function xOut.
   478         -*/
   479         -static void idxDatabaseError(
   480         -  sqlite3 *db,                    /* Database handle */
   481         -  char **pzErrmsg                 /* Write error here */
   482         -){
   483         -  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
   484         -}
   485         -
   486         -/*
   487         -** Prepare an SQL statement.
   488         -*/
   489         -static int idxPrepareStmt(
   490         -  sqlite3 *db,                    /* Database handle to compile against */
   491         -  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
   492         -  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
   493         -  const char *zSql                /* SQL statement to compile */
   494         -){
   495         -  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
   496         -  if( rc!=SQLITE_OK ){
   497         -    *ppStmt = 0;
   498         -    idxDatabaseError(db, pzErrmsg);
   499         -  }
   500         -  return rc;
   501         -}
   502         -
   503         -/*
   504         -** Prepare an SQL statement using the results of a printf() formatting.
   505         -*/
   506         -static int idxPrintfPrepareStmt(
   507         -  sqlite3 *db,                    /* Database handle to compile against */
   508         -  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
   509         -  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
   510         -  const char *zFmt,               /* printf() format of SQL statement */
   511         -  ...                             /* Trailing printf() arguments */
   512         -){
   513         -  va_list ap;
   514         -  int rc;
   515         -  char *zSql;
   516         -  va_start(ap, zFmt);
   517         -  zSql = sqlite3_vmprintf(zFmt, ap);
   518         -  if( zSql==0 ){
   519         -    rc = SQLITE_NOMEM;
   520         -  }else{
   521         -    rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql);
   522         -    sqlite3_free(zSql);
   523         -  }
   524         -  va_end(ap);
   525         -  return rc;
   526         -}
   527         -
   528    658   /*
   529    659   ** Finalize SQL statement pStmt. If (*pRc) is SQLITE_OK when this function
   530    660   ** is called, set it to the return value of sqlite3_finalize() before
   531    661   ** returning. Otherwise, discard the sqlite3_finalize() return value.
   532    662   */
   533    663   static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){
   534    664     int rc = sqlite3_finalize(pStmt);
................................................................................
   553    683   ){
   554    684     sqlite3_stmt *p1 = 0;
   555    685     int nCol = 0;
   556    686     int nTab = strlen(zTab);
   557    687     int nByte = sizeof(IdxTable) + nTab + 1;
   558    688     IdxTable *pNew = 0;
   559    689     int rc, rc2;
   560         -  char *pCsr;
          690  +  char *pCsr = 0;
   561    691   
   562    692     rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTab);
   563    693     while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
   564    694       const char *zCol = (const char*)sqlite3_column_text(p1, 1);
   565    695       nByte += 1 + strlen(zCol);
   566    696       rc = sqlite3_table_column_metadata(
   567    697           db, "main", zTab, zCol, 0, &zCol, 0, 0, 0
................................................................................
  1421   1551       }
  1422   1552     }
  1423   1553     sqlite3_free(zCols);
  1424   1554     sqlite3_free(zOrder);
  1425   1555   
  1426   1556     /* Formulate the query text */
  1427   1557     if( rc==SQLITE_OK ){
  1428         -    rc = idxPrepareStmt(p->db, &pQuery, pzErr, zQuery);
         1558  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
         1559  +    rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery);
  1429   1560     }
  1430   1561     sqlite3_free(zQuery);
  1431   1562   
  1432   1563     if( rc==SQLITE_OK ){
  1433   1564       aStat = (int*)idxMalloc(&rc, sizeof(int)*(nCol+1));
  1434   1565     }
  1435   1566     if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
................................................................................
  1477   1608     return rc;
  1478   1609   }
  1479   1610   
  1480   1611   static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){
  1481   1612     int rc;
  1482   1613     char *zSql;
  1483   1614   
  1484         -  rc = sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
         1615  +  rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
  1485   1616     if( rc!=SQLITE_OK ) return rc;
  1486   1617   
  1487   1618     zSql = sqlite3_mprintf(
  1488         -      "CREATE TABLE temp." UNIQUE_TABLE_NAME 
  1489         -      "  AS SELECT * FROM %Q WHERE sample()"
  1490         -      , zTab
         1619  +      "CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab
  1491   1620     );
  1492   1621     if( zSql==0 ) return SQLITE_NOMEM;
  1493         -  rc = sqlite3_exec(p->db, zSql, 0, 0, 0);
         1622  +  rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0);
  1494   1623     sqlite3_free(zSql);
  1495   1624   
  1496   1625     return rc;
  1497   1626   }
  1498   1627   
  1499   1628   /*
  1500   1629   ** This function is called as part of sqlite3_expert_analyze(). Candidate
................................................................................
  1533   1662   
  1534   1663     if( rc==SQLITE_OK ){
  1535   1664       int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax);
  1536   1665       pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte);
  1537   1666     }
  1538   1667   
  1539   1668     if( rc==SQLITE_OK ){
         1669  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
  1540   1670       rc = sqlite3_create_function(
  1541         -        p->db, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
         1671  +        dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
  1542   1672       );
  1543   1673     }
  1544   1674     if( rc==SQLITE_OK ){
  1545   1675       rc = sqlite3_create_function(
  1546   1676           p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
  1547   1677       );
  1548   1678     }