Re: [sqlite] DB managers that do searches?
On 30 Oct 2007 at 23:31, Kees Nuyt wrote: > [Default] On Tue, 30 Oct 2007 16:57:55 -0400, "Bernie Cosell" > <[EMAIL PROTECTED]> wrote: > > >I'm trying to correct several little problems in a DB I have and I've > >now tried three [maybe four] freeware DB managers and not one includes > >a search function. I have a table with 800+ rows and I'd like to find > >particular records so I can edit them, and not having a search is > >proving to be a real hassle. [I've been spoiled, I guess, about > >phpMyAdmin..:o)] > > There is phpSQLiteAdmin. ...You also may find sqlite3explorer (my > favorite) useful. > See: > http://www.sqlite.org/cvstrac/wiki?p=ManagementTools > for those two and many others. Ah, I'd actually gone through the wiki list, but I didn't go all the way to the bottom to the "Inactive or Unsupported" ones. I've mostly struck out with the 'top' tools, so I'll give those a shot. Thanks!! /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DB managers that do searches?
On 30 Oct 2007 at 14:05, James Dennett wrote: > > -Original Message- > > From: Bernie Cosell [mailto:[EMAIL PROTECTED] > > I'm trying to correct several little problems in a DB I have and I've > > now tried three [maybe four] freeware DB managers and not one includes > > a search function. > It would be very helpful if you'd define what you mean by "a search > function". What databases *do* is store and search Data; SQLite support > relational searches and full-text search, and other databases also allow > searching in various ways either for exact matches, LIKE clauses, > regexps, fuzzy search, natural language search, etc. I guess you've never used a [good] GUI-driven DB manager/administration pgm. The problem is that it is *hugely* easier to do a few clicks and "search" than to keep typing in 80-character SELECTs until you get the WHERE clause just-right, and then go back and edit the command to be an UPDATE (and don't mistype anything!). > I'm also wondering what you mean by a "DB manager" -- do you mean a > graphical user interface interfacing to an underlying database engine, > or something else? Yes. I'm not sure if you've had a chance to play with phpMyAdmin but it makes administering, taking care of, and tweaking MySQL DBs a real snap. The search function, for example, gives you a layout of the table and you can put in the search-values for the fields you care about and then you get a "minitable" of the search results [which are then easy to edit, delete, whatever]. /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB managers that do searches?
On 30 Oct 2007 at 14:27, Trevor Talbot wrote: > To help the conversation along a bit, phpMyAdmin is a web-based > administrative and design interface for MySQL. I don't use it so I > don't know exactly what kind of searching the OP is looking for > either, but at least we know the type of tool :) Just so -- I'm sorry about the confusion [I'd assumed, incorrectly, that my mention of phpMyAdmin would clarify what I was talking about]. Indeed: you can do *everything* from phpMyAdmin. Rename tables, add/delete columns, change column types [and names, of course], construct SQL statements graphically, etc, etc. The particular thing I was referring to was the search facility: you get a GUI presentation of the columns of the table and indicate which ones, and which search values, you care about and you get what we would call a 'view' of *just* those rows that match [so unlike from the command line, you don't even need to get a WHERE clause that matches _exactly_ -- if it just hits a few you just click the 'edit' icon on the one [or more] that you really care about]. I can't remember the last time I needed to do *anything* in terms of managing/editing/etc a MySQL database that required that I fire up the command line interface. /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Proposed sqlite3_initialize() interface
gcc support this, msvc++ and other compilers does not. -Original Message- From: Russell Leighton [mailto:[EMAIL PROTECTED] Sent: terça-feira, 30 de outubro de 2007 23:32 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed sqlite3_initialize() interface On Oct 30, 2007, at 10:18 AM, [EMAIL PROTECTED] wrote: > > 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(...); > I am not sure about the systems that you are trying to support, but for gnu tool chain you can do: gcc -shared -Wl,-init=sqlite3_initialize ... which will run the function at library load time and for static linking ( I think you can use this for dynamic linking too but I am not sure): __attribute__((constructor)) void sqlite3_initialize(void) So the init function would not need to be a public function and no API change would be needed (assuming the target platforms have similar capability). - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
On Oct 30, 2007, at 10:18 AM, [EMAIL PROTECTED] wrote: 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(...); I am not sure about the systems that you are trying to support, but for gnu tool chain you can do: gcc -shared -Wl,-init=sqlite3_initialize ... which will run the function at library load time and for static linking ( I think you can use this for dynamic linking too but I am not sure): __attribute__((constructor)) void sqlite3_initialize(void) So the init function would not need to be a public function and no API change would be needed (assuming the target platforms have similar capability). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB managers that do searches?
Bernie Cosell wrote: I'm trying to correct several little problems in a DB I have and I've now tried three [maybe four] freeware DB managers and not one includes a search function. Do you mean sorting a table on a text column, typing in a few characters, and then going to the row that matches the typed text? I have just been adding that capability to my Tcl-based SQLite handler, but it is not ready for prime time. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB managers that do searches?
[Default] On Tue, 30 Oct 2007 16:57:55 -0400, "Bernie Cosell" <[EMAIL PROTECTED]> wrote: >I'm trying to correct several little problems in a DB I have and I've >now tried three [maybe four] freeware DB managers and not one includes >a search function. I have a table with 800+ rows and I'd like to find >particular records so I can edit them, and not having a search is >proving to be a real hassle. [I've been spoiled, I guess, about >phpMyAdmin..:o)] There is phpSQLiteAdmin. Not as good as phpMyAdmin, but it might be just what you need. You also may find sqlite3explorer (my favorite) useful. Its result grid is quite powerful. See: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools for those two and many others. >Is there a freeware manager for XP that includes >searching capability? [If I were in long-term need of mgr stuff I'd >happily buy one of them [they're inexpensive enough], but I"m just >trying to correct typos in a DB that I recently converted to version 3] >Thanks! > /bernie\ HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve bound host parameters from statement?
On Tue, 30 Oct 2007 17:28:35 +0700, Dan Kennedy
<[EMAIL PROTECTED]> wrote:
>...
>Say you want to query statement object
>X that has 4 variables, you could do this:
>
> pTmp = sqlite3_prepare("SELECT ?, ?, ?, ?");
> sqlite3_transfer_bindings(X, pTmp);
> /* Use sqlite3_step() etc. to fish values out of pTmp */
> sqlite3_transfer_bindings(pTmp, X);
> sqlite3_finalize(pTmp);
>
>Dan.
Very smart indeed!
Would it matter much that sqlite3_transfer_bindings() is marked
obsolete in the docs, and, as a result, only documented in the
source?
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Proposed sqlite3_initialize() interface
On Tue, 30 Oct 2007 14:18:48 +, [EMAIL PROTECTED] wrote: >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(...); > >prior to using any other SQLite interface. In my environment SQLite is used mostly in batch via the command line tool, in PHP (via PDO), and using sqlite3explorer (a great SQLite GUI frontend by Mike Cariotoglou). Of course the command line tool gives no issues at all. Implementation of sqlite3_initialize in PHP might take quite some time, but as long as the dbfile format stays the same that won't be a problem, as we will be using the PHP-specific library version anyway. I hope Mike Cariotoglou is willing to update sqlite3explorer ;) The same probably goes for other less actively maintained frontends. -- ( Kees Nuyt ) c[_] - 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] Your Concurrency Idea
Dan, The journal file contains the "before" image of a modified database block not the after image. during write operations: before images are written to the journal file for recovery. Then the actual change is written to the .db file. A commit is an atomic operation due to unlinking the journal file. During recovery the journal file is played onto its corresponding .db file. This has the affect of a ROLLBACK command return the .db to a consistent state. So using the journal file to allow reads to continue is the correct place, given the current design of sqlite journalling and transactions. Dan Petitt <[EMAIL PROTECTED]> wrote: The journal file sounds like the wrong place to put it, the journal seems to be the place to store information that needs writing to the database on completion; storing some read-only information in the same file seems at odds with its current purpose. Perhaps a separate file(s) might be more appropriate to store this information, this might resolve your file format issues but also improve (but not eliminate) any performance considerations; perhaps the information could even (optionally) be stored in memory to greatly improve things. Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 30 October 2007 17:15 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Your Concurrency Idea "Dan Petitt" wrote: > Richard, i noticed this ticket in the system: > http://www.sqlite.org/cvstrac/tktview?tn=2417,8 > > And wondered if its something that is getting any serious thought or > something that is just a faint possibility? > Seems to be an incompatibly file format change, which more or less rules it out for any consideration. Also unaddressed in the proposal is how to locate a particular page within the journal file without having to do (performance killing) sequential scan of the possible very large file. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve bound host parameters from statement?
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> Joe Wilson wrote:
> > The transferred bindings are still opaque, aren't they?
> >
> > --- Dan Kennedy <[EMAIL PROTECTED]> wrote:
> >
> >> Depends how desperate you are. Say you want to query statement
> >> object X that has 4 variables, you could do this:
> >>
> >> pTmp = sqlite3_prepare("SELECT ?, ?, ?, ?");
> >> sqlite3_transfer_bindings(X, pTmp);
> >> /* Use sqlite3_step() etc. to fish values out of pTmp */
> >> sqlite3_transfer_bindings(pTmp, X);
> >> sqlite3_finalize(pTmp);
> >>
> >> Dan.
> >>
> Joe,
>
> Yes they are, but the ingenious part of Dan's idea is to transfer the
> bindings from one statement, say an insert statement, to a select
> statement. When you run the select it returns the values bound to the
> parameters as the result of the select. This lets you see the values
> that will be used if you run the first insert statement.
Thanks for clarifying that.
I originally thought he was populating another INSERT statement,
not a SELECT.
Clever.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Soft heap limit enforcement performance
--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > Is this memory pooling going to be compile-time optional? > > > > I find that library-specific memory pools are awkward because each > > library tends to have its own schemes that don't play well with each > > other. If you use pools, then that limits the effectiveness of Hoard > > or Boehm GC in a big application. > > There are no current plans to make this optional, since to do > so would instantly double the number of configurations I need > to support. Keeping malloc/free around as a compile-time option might be worth considering if only to be able to debug with Valgrind at the individual allocation level. Trying to debug programs using memory pools, such as the C++ standard template library can be challenging. Some STL implementations offer a malloc/free allocator as a debug alternative. > We have no plans to go to mmap or sbrk for memory. All of the > memory SQLite will manage will come from either a static array > (mem3.c) or from a few large mallocs (mem1.c and mem2.c). I > fail to see how this could adversely effect other libraries > within the same program. It just means that SQLite calls > malloc less often. When an allocator has an entire program's memory to deal with it might be able to optimize its allocations - either for space or speed. For example: http://www.hoard.org/ "...false sharing in your application: threads on different CPUs can end up with memory in the same cache line, or chunk of memory. Accessing these falsely-shared cache lines is hundreds of times slower than accessing unshared cache lines." __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve bound host parameters from statement?
Joe Wilson wrote:
The transferred bindings are still opaque, aren't they?
--- Dan Kennedy <[EMAIL PROTECTED]> wrote:
Depends how desperate you are. Say you want to query statement
object X that has 4 variables, you could do this:
pTmp = sqlite3_prepare("SELECT ?, ?, ?, ?");
sqlite3_transfer_bindings(X, pTmp);
/* Use sqlite3_step() etc. to fish values out of pTmp */
sqlite3_transfer_bindings(pTmp, X);
sqlite3_finalize(pTmp);
Dan.
Joe,
Yes they are, but the ingenious part of Dan's idea is to transfer the
bindings from one statement, say an insert statement, to a select
statement. When you run the select it returns the values bound to the
parameters as the result of the select. This lets you see the values
that will be used if you run the first insert statement.
I thought it was a brilliant example of thinking outside the box.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] DB managers that do searches?
To help the conversation along a bit, phpMyAdmin is a web-based administrative and design interface for MySQL. I don't use it so I don't know exactly what kind of searching the OP is looking for either, but at least we know the type of tool :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
I wrote: > On 10/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > On win32, we have to initialize mutexes at run-time, but this > > can be done within a contrived mutex that we build off of > > a static integer using InterlockedIncrement(). And mutex > > initialization apparently never fails on win32, so we do not > > have to worry with reporting errors that occur during > > mutex initialization. > > That isn't actually true, but handling that particular, rare > out-of-memory error condition that can occur with CriticalSections is > so amazingly inconvenient that most people don't even bother trying :) I realize how fragile that sounds, so to expand a bit: the default behavior is for the process to die immediately. It's sort of like getting a signal when you hit a ulimit on unix. There's no danger of silent failure and mysterious problems later. I'd consider it to be in roughly the same class as handling running out of stack during execution. Not a bug, just not something designed for. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DB managers that do searches?
Hi Bernie,
regarding: "...and not having a search is a real hassle"
I'm not at all sure I understand.
SQLITE (and all the DB managers I know of that incorporate it) supports
most of the SQL 92 language, which allows rather sophisticated searching
via the SELECT statement.
It's not normally the purpose of this list, but you might want to let us
know what criteria you wish to search for and I imagine someone can let
you know how to find those records.
Are you needed a REGEX ("regular expression") search perhaps?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] DB managers that do searches?
> -Original Message- > From: Bernie Cosell [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 30, 2007 1:58 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] DB managers that do searches? > > I'm trying to correct several little problems in a DB I have and I've > now tried three [maybe four] freeware DB managers and not one includes > a search function. I have a table with 800+ rows and I'd like to find > particular records so I can edit them, and not having a search is > proving to be a real hassle. [I've been spoiled, I guess, about > phpMyAdmin..:o)] Is there a freeware manager for XP that includes > searching capability? [If I were in long-term need of mgr stuff I'd > happily buy one of them [they're inexpensive enough], but I"m just > trying to correct typos in a DB that I recently converted to version 3] > Thanks! It would be very helpful if you'd define what you mean by "a search function". What databases *do* is store and search Data; SQLite support relational searches and full-text search, and other databases also allow searching in various ways either for exact matches, LIKE clauses, regexps, fuzzy search, natural language search, etc. I'm also wondering what you mean by a "DB manager" -- do you mean a graphical user interface interfacing to an underlying database engine, or something else? -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DB managers that do searches?
I'm trying to correct several little problems in a DB I have and I've now tried three [maybe four] freeware DB managers and not one includes a search function. I have a table with 800+ rows and I'd like to find particular records so I can edit them, and not having a search is proving to be a real hassle. [I've been spoiled, I guess, about phpMyAdmin..:o)] Is there a freeware manager for XP that includes searching capability? [If I were in long-term need of mgr stuff I'd happily buy one of them [they're inexpensive enough], but I"m just trying to correct typos in a DB that I recently converted to version 3] Thanks! /bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > --- [EMAIL PROTECTED] wrote: > > > Mostly I am interested in making sure that malloc(1000) does not > > > fail even though you have 5 bytes free and they just happen > > > to be scattered about as 100 discontinguous blocks of 500 bytes > > > each. > > > > It's a good goal. You can reduce the likelihood of failure perhaps, > > but I don't think that you can guarantee it without moving blocks > > and reswivelling all the pointers. > > So Joe's advice is give up and go home. Duely noted. But if it > is all the same to you, I think I will ignore this advice and > press onward... Not sure how you concluded that from what I wrote. I merely suggested that moving blocks of memory may be more effective in not fragmenting memory. You disagree. No problem. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
On 10/30/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > On win32, we have to initialize mutexes at run-time, but this > can be done within a contrived mutex that we build off of > a static integer using InterlockedIncrement(). And mutex > initialization apparently never fails on win32, so we do not > have to worry with reporting errors that occur during > mutex initialization. That isn't actually true, but handling that particular, rare out-of-memory error condition that can occur with CriticalSections is so amazingly inconvenient that most people don't even bother trying :) > 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. > > 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(...); > > prior to using any other SQLite interface. (The parameters to > sqlite3_initialize() are not yet designed.) It will be an error > to use any other SQLite interface without first invoking > sqlite3_initialize() exactly one. It is also an error to > invoke sqlite3_initialize() more than once. Some thoughts: * Definitely a major version change. * In order to correctly handle dynamic library scenarios, there needs to be a pair of initialize/finalize functions, and they need to be counted. The first intitialize() does the work, the rest simply increment a counter. The last finalize() does the work, the rest simply decrement. * For future flexibility, I'd suggest making one of the arguments to initialize() be a pointer to a struct. The first member of the struct should be a version indicator, like the VFS interface. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Your Concurrency Idea
The journal file sounds like the wrong place to put it, the journal seems to be the place to store information that needs writing to the database on completion; storing some read-only information in the same file seems at odds with its current purpose. Perhaps a separate file(s) might be more appropriate to store this information, this might resolve your file format issues but also improve (but not eliminate) any performance considerations; perhaps the information could even (optionally) be stored in memory to greatly improve things. Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 30 October 2007 17:15 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Your Concurrency Idea "Dan Petitt" <[EMAIL PROTECTED]> wrote: > Richard, i noticed this ticket in the system: > http://www.sqlite.org/cvstrac/tktview?tn=2417,8 > > And wondered if its something that is getting any serious thought or > something that is just a faint possibility? > Seems to be an incompatibly file format change, which more or less rules it out for any consideration. Also unaddressed in the proposal is how to locate a particular page within the journal file without having to do (performance killing) sequential scan of the possible very large file. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
At 19:41 30/10/2007, you wrote: Mostly I am interested in making sure that malloc(1000) does not fail even though you have 5 bytes free and they just happen to be scattered about as 100 discontinguous blocks of 500 bytes each. On the embebed device i worked (i made only the micro-os with sqlite) 2 years ago i "designed" a pseudo-handle. This worked with a maximum number of masters (255) and divide the memory pool in 8192 bytes size. Then a byte-map of 64KB (65536 blocks of 8192 bytes) so 64MB of memory pool with 0 for free and any other with the masters owner. Each block can be freed or be owned by any of the 255 masters. Of course it had internal fragmentation and minimal external, but the block size can change to any other value or the byte-map (but then a linear search for free or next blocks) and blocks can be moved for minimize external fragmentation. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Mostly I am interested in making sure that malloc(1000) does not > > fail even though you have 5 bytes free and they just happen > > to be scattered about as 100 discontinguous blocks of 500 bytes > > each. > > It's a good goal. You can reduce the likelihood of failure perhaps, > but I don't think that you can guarantee it without moving blocks > and reswivelling all the pointers. So Joe's advice is give up and go home. Duely noted. But if it is all the same to you, I think I will ignore this advice and press onward... > > Is this memory pooling going to be compile-time optional? > > I find that library-specific memory pools are awkward because each > library tends to have its own schemes that don't play well with each > other. If you use pools, then that limits the effectiveness of Hoard > or Boehm GC in a big application. > There are no current plans to make this optional, since to do so would instantly double the number of configurations I need to support. We have no plans to go to mmap or sbrk for memory. All of the memory SQLite will manage will come from either a static array (mem3.c) or from a few large mallocs (mem1.c and mem2.c). I fail to see how this could adversely effect other libraries within the same program. It just means that SQLite calls malloc less often. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: Re[4]: [sqlite] Proposed sqlite3_initialize() interface
[EMAIL PROTECTED] wrote: > > Hello Joe, > > Tuesday, October 30, 2007, 2:08:55 PM, you wrote: > > JW> --- Teg <[EMAIL PROTECTED]> wrote: > >> You'll just end up exchanging an "are you initialized" flag for a "have > >> you called the initialization routine" flag. I don't see it changing > >> the size or complexity. Either way, SQLite either has to ensure it's > >> initialized OR that someone has called the initialize function. It > >> end up being the same check. > >> > >> I'm not sure why this is ever required. Static initialization flag > >> that tells Sqlite to initialize itself internally or tells SQlite that > >> the user has initialized it. > > JW> The user does not need a flag to track sqlite initialization. > JW> He just calls sqlite3_initialize unconditionally before using > JW> sqlite the first time. Every application and dynamically loaded > JW> library has an entry point. > > JW> Although it would be a good idea to allow the sqlite3_initialize > JW> function to be called multiple times, as new dynamically loaded > JW> shared libraries would not know if another shared library is also > JW> making use of sqlite. > > I was speaking of internally, inside SQlite. I'm sure you expect to > get an error if you call into SQLite without calling the initialize > routine first. I wouldn't expect such a thing to be guaranteed. I'd expect that it might work, or it might crash, or it might give an error, or it might silently give incorrect results (though we'd like to avoid the last case), and that hopefully in a debug version an error would be produced. > Instead of returning an error, why not initialize right there instead? If a check was present, initializing would make sense. The question is whether checking at all entry points is sensible. It's not efficient, and might not be accepted for some platforms or uses. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[4]: [sqlite] Proposed sqlite3_initialize() interface
--- Teg <[EMAIL PROTECTED]> wrote: > I was speaking of internally, inside SQlite. I'm sure you expect to > get an error if you call into SQLite without calling the initialize > routine first. Instead of returning an error, why not initialize right there > instead? Because of the overhead in the multi-threaded case to safely initialize global data structures. These incorrect usage checks can be less safe because your program is coded incorrectly, and not crashing is just a bonus - a favor to you, if you will. You shouldn't rely on them. You should fix your code when you encounter such errors. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
--- [EMAIL PROTECTED] wrote: > Mostly I am interested in making sure that malloc(1000) does not > fail even though you have 5 bytes free and they just happen > to be scattered about as 100 discontinguous blocks of 500 bytes > each. It's a good goal. You can reduce the likelihood of failure perhaps, but I don't think that you can guarantee it without moving blocks and reswivelling all the pointers. Most of the better mallocs already perform the similar-size block pool optimization. > > Also, I'm not sure how many libc functions sqlite uses at this > > point. But some of them could malloc memory that is beyond the reach > > of your pools. Then there's the application's mallocs to consider > > as well. > > memcpy, memset, strlen. I think that is about the full set. > SQLite does not use libc very much, as that limits its portability > to embedded platforms. There's also the date functions localtime and gettimeofday. Perhaps they don't malloc in most implementations. > > Are you planning to keep allocations from different connections > > from different databases seperate? It would be nice to have > > unrelated databases on different threads not share a common memory > > pool which would help multi-threaded concurrency. > > Partially. My plan is to have a single global memory space that > all threads share. But each thread grabs big hunks of that space > for its own use on (relatively) infrequent occasions - or at least > with less frequency than mallocs currently occur. So the > synchronization overhead, while not zero, is reduced. Anything is better than 'the one big malloc lock' for concurrency. Is this memory pooling going to be compile-time optional? I find that library-specific memory pools are awkward because each library tends to have its own schemes that don't play well with each other. If you use pools, then that limits the effectiveness of Hoard or Boehm GC in a big application. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Your Concurrency Idea
DRH, I seemed to recall posting that ticket. I don't have any good solutions for the txn journal lookup at the moment. Maybe a form of block chaining from the sourcing DB to the txn journal offset. Each txn journal modification would point to the prior versions offset. So that multiple changes to a block within a transaction could be efficiently traversed within the TXN journal. Only the last change offset is needed to be maintained, ie a tail pointer. When a change is written to the journal first the current pending offset would need to be made to the block. Then the original version would be written. The on disk txn version (writer) would contain a reference to the location in the journal, Along with a global TXN change number from the Master block. Readers would determine that the on disk version does not match the Master Block version and would use the on disks journal offset to access the journal file. Locks on the journal file would need to be maintained indicating a shared read request so that the jouranl would not go away whilst the read is in progress. Just my blue sky idea of resolving read/write concurrency. At a minimum either the original block would need to be made accessible (this idea) or a mechanism to perform block versioning and block level rollback to a prior version would need to be developed in order to improve read/write concurrency. Block level rollback to a version can be accomplished but would be a major change in direction and philosophy. Regards, Ken [EMAIL PROTECTED] wrote: "Dan Petitt" wrote: > Richard, i noticed this ticket in the system: > http://www.sqlite.org/cvstrac/tktview?tn=2417,8 > > And wondered if its something that is getting any serious thought or > something that is just a faint possibility? > Seems to be an incompatibly file format change, which more or less rules it out for any consideration. Also unaddressed in the proposal is how to locate a particular page within the journal file without having to do (performance killing) sequential scan of the possible very large file. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[4]: [sqlite] Proposed sqlite3_initialize() interface
Hello Joe, Tuesday, October 30, 2007, 2:08:55 PM, you wrote: JW> --- Teg <[EMAIL PROTECTED]> wrote: >> You'll just end up exchanging an "are you initialized" flag for a "have >> you called the initialization routine" flag. I don't see it changing >> the size or complexity. Either way, SQLite either has to ensure it's >> initialized OR that someone has called the initialize function. It >> end up being the same check. >> >> I'm not sure why this is ever required. Static initialization flag >> that tells Sqlite to initialize itself internally or tells SQlite that >> the user has initialized it. JW> The user does not need a flag to track sqlite initialization. JW> He just calls sqlite3_initialize unconditionally before using JW> sqlite the first time. Every application and dynamically loaded JW> library has an entry point. JW> Although it would be a good idea to allow the sqlite3_initialize JW> function to be called multiple times, as new dynamically loaded JW> shared libraries would not know if another shared library is also JW> making use of sqlite. I was speaking of internally, inside SQlite. I'm sure you expect to get an error if you call into SQLite without calling the initialize routine first. Instead of returning an error, why not initialize right there instead? -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Proposed sqlite3_initialize() interface
Roger Binns wrote: > [EMAIL PROTECTED] wrote: > > It is also an error to > > invoke sqlite3_initialize() more than once. > > That is a pretty nasty restriction to have. If you link multiple other > libraries into your program, each of which also uses SQLite then you'd > somehow have to arrange that only one of them calls sqlite3_initialize > which is a serious pain. I concur: having a required initialization function is not too bad, but it is more elegant to only require that initialize/cleanup be called in nested pairs (or just to allow arbitrary numbers of calls to initialize if cleanup is a no-op). For my uses of SQLite3 from C++, it is trivial to guarantee that an initialization function is called prior to any other calls. It's not even very hard to add another check to ensure that it's called once only, but I can see *most* clients having to do the same, which is a strong argument for doing it in the library (even such an elegantly minimal library as SQLite3). -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > The only real way to prevent allocation fragmentation is to move > > > blocks of memory around - > > > > Not true. You can prevent fragmentation, for example, by > > not allocating objects beside each other that will be destroyed > > at different times. Or, you can pick a single allocation size > > and only do mallocs of exactly that size. The latter approach > > is what we are moving towards for SQLite. The allocation size > > would be the size of what Emery calls a "reap". If you deal > > with large strings and blobs you might need to allocate a chunk > > of memory larger than this, which destroys your fragmentation > > guarantees. But at least you can write testable requirements > > about when you guarantee that fragmentation will not occur. > > Maybe the nomenclature is confusing me, but you are not *preventing* > memory fragmentation with your proposed scheme, but limiting its > effects. Mostly I am interested in making sure that malloc(1000) does not fail even though you have 5 bytes free and they just happen to be scattered about as 100 discontinguous blocks of 500 bytes each. > By rounding up memory allocations you will inevitably > still have some unused dead memory areas. It's not fragmentation, > per se, but wasted space nonetheless. Memory lifetime analysis is > great but you have to be very vigilant in your code. > > If you only use small database fields perhaps you can get some sort > of limited memory fragmentation guarantee, but when you have large > blobs and strings that require contiguous memory, as you point out, > all bets are off. Correct. For really large strings and blobs, you can use the new incremental I/O mechanism, though, and still avoid using large contiguous blocks of memory. That is more work for the program, but if you are writing an application where this kind of thing is important, that is what you have to do. > > Also, I'm not sure how many libc functions sqlite uses at this > point. But some of them could malloc memory that is beyond the reach > of your pools. Then there's the application's mallocs to consider > as well. memcpy, memset, strlen. I think that is about the full set. SQLite does not use libc very much, as that limits its portability to embedded platforms. > > But the ultimate test is comparing the total memory arena size > against how many real bytes are allocated and in use. If your > library can increase the percentage of heap used more than a > generic malloc like Lea's, then it will be useful. There is generally a space v. speed tradeoff. Allocators that make more efficient use of memory are slower than those that waste a lot of memory. It is unclear at this point what the best tradeoff will be for SQLite. Probably it will vary from one application to another, suggesting that it should be tunable. > > Are you planning to keep allocations from different connections > from different databases seperate? It would be nice to have > unrelated databases on different threads not share a common memory > pool which would help multi-threaded concurrency. > Partially. My plan is to have a single global memory space that all threads share. But each thread grabs big hunks of that space for its own use on (relatively) infrequent occasions - or at least with less frequency than mallocs currently occur. So the synchronization overhead, while not zero, is reduced. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
[EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: The only real way to prevent allocation fragmentation is to move blocks of memory around - Not true. You can prevent fragmentation, for example, by not allocating objects beside each other that will be destroyed at different times. Or, you can pick a single allocation size and only do mallocs of exactly that size. The latter approach is what we are moving towards for SQLite. The allocation size would be the size of what Emery calls a "reap". If you deal with large strings and blobs you might need to allocate a chunk of memory larger than this, which destroys your fragmentation guarantees. But at least you can write testable requirements about when you guarantee that fragmentation will not occur. You are correct that avoiding fragmentation is very difficult. But is also very important for some users and it is thus something we want to be able to provide. -- D. Richard Hipp <[EMAIL PROTECTED]> Great foresight. Programs which should run unattended but fail from time to time by running out of heap are a nuisance. We had some success by allocating memory in fixed size chunks which have optional smaller chunks internally. Fragmentation of the checker board type is avoided at the expense of sub-optimal packing. The large chunk is sized to fit the biggest dynamically allocated object. The chunks are contiguous. On portable software which cannot tolerate fragmentation and has a limited number of dynamically allocated types avoiding free and using a free list for each type is very robust. Lurking problems caused by squirrelly mallocs are avoided. After running for a few months a high water point in memory is established and memory usage does not creep thereafter. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Proposed sqlite3_initialize() interface
--- Teg <[EMAIL PROTECTED]> wrote: > You'll just end up exchanging an "are you initialized" flag for a "have > you called the initialization routine" flag. I don't see it changing > the size or complexity. Either way, SQLite either has to ensure it's > initialized OR that someone has called the initialize function. It > end up being the same check. > > I'm not sure why this is ever required. Static initialization flag > that tells Sqlite to initialize itself internally or tells SQlite that > the user has initialized it. The user does not need a flag to track sqlite initialization. He just calls sqlite3_initialize unconditionally before using sqlite the first time. Every application and dynamically loaded library has an entry point. Although it would be a good idea to allow the sqlite3_initialize function to be called multiple times, as new dynamically loaded shared libraries would not know if another shared library is also making use of sqlite. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > The only real way to prevent allocation fragmentation is to move > > blocks of memory around - > > Not true. You can prevent fragmentation, for example, by > not allocating objects beside each other that will be destroyed > at different times. Or, you can pick a single allocation size > and only do mallocs of exactly that size. The latter approach > is what we are moving towards for SQLite. The allocation size > would be the size of what Emery calls a "reap". If you deal > with large strings and blobs you might need to allocate a chunk > of memory larger than this, which destroys your fragmentation > guarantees. But at least you can write testable requirements > about when you guarantee that fragmentation will not occur. Maybe the nomenclature is confusing me, but you are not *preventing* memory fragmentation with your proposed scheme, but limiting its effects. By rounding up memory allocations you will inevitably still have some unused dead memory areas. It's not fragmentation, per se, but wasted space nonetheless. Memory lifetime analysis is great but you have to be very vigilant in your code. If you only use small database fields perhaps you can get some sort of limited memory fragmentation guarantee, but when you have large blobs and strings that require contiguous memory, as you point out, all bets are off. Also, I'm not sure how many libc functions sqlite uses at this point. But some of them could malloc memory that is beyond the reach of your pools. Then there's the application's mallocs to consider as well. But the ultimate test is comparing the total memory arena size against how many real bytes are allocated and in use. If your library can increase the percentage of heap used more than a generic malloc like Lea's, then it will be useful. Are you planning to keep allocations from different connections from different databases seperate? It would be nice to have unrelated databases on different threads not share a common memory pool which would help multi-threaded concurrency. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Your Concurrency Idea
"Dan Petitt" <[EMAIL PROTECTED]> wrote: > Richard, i noticed this ticket in the system: > http://www.sqlite.org/cvstrac/tktview?tn=2417,8 > > And wondered if its something that is getting any serious thought or > something that is just a faint possibility? > Seems to be an incompatibly file format change, which more or less rules it out for any consideration. Also unaddressed in the proposal is how to locate a particular page within the journal file without having to do (performance killing) sequential scan of the possible very large file. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
Dr. Hipp, On the fly initialization is a big concern for me because I have the misfortune to live in a massively multi-threaded environment. So I am very much in favor of this change. I see that there are already some other proposals out there, but would urge you to make the interface change in the manner described because it maintains clarity. A single thread must initialize the sqlite module before any other operations are allowed. (You can enforce that with debug code.) I would also add one suggestion. Add a sqlite3_deinitialize() call as well. This function would be called after all other calls have completed and there are no more resources in use. While it may be a noop at this time, it may not be at some point. It is also a good place for debug code to ensure that all resources have been released. Mark Spiegel [EMAIL PROTECTED] wrote: As currently implemented, SQLite3 requires no initialization. You just start calling SQLite3 interfaces and they work. We can pull off this trick on Unix because pthread mutexes can be initialized statically at compile-time. static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER; On win32, we have to initialize mutexes at run-time, but this can be done within a contrived mutex that we build off of a static integer using InterlockedIncrement(). And mutex initialization apparently never fails on win32, so we do not have to worry with reporting errors that occur during mutex initialization. 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. 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(...); prior to using any other SQLite interface. (The parameters to sqlite3_initialize() are not yet designed.) It will be an error to use any other SQLite interface without first invoking sqlite3_initialize() exactly one. It is also an error to invoke sqlite3_initialize() more than once. Existing applications that use SQLite would have to be modified to invoke sqlite3_initialize(). Presumably this would happen very early in main(), before any threads were created. No other code changes would be required. This is still just an idea. If you think that adding a new required sqlite3_initialize() interface would cause serious hardship for your use of SQLite, please speak up now. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Proposed sqlite3_initialize() interface
Hello Joe, Tuesday, October 30, 2007, 12:01:37 PM, you wrote: JW> I think the proposed sqlite3_initialize() is a good idea and the JW> library might be a bit smaller/faster as well due to removal of JW> initialization checks in various functions. JW> Any concern about operating systems that already ship with a shared JW> sqlite3 library? Or is that what shared library version numbers JW> are for? JW> --- [EMAIL PROTECTED] wrote: JW> __ JW> Do You Yahoo!? JW> Tired of spam? Yahoo! Mail has the best spam protection around JW> http://mail.yahoo.com JW> - JW> To unsubscribe, send email to [EMAIL PROTECTED] JW> - You'll just end up exchanging an "are you initialized" flag for a "have you called the initialization routine" flag. I don't see it changing the size or complexity. Either way, SQLite either has to ensure it's initialized OR that someone has called the initialize function. It end up being the same check. I'm not sure why this is ever required. Static initialization flag that tells Sqlite to initialize itself internally or tells SQlite that the user has initialized it. C -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Your Concurrency Idea
Richard, i noticed this ticket in the system: http://www.sqlite.org/cvstrac/tktview?tn=2417,8 And wondered if its something that is getting any serious thought or something that is just a faint possibility? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
On Oct 30, 2007 7:18 AM, <[EMAIL PROTECTED]> wrote: > This is still just an idea. If you think that adding a new > required sqlite3_initialize() interface would cause serious > hardship for your use of SQLite, please speak up now. I think this would cause some hardship for dynamically-loaded libraries which are using SQLite and which are multi-threaded. I don't think it's a SERIOUS hardship, because you can usually find (or annoint) some special place in your code to make the call (though that assumption might fail given a broad enough set of operating environments). If sqlite_initialize() needed to be called once per process, it would be handy if it could be called from any thread, and if it could safely be called multiple times. Alternately, it might be reasonable to require it to be called once per thread before anything else in that thread calls into SQLite. It would seem reasonable to me to have SQLite handle this initialization at appropriate entry points, and to add something like SQLITE_OMIT_AUTO_INITIALIZE for those users who can't afford the overhead. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
"Dan Petitt" <[EMAIL PROTECTED]> wrote: > > Alternatively, you don't actually need the interface for > > 99.99% of users out there (Windows, Linux, Mac) so you > > could make it unnecessary for them, but do require it for the > > various esoteric embedded systems. That would justify still > > calling it SQLite version 3. > > That was my first thought, just require it for the OS's that need it; all > other systems are unchanged and work as before. > Having sqlite3_initialize() is only *required* on some obscure systems. But it would certainly be *helpful* on Linux and Win32. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
Joe Wilson <[EMAIL PROTECTED]> wrote: > The only real way to prevent allocation fragmentation is to move > blocks of memory around - Not true. You can prevent fragmentation, for example, by not allocating objects beside each other that will be destroyed at different times. Or, you can pick a single allocation size and only do mallocs of exactly that size. The latter approach is what we are moving towards for SQLite. The allocation size would be the size of what Emery calls a "reap". If you deal with large strings and blobs you might need to allocate a chunk of memory larger than this, which destroys your fragmentation guarantees. But at least you can write testable requirements about when you guarantee that fragmentation will not occur. You are correct that avoiding fragmentation is very difficult. But is also very important for some users and it is thus something we want to be able to provide. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
--- Marco Bambini <[EMAIL PROTECTED]> wrote: > I think that sqlite3_initialize should be allowed to be called more > than once. > With the help of a static flag, only the first time it is executed > the proper initialize functions will be invoked, successive calls to > the sqlite3_initialize should just be a NOP operation... In a single-threaded program, perhaps. But in a multi-threaded program beware the Double Checked Lock: http://en.wikipedia.org/wiki/Double-checked_locking http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
I would endorse the use of an initialization functions as being clean and efficient and one of the simplest and most logical of optimizations, eliminating common expressions. Since your typical application program has an initialization phase it is trivial to add the new API function to legacy applications. Joe Wilson wrote: I think the proposed sqlite3_initialize() is a good idea and the library might be a bit smaller/faster as well due to removal of initialization checks in various functions. Any concern about operating systems that already ship with a shared sqlite3 library? Or is that what shared library version numbers are for? --- [EMAIL PROTECTED] wrote: "Robert Simpson" <[EMAIL PROTECTED]> wrote: Is there a reason this can't be checked/done in sqlit3_open() via an InterlockedCompareExchange() operation on the static integer, and if the mutexes don't exist and can't be created, you just return a different error code? That's the other option. Though we would have to do this on multiple interfaces. Here is a list (complete, I think) off all SQLite interfaces that can be called "first" and would thus need to have the test you propose: sqlite3_auto_extension sqlite3_complete sqlite3_complete16 sqlite3_enable_load_extension sqlite3_enable_shared_cache sqlite3_global_recover sqlite3_libversion sqlite3_libversion_number sqlite3_load_extension sqlite3_malloc sqlite3_memory_alarm sqlite3_memory_highwater sqlite3_memory_used sqlite3_mprintf sqlite3_mutex_alloc sqlite3_open sqlite3_open16 sqlite3_open_v2 sqlite3_realloc sqlite3_release_memory sqlite3_reset_auto_extension sqlite3_sleep sqlite3_snprintf sqlite3_soft_heap_limit sqlite3_thread_cleanup sqlite3_threadsafe sqlite3_vfs_find sqlite3_vfs_register sqlite3_vfs_unregister sqlite3_vmprintf -- D. Richard Hipp <[EMAIL PROTECTED]> __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve bound host parameters from statement?
The transferred bindings are still opaque, aren't they?
--- Dan Kennedy <[EMAIL PROTECTED]> wrote:
> Depends how desperate you are. Say you want to query statement
> object X that has 4 variables, you could do this:
>
> pTmp = sqlite3_prepare("SELECT ?, ?, ?, ?");
> sqlite3_transfer_bindings(X, pTmp);
> /* Use sqlite3_step() etc. to fish values out of pTmp */
> sqlite3_transfer_bindings(pTmp, X);
> sqlite3_finalize(pTmp);
>
> Dan.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Proposed sqlite3_initialize() interface
> Alternatively, you don't actually need the interface for 99.99% of users out there (Windows, Linux, Mac) > so you could make it unnecessary for them, but do require it for the various esoteric embedded systems. > That would justify still calling it SQLite version 3. That was my first thought, just require it for the OS's that need it; all other systems are unchanged and work as before. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
I think that sqlite3_initialize should be allowed to be called more than once. With the help of a static flag, only the first time it is executed the proper initialize functions will be invoked, successive calls to the sqlite3_initialize should just be a NOP operation... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 30, 2007, at 5:14 PM, Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: It is also an error to invoke sqlite3_initialize() more than once. That is a pretty nasty restriction to have. If you link multiple other libraries into your program, each of which also uses SQLite then you'd somehow have to arrange that only one of them calls sqlite3_initialize which is a serious pain. (The wxPython gui library used to have a similar issue when initializing things like cursors and colours and caused endless grief before it was fixed to allow multiple calls). In any event this is a very serious API change and really does qualify for calling it SQLite 4. Alternatively, you don't actually need the interface for 99.99% of users out there (Windows, Linux, Mac) so you could make it unnecessary for them, but do require it for the various esoteric embedded systems. That would justify still calling it SQLite version 3. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHJ1hXmOOfHg372QQRAjCHAKCdH4R/OQNY3ALUli9nRCmbFeyDfACeIHcY 7irdFT/ofCgoNK0jERTjze8= =yB1W -END PGP SIGNATURE- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
The only real way to prevent allocation fragmentation is to move blocks of memory around - i.e., return and manilpulate handles to pointers instead of the pointers themselves. But this adds a lot of runtime overhead and is not C friendly. Anything else is just a compromise. Predictive and statistical schemes only go so far. You still get fragmentation. Maybe if you had special logic for moving and compacting the db page cache memory, that might be sufficient. Anyway, it's interesting stuff. I'm curious as to what solution you'll have. --- [EMAIL PROTECTED] wrote: > I have not read it yet, but a quick scan shows that Emery > completely overlooks one of the key reason I am experiementing > with memory pools: provable correctness. General purpose > allocator, such as Doug Lea's, do an excellent job of > preventing and dealing with memory fragmentation. But > they do not (can not) guarantee that memory will never > fragment. We are working on techniques that will guarantee > that the heap will not fragment. And in order to achieve > that, we need very low-level control of the memory allocation. > Hence my recent interest in memory pools. > > There is also quite a bit of interest in this research > from people using SQLite in embedded machines with bad > malloc() implementations (and, I am told, compelling > reasons why they cannot just substitute a better malloc.) > > Emery's observation that memory pools will not magically > cure the performance problems of a legacy application is > quite correct. You cannot just take any old application > that uses malloc, stick memory pools underneath it, and > expect it to work well. Hence, we are also reworking the > upper layers of SQLite to know that they are using memory > pools and to use those pools effectively. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
Marco Bambini <[EMAIL PROTECTED]> wrote: > > I am experiencing a very strange issue in sqlite 3.4.2 (only with the > Win32 version, OSX and linux works fine). > > I wonder if there was a bug in the 3.4.2 version that I should fix... > Please note that I cannot upgrade to the latest 3.5.x versions... > What makes you think the bug is in SQLite and not in your language interface wrapper? Do you still get the wrong answer if you run the same queries from the CLI? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: > It is also an error to > invoke sqlite3_initialize() more than once. That is a pretty nasty restriction to have. If you link multiple other libraries into your program, each of which also uses SQLite then you'd somehow have to arrange that only one of them calls sqlite3_initialize which is a serious pain. (The wxPython gui library used to have a similar issue when initializing things like cursors and colours and caused endless grief before it was fixed to allow multiple calls). In any event this is a very serious API change and really does qualify for calling it SQLite 4. Alternatively, you don't actually need the interface for 99.99% of users out there (Windows, Linux, Mac) so you could make it unnecessary for them, but do require it for the various esoteric embedded systems. That would justify still calling it SQLite version 3. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHJ1hXmOOfHg372QQRAjCHAKCdH4R/OQNY3ALUli9nRCmbFeyDfACeIHcY 7irdFT/ofCgoNK0jERTjze8= =yB1W -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
On Oct 30, 2007, at 7:18 AM, [EMAIL PROTECTED] wrote: As currently implemented, SQLite3 requires no initialization. You just start calling SQLite3 interfaces and they work. We can pull off this trick on Unix because pthread mutexes can be initialized statically at compile-time. static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER; On win32, we have to initialize mutexes at run-time, but this can be done within a contrived mutex that we build off of a static integer using InterlockedIncrement(). And mutex initialization apparently never fails on win32, so we do not have to worry with reporting errors that occur during mutex initialization. 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. 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(...); prior to using any other SQLite interface. (The parameters to sqlite3_initialize() are not yet designed.) It will be an error to use any other SQLite interface without first invoking sqlite3_initialize() exactly one. It is also an error to invoke sqlite3_initialize() more than once. Existing applications that use SQLite would have to be modified to invoke sqlite3_initialize(). Presumably this would happen very early in main(), before any threads were created. No other code changes would be required. I assume (hope) you mean threads that call into sqlite3. We have a situation where 'sqlite3_initialize' would be called far away from any 'main' - sqlite3 is a runtime loaded extension for the Chicken Scheme system. There isn't any guarantee when the extension is loaded, and therefore when the initialization is done. I doubt any problems but just a heads up that sqlite3 is used in a manner differing from the above scenario by languages such as Scheme, Io, Lua, OCaml, etc. This is still just an idea. If you think that adding a new required sqlite3_initialize() interface would cause serious hardship for your use of SQLite, please speak up now. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- Best Wishes, Kon - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Strange error in sqlite 3.4.2 Win32 version
Hi guys,
I am experiencing a very strange issue in sqlite 3.4.2 (only with the
Win32 version, OSX and linux works fine).
Here it is what's happen:
// create table
CREATE TABLE One( a varchar primary key, b1 integer, b2 integer, b3
integer, z varchar )
CREATE UNIQUE INDEX idx_One ON One( b1, b2, b3 )
// insert 100 rows
// pseudo code
for i as integer = 1 to 100
db.SQLExecute( "INSERT INTO One VALUES( '" + Str(i) + "', " + Str
(i+1) + ", " + Str(i+2) + ", " + Str(i+3) + ", '" + Chr(i +Asc("A"))
+ "' )" )
next
// select case 1
// Getting one column, not in index, FAILS!
rs = db.SQLSelect( "SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
the return value should be 98, but it is 99!
// select case 2
// Getting all columns works
rs = db.SQLSelect( "SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
// select case 3
// Not using whole index works
rs = db.SQLSelect( "SELECT a FROM One WHERE b2 = 100 and b3 = 101" )
// select case 4
// Getting one column, in the index, works
rs = db.SQLSelect( "SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
I wonder if there was a bug in the 3.4.2 version that I should fix...
Please note that I cannot upgrade to the latest 3.5.x versions...
Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Proposed sqlite3_initialize() interface
I think the proposed sqlite3_initialize() is a good idea and the library might be a bit smaller/faster as well due to removal of initialization checks in various functions. Any concern about operating systems that already ship with a shared sqlite3 library? Or is that what shared library version numbers are for? --- [EMAIL PROTECTED] wrote: > "Robert Simpson" <[EMAIL PROTECTED]> wrote: > > > > Is there a reason this can't be checked/done in sqlit3_open() via an > > InterlockedCompareExchange() operation on the static integer, and if the > > mutexes don't exist and can't be created, you just return a different error > > code? > > > > That's the other option. Though we would have to do this on > multiple interfaces. Here is a list (complete, I think) off > all SQLite interfaces that can be called "first" and would > thus need to have the test you propose: > > sqlite3_auto_extension > sqlite3_complete > sqlite3_complete16 > sqlite3_enable_load_extension > sqlite3_enable_shared_cache > sqlite3_global_recover > sqlite3_libversion > sqlite3_libversion_number > sqlite3_load_extension > sqlite3_malloc > sqlite3_memory_alarm > sqlite3_memory_highwater > sqlite3_memory_used > sqlite3_mprintf > sqlite3_mutex_alloc > sqlite3_open > sqlite3_open16 > sqlite3_open_v2 > sqlite3_realloc > sqlite3_release_memory > sqlite3_reset_auto_extension > sqlite3_sleep > sqlite3_snprintf > sqlite3_soft_heap_limit > sqlite3_thread_cleanup > sqlite3_threadsafe > sqlite3_vfs_find > sqlite3_vfs_register > sqlite3_vfs_unregister > sqlite3_vmprintf > > -- > D. Richard Hipp <[EMAIL PROTECTED]> __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
Joe Wilson <[EMAIL PROTECTED]> wrote: > Hi Richard, > > This might be worth a read. This paper discusses limitations of custom > memory allocators: > > Reconsidering Custom Memory Allocation > http://www.cs.umass.edu/~emery/pubs/berger-oopsla2002.pdf > Interesting paper. Thanks for the link. I have not read it yet, but a quick scan shows that Emery completely overlooks one of the key reason I am experiementing with memory pools: provable correctness. General purpose allocator, such as Doug Lea's, do an excellent job of preventing and dealing with memory fragmentation. But they do not (can not) guarantee that memory will never fragment. We are working on techniques that will guarantee that the heap will not fragment. And in order to achieve that, we need very low-level control of the memory allocation. Hence my recent interest in memory pools. There is also quite a bit of interest in this research from people using SQLite in embedded machines with bad malloc() implementations (and, I am told, compelling reasons why they cannot just substitute a better malloc.) Emery's observation that memory pools will not magically cure the performance problems of a legacy application is quite correct. You cannot just take any old application that uses malloc, stick memory pools underneath it, and expect it to work well. Hence, we are also reworking the upper layers of SQLite to know that they are using memory pools and to use those pools effectively. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] libsqlite3.so.0 not found
> $ ldconfig -v > search for libsqlite3.so.0 in the list.. is it available Thanks. It's in /usr/local/lib, and the sqlite3 interface is working now. It didn't create the .db file when I exited though. Is it supposed to create an empty file, or do I have to add data first? > are you running a 64bit OS version? no --Dan Bensen www.prairienet.org/~dsb/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft heap limit enforcement performance
Hi Richard, This might be worth a read. This paper discusses limitations of custom memory allocators: Reconsidering Custom Memory Allocation http://www.cs.umass.edu/~emery/pubs/berger-oopsla2002.pdf This post by Emery Berger outlines the problems with Apache Portable Runtime (APR) memory pools specifically: http://apache.slashdot.org/comments.pl?sid=120623=1=0=thread=10160124 Emery Berger's Hoard memory allocator is widely used in multi-threaded programs to improve concurrency. I've used it and the speedups are quite remarkable. In one case, a multi-threaded server throughput was 4X faster on a 8 CPU box - just by changing the malloc implementation to Hoard. Mind you, programs with coarse-grained locks will not see that sort of performance increase. As far as general compactness of memory allocation goes, I've had great success with Doug Lea's malloc implementation. Highly recommended. http://gee.cs.oswego.edu/dl/html/malloc.html http://gee.cs.oswego.edu/pub/misc/malloc.c http://gee.cs.oswego.edu/pub/misc/malloc.h http://g.oswego.edu/ --- [EMAIL PROTECTED] wrote: > We are actively working on the memory management problem in a > fork of the source tree. See > >http://www.sqlite.org/mpool/fossil > > The focus of our current research is in reducing memory fragmentation, > but this is very much related to limiting memory usage. Assuming we > achieve good results, the experimental fork will be folded back into > the CVS tree at some point. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory Management
Ken <[EMAIL PROTECTED]> wrote: > DRH, > > I looked at the memory management code mem3.c. I noticed a > mutex to protect allocations/deallocations and was wondering > what the impact on performance to a multi threaded DB > applicaiton would be? I guess that depends on your mutex implementation. Or, you could heed my earnest pleas for sanity and *not use threads*. The mutex calls become no-op macros if you compile with -DSQLITE_THREADSAFE=0. Note that mem1.c and mem2.c also have to lock every memory allocation. And every threadsafe implementation of malloc() I've seen locks as well. (How else do you propose to handle a global resource?) > > Other memory allcation schemes seem to allow each thread to > manage memory independently of the other threads. In other > words, the thread would have a hook into its own memory pool > and would not be blocked by any other threads memory requests. > > Some interesting memory managers include: Unfortunately the > first has licensing requirements and the second is copyrighted, > but available for Free. > > http://www.cs.umass.edu/~emery/hoard/screenshot.html > > http://www.garret.ru/~knizhnik/threadalloc/readme.html > We are working on this capability in the mpool fork mentioned in an earlier email today. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
"Robert Simpson" <[EMAIL PROTECTED]> wrote: > > Is there a reason this can't be checked/done in sqlit3_open() via an > InterlockedCompareExchange() operation on the static integer, and if the > mutexes don't exist and can't be created, you just return a different error > code? > That's the other option. Though we would have to do this on multiple interfaces. Here is a list (complete, I think) off all SQLite interfaces that can be called "first" and would thus need to have the test you propose: sqlite3_auto_extension sqlite3_complete sqlite3_complete16 sqlite3_enable_load_extension sqlite3_enable_shared_cache sqlite3_global_recover sqlite3_libversion sqlite3_libversion_number sqlite3_load_extension sqlite3_malloc sqlite3_memory_alarm sqlite3_memory_highwater sqlite3_memory_used sqlite3_mprintf sqlite3_mutex_alloc sqlite3_open sqlite3_open16 sqlite3_open_v2 sqlite3_realloc sqlite3_release_memory sqlite3_reset_auto_extension sqlite3_sleep sqlite3_snprintf sqlite3_soft_heap_limit sqlite3_thread_cleanup sqlite3_threadsafe sqlite3_vfs_find sqlite3_vfs_register sqlite3_vfs_unregister sqlite3_vmprintf -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Sorry, I didn't read the whole story before answering. You are right, the documentation on sqlite3_last_insert_rowid should contain some comment about the conflicts. I guess that, after working with SQLite for a long time, obvious things are not obvious to everyone and are easily forgotten in documentation :) Michael Ruck wrote: Daniel, My usecase is the following: I maintain a lot of tables, which are simply catalogs of predefined or user entered values. I wanted to reduce the amount of code and memory to maintain those tables, as the user can enter values as free text in a lot of web forms and also choose from previously entered values via ajax autocompletion. To simplify my code I wanted to use INSERT OR IGNORE in those catalog tables, as I don't care if the value is already there or not. I just need the rowids as foreign keys for other table(s). An example would be: - CREATE TABLE song (id INTEGER PRIMARY KEY, artist INTEGER, writer INTEGER, title INTEGER) - CREATE TABLE artists (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE writers (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE titles (id INTEGER PRIMARY KEY, value TEXT UNIQUE) For an insert of a new song I wanted to do (pseudo code) INSERT OR IGNORE INTO artists VALUES (NULL, 'user entered or chosen artistname'); rowid-of-first-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO writers VALUES (NULL, 'user entered or chosen writer name'); rowid-of-second-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO titles VALUES (NULL, 'user entered or chosen title'); rowid-of-third-insert = sqlite3_last_insert_rowid(); INSERT INTO song (NULL, rowid-of-first-insert, rowid-of-second-insert, rowid-of-third-insert); This is just a trivial example of what I want to do. My understanding of INSERT OR IGNORE was that it always succeeds, even though the record is already there (which is exactly what I want.) In contrast to INSERT OR REPLACE it doesn't remove the old row and thus keeps the same rowid. In conjunction with the documentation for sqlite3_last_insert_rowid(), which states: "This routine returns the rowid of the most recent INSERT into the database from the database connection given in the first argument. If no inserts have ever occurred on this database connection, zero is returned." I was assuming that I'll receive the rowid even in case where the conflict clause from INSERT OR IGNORE caused the insert not to happen. I was just a bit surprised about this and that's why I asked if this was expected behavior. Dr. Hipps answer cleared the reason for this up and I already started looking for alternatives. The only thing I was asking for is that this behavior (sqlite3_last_insert_rowid() and its results with CONFLICT clauses) be explicitly mentioned in the documentation of sqlite3_last_insert_rowid(). Unfortunately the solution proposed by Shawn Odekirk fails too, as I don't have the rowids for insert statements. If i did, I wouldn't INSERT OR IGNORE or INSERT OR REPLACE in the first place. The id column is automatically maintained by SQlite and I don't want to mess with it. So I'll just do blind inserts and check the return value and do a select if insert fails. Little more code, but it works. Mike -Ursprüngliche Nachricht- Von: Daniel Önnerby [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 30. Oktober 2007 12:03 An: sqlite-users@sqlite.org Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Why are you using the INSERT OR IGNORE? If you read the http://www.sqlite.org/lang_conflict.html it states that the IGNORE will not return any errors. Isn't the default behavior INSERT OR ABORT (or just plain INSERT) what you are looking for? The default INSERT will return an error (SQLITE_CONSTRAINT) if there is a conflict. If you get a conflict then do not trust the sqlite3_last_insert_rowid since (I guess) it will return the last successful insert rowid. Best regards Daniel Michael Ruck wrote: I'm not blaming anyone. I just think it should be mentioned in the docs. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 29. November 2007 20:12 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() As has been carefully explained by several people, it is reliable. You just did not think through your application. You could make an extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id function, but to blithely assume that you can use last_insert_id with INSERT OR IGNORE is not logical and to blame others for your oversight is not helpful. Michael Ruck wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the
[sqlite] Memory Management
DRH, I looked at the memory management code mem3.c. I noticed a mutex to protect allocations/deallocations and was wondering what the impact on performance to a multi threaded DB applicaiton would be? Other memory allcation schemes seem to allow each thread to manage memory independently of the other threads. In other words, the thread would have a hook into its own memory pool and would not be blocked by any other threads memory requests. Some interesting memory managers include: Unfortunately the first has licensing requirements and the second is copyrighted, but available for Free. http://www.cs.umass.edu/~emery/hoard/screenshot.html http://www.garret.ru/~knizhnik/threadalloc/readme.html Regards, Ken
RE: [sqlite] Proposed sqlite3_initialize() interface
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 30, 2007 7:19 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Proposed sqlite3_initialize() interface > > As currently implemented, SQLite3 requires no initialization. > You just start calling SQLite3 interfaces and they work. We > can pull off this trick on Unix because pthread mutexes can > be initialized statically at compile-time. > > static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER; > > On win32, we have to initialize mutexes at run-time, but this > can be done within a contrived mutex that we build off of > a static integer using InterlockedIncrement(). And mutex > initialization apparently never fails on win32, so we do not > have to worry with reporting errors that occur during > mutex initialization. > > 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. > Is there a reason this can't be checked/done in sqlit3_open() via an InterlockedCompareExchange() operation on the static integer, and if the mutexes don't exist and can't be created, you just return a different error code? Robert - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
I always create and XXX_Initialize() (and also XXX_Finalize() for resources cleanup) in all libraries I created, because: - You can perform initializations that cannot be done at compile time; - You can create your internal structures in the required order (C++ has the problem of initialization / finalization order of static objects, that could be a pain in some cases), independing on compiler / link order of your object files; Putting a simple call to a sqlite3_initialize() in a program costs near to nothing in my point of view, and could make things simpler for sqlite3 library. On Oct 30, 2007 12:18 PM, <[EMAIL PROTECTED]> wrote: > As currently implemented, SQLite3 requires no initialization. > You just start calling SQLite3 interfaces and they work. We > can pull off this trick on Unix because pthread mutexes can > be initialized statically at compile-time. > > static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER; > > On win32, we have to initialize mutexes at run-time, but this > can be done within a contrived mutex that we build off of > a static integer using InterlockedIncrement(). And mutex > initialization apparently never fails on win32, so we do not > have to worry with reporting errors that occur during > mutex initialization. > > 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. > > 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(...); > > prior to using any other SQLite interface. (The parameters to > sqlite3_initialize() are not yet designed.) It will be an error > to use any other SQLite interface without first invoking > sqlite3_initialize() exactly one. It is also an error to > invoke sqlite3_initialize() more than once. > > Existing applications that use SQLite would have to be modified > to invoke sqlite3_initialize(). Presumably this would happen > very early in main(), before any threads were created. No other > code changes would be required. > > This is still just an idea. If you think that adding a new > required sqlite3_initialize() interface would cause serious > hardship for your use of SQLite, please speak up now. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
[sqlite] Proposed sqlite3_initialize() interface
As currently implemented, SQLite3 requires no initialization. You just start calling SQLite3 interfaces and they work. We can pull off this trick on Unix because pthread mutexes can be initialized statically at compile-time. static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER; On win32, we have to initialize mutexes at run-time, but this can be done within a contrived mutex that we build off of a static integer using InterlockedIncrement(). And mutex initialization apparently never fails on win32, so we do not have to worry with reporting errors that occur during mutex initialization. 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. 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(...); prior to using any other SQLite interface. (The parameters to sqlite3_initialize() are not yet designed.) It will be an error to use any other SQLite interface without first invoking sqlite3_initialize() exactly one. It is also an error to invoke sqlite3_initialize() more than once. Existing applications that use SQLite would have to be modified to invoke sqlite3_initialize(). Presumably this would happen very early in main(), before any threads were created. No other code changes would be required. This is still just an idea. If you think that adding a new required sqlite3_initialize() interface would cause serious hardship for your use of SQLite, please speak up now. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Daniel, My usecase is the following: I maintain a lot of tables, which are simply catalogs of predefined or user entered values. I wanted to reduce the amount of code and memory to maintain those tables, as the user can enter values as free text in a lot of web forms and also choose from previously entered values via ajax autocompletion. To simplify my code I wanted to use INSERT OR IGNORE in those catalog tables, as I don't care if the value is already there or not. I just need the rowids as foreign keys for other table(s). An example would be: - CREATE TABLE song (id INTEGER PRIMARY KEY, artist INTEGER, writer INTEGER, title INTEGER) - CREATE TABLE artists (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE writers (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE titles (id INTEGER PRIMARY KEY, value TEXT UNIQUE) For an insert of a new song I wanted to do (pseudo code) INSERT OR IGNORE INTO artists VALUES (NULL, 'user entered or chosen artistname'); rowid-of-first-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO writers VALUES (NULL, 'user entered or chosen writer name'); rowid-of-second-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO titles VALUES (NULL, 'user entered or chosen title'); rowid-of-third-insert = sqlite3_last_insert_rowid(); INSERT INTO song (NULL, rowid-of-first-insert, rowid-of-second-insert, rowid-of-third-insert); This is just a trivial example of what I want to do. My understanding of INSERT OR IGNORE was that it always succeeds, even though the record is already there (which is exactly what I want.) In contrast to INSERT OR REPLACE it doesn't remove the old row and thus keeps the same rowid. In conjunction with the documentation for sqlite3_last_insert_rowid(), which states: "This routine returns the rowid of the most recent INSERT into the database from the database connection given in the first argument. If no inserts have ever occurred on this database connection, zero is returned." I was assuming that I'll receive the rowid even in case where the conflict clause from INSERT OR IGNORE caused the insert not to happen. I was just a bit surprised about this and that's why I asked if this was expected behavior. Dr. Hipps answer cleared the reason for this up and I already started looking for alternatives. The only thing I was asking for is that this behavior (sqlite3_last_insert_rowid() and its results with CONFLICT clauses) be explicitly mentioned in the documentation of sqlite3_last_insert_rowid(). Unfortunately the solution proposed by Shawn Odekirk fails too, as I don't have the rowids for insert statements. If i did, I wouldn't INSERT OR IGNORE or INSERT OR REPLACE in the first place. The id column is automatically maintained by SQlite and I don't want to mess with it. So I'll just do blind inserts and check the return value and do a select if insert fails. Little more code, but it works. Mike -Ursprüngliche Nachricht- Von: Daniel Önnerby [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 30. Oktober 2007 12:03 An: sqlite-users@sqlite.org Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Why are you using the INSERT OR IGNORE? If you read the http://www.sqlite.org/lang_conflict.html it states that the IGNORE will not return any errors. Isn't the default behavior INSERT OR ABORT (or just plain INSERT) what you are looking for? The default INSERT will return an error (SQLITE_CONSTRAINT) if there is a conflict. If you get a conflict then do not trust the sqlite3_last_insert_rowid since (I guess) it will return the last successful insert rowid. Best regards Daniel Michael Ruck wrote: > I'm not blaming anyone. I just think it should be mentioned in the docs. > > Mike > > -Ursprüngliche Nachricht- > Von: John Stanton [mailto:[EMAIL PROTECTED] > Gesendet: Donnerstag, 29. November 2007 20:12 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() > > As has been carefully explained by several people, it is reliable. > You just did not think through your application. You could make an > extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id > function, but to blithely assume that you can use last_insert_id with > INSERT OR IGNORE is not logical and to blame others for your oversight > is not helpful. > > Michael Ruck wrote: > >> I don't get an error code. So how should I decide if I should call >> sqlite3_last_insert_rowid() or not? :) That's the problem - I don't >> have >> > any > >> indication if an insert >> was actually performed or if it was simply ignored - thus I don't >> have any possibility to decide if the call is valid or not. This >> makes the OR >> > IGNORE > >> clause or the sqlite3_last_insert_rowid() function useless for *my >> purposes*. I would have never pursued this path in tests, if I >> would've known beforehand that it is not reliable if used with ON CONFLICT clauses. >> >> Mike >>
Re: [sqlite] Soft heap limit enforcement performance
patters <[EMAIL PROTECTED]> wrote:
> We rely on the SQLite memory management to enforce the memory usage in our
> application (running on Windows CE). This has worked quite well for us, but
> have found that when we hit the limit, in some circumstances, performance
> drops significantly.
>
> Looking into the internals of SQLite, it seems that when you are at the
> memory limit, an allocation of size N will attempt to free N bytes from the
> pager. We think this should be increased for performance reasons. By
> altering softHeapLimitEnforcer to free more than is necessary, the limit
> isn't reached again (or at least for some time) which helps in our tests,
> though we haven't done a formal benchmark.
>
> Adding these lines to the softHeapLimitEnforcer seem to help:
>
> if (allocSize < inUse/8) {
> allocSize += inUse/8;
> }
>
> Here, the function's being called with an allocSize equal to the page size
> of the database, and inUse is at the soft heap limit. Instead of freeing a
> page (and then being called over and over, essentially), we free 12% of the
> memory in use. If a formal benchmark should be done, this would be the
> figure to tweak -- 12% gives much improved performance in our tests (when
> the heap limit is roughly 1000 pages in size).
>
We are actively working on the memory management problem in a
fork of the source tree. See
http://www.sqlite.org/mpool/fossil
The focus of our current research is in reducing memory fragmentation,
but this is very much related to limiting memory usage. Assuming we
achieve good results, the experimental fork will be folded back into
the CVS tree at some point.
Your idea of releasing more memory that is strictly necessary has
merit. Please note that you can implement such a schema without
making any changes to the SQLite core by registering your own
limiter function using the sqlite3_memory_alarm() interface. Make
a copy of the implementation of softHeapLimitEnforcer() and
sqlite3_soft_heap_limit(), change their names, then adjust
the renamed softHeaplimitEnforcer() to use whatever memory reclaim
policy seems to work best for you.
You are using SQLite 3.5.x, aren't you?
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Retrieve bound host parameters from statement?
Hello Joe Wilson, >> True, but we would need to access unsupported API to do so. And as we know >> only too well, >> unsupported API is subject to change without notice any time ;-). Therefore >> I would rather not >> write these myself but ask for the possibility to add them to the library >> officially, even if >> "experimental" only. > >Then you'll be waiting forever. > >If you post a patch implementing the feature, at least some >other like-minded programmers might get some benefit from it, >or at least generate some feedback. The simple patch is not enough. To have any value, it must be supported and tested for upcomming versions of SQLite. Using undocumented APIs always carries the risk that the patch will break in the future, possibly staying unnoticed until it caused serious problems to someone. Dan's solution is safe in this regard, and should be preferred - even if using unofficial API calls will certainly be faster. Maybe we'll be lucky and will not be waiting forever!? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieve bound host parameters from statement?
Hello Dan Kennedy,
>> True, but we would need to access unsupported API to do so.
>> And as we know only too well, unsupported API is subject to
>> change without notice any time ;-). Therefore I would rather
>> not write these myself but ask for the possibility to add them
>> to the library officially, even if "experimental" only.
>
>Depends how desperate you are. Say you want to query statement
>object X that has 4 variables, you could do this:
>
> pTmp = sqlite3_prepare("SELECT ?, ?, ?, ?");
> sqlite3_transfer_bindings(X, pTmp);
> /* Use sqlite3_step() etc. to fish values out of pTmp */
> sqlite3_transfer_bindings(pTmp, X);
> sqlite3_finalize(pTmp);
Smart, many thanks!
Still, I believe that faster sqlite3_bound... functions would be a useful
addition to the official API. It seems only logical to have corresponding read
functions to complement the write functions.
They would, for example, help to fill the gap of the unresolved host parameters
if the trace callback was changed to carry along the prepared statement being
executed.
Ralf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] libsqlite3.so.0 not found
if you do $ ldconfig -v or $ ldconfig -v | grep libsqlite3 search for libsqlite3.so.0 in the list.. is it available are you running a 64bit OS version? if yes - depending on the client app - you need the 32bit version if client app is 32bit Filip [EMAIL PROTECTED] wrote: - To: sqlite-users@sqlite.org From: [EMAIL PROTECTED] Date: 10/30/2007 13:21 Subject: [sqlite] libsqlite3.so.0 not found I downloaded the source tarball for 3.5.1 and followed the instructions in README. The build dir is sqlite-3.5.1/../bld. Then cd'd into a subdir of ~ and tried to create a db: $ sqlite3 mysite.db sqlite3: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory The machine is running Zenwalk 2.6, which is an offshoot of Slackware. --Dan Bensen www.prairienet.org/~dsb/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Aggregate function as column
> SELECT number, name, (SELECT COUNT(*) FROM pet WHERE employee.number = > pet.number) AS pets FROM employee That seems like it should work, provided the tables have the definitions implied by the query. What error are you getting? Or you could try something like this completely off-the-cuff-and-untested thing. select employee.number, employee.name, count(pet.name) as petcount from employee join pet on employee.number = pet.number group by employee.number, employee.name order by employee.number, employee.name - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Soft heap limit enforcement performance
We rely on the SQLite memory management to enforce the memory usage in our
application (running on Windows CE). This has worked quite well for us, but
have found that when we hit the limit, in some circumstances, performance
drops significantly.
Looking into the internals of SQLite, it seems that when you are at the
memory limit, an allocation of size N will attempt to free N bytes from the
pager. We think this should be increased for performance reasons. By
altering softHeapLimitEnforcer to free more than is necessary, the limit
isn't reached again (or at least for some time) which helps in our tests,
though we haven't done a formal benchmark.
Adding these lines to the softHeapLimitEnforcer seem to help:
if (allocSize < inUse/8) {
allocSize += inUse/8;
}
Here, the function's being called with an allocSize equal to the page size
of the database, and inUse is at the soft heap limit. Instead of freeing a
page (and then being called over and over, essentially), we free 12% of the
memory in use. If a formal benchmark should be done, this would be the
figure to tweak -- 12% gives much improved performance in our tests (when
the heap limit is roughly 1000 pages in size).
--
View this message in context:
http://www.nabble.com/Soft-heap-limit-enforcement-performance-tf4718320.html#a13488090
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Aggregate function as column
"james stuart" <[EMAIL PROTECTED]> wrote: > I'm hope somebody can help me get unstuck here - > > I'm trying to use an aggregate function in a select statement but it appears > that you can't use one as a column - > > SELECT number, name, (SELECT COUNT(*) FROM pet WHERE employee.number = > pet.number) AS pets FROM employee > > I can't come up with another way of doing this, or working out what I'm > doing wrong. There is a bug in SQLite that prevents it from working correctly with aggregate functions is correlated subqueries. This is going to be something that is very difficult to fix and so I have not yet started to fix it :-) See the ticket at http://www.sqlite.org/cvstrac/tktview?tn=2652 -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Aggregate function as column
I'm hope somebody can help me get unstuck here - I'm trying to use an aggregate function in a select statement but it appears that you can't use one as a column - SELECT number, name, (SELECT COUNT(*) FROM pet WHERE employee.number = pet.number) AS pets FROM employee I can't come up with another way of doing this, or working out what I'm doing wrong. Cheers, James
[sqlite] libsqlite3.so.0 not found
I downloaded the source tarball for 3.5.1 and followed the instructions in README. The build dir is sqlite-3.5.1/../bld. Then cd'd into a subdir of ~ and tried to create a db: $ sqlite3 mysite.db sqlite3: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory The machine is running Zenwalk 2.6, which is an offshoot of Slackware. --Dan Bensen www.prairienet.org/~dsb/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] left join usage and performance
First of all I must say that I enjoyed reading your presentation at http://www.sqlite.org/php2004/page-001.html on the internals of SQLite and the performance tips. I could not find any information on the processing of LEFT JOIN queries though... As SQLite only does loop-joins, I wonder how I should best use LEFT JOINS. Most of my queries use LEFT JOINS to get additional information on - in this case - specific aircraft. 2 Tables "asterix" and "aircraft" with indexes: CREATE INDEX a_modea ON asterix(modeA); CREATE INDEX a_modes ON asterix(modeS); CREATE INDEX b_modes ON aircraft(modeS); the first table (A) contains approx. 4.000.000 records (several hundred entries per aircraft) while the second table (B) contains about 100.000 records (lookup table with aircraft details) total database size is approx. 3GB. (there are 2 other tables in the db similar in size to the first table) the following query takes a long time: select A.ModeA,A.modeS,A.acid,count(A.modeS) plots,B.actype,B.registration from asterix A LEFT JOIN aircraft B ON A.modeS = B.modeS where A.ModeS is not NULL and A.modeA=1234 group by A.ModeS having plots > 5 How does SQLite handle LEFT JOINs ? Is there a better way to write such queries than using LEFT JOIN? Is there a performance benefit in using a combined index like for example: CREATE INDEX a_full ON asterix(modeA,modeS); any tips regarding LEFT JOINs are much appreciated regards, Filip - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Why are you using the INSERT OR IGNORE? If you read the http://www.sqlite.org/lang_conflict.html it states that the IGNORE will not return any errors. Isn't the default behavior INSERT OR ABORT (or just plain INSERT) what you are looking for? The default INSERT will return an error (SQLITE_CONSTRAINT) if there is a conflict. If you get a conflict then do not trust the sqlite3_last_insert_rowid since (I guess) it will return the last successful insert rowid. Best regards Daniel Michael Ruck wrote: I'm not blaming anyone. I just think it should be mentioned in the docs. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 29. November 2007 20:12 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() As has been carefully explained by several people, it is reliable. You just did not think through your application. You could make an extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id function, but to blithely assume that you can use last_insert_id with INSERT OR IGNORE is not logical and to blame others for your oversight is not helpful. Michael Ruck wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the call is valid or not. This makes the OR IGNORE clause or the sqlite3_last_insert_rowid() function useless for *my purposes*. I would have never pursued this path in tests, if I would've known beforehand that it is not reliable if used with ON CONFLICT clauses. Mike -Ursprüngliche Nachricht- Von: Odekirk, Shawn [mailto:[EMAIL PROTECTED] Gesendet: Montag, 29. Oktober 2007 14:04 An: sqlite-users@sqlite.org Betreff: RE: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() The sqlite3_last_insert_rowid function is completely, 100% reliable in your scenario. The problem is that in your scenario you shouldn't be calling that function. The function is called sqlite3_last_insert_rowid, not sqlite3_last_insert_or_ignore_rowid, and not sqlite3_last_insert_or_fail_rowid. It makes perfect sense that it returns the row id of the last row inserted successfully. This function should only be called after a successful insert. In your scenario you have not performed a successful insert. There is no reason to think that the function will return a meaningful row id after a failed insert attempt. I hope my response was not too harsh. You seem so adamant that there is a problem with the function or documentation, and I completely disagree. Shawn -Original Message- From: Michael Ruck [mailto:[EMAIL PROTECTED] Sent: Sunday, October 28, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() I'd suggest putting this into the documentation of sqlite3_last_insert_rowid(), that the call is not reliable in scenarios such as this one. -Ursprüngliche Nachricht- Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 28. Oktober 2007 17:48 An: sqlite-users@sqlite.org Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: Yes, I am well aware of this possibility as I've written in my initial mail. It just doesn't fit with the description of sqlite3_last_insert_rowid() in my understanding. I think this is a bug - either in the documentation or in the implementation. sqlite3_last_insert_rowid() should return the correct id, no matter what and it doesn't. Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED]
Re: [sqlite] Retrieve bound host parameters from statement?
On Mon, 2007-10-29 at 17:49 +0100, Ralf Junker wrote:
> >> I wonder if it is possible to retrieve bound host parameters from a
> >> prepared SQL statement? I am
> >> thinking of the opposite of the sqlite3_bind... family of functions like:
> >>
> >> int sqlite3_bound_int (sqlite3_stmt*, int*);
> >> int sqlite3_bound_double (sqlite3_stmt*, double*);
> >
> >You'd also need to specify the index of the ? parameter you're seeking.
>
> Certainly. Sorry for the ommission, glad you pointed this out.
>
> >> They would be usefull to work around the sqlite3_trace() limitation which
> >> does not replace host
> >> parameters in the SQL. With the sqlite3_bound... functions, the trace
> >> callback would be able
> >> retrieve the parameter values from the statement and replace them or log
> >> them separately.
> >
> >You could create all this functionality in your wrapper level above
> >the sqlite3 API.
> >
> >It would be easy enough for you to modify the sqlite3 sources to add
> >such functions to fish the values out of the internal Vdbe.aVar Mem
> >array of the sqlite3_stmt. If the type does not match what is stored
> >internally, or something was not previously bound or out of range, I
> >imagine an SQLITE_ERROR could be returned. Or maybe you want your
> >bound* functions to coerce the bound value to the type you specify.
>
> True, but we would need to access unsupported API to do so.
> And as we know only too well, unsupported API is subject to
> change without notice any time ;-). Therefore I would rather
> not write these myself but ask for the possibility to add them
> to the library officially, even if "experimental" only.
Depends how desperate you are. Say you want to query statement
object X that has 4 variables, you could do this:
pTmp = sqlite3_prepare("SELECT ?, ?, ?, ?");
sqlite3_transfer_bindings(X, pTmp);
/* Use sqlite3_step() etc. to fish values out of pTmp */
sqlite3_transfer_bindings(pTmp, X);
sqlite3_finalize(pTmp);
Dan.
>
> >Another complementary function, say sqlite3_bound_type, could
> >return the type(s) of the bound field. (I say types plural because
> >sometimes a value can be a combination of types at the same time -
> >i.e., MEM_Real|MEM_Int). These internal types would have to be
> >exposed if you required such functionality.
> >
> >#define MEM_Null 0x0001 /* Value is NULL */
> >#define MEM_Str 0x0002 /* Value is a string */
> >#define MEM_Int 0x0004 /* Value is an integer */
> >#define MEM_Real 0x0008 /* Value is a real number */
> >#define MEM_Blob 0x0010 /* Value is a BLOB */
>
> Indeed very much agreed to!
>
> Ralf
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] database lock error
I've already posted here question about getting SQLITE_BUSY when calling sqlite3_prepare in single thread application dependent on database size. Now I get more detailed diagnostic. I have simple database but with considerable amount of records in some tables. Sql commands looks like: Begin transaction delete from some_table (most of records) select from sqlite_master insert into some_table commit transaction I get SQLITE_BUSY trying to select from sqlite_master. Looking into sqlite code I found that sqlite get exclusive lock on database file when database exceeds some limit. And after this it makes one more lock which of cause ends with error. Here you can find two fragments of sqlite trace. First is for smaller database: First: OPEN 1980 LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) LOCK 1980 2 was 1(0) OPEN 1972 - journal file OPEN 1968 LOCK 1968 1 was 0(0) TEST WR-LOCK 1968 1 (remote) UNLOCK 1968 to 0 was 1(0) LOCK 1968 1 was 0(0) TEST WR-LOCK 1968 1 (remote) UNLOCK 1968 to 0 was 1(0) UNLOCK 1968 to 0 was 0(0) CLOSE 1968 LOCK 1980 4 was 2(0) unreadlock = 1 CLOSE 1972 UNLOCK 1980 to 1 was 4(0) UNLOCK 1980 to 0 was 1(0) UNLOCK 1980 to 0 was 0(0) CLOSE 1980 And second: OPEN 1980 LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) UNLOCK 1980 to 0 was 1(0) LOCK 1980 1 was 0(0) LOCK 1980 2 was 1(0) OPEN 1972 - journal file LOCK 1980 4 was 2(0) - exclusive lock unreadlock = 1 OPEN 1968 LOCK 1968 1 was 0(0) LOCK FAILED 1968 trying for 1 but got 0 UNLOCK 1968 to 0 was 0(0) CLOSE 1968 CLOSE 1972 UNLOCK 1980 to 1 was 4(0) UNLOCK 1980 to 0 was 1(0) UNLOCK 1980 to 0 was 0(0) CLOSE 1980
AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
I'm not blaming anyone. I just think it should be mentioned in the docs. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 29. November 2007 20:12 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() As has been carefully explained by several people, it is reliable. You just did not think through your application. You could make an extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id function, but to blithely assume that you can use last_insert_id with INSERT OR IGNORE is not logical and to blame others for your oversight is not helpful. Michael Ruck wrote: > I don't get an error code. So how should I decide if I should call > sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any > indication if an insert > was actually performed or if it was simply ignored - thus I don't have any > possibility to decide if the call is valid or not. This makes the OR IGNORE > clause or the sqlite3_last_insert_rowid() function useless for *my > purposes*. I would have never pursued this path in tests, if I would've > known beforehand that it is not reliable if used with ON CONFLICT clauses. > > Mike > > -Ursprüngliche Nachricht- > Von: Odekirk, Shawn [mailto:[EMAIL PROTECTED] > Gesendet: Montag, 29. Oktober 2007 14:04 > An: sqlite-users@sqlite.org > Betreff: RE: AW: AW: [sqlite] INSERT OR IGNORE and > sqlite3_last_insert_rowid() > > The sqlite3_last_insert_rowid function is completely, 100% reliable in your > scenario. The problem is that in your scenario you shouldn't be calling > that function. > The function is called sqlite3_last_insert_rowid, not > sqlite3_last_insert_or_ignore_rowid, and not > sqlite3_last_insert_or_fail_rowid. It makes perfect sense that it returns > the row id of the last row inserted successfully. This function should only > be called after a successful insert. In your scenario you have not > performed a successful insert. There is no reason to think that the > function will return a meaningful row id after a failed insert attempt. > I hope my response was not too harsh. You seem so adamant that there is a > problem with the function or documentation, and I completely disagree. > > Shawn > > -Original Message- > From: Michael Ruck [mailto:[EMAIL PROTECTED] > Sent: Sunday, October 28, 2007 12:55 PM > To: sqlite-users@sqlite.org > Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and > sqlite3_last_insert_rowid() > > I'd suggest putting this into the documentation of > sqlite3_last_insert_rowid(), that the call is not reliable in scenarios such > as this one. > > -Ursprüngliche Nachricht- > Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Gesendet: Sonntag, 28. Oktober 2007 17:48 > An: sqlite-users@sqlite.org > Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and > sqlite3_last_insert_rowid() > > > On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: > > >> Yes, I am well aware of this possibility as I've written in my >> initial mail. >> It just doesn't fit with the >> description of sqlite3_last_insert_rowid() in my understanding. I >> think this >> is a bug - either in the documentation >> or in the implementation. sqlite3_last_insert_rowid() should return >> the >> correct id, no matter what and it doesn't. >> >> > > Consider this scenario: > > CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); > INSERT INTO ex1 VALUES(1,1,1); > INSERT INTO ex1 VALUES(2,2,2); > INSERT INTO ex1 VALUES(3,3,3); > > Now you do your INSERT OR IGNORE: > > INSERT OR IGNORE INTO ex1 VALUES(1,2,3); > > Three different constraints fail, one for each of three different > rows. So if sqlite3_last_insert_rowid() were to operate as you > suggest and return the rowid of the failed insert, when rowid > would it return? 1, 2, or 3? > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED]
[sqlite] db4o 500x faster than sqlite?
Hi! I've heard that the guys at db4o said that, under certain circunstances, db4o is 500x faster than sqlite: Is this because of the jdbc driver? What would be a more realistic measure? (db4o is an object database, not a relational one) They are using the poleposition benchmark (http://polepos.sourceforge.net). Anybody knows if this benchmark is accurate for sqlite? Best regards and thx! - To unsubscribe, send email to [EMAIL PROTECTED] -
