Re: [sqlite] Re: UNIQUE constraint on column

[Joe Wilson]

--- Shane Harrelson <[EMAIL PROTECTED]> wrote:
> On 2/5/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > > Yes, it's typical.   Each database instance is typically composed of
> > > around 50k records, all inserted in a single pass.  If I could do
> > > larger transactions (or not do them at all) I would, for if I
> > > encounter an error I have to discard all records - my application is
> > > extremely non-fault tolerant.
> > >
> > > Is there anyway to disable journaling completely?  I'm not certain
> > > that for my application it gains me anything.
> >
> > 50k records is nothing. Just sort it entirely in memory outside of the
> > database and blast in the results via just plain inserts in the two tables
> > in sorted order so sqlite will always perform a table append and not
> > have to shuffle the pages around. It will be significantly faster if you
> > handle the conflicts yourself in your code in memory using STL or a
> > conventional data structure than using a general purpose database such
> > as SQLite.
> >
> >
> 
> I'm working in an embedded environment, I have a total of 64mb of RAM
> for everything - O/S, file system, application usage, etc., and my
> table structure is more complicated (and larger) than the examples
> I've given.  We've considered rolling our on data storage format, but
> wanted to explore the flexibility and ease of use of SQLite.   So far,
> it's been a star.  Kudos to DRH, et.al. on such a nice product.

Fair enough. I wasn't aware of your constraints and how much time
you wanted to dedicate to optimization. I thought you were looking for 
a 10X speedup as opposed to a 1.3X speedup.

In any event, pre-sorting data in primary key order prior to insert 
is always a big win in SQLite. You might consider doing that, even if
in small batches of rows (a thousand at a time). I generally get a 2X
speedup with this simple trick.


 

Never Miss an Email
Stay connected with Yahoo! Mail on your mobile.  Get started!
http://mobile.yahoo.com/services?promote=mail

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

Re: [sqlite] Re: UNIQUE constraint on column

[Shane Harrelson]

On 2/5/07, Joe Wilson <[EMAIL PROTECTED]> wrote:


--- Shane Harrelson <[EMAIL PROTECTED]> wrote:

> On 2/5/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
> > Shane Harrelson wrote:
> > > Perform 50 transactions of 1000 inserts each (5 total).
> > >
> >
> > Shane,
> >
> > Is this your normal usage pattern? Inserting records in blocks of around
> > 1000 per transaction. Or would you be more likely to insert using 1000
> > transactions of 50 records, or perhaps only a single record per transaction?
> >
> > Dennis Cote
> >
>
> Yes, it's typical.   Each database instance is typically composed of
> around 50k records, all inserted in a single pass.  If I could do
> larger transactions (or not do them at all) I would, for if I
> encounter an error I have to discard all records - my application is
> extremely non-fault tolerant.
>
> Is there anyway to disable journaling completely?  I'm not certain
> that for my application it gains me anything.

50k records is nothing. Just sort it entirely in memory outside of the
database and blast in the results via just plain inserts in the two tables
in sorted order so sqlite will always perform a table append and not
have to shuffle the pages around. It will be significantly faster if you
handle the conflicts yourself in your code in memory using STL or a
conventional data structure than using a general purpose database such
as SQLite.




I'm working in an embedded environment, I have a total of 64mb of RAM
for everything - O/S, file system, application usage, etc., and my
table structure is more complicated (and larger) than the examples
I've given.  We've considered rolling our on data storage format, but
wanted to explore the flexibility and ease of use of SQLite.   So far,
it's been a star.  Kudos to DRH, et.al. on such a nice product.

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

Re: [sqlite] database is locked (my solution)

[Joe Wilson]

--- Andrew Teirney <[EMAIL PROTECTED]> wrote:
> If you are interested in the "BEGIN SHARED" transaction i posted a 
> simple patch to this mailing list within the last month if i recall 
> correctly.

 http://www.mail-archive.com/sqlite-users@sqlite.org/msg21260.html

Do you find that using this BEGIN SHARED patch/technique improves 
typical throughput to the database?


 

Get your own web address.  
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

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

Re: [sqlite] Re: UNIQUE constraint on column

[Joe Wilson]

--- Shane Harrelson <[EMAIL PROTECTED]> wrote:

> On 2/5/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
> > Shane Harrelson wrote:
> > > Perform 50 transactions of 1000 inserts each (5 total).
> > >
> >
> > Shane,
> >
> > Is this your normal usage pattern? Inserting records in blocks of around
> > 1000 per transaction. Or would you be more likely to insert using 1000
> > transactions of 50 records, or perhaps only a single record per transaction?
> >
> > Dennis Cote
> >
> 
> Yes, it's typical.   Each database instance is typically composed of
> around 50k records, all inserted in a single pass.  If I could do
> larger transactions (or not do them at all) I would, for if I
> encounter an error I have to discard all records - my application is
> extremely non-fault tolerant.
> 
> Is there anyway to disable journaling completely?  I'm not certain
> that for my application it gains me anything.

50k records is nothing. Just sort it entirely in memory outside of the 
database and blast in the results via just plain inserts in the two tables 
in sorted order so sqlite will always perform a table append and not
have to shuffle the pages around. It will be significantly faster if you 
handle the conflicts yourself in your code in memory using STL or a 
conventional data structure than using a general purpose database such 
as SQLite.


 

Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives.
http://tools.search.yahoo.com/toolbar/features/mail/

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

