Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch normalize_v4 Excluding Merge-Ins
This is equivalent to a diff from 53d3b169d8 to 73a6b8c1b9
2018-12-07
| ||
01:56 | Fix the sqlite3ExprDup() function so that it correctly duplicates the Window object list on a Select that contains window functions. Fix for ticket [f09fcd17810f65f717]. (check-in: db5ed2268e user: drh tags: trunk) | |
2018-12-06
| ||
20:18 | Experimental changes to (optionally) allow double-quoted strings to be checked against known identifiers. (Leaf check-in: 73a6b8c1b9 user: mistachkin tags: normalize_v4) | |
19:15 | Simplify the query flattener so that it does not duplicate the WHERE clause of subquery that is being incorporated into the outer query - copies it directly. This is more efficient. And it also fixes the specific test case show for ticket [f09fcd17810f65f71789525] but it does not resolve the more general problem that sqlite3ExprDup() does not correctly duplicate expressions that contain subqueries with window functions. (check-in: f1b18d44ff user: drh tags: ticket-f09fcd17810f) | |
17:06 | When masking bits off of sqlite3.flags, make sure the mask is 64 bits in size so as not to accidentally mask of high-order bits. (check-in: 53d3b169d8 user: drh tags: trunk) | |
16:50 | When saving off the value of sqlite3.flags, take care to preserve all 64 bits. (check-in: 9c6dbcfab5 user: drh tags: trunk) | |
02:01 | Remove the unused pColHash field from the Table object. (check-in: 3a2c047989 user: drh tags: trunk) | |
2018-12-05
| ||
23:45 | The sqlite3_normalized_sql() interface should not be transforming quoted identifier names into wildcards. Fix this, and at the same time simplify the code substantially. (check-in: e8540377ec user: drh tags: trunk) | |
Changes to src/build.c.
︙ | ︙ | |||
632 633 634 635 636 637 638 639 640 641 642 643 644 645 | } /* Delete any foreign keys attached to this table. */ sqlite3FkDelete(db, pTable); /* Delete the Table structure itself. */ sqlite3DeleteColumnNames(db, pTable); sqlite3DbFree(db, pTable->zName); sqlite3DbFree(db, pTable->zColAff); sqlite3SelectDelete(db, pTable->pSelect); sqlite3ExprListDelete(db, pTable->pCheck); #ifndef SQLITE_OMIT_VIRTUALTABLE sqlite3VtabClear(db, pTable); | > > > > > > | 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 | } /* Delete any foreign keys attached to this table. */ sqlite3FkDelete(db, pTable); /* Delete the Table structure itself. */ #ifdef SQLITE_ENABLE_NORMALIZE if( pTable->pColHash ){ sqlite3HashClear(pTable->pColHash); sqlite3_free(pTable->pColHash); } #endif sqlite3DeleteColumnNames(db, pTable); sqlite3DbFree(db, pTable->zName); sqlite3DbFree(db, pTable->zColAff); sqlite3SelectDelete(db, pTable->pSelect); sqlite3ExprListDelete(db, pTable->pCheck); #ifndef SQLITE_OMIT_VIRTUALTABLE sqlite3VtabClear(db, pTable); |
︙ | ︙ |
Changes to src/prepare.c.
︙ | ︙ | |||
706 707 708 709 710 711 712 713 714 715 716 717 718 719 | rc = sqlite3ApiExit(db, rc); assert( (rc&db->errMask)==rc ); sqlite3_mutex_leave(db->mutex); return rc; } #ifdef SQLITE_ENABLE_NORMALIZE /* ** Attempt to estimate the final output buffer size needed for the fully ** normalized version of the specified SQL string. This should take into ** account any potential expansion that could occur (e.g. via IN clauses ** being expanded, etc). This size returned is the total number of bytes ** including the NUL terminator. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 | rc = sqlite3ApiExit(db, rc); assert( (rc&db->errMask)==rc ); sqlite3_mutex_leave(db->mutex); return rc; } #ifdef SQLITE_ENABLE_NORMALIZE /* ** Checks if the specified token is a table, column, or function name, ** based on the databases associated with the statement being prepared. ** If the function fails, zero is returned and pRc is filled with the ** error code. */ static int shouldTreatAsIdentifier( sqlite3 *db, /* Database handle. */ const char *zToken, /* Pointer to start of token to be checked */ int nToken, /* Length of token to be checked */ int *pRc /* Pointer to error code upon failure */ ){ int bFound = 0; /* Non-zero if token is an identifier name. */ int i, j; /* Database and column loop indexes. */ Schema *pSchema; /* Schema for current database. */ Hash *pHash; /* Hash table of tables for current database. */ HashElem *e; /* Hash element for hash table iteration. */ Table *pTab; /* Database table for columns being checked. */ char *zId; /* Zero terminated name of the identifier */ char zSpace[65]; /* Static space for the zero-terminated name */ if( nToken<sizeof(zSpace) ){ memcpy(zSpace, zToken, nToken); zSpace[nToken] = 0; zId = zSpace; }else{ zId = sqlite3DbStrNDup(db, zToken, nToken); if( zId==0 ){ *pRc = SQLITE_NOMEM_BKPT; return 0; } } if( sqlite3IsRowid(zId) ){ bFound = 1; goto done1; } if( nToken>0 ){ int hash = SQLITE_FUNC_HASH(sqlite3UpperToLower[(u8)zToken[0]], nToken); if( sqlite3FunctionSearch(hash, zId) ){ bFound = 1; goto done1; } } assert( db!=0 ); sqlite3_mutex_enter(db->mutex); sqlite3BtreeEnterAll(db); for(i=0; i<db->nDb; i++){ pHash = &db->aFunc; if( sqlite3HashFind(pHash, zId) ){ bFound = 1; break; } pSchema = db->aDb[i].pSchema; if( pSchema==0 ) continue; pHash = &pSchema->tblHash; if( sqlite3HashFind(pHash, zId) ){ bFound = 1; break; } for(e=sqliteHashFirst(pHash); e; e=sqliteHashNext(e)){ pTab = sqliteHashData(e); if( pTab==0 ) continue; pHash = pTab->pColHash; if( pHash==0 ){ pTab->pColHash = pHash = sqlite3_malloc(sizeof(Hash)); if( pHash ){ sqlite3HashInit(pHash); for(j=0; j<pTab->nCol; j++){ Column *pCol = &pTab->aCol[j]; sqlite3HashInsert(pHash, pCol->zName, pCol); } }else{ *pRc = SQLITE_NOMEM_BKPT; bFound = 0; goto done2; } } if( pHash && sqlite3HashFind(pHash, zId) ){ bFound = 1; goto done2; } } } done2: sqlite3BtreeLeaveAll(db); sqlite3_mutex_leave(db->mutex); done1: if( zId!=zSpace ) sqlite3DbFree(db, zId); return bFound; } /* ** Attempt to estimate the final output buffer size needed for the fully ** normalized version of the specified SQL string. This should take into ** account any potential expansion that could occur (e.g. via IN clauses ** being expanded, etc). This size returned is the total number of bytes ** including the NUL terminator. |
︙ | ︙ | |||
778 779 780 781 782 783 784 | ** Compute a normalization of the SQL given by zSql[0..nSql-1]. Return ** the normalization in space obtained from sqlite3DbMalloc(). Or return ** NULL if anything goes wrong or if zSql is NULL. */ char *sqlite3Normalize( Vdbe *pVdbe, /* VM being reprepared */ const char *zSql, /* The original SQL string */ | | > | 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 | ** Compute a normalization of the SQL given by zSql[0..nSql-1]. Return ** the normalization in space obtained from sqlite3DbMalloc(). Or return ** NULL if anything goes wrong or if zSql is NULL. */ char *sqlite3Normalize( Vdbe *pVdbe, /* VM being reprepared */ const char *zSql, /* The original SQL string */ int nSql, /* Size of the input string in bytes */ u8 prepFlags /* The flags passed to sqlite3_prepare_v3() */ ){ sqlite3 *db; /* Database handle. */ char *z; /* The output string */ int nZ; /* Size of the output string in bytes */ int i; /* Next character to read from zSql[] */ int j; /* Next character to fill in on z[] */ int tokenType = 0; /* Type of the next token */ |
︙ | ︙ | |||
881 882 883 884 885 886 887 888 889 890 891 892 893 894 | if( j>0 && sqlite3IsIdChar(z[j-1]) && sqlite3IsIdChar(zSql[i]) ){ z[j++] = ' '; } if( tokenType==TK_ID ){ int i2 = i, n2 = n; if( nParen==nParenAtIN ) iStartIN = 0; if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; } } copyNormalizedToken(zSql, i, n, flags, z, &j); break; } } } assert( j<nZ && "one" ); | > > > > > > > > > > | 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 | if( j>0 && sqlite3IsIdChar(z[j-1]) && sqlite3IsIdChar(zSql[i]) ){ z[j++] = ' '; } if( tokenType==TK_ID ){ int i2 = i, n2 = n; if( nParen==nParenAtIN ) iStartIN = 0; if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; } if( (prepFlags & SQLITE_PREPARE_CHKIDENTS)!=0 ){ int rc = SQLITE_OK; if( shouldTreatAsIdentifier(db, zSql+i2, n2, &rc)==0 ){ if( rc!=SQLITE_OK ) goto normalizeError; if( sqlite3_keyword_check(zSql+i2, n2)==0 ){ z[j++] = '?'; break; } } } } copyNormalizedToken(zSql, i, n, flags, z, &j); break; } } } assert( j<nZ && "one" ); |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
3637 3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 3649 3650 | ** normalize a SQL statement are unspecified and subject to change. ** At a minimum, literal values will be replaced with suitable ** placeholders. ** </dl> */ #define SQLITE_PREPARE_PERSISTENT 0x01 #define SQLITE_PREPARE_NORMALIZE 0x02 /* ** CAPI3REF: Compiling An SQL Statement ** KEYWORDS: {SQL statement compiler} ** METHOD: sqlite3 ** CONSTRUCTOR: sqlite3_stmt ** | > | 3637 3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 3649 3650 3651 | ** normalize a SQL statement are unspecified and subject to change. ** At a minimum, literal values will be replaced with suitable ** placeholders. ** </dl> */ #define SQLITE_PREPARE_PERSISTENT 0x01 #define SQLITE_PREPARE_NORMALIZE 0x02 #define SQLITE_PREPARE_CHKIDENTS 0x04 /* ** CAPI3REF: Compiling An SQL Statement ** KEYWORDS: {SQL statement compiler} ** METHOD: sqlite3 ** CONSTRUCTOR: sqlite3_stmt ** |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 | /* ** The schema for each SQL table and view is represented in memory ** by an instance of the following structure. */ struct Table { char *zName; /* Name of the table or view */ Column *aCol; /* Information about each column */ Index *pIndex; /* List of SQL indexes on this table. */ Select *pSelect; /* NULL for tables. Points to definition if a view. */ FKey *pFKey; /* Linked list of all foreign keys in this table */ char *zColAff; /* String defining the affinity of each column */ ExprList *pCheck; /* All CHECK constraints */ /* ... also used as column name list in a VIEW */ int tnum; /* Root BTree page for this table */ | > > > | 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 | /* ** The schema for each SQL table and view is represented in memory ** by an instance of the following structure. */ struct Table { char *zName; /* Name of the table or view */ Column *aCol; /* Information about each column */ #ifdef SQLITE_ENABLE_NORMALIZE Hash *pColHash; /* All columns indexed by name */ #endif Index *pIndex; /* List of SQL indexes on this table. */ Select *pSelect; /* NULL for tables. Points to definition if a view. */ FKey *pFKey; /* Linked list of all foreign keys in this table */ char *zColAff; /* String defining the affinity of each column */ ExprList *pCheck; /* All CHECK constraints */ /* ... also used as column name list in a VIEW */ int tnum; /* Root BTree page for this table */ |
︙ | ︙ | |||
4412 4413 4414 4415 4416 4417 4418 | int sqlite3VtabBegin(sqlite3 *, VTable *); FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*); sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*); int sqlite3VdbeParameterIndex(Vdbe*, const char*, int); int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *); void sqlite3ParserReset(Parse*); #ifdef SQLITE_ENABLE_NORMALIZE | | | 4415 4416 4417 4418 4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 | int sqlite3VtabBegin(sqlite3 *, VTable *); FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*); sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*); int sqlite3VdbeParameterIndex(Vdbe*, const char*, int); int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *); void sqlite3ParserReset(Parse*); #ifdef SQLITE_ENABLE_NORMALIZE char *sqlite3Normalize(Vdbe*, const char*, int, u8); #endif int sqlite3Reprepare(Vdbe*); void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*); CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *); int sqlite3TempInMemory(const sqlite3*); const char *sqlite3JournalModename(int); #ifndef SQLITE_OMIT_WAL |
︙ | ︙ |
Changes to src/vdbeapi.c.
︙ | ︙ | |||
1711 1712 1713 1714 1715 1716 1717 | ** Return the normalized SQL associated with a prepared statement. */ const char *sqlite3_normalized_sql(sqlite3_stmt *pStmt){ Vdbe *p = (Vdbe *)pStmt; if( p==0 ) return 0; if( p->zNormSql==0 && p->zSql!=0 ){ sqlite3_mutex_enter(p->db->mutex); | | | 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 | ** Return the normalized SQL associated with a prepared statement. */ const char *sqlite3_normalized_sql(sqlite3_stmt *pStmt){ Vdbe *p = (Vdbe *)pStmt; if( p==0 ) return 0; if( p->zNormSql==0 && p->zSql!=0 ){ sqlite3_mutex_enter(p->db->mutex); p->zNormSql = sqlite3Normalize(p, p->zSql, sqlite3Strlen30(p->zSql), 0); sqlite3_mutex_leave(p->db->mutex); } return p->zNormSql; } #endif /* SQLITE_ENABLE_NORMALIZE */ #ifdef SQLITE_ENABLE_PREUPDATE_HOOK |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
63 64 65 66 67 68 69 | p->expmask = 0; } assert( p->zSql==0 ); p->zSql = sqlite3DbStrNDup(p->db, z, n); #ifdef SQLITE_ENABLE_NORMALIZE assert( p->zNormSql==0 ); if( p->zSql && (prepFlags & SQLITE_PREPARE_NORMALIZE)!=0 ){ | | | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | p->expmask = 0; } assert( p->zSql==0 ); p->zSql = sqlite3DbStrNDup(p->db, z, n); #ifdef SQLITE_ENABLE_NORMALIZE assert( p->zNormSql==0 ); if( p->zSql && (prepFlags & SQLITE_PREPARE_NORMALIZE)!=0 ){ p->zNormSql = sqlite3Normalize(p, p->zSql, n, prepFlags); assert( p->zNormSql!=0 || p->db->mallocFailed ); } #endif } /* ** Swap all content between two VDBE structures. |
︙ | ︙ |
Changes to test/normalize.test.
︙ | ︙ | |||
205 206 207 208 209 210 211 212 213 214 215 216 217 218 | {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}} 430 {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');} 0x2 {0 {SELECT"a"FROM t1 WHERE"x"IN("1","2",?);}} 440 {SELECT 'a' FROM t1 WHERE 'x';} 0x2 {0 {SELECT?FROM t1 WHERE?;}} 450 {SELECT [a] FROM t1 WHERE [x];} | > > > > > | 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 | {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}} 430 {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');} 0x2 {0 {SELECT"a"FROM t1 WHERE"x"IN("1","2",?);}} 431 {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');} 0x6 {0 {SELECT"a"FROM t1 WHERE"x"IN(?,?,?);}} 440 {SELECT 'a' FROM t1 WHERE 'x';} 0x2 {0 {SELECT?FROM t1 WHERE?;}} 450 {SELECT [a] FROM t1 WHERE [x];} |
︙ | ︙ |