/ Check-in [4b73ee33]
Login

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

Overview
Comment:Experimental merge of the est_count_pragma and the pragma-as-vtab branches.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | est-count-pragma-vtab
Files: files | file ages | folders
SHA1: 4b73ee33f31281886256663a0e4a6397d3c8efc5
User & Date: drh 2016-12-16 16:49:11
Context
2016-12-16
16:49
Experimental merge of the est_count_pragma and the pragma-as-vtab branches. Closed-Leaf check-in: 4b73ee33 user: drh tags: est-count-pragma-vtab
15:57
Merge recent trunk enhancements. check-in: 74a0ca1f user: drh tags: est_count_pragma
04:20
Fix an error in the way the "schema" argument to some pragma virtual tables is handled. Closed-Leaf check-in: 546821e2 user: drh tags: pragma-as-vtab
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to main.mk.

   494    494   sqldiff$(EXE):	$(TOP)/tool/sqldiff.c sqlite3.c sqlite3.h
   495    495   	$(TCCX) -o sqldiff$(EXE) -DSQLITE_THREADSAFE=0 \
   496    496   		$(TOP)/tool/sqldiff.c sqlite3.c $(TLIBS) $(THREADLIB)
   497    497   
   498    498   dbhash$(EXE):	$(TOP)/tool/dbhash.c sqlite3.c sqlite3.h
   499    499   	$(TCCX) -o dbhash$(EXE) -DSQLITE_THREADSAFE=0 \
   500    500   		$(TOP)/tool/dbhash.c sqlite3.c $(TLIBS) $(THREADLIB)
          501  +
          502  +faststat1$(EXE):	$(TOP)/tool/faststat1.c sqlite3.c sqlite3.h
          503  +	$(TCCX) -o faststat1$(EXE) -DSQLITE_THREADSAFE=0 \
          504  +		$(TOP)/tool/faststat1.c sqlite3.c $(TLIBS) $(THREADLIB)
   501    505   
   502    506   scrub$(EXE):	$(TOP)/ext/misc/scrub.c sqlite3.o
   503    507   	$(TCC) -I. -DSCRUB_STANDALONE -o scrub$(EXE) $(TOP)/ext/misc/scrub.c sqlite3.o $(THREADLIB)
   504    508   
   505    509   srcck1$(EXE):	$(TOP)/tool/srcck1.c
   506    510   	$(BCC) -o srcck1$(EXE) $(TOP)/tool/srcck1.c
   507    511   

Changes to src/btree.c.

  5031   5031           pCur->curFlags &= ~BTCF_AtLast;
  5032   5032         }
  5033   5033      
  5034   5034       }
  5035   5035     }
  5036   5036     return rc;
  5037   5037   }
         5038  +
         5039  +/*
         5040  +** Move the cursor pCur to a location within its b-tree that is
         5041  +** approximately the x/1e9*nRow entry in the table, assuming the
         5042  +** table contains nRow entries.  So, in other words, if x==0 move
         5043  +** to the first entry and if x=1e9 move to the last entry and if
         5044  +** x=5e8 move to the middle entry.  The final landing spot is
         5045  +** approximate.
         5046  +**
         5047  +** Write an estimate of the number of entries in the b-tree into
         5048  +** the *pnRowEst variable.
         5049  +**
         5050  +** This routine works by first moving the cursor to the root of the
         5051  +** b-tree, then following pointers down to a leaf, selecting a pointer
         5052  +** according to x.
         5053  +**
         5054  +** The estimated number of entries is found by multiplying the number of
         5055  +** entries on the leaf page by the number of pointers at each layer of
         5056  +** non-leaf pages.
         5057  +**
         5058  +** Return SQLITE_OK on success or an error code if problems are encountered.
         5059  +*/
         5060  +int sqlite3BtreeMovetoProportional(
         5061  +  BtCursor *pCur,            /* Cursor to reposition */
         5062  +  u32 x,                     /* approximate location to position the cursor */
         5063  +  sqlite3_uint64 *pnRowEst   /* Write estimated entry count here */
         5064  +){
         5065  +  sqlite3_uint64 n = 1;
         5066  +  int rc;
         5067  +  Pgno chldPg;
         5068  +  u32 mx = 1000000000;
         5069  +  u32 perChild;
         5070  +  u16 rx;
         5071  +  MemPage *pPage;
         5072  +  rc = moveToRoot(pCur);
         5073  +  if( rc ) return rc;
         5074  +  pPage = pCur->apPage[0];
         5075  +  while( !pPage->leaf ){
         5076  +    perChild = (mx+pPage->nCell)/(pPage->nCell+1);
         5077  +    if( perChild<1 ) perChild = 1;
         5078  +    rx = x/perChild;
         5079  +    x %= perChild;
         5080  +    mx = perChild;
         5081  +    if( rx>=pPage->nCell ){
         5082  +      chldPg = get4byte(&pPage->aData[pPage->hdrOffset+8]);
         5083  +    }else{
         5084  +      chldPg = get4byte(findCell(pPage,rx));
         5085  +    }
         5086  +    n *= pPage->nCell+1;
         5087  +    pCur->aiIdx[pCur->iPage] = rx;
         5088  +    rc = moveToChild(pCur, chldPg);
         5089  +    if( rc ) return rc;
         5090  +    pPage = pCur->apPage[pCur->iPage];
         5091  +  }
         5092  +  *pnRowEst = n*pPage->nCell;
         5093  +  if( pPage->nCell==0 ){
         5094  +    rx = 0;
         5095  +  }else{
         5096  +    perChild = mx/pPage->nCell;
         5097  +    if( perChild<1 ) perChild = 1;
         5098  +    rx = x/perChild;
         5099  +    if( rx>=pPage->nCell ) rx = pPage->nCell-1;
         5100  +  }
         5101  +  pCur->aiIdx[pCur->iPage] = rx;
         5102  +
         5103  +  return SQLITE_OK;
         5104  +}
  5038   5105   
  5039   5106   /* Move the cursor so that it points to an entry near the key 
  5040   5107   ** specified by pIdxKey or intKey.   Return a success code.
  5041   5108   **
  5042   5109   ** For INTKEY tables, the intKey parameter is used.  pIdxKey 
  5043   5110   ** must be NULL.  For index tables, pIdxKey is used and intKey
  5044   5111   ** is ignored.

Changes to src/btree.h.

   234    234   void sqlite3BtreeCursorZero(BtCursor*);
   235    235   void sqlite3BtreeCursorHintFlags(BtCursor*, unsigned);
   236    236   #ifdef SQLITE_ENABLE_CURSOR_HINTS
   237    237   void sqlite3BtreeCursorHint(BtCursor*, int, ...);
   238    238   #endif
   239    239   
   240    240   int sqlite3BtreeCloseCursor(BtCursor*);
          241  +int sqlite3BtreeMovetoProportional(BtCursor*,u32,u64*);
   241    242   int sqlite3BtreeMovetoUnpacked(
   242    243     BtCursor*,
   243    244     UnpackedRecord *pUnKey,
   244    245     i64 intKey,
   245    246     int bias,
   246    247     int *pRes
   247    248   );

Changes to src/build.c.

  3580   3580   }
  3581   3581   
  3582   3582   /*
  3583   3583   ** Append a new element to the given IdList.  Create a new IdList if
  3584   3584   ** need be.
  3585   3585   **
  3586   3586   ** A new IdList is returned, or NULL if malloc() fails.
         3587  +**
         3588  +** The zName must have been obtained from sqlite3DbMalloc().  This routine
         3589  +** accepts ownership of the zName string and will ensure that it is freed
         3590  +** when no longer in use.
  3587   3591   */
  3588         -IdList *sqlite3IdListAppend(sqlite3 *db, IdList *pList, Token *pToken){
         3592  +IdList *sqlite3IdListAppend(
         3593  +  Parse *pParse,       /* Parsing context */
         3594  +  IdList *pList,       /* ID list to append to */
         3595  +  char *zName          /* Token to append */
         3596  +){
  3589   3597     int i;
         3598  +  sqlite3 *db = pParse->db;
  3590   3599     if( pList==0 ){
  3591   3600       pList = sqlite3DbMallocZero(db, sizeof(IdList) );
  3592         -    if( pList==0 ) return 0;
         3601  +    if( pList==0 ) goto id_list_append_fail;
  3593   3602     }
  3594   3603     pList->a = sqlite3ArrayAllocate(
  3595   3604         db,
  3596   3605         pList->a,
  3597   3606         sizeof(pList->a[0]),
  3598   3607         &pList->nId,
  3599   3608         &i
  3600   3609     );
  3601   3610     if( i<0 ){
  3602   3611       sqlite3IdListDelete(db, pList);
  3603         -    return 0;
         3612  +    goto id_list_append_fail;
  3604   3613     }
  3605         -  pList->a[i].zName = sqlite3NameFromToken(db, pToken);
         3614  +  pList->a[i].zName = zName;
  3606   3615     return pList;
         3616  +
         3617  +id_list_append_fail:
         3618  +  sqlite3DbFree(db, zName);
         3619  +  return 0;
  3607   3620   }
  3608   3621   
  3609   3622   /*
  3610   3623   ** Delete an IdList.
  3611   3624   */
  3612   3625   void sqlite3IdListDelete(sqlite3 *db, IdList *pList){
  3613   3626     int i;

Changes to src/parse.y.

   200    200   %left BITAND BITOR LSHIFT RSHIFT.
   201    201   %left PLUS MINUS.
   202    202   %left STAR SLASH REM.
   203    203   %left CONCAT.
   204    204   %left COLLATE.
   205    205   %right BITNOT.
   206    206   
   207         -// An IDENTIFIER can be a generic identifier, or one of several
          207  +// An "id" can be a generic identifier, or one of several
   208    208   // keywords.  Any non-standard keyword can also be an identifier.
   209    209   //
   210    210   %token_class id  ID|INDEXED.
          211  +
          212  +// A "number" can be either an integer or a floating point value
          213  +%token_class number INTEGER|FLOAT.
          214  +
   211    215   
   212    216   // The following directive causes tokens ABORT, AFTER, ASC, etc. to
   213    217   // fallback to ID if they will not parse as their original value.
   214    218   // This obviates the need for the "id" nonterminal.
   215    219   //
   216    220   %fallback ID
   217    221     ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
................................................................................
   250    254   }
   251    255   typetoken(A) ::= typename(A) LP signed COMMA signed RP(Y). {
   252    256     A.n = (int)(&Y.z[Y.n] - A.z);
   253    257   }
   254    258   %type typename {Token}
   255    259   typename(A) ::= ids(A).
   256    260   typename(A) ::= typename(A) ids(Y). {A.n=Y.n+(int)(Y.z-A.z);}
   257         -signed ::= plus_num.
   258         -signed ::= minus_num.
          261  +signed ::= PLUS|MINUS number.
          262  +signed ::= number.
   259    263   
   260    264   // "carglist" is a list of additional constraints that come after the
   261    265   // column name and column type in a CREATE TABLE statement.
   262    266   //
   263    267   carglist ::= carglist ccons.
   264    268   carglist ::= .
   265    269   ccons ::= CONSTRAINT nm(X).           {pParse->constraintName = X;}
................................................................................
   803    807   insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
   804    808   
   805    809   %type idlist_opt {IdList*}
   806    810   %destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}
   807    811   %type idlist {IdList*}
   808    812   %destructor idlist {sqlite3IdListDelete(pParse->db, $$);}
   809    813   
   810         -idlist_opt(A) ::= .                       {A = 0;}
          814  +idlist_opt(A) ::= .                   {A = 0;}
   811    815   idlist_opt(A) ::= LP idlist(X) RP.    {A = X;}
   812    816   idlist(A) ::= idlist(A) COMMA nm(Y).
   813         -    {A = sqlite3IdListAppend(pParse->db,A,&Y);}
          817  +    {A = sqlite3IdListAppend(pParse,A,sqlite3NameFromToken(pParse->db,&Y));}
   814    818   idlist(A) ::= nm(Y).
   815         -    {A = sqlite3IdListAppend(pParse->db,0,&Y); /*A-overwrites-Y*/}
          819  +    {A = sqlite3IdListAppend(pParse,0,sqlite3NameFromToken(pParse->db,&Y));
          820  +     /*A-overwrites-Y*/}
   816    821   
   817    822   /////////////////////////// Expression Processing /////////////////////////////
   818    823   //
   819    824   
   820    825   %type expr {ExprSpan}
   821    826   %destructor expr {sqlite3ExprDelete(pParse->db, $$.pExpr);}
   822    827   %type term {ExprSpan}