Re: [sqlite] How to keep syncronized between two DB on different severs?

[Joe Wilson]

http://www.mail-archive.com/sqlite-users@sqlite.org/msg19628.html

--- Alex Cheng <[EMAIL PROTECTED]> wrote:
> I encountered a problem. I have two servers (A and B) which host two same
> DBs, each of them may be modified. If the DB has been modified on server A,
> the DB on server B should also keep synchronized with server A. If the DB on
> server B has been modified, the DB on server A should also be modified
> automatically. How do I implement it? Is there any machanism in Sqlite to do
> it?


 

Get your own web address.  
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

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

[sqlite] How to keep syncronized between two DB on different severs?

[Alex Cheng]

Hi all,

I encountered a problem. I have two servers (A and B) which host two same
DBs, each of them may be modified. If the DB has been modified on server A,
the DB on server B should also keep synchronized with server A. If the DB on
server B has been modified, the DB on server A should also be modified
automatically. How do I implement it? Is there any machanism in Sqlite to do
it?

Thank you.

--
powered by python

Re: [sqlite] Re: UNIQUE constraint on column

[Shane Harrelson]

On 2/5/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

Shane Harrelson wrote:
> Perform 50 transactions of 1000 inserts each (5 total).
>

Shane,

Is this your normal usage pattern? Inserting records in blocks of around
1000 per transaction. Or would you be more likely to insert using 1000
transactions of 50 records, or perhaps only a single record per transaction?

Dennis Cote



Yes, it's typical.   Each database instance is typically composed of
around 50k records, all inserted in a single pass.  If I could do
larger transactions (or not do them at all) I would, for if I
encounter an error I have to discard all records - my application is
extremely non-fault tolerant.

Is there anyway to disable journaling completely?  I'm not certain
that for my application it gains me anything.

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

Re: [sqlite] OR, IN: which is faster?

[chueng alex1985]

Thank you , Joe Wilson.

2007/2/3, Joe Wilson <[EMAIL PROTECTED]>:


--- chueng alex1985 <[EMAIL PROTECTED]> wrote:
> I don't think so. If the field has been indexed, searching speed will be
> imprved very much. The query "f1 = value1 OR f1 = value2 OR f1=value3 OR
> ..." will be faster if the field f1 has been indexed. On the other hand,
the
> clause "f1 in (value1, value2, value3, ...)" seems to be slower then
'OR'
> clause because i think it will get the all value of 'f1' and check
whether
> the f1's value is in (value1, value2, value3, ...). If the table has
100K
> records, it will compare 100K times, in contrast, the OR clause only
need
> compare few times because of the index when the number of values in
(value1,
> value2, value3, ...) list is not too big.

As drh pointed out, if you run the following in sqlite 3.3.12 you'll see
both the IN and the OR queries produce the exact same instructions,
resulting in the same query speed if an index is used:

CREATE TABLE abc(a,b,c);
CREATE INDEX abc_c on abc(c);
explain select * from abc where c in (11,22,33);
explain select * from abc where c=11 or c=22 or c=33;

(same output for both)
0|Goto|0|41|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|3|
4|Integer|0|0|
5|OpenRead|1|3|keyinfo(1,BINARY)
6|MemLoad|2|0|
7|If|0|20|
8|MemInt|1|2|
9|OpenEphemeral|2|0|keyinfo(1,BINARY)
10|SetNumColumns|2|1|
11|Integer|11|0|
12|MakeRecord|1|0|b
13|IdxInsert|2|0|
14|Integer|22|0|
15|MakeRecord|1|0|b
16|IdxInsert|2|0|
17|Integer|33|0|
18|MakeRecord|1|0|b
19|IdxInsert|2|0|
20|Rewind|2|38|
21|Column|2|0|
22|IsNull|-1|37|
23|MemStore|1|1|
24|MemLoad|1|0|
25|MakeRecord|1|0|b
26|MemStore|0|0|
27|MoveGe|1|37|
28|MemLoad|0|0|
29|IdxGE|1|37|+
30|IdxRowid|1|0|
31|MoveGe|0|0|
32|Column|0|0|
33|Column|0|1|
34|Column|0|2|
35|Callback|3|0|
36|Next|1|28|
37|Next|2|21|
38|Close|0|0|
39|Close|1|0|
40|Halt|0|0|
41|Transaction|0|0|
42|VerifyCookie|0|2|
43|Goto|0|1|
44|Noop|0|0|

If you drop in the index abc_c, you will see the behavior you mentioned.
The OR query is on the left, and the IN query is on the right:

