diff options
| -rw-r--r-- | main.mk | 4 | ||||
| -rw-r--r-- | manifest | 44 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/build.c | 31 | ||||
| -rw-r--r-- | src/expr.c | 234 | ||||
| -rw-r--r-- | src/main.c | 12 | ||||
| -rw-r--r-- | src/parse.y | 92 | ||||
| -rw-r--r-- | src/select.c | 50 | ||||
| -rw-r--r-- | src/sqliteInt.h | 39 | ||||
| -rw-r--r-- | src/tokenize.c | 4 | ||||
| -rw-r--r-- | src/trigger.c | 117 | ||||
| -rw-r--r-- | test/all.test | 3 | ||||
| -rw-r--r-- | test/quick.test | 3 | ||||
| -rw-r--r-- | test/view.test | 31 | ||||
| -rw-r--r-- | www/c_interface.tcl | 12 | ||||
| -rw-r--r-- | www/faq.tcl | 7 | ||||
| -rw-r--r-- | www/omitted.tcl | 16 | ||||
| -rw-r--r-- | www/speed.tcl | 159 |
18 files changed, 516 insertions, 344 deletions
@@ -126,8 +126,8 @@ last_change: $(SRC) cat $(SRC) | grep '$$Id: ' | sort +4 | tail -1 \ | awk '{print $$5,$$6}' >last_change -libsqlite.a: $(LIBOBJ) tclsqlite.o - $(AR) libsqlite.a $(LIBOBJ) tclsqlite.o +libsqlite.a: $(LIBOBJ) + $(AR) libsqlite.a $(LIBOBJ) $(RANLIB) libsqlite.a sqlite$(EXE): $(TOP)/src/shell.c libsqlite.a sqlite.h @@ -1,5 +1,5 @@ -C Fix\sfor\sticket\s#138:\sMakefile\sdoesn't\suse\sexec_prefix,\shas\ssome\sinstall\sproblems\s(CVS\s724) -D 2002-08-22T18:18:36 +C Change\sthe\sway\stoken\smemory\sis\sallocated\sin\san\seffort\sto\sfix\sticket\s#136.\nThere\sis\snow\sa\smemory\sleak\swhen\susing\sviews\sof\sviews.\s(CVS\s725) +D 2002-08-24T18:24:52 F Makefile.in bcb81f40d9a17bd94f59e67157b1e1c54c046c2b F Makefile.linux-gcc b86a99c493a5bfb402d1d9178dcdc4bd4b32f906 F README f1de682fbbd94899d50aca13d387d1b3fd3be2dd @@ -14,48 +14,48 @@ F doc/report1.txt a031aaf37b185e4fa540223cb516d3bccec7eeac F install-sh 9d4de14ab9fb0facae2f48780b874848cbf2f895 F libtool c56e618713c9510a103bda6b95f3ea3900dcacd6 F ltmain.sh e9ed72eb1d690f447c13945eaf69e28af531eda1 -F main.mk 6116e91c7f747e9a881f8f94a7ffd17489039ddb +F main.mk 8e34134476c039c89bb404f2712bcbb2f044bdfe F publish.sh a7a8d23e6525bd25d4f5ba9b0fc6edc107d94050 F spec.template 238f7db425a78dc1bb7682e56e3834c7270a3f5e F sqlite.1 83f4a9d37bdf2b7ef079a82d54eaf2e3509ee6ea F src/btree.c 9e21606581a5a4a5b18ad304d7a4f433101f1538 F src/btree.h 0ca6c2631338df62e4f7894252d9347ae234eda9 -F src/build.c d8ce4507c12ddcfd4f678b0b97979cd568d4bfd4 +F src/build.c b367b4a839f978c0225d984e327287852835948e F src/delete.c c9f59ee217e062eb9de7b64b76b5cfff42b2f028 F src/encode.c 346b12b46148506c32038524b95c4631ab46d760 -F src/expr.c 8a6b669ba5d6cd2810e8671f918ddb0fac3dd1b1 +F src/expr.c ee027b908a1e157fc21644121811fa6ec1eec798 F src/func.c e45cd908b9b723d9b91473d09e12c23f786b3fc2 F src/hash.c 6a6236b89c8c060c65dabd300a1c8ce7c10edb72 F src/hash.h cd0433998bc1a3759d244e1637fe5a3c13b53bf8 F src/insert.c 8aefc998c86a3bd53082e2f8fdd049345fcf3463 -F src/main.c c7e313ef70d7e4339a6d8ba1deb31f7b51a3473d +F src/main.c 9f2633cb20cb9cc740353f57178450319c12b743 F src/md5.c 0ae1f3e2cac92d06fc6246d1b4b8f61a2fe66d3b F src/os.c 00d10655e1dc9a52b4aabca58c8d8e45048057b0 F src/os.h 3009379b06941e7796a9812d1b6cbc59b26248c8 F src/pager.c 4b0169e91b34f6ff91e8feb57545c43e4d6eb370 F src/pager.h 6991c9c2dc5e4c7f2df4d4ba47d1c6458f763a32 -F src/parse.y 5de87bb0f5cd0245471483b9c8bf26df6a68979f +F src/parse.y 1b180e14b6346e323bd4279469748716f412cc1c F src/printf.c 5c50fc1da75c8f5bf432b1ad17d91d6653acd167 F src/random.c 19e8e00fe0df32a742f115773f57651be327cabe -F src/select.c f504cc542229f472b3f15cefe5d6782494ee8d92 +F src/select.c 7aa3c3784452ca8f154ae9b3b250ccedc1633354 F src/shell.c 9e9a6eb6bca07f01e6472a603f908a0127ea50ff F src/shell.tcl 27ecbd63dd88396ad16d81ab44f73e6c0ea9d20e F src/sqlite.h.in d3999a9c6374675779058d6cfe5431131618e92b -F src/sqliteInt.h b40ef4cdcfc98e2e9417e73b896c8b2bfec545eb +F src/sqliteInt.h 5bb95f64a2f86b2d14a66e35edba4a2564a6ecd7 F src/table.c eed2098c9b577aa17f8abe89313a9c4413f57d63 F src/tclsqlite.c c502819c209011659e1bbb428cbac5670cce7f79 F src/test1.c 456cb080db85056be723e770435d9509afc3a83a F src/test2.c 279057a854359665b89122070ac1fc472acce1b2 F src/test3.c b99d5ab68ee672f1fbb00520723b5c21bac35822 F src/threadtest.c 72bce0a284647314847bbea44616ceb056bfb77f -F src/tokenize.c b5500e193a82b5b9888fbf947efd90d3b4858178 -F src/trigger.c d88ab4d68d68955c217b38fb6717e090fbbf54a4 +F src/tokenize.c 8bd6251e5237c9a16d0bbfb9894925eb129985fa +F src/trigger.c cc8c6769c2ca37166490ed2b305986268faa3bf8 F src/update.c f07e6ed2c517c92871e54d3f5886d1cf56121b11 F src/util.c bdbf0aedcec21ede2248126bbbe734bcc070b7c8 F src/vdbe.c 5b3bb8ac3bb8dd777abd9fae64a293bfdcc13c54 F src/vdbe.h a9292f2b5fcecef924fa255fb74609e9cbc776c2 F src/where.c ce42cce65d7bf42341627f3fb0a17f69fea6a4f4 -F test/all.test 9a6eb262393f74cb7fb09b17156491a34b941fe3 +F test/all.test efd958d048c70a3247997c482f0b33561f7759f0 F test/bigrow.test 8ab252dba108f12ad64e337b0f2ff31a807ac578 F test/btree.test bf326f546a666617367a7033fa2c07451bd4f8e1 F test/btree2.test e3b81ec33dc2f89b3e6087436dfe605b870c9080 @@ -84,7 +84,7 @@ F test/null.test 5c2b57307e4b6178aae825eb65ddbee01e76b0fd F test/pager.test b0c0d00cd5dce0ce21f16926956b195c0ab5044c F test/pragma.test 0b9675ef1f5ba5b43abfa337744445fc5b01a34a F test/printf.test a29b8afa24edb4411adfe473b12ac32c84098fce -F test/quick.test 21f710471a6eb9b2513d8fe4abd07bdb874f23bb +F test/quick.test b372c8dad4fa1554747e90683fc72e59c0c98502 F test/quote.test 08f23385c685d3dc7914ec760d492cacea7f6e3d F test/rowid.test 4c55943300cddf73dd0f88d40a268cab14c83274 F test/select1.test 0d708cec567104653ec9aa49fecf3444a2e7d150 @@ -109,7 +109,7 @@ F test/unique.test 572aa791327c1e8d797932263e9d67f176cfdb44 F test/update.test 7ffb062d580a972e7870d0f51d5af3ab9bfeae08 F test/vacuum.test 059871b312eb910bbe49dafde1d01490cc2c6bbe F test/version.test c7057526e14c7e3da5718b88e7f566f4182fd5c5 -F test/view.test 3afca084dab44c7a5772d3c6a326adf93ad52050 +F test/view.test e4d60d68ea3965fe0cc8fa83ba9aa42e23199801 F test/where.test c7aba40ad9178acf9c898e53aac9e447e2d2f2f7 F tool/diffdb.c 7524b1b5df217c20cd0431f6789851a4e0cb191b F tool/lemon.c 022adc2830c2705828f744d2c59798bd462eb465 @@ -127,27 +127,27 @@ F www/arch.fig d5f9752a4dbf242e9cfffffd3f5762b6c63b3bcf F www/arch.png 82ef36db1143828a7abc88b1e308a5f55d4336f4 F www/arch.tcl 679a0c48817f71bc91d5911ef386e5ef35d4f178 F www/audit.tcl 90e09d580f79c7efec0c7d6f447b7ec5c2dce5c0 -F www/c_interface.tcl 70548ff5f73c6adcdb7aeced929ebb30a99f5807 +F www/c_interface.tcl e76c9fd609326c34cd45cd040b508b0e21908800 F www/changes.tcl 7326bd48555132ca7f21a0dec84dacea76eacc65 F www/conflict.tcl 81dd21f9a679e60aae049e9dd8ab53d59570cda2 F www/crosscompile.tcl 3622ebbe518927a3854a12de51344673eb2dd060 F www/datatypes.tcl 0cb28565580554fa7e03e8fcb303e87ce57757ae F www/download.tcl 0932d7f4f0e8b2adbbd22fac73132f86e43ab4a9 F www/dynload.tcl 02eb8273aa78cfa9070dd4501dca937fb22b466c -F www/faq.tcl 291b8921c5f1ccdeafd88d05127e1d2842a750c1 +F www/faq.tcl 207d3e31597c63ed3bbecd58aaeaa38c53d39dd4 F www/fileformat.tcl a4b5c2c6e89b7d42d09f97fd4d7bbd39cbf24936 F www/formatchng.tcl b4449e065d2da38b6563bdf12cf46cfe1d4d765e F www/index.tcl 33881038e9664a36e56df3b80ef0828594c8dcd9 F www/lang.tcl d2be2be0328f5c2fea06add825a1e442a1f8ed55 F www/mingw.tcl f1c7c0a7f53387dd9bb4f8c7e8571b7561510ebc -F www/omitted.tcl aa5145a79f5a8919ac41885c30007da9face1d48 +F www/omitted.tcl 118062f40a203fcb88b8d68ef1d7c0073ac191ec F www/opcode.tcl 33c5f2061a05c5d227c72b84c080b3bf74c74f8b F www/quickstart.tcl fde79aa2de20074842b60f780800cdeee6a5dec2 -F www/speed.tcl 7fc83f1b018e1ecc451838449542c3079ed12425 +F www/speed.tcl a20a792738475b68756ea7a19321600f23d1d803 F www/sqlite.tcl ae3dcfb077e53833b59d4fcc94d8a12c50a44098 F www/tclsqlite.tcl 1db15abeb446aad0caf0b95b8b9579720e4ea331 F www/vdbe.tcl 2013852c27a02a091d39a766bc87cff329f21218 -P ea011990c552a7a6019b63f9b4341ad6c8663479 -R 01c77a2fbc8007617e801a4f6c0c3a87 -U jadams -Z d542663e519b76f9974c46422a65caa8 +P 97fc4a71a12b52cda67b5192c3bd7bd10ac0c7a6 +R e9cbaf493a319211c9d9a2010395d53a +U drh +Z df71dce9abf1e8c9e6b3a2493526cd33 diff --git a/manifest.uuid b/manifest.uuid index 40511d325..7c2851e09 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -97fc4a71a12b52cda67b5192c3bd7bd10ac0c7a6
\ No newline at end of file +22d8726e61eec0e53893f492cb2163824b87a23e
\ No newline at end of file diff --git a/src/build.c b/src/build.c index f1073d531..4caad2d34 100644 --- a/src/build.c +++ b/src/build.c @@ -25,7 +25,7 @@ ** ROLLBACK ** PRAGMA ** -** $Id: build.c,v 1.109 2002/08/18 20:28:07 drh Exp $ +** $Id: build.c,v 1.110 2002/08/24 18:24:53 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> @@ -844,10 +844,10 @@ void sqliteCreateView( Select *pSelect, /* A SELECT statement that will become the new view */ int isTemp /* TRUE for a TEMPORARY view */ ){ - Token sEnd; Table *p; + int n; const char *z; - int n, offset; + Token sEnd; sqliteStartTable(pParse, pBegin, pName, isTemp); p = pParse->pNewTable; @@ -860,12 +860,20 @@ void sqliteCreateView( sqliteExprListDelete(pSelect->pOrderBy); pSelect->pOrderBy = 0; } - p->pSelect = pSelect; + /* Make a copy of the entire SELECT statement that defines the view. + ** This will force all the Expr.token.z values to be dynamically + ** allocated rather than point to the input string - which means that + ** they will persist after the current sqlite_exec() call returns. + */ + p->pSelect = sqliteSelectDup(pSelect); + sqliteSelectDelete(pSelect); if( !pParse->initFlag ){ - if( sqliteViewGetColumnNames(pParse, p) ){ - return; - } + sqliteViewGetColumnNames(pParse, p); } + + /* Locate the end of the CREATE VIEW statement. Make sEnd point to + ** the end. + */ sEnd = pParse->sLastToken; if( sEnd.z[0]!=0 && sEnd.z[0]!=';' ){ sEnd.z += sEnd.n; @@ -876,12 +884,9 @@ void sqliteCreateView( while( n>0 && (z[n-1]==';' || isspace(z[n-1])) ){ n--; } sEnd.z = &z[n-1]; sEnd.n = 1; - z = p->pSelect->zSelect = sqliteStrNDup(z, n); - if( z ){ - offset = ((int)z) - (int)pBegin->z; - sqliteSelectMoveStrings(p->pSelect, offset); - sqliteEndTable(pParse, &sEnd, 0); - } + + /* Use sqliteEndTable() to add the view to the SQLITE_MASTER table */ + sqliteEndTable(pParse, &sEnd, 0); return; } diff --git a/src/expr.c b/src/expr.c index 43b4f7ee0..9a358c9fb 100644 --- a/src/expr.c +++ b/src/expr.c @@ -12,7 +12,7 @@ ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** -** $Id: expr.c,v 1.79 2002/07/18 00:34:12 drh Exp $ +** $Id: expr.c,v 1.80 2002/08/24 18:24:54 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> @@ -34,16 +34,17 @@ Expr *sqliteExpr(int op, Expr *pLeft, Expr *pRight, Token *pToken){ pNew->pLeft = pLeft; pNew->pRight = pRight; if( pToken ){ + assert( pToken->dyn==0 ); pNew->token = *pToken; + pNew->token.base = 1; + }else if( pLeft && pRight ){ + sqliteExprSpan(pNew, &pLeft->token, &pRight->token); }else{ + pNew->token.dyn = 0; + pNew->token.base = 1; pNew->token.z = 0; pNew->token.n = 0; } - if( pLeft && pRight ){ - sqliteExprSpan(pNew, &pLeft->span, &pRight->span); - }else{ - pNew->span = pNew->token; - } return pNew; } @@ -53,8 +54,17 @@ Expr *sqliteExpr(int op, Expr *pLeft, Expr *pRight, Token *pToken){ */ void sqliteExprSpan(Expr *pExpr, Token *pLeft, Token *pRight){ if( pExpr ){ - pExpr->span.z = pLeft->z; - pExpr->span.n = pRight->n + Addr(pRight->z) - Addr(pLeft->z); + assert( pExpr->token.dyn==0 ); + if( pLeft->dyn==0 && pRight->dyn==0 ){ + pExpr->token.z = pLeft->z; + pExpr->token.n = pRight->n + Addr(pRight->z) - Addr(pLeft->z); + pExpr->token.base = 0; + }else{ + pExpr->token.z = 0; + pExpr->token.n = 0; + pExpr->token.dyn = 0; + pExpr->token.base = 0; + } } } @@ -71,8 +81,18 @@ Expr *sqliteExprFunction(ExprList *pList, Token *pToken){ } pNew->op = TK_FUNCTION; pNew->pList = pList; + + /* Expr.token.n is the length of the entire function + ** call, including the function arguments. The parser + ** will extend token.n to cover the either length of the string. + ** Expr.nFuncName is the length of just the function name. + */ + pNew->token.dyn = 0; + pNew->token.base = 1; if( pToken ){ + assert( pToken->dyn==0 ); pNew->token = *pToken; + pNew->nFuncName = pToken->n>255 ? 255 : pToken->n; }else{ pNew->token.z = 0; pNew->token.n = 0; @@ -85,6 +105,7 @@ Expr *sqliteExprFunction(ExprList *pList, Token *pToken){ */ void sqliteExprDelete(Expr *p){ if( p==0 ) return; + if( p->token.dyn && p->token.z ) sqliteFree((char*)p->token.z); if( p->pLeft ) sqliteExprDelete(p->pLeft); if( p->pRight ) sqliteExprDelete(p->pRight); if( p->pList ) sqliteExprListDelete(p->pList); @@ -92,69 +113,6 @@ void sqliteExprDelete(Expr *p){ sqliteFree(p); } -/* -** The following group of functions are used to translate the string -** pointers of tokens in expression from one buffer to another. -** -** Normally, the Expr.token.z and Expr.span.z fields point into the -** original input buffer of an SQL statement. This is usually OK -** since the SQL statement is executed and the expression is deleted -** before the input buffer is freed. Making the tokens point to the -** original input buffer saves many calls to malloc() and thus helps -** the library to run faster. -** -** But sometimes we need an expression to persist past the time when -** the input buffer is freed. (Example: The SELECT clause of a -** CREATE VIEW statement contains expressions that must persist for -** the life of the view.) When that happens we have to make a -** persistent copy of the input buffer and translate the Expr.token.z -** and Expr.span.z fields to point to the copy rather than the -** original input buffer. The following group of routines handle that -** translation. -** -** The "offset" parameter is the distance from the original input buffer -** to the persistent copy. These routines recursively walk the entire -** expression tree and shift all tokens by "offset" amount. -** -** The work of figuring out the appropriate "offset" and making the -** presistent copy of the input buffer is done by the calling routine. -*/ -void sqliteExprMoveStrings(Expr *p, int offset){ - if( p==0 ) return; - if( !p->staticToken ){ - if( p->token.z ) p->token.z += offset; - if( p->span.z ) p->span.z += offset; - } - if( p->pLeft ) sqliteExprMoveStrings(p->pLeft, offset); - if( p->pRight ) sqliteExprMoveStrings(p->pRight, offset); - if( p->pList ) sqliteExprListMoveStrings(p->pList, offset); - if( p->pSelect ) sqliteSelectMoveStrings(p->pSelect, offset); -} -void sqliteExprListMoveStrings(ExprList *pList, int offset){ - int i; - if( pList==0 ) return; - for(i=0; i<pList->nExpr; i++){ - sqliteExprMoveStrings(pList->a[i].pExpr, offset); - } -} -static void sqliteSrcListMoveStrings(SrcList *pSrc, int offset){ - int i; - if( pSrc==0 ) return; - for(i=0; i<pSrc->nSrc; i++){ - sqliteSelectMoveStrings(pSrc->a[i].pSelect, offset); - sqliteExprMoveStrings(pSrc->a[i].pOn, offset); - } -} -void sqliteSelectMoveStrings(Select *pSelect, int offset){ - if( pSelect==0 ) return; - sqliteExprListMoveStrings(pSelect->pEList, offset); - sqliteSrcListMoveStrings(pSelect->pSrc, offset); - sqliteExprMoveStrings(pSelect->pWhere, offset); - sqliteExprListMoveStrings(pSelect->pGroupBy, offset); - sqliteExprMoveStrings(pSelect->pHaving, offset); - sqliteExprListMoveStrings(pSelect->pOrderBy, offset); - sqliteSelectMoveStrings(pSelect->pPrior, offset); -} /* ** The following group of routines make deep copies of expressions, @@ -162,10 +120,6 @@ void sqliteSelectMoveStrings(Select *pSelect, int offset){ ** be deleted (by being passed to their respective ...Delete() routines) ** without effecting the originals. ** -** Note, however, that the Expr.token.z and Expr.span.z fields point to -** string space that is allocated separately from the expression tree -** itself. These routines do NOT duplicate that string space. -** ** The expression list, ID, and source lists return by sqliteExprListDup(), ** sqliteIdListDup(), and sqliteSrcListDup() can not be further expanded ** by subsequent calls to sqlite*ListAppend() routines. @@ -178,12 +132,38 @@ Expr *sqliteExprDup(Expr *p){ pNew = sqliteMalloc( sizeof(*p) ); if( pNew==0 ) return 0; memcpy(pNew, p, sizeof(*pNew)); + /* Only make a copy of the token if it is a base token (meaning that + ** it covers a single term of an expression - not two or more terms) + ** or if it is already dynamically allocated. So, for example, in + ** a complex expression like "a+b+c", the token "b" would be duplicated + ** but "a+b" would not be. */ + if( p->token.z!=0 && (p->token.base || p->token.dyn) ){ + pNew->token.z = sqliteStrDup(p->token.z); + pNew->token.dyn = 1; + }else{ + pNew->token.z = 0; + pNew->token.n = 0; + pNew->token.dyn = 0; + } pNew->pLeft = sqliteExprDup(p->pLeft); pNew->pRight = sqliteExprDup(p->pRight); pNew->pList = sqliteExprListDup(p->pList); pNew->pSelect = sqliteSelectDup(p->pSelect); return pNew; } +void sqliteTokenCopy(Token *pTo, Token *pFrom){ + if( pTo->dyn ) sqliteFree((char*)pTo->z); + pTo->base = pFrom->base; + if( pFrom->z ){ + pTo->n = pFrom->n; + pTo->z = sqliteStrNDup(pFrom->z, pFrom->n); + pTo->dyn = 1; + }else{ + pTo->n = 0; + pTo->z = 0; + pTo->dyn = 0; + } +} ExprList *sqliteExprListDup(ExprList *p){ ExprList *pNew; int i; @@ -194,7 +174,14 @@ ExprList *sqliteExprListDup(ExprList *p){ pNew->a = sqliteMalloc( p->nExpr*sizeof(p->a[0]) ); if( pNew->a==0 ) return 0; for(i=0; i<p->nExpr; i++){ - pNew->a[i].pExpr = sqliteExprDup(p->a[i].pExpr); + Expr *pNewExpr, *pOldExpr; + pNew->a[i].pExpr = pNewExpr = sqliteExprDup(pOldExpr = p->a[i].pExpr); + if( pOldExpr->token.z!=0 && pNewExpr && pNewExpr->token.z==0 ){ + /* Always make a copy of the token for top-level expressions in the + ** expression list. The logic in SELECT processing that determines + ** the names of columns in the result set needs this information */ + sqliteTokenCopy(&pNew->a[i].pExpr->token, &p->a[i].pExpr->token); + } pNew->a[i].zName = sqliteStrDup(p->a[i].zName); pNew->a[i].sortOrder = p->a[i].sortOrder; pNew->a[i].isAgg = p->a[i].isAgg; @@ -363,6 +350,9 @@ int sqliteExprIsInteger(Expr *p, int *pValue){ } break; } + case TK_UPLUS: { + return sqliteExprIsInteger(p->pLeft, pValue); + } case TK_UMINUS: { int v; if( sqliteExprIsInteger(p->pLeft, &v) ){ @@ -713,6 +703,39 @@ int sqliteExprResolveIds( } /* +** pExpr is a node that defines a function of some kind. It might +** be a syntactic function like "count(x)" or it might be a function +** that implements an operator, like "a LIKE b". +** +** This routine makes *pzName point to the name of the function and +** *pnName hold the number of characters in the function name. +*/ +static void getFunctionName(Expr *pExpr, const char **pzName, int *pnName){ + switch( pExpr->op ){ + case TK_FUNCTION: { + *pzName = pExpr->token.z; + *pnName = pExpr->nFuncName; + break; + } + case TK_LIKE: { + *pzName = "like"; + *pnName = 4; + break; + } + case TK_GLOB: { + *pzName = "glob"; + *pnName = 4; + break; + } + default: { + *pzName = "can't happen"; + *pnName = 12; + break; + } + } +} + +/* ** Error check the functions in an expression. Make sure all ** function names are recognized and all functions have the correct ** number of arguments. Leave an error message in pParse->zErrMsg @@ -725,6 +748,8 @@ int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){ int nErr = 0; if( pExpr==0 ) return 0; switch( pExpr->op ){ + case TK_GLOB: + case TK_LIKE: case TK_FUNCTION: { int n = pExpr->pList ? pExpr->pList->nExpr : 0; /* Number of arguments */ int no_such_func = 0; /* True if no such function exists */ @@ -732,16 +757,16 @@ int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){ int wrong_num_args = 0; /* True if wrong number of arguments */ int is_agg = 0; /* True if is an aggregate function */ int i; + int nId; /* Number of characters in function name */ + const char *zId; /* The function name. */ FuncDef *pDef; - pDef = sqliteFindFunction(pParse->db, - pExpr->token.z, pExpr->token.n, n, 0); + getFunctionName(pExpr, &zId, &nId); + pDef = sqliteFindFunction(pParse->db, zId, nId, n, 0); if( pDef==0 ){ - pDef = sqliteFindFunction(pParse->db, - pExpr->token.z, pExpr->token.n, -1, 0); + pDef = sqliteFindFunction(pParse->db, zId, nId, -1, 0); if( pDef==0 ){ - if( n==1 && pExpr->token.n==6 - && sqliteStrNICmp(pExpr->token.z, "typeof", 6)==0 ){ + if( n==1 && nId==6 && sqliteStrNICmp(zId, "typeof", 6)==0 ){ is_type_of = 1; }else { no_such_func = 1; @@ -754,19 +779,17 @@ int sqliteExprCheck(Parse *pParse, Expr *pExpr, int allowAgg, int *pIsAgg){ } if( is_agg && !allowAgg ){ sqliteSetNString(&pParse->zErrMsg, "misuse of aggregate function ", -1, - pExpr->token.z, pExpr->token.n, "()", 2, 0); + zId, nId, "()", 2, 0); pParse->nErr++; nErr++; is_agg = 0; }else if( no_such_func ){ - sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1, - pExpr->token.z, pExpr->token.n, 0); + sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1, zId,nId,0); pParse->nErr++; nErr++; }else if( wrong_num_args ){ sqliteSetNString(&pParse->zErrMsg, - "wrong number of arguments to function ",-1, - pExpr->token.z, pExpr->token.n, "()", 2, 0); + "wrong number of arguments to function ", -1, zId, nId, "()", 2, 0); pParse->nErr++; nErr++; } @@ -849,6 +872,7 @@ int sqliteExprType(Expr *p){ case TK_NOTNULL: case TK_NOT: case TK_UMINUS: + case TK_UPLUS: case TK_BITAND: case TK_BITOR: case TK_BITNOT: @@ -859,6 +883,8 @@ int sqliteExprType(Expr *p){ case TK_FLOAT: case TK_IN: case TK_BETWEEN: + case TK_GLOB: + case TK_LIKE: return SQLITE_SO_NUM; case TK_STRING: @@ -1031,6 +1057,19 @@ void sqliteExprCode(Parse *pParse, Expr *pExpr){ sqliteVdbeAddOp(v, OP_Concat, 2, 0); break; } + case TK_UPLUS: { + Expr *pLeft = pExpr->pLeft; + if( pLeft && pLeft->op==TK_INTEGER ){ + sqliteVdbeAddOp(v, OP_Integer, atoi(pLeft->token.z), 0); + sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n); + }else if( pLeft && pLeft->op==TK_FLOAT ){ + sqliteVdbeAddOp(v, OP_String, 0, 0); + sqliteVdbeChangeP3(v, -1, pLeft->token.z, pLeft->token.n); + }else{ + sqliteExprCode(pParse, pExpr->pLeft); + } + break; + } case TK_UMINUS: { assert( pExpr->pLeft ); if( pExpr->pLeft->op==TK_FLOAT || pExpr->pLeft->op==TK_INTEGER ){ @@ -1068,13 +1107,17 @@ void sqliteExprCode(Parse *pParse, Expr *pExpr){ sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg); break; } + case TK_GLOB: + case TK_LIKE: case TK_FUNCTION: { int i; ExprList *pList = pExpr->pList; int nExpr = pList ? pList->nExpr : 0; FuncDef *pDef; - pDef = sqliteFindFunction(pParse->db, - pExpr->token.z, pExpr->token.n, nExpr, 0); + int nId; + const char *zId; + getFunctionName(pExpr, &zId, &nId); + pDef = sqliteFindFunction(pParse->db, zId, nId, nExpr, 0); assert( pDef!=0 ); for(i=0; i<nExpr; i++){ sqliteExprCode(pParse, pList->a[i].pExpr); @@ -1402,9 +1445,16 @@ int sqliteExprCompare(Expr *pA, Expr *pB){ if( pA->pSelect || pB->pSelect ) return 0; if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0; if( pA->token.z ){ + int n; if( pB->token.z==0 ) return 0; - if( pB->token.n!=pA->token.n ) return 0; - if( sqliteStrNICmp(pA->token.z, pB->token.z, pA->token.n)!=0 ) return 0; + if( pA->op==TK_FUNCTION || pA->op==TK_AGG_FUNCTION ){ + n = pA->nFuncName; + if( pB->nFuncName!=n ) return 0; + }else{ + n = pA->token.n; + if( pB->token.n!=n ) return 0; + } + if( sqliteStrNICmp(pA->token.z, pB->token.z, n)!=0 ) return 0; } return 1; } @@ -1475,7 +1525,7 @@ int sqliteExprAnalyzeAggregates(Parse *pParse, Expr *pExpr){ pParse->aAgg[i].isAgg = 1; pParse->aAgg[i].pExpr = pExpr; pParse->aAgg[i].pFunc = sqliteFindFunction(pParse->db, - pExpr->token.z, pExpr->token.n, + pExpr->token.z, pExpr->nFuncName, pExpr->pList ? pExpr->pList->nExpr : 0, 0); } pExpr->iAgg = i; diff --git a/src/main.c b/src/main.c index 1924c8e16..bef414436 100644 --- a/src/main.c +++ b/src/main.c @@ -14,7 +14,7 @@ ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** -** $Id: main.c,v 1.97 2002/08/13 23:02:57 drh Exp $ +** $Id: main.c,v 1.98 2002/08/24 18:24:54 drh Exp $ */ #include "sqliteInt.h" #include "os.h" @@ -795,8 +795,11 @@ int sqlite_create_function( void *pUserData /* User data */ ){ FuncDef *p; + int nName; if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1; - p = sqliteFindFunction(db, zName, strlen(zName), nArg, 1); + nName = strlen(zName); + if( nName>255 ) return 1; + p = sqliteFindFunction(db, zName, nName, nArg, 1); if( p==0 ) return 1; p->xFunc = xFunc; p->xStep = 0; @@ -813,8 +816,11 @@ int sqlite_create_aggregate( void *pUserData /* User data */ ){ FuncDef *p; + int nName; if( db==0 || zName==0 || sqliteSafetyCheck(db) ) return 1; - p = sqliteFindFunction(db, zName, strlen(zName), nArg, 1); + nName = strlen(zName); + if( nName>255 ) return 1; + p = sqliteFindFunction(db, zName, nName, nArg, 1); if( p==0 ) return 1; p->xFunc = 0; p->xStep = xStep; diff --git a/src/parse.y b/src/parse.y index 801f5f3f1..061d16587 100644 --- a/src/parse.y +++ b/src/parse.y @@ -14,7 +14,7 @@ ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** -** @(#) $Id: parse.y,v 1.81 2002/08/18 22:41:22 drh Exp $ +** @(#) $Id: parse.y,v 1.82 2002/08/24 18:24:54 drh Exp $ */ %token_prefix TK_ %token_type {Token} @@ -486,7 +486,7 @@ inscollist(A) ::= nm(Y). {A = sqliteIdListAppend(0,&Y);} %left PLUS MINUS. %left STAR SLASH REM. %left CONCAT. -%right UMINUS BITNOT. +%right UMINUS UPLUS BITNOT. %type expr {Expr*} %destructor expr {sqliteExprDelete($$);} @@ -506,10 +506,12 @@ expr(A) ::= STRING(X). {A = sqliteExpr(TK_STRING, 0, 0, &X);} expr(A) ::= ID(X) LP exprlist(Y) RP(E). { A = sqliteExprFunction(Y, &X); sqliteExprSpan(A,&X,&E); + if( A ) A->token.base = 1; } expr(A) ::= ID(X) LP STAR RP(E). { A = sqliteExprFunction(0, &X); sqliteExprSpan(A,&X,&E); + if( A ) A->token.base = 1; } expr(A) ::= expr(X) AND expr(Y). {A = sqliteExpr(TK_AND, X, Y, 0);} expr(A) ::= expr(X) OR expr(Y). {A = sqliteExpr(TK_OR, X, Y, 0);} @@ -526,18 +528,21 @@ expr(A) ::= expr(X) RSHIFT expr(Y). {A = sqliteExpr(TK_RSHIFT, X, Y, 0);} expr(A) ::= expr(X) likeop(OP) expr(Y). [LIKE] { ExprList *pList = sqliteExprListAppend(0, Y, 0); pList = sqliteExprListAppend(pList, X, 0); - A = sqliteExprFunction(pList, &OP); - sqliteExprSpan(A, &X->span, &Y->span); + A = sqliteExprFunction(pList, 0); + if( A ) A->op = OP; + sqliteExprSpan(A, &X->token, &Y->token); } expr(A) ::= expr(X) NOT likeop(OP) expr(Y). [LIKE] { ExprList *pList = sqliteExprListAppend(0, Y, 0); pList = sqliteExprListAppend(pList, X, 0); - A = sqliteExprFunction(pList, &OP); + A = sqliteExprFunction(pList, 0); + if( A ) A->op = OP; A = sqliteExpr(TK_NOT, A, 0, 0); - sqliteExprSpan(A,&X->span,&Y->span); + sqliteExprSpan(A,&X->token,&Y->token); } -likeop(A) ::= LIKE(X). {A = X;} -likeop(A) ::= GLOB(X). {A = X;} +%type likeop {int} +likeop(A) ::= LIKE. {A = TK_LIKE;} +likeop(A) ::= GLOB. {A = TK_GLOB;} expr(A) ::= expr(X) PLUS expr(Y). {A = sqliteExpr(TK_PLUS, X, Y, 0);} expr(A) ::= expr(X) MINUS expr(Y). {A = sqliteExpr(TK_MINUS, X, Y, 0);} expr(A) ::= expr(X) STAR expr(Y). {A = sqliteExpr(TK_STAR, X, Y, 0);} @@ -546,39 +551,39 @@ expr(A) ::= expr(X) REM expr(Y). {A = sqliteExpr(TK_REM, X, Y, 0);} expr(A) ::= expr(X) CONCAT expr(Y). {A = sqliteExpr(TK_CONCAT, X, Y, 0);} expr(A) ::= expr(X) ISNULL(E). { A = sqliteExpr(TK_ISNULL, X, 0, 0); - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= expr(X) IS NULL(E). { A = sqliteExpr(TK_ISNULL, X, 0, 0); - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= expr(X) NOTNULL(E). { A = sqliteExpr(TK_NOTNULL, X, 0, 0); - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= expr(X) NOT NULL(E). { A = sqliteExpr(TK_NOTNULL, X, 0, 0); - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= expr(X) IS NOT NULL(E). { A = sqliteExpr(TK_NOTNULL, X, 0, 0); - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= NOT(B) expr(X). { A = sqliteExpr(TK_NOT, X, 0, 0); - sqliteExprSpan(A,&B,&X->span); + sqliteExprSpan(A,&B,&X->token); } expr(A) ::= BITNOT(B) expr(X). { A = sqliteExpr(TK_BITNOT, X, 0, 0); - sqliteExprSpan(A,&B,&X->span); + sqliteExprSpan(A,&B,&X->token); } expr(A) ::= MINUS(B) expr(X). [UMINUS] { A = sqliteExpr(TK_UMINUS, X, 0, 0); - sqliteExprSpan(A,&B,&X->span); + sqliteExprSpan(A,&B,&X->token); } -expr(A) ::= PLUS(B) expr(X). [UMINUS] { - A = X; - sqliteExprSpan(A,&B,&X->span); +expr(A) ::= PLUS(B) expr(X). [UPLUS] { + A = sqliteExpr(TK_UPLUS, X, 0, 0); + sqliteExprSpan(A,&B,&X->token); } expr(A) ::= LP(B) select(X) RP(E). { A = sqliteExpr(TK_SELECT, 0, 0, 0); @@ -590,7 +595,7 @@ expr(A) ::= expr(W) BETWEEN expr(X) AND expr(Y). { pList = sqliteExprListAppend(pList, Y, 0); A = sqliteExpr(TK_BETWEEN, W, 0, 0); if( A ) A->pList = pList; - sqliteExprSpan(A,&W->span,&Y->span); + sqliteExprSpan(A,&W->token,&Y->token); } expr(A) ::= expr(W) NOT BETWEEN expr(X) AND expr(Y). { ExprList *pList = sqliteExprListAppend(0, X, 0); @@ -598,29 +603,29 @@ expr(A) ::= expr(W) NOT BETWEEN expr(X) AND expr(Y). { A = sqliteExpr(TK_BETWEEN, W, 0, 0); if( A ) A->pList = pList; A = sqliteExpr(TK_NOT, A, 0, 0); - sqliteExprSpan(A,&W->span,&Y->span); + sqliteExprSpan(A,&W->token,&Y->token); } expr(A) ::= expr(X) IN LP exprlist(Y) RP(E). { A = sqliteExpr(TK_IN, X, 0, 0); if( A ) A->pList = Y; - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= expr(X) IN LP select(Y) RP(E). { A = sqliteExpr(TK_IN, X, 0, 0); if( A ) A->pSelect = Y; - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= expr(X) NOT IN LP exprlist(Y) RP(E). { A = sqliteExpr(TK_IN, X, 0, 0); if( A ) A->pList = Y; A = sqliteExpr(TK_NOT, A, 0, 0); - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } expr(A) ::= expr(X) NOT IN LP select(Y) RP(E). { A = sqliteExpr(TK_IN, X, 0, 0); if( A ) A->pSelect = Y; A = sqliteExpr(TK_NOT, A, 0, 0); - sqliteExprSpan(A,&X->span,&E); + sqliteExprSpan(A,&X->token,&E); } /* CASE expressions */ @@ -717,8 +722,10 @@ plus_opt ::= . cmd ::= CREATE(A) TRIGGER nm(B) trigger_time(C) trigger_event(D) ON nm(E) foreach_clause(F) when_clause(G) BEGIN trigger_cmd_list(S) END(Z). { - sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, - A.z, (int)(Z.z - A.z) + Z.n ); + Token all; + all.z = A.z; + all.n = (Z.z - A.z) + Z.n; + sqliteCreateTrigger(pParse, &B, C, D.a, D.b, &E, F, G, S, &all); } %type trigger_time {int} @@ -769,17 +776,26 @@ trigger_cmd(A) ::= DELETE FROM nm(X) where_opt(Y). trigger_cmd(A) ::= select(X). {A = sqliteTriggerSelectStep(X); } // The special RAISE expression that may occur in trigger programs -expr(A) ::= RAISE(X) LP IGNORE RP(Y). { A = sqliteExpr(TK_RAISE, 0, 0, 0); - A->iColumn = OE_Ignore; sqliteExprSpan(A, &X, &Y);} -expr(A) ::= RAISE(X) LP ROLLBACK COMMA nm(Z) RP(Y). -{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); - A->iColumn = OE_Rollback; sqliteExprSpan(A, &X, &Y);} -expr(A) ::= RAISE(X) LP ABORT COMMA nm(Z) RP(Y). -{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); - A->iColumn = OE_Abort; sqliteExprSpan(A, &X, &Y);} -expr(A) ::= RAISE(X) LP FAIL COMMA nm(Z) RP(Y). -{ A = sqliteExpr(TK_RAISE, 0, 0, &Z); - A->iColumn = OE_Fail; sqliteExprSpan(A, &X, &Y);} +expr(A) ::= RAISE(X) LP IGNORE RP(Y). { + A = sqliteExpr(TK_RAISE, 0, 0, 0); + A->iColumn = OE_Ignore; + /* sqliteExprSpan(A, &X, &Y); */ +} +expr(A) ::= RAISE(X) LP ROLLBACK COMMA nm(Z) RP(Y). { + A = sqliteExpr(TK_RAISE, 0, 0, &Z); + A->iColumn = OE_Rollback; + /* sqliteExprSpan(A, &X, &Y); */ +} +expr(A) ::= RAISE(X) LP ABORT COMMA nm(Z) RP(Y). { + A = sqliteExpr(TK_RAISE, 0, 0, &Z); + A->iColumn = OE_Abort; + /* sqliteExprSpan(A, &X, &Y); */ +} +expr(A) ::= RAISE(X) LP FAIL COMMA nm(Z) RP(Y). { + A = sqliteExpr(TK_RAISE, 0, 0, &Z); + A->iColumn = OE_Fail; + /* sqliteExprSpan(A, &X, &Y); */ +} //////////////////////// DROP TRIGGER statement ////////////////////////////// cmd ::= DROP TRIGGER nm(X). { diff --git a/src/select.c b/src/select.c index 0bb456af7..2171ead1b 100644 --- a/src/select.c +++ b/src/select.c @@ -12,7 +12,7 @@ ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.107 2002/08/04 00:52:38 drh Exp $ +** $Id: select.c,v 1.108 2002/08/24 18:24:54 drh Exp $ */ #include "sqliteInt.h" @@ -156,18 +156,16 @@ static void addWhereTerm( dummy.z = zCol; dummy.n = strlen(zCol); + dummy.base = 1; + dummy.dyn = 0; pE1a = sqliteExpr(TK_ID, 0, 0, &dummy); - pE1a->staticToken = 1; pE2a = sqliteExpr(TK_ID, 0, 0, &dummy); - pE2a->staticToken = 1; dummy.z = pTab1->zName; dummy.n = strlen(dummy.z); pE1b = sqliteExpr(TK_ID, 0, 0, &dummy); - pE1b->staticToken = 1; dummy.z = pTab2->zName; dummy.n = strlen(dummy.z); pE2b = sqliteExpr(TK_ID, 0, 0, &dummy); - pE2b->staticToken = 1; pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0); pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0); pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0); @@ -643,9 +641,9 @@ static void generateColumnNames( zCol = pTab->aCol[iCol].zName; zType = pTab->aCol[iCol].zType; } - if( p->span.z && p->span.z[0] && !showFullNames ){ + if( p->token.z && p->token.z[0] && !showFullNames ){ int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0); - sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n); + sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n); sqliteVdbeCompressSpace(v, addr); }else if( pTabList->nSrc>1 || showFullNames ){ char *zName = 0; @@ -661,13 +659,13 @@ static void generateColumnNames( sqliteVdbeAddOp(v, OP_ColumnName, i, 0); sqliteVdbeChangeP3(v, -1, zCol, 0); } - }else if( p->span.z && p->span.z[0] && !showFullNames ){ + }else if( p->token.z && p->token.z[0] && !showFullNames ){ int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0); - sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n); + sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n); sqliteVdbeCompressSpace(v, addr); - }else if( p->span.z && p->span.z[0] ){ + }else if( p->token.z && p->token.z[0] ){ int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0); - sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n); + sqliteVdbeChangeP3(v, -1, p->token.z, p->token.n); sqliteVdbeCompressSpace(v, addr); }else{ char zName[30]; @@ -730,8 +728,8 @@ Table *sqliteResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){ Expr *p; if( pEList->a[i].zName ){ pTab->aCol[i].zName = sqliteStrDup(pEList->a[i].zName); - }else if( (p=pEList->a[i].pExpr)->span.z && p->span.z[0] ){ - sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0); + }else if( (p=pEList->a[i].pExpr)->token.z && p->token.z[0] ){ + sqliteSetNString(&pTab->aCol[i].zName, p->token.z, p->token.n, 0); }else if( p->op==TK_DOT && p->pRight && p->pRight->token.z && p->pRight->token.z[0] ){ sqliteSetNString(&pTab->aCol[i].zName, @@ -895,16 +893,22 @@ static int fillInColumnList(Parse *pParse, Select *p){ if( pRight==0 ) break; pRight->token.z = zName; pRight->token.n = strlen(zName); - if( zTabName ){ + pRight->token.dyn = 0; + pRight->token.base = 1; + if( zTabName && pTabList->nSrc>1 ){ pLeft = sqliteExpr(TK_ID, 0, 0, 0); - if( pLeft==0 ) break; - pLeft->token.z = zTabName; - pLeft->token.n = strlen(zTabName); pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0); if( pExpr==0 ) break; + pLeft->token.z = zTabName; + pLeft->token.n = strlen(zTabName); + pLeft->token.dyn = 0; + pLeft->token.base = 1; + sqliteSetString((char**)&pExpr->token.z, zTabName, ".", zName, 0); + pExpr->token.n = strlen(pExpr->token.z); + pExpr->token.base = 0; + pExpr->token.dyn = 1; }else{ pExpr = pRight; - pExpr->span = pExpr->token; } pNew = sqliteExprListAppend(pNew, pExpr, 0); } @@ -945,8 +949,10 @@ void sqliteSelectUnbind(Select *p){ if( (pTab = pSrc->a[i].pTab)!=0 ){ if( pTab->isTransient ){ sqliteDeleteTable(0, pTab); +#if 0 sqliteSelectDelete(pSrc->a[i].pSelect); pSrc->a[i].pSelect = 0; +#endif } pSrc->a[i].pTab = 0; if( pSrc->a[i].pSelect ){ @@ -1309,7 +1315,8 @@ static void substExpr(Expr *pExpr, int iTable, ExprList *pEList, int iSub){ pExpr->iTable = pNew->iTable; pExpr->iColumn = pNew->iColumn; pExpr->iAgg = pNew->iAgg; - pExpr->token = pNew->token; + pExpr->nFuncName = pNew->nFuncName; + sqliteTokenCopy(&pExpr->token, &pNew->token); if( iSub!=iTable ){ changeTables(pExpr, iSub, iTable); } @@ -1428,7 +1435,8 @@ int flattenSubquery(Select *p, int iFrom, int isAgg, int subqueryIsAgg){ for(i=0; i<pList->nExpr; i++){ if( pList->a[i].zName==0 ){ Expr *pExpr = pList->a[i].pExpr; - pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n); + assert( pExpr->token.z!=0 ); + pList->a[i].zName = sqliteStrNDup(pExpr->token.z, pExpr->token.n); } } if( isAgg ){ @@ -1535,7 +1543,7 @@ static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){ pExpr = p->pEList->a[0].pExpr; if( pExpr->op!=TK_AGG_FUNCTION ) return 0; if( pExpr->pList==0 || pExpr->pList->nExpr!=1 ) return 0; - if( pExpr->token.n!=3 ) return 0; + if( pExpr->nFuncName!=3 ) return 0; if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){ seekOp = OP_Rewind; }else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index e69579532..912ef83e4 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -11,7 +11,7 @@ ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.142 2002/08/02 10:36:10 drh Exp $ +** @(#) $Id: sqliteInt.h,v 1.143 2002/08/24 18:24:55 drh Exp $ */ #include "sqlite.h" #include "hash.h" @@ -391,11 +391,19 @@ struct Index { /* ** Each token coming out of the lexer is an instance of -** this structure. +** this structure. Tokens are also used as part of an expression. +** +** A "base" token is a real single token such as would come out of the +** lexer. There are also compound tokens which are aggregates of one +** or more base tokens. Compound tokens are used to name columns in the +** result set of a SELECT statement. In the expression "a+b+c", "b" +** is a base token but "a+b" is a compound token. */ struct Token { const char *z; /* Text of the token. Not NULL-terminated! */ - int n; /* Number of characters in this token */ + unsigned dyn : 1; /* True for malloced memory, false for static */ + unsigned base : 1; /* True for a base token, false for compounds */ + unsigned n : 30; /* Number of characters in this token */ }; /* @@ -411,10 +419,10 @@ struct Token { ** Expr.pRight and Expr.pLeft are subexpressions. Expr.pList is a list ** of argument if the expression is a function. ** -** Expr.token is the operator token for this node. Expr.span is the complete -** subexpression represented by this node and all its decendents. These -** fields are used for error reporting and for reconstructing the text of -** an expression to use as the column name in a SELECT statement. +** Expr.token is the operator token for this node. For some expressions +** that have subexpressions, Expr.token can be the complete text that gave +** rise to the Expr. In the latter case, the token is marked as being +** a compound token. ** ** An expression of the form ID or ID.ID refers to a column in a table. ** For such expressions, Expr.op is set to TK_COLUMN and Expr.iTable is @@ -435,13 +443,12 @@ struct Token { struct Expr { u8 op; /* Operation performed by this node */ u8 dataType; /* Either SQLITE_SO_TEXT or SQLITE_SO_NUM */ - u8 isJoinExpr; /* Origina is the ON or USING phrase of a join */ - u8 staticToken; /* Expr.token.z points to static memory */ + u8 isJoinExpr; /* Origin is the ON or USING phrase of a join */ + u8 nFuncName; /* Number of characters in a function name */ Expr *pLeft, *pRight; /* Left and right subnodes */ ExprList *pList; /* A list of expressions used as function arguments ** or in "<expr> IN (<expr-list)" */ Token token; /* An operand token */ - Token span; /* Complete text of the expression */ int iTable, iColumn; /* When op==TK_COLUMN, then this expr node means the ** iColumn-th field of the iTable-th table. */ int iAgg; /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull @@ -677,11 +684,6 @@ struct Parse { * linked list is stored as the "pTrigger" member of the associated * struct Table. * - * The "strings" member of struct Trigger contains a pointer to the memory - * referenced by the various Token structures referenced indirectly by the - * "pWhen", "pColumns" and "step_list" members. (ie. the memory allocated for - * use in conjunction with the sqliteExprMoveStrings() etc. interface). - * * The "step_list" member points to the first element of a linked list * containing the SQL statements specified as the trigger program. * @@ -708,7 +710,6 @@ struct Trigger { int foreach; /* One of TK_ROW or TK_STATEMENT */ TriggerStep *step_list; /* Link list of trigger program steps */ - char *strings; /* pointer to allocation of Token strings */ Trigger *pNext; /* Next trigger associated with the table */ }; @@ -920,10 +921,8 @@ void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int); void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int); void sqliteBeginWriteOperation(Parse*, int); void sqliteEndWriteOperation(Parse*); -void sqliteExprMoveStrings(Expr*, int); -void sqliteExprListMoveStrings(ExprList*, int); -void sqliteSelectMoveStrings(Select*, int); Expr *sqliteExprDup(Expr*); +void sqliteTokenCopy(Token*, Token*); ExprList *sqliteExprListDup(ExprList*); SrcList *sqliteSrcListDup(SrcList*); IdList *sqliteIdListDup(IdList*); @@ -935,7 +934,7 @@ int sqliteSafetyOff(sqlite*); int sqliteSafetyCheck(sqlite*); void sqliteChangeCookie(sqlite*, Vdbe*); void sqliteCreateTrigger(Parse*, Token*, int, int, IdList*, Token*, - int, Expr*, TriggerStep*, char const*,int); + int, Expr*, TriggerStep*, Token*); void sqliteDropTrigger(Parse*, Token*, int); int sqliteTriggersExist(Parse* , Trigger* , int , int , int, ExprList*); int sqliteCodeRowTrigger(Parse*, int, ExprList*, int, Table *, int, int, diff --git a/src/tokenize.c b/src/tokenize.c index ed2329c04..46f6aa864 100644 --- a/src/tokenize.c +++ b/src/tokenize.c @@ -15,7 +15,7 @@ ** individual tokens and sends those tokens one-by-one over to the ** parser for analysis. ** -** $Id: tokenize.c,v 1.47 2002/07/01 12:27:09 drh Exp $ +** $Id: tokenize.c,v 1.48 2002/08/24 18:24:56 drh Exp $ */ #include "sqliteInt.h" #include "os.h" @@ -418,6 +418,8 @@ int sqliteRunParser(Parse *pParse, const char *zSql, char **pzErrMsg){ break; } pParse->sLastToken.z = &zSql[i]; + pParse->sLastToken.base = 1; + pParse->sLastToken.dyn = 0; pParse->sLastToken.n = sqliteGetToken((unsigned char*)&zSql[i], &tokenType); i += pParse->sLastToken.n; if( once ){ diff --git a/src/trigger.c b/src/trigger.c index 53eff0c41..24bd74888 100644 --- a/src/trigger.c +++ b/src/trigger.c @@ -13,6 +13,24 @@ #include "sqliteInt.h" /* +** Delete a linked list of TriggerStep structures. +*/ +static void sqliteDeleteTriggerStep(TriggerStep *pTriggerStep){ + while( pTriggerStep ){ + TriggerStep * pTmp = pTriggerStep; + pTriggerStep = pTriggerStep->pNext; + + if( pTmp->target.dyn ) sqliteFree(pTmp->target.z); + sqliteExprDelete(pTmp->pWhere); + sqliteExprListDelete(pTmp->pExprList); + sqliteSelectDelete(pTmp->pSelect); + sqliteIdListDelete(pTmp->pIdList); + + sqliteFree(pTmp); + } +} + +/* ** This is called by the parser when it sees a CREATE TRIGGER statement. See ** comments surrounding struct Trigger in sqliteInt.h for a description of ** how triggers are stored. @@ -27,13 +45,10 @@ void sqliteCreateTrigger( int foreach, /* One of TK_ROW or TK_STATEMENT */ Expr *pWhen, /* WHEN clause */ TriggerStep *pStepList, /* The triggered program */ - char const *zData, /* The string data to make persistent */ - int zDataLen + Token *pAll /* Token that describes the complete CREATE TRIGGER */ ){ Trigger *nt; Table *tab; - int offset; - TriggerStep *ss; /* Check that: ** 1. the trigger name does not already exist. @@ -98,28 +113,15 @@ void sqliteCreateTrigger( if( nt==0 ) goto trigger_cleanup; nt->name = sqliteStrNDup(pName->z, pName->n); nt->table = sqliteStrNDup(pTableName->z, pTableName->n); - nt->strings = sqliteStrNDup(zData, zDataLen); if( sqlite_malloc_failed ) goto trigger_cleanup; nt->op = op; nt->tr_tm = tr_tm; - nt->pWhen = pWhen; - nt->pColumns = pColumns; + nt->pWhen = sqliteExprDup(pWhen); + sqliteExprDelete(pWhen); + nt->pColumns = sqliteIdListDup(pColumns); + sqliteIdListDelete(pColumns); nt->foreach = foreach; nt->step_list = pStepList; - offset = (int)(nt->strings - zData); - sqliteExprMoveStrings(nt->pWhen, offset); - - ss = nt->step_list; - while( ss ){ - sqliteSelectMoveStrings(ss->pSelect, offset); - if( ss->target.z ){ - ss->target.z += offset; - } - sqliteExprMoveStrings(ss->pWhere, offset); - sqliteExprListMoveStrings(ss->pExprList, offset); - - ss = ss->pNext; - } /* if we are not initializing, and this trigger is not on a TEMP table, ** build the sqlite_master entry @@ -148,7 +150,7 @@ void sqliteCreateTrigger( P3_STATIC); sqliteVdbeChangeP3(v, addr+2, nt->name, 0); sqliteVdbeChangeP3(v, addr+3, nt->table, 0); - sqliteVdbeChangeP3(v, addr+5, nt->strings, 0); + sqliteVdbeChangeP3(v, addr+5, pAll->z, pAll->n); if( !tab->isTemp ){ sqliteChangeCookie(pParse->db, v); } @@ -165,7 +167,6 @@ void sqliteCreateTrigger( tab->pTrigger = nt; return; }else{ - sqliteFree(nt->strings); sqliteFree(nt->name); sqliteFree(nt->table); sqliteFree(nt); @@ -175,20 +176,43 @@ trigger_cleanup: sqliteIdListDelete(pColumns); sqliteExprDelete(pWhen); - { - TriggerStep * pp; - TriggerStep * nn; - - pp = pStepList; - while( pp ){ - nn = pp->pNext; - sqliteExprDelete(pp->pWhere); - sqliteExprListDelete(pp->pExprList); - sqliteSelectDelete(pp->pSelect); - sqliteIdListDelete(pp->pIdList); - sqliteFree(pp); - pp = nn; - } + sqliteDeleteTriggerStep(pStepList); +} + +/* +** Make a copy of all components of the given trigger step. This has +** the effect of copying all Expr.token.z values into memory obtained +** from sqliteMalloc(). As initially created, the Expr.token.z values +** all point to the input string that was fed to the parser. But that +** string is ephemeral - it will go away as soon as the sqlite_exec() +** call that started the parser exits. This routine makes a persistent +** copy of all the Expr.token.z strings so that the TriggerStep structure +** will be valid even after the sqlite_exec() call returns. +*/ +static void sqlitePersistTriggerStep(TriggerStep *p){ + if( p->target.z ){ + p->target.z = sqliteStrNDup(p->target.z, p->target.n); + p->target.dyn = 1; + } + if( p->pSelect ){ + Select *pNew = sqliteSelectDup(p->pSelect); + sqliteSelectDelete(p->pSelect); + p->pSelect = pNew; + } + if( p->pWhere ){ + Expr *pNew = sqliteExprDup(p->pWhere); + sqliteExprDelete(p->pWhere); + p->pWhere = pNew; + } + if( p->pExprList ){ + ExprList *pNew = sqliteExprListDup(p->pExprList); + sqliteExprListDelete(p->pExprList); + p->pExprList = pNew; + } + if( p->pIdList ){ + IdList *pNew = sqliteIdListDup(p->pIdList); + sqliteIdListDelete(p->pIdList); + p->pIdList = pNew; } } @@ -206,6 +230,7 @@ TriggerStep *sqliteTriggerSelectStep(Select *pSelect){ pTriggerStep->op = TK_SELECT; pTriggerStep->pSelect = pSelect; pTriggerStep->orconf = OE_Default; + sqlitePersistTriggerStep(pTriggerStep); return pTriggerStep; } @@ -236,6 +261,7 @@ TriggerStep *sqliteTriggerInsertStep( pTriggerStep->pIdList = pColumn; pTriggerStep->pExprList = pEList; pTriggerStep->orconf = orconf; + sqlitePersistTriggerStep(pTriggerStep); return pTriggerStep; } @@ -259,6 +285,7 @@ TriggerStep *sqliteTriggerUpdateStep( pTriggerStep->pExprList = pEList; pTriggerStep->pWhere = pWhere; pTriggerStep->orconf = orconf; + sqlitePersistTriggerStep(pTriggerStep); return pTriggerStep; } @@ -276,6 +303,7 @@ TriggerStep *sqliteTriggerDeleteStep(Token *pTableName, Expr *pWhere){ pTriggerStep->target = *pTableName; pTriggerStep->pWhere = pWhere; pTriggerStep->orconf = OE_Default; + sqlitePersistTriggerStep(pTriggerStep); return pTriggerStep; } @@ -286,24 +314,11 @@ TriggerStep *sqliteTriggerDeleteStep(Token *pTableName, Expr *pWhere){ void sqliteDeleteTrigger(Trigger *pTrigger){ TriggerStep *pTriggerStep; - pTriggerStep = pTrigger->step_list; - while( pTriggerStep ){ - TriggerStep * pTmp = pTriggerStep; - pTriggerStep = pTriggerStep->pNext; - - sqliteExprDelete(pTmp->pWhere); - sqliteExprListDelete(pTmp->pExprList); - sqliteSelectDelete(pTmp->pSelect); - sqliteIdListDelete(pTmp->pIdList); - - sqliteFree(pTmp); - } - + sqliteDeleteTriggerStep(pTrigger->step_list); sqliteFree(pTrigger->name); sqliteFree(pTrigger->table); sqliteExprDelete(pTrigger->pWhen); sqliteIdListDelete(pTrigger->pColumns); - sqliteFree(pTrigger->strings); sqliteFree(pTrigger); } diff --git a/test/all.test b/test/all.test index 60ed369d5..7767d4743 100644 --- a/test/all.test +++ b/test/all.test @@ -10,7 +10,7 @@ #*********************************************************************** # This file runs all tests. # -# $Id: all.test,v 1.16 2002/08/11 20:10:49 drh Exp $ +# $Id: all.test,v 1.17 2002/08/24 18:24:57 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -34,6 +34,7 @@ set EXCLUDE { quick.test malloc.test misuse.test + memleak.test } # btree2.test diff --git a/test/quick.test b/test/quick.test index ab7cb7ddb..a94ea1eb9 100644 --- a/test/quick.test +++ b/test/quick.test @@ -10,7 +10,7 @@ #*********************************************************************** # This file runs all tests. # -# $Id: quick.test,v 1.3 2002/07/07 16:52:47 drh Exp $ +# $Id: quick.test,v 1.4 2002/08/24 18:24:57 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -23,6 +23,7 @@ set EXCLUDE { quick.test btree2.test malloc.test + memleak.test } foreach testfile [lsort -dictionary [glob $testdir/*.test]] { diff --git a/test/view.test b/test/view.test index be78fbc8f..c60f15bf9 100644 --- a/test/view.test +++ b/test/view.test @@ -11,7 +11,7 @@ # This file implements regression tests for SQLite library. The # focus of this file is testing VIEW statements. # -# $Id: view.test,v 1.8 2002/07/16 02:05:45 drh Exp $ +# $Id: view.test,v 1.9 2002/08/24 18:24:57 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -265,4 +265,33 @@ do_test view-7.6 { } } {1 2 3} +do_test view-8.1 { + execsql { + CREATE VIEW v6 AS SELECT pqr, xyz FROM v1; + SELECT * FROM v6 ORDER BY xyz; + } +} {7 2 13 5 19 8 27 12} +if 0 { +do_test view-8.2 { + db close + sqlite db test.db + execsql { + SELECT * FROM v6 ORDER BY xyz; + } +} {7 2 13 5 19 8 27 12} +do_test view-8.3 { + execsql { + CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6; + SELECT * FROM v7 ORDER BY a; + } +} {9 18 27 39} +do_test view-8.4 { + execsql { PRAGMA vdbe_trace=on; + CREATE VIEW v8 AS SELECT max(cnt) FROM + (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo); + SELECT * FROM v8; + } +} 3 +} + finish_test diff --git a/www/c_interface.tcl b/www/c_interface.tcl index 1066a760b..b1b8855d6 100644 --- a/www/c_interface.tcl +++ b/www/c_interface.tcl @@ -1,7 +1,7 @@ # # Run this Tcl script to generate the sqlite.html file. # -set rcsid {$Id: c_interface.tcl,v 1.34 2002/08/15 11:48:14 drh Exp $} +set rcsid {$Id: c_interface.tcl,v 1.35 2002/08/24 18:24:57 drh Exp $} puts {<html> <head> @@ -716,6 +716,7 @@ routine. The string pointer that these routines return should be freed by passing it to <b>sqlite_freemem()</b>. </p> +<a name="cfunc"> <h2>Adding New SQL Functions</h2> <p>Beginning with version 2.4.0, SQLite allows the SQL language to be @@ -760,7 +761,14 @@ parameter is an open SQLite database on which the functions should be registered, <b>zName</b> is the name of the new function, <b>nArg</b> is the number of arguments, and <b>pUserData</b> is a pointer which is passed through unchanged to the C implementation -of the function. +of the function. Both routines return 0 on success and non-zero +if there are any errors. +</p> + +<p> +The length of a function name may not exceed 255 characters. +Any attempt to create a function whose name exceeds 255 characters +in length will result in an error. </p> <p> diff --git a/www/faq.tcl b/www/faq.tcl index e67382f3f..98232256a 100644 --- a/www/faq.tcl +++ b/www/faq.tcl @@ -1,7 +1,7 @@ # # Run this script to generated a faq.html output file # -set rcsid {$Id: faq.tcl,v 1.18 2002/08/18 19:09:24 drh Exp $} +set rcsid {$Id: faq.tcl,v 1.19 2002/08/24 18:24:57 drh Exp $} puts {<html> <head> @@ -330,6 +330,11 @@ faq { within a 1-megabyte row of the SQLITE_MASTER table. Other than this, there are no constraints on the length of the name of a table, or on the number of columns, etc. Indices are similarly unconstrained.</p> + + <p>The names of tables, indices, view, triggers, and columns can be + as long as desired. However, the names of SQL functions (as created + by the <a href="c_interface.html#cfunc">sqlite_create_function()</a> API) + may not exceed 255 characters in length.</p> } faq { diff --git a/www/omitted.tcl b/www/omitted.tcl index 329b6d3b3..52de2a318 100644 --- a/www/omitted.tcl +++ b/www/omitted.tcl @@ -1,7 +1,7 @@ # # Run this script to generated a omitted.html output file # -set rcsid {$Id: omitted.tcl,v 1.2 2002/08/15 13:45:17 drh Exp $} +set rcsid {$Id: omitted.tcl,v 1.3 2002/08/24 18:24:58 drh Exp $} puts {<html> <head> @@ -45,18 +45,30 @@ feature {CHECK constraints} { feature {Variable subqueries} { Subqueries must be static. They are evaluated only once. They may not, - therefore, refer to variables in the containing query. + therefore, refer to variables in the main query. } feature {FOREIGN KEY constraints} { FOREIGN KEY constraints are parsed but are not enforced. } +feature {Complete trigger support} { + There is some support for triggers but it is not complete. Missing + subfeatures include FOR EACH STATEMENT triggers (currently all triggers + must be FOR EACH ROW), INSTEAD OF triggers on tables (currently + INSTEAD OF triggers are only allowed on views), and recursive + triggers - triggers that trigger themselves. +} + feature {ALTER TABLE} { To change a table you have to delete it (saving its contents to a temporary table) and recreate it from scratch. } +feature {Nested transactions} { + The current implementation only allows a single active transaction. +} + feature {The COUNT(DISTINCT X) function} { You can accomplish the same thing using a subquery, like this:<br /> SELECT count(x) FROM (SELECT DISTINCT x FROM tbl); diff --git a/www/speed.tcl b/www/speed.tcl index c8dd92a4f..3d3e9c7ec 100644 --- a/www/speed.tcl +++ b/www/speed.tcl @@ -1,7 +1,7 @@ # # Run this Tcl script to generate the speed.html file. # -set rcsid {$Id: speed.tcl,v 1.7 2002/08/06 12:05:01 drh Exp $ } +set rcsid {$Id: speed.tcl,v 1.8 2002/08/24 18:24:58 drh Exp $ } puts {<html> <head> @@ -19,22 +19,25 @@ puts { <h2>Executive Summary</h2> <p>A series of tests were run to measure the relative performance of -SQLite 2.4.0, PostgreSQL, and MySQL +SQLite 2.7.0, PostgreSQL 7.1.3, and MySQL 3.23.41. The following are general conclusions drawn from these experiments: </p> <ul> <li><p> - SQLite 2.4.0 is significantly faster than PostgreSQL + SQLite 2.7.0 is significantly faster than PostgreSQL 7.1.3 for most common operations. </p></li> <li><p> - The speed of SQLite 2.4.0 is similar to MySQL. + The speed of SQLite 2.7.0 is similar to MySQL 3.23.41. This is true in spite of the fact that SQLite contains full transaction support whereas the version of MySQL tested did not. </p></li> +<li><p> + These tests did not attempt to measure multi-user performance or + optimization of complex queries involving multiple joins and subqueries. </ul> <h2>Test Environment</h2> @@ -47,15 +50,22 @@ a stock kernel. <p> The PostgreSQL and MySQL servers used were as delivered by default on -RedHat 7.2. No effort was made to tune these engines. Note in particular +RedHat 7.2. (PostgreSQL version 7.1.3 and MySQL version 3.23.41.) +No effort was made to tune these engines. Note in particular the the default MySQL configuration on RedHat 7.2 does not support transactions. Not having to support transactions gives MySQL a -big advantage, but SQLite is still able to hold its own on most -tests. +big speed advantage, but SQLite is still able to hold its own on most +tests. On the other hand, I am told that the default PostgreSQL +configuration is unnecessarily conservative (it is designed to +work on a machine with 8MB of RAM) and that PostgreSQL could +be made to run a lot faster with some knowledgable configuration +tuning. I have not, however, been able to personally confirm +these reports. </p> <p> -SQLite was compiled with -O6 optimization and with +SQLite was tested in the same configuration that it appears +on the website. It was compiled with -O6 optimization and with the -DNDEBUG=1 switch which disables the many "assert()" statements in the SQLite code. The -DNDEBUG=1 compiler option roughly doubles the speed of SQLite. @@ -100,10 +110,10 @@ INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two') INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 4.027</td></tr> -<tr><td>MySQL:</td><td align="right"> 0.113</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 8.409</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.188</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 3.613</td></tr> +<tr><td>MySQL:</td><td align="right"> 0.086</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 8.672</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.286</td></tr> </table> <p>SQLite must close and reopen the database file, and thus invalidate @@ -123,10 +133,10 @@ INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hu COMMIT;<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 5.175</td></tr> -<tr><td>MySQL:</td><td align="right"> 2.444</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 0.858</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.739</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 4.430</td></tr> +<tr><td>MySQL:</td><td align="right"> 2.025</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 0.885</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.753</td></tr> </table> <p> @@ -147,10 +157,10 @@ SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br> SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 3.773</td></tr> -<tr><td>MySQL:</td><td align="right"> 3.023</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 6.281</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 6.247</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 3.274</td></tr> +<tr><td>MySQL:</td><td align="right"> 2.624</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 5.585</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 5.443</td></tr> </table> <p> @@ -163,7 +173,6 @@ store data as binary values where appropriate and can forego this conversion effort. </p> - <h2>Test 4: 100 SELECTs on a string comparison</h2> <blockquote> SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br> @@ -175,10 +184,10 @@ SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br> SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 16.726</td></tr> -<tr><td>MySQL:</td><td align="right"> 5.237</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 6.137</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 6.112</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 14.511</td></tr> +<tr><td>MySQL:</td><td align="right"> 4.616</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 5.966</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 5.918</td></tr> </table> <p> @@ -191,10 +200,10 @@ compariable to or better then PostgreSQL and MySQL. <blockquote> CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b); </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 0.510</td></tr> -<tr><td>MySQL:</td><td align="right"> 0.352</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 0.809</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.720</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 0.483</td></tr> +<tr><td>MySQL:</td><td align="right"> 0.304</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 0.779</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.637</td></tr> </table> <p> @@ -214,10 +223,10 @@ SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br> SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 5.318</td></tr> -<tr><td>MySQL:</td><td align="right"> 1.555</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 1.289</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 1.273</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 4.939</td></tr> +<tr><td>MySQL:</td><td align="right"> 1.335</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 1.165</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 1.144</td></tr> </table> <p> @@ -237,10 +246,10 @@ UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br> COMMIT;<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 1.828</td></tr> -<tr><td>MySQL:</td><td align="right"> 9.272</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 0.915</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.889</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 1.536</td></tr> +<tr><td>MySQL:</td><td align="right"> 7.281</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 0.817</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.726</td></tr> </table> <p> @@ -259,10 +268,10 @@ UPDATE t2 SET b=423958 WHERE a=25000;<br> COMMIT;<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 28.021</td></tr> -<tr><td>MySQL:</td><td align="right"> 8.565</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 10.939</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 11.199</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 29.318</td></tr> +<tr><td>MySQL:</td><td align="right"> 7.514</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 7.681</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 7.852</td></tr> </table> <p> @@ -282,10 +291,10 @@ UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' COMMIT;<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 48.739</td></tr> -<tr><td>MySQL:</td><td align="right"> 7.059</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 7.868</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 6.720</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 50.020</td></tr> +<tr><td>MySQL:</td><td align="right"> 5.841</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 5.346</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 5.393</td></tr> </table> <p> @@ -297,47 +306,53 @@ SQLite is slightly faster than MySQL. <blockquote> BEGIN;<br>INSERT INTO t1 SELECT * FROM t2;<br>INSERT INTO t2 SELECT * FROM t1;<br>COMMIT; </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 54.822</td></tr> -<tr><td>MySQL:</td><td align="right"> 1.512</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 4.423</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 2.386</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 57.834</td></tr> +<tr><td>MySQL:</td><td align="right"> 1.335</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 5.073</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 2.085</td></tr> </table> <p> The poor performance of PostgreSQL in this case appears to be due to its -synchronous behavior. The CPU was mostly idle during the 55 second run. +synchronous behavior. The CPU was mostly idle the test run. Presumably, +PostgreSQL was spending most of its time waiting on disk I/O to complete. +</p> + +<p> +SQLite is slower than MySQL because it creates a temporary table to store +the result of the query, then does an insert from the temporary table. +A future enhancement that moves data directly from teh query into the +insert table should double the speed of SQLite. </p> <h2>Test 11: DELETE without an index</h2> <blockquote> DELETE FROM t2 WHERE c LIKE '%fifty%'; </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 0.734</td></tr> -<tr><td>MySQL:</td><td align="right"> 0.888</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 5.405</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.731</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 0.733</td></tr> +<tr><td>MySQL:</td><td align="right"> 0.768</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 5.418</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.668</td></tr> </table> - <h2>Test 12: DELETE with an index</h2> <blockquote> DELETE FROM t2 WHERE a>10 AND a<20000; </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 2.318</td></tr> -<tr><td>MySQL:</td><td align="right"> 2.600</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 1.436</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.775</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 0.867</td></tr> +<tr><td>MySQL:</td><td align="right"> 2.068</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 1.453</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.745</td></tr> </table> - <h2>Test 13: A big INSERT after a big DELETE</h2> <blockquote> INSERT INTO t2 SELECT * FROM t1; </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 63.867</td></tr> -<tr><td>MySQL:</td><td align="right"> 1.839</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 3.971</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 1.993</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 66.099</td></tr> +<tr><td>MySQL:</td><td align="right"> 1.663</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 4.029</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 1.729</td></tr> </table> <p> @@ -357,20 +372,20 @@ INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen' COMMIT;<br> </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 1.209</td></tr> -<tr><td>MySQL:</td><td align="right"> 1.031</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 0.298</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.282</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 1.168</td></tr> +<tr><td>MySQL:</td><td align="right"> 0.866</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 0.288</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.155</td></tr> </table> <h2>Test 15: DROP TABLE</h2> <blockquote> DROP TABLE t1;<br>DROP TABLE t2; </blockquote><table border=0 cellpadding=0 cellspacing=0> -<tr><td>PostgreSQL:</td><td align="right"> 0.105</td></tr> -<tr><td>MySQL:</td><td align="right"> 0.015</td></tr> -<tr><td>SQLite 2.4:</td><td align="right"> 0.472</td></tr> -<tr><td>SQLite 2.4 (nosync):</td><td align="right"> 0.232</td></tr> +<tr><td>PostgreSQL:</td><td align="right"> 0.100</td></tr> +<tr><td>MySQL:</td><td align="right"> 0.012</td></tr> +<tr><td>SQLite 2.7.0:</td><td align="right"> 0.572</td></tr> +<tr><td>SQLite 2.7.0 (nosync):</td><td align="right"> 0.168</td></tr> </table> <p> |