[sqlite] Merry Christmas
Merry Christmas to the whole mailing list and your families :) Artur Reilin sqlite.yuedream.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update not completely commit(ing)
Quoth Joe Bennett , on 2010-12-24 18:28:18 -0600: > Have a question regarding a particular issue I am dealing with... I > have a database in which I create a pivot table to get a no dupe list > using a 'select distinct'. I then take that list into Python and then > iterate over it to look up data in another table to add in the > latitude and longitude for each entry. Each entry could have over a > thousand 'dupes' that I am trying to update with the lat/lon... > Anyway, what I have noticed is that when I run the following Python > 'SQLite command' sometimes all the 'dupes' get updates and sometimes > some do and some don't as well as sometimes none get updated... I am > able to replicate this with the same SQLite command in SQLite Manager: > > update_data = 'update matrix set %s = %f, %s = %f where %s = "%s"' % > (A_B + '_Lat', Lat_Site, A_B + '_Lon',Lon_Site, A_B, Site[0]) This is pretty hard to determine from just that statement. Actually showing us your schema would help, and some example rows that you expect to be updated by a particular query (after all the substitutions), the results that you get instead, and how you got the results back out of the database to check (including full SELECT query if relevant). But first things first. *cues the instrumental accompaniment* o/` Oompa loompa doopity doo I've got another puzzle for you Oompa loompa floopity fliss If you are wise you're watching for this What do you get writing SQL strings Attempting to earn both your lexical wings Could be a typo that doubled your quotes Or maybe you misread the notes In the FAQ list... http://sqlite.org/faq.html#q24 Oompa loompa bloopity blurn SQL syntax is easy to learn It might help your queries work too Like the oompa loompa oompa Oompa loompa doopity do o/` ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update not completely commit(ing)
On 25 Dec 2010, at 12:28am, Joe Bennett wrote: > update_data = 'update matrix set %s = %f, %s = %f where %s = "%s"' % > (A_B + '_Lat', Lat_Site, A_B + '_Lon',Lon_Site, A_B, Site[0]) You've used double quotes. I'm guessing that the value is a string, in which case you mean single quotes. But that depends on whether your Site values are numeric or alphas. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update not completely commit(ing)
Hi all, Have a question regarding a particular issue I am dealing with... I have a database in which I create a pivot table to get a no dupe list using a 'select distinct'. I then take that list into Python and then iterate over it to look up data in another table to add in the latitude and longitude for each entry. Each entry could have over a thousand 'dupes' that I am trying to update with the lat/lon... Anyway, what I have noticed is that when I run the following Python 'SQLite command' sometimes all the 'dupes' get updates and sometimes some do and some don't as well as sometimes none get updated... I am able to replicate this with the same SQLite command in SQLite Manager: update_data = 'update matrix set %s = %f, %s = %f where %s = "%s"' % (A_B + '_Lat', Lat_Site, A_B + '_Lon',Lon_Site, A_B, Site[0]) I know that the chosen method to add in the variables is not recommended, but I have not figured out how to do it with the ?. So this is what I have right now... Functionality trumps security in this case as I am the only one even opening the file... Any ideas on what I'm doing wrong to get this sporadic result for the updates? -Joe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete from t1 where not in (null)
On Fri, Dec 24, 2010 at 5:19 PM, Andrew Z wrote: > My application accesses an SQLite database created by another > application, and I noticed on my system (SQLite 3.7.2, Fedora 14) if > the subquery returns no values, the delete clause does nothing. Is > this a bug in SQLite? No. SQLite is giving the correct answer. There are NULLs in the right-hand side of your NOT IN clause. Try this instead: delete from moz_favicons where id not in (select favicon_id from moz_places where favicon_id is not null); > The expected result below is that all rows from > moz_favicons are deleted. > > > CREATE TABLE moz_favicons ( id INTEGER PRIMARY KEY, url LONGVARCHAR > UNIQUE, data BLOB, mime_type VARCHAR(32), expiration LONG); > INSERT INTO "moz_favicons" > VALUES(1,'http://fedoraproject.org/static/images/favicon.ico > ','2','image/png',1293223842352252); > INSERT INTO "moz_favicons" > VALUES(2,'http://slashdot.org/favicon.ico > ','82','image/png',1293221318471851); > CREATE TABLE moz_places ( id INTEGER PRIMARY KEY, url LONGVARCHAR, > title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT > 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT > NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, > last_visit_date INTEGER ); > INSERT INTO "moz_places" > > VALUES(1,'place:redirectsMode=2&sort=8&maxResults=10','redirectsMode=2&sort=8&maxResults=10',NULL,0,1,0,NULL,0,NULL); > INSERT INTO "moz_places" > > VALUES(2,'place:folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&excludeItemIfParentHasAnnotation=livemark%2FfeedURI&maxResults=10&excludeQueries=1','folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&excludeItemIfParentHasAnnotation=livemark%2FfeedURI&maxResults=10&excludeQueries=1',NULL,0,1,0,NULL,0,NULL); > INSERT INTO "moz_places" > > VALUES(3,'place:type=6&sort=14&maxResults=10','type=6&sort=14&maxResults=10',NULL,0,1,0,NULL,0,NULL); > INSERT INTO "moz_places" > VALUES(4,'http://docs.fedoraproject.org/release-notes/ > ','/release-notes/','gro.tcejorparodef.scod.',0,0,0,NULL,140,NULL); > > > select "select distinct favicon_id from moz_places"; > select distinct favicon_id from moz_places; > > select "deleting: standard method..."; > delete from moz_favicons where id not in (select distinct favicon_id > from moz_places); -- here is the alleged bug? > > select "remaining URLs"; > select url from moz_favicons; > > select "deleting: dummy, non-null value..."; > delete from moz_favicons where id not in (0); > > select "remaining URLs"; > select url from moz_favicons; > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete from t1 where not in (null)
Hi, SQLite sounds pretty reasonnable to me: >select "select distinct favicon_id from moz_places"; >select distinct favicon_id from moz_places; Returns NULL >select "deleting: standard method..."; >delete from moz_favicons where id not in (select distinct favicon_id >from moz_places); -- here is the alleged bug? Are any id row(s) in moz_favicons = NULL ? No, hence I don't any surprise here. Rows in moz_favicons are: id url datamime_type expiration 1 http://fedoraproject.org/static/images/favicon.ico (BLOB) image/png 1293223842352252 2 http://slashdot.org/favicon.ico (BLOB) image/png 1293221318471851 No, there are no NULLs in IDs. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] delete from t1 where not in (null)
My application accesses an SQLite database created by another application, and I noticed on my system (SQLite 3.7.2, Fedora 14) if the subquery returns no values, the delete clause does nothing. Is this a bug in SQLite? The expected result below is that all rows from moz_favicons are deleted. CREATE TABLE moz_favicons ( id INTEGER PRIMARY KEY, url LONGVARCHAR UNIQUE, data BLOB, mime_type VARCHAR(32), expiration LONG); INSERT INTO "moz_favicons" VALUES(1,'http://fedoraproject.org/static/images/favicon.ico','2','image/png',1293223842352252); INSERT INTO "moz_favicons" VALUES(2,'http://slashdot.org/favicon.ico','82','image/png',1293221318471851); CREATE TABLE moz_places ( id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER ); INSERT INTO "moz_places" VALUES(1,'place:redirectsMode=2&sort=8&maxResults=10','redirectsMode=2&sort=8&maxResults=10',NULL,0,1,0,NULL,0,NULL); INSERT INTO "moz_places" VALUES(2,'place:folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&excludeItemIfParentHasAnnotation=livemark%2FfeedURI&maxResults=10&excludeQueries=1','folder=BOOKMARKS_MENU&folder=UNFILED_BOOKMARKS&folder=TOOLBAR&queryType=1&sort=12&excludeItemIfParentHasAnnotation=livemark%2FfeedURI&maxResults=10&excludeQueries=1',NULL,0,1,0,NULL,0,NULL); INSERT INTO "moz_places" VALUES(3,'place:type=6&sort=14&maxResults=10','type=6&sort=14&maxResults=10',NULL,0,1,0,NULL,0,NULL); INSERT INTO "moz_places" VALUES(4,'http://docs.fedoraproject.org/release-notes/','/release-notes/','gro.tcejorparodef.scod.',0,0,0,NULL,140,NULL); select "select distinct favicon_id from moz_places"; select distinct favicon_id from moz_places; select "deleting: standard method..."; delete from moz_favicons where id not in (select distinct favicon_id from moz_places); -- here is the alleged bug? select "remaining URLs"; select url from moz_favicons; select "deleting: dummy, non-null value..."; delete from moz_favicons where id not in (0); select "remaining URLs"; select url from moz_favicons; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VFS and hot journals
Hi, I recently discovered that for vfs implemented writable formats that actually change the data of the sqlite base (encryption, compression), there should be a complex logic behind the restoration from the hot journal, at least some versions ago. For correct password handling initially I relied on the test read after the first open to detect wrong password and reopen the db with a new one, but if a hot file is present one can lose the data. It looked like between no-error open (that usually always the case for any db) and my test read, there was also a restoration attempt and at least sqlite 3.6.10 deleted -journal if this attempt failed. I implemented a more complex logic when first open also tried to read data from header and reported SQLITE_NOTADB if it could not. This worked for 3.6.10 I tried also 3.7.4 and it looks like starting some version sqlite don't delete -journal if vfs failed to deliver the correct data. But if I disable my complex logic during open, after the correct password is supplied and vfs starts to return correct data, I see some reading/file size requests and something is done, but the final file is malformed. If I repeat the procedure with 3.6.10 and tricky open the restoration process correctly returns the original db (I compared them bit to bit). 3.7.4 with this complex open also returns the correct db. I know that it's deep about internals and I still can rely on my complex logic, but what was that change between 3.6.10 and 3.7.4 that fixed -journal deleting, but introduced something new that sometimes prevents the program from working correctly? Thanks, Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
> Select >H1.ID > from >HASH1 H1 > where >x1_y1 BETWEEN min11 AND max11 AND >x1_y2 BETWEEN min12 AND max12 AND >x1_y3 BETWEEN min13 AND max13 AND >x1_y4 BETWEEN min14 AND max14 AND >x1_y5 BETWEEN min15 AND max15; > > no it's not work at all !! without an rtree index the speed is a total disaster (several minutes) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
i do it.. but it's change nothing :( On 12/24/2010 3:47 PM, Simon Slavin wrote: > On 24 Dec 2010, at 8:17am, Vander Clock Stephane wrote: > >> I have a key like this >> >> 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i >> call node >> Node are integer comprise between 0 and 255 (bytes) > You mean from 000 to 254. 255 breaks your system because you are using > modulus 255. > >> and i need to found "similare" key. >> A similar key is a key where one node is max + - 10 more or lower than >> another node >> >> so >> >> 123-098-230-120-111 >> is similare to >> 120-097-235-118-110 >> >> but not similare to >> 180-197-215-018-010 >> >> it's for our graphical software > Precalculate five sets of minimum and maximum bounds: > > min11 = max((<#randomnumber> % 255)-10,0) > max11 = min((<#randomnumber> % 255)+10,255) > > Then you can just seize the ten values you need from the table and use them > to make up your SELECT command. > > Select >H1.ID > from >HASH1 H1 > where >x1_y1 BETWEEN min11 AND max11 AND >x1_y2 BETWEEN min12 AND max12 AND >x1_y3 BETWEEN min13 AND max13 AND >x1_y4 BETWEEN min14 AND max14 AND >x1_y5 BETWEEN min15 AND max15; > > This will be faster than trying to get SQLite to do the calculations for > every row of your 50 000 000 row table. > > It will work much faster with an index on x1_y1. > It may or may not work faster with an index on > (x1_y1,x1_y2,x1_y3,x1_y4,x1_y5). Try it. > > Simon. > ___ > 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] how to speed up this ?
On 24 Dec 2010, at 12:47pm, Simon Slavin wrote: > Precalculate five sets of minimum and maximum bounds: > > min11 = max((<#randomnumber> % 255)-10,0) > max11 = min((<#randomnumber> % 255)+10,255) > > Then you can just seize the ten values you need from the table and use them > to make up your SELECT command. Sorry, replace the 'seize the ten values you need from the table' with 'use those ten values'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
On 24 Dec 2010, at 8:17am, Vander Clock Stephane wrote: > I have a key like this > > 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i > call node > Node are integer comprise between 0 and 255 (bytes) You mean from 000 to 254. 255 breaks your system because you are using modulus 255. > and i need to found "similare" key. > A similar key is a key where one node is max + - 10 more or lower than > another node > > so > > 123-098-230-120-111 > is similare to > 120-097-235-118-110 > > but not similare to > 180-197-215-018-010 > > it's for our graphical software Precalculate five sets of minimum and maximum bounds: min11 = max((<#randomnumber> % 255)-10,0) max11 = min((<#randomnumber> % 255)+10,255) Then you can just seize the ten values you need from the table and use them to make up your SELECT command. Select H1.ID from HASH1 H1 where x1_y1 BETWEEN min11 AND max11 AND x1_y2 BETWEEN min12 AND max12 AND x1_y3 BETWEEN min13 AND max13 AND x1_y4 BETWEEN min14 AND max14 AND x1_y5 BETWEEN min15 AND max15; This will be faster than trying to get SQLite to do the calculations for every row of your 50 000 000 row table. It will work much faster with an index on x1_y1. It may or may not work faster with an index on (x1_y1,x1_y2,x1_y3,x1_y4,x1_y5). Try it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to speed up this ?
can you gave me the name of a good SSD you advise me to buy ? i decide to make a try ! Thanks again stéphane On 12/24/2010 12:24 AM, John Drescher wrote: > On Thu, Dec 23, 2010 at 4:06 PM, Vander Clock Stephane > wrote: >> that very very much expensive :( how much you thing ? >> > $500 to $600 US. > > John > ___ > 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] how to speed up this ?
> Can you describe what you're trying to do with that command ? of course ! I have a key like this 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i call node Node are integer comprise between 0 and 255 (bytes) and i need to found "similare" key. A similar key is a key where one node is max + - 10 more or lower than another node so 123-098-230-120-111 is similare to 120-097-235-118-110 but not similare to 180-197-215-018-010 it's for our graphical software so i add in a rtree table the key like this insert into rtreetable Node1_min = 123-10, Node1_max = 123+10, Node2_min = 098-10, Node2_max = 098+10, etc.. and after to detect similare node i simply do select from rtreetable where Node1_min >= 120 and Node1_max <= 120 and Node2_min >= 098 and Node2_max <= 098 and etc... it's ok ? thanks you by advance ! stéphane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