0|Goto|0|21|  | 0|Goto|0|36|
1|Integer|0|0|  1|Integer|0|0|
2|OpenRead|0|2| 2|OpenRead|0|2|
3|SetNumColumns|0|3|3|SetNumColumns|0|3|
4|Rewind|0|19|| 4|Rewind|0|34|
5|Column|0|2| | 5|MemLoad|0|0|
6|Integer|11|0|   | 6|If|0|19|
7|Eq|98|14|collseq(BINARY)| 7|MemInt|1|0|
8|Column|0|2| |
8|OpenEphemeral|1|0|keyinfo(1,BINARY)
9|Integer|22|0|   | 9|SetNumColumns|1|1|
10|Eq|98|14|collseq(BINARY)   | 10|Integer|11|0|
11|Column|0|2|| 11|MakeRecord|1|0|b
12|Integer|33|0|  | 12|IdxInsert|1|0|
13|Ne|354|18|collseq(BINARY)  | 13|Integer|22|0|
14|Column|0|0|| 14|MakeRecord|1|0|b
15|Column|0|1|| 15|IdxInsert|1|0|
16|Column|0|2|| 16|Integer|33|0|
17|Callback|3|0|  | 17|MakeRecord|1|0|b
18|Next|0|5|  | 18|IdxInsert|1|0|
19|Close|0|0| | 19|Integer|1|0|
20|Halt|0|0|  | 20|Column|0|2|
21|Transaction|0|0|   | 21|NotNull|-1|25|
22|VerifyCookie|0|3|  | 22|Pop|2|0|
23|Goto|0|1|  | 23|Null|0|0|
24|Noop|0|0|  | 24|Goto|0|28|
  > 25|MakeRecord|1|0|b
  > 26|Found|1|28|
  > 27|AddImm|-1|0|
  > 28|IfNot|1|33|
  > 29|Column|0|0|
  > 30|Column|0|1|
  > 31|Column|0|2|
  > 32|Callback|3|0|
  > 33|Next|0|5|
  > 34|Close|0|0|
  > 35|Halt|0|0|
  > 36|Transaction|0|0|
  > 37|VerifyCookie|0|3|
  > 38|Goto|0|1|
  > 39|Noop|0|0|

You can mimic the index-less OR behavior on the column (even in the
presence
of such an index) by putting a plus before each column in the where
clause,
thus disqualifying the column from using an index:

select * from abc where +c=11 or +c=22 or +c=33

This "OR" query table scan might be faster than using an index in cases
where you know that you will be selecting the majority of the rows in
the table.

>
> 2007/2/1, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
> >
> > Ion Silvestru <[EMAIL PROTECTED]> wrote:
> > > If we have a query where we compare a column to a set of values,
then
> > > which is faster: OR or IN?
> > > Ex: OR: (mycol = "a") OR (mycol = "b") O

Re: [sqlite] Re: newbie ask saving

[Hariyanto]

Can you give me sample code how to use it?
I still not understand when I read capi3ref.html

thx.

On Mon, 5 Feb 2007 18:36:04 -0500
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:

> Hariyanto <[EMAIL PROTECTED]> wrote:
> > I have 2 table:
> > 
> > 1. Table A :
> > no  INTEGER PRIMARY KEY,
> > name varchar(15);
> > 
> > 2. Table B :
> > no  smallint,   (Foreign key)
> > Address varchar(20);
> > Telpvarchar(10);
> > 
> > Usually I use this step when I save data :
> > - insert A (name) VALUES ("Mr.X");
> > - x = Select no_id FROM A where name = 'Mr.X'
> > - Insert B (no_id, Alamat, Telp) VALUES (x, 'x', '123');
> 
> Can't you use sqlite3_last_insert_rowid API?
> 
> Igor Tandetnik
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 

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

Re: [sqlite] Re: UNIQUE constraint on column

[Dennis Cote]

Shane Harrelson wrote:

Perform 50 transactions of 1000 inserts each (5 total).
 


Shane,

Is this your normal usage pattern? Inserting records in blocks of around 
1000 per transaction. Or would you be more likely to insert using 1000 
transactions of 50 records, or perhaps only a single record per transaction?


Dennis Cote

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

[sqlite] Re: newbie ask saving

[Igor Tandetnik]

Hariyanto <[EMAIL PROTECTED]> wrote:

I have 2 table:

1. Table A :
no  INTEGER PRIMARY KEY,
name varchar(15);

2. Table B :
no  smallint,   (Foreign key)
Address varchar(20);
Telpvarchar(10);

Usually I use this step when I save data :
- insert A (name) VALUES ("Mr.X");
- x = Select no_id FROM A where name = 'Mr.X'
- Insert B (no_id, Alamat, Telp) VALUES (x, 'x', '123');


Can't you use sqlite3_last_insert_rowid API?

Igor Tandetnik

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

Re: [sqlite] database is locked (my solution)

[Andrew Teirney]

Andrew,
 
 Nice modification.. Did you buy any chance post this into the sqlite ticketing system?
 
 It would be nice if sqlite would let you acquire a shared lock via the BEGIN statement. 
 


No i didn't actually post this to the ticketing system, to be honest i 
am very new to all this open source stuff and the procedures and that 
associated with requesting changes and providing patches. By all means I 
am okay with posting a ticket, i guess i have this fear that i'll do 
something not quite right and i'll get flamed for it ;-)


Andrew

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

Re: [sqlite] database is locked (my solution)

[Ken]

Andrew,
 
 Nice modification.. Did you buy any chance post this into the sqlite ticketing 
system?
 
 It would be nice if sqlite would let you acquire a shared lock via the BEGIN 
statement. 
 
 
 

Andrew Teirney <[EMAIL PROTECTED]> wrote: > I have multiple thread reading the 
database and a thread inserting in or
> updating the database.
> when i try to simulate this case by executing sqlite3 from shell by
> beginning a transaction from a shell and retrieving data from other shell
> eveything works fine.
> But when it's the cas from my C program it raises the error message 
> database
> is locked.
> How can i avoid this issue?
> thanks a lot

