Re: [sqlite] Race condition -- fixed?

[Trevor Talbot]

I wrote:

> I would still perform rollbacks for any errors other than the above
> expected SQLITE_BUSY cases, of course, since they indicate something
> else went wrong (such as running out of disk space).  I think it's
> safe to say those are all unusual cases though.

Hmm, http://sqlite.org/lang_transaction.html suggests even SQLITE_BUSY
might result in automatic transaction rollback.  sqlite3_step()
suggests the same thing.

Other docs suggest that SQLITE_IOERR_BLOCKED is actually returned in
such cases (like a writing transaction failing a cache spill attempt).

Which is correct?  Silently rolling back a transaction automatically
is bad enough, but doing that on SQLITE_BUSY would be just plain evil.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Race condition -- fixed?

[Trevor Talbot]

On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote:

> Perhaps the best solution is to follow these rules:
>
> IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db):
> --
> (1) Begin the transaction with 'BEGIN IMMEDIATE'.  If you get SQLITE_
> BUSY, it means that another writer is already accessing the db.  Just
> sleep awhile and retry, as many times as necessary.  Once you get
> SQLITE_OK, you're the only writer accessing the db.
>
> (2) If you get SQLITE_BUSY later in the transaction when you want to
> write your cache to disk, it can only be due to one or more readers
> holding SHARED locks (because you're the only writer).  They will
> eventually exit without trying to acquire any stronger locks (again,
> because you're the only writer).  Therefore, no deadlock is possible.
> Just sleep awhile and retry, as many times as necessary.
>
> IF THE TRANSACTION IS A READER (i.e. will only read from the db):
> -
> (1) Begin the transaction with a simple 'BEGIN'.  This starts the
> transaction in the UNLOCKED state, so you *cannot* get a SQLITE_
> BUSY at this point.
>
> (2) If you get SQLITE_BUSY later in the transaction, it can only be
> due to your trying to acquire a SHARED lock while some other process
> holds a PENDING or EXCLUSIVE lock.  That process will eventually
> finish its writing and exit.  Therefore, no deadlock is possible.
> Just sleep awhile and retry, as many times as necessary.
>
>
> If all transactions follow the above rules, then explicit rollbacks
> should never be necessary.

For avoiding deadlocks, yes.  It can also be abstracted so the rest of
your code works on just "read-only" and "read/write" transactions,
which is convenient.

I would still perform rollbacks for any errors other than the above
expected SQLITE_BUSY cases, of course, since they indicate something
else went wrong (such as running out of disk space).  I think it's
safe to say those are all unusual cases though.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Race condition -- fixed?

[Richard Klein]

Trevor Talbot wrote:

On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote:


But am I correct in assuming that one way that SQLite provides
serializable transactions is by automatically rolling back
transactions when necessary (and returning SQLITE_IOERR)?


No.  That will happen in some specific unavoidable cases, but you
cannot assume it will happen all of the time.


I assume you are referring to the case discussed in the article:

http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError

i.e. the case where a non-COMMIT command within an explicit trans-
action fails when the executing process is unable to spill its cache
to disk due to a SQLITE_BUSY error, resulting in an inconsistent cache.


What will most likely
happen instead is that the first INSERT or UPDATE in a transaction
will return SQLITE_BUSY, and you will have to roll back the
transaction yourself.

It's also possible to simply keep
retrying a failing statement until it succeeds, but in the case of
INSERT or UPDATE that may lead to deadlock, as two connections that
want to write cannot proceed until one of them yields.


Perhaps the best solution is to follow these rules:

IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db):
--
(1) Begin the transaction with 'BEGIN IMMEDIATE'.  If you get SQLITE_
BUSY, it means that another writer is already accessing the db.  Just
sleep awhile and retry, as many times as necessary.  Once you get
SQLITE_OK, you're the only writer accessing the db.

(2) If you get SQLITE_BUSY later in the transaction when you want to
write your cache to disk, it can only be due to one or more readers
holding SHARED locks (because you're the only writer).  They will
eventually exit without trying to acquire any stronger locks (again,
because you're the only writer).  Therefore, no deadlock is possible.
Just sleep awhile and retry, as many times as necessary.

IF THE TRANSACTION IS A READER (i.e. will only read from the db):
-
(1) Begin the transaction with a simple 'BEGIN'.  This starts the
transaction in the UNLOCKED state, so you *cannot* get a SQLITE_
BUSY at this point.

(2) If you get SQLITE_BUSY later in the transaction, it can only be
due to your trying to acquire a SHARED lock while some other process
holds a PENDING or EXCLUSIVE lock.  That process will eventually
finish its writing and exit.  Therefore, no deadlock is possible.
Just sleep awhile and retry, as many times as necessary.


If all transactions follow the above rules, then explicit rollbacks
should never be necessary.

- Richard Klein

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance problem for a simple select with range

[drh]

"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, ...);
> 
> CREATE TABLE blocks(
> startIpNum INTEGER,
> endIpNum INTEGER,
> locId INTEGER)
> 
> My Data:
> Blocks table has 2,776,436 rows
> Locations table has 159,488 rows
> 
> My Query:
> select * from blocks,locations where locations.locid = blocks.locid AND ? >=
> blocks.startIpNum AND ? <= blocks.endIpNum
> (replace ? with a number)
> 

To do searches of this kind with maximum efficiency, you normally
want to use a 1-dimensional R-Tree index.  SQLite does not support
RTree indices natively, though it is conceivable that you could
write a RTree virtual table extension for SQLite.

Without an RTree index, and unless you can exploit the distribution
of data in the blocks table, you really cannot do much better than a
full table scan on blocks with an indexed lookup of locations for
each matching block.  That is probably what is happening on your
original query before you added indices.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Re: Question on SQL to retrieve ROWIDs

[Dennis Volodomanov]

Yes, that looks good as well - thank you for the help!

  Dennis



Igor Tandetnik wrote:

Dennis Volodomanov  wrote:

Igor Tandetnik wrote:

Dennis Volodomanov 
wrote:

Let's say I have a simple schema:

CREATE TABLE MyTable ( ID INTEGER PRIMARY KEY, SomeData )

What I'd like to get is 5 records (for example) that are immediately
before a given ID (where before means their ID is less than).


select * from MyTable
where ID < ?
order by ID desc limit 5;


Ah, thank you! I had a similar one but was doing an "asc" to get the
order I wanted, which doesn't make sense - I should sort the results
after I get them on my own.


You could do something like this:

select * from MyTable
where ID in (
   select ID from MyTable
   where ID < ?
   order by ID desc limit 5
)
order by ID asc;  -- or whatever order you want

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Race condition -- fixed?

[Trevor Talbot]

On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote:

> But am I correct in assuming that one way that SQLite provides
> serializable transactions is by automatically rolling back
> transactions when necessary (and returning SQLITE_IOERR)?

No.  That will happen in some specific unavoidable cases, but you
cannot assume it will happen all of the time.  What will most likely
happen instead is that the first INSERT or UPDATE in a transaction
will return SQLITE_BUSY, and you will have to roll back the
transaction yourself.

It's possible to continue within a transaction by ignoring an error
from an individual statement (depending on the nature of the error),
is why I make this distinction.  It's also possible to simply keep
retrying a failing statement until it succeeds, but in the case of
INSERT or UPDATE that may lead to deadlock, as two connections that
want to write cannot proceed until one of them yields.

On the other hand, if you get SQLITE_BUSY on COMMIT, you probably want
to retry it, as it's just waiting for other readers to go away first.
Automatically rolling back then would be bad :)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: Question on SQL to retrieve ROWIDs

[Igor Tandetnik]

Dennis Volodomanov  wrote:

Igor Tandetnik wrote:

Dennis Volodomanov 
wrote:

Let's say I have a simple schema:

CREATE TABLE MyTable ( ID INTEGER PRIMARY KEY, SomeData )

What I'd like to get is 5 records (for example) that are immediately
before a given ID (where before means their ID is less than).


select * from MyTable
where ID < ?
order by ID desc limit 5;


Ah, thank you! I had a similar one but was doing an "asc" to get the
order I wanted, which doesn't make sense - I should sort the results
after I get them on my own.


You could do something like this:

select * from MyTable
where ID in (
   select ID from MyTable
   where ID < ?
   order by ID desc limit 5
)
order by ID asc;  -- or whatever order you want

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Race condition -- fixed?

[Ken]

Its up to you to rollback the transaction.
It would return a SQLITE_BUSY, not an IOERR.



Richard Klein <[EMAIL PROTECTED]> wrote: But am I correct in assuming that one 
way that SQLite provides
serializable transactions is by automatically rolling back
transactions when necessary (and returning SQLITE_IOERR)?

Thanks,
- Richard Klein


[EMAIL PROTECTED] wrote:
> Ken  wrote:
>> BEGIN TRANSACTION;
>> SELECT balance FROM accounts WHERE accountId = '123-45-6789';
>>  UPDATE accounts SET balance = 
>>   WHERE accountId = '123-45-6789';
>> COMMIT;
>>
>> This is a comman and naive assumption that the balance selected 
>> will remain consistent.
>>
> 
> Actually, SQLite does provide this guarantee.  Nothing in
> the database will change during a transaction, except for
> changes caused by INSERT, UPDATE, and DELETE statements
> that occur within the transaction itself.  It is not
> possible for another process to modify the value of the
> "balance" in between the SELECT and the UPDATE in the
> SQL above.
> 
> This is true of SQLite because isolation in SQLite
> is "SERIALIZABLE".  This is the highest level of isolate
> provided by SQL.  Most client/server database engines
> by default implement "READ COMMITTED".  The value of
> "balance" might change between the SELECT and the
> UPDATE in MySQL, for example.  (I'm less clear about
> what happens in PostgreSQL and Oracle.  The point is
> that your mileage may vary so be cautious.)
> 
> But SQLite gets this right.  Transactions are fully
> serializable, which means they appear as if the entire
> transaction happens instanteously with no chance for
> outside processes to change values in the middle of a
> transaction.
> 
> --
> D. Richard Hipp 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Question on SQL to retrieve ROWIDs

[Dennis Volodomanov]

Ah, thank you! I had a similar one but was doing an "asc" to get the 
order I wanted, which doesn't make sense - I should sort the results 
after I get them on my own.


  Dennis



Igor Tandetnik wrote:

Dennis Volodomanov  wrote:

Hello all,

I can't seem to figure out a working SQL for the following condition,
and I'm not sure if it's possible in SQL at all (but hope it is).

Let's say I have a simple schema:

CREATE TABLE MyTable ( ID INTEGER PRIMARY KEY, SomeData )

What I'd like to get is 5 records (for example) that are immediately
before a given ID (where before means their ID is less than).


select * from MyTable
where ID < ?
order by ID desc limit 5;

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Question on SQL to retrieve ROWIDs

[Dennis Volodomanov]

Hello all,

I can't seem to figure out a working SQL for the following condition, 
and I'm not sure if it's possible in SQL at all (but hope it is).


Let's say I have a simple schema:

CREATE TABLE MyTable ( ID INTEGER PRIMARY KEY, SomeData )

What I'd like to get is 5 records (for example) that are immediately 
before a given ID (where before means their ID is less than).


For example, with the sample data:

1, a
2, b
3, c
4, d
5, e
6, f
7, g
8, h

and the ID = 8, I'd like to get:

h, g, f, e, d

I'd appreciate any help on this!

Thank you in advance,

  Dennis



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Race condition -- fixed?

[Richard Klein]

But am I correct in assuming that one way that SQLite provides
serializable transactions is by automatically rolling back
transactions when necessary (and returning SQLITE_IOERR)?

Thanks,
- Richard Klein


[EMAIL PROTECTED] wrote:

Ken <[EMAIL PROTECTED]> wrote:

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE accountId = '123-45-6789';
 UPDATE accounts SET balance = 
  WHERE accountId = '123-45-6789';
COMMIT;

This is a comman and naive assumption that the balance selected 
will remain consistent.




Actually, SQLite does provide this guarantee.  Nothing in
the database will change during a transaction, except for
changes caused by INSERT, UPDATE, and DELETE statements
that occur within the transaction itself.  It is not
possible for another process to modify the value of the
"balance" in between the SELECT and the UPDATE in the
SQL above.

This is true of SQLite because isolation in SQLite
is "SERIALIZABLE".  This is the highest level of isolate
provided by SQL.  Most client/server database engines
by default implement "READ COMMITTED".  The value of
"balance" might change between the SELECT and the
UPDATE in MySQL, for example.  (I'm less clear about
what happens in PostgreSQL and Oracle.  The point is
that your mileage may vary so be cautious.)

But SQLite gets this right.  Transactions are fully
serializable, which means they appear as if the entire
transaction happens instanteously with no chance for
outside processes to change values in the middle of a
transaction.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[John Stanton]

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]
-

Re: [sqlite] Retrieve bound host parameters from statement?

[Ralf Junker]

>> 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.

>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]
-

AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

This does look like a solution indeed. I'll try this one later.

Thank you!
Mike 

-Ursprüngliche Nachricht-
Von: Simon Davies [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 29. Oktober 2007 16:40
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

On 29/10/2007, Odekirk, Shawn <[EMAIL PROTECTED]> wrote:
> Adding "primary key" to column "a" results in the behavior I think you
> were first expecting.
>
> sqlite> create table tmp (a integer primary key, b integer);
> sqlite> create unique index tmpIndex on tmp (a, b);
> sqlite> insert into tmp values (1, 1);
> sqlite> insert into tmp values (2, 2);
> sqlite> select last_insert_rowid();
> 2
> sqlite> insert or replace into tmp values (1, 1);
> sqlite> select last_insert_rowid();
> 1
> sqlite> select * from tmp;
> 1|1
> 2|2
>
> I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve
> his problem.
>
> Shawn
>

That was the result I was seeking (as a potential answer to Michael's
problem).

Looking at Michael's original post, he has declared an INTEGER PRIMARY
KEY column, so "OR REPLACE" instead of "OR IGNORE" could be the
solution as you suggest.

Rgds,
Simon


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

Ok, thanks I haven't seen this function. I'll try it. 

Mike

-Ursprüngliche Nachricht-
Von: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 29. Oktober 2007 17:01
An: SQLite
Betreff: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()

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

sqlite3_changes

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Igor Tandetnik]

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


