/ Check-in [65444f2e]
Login

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

Overview
Comment:Enhance faststat1.c to deal better with WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | est_count_pragma
Files: files | file ages | folders
SHA1: 65444f2e35cfd51ece1ba6e37b39d181da479137
User & Date: drh 2016-10-25 18:28:29
Context
2016-10-25
19:21
Further refinements to the faststat1.c utility. check-in: b051fd19 user: drh tags: est_count_pragma
18:28
Enhance faststat1.c to deal better with WITHOUT ROWID tables. check-in: 65444f2e user: drh tags: est_count_pragma
17:28
Merge recent trunk changes, and especially the PRAGMA index_info enhancement which is needed on this branch. check-in: c3570e46 user: drh tags: est_count_pragma
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to tool/faststat1.c.

   115    115         n++;
   116    116       }
   117    117       sqlite3_finalize(pStmt);
   118    118     }
   119    119     return n==0 ? 0 : (sqlite3_int64)(sum/n);
   120    120   }
   121    121   
   122         -/*
   123         -** Stat1 for a table.
   124         -*/
   125         -static void analyzeTable(const char *zTab){
   126         -  sqlite3_int64 n = estEntryCount(zTab);
   127         -  sqlite3_stmt *pStmt;
   128         -  if( n==0 ){
   129         -    printf("-- empty table: %s\n", zTab);
   130         -    return;
   131         -  }
   132         -  pStmt = db_prepare(
   133         -     "INSERT INTO temp.est_stat1(tbl,idx,stat)"
   134         -     "VALUES(\"%w\",NULL,'%lld')", zTab, n
   135         -  );
   136         -  sqlite3_step(pStmt);
   137         -  sqlite3_finalize(pStmt);
   138         -}
   139         -
   140    122   /*
   141    123   ** Compare the i-th column of pStmt against pValue.  Return true if they
   142    124   ** are different.
   143    125   */
   144    126   static int columnNotEqual(sqlite3_stmt *pStmt, int i, sqlite3_value *pValue){
   145    127     int n1, n2, n;
   146    128     if( sqlite3_column_type(pStmt,i)!=sqlite3_value_type(pValue) ) return 1;
................................................................................
   173    155         return n1!=n2;
   174    156    
   175    157     }
   176    158     return 1;
   177    159   }
   178    160   
   179    161   /*
   180         -** Stat1 for an index;
          162  +** Stat1 for an index.  Return non-zero if an entry was created.
   181    163   */
   182         -static void analyzeIndex(const char *zTab, const char *zIdx){
          164  +static int analyzeIndex(const char *zTab, const char *zIdx){
   183    165     sqlite3_int64 n = estEntryCount(zIdx);
   184    166     sqlite3_stmt *pStmt;
   185    167     sqlite3_uint64 *aCnt;
   186    168     sqlite3_value **apValue;
   187    169     int nCol = 0;
   188    170     int nByte;
   189    171     int i, j, k;
................................................................................
   190    172     int iLimit;
   191    173     int nRow = 0;
   192    174     char *zRes;
   193    175     int szRes;
   194    176     int rc;
   195    177   
   196    178   # define N_SPAN  5
   197         -  if( n==0 ) return;
          179  +  if( n==0 ) return 0;
   198    180     pStmt = db_prepare("PRAGMA index_xinfo=\"%w\"", zIdx);
   199    181     while( sqlite3_step(pStmt)==SQLITE_ROW ){
   200    182       const char *zColl = (const char*)sqlite3_column_text(pStmt,4);
   201    183       if( sqlite3_stricmp(zColl,"binary")!=0 ){
   202    184         printf("-- cannot analyze index \"%s\" because column \"%s\" uses"
   203    185                " collating sequence \"%s\".\n",
   204    186                zIdx, sqlite3_column_text(pStmt, 2), zColl);
   205    187         sqlite3_finalize(pStmt);
   206         -      return;
          188  +      return 0;
   207    189       }
   208    190       if( sqlite3_column_int(pStmt, 5)==0 ) break;
   209    191       nCol++;
   210    192     }
   211    193     sqlite3_finalize(pStmt);
          194  +  if( nCol==0 ) return 0;
   212    195     nByte = (sizeof(aCnt[0]) + sizeof(apValue[0]))*nCol + 30*(nCol+1);
   213    196     aCnt = sqlite3_malloc( nByte );
   214    197     if( aCnt==0 ){
   215    198       runtimeError("out of memory");
   216    199     }
   217    200     memset(aCnt, 0, nByte);
   218    201     apValue = (sqlite3_value**)&aCnt[nCol];
................................................................................
   243    226       pStmt = db_prepare("PRAGMA btree_sample(\"%w\",%g,%lld)",
   244    227                          zIdx, ((double)i)/(double)N_SPAN, n*2);
   245    228     }  
   246    229     for(j=0; j<nCol; j++) sqlite3_value_free(apValue[j]);
   247    230     sqlite3_snprintf(szRes, zRes, "%lld", n);
   248    231     k = (int)strlen(zRes);
   249    232     for(j=0; j<nCol; j++){
   250         -    sqlite3_snprintf(szRes-k, zRes+k, " %d", nRow/aCnt[j]);
          233  +    sqlite3_snprintf(szRes-k, zRes+k, " %d", (nRow+aCnt[j]-1)/aCnt[j]);
   251    234       k += (int)strlen(zRes+k);
   252    235     }
   253    236     pStmt = db_prepare(
   254    237        "INSERT INTO temp.est_stat1(tbl,idx,stat)"
   255    238        "VALUES(\"%w\",\"%w\",'%s')", zTab, zIdx, zRes
   256    239     );
   257    240     sqlite3_step(pStmt);
   258    241     sqlite3_finalize(pStmt);
          242  +  return 1;
          243  +}
          244  +
          245  +/*
          246  +** Stat1 for a table.
          247  +*/
          248  +static void analyzeTable(const char *zTab){
          249  +  sqlite3_int64 n = estEntryCount(zTab);
          250  +  sqlite3_stmt *pStmt;
          251  +  int nIndex = 0;
          252  +  int isWithoutRowid = 0;
          253  +  if( n==0 ){
          254  +    printf("-- empty table: %s\n", zTab);
          255  +    return;
          256  +  }
          257  +  if( analyzeIndex(zTab,zTab) ){
          258  +    isWithoutRowid = 1;
          259  +    nIndex++;
          260  +  }
          261  +  pStmt = db_prepare("PRAGMA index_list(\"%w\")", zTab);
          262  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          263  +    if( sqlite3_column_text(pStmt,3)[0]=='p' && isWithoutRowid ) continue;
          264  +    if( sqlite3_column_int(pStmt,4)==0 ) nIndex++;
          265  +    analyzeIndex(zTab, (const char*)sqlite3_column_text(pStmt,1));
          266  +  }
          267  +  sqlite3_finalize(pStmt);
          268  +  if( nIndex==0 ){
          269  +    pStmt = db_prepare(
          270  +       "INSERT INTO temp.est_stat1(tbl,idx,stat)"
          271  +       "VALUES(\"%w\",NULL,'%lld')", zTab, n
          272  +    );
          273  +    sqlite3_step(pStmt);
          274  +    sqlite3_finalize(pStmt);
          275  +  }
   259    276   }
          277  +
   260    278   
   261    279   /*
   262    280   ** Print the sqlite3_value X as an SQL literal.
   263    281   */
   264    282   static void printQuoted(FILE *out, sqlite3_value *X){
   265    283     switch( sqlite3_value_type(X) ){
   266    284       case SQLITE_FLOAT: {
................................................................................
   397    415       cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb);
   398    416     }
   399    417     rc = sqlite3_exec(g.db, "CREATE TEMP TABLE est_stat1(tbl,idx,stat);",
   400    418                       0, 0, &zErrMsg);
   401    419     if( rc || zErrMsg ){
   402    420       cmdlineError("Cannot CREATE TEMP TABLE");
   403    421     }
   404         -  pStmt = db_prepare("SELECT type, name, tbl_name FROM sqlite_master"
   405         -                     " WHERE type IN ('table','index')"
   406         -                     "   AND rootpage>0"
   407         -                     "   AND (type='index' OR name NOT LIKE 'sqlite_%%')"
   408         -                     " ORDER BY tbl_name, type DESC, name");
          422  +  pStmt = db_prepare("SELECT name FROM sqlite_master"
          423  +                     " WHERE type='table' AND rootpage>0"
          424  +                     "   AND name NOT LIKE 'sqlite_%%'"
          425  +                     " ORDER BY name");
   409    426     while( sqlite3_step(pStmt)==SQLITE_ROW ){
   410         -    const char *zType = (const char*)sqlite3_column_text(pStmt, 0);
   411         -    const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
   412         -    const char *zTblName = (const char*)sqlite3_column_text(pStmt, 2);
   413         -    if( zType[0]=='t' ){
   414         -      analyzeTable(zName);
   415         -    }else{
   416         -      analyzeIndex(zTblName, zName);
   417         -    }
          427  +    const char *zName = (const char*)sqlite3_column_text(pStmt, 0);
          428  +    analyzeTable(zName);
   418    429     }
   419    430     sqlite3_finalize(pStmt);
   420    431     dump_table("temp.est_stat1","sqlite_stat1");
   421    432     sqlite3_close(g.db);
   422    433     return 0;
   423    434   }