I myself was running into this problem a lot whilst trying to use a lua 
binding to sqlite where each thread used its own connection (obtained 
via a connection pool). The reason for getting the "database is locked" 
is that i was creating situations where there would be a deadlock 
because of the type of locks held by the various connections, one of 
them would have to yeild to allow the other to continue. This baffled me 
for a bit as i noticed my busy handler was not being called.

The solution i found was to wrap the complete operation i was doing in a 
transaction via "BEGIN" ... "END". I did however find the default style 
of "BEGIN" (which is a deffered style lock) i could end up with database 
is locked problem, this was because there would have been a deadlock in 
accessing the database due to the deffered style of locking. So alas i 
did some more investigation.

In the end i started to use "BEGIN IMMEDIATE" which acquires a pending 
lock on execution of the BEGIN, thus i could be sure that the lock 
required for the operation i was going to perform was granted, this also 
meant that any busy handler would be run whilst acquiring the lock.

However because of the usage pattern where there were multiple readers 
and a single writer this was obviously not the best idea, as it meant 
there could only be one thread/process accessing the database at one 
time. To get around this i wanted to be able to acquire a "SHARED" lock 
  via a "BEGIN" statement, have any busy handling operating around there.

Unfortunately sqlite by default doesn't support acquiring a shared lock 
  when the "BEGIN" is executed, even with a type specifier, for this i 
extended sqlite to enable a "BEGIN SHARED" style of transaction where 
this shared lock has been acquired. In doing this i was able to do all 
my busy handling around the "BEGIN" statements, and have multiple 
readers read from the db via "BEGIN SHARED", and then have writers call 
"BEGIN IMMEDIATE".

More info in the "BEGIN [TRANSACTION] ..." can be found at 
http://www.sqlite.org/lang_transaction.html

I would strongly suggest reading http://www.sqlite.org/lockingv3.html to 
get an overview of the different state of locks that can be acquired on 
the database.

If you are interested in the "BEGIN SHARED" transaction i posted a 
simple patch to this mailing list within the last month if i recall 
correctly.

Hope this helps,

Andrew

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

[sqlite] newbie ask saving

[Hariyanto]

I have 2 table:

1. Table A :
no  INTEGER PRIMARY KEY,
name varchar(15);

2. Table B :
no  smallint,   (Foreign key)
Address varchar(20);
Telpvarchar(10);

Usually I use this step when I save data :
- insert A (name) VALUES ("Mr.X");
- x = Select no_id FROM A where name = 'Mr.X'
- Insert B (no_id, Alamat, Telp) VALUES (x, 'x', '123');

Is there another way to save value in table B ?

thx 
Hong.

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

Re: [sqlite] database is locked (my solution)

[Andrew Teirney]

I have multiple thread reading the database and a thread inserting in or
updating the database.
when i try to simulate this case by executing sqlite3 from shell by
beginning a transaction from a shell and retrieving data from other shell
eveything works fine.
But when it's the cas from my C program it raises the error message 
database

is locked.
How can i avoid this issue?
thanks a lot


I myself was running into this problem a lot whilst trying to use a lua 
binding to sqlite where each thread used its own connection (obtained 
via a connection pool). The reason for getting the "database is locked" 
is that i was creating situations where there would be a deadlock 
because of the type of locks held by the various connections, one of 
them would have to yeild to allow the other to continue. This baffled me 
for a bit as i noticed my busy handler was not being called.


The solution i found was to wrap the complete operation i was doing in a 
transaction via "BEGIN" ... "END". I did however find the default style 
of "BEGIN" (which is a deffered style lock) i could end up with database 
is locked problem, this was because there would have been a deadlock in 
accessing the database due to the deffered style of locking. So alas i 
did some more investigation.


In the end i started to use "BEGIN IMMEDIATE" which acquires a pending 
lock on execution of the BEGIN, thus i could be sure that the lock 
required for the operation i was going to perform was granted, this also 
meant that any busy handler would be run whilst acquiring the lock.


However because of the usage pattern where there were multiple readers 
and a single writer this was obviously not the best idea, as it meant 
there could only be one thread/process accessing the database at one 
time. To get around this i wanted to be able to acquire a "SHARED" lock 
 via a "BEGIN" statement, have any busy handling operating around there.


Unfortunately sqlite by default doesn't support acquiring a shared lock 
 when the "BEGIN" is executed, even with a type specifier, for this i 
extended sqlite to enable a "BEGIN SHARED" style of transaction where 
this shared lock has been acquired. In doing this i was able to do all 
my busy handling around the "BEGIN" statements, and have multiple 
readers read from the db via "BEGIN SHARED", and then have writers call 
"BEGIN IMMEDIATE".


More info in the "BEGIN [TRANSACTION] ..." can be found at 
http://www.sqlite.org/lang_transaction.html


I would strongly suggest reading http://www.sqlite.org/lockingv3.html to 
get an overview of the different state of locks that can be acquired on 
the database.


If you are interested in the "BEGIN SHARED" transaction i posted a 
simple patch to this mailing list within the last month if i recall 
correctly.


Hope this helps,

Andrew

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

Re: [sqlite] Re: UNIQUE constraint on column

[Dennis Cote]

Shane Harrelson wrote:

