aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/build.c99
-rw-r--r--src/expr.c6
-rw-r--r--src/pragma.c8
-rw-r--r--src/sqliteInt.h5
4 files changed, 85 insertions, 33 deletions
diff --git a/src/build.c b/src/build.c
index 83ce948dc..245961808 100644
--- a/src/build.c
+++ b/src/build.c
@@ -1026,6 +1026,7 @@ void sqlite3AddColumn(Parse *pParse, Token *pName){
** be called next to set pCol->affinity correctly.
*/
pCol->affinity = SQLITE_AFF_NONE;
+ pCol->szEst = 1;
p->nCol++;
}
@@ -1067,15 +1068,18 @@ void sqlite3AddNotNull(Parse *pParse, int onError){
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
-char sqlite3AffinityType(const char *zIn){
+char sqlite3AffinityType(const char *zIn, u8 *pszEst){
u32 h = 0;
char aff = SQLITE_AFF_NUMERIC;
+ const char *zChar;
- if( zIn ) while( zIn[0] ){
+ if( zIn==0 ) return aff;
+ while( zIn[0] ){
h = (h<<8) + sqlite3UpperToLower[(*zIn)&0xff];
zIn++;
if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){ /* CHAR */
- aff = SQLITE_AFF_TEXT;
+ aff = SQLITE_AFF_TEXT;
+ zChar = zIn;
}else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){ /* CLOB */
aff = SQLITE_AFF_TEXT;
}else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){ /* TEXT */
@@ -1099,7 +1103,25 @@ char sqlite3AffinityType(const char *zIn){
break;
}
}
-
+ if( pszEst ){
+ if( aff>=SQLITE_AFF_NUMERIC ){
+ *pszEst = 1;
+ }else if( zChar ){
+ *pszEst = 1;
+ while( zChar[0] ){
+ int v;
+ if( sqlite3Isdigit(zChar[0]) && sqlite3GetInt32(zChar, &v) ){
+ v = v/4 + 1;
+ if( v>255 ) v = 255;
+ *pszEst = v;
+ break;
+ }
+ zChar++;
+ }
+ }else{
+ *pszEst = 3;
+ }
+ }
return aff;
}
@@ -1121,7 +1143,7 @@ void sqlite3AddColumnType(Parse *pParse, Token *pType){
pCol = &p->aCol[p->nCol-1];
assert( pCol->zType==0 );
pCol->zType = sqlite3NameFromToken(pParse->db, pType);
- pCol->affinity = sqlite3AffinityType(pCol->zType);
+ pCol->affinity = sqlite3AffinityType(pCol->zType, &pCol->szEst);
}
/*
@@ -1469,7 +1491,7 @@ static char *createTableStmt(sqlite3 *db, Table *p){
zType = azType[pCol->affinity - SQLITE_AFF_TEXT];
len = sqlite3Strlen30(zType);
assert( pCol->affinity==SQLITE_AFF_NONE
- || pCol->affinity==sqlite3AffinityType(zType) );
+ || pCol->affinity==sqlite3AffinityType(zType, 0) );
memcpy(&zStmt[k], zType, len);
k += len;
assert( k<=n );
@@ -1479,6 +1501,39 @@ static char *createTableStmt(sqlite3 *db, Table *p){
}
/*
+** Estimate the total row width for a table.
+*/
+static unsigned estimatedTableWidth(const Table *pTab){
+ unsigned wTable = 0;
+ const Column *pTabCol;
+ int i;
+ for(i=pTab->nCol, pTabCol=pTab->aCol; i>0; i--, pTabCol++){
+ wTable += pTabCol->szEst;
+ }
+ if( pTab->iPKey<0 ) wTable++;
+ return wTable;
+}
+
+/*
+** Set the iScanRatio for an index based on estimates of the average
+** table row width and average index row width. Estimates are derived
+** from the declared datatypes of the various columns.
+*/
+static void setIndexScanRatio(Index *pIdx, unsigned wTable){
+ unsigned wIndex = 1;
+ int i;
+ const Column *aCol = pIdx->pTable->aCol;
+ for(i=0; i<pIdx->nColumn; i++){
+ assert( pIdx->aiColumn[i]>=0 && pIdx->aiColumn[i]<pIdx->pTable->nCol );
+ wIndex += aCol[pIdx->aiColumn[i]].szEst;
+ }
+ assert( 100*wIndex/wTable <= 255 );
+ pIdx->iScanRatio = (u8)(128*wIndex/wTable);
+ /* printf("%s: wIndex=%d wTable=%d ratio=%d\n",
+ ** pIdx->zName, wIndex, wTable, (100*pIdx->iScanRatio)/128); */
+}
+
+/*
** This routine is called to report the final ")" that terminates
** a CREATE TABLE statement.
**
@@ -1504,9 +1559,11 @@ void sqlite3EndTable(
Token *pEnd, /* The final ')' token in the CREATE TABLE */
Select *pSelect /* Select from a "CREATE ... AS SELECT" */
){
- Table *p;
- sqlite3 *db = pParse->db;
- int iDb;
+ Table *p; /* The new table */
+ sqlite3 *db = pParse->db; /* The database connection */
+ int iDb; /* Database in which the table lives */
+ Index *pIdx; /* An implied index of the table */
+ unsigned wTable; /* Estimated average width of a row in the table */
if( (pEnd==0 && pSelect==0) || db->mallocFailed ){
return;
@@ -1526,6 +1583,12 @@ void sqlite3EndTable(
}
#endif /* !defined(SQLITE_OMIT_CHECK) */
+ /* Compute the iScanRatio of implied indices */
+ wTable = estimatedTableWidth(p);
+ for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
+ setIndexScanRatio(pIdx, wTable);
+ }
+
/* If the db->init.busy is 1 it means we are reading the SQL off the
** "sqlite_master" or "sqlite_temp_master" table on the disk.
** So do not write to the disk again. Extract the root page number
@@ -2443,15 +2506,6 @@ static void sqlite3RefillIndex(Parse *pParse, Index *pIndex, int memRootPage){
}
/*
-** Estimate the average width of a table column based on its affinity type.
-*/
-static unsigned estimatedColumnWidth(const Column *p){
- if( p->affinity>=SQLITE_AFF_NUMERIC ) return 1;
- if( p->affinity==SQLITE_AFF_TEXT ) return 3;
- return 2;
-}
-
-/*
** Create a new index for an SQL table. pName1.pName2 is the name of the index
** and pTblList is the name of the table that is to be indexed. Both will
** be NULL for a primary key or an index that is created to satisfy a
@@ -2493,8 +2547,6 @@ Index *sqlite3CreateIndex(
int iDb; /* Index of the database that is being written */
Token *pName = 0; /* Unqualified name of the index to create */
struct ExprList_item *pListItem; /* For looping over pList */
- unsigned wTable = 0; /* Approximate "width" of the table */
- unsigned wIndex = 0; /* Approximate "width" of this index */
const Column *pTabCol; /* A column in the table */
int nCol; /* Number of columns */
int nExtra = 0; /* Space allocated for zExtra[] */
@@ -2738,7 +2790,6 @@ Index *sqlite3CreateIndex(
goto exit_create_index;
}
pIndex->aiColumn[i] = j;
- wIndex += estimatedColumnWidth(pTabCol);
if( pListItem->pExpr ){
int nColl;
assert( pListItem->pExpr->op==TK_COLLATE );
@@ -2762,11 +2813,9 @@ Index *sqlite3CreateIndex(
if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
}
sqlite3DefaultRowEst(pIndex);
- for(j=pTab->nCol, pTabCol=pTab->aCol; j>0; j--, pTabCol++){
- wTable += estimatedColumnWidth(pTabCol);
+ if( pParse->pNewTable==0 ){
+ setIndexScanRatio(pIndex, estimatedTableWidth(pTab));
}
- assert( 100*wIndex/wTable <= 255 );
- pIndex->iScanRatio = (u8)(128*wIndex/wTable);
if( pTab==pParse->pNewTable ){
/* This routine has been called to create an automatic index as a
diff --git a/src/expr.c b/src/expr.c
index b8b0eb731..eb2f54563 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -41,7 +41,7 @@ char sqlite3ExprAffinity(Expr *pExpr){
#ifndef SQLITE_OMIT_CAST
if( op==TK_CAST ){
assert( !ExprHasProperty(pExpr, EP_IntValue) );
- return sqlite3AffinityType(pExpr->u.zToken);
+ return sqlite3AffinityType(pExpr->u.zToken, 0);
}
#endif
if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER)
@@ -2461,7 +2461,7 @@ int sqlite3ExprCodeTarget(Parse *pParse, Expr *pExpr, int target){
int aff, to_op;
inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
assert( !ExprHasProperty(pExpr, EP_IntValue) );
- aff = sqlite3AffinityType(pExpr->u.zToken);
+ aff = sqlite3AffinityType(pExpr->u.zToken, 0);
to_op = aff - SQLITE_AFF_TEXT + OP_ToText;
assert( to_op==OP_ToText || aff!=SQLITE_AFF_TEXT );
assert( to_op==OP_ToBlob || aff!=SQLITE_AFF_NONE );
@@ -3128,7 +3128,7 @@ void sqlite3ExplainExpr(Vdbe *pOut, Expr *pExpr){
case TK_CAST: {
/* Expressions of the form: CAST(pLeft AS token) */
const char *zAff = "unk";
- switch( sqlite3AffinityType(pExpr->u.zToken) ){
+ switch( sqlite3AffinityType(pExpr->u.zToken, 0) ){
case SQLITE_AFF_TEXT: zAff = "TEXT"; break;
case SQLITE_AFF_NONE: zAff = "NONE"; break;
case SQLITE_AFF_NUMERIC: zAff = "NUMERIC"; break;
diff --git a/src/pragma.c b/src/pragma.c
index f533b71c2..a0f49b908 100644
--- a/src/pragma.c
+++ b/src/pragma.c
@@ -1455,17 +1455,19 @@ void sqlite3Pragma(
pIdx = pTab->pIndex;
if( pIdx ){
int i = 0;
- sqlite3VdbeSetNumCols(v, 3);
- pParse->nMem = 3;
+ sqlite3VdbeSetNumCols(v, 4);
+ pParse->nMem = 4;
sqlite3CodeVerifySchema(pParse, iDb);
sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC);
sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC);
sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC);
+ sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "r", SQLITE_STATIC);
while(pIdx){
sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3);
- sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3);
+ sqlite3VdbeAddOp2(v, OP_Integer, pIdx->iScanRatio*100/128, 4);
+ sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
++i;
pIdx = pIdx->pNext;
}
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 2fb5c4063..5acfe38c5 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1197,7 +1197,8 @@ struct Column {
char *zColl; /* Collating sequence. If NULL, use the default */
u8 notNull; /* An OE_ code for handling a NOT NULL constraint */
char affinity; /* One of the SQLITE_AFF_... values */
- u16 colFlags; /* Boolean properties. See COLFLAG_ defines below */
+ u8 szEst; /* Estimated size of this column. INT==1 */
+ u8 colFlags; /* Boolean properties. See COLFLAG_ defines below */
};
/* Allowed values for Column.colFlags:
@@ -3082,7 +3083,7 @@ void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
-char sqlite3AffinityType(const char*);
+char sqlite3AffinityType(const char*, u8*);
void sqlite3Analyze(Parse*, Token*, Token*);
int sqlite3InvokeBusyHandler(BusyHandler*);
int sqlite3FindDb(sqlite3*, Token*);
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-22 13:38:25 +0000