sqlite3_changes

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Performance problem for a simple select with range

[Igor Tandetnik]

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]
-

Re: [sqlite] Performance problem for a simple select with range

[Kees Nuyt]

[Default] On Mon, 29 Oct 2007 15:25:18 +0200, "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)
>
>Disclaimer:
>I'm a bit new to databases.
>
>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? (see the disclaimer)
>
>Anyway, I thought the problem was that startIpNum, endIpNum are not indexed.
>So I added indices for them (even tried indexing them both). This only makes
>the query take about 3 seconds.
>Ideas anyone?
>
>Source:
>is attached.
>
>Thank you for your help

Just some suggestions:
Index locid in both tables, and rewrite

> select *
>   from blocks,locations 
>  where locations.locid = blocks.locid
>  AND ? >= blocks.startIpNum 
>  AND ? <= blocks.endIpNum

to:

 select *
   from blocks
  INNER JOIN locations USING (locid)
  where ? >= blocks.startIpNum 
AND ? <= blocks.endIpNum

or:

 select *
   from locations
  INNER JOIN blocks USING (locid)
  where ? >= blocks.startIpNum 
AND ? <= blocks.endIpNum

HTH
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Simon Davies]

On 29/10/2007, Odekirk, Shawn <[EMAIL PROTECTED]> wrote:
> Adding "primary key" to column "a" results in the behavior I think you
> were first expecting.
>
> sqlite> create table tmp (a integer primary key, b integer);
> sqlite> create unique index tmpIndex on tmp (a, b);
> sqlite> insert into tmp values (1, 1);
> sqlite> insert into tmp values (2, 2);
> sqlite> select last_insert_rowid();
> 2
> sqlite> insert or replace into tmp values (1, 1);
> sqlite> select last_insert_rowid();
> 1
> sqlite> select * from tmp;
> 1|1
> 2|2
>
> I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve
> his problem.
>
> Shawn
>