However, in an effort to provide some useful performance data for
everyone, I'm making available the results of my testing with the
various suggested insertion algorithms below.  The fastest was my
"Hack" version, followed closely by the "Insert/Select/Insert" version
(which DRH agreed would most likely be the fastest "approved"
mechanism), with the "Insert+Ignore/Insert+Select" version proposed by
Dennis Cote, while being simplest, was the slowest.



 Windows Embedded
 (ms)   (%)  (ms) (%)
V0  27180   302330
V1  3014   10.89  32068   6.07
V2  3468   27.59  36275  19.98


Shane,

Thanks for posting your benchmark data. Your testing method is impeccable.

I'm still surprised the two insert method is so much slower. But, with 
your 10% collision rate, it is doing a select as opposed to a call to 
sqlite_last_insert_rowid for the 90% of rows that don't collide. In then 
end, it seems that adds up to substantial time even when the pages are 
all cached.


Dennis Cote



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

Re: [sqlite] Re: UNIQUE constraint on column

[Shane Harrelson]

On 2/5/07, A. Pagaltzis <[EMAIL PROTECTED]> wrote:

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-02-04 13:35]:
> "A. Pagaltzis" <[EMAIL PROTECTED]> wrote:
> > It's a pity that INSERT OR IGNORE (apparently?) does not set
> > last_insert_id properly regardless of outcome,
>
> Consider this case:
>
>   CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z UNIQUE);
>   INSERT INTO t1 VALUES(1,'a','b');
>   INSERT INTO t1 VALUES(2,'x','y');
>
>   INSERT OR IGNORE INTO t1 VALUES(NULL, 'a','y');
>
> For the INSERT OR IGNORE statement, does the last_insert_rowid
> get set to 1 or 2?

Yeah, good point. Silly I didn't think that far as we've just had
this in another thread.

Out of curiosity, though – does SQLite find both rows in this
case, or does it abort as soon as it sees any one constraint
violation before it knows there would be more?

Regards,
--
Aristotle Pagaltzis // 



I do agree with DRH that it would be difficult to come up with a
consistent mechanism for returning the rowid for constraint
collisions, and that the benefit of this is negligible.

In my application, the benefit was reduced greatly with the addition
of some simple caching to make the likely hood of duplicate string
insertions even smaller.

However, in an effort to provide some useful performance data for
everyone, I'm making available the results of my testing with the
various suggested insertion algorithms below.  The fastest was my
"Hack" version, followed closely by the "Insert/Select/Insert" version
(which DRH agreed would most likely be the fastest "approved"
mechanism), with the "Insert+Ignore/Insert+Select" version proposed by
Dennis Cote, while being simplest, was the slowest.

All the tests were performed with version 3.3.12 of SQLite on unloaded systems.

For the Windows version, I used the pre-compiled DLL version available here:
http://www.sqlite.org/sqlitedll-3_3_12.zip

For the embedded version, I used the "pure C" source version here:
http://www.sqlite.org/sqlite-source-3_3_12.zip
compiled with the following options:
-DOS_UNIX -DNO_TCL -DTHREADSAFE=1 -DNDEBUG -DTEMP_STORE=2 -DHAVE_USLEEP=1

The Windows test were done on a laptop running Windows XP SP2 with an
Intel Dual Core processor running at 1.83 GHz and 1GB of RAM.  Under
Windows, the SetProcessAffinityMask() kernel API was used to force
single CPU/core usage to try and avoid multi-processor/core timing
issues.  Additionally, the GetProcessTimes() kernel API was used to
get the total CPU usage time (rather than the "wall" time.)

The embedded tests were done on a proprietary board with a single CPU
and a 64MB RAM shared between application usage and a RAM based file
system (ie. no hard drive).

The full source code for the Windows version of my test is here:
http://www.mo-ware.com/shane/misc/insert_test.c
I'd appreciate any comments on how to improve it's performance
as at this point, that my key goal.


Schema:

CREATE TABLE Objects ( ObjectId INTEGER PRIMARY KEY, StringId INTEGER )
CREATE TABLE Strings ( StringId INTEGER PRIMARY KEY, Value VARCHAR(30) UNIQUE )


Version 0 (Hack):

INSERT INTO Strings (value) VALUES ('foo')
if string insert result is SQLITE_OK
  rowid = last_insert_rowid
else if result is SQLITE_CONSTRAINT
  rowid = get_rowid_from_VDBE_stack
end if
if rowid
  INSERT INTO Objects (StringId) VALUES (rowid)
endif


Version 1 (Insert/Select/Insert):

INSERT INTO Strings (value) VALUES ('foo')
if string insert result is SQLITE_OK
  rowid = last_insert_rowid
else if result is SQLITE_CONSTRAINT
  rowid = SELECT StringId FROM Strings WHERE Value = 'foo'
end if
if rowid
  INSERT INTO Objects (StringId) VALUES (rowid)
endif


Version 2 (Insert+Ignore/Insert+Select):

INSERT OR IGNORE INTO Strings (value) VALUES ('foo')
if string insert result is SQLITE_OK
  INSERT INTO Objects (StringId) VALUES ((SELECT StringId FROM
Strings WHERE Value = 'foo'))
end if


Test procedure:
Perform 50 transactions of 1000 inserts each (5 total).
The test was coded such that each insert has ~10% chance of being a
duplicate string insert.  It should be noted that be using the same
random seed for all runs and versions, the exact same database (byte
for byte) is generated.


