[sqlite] My first post, a few wishes..
1) I wish for a better way to recreate a table's definition. The FAQ example is no good if you are unknown to it's fields. For example, imy dbms obtains the create table query for the user, he might want to reset table name as add or remove fields. After that the copy as suggested in the FAQ does not know what fields to use. See also #2 2) Empty table does not return fields. After i create a table + fields, there seems to be no way to enumerate the fieldnames if there isn't any data inserted. Both cols and rows return 0. I'm using sqlite3_get_table() I heard before to make use of prepare but i can't figure out how. 3) May i suggest a better (easier) forum for sqlite? I can setup a forum independant of my own sites. No commercials etc..
[sqlite] Questions about VDBE, deadlocks and SQLITE_BUSY
Some pysqlite users are experiencing problems with locks not going away, as described in http://www.third-bit.com/trac/argon/wiki/Locking In order to fix this, I need some information: - is it true that after sqlite3_prepare, only the first call to sqlite3_step can return SQLITE_BUSY, or can subsequent calls also return SQLITE_BUSY? - like described in the wiki page, is it true that if sqlite3_step returns SQLITE_BUSY, I need to sqlite3_reset or sqlite3_finalize to avoid potential deadlocks? If that's true, I will need to sqlite3_reset, then rebind the parameters again and try sqlite3_step again, right? How did others avoid the locking problems described above? Other people implementing SQLite wrappers must have similar issues, I suppose ... Cheers, -- Gerhard -- Gerhard Häring - [EMAIL PROTECTED] - Python, web database development signature.asc Description: Digital signature
Re: [sqlite] Questions about VDBE, deadlocks and SQLITE_BUSY
On Thu, 2005-07-07 at 12:56 +0200, Gerhard Haering wrote: - is it true that after sqlite3_prepare, only the first call to sqlite3_step can return SQLITE_BUSY, or can subsequent calls also return SQLITE_BUSY? Yes. I think this is true. At least I cannot think of any path by which you could get SQLITE_BUSY on 2nd or subsequent calls to sqlite3_step(). All the locks required are obtained on the very first call. Note however that a call to sqlite3_exec() is really divided up into multiple calls to sqlite3_prepare() and sqlite3_step(), one each for each complete SQL statement in the argument to sqlite3_exec. So one statement could run to completion then a second statement could hit a lock. But that cannot happen when using sqlite3_prepare() directly because sqlite3_prepare() will only compile one statement at a time. - like described in the wiki page, is it true that if sqlite3_step returns SQLITE_BUSY, I need to sqlite3_reset or sqlite3_finalize to avoid potential deadlocks? If that's true, I will need to sqlite3_reset, then rebind the parameters again and try sqlite3_step again, right? This is *not* correct. In fact, I do not see how calling sqlite3_finalize or sqlite3_reset is even helpful in resolving the lock issue. To avoid the deadlock, some thread needs to prepare and run a COMMIT or ROLLBACK. Resetting or finalizing other statements will not help. A typical scenario follows. I use the term thread to mean either a traditional thread or a full blown process - both work the same way. 1. Threads A and B both execute BEGIN. This acquires no locks or otherwise touches the database in any way. It just turns off auto-commit mode in both threads. 2. Threads A and B both execute SELECT This acquires a shared lock for both threads. This works fine. 3. Threads A and B both execute UPDATE One thread (let's say thread A) succeeds, the other gets an SQLITE_BUSY reply. The first thread gets a reserved lock before doing the update. a reserved lock is a kind of exclusive lock so the other thread blocks and has to fail. Thread A was successful at the UPDATE command, but all the changes are still held in RAM - nothing can be written to disk until thread B releases its shared lock. If the UPDATE is large so that the changes will not all fit in SQLite's page cache, then both threads will get an SQLITE_BUSY return. 4. Thread A tries to COMMIT. It gets an SQLITE_BUSY return because thread B is still holding a shared lock on the database. At this point we are stuck. Either thread A will need to ROLLBACK to let thread B proceed. Or thread B will have to either ROLLBACK or COMMIT (the same thing in this case since thread B has made not changes) to let thread A proceed. One or the other of the two threads will need to prepare and step a ROLLBACK. Just resetting or finalizing previous statements and retrying them will not help. One way to avoid this scenario is to always use BEGIN EXCLUSIVE instead of just BEGIN. BEGIN EXCLUSIVE always goes ahead and gets an exclusive lock on the database file or returns SQLITE_BUSY if it cannot. That way, you can never get two threads holding a shared lock and both trying to promote to an exclusive lock. This avoids the deadlock scenario described above, but it also reduces the amount of concurrency. So it is a tradeoff. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] My first post, a few wishes..
On 7/7/05, Edwin Knoppert [EMAIL PROTECTED] wrote: 1) I wish for a better way to recreate a table's definition. The FAQ example is no good if you are unknown to it's fields. For example, imy dbms obtains the create table query for the user, he might want to reset table name as add or remove fields. After that the copy as suggested in the FAQ does not know what fields to use. SELECT name FROM sqlite_master WHERE type='table' ORDER BY name; Is this what you are after? This is (9) of the FAQ. See also #2 2) Empty table does not return fields. After i create a table + fields, there seems to be no way to enumerate the fieldnames if there isn't any data inserted. Both cols and rows return 0. I'm using sqlite3_get_table() I heard before to make use of prepare but i can't figure out how. 3) May i suggest a better (easier) forum for sqlite? I can setup a forum independant of my own sites. No commercials etc.. -- Kiel W. [EMAIL PROTECTED] -- time is swift
RE: [sqlite] My first post, a few wishes..
I took it to mean that he wants: SELECT sql FROM sqlite_master WHERE type='table'; But I could be wrong. -Original Message- From: Kiel W. [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 8:52 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] My first post, a few wishes.. On 7/7/05, Edwin Knoppert [EMAIL PROTECTED] wrote: 1) I wish for a better way to recreate a table's definition. The FAQ example is no good if you are unknown to it's fields. For example, imy dbms obtains the create table query for the user, he might want to reset table name as add or remove fields. After that the copy as suggested in the FAQ does not know what fields to use. SELECT name FROM sqlite_master WHERE type='table' ORDER BY name; Is this what you are after? This is (9) of the FAQ. See also #2 2) Empty table does not return fields. After i create a table + fields, there seems to be no way to enumerate the fieldnames if there isn't any data inserted. Both cols and rows return 0. I'm using sqlite3_get_table() I heard before to make use of prepare but i can't figure out how. 3) May i suggest a better (easier) forum for sqlite? I can setup a forum independant of my own sites. No commercials etc.. -- Kiel W. [EMAIL PROTECTED] -- time is swift
Re: [sqlite] My first post, a few wishes..
Sorry, i seem to have posted to another thread somehow.. No you misunderstood me, i'm well able to obtain the fields but it seems not to have the fieldnames when no rows where inserted. ? - Original Message - From: Kiel W. [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Thursday, July 07, 2005 2:52 PM Subject: Re: [sqlite] My first post, a few wishes.. On 7/7/05, Edwin Knoppert [EMAIL PROTECTED] wrote: 1) I wish for a better way to recreate a table's definition. The FAQ example is no good if you are unknown to it's fields. For example, imy dbms obtains the create table query for the user, he might want to reset table name as add or remove fields. After that the copy as suggested in the FAQ does not know what fields to use. SELECT name FROM sqlite_master WHERE type='table' ORDER BY name; Is this what you are after? This is (9) of the FAQ. See also #2 2) Empty table does not return fields. After i create a table + fields, there seems to be no way to enumerate the fieldnames if there isn't any data inserted. Both cols and rows return 0. I'm using sqlite3_get_table() I heard before to make use of prepare but i can't figure out how. 3) May i suggest a better (easier) forum for sqlite? I can setup a forum independant of my own sites. No commercials etc.. -- Kiel W. [EMAIL PROTECTED] -- time is swift
Re: [sqlite] My first post, a few wishes..
Edwin Knoppert [EMAIL PROTECTED] writes: Sorry, i seem to have posted to another thread somehow.. No you misunderstood me, i'm well able to obtain the fields but it seems not to have the fieldnames when no rows where inserted. I think what you're looking for is PRAGMA TABLE_INFO(table_name) SQLite version 3.1.3.1 Enter .help for instructions sqlite create table aaa(i integer, t text); sqlite .mode line sqlite pragma table_info(aaa); cid = 0 name = i type = integer notnull = 0 dflt_value = pk = 0 cid = 1 name = t type = text notnull = 0 dflt_value = pk = 0 sqlite
Re: [sqlite] My first post, a few wishes..
I'm not using c and thus i do not compile the source into my app :) So i'm calling sqlite3_get_table() to obtain data. It's purely to instruct the sqlite 3 dll, think as if i'm where using VB6 (without some 'odd' wrapper/helper dll's) I'm actually using PowerBASIC - PB/WIN80. I'm also having trouble obtaining the field types, but that will be another post. - Original Message - From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Cc: Kiel W. [EMAIL PROTECTED] Sent: Thursday, July 07, 2005 3:35 PM Subject: Re: [sqlite] My first post, a few wishes.. Edwin Knoppert [EMAIL PROTECTED] writes: Sorry, i seem to have posted to another thread somehow.. No you misunderstood me, i'm well able to obtain the fields but it seems not to have the fieldnames when no rows where inserted. I think what you're looking for is PRAGMA TABLE_INFO(table_name) SQLite version 3.1.3.1 Enter .help for instructions sqlite create table aaa(i integer, t text); sqlite .mode line sqlite pragma table_info(aaa); cid = 0 name = i type = integer notnull = 0 dflt_value = pk = 0 cid = 1 name = t type = text notnull = 0 dflt_value = pk = 0 sqlite
Re: [sqlite] Memory Allocated
On Wed, 6 Jul 2005, John Dean wrote: At 13:53 06/07/2005, you wrote: On Wed, 2005-07-06 at 09:38 -0300, Gerson LuÃs Fontoura Vaz wrote: I'm using SQLite for the first time... We are using SQLite version 2.8.15. Why, oh why are you using 2.8.15 when 3.2.2 is known to be faster, smaller, and to contain many more features??? I haven't made the switch because some APIs from 2.8.15 were never ported over to 3.x.x. If they did they certainly were not included in the docs. This makes writing an updated driver for Rekall somewhat time consuming. I would have thought retaining backward compatibility to V2.8.x could be taken for granted but it seems I am wrong. What's happened to all those nice sqlite__printf convenience functions How about this: http://www.sqlite.org/capi3ref.html#sqlite3_mprintf Perhaps if someone could write a sqlite.h for use with SQLite 3, that translated SQLite 2 calls to SQLite 3 API, then migration could be smoother for the likes of yourself. Such a wrapper would have to optional, as the SQLite 3 API was designed to be disjoint from the SQLite 2 API to allow the two to co-exist. Regards John Dean, Christian -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] My first post, a few wishes..
Edwin Knoppert wrote: I'm not using c and thus i do not compile the source into my app :) So i'm calling sqlite3_get_table() to obtain data. It's purely to instruct the sqlite 3 dll, think as if i'm where using VB6 (without some 'odd' wrapper/helper dll's) I'm actually using PowerBASIC - PB/WIN80. SQLite version 3.1.3.1 Enter .help for instructions sqlite create table aaa(i integer, t text); sqlite .mode line sqlite pragma table_info(aaa); cid = 0 name = i type = integer notnull = 0 dflt_value = pk = 0 cid = 1 name = t type = text notnull = 0 dflt_value = pk = 0 sqlite You can issue/execute a pragma as a query. From a pythonwin session: import sqlite con=sqlite.connect(test) csr=con.cursor() csr.execute(create table aaa(i integer, t text)) csr.execute(pragma table_info(aaa)) result=csr.fetchall() for r in result: ... print r ... (0, 'i', 'integer', 0, None, 0) (1, 't', 'text', 0, None, 0) Martin aka xqp [my first post too]
[sqlite] freepages.c ported to sqlite v3. Request feedback.
Hello. Several months ago I ported the free pages code from sqlite v2 to v3. I found the original at [1]. My copy is at [2]. I unsuccessfully tried to contact the original author. This code simply computes the amount of slack space in the database. Our database will sometimes grow quite large and then most of the rows will be deleted. We want to reclaim the space, but due to the way our application works, it is not efficient to vacuum the database right after we delete the rows in question. So, we periodically call the free space routine and vacuum our database if the amount of slack space exceeds a certain threshold. Good, bad or ugly, that is what we do :) I would like to see this code ultimately merged into the official sqlite database distribution (so that we are not distributing a hacked version). However, I am not an sqlite genius. I would very much appreciate some peer-review. (For example, I removed all of the database integrity checks b/c the v2 code failed on a v3 system. I also had to copy many struct definitions from other files as these structures were not exposed via header files. These hacks seem ugly to me but I'm not sure what to do about it). I grant my code to the community. I can be licensed under the current sqlite license. Your comments are appreciated. Thank you. [1] http://web.utk.edu/~jplyon/sqlite/code/freepages.c [2] http://unwg.no-ip.com/freepages.c
Re: [sqlite] freepages.c ported to sqlite v3. Request feedback.
On Thu, 7 Jul 2005, Dennis Jenkins wrote: Hello. Several months ago I ported the free pages code from sqlite v2 to v3. I found the original at [1]. My copy is at [2]. I unsuccessfully tried to contact the original author. This code simply computes the amount of slack space in the database. Our database will sometimes grow quite large and then most of the rows will be deleted. We want to reclaim the space, but due to the way our application works, it is not efficient to vacuum the database right after we delete the rows in question. So, we periodically call the free space routine and vacuum our database if the amount of slack space exceeds a certain threshold. Good, bad or ugly, that is what we do :) Why bother? That the file got that big in the first place indicates space is not a problem. KISS, disk space is cheap. I would like to see this code ultimately merged into the official sqlite database distribution (so that we are not distributing a hacked version). However, I am not an sqlite genius. I would very much appreciate some peer-review. (For example, I removed all of the database integrity checks b/c the v2 code failed on a v3 system. I also had to copy many struct definitions from other files as these structures were not exposed via header files. These hacks seem ugly to me but I'm not sure what to do about it). Rather than bypassing the btree and pager code, such functionality could be added to the Btree API, and PRAGMAs created to retrieve information. Something like: PRAGMA get_page_count; PRAGMA get_free_page_count; Problem here is that there is no way of specifying which attached database to get the counts for. The other alternative is to add the btree functions and write a custom shell that links against btree.o etc., much like the original freepages.c. I grant my code to the community. I can be licensed under the current sqlite license. Your comments are appreciated. Thank you. [1] http://web.utk.edu/~jplyon/sqlite/code/freepages.c [2] http://unwg.no-ip.com/freepages.c -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] conditional queries against views
Can anyone explain why these queries don't work? And, is there a workaround? create table t1 (id int); create table t2 (id int, name varchar(32)); create view v1 as select a.id, b.name from t1 a, t2 b where a.id=b.id; select * from v1 where a.id=1; select * from v1 where t1.id=1; select * from v1 where t2.id=1; select * from v1 where id=1; -- Marvin Keith Bellamy Software Engineer Innovision Corporation 913.438.3200
[sqlite] difference between sqlite3_reset() and sqlite3_clear_bindings()
Can someone please explain the difference between sqlite3_reset() and sqlite3_clear_bindings() and when would I use one over the other. I've read the 3.2.2 docs and it's not 100% clear to me. Thanks
Re: [sqlite] conditional queries against views
On Thursday 07 July 2005 02:19 pm, Marvin Bellamy wrote: Can anyone explain why these queries don't work? And, is there a workaround? create table t1 (id int); create table t2 (id int, name varchar(32)); create view v1 as select a.id, b.name from t1 a, t2 b where a.id=b.id; ugly way of doing a join query but we'll go with it ;) better to use this instead: create view v2 as select a.id, b.name from t1 a inner join t2 b on a.id=b.id; select * from v1 where a.id=1; bad column name select * from v1 where t1.id=1; same here select * from v1 where t2.id=1; and here select * from v1 where id=1; this one works for me. $ sqlite SQLite version 2.8.16 Enter .help for instructions sqlite create table t1(id int); sqlite create table t2 (id int, name varchar(32)); sqlite insert into t1 values(1); sqlite insert into t1 values(2); sqlite insert into t1 values(3); sqlite insert into t2 values(1, 'blah'); sqlite insert into t2 values(2, 'blah2'); sqlite create view v1 as select a.id, b.name from t1 a, t2 b where a.id=b.id; sqlite select * from v1; 1|blah 2|blah2 sqlite .headers on sqlite select * from v1; id|name 1|blah 2|blah2 sqlite select * from v1 where id=1; id|name 1|blah sqlite select * from v1 where a.id=1; SQL error: no such column: a.id sqlite select * from v1 where t1.id=1; SQL error: no such column: t1.id sqlite select * from v1 where t2.id=1; SQL error: no such column: t2.id
Re: [sqlite] conditional queries against views
Thanks for the join tip. This must be a 3.2.1-specific bug, because that last example doesn't work for me. Looks like its been reported already. -- Marvin Keith Bellamy Software Engineer Innovision Corporation 913.438.3200 Stephen Leaf wrote: On Thursday 07 July 2005 02:19 pm, Marvin Bellamy wrote: Can anyone explain why these queries don't work? And, is there a workaround? create table t1 (id int); create table t2 (id int, name varchar(32)); create view v1 as select a.id, b.name from t1 a, t2 b where a.id=b.id; ugly way of doing a join query but we'll go with it ;) better to use this instead: create view v2 as select a.id, b.name from t1 a inner join t2 b on a.id=b.id; select * from v1 where a.id=1; bad column name select * from v1 where t1.id=1; same here select * from v1 where t2.id=1; and here select * from v1 where id=1; this one works for me. $ sqlite SQLite version 2.8.16 Enter .help for instructions sqlite create table t1(id int); sqlite create table t2 (id int, name varchar(32)); sqlite insert into t1 values(1); sqlite insert into t1 values(2); sqlite insert into t1 values(3); sqlite insert into t2 values(1, 'blah'); sqlite insert into t2 values(2, 'blah2'); sqlite create view v1 as select a.id, b.name from t1 a, t2 b where a.id=b.id; sqlite select * from v1; 1|blah 2|blah2 sqlite .headers on sqlite select * from v1; id|name 1|blah 2|blah2 sqlite select * from v1 where id=1; id|name 1|blah sqlite select * from v1 where a.id=1; SQL error: no such column: a.id sqlite select * from v1 where t1.id=1; SQL error: no such column: t1.id sqlite select * from v1 where t2.id=1; SQL error: no such column: t2.id
Re: [sqlite] conditional queries against views
On Thursday 07 July 2005 04:13 pm, Marvin Bellamy wrote: Thanks for the join tip. This must be a 3.2.1-specific bug, because that last example doesn't work for me. Looks like its been reported already. Oops.. guess I missed that. this works great: sqlite create view v2 as select a.id as id, b.name as name from t1 a inner join t2 b on a.id=b.id; simply give them an alias. sqlite select * from v2 where id=1; id|name 1|foo this is also using 3.2.1
[sqlite] SQLite encryption key length?
For those using the encrypted SQLite extension: The instructions say the max key length can be 256 *bytes*, and looking at the code does seem to confirm this. But there is also a comment at the top of the encryption code which says change the maximum key size to 56 *bits* in order to comply with US Government export So is it 256 bits or bytes which is the true max key length that should be used (assuming non-export) ? -Dave
Re: [sqlite] Mozilla + SQLite?
On Wed, 2005-07-06 at 08:40 -0700, Scott Baker wrote: Looks like Firefox is gearing up to store some of its information in SQLite? Does anyone know anything more about this? http://gemal.dk/blog/2005/07/06/mozilla_firefox_bookmarks_in_for_a_rewrite/ We'll be using sqlite, but not directly -- there is a layer currently called mozStorage which is basically a XPCOM/C++ wrapper around sqlite that I've written, along with convenience wrappers for working with mozStorage within JavaScript. (For example, statement parameters can be assigned via stmt.params.paramName = 'value' and simple statements can just be called as functions, as in stmt(param1, param2);) On 7/6/05, D. Richard Hipp [EMAIL PROTECTED] wrote: The copy of Firefox I use (version 1.0 that comes with SuSE 9.2) stores all its configuration information and cache in a bunch of files under ~/.mozilla/firefox. If I try to launch two versions of firefox as the same user but on separate displays (for example one on the console and another on a remove X terminal or on an Xvnc server) the second one has problems because the two instances cannot share configuration files without risking collisions. And if I power-off without a clean shutdown, lock files persist which I have to clean up manually. Moving configuration information into an SQLite database will resolve these issues, I hope. Because SQLite transactions are isolated, multiple instances of Firefox will be able to share the same configuration. And because SQLite transactions are atomic, a power-off in the middle of a transaction will cause the transaction to roll back automatically. I *hope* that is what the SQLite integration with firefox will accomplish. But again, I don't really know. That is basically the plan; some design information is at http://wiki.mozilla.org/Mozilla2:Unified_Storage , though it needs to be updated somewhat. I don't think we'll be able to do a full conversion in time for the next release (after 1.1), but we'll certainly complete the process halfway through. There are some problems that we're going to have to resolve for profile sharing to fully work, mainly getting cross-application triggers to work correctly. We're thinking to just build a separate trigger mechanism within mozStorage, and have each app that makes changes distribute trigger notifications with an IPC system to other apps using the same database... but we're not that far yet :) Note that the next release of Sunbird (the calendar project) and the first release of Lightning (calendar integration in thunderbird) uses mozStorage/sqlite for storing all local calendar data. - Vlad
Re: [sqlite] difference between sqlite3_reset() and sqlite3_clear_bindings()
--- Patrick Dunnigan [EMAIL PROTECTED] wrote: Can someone please explain the difference between sqlite3_reset() and sqlite3_clear_bindings() and when would I use one over the other. _clear_bindings() sets the bound values of all SQL variables in a compiled statement to NULL. I don't know why people need to do this. _reset() makes a compiled statement ready to execute from the beginning again. Bound variable values are unchanged by a call to sqlite3_reset(). Use it instead of sqlite3_finalize() if you want to execute the same compiled statement a second time. Sell on Yahoo! Auctions no fees. Bid on great items. http://auctions.yahoo.com/