That was the result I was seeking (as a potential answer to Michael's problem).

Looking at Michael's original post, he has declared an INTEGER PRIMARY
KEY column, so "OR REPLACE" instead of "OR IGNORE" could be the
solution as you suggest.

Rgds,
Simon

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Kees Nuyt]

[Default] On Mon, 29 Oct 2007 15:00:51 +0100, "Michael Ruck"
<[EMAIL PROTECTED]> 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

Perhaps
http://www.sqlite.org/capi3ref.html#sqlite3_update_hook
can help you solve your problem?
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Retrieve bound host parameters from statement?

[Joe Wilson]

--- Ralf Junker <[EMAIL PROTECTED]> 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. 

> 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.

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 */



__
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] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Odekirk, Shawn]

Adding "primary key" to column "a" results in the behavior I think you
were first expecting.

sqlite> create table tmp (a integer primary key, b integer);
sqlite> create unique index tmpIndex on tmp (a, b);
sqlite> insert into tmp values (1, 1);
sqlite> insert into tmp values (2, 2);
sqlite> select last_insert_rowid();
2
sqlite> insert or replace into tmp values (1, 1);
sqlite> select last_insert_rowid();
1
sqlite> select * from tmp;
1|1
2|2

I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve
his problem.

Shawn

-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 11:02 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Thanks for the explanation!

