Re: [sqlite] lock counting?
After reading Dan's response, I must be mis-interpreting these comments.
Please ignore.
--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- Richard Klein <[EMAIL PROTECTED]> wrote:
> > In implementing xLock in a VFS, do we need to worry
> > about lock counts, i.e. nested locking?
> >
> > In other words, if a process asks for, say, a SHARED
> > lock, and he already has one, should we increment a
> > SHARED lock count? Or is it okay to just return,
> > i.e. to treat the request as a no-op?
>
> See comments for unixLock() and unixUnlock() in os_unix.c.
>
> /*
> ** An instance of the following structure is allocated for each open
> ** inode on each thread with a different process ID. (Threads have
> ** different process IDs on linux, but not on most other unixes.)
> **
> ** A single inode can have multiple file descriptors, so each unixFile
> ** structure contains a pointer to an instance of this object and this
> ** object keeps a count of the number of unixFile pointing to it.
> */
> struct lockInfo {
> struct lockKey key; /* The lookup key */
> int cnt; /* Number of SHARED locks held */
> int locktype;/* One of SHARED_LOCK, RESERVED_LOCK etc. */
> int nRef;/* Number of pointers to this structure */
> };
>
> ...
>
> /* If a SHARED lock is requested, and some thread using this PID already
> ** has a SHARED or RESERVED lock, then increment reference counts and
> ** return SQLITE_OK.
> */
> if( locktype==SHARED_LOCK &&
> (pLock->locktype==SHARED_LOCK || pLock->locktype==RESERVED_LOCK) ){
> assert( locktype==SHARED_LOCK );
> assert( pFile->locktype==0 );
> assert( pLock->cnt>0 );
> pFile->locktype = SHARED_LOCK;
> pLock->cnt++;
> pFile->pOpen->nLock++;
> goto end_lock;
> }
__
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] lock counting?
--- Richard Klein <[EMAIL PROTECTED]> wrote:
> In implementing xLock in a VFS, do we need to worry
> about lock counts, i.e. nested locking?
>
> In other words, if a process asks for, say, a SHARED
> lock, and he already has one, should we increment a
> SHARED lock count? Or is it okay to just return,
> i.e. to treat the request as a no-op?
See comments for unixLock() and unixUnlock() in os_unix.c.
/*
** An instance of the following structure is allocated for each open
** inode on each thread with a different process ID. (Threads have
** different process IDs on linux, but not on most other unixes.)
**
** A single inode can have multiple file descriptors, so each unixFile
** structure contains a pointer to an instance of this object and this
** object keeps a count of the number of unixFile pointing to it.
*/
struct lockInfo {
struct lockKey key; /* The lookup key */
int cnt; /* Number of SHARED locks held */
int locktype;/* One of SHARED_LOCK, RESERVED_LOCK etc. */
int nRef;/* Number of pointers to this structure */
};
...
/* If a SHARED lock is requested, and some thread using this PID already
** has a SHARED or RESERVED lock, then increment reference counts and
** return SQLITE_OK.
*/
if( locktype==SHARED_LOCK &&
(pLock->locktype==SHARED_LOCK || pLock->locktype==RESERVED_LOCK) ){
assert( locktype==SHARED_LOCK );
assert( pFile->locktype==0 );
assert( pLock->cnt>0 );
pFile->locktype = SHARED_LOCK;
pLock->cnt++;
pFile->pOpen->nLock++;
goto end_lock;
}
__
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] lock counting?
On 11/1/07, Richard Klein <[EMAIL PROTECTED]> wrote: > In implementing xLock in a VFS, do we need to worry > about lock counts, i.e. nested locking? No. You don't need to worry about that. > In other words, if a process asks for, say, a SHARED > lock, and he already has one, should we increment a > SHARED lock count? Or is it okay to just return, > i.e. to treat the request as a no-op? Locks are on a per-handle basis. If a handle already has an equal or greater lock to that requested, treat the request as a no-op. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
Have the trigger function set a flag or put an item in a work queue after it updates the database. After the commit, check/clear the flag or empty the work queue, raising the semaphore if necessary. --- Bill Gatliff <[EMAIL PROTECTED]> wrote: > My application is a mobile platform with a GUI that wants to display > frequently-updated data in a database. The GUI is a separate process > from the one providing the data, and is one of several consumers of that > data. > > I prefer not to poll for changes, because the system is > performance-constrained. So instead, I'm using an AFTER UPDATE trigger > in the data-generating process to launch a C function that posts to a > semaphore. The GUI process is thus unblocked, and it then does a SELECT > to get the data. I'm using the C/C++ API everywhere. > > The problem I'm seeing is that the GUI process is getting stale data in > its SELECT, unless it does a brief sleep between the sem_wait() and the > sqlite3_exec(). Specifically, the value returned is the value > immediately before the UPDATE. It's as if the trigger in the > data-generating process and the query in the GUI process are both > running before the new value is actually committed to the database file > and/or whatever caches are in between. __ 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] Problem using POSIX semaphores and triggers to signal updates
Trevor Talbot wrote: On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: I prefer not to poll for changes, because the system is performance-constrained. So instead, I'm using an AFTER UPDATE trigger in the data-generating process to launch a C function that posts to a semaphore. The GUI process is thus unblocked, and it then does a SELECT to get the data. I'm using the C/C++ API everywhere. The problem I'm seeing is that the GUI process is getting stale data in its SELECT, unless it does a brief sleep between the sem_wait() and the sqlite3_exec(). Specifically, the value returned is the value immediately before the UPDATE. It's as if the trigger in the data-generating process and the query in the GUI process are both running before the new value is actually committed to the database file and/or whatever caches are in between. The trigger necessarily fires immediately after the row change, but before the transaction is committed. It's the committing that takes most of the time in a database write operation. So yes, definitely expected behavior. Bummer. :) If your platform has a file modification notification mechanism, you may be able to sleep on that instead. Of course the problem with this approach is that it's only a coarse-grained "something changed" notification, and doesn't tell you what changed. You may be able to counter that by having the trigger store a note about what changed in a separate table that your GUI queries when it wants to know specifics. Perhaps, but that may just move the problem. The "GUI information" table might not be committed before the GUI process wakes up to find out what happened. If you don't have a cheap file notification, something more creative might be necessary. Do you control the sqlite library used by the data writers? Perhaps you can modify it to provide notifications immediately after commit instead. (I'm assuming you don't want to modify the writers themselves directly, by having them signal after they issue a COMMIT.) I control the code on both ends. I looked at the sqlite3 code, and the places to modify didn't exactly jump off the page at me. :) Does sqlite3_enable_shared_cache() factor into this any? I turned it on, and it didn't seem to help. What about sqlite3_commit_hook()? Though the (very few) examples I've managed to find don't make it clear how to figure out the rowid that was updated. And without that, I can't easily determine which semaphore I need to post to... Can I pass the database handle as the argument to the callback? b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: > My application is a mobile platform with a GUI that wants to display > frequently-updated data in a database. The GUI is a separate process > from the one providing the data, and is one of several consumers of that > data. > I prefer not to poll for changes, because the system is > performance-constrained. So instead, I'm using an AFTER UPDATE trigger > in the data-generating process to launch a C function that posts to a > semaphore. The GUI process is thus unblocked, and it then does a SELECT > to get the data. I'm using the C/C++ API everywhere. > > The problem I'm seeing is that the GUI process is getting stale data in > its SELECT, unless it does a brief sleep between the sem_wait() and the > sqlite3_exec(). Specifically, the value returned is the value > immediately before the UPDATE. It's as if the trigger in the > data-generating process and the query in the GUI process are both > running before the new value is actually committed to the database file > and/or whatever caches are in between. The trigger necessarily fires immediately after the row change, but before the transaction is committed. It's the committing that takes most of the time in a database write operation. So yes, definitely expected behavior. If your platform has a file modification notification mechanism, you may be able to sleep on that instead. Of course the problem with this approach is that it's only a coarse-grained "something changed" notification, and doesn't tell you what changed. You may be able to counter that by having the trigger store a note about what changed in a separate table that your GUI queries when it wants to know specifics. If you don't have a cheap file notification, something more creative might be necessary. Do you control the sqlite library used by the data writers? Perhaps you can modify it to provide notifications immediately after commit instead. (I'm assuming you don't want to modify the writers themselves directly, by having them signal after they issue a COMMIT.) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem using POSIX semaphores and triggers to signal updates
Guys: I'm a relatively-new SQLite user, but I'm luuuvin' it! :) My application is a mobile platform with a GUI that wants to display frequently-updated data in a database. The GUI is a separate process from the one providing the data, and is one of several consumers of that data. I prefer not to poll for changes, because the system is performance-constrained. So instead, I'm using an AFTER UPDATE trigger in the data-generating process to launch a C function that posts to a semaphore. The GUI process is thus unblocked, and it then does a SELECT to get the data. I'm using the C/C++ API everywhere. The problem I'm seeing is that the GUI process is getting stale data in its SELECT, unless it does a brief sleep between the sem_wait() and the sqlite3_exec(). Specifically, the value returned is the value immediately before the UPDATE. It's as if the trigger in the data-generating process and the query in the GUI process are both running before the new value is actually committed to the database file and/or whatever caches are in between. Is this expected behavior? Is there a better workaround than simply sleeping after getting unblocked? Am I missing an entirely different--- and better--- way of synchronizing my GUI to the database? Thanks! b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] lock counting?
In implementing xLock in a VFS, do we need to worry about lock counts, i.e. nested locking? In other words, if a process asks for, say, a SHARED lock, and he already has one, should we increment a SHARED lock count? Or is it okay to just return, i.e. to treat the request as a no-op? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
You're not the least bit interested in finding out what the issue in CodeWarrior was? It might be a symptom of another problem. --- Marco Bambini <[EMAIL PROTECTED]> wrote: > The problem was somewhere inside CodeWarrior because the same exact > code worked fine with Visual C. > I used CodeWarrior for Win for all my win32 sqlite compilation but it > seems time to update my Win Dev environment... __ 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] Performance problem for a simple select with range
Igor Tandetnik wrote: > > Try searching for a value that doesn't fall into any block - you'll > likely find that the query takes a noticeable time to produce zero > records. Pick a large value that's greater than all startIpNum's. > Yes, you are right. That's why I'm going with the original query you suggested. Thanks again Dani -- View this message in context: http://www.nabble.com/Performance-problem-for-a-simple-select-with-range-tf4711654.html#a13517991 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DB managers that do searches?
> happy user of sqliteman Thanks for the tip, it is quite nice. Two things: Help doesn't launch from the interface and it always seems to give Row(s) returned: 256 even when there are lot more. Another nice one is SQL2006 Pro from OsenXPSuite. RBS -Original Message- From: Bernie Cosell [mailto:[EMAIL PROTECTED] Sent: 31 October 2007 19:20 To: sqlite-users@sqlite.org Subject: RE: [sqlite] DB managers that do searches? On 31 Oct 2007 at 11:37, James Dennett wrote: > Bernie Cosell wrote: > > I guess you've never used a [good] GUI-driven DB > manager/administration > > pgm. > > Your guess (luckily for me) is very wrong. It's just that I call these > GUIs, not "DB managers". Ah... a terminology problem.. I'll just point out that the section in the wiki that has all of these pgms in it is called "Management Tools". > .. They're handy. I have a number of them > installed on the machine on which I write this, and I use them in > addition to command line tools. As I mentioned in another msg on this thread, we use phpMyAdmin for our MySQL databases at work and I can't remember the last time anyone at work needed (or wanted) to use the command line tool. Different strokes... > That's not a problem, is it? Just a question of using a tool at the > right level. If you want to automate things, writing code is often a > good way. If you want to do ad hoc work, a visual tool can be much more > convenient. Just so. Only difference between us here is that I have virtually no use for the command line tool: if I need to automate, I'll just write a little Perl/DBI pgm to do it (I have dozens of 'em..:o)) and I use the "visual tool" for everything else. I'd rather write a small Perl program that try to cobble up a script to be read into the command line app. As above, YMMV... > So you're looking for a graphical tool to allow you to manually view and > modify information in a SQLite3 database? Yes, and I'm now the happy user of sqliteman, so my search is over..:o). It's "query manager" does *exactly* what I needed and works wonderfully. (and indeed, easily found the index conflict I was trying to sort out easily.) /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
The problem was somewhere inside CodeWarrior because the same exact
code worked fine with Visual C.
I used CodeWarrior for Win for all my win32 sqlite compilation but it
seems time to update my Win Dev environment...
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Oct 31, 2007, at 7:59 PM, Joe Wilson wrote:
Can you post the output of this command when you compile
sqlite 3.4.2 with code warrior for your test.sqlite database?
explain SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
With the sqlite 3.5.1 shell compiled with gcc 4.1.1 I see:
0|Goto|0|25|
1|Integer|0|0|# One
2|OpenRead|0|2|
3|SetNumColumns|0|4|
4|Integer|0|0|# idx_One
5|OpenRead|1|4|keyinfo(3,BINARY,BINARY)
6|Integer|99|0|
7|IsNull|-1|22|
8|Integer|100|0|
9|IsNull|-2|22|
10|Integer|101|0|
11|IsNull|-3|22|
12|MakeRecord|3|0|ddd
13|MemStore|0|0|
14|MoveGe|1|22|
15|MemLoad|0|0|
16|IdxGE|1|22|+
17|IdxRowid|1|0|
18|MoveGe|0|0|
19|Column|0|0|# One.a
20|Callback|1|0|
21|Next|1|15|
22|Close|0|0|
23|Close|1|0|
24|Halt|0|0|
25|Transaction|0|0|
26|VerifyCookie|0|2|
27|TableLock|0|2|One
28|Goto|0|1|
29|Noop|0|0|
Just for the heck of it, can you also provide the code warrior/3.4.2
output for these commands as well?
-- select case 2
-- Getting all columns works
explain SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
-- select case 3
-- Not using whole index works
explain SELECT a FROM One WHERE b2 = 100 and b3 = 101;
-- select case 4
-- Getting one column, in the index, works
explain SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
--- Marco Bambini <[EMAIL PROTECTED]> wrote:
To be really sure I rewrote the example in C linked to the official
sqlite 3.4.2.
Here it is my source code:
#include
#include
#include
#include "sqlite3.h"
int main(void)
{
sqlite3 *db = NULL;
int rc = SQLITE_OK;
charsql[256];
char**result;
inti, nrow, ncol;
// open db
rc = sqlite3_open("test.sqlite", );
if (rc != SQLITE_OK) goto abort;
// create table
rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// create index
rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
b3);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// insert loop
for (i=1; i<=100; i++)
{
snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
'A');", i, i+1, i+2, i+3);
rc = sqlite3_exec(db, sql, NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
}
// query test 1
rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
100 and b3 = 101;", , , , NULL);
if (rc != SQLITE_OK) goto abort;
for(i=0; i 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...
__
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] Strange error in sqlite 3.4.2 Win32 version
Could you also report the md5sum values of the resultant
test.sqlite database file for Win32/CodeWarrior, OSX and linux?
(Assuming they all have the same default pragma page_size=1024;)
--- Marco Bambini <[EMAIL PROTECTED]> wrote:
> To be really sure I rewrote the example in C linked to the official
> sqlite 3.4.2.
> Here it is my source code:
>
> #include
> #include
> #include
> #include "sqlite3.h"
>
> int main(void)
> {
> sqlite3 *db = NULL;
> int rc = SQLITE_OK;
> charsql[256];
> char**result;
> int i, nrow, ncol;
>
> // open db
> rc = sqlite3_open("test.sqlite", );
> if (rc != SQLITE_OK) goto abort;
>
> // create table
> rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
> integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
> if (rc != SQLITE_OK) goto abort;
>
> // create index
> rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
> b3);", NULL, 0, NULL);
> if (rc != SQLITE_OK) goto abort;
>
> // insert loop
> for (i=1; i<=100; i++)
> {
> snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
> 'A');", i, i+1, i+2, i+3);
> rc = sqlite3_exec(db, sql, NULL, 0, NULL);
> if (rc != SQLITE_OK) goto abort;
> }
>
> // query test 1
> rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
> 100 and b3 = 101;", , , , NULL);
> if (rc != SQLITE_OK) goto abort;
>
> for(i=0; i {
> printf(result[i]);
> printf("\t\t");
> }
> printf("\n");
>
> for(i=0; i {
> printf(result[ncol+i]);
> printf("\t\t");
> if (i % ncol == 0) printf("\n");
> }
>
> // free table
> sqlite3_free_table(result);
>
> // close db
> sqlite3_close(db);
>
> printf("simple test finished!\n");
> return 0;
>
> abort:
> printf("%s\n", sqlite3_errmsg(db));
> if (db != NULL) sqlite3_close(db);
> return -1;
> }
>
> On Windows (not on Mac!) it returns 99 instead of the correct 98 value.
> Anyone can confirm that on Windows?
>
> Thanks a lot.
> ---
> Marco Bambini
> http://www.sqlabs.net
> http://www.sqlabs.net/blog/
> http://www.sqlabs.net/realsqlserver/
>
>
>
> On Oct 30, 2007, at 5:15 PM, [EMAIL PROTECTED] wrote:
>
> > 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).
__
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] DB managers that do searches?
On 31 Oct 2007 at 11:37, James Dennett wrote: > Bernie Cosell wrote: > > I guess you've never used a [good] GUI-driven DB > manager/administration > > pgm. > > Your guess (luckily for me) is very wrong. It's just that I call these > GUIs, not "DB managers". Ah... a terminology problem.. I'll just point out that the section in the wiki that has all of these pgms in it is called "Management Tools". > .. They're handy. I have a number of them > installed on the machine on which I write this, and I use them in > addition to command line tools. As I mentioned in another msg on this thread, we use phpMyAdmin for our MySQL databases at work and I can't remember the last time anyone at work needed (or wanted) to use the command line tool. Different strokes... > That's not a problem, is it? Just a question of using a tool at the > right level. If you want to automate things, writing code is often a > good way. If you want to do ad hoc work, a visual tool can be much more > convenient. Just so. Only difference between us here is that I have virtually no use for the command line tool: if I need to automate, I'll just write a little Perl/DBI pgm to do it (I have dozens of 'em..:o)) and I use the "visual tool" for everything else. I'd rather write a small Perl program that try to cobble up a script to be read into the command line app. As above, YMMV... > So you're looking for a graphical tool to allow you to manually view and > modify information in a SQLite3 database? Yes, and I'm now the happy user of sqliteman, so my search is over..:o). It's "query manager" does *exactly* what I needed and works wonderfully. (and indeed, easily found the index conflict I was trying to sort out easily.) /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] Strange error in sqlite 3.4.2 Win32 version
Can you post the output of this command when you compile
sqlite 3.4.2 with code warrior for your test.sqlite database?
explain SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
With the sqlite 3.5.1 shell compiled with gcc 4.1.1 I see:
0|Goto|0|25|
1|Integer|0|0|# One
2|OpenRead|0|2|
3|SetNumColumns|0|4|
4|Integer|0|0|# idx_One
5|OpenRead|1|4|keyinfo(3,BINARY,BINARY)
6|Integer|99|0|
7|IsNull|-1|22|
8|Integer|100|0|
9|IsNull|-2|22|
10|Integer|101|0|
11|IsNull|-3|22|
12|MakeRecord|3|0|ddd
13|MemStore|0|0|
14|MoveGe|1|22|
15|MemLoad|0|0|
16|IdxGE|1|22|+
17|IdxRowid|1|0|
18|MoveGe|0|0|
19|Column|0|0|# One.a
20|Callback|1|0|
21|Next|1|15|
22|Close|0|0|
23|Close|1|0|
24|Halt|0|0|
25|Transaction|0|0|
26|VerifyCookie|0|2|
27|TableLock|0|2|One
28|Goto|0|1|
29|Noop|0|0|
Just for the heck of it, can you also provide the code warrior/3.4.2
output for these commands as well?
-- select case 2
-- Getting all columns works
explain SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
-- select case 3
-- Not using whole index works
explain SELECT a FROM One WHERE b2 = 100 and b3 = 101;
-- select case 4
-- Getting one column, in the index, works
explain SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
--- Marco Bambini <[EMAIL PROTECTED]> wrote:
> To be really sure I rewrote the example in C linked to the official
> sqlite 3.4.2.
> Here it is my source code:
>
> #include
> #include
> #include
> #include "sqlite3.h"
>
> int main(void)
> {
> sqlite3 *db = NULL;
> int rc = SQLITE_OK;
> charsql[256];
> char**result;
> int i, nrow, ncol;
>
> // open db
> rc = sqlite3_open("test.sqlite", );
> if (rc != SQLITE_OK) goto abort;
>
> // create table
> rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
> integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
> if (rc != SQLITE_OK) goto abort;
>
> // create index
> rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
> b3);", NULL, 0, NULL);
> if (rc != SQLITE_OK) goto abort;
>
> // insert loop
> for (i=1; i<=100; i++)
> {
> snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
> 'A');", i, i+1, i+2, i+3);
> rc = sqlite3_exec(db, sql, NULL, 0, NULL);
> if (rc != SQLITE_OK) goto abort;
> }
>
> // query test 1
> rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
> 100 and b3 = 101;", , , , NULL);
> if (rc != SQLITE_OK) goto abort;
>
> for(i=0; i {
> printf(result[i]);
> printf("\t\t");
> }
> printf("\n");
>
> for(i=0; i {
> printf(result[ncol+i]);
> printf("\t\t");
> if (i % ncol == 0) printf("\n");
> }
>
> // free table
> sqlite3_free_table(result);
>
> // close db
> sqlite3_close(db);
>
> printf("simple test finished!\n");
> return 0;
>
> abort:
> printf("%s\n", sqlite3_errmsg(db));
> if (db != NULL) sqlite3_close(db);
> return -1;
> }
>
> On Windows (not on Mac!) it returns 99 instead of the correct 98 value.
> Anyone can confirm that on Windows?
>
> Thanks a lot.
> ---
> Marco Bambini
> http://www.sqlabs.net
> http://www.sqlabs.net/blog/
> http://www.sqlabs.net/realsqlserver/
>
>
>
> On Oct 30, 2007, at 5:15 PM, [EMAIL PROTECTED] wrote:
>
> > 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...
__
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] DB managers that do searches?
Bernie Cosell wrote: > 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. Your guess (luckily for me) is very wrong. It's just that I call these GUIs, not "DB managers". They're handy. I have a number of them installed on the machine on which I write this, and I use them in addition to command line tools. > 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!). That's not a problem, is it? Just a question of using a tool at the right level. If you want to automate things, writing code is often a good way. If you want to do ad hoc work, a visual tool can be much more convenient. > > 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]. So you're looking for a graphical tool to allow you to manually view and modify information in a SQLite3 database? -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Performance problem for a simple select with range
Doug <[EMAIL PROTECTED]> wrote: I'm not an SQL guru by any means, so seeing this made a light go on. Does that mean it is a good idea in the general case to always add "limit 1" to a select that you know should only return 1 row? That probably can't hurt (if you are sure the query would only ever return one row, of course). For many queries it would be redundant and won't improve the time at all, but I can't think of any way it could make it slower. I'm assuming this works because the engine can short-cut out as soon as it finds that first matching row. Correct. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance problem for a simple select with range
I'm not an SQL guru by any means, so seeing this made a light go on. Does that mean it is a good idea in the general case to always add "limit 1" to a select that you know should only return 1 row? I'm assuming this works because the engine can short-cut out as soon as it finds that first matching row. > -Original Message- > From: Dani Va [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 31, 2007 8:30 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Performance problem for a simple select with range > > > First, thanks, your suggestion worked. > > To my surprise, it was enough to add "limit 1" to the original query. > > So: > > select * from blocks,locations where locations.locid = blocks.locid AND ? >= > blocks.startIpNum AND ? <= blocks.endIpNum limit 1 > takes about 1.398-005 seconds > > and > > select * from blocks,locations where locations.locid = blocks.locid AND ? >= > blocks.startIpNum AND ? <= blocks.endIpNum > takes about 3 seconds. > > > > > > Igor Tandetnik wrote: > > > > Dani Valevski <[EMAIL PROTECTED]> wrote: > >>> I think I have a performance problem for a simple select with range. > >>> > >>> My Tables: > >>> CREATE TABLE locations( > >>>locidINTEGER PRIMARY KEY, > >>>country TEXT, > >>>regionTEXT, > >>>cityTEXT, > >>>postalCode TEXT, > >>>latitude REAL, > >>>longitude REAL, > >>>dmaCode INTEGER, > >>>areaCode INTEGER) > >>> > >>> CREATE TABLE blocks( > >>>startIpNum INTEGER, > >>>endIpNum INTEGER, > >>>locId INTEGER) > >>> > >>> My Data: > >>> http://www.maxmind.com/app/geolitecity > >>> Blocks table has 2,776,436 rows > >>> Locations table has 159,488 rows > >>> > >>> After inserting the data I run analyze. > >>> > >>> My Query: > >>> select * from blocks,locations where locations.locid = blocks.locid > >>> AND ? >= blocks.startIpNum AND ? <= blocks.endIpNum > >>> (replace ? with a number) > >>> > >>> Performance issues: > >>> I use python's sqlite3 module to run the query. > >>> With this configuration it takes about 0.6 seconds to complete the > >>> query. I > >>> think this is too slow. I could write a binary tree myself and have > >>> searches > >>> like this take, O(log(num_rows)) which is > >>> 7*something_which_shouldnt_take_too_much. Am I wrong? > > > > And what would you use as a key for this binary tree? I bet you would > > utilize additional information that the DB engine doesn't have - that > > your blocks don't overlap (they don't, right?) Try coming up with a > > search strategy without making this assumption. > > > > Try this: create an index on startIpNum, and run a query like this: > > > > select * from blocks, locations > > where blocks.startIpNum <= ? and blocks.locid = locations.locid > > order by blocks.startIpNum desc limit 1; > > > > This gives you the record with the largest value of startIpNum that is > > still smaller than the threshold, and should be very fast. It can > > produce a false positive - make the additional check for (? <= > > startIpEnd) in your application code. Don't put this check into the > > query though, or you will force it back into O(N) behavior in case your > > target value doesn't fall within any block after all. > > > > Igor Tandetnik > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > -- > View this message in context: http://www.nabble.com/Performance-problem-for-a- > simple-select-with-range-tf4711654.html#a13509241 > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Performance problem for a simple select with range
Dani Va <[EMAIL PROTECTED]> wrote: First, thanks, your suggestion worked. To my surprise, it was enough to add "limit 1" to the original query. Try searching for a value that doesn't fall into any block - you'll likely find that the query takes a noticeable time to produce zero records. Pick a large value that's greater than all startIpNum's. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance problem for a simple select with range
First, thanks, your suggestion worked. To my surprise, it was enough to add "limit 1" to the original query. So: select * from blocks,locations where locations.locid = blocks.locid AND ? >= blocks.startIpNum AND ? <= blocks.endIpNum limit 1 takes about 1.398-005 seconds and select * from blocks,locations where locations.locid = blocks.locid AND ? >= blocks.startIpNum AND ? <= blocks.endIpNum takes about 3 seconds. Igor Tandetnik wrote: > > Dani Valevski <[EMAIL PROTECTED]> wrote: >>> I think I have a performance problem for a simple select with range. >>> >>> My Tables: >>> CREATE TABLE locations( >>>locidINTEGER PRIMARY KEY, >>>country TEXT, >>>regionTEXT, >>>cityTEXT, >>>postalCode TEXT, >>>latitude REAL, >>>longitude REAL, >>>dmaCode INTEGER, >>>areaCode INTEGER) >>> >>> CREATE TABLE blocks( >>>startIpNum INTEGER, >>>endIpNum INTEGER, >>>locId INTEGER) >>> >>> My Data: >>> http://www.maxmind.com/app/geolitecity >>> Blocks table has 2,776,436 rows >>> Locations table has 159,488 rows >>> >>> After inserting the data I run analyze. >>> >>> My Query: >>> select * from blocks,locations where locations.locid = blocks.locid >>> AND ? >= blocks.startIpNum AND ? <= blocks.endIpNum >>> (replace ? with a number) >>> >>> Performance issues: >>> I use python's sqlite3 module to run the query. >>> With this configuration it takes about 0.6 seconds to complete the >>> query. I >>> think this is too slow. I could write a binary tree myself and have >>> searches >>> like this take, O(log(num_rows)) which is >>> 7*something_which_shouldnt_take_too_much. Am I wrong? > > And what would you use as a key for this binary tree? I bet you would > utilize additional information that the DB engine doesn't have - that > your blocks don't overlap (they don't, right?) Try coming up with a > search strategy without making this assumption. > > Try this: create an index on startIpNum, and run a query like this: > > select * from blocks, locations > where blocks.startIpNum <= ? and blocks.locid = locations.locid > order by blocks.startIpNum desc limit 1; > > This gives you the record with the largest value of startIpNum that is > still smaller than the threshold, and should be very fast. It can > produce a false positive - make the additional check for (? <= > startIpEnd) in your application code. Don't put this check into the > query though, or you will force it back into O(N) behavior in case your > target value doesn't fall within any block after all. > > Igor Tandetnik > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Performance-problem-for-a-simple-select-with-range-tf4711654.html#a13509241 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query optimization
Igor Tandetnik wrote: > > Reordering LEFT JOIN changes the meaning of the statement. You don't > want your DBMS to do that to you behind your back. Make sure you know > what you are doing, and that the reordered statement still does what > it's supposed to do. Again, (A LEFT JOIN B) produces different results, > in general, than (B LEFT JOIN A). > > Igor Tandetnik > Ah, good point. I overlooked this because in this instance the outer joins actually have the semantics of inner joins (i.e. I know the tables have the same entries with the same primary keys). I'm using outer joins because the query is generated using a generic framework which might need outer joins in some cases. But considering the implications of our approach for the query optimizer, I'll rewrite it so it uses inner joins and handles the other cases in some other way. Cheers, Matt -- View this message in context: http://www.nabble.com/Query-optimization-tf4724113.html#a13508442 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Query optimization
Matthew Gertner wrote: I'm running the following query: SELECT DISTINCT _Resource.ResourceType, _Resource.Id, _Resource.OwnerId, _Resource.Created, _Resource.Modified, _Resource.Name, _Resource.ParentId, _Resource.Version, _Resource.Description, _Resource.Creator, _File.Size, _File.MimeType, _File.OriginURI, _File.OriginMimeType, _File.Hash, _File.ContentRating, _File.OriginalExtension, _File.Thumbnail, _File.Data, _File.TorrentInfoHash, _Resource_Metadata.New, _File_Metadata.IsPublished, _File_Metadata.ReceiveStatus, _File_Metadata.ShareMessageId, _Audio.Length, _Audio.BitRate, _Image.Height, _Image.Width, _Image.ColorsUsed, _Text.Summary, _Video.Height, _Video.Width, _Video.Length, _Video.FrameRate, _Video.DataRate, _Video.SampleSize FROM _File LEFT OUTER JOIN _Resource ON _File.Id=_Resource.Id LEFT OUTER JOIN _Resource_Metadata ON _Resource_Metadata.ParentId=_Resource.Id LEFT OUTER JOIN _File_Metadata ON _File_Metadata.Id=_Resource_Metadata.Id LEFT OUTER JOIN _Audio ON _File.Id=_Audio.Id LEFT OUTER JOIN _Image ON _File.Id=_Image.Id LEFT OUTER JOIN _Text ON _File.Id=_Text.Id LEFT OUTER JOIN _Video ON _File.Id=_Video.Id LEFT OUTER JOIN _Source ON _Source.ParentId=_Resource_Metadata.Id LEFT OUTER JOIN _Source_PeerCommonName ON _Source_PeerCommonName.ResourceId=_Source.Id WHERE _Source_PeerCommonName.Value=? AND _File_Metadata.Id IN (SELECT Id FROM _File_Metadata WHERE ReceiveStatus=?) EXPLAIN QUERY PLAN tells me that a full scan is being performed on the _File table, which is the first in the FROM clause. When I reorder the joins so that _File_Metadata is first in the FROM clause, then its primary key index (on Id) is used, as expected (since Id is used in the WHERE clause). I would have expected SQLite's query optimizer to reorder the joins automatically to use an index rather than a full table scan. Reordering LEFT JOIN changes the meaning of the statement. You don't want your DBMS to do that to you behind your back. Make sure you know what you are doing, and that the reordered statement still does what it's supposed to do. Again, (A LEFT JOIN B) produces different results, in general, than (B LEFT JOIN A). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
Hmm ... I was using CodeWarrior for Windows ... maybe its time to upgrade --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 12:14 PM, Dan Petitt wrote: I compiled up your code and ran it on Windows using VC6 and got: a 98 Hope this helps Dan -Original Message- From: Marco Bambini [mailto:[EMAIL PROTECTED] Sent: 31 October 2007 09:33 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version No, the database is created by the code it doesn't already exists. I was able to reproduce the issue only on Windows, Mac and Linux worked fine. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:25 AM, Stephan Beal wrote: On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: ... On Windows (not on Mac!) it returns 99 instead of the correct 98 value. Anyone can confirm that on Windows? Hi, Marco! While i can't confirm how it behaves under Windows, i can confirm that it returns 98 on Linux: [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib - lsqlite3 [EMAIL PROTECTED]:~/tmp$ ./win a 98 simple test finished! i quickly scanned through your code and found no reason that 99 should come up. One thing to check: does your test.sqlite DB already exist o your windows box, with a record already in it? That would explain the discrepancy (but if that were the case, the CREATE TABLE call should fail, so that's probably not the problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ -- -- - To unsubscribe, send email to [EMAIL PROTECTED] -- -- - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Strange error in sqlite 3.4.2 Win32 version
I compiled up your code and ran it on Windows using VC6 and got: a 98 Hope this helps Dan -Original Message- From: Marco Bambini [mailto:[EMAIL PROTECTED] Sent: 31 October 2007 09:33 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version No, the database is created by the code it doesn't already exists. I was able to reproduce the issue only on Windows, Mac and Linux worked fine. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:25 AM, Stephan Beal wrote: > On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: >> >> ... >> On Windows (not on Mac!) it returns 99 instead of the correct 98 >> value. >> Anyone can confirm that on Windows? > > > Hi, Marco! While i can't confirm how it behaves under Windows, i can > confirm that it returns 98 on Linux: > > [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib - > lsqlite3 > [EMAIL PROTECTED]:~/tmp$ ./win > a > 98 > simple test finished! > > i quickly scanned through your code and found no reason that 99 should > come up. > > One thing to check: does your test.sqlite DB already exist o your > windows box, with a record already in it? That would explain the > discrepancy (but if that were the case, the CREATE TABLE call should > fail, so that's probably not the problem). > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Query optimization
I'm running the following query: SELECT DISTINCT _Resource.ResourceType, _Resource.Id, _Resource.OwnerId, _Resource.Created, _Resource.Modified, _Resource.Name, _Resource.ParentId, _Resource.Version, _Resource.Description, _Resource.Creator, _File.Size, _File.MimeType, _File.OriginURI, _File.OriginMimeType, _File.Hash, _File.ContentRating, _File.OriginalExtension, _File.Thumbnail, _File.Data, _File.TorrentInfoHash, _Resource_Metadata.New, _File_Metadata.IsPublished, _File_Metadata.ReceiveStatus, _File_Metadata.ShareMessageId, _Audio.Length, _Audio.BitRate, _Image.Height, _Image.Width, _Image.ColorsUsed, _Text.Summary, _Video.Height, _Video.Width, _Video.Length, _Video.FrameRate, _Video.DataRate, _Video.SampleSize FROM _File LEFT OUTER JOIN _Resource ON _File.Id=_Resource.Id LEFT OUTER JOIN _Resource_Metadata ON _Resource_Metadata.ParentId=_Resource.Id LEFT OUTER JOIN _File_Metadata ON _File_Metadata.Id=_Resource_Metadata.Id LEFT OUTER JOIN _Audio ON _File.Id=_Audio.Id LEFT OUTER JOIN _Image ON _File.Id=_Image.Id LEFT OUTER JOIN _Text ON _File.Id=_Text.Id LEFT OUTER JOIN _Video ON _File.Id=_Video.Id LEFT OUTER JOIN _Source ON _Source.ParentId=_Resource_Metadata.Id LEFT OUTER JOIN _Source_PeerCommonName ON _Source_PeerCommonName.ResourceId=_Source.Id WHERE _Source_PeerCommonName.Value=? AND _File_Metadata.Id IN (SELECT Id FROM _File_Metadata WHERE ReceiveStatus=?) EXPLAIN QUERY PLAN tells me that a full scan is being performed on the _File table, which is the first in the FROM clause. When I reorder the joins so that _File_Metadata is first in the FROM clause, then its primary key index (on Id) is used, as expected (since Id is used in the WHERE clause). I would have expected SQLite's query optimizer to reorder the joins automatically to use an index rather than a full table scan. Am I doing something wrong or do I have to reorder the joins myself to optimize index usage? -- View this message in context: http://www.nabble.com/Query-optimization-tf4724113.html#a13506536 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
No, the database is created by the code it doesn't already exists. I was able to reproduce the issue only on Windows, Mac and Linux worked fine. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:25 AM, Stephan Beal wrote: On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: ... On Windows (not on Mac!) it returns 99 instead of the correct 98 value. Anyone can confirm that on Windows? Hi, Marco! While i can't confirm how it behaves under Windows, i can confirm that it returns 98 on Linux: [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib - lsqlite3 [EMAIL PROTECTED]:~/tmp$ ./win a 98 simple test finished! i quickly scanned through your code and found no reason that 99 should come up. One thing to check: does your test.sqlite DB already exist o your windows box, with a record already in it? That would explain the discrepancy (but if that were the case, the CREATE TABLE call should fail, so that's probably not the problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: > > ... > On Windows (not on Mac!) it returns 99 instead of the correct 98 value. > Anyone can confirm that on Windows? Hi, Marco! While i can't confirm how it behaves under Windows, i can confirm that it returns 98 on Linux: [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib -lsqlite3 [EMAIL PROTECTED]:~/tmp$ ./win a 98 simple test finished! i quickly scanned through your code and found no reason that 99 should come up. One thing to check: does your test.sqlite DB already exist o your windows box, with a record already in it? That would explain the discrepancy (but if that were the case, the CREATE TABLE call should fail, so that's probably not the problem). -- - stephan beal http://wanderinghorse.net/home/stephan/
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
To be really sure I rewrote the example in C linked to the official
sqlite 3.4.2.
Here it is my source code:
#include
#include
#include
#include "sqlite3.h"
int main(void)
{
sqlite3 *db = NULL;
int rc = SQLITE_OK;
charsql[256];
char**result;
int i, nrow, ncol;
// open db
rc = sqlite3_open("test.sqlite", );
if (rc != SQLITE_OK) goto abort;
// create table
rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// create index
rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
b3);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// insert loop
for (i=1; i<=100; i++)
{
snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
'A');", i, i+1, i+2, i+3);
rc = sqlite3_exec(db, sql, NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
}
// query test 1
rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
100 and b3 = 101;", , , , NULL);
if (rc != SQLITE_OK) goto abort;
for(i=0; i 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]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Proposed sqlite3_initialize() interface
On Oct 30, 2007 6:32 PM, Russell Leighton <[EMAIL PROTECTED]> wrote: > > 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: You may have noticed that the purpose of this function is to return some useful failure information on platforms which can err. -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
