Re: [sqlite] Strange behavior with sum
On Thu, 01 Jun 2006 23:08:13 -0500, Kareem Badr <[EMAIL PROTECTED]> wrote: > Does anyone know what's going on here? Is this just user error? It's just integer division. D:\sqlite>sqlite3 :memory: SQLite version 3.3.4 Enter ".help" for instructions sqlite> create table bob (f1 varchar, f2 varchar); sqlite> insert into bob values (206140624896,237003182080); sqlite> select sum(f1),sum(f2) from bob; 206140624896|237003182080 sqlite> select sum(f1)/sum(f2) from bob; 0 sqlite> select cast(sum(f1) as double)/cast(sum(f2) as double) from bob; 0.869779988128673 sqlite> klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Strange behavior with sum
I tried that, but the fields ended up getting set to '', rather than 0. The fields are defined as varchar, remember. *But*, I don't think the NULL values are causing the problems, because the query runs fine when more than 1 row was returned. And my query actually returned all the rows, including the one that contained the NULL values. Just to clarify: > SELECT d.name, free_space, size, free_space/size FROM devices d LEFT OUTER JOIN disks ON (d.id =disks.computer_id and disks.size not null and disks.free_space not null) WHERE d.name='kensho'; > kensho|71975571456|79966171136|0.900075249740165 > kensho||| > kensho|206140624896|237003182080|0.869779988128673 > SELECT d.name, sum(free_space), sum(size), sum(free_space)/sum(size) FROM devices d LEFT OUTER JOIN disks ON (d.id=disks.computer_id) WHERE d.name='kensho'; > kensho|278116196352.0|316969353216.0|0.877422985945511 > SELECT d.name, sum(free_space), sum(size), sum(free_space)/sum(size) FROM devices d LEFT OUTER JOIN disks ON (d.id =disks.computer_id and disks.name='G:') WHERE d.name='kensho'; > kensho|206140624896|237003182080|0 I'm really starting to think I found a bug. From what I remember of aggregates, if there is a NULL in the calculation on any field, the result will always be NULL. This is a huge beware. I'm not sure if it's affecting what you're doing, but I have a clue that it may be. How are the results affected when you update devices set free_space = 0 where free_space is NULL; update devices set size = 0 where size is NULL; and rerun the two initial queries again?
Re: [sqlite] Strange behavior with sum
Kareem Badr wrote: > Bill KING wrote: >> Kareem Badr wrote: >> >>> I should mention that free_space and size are defined as varchars, for >>> some reason. Not sure if that has anything to do with the issues I'm >>> seeing. >>> >>> >> Do any of these fields contain NULL? as that will definately have an >> impact upon both joins and upon calculations. (when using a numeric >> field, it's always better to set the field NOT NULL, and default it >> to 0). >> >> > Some of the fields contain NULL, yes, but not in the row that is > returned by the query that is returning a single row. > > Best I can figure out is that, for whatever reason, when a single row > is returned, the division results in 0. When more than 1 row is > returned (even if the row has nulls in the field...which one of the > rows does), the division works. > > Here are the contents of the table: > > C: | 71975571456 | 79966171136 > D: | | > G: | 206140624896 | 237003182080 > > > I also noticed that in the case where the division worked, the sums > are displayed as floats in the resulting row. Very odd. > >From what I remember of aggregates, if there is a NULL in the calculation on any field, the result will always be NULL. This is a huge beware. I'm not sure if it's affecting what you're doing, but I have a clue that it may be. How are the results affected when you update devices set free_space = 0 where free_space is NULL; update devices set size = 0 where size is NULL; and rerun the two initial queries again? -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
Re: [sqlite] Strange behavior with sum
Bill KING wrote: Kareem Badr wrote: I should mention that free_space and size are defined as varchars, for some reason. Not sure if that has anything to do with the issues I'm seeing. Do any of these fields contain NULL? as that will definately have an impact upon both joins and upon calculations. (when using a numeric field, it's always better to set the field NOT NULL, and default it to 0). Some of the fields contain NULL, yes, but not in the row that is returned by the query that is returning a single row. Best I can figure out is that, for whatever reason, when a single row is returned, the division results in 0. When more than 1 row is returned (even if the row has nulls in the field...which one of the rows does), the division works. Here are the contents of the table: C: | 71975571456 | 79966171136 D: | | G: | 206140624896 | 237003182080 I also noticed that in the case where the division worked, the sums are displayed as floats in the resulting row. Very odd.
Re: [sqlite] Strange behavior with sum
Kareem Badr wrote: > I should mention that free_space and size are defined as varchars, for > some reason. Not sure if that has anything to do with the issues I'm > seeing. > Do any of these fields contain NULL? as that will definately have an impact upon both joins and upon calculations. (when using a numeric field, it's always better to set the field NOT NULL, and default it to 0). -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
Re: [sqlite] Strange behavior with sum
I should mention that free_space and size are defined as varchars, for some reason. Not sure if that has anything to do with the issues I'm seeing.
Re: [sqlite] Strange behavior with sum
Yet, the following query: SELECT d.name, disks.name, sum(free_space), sum(size) FROM devices d LEFT OUTER JOIN disks ON (d.id=disks.computer_id AND disks.name='G:') WHERE d.name='kensho'; Gives the following result: kensho|G:|206140624896|237003182080 Which I can use to calculate the percentage outside of SQL. So...it's actually the division that's causing the problem? This query grabs the 2 sums separately, and the calculated percentage, with odd results: > SELECT d.name, sum(free_space), sum(size), sum(free_space)/sum(size) FROM devices d LEFT OUTER JOIN disks ON (d.id =disks.computer_id AND disks.name = 'G:') WHERE d.name='kensho'; > kensho|206140624896|237003182080|0 Does anyone know what's going on here? Is this just user error? Ultimately, what I'm trying to do is grab only those devices records that have free_space/size over a certain percentage. But to do that I need to use a HAVING clause that tests SUM(free_space)/SUM(size)...which always seems to be 0 when only one record is returned. Any help or insight would be greatly appreciated. Kareem Badr wrote: This is probably my own SQL rustiness, but can someone explain this to me? If I run this query: SELECT d.name, disks.name, free_space/size FROM devices d LEFT OUTER JOIN disks ON (d.id=disks.computer_id AND disks.name='G:') WHERE d.name='kensho' ; I get the following result: kensho|G:|0.869779988128673 If I run this query: SELECT d.name, disks.name, SUM(free_space)/SUM(size) FROM devices d LEFT OUTER JOIN disks ON(d.id=disks.computer_id AND disks.name='G:') WHERE d.name='kensho' ; I get the following result: kensho|G:|0 The only difference between the 2 queries is that I am returning SUM(free_space)/SUM(size) in the second one, istead of just free_space/size. Why is trying to sum the results when there is a single row resulting in 0? Is there just some weird SQL syntax that I am forgetting?
[sqlite] Strange behavior with sum
This is probably my own SQL rustiness, but can someone explain this to me? If I run this query: SELECT d.name, disks.name, free_space/size FROM devices d LEFT OUTER JOIN disks ON (d.id=disks.computer_id AND disks.name='G:') WHERE d.name='kensho' ; I get the following result: kensho|G:|0.869779988128673 If I run this query: SELECT d.name, disks.name, SUM(free_space)/SUM(size) FROM devices d LEFT OUTER JOIN disks ON(d.id=disks.computer_id AND disks.name='G:') WHERE d.name='kensho' ; I get the following result: kensho|G:|0 The only difference between the 2 queries is that I am returning SUM(free_space)/SUM(size) in the second one, istead of just free_space/size. Why is trying to sum the results when there is a single row resulting in 0? Is there just some weird SQL syntax that I am forgetting?
Re: [sqlite] Inserting python data structues into database
Not the question. You probably chose to store it in an SQL DBMS so that you can use the DML to access the data in some way. Do you want to access individual array rows, individual array elements or just store and retrieve the entire array? jt wrote: From pysqlite http://initd.org/tracker/pysqlite. On 6/2/06, John Stanton <[EMAIL PROTECTED]> wrote: Python newsgroup wrote: > Hi, > > What is the most efficient way to enter python binary data such as lists or > dictionaries in to sqlite? Has anyone had any experiences with this? We > will > be inserting a list of lists of integers into our database. > For example: > [[1,2,3],[1,4,6],[1,1,1],[2,4,6],[12,32,4],...,[1,3,4]] > > Any suggestions will be appreciated > > cheers, > Bijan > How do you want to access it?
Re: [sqlite] Query performance issues - index selection
Steve Green <[EMAIL PROTECTED]> wrote: > the unary + operator in front of the two utime terms did cause the optimal > index to be used... Can you explain why this works? A unary + in front of a column name disqualifies the term from consideration by the optimizer, and hence from use by indices. That left the other index as the only choice. > > What is the best way to send you sample data (and what is the maximum > file size that you'd like)? > Actually, if you could just send me your schema and the query that is causing problems by private email, I can probably use that to figure out why the optimizer is failing so badly in this case. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Query performance issues - index selection
Steve Green <[EMAIL PROTECTED]> wrote: > CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id ); > ... > CREATE INDEX ix_data_ut on data( u_id, utime ); Wouldn't a unique index on (u_id, utime, r_id) get you the pk constraint and better performance on your query with no other index to confuse things? Regards
Re: [sqlite] Query performance issues - index selection
Richard, Thanks for the reply. I did run ANALYZE and that didn't help. However, the unary + operator in front of the two utime terms did cause the optimal index to be used... Can you explain why this works? I also realized that if I change the "where" clause to something like where u_id >= 0 and utime >= ..., it will cuase the optimal index to be used (however, if we every started using negative u_id values, it would break). What is the best way to send you sample data (and what is the maximum file size that you'd like)? Steve [EMAIL PROTECTED] wrote: Steve Green <[EMAIL PROTECTED]> wrote: [...] select u_id, sum( data1 ), sum( data2 ) from data where utime >= 1146441600 and utime < 114912 group by u_id order by sum( data1 ) desc limit 10 [...] So, is there a way that I can get sqlite to use the optimal index without having to remove my unique index? Have you run ANALYZE. That might fix it. If not, try adding a unary + in front of the two utime terms in the WHERE clause: WHERE +utime >= 1145441600 AND +utime < 114912 If you can send me some sample data and queries, that would be great. It will help me to improve the optimizer so that these kinds of things come up less often in the future. -- D. Richard Hipp <[EMAIL PROTECTED]> -- Steve Green SAVVIS Transforming Information Technology SM This message contains information which may be confidential and/or privileged. Unless you are the intended recipient (or authorized to receive for the intended recipient), you may not read, use, copy or disclose to anyone the message or any information contained in the message. If you have received the message in error, please advise the sender by reply e-mail at [EMAIL PROTECTED] and delete the message and any attachment(s) thereto without retaining any copies.
Re: [sqlite] Query performance issues - index selection
Steve Green <[EMAIL PROTECTED]> wrote: [...] > > select u_id, sum( data1 ), sum( data2 ) > from data where utime >= 1146441600 and utime < 114912 > group by u_id > order by sum( data1 ) desc > limit 10 > [...] > > So, is there a way that I can get sqlite to use the optimal index > without having to remove my unique index? Have you run ANALYZE. That might fix it. If not, try adding a unary + in front of the two utime terms in the WHERE clause: WHERE +utime >= 1145441600 AND +utime < 114912 If you can send me some sample data and queries, that would be great. It will help me to improve the optimizer so that these kinds of things come up less often in the future. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Re: Query performance issues - index selection
Sorry, I forgot to mention that I'm using sqlite v3.3.4 on redhat linux v7.3 Steve Steve Green wrote: Hi, I have the following schema CREATE TABLE data( utime int4, r_id int2, u_id int4, data1 int8, data2 int8 ); Each row is uniquely defined by utime, r_id, and u_id, so I have the following index CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id ); This index is also needed because at update time, I use "insert or replace" to update the data table. The type of query that I'm attempting to perform is similar to select u_id, sum( data1 ), sum( data2 ) from data where utime >= 1146441600 and utime < 114912 group by u_id order by sum( data1 ) desc limit 10 My current table has about 2.5 million rows and about 86,000 distinct u_id values in the time period selected, and the query takes about 13.5 minutes. Performing an explain query plan reveals 0|0|TABLE data WITH INDEX pk_data so the primary index is being used... Based on some past experiences, I added the following index to the table CREATE INDEX ix_data_ut on data( u_id, utime ); Note that the utime is the last parameter in the index. With the primary index in place, I was not able to convince sqlite to use this index. To test the index, I was forced to drop the primary index (which I can't do in my production environment). After dropping the primary index, an explain query plan revealed 0|0|TABLE data WITH INDEX ix_data_ut and the query ran in 26 seconds... Subsequent tests using the following indexes provided no performance improvement over the unique index, although sqlite's query planner chose these indexes over the unique index CREATE INDEX ix_data_tu on data( utime, u_id ); CREATE INDEX ix_data_t on data( utime ); So, is there a way that I can get sqlite to use the optimal index without having to remove my unique index? Perhaps this would involve rewritting the query, but I'm at a loss as to how that could be done. Thanks for your time, Steve -- Steve Green SAVVIS Transforming Information Technology SM This message contains information which may be confidential and/or privileged. Unless you are the intended recipient (or authorized to receive for the intended recipient), you may not read, use, copy or disclose to anyone the message or any information contained in the message. If you have received the message in error, please advise the sender by reply e-mail at [EMAIL PROTECTED] and delete the message and any attachment(s) thereto without retaining any copies.
[sqlite] Query performance issues - index selection
Hi, I have the following schema CREATE TABLE data( utime int4, r_id int2, u_id int4, data1 int8, data2 int8 ); Each row is uniquely defined by utime, r_id, and u_id, so I have the following index CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id ); This index is also needed because at update time, I use "insert or replace" to update the data table. The type of query that I'm attempting to perform is similar to select u_id, sum( data1 ), sum( data2 ) from data where utime >= 1146441600 and utime < 114912 group by u_id order by sum( data1 ) desc limit 10 My current table has about 2.5 million rows and about 86,000 distinct u_id values in the time period selected, and the query takes about 13.5 minutes. Performing an explain query plan reveals 0|0|TABLE data WITH INDEX pk_data so the primary index is being used... Based on some past experiences, I added the following index to the table CREATE INDEX ix_data_ut on data( u_id, utime ); Note that the utime is the last parameter in the index. With the primary index in place, I was not able to convince sqlite to use this index. To test the index, I was forced to drop the primary index (which I can't do in my production environment). After dropping the primary index, an explain query plan revealed 0|0|TABLE data WITH INDEX ix_data_ut and the query ran in 26 seconds... Subsequent tests using the following indexes provided no performance improvement over the unique index, although sqlite's query planner chose these indexes over the unique index CREATE INDEX ix_data_tu on data( utime, u_id ); CREATE INDEX ix_data_t on data( utime ); So, is there a way that I can get sqlite to use the optimal index without having to remove my unique index? Perhaps this would involve rewritting the query, but I'm at a loss as to how that could be done. Thanks for your time, Steve -- Steve Green SAVVIS Transforming Information Technology SM This message contains information which may be confidential and/or privileged. Unless you are the intended recipient (or authorized to receive for the intended recipient), you may not read, use, copy or disclose to anyone the message or any information contained in the message. If you have received the message in error, please advise the sender by reply e-mail at [EMAIL PROTECTED] and delete the message and any attachment(s) thereto without retaining any copies.
Re: [sqlite] Inserting python data structues into database
From pysqlite http://initd.org/tracker/pysqlite. On 6/2/06, John Stanton <[EMAIL PROTECTED]> wrote: Python newsgroup wrote: > Hi, > > What is the most efficient way to enter python binary data such as lists or > dictionaries in to sqlite? Has anyone had any experiences with this? We > will > be inserting a list of lists of integers into our database. > For example: > [[1,2,3],[1,4,6],[1,1,1],[2,4,6],[12,32,4],...,[1,3,4]] > > Any suggestions will be appreciated > > cheers, > Bijan > How do you want to access it? -- Julien
Re: [sqlite] unsuscribe
Jay Sprenkle schreef: On 5/31/06, René Tegel <[EMAIL PROTECTED]> wrote: Eugene Wee schreef: > > Oh, and if one's email address is [EMAIL PROTECTED], according to > one of the first few emails I received from the mail manager, another > way to unsubscribe is to send an email to: > [EMAIL PROTECTED] Interesting.. this suggest that anyone could easily unsubscribe any other list member using an auto-reply account, since unsubscribing works much like subscribing just send 'from' [EMAIL PROTECTED] to [EMAIL PROTECTED] and the subscription will be ended... may work with out-of-office replies that include the [original subject] as well as paypal/ebay... I think you could unsub any account with an autoresponder on it since the email asking you to confirm your unsubscription would go to the subscriber and not to you. Yes, i believe that is true. It seems possible to unsubcribe an autoresponder account using any email address as from when requesting...
Re: [sqlite] Inserting python data structues into database
Python newsgroup wrote: Hi, What is the most efficient way to enter python binary data such as lists or dictionaries in to sqlite? Has anyone had any experiences with this? We will be inserting a list of lists of integers into our database. For example: [[1,2,3],[1,4,6],[1,1,1],[2,4,6],[12,32,4],...,[1,3,4]] Any suggestions will be appreciated cheers, Bijan How do you want to access it?
Re: [sqlite] Inserting python data structues into database
Try the pickle module, it dumps nearly any datastruct (except file object) in a string. You can load it back afterwards. On 6/1/06, Python newsgroup <[EMAIL PROTECTED]> wrote: Hi, What is the most efficient way to enter python binary data such as lists or dictionaries in to sqlite? Has anyone had any experiences with this? We will be inserting a list of lists of integers into our database. For example: [[1,2,3],[1,4,6],[1,1,1],[2,4,6],[12,32,4],...,[1,3,4]] Any suggestions will be appreciated cheers, Bijan -- Julien
[sqlite] Inserting python data structues into database
Hi, What is the most efficient way to enter python binary data such as lists or dictionaries in to sqlite? Has anyone had any experiences with this? We will be inserting a list of lists of integers into our database. For example: [[1,2,3],[1,4,6],[1,1,1],[2,4,6],[12,32,4],...,[1,3,4]] Any suggestions will be appreciated cheers, Bijan
Re: [sqlite] OffTopic: Locking on Windows XP
Don't know if this helps: http://support.microsoft.com/kb/Q296264 Looks like you need to turn optimistic locking off. Same is true with MS Access if the file is located on a share. http://support.microsoft.com/default.aspx?scid=KB;EN-US;q300216&; -- View this message in context: http://www.nabble.com/OffTopic%3A+Locking+on+Windows+XP-t1718929.html#a4669102 Sent from the SQLite forum at Nabble.com.
RE: [sqlite] OffTopic: Locking on Windows XP
>// Try and lock the file for writing
>ULARGE_INTEGER bytesToLock.QuadPart = 2147483648; // 2^31 if
(LockFile(h, 0, 0, bytesToLock.LowPart,
>bytesToLock.HighPart) == 0) { ...
>}
Sorry, finger trouble :)
Should read:
// Try and lock the file for writing
ULARGE_INTEGER bytesToLock.QuadPart = 2147483648; // 2^31
if (LockFile(h, 0, 0, bytesToLock.LowPart, bytesToLock.HighPart) == 0)
{
...
}
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
[sqlite] OffTopic: Locking on Windows XP
Hi,
This is more of a heads up than anything else. I'm developing an app
that writes to files using the Windows API. I actually used some code
from the SQLite file library hence this message.
The code snippet is:
// Try to open existing file
h = CreateFileA(path,
GENERIC_WRITE,
0,
NULL,
CREATE_NEW,
(FILE_ATTRIBUTE_NORMAL | FILE_FLAG_SEQUENTIAL_SCAN),
NULL);
if(h == INVALID_HANDLE_VALUE )
{
...
}
// Try and lock the file for writing
ULARGE_INTEGER bytesToLock.QuadPart = 2147483648; // 2^31
if (LockFile(h, 0, 0, bytesToLock.LowPart, bytesToLock.HighPart) == 0)
{
...
}
The app keeps the file locked until completion. The app takes a few mins
to run so I thought I test the locking. I opened Explorer and then
opened the file in Wordpad. Add a few garbage words and hit 'Save'. To
my surprise it had saved. I could confirm this by closing and reopening
the file in Wordpad seeing the change.
I checked my code and also tried commenting out the LockFile call (just
using the CreateFile call with the SharedMode parameter set to 0). Still
the same result.
I then changed the directory the app was writing to the local hard
drive. This time I got the usual "This file is opened by another
process..." popup when trying to open in Wordpad. So the difference was
the output directory.
It so happened that the output directory was on a network drive that had
been "Made available offline" (not sure its proper term) and was offline
at the time the app was running.
I then changed the output directory of the app to another online network
drive. Again, this time I got the usual "This file is opened by another
process..." popup when trying to open in Wordpad.
So unless someone could point out either a code/human error, I believe
locking capabilities are nonexistent for network drives that are have
file synchronisation enabled and are in offline mode.
Obviously DRH needs to verify but I'd imagine this could be a problem if
you use a multi-thread/multi-process application accessing a SQLite db
on a particular (though unlikely) network setup described above.
Regards
Nick
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
Re: [sqlite] Binary compatibility
Nikki Locke <[EMAIL PROTECTED]> wrote: > > Given that I am using SQLite.Net, if I execute this pragma as the first > command > passed over the connection, before creating any tables, will it work? Or do I > really have to execute it "before creating the database"? I think you're confusing creating the database with creating the file. When you open a non-existent db file, your file system is probably creating a file of zero size; the database hasn't been initialized yet. You can easily verify this by looking at the file size after you open() the db but before you do anything else. Regards
Re: [sqlite] How do you unsubscribe?
On Thu, 2006-06-01 at 09:28 -0700, Richard Battagline wrote: > How do you unsubscribe? > Send an email to [EMAIL PROTECTED] which will return all the information about the list or check your sqlite mailbox for the welcome message. -- G. Roderick Singleton <[EMAIL PROTECTED]> PATH tech smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] How do you unsubscribe?
Hi Richard, Send an email to [EMAIL PROTECTED] Actually, I sent an email to: [EMAIL PROTECTED] Hopefully it has resulted in an unsubscribe confirmation message sent to you, and replying to that email should unsubscribe you from this list. Regards, Eugene Wee Richard Battagline wrote: How do you unsubscribe?
Re: [sqlite] How do you unsubscribe?
On 6/1/06, Richard Battagline <[EMAIL PROTECTED]> wrote: How do you unsubscribe? Send an email to [EMAIL PROTECTED] -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] How do you unsubscribe?
Richard Battagline <[EMAIL PROTECTED]> wrote: > How do you unsubscribe? Read the headers of any message from the group.
RE: [sqlite] Multithreading. Again.
I was reading sqlite3_open documentation earlier this week and noticed that the docs say: "The returned sqlite3* can only be used in the same thread in which it was created. It is an error to call sqlite3_open() in one thread then pass the resulting database handle off to another thread to use. This restriction is due to goofy design decisions (bugs?) in the way some threading implementations interact with file locks." http://www.sqlite.org/capi3ref.html#sqlite3_open I was surprised to see this because I was under the impression that this issue had been fixed as you mentioned below. I'm glad to hear that it really is fixed. Is this a "documentation bug" leftover from previous versions? Thanks! Pat -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 01, 2006 8:38 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] Multithreading. Again. "Peter Cunderlik" <[EMAIL PROTECTED]> wrote: > Hello everybody, > > I'd like to ask a couple of silly questions before newcomers like me > get moderated. :-) I've browsed through the documentation and this > mailing list, trying to understand issues with multithreading. I'd > like if someone could confirm my conclusions. > > 1. As of SQLite 3.3.5, there is no multithreading (MT) problem with > the SQLite itself. All problems come from the underlying OS libraries. I would argue that this has always been the case. But beginning in version 3.3.1, SQLite has taken additional steps to partially work around problems in the OS. So the OS problems are less troublesome. > > 2. The only MT problem is with locking the database in a case when a > thread uses sqlite3 structure created by a different thread. Thus, > using connection pool (allocated by one thread, used by other threads) > is not safe. This was the case prior to version 3.3.1. Beginning with 3.3.1 and following, you can move an sqlite3 structure from one thread to another as long as there are no locks being held by that structure. The easiest way to make sure that no locks are held is to finalize all statements associated with the sqlite3 structure. > > 3. Just a thought: In case of in-memory database, no file locking is > required, so MT is no issue. (?) Correct. Of course, it has always been the case and probably always will be that you cannot use the same sqlite3 connection in two or more threads at the same time. You can use different sqlite3 connections at the same time in different threads, or you can move the same sqlite3 connection across threads (subject to the constraints above) but never, never try to use the same connection simultaneously in two or more threads. Often you will get an SQLITE_MISUSE error if you try to use the same sqlite3 connection in two threads at the same time, but there are race conditions which can cause this error check to be missed, so do not count on it. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] How do you unsubscribe?
How do you unsubscribe? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Queries against NULL data
That seems to work perfectly. How is the performance, comapared to changing the queries to check for NULL explicitly? I usually use the coalesce() function to fix this. For example: SELECT * FROm table WHERE coalesce(test_field,'') != 'test value'; The "coalesce(test_field,'')" will convert all NULL values of test_field to an empty string and leave all other values unchanged.
Re: [sqlite] Multithreading. Again.
"Peter Cunderlik" <[EMAIL PROTECTED]> wrote: > Hello everybody, > > I'd like to ask a couple of silly questions before newcomers like me > get moderated. :-) I've browsed through the documentation and this > mailing list, trying to understand issues with multithreading. I'd > like if someone could confirm my conclusions. > > 1. As of SQLite 3.3.5, there is no multithreading (MT) problem with > the SQLite itself. All problems come from the underlying OS libraries. I would argue that this has always been the case. But beginning in version 3.3.1, SQLite has taken additional steps to partially work around problems in the OS. So the OS problems are less troublesome. > > 2. The only MT problem is with locking the database in a case when a > thread uses sqlite3 structure created by a different thread. Thus, > using connection pool (allocated by one thread, used by other threads) > is not safe. This was the case prior to version 3.3.1. Beginning with 3.3.1 and following, you can move an sqlite3 structure from one thread to another as long as there are no locks being held by that structure. The easiest way to make sure that no locks are held is to finalize all statements associated with the sqlite3 structure. > > 3. Just a thought: In case of in-memory database, no file locking is > required, so MT is no issue. (?) Correct. Of course, it has always been the case and probably always will be that you cannot use the same sqlite3 connection in two or more threads at the same time. You can use different sqlite3 connections at the same time in different threads, or you can move the same sqlite3 connection across threads (subject to the constraints above) but never, never try to use the same connection simultaneously in two or more threads. Often you will get an SQLITE_MISUSE error if you try to use the same sqlite3 connection in two threads at the same time, but there are race conditions which can cause this error check to be missed, so do not count on it. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Queries against NULL data
Kareem Badr <[EMAIL PROTECTED]> wrote: > I read the section on the SQLite website regarding the inconsistencies > of how NULL values are handled in various databases. I didn't see > anything that mentioned what I am running into, though. > > In a nutshell, the following queries do not return rows when test_field > is NULL. It seems counter-intuitive to me. > > SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%' > SELECT * FROM my_table WHERE test_field != 'test value' > > I can understand the NOT LIKE query not returning rows with NULL more > than I can understand the != query not returning any rows. NULL does not > equal 'test value'. > > Is there an easy way around this, other than adding "OR test_field IS > NULL" to my queries? The example above is a lot simpler than the case I > need to handle in production, so I would like to avoid having to modify > every query to handle NULL values in a special case. > I usually use the coalesce() function to fix this. For example: SELECT * FROm table WHERE coalesce(test_field,'') != 'test value'; The "coalesce(test_field,'')" will convert all NULL values of test_field to an empty string and leave all other values unchanged. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Binary compatibility
D. Richard Hipp wrote: > 3.2.2 will refuse to read a database file created by 3.3.5 > unless version 3.3.5 was compiled with -DSQLITE_DEFAULT_FILE_FORMAT=1 > or the "PRAGMA legacy_file_format=ON" pragma is used prior to creating > the database. But if the database is created in a way that 3.2.2 > can read it, there should be no incompatibilities between 3.3.5 > and 3.2.2 (or any other SQLite version, for that matter). Anything > created by version 3.2.2 should always be readable by 3.3.5, regardless. Google can't find this pragma documented anywhere at sqlite.org. Given that I am using SQLite.Net, if I execute this pragma as the first command passed over the connection, before creating any tables, will it work? Or do I really have to execute it "before creating the database"? -- Nikki Locke, Trumphurst Ltd. PC & Unix consultancy & programming http://www.trumphurst.com/
Re: [sqlite] Queries against NULL data
On 6/1/06, Kareem Badr <[EMAIL PROTECTED]> wrote: I read the section on the SQLite website regarding the inconsistencies of how NULL values are handled in various databases. I didn't see anything that mentioned what I am running into, though. In a nutshell, the following queries do not return rows when test_field is NULL. It seems counter-intuitive to me. SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%' SELECT * FROM my_table WHERE test_field != 'test value' NULL is "nothing was ever entered into this column" Which is different from an "Empty string" was entered into this column. A user might legitimately want a field left blank, which is different from the case where they never entered anything for that field. Is there an easy way around this, other than adding "OR test_field IS NULL" to my queries? The example above is a lot simpler than the case I need to handle in production, so I would like to avoid having to modify every query to handle NULL values in a special case. No, except you could set a default value of the empty string for that column. If no value is specified, then the default is used, and you won't get NULLs. -- SqliteImporter, SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite
Re: [sqlite] Queries against NULL data
The behavior you are seeing is what is expected. NULL only every matches the IS NULL criteria. It doesn't pass equality, inequality, greater than or less than tests. The best option is to define the field as NOT NULL. NULLs cause all manner of trouble, and the best option is usually to avoid dealing with them. Clay Dowling Kareem Badr said: > I read the section on the SQLite website regarding the inconsistencies > of how NULL values are handled in various databases. I didn't see > anything that mentioned what I am running into, though. > > In a nutshell, the following queries do not return rows when test_field > is NULL. It seems counter-intuitive to me. > > SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%' > SELECT * FROM my_table WHERE test_field != 'test value' > > I can understand the NOT LIKE query not returning rows with NULL more > than I can understand the != query not returning any rows. NULL does not > equal 'test value'. > > Is there an easy way around this, other than adding "OR test_field IS > NULL" to my queries? The example above is a lot simpler than the case I > need to handle in production, so I would like to avoid having to modify > every query to handle NULL values in a special case. > -- Simple Content Management http://www.ceamus.com
[sqlite] Queries against NULL data
I read the section on the SQLite website regarding the inconsistencies of how NULL values are handled in various databases. I didn't see anything that mentioned what I am running into, though. In a nutshell, the following queries do not return rows when test_field is NULL. It seems counter-intuitive to me. SELECT * FROM my_table WHERE test_field NOT LIKE 'test value%' SELECT * FROM my_table WHERE test_field != 'test value' I can understand the NOT LIKE query not returning rows with NULL more than I can understand the != query not returning any rows. NULL does not equal 'test value'. Is there an easy way around this, other than adding "OR test_field IS NULL" to my queries? The example above is a lot simpler than the case I need to handle in production, so I would like to avoid having to modify every query to handle NULL values in a special case.
[sqlite] Multithreading. Again.
Hello everybody, I'd like to ask a couple of silly questions before newcomers like me get moderated. :-) I've browsed through the documentation and this mailing list, trying to understand issues with multithreading. I'd like if someone could confirm my conclusions. 1. As of SQLite 3.3.5, there is no multithreading (MT) problem with the SQLite itself. All problems come from the underlying OS libraries. 2. The only MT problem is with locking the database in a case when a thread uses sqlite3 structure created by a different thread. Thus, using connection pool (allocated by one thread, used by other threads) is not safe. 3. Just a thought: In case of in-memory database, no file locking is required, so MT is no issue. (?) Until now I've managed to avoid using DB from different threads, but now I have no other choice but to start using the DB in a MT fashion and I have to make my application run at least on Win32, RHEL4 and RH 7.3. Since I'm neither a kernel hacker nor an SQLite guru, I'm quite confused what is safe and where and when. My main question is - which OSes suffer from the database file locking problem? Maybe it'd be nice to have a table of OSes (their runtime libraries) and SQLite (compile-time options/defines) and possible problems with multithreading. Thanks for your time. Peter Cunderlik
Re: [sqlite] question about php_pdo_sqlite
yuyen wrote: > And the error message is: > "execute err: SQLSTATE[HY000]: General error: 8 attempt to write a > readonly database" > > So the problem is why the database becomes read only. > This time, you get another message :) Are you sure the Web Server user (Apache or...) has write access to your sqlite database file ?
Re: [sqlite] question about php_pdo_sqlite
Hi, DJ
I try the following code:
--
try{
$dsn = 'sqlite:counter/cntdata.sq3';
$myConn = new PDO($dsn);
$myConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$j = $myConn->exec('update test_item set balqty = 100');
}catch (PDOException $e){
die('execute err: '.$e->getMessage());
}
print("$j records are done!");
--
And the error message is:
"execute err: SQLSTATE[HY000]: General error: 8 attempt to write a readonly
database"
So the problem is why the database becomes read only.
Jack
[sqlite] Re: FW: confirm subscribe to sqlite-users@sqlite.org
"Anthireddy, Srinivasreddy IN BLR SISL" <[EMAIL PROTECTED]> wrote: > Hello, > > This is Srinivas Reddy A, working for SIEMENS INDIA. > > I'm wrote one application to take the performance measurements in c++ = > using sqlite_open(), sqlite_exec(), sqlite_close() API's how you people = > given one sample application in the website. > > It's taking the for Insert 1000 records is 1min : 59sec : 889millisec = > for table structure is 4 INTEGER's, 1 VARCHAR(100). But what you have = > taken the test environment and given the details in website = > (http://www.sqlite.org/speed.html) SQLite 2.7.6: 13sec.061millisec and = > SQLite 2.7.6 (nosync): 0min.223millisec only. > > Can you help me out how to use the SQLite to get the good performance. > > Aviating for your replay. > > Thanks & Regards, > Srinivas Reddy A. Please consider using SQLite version 3 instead of SQLite version 2. Version 3 is newer and is actively developed. SQLite 2 is in maintenance only. To make your INSERTs go faster, put them inside a transaction: BEGIN; INSERT ...; -- repeat 1000 times COMMIT; -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Purging the mailing list roles. Was: Please RestoreYourAccount Access
>Are you volunteering for the human moderator job? >I'm sure that Dr. Richard Hipp's time is worth much more doing something (perhaps anything) other than >moderating all messages posted here. I also think any human moderator will produce long delays in posting some >messages while they wait for a batch of message to queue up (or while they sleep). Dennis, You misunderstood me probably because I did not go into detail. All I was suggesting was that someone (and I'd volunteer if that helps) processes email from those on the moderated status list. This was not to moderate the whole list. I would guess that it would only be 1 or 2 messages a day. For example: Assume that there is a 3 month posting window. If you do not post in 3 months, you are placed on moderator status. The next message you send is held until a human moderator has confirmed the message was not spam. If the message is spam, the subscriber is removed from the list. If the message is not spam, than your posting window is extended for another 3 months. It was suppose to be a balance between maintaining the list to avoid spam and minimising the disruption to someone's social life. Anyway, ignore what I've said. What about simply forcing new subscribers to be moderated. On their first post, check the message isn't spam and then allow free posting thereafter. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite] question about php_pdo_sqlite
yuyen wrote:
> HI, JC
> I use php_do_sqlite with PHP 5.1.4 and Sqlite 3.2.8 on Windows XP. The
> following is my coding:
>
> try{
> $dsn = 'sqlite:counter/cntdata.sq3';
> $myConn = new PDO($dsn);
> $myConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
>
> $myPre = $myConn->prepare('update test_item set balqty = 100');
> $myPre->execute();
> }catch (PDOException $e){
> die('execute err: '.$e->getMessage());
> }
> print('done!');
> ?>
> -
> And the error is:
> execute err: SQLSTATE[HY000]: General error: 1 SQL logic error or
> missing database
>
> -
> Would you please have a look and give me an advise. Thank you!
>
>
> Jack
>
Not having a windows box at hand, I can only guess things :)
But, if I remember (and sounds like this in PDO code) dns is passed as
is, so for windows, your dsn should be:
$dsn = 'sqlite:counter\\cntdata.sq3';
or, if you want a really portable way:
$dsn = 'sqlite:counter' . DIRECTORY_SEPARATOR . 'cntdata.sq3';
JC
Re: [sqlite] question about php_pdo_sqlite
This one time, at band camp, "yuyen" <[EMAIL PROTECTED]> wrote:
> HI, JC
> I use php_do_sqlite with PHP 5.1.4 and Sqlite 3.2.8 on Windows XP. The
> following is my coding:
-8< snip
try {
$dbh = new PDO("sqlite:counter/cntdata.sq3";
/*** echo a message saying we have connected ***/
echo 'Connected to database';
/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** our sql statement ***/
$sql = 'UPDATE test_item SET balqty = 100';
/*** we use PDO::exec because no results are returned ***/
$dbh->exec($sql);
/*** give praise and thanks to the PHP/SQLite gods ***/
echo 'Item updated successfully';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $sql . '' . $e->getMessage();
}
?>
Kevin
--
"Democracy is two wolves and a lamb voting on what to have for lunch.
Liberty is a well-armed lamb contesting the vote."