................................................................................
  1321   1326   cmd ::= VACUUM nm(X).          {sqlite3Vacuum(pParse,&X);}
  1322   1327   %endif  SQLITE_OMIT_ATTACH
  1323   1328   %endif  SQLITE_OMIT_VACUUM
  1324   1329   
  1325   1330   ///////////////////////////// The PRAGMA command /////////////////////////////
  1326   1331   //
  1327   1332   %ifndef SQLITE_OMIT_PRAGMA
  1328         -cmd ::= PRAGMA nm(X) dbnm(Z).                {sqlite3Pragma(pParse,&X,&Z,0,0);}
  1329         -cmd ::= PRAGMA nm(X) dbnm(Z) EQ nmnum(Y).    {sqlite3Pragma(pParse,&X,&Z,&Y,0);}
  1330         -cmd ::= PRAGMA nm(X) dbnm(Z) LP nmnum(Y) RP. {sqlite3Pragma(pParse,&X,&Z,&Y,0);}
  1331         -cmd ::= PRAGMA nm(X) dbnm(Z) EQ minus_num(Y). 
  1332         -                                             {sqlite3Pragma(pParse,&X,&Z,&Y,1);}
  1333         -cmd ::= PRAGMA nm(X) dbnm(Z) LP minus_num(Y) RP.
  1334         -                                             {sqlite3Pragma(pParse,&X,&Z,&Y,1);}
  1335         -
  1336         -nmnum(A) ::= plus_num(A).
  1337         -nmnum(A) ::= nm(A).
  1338         -nmnum(A) ::= ON(A).
  1339         -nmnum(A) ::= DELETE(A).
  1340         -nmnum(A) ::= DEFAULT(A).
  1341         -%endif SQLITE_OMIT_PRAGMA
  1342         -%token_class number INTEGER|FLOAT.
  1343         -plus_num(A) ::= PLUS number(X).       {A = X;}
  1344         -plus_num(A) ::= number(A).
  1345         -minus_num(A) ::= MINUS number(X).     {A = X;}
         1333  +cmd ::= PRAGMA nm(X) dbnm(Z).                {sqlite3Pragma(pParse,&X,&Z,0);}
         1334  +cmd ::= PRAGMA nm(X) dbnm(Z) EQ pragma_arglist(Y).
         1335  +     {sqlite3Pragma(pParse,&X,&Z,Y);}
         1336  +cmd ::= PRAGMA nm(X) dbnm(Z) LP pragma_arglist(Y) RP.
         1337  +     {sqlite3Pragma(pParse,&X,&Z,Y);}
         1338  +
         1339  +%type pragma_arglist {IdList*}
         1340  +%destructor pragma_arglist {sqlite3IdListDelete(pParse->db,$$);}
         1341  +  
         1342  +pragma_arglist(A) ::= pragma_arg(X).
         1343  +   { A = sqlite3IdListAppend(pParse,0,X)/*A-overwrites-X*/; }
         1344  +pragma_arglist(A) ::= pragma_arglist(A) COMMA pragma_arg(Y).
         1345  +   { A = sqlite3IdListAppend(pParse,A,Y); }
         1346  +
         1347  +%type pragma_arg {char*}
         1348  +%destructor pragma_arg {sqlite3DbFree(pParse->db,$$);}
         1349  +pragma_arg(A) ::= number(X).
         1350  +    {A = sqlite3NameFromToken(pParse->db,&X);/*A-overwrites-X*/}
         1351  +pragma_arg(A) ::= nm(X).
         1352  +    {A = sqlite3NameFromToken(pParse->db,&X);/*A-overwrites-X*/}
         1353  +pragma_arg(A) ::= ON|DELETE|DEFAULT(X).
         1354  +    {A = sqlite3NameFromToken(pParse->db,&X);/*A-overwrites-X*/}
         1355  +pragma_arg(A) ::= PLUS number(X).   {A = sqlite3NameFromToken(pParse->db,&X);}
         1356  +pragma_arg(A) ::= MINUS number(X).  {A = sqlite3MPrintf(pParse->db,"-%T",&X);}
         1357  +
         1358  +%endif SQLITE_OMIT_PRAGMA
  1346   1359   //////////////////////////// The CREATE TRIGGER command /////////////////////
  1347   1360   
  1348   1361   %ifndef SQLITE_OMIT_TRIGGER
  1349   1362   
  1350   1363   cmd ::= createkw trigger_decl(A) BEGIN trigger_cmd_list(S) END(Z). {
  1351   1364     Token all;
  1352   1365     all.z = A.z;

Changes to src/pragma.c.

   296    296   }
   297    297   
   298    298   /*
   299    299   ** Process a pragma statement.  
   300    300   **
   301    301   ** Pragmas are of this form:
   302    302   **
   303         -**      PRAGMA [schema.]id [= value]
   304         -**
   305         -** The identifier might also be a string.  The value is a string, and
   306         -** identifier, or a number.  If minusFlag is true, then the value is
   307         -** a number that was preceded by a minus sign.
          303  +**      PRAGMA [schema.]id [= value-list]
   308    304   **
   309    305   ** If the left side is "database.id" then pId1 is the database name
   310    306   ** and pId2 is the id.  If the left side is just "id" then pId1 is the
   311    307   ** id and pId2 is any empty string.
   312    308   */
   313    309   void sqlite3Pragma(
   314    310     Parse *pParse, 
   315    311     Token *pId1,        /* First part of [schema.]id field */
   316    312     Token *pId2,        /* Second part of [schema.]id field, or NULL */
   317         -  Token *pValue,      /* Token for <value>, or NULL */
   318         -  int minusFlag       /* True if a '-' sign preceded <value> */
          313  +  IdList *pValues     /* The value-list arguments.  NULL if omitted */
   319    314   ){
   320    315     char *zLeft = 0;       /* Nul-terminated UTF-8 string <id> */
   321    316     char *zRight = 0;      /* Nul-terminated UTF-8 string <value>, or NULL */
   322    317     const char *zDb = 0;   /* The database name */
   323    318     Token *pId;            /* Pointer to <id> token */
   324    319     char *aFcntl[4];       /* Argument to SQLITE_FCNTL_PRAGMA */
   325    320     int iDb;               /* Database index for <database> */
   326    321     int rc;                      /* return value form SQLITE_FCNTL_PRAGMA */
   327    322     sqlite3 *db = pParse->db;    /* The database connection */
   328    323     Db *pDb;                     /* The specific database being pragmaed */
   329    324     Vdbe *v = sqlite3GetVdbe(pParse);  /* Prepared statement */
   330    325     const PragmaName *pPragma;   /* The pragma */
   331    326   
   332         -  if( v==0 ) return;
          327  +  if( v==0 ) goto pragma_out;
   333    328     sqlite3VdbeRunOnlyOnce(v);
   334    329     pParse->nMem = 2;
   335    330   
   336    331     /* Interpret the [schema.] part of the pragma statement. iDb is the
   337    332     ** index of the database this pragma is being applied to in db.aDb[]. */
   338    333     iDb = sqlite3TwoPartName(pParse, pId1, pId2, &pId);
   339         -  if( iDb<0 ) return;
          334  +  if( iDb<0 ) goto pragma_out;
   340    335     pDb = &db->aDb[iDb];
   341    336   
   342    337     /* If the temp database has been explicitly named as part of the 
   343    338     ** pragma, make sure it is open. 
   344    339     */
   345    340     if( iDb==1 && sqlite3OpenTempDatabase(pParse) ){
   346    341       return;
   347    342     }
   348    343   
   349    344     zLeft = sqlite3NameFromToken(db, pId);
   350         -  if( !zLeft ) return;
   351         -  if( minusFlag ){
   352         -    zRight = sqlite3MPrintf(db, "-%T", pValue);
   353         -  }else{
   354         -    zRight = sqlite3NameFromToken(db, pValue);
   355         -  }
          345  +  if( !zLeft ) goto pragma_out;
          346  +  if( pValues ) zRight = pValues->a[0].zName;
   356    347   
   357    348     assert( pId2 );
   358    349     zDb = pId2->n>0 ? pDb->zDbSName : 0;
   359    350     if( sqlite3AuthCheck(pParse, SQLITE_PRAGMA, zLeft, zRight, zDb) ){
   360    351       goto pragma_out;
   361    352     }
   362    353   
................................................................................
  1120   1111     }
  1121   1112     break;
  1122   1113   
  1123   1114     case PragTyp_INDEX_INFO: if( zRight ){
  1124   1115       Index *pIdx;
  1125   1116       Table *pTab;
  1126   1117       pIdx = sqlite3FindIndex(db, zRight, zDb);
         1118  +    if( pIdx==0 ){
         1119  +      pTab = sqlite3FindTable(db, zRight, zDb);
         1120  +      if( pTab && !HasRowid(pTab) ) pIdx = sqlite3PrimaryKeyIndex(pTab);
         1121  +    }
  1127   1122       if( pIdx ){
  1128   1123         int i;
  1129   1124         int mx;
  1130   1125         if( pPragma->iArg ){
  1131   1126           /* PRAGMA index_xinfo (newer version with more rows and columns) */
  1132   1127           mx = pIdx->nColumn;
  1133   1128           pParse->nMem = 6;
................................................................................
  1366   1361     case PragTyp_CASE_SENSITIVE_LIKE: {
  1367   1362       if( zRight ){
  1368   1363         sqlite3RegisterLikeFunctions(db, sqlite3GetBoolean(zRight, 0));
  1369   1364       }
  1370   1365     }
  1371   1366     break;
  1372   1367   
         1368  +  /*
         1369  +  **   PRAGMA est_row_cnt(<table-or-index>,<fraction>);
         1370  +  **
         1371  +  ** Seek in <table-or-index> through the first <fraction> of rows and
         1372  +  ** estimate the total number of rows based on the path back up to the
         1373  +  ** root.
         1374  +  */
         1375  +  case PragTyp_EST_COUNT: {
         1376  +    Index *pIdx;
         1377  +    Table *pTab = 0;
         1378  +    Pgno iRoot = 0;
         1379  +    const char *zName = 0;
         1380  +    int regResult;
         1381  +    double r;
         1382  +    if( (pIdx = sqlite3FindIndex(db, zRight, zDb))!=0 ){
         1383  +      iRoot = pIdx->tnum;
         1384  +      zName = pIdx->zName;
         1385  +    }else if( (pTab = sqlite3FindTable(db, zRight, zDb))!=0 ){
         1386  +      zName = pTab->zName;
         1387  +      if( HasRowid(pTab) ){
         1388  +        iRoot = pTab->tnum;
         1389  +      }else{
         1390  +        pIdx = sqlite3PrimaryKeyIndex(pTab);
         1391  +        iRoot = pIdx->tnum;
         1392  +      }
         1393  +    }else{
         1394  +      break;
         1395  +    }
         1396  +    sqlite3TableLock(pParse, iDb, iRoot, 0, zName);
         1397  +    regResult = ++pParse->nMem;
         1398  +    if( pValues->nId>=2 ){
         1399  +      const char *z = pValues->a[1].zName;
         1400  +      sqlite3AtoF(z, &r, sqlite3Strlen30(z), SQLITE_UTF8);
         1401  +    }else{
         1402  +      r = 0.5;
         1403  +    }
         1404  +    if( r<0.0 ) r = 0.0;
         1405  +    if( r>1.0 ) r = 1.0;
         1406  +    sqlite3CodeVerifySchema(pParse, iDb);
         1407  +    pParse->nTab++;
         1408  +    sqlite3VdbeAddOp4Int(v, OP_OpenRead, 0, iRoot, iDb, 1);
         1409  +    if( pIdx ) sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
         1410  +    sqlite3VdbeAddOp3(v, OP_EstRowCnt, 0, regResult, (int)(r*1000000000));
         1411  +    sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 1);
         1412  +  }
         1413  +  break;
         1414  +
         1415  +  /*
         1416  +  **   PRAGMA btree_sample(<table-or-index>,<fraction>,<limit>);
         1417  +  **
         1418  +  ** Seek in <table-or-index> through the first <fraction> of rows and
         1419  +  ** then begin returning rows, one by one.  A max of <limit> rows will
         1420  +  ** be returned.
         1421  +  */
         1422  +  case PragTyp_BTREE_SAMPLE: {
         1423  +    Index *pIdx;
         1424  +    Table *pTab = 0;
         1425  +    Pgno iRoot = 0;
         1426  +    Pgno iLock = 0;
         1427  +    int nCol = 0;
         1428  +    const char *zName = 0;
         1429  +    int iLimit = 10;
         1430  +    int i;
         1431  +    int regResult;
         1432  +    int regLimit;
         1433  +    int addrTop;
         1434  +    int addrJmp;
         1435  +    int addrSkip;
         1436  +    double r;
         1437  +    if( (pIdx = sqlite3FindIndex(db, zRight, zDb))!=0 ){
         1438  +      iRoot = pIdx->tnum;
         1439  +      iLock = pIdx->pTable->tnum;
         1440  +      zName = pIdx->zName;
         1441  +      nCol = pIdx->nColumn;
         1442  +    }else if( (pTab = sqlite3FindTable(db, zRight, zDb))!=0 ){
         1443  +      zName = pTab->zName;
         1444  +      if( HasRowid(pTab) ){
         1445  +        iLock = iRoot = pTab->tnum;
         1446  +        nCol = pTab->nCol;
         1447  +      }else{
         1448  +        pIdx = sqlite3PrimaryKeyIndex(pTab);
         1449  +        iLock = iRoot = pIdx->tnum;
         1450  +        nCol = pIdx->nColumn;
         1451  +      }
         1452  +    }else{
         1453  +      break;
         1454  +    }
         1455  +    sqlite3VdbeSetNumCols(v, nCol);
         1456  +    for(i=0; i<nCol; i++){
         1457  +      char zCol[30];
         1458  +      sqlite3_snprintf(sizeof(zCol),zCol,"c%06d",i);
         1459  +      sqlite3VdbeSetColName(v, i, COLNAME_NAME, zCol, SQLITE_TRANSIENT);
         1460  +    }
         1461  +    if( pValues->nId>=2 ){
         1462  +      const char *z = pValues->a[1].zName;
         1463  +      sqlite3AtoF(z, &r, sqlite3Strlen30(z), SQLITE_UTF8);
         1464  +    }else{
         1465  +      r = 0.5;
         1466  +    }
         1467  +    if( r<0.0 ) r = 0.0;
         1468  +    if( r>1.0 ) r = 1.0;
         1469  +    if( pValues->nId>=3 ){
         1470  +      iLimit = sqlite3Atoi(pValues->a[2].zName);
         1471  +    }
         1472  +    pParse->nTab++;
         1473  +    sqlite3TableLock(pParse, iDb, iLock, 0, zName);
         1474  +    sqlite3CodeVerifySchema(pParse, iDb);
         1475  +    sqlite3VdbeAddOp4Int(v, OP_OpenRead, 0, iRoot, iDb, nCol);
         1476  +    if( pIdx ) sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
         1477  +    regLimit = ++pParse->nMem;
         1478  +    regResult = pParse->nMem+1;
         1479  +    pParse->nMem += nCol;
         1480  +    sqlite3VdbeAddOp2(v, OP_Integer, iLimit, regLimit);
         1481  +    addrSkip = sqlite3VdbeAddOp1(v, OP_Rewind, 0); VdbeCoverage(v);
         1482  +    sqlite3VdbeAddOp3(v, OP_EstRowCnt, 0, regResult, (int)(r*1000000000));
         1483  +    addrTop = sqlite3VdbeCurrentAddr(v);
         1484  +    for(i=0; i<nCol; i++){
         1485  +      sqlite3VdbeAddOp3(v, OP_Column, 0, i, regResult+i);
         1486  +    }
         1487  +    sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nCol);
         1488  +    addrJmp = sqlite3VdbeAddOp1(v, OP_DecrJumpZero, regLimit); VdbeCoverage(v);
         1489  +    sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop); VdbeCoverage(v);
         1490  +    sqlite3VdbeJumpHere(v, addrJmp);
         1491  +    sqlite3VdbeJumpHere(v, addrSkip);
         1492  +  }
         1493  +  break;
         1494  +
  1373   1495   #ifndef SQLITE_INTEGRITY_CHECK_ERROR_MAX
  1374   1496   # define SQLITE_INTEGRITY_CHECK_ERROR_MAX 100
  1375   1497   #endif
  1376   1498   
  1377   1499   #ifndef SQLITE_OMIT_INTEGRITY_CHECK
  1378   1500     /* Pragma "quick_check" is reduced version of 
  1379   1501     ** integrity_check designed to detect most database corruption
................................................................................
  1951   2073     break;
  1952   2074   #endif
  1953   2075   
  1954   2076     } /* End of the PRAGMA switch */
  1955   2077   
  1956   2078   pragma_out:
  1957   2079     sqlite3DbFree(db, zLeft);
  1958         -  sqlite3DbFree(db, zRight);
         2080  +  sqlite3IdListDelete(db, pValues);
  1959   2081   }
  1960   2082   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1961   2083   /*****************************************************************************
  1962   2084   ** Implementation of an eponymous virtual table that runs a pragma.
  1963   2085   **
  1964   2086   */
  1965   2087   typedef struct PragmaVtab PragmaVtab;