On 29/10/2007, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Simon Davies wrote:
> > Following this thread, I was experimenting with last_insert_rowid(),
> > and found the following, which does not look right:
> >
> > SQLite version 3.4.2
> > Enter ".help" for instructions
> > sqlite>
> > sqlite> create table tmp( a integer, b integer );
> > sqlite> create unique index tmpIndex on tmp( a, b );
> > sqlite> insert into tmp values( 1, 1 );
> > sqlite> insert into tmp values( 2, 2 );
> > sqlite> select last_insert_rowid();
> > 2
> > sqlite>
> > sqlite> insert or replace into tmp values( 1, 1 );
> > sqlite> select last_insert_rowid();
> > 3
> > <-- !!!???!!!
> > sqlite> select * from tmp;
> > 2|2
> > 1|1
> > sqlite>
> >
> >
> >
> >
> Simon,
>
> If you change your query to;
>
>select rowid, * from tmp;
>
> it will display the rowid which is different than either of the fields
> in the table.
>
> When doing a replace sqlite deletes the existing row and adds a new
row.
>
> HTH
> Dennis Cote
>
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Odekirk, Shawn]

This link gives a little more information:
http://www.sqlite.org/autoinc.html

Shawn

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 29, 2007 10:57 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Simon Davies wrote:
> Following this thread, I was experimenting with last_insert_rowid(),
> and found the following, which does not look right:
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table tmp( a integer, b integer );
> sqlite> create unique index tmpIndex on tmp( a, b );
> sqlite> insert into tmp values( 1, 1 );
> sqlite> insert into tmp values( 2, 2 );
> sqlite> select last_insert_rowid();
> 2
> sqlite>
> sqlite> insert or replace into tmp values( 1, 1 );
> sqlite> select last_insert_rowid();
> 3
> <-- !!!???!!!
> sqlite> select * from tmp;
> 2|2
> 1|1
> sqlite>
>
>
>
>   
Simon,

If you change your query to;

select rowid, * from tmp;

it will display the rowid which is different than either of the fields 
in the table.

When doing a replace sqlite deletes the existing row and adds a new row.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Simon Davies]

Thanks for the explanation!

On 29/10/2007, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Simon Davies wrote:
> > Following this thread, I was experimenting with last_insert_rowid(),
> > and found the following, which does not look right:
> >
> > SQLite version 3.4.2
> > Enter ".help" for instructions
> > sqlite>
> > sqlite> create table tmp( a integer, b integer );
> > sqlite> create unique index tmpIndex on tmp( a, b );
> > sqlite> insert into tmp values( 1, 1 );
> > sqlite> insert into tmp values( 2, 2 );
> > sqlite> select last_insert_rowid();
> > 2
> > sqlite>
> > sqlite> insert or replace into tmp values( 1, 1 );
> > sqlite> select last_insert_rowid();
> > 3
> > <-- !!!???!!!
> > sqlite> select * from tmp;
> > 2|2
> > 1|1
> > sqlite>
> >
> >
> >
> >
> Simon,
>
> If you change your query to;
>
>select rowid, * from tmp;
>
> it will display the rowid which is different than either of the fields
> in the table.
>
> When doing a replace sqlite deletes the existing row and adds a new row.
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Dennis Cote]

