[sqlite] Cache size tuning
Does SQLite provide any tools to help the developer tune the database cache size? Thanks, - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.10
D. Richard Hipp wrote: > All this is to say that we believe that SQLite version 3.6.10 is the > most stable, most thoroughly tested, and bug-free version of SQLite > that has ever existed. Please do not be freaked out by three releases > occurring in one week. > Not freaked out at all. Release every day if you have to. Thanks to you and the team for such a wonderful product. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does "PRAGMA integrity_check" actually do?
Roger Binns wrote: >> Writing to the beginning of the file (the first 40 bytes >> or so) corrupts the database so badly that SQLite can't >> even execute the pragma. > > The header contains a signature for the file and important meta > information which make the file useless if they are wrong. See > http://www.sqlite.org/fileformat.html#tocentry_71 > Great document! I wasn't aware that it existed. Thanks! > That said, if you are using a system that random corrupts files then > your problems are far larger than SQLite. How do you deal with other > data being corrupted, or even ensure that the programs you run aren't > messed with? > We're confident of our file system, because we've never had any problems with it. However, we have seen some nasty data corruption problems with our current database system, which we hope to eliminate by converting to SQLite. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] erratum
"does undetected" => "goes undetected" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What does "PRAGMA integrity_check" actually do?
I wrote a test program to deliberately trash a database file, and then see if "PRAGMA integrity_check" could detect the corruption. The only thing I found that works is writing beyond the end of the file. Writing to the beginning of the file (the first 40 bytes or so) corrupts the database so badly that SQLite can't even execute the pragma. Writing to the middle of the file -- even a large swath of garbage -- does undetected by the pragma. I was hoping that the integrity checking pragma would be more robust than this. Am I missing something? Thanks, - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction within script
D. Richard Hipp wrote: > ... nested transactions (in the form of SAVEPOINTs) will appear in > the next SQLite release, which we hope to get out by mid-January ... > Nice! I'm hoping you will also provide a SQLITE_OMIT_SAVEPOINT compilation option, for us embedded guys who are always worrying about memory? :-) Thanks! - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] vfs implementation question
D. Richard Hipp wrote: > On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote: > > >> Should the xLock member of sqlite3_io_methods object block until the >> lock can be acquired? > > xLock does not block on any of the built-in VFSes. But if you want to > make your own custom VFS that blocks on locks, I don't know of any > reason why that wouldn't work. I wrote a custom VFS that blocks on locks, and it works just fine. I also implemented a subset of the five locking levels of SQLite: just UNLOCKED and EXCLUSIVE. This means that as soon as a transaction asks for a SHARED lock, it actually gets an EXCLUSIVE lock, which locks out all other transactions until the first one commits. This works fine in an embedded application where there are only a few threads, whose transactions execute quickly. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Erratum
On the page describing the various sqlite3_bind_xxx_yyy() functions: http://www.sqlite.org/c3ref/bind_blob.html 3rd paragraph: "The index for named parameters can be looked up using the sqlite3_bind_parameter_name() API if desired." should be: "... sqlite3_bind_parameter_index() ..." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross compiling sqlite3.c, anamolies.
Stephen Oberholtzer wrote:
> On Tue, May 27, 2008 at 3:59 PM, Richard Klein <[EMAIL PROTECTED]>
> wrote:
>
>>> On May 26, 2008, at 3:24 PM, A. H. Ongun wrote:
>>>> Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I
>>>> get hundreds of error messages.
>>>>
>>>> I am puzzled to see why this is so.
>>> My guess would be because SQLite is written in C, not C++.
>>>
>>> D. Richard Hipp
>>> [EMAIL PROTECTED]
>> My company often needs to compile SQLite under C++, so we ran into the
>> same problem. It's easy to get rid of the error messages: Mostly it's
>> a matter of adding explicit typecasts, and of separating nested structs.
>
>
> An "extern C" wrapper doesn't work?
No, the syntax
extern "C" {
...
}
tells the C++ compiler to generate C-style linkage for all functions
declared within the curly braces, i.e. to allow the functions to be
callable from C programs. It doesn't mean "compile everything within
the braces as if it were C".
However, you may be able to use the appropriate command-line option
for your compiler. For example, the -xc option tells gcc to compile
the input file as C, regardless of the file's extension.
- Richard Klein
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross compiling sqlite3.c, anamolies.
> On May 26, 2008, at 3:24 PM, A. H. Ongun wrote: >> Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I >> get hundreds of error messages. >> >> I am puzzled to see why this is so. > > My guess would be because SQLite is written in C, not C++. > > D. Richard Hipp > [EMAIL PROTECTED] My company often needs to compile SQLite under C++, so we ran into the same problem. It's easy to get rid of the error messages: Mostly it's a matter of adding explicit typecasts, and of separating nested structs. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite remote management tools?
Federico Granata wrote: >> I was hoping there might be a client/server management tool out there. >> I would need the source code, since the server part would need to be >> ported to my embedded device. >> > Maybe you haven't yet read this http://www.sqlite.org/serverless.html > There isn't a sqlite server so you can't have a sqlite client, local or > remote. Sqlite doesn't come with a server, but some enterprising tool developer could write one, right? :-) - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite remote management tools?
Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Richard Klein wrote: >> Are there any GUI-based tools that provide >> management of a remote SQLite database? > > SQLite is an in process database and only provides code that ultimately > calls the operating system's open/read/write/lock/close methods. There > is nothing "remote" to it! True! Remote management would have to rely on either NFS-style mounting of remote volumes, or on a client/server mechanism. I was hoping there might be a client/server management tool out there. I would need the source code, since the server part would need to be ported to my embedded device. Thanks, - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite remote management tools?
Are there any GUI-based tools that provide management of a remote SQLite database? I'm looking for a workstation-based tool that will allow me to manage a database on an embedded device. Thanks in advance, - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed SQLite C/C++ interface behavior change.
> Does anybody have any thoughts on this proposed behavior changes for > the sqlite3_close() interface? > > D. Richard Hipp > [EMAIL PROTECTED] Fine with me. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_mode not implemented?
Bob Ebert wrote: > Is journal_mode a future optimization? The atomic commit documentation > (http://sqlite.org/atomiccommit.html) as well as the pragma docs > (http://www.sqlite.org/pragma.html) make tantalizing references to this > potentially useful optimization, but I've searched the 3.5.1 and 3.5.8 > sources and I can't find any mention of journal_mode switches in the > source. It appears as if 3.5.x behaves as if this pragma were set to > DELETE. > > Ah, ok, more digging in the wiki suggests that this isn't a released > feature yet, it's post-3.5.8. Anyone care to speculate on when this > will hit release? Regarding the new PRAGMA journal_mode: Since PERSIST is likely to be faster than DELETE on most platforms, is there ever a reason *not* to use it? Put differently, is there any advantage to DELETE over PERSIST? - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA journal_mode
I have a question about the new PRAGMA journal_mode: Since PERSIST is likely to be faster than DELETE on most platforms, is there ever a reason *not* to use it? Put differently, is there any advantage to DELETE over PERSIST? - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA journal_mode
I have a question about the new PRAGMA journal_mode: Since PERSIST is likely to be faster than DELETE on most platforms, is there ever a reason *not* to use it? Put differently, is there any advantage to DELETE over PERSIST? - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with SQLITE_OMIT_xxx
That's a good idea. My solution was simply to break up the one long continuation line into a bunch of single-line assignments: OPTS += -DSQLITE_DISABLE_LFS OPTS += -DSQLITE_OMIT_ALTERTABLE # OPTS += -DSQLITE_OMIT_ANALYZE OPTS += -DSQLITE_OMIT_ATTACH OPTS += -DSQLITE_OMIT_AUTHORIZATION OPTS += -DSQLITE_OMIT_AUTOVACUUM ... Scott Hess wrote: > Something you can do in such cases is instead of putting a comment at > the beginning of the line, change the define: > > -DXSQLITE_OMIT_TRIGGER\ > > Yeah, so there's a chance that you've just randomly changed the define > to enable Scary Feature X, if that's really a concern add more > arbitrary stuff, or append _notreally or whatever makes you > comfortable. > > -scott > > > On Mon, Apr 21, 2008 at 6:31 PM, Richard Klein > <[EMAIL PROTECTED]> wrote: >> D. Richard Hipp wrote: >>> On Apr 21, 2008, at 9:03 PM, Richard Klein wrote: >>>> I regenerated and recompiled the source files >>>> specifying that the following features (among >>>> others) are to be omitted: >>>> >>>> SQLITE_OMIT_REINDEX >>>> SQLITE_OMIT_VIEW >>>> SQLITE_OMIT_VIRTUALTABLE >>> It worked OK when I tried this combination. Are >>> you *sure* you set these options when you ran >>> "make target_source"? Are you certain that you >>> are using the correct set of source files? >> My bad. In the Makefile, I broke up OPTS using a >> backslash at the end of each line. Of course, this >> means that 'make' will ignore everything after the >> first '#' that it encounters. >> >> So my Makefile OPTS and my compilation OPTS are out >> of sync. >> >> As you can see from my OPTS definition, 'make' never >> even saw the OMITs of REINDEX, VIEW, and VIRTUALTABLE. >> >> It's amazing that I got as few link errors as I did! >> >> Sorry about that, >> - Richard >> >> -- >> >> OPTS = -DSQLITE_DISABLE_LFS \ >>-DSQLITE_OMIT_ALTERTABLE \ >>-DSQLITE_OMIT_ANALYZE\ >>-DSQLITE_OMIT_ATTACH \ >>-DSQLITE_OMIT_AUTHORIZATION \ >>-DSQLITE_OMIT_AUTOVACUUM \ >>-DSQLITE_OMIT_AUTOINCREMENT \ >>-DSQLITE_OMIT_BETWEEN_OPTIMIZATION \ >>-DSQLITE_OMIT_BLOB_LITERAL \ >>-DSQLITE_OMIT_CAST \ >>-DSQLITE_OMIT_CHECK \ >>-DSQLITE_OMIT_COMPLETE \ >>-DSQLITE_OMIT_COMPOUND_SELECT\ >>-DSQLITE_OMIT_CONFLICT_CLAUSE\ >>-DSQLITE_OMIT_DATETIME_FUNCS \ >>-DSQLITE_OMIT_EXPLAIN\ >>-DSQLITE_OMIT_FAULTINJECTOR \ >>-DSQLITE_OMIT_FLAG_PRAGMAS \ >>-DSQLITE_OMIT_FLOATING_POINT \ >>-DSQLITE_OMIT_FOREIGN_KEY\ >>-DSQLITE_OMIT_GET_TABLE \ >>-DSQLITE_OMIT_GLOBALRECOVER \ >>-DSQLITE_OMIT_INCRBLOB \ >> # -DSQLITE_OMIT_INTEGRITY_CHECK\ >>-DSQLITE_OMIT_LIKE_OPTIMIZATION \ >>-DSQLITE_OMIT_LOAD_EXTENSION \ >>-DSQLITE_OMIT_MEMORYDB \ >>-DSQLITE_OMIT_PAGER_PRAGMAS \ >> # -DSQLITE_OMIT_PARSER \ >> # -DSQLITE_OMIT_PRAGMA \ >>-DSQLITE_OMIT_PROGRESS_CALLBACK \ >>-DSQLITE_OMIT_QUICKBALANCE \ >>-DSQLITE_OMIT_REINDEX\ >>-DSQLITE_OMIT_SCHEMA_PRAGMAS \ >> # -DSQLITE_OMIT_SCHEMA_VERSION_PRAGMAS \ >>-DSQLITE_OMIT_SHARED_CACHE \ >>-DSQLITE_OMIT_SUBQUERY \ >>-DSQLITE_OMIT_TCL_VARIABLE \ >>-DSQLITE_OMIT_TEMPDB \ >>-DSQLITE_OMIT_TRACE \ >> # -DSQLITE_OMIT_TRIGGER\ >>-DSQLITE_OMIT_UTF16 \ >>-DSQLITE_OMIT_VACUUM \ >>-DSQLITE_OMIT_VIEW \ >>-DSQLITE_OMIT_VIRTUALTABLE \ >>-DSQLITE_OMIT_XFER_OPT >> >> ___ >> 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
Re: [sqlite] Performance statistics?
> Richard Klein wrote: >> Does SQLite have a mechanism, in addition to the >> ANALYZE statement, for recording and dumping >> performance statistics? >> > > What kind of performance statistics are you looking for? > > SQLiteSpy (see > http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index) measures > the execution time of each SQL statement to help you optimize your SQL. > > Dennis Cote I was thinking of something like the tools that Oracle provides to assist with performance monitoring and tuning: ADDM, TKProf, Statspack. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance statistics?
Does SQLite have a mechanism, in addition to the ANALYZE statement, for recording and dumping performance statistics? Thanks, - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_PAGER_PRAGMAS
I've generated and compiled the SQLite sources with the option SQLITE_OMIT_PAGER_PRAGMAS. If I call sqlite_prepare() and sqlite3_step() on the SQL statement "PRAGMA cache_size = 100;", I get return codes of SQLITE_OK and SQLITE_DONE, respectively, but the cache_size doesn't seem to change. Is this the correct behavior? If so, fine -- it's just that I would have expected sqlite3_ prepare() to return an error code. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve number of cached pages in memory?
You're right! A grep of the source code confirms that. My mistake. - Richard Jay A. Kreibich wrote: > On Tue, Apr 22, 2008 at 03:33:27PM -0700, Richard Klein scratched on the wall: >> You can call sqlite3_memory_highwater() to find >> the maximum amount of memory (in bytes) that your >> app has used. >> >> Then you can divide this number by 2000 (the default >> size of a page) to determine the max number of pages >> that have been cached. > > > The default page size is 1024 bytes. > > The default cache size (in pages) is 2000. > > > (At least according to http://www.sqlite.org/compile.html) > > > -j > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve number of cached pages in memory?
You can call sqlite3_memory_highwater() to find the maximum amount of memory (in bytes) that your app has used. Then you can divide this number by 2000 (the default size of a page) to determine the max number of pages that have been cached. - Richard Klein Ralf Junker wrote: > I need to retrieve the number of pages a SQLite database connection has > currently allocated in memory. The documentation unfortunately turned up no > results. I know about "PRAGMA cache_size", but this returns the maximum > number of pages possibly allowed in the cache, not the actual number of pages > currently cached. > > My aim is to calculate the accurate number of bytes actually consumed by a > single cached page. This figure will then allow to set PRAGMA cache_size to a > more precise value in order to limit memory usage. > > I do mind using undocumented APIs and will not cry tears if they change > without notice, so any pointers are welcome! > > Many thanks, > > Ralf > > ___ > 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
Re: [sqlite] OMIT_VIEW / TRIGGER bug?
While your solution will eliminate the link errors, I think that my
solution -- replacing && with || in the conditional expression -- is
actually the correct one, for two reasons:
(1) In the source code, every other conditional expression involving
OMIT_VIEW and OMIT_TRIGGER uses not && but ||.
(2) Logically, specifying OMIT_VIEW should be enough to omit sqlite3-
MaterializeView() -- I shouldn't have to additionally specify OMIT_
TRIGGER.
- Richard Klein
Mark Spiegel wrote:
> This was a problem for me too. I just chalked it up to deleting options
> and using the amalgamated source. (Seem to recall reading that this is
> not recommended.)
>
> Replace:
>
> SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32,
> int);
>
> with:
>
> #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
> SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32,
> int);
> #else /* #if !defined(SQLITE_OMIT_VIEW) &&
> !defined(SQLITE_OMIT_TRIGGER) */
> # define sqlite3MaterializeView(A,B,C,D,E) 0
> #endif /* #if !defined(SQLITE_OMIT_VIEW) &&
> !defined(SQLITE_OMIT_TRIGGER) */
>
> or equivalent. As I recall, this function is called from within an if
> block whose condition (because of the #defines) will never be true. For
> those of us who must live with the MSFT compilers, this is a problem.
>
> Richard Klein wrote:
>> I fixed my OPTS in the Makefile so that they are in sync
>> with my compilation options.
>>
>> Now all the unresolved references in the parser have dis-
>> appeared, but I'm still left with two unresolved references
>> to the function sqlite3MaterializeView():
>>
>> delete.obj : error LNK2019: unresolved external symbol
>> _sqlite3MaterializeView referenced in function _sqlite3DeleteFrom
>> update.obj : error LNK2019: unresolved external symbol
>> _sqlite3MaterializeView referenced in function _sqlite3Update
>>
>> The function sqlite3MaterializeView() is defined in the
>> file delete.c, as follows:
>>
>>
>> #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
>> /*
>> ** Evaluate a view and store its result in an ephemeral table. The
>> ** pWhere argument is an optional WHERE clause that restricts the
>> ** set of rows in the view that are to be added to the ephemeral table.
>> */
>> void sqlite3MaterializeView(
>>...
>> ){
>>...
>> }
>> #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
>>
>>
>> In my application, I've defined SQLITE_OMIT_VIEW, but *not*
>> SQLITE_OMIT_TRIGGER; that is, I want TRIGGERs, but not VIEWs.
>>
>> It would seem that in the conditional compilation expression
>> shown above, the && should be replaced by ||:
>>
>> #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
>>
>> In other words, if VIEW *or* TRIGGER is supported, then define
>> the function sqlite3MaterializeView().
>>
>> Making that change fixes the problem.
>>
>> - Richard
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] OMIT_VIEW / TRIGGER bug?
I fixed my OPTS in the Makefile so that they are in sync
with my compilation options.
Now all the unresolved references in the parser have dis-
appeared, but I'm still left with two unresolved references
to the function sqlite3MaterializeView():
delete.obj : error LNK2019: unresolved external symbol _sqlite3MaterializeView
referenced in function _sqlite3DeleteFrom
update.obj : error LNK2019: unresolved external symbol _sqlite3MaterializeView
referenced in function _sqlite3Update
The function sqlite3MaterializeView() is defined in the
file delete.c, as follows:
#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
/*
** Evaluate a view and store its result in an ephemeral table. The
** pWhere argument is an optional WHERE clause that restricts the
** set of rows in the view that are to be added to the ephemeral table.
*/
void sqlite3MaterializeView(
...
){
...
}
#endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
In my application, I've defined SQLITE_OMIT_VIEW, but *not*
SQLITE_OMIT_TRIGGER; that is, I want TRIGGERs, but not VIEWs.
It would seem that in the conditional compilation expression
shown above, the && should be replaced by ||:
#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
In other words, if VIEW *or* TRIGGER is supported, then define
the function sqlite3MaterializeView().
Making that change fixes the problem.
- Richard
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with SQLITE_OMIT_xxx
D. Richard Hipp wrote: > On Apr 21, 2008, at 9:03 PM, Richard Klein wrote: >> I regenerated and recompiled the source files >> specifying that the following features (among >> others) are to be omitted: >> >> SQLITE_OMIT_REINDEX >> SQLITE_OMIT_VIEW >> SQLITE_OMIT_VIRTUALTABLE > > It worked OK when I tried this combination. Are > you *sure* you set these options when you ran > "make target_source"? Are you certain that you > are using the correct set of source files? My bad. In the Makefile, I broke up OPTS using a backslash at the end of each line. Of course, this means that 'make' will ignore everything after the first '#' that it encounters. So my Makefile OPTS and my compilation OPTS are out of sync. As you can see from my OPTS definition, 'make' never even saw the OMITs of REINDEX, VIEW, and VIRTUALTABLE. It's amazing that I got as few link errors as I did! Sorry about that, - Richard -- OPTS = -DSQLITE_DISABLE_LFS \ -DSQLITE_OMIT_ALTERTABLE \ -DSQLITE_OMIT_ANALYZE\ -DSQLITE_OMIT_ATTACH \ -DSQLITE_OMIT_AUTHORIZATION \ -DSQLITE_OMIT_AUTOVACUUM \ -DSQLITE_OMIT_AUTOINCREMENT \ -DSQLITE_OMIT_BETWEEN_OPTIMIZATION \ -DSQLITE_OMIT_BLOB_LITERAL \ -DSQLITE_OMIT_CAST \ -DSQLITE_OMIT_CHECK \ -DSQLITE_OMIT_COMPLETE \ -DSQLITE_OMIT_COMPOUND_SELECT\ -DSQLITE_OMIT_CONFLICT_CLAUSE\ -DSQLITE_OMIT_DATETIME_FUNCS \ -DSQLITE_OMIT_EXPLAIN\ -DSQLITE_OMIT_FAULTINJECTOR \ -DSQLITE_OMIT_FLAG_PRAGMAS \ -DSQLITE_OMIT_FLOATING_POINT \ -DSQLITE_OMIT_FOREIGN_KEY\ -DSQLITE_OMIT_GET_TABLE \ -DSQLITE_OMIT_GLOBALRECOVER \ -DSQLITE_OMIT_INCRBLOB \ # -DSQLITE_OMIT_INTEGRITY_CHECK\ -DSQLITE_OMIT_LIKE_OPTIMIZATION \ -DSQLITE_OMIT_LOAD_EXTENSION \ -DSQLITE_OMIT_MEMORYDB \ -DSQLITE_OMIT_PAGER_PRAGMAS \ # -DSQLITE_OMIT_PARSER \ # -DSQLITE_OMIT_PRAGMA \ -DSQLITE_OMIT_PROGRESS_CALLBACK \ -DSQLITE_OMIT_QUICKBALANCE \ -DSQLITE_OMIT_REINDEX\ -DSQLITE_OMIT_SCHEMA_PRAGMAS \ # -DSQLITE_OMIT_SCHEMA_VERSION_PRAGMAS \ -DSQLITE_OMIT_SHARED_CACHE \ -DSQLITE_OMIT_SUBQUERY \ -DSQLITE_OMIT_TCL_VARIABLE \ -DSQLITE_OMIT_TEMPDB \ -DSQLITE_OMIT_TRACE \ # -DSQLITE_OMIT_TRIGGER\ -DSQLITE_OMIT_UTF16 \ -DSQLITE_OMIT_VACUUM \ -DSQLITE_OMIT_VIEW \ -DSQLITE_OMIT_VIRTUALTABLE \ -DSQLITE_OMIT_XFER_OPT ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems with SQLITE_OMIT_xxx
I regenerated and recompiled the source files specifying that the following features (among others) are to be omitted: SQLITE_OMIT_REINDEX SQLITE_OMIT_VIEW SQLITE_OMIT_VIRTUALTABLE Nevertheless, I am getting the following link errors: delete.obj : error LNK2019: unresolved external symbol _sqlite3MaterializeView referenced in function _sqlite3DeleteFrom update.obj : error LNK2019: unresolved external symbol _sqlite3MaterializeView referenced in function _sqlite3Update parse.obj : error LNK2019: unresolved external symbol _sqlite3VtabArgExtend referenced in function _yy_reduce parse.obj : error LNK2019: unresolved external symbol _sqlite3VtabArgInit referenced in function _yy_reduce parse.obj : error LNK2019: unresolved external symbol _sqlite3VtabBeginParse referenced in function _yy_reduce parse.obj : error LNK2019: unresolved external symbol _sqlite3VtabFinishParse referenced in function _yy_reduce parse.obj : error LNK2019: unresolved external symbol _sqlite3Reindex referenced in function _yy_reduce parse.obj : error LNK2019: unresolved external symbol _sqlite3CreateView referenced in function _yy_reduce Are there any workarounds for these, or will I have to remove the above OMIT options? Thanks, - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_xxx
> On Apr 21, 2008, at 4:25 PM, Richard Klein wrote: >> (3) We build SQLite for many different target platforms, using >> various C and C++ compilers. We get many (i.e. hundreds) of >> warnings, and even some errors. When fixing these problems, >> it is simply easier to edit many smaller files rather than one >> huge, unwieldy file. > > As for warnings, see http://www.sqlite.org/faq.html#q17 > SQLite is ANSI-C code, not C++ so if you try to compile > it with a C++ compiler you might well get errors. I suggest > you use a C compiler instead. Surely Visual Studio must > include a C compiler. > > D. Richard Hipp > [EMAIL PROTECTED] Our target platforms are a variety of set-top boxes from various manufacturers, so we're pretty much stuck with whatever toolchain we get from the vendor. The compiler is often ancient, and some- times is for C++ rather than C. We have to make sure our source code, including SQLite, compiles flawlessly in *all* these environ- ments. It's not a big deal, just a minor hassle. - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_xxx
Thanks, Mark! I use the individual source files rather than the amalgamation, for several reasons: (1) Visual Studio has trouble generating line number info for files that have more than 64K lines. (2) Perforce (our version control software) has trouble diff'ing two versions of a large file. (3) We build SQLite for many different target platforms, using various C and C++ compilers. We get many (i.e. hundreds) of warnings, and even some errors. When fixing these problems, it is simply easier to edit many smaller files rather than one huge, unwieldy file. - Richard Mark Spiegel wrote: > That's what I do. Once your makefile is set up, make the "sqlite3.c" > target if you want an amalgamated source file. Be sure to carefully > coordinate the defined values between the preprocessing step (to > generate your source file(s)) and the build of your application/dll. > > If you are using amalgamated source, you may find a few other small > problems when building your app, but they are easy to fix. > > Richard Klein wrote: >>> Richard Klein wrote: >>> >>>> In order to reduce SQLite's memory footprint in my embedded >>>> application, I want to use the SQLITE_OMIT_xxx options to >>>> remove unneeded features from SQLite. >>>> >>>> Using Cygwin running on Windows, I have successfully down- >>>> loaded the canonical sources and autoconfigured the Makefile. >>>> >>>> The Makefile seems to indicate that in order to generate >>>> the parser, opcodes, and keyword hash function so that they >>>> omit the unneeded features, I need only add the following >>>> line to the Makefile: >>>> >>>> OPTS = -DSQLITE_OMIT_xxx -DSQLITE_OMIT_yyy ... >>>> >>>> Is this correct? >>>> >>>> >>> I believe so, but I haven't ever used the OMIT options when building SQLite. >>> >>> Are you having a problem when you do this? >>> >>> Dennis Cote >>> >>> >> I haven't tried it yet ... I'll let you know if I have any >> problems. Thanks! >> >> - Richard >> >> ___ >> 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
Re: [sqlite] SQLITE_OMIT_xxx
> Richard Klein wrote: >> In order to reduce SQLite's memory footprint in my embedded >> application, I want to use the SQLITE_OMIT_xxx options to >> remove unneeded features from SQLite. >> >> Using Cygwin running on Windows, I have successfully down- >> loaded the canonical sources and autoconfigured the Makefile. >> >> The Makefile seems to indicate that in order to generate >> the parser, opcodes, and keyword hash function so that they >> omit the unneeded features, I need only add the following >> line to the Makefile: >> >> OPTS = -DSQLITE_OMIT_xxx -DSQLITE_OMIT_yyy ... >> >> Is this correct? >> > > I believe so, but I haven't ever used the OMIT options when building SQLite. > > Are you having a problem when you do this? > > Dennis Cote > I haven't tried it yet ... I'll let you know if I have any problems. Thanks! - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_xxx
In order to reduce SQLite's memory footprint in my embedded application, I want to use the SQLITE_OMIT_xxx options to remove unneeded features from SQLite. Using Cygwin running on Windows, I have successfully down- loaded the canonical sources and autoconfigured the Makefile. The Makefile seems to indicate that in order to generate the parser, opcodes, and keyword hash function so that they omit the unneeded features, I need only add the following line to the Makefile: OPTS = -DSQLITE_OMIT_xxx -DSQLITE_OMIT_yyy ... Is this correct? Thanks, - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using the SQLITE_OMIT_xxx options
In order to reduce SQLite's memory footprint in my embedded application, I want to use the SQLITE_OMIT_xxx options to remove unneeded features from SQLite. Using Cygwin running on Windows, I have successfully down- loaded the canonical sources and autoconfigured the Makefile. The Makefile seems to indicate that in order to generate the parser, opcodes, and keyword hash function so that they omit the unneeded features, I need only add the following line to the Makefile: OPTS = -DSQLITE_OMIT_xxx -DSQLITE_OMIT_yyy ... Is this correct? Thanks, - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_TRIGGER compilation problems
> On Apr 15, 2008, at 10:34 PM, Richard Klein wrote: >> I compiled SQLite 3 (version 3.5.7), specifying >> SQLITE_OMIT_TRIGGER to reduce the size of the >> generated code. >> > > The OMIT macros are not supported in the preprocessed source > code or in the amalgamation. To use the OMIT macros you much > compile from canonical sources using a unix-like development > environment. > > The reason for this is that SQLite includes several code files that > contain automatically generated code. And the generated code > depends on which OMIT macros are present. > > D. Richard Hipp > [EMAIL PROTECTED] Unfortunately, our build system doesn't support the notion of source code that is automatically generated. However, it does support the ability to turn "capabilities" on or off at build time, and to have these capabilities map to compilation switches. Thus, for example, the capability "CAP_SQL_TRIGGER = 0" can map to -DSQLITE_OMIT_TRIGGER at compile time. I plan to create capabilities corresponding to most of the SQLITE_ OMIT_xxx options. I think this will work just fine, even though I am using the preprocessed source code. All it means is that the parser will be bigger than it needs to be. By the way, I originally tried to use the amalgamation. While it worked, it was unwieldy, for a number of reasons: (1) Visual Studio has trouble generating line number info for source files containing more than 64K lines. (2) Perforce (the source control system that we use) has trouble diff'ing two versions of a very large file. (3) We have to make numerous small mods to SQLite in order to get it to compile on all our target platforms (set-top boxes), and it is simply easier to edit several small, pre- processed source files instead of one huge amalgamation file. There are several reasons we get many compilation warnings (and even a few errors) when we compile SQLite: (1) Each target has its own, often archaic, compiler, which has probably not been tested by the SQLite develop- ment team; (2) We usually compile at a fairly high warning level, so we get a lot of warnings about implicit type conversions. Inserting an explicit type cast usually fixes these. (3) We compile several targets using a C++, not C, compiler. There are a few places in the SQLite source where this causes problems. Most notably, in sqliteInt.h a number of nested structs are defined; these cause scoping problems in C++. The workaround is to move the nested struct outside of (and immediately preceding) the main struct. - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_TRIGGER compilation problems
I compiled SQLite 3 (version 3.5.7), specifying SQLITE_OMIT_TRIGGER to reduce the size of the generated code. I got the following compilation warnings in parse.c: - parse.y(990) : error C2220: warning treated as error - no object file generated parse.y(990) : warning C4013: 'sqlite3DeleteTriggerStep' undefined; assuming extern returning int parse.y(959) : warning C4013: 'sqlite3FinishTrigger' undefined; assuming extern returning int parse.y(965) : warning C4013: 'sqlite3BeginTrigger' undefined; assuming extern returning int parse.y(1006) : warning C4013: 'sqlite3TriggerUpdateStep' undefined; assuming extern returning int parse.y(1006) : warning C4047: '=' : 'TriggerStep *' differs in levels of indirection from 'int' parse.y(1011) : warning C4013: 'sqlite3TriggerInsertStep' undefined; assuming extern returning int parse.y(1011) : warning C4047: '=' : 'TriggerStep *' differs in levels of indirection from 'int' parse.y(1014) : warning C4047: '=' : 'TriggerStep *' differs in levels of indirection from 'int' parse.y(1018) : warning C4013: 'sqlite3TriggerDeleteStep' undefined; assuming extern returning int parse.y(1018) : warning C4047: '=' : 'TriggerStep *' differs in levels of indirection from 'int' parse.y(1021) : warning C4013: 'sqlite3TriggerSelectStep' undefined; assuming extern returning int parse.y(1021) : warning C4047: '=' : 'TriggerStep *' differs in levels of indirection from 'int' parse.y(1049) : warning C4013: 'sqlite3DropTrigger' undefined; assuming extern returning int - The various trigger functions are declared in sqliteInt.h as follows: - #ifndef SQLITE_OMIT_TRIGGER void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*, Expr*,int, int); void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*); void sqlite3DropTrigger(Parse*, SrcList*, int); void sqlite3DropTriggerPtr(Parse*, Trigger*); int sqlite3TriggersExist(Parse*, Table*, int, ExprList*); int sqlite3CodeRowTrigger(Parse*, int, ExprList*, int, Table *, int, int, int, int, u32*, u32*); void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*); void sqlite3DeleteTriggerStep(TriggerStep*); TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*); TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*, ExprList*,Select*,int); TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, int); TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*); void sqlite3DeleteTrigger(Trigger*); void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*); void sqlite3SelectMask(Parse *, Select *, u32); #else # define sqlite3TriggersExist(A,B,C,D,E,F) 0 # define sqlite3DeleteTrigger(A) # define sqlite3DropTriggerPtr(A,B) # define sqlite3UnlinkAndDeleteTrigger(A,B,C) # define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J,K) 0 # define sqlite3SelectMask(A, B, C) #endif - It would seem that, in the #else section, we just need to add 9 more no-op macros for the undefined trigger functions. - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Website typo
The web page http://www.sqlite.org/compile.html contains the following typo: SQLITE_OMIT_TRIGGER Defining this option omits support for VIEW objects... It should read: ... omits support for TRIGGER objects... - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] schema design question
Jay A. Kreibich wrote: > On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the wall: > >> My advice would be to try it and see. If table creation takes too long, >> you can always remove the UNIQUE constraint, and then write a routine to >> check the table for uniqueness after it's created. > > That "routine" could simply be creating an explicit unique index on > the column after all the data is loaded. If the index can be > created, you're good to go. > > This is still a slow process, but I know the creation of indexes on > existing tables is one area the development team hopes to speed up. > I'm not sure what the priority of that is, however. > >-j > Wouldn't this take just as long as creating the index immediately after creating the table, and then letting each INSERT update the index? Creating the index after all the data is loaded requires that the first row be inserted into the index Btree, then the second row be inserted into the Btree at the proper place, then the third row, and so on, re-balancing the Btree as necessary. Isn't this exactly the same process that takes place when you create the index first and then insert each row? - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] schema design question
> Jeff Gibson wrote: >> One thing your earlier suggestion brought up. The way I was hooking up >> tables before was something along the lines of: >> >> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, ); >> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, ); >> CREATE TABLE link(id1 INTEGER, id2 INTEGER); >> >> My understanding of your suggestion is: >> >> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER, >> ); >> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, ); >> >> with the understanding that id2 in primary will often be NULL. Are >> there any circumstances where the first alternative is more >> appropriate? I'm pretty new to databases, but I got my original idea >> from a few schemas that I've seen. I'm just trying to understand the >> trade-offs. >> Thanks a lot for your help, >> Jeff >> > > These different forms of linking the records are used for different > types of relations. The two tables can have records that are related in > a various combinations of one or many to one or many. > > one to one > many to one > one to many > many to many > > Using a third table is required to implement a many to many relation. > Each record in the third table stores one item of the relation (i.e > which record in the first table is related to which record in the second > table). > > A one to many relation is created by assigning an id to the record in > the one side of the relation and referencing that id in a column on the > many side of the relation. A many to one relation is the same a one to > many relation, with the order of the tables reversed. This is what you > have shown as Richard's suggestion. > > A one to one relation can be created by assigning an id to one record > and using that same id as the primary key on the related record. > > For your case, you need a one to one relation between the primary and > secondary tables. This can be done by using the same id for the related > record in the secondary table as was assigned to the record in the > primary table. > > CREATE TABLE primary(id INTEGER PRIMARY KEY, ); > CREATE TABLE secondary(id INTEGER PRIMARY KEY, ); > > insert into primary values(null, ); > insert into secondary values(last_insert_rowid(), ); > > When you want to retrieve the records you can use a join > > select * from primary join secondary using(id); > > or you can use a second select to retrieve the secondary fields using > the id obtained from the primary field. > > select * from primary; > if (has_secondary()) > select * from secondary where id = primary.id; > > This does not waste any space storing unnecessary null fields. You > should only resort the more complex foreign keys when you need to > represent a more complex relation. > > HTH > Dennis Cote As Dennis points out, I had assumed that the relationship between the primary and secondary tables was many-to-one, i.e. that several entries in the primary table could refer to the same entry in the secondary table. If that is not the case -- if the relationship is in fact one-to-one -- then Dennis's solution is the best one. I would use Dennis's two-SELECT approach rather than the join if speed is an issue. - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] schema design question
Jeff - I'm pretty new to databases myself, but I have seen examples of schemas that resemble your first alternative, i.e. that involve the creation of a third table containing just the linkages between the primary and secondary tables. And in fact, I think that is the right solution for your appli- cation. Although the link table duplicates the id1 column, it contains no wasted (NULL) entries. The second alternative (my proposal) doesn't duplicate id1, but 90% of the id2's are NULL. Since the primary table is very large, this represents an excessive waste of space. - Richard Jeff Gibson wrote: > Right. Hence my hesitation. :-) I suppose it's possible to check > uniqueness once at the end in O(N), but it would also take O(N) extra > storage, and I doubt sqlite is doing that... > > One thing your earlier suggestion brought up. The way I was hooking up > tables before was something along the lines of: > > CREATE TABLE primary(id1 INTEGER PRIMARY KEY, ); > CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, ); > CREATE TABLE link(id1 INTEGER, id2 INTEGER); > > My understanding of your suggestion is: > > CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER, > ); > CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, ); > > with the understanding that id2 in primary will often be NULL. Are > there any circumstances where the first alternative is more > appropriate? I'm pretty new to databases, but I got my original idea > from a few schemas that I've seen. I'm just trying to understand the > trade-offs. > Thanks a lot for your help, > Jeff > > Richard Klein wrote: >> On second thought, checking the entire table for uniqueness would seem >> to require O(N log N), regardless of whether it is done one INSERT at >> a time, or all at once after the table is created! >> >> - Richard >> >> Richard Klein wrote: >> >>> Quoting from the description of CREATE TABLE in the SQL Syntax section of >>> the SQLite documentation: >>> >>> "The UNIQUE constraint causes an index to be created on the specified >>> columns. >>> This index must contain unique keys." >>> >>> The creation of an index would seem to imply an O(log N) search on each >>> insertion, so you should be okay. >>> >>> My advice would be to try it and see. If table creation takes too long, >>> you can always remove the UNIQUE constraint, and then write a routine to >>> check the table for uniqueness after it's created. >>> >>> - Richard >>> >>> >>> Jeff Gibson wrote: >>> >>>> I see. Fortunately my application simplifies this since the database is >>>> created once and read many times, but is never modified after creation >>>> time. Regarding constraints, I was thinking it might be helpful to add >>>> a few where applicable (whether foreign key constraints or even simple >>>> uniqueness constraints) basically as assertions, but I was worried about >>>> the overhead it would take to enforce them when I'm creating the >>>> database. Do you know if a uniqueness constraint, for instance, does an >>>> O(N) search on each insertion? If so, it sounds prohibitive. >>>> Thanks, >>>> Jeff >>>> >>>> Richard Klein wrote: >>>> >>>>> Jeff, >>>>> >>>>> I think that's the right way to go for your application. There are a few >>>>> things you should be aware of regarding this approach. >>>>> >>>>> A column in one table that references a column in another table is called >>>>> a "foreign key" in database lingo. >>>>> >>>>> An issue with foreign keys is that it is important to keep the referencing >>>>> table (big_table) in sync with the referenced table (secondary_table). >>>>> >>>>> For example, if you delete an entry from secondary_table, you want to >>>>> update >>>>> the foreign key column in all entries in big_table that reference that >>>>> entry. >>>>> >>>>> What's the proper way to update the foreign key? It depends on your >>>>> appli- >>>>> cation. You might want to set the foreign key in the referencing entries >>>>> to >>>>> NULL, or you might want to delete the referencing entries, or you might >>>>> want >>>>> to do something else. >
Re: [sqlite] schema design question
On second thought, checking the entire table for uniqueness would seem to require O(N log N), regardless of whether it is done one INSERT at a time, or all at once after the table is created! - Richard Richard Klein wrote: > Quoting from the description of CREATE TABLE in the SQL Syntax section of > the SQLite documentation: > > "The UNIQUE constraint causes an index to be created on the specified columns. > This index must contain unique keys." > > The creation of an index would seem to imply an O(log N) search on each > insertion, so you should be okay. > > My advice would be to try it and see. If table creation takes too long, > you can always remove the UNIQUE constraint, and then write a routine to > check the table for uniqueness after it's created. > > - Richard > > > Jeff Gibson wrote: >> I see. Fortunately my application simplifies this since the database is >> created once and read many times, but is never modified after creation >> time. Regarding constraints, I was thinking it might be helpful to add >> a few where applicable (whether foreign key constraints or even simple >> uniqueness constraints) basically as assertions, but I was worried about >> the overhead it would take to enforce them when I'm creating the >> database. Do you know if a uniqueness constraint, for instance, does an >> O(N) search on each insertion? If so, it sounds prohibitive. >> Thanks, >> Jeff >> >> Richard Klein wrote: >>> Jeff, >>> >>> I think that's the right way to go for your application. There are a few >>> things you should be aware of regarding this approach. >>> >>> A column in one table that references a column in another table is called >>> a "foreign key" in database lingo. >>> >>> An issue with foreign keys is that it is important to keep the referencing >>> table (big_table) in sync with the referenced table (secondary_table). >>> >>> For example, if you delete an entry from secondary_table, you want to update >>> the foreign key column in all entries in big_table that reference that >>> entry. >>> >>> What's the proper way to update the foreign key? It depends on your appli- >>> cation. You might want to set the foreign key in the referencing entries to >>> NULL, or you might want to delete the referencing entries, or you might want >>> to do something else. >>> >>> In standard, full-blown SQL, you can define the synchronization behavior you >>> want with a "foreign key constraint". That is, you might create big_table >>> as follows: >>> >>> CREATE TABLE big_table ( >>> idINTEGER PRIMARY KEY, >>> col1 INTEGER, >>> col2 REAL, >>> col3 TEXT, >>> col4 BLOB, >>> col5 INTEGER, >>> CONSTRAINT col5_fk FOREIGN KEY(col5) >>>REFERENCES secondary_table(id) ON DELETE SET NULL, >>> ); >>> >>> This would define col5 as a foreign key referencing the id column of >>> secondary_ >>> table, and would specify that col5 should be set to NULL in all referencing >>> entries in big_table when an entry in secondary_table is deleted. >>> >>> Unfortunately, SQLite does not implement foreign key constraints. More >>> precisely, >>> they don't cause syntax errors, but they aren't enforced. Therefore, you >>> will >>> have to implement the desired synchronization behavior yourself. >>> Fortunately, >>> this is easy to do with the use of TRIGGERs, which *are* implemented in >>> SQLite. >>> >>> Here are some links that might be useful: >>> >>> Foreign keys: http://en.wikipedia.org/wiki/Foreign_key >>> SQLite triggers: http://www.sqlite.org/lang_createtrigger.html >>> >>> Hope this helps, >>> - Richard >>> >>> >>>> Thanks! I'll give that a try. >>>>Jeff >>>> >>>> Richard Klein wrote: >>>> >>>>>> Whether or not the the secondary columns are needed is a function of one >>>>>> of the primary columns. That function involves values from another >>>>>> table, though, so the general case would require a join. That other >>>>>> table is small, however, so I generally cache it outside the database. >>>>>> Some pseudocode for my expected use would be something like: >>>>>>
Re: [sqlite] schema design question
Quoting from the description of CREATE TABLE in the SQL Syntax section of
the SQLite documentation:
"The UNIQUE constraint causes an index to be created on the specified columns.
This index must contain unique keys."
The creation of an index would seem to imply an O(log N) search on each
insertion, so you should be okay.
My advice would be to try it and see. If table creation takes too long,
you can always remove the UNIQUE constraint, and then write a routine to
check the table for uniqueness after it's created.
- Richard
Jeff Gibson wrote:
> I see. Fortunately my application simplifies this since the database is
> created once and read many times, but is never modified after creation
> time. Regarding constraints, I was thinking it might be helpful to add
> a few where applicable (whether foreign key constraints or even simple
> uniqueness constraints) basically as assertions, but I was worried about
> the overhead it would take to enforce them when I'm creating the
> database. Do you know if a uniqueness constraint, for instance, does an
> O(N) search on each insertion? If so, it sounds prohibitive.
> Thanks,
> Jeff
>
> Richard Klein wrote:
>> Jeff,
>>
>> I think that's the right way to go for your application. There are a few
>> things you should be aware of regarding this approach.
>>
>> A column in one table that references a column in another table is called
>> a "foreign key" in database lingo.
>>
>> An issue with foreign keys is that it is important to keep the referencing
>> table (big_table) in sync with the referenced table (secondary_table).
>>
>> For example, if you delete an entry from secondary_table, you want to update
>> the foreign key column in all entries in big_table that reference that entry.
>>
>> What's the proper way to update the foreign key? It depends on your appli-
>> cation. You might want to set the foreign key in the referencing entries to
>> NULL, or you might want to delete the referencing entries, or you might want
>> to do something else.
>>
>> In standard, full-blown SQL, you can define the synchronization behavior you
>> want with a "foreign key constraint". That is, you might create big_table
>> as follows:
>>
>> CREATE TABLE big_table (
>> idINTEGER PRIMARY KEY,
>> col1 INTEGER,
>> col2 REAL,
>> col3 TEXT,
>> col4 BLOB,
>> col5 INTEGER,
>> CONSTRAINT col5_fk FOREIGN KEY(col5)
>>REFERENCES secondary_table(id) ON DELETE SET NULL,
>> );
>>
>> This would define col5 as a foreign key referencing the id column of
>> secondary_
>> table, and would specify that col5 should be set to NULL in all referencing
>> entries in big_table when an entry in secondary_table is deleted.
>>
>> Unfortunately, SQLite does not implement foreign key constraints. More
>> precisely,
>> they don't cause syntax errors, but they aren't enforced. Therefore, you
>> will
>> have to implement the desired synchronization behavior yourself.
>> Fortunately,
>> this is easy to do with the use of TRIGGERs, which *are* implemented in
>> SQLite.
>>
>> Here are some links that might be useful:
>>
>> Foreign keys: http://en.wikipedia.org/wiki/Foreign_key
>> SQLite triggers: http://www.sqlite.org/lang_createtrigger.html
>>
>> Hope this helps,
>> - Richard
>>
>>
>>> Thanks! I'll give that a try.
>>>Jeff
>>>
>>> Richard Klein wrote:
>>>
>>>>> Whether or not the the secondary columns are needed is a function of one
>>>>> of the primary columns. That function involves values from another
>>>>> table, though, so the general case would require a join. That other
>>>>> table is small, however, so I generally cache it outside the database.
>>>>> Some pseudocode for my expected use would be something like:
>>>>>
>>>>> prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
>>>>> while(step()) {
>>>>>
>>>>>if( F(primary_column_values) ) {
>>>>> Fetch secondary values
>>>>> }
>>>>>
>>>>> do something with primary and maybe secondary values;
>>>>>
>>>>> }
>>>>>
>>>>> Where F would be implemented outside the database.
>>>>> Thanks,
>>>>> Jeff
>>>>>
>>>&
Re: [sqlite] schema design question
Jeff,
I think that's the right way to go for your application. There are a few
things you should be aware of regarding this approach.
A column in one table that references a column in another table is called
a "foreign key" in database lingo.
An issue with foreign keys is that it is important to keep the referencing
table (big_table) in sync with the referenced table (secondary_table).
For example, if you delete an entry from secondary_table, you want to update
the foreign key column in all entries in big_table that reference that entry.
What's the proper way to update the foreign key? It depends on your appli-
cation. You might want to set the foreign key in the referencing entries to
NULL, or you might want to delete the referencing entries, or you might want
to do something else.
In standard, full-blown SQL, you can define the synchronization behavior you
want with a "foreign key constraint". That is, you might create big_table
as follows:
CREATE TABLE big_table (
idINTEGER PRIMARY KEY,
col1 INTEGER,
col2 REAL,
col3 TEXT,
col4 BLOB,
col5 INTEGER,
CONSTRAINT col5_fk FOREIGN KEY(col5)
REFERENCES secondary_table(id) ON DELETE SET NULL,
);
This would define col5 as a foreign key referencing the id column of secondary_
table, and would specify that col5 should be set to NULL in all referencing
entries in big_table when an entry in secondary_table is deleted.
Unfortunately, SQLite does not implement foreign key constraints. More
precisely,
they don't cause syntax errors, but they aren't enforced. Therefore, you will
have to implement the desired synchronization behavior yourself. Fortunately,
this is easy to do with the use of TRIGGERs, which *are* implemented in SQLite.
Here are some links that might be useful:
Foreign keys: http://en.wikipedia.org/wiki/Foreign_key
SQLite triggers: http://www.sqlite.org/lang_createtrigger.html
Hope this helps,
- Richard
> Thanks! I'll give that a try.
>Jeff
>
> Richard Klein wrote:
>>> Whether or not the the secondary columns are needed is a function of one
>>> of the primary columns. That function involves values from another
>>> table, though, so the general case would require a join. That other
>>> table is small, however, so I generally cache it outside the database.
>>> Some pseudocode for my expected use would be something like:
>>>
>>> prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
>>> while(step()) {
>>>
>>>if( F(primary_column_values) ) {
>>> Fetch secondary values
>>> }
>>>
>>> do something with primary and maybe secondary values;
>>>
>>> }
>>>
>>> Where F would be implemented outside the database.
>>> Thanks,
>>> Jeff
>>>
>> I assume that the primary SELECT shown above can be made suitably fast
>> by creating the appropriate indices on big_table.
>>
>> If the secondary columns are kept in a separate, secondary_table, and
>> a fifth primary column is added that contains the ROWID of the approp-
>> riate entry in the secondary_table (or NULL if the secondary_table is
>> not needed), then the "Fetch secondary values" operation should be very
>> fast as well.
>>
>> It seems to me that this approach would be faster than a join, and
>> would consume less space than an 8-column table containing mostly
>> NULLs in the secondary columns.
>>
>> Of course, this approach would cost you some extra space, in the form
>> of the 5th primary column containing the secondary ROWID.
>>
>> - Richard Klein
>>
>> ___
>> 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
Re: [sqlite] schema design question
> Whether or not the the secondary columns are needed is a function of one
> of the primary columns. That function involves values from another
> table, though, so the general case would require a join. That other
> table is small, however, so I generally cache it outside the database.
> Some pseudocode for my expected use would be something like:
>
> prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
> while(step()) {
>
>if( F(primary_column_values) ) {
> Fetch secondary values
> }
>
> do something with primary and maybe secondary values;
>
> }
>
> Where F would be implemented outside the database.
> Thanks,
> Jeff
I assume that the primary SELECT shown above can be made suitably fast
by creating the appropriate indices on big_table.
If the secondary columns are kept in a separate, secondary_table, and
a fifth primary column is added that contains the ROWID of the approp-
riate entry in the secondary_table (or NULL if the secondary_table is
not needed), then the "Fetch secondary values" operation should be very
fast as well.
It seems to me that this approach would be faster than a join, and
would consume less space than an 8-column table containing mostly
NULLs in the secondary columns.
Of course, this approach would cost you some extra space, in the form
of the 5th primary column containing the secondary ROWID.
- Richard Klein
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] schema design question
> I'm pretty new to databases, and I have a schema design question. I > don't know enough about the guts of how sqlite works to know how to make > some tradeoffs. I have a large (potentially millions of entries) table > and it has 4 columns which are needed for every entry, and 4 more that > are needed for about 10% of the entries. I'm trying to decide whether I > want one table with 8 columns with a bunch of NULLs or two tables with > no NULLs that will require a join to get all of the 8 column values. I > assume this is a space/performance tradeoff, since I would think > searching one table would be a lot faster than doing a join, but I'm not > sure what the impact would be in terms of disk/memory/performance of all > those NULLs. >Does anybody have any suggestions? >Thanks, >Jeff Can you give us a little more information? Specifically, is there any way to tell, by looking at the 4 primary columns, that you are dealing with one of the 10% entries that requires looking at the 4 secondary columns? - RichardKlein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> On 4/9/08, Richard Klein <[EMAIL PROTECTED]> wrote: >>> Richard Klein <[EMAIL PROTECTED]> >>> wrote: >>>> One question: I thought that the '\' character is not part of the >>>> SQL standard. (That's why I specified '/' instead as my ESCAPE >>>> character). >>> I'm not sure I understand. You can use any character as an escape >>> character. E.g. LIKE '2!_%' ESCAPE '!'. A backslash is as good as any. >>> >>> Igor Tandetnik >> I'm a SQL newbie, so I may very well be wrong about this ... >> >> My understanding is that you can use any legal SQL character as an escape >> character, but that a backslash is not a legal SQL character. >> >> I got this impression from a sentence I read on the SQLite website (in >> the description of literal numeric values): >> >> "C-style escapes using the backslash character are not supported because >> they are not standard SQL." >> > > If I understand correctly Igor Tandetnik's comment "A backslash is as > good as any.", in the above quote the phrase "they are not standard > SQL" refers to C-style escapes and not to the use of the backslash > character as an escape defined with ESCAPE. > > LIKE '2\_%' > > is not valid. > > LIKE '2\_%' ESCAPE '\' > > is valid. > > Robert Wishlaw > I just now checked the use of backslash as an escape character in LIKE clauses, and it works just fine. So, regardless of whether backslash is a legal SQL character, it is apparently acceptable to SQLite, at least in LIKE clauses. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> Richard Klein <[EMAIL PROTECTED]>
> wrote:
>> One question: I thought that the '\' character is not part of the
>> SQL standard. (That's why I specified '/' instead as my ESCAPE
>> character).
>
> I'm not sure I understand. You can use any character as an escape
> character. E.g. LIKE '2!_%' ESCAPE '!'. A backslash is as good as any.
>
> Igor Tandetnik
I'm a SQL newbie, so I may very well be wrong about this ...
My understanding is that you can use any legal SQL character as an escape
character, but that a backslash is not a legal SQL character.
I got this impression from a sentence I read on the SQLite website (in
the description of literal numeric values):
"C-style escapes using the backslash character are not supported because
they are not standard SQL."
So then I checked the spec, and found syntax definitions (reproduced at
the end of this email) that do seem to confirm that backslash ('\') is
not a legal SQL language character.
I haven't checked to see whether SQLite accepts backslash as an escape
character.
- Richard Klein
==
::=
|
|
::=
|
::=
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
| P | Q | R | S | T | U | V | W | X | Y | Z
::=
a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
| p | q | r | s | t | u | v | w | x | y | z
::=
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
::=
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
::= !! space character in character set in use
::= "
::= %
::= &
::= '
::= (
::= )
::= *
::= +
::= ,
::= -
::= .
::= /
::= :
::= ;
::= <
::= =
::= >
::= ?
::= [
::= ]
::= _
::= |
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> Xuanvinh Vu <[EMAIL PROTECTED]> wrote: >> Sorry it was actually quite simple for the WHERE clause it should be >> >> WHERE Name LIKE '2_%' >> >> to select Name that starts with the string "2_" > > Note that '_' is one of the special characters LIKE operator recognizes: > it matches any character (the other one, %, matches any sequence of > characters). Thus, the condition you show is actually looking for > strings that begin with '2' and are at least two characters long. > > You want something like this: > > WHERE Name LIKE '2\_%' ESCAPE '\' > > Igor Tandetnik Looks like Igor's and my replies crossed in the (e)mail! One question: I thought that the '\' character is not part of the SQL standard. (That's why I specified '/' instead as my ESCAPE character). - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select row names that start with this string...
> Xuanvinh Vu <[EMAIL PROTECTED]> wrote: >> I have search google but have not found a solution. I have a field >> called Name and I want to select the rows that have Name starts with >> certain string. How could I do this? > > select * from tableName where Name LIKE 'xyz%'; > > Igor Tandetnik I would add that it seems from Xuanvinh's post that he is looking for names that start with '2_'. Since LIKE considers '_' to be a metacharacter that matches any single character, wouldn't the '_' need to be escaped? Something like this: select * from tableName where Name LIKE '2/_%' ESCAPE '/'; - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2.8.17 --> 3.5.6 performance regression
Mike, You guessed correctly! I inserted a "PRAGMA synchronous = NORMAL;" into the SQLite 3 version of my test suite, and obtained the same performance as I observed with SQLite 2. I should have realized that the kind of slowdown I was observing could only be explained by extra disk I/O operations. Thanks for your help! - Richard > I would guess PRAGMA synchronous. Per documentation: > > "In SQLite version 2, the default value is NORMAL. For version 3, the > default was changed to FULL." > > Try setting it to NORMAL for v3 tests and see what that does. > > -- Mike > > On Thu, Mar 27, 2008 at 11:06 PM, Richard Klein > <[EMAIL PROTECTED]> wrote: >> I've recently upgraded from SQLite 2.8.17 >> to 3.5.6. >> >> Upon running a test program that measures >> the execution time of SQL statements typical >> for my application, I've noticed a definite >> performance degradation: >> >> INSERT: 34% slowdown >> UPDATE: 47%" >> DELETE: 50%" >> >> Has anyone else noticed this? I haven't done >> any profiling of the code (yet) to see where >> the time is being spent. >> >> - Richard >> >> ___ >> 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
Re: [sqlite] Trigger's actions and callbacks
> Hi, all.
>
> I have the following problem: I have some processes that access the same db
> file. When one of them would change some data inside the db it must notify
> all other processes about change AND send them changed data (or it's unique
> description).
>
> But I didn't find any method to perform this using db triggers or callbacks.
> On some certain reasons I am not allowed to write wrapper that will send
> notifications using IPC. I would like db to perform this task.
I have a similar problem, which I solved using db triggers, along with a
callback mechanism involving a user-defined function.
> As for triggers and callbacks (hooks): they are fired before the data is
> stored to the db (file), so that I coudn't even read changed data back
> inside hook. sqlite_update_hook() returnes rowID, but I didn't find any API
> to use this value...
Triggers can be defined to happen BEFORE or AFTER the database event.
Either way, you can access the old and new values of a field using the
OLD and NEW keywords. See www.sqlite.org->Documentation->SQL Syntax->CREATE
TRIGGER
for details.
> I would appriciate any ideas or comments.
> Thanks in advance.
Below is a memo I wrote to my boss describing how we could do change
notification
in SQLite. It is written in terms of SQLite 2; to use the same mechanism in
SQLite 3, you would use sqlite3_create_function() instead of
sqlite_create_function()
to register your user-defined function. Note that in SQLite 3, the parameters
to
sqlite(3)_create_function() have changed, as have the parameters passed to your
user-defined function.
If people are interested, I can turn this into documentation (Wiki article?)
for the SQLite website.
Regards,
- Richard Klein
==
Hi Dave,
As you mentioned earlier, the PVR 2.0 Scheduler maintains its own data
structures
that must be kept in sync with the database. Therefore, if the app modifies the
database, the Scheduler needs to be notified of the change.
In playing around with SQLite, I have come up with a change notification
mechanism
that makes use of a user-defined function is conjunction with a SQL entity
known as
a "trigger".
A trigger is a SQL statement that is associated with a specified table, and
with a
specified action (INSERT, UPDATE, or DELETE) on that table. This SQL statement
is
automatically executed when the associated action is performed on the associated
table.
To create an INSERT trigger on the 'requests' table, you would execute the
following
SQL statement:
CREATE TRIGGER InsertedRequest AFTER INSERT ON requests
BEGIN
SELECT ChangeNotify('requests', 'INSERT', NEW.ulRequestId);
END;
UPDATE and DELETE triggers would be created in a similar fashion.
The above statement defines a trigger, named 'InsertedRequest', that will fire
*after*
an INSERT operation is performed on the 'requests' table. This trigger will
call a
user-defined function named 'ChangeNotify'. ChangeNotify takes three
parameters: The
name of the affected table, the operation (INSERT, UPDATE, or DELETE) that was
performed,
and the Request ID (ulRequestId) of the affected row.
The ChangeNotify function is defined as follows:
static void changeNotify(sqlite_func* context, int argc, const char** argv)
{
const char *table, *action, *rowid;
assert(argc == 3);
table = argv[0];
action = argv[1];
rowid = argv[2];
dprintf("Database changed: table = %s, action = %s, rowid = %s\n", table,
action, rowid);
}
Like all user-defined functions, changeNotify() is called with three
parameters: An opaque
pointer 'context', the argument count 'argc' (which will be '3' in this case),
and the three
arguments (table, action, and row in this case).
This simple version of changeNotify() simply prints out the calling parameters.
The production
version will invoke a callback that has been registered by the Scheduler.
Of course, like all user-defined functions, changeNotify() must be registered
with SQLite:
status = sqlite_create_function(pDb, "ChangeNotify", 3, changeNotify,
NULL);
if (status != SQLITE_OK) {
/* error */
}
where
pDbis an opaque pointer;
"ChangeNotify" is the name of the user-defined function;
3 is the number of parameters taken by the user-defined function;
and
changeNotify is the address of the user-defined function.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] mem5 ("buddy system") usable?
> On Mar 31, 2008, at 10:00 PM, Richard Klein wrote: >> I just downloaded 3.5.7, and noticed that there is a >> new memory allocator mem5.c, which apparently uses the >> "buddy system" to allocate power-of-two-sized chunks >> of memory from a static pool. This allocator is used >> by defining SQLITE_POW2_MEMORY_SIZE. >> >> Is it okay to use this allocator, or is it only exper- >> imental (and therefore liable to disappear in a future >> release)? >> > > I'm not making any promises about any of the current five > memory allocators. I might decide to replace them all tomorrow. > > But mem5 is high on the list of memory allocators to keep > since it can, under some circumstances, guarantee not to > fragment memory, which is a desirable property for > embedded systems. > > D. Richard Hipp > [EMAIL PROTECTED] > Fair enough. But can I assume that mem5 *does* currently work, as far as you know? (I'd really like to use it, as I'm using SQLite on an embedded system.) - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] mem5 ("buddy system") usable?
I just downloaded 3.5.7, and noticed that there is a new memory allocator mem5.c, which apparently uses the "buddy system" to allocate power-of-two-sized chunks of memory from a static pool. This allocator is used by defining SQLITE_POW2_MEMORY_SIZE. Is it okay to use this allocator, or is it only exper- imental (and therefore liable to disappear in a future release)? Thanks, - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MEMORY_SIZE=nnn
I think I found the answer to my own question: (1) Pick an arbtrary, huge initial value for nnn in the compilation option SQLITE_MEMORY_SIZE=nnn; (2) Let my app run for awhile; (3) Call sqlite_memory_highwater() to find out the maximum amount of memory that has ever been allocated (i.e. the high water mark); (4) Recompile with SQLITE_MEMORY_SIZE = high_ water_mark + 20% (or whatever). - Richard Richard Klein wrote: > I've been porting 3.5.6 to my company's embedded > platform. I've implemented a custom VFS, and a > custom Mutex subsystem, and these seem to work > just fine. > > I tried to implement a custom Memory subsystem, > but I see that as of 3.5.2 the compilation option > SQLITE_OMIT_MEMORY_ALLOCATION is no longer supported. > > Instead, it is recommended that I use the compilation > option SQLITE_MEMORY_SIZE=nnn, which will cause SQLite > to allocate memory from a static block of storage that > is nnn bytes in size. > > My question is: What is a reasonable value for nnn? > > Thanks, > - Richard > ___ > 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] 2.8.17 --> 3.5.6 performance regression
I've recently upgraded from SQLite 2.8.17 to 3.5.6. Upon running a test program that measures the execution time of SQL statements typical for my application, I've noticed a definite performance degradation: INSERT: 34% slowdown UPDATE: 47%" DELETE: 50%" Has anyone else noticed this? I haven't done any profiling of the code (yet) to see where the time is being spent. - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_MEMORY_SIZE=nnn
I've been porting 3.5.6 to my company's embedded platform. I've implemented a custom VFS, and a custom Mutex subsystem, and these seem to work just fine. I tried to implement a custom Memory subsystem, but I see that as of 3.5.2 the compilation option SQLITE_OMIT_MEMORY_ALLOCATION is no longer supported. Instead, it is recommended that I use the compilation option SQLITE_MEMORY_SIZE=nnn, which will cause SQLite to allocate memory from a static block of storage that is nnn bytes in size. My question is: What is a reasonable value for nnn? Thanks, - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] dot locking
I seem to recall a post from someone (drh?) stating that the Old School dot-locking routines one sees in the amalgamation have not yet been thoroughly tested and should not be used. Is this still true? Thanks, - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, Prevent Master Journal file.
Ken - You could write your own VFS that overrides the xOpen method so that it doesn't create a journal file. Quoting from the documentation: "The file I/O implementation can use the object type flags to change the way it deals with files. For example, an application that does not care about crash recovery or rollback, might make the open of a journal file a no-op. Writes to this journal are also a no-op. Any attempt to read the journal returns SQLITE_IOERR." The object type flags referred to in the above paragraph are included in the 'flags' parameter passed to xOpen. See http://www.sqlite.org/34to35.html for more details. - Richard Ken wrote: > Maybe this could be added as a "Feature request" to open_v2 ? > > Add, > > #define SQLITE_OPEN_OMIT_JOURNAL 0x8000 > > Test this flag and set the omit_journal parameter to Btree factory... To > cause sqlite to omit journal creation. Obviously this has serious impact to a > databases recoverability. But when the Durability component of ACID is not > needed it should provide a nice performance boost! > > In my particular case, I have many small db files that have data. These are > "batched" into a db file. If the process fails the in progress batch will > simply be overwritten and re-created. Hence it is an all or none approach and > does not need any recoverability. > > Thanks, > Ken > > > > > ___ > 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
Re: [sqlite] table names from subselects?
Jay Sprenkle wrote: > I'm not sure if this counts as a bug or not. > > SQLite version 3.5.2 > Enter ".help" for instructions > sqlite> select * from (select user.id from user ) ; > 0 > 1 > 2 > 3 > 4 > sqlite> select * from (select user.id from user ) where id=1 ; > SQL error: no such column: id > sqlite> select * from (select user.id from user ) where user.id=1 ; > SQL error: no such column: user.id > sqlite> select * from (select user.id from user ) as blah where blah.id=1 ; > SQL error: no such column: blah.id > sqlite> select * from (select user.id from user ) as blah where > blah.user.id=1 ; > > SQL error: no such column: blah.user.id > sqlite> > > Should the result of a subselect have a table name? If not, how do you > reference it? > > > select * from (select id from user where id = 1); - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficiency Question - Value Or Liability for Indexing of This Table?
I agree. I would add that the same considerations apply to denormalizing a database to gain efficiency. Start with a normalized database. If performance is lacking, try various kinds of denormalization. Back out any denormalization that doesn't improve performance. When testing performance, be sure to test all use cases. Denormalization tends to speed up SELECTs at the expense of slowing down INSERTs, UPDATEs, and DELETEs. - Richard Klein [EMAIL PROTECTED] wrote: > "Lee Crain" <[EMAIL PROTECTED]> wrote: >> I am debating the performance gains to be realized, if any, by indexing a >> particular table in a system implementation for which I am responsible. >> > > You are getting way ahead of yourself. > > Stop trying to speculate about whether or not an index > or indices will be useful. Write your application first. > Then test it. Measure the speed. Then add an index and > test and measure again. Compare the measurements. Add > a different index or combination indices. Repeat until > done. > > Premature optimization leads to bad design. Build your > application first, then measure it to see where optimization > is needed. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > ___ > 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
Re: [sqlite] Dump w/o Schema
Mark Riehl wrote: Is there a way to dump out the contents of the database w/o having the CREATE TABLE statements? For example, I can do the following: sqlite3 foo.db .dump > foo.dmp However, foo.dmp contains all of the CREATE TABLE statements. I just want all of the INSERT INTO statements associated with this database. I looked through the documentation but didn't find the answer to this one, I apologize if it's already in the docs. Thanks, Mark No, there is no way to do this in sqlite3. The .dump command is intended to produce a complete set of SQL statements that, when executed, will recreate the database being dumped. Can't you just remove the CREATE TABLE statements manually, or pipe the output of .dump to sed or awk? Regards, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit fails due to "database is locked" in active transaction
Requiring the second transaction to complete first is expected in terms of SQLIte's concurrency system. So in terms of using SQLite, I need to close the entire transaction and restart it when I get a "database locked" return code in a writer thread? It's not enough to just retry the commit in a little while? You don't need to close the connection, but you do need to ROLLBACK the transaction, unless you have some sort of a priori knowledge that the second transaction will not try to write to the database. In such a case, the second transaction will not try to acquire the RESERVED lock already held by the first transaction, and so the second transaction will eventually run to completion. In such a scenario, the first transaction can sit in a busy wait loop (sleep for a bit, then retry the COMMIT) until the COMMIT succeeds. However, if the second transaction will (or might) try to write to the database, you must ROLLBACK the first transaction, sleep for a bit, and restart the first transaction. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Occasional Database is Locked Error
Mark Riehl wrote: Am I just getting [un]lucky and issuing selects at the same time I'm writing from the other process? Yes. I'm planning on using a web-based app to periodically poll the database (to provide summary information) while the C++ app performs the inserts. What is the best way to reduce/minimize the lock issue? There is no way to reduce/minimize the lock issue. "Database is locked" is a "normal" error return that your polling app must be prepared to deal with. As other folks have noted, the way to deal with it is to sleep for awhile and then retry the SELECT. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Consortium Launches
[EMAIL PROTECTED] wrote:
SQLite Consortium Launches With Mozilla And
Symbian As Charter Members
...
Additional information is available at the SQLite website,
http://www.sqlite.org/.
Congratulations!
You might want to fix one small typo I noticed:
"The SQLite Consortium is a membership association dedicated to
insuring the continuing vitality and independent of SQLite."
should be:
"... independence of SQLite."
^^
Richard Klein
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] SQLite is in Android
Surely I'm not the first person to notice that SQLite is part of Google's open-source Android platform for mobile phones. This is a *huge* win for SQLite ... - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need help reading 3.3.2 database files with 3.5.2...
The previous statement is actually more general: SQLite version 3.x.y can read and write any database created by any prior version of SQLite. Even SQLite 2.w.z ? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
Dennis Cote wrote: 1) The line spacing of the sans-serif Verdana font is too tight, making it difficult to read in blocks of text. I would suggest adding "line-height: 16pt;" or something similar to the body section of your CSS. The additional spacing make the text areas more readable to me. 2) I generally find the default Verdana font size to be too large. The whole site looks better to me if I do (in Firefox) View->Text Size->Decrease once to reduce the font size everywhere. 4) The colors used for links are too similar to the text color and to each other. If the links are supposed to standout from the text, the color should be more distinctive. I appreciate that the links are underlined, but I think a more distinctive color would help locating them quickly. Also, the color difference between visited links is insufficient to allow one to tell if a link has been visited or not with a glance, unless it is adjacent to another link. If you don't want to mark visited links, then make the colors the same. If you do, then make the color more obviously different from an unvisited link. I agree. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
* Suggestions for something better to put on the home page. I see the home page has been expanded. Very nice! I would add some formatting to the overview text to make it more visually appealing. Perhaps make each paragraph a bullet item, with the first sentence in bold: o *SQLite is an embedded SQL database engine.* Unlike most other SQL databases, SQLite does not have a ... o *SQLite is a compact library.* With all features enabled, the library size can be less than 250KiB,... o *SQLite is very reliable.* [NOTE: This sounds less wishy-washy then "has a reputation for being very reliable."] Over two-thirds of the source code ... o *SQLite is well supported.* The SQLite code base is supported by an international team of developers ... Something along those lines ... Cheers, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
[EMAIL PROTECTED] wrote: What? And encourage people to write multitheaded programs? Not likely... I've been meaning to ask ... When you say that multiple threads are evil, do you mean "as opposed to multiple processes"? Or do you feel that multiprogramming in general is evil? - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
John Stanton wrote: How about having adding a social networking capability so that non-technical people will have a reason to use the website. You cannot expect to attract them with a frugal and highly functional embedded database library. LOL! - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] wrote: "Mark Wyszomierski" <[EMAIL PROTECTED]> wrote: I put up 4 variations. Please, everyone, offer your opinions: (1) http://sqlite.hwaci.com/v1/ No CSS of any kind. (2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners (3) http://sqlite.hwaci.com/v3/ CSS menus with square corners (4) http://sqlite.hwaci.com/v4/ CSS font specification only (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. That leaves me with (4). I suppose we could go with (4) now and change it later I like (1) the best. It is simple, uncluttered, and minimalist. I don't think the fonts are ugly at all, at least not in IE6. (What's so ugly about Times? It's the most popular font in the world.) (4) is also fine, if you insist on using CSS... - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: It takes time to get all popular browsers working, but it leaves a good first impression with potential users of your software. It seems like a better solution would be to do the website without any CSS and then spend the days or weeks of frustration saved working on SQLite instead. AMEN! - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] wrote: Regarding the basic "look" of the site, we were considering using a style similar to the once found at ActiveState http://www.activestate.com/ However, as we started to prototype this, we wrote down a very simple CSS/Javascript-free template and after looking at it, thought that this template might actually be better. By being CSS and Javascript-free, the new design also stays closer to the minimalist spirit of SQLite. A rough prototype of what a revised website might look like can be seen at http://sqlite.hwaci.com/ Personally, I prefer the minimalist look. And nobody is especially happy with the content of the homepage. (Suggestions for what should appear on the homepage are welcomed.) I happen to like the content of the homepage (of the *existing* site, not the new one). On the left, it shows all the info that a newbie wants to know about SQLite. On the right, it shows all the news of interest to an experienced SQLite user. It thus satisfies a wide audience at a single glance. Perfect! - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] wrote: There is a new look up on the demo site at http://sqlite.hwaci.com/ It looks good on Firefox and Safari, but IE6 renders it incorrectly. Being entirely in the unix world now, I am of a mind to ignore the IE6 problem and just let lingering IE6 users see a goofed up display. I wonder if others have differing views on this. I think it's worth the effort to make it look good under IE6. I'm a big fan of open source, and am not particularly fond of Microsoft, but IE6 is still my browser of choice. Why? Simply because I have found that it still does a better job of rendering most web content (probably because most web designers optimize for IE). - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] wrote: We are looking at renovating the design of the SQLite website and would love to have suggestions from the community. If you have any ideas on how to improve the SQLite website, please constribute either to the mailing list or directly to me. o The 'contrib' section could be better organized, say into categories. Also it would be nice if someone could at least desk-check these contributions for accuracy, relevancy, etc. o The 'documentation' section could be organized into subsections, with a table of contents. Someone should write documentation on how to test SQLite using the test fixture. Also need documentation on how to use Mktclapp to build the test scripts, Tcl interpreter, and SQLite code into an executable that can run on any platform. o The 'faq' should be updated to include some additional frequently asked questions, such as "how do I implement a scrolling cursor using SQLite?" (Yes, there's already an article in the Wiki, but the faq should at least link to that article.) o The 'wiki' Index page needs updating. o It's not immediately obvious that when you select certain links -- namely, 'bugs', 'timeline', and 'wiki', that you are entering a browser-based tool (Cvstrac?) where some links have different meanings than what you'd expect. For example, the 'Home' link (in the blue rectangle) takes you to the Cvstrac home page, not the SQLite home page. The blue rectangle does give the user some indication that he is in the Cvstrac context; however, I'd recommend making this more explicit -- maybe put a Cvstrac logo inside the blue rectangle. o Overall, I love the way the website emphasizes content over form, and recommend you keep it that way. Give us meat, meat, and more meat! Keep the sales bulls**t to a minimum. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Transactions on a shared database connection
Richard Klein wrote: John Firebaugh wrote: I take it this mean that if, say, thread A begins a transaction, and modifies the database, then thread B modifies the database, then thread A commits the transactions, B's modifications are also committed? Nope. Once thread A begins a transaction, no other thread (or process) can modify the database until thread A commits. (That's what "atomic transaction" means!) In the above scenario, thread B will likely get a SQLITE_BUSY when he tries to UPDATE. He should sleep for awhile and try again. - Richard Klein Oops, didn't read the fine print: Threads A and B share a connection. I don't know what would happen in that case. - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Transactions on a shared database connection
John Firebaugh wrote: I take it this mean that if, say, thread A begins a transaction, and modifies the database, then thread B modifies the database, then thread A commits the transactions, B's modifications are also committed? Nope. Once thread A begins a transaction, no other thread (or process) can modify the database until thread A commits. (That's what "atomic transaction" means!) In the above scenario, thread B will likely get a SQLITE_BUSY when he tries to UPDATE. He should sleep for awhile and try again. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] lock counting?
Dan Kennedy wrote: From SQLite's point of view, locks are on a per file-handle basis. If a unix process that uses sqlite has two file-handles open on the same file (i.e. because the user has opened two separate connections with two calls to sqlite3_open()), SQLite expects them to be able to obtain and hold locks independently. A shared lock on one handle should prevent obtaining an exclusive lock on the other, just as it would if the two handles were opened by different processes. If a shared lock is obtained on both handles, then released on one, SQLite expects that the other handle still holds it's shared lock. That's what I thought. The above paragraph describes how sqlite expects the vfs implementation to work. Unix doesn't share this simple view of the world, so the implementation of sqlite3_vfs for unix has to jump through all sorts of hoops to implement it. The embedded OS for which I am implementing a VFS doesn't have any sort of file locking primitives. The downside of this is that I have to implement the locking mechanism from scratch. The upside is that I don't have any weird side effects to workaround, so I can make the locking work exactly the way it's supposed to. :-) So when I say you don't need to worry about reference counting, I mean if sqlite calls xLock() on a file-handle twice, then xUnlock() once, the file-handle should move into the "unlocked" state. That's *exactly* what I wanted to know. Thanks again, Dan and Joe! - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] lock counting?
Joe Wilson wrote:
--- Richard Klein <[EMAIL PROTECTED]> wrote:
In implementing xLock in a VFS, do we need to worry
about lock counts, i.e. nested locking?
In other words, if a process asks for, say, a SHARED
lock, and he already has one, should we increment a
SHARED lock count? Or is it okay to just return,
i.e. to treat the request as a no-op?
See comments for unixLock() and unixUnlock() in os_unix.c.
/*
** An instance of the following structure is allocated for each open
** inode on each thread with a different process ID. (Threads have
** different process IDs on linux, but not on most other unixes.)
**
** A single inode can have multiple file descriptors, so each unixFile
** structure contains a pointer to an instance of this object and this
** object keeps a count of the number of unixFile pointing to it.
*/
struct lockInfo {
struct lockKey key; /* The lookup key */
int cnt; /* Number of SHARED locks held */
int locktype;/* One of SHARED_LOCK, RESERVED_LOCK etc. */
int nRef;/* Number of pointers to this structure */
};
...
/* If a SHARED lock is requested, and some thread using this PID already
** has a SHARED or RESERVED lock, then increment reference counts and
** return SQLITE_OK.
*/
if( locktype==SHARED_LOCK &&
(pLock->locktype==SHARED_LOCK || pLock->locktype==RESERVED_LOCK) ){
assert( locktype==SHARED_LOCK );
assert( pFile->locktype==0 );
assert( pLock->cnt>0 );
pFile->locktype = SHARED_LOCK;
pLock->cnt++;
pFile->pOpen->nLock++;
goto end_lock;
}
I think you're referring to the 3rd & 4th lines from the bottom of this
code snippet:
pLock->cnt++;
pFile->pOpen->nLock++;
True, these lines increment lock counts, but not for the reason I was
worried about in my original post, i.e. not for the purpose of keeping
track of *nested* locks held by a single file descriptor.
The statement 'pLock->cnt++;' is merely keeping track of the number of
SHARED locks currently held on the file in question. This is important
information. For example, if the file is currently in the SHARED state
(pLock->locktype==SHARED_LOCK), and the last SHARED lock is released by
a file descriptor, we need to realize that the file is now UNLOCKED.
Or, suppose the file is SHARED and a file descriptor wants to acquire
an EXCLUSIVE lock on the file. Well, if there is only one SHARED lock
currently held on the file (pLock->cnt==1), and the file descriptor in
question is the one who holds it (pFile->locktype==SHARED_LOCK), then
there is no harm done in promoting that SHARED lock to EXCLUSIVE.
The statement 'pFile->pOpen->nLock++;' is incrementing the lock count
in the 'openCnt' struct for the database file in question:
/*
** An instance of the following structure is allocated for each open
** inode. This structure keeps track of the number of locks on that
** inode. If a close is attempted against an inode that is holding
** locks, the close is deferred until all locks clear by adding the
** file descriptor to be closed to the pending list.
*/
struct openCnt {
struct openKey key; /* The lookup key */
int nRef; /* Number of pointers to this structure */
int nLock;/* Number of outstanding locks */
int nPending; /* Number of pending close() operations */
int *aPending;/* Malloced space holding fd's awaiting a close() */
};
The 'openCnt' struct is necessary to work around another weirdness
of POSIX advisory locks:
** If you close a file descriptor that points to a file that has locks,
** all locks on that file that are owned by the current process are
** released. To work around this problem, each unixFile structure contains
** a pointer to an openCnt structure. There is one openCnt structure
** per open inode, which means that multiple unixFiles can point to a single
** openCnt. When an attempt is made to close a unixFile, if there are
** other unixFiles open on the same inode that are holding locks, the call
** to close() the file descriptor is deferred until all of the locks clear.
** The openCnt structure keeps a list of file descriptors that need to
** be closed and that list is walked (and cleared) when the last lock
** clears.
Cheers,
- Richard Klein
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] lock counting?
Dan Kennedy wrote: On 11/1/07, Richard Klein <[EMAIL PROTECTED]> wrote: In implementing xLock in a VFS, do we need to worry about lock counts, i.e. nested locking? No. You don't need to worry about that. In other words, if a process asks for, say, a SHARED lock, and he already has one, should we increment a SHARED lock count? Or is it okay to just return, i.e. to treat the request as a no-op? Locks are on a per-handle basis. If a handle already has an equal or greater lock to that requested, treat the request as a no-op. That's what I thought. Thanks! - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] lock counting?
In implementing xLock in a VFS, do we need to worry about lock counts, i.e. nested locking? In other words, if a process asks for, say, a SHARED lock, and he already has one, should we increment a SHARED lock count? Or is it okay to just return, i.e. to treat the request as a no-op? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
[EMAIL PROTECTED] wrote: But there are other operating systems using SQLite that do not work this way. They need a way to initialize mutexes (and possibly other objects such as malloc) prior to running any SQLite interface. And the initialization needs to be able to fail and return an error code. This is true of the Porter layer that we use on our set-top boxes. I had to add sqlite_initialize(...) and sqlite_finalize(...) functions when I ported SQLite 2 to our Porter. To accomodate this need, we are considering an incompatible API change to SQLite. We are thinking of requiring that an application invoke: int sqlite3_initialize(...); Sounds good to me. I would also recommend adding: int sqlite3_finalize(...); - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote: But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? No. That will happen in some specific unavoidable cases, but you cannot assume it will happen all of the time. I assume you are referring to the case discussed in the article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError i.e. the case where a non-COMMIT command within an explicit trans- action fails when the executing process is unable to spill its cache to disk due to a SQLITE_BUSY error, resulting in an inconsistent cache. What will most likely happen instead is that the first INSERT or UPDATE in a transaction will return SQLITE_BUSY, and you will have to roll back the transaction yourself. It's also possible to simply keep retrying a failing statement until it succeeds, but in the case of INSERT or UPDATE that may lead to deadlock, as two connections that want to write cannot proceed until one of them yields. Perhaps the best solution is to follow these rules: IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db): -- (1) Begin the transaction with 'BEGIN IMMEDIATE'. If you get SQLITE_ BUSY, it means that another writer is already accessing the db. Just sleep awhile and retry, as many times as necessary. Once you get SQLITE_OK, you're the only writer accessing the db. (2) If you get SQLITE_BUSY later in the transaction when you want to write your cache to disk, it can only be due to one or more readers holding SHARED locks (because you're the only writer). They will eventually exit without trying to acquire any stronger locks (again, because you're the only writer). Therefore, no deadlock is possible. Just sleep awhile and retry, as many times as necessary. IF THE TRANSACTION IS A READER (i.e. will only read from the db): - (1) Begin the transaction with a simple 'BEGIN'. This starts the transaction in the UNLOCKED state, so you *cannot* get a SQLITE_ BUSY at this point. (2) If you get SQLITE_BUSY later in the transaction, it can only be due to your trying to acquire a SHARED lock while some other process holds a PENDING or EXCLUSIVE lock. That process will eventually finish its writing and exit. Therefore, no deadlock is possible. Just sleep awhile and retry, as many times as necessary. If all transactions follow the above rules, then explicit rollbacks should never be necessary. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
But am I correct in assuming that one way that SQLite provides serializable transactions is by automatically rolling back transactions when necessary (and returning SQLITE_IOERR)? Thanks, - Richard Klein [EMAIL PROTECTED] wrote: Ken <[EMAIL PROTECTED]> wrote: BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; This is a comman and naive assumption that the balance selected will remain consistent. Actually, SQLite does provide this guarantee. Nothing in the database will change during a transaction, except for changes caused by INSERT, UPDATE, and DELETE statements that occur within the transaction itself. It is not possible for another process to modify the value of the "balance" in between the SELECT and the UPDATE in the SQL above. This is true of SQLite because isolation in SQLite is "SERIALIZABLE". This is the highest level of isolate provided by SQL. Most client/server database engines by default implement "READ COMMITTED". The value of "balance" might change between the SELECT and the UPDATE in MySQL, for example. (I'm less clear about what happens in PostgreSQL and Oracle. The point is that your mileage may vary so be cautious.) But SQLite gets this right. Transactions are fully serializable, which means they appear as if the entire transaction happens instanteously with no chance for outside processes to change values in the middle of a transaction. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: On 10/25/07, Richard Klein <[EMAIL PROTECTED]> wrote: Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly > complicated about that. It's not complicated at all. It's just an aspect of database programming that I was unaware of, until now (I'm a database newbie). You're also implementing SQLite's locking system for your own platform, so you need to be concerned with these details anyway. You're not just blindly implementing it, you're also verifying your understanding of the goals and taking the time to question SQLite's behavior to make sure everything is correct. I think that's absolutely wonderful. Thank you for understanding that! :-) - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Dan Kennedy wrote: On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Evaluating the UPDATE requires a RESERVED lock on the database file. Since only one process can hold the RESERVED lock, this particular scenario cannot occur. One or other of the updates will fail with SQLITE_BUSY. Dan. I went over the documentation again, and it appears that you are correct: A process wanting to do an UPDATE must first acquire a RESERVED lock. However, I believe there is still a race condition. Assume that process B is the first to reach the UPDATE statement, and so process B gets the RESERVED lock (which he later escalates to PENDING and EXCLUSIVE). Then process A is stuck in a busy wait until process B commits and releases his locks. At this process A acquires the RESERVED lock and does *her* UPDATE, but she does it using the old, now *stale*, value of the account balance ($10,000). That is, she computes the new balance as ($10,000 - $1,000) = $9,000, which is *wrong*. She needs to *re-execute* her SELECT statement to pick up the new balance of $11,000. Or, in general: She needs to start her transaction over from the beginning. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly > complicated about that. It's not complicated at all. It's just an aspect of database programming that I was unaware of, until now (I'm a database newbie). For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. That's true. If you begin your transaction with BEGIN EXCLUSIVE, then it cannot be interrupted by another transaction wishing to write to the database, so rollback won't be an issue. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Lee Crain wrote: I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. I think that's a perfectly acceptable solution, if you can live with the performance hit (and it sounds like you can). - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Race condition -- fixed?
As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to implement xLock()?
John Stanton wrote: Are you certain that you need to run a full suite of tests on Sqlite to verify that your compatibility layer is correct? How about developing a test suite for your porting layer If it works identically on each platform you can have confidence that your overlaid software will also work. We do have a test suite for our porting layer that we require to execute flawlessly on each platform. That's why I'm thinking that it would be sufficient to run the SQLite Tcl test scripts on our WIN32 platform only: A success- ful test run would mean that my implementation of the SQLite compatibility layer is correct on WIN32, and so is probably correct on *all* platforms, since the only difference between platforms is the porting layer, and that has already been independently verified. As I mentioned in a previous email, the above reasoning has been vindicated by practical experience: Whenever we find a difference in behavior between the WIN32 platform and platform 'X', the difference is almost invariably due to a bug in the porting layer for one of those two platforms. Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to implement xLock()?
Joe Wilson wrote: --- Richard Klein <[EMAIL PROTECTED]> wrote: I should be able to use your test suite s/your/the/ Sqlite and its test suite are drh's work. Hmm ... Somehow I got the impression that the team of SQLite core developers had expanded to include a few folks in addition to drh, and that you were one of them ... It might be possible to statically compile the Tcl+sqlite test harness (testfixture) down to under a meg to test on the embedded hardware. But I don't know how much RAM and temp store the test suite requires. The problem isn't the RAM -- the box has plenty of it (> 16 meg). The problem is porting Tcl to the box. I would *like* to do it, but I'm concerned that it would be a major effort -- much greater than the effort required to port SQLite. You could give it a shot. Your future cable customers deserve nothing less. Actually, they're *current* customers -- millions of them. New versions of our middleware are pushed out to their boxes in the middle of the night. So once we incorporate SQLite into our middleware, there will suddenly be millions of new SQLite users -- literally overnight. The good news is that I have already sold management on SQLite 2. Now I'm trying to upsell them to SQLite 3. Wish me luck! - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to implement xLock()?
Joe Wilson wrote: --- Richard Klein <[EMAIL PROTECTED]> wrote: Joe Wilson wrote: Just implement it and run the test suite. It's easier to respond to a specific problem than a hypothetical. Is the test suite available only in Tcl? Or is there a C version as well? Tcl. But you don't have to run the test suite on the embedded system necessarily. You can simulate it on a PC with the same code. Yes, I was just thinking the same thing. My company develops middleware for set-top boxes. Since we don't want to rewrite our middleware for every different brand of box (each with its own OS), we have specified our own box-independent OS layer, which we call the "Porter". The Porter, like SQLite's "os_x.c" layer, needs to be re-implemented for each different kind of box. I am implementing "os_porter.c", which will allow SQLite to run on top of Porter (and therefore on any box we support). Naturally, we have written a version of Porter that runs on Windows, so that we can develop, run, and debug our middleware on an ordinary PC. Since Tcl runs on Windows, I should be able to use your test suite to test my implement- ation of "os_porter.c" on a PC. It would be nice to be able to run the test suite on the various boxes, but I don't think that will be necessary. Experience has shown that when our middleware behaves differently on a box than on a PC, the difference is almost always due to a bug in the box's Porter implementation, and *not* to a bug in the middleware. Thanks Joe, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to implement xLock()?
Joe Wilson wrote: Just implement it and run the test suite. It's easier to respond to a specific problem than a hypothetical. Is the test suite available only in Tcl? Or is there a C version as well? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to implement xLock()?
Hello all, I am reposting the following question. Any input would be much appreciated, as it will help speed my port of SQLite to my company's embedded platform. (I am trying to sell my company on SQLite; if I succeed, every cable television customer in the UK will soon be using SQLite!) - I am porting SQLite 3.5.x to an embedded OS, and am trying to figure out how to implement xLock(), xUnlock(), and xCheck- ReservedLock(). My target OS doesn't have any file locking primitives, so I intend to implement the locking using a hash table of lockInfo structs, along with pLock and locktype fields in the file descriptor [similar to what unixLock() does]. The xLock() function has the following prototype: static int xLock(sqlite3_file *id, int locktype); where 'id' is a pointer to an open file descriptor belonging to a process (or thread) attached to the database file, and 'locktype' is the type of lock (SHARED, RESERVED, PENDING, or EXCLUSIVE) that the process would *like* to acquire. It is assumed that xLock() has some way of knowing and updating not only the current lock type held by the process, but also the current lock state of the database file. In my implementation of xLock(), as in unixLock(), the current lock type held by the process will be in id->locktype, and the current lock state of the file will be in id->pLock (a pointer to the lockInfo struct for that file). I am thinking of writing xLock() so that it handles *all possible* locking transitions requested by a process, regardless of whether or not they will actually occur in practice: UNLOCKED -> SHARED UNLOCKED -> RESERVED UNLOCKED -> PENDING UNLOCKED -> EXCLUSIVE SHARED -> RESERVED SHARED -> PENDING SHARED -> EXCLUSIVE RESERVED -> PENDING RESERVED -> EXCLUSIVE PENDING -> EXCLUSIVE For example, if a database file is UNLOCKED, and an attached process wants to go from UNLOCKED straight to EXCLUSIVE, it seems perfectly safe to let him do it, even though the doc- umentation states that a process wanting an EXCLUSIVE lock always acquires a SHARED lock first. In other words, I am thinking of coding xLock() so that it allows all legal transitions (and disallows all illegal ones), but has no knowledge of the pager's policies regarding trans- itions. That is, I want a strict separation between OS-level policies (what is legal from the point of view of the lock state defin- itions) and pager-level policies (what is actually done in practice). Does this seem like a reasonable idea? More importantly, will it work (not break SQLite)? Thanks, Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] winLock() in SQLITE 3.5.1...
Mark Spiegel wrote: While working on a VFS for use in 3.5.1, I was looking at the winLock() and have a question. Is it possible for the lock on a winFile object to progress from SHARED_LOCK to EXCLUSIVE_LOCK without first acquiring a RESERVED_LOCK? I have a similar question. I, too, am writing a VFS to port SQLite 3.5.x to an embedded OS, and am trying to figure out how to implement xLock(), xUnlock(), and xCheckReservedLock(). My target OS doesn't have any file locking primitives, so I intend to implement the locking using a hash table of fileLock nodes [similar to unixLock()] and a lockType field in the file descriptor. I am thinking of writing xLock() so that it handles *all possible* locking transitions, regardless of whether or not they will actually occur in practice: UNLOCKED -> SHARED UNLOCKED -> RESERVED UNLOCKED -> PENDING UNLOCKED -> EXCLUSIVE SHARED -> RESERVED SHARED -> PENDING SHARED -> EXCLUSIVE RESERVED -> PENDING RESERVED -> EXCLUSIVE PENDING -> EXCLUSIVE For example, if a database file is UNLOCKED, and an attached process wants to go from UNLOCKED straight to EXCLUSIVE, it seems perfectly safe to let him do it, even though the doc- umentation states that a process wanting an EXCLUSIVE lock always acquires a SHARED lock first. In other words, I am thinking of coding xLock() so that it allows all legal transitions (and disallows all illegal ones), but has no knowledge of the pager's policies regarding trans- itions. That is, I want a strict separation between OS-level policies (what is legal from the point of view of the lock state defin- itions) and pager-level policies (what is actually done in practice). Does this seem like a reasonable idea? More importantly, will it work (not break SQLite)? Thanks, Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite 3.x lock states
Here is my current understanding (by "process" I mean a process *or* thread that has opened the database): >> *snip* >> Correct. On the other hand, our hope as always been that you do not need to understand any of this in order to use SQLite. It should all just work. The only time you might to know this stuff is when you are trying to squeeze every last ounce of concurrency out of the system. -- D. Richard Hipp <[EMAIL PROTECTED]> ... or when you're porting sqlite 3 to an embedded OS, as I am, and you need to figure out how to implement xLock, xUnlock, and xCheckReservedLock!;-) Thanks, Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite 3.x lock states
Hello all, I'm upgrading from sqlite 2 to 3, and am trying to get a handle on the new lock states. Here is my current understanding (by "process" I mean a process *or* thread that has opened the database): A database is in the UNLOCKED state if every process holds *no* lock of any kind on the database. A database is in the SHARED state if every process holds either no lock or a SHARED lock. Processes holding a SHARED lock may read, but not write, the database. A database is in the RESERVED state if one of the processes has a RESERVED lock. Only *one* process at a time can hold a RESERVED lock. The process holding the RESERVED lock intends to write to the database file by the time its current transaction ends. Every other process must hold either no lock or a SHARED lock. A process holding no lock can acquire a SHARED lock, but nothing stronger than that. Processes holding SHARED locks can continue to read. A database is in the PENDING state if one of the processes has a PENDING lock. Only *one* process at a time can hold a PENDING lock. The process holding the PENDING lock intends to write to the database file as soon as all other processes drop their SHARED locks. Every other process must hold either no lock or a SHARED lock. A process holding no lock cannot acquire a lock of any kind. Processes holding SHARED locks can continue to read. A database is in the EXCLUSIVE state if one of the processes has an EXCLUSIVE lock. Only *one* process at a time can hold an EX- CLUSIVE lock. The process holding the EXCLUSIVE lock is currently writing to the database file. Every other process must hold *no* lock. No other process can acquire a lock of any kind. Do I have this right? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is RESERVED state for?
Hello all, I'm in the process of upgrading from SQLite 2 to SQLite 3, and am trying to understand the new lock states that version 3 introduced. I need this understanding because I will be porting SQLite 3 to an embedded OS that has no file locking API, and I don't want to use the "old-school" mechanism of having a .lock file (too slow). I have a couple of questions: (1) Can more than one process have a PENDING lock on a database? (I'm guessing that the answer is "no". My guess is that if one process has a PENDING lock on a database, then all the other processes that have opened that database have either no lock or a SHARED lock on the database. I'm further guessing that when all the SHARED locks eventually drop away, the PENDING lock is automatically promoted to EXCLUSIVE. Do I have this right?) (2) What is the purpose of the RESERVED lock? Since it doesn't seem to lead (directly or indirectly) to the PENDING or EXCLUSIVE state, and since it doesn't prevent other processes from acquiring SHARED locks, what is it good for? Thanks in advance, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Any more file format changes planned?
As you know, when the SQLite developers moved from version 2 to 3, they had to change the database file format (to implement the new features that version 3 offered). My question is: Can the developers envision any future feature additions that would require the file format to change again? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_exec function error:database is locked
varunkumar <[EMAIL PROTECTED]> wrote: so two different processes cannot access the database at a time One process cannot access the database at the same instant in time that another process is modifying the database. -- D. Richard Hipp <[EMAIL PROTECTED]> To clarify further: Process A and process B can both have the same database *open* at the same time. However, if process A tries to access (read or write) the database while process B is modifying (writing) it, then process A will get a SQLITE_BUSY error code returned to it. Process A should be prepared to handle this SQLITE_BUSY error. Typically he will want to sleep for a little while, and then try again. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SELECT crashes with small cache?
Joe Wilson wrote: --- Richard Klein <[EMAIL PROTECTED]> wrote: I am seeing SQLite crashing during execution of a SELECT statement when I make the page cache very small (40 pages). When I bump the cache up to 50 pages, the problem goes away. The problem only occurs on my RISC platform, not on my x86-based platform. Also, I am using SQLite 2 (not 3). I've never used the sqlite2 library, so I can't comment on that. But sqlite3 does a lot of checking in low memory situations and has very robust error handling and recovery. For example, SQLite3 is the only complex library I've seen that can survive malloc returning NULL for no memory. Actually, sqlite2 seems to manage quite nicely in low memory situations, at least on my x86-based platform: I was able to reduce MAX_PAGES to 1, and TEMP_PAGES to 0, and sqlite2 still seemed to run just fine. It's only on my SPARC-based platform that I have any problem, and that could be due to factors beyond sqlite2's control. For example, there could be a problem with the way we've redefined malloc() and free() on that platform. Without a decent debugger on that platform, it's difficult to track down the problem. However, I'm not going to worry about it right now; instead, I'm going to upgrade to sqlite3. If the problem still exists after I upgrade, then I'll worry it. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SELECT crashes with small cache?
Hello all, I am seeing SQLite crashing during execution of a SELECT statement when I make the page cache very small (40 pages). When I bump the cache up to 50 pages, the problem goes away. The problem only occurs on my RISC platform, not on my x86-based platform. Also, I am using SQLite 2 (not 3). My RISC platform prints out the stack trace in hex, not program symbols, so I can't yet tell you where in SQLite I'm crashing. (I'll try to get more info from the vendor on how to use their debugger.) Anyone ever see anything like this? Regards, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