Timing data:
10 runs, throw out lowest and highest, and average the remaining 8.
Total run time reported in milliseconds (ms), with the percent slower
than the fastest.

 Windows Embedded
 (ms)   (%)  (ms) (%)
V0  27180   302330
V1  3014   10.89  32068   6.07
V2  3468   27.59  36275  19.98

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

Re: [sqlite] DROP TABLE IF EXISTS my_table

[John Stanton]

If you can find a way of dropping a table which doesn't exist, use it. 
Otherwise just drop the table.


Cecilia VIGNY wrote:

Hi,

I'm using SQLite with a PHP program and I would like to execute this SQL 
request : DROP TABLE IF EXISTS clients;


See my code :

$allsqls=<<$sqlite = sqlite_open('pac.sdb') or die ('Impossible de se connecter a 
la base SQLite');


//Création des tables dans la base SQLite
$sqls=explode("\n",$allsqls);
foreach(array_values($sqls) as $sql)
{
   $sql = sqlite_query($sqlite, $sql) or die('Impossible d\'executer la 
requete');

}


When I test my program, this error occures :
Warning: sqlite_query(): near "EXISTS": syntax error in  on line 61

Why ? Can't we use "IF EXISTS" with php ? Does another solution exist ?

Thank you for your help :)





Ce message est protégé par les règles relatives au secret des 
correspondances. Il est donc établi à destination exclusive de son 
destinataire. Celui-ci peut donc contenir des informations 
confidentielles. La divulgation de ces informations est à ce titre 
rigoureusement interdite. Si vous avez reçu ce message par erreur, merci 
de le renvoyer à l'expéditeur dont l'adresse e-mail figure ci-dessus et 
de détruire le message ainsi que toute pièce jointe.


This message is protected by the secrecy of correspondence rules. 
Therefore, this message is intended solely for the attention of the 
addressee. This message may contain privileged or confidential 
information, as such the disclosure of these informations is strictly 
forbidden. If, by mistake, you have received this message, please return 
this message to the addressser whose e-mail address is written above and 
destroy this message and all files attached.




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] database is locked

[John Stanton]

You cannot avoid it if you have competing processes accessing the same 
database.  What you have to do is synchronize access in your code.


anis chaaba wrote:

Hello
Please can you tell me how can i avoid having error message "database is
locked'?
thanks




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

RE: [sqlite] BNF for sqlite3?

[WB Stow]

I would use the existing parser if my program were written in C, but it's
not. I will look at it as a reference when writing the BNF though.

Through my search I could see that this is not the first time that this
question has been asked. So, once I'm done I will provide you all with the
file.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 05, 2007 10:35 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BNF for sqlite3?

WB Stow wrote:
> Does anyone have a BNF file
(http://en.wikipedia.org/wiki/Backus-Naur_form)
> for SQLite3? I want to generate a parser with it.
>
> If not, I guess I can start with the SQL92 BNF that I have and add the
> sqlite3 stuff to it...
>
>   
Wayne,

The documentation at http://www.sqlite.org/lang.html contains a fairly 
complete pseudo BNF of all the SQL statements that SQLite accepts at the 
top of each statements' description.

There is already a complete freely available open source parser for the 
SQL that SQLite accepts in the SQLite source. It is a C language parser 
generated by the lemon parser generator (also included in SQLite). You 
may be able to use that directly, or model your parser on it. If you 
build your own, be aware that the language does change from time to time.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

[sqlite] long text on pocketpc

[Pepa]

I can't read more then 2KB from text column. I use 
http://www.codeproject.com/ce/SQLite3Wrapper.asp and compile dll library 
from newest sqlite source. I use VS 2005 and target platform is wm5 
pocketpc (arm). Other test application on .net cf with .net library (not 
original source) and sqlite3 command line utility work good with same 
database and I get more than 2KB. Do you know, where is the problem? 
Application on .net works good but I want to use native code for higher 
performance.

Excuse for my english.

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

Re: [sqlite] Re: DROP TABLE IF EXISTS my_table

[Dennis Cote]

Igor Tandetnik wrote:


IF EXISTS clause is supported with SQLite v3.3.0 and up.


Igor,

Right you are, version 3.3.0.

I was mistakenly looking at the entry for IF EXISTS on create and drop 
of triggers and views, that didn't happen until 3.3.8. Sorry if I caused 
any confusion.


Dennis Cote

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

[sqlite] Re: UNIQUE constraint on column

[A. Pagaltzis]

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-02-04 13:35]:
> "A. Pagaltzis" <[EMAIL PROTECTED]> wrote:
> > It's a pity that INSERT OR IGNORE (apparently?) does not set
> > last_insert_id properly regardless of outcome,
> 
> Consider this case:
> 
>   CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z UNIQUE);
>   INSERT INTO t1 VALUES(1,'a','b');
>   INSERT INTO t1 VALUES(2,'x','y');
> 
>   INSERT OR IGNORE INTO t1 VALUES(NULL, 'a','y');
> 
> For the INSERT OR IGNORE statement, does the last_insert_rowid
> get set to 1 or 2?

Yeah, good point. Silly I didn’t think that far as we’ve just had
this in another thread.

Out of curiosity, though – does SQLite find both rows in this
case, or does it abort as soon as it sees any one constraint
violation before it knows there would be more?

Regards,
-- 
Aristotle Pagaltzis // 

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

Re: [sqlite] database is locked