Simon Davies wrote:

Following this thread, I was experimenting with last_insert_rowid(),
and found the following, which does not look right:

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> create table tmp( a integer, b integer );
sqlite> create unique index tmpIndex on tmp( a, b );
sqlite> insert into tmp values( 1, 1 );
sqlite> insert into tmp values( 2, 2 );
sqlite> select last_insert_rowid();
2
sqlite>
sqlite> insert or replace into tmp values( 1, 1 );
sqlite> select last_insert_rowid();
3
<-- !!!???!!!
sqlite> select * from tmp;
2|2
1|1
sqlite>



  

Simon,

If you change your query to;

   select rowid, * from tmp;

it will display the rowid which is different than either of the fields 
in the table.


When doing a replace sqlite deletes the existing row and adds a new row.

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Dennis Cote]

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,

It seems to me that you have asked for exactly that behavior by using 
INSERT OR IGNORE. By using this clause you have said you don't care if a 
row is inserted or not. Why would you expect an error code?


If you need to know if the insert fails then you should use INSERT OR 
FAIL. When it fails you will be notified, and you can handle the failure 
in an appropriate manner.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Simon Davies]

Hi All,

Following this thread, I was experimenting with last_insert_rowid(),
and found the following, which does not look right:

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> create table tmp( a integer, b integer );
sqlite> create unique index tmpIndex on tmp( a, b );
sqlite> insert into tmp values( 1, 1 );
sqlite> insert into tmp values( 2, 2 );
sqlite> select last_insert_rowid();
2
sqlite>
sqlite> insert or replace into tmp values( 1, 1 );
sqlite> select last_insert_rowid();
3
<-- !!!???!!!
sqlite> select * from tmp;
2|2
1|1
sqlite>

Rgds,
Simon

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Mechanism for pre-allocating contiguous files for Sqlite?

[Mark Spiegel]

I'm writing a VFS right now.  One of the parameters to the open is the 
file type (various types of DBs and journals).  You should be able to 
use that info. 

Also, look for a took called config on the sysinternals site.  It allows 
you to pre-allocate contiguous files.  Better, it allows you to defrag a 
single file.


Also, if you are feeling particularly sadistic, you could build a 
defragger into your open/close routines using the appropriate NTFS 
IOCTLs.  ;-)


Gary Moyer wrote:

Hi Teg,

Isn't an open issued for the database and journal separately?

I'm very familiar with the behavior for read-only, not so much for
read/write...

Regards,
-- Gary


On 10/28/07, Teg <[EMAIL PROTECTED]> wrote:
  

Hello Gary,

Sunday, October 28, 2007, 4:51:11 PM, you wrote:

GM> Hi Teg,

GM> Have you considered the SQLite VFS?

GM> Regards,
GM> -- Gary


GM> On 10/28/07, Teg <[EMAIL PROTECTED]> wrote:


I'd like to pre-allocate the DB for Sqlite so, I can ensure it's a
contiguous block of space on the disk. I'm aware of the "Insert a
bunch of data then delete" method but, it doesn't ensure a contiguous
block on disk. Is there some way I can allocate a file with OS calls


and


then
use it as an SQLite DB?

C






-


To unsubscribe, send email to [EMAIL PROTECTED]




-



Tried. One problem is at the VFS level, the code doesn't know if it's
writing to a journal or main DB file. You can pre-allocate in VFS
but, when the journal and main DB are combined, the main DB grows by
journal file size (or so I seem to observe).

This would probably have to be done in the pager.

--
Best regards,
Tegmailto:[EMAIL PROTECTED]



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





  

[sqlite] Retrieve bound host parameters from statement?

[Ralf Junker]

Hello,

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*);

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.

Ralf


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

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]
-

[sqlite] Performance problem for a simple select with range

