[sqlite] Version 3.19.3 containing an important bug fix
A bug in the auto_vacuum logic for SQLite versions 3.16.0 through 3.19.2 can (rarely) lead to database corruption. SQLite version 3.19.3 has just been released to fix this bug. https://sqlite.org/ https://sqlite.org/download.html See https://www.sqlite.org/src/info/fda22108 for more information about the bug. The problem can only arise on databases that have auto_vacuum enabled. Nevertheless, all users are encouraged to update. If you have database files that are in auto_vacuum mode, you should consider running VACUUM on those databases after updating, in order to clear any database inconsistencies that might have been introduced by this bug. If you encounter any problems with this release, please send email to sqlite-users@mailinglists.sqlite.org or directly to me. Thanks. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.16.2
SQLite version 3.7.16.2 is now available on the SQLite website http://www.sqlite.org/ The 3.7.16.2 patch release contains a two-character change in the Windows OS interface that fixes a long-standing race condition that could lead to database corruption. This bug has apparently existed in the code every since version 3.0.0. The corruption opportunity arises as follows: (1) A process crashes or is killed while in the middle of a COMMIT, leaving behind a hot journal that needs to be recovered. (2) Two or more new processes attach to the database and try to run the recovery at the same time. If step (2) happens on a fast multi-core machine, and you are unlucky, then the xCheckReservedLock() method of the OS interface object might return a false positive result, deceiving one of processes into thinking that the recovery had already been accomplished and causing that process to delete the rollback journal and thereby corrupting the database. Note that this flaw was present in the Windows interface only. The unix/posix OS interface works correctly. For unix, the only thing that has changed between version 3.7.16.1 and 3.7.16.2 is the version number. This problem was found internally while conducting stress testing for other components of SQLite. We have never received a report of database corruption from the wild that can be traced back to this flaw. Nevertheless, it seems prudent to get a fix into circulation as quickly as possible. Please send email to the SQLite users mailing list, or directly to me, if you encounter any problems with this release. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Version 3.7.16.1
SQLite version 3.7.16.1 is now available on the SQLite website http://www.sqlite.org/ Version 3.7.16.1 is a patch release with only minor changes from version 3.7.16. The reason for this patch release is to fix a bug in the query optimizer enhancements that were part of version 3.7.15. The optimizer bug could cause an ORDER BY clause to be optimized out even though sorting was required, resulting in output that is not in the correct order. A few other obscure problems were also fixed with this release. See the change log at https://www.sqlite.org/releaselog/3_7_16_1.html for details. All users of SQLite versions 3.7.15 and 3.7.16 are encouraged to upgrade. As always, please let me know if you encounter any problems with this or any other release of SQLite. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.16
SQLite version 3.7.16 is now available on the SQLite website http://www.sqlite.org/ SQLite version 3.7.16 is a regularly scheduled release of SQLite. This release contains several langauge enhancements and improvements to the query optimizer. A list of the major enhancements and optimizations can be see on the change log at http://www.sqlite.org/releaselog/3_7_16.html There was one important bug fix (see Ticket fc7bd6358f, http://www.sqlite.org/src/info/fc7bd6358f) that addresses an incorrect query result that could have occurred in a three-way join where the join constraints compared INTEGER columns to TEXT columns. This bug had been in the code for time out of mind and had never before been reported, so we surmise that it is very obscure. Nevertheless, all users are advised to upgrade to avoid any future problems associated with this bug. Please send email to the SQLite Users mailing list (sqlite-users@sqlite.org) or directly to me if you encounter any problems with this new release. Thanks. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Version 3.7.15.1
SQLite version 3.7.15.1, a patch release, is now available on the SQLite website: http://www.sqlite.org/ This patch release fixes a single bug the managed to sneak into the 3.7.15 release from last week. Two lines of code changed and one assert() was added: http://www.sqlite.org/src/fdiff?v1=53b991af50dab230=74d72b1613aac386#chunk1 The complete patch includes the change above, and some new test cases, and the version number and configure script were updated. A description of the bug that was fixed is here: http://www.sqlite.org/src/info/a7b7803e8d1e869 The bug causes a NULL pointer dereference given some unusual but perfectly legal SQL. The bug is not data dependent and is thus not a security vulnerability (since if an attacker can inject arbitrary SQL into your application, then you have already been compromised). If you application does not use the unusual SQL construct necessary to tickle this bug (and most applications don't) then you are perfectly safe staying with whatever prior version of SQLite you are currently using. Nevertheless, upgrading to 3.7.15.1 is recommended. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.14.1
SQLite version 3.7.14.1 is now available on the SQLite website http://www.sqlite.org/ Version 3.7.14.1 is a patch release with minimal difference from 3.7.14. The changes in version 3.7.14.1 include a one-line bug fix in the SQLite core (see www.sqlite.org/src/fdiff?v1=22783f4275f=e74f9ed463b), a larger fix for a long-standing bug in the TCL interface, and a work-around for a compiler bug that can come up when building WinRT applications for ARM. The first bug, which can cause a segfault when trying to evaluate an obscure but perfectly valid LEFT JOIN, was the primary reason for this patch release. That particular bug was introduced in version 3.7.14. So if you have already upgraded to version 3.7.14, you should upgrade again to 3.7.14.1. But if you are still on 3.7.13 or earlier, the upgrade is not nearly so urgent. As always, please let me know if you encounter any problems. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.12
SQLite version 3.7.12 is now available on the SQLite website: http://www.sqlite.org/ An overview of the enhancements in this release can be seen here: http://www.sqlite.org/releaselog/3_7_12.html Please send email to the sqlite-users@sqlite.org mailing list if you encounter any problems with this release. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.9
SQLite version 3.7.9 is now available on the primary and on the backup websites: http://www.sqlite.org/ http://www2.sqlite.org/ http://www3.sqlite.org/ Version 3.7.9 is a periodic maintenance release. Upgrading from versions 3.7.6.3 and later is optional. Upgrading from prior versions is recommended. Additional information about this release can be found at: http://www.sqlite.org/releaselog/3_7_9.html http://www.sqlite.org/news.html As always, please let me know if you encounter any difficulty with this release. D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.7 released
SQLite version 3.7.7 is now available on the SQLite website: http://www.sqlite.org/ A list of changes is available at http://www.sqlite.org/releaselog/3_7_7.html Further information about this release can be seen at http://www.sqlite.org/news.html Please post on the SQLite mailing list (sqlite-users@sqlite.org) if you encounter any problems with this release. -- D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.6.3
SQLite version 3.7.6.3 is now available on the SQLite website http://www.sqlite.org/ http://www.sqlite.org/download.html Version 3.7.6.3 is a patch release that fixes an obscure but nasty bug in WAL-mode. Upgrading is recommended for all users. The bug is present in all prior releases of SQLite that support WAL. In prior releases of SQLite, if you set PRAGMA journal_mode=WAL, and if you set PRAGMA cache_size=N where N is very small (less than 10) and if you do a multi-statement transaction where the last SQLite statement prior to COMMIT is a SELECT statement that requires all of your cache memory to complete, then your COMMIT might be silently converted into a ROLLBACK. The database does not corrupt, but any changes you made to the database during the transaction will be lost. Since cache_size defaults to 2000, applications that never mess with cache_size (which is to say, the vast majority of applications) should never have a problem. But sometimes developers working on low-memory devices try to crank down cache_size in an effort to save memory. If you are one of those developers, you should probably think seriously about upgrading. Additional information: http://www.sqlite.org/src/info/2d1a5c67df http://www.sqlite.org/news.html The patch needed to fix this problem in any 3.7.x release of SQLite can be seen here: http://www.sqlite.org/src/fdiff?v1=b7fe4b8e51d51a06=4b2358556c88660a Please respond to the sqlite-users mailing list (sqlite-users@sqlite.org) or directly to me if you encounter any problems. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.6
On Apr 12, 2011, at 8:49 AM, D. Richard Hipp wrote: > SQLite version 3.7.6 is not available from the website: > http://www.sqlite.org/ The "not" in the sentence above should be "now", of course. Sorry for the typo. > > Version 3.7.6 is a regularly scheduled bi-monthly maintenance release. > Updating from version 3.7.5 is optional. Updating from versions prior to > 3.7.5 is recommended. > > A summary of changes in SQLite version 3.7.6 can be seen at > >http://www.sqlite.org/releaselog/3_7_6.html > > Please send email to the sqlite-users@sqlite.org mailing list, or directly to > me, if you encounter any problems. Thanks. > > D. Richard Hipp > d...@hwaci.com > > > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.6
SQLite version 3.7.6 is not available from the website: http://www.sqlite.org/ Version 3.7.6 is a regularly scheduled bi-monthly maintenance release. Updating from version 3.7.5 is optional. Updating from versions prior to 3.7.5 is recommended. A summary of changes in SQLite version 3.7.6 can be seen at http://www.sqlite.org/releaselog/3_7_6.html Please send email to the sqlite-users@sqlite.org mailing list, or directly to me, if you encounter any problems. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.0.1
SQLite version 3.7.0.1 is now available on the SQLite website: http://www.sqlite.org/ http://www.sqlite.org/download.html Version 3.7.0.1 is a patch release that fixes a bug in version 3.7.0 that can lead to database corruption if the same database file is written alternately by version 3.7.0 and version 3.6.23.1 or earlier. Additional information on this problem can be found at: http://www.sqlite.org/src/info/51ae9cad31 In addition, a typo in the OS/2 driver and a performance regression were fixed. The differences between 3.7.0 and 3.7.0.1 are minimal, but because of the possibility of database corruption, upgrading to version 3.7.0.1 is highly recommended. If you encounter any problems, please report them directly to me or to the sqlite-users@sqlite.org mailing list. D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.0
SQLite version 3.7.0 is now available on the website http://www.sqlite.org/ The most important change in version 3.7.0 is that SQLite now supports write-ahead logs as an optional method for transaction control, for improved performance and concurrency. Additional information can be found here: http://www.sqlite.org/wal.html We are actually already using the write-ahead logging feature on the SQLite website itself, in the Fossil DVCS that tracks all changes to the SQLite source tree. (Yes, the SQLite write-ahead log code is stored in an SQLite write-ahead log database - how's that for recursion!) http://www.sqlite.org/src The added concurrency of the write-ahead log journaling mode allows multiple users to be doing extended read operations, such as checking out historical versions of the SQLite code or looking at extended timelines simultaneously with developers making new checkins, adding or editing tickets, or actually rebuilding the entire 10-year source code database. The write-ahead log code has performed very well so far for us. Version 3.7.0 also marks the official cut-over to our new SQLite logo and a new color scheme for the website. We hope you like the new look. The 114-day time span since the previous release (version 3.6.23.1) is the longest span between consecutive releases in the 10-year history of SQLite. Much of that time was spent testing and stressing the new write-ahead log feature. This is probably the most thoroughly tested release of SQLite that we have every produced. And so even though the write-ahead logging feature is entirely new, we are very hopeful that the 3.7.0 release will prove to be stable and robust and ready for production use. Of course, if you do happen to run into problems, please let me know at once. Thanks! D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] New 3.7.0 snapshot - release estimated for July 22
The signature of the VFS has changed slightly - the xShmOpen() method has been removed. Implementations are now expected to automatically create the shared memory on the first call to xShmMap(). And the xShmClose() method has been renamed to xShmUnmap(). A new snapshot with these changes is now available in the usual place: http://www.sqlite.org/draft/download.html The target release date for version 3.7.0 has slipped until 2010-07-22 (a slip of one week). We were going to try to revise WAL so that it worked as a read-only database if write permission to key files was not available. But that was going to turn out to be a big, error- prone mess, and so we decided to back off and simply make it a limitation of WAL that a database could not be read or written in WAL mode if write permissions were lacking. Additional explanation at http://www.sqlite.org/draft/wal.html#readonly The documentation has also been enhanced (in a subsection immediately following the section on read-only databases linked above) to explain our decision to implement shared-memory using a mmapped file in the same directory as the original database. Comments and criticism of this decision are encouraged. Please evaluate the snapshot and provide feedback on this mailing list. The 2010-07-22 release date target assumes no more major problems. But we would rather encounter (and fix) a major problem before the release rather than afterwards. We will slip the release again if necessary. Your beta testing is *very* important. Thanks! D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database corruption on Linux ext3
An appliance manufacturer has discovered a database corruption issue on Linux using ext3. The issue is documented here: http://www.sqlite.org/draft/lockingv3.html#ext3-barrier-problem You are encouraged to submit comments, insights, criticism, and analysis to this mailing list. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please beta-test SQLite 3.7.0
We have scheduled the release of SQLite version 3.7.0 for Thursday, 2010-07-15. That date could yet change, but 2010-07-15 is our target. SQLite version 3.7.0 will feature the addition of a write-ahead log (WAL) capability for transaction control. See http://www.sqlite.org/draft/wal.html for additional information. WAL should increase performance and concurrency in many situations. Version 3.7.0 also has other changes, including a new feature that automatically creates transient indices to improve performance for some complex joins, and other performance enhancements. Version 3.7.0 has been long in the making. We've worked on it for months. We have checked in nearly 600 separate change-sets since 3.6.23.1. About 7.5% of the source code in version 3.7.0 is new. Version 3.7.0 is currently passing all of our extensive internal tests. We have verified it using our 41-point pre-release checklist (which takes several days to work through). And version 3.7.0 is currently in use running the www.sqlite.org website (in WAL mode). We have tested the new release about as much as we can. Additional help from you is needed: Please help us by beta-testing SQLite 3.7.0 in your application! Report any problems found on this mailing list. You can download daily snapshots from http://www.sqlite.org/draft/download.html and view updated documentation at http://www.sqlite.org/draft/index.html Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Development snapshots for SQLite 3.7.0 available
On the download page of the draft website: http://www.sqlite.org/draft/download.html you can now find amalgamations (the sqlite3.c source file) for development snapshots. These snapshots are intended for beta-testing only. Interfaces and file formats in these snapshots are subject to change. Nevertheless, these snapshots should be reasonably stable in the sense that they pass our internal tests. It will be a great help to our testing efforts if you will give one of these amalgamations a try in your software and report any problems you encounter to this mailing list. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: CRITICAL bug in sqlite3VdbeExec() code
-- Forwarded message --
From: jur...@ramzes.net
To: sqlite-users@sqlite.org
Date: Thu, 24 Jun 2010 11:15:20 +0200
Subject: CRITICAL bug in sqlite3VdbeExec() code
Dear friends,
This is my bug report.
sqlite version: 3.6.23.1, Check-in [2e6a462ceb]
file: vdbe.c
line: 971
function: sqlite3VdbeExec()
description:
Local variable "pOut is not properly initialized in some cases.
For example:
...
/* Opcode: Variable P1 P2 * P4 *
**
** Transfer the values of bound parameter P1 into register P2
**
** If the parameter is named, then its name appears in P4 and P3==1.
** The P4 value is used by sqlite3_bind_parameter_name().
*/
case OP_Variable: {/* out2-prerelease */
Mem *pVar; /* Value being transferred */
assert( pOp->p1>0 && pOp->p1<=p->nVar );
pVar = >aVar[pOp->p1 - 1];
if( sqlite3VdbeMemTooBig(pVar) ){
goto too_big;
}
sqlite3VdbeMemShallowCopy(pOut, pVar, MEM_Static); <<<--- ***
HERE***
UPDATE_MAX_BLOBSIZE(pOut);
break;
}
...
Function sqlite3VdbeMemShallowCopy() is called with pOut == NULL, of
course with access violation (in Windows version).
"case OP_Variable" I have checked under M$ debugger (not coverable
errors in my PHP/PDO-Sqlite script with parameters binding), but
possible other cases in massive switch statement, I think ...
greetings from Poland,
JureKL.
--- End forwarded message -
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Oracle joins the SQLite Consortium
The SQLite developers are pleased to announce that Oracle has joined the SQLite Consortium. The SQLite Consortium is a collaboration of major users of SQLite designed to ensure the continuing vitality and independence of SQLite. In exchange for sponsorship, SQLite Consortium Members receive enterprise-level technical support, access to proprietary SQLite add-ons such as the SQLite Encryption Extension and TH3, and guarantees that SQLite will continue to be actively maintained and developed and that it will not fall under the control of a competitor. Oracle uses the parser, code generator, and virtual machine from SQLite in its Berkeley DB product. Additional information about Berkeley DB's SQL API is available at http://www.oracle.com/technology/products/berkeley-db/sql.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-announce] HELP : sqlite execute low speed in ARM9+Linux embadded system.
Questions such as this belong on sqlite-users@sqlite.org, not on
sqlite-annou...@sqlite.org
. Thank you.
On Jun 16, 2010, at 11:24 PM, backup wrote:
> hi, everyone,
> I program in an embaded system: ARM9 soc S3C2410 +Linux 2.4+sqlite
> 3.3.
> All data store in NAND FLASH , the file system is YAFFS .
> My question is the function "sqlite3_exec(.)" take too many
> seconds,
> the following is my source code, please see the "printf" 's comment,
> every "sqlite3_exec(.)" takes 5 seconds, but how to reduce
> the time?
>
> //-- start of code
> #define RECORD_NUM 1
>
> char QueryData(void)
> {
> struct timeval tpstart,tpend;
> float timeuse;
>
> char ErrorFlag=1;
> unsigned int di,dj,dk;
> DataBuffer[0]='\0'; //DataBuffer is goblal variable
> sprintf(sqlstr,"select * from db where isSent=0 limit
> %d;",RECORD_NUM);
> p(semid);
>
> gettimeofday(,NULL);
> rc = sqlite3_exec(db, sqlstr, SQLCallBack, 0, );
> gettimeofday(,NULL);
> timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
> tpend.tv_usec-tpstart.tv_usec;
> timeuse/=100;
> printf("sqlite3 select * Used Time：%f\n",timeuse); // print value :5
> seconds
>
>
> v(semid);
>
> sprintf(sqlstr,"update db set isSent=1 where ID in (select ID from
> db where isSent=0 limit %d);",RECORD_NUM);
> p(semid);
> gettimeofday(,NULL);
>
>
> rc = sqlite3_exec(db, sqlstr, 0, 0, );
>
> gettimeofday(,NULL);
> timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
> tpend.tv_usec-tpstart.tv_usec;
> timeuse/=100;
> printf("sqlite3 update Used Time：%f\n",timeuse); //print value : 5s
>
>
>
> v(semid);
> }
> static int SQLCallBack(void *NotUsed, int argc, char **argv, char
> **azColName)
> {
>
> struct timeval tpstart,tpend;
> float timeuse;
>
>
> int i;
> char tstr[500];
> gettimeofday(,NULL);
>
> // argv[0] is ID ,no use for server
> sprintf(tstr,"'%s',",argv[1]);
> strcat(DataBuffer,tstr);
> for(i=2; i<argc; i++){
> // printf("%s,",argv[i] ? argv[i] : "N");
> sprintf(tstr,"%s,",argv[i]);
> strcat(DataBuffer,tstr);
> }
> strcat(DataBuffer,"\n");
>
> gettimeofday(,NULL);
> timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+
> tpend.tv_usec-tpstart.tv_usec;
> timeuse/=100;
> printf("SQLCallBack Used Time：%f\n",timeuse); // print value :
> 0.000280s
>
> return 0;
> }
>
> //-- end of code
>
>
> 网易为中小企业免费提供企业邮箱（自主域名）
> ___
> sqlite-announce mailing list
> sqlite-annou...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite turns 10 years old
The first code check-in for SQLite occurred on 2000-05-29 14:26 UTC - ten years ago today. http://www.sqlite.org/src/timeline?c=2000-05-29+14:26 Some of the code in SQLite (such as the Lemon parser generator and the printf implementation) dates back to the late 1980s. But the core of SQLite was not started until 10 years ago. Ten years is not that long ago, though it has been long enough to amass 7114 check-ins - an average of 2.1 check-ins per day. If you are overseeing such a project, 10 years seems like forever. It has hard for me to remember a time when I wasn't working on SQLite. In celebration of SQlite's 10th birthday, we are revamping the look of the SQLite website. You can see a preview of the new look at http://www.sqlite.org/draft/index.html We won't push the new look out to the main website until we do the next release which might not be until July or maybe even August. We had hoped to have SQLite version 3.7.0 ready in time for the 10th birthday celebration, but http://www.sqlite.org/draft/wal.html is taking longer than planned. We want to make sure to get things right so that SQLite lives to see its 20th and 30th birthdays! Thanks, everybody, for helping to make SQLite the most widely deployed SQL database engine in the world. And Happy 10th Birthday to SQLite! D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to compile RTREE using eclipse on windows
The following message is forwarded from private email. (I know nothing about eclipse or windows so I can't help.) Please reply to this list with CC to k52...@hanmail.net if you have any suggestions. > Hello > I am a student studying in south Korea about sqlite. > I have sent the e-mail to you before to ask something about rtree. > But I have not solve the problem yet, so I send the e-mail one more > time. > > I downloaded sqlite-3.6.23.1.tar.gz file and then I execute > CONFIGURE and MAKE in linux. > After that, I compiled with config.h, parse.h, parse.c, opcode.h, > opcode.c, sqlite3.h, keywordhash.h file and Sqlite-3.6.3.23.1’s > other source file in windows eclipse environment. > > > > I except a few files like fts1…fts3 which make some error. > In this situation, compile is ok. Works well. > However.. > > > > It doesn’t make error to compile giving option ,– > DSQLITE_ENABLE_RTREE=1, like this one, > But it brings this problem. > > > > Sq.exe’s operation is stopped > you can find the way to solve this problem though on-line. > Close the program after checking solving method though on-line > Close the program > > The program is closed by making table. > When I make Rtree table also I can see the same problem.(the program > is closed) > > I can not use the amalgamation vision to make Sqlite program. > > Please let me know how to compile in window’s eclipse environment > with Sqlite-3.6.23.1.tar.gz file > Thank you for giving many information. Thank you. > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed: drop support for LinuxThreads
On May 19, 2010, at 6:35 PM, Mike Frysinger wrote: > On Wednesday 12 May 2010 11:43:13 D. Richard Hipp wrote: >> If we drop support for (the non-standard, non-compliant) LinuxThreads >> threading library and instead support only standard Posix threads >> implemented using NPTL, beginning with SQLite release 3.7.0, what >> disruptions might this cause? >> >> Is anybody still using LinuxThreads? > > yes. there are some architectures that lack NPTL at all, and most > ports using > uClibc (the most common embedded port) does not have NPTL support. > > is leaving the code alone that big of a hassle ? Yes, it is a huge hassle. And it has already been removed. Beginning with version 3.7.0, SQLite requires posix threads if you want to use it in a multi-threaded environment on unix. LinuxThreads is not an acceptable substitute. Of course, you can still take the sensible approach and avoid multithreading all together. Failing that, SQLite should still work on LinuxThreads if you avoid passing database connections from one thread to another. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Proposed: drop support for LinuxThreads
If we drop support for (the non-standard, non-compliant) LinuxThreads threading library and instead support only standard Posix threads implemented using NPTL, beginning with SQLite release 3.7.0, what disruptions might this cause? Is anybody still using LinuxThreads? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Proposed new sqlite3_open_v3() interface
Community feedback is requested for the following proposed new SQLite C API: int sqlite3_open_v3(const char*, sqlite3**, int, const char*); The new database connection constructor would work exactly like sqlite3_open_v2() with the following exceptions: (1) Foreign Key constraints would be enabled by default. (2) Recursive triggers would be enabled by default. (3) The default database file format would be format 4 (meaning that new databases would be unreadable by versions of SQLite prior to 3.1.3). (4) The misfeature of allowing double-quoted identifiers to fall back to being strings if no matching identifier could be found would be disabled. (5) PRIMARY KEY columns would automatically be NOT NULL. (The would not change the current INTEGER PRIMARY KEY behavior, only non-INTEGER PRIMARY KEYs.) We are very hesitant to make any of the above changes to SQLite for the default case since they run the risk of breaking legacy code. By making the newer behavior the default only for the new interface sqlite3_open_v3() and leaving the behavior unchanged for legacy constructors, we hope to encourage newer applications to use the newer features while avoiding breakage of older applications. Question 1: Are there any objections to this approach? Question 2: Are there other foibles that we could correct using sqlite3_open_v3? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] page_size
On Apr 21, 2010, at 4:37 PM, Pavel Ivanov wrote: > I don't know anything about internal support of pages bigger than 32k. > But I want to warn you: each table and each index in SQLite occupy at > least 1 database page. So let's say you have 4 tables with 1 > additional index each (besides 'integer primary key' one). With 256k > pages this schema will result in a database of more than 2 Mb without > any data stored. Is your embedded FS okay with this storage amount? Furthermore, SQLite changes whole pages at a time. So in a database with 256kB pages, if you change a single byte, you still have to write 256kB both to the rollback journal and to the database file. > > > Pavel > > On Tue, Apr 20, 2010 at 4:51 PM, Tom Broadbent > <tom.broadb...@plasticlogic.com> wrote: >> i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k >> (below). is this limitation still valid? >> >> we have an embedded FS that is _very_ slow and performs best w/ a >> write page size of 256k. will bad things happen if i configure >> SQLite w/ 256k pages? >> >> thanks >> tom >> >> Maximum Database Page Size >> >> An SQLite database file is organized as pages. The size of each >> page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The >> default value for SQLITE_MAX_PAGE_SIZE is 32768. The current >> implementation will not support a larger value. >> >> It used to be the case that SQLite would allocate some stack >> structures whose size was proportional to the maximum page size. >> For this reason, SQLite would sometimes be compiled with a smaller >> maximum page size on embedded devices with limited stack memory. >> But more recent versions of SQLite put these large structures on >> the heap, not on the stack, so reducing the maximum page size is no >> longer necessary on embedded devices. There is no longer any real >> reason to lower the maximum page size. >> >> __ >> This email has been scanned by the MessageLabs Email Security System. >> For more information please visit http://www.messagelabs.com/email >> __ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT storage
On Apr 17, 2010, at 2:57 PM, slowpoison wrote: > > I want to know whether a TEXT field, when stored, will always take the > exact amount of space allocated for it in the schema definition. So, > when I say TEXT(1024), is the field guaranteed to take 1024 bytes on > disk per record or is there a non-trivial storage scheme at work > similar to VARCHAR types, which tries to optimize space usage. The latter. SQLite stores all strings as if they were in a VARCHAR(10). (That's VARCHAR(one-billion).) A 5-byte string requires 6 bytes of disk (one byte for the string size and 5 for the string itself). A one-billion byte string requires one-billion-and- five bytes of disk (5 bytes for the string size and one billion bytes to hold the string itself.) D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on the VdbeCursor structure changes
On Apr 17, 2010, at 1:02 PM, Paul Shaffer wrote:
> Due to changes in VdbeCursor structure, this code for 3.6.16 won't
> compile
> anymore:
>
> else if(pC->pseudoTable)
> {
> *prowid = pC->iKey;
> }
>
Your application should not be messing with internal data structures
of SQLite, all of which are subject to change without notice (as you
have discovered.)
Perhaps if you explain to us what you are trying to accomplish we will
be better able to help you.
> and for 3.6.23 would have to be replaced by something like this:
>
> else if(pC->pseudoTableReg>0)
> {
> //*prowid =
> }
>
> My problem is that after about an hour of reverse engineering I can't
> figure out a way to get the row id with the new code. Any help
> appreciated.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help test the latest query planner changes
On Apr 16, 2010, at 11:52 AM, Max Vlasov wrote: > > SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date, > StihiAuthCandidates.Num as Num FROM StihiAuthors > INNER JOIN StihiAuthCandidates ON > StihiAuthors.Id=StihiAuthCandidates.AuthorId > LEFT JOIN StihiPoems ON Date=StihiPoems.PoemDate AND > Num=StihiPoems.PoemNum > WHERE StihiAuthors.IsFav=1 AND StihiPoems.rowid Is Null > > sqlite3-amalgamation-3_6_23_1.dll > reported 747 milliseconds returning 22,642 rows > > sqlite3-20100415132938.dll > reported 563 milliseconds Thanks for the report! It is reassuring to know that the recent changes actually did some good! D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Apr 15, 2010, at 10:22 AM, P Kishor wrote: > On Thu, Apr 15, 2010 at 3:36 AM, Wiktor Adamski > <bardzotajneko...@interia.pl> wrote: >> SQLite version 3.6.23.1 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> create table t(a); >> sqlite> insert into t values(1); >> sqlite> insert into t values(2); >> sqlite> select * from (select * from t limit 1) >> ...> union all >> ...> select 3; >> 1 >> sqlite> > > > fwiw, the above works correctly in 3.6.23 (see below), so that minor > version number bump might have introduced the issue The problem is with UNION ALL, not UNION. The problem was actually introduced on 2005-10-06 for version 3.2.8 by check-in: http://www.sqlite.org/src/info/edca8913ca012fc0c17343a27f819de95147b1bd This problem has been in every version of SQLite for the past four and a half years and nobody before now has noticed. > > punk...@lucknow ~$sqlite3 > -- Loading resources from /Users/punkish/.sqliterc > SQLite version 3.6.23 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE t (a); > sqlite> INSERT INTO t VALUES (1); > sqlite> INSERT INTO t VALUES (2); > sqlite> SELECT * FROM t; > a > -- > 1 > 2 > sqlite> SELECT * FROM t LIMIT 1; > a > -- > 1 > sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1); > a > -- > 1 > sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1) UNION SELECT 3; > a > -- > 1 > 3 > sqlite> > > > > -- > Puneet Kishor http://www.punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Commons Fellow, http://sciencecommons.org/about/whoweare/ > kishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is > science > = > == > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please help test the latest query planner changes
We've been tweaking of the SQLite query planner in an effort to help joins run faster. But whenever you tweak the query planner, there is always a risk that some query plans might become significantly slower. Your help in identifying any performance regressions is greatly appreciated. Check-in f538d759beda67 of SQLite should be a drop-in replacement for SQLite versions 3.6.22 or 3.6.23. Please test out check-in f538d759beda67 in your applications as you are able and let us know if you see any issues. You can download the f538d759beda67 check-in directly from http://www.sqlite.org/src/info/f538d759be Or, you can get a prebuilt amalgamation at http://www.sqlite.org/sqlite3-20100415132938.zip We are looking forward to your feedback, both positive and negative. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Content filtered message notification
SQLite is a software library used by a wide variety of computer programs, including iTunes, Skype, Adobe Acrobat, Firefox, Chrome, Dropbox, and others. SQLite is not something that is "installed". It is a component part of those other programs. We have found that errors like the one you are seeing often go away if you reinstall iTunes. On Apr 11, 2010, at 6:35 AM, sqlite-users-boun...@sqlite.org wrote: > The attached message matched the sqlite-users mailing list's content > filtering rules and was prevented from being forwarded on to the list > membership. You are receiving the only remaining copy of the > discarded message. > > > From: "Femi" <f...@planet4networks.com> > Date: April 11, 2010 4:21:46 AM EDT > To: <sqlite-users@sqlite.org> > Subject: procedure entry point error message > Reply-To: <f...@planet4networks.com> > > > > > Hi > I hope you can help every time I start my pc I have this error > message as I am not a programmer I did not put SQL Lite on my > machine for programming. > > Thanks in advance > > Femi Omomo > Email: f...@planet4networks.com > Website: www.planet4networks.com > Tel: 01293 510051 UK > > > > > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 'DatabaseError: database disk image is malformed'
On Apr 10, 2010, at 10:21 AM, Rutger Hofman wrote: > [I first posted this on the Trac user list. There, I was referred to > the > sqlite mailing lists.] > > I am running 3 trac instances on a FreeBSD server; trac 0.11b, sqlite3 > 3.4.1, pysqlite-2.3.5. More or less simultaneously (at least within a > few days) all three trac databases got corrupted. You can check for > yourself at e.g. http://trac.rfidguardian.org:8000/trac-0-11b, it > shows > a python stack trace with at bottom a database disk image is > malformed. > > When I have a look at one of the sqlite3 databases, the header block > is > obviously corrupted: > > 000 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 SQLite format 3. > 010 04 00 01 01 00 40 20 20 00 01 D5 6D 00 00 00 00 .@ ...m > 020 00 00 24 AA 00 00 04 9A 38 32 2E 39 35 2E 31 35 ..$.82.95.15 > 030 37 2E 32 31 20 2D 20 2D 20 5B 30 36 2F 41 70 72 7.21 - - [06/Apr > 040 2F 32 30 31 30 20 31 34 3A 33 30 3A 31 30 5D 20 /2010 14:30:10] > 050 22 47 45 54 20 2F 74 72 61 63 2D 30 2D 31 31 62 "GET /trac-0-11b > 060 20 48 54 54 50 2F 31 2E 31 22 20 35 30 30 20 2D HTTP/1.1" 500 - > 070 0A 74 72 75 6E 6B 2F 73 72 63 2F 75 69 2F 73 74 .trunk/src/ui/st > 080 64 69 6E 20 48 54 54 50 2F 31 2E 31 22 20 35 30 din HTTP/1.1" 50 > 090 30 20 2D 0A 39 39 34 20 48 54 54 50 2F 31 2E 31 0 -.994 HTTP/1.1 > 0A0 22 20 35 30 30 20 2D 0A 2E 30 22 20 35 30 30 20 " 500 -..0" 500 > 0B0 2D 0A 20 35 30 30 20 2D 0A 54 50 2F 31 2E 30 22 -. 500 -.TP/1.0" > 0C0 20 35 30 30 20 2D 0A 22 20 35 30 30 20 2D 0A 50 500 -." 500 -.P > 0D0 2F 31 2E 31 22 20 35 30 30 20 2D 0A 31 2E 30 22 /1.1" 500 -.1.0" > 0E0 20 35 30 30 20 2D 0A 30 30 39 2D 30 37 2D 32 34 500 -.009-07-24 > 0F0 54 32 30 25 33 41 35 30 25 33 41 35 35 5A 25 32 T20%3A50%3A55Z%2 All of that text in the header that looks like Apache logfile entires - none of that should be in the first page of the database file. Nor does that text appear to be in a format that would appear anywhere in a valid SQLite database. So I'm guessing that some other process has decided to open the SQLite database and overwrite it with log file information. What does Trac call its database? something.db? Lots of programs use the ".db" suffix. Perhaps one of these other programs mistook the SQLite database for a file in a different format and tried to overwrite it with new information that is in some other (non-SQLite) format. Just a guess. You are unlikely to be able to recover any useful content from a database that has been so thoroughly trashed. > > E.g. bytes 0x01c..0x01f should give the database size in 1K pages; > that > should be 1408 / 1024 = 13750 = 0x35b6. It is 0x00. And > other fields are as obviously broken. > > What can be the cause of this simultaneous corruption? Is there a > way to > recover the database? > > Alas, we had a misunderstanding w/ our web host on backup policy; he > turns out to keep multiple versions of the current files, but no > rollback... > > Thanks, > > Rutger Hofman > VU Amsterdam > http://www.rfidguardian.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Android database corruption
On Apr 6, 2010, at 9:15 AM, cliff 2 wrote: > > > cliff 2 wrote: >> >> >> Thanks very much for the quick response! Unfortunately, the android >> SQLiteDatabase class deletes the database when it hits this, so it >> doesn't >> give me a chance to do that. However I do have a channel to the >> android db >> developers, so I will forward this onto them, and hopefully they >> can do >> something with it. >> >> Thanks again for your help. >> >> >> >> D. Richard Hipp wrote: >>> >>> >>> ... >>>> >>>> Not sure at this point what kind of work-arounds are available to >>>> you. >>> >>> >>> To amplify what Shane said, the corruption is probably just in the >>> page cache, not in the database file itself. So if you are able to >>> close and reopen the database connection when you run into problems, >>> that might provide a work-around (assuming we are guessing correctly >>> at the root cause of your problem.) >>> >>> D. Richard Hipp >>> d...@hwaci.com >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> > > We changed the db in question to only open in readonly mode when > doing a > select rather than using a writable connection all the time. This has > greatly reduced the frequency of this error occurring, though it still > occurs. Given that the closing and opening of the db when this > happens isn't > possible. Is there anything else you can think of that will further > reduce > the frequency of this occurring? Is there an operation that we can > avoid for > example? Statically link your application against SQLite 3.6.23 instead of using the SQLite 3.5.9 that is found on Android. The bug you are hitting was fixed in SQLite 3.6.2. > > Even if the android guys can fix their end, there will still be a > large > number of devices deployed with the current version of android and > sqlite, > therefore anything we can do to minimize this would be useful. > -- > View this message in context: > http://old.nabble.com/Android-database-corruption-tp28044218p28151650.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it safe to backup an in-use sqlite3 database by copying the underlying database file?
On Apr 5, 2010, at 5:42 PM, Joshua Redstone wrote: > Hi sqlite-users, > I'm looking for an easy way to backup a sqlite3 database while it is > in use. If I could get away with linux 'cp', that'd be easier than > writing c++ code according to the online backup API > (http://www.sqlite.org/backup.html > ), and also it has the advantage that it would not block writes. > Initially I thought that 'cp' would be safe because sqlite is crash- > safe, but then I realized that 'cp' is not atomic, so it's possible > it won't work. > Thoughts? Right. "cp" is not atomic. "cp" will *usually* work, but sometimes you will get unluck and the database will change in the middle of the "cp" and what you end up with will be the first have of one database and the second half of a different database. The backup API is safer. > Thanks, > Josh > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in 3.6.23?
Line 40671 + 0x1c bytes >> MyApp.exe!sqlite3VdbeExec(Vdbe * p=0x) Line 55684 + 0x9 >> bytes >> MyApp.exe!sqlite3Step(Vdbe * p=0x) Line 51342 + 0x7 bytes >> MyApp.exe!sqlite3_step(sqlite3_stmt * pStmt=0x002686b8) Line 51404 >> + 0x7 bytes >> MyApp.exe!sqlite3_exec(sqlite3 * db=0x002686b8, const char * >> zSql=0x01f2b798, int (void *, int, char * *, char * *)* >> xCallback=0x, void * pArg=0x, char * * >> pzErrMsg=0x00e7efe0) Line 11308 + 0x6 bytes >>> MyApp.exe! >>> CMySQLWrapper >>> ::Exec >>> (ATL::CStringT<char,StrTraitMFC_DLL<char,ATL::ChTraitsCRT > >>> > query="UPDATE tablename SET field=12345;") Line 1335 + 0x27 bytes >> >> >> Does this seem to be a bug? Is there other info that is needed? >> >> -- Kevin >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.6.23 may cause memory overrun problem
On Apr 2, 2010, at 8:34 AM, Daniel Lin wrote:
> Let me describe the situation which I found:
>
> line 741 calculated as 17 bytes.
> line 746 memcpy the zP4's original allocated size is 16 bytes only.
>
> So, on line 746, it will copy one byte unknown data on last byte to
> the new buffer.
http://www.sqlite.org/src/ci/937b931a7c
>
> Or, this problem is occurs by the line 741, it calculate the wrong
> number?
>
> On Fri, Apr 2, 2010 at 19:52, D. Richard Hipp <d...@hwaci.com> wrote:
>
> On Apr 2, 2010, at 6:15 AM, Daniel Lin wrote:
>
> I've read the test page which you provide.
> But, I still think I'm right. Because Borland C++ Builder's Code
> Guard detect by following method:
>
> 1. Make special tag on 'malloc' memory boundry.
> 2. Check the memcpy is overrun the previous malloc boundry.
>
> I've traced back the callee & caller's code, I'm 80% sure your code
> seems wrong on that line.
>
> That's why I still think your code has problem on that line.
> And I still don't know why my modification will cause malfunction.
> Is there any sample could show the malfunction?
>
> Maybe dmalloc could also help the checking issue. (But, I'm not sure
> it could help on this overrun problem.)
>
> 0708 void sqlite3VdbeChangeP4(Vdbe *p, int addr, const char *zP4,
> int n){
> ...
> 0736 }else if( n==P4_KEYINFO ){
> 0737 KeyInfo *pKeyInfo;
> 0738 int nField, nByte;
> 0739
> 0740 nField = ((KeyInfo*)zP4)->nField;
> 0741 nByte = sizeof(*pKeyInfo) + (nField-1)*sizeof(pKeyInfo-
> >aColl[0]) + nField;
> 0742 pKeyInfo = sqlite3Malloc( nByte );
> 0743 pOp->p4.pKeyInfo = pKeyInfo;
> 0744 if( pKeyInfo ){
> 0745 u8 *aSortOrder;
> 0746 memcpy((char*)pKeyInfo, zP4, nByte);
> 0747 aSortOrder = pKeyInfo->aSortOrder;
> 0748 if( aSortOrder ){
> 0749 pKeyInfo->aSortOrder = (unsigned char*)
> >aColl[nField];
> 0750 memcpy(pKeyInfo->aSortOrder, aSortOrder, nField);
> 0751 }
> 0752 pOp->p4type = P4_KEYINFO;
> 0753 }else{
> 0754 p->db->mallocFailed = 1;
> 0755 pOp->p4type = P4_NOTUSED;
> 0756 }
>
> A pointer to the object to be copied is passed in as parameter zP4.
> The size of the object is computed on line 741. Sufficient space to
> copy the object is allocated on line 742. The copy occurs on line
> 746. Lines 747 through 751 copy over some auxiliary information
> into the tail the same memory allocation as is used by the original
> object. The code is correct is stands.
>
> To see why your change will cause a malfunction, all you have to do
> is run the test suite.
>
> Further discussion to sqlite-users@sqlite.org, please.
>
>
>
>
>
> On Fri, Apr 2, 2010 at 11:34, D. Richard Hipp <d...@hwaci.com> wrote:
>
> On Apr 1, 2010, at 9:34 PM, Daniel Lin wrote:
>
> Hi, Richard,
>
> Still not got your reply.
> I've changed the line, and it works well.
> So, I don't know when will the malfunction will occur.
>
> Have you ever used the codeguard like utility to check the sqlite at
> runtime?
>
>
> http://valgrind.org/
> http://www.sqlite.org/testing.html
>
>
>
> On Thu, Mar 25, 2010 at 08:47, Daniel Lin <dlin...@gmail.com> wrote:
> Why, I found the nByte which calculated is 17 bytes.
> But the data structure which come from is only 16 bytes.
>
> That let the code guard noticed me it maybe wrong.
>
>
> On Wed, Mar 24, 2010 at 19:57, D. Richard Hipp <d...@hwaci.com> wrote:
>
> On Mar 24, 2010, at 7:11 AM, Daniel Lin wrote:
>
> About this bug, I found change the code to following will let it
> workable.
> But, I require your confirm.
>
> memcpy(pKeyInfo, zP4, sizeof(*pKeyInfo));
>
> The SQLite code is correct as written. Your change will cause
> SQLite to malfunction.
>
> Check-in http://www.sqlite.org/src/ci/be27897991 is an attempt to
> suppress the warning from code guard. But as I have no way of
> testing to see if the change obtained that goal.
>
>
>
>
> On Wed, Mar 24, 2010 at 19:04, Daniel Lin <dlin...@gmail.com> wrote:
> Dear Dr.,
>
> I use Borland C++ Builder with code guard checking function enabled
> mode . (on 32 bits Windows XP)
>
> I found in an amalgamation sqlite.c may cause memory overrun.
>
> SQLITE_PRIVATE void sqlite3VdbeChangeP4(Vdbe *p, int addr, const
> char *zP4, int n){
> ...
> pOp->p4.pKeyInfo = pKeyInfo;
> if (pKeyInfo ){
>u8 *aSortOrder;
>memcpy(pKeyInfo, zP4, nByte); /* this line force copy 17 bytes
> from 16 bytes structure */
>aSortOrder = pKeyInfo->aSortOrder;
>
Re: [sqlite] sqlite 3.6.23 may cause memory overrun problem
On Apr 2, 2010, at 6:15 AM, Daniel Lin wrote:
>
> I've read the test page which you provide.
> But, I still think I'm right. Because Borland C++ Builder's Code
> Guard detect by following method:
>
> 1. Make special tag on 'malloc' memory boundry.
> 2. Check the memcpy is overrun the previous malloc boundry.
>
> I've traced back the callee & caller's code, I'm 80% sure your code
> seems wrong on that line.
>
> That's why I still think your code has problem on that line.
> And I still don't know why my modification will cause malfunction.
> Is there any sample could show the malfunction?
>
> Maybe dmalloc could also help the checking issue. (But, I'm not sure
> it could help on this overrun problem.)
0708 void sqlite3VdbeChangeP4(Vdbe *p, int addr, const char *zP4, int
n){
...
0736 }else if( n==P4_KEYINFO ){
0737 KeyInfo *pKeyInfo;
0738 int nField, nByte;
0739
0740 nField = ((KeyInfo*)zP4)->nField;
0741 nByte = sizeof(*pKeyInfo) + (nField-1)*sizeof(pKeyInfo-
>aColl[0]) + nField;
0742 pKeyInfo = sqlite3Malloc( nByte );
0743 pOp->p4.pKeyInfo = pKeyInfo;
0744 if( pKeyInfo ){
0745 u8 *aSortOrder;
0746 memcpy((char*)pKeyInfo, zP4, nByte);
0747 aSortOrder = pKeyInfo->aSortOrder;
0748 if( aSortOrder ){
0749 pKeyInfo->aSortOrder = (unsigned char*)
>aColl[nField];
0750 memcpy(pKeyInfo->aSortOrder, aSortOrder, nField);
0751 }
0752 pOp->p4type = P4_KEYINFO;
0753 }else{
0754 p->db->mallocFailed = 1;
0755 pOp->p4type = P4_NOTUSED;
0756 }
A pointer to the object to be copied is passed in as parameter zP4.
The size of the object is computed on line 741. Sufficient space to
copy the object is allocated on line 742. The copy occurs on line
746. Lines 747 through 751 copy over some auxiliary information into
the tail the same memory allocation as is used by the original
object. The code is correct is stands.
To see why your change will cause a malfunction, all you have to do is
run the test suite.
Further discussion to sqlite-users@sqlite.org, please.
>
>
> On Fri, Apr 2, 2010 at 11:34, D. Richard Hipp <d...@hwaci.com> wrote:
>
> On Apr 1, 2010, at 9:34 PM, Daniel Lin wrote:
>
> Hi, Richard,
>
> Still not got your reply.
> I've changed the line, and it works well.
> So, I don't know when will the malfunction will occur.
>
> Have you ever used the codeguard like utility to check the sqlite at
> runtime?
>
>
> http://valgrind.org/
> http://www.sqlite.org/testing.html
>
>
>
> On Thu, Mar 25, 2010 at 08:47, Daniel Lin <dlin...@gmail.com> wrote:
> Why, I found the nByte which calculated is 17 bytes.
> But the data structure which come from is only 16 bytes.
>
> That let the code guard noticed me it maybe wrong.
>
>
> On Wed, Mar 24, 2010 at 19:57, D. Richard Hipp <d...@hwaci.com> wrote:
>
> On Mar 24, 2010, at 7:11 AM, Daniel Lin wrote:
>
> About this bug, I found change the code to following will let it
> workable.
> But, I require your confirm.
>
> memcpy(pKeyInfo, zP4, sizeof(*pKeyInfo));
>
> The SQLite code is correct as written. Your change will cause
> SQLite to malfunction.
>
> Check-in http://www.sqlite.org/src/ci/be27897991 is an attempt to
> suppress the warning from code guard. But as I have no way of
> testing to see if the change obtained that goal.
>
>
>
>
> On Wed, Mar 24, 2010 at 19:04, Daniel Lin <dlin...@gmail.com> wrote:
> Dear Dr.,
>
> I use Borland C++ Builder with code guard checking function enabled
> mode . (on 32 bits Windows XP)
>
> I found in an amalgamation sqlite.c may cause memory overrun.
>
> SQLITE_PRIVATE void sqlite3VdbeChangeP4(Vdbe *p, int addr, const
> char *zP4, int n){
> ...
> pOp->p4.pKeyInfo = pKeyInfo;
> if (pKeyInfo ){
> u8 *aSortOrder;
> memcpy(pKeyInfo, zP4, nByte); /* this line force copy 17 bytes
> from 16 bytes structure */
> aSortOrder = pKeyInfo->aSortOrder;
> if( aSortOrder ){
> pKeyInfo->aSortOrder = (unsigned char*)>aColl[nField];
> memcpy(pKeyInfo->aSortOrder, aSortOrder, nField);
> }
> pOp->p4type = P4_KEYINFO;
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
>
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
>
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dead link on sqlite-announce info page
On Apr 2, 2010, at 12:20 AM, H. Phil Duby wrote: > Hi All, > > I just subscribed to the announce mailing list. The welcome message > includes a link to > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce, > which has a > link to the sqlite-announce > Archives<http://sqlite.org:8080/pipermail/sqlite-announce/>. > I get a 404 error for the page at > http://sqlite.org:8080/pipermail/sqlite-announce/ That is a bug in the mailing list manager software (Mailman) which nobody I have every talked to knows how to fix. I and several other developers have searched and searched and yet we have no idea where in the code that URL is generated. > > -- > mMerlin > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] REQUEST: Implement APDB like PRAGMA for CHS access
On Apr 1, 2010, at 12:33 PM, Noah Hart wrote: > Maybe SQLite could implement a PRAGMA to use the > > physical memory locator (CHS# - Cylinder, Head, Sector) as data-access > keys as well > > > > Could even be faster than the INTEGER PRIMARY KEY now used. > > > > What do you think? > We don't do April fools jokes. Nor do we find them amusing. > > > http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Datab > ase.aspx > > > > Regards, > > > > Noah > > > > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. > If you are not the addressee or authorized to receive this for the > addressee, you must not use, copy, disclose, or take any action > based on this message or any information herein. If you have > received this message in error, please advise the sender immediately > by reply e-mail and delete this message. Thank you for your > cooperation. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A proposed change to SQLITE_STMTSTATUS_FULLSCAN_STEP
The sqlite3_stmt_status() interface with the SQLITE_STMTSTATUS_FULLSCAN_STEP operator returns the number of full- table scan steps that occurred during the evaluation of a prepared statement. This information can be used by debugging logic within an application to help locate cases where new indices would improve performance. We are proposing to change the meaning of SQLITE_STMTSTATUS_FULLSCAN_STEP slightly so that it only counts full- table scan steps for tables in queries that do not include the NOT INDEXED clause. This would technically be an incompatible change. However, we are thinking that nobody ever uses the NOT INDEXED clause (it is an SQLite extension and is not standard SQL) nor do many people use SQLITE_STMTSTATUS_FULLSCAN_STEP. And hence, we are guessing that we can make this change without breaking any legacy code. If you think otherwise, please let me know. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On Mar 29, 2010, at 4:19 PM, Kevin M. wrote: > > However, this method breaks down if a store a 16-bit integer value > like "99" and SQLite stores it internally as an 8-bit value (to save > space) and subsequently retrieves it and gives me a value of 1 for > sqlite3_column_bytes(). I think you are misunderstanding what sqlite3_column_bytes() does The sqlite3_column_bytes() function converts the result (an integer in your case) into a string, then returns the number of bytes in that string, exclusive of the final nul terminator. SQLite does not provide a means to determine the number of bytes of underlying storage used for a value. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
On Mar 29, 2010, at 1:56 PM, Alexey Pechnikov wrote: > Hello! > > On Monday 29 March 2010 20:22:36 D. Richard Hipp wrote: >> SQLite version 1 used gdbm for storage. There were many problems >> with >> that approach: (1) gdbm is a hash-based system so it could not do >> range queries (2) gdbm is GPL, (3) Each table and index is in a >> separate file so your "database" was a directory full of files >> instead >> of a single file, (4) there is no support for transactions, (5) gdbm >> is highly vulnerable to corruption if a power loss occurs while it is >> being updated. > > May be the Tokyo Cabinet DBMS is more better? BerkeleyDB is required > administration and it's strange solution in many embedded devices and > for some desktops and servers too. (1) Tokyo cabinet did not exist in 2001 when I was looking for an alternative to gdbm. (2) When you configure Tokyo cabinet so that it is robust against power loss (so that it doesn't corrupt the database file during a power loss) it is about 10x slower than SQLite. (3) Tokyo cabinet has less concurrency than SQLite. (4) Tokyo cabinet is not able to put an entire SQL database into a single file. It would require a directory full of files, just as gdbm did. (5) Tokyo cabinet does not support transactions across files, so it cannot be used in an SQL engine that is transaction (since at the SQL level you must have transactions that span multiple tables.) I can come up with additional reasons why replacing the existing SQLite backend with TC is not a good idea, but perhaps the above will suffice. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
On Mar 29, 2010, at 12:03 PM, Jay A. Kreibich wrote: > On Mon, Mar 29, 2010 at 09:15:45AM +0530, Roger Binns scratched on > the wall: > >> I believe the btree/paging layer is replaced with BDB. > > Didn't SQLite "1" use a dbm library for the storage layer? > > The more things change > SQLite version 1 used gdbm for storage. There were many problems with that approach: (1) gdbm is a hash-based system so it could not do range queries (2) gdbm is GPL, (3) Each table and index is in a separate file so your "database" was a directory full of files instead of a single file, (4) there is no support for transactions, (5) gdbm is highly vulnerable to corruption if a power loss occurs while it is being updated. So after less than a year of SQLite 1, I wrote my own b-tree library and hooked it up to SQLite, changed the license from GPL to Public Domain, and called the result "SQLite version 2". That was in 2001, starting on http://www.sqlite.org/src/timeline? c=2001-04-17+20%3a09%3a11 and continuing through http://www.sqlite.org/src/timeline?c=2001-09-13+13%3A46%3A56 and beyond. >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite over NFS
On Mar 29, 2010, at 10:50 AM, Kumar, Abhinav wrote: > Hi, > > I am using SQLite version 3.5.9. My db sizes are 50-100 Gb. My DB is > a typical star schema. I am seeing an order of magnitude more time > to do a simple select query when doing over NFS (30-60 seconds) as > compared to local disk (2 seconds). Is there any way to optimize > this ? No. In any SQL database implementation (any non-SQL database too) the database engine itself normally does a lot of filtering of content before handing the results over to the application. So if you will imagine three boxes -- the disk drive, the database engine, and the application -- the traffic between the database engine and the disk drive is normally much larger than the traffic between the database engine and the application. Now, if the disk drive is separated from the application by a network, at some point you are going to need to move content across the network. Because the network is normally the slowest piece of hardware, you normally want to minimize the amount of traffic you move over the network. For that reason, you want the database engine and the disk drive to be on the same side of the network, not on opposite sides. The link between the database engine and the application should be the one that spans the network, since that is your lowest bandwidth link. Hence, if you have an NFS disk, you really ought to be using a client/ server database engine which will allow you to position the database engine on the server so that it can talk to the disk directly and without a network hop. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bug with collating sequences?
On Mar 29, 2010, at 10:40 AM, Jay A. Kreibich wrote: > On Mon, Mar 29, 2010 at 12:16:57PM +0200, Rickard Utgren scratched > on the wall: >> On Mon, Mar 29, 2010 at 12:07, Dan Kennedy <danielk1...@gmail.com> >> wrote: >> >>> This database was created with a different version of the collation >>> sequence than found in the script. I think the -nocase switch was >>> probably passed to [string compare]. > >> I didn't realize this would matter, but at least now I don't have to >> worry about my code breaking it again, thanks. > > If you change a collation that is used in an index, you must rebuild > the index. That's what the VACUUM command is doing, and why it is > "fixing" the problem. You can do this more quickly with the REINDEX > command. Yes. In fact, the REINDEX command was originally created for the specific purpose of rebuilding indices when the definition of a collating function changed. That's why REINDEX will accept the name of a collating function as its argument and only rebuild those indices that use the named collating function. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will CEROD work with the new BerkleyDB implementation?
On Mar 29, 2010, at 9:08 AM, Garry Watkins wrote: > Will CEROD work with the new BerkleyDB implementation? Please understand that the new SQL parser on BDB is entirely the work of Oracle and is independent of SQLite. We here at SQLite.org have not participated in that effort. We don't have any control over it, no rights to it, and no special knowledge of what Oracle has done, other than what we can see for ourselves by downloading and looking at the Oracle code. We didn't even know it was taking place until it was largely complete. So questions about what works or doesn't work with BDB should probably be directed to Oracle, not here. That said, it seems to me that the Oracle effort has replaced the Btree and Pager layers of SQLite with their own BDB implementation. But CEROD works on the pager layer. So CEROD is not likely to be compatible with BDB, even if you are using the SQLite front end on BDB. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Android database corruption
On Mar 26, 2010, at 12:10 PM, Shane Harrelson wrote: > On Fri, Mar 26, 2010 at 11:38 AM, cliff 2 > <bailey.cliff...@gmail.com> wrote: > >> >> Hi, >> >> Hopefully someone here can help where the android guys haven't been >> able >> to. >> We are having an issue with our sqlite database running on the >> android >> platform. We are accessing the db from the sdcard on an android phone >> running android 2.1. >> >> As all we are doing is reading the database, I really can't see how >> we can >> be corrupting it. > You're probably hitting a cache corruption issue caused by a mutex > issue > previously fixed. More information here: > > http://www.sqlite.org/src/timeline?n=8=2010-01-31 > > Not sure at this point what kind of work-arounds are available to you. To amplify what Shane said, the corruption is probably just in the page cache, not in the database file itself. So if you are able to close and reopen the database connection when you run into problems, that might provide a work-around (assuming we are guessing correctly at the root cause of your problem.) D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open16_v2
On Mar 26, 2010, at 8:28 AM, NSRT Mail account. wrote: > Looking at, http://www.sqlite.org/c3ref/open.html it seems that a > function sqlite3_open16_v2() is inexplicably missing. Is there a > reason for this? > An sqlite3_open16_v2() would add no new capabilities. The sqlite3_open16() interface is considered a mistake. It is maintained for historical compatibility. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running UPDATE on a large table uses too much RAM
On Mar 26, 2010, at 8:11 AM, NSRT Mail account. wrote: > I have a database with a single table. The database is 23922826240 > bytes. The table has 2147483685 rows in it. > > Running: UPDATE t SET v=1; makes my application start to use all > available RAM, then swap, and is finally killed by the OS (I do not > have 23GB of free RAM+swap). As part of UPDATE processing, the rowid of every row to be updated is stored in RAM. Normally this is not a problem, since it is unusual to want to update 2147483685 rows in one go. Try running your updates in batches of a million or so. > > Should running an UPDATE cause the table to first be copied to RAM > and modified there? Is there any way to get this UPDATE to work with > meager resources (given at least 50GB free on the partition the > database is stored on)? > > Thanks. > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue "sqlite database locked"
On Mar 26, 2010, at 12:40 AM, <sudha.bheema...@nokia.com>
<sudha.bheema...@nokia.com
> wrote:
> Hi,
>
>
> I have downloaded the sqlite from the link
> http://www.sqlite.org/download.html
> version sqlite-3.6.23.so.gz<http://www.sqlite.org/
> sqlite-3.6.23.so.gz> (220.62 KiB). I am using fedora -9-i386. Have
> mounted a
> Shared drive on my pc to the fedora image.I copied the extracted
> sqlite db and saved it on the fedora usr/lib directory from where
> the code loads the db.
Please see the second paragraph of http://www.sqlite.org/faq.html#q5
for a likely explanation of what is going wrong.
>
> This is the piece of code that I am using to do the operations on
> sqlite db.
>
> #include
> #include "sqlite3.h"
> #include
>
> using namespace std;
>
> typedef void* HINSTANCE;
>
> typedef int (*FnPtr_sqlite3_open)(const char *filename, sqlite3
> **ppDb );
> typedef int (*FnPtr_sqlite3_extended_result_codes) (sqlite3*, int
> onoff);
> typedef int (*FnPtr_sqlite3_prepare_v2)(sqlite3 *db, const char
> *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail );
> typedef int (*FnPtr_sqlite3_step)(sqlite3_stmt*);
> typedef int (*FnPtr_sqlite3_finalize)(sqlite3_stmt *pStmt);
>
> class TDBClass
> {
> public:
>HINSTANCE LoadLibrary();
>void LoadFunctions();
>void VerifyLoadedFunction(void* aFnPtr);
> public:
>FnPtr_sqlite3_open sqlite3_open;
>FnPtr_sqlite3_extended_result_codes
> sqlite3_extended_result_codes;
>FnPtr_sqlite3_prepare_v2sqlite3_prepare_v2;
>FnPtr_sqlite3_step sqlite3_step;
>FnPtr_sqlite3_finalize sqlite3_finalize;
>
>HINSTANCE sqLiteHndl;
> };
>
>
> HINSTANCE TDBClass::LoadLibrary()
>{
>sqLiteHndl = dlopen("/usr/lib/sqlite-3.6.23.so", RTLD_LAZY|
> RTLD_GLOBAL);
>return sqLiteHndl;
>}
>
> void* GetProcAddress(HINSTANCE aHandle, const char* aSymbol)
>{
>return dlsym(aHandle, aSymbol);
>}
>
> void TDBClass::LoadFunctions()
>{
>sqlite3_open =
> (FnPtr_sqlite3_open)GetProcAddress(sqLiteHndl,"sqlite3_open");
>if(sqlite3_open== NULL)
>{
>return;
>}
>sqlite3_prepare_v2 = (FnPtr_sqlite3_prepare_v2)
> GetProcAddress(sqLiteHndl,"sqlite3_prepare_v2");
>sqlite3_step= (FnPtr_sqlite3_step)
> GetProcAddress(sqLiteHndl,"sqlite3_step");
>sqlite3_extended_result_codes =
> (FnPtr_sqlite3_extended_result_codes) GetProcAddress(sqLiteHndl,
> "sqlite3_extended_result_codes" );
>}
>
>
>
> int main()
>{
>cout << "!!!Hello World!!!" << endl; // prints !!!Hello
> World!!!
>
>sqlite3* sqlhandle;
>TDBClass tdbhandle;
>HINSTANCE sqlitehandle = tdbhandle.LoadLibrary();
>tdbhandle.LoadFunctions();
>int err = tdbhandle.sqlite3_open("/root/Sudha/epoc32/winscw/c/
> tswi/tscrtool/scr.db",);
>err = tdbhandle.sqlite3_extended_result_codes(sqlhandle, 0);
>sqlite3_stmt* stmtHandle = NULL;
>const char* stmtTail = NULL;
>
>const char* statement = "CREATE TABLE
> SoftwareTypeNames(NameId INTEGER PRIMARY KEY NOT NULL,SoftwareTypeId
> INTEGER NOT NULL,Locale INTEGER DEFAULT 0,Name TEXT NOT NULL);";
>std::string stmt(statement);
>tdbhandle.sqlite3_prepare_v2(sqlhandle, stmt.c_str(),
> stmt.size(), , );
>int err1 = tdbhandle.sqlite3_step(stmtHandle);
>
>return 0;
> }
>
>
> This step int err1 = tdbhandle.sqlite3_step(stmtHandle);
> in the code returns 5 (sqlite_busy) every time I run the program.
> (I don't have any multi threads in my program which can lock the db. )
>
> Any help on this is appreciated.
>
>
> Regards
> sudha
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Mar 25, 2010, at 7:38 PM, P Kishor wrote: > > If you see the docs at Pg or MySQL, they are in BNF format (I believe > that is what it is called). It is plain text, so it can be parsed, > seen, read aloud, and viewed via lynx (for those who care). I am very sorry that you find the syntax diagrams of SQLite difficult to read. That is not the experience of most other users, however, who report finding the syntax diagrams easier to comprehend at a quick glance. We do not have the resources to maintain two parallel sets of syntax documentation with different representations to suite the preferences of individual readers.. And so the syntax diagrams will be all that is available for the foreseeable future. I apologize for whatever inconvenience this may cause you. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hello.. I have some question for sqlite3..
On Mar 24, 2010, at 7:44 PM, 김민수 wrote: > hello.. > I'm a student in Korea who study Database systems. > I have some question for you. > It is important for me so eventhen you are busy plz teach me about > some question.. > > I would like to use sqlite3 with eclipse and mingw. > Actually I have to use rtree moudle but i don't know how to complie.. > I already did some steps. > In linux, I made source file after executing : ../configure -> make > Sqlite3 works well with importing and compling in windows eclipse > environment. > After then , i execute " -DSQLITE_ENABLE_RTREE=1 " in linux's > makefile as option. > but ater using rtree I can see these message.. : no such rtree > module.. > > -> create virtual table test using rtree (a,b,c,d,e); >: no such module : rtree > > The reason why I bring the file in linux is I already know Parser > doesn't be made in windows environment.. I suggest you use the SQLite amalgamation. http://www.sqlite.org/amalgamation.html Either download a prebuilt amalgamation source file from the website, or else type make sqlite3.c on Linux. Then move the files sqlite3.c and sqlite3.h over to windows and compile them there together with -DSQLITE_ENABLE_RTREE. > > How can I use sqlite3 in windows eclipse environment..? > I have to use eclipse in windows. > I really thank for you it you teach me How to use sqlite's rtree in > detail.. > > thank you.. > I look forward to your enswer.. > > > > > > > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.6.23 may cause memory overrun problem
On Mar 24, 2010, at 7:11 AM, Daniel Lin wrote:
> About this bug, I found change the code to following will let it
> workable.
> But, I require your confirm.
>
> memcpy(pKeyInfo, zP4, sizeof(*pKeyInfo));
The SQLite code is correct as written. Your change will cause SQLite
to malfunction.
Check-in http://www.sqlite.org/src/ci/be27897991 is an attempt to
suppress the warning from code guard. But as I have no way of testing
to see if the change obtained that goal.
>
> On Wed, Mar 24, 2010 at 19:04, Daniel Lin <dlin...@gmail.com> wrote:
> Dear Dr.,
>
> I use Borland C++ Builder with code guard checking function enabled
> mode . (on 32 bits Windows XP)
>
> I found in an amalgamation sqlite.c may cause memory overrun.
>
> SQLITE_PRIVATE void sqlite3VdbeChangeP4(Vdbe *p, int addr, const
> char *zP4, int n){
> ...
> pOp->p4.pKeyInfo = pKeyInfo;
> if (pKeyInfo ){
> u8 *aSortOrder;
> memcpy(pKeyInfo, zP4, nByte); /* this line force copy 17
> bytes from 16 bytes structure */
> aSortOrder = pKeyInfo->aSortOrder;
> if( aSortOrder ){
> pKeyInfo->aSortOrder = (unsigned char*)
> >aColl[nField];
> memcpy(pKeyInfo->aSortOrder, aSortOrder, nField);
> }
> pOp->p4type = P4_KEYINFO;
>
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Sqlite User
Begin forwarded message: > From: Alexis Colon <turbines...@gmail.com> > Date: March 21, 2010 1:00:18 AM EDT > To: d...@hwaci.com > Subject: Sqlite User > > Hello. > > please help > how many user at the same time ken use sqlite > > thanks the any help > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about binding
On Mar 19, 2010, at 3:29 PM, David Bicking wrote:
>
>
> --- On Fri, 3/19/10, Vance E. Neff <ven...@intouchmi.com> wrote:
>
>
>> UPDATE table1 set (?, ?, ?) WHERE col1=? and col2=?;
>>
>> I've never used binding before but have known it is a good
>> idea in order
>> to avoid injection of bad stuff.
>>
>> Vance
>>
>
> You count the question marks from left to right.
>
>> UPDATE table1 set (<1>, <2>, <3>) WHERE col1=<4> and col2=<5>;
>
> You can also put the index number you want to use after the ? so
> they can be in any order you want.
Better still is to use a symbolic name for the parameters. The
symbolic names can be any identifier that begins with $, :, or @.
Examples:
UPDATE table1 SET col1=$c1val, co...@c2val, col3=:c3val
WHERE co...@c2val AND col3=:c3val;
You still have to translate the symbolic name into a "parameter index"
before you bind it. The sqlite3_bind_parameter_index() routine will
do that for you.
In the programs I write, I always try to use symbolic names for
parameters and I rig the infrastructure to handle the mapping from
symbolic name to parameter index. For example, if you are using the
TCL interface to SQLite, you just specify TCL variables embedded in
the SQL:
db eval {UPDATE table1 SET col1=$c1val WHERE col2=$c2val}
In the statement above, the TCL interface automatically looks up the
values of TCL variables $c1val and $c2val and binds them appropriately
before running the statement. It doesn't get any cleaner than this.
Unfortunately, other programming languages require more complex
syntax. In the implementation of "Fossil" I do this:
db_prepare(, "UPDATE table1 SET col1=$c1val WHERE col2=
$c2val");
db_bind_int(, "$c1val", 123);
db_bind_double(, "$c2val, 456.78);
db_step();
db_finalize();
The db_bind_int() and db_bind_double() and similar routines wrap the
sqlite3_bind_x() and sqlite3_bind_parameter_index() calls.
If we've learned one thing over the history of computing it is that
programmers are notoriously bad at counting parameters and that
symbolic names tend to be much better at avoiding bugs.
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM & journal size
On Mar 14, 2010, at 7:19 PM, Matthew L. Creech wrote: > Hi, > > I have a SQLite database with one large table, and I'd like to shrink > the size of that table to free up space in the filesystem. > > I'm finding that it needs a full 100 MB for the journal, even > though once the VACUUM succeeds the resulting DB is only 50 MB. > > Any tips are appreciated. Thanks! PRAGMA journal_mode=OFF; Be warned, though, that if you session crashes or you lose power, your database will go corrupt if there is no journal. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 offsets() function, sorted list?
On Mar 15, 2010, at 1:42 PM, Nasron Cheong wrote: > Are the results of the offsets() function used in the FTS3 module > sorted in > any form? Can I rely on this always being the case? Do not rely on the order of offsets returned by the offsets() function of FTS3. I think they are currently sorted, but that might change in a subsequent release. > > Thanks. > > - Nasron > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On Mar 11, 2010, at 12:18 PM, Skand wrote: >> >> Run REINDEX on your database. >> >> Get SQLite 3.6.23 and use that in place of 3.3.6 moving forward. >> > > REINDEX throws following error: > > SQL error: indexed columns are not unique > > Is there a graceful way to fix this? Run sqlite3 olddatabase >file.txt Then edit file.txt to remove the duplicate entries. Then: sqlite3 newdatabase Can you hypothesize the cause for this > error given that the schema specifies that the primary key consists > of all 3 > columns in the database? http://www.sqlite.org/lockingv3.html#how_to_corrupt D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On Mar 11, 2010, at 11:42 AM, Skand wrote: > > > D. Richard Hipp wrote: >> >> >> On Mar 11, 2010, at 11:13 AM, Skand wrote: >> >> >>> >>> sqlite> select count(*) from ip_domain_table where ttl < >>> 99 ; >>> >>> 1605343 >> >> What does "PRAGMA integrity_check" show you at this point? >> >> > > The integrity check shows 395 lines similar to: > "rowid 16422938 missing from index sqlite_autoindex_ip_domain_table_1" > > What does it mean? How can I fix this? I am running sqlite3 version > 3.3.6. Run REINDEX on your database. Get SQLite 3.6.23 and use that in place of 3.3.6 moving forward. > > Another followup question: Subsequent deletes after the first one, > keeps > removing partial entries with every run. > > sqlite> pragma cache_size=10; delete from ip_domain_table where > ttl < > 99; > sqlite> select count(*) from ip_domain_table where ttl < 99; > 258 > sqlite> pragma cache_size=10; delete from ip_domain_table where > ttl < > 99; > sqlite> select count(*) from ip_domain_table where ttl < 99; > 142 > > -- > View this message in context: > http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27866031.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On Mar 11, 2010, at 11:13 AM, Skand wrote: > > Hi Folks, > > Whats going on here? I would expect the following delete to delete > everything under 99. > > sqlite> .schema > > CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl > INTEGER, > PRIMARY KEY(ip_domain, answer, ttl)); > > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 1605343 What does "PRAGMA integrity_check" show you at this point? > > sqlite> pragma cache_size=10; delete from ip_domain_table where > ttl < > 99; > > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 258 > > sqlite> select * from ip_domain_table where ttl < 99 limit 1; > 107.35.138.41|127.2.0.2|1266895619 > > The first "select" shows that there are 1605343 entries which have > ttl below > 99. So after the following delete, shouldn't the number of > entries > go down to 0? If the TTL corresponding to these entries were > something else, > why should they be counted for in the select in the first place? The > delta > of entries between the two selects should be 0. > > Do I have some fundamental misunderstanding about how sqlite stores > values > in database? > > > > -- > View this message in context: > http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27865654.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE
On Mar 11, 2010, at 8:46 AM, mona.alsha...@gmail.com wrote: > Hello, > Thanks for your fast reply. > > I just did uninstalled itunes, restarted my pc then downloaded it > again facing same problem. > > When I click Microsoft online solution they direct me to SQlite but > I don't know which file to download SQLite is not a program. It is not a consumer application. SQLite is a component library used by programmers to build applications like iTunes, Firefox, Chrome, Photoshop, Skype and many others. There is nothing on the SQLite website that you can download to fix this problem. Some other application has overwritten a DLL which is causing your problem. I'm sorry but I have no way of telling what that application might be. Perhaps a windows expert can offer better advice, and for that reason I have forwarded your request to the SQLite mailing list. My suggested solution is simple: Get a Mac. > > Regards > Mona > ------Original Message-- > From: D. Richard Hipp > To: Mona Alshaikh > Cc: General Discussion of SQLite Database > Subject: Re: SQLITE > Sent: Mar 11, 2010 4:39 PM > > > On Mar 11, 2010, at 8:20 AM, Muna Al Shaikh wrote: > >> >> Hello, >> >> I need help, every time i open my iTunes and go to iTune store i got >> an error . and when i search for a solution i get directed to SQlite >> website.. but there are many files and i dont really know which one >> to choose and how to install it. >> >> can you please guide me on which file to download to solve me itunes >> error and how to install it. > > Probably some other application has overwritten the SQLite.dll file > that iTunes requires. Try reinstalling iTunes. That should clear th > e problem. > >> >> regards >> >> -- >> Mona Ahmed Al Shaikh, MBA >> >> Assistant Vice President - Asset Management >> > > D. Richard Hipp > d...@hwaci.com > > > > > > Sent from my BlackBerry® smartphone from Zain Kuwait D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE
On Mar 11, 2010, at 8:20 AM, Muna Al Shaikh wrote: > > Hello, > > I need help, every time i open my iTunes and go to iTune store i got > an error . and when i search for a solution i get directed to SQlite > website.. but there are many files and i dont really know which one > to choose and how to install it. > > can you please guide me on which file to download to solve me itunes > error and how to install it. Probably some other application has overwritten the SQLite.dll file that iTunes requires. Try reinstalling iTunes. That should clear th e problem. > > regards > > -- > Mona Ahmed Al Shaikh, MBA > > Assistant Vice President - Asset Management > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.23
On Mar 10, 2010, at 12:54 AM, Melton Low wrote: > I just tried to build 3.6.23 on a PPC Mac running OS X 10.4. Please try adding SQLITE_ENABLE_LOCKING_STYLE=0 to the compiler options. > >> >> I got an undefined symbol error and the make aborted. > > /bin/sh ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1 > -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2 -o libsqlite3.la > -rpath > /usr/local/lib -no-undefined -version-info 8:6:8 sqlite3.lo > gcc -dynamiclib -o .libs/libsqlite3.0.8.6.dylib .libs/sqlite3.o > -install_name /usr/local/lib/libsqlite3.0.dylib - > compatibility_version 9 > -current_version 9.6 > ld: Undefined symbols: > _gethostuuid > /usr/libexec/gcc/powerpc-apple-darwin8/4.0.1/libtool: internal link > edit > command failed > make: *** [libsqlite3.la] Error 1 > > I have attached the full configure and make output as an attachment. > > Your help would be appreciated. > > Mel > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.23 - date.c
On Mar 10, 2010, at 7:36 AM, Israel Lins Albuquerque wrote: > In this new version: > > > at file date.c line 1095 are changed? Why? > > previous version > STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), > STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, > currentTimeFunc), > > new version > STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d", 0, currentTimeFunc), > STR_FUNCTION(current_date, 0, "%Y-%m-%d %H:%M:%S", 0, > currentTimeFunc), > > I fink this is a problem because: > SELECT CURRENT_DATE will return 2010-03-10 09:34:55 > SELECT CURRENT_TIMESTAMP will return 2010-03-10 > > Are this correct? I think you have it backwards. What you have labeled as "previous version" above is what the current code looks like and your "current version" is what it used to look like. See http://www.sqlite.org/src/ci/eb98265b59 for a diff of the change. This was a bug fix. The code in question only comes into play when you compile with SQLITE_OMIT_DATETIME_FUNCS and so it doesn't matter for most people. We didn't discover the problem until recently when we were adding some tests that make use of SQLITE_OMIT_DATETIME_FUNCS. > > > Regards , > > Israel Lins Albuquerque > Developer > Polibrás Brasil Software Ltda. > > > _______ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite
On Mar 10, 2010, at 1:44 AM, DbSchema Support wrote: > Hello, > > Here is Roberto from DbSchema. > Is it possible with Sqlite to add constraints using ALTER TABLE ... ? > This would be required since not allways the Foreign keys are > specified when creating the table. > I'm looking forward for an reply, > No. The ALTER TABLE command in SQLite only supports ALTER TABLE ... ADD COLUMN ... and ALTER TABLE ... RENAME For any other changes, you'll need to (1) rename the original table, (2) create a new table in the desired form, (3) copy the content of the original table into the new table, and (4) drop the original table. > Best regards, > Roberto Luca > -- http://www.dbschema.com D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JAVA and sqllite.
On Mar 10, 2010, at 4:04 AM, Fredrik Svensson wrote: > Hi! > > I would like to use sqllite in my java project but to my surprise i > see no java support? > > Is there a away to use sqllite in java? There are about a dozen different solutions. Please do a web search for "java sqlite" or "jdbc sqlite" to get a list. > > Many thanks. > > //F. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About Sqlite Virtual Table
Your question has been forwarded to the sqlite-users@sqlite.org mailing list. On Mar 10, 2010, at 4:29 AM, bibo lv wrote: > Hi： > I use sqlite as an open source in my software。When virtual > table is used, > it is not the same as 'real table'. For example the sql : > "select * from (select * from table1 where obj = 'a'), (select * > from table2 where obj = 'b')" > when table1 and table2 are real tables, the result shows as we > expect, unfortunately when > they are virtual tables using fts3 to create, the result is not > right. >Further more, when i debug fts3 with sql -- > 1."select * from virtual_table where a = ''1' and b ='2'" > 2."select * from virtual_table where a = ''1' or b ='2'" > i can not fetch the values of a and b using 1 but fail using 2 in > xFilter. > Can you tell me how can i deal with the problems. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite files on your computer
On Mar 9, 2010, at 8:35 PM, C G wrote: > Yes I am sending this in regards to sqlite files I have found on my > computer. What are they ? Why are they on my computer ? Why even > after I re install windows , is it STILL logging my key strokes ? > Is this a key logger ? How do I remove it from my computer ? > Please get back to me ASAP. As I feel my privacy has been violated!! > > *** Cyndi *** > > > SQLite is a database engine used by many different programs to store data. Programs that use SQLite include iTunes, Adobe Acrobat Reader, Skype, Firefox, Chrome, and Mcafee Antivirus, as well as countless others. One or more of these other programs is leaving the SQLite files on your computer. SQLite is not a program or application. It is not a virus. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.23
On Mar 9, 2010, at 5:07 PM, Roger Binns wrote: > D. Richard Hipp wrote: >> SQLite version 3.6.23 is now available from the SQLite website: >> http://www.sqlite.org/ > > The download page no longer mentions any version of SQLite: > > http://www.sqlite.org/download.html Oops. Does now. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.23
On Mar 9, 2010, at 4:40 PM, Terence Martin wrote: > On Tue, 9 Mar 2010 16:00:27 -0500 > "D. Richard Hipp" <d...@hwaci.com> wrote: > >> SQLite version 3.6.23 is now available from the SQLite website: >> http://www.sqlite.org/ >> >> >> As always, please let me know if you find any problems in the new >> release. >> >> D. Richard Hipp >> d...@hwaci.com > > I think there may be a problem with the currentl 3.6.23 amalgamation > download. First I noticed that it compiled the library fine but blew > up > in shell.c (2418) with an error regarding SQLITE_CONFIG_LOG not being > defined. > > On closer examination it appears that the sqlite3.[ch] files are from > 3.6.22 (they diff out to be identical). > > I double checked that the file I downloaded says it's 3.6.23. Yes - there was a boo boo in the build. Please download a fresh copy and try again. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.6.23
SQLite version 3.6.23 is now available from the SQLite website: http://www.sqlite.org/ Version 3.6.23 is a regularly scheduled bimonthly release of SQLite. Upgrading from version 3.6.22 is optional. For further information on the enhancements in version 3.6.23 visit: http://www.sqlite.org/news.html http://www.sqlite.org/releaselog/3_6_23.html For the past several years, we have been doing a new release of SQLite every month whether it needed one or not. Beginning this year, we are attempting to slow down the release cycle. We are currently aiming for a release every two months. We may move to less frequent releases depending on how the current schedule is received. Of course, if a serious bug is found, we will make unscheduled emergency releases as necessary. But in the absence of serious problems, we will be striving to slow down the release cycle of SQLite, in order to mitigate the perception of excessive "code churn". As always, please let me know if you find any problems in the new release. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Different temp_store_directory settings okay?
On Mar 4, 2010, at 4:30 PM, Brian Dantes wrote: > D. Richard Hipp wrote: > >> It is OK for different processes to use different >> temp_store_directory > >> settings. The temp_store_directory is only used for TEMP tables. It > >> does not play a roll in the persistent state of the database. > > I was worried because of this statement in the docs: > > "When the temp_store_directory setting is changed, all existing > temporary tables, indices, triggers, and viewers are immediately > deleted." > > If I have one application using the default temp_store_directory, say > /tmp, > and another that sets it explicitly to something else, does that > second > application blow away all the temporary data for the first application > sitting in /tmp? No. > >>> I am experiencing physical DB corruption and am searching >>> for possible explanations. >> Is the corruption repeatable? What version of SQLite are you >> running? > > No, unfortunately. 3.6.14.2. > > -Brian Dantes > >> -Original Message- >> From: Brian Dantes >> Sent: Thursday, March 04, 2010 1:10 PM >> To: 'sqlite-users@sqlite.org' >> Subject: Different temp_store_directory settings okay? >> >> Is it okay for two different *processes* using >> independent connections to the same database two >> have different values for the temp_store_directory >> pragma? >> >> The docs make it clear this is not okay for two >> *threads* in the same process -- but for two >> processes is not so clear. >> >> I am experiencing physical DB corruption and am searching >> for possible explanations. >> >> Brian Dantes > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Different temp_store_directory settings okay?
On Mar 4, 2010, at 4:10 PM, Brian Dantes wrote: > Is it okay for two different *processes* using > independent connections to the same database two > have different values for the temp_store_directory > pragma? > > The docs make it clear this is not okay for two > *threads* in the same process -- but for two > processes is not so clear. It is OK for different processes to use different temp_store_directory settings. The temp_store_directory is only used for TEMP tables. It does not play a roll in the persistent state of the database. > > I am experiencing physical DB corruption and am searching > for possible explanations. Is the corruption repeatable? What version of SQLite are you running? > > Brian Dantes > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WIKI login
On Feb 25, 2010, at 4:08 PM, Steve Topov wrote: > I wanted to update information about our products SQLPro - > http://www.vive.net/products/sqlpro.htm and DataPro - > http://www.vive.net/products/datapro.htm in ManagementTools and > ConverterTools. Fair enough. I've reenable access to the CVSTrac wiki at http://www.sqlite.org/cvstrac/wiki D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WIKI login
On Feb 25, 2010, at 3:29 PM, Steve Topov wrote: > How can I get User name and password to login to Wiki? We don't have a open wiki right now. The old CVSTrac wiki at http://www.sqlite.org/cvstrac/wiki is no longer monitored and was starting to pick up spam, so I turned off write permission on it for everyone. We have never opened up the Fossil wiki at http://www.sqlite.org/src/wiki for anonymous contribution, preferring instead to do all our own documentation. What did you want to write about? > > Steve Topov > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite Step Strange Results
On Feb 25, 2010, at 10:19 AM, Rob Read wrote: > We are using sqlite in a low memory environment and as such have > been using > the MEMSYS5 memory allocator. This has been working great to confine > our > memory usage to the buffer that gets passed during start time. I have > recently run into some strange results while doing a step to insert > a blob > into the database. If I set my cache size, using the pragma, to > 1500 then > the step fails with SQLITE_NOMEM, but if I do this same process and > decrease > the cache size to 1000 then the step succeeds. This might sound like > a easy > problem to fix in that my page cache is taking up the memory that > the step > needs to complete the insert, but if I check the memory usage by > calling > sqlite3_memory_used(), in both situations I have plenty of memory > available > to complete the transfer. If anyone could shed some light on this > issue I > would appreciate it. > Probably this is due to memory fragmentation. In a low-memory environment, you should use sqlite3_config(SQLITE_CONFIG_PAGECACHE,...) to set up a separate buffer for use by the page cache. The page cache is the primary memory user, so allocate most of your memory budget to it. And since all page cache allocations are the same size, you won't get fragmentation there. You can normally get by with much less memory for the general-purpose heap, depending on how many prepared statements you are holding at once and how complex your schema is. > > > Robert Read > > Lead Software Engineer > > Saltillo Corporation > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
On Feb 24, 2010, at 5:45 PM, Igor Tandetnik wrote: > D. Richard Hipp <d...@hwaci.com> wrote: >> This problem has existed in SQLite forever and has never been seen >> before. So it is obscure. The simple fix is to put the ON clause on >> the CROSS JOIN where it belongs. > > Does CROSS JOIN allow an ON clause? That doesn't make much sense. I > guess I'm missing something obvious. It does in SQLite. Maybe that isn't really sensible, but SQLite does it anyway. If it concerns you, simply rewrite as an INNER JOIN. > > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
On Feb 24, 2010, at 5:20 PM, Pavel Ivanov wrote: >> Does INDEX sqlite_autoindex_currency_1 contain only pointers to the >> identical currencies? > > First of all AFAIK 'PRIMARY KEY' implies uniqueness of the column. And > second: I bet if you execute 'PRAGMA case_sensitive_like = true' then > plans will be the same. > See http://www.sqlite.org/optoverview.html#like_opt for details. > Guys: Thanks for all the discussion. But I know what is causing the problem. I would have already posted the ticket, but I'm having a little problem with Fossil right this minute and I need to debug that first... The problem is that the query optimizer is using the c1.cur=c2.cur term together with indices to limit the search to only those rows that satisfy the condition. That's the right thing to do in most cases, but not when the expression is on a LEFT JOIN but refers only to tables to the right of the LEFT JOIN. This problem has existed in SQLite forever and has never been seen before. So it is obscure. The simple fix is to put the ON clause on the CROSS JOIN where it belongs. Yes, SQLite should still do the right thing even if the ON is in the wrong place, and I'll fix that directly. Let me get Fossil running again first, though, please. Thanks for reporting the problem and for the analysis. > > Pavel > > On Wed, Feb 24, 2010 at 5:15 PM, ve3meo <holden_fam...@sympatico.ca> > wrote: >> >> "ve3meo" <holden_fam...@sympatico.ca> wrote in >> message news:hm47t5$5l...@dough.gmane.org... >>> >>> "Igor Tandetnik" <itandet...@mvps.org> wrote in >>> message news:hm45gu$s5...@dough.gmane.org... >>>> Mark Brand <mabr...@mabrand.nl> wrote: >>>>> --Gives unexpected results >>>>> SELECT c1.cur cur1, c2.cur cur2, COALESCE(self.rate, x.rate) rate >>>>> FROM currency c1 >>>>> CROSS JOIN currency c2 >>>>> LEFT JOIN exchange x >>>>>ON x.cur1=c1.cur >>>>>AND x.cur2=c2.cur >>>>> LEFT JOIN (SELECT 1 rate) self >>>>>ON c1.cur=c2.cur; >>>>> >>>>> /* results >>>>> >>>>> EUR|EUR|1 >>>>> GBP|GBP|1 >>>>> USD|USD|1 >>>>> >>>>> */ >>>> >>>> Yes, looks like a bug to me. I see no reason why it shouldn't >>>> work. LEFT >>>> JOIN should never produce fewer rows than what left-hand-side table >>>> contains. >>>> >>>> As a workaround, try this instead: >>>> >>>> SELECT c1.cur cur1, c2.cur cur2, >>>>(case when c1.cur=c2.cur then 1 else x.rate) rate >>>> FROM currency c1 >>>> CROSS JOIN currency c2 >>>> LEFT JOIN exchange x >>>>ON x.cur1=c1.cur >>>>AND x.cur2=c2.cur; >>>> >>>> Igor Tandetnik >>> >>> One little change makes it work: >>> >>> SELECT c1.cur cur1, c2.cur cur2, COALESCE(self.rate,x.rate) rate >>> FROM currency c1 >>> CROSS JOIN currency c2 >>> LEFT JOIN exchange x >>>ON x.cur1=c1.cur >>>AND x.cur2=c2.cur >>> LEFT JOIN (SELECT 1 rate) self >>>ON c1.cur LIKE c2.cur; >>> >>> Don't ask me why '=' and 'LIKE' should behave differently in this >>> example. >>> >>> Tom >> >> They do affect the EXPLAIN QUERY PLAN results: >> >> "=" >> order from detail >> 0 0 TABLE currency AS c1 >> 1 1 TABLE currency AS c2 WITH INDEX sqlite_autoindex_currency_1 >> 2 2 TABLE exchange AS x WITH INDEX sqlite_autoindex_exchange_1 >> 3 3 TABLE AS self >> >> "LIKE" >> order from detail >> 0 0 TABLE currency AS c1 >> 1 1 TABLE currency AS c2 >> 2 2 TABLE exchange AS x WITH INDEX sqlite_autoindex_exchange_1 >> 3 3 TABLE AS self >> >> Does INDEX sqlite_autoindex_currency_1 contain only pointers to the >> identical currencies? >> >> Tom >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close ==> " library routine called out of sequence"
On Feb 24, 2010, at 1:12 PM, Mike Shal wrote: >> >> This compiler is llvm. Check it out at http://llvm.org >> You can test for this mistake on the demo page at http://llvm.org/ >> demo >> FWIW, there are currently two places in SQLite where we have had to complicate the code in order to work around bugs in LLVM. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in porter stemmer
We got the Porter stemmer code directly from Martin Porter.
I'm sorry it does not work like you want it to. Unfortunately, we
cannot change it now without introducing a serious incompatibility
with the millions and millions of applications already in the field
that are using the existing implementation.
FTS3 has a pluggable stemmer module. You can write your own stemmer
that works "correctly" if you like, and link it in for use in your
applications. We will also investigate making your recommended
changes for FTS4. However, in order to maintain backwards
compatibility of FTS3, we cannot change the stemmer algorithm, even to
fix a "bug".
On Feb 24, 2010, at 9:59 AM, James Berry wrote:
> Can somebody please clarify the bug reporting process for sqlite? My
> understanding is that it's not possible to file bug reports
> directly, and that the advise is to write to the user list first.
> I've done that (below) but have no response so far and am concerned
> that this means the bug report will just be forgotten others, as
> well as by me.
>
> How does this bug move from a message on a list to a ticket (and
> ultimately a patch, we hope) in the system?
>
> James
>
> On Feb 22, 2010, at 2:51 PM, James Berry wrote:
>
>> I'm writing to report a bug in the porter-stemmer algorithm
>> supplied as part of the FTS3 implementation.
>>
>> The stemmer has an inverted logic error that prevents it from
>> properly stemming words of the following form:
>>
>> dry -> dri
>> cry -> cri
>>
>> This means, for instance, that the following words don't stem the
>> same:
>>
>> dried -> dri -doesn't match- dry
>> cried -> cry -doesn't match- cry
>>
>> The bug seems to have been introduced as a simple logic error by
>> whoever wrote the stemmer code. The original description of step 1c
>> is here: http://snowball.tartarus.org/algorithms/english/stemmer.html
>>
>> Step 1c:
>> replace suffix y or Y by i if preceded by a non-vowel which is
>> not the first letter of the word (so cry -> cri, by -> by, say ->
>> say)
>>
>> But the code in sqlite reads like this:
>>
>> /* Step 1c */
>> if( z[0]=='y' && hasVowel(z+1) ){
>> z[0] = 'i';
>> }
>>
>> In other words, sqlite turns the y into an i only if it is preceded
>> by a vowel (say -> sai), while the algorithm intends this to be
>> done if it is _not_ preceded by a vowel.
>>
>> But there are two other problems in that same line of code:
>>
>> (1) hasVowel checks whether a vowel exists anywhere in the string,
>> not just in the next character, which is incorrect, and goes
>> against the step 1c directions above. (amplify would not be
>> properly stemmed to amplifi, for instance)
>>
>> (2) The check for the first letter is not performed (for words
>> like "by", etc)
>>
>> I've fixed both of those errors in the patch below:
>>
>> /* Step 1c */
>> - if( z[0]=='y' && hasVowel(z+1) ){
>> + if( z[0]=='y' && isConsonant(z+1) && z[2] ){
>>z[0] = 'i';
>> }
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Sqlite files in temp folder
Begin forwarded message: > From: "Anita Asken" <markani...@yahoo.co.uk> > Date: February 24, 2010 6:04:35 AM EST > To: <d...@hwaci.com> > Subject: Sqlite files in temp folder > > Dear Sir, > I have been contacted by a friend who has the above > files appearing in her Temp folder on a daily basis. These files > are up to and including 40GB in size (when compressed) and are > filling the Hard drive at an alarming rate. Can you offer any > advice on how to stop the files being written, and remove the > programme(s) that are writing these files. > > Your help in this matter is very much appreciated. > > Kind regards, > > Mark Asken D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.22 ATTACH no longer works for files outside the current working directory
On Feb 17, 2010, at 6:52 AM, Hick Gunter wrote: > Ok I went back to square one and retrieved the amalgamation source > from the recommended distribution. It is still failing at the > "reduce" step before the "shift 627", which is where the > sqlite3Attach() function is called. > > > [sgi...@sgtxe1 ~]$ asql > SQLite version 3.6.22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> attach 'test.db' as test; > Error: unable to resolve operation > sqlite> > The error message text "unable to resolve operation" occurs nowhere in the SQLite source code. And nobody else is able to reproduce this problem. So it seems likely to us that you are using adulterate source code. Please try again with fresh, unaltered SQLite source files. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.22 ATTACH no longer works for files outside the current working directory
On Feb 15, 2010, at 11:36 AM, Hick Gunter wrote: > In SQLite 3.5.9 it used to be possible to write > > sqlite> ATTACH '/some/unix/file/name.db' AS mydb; > > > In SQLite 3.6.22 this results in the error message > > Error: unable to resolve operation Works fine when I try it. Are you using the sqlite3.exe from the SQLite website or another third-party shell? Might your third-party shell be playing games with the string somehow? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Downloading older version of sqlite
On Feb 8, 2010, at 5:10 PM, Fantoosh wrote: > Hi All, > > I need to download an older version of sqlite - 3.5.3. Any ideas on > how/where > i can get this version? I looked at the CVS branches/tags and I > couldn't find > one that said 3.5.3. http://www.sqlite.org/src/info/a39007d5b1 D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] negative numbers
On Jan 31, 2010, at 9:19 AM, james pruett wrote:
> I am writing an open source program.
>
> I am having trouble getting any results using this query. This
> returns none
>
> select * from signs where lon>-121 and lon<-119;
>
> onCreate(SQLiteDatabase db) {
> db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
>+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + TIME
>+ " VARCHAR(45), " + TAGG
>+ " VARCHAR(45), " + LAT
>+ " FLOAT , " + LON
>+ " VARCHAR(45), " + COG
>+ " VARCHAR(45), " + MPH
>+ " VARCHAR(45), " + KPH
>+ " TEXT NOT NULL);");
> }
>
> Here is the database using RazorSQL v504
> select * from signs;
> _id time tag lat loncog
> mph kph
> 11264890306692test20.0-120.00.045.00.0
> 21264890311484test20.0-120.00.045.00.0
> 31264890345263test20.0-120.00.045.00.0
> 41264890346700test20.0-120.00.045.00.0
>
> ---
> I have tried lon= varchar(45) and also as a FLOAT, and both don't seem
> to handle negative numbers correctly.
FLOAT should work. Are you sure you tried it correctly?
> Any help appreciated!
>
> Jim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite query performs 10 times slower than MS Access query
On Jan 7, 2010, at 8:18 AM, Tim Romano wrote: > I don't claim any SQLite expertise, but indexes on values like > True/False, Male/Female -- i.e. where there are only a couple or a few > different values possible in the column -- are normally to be > avoided > because low-cardinality indexes tend to be inefficient. What is the > advice of the SQLite gurus in this respect? This is good advice. If you run ANALYZE, SQLite will figure out that the index is mostly useless and will hardly ever use it. (There are some obscure queries for which such an index would be useful, but they are the exception rather than the rule.) On the other hand, SQLite still has to maintain the index on INSERT, UPDATE, and DELETE operations which will slow those operations down. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite query performs 10 times slower than MS Access query
On Jan 29, 2010, at 6:15 AM, Max Vlasov wrote: > To be sure that there's no > full or almost-full table scan I use VFS monitoring in such cases. The sqlite3_stmt_status() interface was designed for this purpose. http://www.sqlite.org/c3ref/stmt_status.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parsing create statements
On Jan 21, 2010, at 5:36 PM, BareFeet wrote: > > Vivien wrote: >> You can use SQLite's own SQL parser >> (http://www.hwaci.com/sw/lemon/lemon.html) with SQLite's own grammar >> (to ba found in SQLite's sources) and adapt it to you needs. > > That's the theory, but how can I do that easily? It seems that I > either have to reverse engineer the virtual machine code it produces > or else write my own parsers in Lemon. I would hope there's a way to > intercept the parsing built into SQLite to extract the parse tree, > but none of my attempts to investigate this have been fruitful. SQLite doesn't really do a parse tree so much. It does a little. Sometimes. But its style of operation is closer to "syntax directed translation", especially for the CREATE TABLE statement. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline
I am unable to reproduce this problem. Using the script below, with RMNOCASE changed to just NOCASE, everything works fine on the SQLite command-line shell on the website on Linux. I also tried various other versions of SQLite with the same result. On Jan 21, 2010, at 8:00 AM, Hub Dog wrote: > I hava a table. The table schema is > > CREATE TABLE AddressTable > ( > AddressID INTEGER PRIMARY KEY , > AddressType INTEGER , > Name TEXT COLLATE RMNOCASE , > Street1 TEXT , > Street2 TEXT , > City TEXT , > State TEXT , > Zip TEXT , > Country TEXT , > Phone1 TEXT , > Phone2 TEXT , > Fax TEXT , > Email TEXT , > URL TEXT , > Latitude INTEGER , > Longitude INTEGER , > Note BLOB > ) ;. > > if I execute following sql to query data , the sqlite 3.6.22 command > line > downloaded from www.sqlite.org will crash. > > SELECT > Adr.Name COLLATE NOCASE AS AddressName > FROM > AddressTable AS Adr > WHERE > Adr.Name LIKE '%_'. > > if I change the Adr.Name to AddressName , the sql execute result is > ok. > > SELECT > Adr.Name COLLATE NOCASE AS AddressName > FROM > AddressTable AS Adr > WHERE > AddressName LIKE '%_' ; > > it seems the crash was related with the collate RMNOCASE of > AddressTable > table's field Name. > in default sqlite command line, there is no rmnocase collation. so I > mapped > it to the default nocase collation. > ___________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile time warning
On Jan 21, 2010, at 7:54 AM, Daniel Carrera wrote: > Hello, > > I have a simple program that uses SQLite. When I compile it I see the > following warning: > > /home/daniel/.local/lib/libsqlite3.a(sqlite3.o): In function `memset': > /usr/include/bits/string3.h:82: warning: memset used with constant > zero > length parameter; this could be due to transposed parameters > > Does anyone know what this means and how I can remove it? This is caused by a bug in GCC. http://gcc.gnu.org/bugzilla/show_bug.cgi?id=42688 has additional information. But the bug appears to be harmless (correct code is generated in spite of the warning). So you should simply ignore it. > > Thanks, > Daniel. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fossil/SQLite server is down
On Jan 18, 2010, at 2:48 AM, David Koenig wrote: > The server hosting Fossil and SQLite is down. You or the admins (if > there are admins other than you) probably know by now, but this can't > hurt. > This would be a problem at our service provider, Linode. They have been alerted to the malfunction. In the meantime, you can access the first backup server at http://www2.sqlite.org/ http://www2.fossil-scm.org/ Unfortunately, not all services are available on the backup. Specifically, the mailing lists and the download page for Fossil precompiled executables are only available on the primary server. But hopefully Linode will have the problem resolved soon. A second backup server is provided by a different hosting service (Hurricane Electric) but hopefully that 2nd backup will not be necessary D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL syntax diagrams
On Jan 15, 2010, at 9:10 AM, Andy Gibbs wrote: > > Hi, > > I really like the way the SQL syntax diagrams are done (e.g. at > http://www.sqlite.org/syntaxdiagrams.html) > . What software did you use for it? > http://wiki.tcl.tk/21708 Also: http://www.sqlite.org/faq.html#q25 and http://www.sqlite.org/search?q=syntax+diagrams D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CANTOPEN Bohrbug
On Jan 14, 2010, at 12:06 AM, Jamie Hardt wrote: > > So, has anyone else run into SQLITE_CANTOPEN in a situation where it > wasn't a permissions issue? > My guess would be that the SQLite database is located in the same directory hierarchy that you are indexing and that somehow the system calls you are making to enumerate the paths are interfering with SQLite's ability to write to the database. The fact that it is a CANTOPEN error suggests that it was trying to open a new rollback journal. You might work around the problem by putting all of your inserts inside a single big transaction. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sort Alphanumeric, Numeric data in a VARCHAR
On Jan 13, 2010, at 8:11 PM, Gary Baranzini wrote: > Hi, > > I'm having trouble sorting the following data: > > point_number - VARCHAR(10) > > 11.01 > 11.23 > 77.01 > 55.09 > A06 > 1010.01 > 1010.09 > A03 > > How can I get the A03, A06 at the end of the list? SELECT * FROM mytable ORDER BY CASE WHEN point_number GLOB '*[^0-9.]*' THEN point_number ELSE cast(point_number AS real) END; D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reasons for SQLITE_CANTOPEN
On Jan 12, 2010, at 6:58 AM, Ian Jackson wrote:
> Nikolaus Rath writes ("Re: [sqlite] Reasons for SQLITE_CANTOPEN"):
>> Edzard Pasma <pasm...@concepts.nl> writes:
>>> Hope strace (see Roger Binns' post) will help you further. Also lsof
>>> may help.
>>
>> The problem was indeed that I reached the maximum number of fds.
>> Thanks
>> for the help!
>
> If SQLite had reported the errno value from the failed system call,
> the cause would have been obvious and this entire investigation would
> have been unnecessary.
sqlite3_file_control(db, "main", SQLITE_LAST_ERRNO, _value);
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed regression after 3.6.17
On Jan 11, 2010, at 9:28 PM, ve3meo wrote: > Oddly enough, by revising the query to explicitly > use an index, the two later releases are much faster... Did you run ANALYZE before you tried using explicit indices? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTERSECT optimization, is it possible?
On Jan 10, 2010, at 4:50 AM, Max Vlasov wrote: > Documentation says that INTERSECT implemented with temporary tables > either > in memory or on disk. Is it always the case? No. If there is an ORDER BY clause, SQLite may run each subquery as a separate co-routine and merge the results. If the ORDER BY on both subqueries can be computed using indices, then the INTERSECT will run in either linear or logarithmic time (depending on what indices are available) and in constant space. This is also true of UNION and EXCEPT. If there is an ORDER BY clause but there does not exist indices needed to implement the ORDER BY for one or both subqueries, then the subquery might get evaluated into a temporary table, and sorted there, prior to the merge step. I *think* it will always be the case that if an INTERSECT query has an ORDER BY clause and if sqlite3_stmt_status(db, SQLITE_STMTSTATUS_SORT, 1) returns 0, then the query does not use temporary tables and runs in constant space. But I might be wrong. And in any event, that rule is subject to change in a future release if we decide we can get better performance by doing things differently. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [bug+patch] Old lemon bug reintroduced
On Jan 6, 2010, at 7:50 PM, D. Richard Hipp wrote: > > On Jan 6, 2010, at 7:47 PM, Wilson, Ronald wrote: > >> Does check-in [077a6bee2d] resolve the issue below? >> >> http://www.sqlite.org/src/vinfo/077a6bee2d >> > > > Yes. I didn't realize the issue had a ticket. I'll close it. Silly me - I was right: there is no ticket on this issue. So there is nothing to close. But, yes, the issue with lemon is fixed by the change above. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
