[sqlite] busy_timeout() doesn't work
Hello all,
I have found that busy_timeout doesn work in this case:
---%
#include stdio.h
#include sqlite3.h
int check_error (int rc, char *zErrMsg)
{
if( rc!=SQLITE_OK ){
fprintf(stderr, SQL error: %s\n, zErrMsg);
sqlite3_free(zErrMsg);
}
}
int main(int argc, char **argv){
sqlite3 *db, *db2;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open(test.db, db);
rc = sqlite3_open(test.db, db2);
printf(db1 start trans\n);
rc = sqlite3_exec(db, BEGIN TRANSACTION , NULL, NULL, zErrMsg);
check_error(rc, zErrMsg);
printf(db1 insert\n);
rc = sqlite3_exec(db, INSERT INTO Blah VALUES ( 1, 'Test1' ) ,
NULL, NULL, zErrMsg);
check_error(rc, zErrMsg);
sqlite3_busy_timeout(db2, 3);
printf(db2 start trans\n);
rc = sqlite3_exec(db2, BEGIN TRANSACTION , NULL, NULL, zErrMsg);
check_error(rc, zErrMsg);
/* SQLITE should wait for 3 second before returning error, but it doesn't */
printf(db2 insert\n);
rc = sqlite3_exec(db2, INSERT INTO Blah VALUES ( 1, 'Test1' ) ,
NULL, NULL, zErrMsg);
check_error(rc, zErrMsg);
sqlite3_close(db);
return 0;
}
---%
Most interesting thing that If you try to INSERT in db2 WITHOUT
transaction busy_timeout() will work correctly.
--
Alexander Batyrshin aka bash
Biomechanical Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DBD::SQLite::Amalgamation-3.6.1.2 FTS3 seg faulting... solution... upgrade code
I mean what is benefits of using DBD::SQLite::Amalgamation? Where it can be needed? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid On Sun, Sep 21, 2008 at 9:51 AM, P Kishor [EMAIL PROTECTED] wrote: On 9/21/08, Alexander Batyrshin [EMAIL PROTECTED] wrote: Sorry for off-topic. What is main difference between DBD::SQLite and DBD::SQLite::Amalgamation? Exactly what it says on the box. The latter uses the SQLite amalgamation. The former doesn't. They both work exactly the same for the end user. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid On Sat, Sep 20, 2008 at 8:04 PM, P Kishor [EMAIL PROTECTED] wrote: I encountered this problem and solved it, so hopefully this will help some other poor sod. Audrey Tang's otherwise most excellent DBD::SQLite::Amalgamation (bless her for this incredible package) was causing segmentation faults for me while doing FTS3 searches on a RH ES3 Linux box. The package version 3.6.1.2 has code for SQLite 3.6.1. I upgraded the code in the package to SQLite 3.6.2 (just copied the corresponding files from the SQLite tarball... one file had to be renamed from sqlite.c to sqlite-amalgamation.c) and rebuilt the DBD. Everything is now fine in happy town. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [3.6.2] make test errors
I don't know, if it's really needed by community. But I got this erros by make test on x86_64-pc-linux-gnu platform: 12 errors out of 23377 tests Failures on these tests: lookaside-1.4 lookaside-1.5 memsubsys1-2.3 memsubsys1-2.4 memsubsys1-3.2.4 memsubsys1-4.3 memsubsys1-4.4 memsubsys1-5.3 memsubsys1-6.3 memsubsys1-6.4 memsubsys1-7.4 memsubsys1-7.5 Unfreed memory: 1080 bytes Writing unfreed memory log to ./memleak.txt Memory used: now 1080 max 112168 max-size4128768 Page-cache used: now 0 max 0 max-size 4096 Page-cache overflow: now 0 max3258200 Scratch memory used: now 0 max 0 Scratch overflow: now 0 max 33320 max-size 33320 Maximum memory usage: 112168 bytes Current memory usage: 1080 bytes Number of malloc() : -1 calls make: *** [test] Error 1 -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DBD::SQLite::Amalgamation-3.6.1.2 FTS3 seg faulting... solution... upgrade code
Sorry for off-topic. What is main difference between DBD::SQLite and DBD::SQLite::Amalgamation? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid On Sat, Sep 20, 2008 at 8:04 PM, P Kishor [EMAIL PROTECTED] wrote: I encountered this problem and solved it, so hopefully this will help some other poor sod. Audrey Tang's otherwise most excellent DBD::SQLite::Amalgamation (bless her for this incredible package) was causing segmentation faults for me while doing FTS3 searches on a RH ES3 Linux box. The package version 3.6.1.2 has code for SQLite 3.6.1. I upgraded the code in the package to SQLite 3.6.2 (just copied the corresponding files from the SQLite tarball... one file had to be renamed from sqlite.c to sqlite-amalgamation.c) and rebuilt the DBD. Everything is now fine in happy town. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] JOIN works very strange [3.6.2]
Hello everyone, I gets strange result from this query on SQLite-3.6.2 SELECT town.id, town_log.new_player_id, player.name FROM town_log LEFT JOIN town LEFT JOIN player ON town.id = town_log.town_id AND town_log.new_player_id = player.id WHERE town_log.id = 5195 As you can see this query should return only 1 row, because town_log.id is unique key. But I gets rows with different town.id (1 col), with constant new_player.id (2 col) and JOIN do not work for player.name, couse 3 col is empty. result: [] 45512 9266 44544 9266 45229 9266 46376 9266 45927 9266 46645 9266 [] Any ideas what's going on? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOIN works very strange [3.6.2]
I just want to add that this SQL query works great at 3.5.4. And this is explain: addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT town.id, town_log.new_player_id, player.name FROM town_log LEFT JOIN town LEFT JOIN player ON town.id = town_log.town_id AND town_log.new_player_id = player.id WHERE town_log.id = 5195; 00 1 Integer5195 1 000 2 Goto 0 46000 3 SetNumColumns 0 5 000 4 OpenRead 0 14000 5 SetNumColumns 0 1 000 6 OpenRead 1 6 000 7 SetNumColumns 0 2 000 8 OpenRead 2 2 000 9 SetNumColumns 0 2 000 10OpenRead 3 3 0 keyinfo(1,BINARY) 00 11MustBeInt 1 41000 12NotExists 0 41100 13Integer0 3 000 14Rewind 1 38000 15Integer1 3 000 16Integer0 4 000 17Column 0 4 600 18IsNull 6 33000 19Affinity 6 1 0 db 00 20MoveGe 3 336 1 00 21IdxGE 3 336 1 01 22IdxRowid 3 10000 23MoveGe 2 0 10 00 24Column 1 0 10 00 25Column 0 1 11 00 26Ne 113210collseq(BINARY) 6b 27Integer1 4 000 28Column 1 0 12 00 29Column 0 4 13 00 30Column 2 1 14 00 31ResultRow 123 000 32Next 3 21000 33IfPos 4 37000 34NullRow2 0 000 35NullRow3 0 000 36Goto 0 27000 37Next 1 15000 38IfPos 3 41000 39NullRow1 0 000 40Goto 0 15000 41Close 0 0 000 42Close 1 0 000 43Close 2 0 000 44Close 3 0 000 45Halt 0 0 000 46Transaction0 0 000 47VerifyCookie 0 92000 48TableLock 0 140 town_log 00 49TableLock 0 6 0 town 00 50TableLock 0 2 0 player 00 51Goto 0 3 000 PS: And the last one. I want to say that generally SQLite-3.6.3 is slowly than 3.5.4 on my OS X 10.5.5. My result is that 3.6.3 is slowly near 5-7%. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid On Sat, Sep 20, 2008 at 8:45 AM, Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello everyone, I gets strange result from this query on SQLite-3.6.2 SELECT town.id, town_log.new_player_id, player.name FROM town_log LEFT JOIN town LEFT JOIN player ON town.id = town_log.town_id AND town_log.new_player_id = player.id WHERE town_log.id = 5195 As you can see this query should return only 1 row, because town_log.id is unique key. But I gets rows with different town.id (1 col), with constant new_player.id (2 col) and JOIN do not work for player.name, couse 3 col is empty. result: [] 45512 9266 44544 9266 45229 9266 46376 9266 45927 9266 46645 9266 [] Any ideas what's going on? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [3.6.2] make test doesn't work on OS X 10.5.5
Looks like make test can't links with TCL library: Undefined symbols: _Tcl_GetIndexFromObjStruct, referenced from: _processDevSymArgs in ccpuPiSI.o _test_config in ccy8gMQR.o _Tcl_GetInt, referenced from: _test_get_table_printf in ccux7BhL.o _sqlite3_mprintf_int in ccux7BhL.o _sqlite3_mprintf_str in ccux7BhL.o [...] _Tcl_ResetResult, referenced from: _sqlite3TestErrCode in ccux7BhL.o _overloadedGlobFunction in cc8Hgdly.o _overloadedGlobFunction in cc8Hgdly.o _c_collation_test in cc7AxOBb.o _c_realloc_test in cc7AxOBb.o _c_misuse_test in cc7AxOBb.o _DbTraceHandler in ccUU2Qo6.o _DbProfileHandler in ccUU2Qo6.o _DbObjCmd in ccUU2Qo6.o ld: symbol(s) not found collect2: ld returned 1 exit status make: *** [testfixture] Error 1 -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to use IN keyword for multi-column index
Hello All, For example we have table like this: CREATE TABLE map ( name text, x integer, y integer ); CREATE INDEX map_xy ON map(x,y); How to query this table with IN keyword? Query like this, doesn't work: SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3)); -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use IN keyword for multi-column index
IN only works on a single column. The closest you can get to this is something like SELECT map.* FROM map join ( select 1 x, 1 y union all select 1 x, 2 y union all select 1 x, 3 y) t ON map.x = t.x AND map.y=t.y; Thanks. I will use more than 3 keys, so I will create temporary memory table with keys for this stuff. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use IN keyword for multi-column index
2. I tried this first: [ select * from map where (x=1 and y=1) or (x=1 and y=2) or (x=1 and y=3) ] but that didn't use the index -- not on 3.5.6 anyway AFAIK OR will always omit indexes, this is why I am trying to use IN -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is faster?
I am more interesting in theoretical answer :) On Fri, Apr 25, 2008 at 5:24 PM, Federico Granata [EMAIL PROTECTED] wrote: If you are under linux you can use time command to execute sqlite with various query and see which one is faster. -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). On Fri, Apr 25, 2008 at 10:04 AM, Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello people, I have two SQL commands doing the same thing: 1. SELECT id FROM foo WHERE expr1 EXCEPT SELECT id FROM bar WHERE expr2 2. SELECT id FROM foo WHERE expr1 AND id not IN (SELECT id FTOM bar WHERE expr2) Can you say which one is faster? I prefer second option because I can use extra condition like LIMIT. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency access to SQLite
For example, if 2 processes executes simple SQL INSERT commands and gets situation like above, one of them can easily drop read lock and wait for another one. There is no problem for this case. Two concurrent inserts never result in a deadlock. For a deadlock to occur in SQLite, at least one transaction should start as a read-only (with a select statement) and later attempt to promote to read-write (with insert, update or delete statements). In this case you may get into a situation where the first transaction holds a shared lock and waits to promote it to reserved, and the second one holds a pending lock, wants to promote it to exclusive and waits for all readers (shared locks) to clear. I have some additional questions, just for consistency of my knowledge. So, please, don't irritate. 1. Any single SQL command in SQLite start transaction. Any write operation should start with getting shared lock. So question is shared lock == read lock? if its true, then two inserts is transactions which starts with read lock... So it's still possible situation like above. 2. What will be if we have SQL command like this UPDATE ... SELECT ? In this case we have explicitly calls to SELECT which should get read lock. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it safe to ...
On Mon, Mar 24, 2008 at 3:58 PM, [EMAIL PROTECTED] wrote: Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello, Is it safe to use this algorithm: open_db fork() sql_do() // both parent and child executes sql statements close_db I am not familiar with locking mechanism and I am afraid that if parent and child will use the same DB handlers it can cause of DB corruptions It is not safe to carry an open SQLite database connection across a fork. The documentation says this somewhere, IIRC, but I don't remember exactly where. I should probably state this fact in the documentation for sqlite3_open()... I have new question. Is it save to close SQLite database at child process or I should close it exactly before fork()? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Concurrency access to SQLite
Hello All,
I am observing situation, that my concurrency process does not have
access to SQLite database with equal probability.
Here is example. I have N process that do work like this:
while (1) {
do_some_work(); // takes ~ 30 sec
save_work_result_to_sqlite(); // takes ~ 1 sec
}
So, as you can see, these N process has concurrency access to SQLite database.
In theory in worst case, save_work_result_to_sqlite() should NOT wait
for access to database longer than N * 1 sec.
But in practice, some process blocks on save_work_to_sqlite() more
than N*2 sec and dies on my SQLITE_BUSY asserts :/
So, I am wondering, is there any ideas how to avoid this?
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency access to SQLite
So, you advice me, to implement synchronization inside my process by my self?
On Thu, Apr 24, 2008 at 3:40 PM, John Stanton [EMAIL PROTECTED] wrote:
You have a single shared resource, Sqlite, and you have to synchronize
access. You can use the internal locking in Sqlite and use polling or
wait on a mutex or semaphore.
Alexander Batyrshin wrote:
Hello All,
I am observing situation, that my concurrency process does not have
access to SQLite database with equal probability.
Here is example. I have N process that do work like this:
while (1) {
do_some_work(); // takes ~ 30 sec
save_work_result_to_sqlite(); // takes ~ 1 sec
}
So, as you can see, these N process has concurrency access to SQLite
database.
In theory in worst case, save_work_result_to_sqlite() should NOT wait
for access to database longer than N * 1 sec.
But in practice, some process blocks on save_work_to_sqlite() more
than N*2 sec and dies on my SQLITE_BUSY asserts :/
So, I am wondering, is there any ideas how to avoid this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency access to SQLite
Oh... Nope, I am not using any thread-mechanism.
I am using simple processes (via fork). So synchronization should be
task for SQLite library.
But right now I am confused, because my processes do not blocks on
sqlite3_exec. They immediately report BUSY_TIMEOUT, without awaiting
for time set by sqlite3_busy_timeout.
On Thu, Apr 24, 2008 at 4:29 PM, John Stanton [EMAIL PROTECTED] wrote:
If it is one process I would assign a mutex to the resource (Sqlite) and
wait on it to get access to the resource. When the Sqlite operation is
complete release the mutex and the next thread will have exclusive
access to it.
If you use pthreads you can use read and write locks to get concurrency
on reads.
To my mind syncing on a mutex is better and simpler than polling the
resource using SQLITE_BUSY.
Alexander Batyrshin wrote:
So, you advice me, to implement synchronization inside my process by my
self?
On Thu, Apr 24, 2008 at 3:40 PM, John Stanton [EMAIL PROTECTED] wrote:
You have a single shared resource, Sqlite, and you have to synchronize
access. You can use the internal locking in Sqlite and use polling or
wait on a mutex or semaphore.
Alexander Batyrshin wrote:
Hello All,
I am observing situation, that my concurrency process does not have
access to SQLite database with equal probability.
Here is example. I have N process that do work like this:
while (1) {
do_some_work(); // takes ~ 30 sec
save_work_result_to_sqlite(); // takes ~ 1 sec
}
So, as you can see, these N process has concurrency access to SQLite
database.
In theory in worst case, save_work_result_to_sqlite() should NOT wait
for access to database longer than N * 1 sec.
But in practice, some process blocks on save_work_to_sqlite() more
than N*2 sec and dies on my SQLITE_BUSY asserts :/
So, I am wondering, is there any ideas how to avoid this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency access to SQLite
I am not understand this example. First of all second process can't
promote exclusive lock from reserved. It should use intermediate
pending lock. And secondary why first process can't just drop read
lock and then invoke busy handler?
In this case any write to database that already has process
On Thu, Apr 24, 2008 at 6:01 PM, Simon Davies
[EMAIL PROTECTED] wrote:
Alexander,
From http://www.sqlite.org/c3ref/busy_handler.html
The presence of a busy handler does not guarantee that it will be
invoked when there is lock contention. If SQLite determines that
invoking the busy handler could result in a deadlock, it will go ahead
and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the
busy handler. Consider a scenario where one process is holding a read
lock that it is trying to promote to a reserved lock and a second
process is holding a reserved lock that it is trying to promote to an
exclusive lock. The first process cannot proceed because it is blocked
by the second and the second process cannot proceed because it is
blocked by the first. If both processes invoke the busy handlers,
neither will make any progress. Therefore, SQLite returns SQLITE_BUSY
for the first process, hoping that this will induce the first process
to release its read lock and allow the second process to proceed
Rgds,
Simon
2008/4/24 Alexander Batyrshin [EMAIL PROTECTED]:
Oh... Nope, I am not using any thread-mechanism.
I am using simple processes (via fork). So synchronization should be
task for SQLite library.
But right now I am confused, because my processes do not blocks on
sqlite3_exec. They immediately report BUSY_TIMEOUT, without awaiting
for time set by sqlite3_busy_timeout.
On Thu, Apr 24, 2008 at 4:29 PM, John Stanton [EMAIL PROTECTED] wrote:
If it is one process I would assign a mutex to the resource (Sqlite) and
wait on it to get access to the resource. When the Sqlite operation is
complete release the mutex and the next thread will have exclusive
access to it.
If you use pthreads you can use read and write locks to get concurrency
on reads.
To my mind syncing on a mutex is better and simpler than polling the
resource using SQLITE_BUSY.
Alexander Batyrshin wrote:
So, you advice me, to implement synchronization inside my process by
my self?
On Thu, Apr 24, 2008 at 3:40 PM, John Stanton [EMAIL PROTECTED]
wrote:
You have a single shared resource, Sqlite, and you have to
synchronize
access. You can use the internal locking in Sqlite and use polling
or
wait on a mutex or semaphore.
Alexander Batyrshin wrote:
Hello All,
I am observing situation, that my concurrency process does not
have
access to SQLite database with equal probability.
Here is example. I have N process that do work like this:
while (1) {
do_some_work(); // takes ~ 30 sec
save_work_result_to_sqlite(); // takes ~ 1 sec
}
So, as you can see, these N process has concurrency access to
SQLite database.
In theory in worst case, save_work_result_to_sqlite() should NOT
wait
for access to database longer than N * 1 sec.
But in practice, some process blocks on save_work_to_sqlite() more
than N*2 sec and dies on my SQLITE_BUSY asserts :/
So, I am wondering, is there any ideas how to avoid this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency access to SQLite
Dropping the read lock is the same as rolling back the transaction. The first process can, in fact, do this. And the second process is waiting for the first process to do this. But the first process cannot do it automatically. The application must issue a COMMIT or ROLLBACK command to make it happen. This is looks little bit odd for me. For example, if 2 processes executes simple SQL INSERT commands and gets situation like above, one of them can easily drop read lock and wait for another one. There is no problem for this case. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency access to SQLite
I got it. Fixed my program with IMMEDIATE transaction. On Fri, Apr 25, 2008 at 12:01 AM, Igor Tandetnik [EMAIL PROTECTED] wrote: Alexander Batyrshin [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Dropping the read lock is the same as rolling back the transaction. The first process can, in fact, do this. And the second process is waiting for the first process to do this. But the first process cannot do it automatically. The application must issue a COMMIT or ROLLBACK command to make it happen. For example, if 2 processes executes simple SQL INSERT commands and gets situation like above, one of them can easily drop read lock and wait for another one. There is no problem for this case. Two concurrent inserts never result in a deadlock. For a deadlock to occur in SQLite, at least one transaction should start as a read-only (with a select statement) and later attempt to promote to read-write (with insert, update or delete statements). In this case you may get into a situation where the first transaction holds a shared lock and waits to promote it to reserved, and the second one holds a pending lock, wants to promote it to exclusive and waits for all readers (shared locks) to clear. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Deadlock with attached databases
Hello All, Is it possible that SQLite deadlocks if it uses attached databases? I am not sure, but if check_all_db_and_lock_all_of_them() is not atomic, it can be... -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it safe to ...
Hello, Is it safe to use this algorithm: open_db fork() sql_do() // both parent and child executes sql statements close_db I am not familiar with locking mechanism and I am afraid that if parent and child will use the same DB handlers it can cause of DB corruptions -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in quickstart documentation
Hello,
I found that there is some mess in quickstart manual -
http://www.sqlite.org/quickstart.html
At C code example some lines ends with argv}; hd_resolve_one {0}; hd_puts {);
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to build sqlite3 (shell.c) shared linked?
Hello, Is there any special arguments to ./configure for building sqlite3 (shell.c) shared linked to sqlite library? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] looping over a result set in a query
Hello Kishor, a: if you want to find all edges, why your query returns nodes? So I think it should be like this: SELECT edge_id FROM edge WHERE from_node = $node OR to_node = $node; This query is not good, because OR drop out any index optimization. But As I see your example doesn't have indexes on from_node, to_node. So, if you want use all power of indexes, it'll be better to use: SELECT edge_id FROM edge WHERE from_node = $node UNION SELECT edge_id FROM edge WHERE to_node = $node If you don't use loop edges, it will be faster to use UNION ALL. I am not really understand what is count of the forward links. Can you give more detailed definition? On Feb 9, 2008 4:10 PM, P Kishor [EMAIL PROTECTED] wrote: I have a table of nodes and edges like so CREATE TABLE edge ( edge_id INTEGER PRIMARY KEY, from_node_id TEXT, to_node_id TEXT, .. ); CREATE TABLE node ( node_id INTEGER PRIMARY KEY, node_name TEXT, .. ); Given a $node_id, I want to find (a) all the edges where that node_id appears either as a from_node_id or a to_node_id, and (b) a count of the forward links as well. For (a), I do the following SELECT node_id, node_name FROM ( SELECT e.to_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.to_node_id = n.node_id WHERE e.from_node_id = $node_id UNION SELECT e.from_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.from_node_id = n.node_id WHERE e.to_node_id = $node_id ) For (b), I can't think of any better way than looping over the result of (a), and running the following query for each node_id in the result (in this case, each node_id will be the forward looking node for the original node_id). Psuedo-code ahead foreach node_id AS $other_node_id in result-of-a SELECT Count(node_id) AS count_of_other_node_id FROM ( SELECT e.to_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.to_node_id = n.node_id WHERE e.from_node_id = $other_node_id UNION SELECT e.from_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.from_node_id = n.node_id WHERE e.to_node_id = $other_node_id ) My questions -- is there a way to do both (a) and (b) better, and is it possible to do them all in one query? -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] looping over a result set in a query
For your pseudo-code for (b) i can suggest this, but i still not understand why you need it :-/ SUBQUERY = SELECT e.to_node_id AS node_id FROM edge e WHERE e.from_node_id = $node_id UNION SELECT e.from_node_id AS node_id FROM edge e WHERE e.to_node_id = $node_id SELECT count(edge_id) FROM edge WHERE edge.to_node IN (SUBQUERY) OR edge.from_node IN (SUBQUERY) I moved some SQL inside SUBQUERY for easy reading-understanding. You can eliminate OR like in my previous email, but full query will be really huge and unreadable. On Feb 9, 2008 4:10 PM, P Kishor [EMAIL PROTECTED] wrote: I have a table of nodes and edges like so CREATE TABLE edge ( edge_id INTEGER PRIMARY KEY, from_node_id TEXT, to_node_id TEXT, .. ); CREATE TABLE node ( node_id INTEGER PRIMARY KEY, node_name TEXT, .. ); Given a $node_id, I want to find (a) all the edges where that node_id appears either as a from_node_id or a to_node_id, and (b) a count of the forward links as well. For (a), I do the following SELECT node_id, node_name FROM ( SELECT e.to_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.to_node_id = n.node_id WHERE e.from_node_id = $node_id UNION SELECT e.from_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.from_node_id = n.node_id WHERE e.to_node_id = $node_id ) For (b), I can't think of any better way than looping over the result of (a), and running the following query for each node_id in the result (in this case, each node_id will be the forward looking node for the original node_id). Psuedo-code ahead foreach node_id AS $other_node_id in result-of-a SELECT Count(node_id) AS count_of_other_node_id FROM ( SELECT e.to_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.to_node_id = n.node_id WHERE e.from_node_id = $other_node_id UNION SELECT e.from_node_id AS node_id, n.node_name AS node_name FROM edge e JOIN node n ON e.from_node_id = n.node_id WHERE e.to_node_id = $other_node_id ) My questions -- is there a way to do both (a) and (b) better, and is it possible to do them all in one query? -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] looping over a result set in a query
Is it possible to refine/combine the above two sets of queries into one? Yes. It's possible: A) SELECT e.to_node_id AS node_id FROM edge e WHERE e.from_node_id = $node_id UNION SELECT e.from_node_id AS node_id FROM edge e WHERE e.to_node_id = $node_id B) SELECT count(edge_id) FROM edge WHERE edge.to_node_id IN (Query_A) OR edge.from_node_id IN (Query_A) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac binary Cannot Download
The same here: # wget http://sqlite.org/sqlite3-3.5.6-osx-x86.bin.gz --12:29:52-- http://sqlite.org/sqlite3-3.5.6-osx-x86.bin.gz = `sqlite3-3.5.6-osx-x86.bin.gz' Resolving sqlite.org... 67.18.92.124 Connecting to sqlite.org[67.18.92.124]:80... connected. HTTP request sent, awaiting response... End of file while parsing headers. Retrying. --12:30:10-- http://sqlite.org/sqlite3-3.5.6-osx-x86.bin.gz (try: 2) = `sqlite3-3.5.6-osx-x86.bin.gz' Connecting to sqlite.org[67.18.92.124]:80... connected. HTTP request sent, awaiting response... End of file while parsing headers. Retrying. PS. I am using sources for SQLite under Mac OS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this list available in *DIGEST* form??
Goto http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Would you like to receive list mail batched in a daily digest? No Yes On Feb 8, 2008 5:31 AM, Rob Sciuk [EMAIL PROTECTED] wrote: How do I sign up for the digest rather than the regular feed?? Cheers, Rob. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug found in the new register-based VM
Maybe it'll be better that hot-fixes goes under the same version plus hot-fix number? For example at 3.5.5 was introduced new VM, so all fixes for this will goes in releases like 3.5.5.X? On Feb 7, 2008 12:57 AM, [EMAIL PROTECTED] wrote: I had been saying in release announcements that no bugs have been found in the new register-based virtual machine introduced in SQLite version 3.5.5. That changed with ticket #2927. We have now observed our first register-VM bug. http://www.sqlite.org/cvstrac/tktview?tn=2927 There will likely be a new release (version 3.5.7) within a few days in order to fix the problem discovered by ticket #2927. -- D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?
Hello, What do you expect to see? From the code, I'm guessing something like: This is test-case program for testing DBD-SQLite behavior. Dumper is only for be sure, that data was read correctly from database. If you're just trying to silence the closing dbh with active handles... warning, undef $sth; usually works for me. I see you have it commented in your code? DBD-SQLite has spewed this warning for as long as I can remember. And $dbh-finish; doesn't squash it. Yes, this is what I want. undef $sth doesn't work for statement that was prepare_cached. Because statement is still allocated inside $dbh buffers for cached statement. Also, I see you could save the sprintf and $dbh-quote by changing to: my $sql = select a_session from sessions where id = ?; my $sth = $dbh-prepare_cached($sql); $sth-execute($sid); Yes, i know, but this is only test-case program without any optimization and code-beauty refactoring. In your example, the value of $sid, after doing the $dbh-quote, is parsed by the SQL parser. Doing that has always been unreliable for me, and it's generally open to SQL injection. In the example above, $sid isn't parsed/compiled by SQLite, it's just passed as-is as a bound parameter after $sth is prepared. What kind of SQL injection is possible here? Are you building a web session manager using SQLite as the data store? How is Storable working for you? I usually just use Data::Dumper, and eval the stored hash. But doing the eval has always worried me :-)) It's work without any problems for me handling over 150k hits/day. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?
I think i found solution. The problem is that DBD::SQlite-disconnect() method execute sqlite3_close() function. This function return SQLITE_BUSY in case if there are any active statement. From API: Applications should finalize all prepared statements and close all BLOBs associated with the sqlite3 object prior to attempting to close the sqlite3 object. Currently DBD::SQLite can finalize statements only via DESTROY method. In simplest case you can always use undef $sth or wait untill it goes out of scope which will finalize statement. But if you prepared statement via cache (prepare_cached) it will not work for you, because statement is till inside DBI cache. In this case we can call DESTROY on our cached statement only via DESTROY for database handler. And we can achieve it by undef $dbh. undef $dbh - will close all cached statements and close database without any errors. Conclusion: avoid using $dbh-disconnect() for DBD::SQLite, instead use undef $dbh. On Feb 5, 2008 3:37 AM, Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello, What do you expect to see? From the code, I'm guessing something like: This is test-case program for testing DBD-SQLite behavior. Dumper is only for be sure, that data was read correctly from database. If you're just trying to silence the closing dbh with active handles... warning, undef $sth; usually works for me. I see you have it commented in your code? DBD-SQLite has spewed this warning for as long as I can remember. And $dbh-finish; doesn't squash it. Yes, this is what I want. undef $sth doesn't work for statement that was prepare_cached. Because statement is still allocated inside $dbh buffers for cached statement. Also, I see you could save the sprintf and $dbh-quote by changing to: my $sql = select a_session from sessions where id = ?; my $sth = $dbh-prepare_cached($sql); $sth-execute($sid); Yes, i know, but this is only test-case program without any optimization and code-beauty refactoring. In your example, the value of $sid, after doing the $dbh-quote, is parsed by the SQL parser. Doing that has always been unreliable for me, and it's generally open to SQL injection. In the example above, $sid isn't parsed/compiled by SQLite, it's just passed as-is as a bound parameter after $sth is prepared. What kind of SQL injection is possible here? Are you building a web session manager using SQLite as the data store? How is Storable working for you? I usually just use Data::Dumper, and eval the stored hash. But doing the eval has always worried me :-)) It's work without any problems for me handling over 150k hits/day. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] One statement for several databases...
Hello all, Is it possible to prepare statement and use it for several databases with identical schemas? And is it possible to create statement, re-open database and then use it again for same database? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DBD::SQLite 1.14 prepare_cached bug?
Hello All,
I don't know is it right place to discuss this or not. Sorry If I am
doing something wrong.
Installed sqlite-3.5.4 and DBD::SQLite-1.14
I get problems with this code:
%
#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
use Storable;
use warnings;
sub get_session {
my ($dbh) = shift;
#$dbh-{TraceLevel} = 2;
my $sid = $ARGV[0];
my $SQL = sprintf(select a_session from sessions where id = %s,
$dbh-quote($sid));
my $sth = $dbh-prepare_cached($SQL, undef, 3);
$sth-execute;
my ($val) = $sth-fetchrow_array;
#my ($val2) = $sth-fetchrow_array;
$sth-finish;
#[3] undef $sth;
my $session = Storable::thaw($val);
}
my $dbh = DBI-connect('dbi:SQLite:dbname=db/sessions.db');
print Dumper(get_session($dbh));
$dbh-disconnect;
%
If we run program as it looks, result will be:
DBI::db=HASH(0x87a79c)-disconnect invalidates 1 active statement
handle (either destroy statement handles or call finish on them before
disconnecting) at ./decode_sessions.pl line 26.
closing dbh with active statement handles at ./decode_sessions.pl line 26.
Note: that my database does not contain duplicated records.
If I uncomment (1), (2) or (1)+(2) result:
closing dbh with active statement handles at ./decode_sessions.pl line 26.
Inside DBD-SQLite this errors goes from this:
%
int
sqlite_db_disconnect (SV *dbh, imp_dbh_t *imp_dbh)
{
dTHR;
DBIc_ACTIVE_off(imp_dbh);
if (DBIc_is(imp_dbh, DBIcf_AutoCommit) == FALSE) {
sqlite_db_rollback(dbh, imp_dbh);
}
if (sqlite3_close(imp_dbh-db) == SQLITE_BUSY) {
/* active statements! */
warn(closing dbh with active statement handles);
}
imp_dbh-db = NULL;
av_undef(imp_dbh-functions);
imp_dbh-functions = (AV *)NULL;
av_undef(imp_dbh-aggregates);
imp_dbh-aggregates = (AV *)NULL;
return TRUE;
}
%
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?
Opss. Code with numbers looks like this: $sth-execute; my ($val) = $sth-fetchrow_array; #[1] my ($val2) = $sth-fetchrow_array; #[2] $sth-finish; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5
How to get error messages from test? Scroll up and copy-paste? Or there is some other more handy method? On Jan 31, 2008 7:03 PM, [EMAIL PROTECTED] wrote: Rob Sciuk [EMAIL PROTECTED] wrote: Ran the tests on Freebsd, the make test summary follows: [delenda = ... Ok] 16 errors out of 38961 tests Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2 All memory allocations freed - no leaks Maximum memory usage: 14161974 bytes Current memory usage: 0 bytes *** Error code 1 We get zero test failures on Linux. I don't know if these are significant or not without seeing the test error messages that accompany each failure. Probably this is things where your system is printing 3.4e+05 whereas SQLite is looking for 3.4e+005, in which case the errors are benign. But without seeing the actual errors, I cannot say for sure. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5
How to get error messages from test? Scroll up and copy-pase? Or there is some other more handy method? On Jan 31, 2008 7:03 PM, [EMAIL PROTECTED] wrote: Rob Sciuk [EMAIL PROTECTED] wrote: Ran the tests on Freebsd, the make test summary follows: [delenda = ... Ok] 16 errors out of 38961 tests Failures on these tests: bind-4.4 bind-4.5 cast-3.14 cast-3.18 cast-3.24 printf-1.7.6 printf-1.8.6 printf-1.9.7 tcl-1.6 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2 All memory allocations freed - no leaks Maximum memory usage: 14161974 bytes Current memory usage: 0 bytes *** Error code 1 We get zero test failures on Linux. I don't know if these are significant or not without seeing the test error messages that accompany each failure. Probably this is things where your system is printing 3.4e+05 whereas SQLite is looking for 3.4e+005, in which case the errors are benign. But without seeing the actual errors, I cannot say for sure. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5
Here is my make test on Mac OS 10.5.1: 8 errors out of 38117 tests Failures on these tests: lock4-1.3 vtab6-2.2 vtab6-2.4 vtab6-2.5 vtab6-2.6 vtab6-7.1 vtab6-9.1.1 vtab6-9.2 All memory allocations freed - no leaks Maximum memory usage: 14161966 bytes Current memory usage: 0 bytes make: *** [test] Error 1 -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] make test on FreeBSD 6.2-R, SQLite 3.5.5
Here is my errors on mac os 10.5 with 3.5.5 SQLite
lock4-1.3...
Error: database is locked
vtab6-2.2...
Expected: [1 2 3 {} 2 3 4 1 3 4 5 2]
Got: [1 2 3 {} 2 3 4 {} 3 4 5 {}]
vtab6-2.4...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3]
Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.5...
Expected: [2 3 4 {} {} {} 3 4 5 1 2 3]
Got: [2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-2.6...
Expected: [1 2 3 {} {} {} 2 3 4 {} {} {}]
Got: [1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 {} {} {}]
vtab6-7.1...
Expected: [1 999 999 2 131 130 999]
Got: [1 999 999 999 999 999 999]
vtab6-9.1.1...
Expected: []
Got: [2 22 {}]
vtab6-9.2...
Expected: []
Got: [2 22 {}]
On Jan 31, 2008 7:27 PM, [EMAIL PROTECTED] wrote:
Alexander Batyrshin [EMAIL PROTECTED] wrote:
How to get error messages from test? Scroll up and copy-paste? Or
there is some other more handy method?
make test | tee testout.txt
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] make test problems on Fedora 7
Looks like you built it without TCL support On Jan 31, 2008 7:54 PM, Scott Baker [EMAIL PROTECTED] wrote: I downloaded sqlite 3.5.5 and ran make test. I get the following errors: /tmp/cc9i7nKr.o: In function `Sqlitetest1_Init': /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to `Tcl_CreateCommand' /tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to `Tcl_CreateObjCommand' /tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to `Tcl_LinkVar' /tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined references to `Tcl_LinkVar' follow /tmp/cc9i7nKr.o: In function `test_io_trace': /tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to `Tcl_AppendResult' /tmp/cc9i7nKr.o: In function `test_printf': /tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to `Tcl_AppendResult'/tmp/cc9i7nKr.o: In function `Sqlitetest1_Init': /tmp/sqlite-3.5.5/./src/test1.c:4562: undefined reference to `Tcl_CreateCommand' /tmp/sqlite-3.5.5/./src/test1.c:4565: undefined reference to `Tcl_CreateObjCommand' /tmp/sqlite-3.5.5/./src/test1.c:4568: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4570: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4572: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4574: undefined reference to `Tcl_LinkVar' /tmp/sqlite-3.5.5/./src/test1.c:4576: undefined reference to `Tcl_LinkVar' /tmp/cc9i7nKr.o:/tmp/sqlite-3.5.5/./src/test1.c:4578: more undefined references to `Tcl_LinkVar' follow /tmp/cc9i7nKr.o: In function `test_io_trace': /tmp/sqlite-3.5.5/./src/test1.c:251: undefined reference to `Tcl_AppendResult' /tmp/cc9i7nKr.o: In function `test_printf': /tmp/sqlite-3.5.5/./src/test1.c:1103: undefined reference to `Tcl_AppendResult' /tmp/cc9i7nKr.o: In function `getDbPointer': /tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to `Tcl_GetCommandInfo' /tmp/cc9i7nKr.o: In function `test_busy_timeout': /tmp/sqlite-3.5.5/./src/test1.c:4012: undefined reference to `Tcl_AppendResult' /tmp/sqlite-3.5.5/./src/test1.c:4017: undefined reference to `Tcl_GetInt' /tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to `Tcl_AppendResult' /tmp/cc9i7nKr.o: In function `getDbPointer': /tmp/sqlite-3.5.5/./src/test1.c:108: undefined reference to `Tcl_GetCommandInfo' /tmp/cc9i7nKr.o: In function `test_busy_timeout': /tmp/sqlite-3.5.5/./src/test1.c:4012: undefined reference to `Tcl_AppendResult' /tmp/sqlite-3.5.5/./src/test1.c:4017: undefined reference to `Tcl_GetInt' /tmp/sqlite-3.5.5/./src/test1.c:4019: undefined reference to `Tcl_AppendResult' Etc, etc, etc. I have tcl-devel installed, but I'm assuming I need some other tcl package? Any idea what I need? -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] make test problems on Fedora 7
Oh... you know about it. Sorry Etc, etc, etc. I have tcl-devel installed, but I'm assuming I need some other tcl package? Any idea what I need? On Jan 31, 2008 8:27 PM, Alexander Batyrshin [EMAIL PROTECTED] wrote: Looks like you built it without TCL support - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to make correct transaction use only SQL?
Thank you =) This is good idea :) On Jan 30, 2008 2:44 PM, Samuel R. Neff [EMAIL PROTECTED] wrote: Instead of piping the sql into sqlite3.exe, use the .read command instead. C:\Temp\ssqlite3 test.dat SQLite version 3.4.2 Enter .help for instructions sqlite .read test.sql SQL error near line 10: column id is not unique SQL error near line 12: cannot commit - no transaction is active sqlite select * from t1; sqlite .read aborts on first error, whereas piping doesn't know to do that (and presumably there's no way it could know). HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Alexander Batyrshin [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 29, 2008 9:37 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to make correct transaction use only SQL? For example i have this tabe: CREATE TABLE t1 ( id int unique ON CONFLICT ROLLBACK, val char ); And I have to execute this sql file: BEGIN TRANSACTION; INSERT INTO t1 (id, val) VALUES(1, 'val1'); INSERT INTO t1 (id, val) VALUES(2, 'val2'); INSERT INTO t1 (id, val) VALUES(3, 'val3'); INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT INSERT INTO t1 (id, val) VALUES(4, 'val5'); COMMIT; If we execute this sql file, only INSERT before CONFLICT case will be rollback-ed, but last one still will be executed and remains in database... cat test.sql | sqlite3 test.db SQL error near line 11: column id is not unique SQL error near line 13: cannot commit - no transaction is active $ sqlite3 test.db SQLite version 3.4.0 sqlite select * from t1; 4|val5 I what that on conflict _whole_ transaction will ROLLBACK and state of database will be exactly like at moment of execution BEGIN TRANSACTION. How it is possible using only SQL? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE VIEW or CREATE TEMP TABLE
I got better performance when started to use TEMP tables with temp_store = MEMORY. But everything depends on situation. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] CREATE VIEW or CREATE TEMP TABLE
Offtop: You are trying to make something like statistic for game? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Default ON CONFLICT clause
Hello all, What is default ON CONFLICT clause? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Default ON CONFLICT clause
Yep. It is last line in this document. Somehow i missed it :) On Jan 30, 2008 3:03 AM, P Kishor [EMAIL PROTECTED] wrote: On 1/29/08, Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello all, What is default ON CONFLICT clause? The algorithm specified in the OR clause of a INSERT or UPDATE overrides any algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used. http://www.sqlite.org/lang_conflict.html -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to make correct transaction use only SQL?
For example i have this tabe: CREATE TABLE t1 ( id int unique ON CONFLICT ROLLBACK, val char ); And I have to execute this sql file: BEGIN TRANSACTION; INSERT INTO t1 (id, val) VALUES(1, 'val1'); INSERT INTO t1 (id, val) VALUES(2, 'val2'); INSERT INTO t1 (id, val) VALUES(3, 'val3'); INSERT INTO t1 (id, val) VALUES(3, 'val4'); -- CONFLICT INSERT INTO t1 (id, val) VALUES(4, 'val5'); COMMIT; If we execute this sql file, only INSERT before CONFLICT case will be rollback-ed, but last one still will be executed and remains in database... cat test.sql | sqlite3 test.db SQL error near line 11: column id is not unique SQL error near line 13: cannot commit - no transaction is active $ sqlite3 test.db SQLite version 3.4.0 sqlite select * from t1; 4|val5 I what that on conflict _whole_ transaction will ROLLBACK and state of database will be exactly like at moment of execution BEGIN TRANSACTION. How it is possible using only SQL? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Performance of Unique Index
Hello All, Is there any difference in speed of access for unique and usual index? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
I have never been in this situation (32/64 bit) and i don't have red-hat for test it. I don't know how to help you. On Jan 26, 2008 6:28 AM, Jim Dodgen [EMAIL PROTECTED] wrote: thanks for all the help, I think I am getting closer ... but still broken ... look for the --- for my comments --- tail of the install of sqlite 3.5.4 -- /usr/bin/install -c -d /usr/local/bin ./libtool --mode=install /usr/bin/install -c sqlite3 /usr/local/bin /usr/bin/install -c sqlite3 /usr/local/bin/sqlite3 /usr/bin/install -c -d /usr/local/include /usr/bin/install -c -m 0644 sqlite3.h /usr/local/include /usr/bin/install -c -m 0644 ../sqlite-3.5.4/src/sqlite3ext.h /usr/local/include /usr/bin/install -c -d /usr/local/lib/pkgconfig; /usr/bin/install -c -m 0644 sqlite3.pc /usr/local/lib/pkgconfig; # ls -l /usr/local/lib/libsql* -rw-r--r-- 1 root root 3221590 Jan 25 20:55 /usr/local/lib/libsqlite3.a -rwxr-xr-x 1 root root 823 Jan 25 20:55 /usr/local/lib/libsqlite3.la lrwxrwxrwx 1 root root 19 Jan 25 20:55 /usr/local/lib/libsqlite3.so - libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 Jan 25 20:55 /usr/local/lib/libsqlite3.so.0 - libsqlite3.so.0.8.6 -rwxr-xr-x 1 root root 1840520 Jan 25 20:55 /usr/local/lib/libsqlite3.so.0.8.6 --- then when I run the perl makefile: # SQLITE_LOCATION=/usr/local/lib perl Makefile.PL Checking installed SQLite version... Looks good Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ /usr/lib64/perl5/vendor_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 140 Using DBI 1.52 (for perl 5.008006 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ Writing Makefile for DBD::SQLite --- now things get funky when I make #make cp lib/DBD/SQLite.pm blib/lib/DBD/SQLite.pm /usr/bin/perl -p -e s/~DRIVER~/SQLite/g /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/Driver.xst SQLite.xsi /usr/bin/perl /usr/lib/perl5/5.8.6/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.6/ExtUtils/typemap SQLite.xs SQLite.xsc mv SQLite.xsc SQLite.c gcc -c -I. -I/usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\1.14\ -DXS_VERSION=\1.14\ -fPIC -I/usr/lib64/perl5/5.8.6/x86_64-linux-thread-multi/CORE -DSQLITE_CORE -DSQLITE_ENABLE_FTS2 -DNDEBUG=1 -DSQLITE_PTR_SZ=8 -DHAVE_USLEEP=1 SQLite.c gcc -c -I. -I/usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -DVERSION=\1.14\ -DXS_VERSION=\1.14\ -fPIC -I/usr/lib64/perl5/5.8.6/x86_64-linux-thread-multi/CORE -DSQLITE_CORE -DSQLITE_ENABLE_FTS2 -DNDEBUG=1 -DSQLITE_PTR_SZ=8 -DHAVE_USLEEP=1 dbdimp.c Running Mkbootstrap for DBD::SQLite () chmod 644 SQLite.bs rm -f blib/arch/auto/DBD/SQLite/SQLite.so gcc -shared SQLite.o dbdimp.o -o blib/arch/auto/DBD/SQLite/SQLite.so -lsqlite3 /usr/bin/ld: /usr/lib/gcc/x86_64-redhat-linux/4.0.0/../../../../lib64/libsqlite3.a(main.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/lib/gcc/x86_64-redhat-linux/4.0.0/../../../../lib64/libsqlite3.a: could not read symbols: Bad value collect2: ld returned 1 exit status make: *** [blib/arch/auto/DBD/SQLite/SQLite.so] Error 1 # --- I suspect that it has something to do with this being a 64 bit processor, I suspect that sqlite 3.5.4 is being bult as a 32 bit image --- and the perl module is a 64 bit image. and is looking for the 64 bit libs? Alexander Batyrshin wrote: if you DBD::SQlite built statically, then it uses it's internal SQLite If it's linked again libsqlite, you can check it by command ldd on: # find /usr/lib/perl5/ -name 'SQLite.so' /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 = /usr/lib/libsqlite3.so.0 (0xb7ed7000) libc.so.6 = /lib/libc.so.6 (0xb7da7000) libpthread.so.0 = /lib/libpthread.so.0 (0xb7d9) /lib/ld-linux.so.2 (0x4100) On Jan 26, 2008 12:00 AM, Jim Dodgen [EMAIL PROTECTED] wrote: thanks for the education, looks like i have multiple versions floating around, as seen below I'll blow a way the lib64 version just to eliminate the confusion. How do I tell which one is being used? computer A (this has 3.5.4 installed) lrwxrwxrwx 1 rootroot 19 Feb 8 2006 /usr/lib64/libsqlite3.so.0 - libsqlite3.so.0.8.6 lrwxrwxrwx 1 rootroot 19 Jan 24 15:59 /usr/local/lib
Re: [sqlite] DBD::SQLite for 3.5.4?
if you DBD::SQlite built statically, then it uses it's internal SQLite If it's linked again libsqlite, you can check it by command ldd on: # find /usr/lib/perl5/ -name 'SQLite.so' /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 = /usr/lib/libsqlite3.so.0 (0xb7ed7000) libc.so.6 = /lib/libc.so.6 (0xb7da7000) libpthread.so.0 = /lib/libpthread.so.0 (0xb7d9) /lib/ld-linux.so.2 (0x4100) On Jan 26, 2008 12:00 AM, Jim Dodgen [EMAIL PROTECTED] wrote: thanks for the education, looks like i have multiple versions floating around, as seen below I'll blow a way the lib64 version just to eliminate the confusion. How do I tell which one is being used? computer A (this has 3.5.4 installed) lrwxrwxrwx 1 rootroot 19 Feb 8 2006 /usr/lib64/libsqlite3.so.0 - libsqlite3.so.0.8.6 lrwxrwxrwx 1 rootroot 19 Jan 24 15:59 /usr/local/lib/libsqlite3.so.0 - libsqlite3.so.0.8.6 computer B (I have not done the upgrade here yet) lrwxrwxrwx 1 root root 19 May 29 2007 /usr/lib64/libsqlite3.so.0 - libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 May 29 2007 /usr/lib/libsqlite3.so.0 - libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 Jul 26 2007 /usr/local/lib/libsqlite3.so.0 - libsqlite3.so.0.8.6 I think I should clean out all the sqlite libs and bins, is all i have to delete are sqlite3 and libsqlite3.so.0* on the system? thanks for the help Jim Alexander Batyrshin wrote: There is two way of compiling DBD::SQLite: 1. to use his own internal version of SQLite USE_LOCAL_SQLITE=1 perl Maker.pl 2. to use shared library of SQLite SQLITE_LOCATION=/path/to/libsqlite perl Makefile.pl So if you install 3.5.4 in /usr/local/lib, you should set SQLITE_LOCATION=/usr/local/lib/ On Jan 25, 2008 5:13 AM, Jim Dodgen [EMAIL PROTECTED] wrote: I have tend to build the DBD::SQLite from source, when ever I have built with it looking for sqlite libs it reports a old version older than 3.3.9 or something and then uses the current 3.4.2 stuff supplied in the module. I do have 3.5.4 installed, it migh be that there could be a older version hiding someplace. not sure how to find it or delete it. I'm on fedora also a RHES Alexander Batyrshin wrote: I have no problem with 3.5.4. Maybe your is linked with libsqlite in other dirrectory? For example your DBD::SQLite is linked against /usr/lib/libsqlite3.so.0, and you installed new 3.5.2 into /usr/local/lib ? Here is my linking information: # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 = /usr/lib/libsqlite3.so.0 (0xb7eb7000) libc.so.6 = /lib/libc.so.6 (0xb7d87000) libpthread.so.0 = /lib/libpthread.so.0 (0xb7d7) /lib/ld-linux.so.2 (0x4100) On Jan 25, 2008 4:41 AM, Jim Dodgen [EMAIL PROTECTED] wrote: sorry I attached another email by accident, it's content is not related to my question Jim Jim Dodgen wrote: the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have attempted to get a version up to 3.5.2 with no success so far. anyone have any success yet? If so what is the magic. Jim - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Differences SQLite 32bit/64bit
Hello All, I am interesting is there any benefits in SQLite built for 64 bit linux platform (amd64)? I am not good at subject of 32/64, but IMHO SQLite 64 should work faster or I am wrong? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite omit using index
As usual your answer is perfect in explanation!
Thank you very much.
On Jan 24, 2008 2:49 AM, [EMAIL PROTECTED] wrote:
Alexander Batyrshin [EMAIL PROTECTED] wrote:
Hello All,
I've found that SQLite-3.5.4 doesnt use index in this situation:
sqlite create table t1 (id int primary key, val int);
sqlite create table t2 (id unique, val int primary key);
sqlite explain query plan update t1 set val = (select t2.val from t2
where t1.id = t2.id);
0|0|TABLE t1
0|0|TABLE t2
In this case, SQLite should takes value from t2 via unique id INDEX,
but it doesn't
The t2.id field has no datatype specified. That means it has
an affinity of NONE. (See http://www.sqlite.org/datatypes3.html
paragraph 2.1 bullet 3.) That means that if you insert a string
into t2.id it goes in as a string:
INSERT INTO t2(id) VALUES('123');
SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
-- answer text
Or if you insert an integer, it goes in as an integer:
INSERT INTO t2(id) VALUES(123);
SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
-- answer integer
But the t1.id column to which you are comparing t2.id has
an affinity of INTEGER. (paragraph 2.1 bullet 1.) That means
if you insert a string it is converted into an integer if it
looks like an integer.
INSERT INTO t1(id) VALUES('123');
SELECT typeof(id) FROM t1 WHERE rowid=last_insert_rowid();
-- answer integer
Now, the index on t2(id) also uses NO-affinity because the
affinity of the column is NONE. So the index stores separate
entries in separate places for '123' and 123. But the value
you are comparing against is always an integer, because it is
coming out of t1.id which has integer affinity. So if you
look up the entry using just the integer value 123, you will
miss the '123' entry. That is unacceptable. Hence, you cannot
use a value with INTEGER-affinity as the key to an index
with NO-affinity.
Hence the index on t2.id cannot be used to speed the search.
You can get the index to work by saying:
create table t1(id int primary key, val int);
create table t2(id INT unique, val int primary key);
Note the added INT in the definition of t2.id, thus
giving it integer affinity. You'll still be able to store
text in t2.id if you want to, but if that text looks like
an integer, it is converted into an integer.
Please also not that INT PRIMARY KEY is not the same
thing as INTEGER PRIMARY KEY. You probably want
to use INTEGER PRIMARY KEY in this context, not what
you have - but that is a whole other issue.
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Cache for SQLite
Hello All, I've used SQLite for half of year and find it perfect. But for my case (web-site) there is a gap in feature like cache. I know that file-system cache do a lot of work for SQLite, but it is still not perfect. For example IMHO it's possible to crate something like daemon which will be between application and SQLite engine and which will do caching. Do you know any extensions/modification/patch that allow to add cache feature? -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
On Jan 24, 2008 4:03 PM, Doug [EMAIL PROTECTED] wrote: I don't know of a daemon, but based on someone else's post where they described keeping a pool of sqlite3* handles to the database, and always reusing the most recently used handle first (so that the SQLite page cache is most likely still valid) I saw a very big jump in performance. Perhaps that would help in your case too? Sounds interesting, maybe it help me a little. I am using Perl DBD::SQLite, so i need some investigation how this library work. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
Hello Clark, I am using Apache + Fast-CGI :) But my next move will be to mod_perl. Currently I have only idea to use something like Cache::SharedMemoryCache or Cache::Memcached for implementing caching inside my application. What are you thinking about this? If you have any interesting ideas or knowledge - it'll be great if you share it with me. On Jan 24, 2008 6:06 PM, Clark Christensen [EMAIL PROTECTED] wrote: I don't think you're going to get the kind of caching you want using Perl and a web server (Apache, right?). There's just no persistence across processes, no shared memory, no database connections. Now, Apache's mod_perl and some associated modules could get you all that and more. For me, anyway, it requires a big adjustment in the way you build your apps if you want to take advantage of the shared $dbh, shared variables, and caching. For me, the investment isn't quite worth the benefit. -Clark -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache for SQLite
Hello John, Right now i am using apache + fcgid (fast-cgi). I will try to keep database handler open. But i need to implement it, because i am using now more than 200 databases. On Jan 24, 2008 9:38 PM, John Stanton [EMAIL PROTECTED] wrote: Using Apache is the problem. The connections are not persistent so caching is destroyed. It sounds like you are using CGI, and that makes it more so. Somevariant like fastcgi (?) might give you what you look for. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
I have no problem with 3.5.4. Maybe your DBD::SQLite is linked with libsqlite in other dirrectory? For example your DBD::SQLite is linked against /usr/lib/libsqlite3.so.0, and you installed new 3.5.2 into /usr/local/lib ? Here is my linking information: # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 = /usr/lib/libsqlite3.so.0 (0xb7eb7000) libc.so.6 = /lib/libc.so.6 (0xb7d87000) libpthread.so.0 = /lib/libpthread.so.0 (0xb7d7) /lib/ld-linux.so.2 (0x4100) On Jan 25, 2008 4:41 AM, Jim Dodgen [EMAIL PROTECTED] wrote: sorry I attached another email by accident, it's content is not related to my question Jim Jim Dodgen wrote: the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have attempted to get a version up to 3.5.2 with no success so far. anyone have any success yet? If so what is the magic. Jim - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB disappears at times...
Did you try to use it on real drive disk? On Jan 25, 2008 3:17 AM, Rasanth Akali Kandoth [EMAIL PROTECTED] wrote: Hi All, i have an application that uses sqlite3 version 3.3.17 to create a DB on a ramdisk. I see a strange issue that the DB disappears at times( the DB size is becoming zero. it was arround 16k after all my tables are created). i dont have any code in my application which deletes the db file or deletes all the tables in the db. does anyone know why this is happening? ThanksRegards, Rasanth -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DBD::SQLite for 3.5.4?
There is two way of compiling DBD::SQLite: 1. to use his own internal version of SQLite USE_LOCAL_SQLITE=1 perl Maker.pl 2. to use shared library of SQLite SQLITE_LOCATION=/path/to/libsqlite perl Makefile.pl So if you install 3.5.4 in /usr/local/lib, you should set SQLITE_LOCATION=/usr/local/lib/ On Jan 25, 2008 5:13 AM, Jim Dodgen [EMAIL PROTECTED] wrote: I have tend to build the DBD::SQLite from source, when ever I have built with it looking for sqlite libs it reports a old version older than 3.3.9 or something and then uses the current 3.4.2 stuff supplied in the module. I do have 3.5.4 installed, it migh be that there could be a older version hiding someplace. not sure how to find it or delete it. I'm on fedora also a RHES Alexander Batyrshin wrote: I have no problem with 3.5.4. Maybe your is linked with libsqlite in other dirrectory? For example your DBD::SQLite is linked against /usr/lib/libsqlite3.so.0, and you installed new 3.5.2 into /usr/local/lib ? Here is my linking information: # ldd /usr/lib/perl5/site_perl/5.8.8/i686-linux/auto/DBD/SQLite/SQLite.so libsqlite3.so.0 = /usr/lib/libsqlite3.so.0 (0xb7eb7000) libc.so.6 = /lib/libc.so.6 (0xb7d87000) libpthread.so.0 = /lib/libpthread.so.0 (0xb7d7) /lib/ld-linux.so.2 (0x4100) On Jan 25, 2008 4:41 AM, Jim Dodgen [EMAIL PROTECTED] wrote: sorry I attached another email by accident, it's content is not related to my question Jim Jim Dodgen wrote: the latest DBD::SQLite (a Perl module) was buit with 3.4.2 I have attempted to get a version up to 3.5.2 with no success so far. anyone have any success yet? If so what is the magic. Jim - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance degradation after 3.3.17 - 3.5.4 upgrade
On Jan 21, 2008 12:58 AM, Alexander Batyrshin [EMAIL PROTECTED] wrote: On Jan 20, 2008 11:32 PM, [EMAIL PROTECTED] wrote: Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello everyone. I've discover performance degradation due to update 3.3.17 - 3.5.4. This SQL query work very slowly: DELETE FROM population_stamp WHERE town_id IN ( SELECT DISTINCT town_id FROM population_stamp EXCEPT SELECT id FROM town ); I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if you omit the DISTINCT operator. EXCEPT implies DISTINCT anyway, so you are not losing anything. But SQLite does not optimize out redundant DISTINCTs so it is computing the DISTINCT twice. It's faster only for 3.5.4, but still slowly for 3.3.17 (time in seconds: us - user, sy - system) 3.5.4 +DISTINCT: 5.474us 0.287sy 3.5.4 -DISTINCT: 3.397us 0.259sy 3.3.17 -DISTINCT: 4.129us 0.228sy 3.3.17 +DISTINCT: 2.959us 0.180sy These is timing example for my small testing database. With real database difference will be more visible. D. Richard Hipp, do you have any ideas why 3.3.17 version with DISTINCT works faster that new 3.5.4 without DISTINCT? In my test case difference is greater that 0.5 second. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Performance degradation after 3.3.17 - 3.5.4 upgrade
0 100 94Rowid 0 0 95Column 0 2 96Column 0 1 97MakeIdxRec 2 0 dd 98IdxDelete 1 0 99Delete 0 1 population_stamp 100 Goto0 91 101 Close 1 19 102 Close 0 0 103 Halt0 0 104 Transaction 0 1 105 VerifyCookie0 151 106 TableLock -1 18 population_stamp 107 TableLock 0 8 town 108 Goto0 1 109 Noop0 0 Explain from 3.3.17 0|Goto|0|76| 1|Integer|0|0| 2|OpenRead|0|18| 3|SetNumColumns|0|2| 4|Rewind|0|58| 5|MemLoad|0|0| 6|If|0|45| 7|MemInt|1|0| 8|OpenEphemeral|3|0|keyinfo(1,BINARY) 9|SetNumColumns|3|1| 10|OpenEphemeral|4|1|keyinfo(1,BINARY) 11|OpenEphemeral|5|0|keyinfo(1,BINARY) 12|Integer|0|0| 13|OpenRead|2|18| 14|SetNumColumns|2|2| 15|Rewind|2|25| 16|Column|2|1| 17|MakeRecord|-1|0| 18|Distinct|5|21| 19|Pop|2|0| 20|Goto|0|24| 21|IdxInsert|5|0| 22|MakeRecord|1|0| 23|IdxInsert|4|0| 24|Next|2|16| 25|Close|2|0| 26|Integer|0|0| 27|OpenRead|1|8| 28|SetNumColumns|1|1| 29|Rewind|1|35| 30|Column|1|0| 31|MakeRecord|1|0| 32|NotFound|4|34| 33|Delete|4|0| 34|Next|1|30| 35|Close|1|0| 36|Rewind|4|44| 37|Column|4|0| 38|NotNull|-1|41| 39|Pop|1|0| 40|Goto|0|43| 41|MakeRecord|1|0|c 42|IdxInsert|3|0| 43|Next|4|37| 44|Close|4|0| 45|Integer|1|0| 46|Column|0|1| 47|NotNull|-1|51| 48|Pop|2|0| 49|Null|0|0| 50|Goto|0|54| 51|MakeRecord|1|0|c 52|Found|3|54| 53|AddImm|-1|0| 54|IfNot|1|57| 55|Rowid|0|0| 56|FifoWrite|0|0| 57|Next|0|5| 58|Close|0|0| 59|Integer|0|0| 60|OpenWrite|0|18| 61|SetNumColumns|0|3| 62|Integer|0|0| 63|OpenWrite|1|19|keyinfo(2,BINARY,BINARY) 64|FifoRead|0|73| 65|NotExists|0|72| 66|Rowid|0|0| 67|Column|0|2| 68|Column|0|1| 69|MakeIdxRec|2|0|dd 70|IdxDelete|1|0| 71|Delete|0|1|population_stamp 72|Goto|0|64| 73|Close|1|19| 74|Close|0|0| 75|Halt|0|0| 76|Transaction|0|1| 77|VerifyCookie|0|281| 78|Goto|0|1| 79|Noop|0|0| -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Performance degradation after 3.3.17 - 3.5.4 upgrade
On Jan 20, 2008 3:32 PM, Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello everyone. I've discover performance degradation due to update 3.3.17 - 3.5.4. This SQL query work very slowly: DELETE FROM population_stamp WHERE town_id IN ( SELECT DISTINCT town_id FROM population_stamp EXCEPT SELECT id FROM town ); Some info about tables: CREATE TABLE population_stamp( population int, town_id int, stamp_id int ); CREATE INDEX popstamp_idx on population_stamp(stamp_id, town_id); Some new info. Exactly this query work very-very slowly: SELECT DISTINCT town_id FROM population_stamp; 3.5.4 explain: addr opcode p1 p2 p3 -- -- -- - 0 OpenEphemeral 1 2 keyinfo(1,BINARY) 1 MemInt 0 2 2 MemInt 0 1 3 Goto0 13 4 MemInt 1 2 5 Return 0 0 6 IfMemPos1 8 7 Return 0 0 8 MemLoad 0 0 9 Callback1 0 10Return 0 0 11MemNull 0 0 12Return 0 0 13Gosub 0 11 14Goto0 41 15Integer 0 0 16OpenRead0 18 17SetNumColumns 0 2 18Rewind 0 25 19StackDepth -1 0 20Column 0 1 21Sequence1 0 22MakeRecord 2 0 23IdxInsert 1 0 24Next0 19 25Close 0 0 26Sort1 40 27Column 1 0 28MemStore4 0 29MemLoad 3 0 30Eq 512 35 collseq(BINARY) 31MemMove 3 4 32Gosub 0 6 33IfMemPos2 40 34Gosub 0 11 35Column 1 0 36MemStore0 1 37MemInt 1 1 38Next1 27 39Gosub 0 6 40Halt0 0 41Transaction 0 0 42VerifyCookie0 1619 43TableLock 0 18 population_stamp 44Goto0 15 45Noop0 0 3.3.17 explain: explain SELECT DISTINCT town_id FROM population_stamp; addr opcode p1 p2 p3 -- -- -- - 0 OpenEphemeral 1 0 keyinfo(1,BINARY) 1 Goto0 16 2 Integer 0 0 3 OpenRead0 18 4 SetNumColumns 0 2 5 Rewind 0 14 6 Column 0 1 7 MakeRecord -1 0 8 Distinct1 11 9 Pop 2 0 10Goto0 13 11IdxInsert 1 0 12Callback1 0 13Next0 6 14Close 0 0 15Halt0 0 16Transaction 0 0 17VerifyCookie0 1619 18Goto0 2 19Noop0 0 -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance degradation after 3.3.17 - 3.5.4 upgrade
On Jan 20, 2008 11:32 PM, [EMAIL PROTECTED] wrote: Alexander Batyrshin [EMAIL PROTECTED] wrote: Hello everyone. I've discover performance degradation due to update 3.3.17 - 3.5.4. This SQL query work very slowly: DELETE FROM population_stamp WHERE town_id IN ( SELECT DISTINCT town_id FROM population_stamp EXCEPT SELECT id FROM town ); I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if you omit the DISTINCT operator. EXCEPT implies DISTINCT anyway, so you are not losing anything. But SQLite does not optimize out redundant DISTINCTs so it is computing the DISTINCT twice. It's faster only for 3.5.4, but still slowly for 3.3.17 (time in seconds: us - user, sy - system) 3.5.4 +DISTINCT: 5.474us 0.287sy 3.5.4 -DISTINCT: 3.397us 0.259sy 3.3.17 -DISTINCT: 4.129us 0.228sy 3.3.17 +DISTINCT: 2.959us 0.180sy These is timing example for my small testing database. With real database difference will be more visible. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