[Markus Hoenicka]

anis chaaba <[EMAIL PROTECTED]> was heard to say:

> I have multiple thread reading the database and a thread inserting in or
> updating the database.
> when i try to simulate this case by executing sqlite3 from shell by
> beginning a transaction from a shell and retrieving data from other shell
> eveything works fine.
> But when it's the cas from my C program it raises the error message database
> is locked.
> How can i avoid this issue?
> thanks a lot
>

Do your threads share the same connection? If yes, using separate connections
might help.

regards,
Markus

-- 
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de


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

Re: [sqlite] database is locked

[anis chaaba]

I have multiple thread reading the database and a thread inserting in or
updating the database.
when i try to simulate this case by executing sqlite3 from shell by
beginning a transaction from a shell and retrieving data from other shell
eveything works fine.
But when it's the cas from my C program it raises the error message database
is locked.
How can i avoid this issue?
thanks a lot

2007/2/5, Dennis Cote <[EMAIL PROTECTED]>:


anis chaaba wrote:
> Hello
> Please can you tell me how can i avoid having error message "database is
> locked'?
> thanks
>
You will have to provide more details about exactly what  you are doing
and how you are doing it for anybody to be able to assist you.

SQLite does support multiple process accessing the database. This page
explains the nature of your error in more detail
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] Re: DROP TABLE IF EXISTS my_table

[Igor Tandetnik]

Cecilia VIGNY 
wrote:


I'm using SQLite with a PHP program and I would like to execute this
SQL request : DROP TABLE IF EXISTS clients;

When I test my program, this error occures :
Warning: sqlite_query(): near "EXISTS": syntax error in  on line
61


IF EXISTS clause is supported with SQLite v3.3.0 and up. It appears that 
you are using an earlier version.


Igor Tandetnik 



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

Re: [sqlite] DROP TABLE IF EXISTS my_table

[Dennis Cote]

Cecilia VIGNY wrote:


Why ? Can't we use "IF EXISTS" with php ? Does another solution exist ?


What version of sqlite are you using? You can check with

   select sqlite_version();

The IF EXISTS clause was added to SQLite in version 3.3.8. Older version 
will not accept that syntax.


HTH
Dennis Cote



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

Re: [sqlite] database is locked

[Dennis Cote]

anis chaaba wrote:

Hello
Please can you tell me how can i avoid having error message "database is
locked'?
thanks

You will have to provide more details about exactly what  you are doing 
and how you are doing it for anybody to be able to assist you.


SQLite does support multiple process accessing the database. This page 
explains the nature of your error in more detail 
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked


HTH
Dennis Cote

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

Re: [sqlite] BNF for sqlite3?

[Dennis Cote]

WB Stow wrote:

