Re: [sqlite] sqlite3.exe and formatting binary (GUID) data
John Machin wrote: Assuming your guid is a BLOB, then SELECT other_stuff, QUOTE(guid), etc will display it as hex e.g. X'01020304' This is great; now I can see them at least! P.S. Of course even better would be the ability to read/write GUIDs as properly formatted strings! (Note: I am not asking that they be treated internally as anything other than a binary chunk of data -- just the text form being changed). On output, how do you expect it to determine what blobs are guids? column_name like '%guid%' ?? Yes...is that a problem? I have not really looked at the SQLite code much. I know it remembers the declared types and assigns appropriate internal types. My thinking was that for certain types (GUID, UUID, maybe even Datetime/timestamp etc) it, or the user, could (optionally, for backward compatibility) assign 'toString' and 'fromString' operators. ie. not promote the to full types, but assign them a quasi-type status. In the case of dates, for example, it would allow me to enter '1-Jan-1970' and fromString would produce '1970-01-01'. In the case of GUIDs, it would display and load them in the standard GUID representation. This would not even necessarily need to form part of the standard sqlite code, but could be done as plugable functions. But, as I said, I have not really looked much at sqlite internals to know if this is completely impractical. On input: does the SQL standard define a guid literal? No; but GUIDs do. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 support for 64-bit unsigned integers
I tried inserting 2^63-1 and the two integers after it into an SQLite3 db, but this happened: SQLite version 3.6.11 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE test (test INT); sqlite INSERT INTO test VALUES (9223372036854775807); sqlite INSERT INTO test VALUES (9223372036854775808); sqlite INSERT INTO test VALUES (9223372036854775809); sqlite .mode line sqlite SELECT * FROM test; test = 9223372036854775807 test = 9.22337203685478e+18 test = 9.22337203685478e+18 sqlite SELECT * FROM test WHERE test = '9223372036854775808'; test = 9.22337203685478e+18 test = 9.22337203685478e+18 Why the sudden switch to scientific notation and loss of precision? Are 64-bit integers signed (ie -2^63 to 2^63-1)? Can I unsign them? Since sqlite3 uses 64-bit ints for rowid, I figured they'd be unsigned. Workarounds? -- We're just a Bunch Of Regular Guys, a collective group that's trying to understand and assimilate technology. We feel that resistance to new ideas and technology is unwise and ultimately futile. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 support for 64-bit unsigned integers
On 25/05/2009 4:28 PM, Kelly Jones wrote: I tried inserting 2^63-1 and the two integers after it into an SQLite3 db, but this happened: SQLite version 3.6.11 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE test (test INT); sqlite INSERT INTO test VALUES (9223372036854775807); sqlite INSERT INTO test VALUES (9223372036854775808); sqlite INSERT INTO test VALUES (9223372036854775809); sqlite .mode line sqlite SELECT * FROM test; test = 9223372036854775807 test = 9.22337203685478e+18 test = 9.22337203685478e+18 sqlite SELECT * FROM test WHERE test = '9223372036854775808'; test = 9.22337203685478e+18 test = 9.22337203685478e+18 Why the sudden switch to scientific notation and loss of precision? See answer to next question. Are 64-bit integers signed (ie -2^63 to 2^63-1)? SQLite's integers are 64-bit signed two's-complement. Don't bet the ranch on -2^63. Can I unsign them? No. Since sqlite3 uses 64-bit ints for rowid, I figured they'd be unsigned. Since using rowids at the rate of 1 million per second would bump into 2^63 after about 292,000 years, and since SQLite has only one integer type, to figure that it'd be signed would be a better betting proposition. Workarounds? BLOBs, maybe, depending what you want 64-bit unsigned integers for. What's the use case? Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to install sqlite3-3.6.14.1.bin.gz
Hi, Can anyone help me on how to run sqlite3-3.6.14.1.bin.gz on Linux machine. I am working on Fedora 10. Please if anyone can provide any input on this. -- Thanks and Regards, Manasi Save ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] read_uncommitted=on; question
Hello all, Is it possible that with the read_uncommitted=1 and the shared cache mode turned on (multithreaded application), that some data that has been inserted into the database, but not yet committed, could not be picked up by another thread as being in the database? I thought (from reading the docs) that with read_uncommitted=1 and sqlite3_enable_shared_cache(1), if I INSERT something, it will be picked up if I do a SELECT on another thread's connection as being in the database, even if a COMMIT has not been issued yet. Am I wrong in my understanding? Thanks in advance for any insight, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
On May 25, 2009, at 2:54 PM, Dennis Volodomanov wrote: Hello all, Is it possible that with the read_uncommitted=1 and the shared cache mode turned on (multithreaded application), that some data that has been inserted into the database, but not yet committed, could not be picked up by another thread as being in the database? I thought (from reading the docs) that with read_uncommitted=1 and sqlite3_enable_shared_cache(1), if I INSERT something, it will be picked up if I do a SELECT on another thread's connection as being in the database, even if a COMMIT has not been issued yet. Am I wrong in my understanding? That's correct. Normally, when using shared-cache mode, if connection A writes to a table (within a transaction) then a second connection to the same shared- cache is unable to read from that table until connection A either COMMITs or ROLLBACKs its open transaction. Trying to do so returns SQLITE_LOCKED. However, when in read_uncommitted=1 mode, the second connection reads the modified contents of the table, even though that data has not yet been (and indeed may never be) committed. Dan. Thanks in advance for any insight, Dennis ___ 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] [sqlite-dev] How to install sqlite3-3.6.14.1.bin.gz
On May 25, 2009, at 2:52 PM, Manasi Save wrote: Hi, Can anyone help me on how to run sqlite3-3.6.14.1.bin.gz on Linux machine. I am working on Fedora 10. Please if anyone can provide any input on this. Like this: d...@computer2:~/tmp$ wget http://www.sqlite.org/sqlite3-3.6.14.1.bin.gz ... output ... d...@computer2:~/tmp$ gunzip sqlite3-3.6.14.1.bin.gz d...@computer2:~/tmp$ chmod 755 sqlite3-3.6.14.1.bin d...@computer2:~/tmp$ ./sqlite3-3.6.14.1.bin SQLite version 3.6.14.1 Enter .help for instructions Enter SQL statements terminated with a ; sqlite -- Thanks and Regards, Manasi Save ___ sqlite-dev mailing list sqlite-...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] queries for a fulltext-engine
Igor Tandetnik schrieb: Lukas Haase lukasha...@gmx.at wrote in message news:gv9fcm$5r...@ger.gmane.org I have a database containing thousands of HTML pages (topics). There is a fulltext index for these topics. First there is a table containing all single words. Each word is identified by its fulltextID: CREATE TABLE fulltext( fulltextID INTEGER PRIMARY KEY, word VARCHAR(100) COLLATE NOCASE ); Now there is a linking table between the words and the HTML pages (topics): CREATE TABLE topic_fulltext( topicID INTEGER, fulltextID INTEGER, PRIMARY KEY(topicID, fulltextID) ); Finding a topic containing a specific word is not too hard: SELECT topic_fulltext.topicID FROM fulltext JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID WHERE word LIKE 'Word%'; But now I want to be able to search with more complex queries. For example: * List all topics containing (word1 AND word2) You could do something like this: Oh, thank you, this seems more like what I am looking for :-) :-) SELECT topic_fulltext.topicID FROM topic_fulltext where exists (select 1 from fulltext WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 'word1%') and exists (select 1 from fulltext WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 'word2%') Unfortunately this does not work completely :( In the first WHERE clause I restrict to entried containing only the word1 (the resultset will contain only entries with word1). So the second WHERE clause will always fail as there are no rows with word2 left. This seems to work only for my OR-requirement (topics containg either word1 OR word2) when replacing AND with OR. SELECT topic_fulltext.topicID FROM topic_fulltext where fulltextID in ( select fulltextID from topic_fulltext where word LIKE 'word1%' intersect select fulltextID from topic_fulltext where word LIKE 'word2%'); Unfortunately this does not work either. The set is taken from the fullwords, i.e. the result of the inner SELECT clause will contain fulltextIDs. And they will obviosly never intersect. So I have the same problem as above: Replacing with UNION ALL yields my OR-requirement but I can't get working it with AND :-( Test it, see which one works faster. The second one seems to be much faster. Though it's too slow (3s or so), but I hope I can tune up the query on the end... [...] * List all topics containing (word1 AND word2 AND ... AND word10) * List all topics containing ((word1 OR word2) AND word3 OR word3) The approach above should work for any boolean combination. I have forgotten one third type: the NOT. E.g.: * List all topics containing (NOT(word1 OR word2) AND word3) But this is no hard requirement, but boolean AND and OR combinations are. Thank you for your approaches, I tried to get the AND and OR working with it but I still do not figure it out :-( By the way: If there is a better way to organize the index in the database: This would be no problem if the queries will get simpler and faster. (As long as the memeory requirement stays approx. the same) Thank you again Igor, Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking if an integer column is set to NULL
On Mon, May 25, 2009 at 1:15 PM, chandan chandan.b...@globaledgesoft.com wrote: I have used sqlite3_bind_null() API to bind an integer column with NULL. When I read the value of that integer column I get the value as 0 (zero). Is there any way I can check if the column is set to NULL? This was something that confused me at first, so perhaps it's not crystal clear in the documentation: http://www.sqlite.org/capi3ref.html#sqlite3_column_blob [Annotations mine] The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column [NB I initially read this as the initial data type of the column rather than the initial data type of the result, assuming that it would return the column affinity rather than the stored type]. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The value returned by sqlite3_column_type() is only meaningful if no type conversions have occurred as described below. After a type conversion, the value returned by sqlite3_column_type() is undefined. Future versions of SQLite may change the behavior of sqlite3_column_type() following a type conversion. In other words, if you call sqlite3_column_type() before you call sqlite3_column_int(), you can differentiate the two cases. Best wishes, Hamish ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking if an integer column is set to NULL
Thanks a lot!. The solution works :-) Hamish Allan wrote: On Mon, May 25, 2009 at 1:15 PM, chandan chandan.b...@globaledgesoft.com wrote: I have used sqlite3_bind_null() API to bind an integer column with NULL. When I read the value of that integer column I get the value as 0 (zero). Is there any way I can check if the column is set to NULL? This was something that confused me at first, so perhaps it's not crystal clear in the documentation: http://www.sqlite.org/capi3ref.html#sqlite3_column_blob [Annotations mine] The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column [NB I initially read this as the initial data type of the column rather than the initial data type of the result, assuming that it would return the column affinity rather than the stored type]. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The value returned by sqlite3_column_type() is only meaningful if no type conversions have occurred as described below. After a type conversion, the value returned by sqlite3_column_type() is undefined. Future versions of SQLite may change the behavior of sqlite3_column_type() following a type conversion. In other words, if you call sqlite3_column_type() before you call sqlite3_column_int(), you can differentiate the two cases. Best wishes, Hamish ___ 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] Checking if an integer column is set to NULL
On 25/05/2009 10:15 PM, chandan wrote:
Hi,
I have used sqlite3_bind_null() API to bind an integer column with
NULL. When I read the value of that integer column I get the value as 0
(zero). Is there any way I can check if the column is set to NULL?
You do realise that calling it that integer column is more hopeful
than meaningful, don't you?
How are you reading the value of that integer column?
Here are some ways you can display it and test it using SQL:
sqlite create table t (i integer);
sqlite insert into t values(1);
sqlite insert into t values(0);
sqlite insert into t values(-1);
sqlite insert into t values(null);
sqlite insert into t values(123.456);
sqlite insert into t values('abcdef');
sqlite insert into t values(x'f000baaa');
sqlite select rowid, i, quote(i), typeof(i) from t;
1|1|1|integer
2|0|0|integer
3|-1|-1|integer
4||NULL|null
5|123.456|123.456|real
6|abcdef|'abcdef'|text
7||X'F000BAAA'|blob
sqlite select rowid, i, quote(i), typeof(i) from t where i is null;
4||NULL|null
Cheers,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] queries for a fulltext-engine
Lukas Haase lukasha...@gmx.at wrote in message news:gve1lh$30...@ger.gmane.org SELECT topic_fulltext.topicID FROM topic_fulltext where exists (select 1 from fulltext WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 'word1%') and exists (select 1 from fulltext WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 'word2%') Unfortunately this does not work completely :( In the first WHERE clause I restrict to entried containing only the word1 (the resultset will contain only entries with word1). So the second WHERE clause will always fail as there are no rows with word2 left. Right. I was thinking about a third table, topics, that lists all topics (and likely additional information about them), so that you have a classic many-to-many relationship. I suspect you have one. In this case you can do SELECT topics.topicID FROM topics where exists (select 1 from topic_fulltext join fulltext on (topic_fulltext.fulltextID = fulltext.fulltextID) WHERE topic_fulltext.topicID = topics.topicID and word LIKE 'word1%') and exists (select 1 from topic_fulltext join fulltext on (topic_fulltext.fulltextID = fulltext.fulltextID) WHERE topic_fulltext.topicID = topics.topicID and word LIKE 'word2%'); If for some strange reason you don't have topics table, then you can do SELECT distinct tf1.topicID from topic_fulltext tf1 where exists (select 1 from topic_fulltext tf2 join fulltext on (tf2.fulltextID = fulltext.fulltextID) WHERE tf1.topicID = tf2.topicID and word LIKE 'word1%') and exists (select 1 from topic_fulltext tf2 join fulltext on (tf2.fulltextID = fulltext.fulltextID) WHERE tf1.topicID = tf2.topicID and word LIKE 'word2%') Basically, (select distinct topicID from topic_fulltext) plays the role of topics table. SELECT topic_fulltext.topicID FROM topic_fulltext where fulltextID in ( select fulltextID from topic_fulltext where word LIKE 'word1%' intersect select fulltextID from topic_fulltext where word LIKE 'word2%'); Unfortunately this does not work either. For the same reason. Make it select topicID from topic_fulltext join fulltext on ( topic_fulltext.fulltextID=fulltext.fulltextID) where word LIKE 'word1%' intersect select topicID from topic_fulltext join fulltext on ( topic_fulltext.fulltextID=fulltext.fulltextID) where word LIKE 'word2%' Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.6.14.2
SQLite version 3.6.14.2 is now available on the SQLite website. Version 3.6.14.2 is a very small change over version 3.6.14.1. Version 3.6.14.2 fixes a single bug. The bug report and patch can be seen here: http://www.sqlite.org/cvstrac/tktview?tn=3879 http://www.sqlite.org/cvstrac/chngview?cn=6677 The bug that is fixed is an obscure corner-case in part of the code generator within the sqlite3_prepare() logic. Applications are very unlikely to hit this bug. But if they do, SQLite will give the wrong answer. And, though rare, it is difficult to characterize the kinds of queries that might hit this bug. For that reason, we have done an unscheduled patch release to fix the problem. The bug that is fixed was introduced in version 3.6.14. So upgrading is recommended for users of versions 3.6.14 and 3.6.14.1. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Older versions of sqlite3 analyzer?
Hi all, is there any pre-built windows sqlite3_analyzer binary for older SQLite Versions (3.5.9 in particular)? The download page only shows the 3.6.1 compatible one. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reading pragma's results
Hi there. I'm trying the following code http://pastebin.ca/143395 Strangelly, both pragmas don't return any row at all ! What am I missing ? The same happens if I open a data file on disk. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading pragma's results
Sorry, wrong url... http://pastebin.ca/1433959 On Mon, May 25, 2009 at 5:37 PM, João Eiras joao.ei...@gmail.com wrote: Hi there. I'm trying the following code http://pastebin.ca/143395 Strangelly, both pragmas don't return any row at all ! What am I missing ? The same happens if I open a data file on disk. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading pragma's results
João Eiras joao.ei...@gmail.com wrote in message news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com Sorry, wrong url... http://pastebin.ca/1433959 On Mon, May 25, 2009 at 5:37 PM, João Eiras joao.ei...@gmail.com wrote: Hi there. I'm trying the following code http://pastebin.ca/143395 Are you, by any chance, building a release version of that code? Realize that, in release build, assert() completely disappears together with whatever expression it contains, so your program becomes mostly a no-op. How exactly do you determine whether your code works or doesn't work? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading pragma's results
On Mon, May 25, 2009 at 6:48 PM, Igor Tandetnik itandet...@mvps.org wrote: João Eiras joao.ei...@gmail.com wrote in message news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com Sorry, wrong url... http://pastebin.ca/1433959 On Mon, May 25, 2009 at 5:37 PM, João Eiras joao.ei...@gmail.com wrote: Hi there. I'm trying the following code http://pastebin.ca/143395 Are you, by any chance, building a release version of that code? Realize that, in release build, assert() completely disappears together with whatever expression it contains, so your program becomes mostly a no-op. If I posted that code, it's safe to assume it is running and I know what an assert it. How exactly do you determine whether your code works or doesn't work? Because if breaks many of the asserts, like, I get errors in standards output. Igor Tandetnik ___ 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] Reading pragma's results
Btw, the same happens with the command line sqlite program $ sqlite SQLite version 2.8.17 Enter .help for instructions sqlite create table t(a integer); sqlite insert into t values(1); sqlite select * from t; 1 sqlite pragma page_size; sqlite pragma page_count; sqlite Both pragmas don't return anything On Mon, May 25, 2009 at 6:55 PM, João Eiras joao.ei...@gmail.com wrote: On Mon, May 25, 2009 at 6:48 PM, Igor Tandetnik itandet...@mvps.org wrote: João Eiras joao.ei...@gmail.com wrote in message news:e72b1b360905250934h1b39c95eycab618faca87d...@mail.gmail.com Sorry, wrong url... http://pastebin.ca/1433959 On Mon, May 25, 2009 at 5:37 PM, João Eiras joao.ei...@gmail.com wrote: Hi there. I'm trying the following code http://pastebin.ca/143395 Are you, by any chance, building a release version of that code? Realize that, in release build, assert() completely disappears together with whatever expression it contains, so your program becomes mostly a no-op. If I posted that code, it's safe to assume it is running and I know what an assert it. How exactly do you determine whether your code works or doesn't work? Because if breaks many of the asserts, like, I get errors in standards output. Igor Tandetnik ___ 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] Reading pragma's results
On Mon, May 25, 2009 at 1:10 PM, João Eiras joao.ei...@gmail.com wrote: Btw, the same happens with the command line sqlite program $ sqlite SQLite version 2.8.17 These pragmas didn't exist six years ago when 2.8.17 was current. The only pragmas in that version are: default_cache_size cache_size default_synchronous synchronous temp_store default_temp_store Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading pragma's results
Okay, but I'm using the latest sqlite source version 3.6 On Mon, May 25, 2009 at 7:17 PM, Derrell Lipman derrell.lip...@unwireduniverse.com wrote: On Mon, May 25, 2009 at 1:10 PM, João Eiras joao.ei...@gmail.com wrote: Btw, the same happens with the command line sqlite program $ sqlite SQLite version 2.8.17 These pragmas didn't exist six years ago when 2.8.17 was current. The only pragmas in that version are: default_cache_size cache_size default_synchronous synchronous temp_store default_temp_store Derrell ___ 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] SQLite3 support for 64-bit unsigned integers
On Sun, May 24, 2009 at 11:28:59PM -0700, Kelly Jones scratched on the wall: I tried inserting 2^63-1 and the two integers after it into an SQLite3 db, but this happened: SQLite version 3.6.11 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE test (test INT); sqlite INSERT INTO test VALUES (9223372036854775807); sqlite INSERT INTO test VALUES (9223372036854775808); sqlite INSERT INTO test VALUES (9223372036854775809); sqlite .mode line sqlite SELECT * FROM test; test = 9223372036854775807 test = 9.22337203685478e+18 test = 9.22337203685478e+18 sqlite SELECT * FROM test WHERE test = '9223372036854775808'; test = 9.22337203685478e+18 test = 9.22337203685478e+18 Why the sudden switch to scientific notation and loss of precision? For more details, see http://sqlite.org/datatype3.html You're creating a column with an INTEGER affinity, but then overflowed what an integer can represent, so SQLite found some other representation. From the docs: A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no fractional component and a magnitude that is less than or equal to the largest possible integer (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class. It helps to remember that the default type of all numbers in SQL is some type of real (in the formal mathematical sense, not the programming sense of a floating-point number) number. Are 64-bit integers signed (ie -2^63 to 2^63-1)? *All* integer values in SQLite are always signed. (And yes, being two's complement, that's the correct range) Workarounds? Since you haven't said what you're trying to do, it is difficult to propose a workaround. If all you need is a unsigned long long, you can either use an 8-byte BLOB (and a lot of casting) or you can just store the value as a signed int and cast back and forth. BLOBs are likely the safer choice, since the sort order will be correct. You could also break things up into two major/minor columns that each hold 32-ish bit numbers (SQLite auto sizes integers to their minimum representation of 1, 2, 3, 4, 6, or 8 bytes). Or 8 bit/48 bit, or whatever. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [OT] How to implement paging
I want to thank the mailing list in general you all have been very helpful in my learning both SQLite and SQL in general. I am working on paging right now and simply don't know the SQL way of implementing it. I have read through the SQLite page on scrolling cursor (http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor), VERY helpful. One thing the page does not cover, and I think this is more general SQL than SQLite, but how does one implement paging so that the user knows which page they are on? Example: Following the logic of the ScrollingCursor page, lets assume a total result set of 88 titles. If the lasttitle happens to be the 29th title, so the set that is returned is 30 through 34, how do I determine that this is the 6th page of a total of 18 pages? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading pragma's results
Well, thank you all. I managed to solve the problem. It was some initialization problems with sqlite. On Mon, May 25, 2009 at 7:20 PM, João Eiras joao.ei...@gmail.com wrote: Okay, but I'm using the latest sqlite source version 3.6 On Mon, May 25, 2009 at 7:17 PM, Derrell Lipman derrell.lip...@unwireduniverse.com wrote: On Mon, May 25, 2009 at 1:10 PM, João Eiras joao.ei...@gmail.com wrote: Btw, the same happens with the command line sqlite program $ sqlite SQLite version 2.8.17 These pragmas didn't exist six years ago when 2.8.17 was current. The only pragmas in that version are: default_cache_size cache_size default_synchronous synchronous temp_store default_temp_store Derrell ___ 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] [OT] How to implement paging
On 25 May 2009, at 6:58pm, Sam Carleton wrote: Example: Following the logic of the ScrollingCursor page, lets assume a total result set of 88 titles. If the lasttitle happens to be the 29th title, so the set that is returned is 30 through 34, how do I determine that this is the 6th page of a total of 18 pages? You're going to have to know how many rows are displayed on a page. When you get your initial results back from the table, count the number of rows (or use the library function that returns it) and divide one by the other. This gives you (more or less) the number of the last page of results. As well as keeping track of which page you're on, keep the current page number in a variable. Just modify it when they hit 'next' or 'previous'. However, there's another way to do it. If you know that you're never going to have more than a couple of hundred results, why display them as pages at all ? Display them all, and provide a search function which lets people see just the records which contain their search field. This is faster and more efficient than asking your users to wade through many pages. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
On Mon, May 25, 2009 at 2:31 PM, Simon Slavin slav...@hearsay.demon.co.uk wrote: On 25 May 2009, at 6:58pm, Sam Carleton wrote: Example: Following the logic of the ScrollingCursor page, lets assume a total result set of 88 titles. If the lasttitle happens to be the 29th title, so the set that is returned is 30 through 34, how do I determine that this is the 6th page of a total of 18 pages? You're going to have to know how many rows are displayed on a page. When you get your initial results back from the table, count the number of rows (or use the library function that returns it) and divide one by the other. This gives you (more or less) the number of the last page of results. As well as keeping track of which page you're on, keep the current page number in a variable. Just modify it when they hit 'next' or 'previous'. So in the end, you are saying that it is completely and totally the responsibility of the frontend to keep track of the page number, correct? The result set should simply return a total count so that the # of pages can be calculated. Correct? However, there's another way to do it. If you know that you're never going to have more than a couple of hundred results, why display them as pages at all ? Display them all, and provide a search function which lets people see just the records which contain their search field. This is faster and more efficient than asking your users to wade through many pages. That is a very good question. The main reason is that my application is a kiosk system that can be run on a touch screen display. Paging is much easier than scrolling on touch screens. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
Hi Sam, On May 25, 2009, at 10:58 AM, Sam Carleton wrote: Example: Following the logic of the ScrollingCursor page, lets assume a total result set of 88 titles. If the lasttitle happens to be the 29th title, so the set that is returned is 30 through 34, how do I determine that this is the 6th page of a total of 18 pages? Let's assume 88 titles. For the sake of the argument, say you decide to display 12 per page. That would be 88 / 12 = 7.3 = 8 pages. If you need to know in which page a specific title lies (say 63), you can do something like ceil (63 % 12) = ceil (5.25) = 6th page. Following the same logic, ceil (88 / 12) = 8 pages total. Does that answer your question? Cheers, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
On Mon, May 25, 2009 at 2:43 PM, Tito Ciuro tci...@mac.com wrote: Hi Sam, On May 25, 2009, at 10:58 AM, Sam Carleton wrote: Example: Following the logic of the ScrollingCursor page, lets assume a total result set of 88 titles. If the lasttitle happens to be the 29th title, so the set that is returned is 30 through 34, how do I determine that this is the 6th page of a total of 18 pages? Let's assume 88 titles. For the sake of the argument, say you decide to display 12 per page. That would be 88 / 12 = 7.3 = 8 pages. If you need to know in which page a specific title lies (say 63), you can do something like ceil (63 % 12) = ceil (5.25) = 6th page. Following the same logic, ceil (88 / 12) = 8 pages total. Does that answer your question? Actually, no it doesn't. The math is simple enough, along with finding the total count. The question is how do I find out the number of the title passed in... According to the ScrollingCursor page, I should be passing in a title, aka a string. Since my record set is very dynamic, the title might be the 63rd one time, but the next time it could be the 71st or 55th. How do I find out the index of the start of the page as to apply some basic math to find the page number? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 support for 64-bit unsigned integers
Just a small clarification you probably forgot... On Mon, May 25, 2009 at 6:56 PM, Jay A. Kreibich j...@kreibi.ch wrote: Since you haven't said what you're trying to do, it is difficult to propose a workaround. If all you need is a unsigned long long, you can either use an 8-byte BLOB (and a lot of casting) or you can just store the value as a signed int and cast back and forth. BLOBs are likely the safer choice, since the sort order will be correct. This will be true if the BLOBs are stored as big-endian 64-bit integers, not if they are stored as little-endian (the first byte would then be the one less significant). Regards, ~Nuno Lucas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
Hello Sam, Paging or scrolling is purely an abstraction you create with the GUI itself. If you load up 88 titles into memory and your screen display is 40 lines then you have 3 pages in memory and you simply replace a page each time they scroll or page up/down. You seem to be letting the back end dictate what the GUI does when in fact it's better to abstract the whole thing so, you can change the back end at will and not have to change the GUI. For 88 items, I doubt I'd even use a DB. Nothing beats a flat text file when you have a tiny data set. SC That is a very good question. The main reason is that my application SC is a kiosk system that can be run on a touch screen display. Paging SC is much easier than scrolling on touch screens. SC Sam SC ___ SC sqlite-users mailing list SC sqlite-users@sqlite.org SC http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
On Mon, May 25, 2009 at 4:05 PM, Teg t...@djii.com wrote: Hello Sam, Paging or scrolling is purely an abstraction you create with the GUI itself. If you load up 88 titles into memory and your screen display is 40 lines then you have 3 pages in memory and you simply replace a page each time they scroll or page up/down. You seem to be letting the back end dictate what the GUI does when in fact it's better to abstract the whole thing so, you can change the back end at will and not have to change the GUI. For 88 items, I doubt I'd even use a DB. Nothing beats a flat text file when you have a tiny data set. I hear you that paging should be frontend logic, normally. The problem is that I have a *DYNAMIC* record set that is constantly changing: The nature of the dataset is that it can grow very quickly early on and at any point after that rows can be deleted through out. It is this last fact that I need the help of the backend to figure out the page. Assume 5 items per page. If there are 88 items in the record set on one call that returns 50 to 55, before the next call, items 3,12, 17, 32, and 42 are all deleted from the record set, Item #55 is now going to be #50. If the front end is simply giving the backend an unique identifier of the item, not the PK, per the recommendations of the ScrollingCursor page, how does the front end learn that it now is getting #50 through #54 rather than #55 through #59? Further, I am assuming this is a problem with paging on any dataset in any database, thus a backend issue :) Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
Hello Sam, Are you planning on periodically updating the display as the user interacts with it? Have items pop in and pop out again as they're added or deleted? From your description, the data displayed in the GUI will go stale very quickly. I actually have similar logic in my app and I always append new things to the bottom so, it doesn't affect the current displayed page. Nothing annoys me more than to have items dynamically appearing and disappearing while I'm trying to interact with a GUI. Monday, May 25, 2009, 4:32:56 PM, you wrote: SC On Mon, May 25, 2009 at 4:05 PM, Teg t...@djii.com wrote: Hello Sam, Paging or scrolling is purely an abstraction you create with the GUI itself. If you load up 88 titles into memory and your screen display is 40 lines then you have 3 pages in memory and you simply replace a page each time they scroll or page up/down. You seem to be letting the back end dictate what the GUI does when in fact it's better to abstract the whole thing so, you can change the back end at will and not have to change the GUI. For 88 items, I doubt I'd even use a DB. Nothing beats a flat text file when you have a tiny data set. SC I hear you that paging should be frontend logic, normally. The SC problem is that I have a *DYNAMIC* record set that is constantly SC changing: SC The nature of the dataset is that it can grow very quickly early on SC and at any point after that rows can be deleted through out. It is SC this last fact that I need the help of the backend to figure out the SC page. SC Assume 5 items per page. If there are 88 items in the record set on SC one call that returns 50 to 55, before the next call, items 3,12, 17, SC 32, and 42 are all deleted from the record set, Item #55 is now going SC to be #50. If the front end is simply giving the backend an unique SC identifier of the item, not the PK, per the recommendations of the SC ScrollingCursor page, how does the front end learn that it now is SC getting #50 through #54 rather than #55 through #59? SC Further, I am assuming this is a problem with paging on any dataset in SC any database, thus a backend issue :) SC Sam -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
I understand and agree that things changing in the middle is not ideal. In the situation I am dealing with, things MUST disappear in the middle of the dataset. As far as adding things, it should happen at the end, but that is outside of my control, it all depends on how things are sorted. Normally things will be added to the end of the dataset, though. I take it that there is no solution to my problem? Sam On Mon, May 25, 2009 at 4:53 PM, Teg t...@djii.com wrote: Hello Sam, Are you planning on periodically updating the display as the user interacts with it? Have items pop in and pop out again as they're added or deleted? From your description, the data displayed in the GUI will go stale very quickly. I actually have similar logic in my app and I always append new things to the bottom so, it doesn't affect the current displayed page. Nothing annoys me more than to have items dynamically appearing and disappearing while I'm trying to interact with a GUI. Monday, May 25, 2009, 4:32:56 PM, you wrote: SC On Mon, May 25, 2009 at 4:05 PM, Teg t...@djii.com wrote: Hello Sam, Paging or scrolling is purely an abstraction you create with the GUI itself. If you load up 88 titles into memory and your screen display is 40 lines then you have 3 pages in memory and you simply replace a page each time they scroll or page up/down. You seem to be letting the back end dictate what the GUI does when in fact it's better to abstract the whole thing so, you can change the back end at will and not have to change the GUI. For 88 items, I doubt I'd even use a DB. Nothing beats a flat text file when you have a tiny data set. SC I hear you that paging should be frontend logic, normally. The SC problem is that I have a *DYNAMIC* record set that is constantly SC changing: SC The nature of the dataset is that it can grow very quickly early on SC and at any point after that rows can be deleted through out. It is SC this last fact that I need the help of the backend to figure out the SC page. SC Assume 5 items per page. If there are 88 items in the record set on SC one call that returns 50 to 55, before the next call, items 3,12, 17, SC 32, and 42 are all deleted from the record set, Item #55 is now going SC to be #50. If the front end is simply giving the backend an unique SC identifier of the item, not the PK, per the recommendations of the SC ScrollingCursor page, how does the front end learn that it now is SC getting #50 through #54 rather than #55 through #59? SC Further, I am assuming this is a problem with paging on any dataset in SC any database, thus a backend issue :) SC Sam -- Best regards, Teg mailto:t...@djii.com ___ 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] Simple Outer Join question?
Hallo, I have a table 'person' and a table 'group'. Every person can join none, one or more groups. No I want to select all persons except those who are member in group 1. - Sounds simple, but not for me. Thanks in advance Leo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Outer Join question?
On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag leofrei...@netcologne.de wrote: Hallo, I have a table 'person' and a table 'group'. Every person can join none, one or more groups. No I want to select all persons except those who are member in group 1. - Sounds simple, but not for me. This is an n:m relationship. If group has more attributes (columns) than just its number, you need a third table: person_group. Then join person with person_group where group_id != 1; The person_group table could be called membership, if you like. Thanks in advance Leo -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
On 25 May 2009, at 7:42pm, Sam Carleton wrote: So in the end, you are saying that it is completely and totally the responsibility of the frontend to keep track of the page number, correct? The result set should simply return a total count so that the # of pages can be calculated. Correct? Yes. That is one way of handling the problem. Bear in mind that your database engine doesn't know anything about how many lines you want to fit on the screen at once: that is your application's problem and nothing to do with the data. The main reason is that my application is a kiosk system that can be run on a touch screen display. Paging is much easier than scrolling on touch screens. Ah, for a kiosk system, your original solution is better. Quite right. On 25 May 2009, at 9:32pm, Sam Carleton wrote: I hear you that paging should be frontend logic, normally. The problem is that I have a *DYNAMIC* record set that is constantly changing: On 25 May 2009, at 9:53pm, Teg wrote: Are you planning on periodically updating the display as the user interacts with it? Have items pop in and pop out again as they're added or deleted? From your description, the data displayed in the GUI will go stale very quickly. The problem is even worse than that. There are three common approaches: A) Do the search when someone starts first asks for the list. Ignore all changes to the data until they've exited the list and gone back in. B) Each time the user moves from page to page, reflect changes to the data. C) Constantly update the display to show changes in the data even if the user isn't hitting any keys. They're all doable, but you're going to have to decide which of these you want to do. I've done (C) in a PHP/AJAX solution and it wasn't too hard: you have to constantly check to see if any changes have been made to the table. But it places a lot of load on the server and requires some extremely 'cheap' method of checking to see if any changes have been made. Be aware that using just one 'SELECT' gives you solution (A): the command makes a table in memory which doesn't change even if the data changes before you've got all the rows from the response. (B), on the other hand, has a number of problems if your users expect to see every record as they're paging through. For instance, suppose you're showing 10 records per page. Your user is on the first page when someone deletes the tenth record. Does this shift record 11 to the first page ? If so, then when the user hits 'next' do they miss the eleventh record ? Another possibility: the user is viewing the second page when someone deletes the first five records in the list. The user now hits 'previous'. Do you now show just five records on the display, or do you show five records again ? Questions like the above are reasons I don't like paging solutions for live data. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
I thought (from reading the docs) that with read_uncommitted=1 and sqlite3_enable_shared_cache(1), if I INSERT something, it will be picked up if I do a SELECT on another thread's connection as being in the database, even if a COMMIT has not been issued yet. Am I wrong in my understanding? That's correct. Normally, when using shared-cache mode, if connection A writes to a table (within a transaction) then a second connection to the same shared- cache is unable to read from that table until connection A either COMMITs or ROLLBACKs its open transaction. Trying to do so returns SQLITE_LOCKED. However, when in read_uncommitted=1 mode, the second connection reads the modified contents of the table, even though that data has not yet been (and indeed may never be) committed. Ok, thank you for confirming that. It seems that connection B *sometimes* doesn't see data just inserted into a table by connection A. I will try updating to 3.6.14.2, but most probably that won't change anything. Another question - is it possible for 2 threads sharing the same connection to do an insert at exactly the same time, thus potentially causing this problem? Maybe thread 1 did a prepare, but not yet step and the same data is being prepared by thread 2 - would that cause a problem that I'm seeing then? As a side note - I'm monitoring all error codes returned by prepare/step, so any busy states will cause a small sleep and retry. Thanks again, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
On 26 May 2009, at 12:36am, Dennis Volodomanov wrote: Ok, thank you for confirming that. It seems that connection B *sometimes* doesn't see data just inserted into a table by connection A. How long a time is 'just' ? You might want everything to be completely up-to-date but does that record really matter if it didn't exist a fraction of a second ago ? If the difference matters to you then perhaps you shouldn't be using read_uncommitted. That way your database will be completely consistent (assuming you're using transactions properly). Another question - is it possible for 2 threads sharing the same connection to do an insert at exactly the same time, thus potentially causing this problem? Nope. Even if you're using a multi-core processor, your motherboard can still only handle one memory access instruction at a time. Multiprocessing is an illusion carefully maintained by the operating system. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
Ok, thank you for confirming that. It seems that connection B *sometimes* doesn't see data just inserted into a table by connection A. How long a time is 'just' ? You might want everything to be completely up-to-date but does that record really matter if it didn't exist a fraction of a second ago ? If the difference matters to you then perhaps you shouldn't be using read_uncommitted. That way your database will be completely consistent (assuming you're using transactions properly). Basically these are file names being inserted into a table, so, before each insert we check whether that file exists in the table already or not (by doing a SELECT on an indexed lowercase full file path). So, it really matters to me that the first insert completes and data is available for a select that follows immediately from another thread (but same database connection). If I turn off read_uncommitted, then data won't be visible by other threads (same database connection) until a commit is done, right? So, that would be pretty much the same as what's happening now with read_uncommitted turned on. Another question - is it possible for 2 threads sharing the same connection to do an insert at exactly the same time, thus potentially causing this problem? Nope. Even if you're using a multi-core processor, your motherboard can still only handle one memory access instruction at a time. Multiprocessing is an illusion carefully maintained by the operating system. Yes, I understand that, but would the first prepare already lock the database for writing, or does that occur only during a step? Thank you, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
Dennis Volodomanov dennis.volodoma...@conceiva.com wrote in message news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com Ok, thank you for confirming that. It seems that connection B *sometimes* doesn't see data just inserted into a table by connection A. How do you know the read on connection B doesn't actually happen right _before_ the write on connection A? What kind of synchronization do you employ between these two threads? Another question - is it possible for 2 threads sharing the same connection to do an insert at exactly the same time, thus potentially causing this problem? read_uncommitted just turns off read locks. Write operations still acquire write locks on tables they touch, so you can't have simultaneous inserts into the same table. Maybe thread 1 did a prepare, but not yet step and the same data is being prepared by thread 2 This makes no sense. You don't prepare data - you prepare statements. No data is touched by sqlite3_prepare call. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
Dennis Volodomanov dennis.volodoma...@conceiva.com wrote in message news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com If I turn off read_uncommitted, then data won't be visible by other threads (same database connection) until a commit is done, right? Wrong. Statements on the same connection certainly see changes made on that connection, committed or otherwise. Are you talking about the same connection, or two different connections in shared cache mode? You started describing the latter, but now keep mentioning the former. Which way is it? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
How do you know the read on connection B doesn't actually happen right _before_ the write on connection A? What kind of synchronization do you employ between these two threads? I'm using a critical section (with a CSingleLock) to synchronize threads. However, just looking back at the code more closely now, I think I see where the problem is. Both checks for existence of data can finish up before any of the inserts are done and that is most probably what I'm seeing here. Thanks! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Outer Join question?
Kees Nuyt k.n...@zonnet.nl wrote in message news:8u3m151rqbbel40ilsvaatqmfhcnhsj...@dim53.demon.nl On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag leofrei...@netcologne.de wrote: I have a table 'person' and a table 'group'. Every person can join none, one or more groups. No I want to select all persons except those who are member in group 1. - Sounds simple, but not for me. This is an n:m relationship. If group has more attributes (columns) than just its number, you need a third table: person_group. Then join person with person_group where group_id != 1; That would also pick people that are both in group 1 and group 2. You would need something like select * from person where person_id not in ( select person_id from person_group where group_id=1); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
Wrong. Statements on the same connection certainly see changes made on that connection, committed or otherwise. Are you talking about the same connection, or two different connections in shared cache mode? You started describing the latter, but now keep mentioning the former. Which way is it? It's multiple threads all using the same sqlite3* handle. I call sqlite3_enable_shared_cache(1); before opening the database and have PRAGMA read_uncommitted=1; right after opening the database. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
On 26 May 2009, at 1:11am, Dennis Volodomanov wrote: Basically these are file names being inserted into a table, so, before each insert we check whether that file exists in the table already or not (by doing a SELECT on an indexed lowercase full file path). So, it really matters to me that the first insert completes and data is available for a select that follows immediately from another thread (but same database connection). Ah ! Okay, if that's what you want, you can take advantage of the variations on the INSERT call. Define the filename column as UNIQUE, or if there's a combination, create a UNIQUE index for the table, then use one of INSERT OR REPLACE INSERT OR FAIL INSERT OR IGNORE depending on which logic best serves what your program does. So if you don't care if the filename is already in the table, you can use INSERT OR IGNORE, so the INSERT will always work but never generate duplicate entries. On the other hand, if you need to trap and handle the case where the filename is already there, use INSERT OR FAIL, and the error you get back will tell you whether the filename was already in the table. See http://www.sqlite.org/lang_conflict.html for a detailed discussion of what happens for the various clauses. Because catching and dealing with the duplication is handled within the library function, using these things appropriately should mean that you don't have to do any fancy worrying about threads, processes or simultaneity at all: if anything funny goes on, only one of the INSERT operations will succeed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
Because catching and dealing with the duplication is handled within the library function, using these things appropriately should mean that you don't have to do any fancy worrying about threads, processes or simultaneity at all: if anything funny goes on, only one of the INSERT operations will succeed. Thank you for the info! Unfortunately things are a bit more complex, so I can't rely on the INSERT to tell me whether the file is there or not, because there's some heavy processing before that INSERT which of course shouldn't be done if the file is already there. I'm changing the program's logic in handling this, because SQLite is not at fault nor can it solve this problem on its own. I'm basically going to do a quick INSERT, then start the heavy processing and then do an UPDATE. This will let me do SELECTs to check existence from other threads without locking things up. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
Dennis Volodomanov dennis.volodoma...@conceiva.com wrote in message news:8501919721c3de4c81bca22846b08721a3f...@lazarus.conceiva.com Wrong. Statements on the same connection certainly see changes made on that connection, committed or otherwise. Are you talking about the same connection, or two different connections in shared cache mode? You started describing the latter, but now keep mentioning the former. Which way is it? It's multiple threads all using the same sqlite3* handle. I call sqlite3_enable_shared_cache(1); before opening the database and have PRAGMA read_uncommitted=1; right after opening the database. None of that has any effect as long as you only have one connection. For the cache to be shared, you need at least two connections to share it between. So you may as well drop those call. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] How to implement paging
Hello Sam, SC I take it that there is no solution to my problem? That's our job isn't it? Designing some solution that works? - Might have a worker thread watching the DB and have it sending notifications to the GUI as deletes and additions were made to the DB so, the display would be updated in real time. - Might design it so, updates to the DB are sent to a table of updates and the worker thread both integrates the updates AND notifies the GUI of the specific update (something like a task queue but, in the DB itself). - if the updates are external, might make the updater update an update table. So, the worker thread knows what was done without having to scan the whole DB. - If I couldn't do that, I might keep all the rowid's in memory and refresh the rowid list from time to time to find the changes (added and deleted rowids). This is kind of basic to problems like this. It's not that there isn't a solution, there's a 100 different ways to do it. You just have to pick one. The realtime-ness of the display will determine the design. In Windows for instance a grid never has to be filling in with data, it can re-paint the items dynamically in real time as a result of notifications. I'd probably use a pure virtual list control, worker thread that monitors the DB and gated notifications to the GUI that stalls the worker thread until the notification is processed (since in windows, worker threads can't talk directly to the GUI elements). In that way the GUI remains responsive, only a single thread talks to the DB and the display can be updated in real time. It's how I handle list controls with 500,000+ elements. Then you just pick whether you want it to scroll or page. Monday, May 25, 2009, 5:02:22 PM, you wrote: SC I understand and agree that things changing in the middle is not SC ideal. In the situation I am dealing with, things MUST disappear in SC the middle of the dataset. As far as adding things, it should happen SC at the end, but that is outside of my control, it all depends on how SC things are sorted. Normally things will be added to the end of the SC dataset, though. SC I take it that there is no solution to my problem? SC Sam SC On Mon, May 25, 2009 at 4:53 PM, Teg t...@djii.com wrote: Hello Sam, Are you planning on periodically updating the display as the user interacts with it? Have items pop in and pop out again as they're added or deleted? From your description, the data displayed in the GUI will go stale very quickly. I actually have similar logic in my app and I always append new things to the bottom so, it doesn't affect the current displayed page. Nothing annoys me more than to have items dynamically appearing and disappearing while I'm trying to interact with a GUI. Monday, May 25, 2009, 4:32:56 PM, you wrote: SC On Mon, May 25, 2009 at 4:05 PM, Teg t...@djii.com wrote: Hello Sam, Paging or scrolling is purely an abstraction you create with the GUI itself. If you load up 88 titles into memory and your screen display is 40 lines then you have 3 pages in memory and you simply replace a page each time they scroll or page up/down. You seem to be letting the back end dictate what the GUI does when in fact it's better to abstract the whole thing so, you can change the back end at will and not have to change the GUI. For 88 items, I doubt I'd even use a DB. Nothing beats a flat text file when you have a tiny data set. SC I hear you that paging should be frontend logic, normally. The SC problem is that I have a *DYNAMIC* record set that is constantly SC changing: SC The nature of the dataset is that it can grow very quickly early on SC and at any point after that rows can be deleted through out. It is SC this last fact that I need the help of the backend to figure out the SC page. SC Assume 5 items per page. If there are 88 items in the record set on SC one call that returns 50 to 55, before the next call, items 3,12, 17, SC 32, and 42 are all deleted from the record set, Item #55 is now going SC to be #50. If the front end is simply giving the backend an unique SC identifier of the item, not the PK, per the recommendations of the SC ScrollingCursor page, how does the front end learn that it now is SC getting #50 through #54 rather than #55 through #59? SC Further, I am assuming this is a problem with paging on any dataset in SC any database, thus a backend issue :) SC Sam -- Best regards, Teg mailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
It's multiple threads all using the same sqlite3* handle. I call sqlite3_enable_shared_cache(1); before opening the database and have PRAGMA read_uncommitted=1; right after opening the database. None of that has any effect as long as you only have one connection. For the cache to be shared, you need at least two connections to share it between. So you may as well drop those call. Yes, good point... I might compare performance differences in using a shared connection (multiple threads each opening its own copy of the database and sharing the connection) vs using the same database from those threads, unless such timings have already been done? Thanks! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
On 26 May 2009, at 4:56am, Dennis Volodomanov wrote: Yes, good point... I might compare performance differences in using a shared connection (multiple threads each opening its own copy of the database and sharing the connection) vs using the same database from those threads, unless such timings have already been done? You would not necessarily get the same results. The way this works would be very sensitive to interactions between the thread scheduling algorithm and the sqlite3 functions, and to how fast the CPUs dealt with sqlite3 functions. One set of timings on processors of a certain speed under a certain OS may not be useful for your particular setup. But this whole thread suggests a little to me that you're engaging in premature optimization: http://en.wikipedia.org/wiki/Optimization_(computer_science)#When_to_optimize You're probably better off ignoring all this stuff and just getting a working solution. Only then is it worth running some sort of profiling system on your application to find out which bits are most worth optimising. Since the sqlite3 library is pretty fast already you might find that fiddling with shared cache defaults could make only 5% the improvement that improving the rest of your code will. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read_uncommitted=on; question
You're probably better off ignoring all this stuff and just getting a working solution. Only then is it worth running some sort of profiling system on your application to find out which bits are most worth optimising. Since the sqlite3 library is pretty fast already you might find that fiddling with shared cache defaults could make only 5% the improvement that improving the rest of your code will. I agree - if I ever did those timings, now would not be the best time :) The solution that I have now works well (without shared cache), so I'll move on to other areas for the time being - it's fast enough, as most time is spent elsewhere (processing the actual files). Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on errors - IOERR and CANTOPEN
I sometimes get either a SQLITE_IOERR or a SQLITE_CANTOPEN when issuing BEGIN IMMEDIATE TRANSACTION or END TRANSACTION, however the database file is there and is being used by another thread. I thought I'd get the usual SQLITE_BUSY or SQLITE_LOCKED, but sometimes these file-related errors come up. Does anyone know why they come up and what should be the correct logic to continue? Should (and can it) the operation in question be retried, as if a BUSY/LOCKED was encountered? Thanks in advance, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
