Re: [sqlite] function auxiliary data
Hi Igor, On Tue, Jul 16, 2013 at 7:06 PM, Igor Tandetnik i...@tandetnik.org wrote: The metadata only survives a single iteration over the statement; in other words, it is cleared when you call sqlite3_reset (from the documentation: SQLite will invoke the destructor function... when the SQL statement completes). And you can't rebind the parameter in the middle of an iteration. So it looks like everything should work the way you want. This is what I hoped, but I'm using the auxdata functions in an extension I'm developing, and the behavior I'm experiencing is a bit different. At present I'm accessing the database from python, so I don't know the exact moment when sqlite3_reset might get called, but from what I'm seeing, things work as expected as long as literals are used. With parameters binding, if the same query is repeatedly executed, it behaves as if metadata were preserved (in my case this means that the actual values passed to later queries are ignored and the results are determined by the values passed to the first query execution). In a test performed using APSW I disabled the statements cache and the code behaved as expected also with parameters binding. This brought me to think that the metadata associated to the parameters in a compiled statement might be preserved until the statement is finalized, but I will investigate things more in detail. Best regards, Riccardo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Passing collation to xBestIndex
Hello, in virtual table mechanism is missing a way to correctly handle following queries: SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE; SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE; To xBestIndex function is passed only constraint field = 'abc', but there is no way to correctly compare (by correctly I mean accordingly to passed collation) those strings because of missing collation functions in constraint definition. Patch for this issue exists for a long time: http://osdir.com/ml/sqlite-users/2011-09/msg00152.html Is there any chance that it could get merged? Dusan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] function auxiliary data
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [I'm the APSW author] On 17/07/13 00:59, Riccardo Vianello wrote: In a test performed using APSW I disabled the statements cache and the code behaved as expected also with parameters binding. This brought me to think that the metadata associated to the parameters in a compiled statement might be preserved until the statement is finalized, but I will investigate things more in detail. APSW does sqlite3_reset the statement in many circumstances, including hitting the end of the results and on errors. sqlite3_finalize is only called when the statement is being destroyed which includes eviction from the statement cache, and on getting SQLITE_SCHEMA error. I do wish SQLite had a standard statement cache available. Pretty much all wrappers implement their own which is duplicate work and tricky to get right. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlHmaBcACgkQmOOfHg372QS7nwCfYVAcvSQSD0/aNmY/OGwdG6KI ZOQAmgLRuJaHxR+BJHSjtK9VU79yFlPR =w0yh -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On Tue, Jul 16, 2013 at 8:13 PM, Eduardo emorr...@yahoo.es wrote: Can you show us the query and/or schemas? If not: Sure, I appended everything in the bottom of this email. Unfortunately gmail will mess-up the layout, I hope it will be readable. (See here, it seems google does not know the mean of should) https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04 a) Does it JOIN multiple big tables (in rows and/or columns)? No, but I got a sub query is probably the culprit. e) Did you normalize the database? Should be, yes. In both cases (if you can show us the query/schema or not) what do you really want to ask to the database? (not to us but to sqlite3, perhaps the query can be reformulated) Here is everything... I think the problem is shown in the: Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows) that is part of a sub-query. -- First statement (sets the pragmas): PRAGMA foreign_keys = ON; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY; PRAGMA cache_size = -10240; PRAGMA auto_vacuum = NONE; PRAGMA locking_mode = EXCLUSIVE; PRAGMA secure_delete = OFF; PRAGMA temp_store = 0; -- Second statement (creates/clean the schema): BEGIN TRANSACTION; DROP TABLE IF EXISTS tour; CREATE TABLE tour (id integer, score integer NOT NULL, cost integer NOT NULL, last_poi integer, unsorted_path blob, unsorted_path_tmp blob, PRIMARY KEY(id)); DROP INDEX IF EXISTS tour_unsorted_path_idx; CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); DROP TABLE IF EXISTS categories; CREATE TABLE categories (tour_id integer NOT NULL, order_idx integer NOT NULL, value integer NOT NULL, FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE); DROP INDEX IF EXISTS cats_tour_id_idx; CREATE INDEX cats_tour_id_idx ON categories (tour_id); DROP TABLE IF EXISTS path; CREATE TABLE path (tour_id integer NOT NULL, order_idx integer NOT NULL, node_id integer NOT NULL, FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE); DROP INDEX IF EXISTS path_tour_id_idx; CREATE INDEX path_tour_id_idx ON path (tour_id); DROP TRIGGER IF EXISTS set_last_poi_trg; CREATE TRIGGER set_last_poi_trg AFTER INSERT ON path BEGIN UPDATE tour SET last_poi = ( SELECT node_id FROM path WHERE tour_id == NEW.tour_id ORDER BY order_idx DESC LIMIT 1 ) WHERE id == NEW.tour_id; END; COMMIT; VACUUM; -- Finally the statement executed in a normal program -- execution with the explain if appliable. -- I prepare all this statements and put in hash table -- and finalize them at the end of the program. -- This output comes from the first time, when I store them. SQL STATEMENT: INSERT INTO path (tour_id, order_idx, node_id) VALUES (?1, ?2, ?3) SQL STATEMENT: INSERT INTO tour (score, cost) VALUES (?1, ?2) SQL STATEMENT: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2 8 - Query: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2 Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) - 8 SQL STATEMENT: UPDATE tour SET unsorted_path=unsorted_path_tmp, unsorted_path_tmp = NULL WHERE id = ?1; 8 - Query: UPDATE tour SET unsorted_path=unsorted_path_tmp, unsorted_path_tmp = NULL WHERE id = ?1; Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) - 8 SQL STATEMENT: INSERT INTO categories (tour_id, order_idx, value) VALUES (?1, ?2, ?3) SQL STATEMENT: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost t2.cost) 8 - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost t2.cost) Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~25 rows) - 8 8 - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost t2.cost) Explain: 0 0 0 EXECUTE LIST SUBQUERY 0 - 8 8 - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost t2.cost) Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows) - 8 8 - Query: DELETE FROM tour WHERE id IN (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi AND t1.cost
Re: [sqlite] Passing collation to xBestIndex
On 07/17/2013 03:46 PM, Dušan Paulovič wrote: Hello, in virtual table mechanism is missing a way to correctly handle following queries: SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE; SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE; To xBestIndex function is passed only constraint field = 'abc', but there is no way to correctly compare (by correctly I mean accordingly to passed collation) those strings because of missing collation functions in constraint definition. Patch for this issue exists for a long time: http://osdir.com/ml/sqlite-users/2011-09/msg00152.html Is there any chance that it could get merged? I think the main problem is that it is not binary compatible. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing collation to xBestIndex
What do you mean? I use it for a while on Windows and all works. Binary not compatible with what? Dusan 2013/7/17 Dan Kennedy danielk1...@gmail.com On 07/17/2013 03:46 PM, Dušan Paulovič wrote: Hello, in virtual table mechanism is missing a way to correctly handle following queries: SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE; SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE; To xBestIndex function is passed only constraint field = 'abc', but there is no way to correctly compare (by correctly I mean accordingly to passed collation) those strings because of missing collation functions in constraint definition. Patch for this issue exists for a long time: http://osdir.com/ml/sqlite-**users/2011-09/msg00152.htmlhttp://osdir.com/ml/sqlite-users/2011-09/msg00152.html Is there any chance that it could get merged? I think the main problem is that it is not binary compatible. Dan. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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
Re: [sqlite] Passing collation to xBestIndex
On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič paulo...@gisoft.cz wrote: What do you mean? I use it for a while on Windows and all works. Binary not compatible with what? What Dan means is that libraries built with and without this feature might not be binary compatible. i compile against an sqlite3 DLL without this feature, then my sysadmin updates libsqlite3 to one with this feature, and my binary will then likely segfault at some point (or otherwise invoke undefined behaviour) because the binary signatures of the sqlite structures in my app no longer match those in the library. A recompile of my app (with no code changes) would be necessary to fix this. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 : Corrupt/IO Error
Hi, I have question on SQLITE_CORRUPT, SQLITE_IOERR occur during database access. Question 1) How to get generated this kind of error for testing. 2) How do we make database safe from these error Or What is Possibility to access database after error. Cheers- Techi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] function auxiliary data
Hi Roger, thanks for the additional information (and also thanks a lot for APSW, it's a really valuable tool). On Wed, Jul 17, 2013 at 11:47 AM, Roger Binns rog...@rogerbinns.com wrote: APSW does sqlite3_reset the statement in many circumstances, including hitting the end of the results and on errors. sqlite3_finalize is only called when the statement is being destroyed which includes eviction from the statement cache, and on getting SQLITE_SCHEMA error. I do wish SQLite had a standard statement cache available. Pretty much all wrappers implement their own which is duplicate work and tricky to get right. I think the statement cache is not probably the cause here (although it's apparently quite convenient to me that APSW allows switching it off and have the same statement compiled again, I'm not sure I can do the same with pysqlite), but since sqlite3_reset doesn't clear the binding and since (I guess) the relevant use case for these auxiliary data is storing results that depend on the parameter values, if the metadata is not destroyed when a parameter is reassigned then the overall usability for this feature seems somewhat reduced to me. -- Riccardo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 : Corrupt/IO Error
On Wed, Jul 17, 2013 at 12:55 PM, techi eth techi...@gmail.com wrote: Hi, I have question on SQLITE_CORRUPT, SQLITE_IOERR occur during database access. Question 1) How to get generated this kind of error for testing. While your application has the db opened, use a tool of your choice to overwrite part of the db file. e.g. load it in a text editor and re-save it. That should cause your app to fail fairly quickly. 2) How do we make database safe from these error Or What is Possibility to access database after error. By definition, a corrupt db cannot be safely/reliably accessed. Once it is corrupt, any access has undefined behaviour. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 : Corrupt/IO Error
On Wed, Jul 17, 2013 at 12:55 PM, techi eth techi...@gmail.com wrote: 2) How do we make database safe from these error Or What is Possibility to access database after error. After error, as Stephan said you are out of luck. You should avoid this errors in the first place: - make backup often, - keep the safety features of sqlite3 on, - use a error resilient filesystem (e.g., zfs), - use ecc memory. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing collation to xBestIndex
But it is the same as with new functions in sqlite3_module. Old SQLite extensions does not implement xRename function which is now needed. Also, new feature could be made optional using macro switch like some other features in SQLite. 2013/7/17 Stephan Beal sgb...@googlemail.com On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič paulo...@gisoft.cz wrote: What do you mean? I use it for a while on Windows and all works. Binary not compatible with what? What Dan means is that libraries built with and without this feature might not be binary compatible. i compile against an sqlite3 DLL without this feature, then my sysadmin updates libsqlite3 to one with this feature, and my binary will then likely segfault at some point (or otherwise invoke undefined behaviour) because the binary signatures of the sqlite structures in my app no longer match those in the library. A recompile of my app (with no code changes) would be necessary to fix this. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ 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
Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off
On 16.07.2013 13:26, Bernd Lehmkuhl wrote:
On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl be...@web.de wrote:
Am 15.07.2013 22:26, schrieb Simon Slavin:
The following two statements do different things.
INSERT INTO myTable VALUES (01)
INSERT INTO myTable VALUES ('01')
Can you tell what's being done in your setup ? Is there a way using your API
that you can stress that the value you're binding or inserting is text, not a
number ?
As I use a parameterized query, I'm pretty certain that it should be '01' - the
second case. Stepping through the code in VS Debugger also shows that DbType of
that parameter is String and Value is '01'.
If the column in the table really is defined as TEXT, and the INSERT commands
do have apostrophes around the values, then SQLite3 should not be losing that
zero.
Can you open the database in some other tool (e.g. the sqlite3 command-line
tool, available from the SQLite site) and see what the table schema says ? Your
commands should be something like
sqlite3 myDatabaseFile
.schema
.quit
If you have lots of tables you can do .schema mytable instead of just
.schema.
If you want to experiment you can manually type in an INSERT yourself, then do
a SELECT and see whether the zeros were preserved.
Simon.
Hi Simon,
The actual table definition is :
CREATE TABLE T_VWG_ABFALLVERZEICHNIS (SCHLUESSEL String, BESCHREIBUNG String,
BEMERKUNG String, ID_GUID String, IST_PAUSCHALIERT String,
IST_KATEGORIE_ESK_BETRIEBSSTOF String);
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select schluessel, typeof(schluessel)
... from t_vwg_abfallverzeichnis
... where id_guid in ('BEE7F6108F02416FA0C9D5DC777DB172',
'6061A3864C2546C4A7DDA9FDB321459F');
1|integer
01 01|text
sqlite
I *expected* to gain TEXT affinity through the use of System.Data.SQLite and
it's strongly typed types (doppelt gemoppelt?), but apparently this is not
totally effective. Maybe Joe Mistachkin can say something about that?
Bernd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
String is not a valid type specifier for TEXT colums see
http://www.sqlite.org/datatype3.html type names which result in TEXT
affinity.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite locking issue with ATTACH'ed databases
Hi everyone,
I have an interesting locking problem that I'm wondering if someone can help
with some insight.
I have a master database with some metadata, and several sub-databases to store
logging events. I have one reader object and one writer object that attach to
the sub-databases and encapsulate the read/write operations respectively.
I've found a very unexpected locking behavior with the attached databases and
exclusive transactions. One of my unit tests does the following:
Begin EXCLUSIVE TRANSACTION;
insert several rows of data;
Commit transaction;
Prepare query statement;
Iterate through one or more rows;
Reset statement;
Attempt to begin transaction; --- SQLITE_BUSY
Would like to write more here, but can't unless I close/open the connection;
I can't seem to figure out any reason why I can't create a new exclusive
transaction here, and I feel it must have to do with the fact that I have
attached to sub-databases (possibly the same sub-database) with my
reader/writer objects. This is single threaded and only database connection
(with attach/detach logic).
I have verified that all statements prepared by the connection are properly
reset - this is handled by my C++ wrappers, and any errors will throw an
exception. I even iterated through all of the current statements with the
following code immediately before my transaction failure, with no results:
sqlite3_stmt *stmt = NULL;
while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
{
if (sqlite3_stmt_busy(stmt))
{
const char* sql = sqlite3_sql(stmt);
std::cout sql \r\n;
}
}
Can anyone think of a reason why attached databases would prevent entering a
second transaction? BTW, it doesn't seem to work with immediate transactions
either. If I remove the query, everything works fine.
Thanks!
This email, including any attachments and files transmitted with it, are for
the sole use of the intended recipient(s) to whom this email is addressed, and
may contain confidential and/or privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the
intended recipient, please be advised that you have received this email in
error, and please contact the sender by reply email and destroy all copies
(including all electronic and hard copies) of the original message. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On Wed, 17 Jul 2013 12:04:52 +0200 Paolo Bolzoni paolo.bolzoni.br...@gmail.com wrote: On Tue, Jul 16, 2013 at 8:13 PM, Eduardo emorr...@yahoo.es wrote: Can you show us the query and/or schemas? If not: Sure, I appended everything in the bottom of this email. Unfortunately gmail will mess-up the layout, I hope it will be readable. (See here, it seems google does not know the mean of should) https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04 a) Does it JOIN multiple big tables (in rows and/or columns)? No, but I got a sub query is probably the culprit. e) Did you normalize the database? Should be, yes. In both cases (if you can show us the query/schema or not) what do you really want to ask to the database? (not to us but to sqlite3, perhaps the query can be reformulated) Here is everything... I think the problem is shown in the: Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows) that is part of a sub-query. -- First statement (sets the pragmas): PRAGMA foreign_keys = ON; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY; PRAGMA cache_size = -10240; PRAGMA auto_vacuum = NONE; PRAGMA locking_mode = EXCLUSIVE; PRAGMA secure_delete = OFF; PRAGMA temp_store = 0; -- Second statement (creates/clean the schema): BEGIN TRANSACTION; DROP TABLE IF EXISTS tour; CREATE TABLE tour (id integer, score integer NOT NULL, cost integer NOT NULL, last_poi integer, unsorted_path blob, unsorted_path_tmp blob, PRIMARY KEY(id)); Some changes, if blob is bigger than a few bytes, you should normalize them. If 2 blobs are equal, their id must be equal and you don't waste time comparing nor memory joining blob content. So you get: DROP TABLE IF EXISTS tour_blob; CREATE TABLE tour_blob (id INTEGER PRIMARY KEY, n_blob blob); DROP TABLE IF EXISTS tour; CREATE TABLE tour (id integer, score integer NOT NULL, cost integer NOT NULL, last_poi integer, FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON DELETE CASACADE, FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) ON DELETE CASACADE, PRIMARY KEY(id)); You can replace tour_unsorted_path_idx with a new index too: DROP INDEX IF EXISTS tour_unsorted_path_idx; CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx; CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, last_poi, cost); Take care and make cost the last one, because cost is compared with inequality. You use '==' instead '=', take care too. I made the same error in a mail some weeks ago. For this query: SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1 8 - Query: SELECT id FROM tour ORDER BY id LIMIT ?1 Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows) - 8 I don't know why it doesn't use the primary index. Perhaps analyze statistics before solves the problem. HTH --- --- Eduardo Morras emorr...@yahoo.es ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing collation to xBestIndex
Well, to make it binary compatible with existing virtual table implementations, there could be added new function returning const pointer to CollSeq structure from passed index contraint. This function should be callable only from xBestIndex function. Something like: const CollSeq * sqlite3_vtab_constraint_coll_seq(const sqlite3_index_constraint* pConstraint); sqlite3_index_constraint contains iTermOffset, so to find a collation sequence for it should not be so big problem. How about it? 2013/7/17 Dušan Paulovič paulo...@gisoft.cz But it is the same as with new functions in sqlite3_module. Old SQLite extensions does not implement xRename function which is now needed. Also, new feature could be made optional using macro switch like some other features in SQLite. 2013/7/17 Stephan Beal sgb...@googlemail.com On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič paulo...@gisoft.cz wrote: What do you mean? I use it for a while on Windows and all works. Binary not compatible with what? What Dan means is that libraries built with and without this feature might not be binary compatible. i compile against an sqlite3 DLL without this feature, then my sysadmin updates libsqlite3 to one with this feature, and my binary will then likely segfault at some point (or otherwise invoke undefined behaviour) because the binary signatures of the sqlite structures in my app no longer match those in the library. A recompile of my app (with no code changes) would be necessary to fix this. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ 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
Re: [sqlite] Memory usage of sqlite3
Some changes, if blob is bigger than a few bytes, you should normalize them. If 2 blobs are equal, their id must be equal and you don't waste time comparing nor memory joining blob content. So you get: They are quite small (max ~70 bytes...) DROP TABLE IF EXISTS tour_blob; CREATE TABLE tour_blob (id INTEGER PRIMARY KEY, n_blob blob); DROP TABLE IF EXISTS tour; CREATE TABLE tour (id integer, score integer NOT NULL, cost integer NOT NULL, last_poi integer, FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON DELETE CASACADE, FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) ON DELETE CASACADE, PRIMARY KEY(id)); You can replace tour_unsorted_path_idx with a new index too: DROP INDEX IF EXISTS tour_unsorted_path_idx; CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path); DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx; CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, last_poi, cost); Take care and make cost the last one, because cost is compared with inequality. Very interesting, this sounds a very good point! You use '==' instead '=', take care too. I made the same error in a mail some weeks ago. Ops... For this query: SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1 8 - Query: SELECT id FROM tour ORDER BY id LIMIT ?1 Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows) - 8 I don't know why it doesn't use the primary index. Perhaps analyze statistics before solves the problem. It seems Explain does not consider the LIMIT, it should not be something to worry about. Thanks a lot. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing collation to xBestIndex
On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the wall: But it is the same as with new functions in sqlite3_module. Old SQLite extensions does not implement xRename function which is now needed. Also, new feature could be made optional using macro switch like some other features in SQLite. Actually, xRename() was part of the v1 module interface. xSavepoint(), xRelease(), and xRollbackTo() were added in the v2 interface. All three of these functions are optional, and the fact they were added at the end of the data structure means a v1 module will run under a modern version of SQLite just fine. http://www.sqlite.org/c3ref/module.html -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
On 16 Jul 2013, at 11:24pm, Loren Keagle loren.kea...@braemarllc.com wrote: Begin EXCLUSIVE TRANSACTION; insert several rows of data; Commit transaction; Prepare query statement; Iterate through one or more rows; Reset statement; Attempt to begin transaction; --- SQLITE_BUSY Would like to write more here, but can't unless I close/open the connection; I assume you're checking the result codes returned by all the API calls before the second BEGIN to see that they all return SQLITE_OK. Please add a _finalize() after the _reset() just for testing purposes. I know you may not want it as part of your production code. Is the statement that gets a busy a BEGIN or BEGIN IMMEDIATE or BEGIN EXCLUSIVE ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
If you remove a busy check, does it output any statements?
Do you have any custom functions/operations running so they could block
sqlite in creating new statement?
2013/7/17 Loren Keagle loren.kea...@braemarllc.com
Hi everyone,
I have an interesting locking problem that I'm wondering if someone can
help with some insight.
I have a master database with some metadata, and several sub-databases to
store logging events. I have one reader object and one writer object that
attach to the sub-databases and encapsulate the read/write operations
respectively.
I've found a very unexpected locking behavior with the attached databases
and exclusive transactions. One of my unit tests does the following:
Begin EXCLUSIVE TRANSACTION;
insert several rows of data;
Commit transaction;
Prepare query statement;
Iterate through one or more rows;
Reset statement;
Attempt to begin transaction; --- SQLITE_BUSY
Would like to write more here, but can't unless I close/open the
connection;
I can't seem to figure out any reason why I can't create a new exclusive
transaction here, and I feel it must have to do with the fact that I have
attached to sub-databases (possibly the same sub-database) with my
reader/writer objects. This is single threaded and only database connection
(with attach/detach logic).
I have verified that all statements prepared by the connection are
properly reset - this is handled by my C++ wrappers, and any errors will
throw an exception. I even iterated through all of the current statements
with the following code immediately before my transaction failure, with no
results:
sqlite3_stmt *stmt = NULL;
while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
{
if (sqlite3_stmt_busy(stmt))
{
const char* sql = sqlite3_sql(stmt);
std::cout sql \r\n;
}
}
Can anyone think of a reason why attached databases would prevent entering
a second transaction? BTW, it doesn't seem to work with immediate
transactions either. If I remove the query, everything works fine.
Thanks!
This email, including any attachments and files transmitted with it, are
for the sole use of the intended recipient(s) to whom this email is
addressed, and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please be advised that you have received
this email in error, and please contact the sender by reply email and
destroy all copies (including all electronic and hard copies) of the
original message. Thank you.
___
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
Re: [sqlite] Passing collation to xBestIndex
Yes, xRename was a part of the v1 module interface, but v1 module interface is not the first verson of it. Old sqlite extensions does not even contain xRename in their VT implementations and xFindFunction was the last one considered. I understand what you mean by binary incompatibility, but it is not a reason to keep this issue unresolved. Without possibility to custom handling of collation in xBestIndex function is this great idea limited to be used only in case of numeric queries. Now it is no way to return correct results from VT using xBestIndex. Yes, I can keep these constraints on SQLite, but then perhaps these constraints should be filtered out from xBestIndex consideration. Perhaps new function returning CollSeq array for constraint structure in API could resolve it - as I suggested in previous post. It can be resolved even with binary compatibility. Dusan 2013/7/17 Jay A. Kreibich j...@kreibi.ch On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the wall: But it is the same as with new functions in sqlite3_module. Old SQLite extensions does not implement xRename function which is now needed. Also, new feature could be made optional using macro switch like some other features in SQLite. Actually, xRename() was part of the v1 module interface. xSavepoint(), xRelease(), and xRollbackTo() were added in the v2 interface. All three of these functions are optional, and the fact they were added at the end of the data structure means a v1 module will run under a modern version of SQLite just fine. http://www.sqlite.org/c3ref/module.html -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ 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] incremental_vacuum within or outside transaction?
Is there any rationale for (not) running incremental_vacuum in an
transaction?
Unlike a full VACUUM, it seems to work with or without.
Purpose:
We are running with auto_vacuum = INCREMENTAL, and when closing the file in
our software, we do an on-demand cleanup like so:
if (Query('pragma freelist_count') threshold)
while (time_passed max_time)
Exec('pragma incremental_vacuum(1)');
In practcie, the condition involves more than a single query, and we will
elevate that from silent to with UI if there's a lot to be reclaimed.
A quick (statistically insigificant) test suggests that we do make more
progress within an transaction, OTOH it feels strange.
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/incremental-vacuum-within-or-outside-transaction-tp70086.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing collation to xBestIndex
OK, I have looked over all possible solutions for this issue and the best I
could find is to extend sqlite3_index_info structure as follows:
struct sqlite3_index_info {
...
/* Extra info */
struct sqlite3_index_extras {
int iVersion;
sqlite3_collseq **coll_seq; /* Collation sequences (same size as
aConstraints) */
} *extras;
};
this would be binary compatible and also extendable for needs in future.
How about this?
2013/7/17 Dan Kennedy danielk1...@gmail.com
On 07/17/2013 03:46 PM, Dušan Paulovič wrote:
Hello,
in virtual table mechanism is missing a way to correctly handle following
queries:
SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;
SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE;
To xBestIndex function is passed only constraint field = 'abc', but there
is no way to correctly compare (by correctly I mean accordingly to passed
collation) those strings because of missing collation functions in
constraint definition.
Patch for this issue exists for a long time:
http://osdir.com/ml/sqlite-**users/2011-09/msg00152.htmlhttp://osdir.com/ml/sqlite-users/2011-09/msg00152.html
Is there any chance that it could get merged?
I think the main problem is that it is not binary compatible.
Dan.
__**_
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] Guidance with Python and nested cursors
I am using Python to query a table for all its rows, for each row, I query related rows from a second table, then perform some processing and insert in to a third table. What is the technically correct approach for this? I would rather not accumulate all of the first tables data to make one off selects from table two, then insert to table three. I would prefer to iterate over table one etc. How does one setup the connection and cursor for this style of task? Thanks for any guidance, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Guidance with Python and nested cursors
On Jul 17, 2013, at 9:07 PM, Joseph L. Casale jcas...@activenetwerx.com wrote: I am using Python to query a table for all its rows, for each row, I query related rows from a second table, then perform some processing and insert in to a third table. What is the technically correct approach for this? From the above outline, one SQL statement: insert intotableA ( ... ) select ... fromtableB jointableC on tableC... = tableB… When in Rome, do as the Romans do -- Ambrose, allegedly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Guidance with Python and nested cursors
From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille Sent: Wednesday, July 17, 2013 1:25 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Guidance with Python and nested cursors On Jul 17, 2013, at 9:07 PM, Joseph L. Casale jcas...@activenetwerx.com wrote: I am using Python to query a table for all its rows, for each row, I query related rows from a second table, then perform some processing and insert in to a third table. What is the technically correct approach for this? From the above outline, one SQL statement: Hi, Problem is I need to perform some Python processing of the data, then insert. Thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite locking issue with ATTACH'ed databases
Prepare query statement;
Iterate through one or more rows;
Reset statement;
Attempt to begin transaction; --- SQLITE_BUSY
The sqlite3_reset() function is called to reset a prepared statement
object back to its initial state, ready to be re-executed. Any SQL
statement variables that had values bound to them using the
sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings()
to reset the bindings.
To close a prepared statement you need to use finalize.
The sqlite3_finalize() function is called to delete a prepared
statement. If the most recent evaluation of the statement encountered no
errors or if the statement is never been evaluated, then
sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of
statement S failed, then sqlite3_finalize(S) returns the appropriate
error code or extended error code.
You have one prepared statement open and then try to start a
transaction. This gives you a busy error.
On 07/17/2013 06:56 PM, Dušan Paulovič wrote:
If you remove a busy check, does it output any statements?
Do you have any custom functions/operations running so they could block
sqlite in creating new statement?
2013/7/17 Loren Keagle loren.kea...@braemarllc.com
Hi everyone,
I have an interesting locking problem that I'm wondering if someone can
help with some insight.
I have a master database with some metadata, and several sub-databases to
store logging events. I have one reader object and one writer object that
attach to the sub-databases and encapsulate the read/write operations
respectively.
I've found a very unexpected locking behavior with the attached databases
and exclusive transactions. One of my unit tests does the following:
Begin EXCLUSIVE TRANSACTION;
insert several rows of data;
Commit transaction;
Prepare query statement;
Iterate through one or more rows;
Reset statement;
Attempt to begin transaction; --- SQLITE_BUSY
Would like to write more here, but can't unless I close/open the
connection;
I can't seem to figure out any reason why I can't create a new exclusive
transaction here, and I feel it must have to do with the fact that I have
attached to sub-databases (possibly the same sub-database) with my
reader/writer objects. This is single threaded and only database connection
(with attach/detach logic).
I have verified that all statements prepared by the connection are
properly reset - this is handled by my C++ wrappers, and any errors will
throw an exception. I even iterated through all of the current statements
with the following code immediately before my transaction failure, with no
results:
sqlite3_stmt *stmt = NULL;
while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
{
if (sqlite3_stmt_busy(stmt))
{
const char* sql = sqlite3_sql(stmt);
std::cout sql \r\n;
}
}
Can anyone think of a reason why attached databases would prevent entering
a second transaction? BTW, it doesn't seem to work with immediate
transactions either. If I remove the query, everything works fine.
Thanks!
This email, including any attachments and files transmitted with it, are
for the sole use of the intended recipient(s) to whom this email is
addressed, and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please be advised that you have received
this email in error, and please contact the sender by reply email and
destroy all copies (including all electronic and hard copies) of the
original message. Thank you.
___
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
--
ASCII ribbon campaign ( )
against HTML e-mail X
www.asciiribbon.org / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] function auxiliary data
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/07/13 04:03, Riccardo Vianello wrote: I'm not sure I can do the same with pysqlite), pysqlite forces a minimum statement cache size of 5. but since sqlite3_reset doesn't clear the binding It can't in general since you can immediately rerun the statement with the same bindings. However that isn't exposed in APSW so you can edit the code to clear the bindings after each reset call. It doesn't do that at the moment as a form of being lazy - only clearing bindings when the statement is re-executed. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlHnCn8ACgkQmOOfHg372QT2HwCeL02RdUT1/6rpKLIxuQ4HAehg 4asAnj29dAUc4ZHqVvY1UWZPa15VWOd9 =IO2u -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Guidance with Python and nested cursors
If the table you are modifying is not being used in the in progress queries then you can just do the inserts -- wrapping the whole process in a single big transaction if you like. If the inserts may affect an open cursor (query) then you can specify an ORDER BY on the affected query, and put a + in front of one of the column names. This has the effect of forcing the query results to be put into a temp b-tree on the first call to step, and your row retrievals cursor through the temp table (as it were). This is effectively the same as retrieving the entire result set into a list except that the buffer is within the database engine and not within your application. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Joseph L. Casale Sent: Wednesday, 17 July, 2013 13:07 To: sqlite-users@sqlite.org Subject: [sqlite] Guidance with Python and nested cursors I am using Python to query a table for all its rows, for each row, I query related rows from a second table, then perform some processing and insert in to a third table. What is the technically correct approach for this? I would rather not accumulate all of the first tables data to make one off selects from table two, then insert to table three. I would prefer to iterate over table one etc. How does one setup the connection and cursor for this style of task? Thanks for any guidance, jlc ___ 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
Re: [sqlite] Guidance with Python and nested cursors
It is perfectly allowed to open multiple cursors against a single connection.
You can only execute one statement per cursor at a time, but you can have
multiple cursors running from the same connection:
cr1 = cn.cursor()
cr2 = cn.cursor()
cr1.execute('select ...')
while True:
row = cr1.fetchone()
if not row:
break
...
cr2.execute('INSERT ...')
for example. If you are inserting into one of the tables used in the outer
select, simply make sure that select has an order by with a + in front of one
of the column names to avoid side effects (ie, changes made to the database by
the insert are visible to all statements/cursors on that connection even before
those changes are committed).
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Joseph L. Casale
Sent: Wednesday, 17 July, 2013 13:41
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Guidance with Python and nested cursors
From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
Sent: Wednesday, July 17, 2013 1:25 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Guidance with Python and nested cursors
On Jul 17, 2013, at 9:07 PM, Joseph L. Casale
jcas...@activenetwerx.com wrote:
I am using Python to query a table for all its rows, for each row,
I query related rows from a
second table, then perform some processing and insert in to a third
table.
What is the technically correct approach for this?
From the above outline, one SQL statement:
Hi,
Problem is I need to perform some Python processing of the data, then
insert.
Thanks!
jlc
___
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
Re: [sqlite] Guidance with Python and nested cursors
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 17/07/13 18:37, Keith Medcalf wrote:
cr1 = cn.cursor() cr2 = cn.cursor()
cr1.execute('select ...') while True: row = cr1.fetchone() if not row:
break
While that is normal DBAPI, it is far more verbose and unpythonic than the
SQLite wrappers (disclosure: I'm the author of one of them). Code can use
iterators and look something like this:
for id,one,two in db.cursor().execute(select id, one, two from XXX):
three=one+two
db.cursor().execute(insert into , (three,))
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)
iEYEARECAAYFAlHnV8sACgkQmOOfHg372QTfbwCeIYJTKpLSn+RGlsDcMA/S18WI
4QcAoOHgcf3mcRwfWwR2IrB87DbS0oQY
=gtkt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