Changes to src/pragma.h.

     4      4   ** that script and rerun it.
     5      5   */
     6      6   
     7      7   /* The various pragma types */
     8      8   #define PragTyp_HEADER_VALUE                   0
     9      9   #define PragTyp_AUTO_VACUUM                    1
    10     10   #define PragTyp_FLAG                           2
    11         -#define PragTyp_BUSY_TIMEOUT                   3
    12         -#define PragTyp_CACHE_SIZE                     4
    13         -#define PragTyp_CACHE_SPILL                    5
    14         -#define PragTyp_CASE_SENSITIVE_LIKE            6
    15         -#define PragTyp_COLLATION_LIST                 7
    16         -#define PragTyp_COMPILE_OPTIONS                8
    17         -#define PragTyp_DATA_STORE_DIRECTORY           9
    18         -#define PragTyp_DATABASE_LIST                 10
    19         -#define PragTyp_DEFAULT_CACHE_SIZE            11
    20         -#define PragTyp_ENCODING                      12
    21         -#define PragTyp_FOREIGN_KEY_CHECK             13
    22         -#define PragTyp_FOREIGN_KEY_LIST              14
    23         -#define PragTyp_INCREMENTAL_VACUUM            15
    24         -#define PragTyp_INDEX_INFO                    16
    25         -#define PragTyp_INDEX_LIST                    17
    26         -#define PragTyp_INTEGRITY_CHECK               18
    27         -#define PragTyp_JOURNAL_MODE                  19
    28         -#define PragTyp_JOURNAL_SIZE_LIMIT            20
    29         -#define PragTyp_LOCK_PROXY_FILE               21
    30         -#define PragTyp_LOCKING_MODE                  22
    31         -#define PragTyp_PAGE_COUNT                    23
    32         -#define PragTyp_MMAP_SIZE                     24
    33         -#define PragTyp_PAGE_SIZE                     25
    34         -#define PragTyp_SECURE_DELETE                 26
    35         -#define PragTyp_SHRINK_MEMORY                 27
    36         -#define PragTyp_SOFT_HEAP_LIMIT               28
    37         -#define PragTyp_STATS                         29
    38         -#define PragTyp_SYNCHRONOUS                   30
    39         -#define PragTyp_TABLE_INFO                    31
    40         -#define PragTyp_TEMP_STORE                    32
    41         -#define PragTyp_TEMP_STORE_DIRECTORY          33
    42         -#define PragTyp_THREADS                       34
    43         -#define PragTyp_WAL_AUTOCHECKPOINT            35
    44         -#define PragTyp_WAL_CHECKPOINT                36
    45         -#define PragTyp_ACTIVATE_EXTENSIONS           37
    46         -#define PragTyp_HEXKEY                        38
    47         -#define PragTyp_KEY                           39
    48         -#define PragTyp_REKEY                         40
    49         -#define PragTyp_LOCK_STATUS                   41
    50         -#define PragTyp_PARSER_TRACE                  42
           11  +#define PragTyp_BTREE_SAMPLE                   3
           12  +#define PragTyp_BUSY_TIMEOUT                   4
           13  +#define PragTyp_CACHE_SIZE                     5
           14  +#define PragTyp_CACHE_SPILL                    6
           15  +#define PragTyp_CASE_SENSITIVE_LIKE            7
           16  +#define PragTyp_COLLATION_LIST                 8
           17  +#define PragTyp_COMPILE_OPTIONS                9
           18  +#define PragTyp_DATA_STORE_DIRECTORY          10
           19  +#define PragTyp_DATABASE_LIST                 11
           20  +#define PragTyp_DEFAULT_CACHE_SIZE            12
           21  +#define PragTyp_ENCODING                      13
           22  +#define PragTyp_EST_COUNT                     14
           23  +#define PragTyp_FOREIGN_KEY_CHECK             15
           24  +#define PragTyp_FOREIGN_KEY_LIST              16
           25  +#define PragTyp_INCREMENTAL_VACUUM            17
           26  +#define PragTyp_INDEX_INFO                    18
           27  +#define PragTyp_INDEX_LIST                    19
           28  +#define PragTyp_INTEGRITY_CHECK               20
           29  +#define PragTyp_JOURNAL_MODE                  21
           30  +#define PragTyp_JOURNAL_SIZE_LIMIT            22
           31  +#define PragTyp_LOCK_PROXY_FILE               23
           32  +#define PragTyp_LOCKING_MODE                  24
           33  +#define PragTyp_PAGE_COUNT                    25
           34  +#define PragTyp_MMAP_SIZE                     26
           35  +#define PragTyp_PAGE_SIZE                     27
           36  +#define PragTyp_SECURE_DELETE                 28
           37  +#define PragTyp_SHRINK_MEMORY                 29
           38  +#define PragTyp_SOFT_HEAP_LIMIT               30
           39  +#define PragTyp_STATS                         31
           40  +#define PragTyp_SYNCHRONOUS                   32
           41  +#define PragTyp_TABLE_INFO                    33
           42  +#define PragTyp_TEMP_STORE                    34
           43  +#define PragTyp_TEMP_STORE_DIRECTORY          35
           44  +#define PragTyp_THREADS                       36
           45  +#define PragTyp_WAL_AUTOCHECKPOINT            37
           46  +#define PragTyp_WAL_CHECKPOINT                38
           47  +#define PragTyp_ACTIVATE_EXTENSIONS           39
           48  +#define PragTyp_HEXKEY                        40
           49  +#define PragTyp_KEY                           41
           50  +#define PragTyp_REKEY                         42
           51  +#define PragTyp_LOCK_STATUS                   43
           52  +#define PragTyp_PARSER_TRACE                  44
    51     53   
    52     54   /* Property flags associated with various pragma. */
    53     55   #define PragFlg_NeedSchema 0x01 /* Force schema load before running */
    54     56   #define PragFlg_NoColumns  0x02 /* OP_ResultRow called with zero columns */
    55     57   #define PragFlg_ReadOnly   0x04 /* Read-only HEADER_VALUE */
    56     58   #define PragFlg_Result0    0x08 /* Acts as query when no argument */
    57     59   #define PragFlg_Result1    0x10 /* Acts as query when has one argument */