[Dani Valevski]

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)

Disclaimer:
I'm a bit new to databases.

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? (see the disclaimer)

Anyway, I thought the problem was that startIpNum, endIpNum are not indexed.
So I added indices for them (even tried indexing them both). This only makes
the query take about 3 seconds.
Ideas anyone?

Source:
is attached.

Thank you for your help





-- 
Dani
http://daniva.googlepages.com


-- 
Dani
http://daniva.googlepages.com
'''geolite
GeoLite City is a free IP to city database provided by MaxMind. 
They provide a C API (and a python wrapper) for the database.
If you can't compile the C sources on your server (or get a binary 
version), this script might be helpful for you. 
The script puts the geoip data in a sqllite database, and provides
interfaces for updating and searching the database.

To use this script, get the database in CSV format:
http://www.maxmind.com/app/geolitecity

You also need to have python 2.5 for this script (sqlite3 is used)
'''

import sqlite3 as sqlite
import os

def dottedQuadToNum(ip):
"convert decimal dotted quad string to long integer"

hexn = ''.join(["%02X" % long(i) for i in ip.split('.')])
return long(hexn, 16)


def cursorToDict(cursor):
val = cursor.next()
return dict([(cursor.description[i][0],val[i]) for i in xrange(len(cursor.description))])

def test():
import sqlite3
from time import clock
x = sqlite3.connect('geolite.db')
y = x.cursor()
ip = dottedQuadToNum("84.108.189.94")
res = y.execute('select * from blocks,locations where locations.locid = blocks.locid AND ? >= blocks.startIpNum AND ? <= blocks.endIpNum', [ip,ip])
begin = clock()
f = res.next() 
end = clock()
y.close()
x.close()
return end-begin, f

def test2():
from time import clock
x = GeoLiteDB()
x.connect();
begin = clock()
x.ipLocation("84.108.189.94");
end = clock()
x.close()
return end - begin


def createDB(dbPath = 'geolite.db', locationsPath='GeoLiteCity-Location.csv', blocksPath='GeoLiteCity-Blocks.csv', warnOnDelete = True):
if os.path.exists(dbPath):
if warnOnDelete:
	print "file %s will be deleted. Press any key to continue, or 'n' to abort..." % (os.path.abspath(dbPath))
	if getch() == 'n':
	print 'aborted.'
	return None
	os.remove(os.path.abspath(dbPath))	
conn = sqlite.connect(dbPath)
cursor = conn.cursor()
try:
cursor.execute('''CREATE TABLE locations(
locid	INTEGER PRIMARY KEY,
country TEXT,
region	TEXT,
city	TEXT,
postalCode TEXT,
latitude REAL,
longitude REAL,
dmaCode INTEGER,
areaCode INTEGER)''')

	cursor.execute('''CREATE TABLE blocks(
startIpNum INTEGER,
endIpNum INTEGER,
locId INTEGER)''')

	locations = file(locationsPath,'r')
	print ('parsing locations. This will a while.')
	print locations.readline().strip() #should print copyright note
print locations.readline().strip() #should print column names
lines = ([x.strip('"') for x in line.strip().split(',')] for line in locations.xreadlines())
cursor.executemany('insert into locations values (?,?,?,?,?,?,?,?,?)', lines)
	locations.close()

	blocks = file(blocksPath,'r')
	print ('parsing blocks. This will take longer.')
	print blocks.readline().strip() #should print copyright note
print blocks.readline().strip() #should print column names
lines = ([x.strip('"') for x in line.strip().split(',')] for line in blocks.xreadlines())
	cursor.executemany('insert into blocks values (?,?,?)', lines)	
	blocks.close()

#cursor.execute('''CREATE UNIQUE INDEX startIpNumIx ON blocks(startIpNum);''')
#	cursor.execute('''CREATE UNIQUE INDEX endIpNumIx ON blocks(endIpNum);''')

conn.commit()

	print 'analyze'
	cursor.execute('''ANALYZE;''')

numBlocks = cursor.execute('select count(*) 

RE: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Odekirk, Shawn]

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] Re: Re[sqlite] garding software of SQlite2.1

[Kees Nuyt]