Does anyone have a BNF file (http://en.wikipedia.org/wiki/Backus-Naur_form)
for SQLite3? I want to generate a parser with it.

If not, I guess I can start with the SQL92 BNF that I have and add the
sqlite3 stuff to it...

  

Wayne,

The documentation at http://www.sqlite.org/lang.html contains a fairly 
complete pseudo BNF of all the SQL statements that SQLite accepts at the 
top of each statements' description.


There is already a complete freely available open source parser for the 
SQL that SQLite accepts in the SQLite source. It is a C language parser 
generated by the lemon parser generator (also included in SQLite). You 
may be able to use that directly, or model your parser on it. If you 
build your own, be aware that the language does change from time to time.


HTH
Dennis Cote

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

Re: [sqlite] two process problem

[Dennis Cote]

Joe Wilson wrote:

--- Dennis Cote <[EMAIL PROTECTED]> wrote:
  

On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote:


SQLSTATE[HY000]: General error: 1 SQL logic error or missing database
and
SQLSTATE[HY000]: General error: 8 attempt to write a readonly database
  

Tom what wrapper are you using to access sqlite?  These messages are not
from sqlite itself, but your wrapper. It may have restrictions that sqlite
does not.




He got this from sqlite3_errmsg() which in turn calls sqlite3ErrStr():

  

Joe,

I was asking about the other end of the messages. These parts are not 
produced by sqlite.


SQLSTATE[HY000]: General error: 1

SQLSTATE[HY000]: General error: 8


I suspect some wrapper added these prefixes to the standard sqlite error 
strings.

Dennis Cote


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

[sqlite] malformed database schema

[Paolo Vernazza]

A client of mine reported a problem with a malformed DB.
I tried to recovery the DB but without success.
If I edit the file with an hex editor I see any data, but using sqlite I 
can't access it.


>sqlite3 data.sqlite
SQLite version 3.3.12
Enter ".help" for instructions
sqlite> PRAGMA integrity_check;
SQL error: malformed database schema - unrecognized token: "?"
sqlite> .dump
BEGIN TRANSACTION;
COMMIT;
sqlite> vacuum;
SQL error: SQL logic error or missing database
sqlite>

Is there some way I can recover the data?

Thanks

Paolo

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

[sqlite] DROP TABLE IF EXISTS my_table

[Cecilia VIGNY]

Hi,

I'm using SQLite with a PHP program and I would like to execute this SQL 
request : DROP TABLE IF EXISTS clients;


See my code :

$allsqls=<<$sqlite = sqlite_open('pac.sdb') or die ('Impossible de se connecter a 
la base SQLite');


//Création des tables dans la base SQLite
$sqls=explode("\n",$allsqls);
foreach(array_values($sqls) as $sql)
{
   $sql = sqlite_query($sqlite, $sql) or die('Impossible d\'executer la 
requete');

}


When I test my program, this error occures :
Warning: sqlite_query(): near "EXISTS": syntax error in  on line 61

Why ? Can't we use "IF EXISTS" with php ? Does another solution exist ?

Thank you for your help :)





Ce message est protégé par les règles relatives au secret des correspondances. 
Il est donc établi à destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est à ce titre rigoureusement interdite. Si vous avez reçu ce 
message par erreur, merci de le renvoyer à l'expéditeur dont l'adresse e-mail 
figure ci-dessus et de détruire le message ainsi que toute pièce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.



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

[sqlite] database is locked

[anis chaaba]

Hello
Please can you tell me how can i avoid having error message "database is
locked'?
thanks

Re: [sqlite] Appropriate uses for SQLite

[Chris Hanson]

On Feb 2, 2007, at 8:59 AM, Clark Christensen wrote:

FWIW, I'm not convinced Samba has locking working correctly.  Using  
a very recent Samba version, I managed to corrupt a SQLite database  
last fall by (I think) doing simultaneous writes from the Linux host  
box, and my WinXP client box (via a SMB drive map).  I'm guessing  
the XP writes started first.  It seems unlikely it would have  
happened had the Linux host started first.


This is a situation where you really need all file locking managed by  
the operating system, or you need to access the file only via a single  
mechanism (e.g. only via SMB, even on the Linux host box from which  
the SMB mount is exported).


If you have clients accessing a file via different remote mechanisms,  
these mechanisms *may not* share an underlying locking infrastructure,  
which can easily lead to corruption.  Only if they are *guaranteed to*  
share an underlying locking infrastructure is it safe to access a file  
that requires any sort of locking via multiple different remote  
mechanisms.  This is a general issue with any shared-access remote  
filesystem and any operating system, not something specific to SQLite,  
NFS, SMB, Windows, or Linux.


  -- Chris


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

Re: [sqlite] Re: Shared Lock Transactions

[anis chaaba]

How do I enable shared locking?

2007/1/25, Ken <[EMAIL PROTECTED]>:


From os_unix.h:... After reading this, locking makes more sense!
   Although the Lock may physically be an exclusive lock, the
implementation is actually a logcially  "SHARED" lock.



/* The following describes the implementation of the various locks and
   ** lock transitions in terms of the POSIX advisory shared and exclusive
   ** lock primitives (called read-locks and write-locks below, to avoid
   ** confusion with SQLite lock names). The algorithms are complicated
   ** slightly in order to be compatible with windows systems
simultaneously
   ** accessing the same database file, in case that is ever required.
   **
   ** Symbols defined in os.h indentify the 'pending byte' and the
'reserved
   ** byte', each single bytes at well known offsets, and the 'shared byte
   ** range', a range of 510 bytes at a well known offset.
   **
   ** To obtain a SHARED lock, a read-lock is obtained on the 'pending
   ** byte'.  If this is successful, a random byte from the 'shared byte
   ** range' is read-locked and the lock on the 'pending byte' released.
   **
   ** A process may only obtain a RESERVED lock after it has a SHARED
lock.
   ** A RESERVED lock is implemented by grabbing a write-lock on the
   ** 'reserved byte'.
**
   ** A process may only obtain a PENDING lock after it has obtained a
   ** SHARED lock. A PENDING lock is implemented by obtaining a write-lock
   ** on the 'pending byte'. This ensures that no new SHARED locks can be
   ** obtained, but existing SHARED locks are allowed to persist. A
process
   ** does not have to obtain a RESERVED lock on the way to a PENDING
lock.
   ** This property is used by the algorithm for rolling back a journal
file
   ** after a crash.
   **
   ** An EXCLUSIVE lock, obtained after a PENDING lock is held, is
   ** implemented by obtaining a write-lock on the entire 'shared byte
   ** range'. Since all other locks require a read-lock on one of the
bytes
   ** within this range, this ensures that no other locks are held on the
   ** database.
   **
   ** The reason a single byte cannot be used instead of the 'shared byte
   ** range' is that some versions of windows do not support read-locks.
By
   ** locking a random byte from a range, concurrent SHARED locks may
exist
   ** even if the locking primitive used is always a write-lock.
   */


"A. Pagaltzis" <[EMAIL PROTECTED]> wrote: * Jay Sprenkle  [2007-01-22
15:20]:
> My understanding was that a "shared lock" is a metaphor, and
> IMHO, a fairly stupid one. If you lock a room, nobody else can
> get in, it's not a mechanism for sharing, it's a mechanism for
> preventing sharing.

Reasoning by analogy rarely leads to anything but a fallacy.

A shared lock prevents exclusive locks from being granted and an
exclusive lock prevents shared locks from being granted, so I'm
not sure what sort of sharing/preventing business you're talking
about anyway.

Regards,
--
Aristotle Pagaltzis //


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] database is locked

[anis chaaba]

Hello,
I have processes that access to my database, one writing on it and the other
just reading. when this case take place an error message occure saying that
database is locked.
I saw in sqlite3 docs that sqlite3 enable this type of access but i don't
know how can I activate this option.
Is that by compiling sqlite3 with a special option or by specifying this
option in my c acess code?
thanks in advance for help