................................................................................
   149    151    {/* zName:     */ "automatic_index",
   150    152     /* ePragTyp:  */ PragTyp_FLAG,
   151    153     /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns,
   152    154     /* ColNames:  */ 0, 0,
   153    155     /* iArg:      */ SQLITE_AutoIndex },
   154    156   #endif
   155    157   #endif
          158  + {/* zName:     */ "btree_sample",
          159  +  /* ePragTyp:  */ PragTyp_BTREE_SAMPLE,
          160  +  /* ePragFlg:  */ PragFlg_NeedSchema,
          161  +  /* ColNames:  */ 0, 0,
          162  +  /* iArg:      */ 0 },
   156    163    {/* zName:     */ "busy_timeout",
   157    164     /* ePragTyp:  */ PragTyp_BUSY_TIMEOUT,
   158    165     /* ePragFlg:  */ PragFlg_Result0,
   159    166     /* ColNames:  */ 45, 1,
   160    167     /* iArg:      */ 0 },
   161    168   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
   162    169    {/* zName:     */ "cache_size",
................................................................................
   257    264   #if !defined(SQLITE_OMIT_UTF16)
   258    265    {/* zName:     */ "encoding",
   259    266     /* ePragTyp:  */ PragTyp_ENCODING,
   260    267     /* ePragFlg:  */ PragFlg_Result0,
   261    268     /* ColNames:  */ 0, 0,
   262    269     /* iArg:      */ 0 },
   263    270   #endif
          271  + {/* zName:     */ "est_count",
          272  +  /* ePragTyp:  */ PragTyp_EST_COUNT,
          273  +  /* ePragFlg:  */ PragFlg_NeedSchema,
          274  +  /* ColNames:  */ 0, 0,
          275  +  /* iArg:      */ 0 },
   264    276   #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
   265    277    {/* zName:     */ "foreign_key_check",
   266    278     /* ePragTyp:  */ PragTyp_FOREIGN_KEY_CHECK,
   267    279     /* ePragFlg:  */ PragFlg_NeedSchema,
   268    280     /* ColNames:  */ 38, 4,
   269    281     /* iArg:      */ 0 },
   270    282   #endif
................................................................................
   599    611    {/* zName:     */ "writable_schema",
   600    612     /* ePragTyp:  */ PragTyp_FLAG,
   601    613     /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns,
   602    614     /* ColNames:  */ 0, 0,
   603    615     /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
   604    616   #endif
   605    617   };
   606         -/* Number of pragmas: 60 on by default, 73 total. */
          618  +/* Number of pragmas: 62 on by default, 75 total. */

Changes to src/select.c.

  2033   2033     int regLimit, regOffset;      /* Registers used by LIMIT and OFFSET */
  2034   2034   
  2035   2035     /* Obtain authorization to do a recursive query */
  2036   2036     if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ) return;
  2037   2037   
  2038   2038     /* Process the LIMIT and OFFSET clauses, if they exist */
  2039   2039     addrBreak = sqlite3VdbeMakeLabel(v);
         2040  +  p->nSelectRow = 320;  /* 4 billion rows */
  2040   2041     computeLimitRegisters(pParse, p, addrBreak);
  2041   2042     pLimit = p->pLimit;
  2042   2043     pOffset = p->pOffset;
  2043   2044     regLimit = p->iLimit;
  2044   2045     regOffset = p->iOffset;
  2045   2046     p->pLimit = p->pOffset = 0;
  2046   2047     p->iLimit = p->iOffset = 0;