On Sun, 28 Oct 2007 21:08:21 -0700 (PDT), Vijaya Lakshmi
<[EMAIL PROTECTED]> wrote:

>
>Hi,
>Thank you very much for your response.I tried to convert SQLite2.1 version
>to SQlite3 version but unable to convert them .Could you please explain in
>which command prompt we need to convert them.

>From a command shell (CMD.EXE in windows).
With
>> sqlite2 yourv2.db .dump | sqlite3 yourv3.db
I mean:
sqlite2   = sqlite.exe, SQLite v2 command line program
yourv2.db = your v2 database
.dump = the SQLite command that dumps schema 
and data to stdout
| = pipe symbol
sqlite3   = sqlite3.exe, SQLite v3 command line program
yourv3.db = the v3 database you might want to make.

>From given url we found only
>.exe file not software. We need software. If we convert them into SQLite3
>version we nee to change source code of .Net. So we don't want to convert
>them. Thats why we need SQLite2.1 version software.

The windows .dll for v 2.8.17 is still available from:
http://www.sqlite.org/sqlitedll-2_8_17.zip 
(that is what I meant with: the download page doesn't list them,
but old versions are still available with the same naming scheme
as current versions). You might even be able to find v2.1 in
this way, but first you should try v2.8.17.

You can use the v2.8.17 sqlite.exe program to test if your
version 2.1 database is accessible from version 2.8.17.


>thank u
>vijaya
>
>
>Kees Nuyt wrote:
>> 
>> [Default] On Thu, 25 Oct 2007 21:04:44 -0700 (PDT), Vijaya
>> Lakshmi <[EMAIL PROTECTED]> wrote:
>> 
>>>Hi all,
>>>  I need small help regarding software .Acutually my application was
>>>developed in SQLite2.1 by some body now i need SQLite2.1 version.Actually
>I
>>>found SQLite3 version but by using this software i am unable to run my
>>>application.please let me know from which site i can get SQLite2.1 version
>>>software.
>>>thank for ur help in advance.
>>>
>>>vijaya
>> 
>> http://www.sqlite.org/download.html
>> Scroll to: Historical Binaries And Source Code
>> 
>> You will find 2.8.17 there.
>> 2.8.17 is outdated but stable.
>> You should try 2.8.17, 2.1 is really obsolete.
>> See http://www.sqlite.org/cvstrac/timeline for changes, at the
>> bottom you can specify what you want to see.
>> 
>> If 2.8.17 really is not an option for you, you'll have to try to
>> get an earlier version from CVS, or try to download an earlier
>> version by specifying the version you need by hand, using the
>> same naming scheme as the versions listed on the download page.
>> Often the older files are still there, just not listed anymore
>> on the download page.
>> 
>> Upgrading to 3.5.1 might be less difficult than you first think,
>> and v2 databases can usually be converted easily to v3 with:
>>  sqlite2 yourv2.db .dump | sqlite3 yourv3.db
>> 
>> HTH
>> -- 
>>   (  Kees Nuyt
>>   )
>> c[_]
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>> 
>> 
>> 
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] improving performance on SELECT

[Brad Stiles]

> I'm trying to improve SELECT queries on a db I created.
> 
> Here's the part I think is relevant:
> 
> SELECT fid, man_fsetid, pmfeature.allele, pmfeature.strand FROM
> featureSet, pmfeature WHERE man_fsetid IN () AND
> pmfeature.fsetid = featureSet.fsetid ORDER BY fid
> 
> That list usually contains 10K or more "man_fsetid" elements.

If it really "usually" contains that many items, I'd suggest using a temporary 
table if it's feasible, then doing a JOIN on that table in your query.  
Something along the lines of:

  SELECT   fid, 
   man_fsetid, 
   pmfeature.allele, 
   pmfeature.strand 
  FROM featureSet, pmfeature, tempTable t
  WHEREpmfeature.fsetid = featureSet.fsetid
  AND  pmfeature.man_fsetid = t.man_fsetid
  ORDER BY fid

You'd need to run some benchmarks to see which is really fastest, though.

Brad

-
To unsubscribe, send email to [EMAIL PROTECTED]
-