................................................................................
  5166   5167     if( pDest->eDest==SRT_EphemTab ){
  5167   5168       sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr);
  5168   5169     }
  5169   5170   
  5170   5171     /* Set the limiter.
  5171   5172     */
  5172   5173     iEnd = sqlite3VdbeMakeLabel(v);
  5173         -  p->nSelectRow = 320;  /* 4 billion rows */
         5174  +  if( (p->selFlags & SF_FixedLimit)==0 ){
         5175  +    p->nSelectRow = 320;  /* 4 billion rows */
         5176  +  }
  5174   5177     computeLimitRegisters(pParse, p, iEnd);
  5175   5178     if( p->iLimit==0 && sSort.addrSortIndex>=0 ){
  5176   5179       sqlite3VdbeChangeOpcode(v, sSort.addrSortIndex, OP_SorterOpen);
  5177   5180       sSort.sortFlags |= SORTFLAG_UseSorter;
  5178   5181     }
  5179   5182   
  5180   5183     /* Open an ephemeral index to use for the distinct set.

Changes to src/sqliteInt.h.

  3558   3558   void sqlite3ExprListSetSortOrder(ExprList*,int);
  3559   3559   void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
  3560   3560   void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
  3561   3561   void sqlite3ExprListDelete(sqlite3*, ExprList*);
  3562   3562   u32 sqlite3ExprListFlags(const ExprList*);
  3563   3563   int sqlite3Init(sqlite3*, char**);
  3564   3564   int sqlite3InitCallback(void*, int, char**, char**);
  3565         -void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);
         3565  +void sqlite3Pragma(Parse*,Token*,Token*,IdList*);
  3566   3566   #ifndef SQLITE_OMIT_VIRTUALTABLE
  3567   3567   Module *sqlite3PragmaVtabRegister(sqlite3*,const char *zName);
  3568   3568   #endif
  3569   3569   void sqlite3ResetAllSchemasOfConnection(sqlite3*);
  3570   3570   void sqlite3ResetOneSchema(sqlite3*,int);
  3571   3571   void sqlite3CollapseDatabaseArray(sqlite3*);
  3572   3572   void sqlite3CommitInternalChanges(sqlite3*);
................................................................................
  3636   3636     void sqlite3AutoincrementEnd(Parse *pParse);
  3637   3637   #else
  3638   3638   # define sqlite3AutoincrementBegin(X)
  3639   3639   # define sqlite3AutoincrementEnd(X)
  3640   3640   #endif
  3641   3641   void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int);
  3642   3642   void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*);
  3643         -IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
         3643  +IdList *sqlite3IdListAppend(Parse*, IdList*, char*);
  3644   3644   int sqlite3IdListIndex(IdList*,const char*);
  3645   3645   SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int);
  3646   3646   SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
  3647   3647   SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,
  3648   3648                                         Token*, Select*, Expr*, IdList*);
  3649   3649   void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *);
  3650   3650   void sqlite3SrcListFuncArgs(Parse*, SrcList*, ExprList*);

Changes to src/vdbe.c.

  4882   4882     if( rc ) goto abort_due_to_error;
  4883   4883     pC->nullRow = (u8)res;
  4884   4884     assert( pOp->p2>0 && pOp->p2<p->nOp );
  4885   4885     VdbeBranchTaken(res!=0,2);
  4886   4886     if( res ) goto jump_to_p2;
  4887   4887     break;
  4888   4888   }
         4889  +
         4890  +/*
         4891  +** Opcode: EstRowCnt P1 P2 P3 * * *
         4892  +**
         4893  +** Estimate the number of entries in btree for cursor P1 do a proportional
         4894  +** seek to of P3.  Store the result as a floating point value in P2.
         4895  +*/
         4896  +case OP_EstRowCnt: {  /* out2 */
         4897  +  VdbeCursor *pC;
         4898  +  BtCursor *pCrsr;
         4899  +  int rc;
         4900  +  sqlite3_uint64 n = 0;
         4901  +
         4902  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         4903  +  pC = p->apCsr[pOp->p1];
         4904  +  assert( pC->eCurType==CURTYPE_BTREE );
         4905  +  pCrsr = pC->uc.pCursor;
         4906  +  assert( pCrsr );
         4907  +  rc = sqlite3BtreeMovetoProportional(pCrsr, pOp->p3, &n);
         4908  +  if( rc ) goto abort_due_to_error;
         4909  +  pOut = out2Prerelease(p, pOp);
         4910  +  pOut->flags = MEM_Real;
         4911  +  pOut->u.r = n;
         4912  +  pC->nullRow = 0;
         4913  +  pC->deferredMoveto = 0;
         4914  +  pC->cacheStatus = CACHE_STALE;
         4915  +  break;
         4916  +}
  4889   4917   
  4890   4918   /* Opcode: Next P1 P2 P3 P4 P5
  4891   4919   **
  4892   4920   ** Advance cursor P1 so that it points to the next key/data pair in its
  4893   4921   ** table or index.  If there are no more key/value pairs then fall through
  4894   4922   ** to the following instruction.  But if the cursor advance was successful,
  4895   4923   ** jump immediately to P2.

Changes to test/with3.test.

    56     56     CREATE TABLE t1(x);
    57     57     WITH
    58     58       x1(a) AS (values(100))
    59     59     INSERT INTO t1(x)
    60     60       SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
    61     61     SELECT * FROM t1;
    62     62   } {200}
           63  +
           64  +#-------------------------------------------------------------------------
           65  +# Test that the planner notices LIMIT clauses on recursive WITH queries.
           66  +#
           67  +
           68  +ifcapable analyze {
           69  +  do_execsql_test 3.1.1 {
           70  +    CREATE TABLE y1(a, b);
           71  +    CREATE INDEX y1a ON y1(a);
           72  +
           73  +    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000)
           74  +      INSERT INTO y1 SELECT i%10, i FROM cnt;
           75  +    ANALYZE;
           76  +
           77  +  }
           78  +
           79  +  do_eqp_test 3.1.2 {
           80  +    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1)
           81  +    SELECT * FROM cnt, y1 WHERE i=a
           82  +  } {
           83  +    3 0 0 {SCAN TABLE cnt} 
           84  +    1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)}
           85  +    0 0 0 {SCAN SUBQUERY 1} 
           86  +    0 1 1 {SEARCH TABLE y1 USING INDEX y1a (a=?)}
           87  +  }
           88  +
           89  +  do_eqp_test 3.1.3 {
           90  +    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
           91  +    SELECT * FROM cnt, y1 WHERE i=a
           92  +  } {
           93  +    3 0 0 {SCAN TABLE cnt} 
           94  +    1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)}
           95  +    0 0 1 {SCAN TABLE y1} 
           96  +    0 1 0 {SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (i=?)}
           97  +  }
           98  +}
           99  +
          100  +do_execsql_test 3.2.1 {
          101  +  CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER);
          102  +  CREATE TABLE w2(pk INTEGER PRIMARY KEY);
          103  +}
          104  +
          105  +do_eqp_test 3.2.2 {
          106  +  WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1)
          107  +     UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
          108  +     SELECT * FROM c, w2, w1
          109  +     WHERE c.id=w2.pk AND c.id=w1.pk;
          110  +} {
          111  +  2 0 0 {EXECUTE SCALAR SUBQUERY 3} 
          112  +  3 0 0 {SCAN TABLE w2} 
          113  +  4 0 0 {SCAN TABLE w1}
          114  +  4 1 1 {SCAN TABLE c} 
          115  +  1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 0 {SCAN SUBQUERY 1}
          116  +  0 1 1 {SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)} 
          117  +  0 2 2 {SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)}
          118  +}
    63    119   
    64    120   finish_test

Changes to test/without_rowid1.test.

   323    323   } {1 {CHECK constraint failed: t70a}}
   324    324   do_catchsql_test 7.3 {
   325    325     CREATE TABLE t70b(
   326    326        a INT CHECK( rowid!=33 ),
   327    327        b TEXT PRIMARY KEY
   328    328     ) WITHOUT ROWID;
   329    329   } {1 {no such column: rowid}}
          330  +
          331  +# The PRAGMA index_info and index_xinfo pragmas work on 
          332  +# WITHOUT ROWID tables too, but not on rowid tables.
          333  +#
          334  +do_execsql_test 8.1 {
          335  +  CREATE TABLE t80a(a TEXT, b INT, c BLOB, PRIMARY KEY(c,b));
          336  +  PRAGMA index_info(t80a);
          337  +} {}
          338  +do_execsql_test 8.2 {
          339  +  PRAGMA index_xinfo(t80a);
          340  +} {}
          341  +do_execsql_test 8.3 {
          342  +  CREATE TABLE t80b(a TEXT, b INT, c BLOB, PRIMARY KEY(c,b)) WITHOUT ROWID;
          343  +  PRAGMA index_info(t80b);
          344  +} {0 2 c 1 1 b}
          345  +do_execsql_test 8.4 {
          346  +  PRAGMA index_xinfo(t80b);
          347  +} {0 2 c 0 BINARY 1 1 1 b 0 BINARY 1 2 0 a 0 BINARY 0}
          348  +
   330    349   
   331    350     
   332    351   finish_test

Added tool/faststat1.c.

            1  +/*
            2  +** 2016-10-24
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This is a utility program that uses the est_count and btree_sample
           14  +** pragmas to try to approximate the content of the sqlite_stat1 table
           15  +** without doing a full table scan.
           16  +**
           17  +** To compile, simply link against SQLite.
           18  +**
           19  +** See the showHelp() routine below for a brief description of how to
           20  +** run the utility.
           21  +*/
           22  +#include <stdio.h>
           23  +#include <stdlib.h>
           24  +#include <stdarg.h>
           25  +#include <ctype.h>
           26  +#include <string.h>
           27  +#include <assert.h>
           28  +#include "sqlite3.h"
           29  +
           30  +/*
           31  +** All global variables are gathered into the "g" singleton.
           32  +*/
           33  +struct GlobalVars {
           34  +  const char *zArgv0;       /* Name of program */
           35  +  unsigned fDebug;          /* Debug flags */
           36  +  sqlite3 *db;              /* The database connection */
           37  +} g;
           38  +
           39  +/*
           40  +** Allowed values for g.fDebug
           41  +*/
           42  +#define DEBUG_NONE          0
           43  +
           44  +  
           45  +/*
           46  +** Print an error resulting from faulting command-line arguments and
           47  +** abort the program.
           48  +*/
           49  +static void cmdlineError(const char *zFormat, ...){
           50  +  va_list ap;
           51  +  fprintf(stderr, "%s: ", g.zArgv0);
           52  +  va_start(ap, zFormat);
           53  +  vfprintf(stderr, zFormat, ap);
           54  +  va_end(ap);
           55  +  fprintf(stderr, "\n\"%s --help\" for more help\n", g.zArgv0);
           56  +  exit(1);
           57  +}
           58  +
           59  +/*
           60  +** Print an error message for an error that occurs at runtime, then
           61  +** abort the program.
           62  +*/
           63  +static void runtimeError(const char *zFormat, ...){
           64  +  va_list ap;
           65  +  fprintf(stderr, "%s: ", g.zArgv0);
           66  +  va_start(ap, zFormat);
           67  +  vfprintf(stderr, zFormat, ap);
           68  +  va_end(ap);
           69  +  fprintf(stderr, "\n");
           70  +  exit(1);
           71  +}
           72  +
           73  +/*
           74  +** Return the current time in milliseconds since the julian epoch.
           75  +*/
           76  +static sqlite3_int64 currentTime(void){
           77  +  sqlite3_vfs *pVfs = sqlite3_vfs_find(0);
           78  +  sqlite3_int64 x = 0;
           79  +  (void)pVfs->xCurrentTimeInt64(pVfs, &x);
           80  +  return x;
           81  +}
           82  +
           83  +/*
           84  +** Prepare a new SQL statement.  Print an error and abort if anything
           85  +** goes wrong.
           86  +*/
           87  +static sqlite3_stmt *db_vprepare(const char *zFormat, va_list ap){
           88  +  char *zSql;
           89  +  int rc;
           90  +  sqlite3_stmt *pStmt;
           91  +
           92  +  zSql = sqlite3_vmprintf(zFormat, ap);
           93  +  if( zSql==0 ) runtimeError("out of memory");
           94  +  rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0);
           95  +  if( rc ){
           96  +    runtimeError("SQL statement error: %s\n\"%s\"", sqlite3_errmsg(g.db),
           97  +                 zSql);
           98  +  }
           99  +  sqlite3_free(zSql);
          100  +  return pStmt;
          101  +}
          102  +static sqlite3_stmt *db_prepare(const char *zFormat, ...){
          103  +  va_list ap;
          104  +  sqlite3_stmt *pStmt;
          105  +  va_start(ap, zFormat);
          106  +  pStmt = db_vprepare(zFormat, ap);
          107  +  va_end(ap);
          108  +  return pStmt;
          109  +}
          110  +
          111  +/*
          112  +** Estimate the number of rows in the given table or index.
          113  +*/
          114  +static sqlite3_int64 estEntryCount(const char *zTabIdx){
          115  +  double sum = 0.0;
          116  +  int i;
          117  +  int n = 0;
          118  +  sqlite3_stmt *pStmt;
          119  +# define N_CNT_SAMPLE 10
          120  +  for(i=0; i<=N_CNT_SAMPLE; i++){
          121  +    pStmt = db_prepare("PRAGMA est_count(\"%w\",%g)", 
          122  +                       zTabIdx, ((double)i)/(double)(N_CNT_SAMPLE));
          123  +    if( sqlite3_step(pStmt)==SQLITE_ROW ){
          124  +      sum += sqlite3_column_double(pStmt, 0);
          125  +      n++;
          126  +    }
          127  +    sqlite3_finalize(pStmt);
          128  +  }
          129  +  return n==0 ? 0 : (sqlite3_int64)(sum/n);
          130  +}
          131  +
          132  +/*
          133  +** Compare the i-th column of pStmt against pValue.  Return true if they
          134  +** are different.
          135  +*/
          136  +static int columnNotEqual(sqlite3_stmt *pStmt, int i, sqlite3_value *pValue){
          137  +  int n1, n2, n;
          138  +  if( sqlite3_column_type(pStmt,i)!=sqlite3_value_type(pValue) ) return 1;
          139  +  switch( sqlite3_column_type(pStmt,i) ){
          140  +    case SQLITE_NULL:
          141  +      return 0;  /* Nulls compare equal to one another in this context */
          142  +
          143  +    case SQLITE_INTEGER:
          144  +      return sqlite3_column_int64(pStmt,i)!=sqlite3_value_int64(pValue);
          145  +
          146  +    case SQLITE_FLOAT:
          147  +      return sqlite3_column_double(pStmt,i)!=sqlite3_value_double(pValue);
          148  +
          149  +    case SQLITE_BLOB:
          150  +      n1 = sqlite3_column_bytes(pStmt,i);
          151  +      n2 = sqlite3_value_bytes(pValue);
          152  +      n = n1<n2 ? n1 : n2;
          153  +      if( memcmp(sqlite3_column_blob(pStmt,i), sqlite3_value_blob(pValue),n) ){
          154  +        return 1;
          155  +      }
          156  +      return n1!=n2;
          157  +
          158  +    case SQLITE_TEXT:
          159  +      n1 = sqlite3_column_bytes(pStmt,i);
          160  +      n2 = sqlite3_value_bytes(pValue);
          161  +      n = n1<n2 ? n1 : n2;
          162  +      if( memcmp(sqlite3_column_text(pStmt,i), sqlite3_value_text(pValue),n) ){
          163  +        return 1;
          164  +      }
          165  +      return n1!=n2;
          166  + 
          167  +  }
          168  +  return 1;
          169  +}
          170  +
          171  +/*
          172  +** Stat1 for an index.  Return non-zero if an entry was created.
          173  +*/
          174  +static int analyzeIndex(const char *zTab, const char *zIdx){
          175  +  sqlite3_int64 n = estEntryCount(zIdx);
          176  +  sqlite3_stmt *pStmt;
          177  +  sqlite3_uint64 *aCnt;
          178  +  sqlite3_value **apValue;
          179  +  int nCol = 0;
          180  +  int nByte;
          181  +  int i, j, k;
          182  +  int iLimit;
          183  +  int nRow = 0;
          184  +  char *zRes;
          185  +  int szRes;
          186  +  int rc;
          187  +
          188  +# define N_SPAN  5
          189  +  if( n==0 ) return 0;
          190  +  pStmt = db_prepare("PRAGMA index_xinfo=\"%w\"", zIdx);
          191  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          192  +    const char *zColl = (const char*)sqlite3_column_text(pStmt,4);
          193  +    if( sqlite3_stricmp(zColl,"binary")!=0 ){
          194  +      printf("-- cannot analyze index \"%s\" because column \"%s\" uses"
          195  +             " collating sequence \"%s\".\n",
          196  +             zIdx, sqlite3_column_text(pStmt, 2), zColl);
          197  +      sqlite3_finalize(pStmt);
          198  +      return 0;
          199  +    }
          200  +    if( sqlite3_column_int(pStmt, 5)==0 ) break;
          201  +    nCol++;
          202  +  }
          203  +  sqlite3_finalize(pStmt);
          204  +  if( nCol==0 ) return 0;
          205  +  nByte = (sizeof(aCnt[0]) + sizeof(apValue[0]))*nCol + 30*(nCol+1);
          206  +  aCnt = sqlite3_malloc( nByte );
          207  +  if( aCnt==0 ){
          208  +    runtimeError("out of memory");
          209  +  }
          210  +  memset(aCnt, 0, nByte);
          211  +  apValue = (sqlite3_value**)&aCnt[nCol];
          212  +  zRes = (char*)&apValue[nCol];
          213  +  szRes = 30*(nCol+1);
          214  +
          215  +  iLimit = n>10000 ? 100 : 20000;
          216  +  pStmt = db_prepare("PRAGMA btree_sample(\"%w\",0.0,%lld)",
          217  +                     zIdx, n*2);
          218  +  for(i=0; i<=N_SPAN; i++){
          219  +    k = 0;
          220  +    while( k<iLimit && (rc = sqlite3_step(pStmt))==SQLITE_ROW ){
          221  +      int iFirst;
          222  +      for(iFirst=0; iFirst<nCol; iFirst++){
          223  +        if( apValue[iFirst]==0 ) break;
          224  +        if( columnNotEqual(pStmt, iFirst, apValue[iFirst]) ) break;
          225  +      }
          226  +      for(j=iFirst; j<nCol; j++){
          227  +        aCnt[j]++;
          228  +        sqlite3_value_free(apValue[j]);
          229  +        apValue[j] = sqlite3_value_dup(sqlite3_column_value(pStmt,j));
          230  +      }
          231  +      if( k==0 && iFirst==nCol ){
          232  +        nRow += n/(N_SPAN+1) - iLimit;
          233  +      }
          234  +      nRow++;
          235  +      k++;
          236  +    }
          237  +    sqlite3_finalize(pStmt);
          238  +    if( rc!=SQLITE_ROW || i==N_SPAN-1 ) break;
          239  +    pStmt = db_prepare("PRAGMA btree_sample(\"%w\",%g,%lld)",
          240  +                       zIdx, ((double)i)/(double)N_SPAN, n*2);
          241  +  }  
          242  +  for(j=0; j<nCol; j++) sqlite3_value_free(apValue[j]);
          243  +  sqlite3_snprintf(szRes, zRes, "%lld", n);
          244  +  k = (int)strlen(zRes);
          245  +  for(j=0; j<nCol; j++){
          246  +    sqlite3_snprintf(szRes-k, zRes+k, " %d", (nRow+aCnt[j]-1)/aCnt[j]);
          247  +    k += (int)strlen(zRes+k);
          248  +  }
          249  +  printf("INSERT INTO sqlite_stat1 VALUES('%s','%s','%s');\n",
          250  +         zTab, zIdx, zRes);
          251  +  return 1;
          252  +}
          253  +
          254  +/*
          255  +** Stat1 for a table.
          256  +*/
          257  +static void analyzeTable(const char *zTab){
          258  +  sqlite3_int64 n = estEntryCount(zTab);
          259  +  sqlite3_stmt *pStmt;
          260  +  int nIndex = 0;
          261  +  int isWithoutRowid = 0;
          262  +  if( n==0 ){
          263  +    printf("-- empty table: %s\n", zTab);
          264  +    return;
          265  +  }
          266  +  if( analyzeIndex(zTab,zTab) ){
          267  +    isWithoutRowid = 1;
          268  +    nIndex++;
          269  +  }
          270  +  pStmt = db_prepare("PRAGMA index_list(\"%w\")", zTab);
          271  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          272  +    if( sqlite3_column_text(pStmt,3)[0]=='p' && isWithoutRowid ) continue;
          273  +    if( sqlite3_column_int(pStmt,4)==0 ) nIndex++;
          274  +    analyzeIndex(zTab, (const char*)sqlite3_column_text(pStmt,1));
          275  +  }
          276  +  sqlite3_finalize(pStmt);
          277  +  if( nIndex==0 ){
          278  +    printf("INSERT INTO sqlite_stat1 VALUES('%s',NULL,'%lld');\n", zTab, n);
          279  +  }
          280  +}
          281  +
          282  +/*
          283  +** Print sketchy documentation for this utility program
          284  +*/
          285  +static void showHelp(void){
          286  +  printf("Usage: %s [options] DBFILE\n", g.zArgv0);
          287  +  printf(
          288  +"Generate an approximate sqlite_stat1 table for the database in the DBFILE\n"
          289  +"file. Write the result to standard output.\n"
          290  +"Options:\n"
          291  +"  (none yet....)\n"
          292  +  );
          293  +}
          294  +
          295  +int main(int argc, char **argv){
          296  +  const char *zDb = 0;
          297  +  int i;
          298  +  int rc;
          299  +  char *zErrMsg = 0;
          300  +  sqlite3_stmt *pStmt;
          301  +  sqlite3_int64 iStart, iTotal;
          302  +
          303  +  g.zArgv0 = argv[0];
          304  +  sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
          305  +  iStart = currentTime();
          306  +  for(i=1; i<argc; i++){
          307  +    const char *z = argv[i];
          308  +    if( z[0]=='-' ){
          309  +      z++;
          310  +      if( z[0]=='-' ) z++;
          311  +      if( strcmp(z,"debug")==0 ){
          312  +        if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]);
          313  +        g.fDebug = strtol(argv[++i], 0, 0);
          314  +      }else
          315  +      if( strcmp(z,"help")==0 ){
          316  +        showHelp();
          317  +        return 0;
          318  +      }else
          319  +      {
          320  +        cmdlineError("unknown option: %s", argv[i]);
          321  +      }
          322  +    }else if( zDb==0 ){
          323  +      zDb = argv[i];
          324  +    }else{
          325  +      cmdlineError("unknown argument: %s", argv[i]);
          326  +    }
          327  +  }
          328  +  if( zDb==0 ){
          329  +    cmdlineError("database filename required");
          330  +  }
          331  +  rc = sqlite3_open_v2(zDb, &g.db, SQLITE_OPEN_READONLY, 0);
          332  +  if( rc ){
          333  +    cmdlineError("cannot open database file \"%s\"", zDb);
          334  +  }
          335  +  rc = sqlite3_exec(g.db, "SELECT * FROM sqlite_master", 0, 0, &zErrMsg);
          336  +  if( rc || zErrMsg ){
          337  +    cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb);
          338  +  }
          339  +  printf("ANALYZE sqlite_master;\nDELETE FROM sqlite_stat1;\n");
          340  +  pStmt = db_prepare("SELECT name FROM sqlite_master"
          341  +                     " WHERE type='table' AND rootpage>0"
          342  +                     "   AND name NOT LIKE 'sqlite_%%'"
          343  +                     " ORDER BY name");
          344  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          345  +    const char *zName = (const char*)sqlite3_column_text(pStmt, 0);
          346  +    analyzeTable(zName);
          347  +  }
          348  +  sqlite3_finalize(pStmt);
          349  +  printf("ANALYZE sqlite_master;\n");
          350  +  sqlite3_close(g.db);
          351  +  iTotal = currentTime() - iStart;
          352  +  printf("-- elapsed time: %lld.%03lld seconds\n", iTotal/1000, iTotal%1000);
          353  +  return 0;
          354  +}

Changes to tool/mkpragmatab.tcl.

   273    273     IF:   !defined(SQLITE_OMIT_INTEGRITY_CHECK)
   274    274   
   275    275     NAME: quick_check
   276    276     TYPE: INTEGRITY_CHECK
   277    277     FLAG: NeedSchema
   278    278     IF:   !defined(SQLITE_OMIT_INTEGRITY_CHECK)
   279    279   
          280  +  NAME: est_count
          281  +  FLAG: NeedSchema
          282  +
          283  +  NAME: btree_sample
          284  +  FLAG: NeedSchema
          285  +
   280    286     NAME: encoding
   281    287     FLAG: Result0
   282    288     IF:   !defined(SQLITE_OMIT_UTF16)
   283    289   
   284    290     NAME: schema_version
   285    291     TYPE: HEADER_VALUE
   286    292     ARG:  BTREE_SCHEMA_VERSION