[sqlite] sqlite3-dbf - converter of XBase / FoxPro tables to SQLite
This is not tested enough but it's work for me. May be it's interesting for somebody. http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3-dbf - converter of XBase / FoxPro tables to SQLite
Alexey Pechnikov pechni...@... writes: This is not tested enough but it's work for me. May be it's interesting for somebody. http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf I'm only a heavy user (!= programmer) so if you have an extension for directly importing dbf-files into sqlite you make me very happy; BTW: I've to work with M$ and can't use standard unix libraries. thx in advance ;-) Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQlite 3.7.0 fix for sys/mman.h inclusion
When building sqlite 3.7.0 on a system that doesn't have mmap, I use the SQLITE_OMIT_WAL option, but this check didn't make it around the sys/mman.h inclusion. Patch attached to fix this problem. Thanks, Alan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Repairing a Database.
Thank you, I believe this will help. Since my last email I took Rogers suggestion of looking over the structure of a database file and decided that if there is anything recovered from the .dump that it will need to be inserted into a database template in order to be functional. The result was successful. I created a new database by importing the schema of a backup database that had not been corrupted like this; .output Schema.txt .schema I then created a new database and set all of my user_version, journal_mode, etc. to match my original pre-corrupt specifications. Then I used .read to read in Schema.txt. This created an empty template database. Then I read in my dump file. The only errors were that the tables already exist but the insert into statements went through and my new database will launch in my application and I am able to recover the 3D symbols that were stored there and back them up. It is not a 100% of course but it shows promise for future corrupt databases. I will test it on a few more of my client's databases and send an update of my findings. Thank you all for your help, ~Kirk -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, August 05, 2010 4:40 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Repairing a Database. On 4 Aug 2010, at 10:15pm, Kirk Clemons wrote: Also, does this mean that I could take a backup copy of my database and import the data that is retrieved from the .dump command on the corrupt database? We cannot tell what .dump will get from your old database because it is corrupt. It might miss out lots of records. It might appear to be doing all the records but actually put the same values in each one. It might dump the entire database perfectly. If so how would I do this and get past the PRIMARY KEY/existing table errors? You can edit the file to replace 'INSERT' with 'INSERT OR IGNORE' or some variation on that. See http://www.sqlite.org/lang_insert.html 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
[sqlite] Slowdown when adding terms to query
Hi all, I have a query that is working at an acceptable speed, and I need to add something else to it. As soon as I add the extra terms it grinds to a halt (taking well over 5 minutes to perform the query on a relatively small dataset c.100,000 records). I've looked into the speed optimisations suggested on the site and tried a few but can't seem to speed it up. I think setting better indices will help me, but I'm fairly green when it comes to choosing which will be best for a query. My acceptably fast query is fairly complicated as it pulls information from a number of different tables, linking them all using WHERE clauses (I've read somewhere this is the fastest way to do this in SQLite). It is shown below (apologies for it's length) though I have cut out the information about which columns it is selecting as it makes it too long to comfortably read. *SELECT* Multiple columns from each table *FROM* Delivery, Match, Tour, Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Batsman.ID = 1234567890); There are often multiple IDs specified at the end of the WHERE clause, as these are added depending upon selections a user has made from a GUI. In a very specific case I need to select the above plus one unrelated row. Therefore my query changes to (for example): *SELECT* Multiple columns from each table *FROM* Delivery, Match, Tour, Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Delivery.ID = 1987654321); This is the query that takes an unbelievable amount of time to process compared to the approximately 10 seconds the first query does. Does anyone have any suggestions for what indices to use or how to change this query to speed it up? I have done an EXPLAIN QUERY PLAN on the query, which gave me the following: 0,2,TABLE Tour 1,4,TABLE Country 2,1,TABLE Match 3,11,TABLE Team AS BowlingTeam 4,12,TABLE Team AS BattingTeam 5,13,TABLE Team AS HomeTeam 6,14,TABLE Team AS AwayTeam 7,3,TABLE Ground 8,8,TABLE Player AS Bowler 9,9,TABLE Player AS Batsman 10,10,TABLE Player AS Partner 11,5,TABLE PlayerTeam AS BowlerPT 12,6,TABLE PlayerTeam AS BatsmanPT 13,0,TABLE Delivery VIA MULTI-INDEX UNION 14,7,TABLE PlayerTeam AS PartnerPT 0,0,TABLE Delivery WITH INDEX Delivery_BatsmanPTIdx 0,0,TABLE Delivery WITH INDEX sqlite_autoindex_Delivery_1 But I am unsure on how to read this, and what information it is giving me about how to better select my indices. Thanks in advance for all your help, Ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: Slowdown when adding terms to query
Try putting Delivery.ID=1987654321 at the front of your query. I suspect that's a pretty small set. And I also assume you have a Delivery index for ID. Would help if you would show your indexes. Also, show the explain with and without the added column. That should show what it's doing different. Also...how big is the database? Can you increase the cache to squeeze it all in memory? PRAGMA cache_size=? -- default is 2000. I believe the general rule is to do the table joins from least-to-most matches. If you have one part of your query that returns a very small set do that first. I'm sure somebody will have a much better solution than what I'm saying here...I'm always having to re-learn SQL as I don't use it very often. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Edward Hawke Sent: Fri 8/6/2010 4:23 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] Slowdown when adding terms to query Hi all, I have a query that is working at an acceptable speed, and I need to add something else to it. As soon as I add the extra terms it grinds to a halt (taking well over 5 minutes to perform the query on a relatively small dataset c.100,000 records). I've looked into the speed optimisations suggested on the site and tried a few but can't seem to speed it up. I think setting better indices will help me, but I'm fairly green when it comes to choosing which will be best for a query. My acceptably fast query is fairly complicated as it pulls information from a number of different tables, linking them all using WHERE clauses (I've read somewhere this is the fastest way to do this in SQLite). It is shown below (apologies for it's length) though I have cut out the information about which columns it is selecting as it makes it too long to comfortably read. *SELECT* Multiple columns from each table *FROM* Delivery, Match, Tour, Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Batsman.ID = 1234567890); There are often multiple IDs specified at the end of the WHERE clause, as these are added depending upon selections a user has made from a GUI. In a very specific case I need to select the above plus one unrelated row. Therefore my query changes to (for example): *SELECT* Multiple columns from each table *FROM* Delivery, Match, Tour, Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Delivery.ID = 1987654321); This is the query that takes an unbelievable amount of time to process compared to the approximately 10 seconds the first query does. Does anyone have any suggestions for what indices to use or how to change this query to speed it up? I have done an EXPLAIN QUERY PLAN on the query, which gave me the following: 0,2,TABLE Tour 1,4,TABLE Country 2,1,TABLE Match 3,11,TABLE Team AS BowlingTeam 4,12,TABLE Team AS BattingTeam 5,13,TABLE Team AS HomeTeam 6,14,TABLE Team AS AwayTeam 7,3,TABLE Ground 8,8,TABLE Player AS Bowler 9,9,TABLE Player AS Batsman 10,10,TABLE Player AS Partner 11,5,TABLE
Re: [sqlite] Slowdown when adding terms to query
On Fri, Aug 6, 2010 at 5:23 AM, Edward Hawke edhawk...@googlemail.comwrote: I have done an EXPLAIN QUERY PLAN on the query, which gave me the following: 0,2,TABLE Tour 1,4,TABLE Country 2,1,TABLE Match 3,11,TABLE Team AS BowlingTeam 4,12,TABLE Team AS BattingTeam 5,13,TABLE Team AS HomeTeam 6,14,TABLE Team AS AwayTeam 7,3,TABLE Ground 8,8,TABLE Player AS Bowler 9,9,TABLE Player AS Batsman 10,10,TABLE Player AS Partner 11,5,TABLE PlayerTeam AS BowlerPT 12,6,TABLE PlayerTeam AS BatsmanPT 13,0,TABLE Delivery VIA MULTI-INDEX UNION 14,7,TABLE PlayerTeam AS PartnerPT 0,0,TABLE Delivery WITH INDEX Delivery_BatsmanPTIdx 0,0,TABLE Delivery WITH INDEX sqlite_autoindex_Delivery_1 But I am unsure on how to read this, and what information it is giving me about how to better select my indices. What this shows us is that, except for the subqueries on the Delivery table, you are not using any indices anywhere. As far as this query is concerned, you might as well not have any indices on any of your tables other than Delivery. You can start by adding indices on terms that appear on either side of an = in your WHERE clause. The whole story is rather more complex, but that will be a good start. -- - 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] Slowdown when adding terms to query
Edward Hawke edhawk...@googlemail.com wrote: I have a query that is working at an acceptable speed, and I need to add something else to it. As soon as I add the extra terms it grinds to a halt (taking well over 5 minutes to perform the query on a relatively small dataset c.100,000 records). But because you repeat the same table three-four times in the FROM clause, thus building a cross-product, you are effectively working with a dataset of some 10^15 records. Anything that suppress the use of indexes by SQLite will cause performance to tank. My acceptably fast query is fairly complicated as it pulls information from a number of different tables, linking them all using WHERE clauses (I've read somewhere this is the fastest way to do this in SQLite). It is shown below (apologies for it's length) though I have cut out the information about which columns it is selecting as it makes it too long to comfortably read. *SELECT* Multiple columns from each table *FROM* Delivery, Match, Tour, Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Batsman.ID = 1234567890); There are often multiple IDs specified at the end of the WHERE clause, as these are added depending upon selections a user has made from a GUI. In a very specific case I need to select the above plus one unrelated row. Therefore my query changes to (for example): *SELECT* Multiple columns from each table *FROM* Delivery, Match, Tour, Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID AND Delivery.ID = 1987654321); Using OR pretty much kills optimization in SQLite. Run this as two separate queries, or as a UNION query with two subqueries, each of which only uses AND. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE %key%) searches, would FTS3 with suffix-tree tokenizer be the fast way?
A variant on Simon's plan. Are the 10,000 rows static, slowly changing, or frequently changing? Does it make sense to pre-calculate some counts at the time data is loaded? Is this memory constrained so much that you can't afford 1 or 2 MB to let you look up based on ints? (I'm assuming that one letter is all you are after, either 'starts with' or 'contains' and not in order combinations.) Adam On Thu, Aug 5, 2010 at 5:40 PM, Simon Slavin slav...@bigfraud.org wrote: On 5 Aug 2010, at 10:03pm, Sam Roberts wrote: But do you think the section would make the counting faster? I think I'd have to get the row counts like this, which would still do the slow full table scan: select section, count(*) from my_table where name like '%e%' group by section; But 'group by section' can profit from the index on the section column so it should be faster. As with all these things, the suggestion is to try it and see. You should try six or seven different solutions including shuffling columns and indexes before you settle on the one that will be in your final code. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3-dbf - converter of XBase / FoxPro tables to SQLite
Sources for MinGW and compiled binary. Note: MEMO files does not supported because I don't know how to emulate mmap interface for MinGW http://mobigroup.ru/files/sqlite3-dbf/ 2010/8/6 Oliver Peters oliver@web.de: I'm only a heavy user (!= programmer) so if you have an extension for directly importing dbf-files into sqlite you make me very happy; BTW: I've to work with M$ and can't use standard unix libraries. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] copying only new rows from one table to another
Newbie question I have two tables and I want to copy (occasionally) a few thousand columns that have been added to table1 into table2, both tables have the same unique key but otherwise the columns are different. table2 will be initially populated from table1 using INSERT into table table2 SELECT refno from table1 WHERE flag 0 I could drop table2 and then do the above but will then obviously lose any data that has been modified in table2. How can I just append the new rows from table1 into table2 Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copying only new rows from one table to another
Paul Sanderson sandersonforens...@gmail.com wrote: I have two tables and I want to copy (occasionally) a few thousand columns that have been added to table1 into table2, both tables have the same unique key but otherwise the columns are different. table2 will be initially populated from table1 using INSERT into table table2 SELECT refno from table1 WHERE flag 0 I could drop table2 and then do the above but will then obviously lose any data that has been modified in table2. How can I just append the new rows from table1 into table2 If refno is a primary key in table2, or has a unique constraint on it, you can do insert or ignore into table2(refno) select refno from table1; or ingore part instructs SQLite to skip over all rows that would cause uniqueness conflict if inserted. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copying only new rows from one table to another
Thanks igor - ill give that a go. On 6 August 2010 16:36, Igor Tandetnik itandet...@mvps.org wrote: Paul Sanderson sandersonforens...@gmail.com wrote: I have two tables and I want to copy (occasionally) a few thousand columns that have been added to table1 into table2, both tables have the same unique key but otherwise the columns are different. table2 will be initially populated from table1 using INSERT into table table2 SELECT refno from table1 WHERE flag 0 I could drop table2 and then do the above but will then obviously lose any data that has been modified in table2. How can I just append the new rows from table1 into table2 If refno is a primary key in table2, or has a unique constraint on it, you can do insert or ignore into table2(refno) select refno from table1; or ingore part instructs SQLite to skip over all rows that would cause uniqueness conflict if inserted. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paul Sanderson Sanderson Forensics +44 (0)1869 325667 www.sandersonforensics.com http://www.twitter.com/sandersonforens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database is locked
I expect that this has been asked before but I cant find anything useful via google. I am updating about 20 rows in a table and setting a particular value to a particular value. The first time I do this all is OK, if I try again a few seconds later I get a database is locked error. The code is (using UniDAC), x is passed as a value to the function try { UniConnection1-StartTransaction(); for(int i=0; iCount; i++) { Tab-RecNo = i; Tab-Edit(); Tab-FieldByName(cat)-AsInteger = x; ThumbTab-Post(); } } __finally { UniConnection1-Commit(); Beep(1000,200); } I have added the Beep message to make sure that commit is returning quickly, which it is doing I am updating the same set of records, if I move on within the dataset by count records and then update a new set of rows all works OK, if I then move back to the previous set and update all is OK. The error just happens when I trya nd update the same set of records on two consecutive occasions What can cause the dataabse to be locked in this way, are there any sqliteisms I should be looking for. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE %key%) searches, would FTS3 with suffix-tree tokenizer be the fast way?
On Fri, Aug 6, 2010 at 6:11 AM, Adam DeVita adev...@verifeye.com wrote: A variant on Simon's plan. Are the 10,000 rows static, slowly changing, or frequently changing? Never change, it's read-only. Does it make sense to pre-calculate some counts at the time data is loaded? Is this memory constrained so much that you can't afford 1 or 2 MB to let you look up based on ints? (I'm assuming that one letter is all you are after, either 'starts with' or 'contains' and not in order combinations.) No, substrings, it's just that I then need a count of matching substrings by first char. Good idea, there are a number of other queries where pre-calculating is linear in the space cost, but here the the usage is interactive search, where as they type more of the name, it narrows down the search results as people type in more. Pre-calculating would be about 40 factorial in space, there are about 64000 3-character strings, and then once they typed the 4th char in it would be slow again. Of course, not all of those exist. Hm. Maybe I'll try to precalculate the suffix tree, and see how many results there really are, I don't need to store zero results. The fastest I've found so far is using FTS3. Its a little slow, but not unusably so. There are only 2500 rows now, I hope that it will scale well as the DB increases in size. I'm still considering other approaches, maybe a custom b-tree. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)
On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. phajdan...@chromium.orgwrote: I'm attaching a suggested patch to fix locale-unsafe usage of tolower in FTS code. The goal is to make Chromium closer to the upstream, so if you have a better solution, that's great. Oh, I have just noticed that the mailing list removes all attachments. What is the best way to send patches then? By the way, any suggestions about the Chromium patch I linked to ( http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup)? It seems that it has somehow been fixed in fts3 code. I'm not yet very familiar with the SQLite codebase though, so could you point me to the fixes? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copying only new rows from one table to another
On Fri, Aug 06, 2010 at 04:30:49PM +0100, Paul Sanderson scratched on the wall: Newbie question I have two tables and I want to copy (occasionally) a few thousand columns that have been added to table1 into table2, both tables have the same unique key but otherwise the columns are different. table2 will be initially populated from table1 using INSERT into table table2 SELECT refno from table1 WHERE flag 0 I could drop table2 and then do the above but will then obviously lose any data that has been modified in table2. How can I just append the new rows from table1 into table2 In addition to Igor's suggestion, you may also be able to do an LEFT OUTER JOIN, and look for NULLs in the right-hand side (assuming your unique keys are NOT NULL): INSERT INTO table2 SELECT table1.refno, ... FROM table1 LEFT OUTER JOIN table2 ON table1.key = table2.key WHERE table2.key IS NULL; I have no idea which would be faster. You'll need to try and see. You could also try to do a sub-query that does a compound EXCEPT, although I would guess that's slower for this case. I don't actually know, however. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)
If ch is an unsigned char then how is the following unsafe: ch = (ch0x80) ? tolower(ch) : ch And why does it need to be changed to ch = (ch='A' ch='Z') ? ch - 'A' + 'a' : ch; There is only one such instance of code remaining in FTS3 (at fts3_tokenizer1.c:196) but I want to understand what the issue is before I change it. On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr. phajdan...@chromium.orgwrote: On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. phajdan...@chromium.org wrote: I'm attaching a suggested patch to fix locale-unsafe usage of tolower in FTS code. The goal is to make Chromium closer to the upstream, so if you have a better solution, that's great. Oh, I have just noticed that the mailing list removes all attachments. What is the best way to send patches then? By the way, any suggestions about the Chromium patch I linked to ( http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup )? It seems that it has somehow been fixed in fts3 code. I'm not yet very familiar with the SQLite codebase though, so could you point me to the fixes? ___ 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] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)
This bug comment describes the problem: http://code.google.com/p/chromium/issues/detail?id=15261#c20 excerpt: Apparently the problem is caused by tolower(), whose behavior is affected by current locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than 'i', because lower case of 'I' defined in tr_TR is 'ı' (U+0131). I think at the time the bug was being diagnosed, sqlite3_strnicmp() wasn't being exposed. I think that does the right thing because it uses the internal UpperToLower table. -scott On Fri, Aug 6, 2010 at 11:11 AM, Richard Hipp d...@sqlite.org wrote: If ch is an unsigned char then how is the following unsafe: ch = (ch0x80) ? tolower(ch) : ch And why does it need to be changed to ch = (ch='A' ch='Z') ? ch - 'A' + 'a' : ch; There is only one such instance of code remaining in FTS3 (at fts3_tokenizer1.c:196) but I want to understand what the issue is before I change it. On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr. phajdan...@chromium.orgwrote: On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. phajdan...@chromium.org wrote: I'm attaching a suggested patch to fix locale-unsafe usage of tolower in FTS code. The goal is to make Chromium closer to the upstream, so if you have a better solution, that's great. Oh, I have just noticed that the mailing list removes all attachments. What is the best way to send patches then? By the way, any suggestions about the Chromium patch I linked to ( http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup )? It seems that it has somehow been fixed in fts3 code. I'm not yet very familiar with the SQLite codebase though, so could you point me to the fixes? ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE %key%) searches, would FTS3 with suffix-tree tokenizer be the fast way?
On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts vieuxt...@gmail.com wrote: FTS3 only searches full terms/words by default, but I think if I built a custom tokenizer that returned all the suffix trees for a name: FTS3 can do prefix searches, MATCH 'a*'. Also, it aimed to support multiple hits at the same position, for stemming purposes. So you might be able to get away with making a copy of fts3_tokenizer1.c, and modifying it to keep an additional flag in the cursor to let you return each token twice (once reversed). I can't offhand think of how to distinguish the resulting prefix matches from suffix matches. Maybe you can work that out yourself by using the rows returned to figure it out. Also note that this will possibly interact poorly with the snippeting and offset functions. As a short-term proof-of-concept hack, you could just have two tables. Insert your originals into one table, then take last_insert_rowid() and insert the document reversed into the other table. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)
FTS3 updated here: http://www.sqlite.org/src/ci/b8b465ed2c On Fri, Aug 6, 2010 at 2:24 PM, Scott Hess sh...@google.com wrote: This bug comment describes the problem: http://code.google.com/p/chromium/issues/detail?id=15261#c20 excerpt: Apparently the problem is caused by tolower(), whose behavior is affected by current locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than 'i', because lower case of 'I' defined in tr_TR is 'ı' (U+0131). I think at the time the bug was being diagnosed, sqlite3_strnicmp() wasn't being exposed. I think that does the right thing because it uses the internal UpperToLower table. -scott On Fri, Aug 6, 2010 at 11:11 AM, Richard Hipp d...@sqlite.org wrote: If ch is an unsigned char then how is the following unsafe: ch = (ch0x80) ? tolower(ch) : ch And why does it need to be changed to ch = (ch='A' ch='Z') ? ch - 'A' + 'a' : ch; There is only one such instance of code remaining in FTS3 (at fts3_tokenizer1.c:196) but I want to understand what the issue is before I change it. On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr. phajdan...@chromium.orgwrote: On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. phajdan...@chromium.org wrote: I'm attaching a suggested patch to fix locale-unsafe usage of tolower in FTS code. The goal is to make Chromium closer to the upstream, so if you have a better solution, that's great. Oh, I have just noticed that the mailing list removes all attachments. What is the best way to send patches then? By the way, any suggestions about the Chromium patch I linked to ( http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup )? It seems that it has somehow been fixed in fts3 code. I'm not yet very familiar with the SQLite codebase though, so could you point me to the fixes? ___ 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 ___ 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] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)
On Fri, Aug 06, 2010 at 02:11:33PM -0400, Richard Hipp wrote: If ch is an unsigned char then how is the following unsafe: ch = (ch0x80) ? tolower(ch) : ch And why does it need to be changed to ch = (ch='A' ch='Z') ? ch - 'A' + 'a' : ch; There is only one such instance of code remaining in FTS3 (at fts3_tokenizer1.c:196) but I want to understand what the issue is before I change it. The problem is that libc's tolower() is locale-aware. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)
Yes. Pawel is wondering if he could patch fts1 and fts2. I don't think Chromium cares about fts1 (our version was patched for completeness), but I believe there are still places where fts2 is present because older databases might be using it. -scott On Fri, Aug 6, 2010 at 12:10 PM, Richard Hipp d...@sqlite.org wrote: FTS3 updated here: http://www.sqlite.org/src/ci/b8b465ed2c On Fri, Aug 6, 2010 at 2:24 PM, Scott Hess sh...@google.com wrote: This bug comment describes the problem: http://code.google.com/p/chromium/issues/detail?id=15261#c20 excerpt: Apparently the problem is caused by tolower(), whose behavior is affected by current locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than 'i', because lower case of 'I' defined in tr_TR is 'ı' (U+0131). I think at the time the bug was being diagnosed, sqlite3_strnicmp() wasn't being exposed. I think that does the right thing because it uses the internal UpperToLower table. -scott On Fri, Aug 6, 2010 at 11:11 AM, Richard Hipp d...@sqlite.org wrote: If ch is an unsigned char then how is the following unsafe: ch = (ch0x80) ? tolower(ch) : ch And why does it need to be changed to ch = (ch='A' ch='Z') ? ch - 'A' + 'a' : ch; There is only one such instance of code remaining in FTS3 (at fts3_tokenizer1.c:196) but I want to understand what the issue is before I change it. On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr. phajdan...@chromium.orgwrote: On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. phajdan...@chromium.org wrote: I'm attaching a suggested patch to fix locale-unsafe usage of tolower in FTS code. The goal is to make Chromium closer to the upstream, so if you have a better solution, that's great. Oh, I have just noticed that the mailing list removes all attachments. What is the best way to send patches then? By the way, any suggestions about the Chromium patch I linked to ( http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup )? It seems that it has somehow been fixed in fts3 code. I'm not yet very familiar with the SQLite codebase though, so could you point me to the fixes? ___ 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 ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi table select
Igor Tandetnik wrote: select table1.id, table2.date from table1 left join table2 on (table1.id = table2.id); Thanks, You beat me to it. And for those who don't have a clue what a left join is (this was me 2 days ago) It includes all of the entries from the left table (even if there are no entries on the right table), matched with the entries that are on the right table. -- View this message in context: http://old.nabble.com/Multi-table-select-tp29356401p29363582.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE %key%) searches, would FTS3 with suffix-tree tokenizer be the fast way?
On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess sh...@google.com wrote: On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts vieuxt...@gmail.com wrote: FTS3 only searches full terms/words by default, but I think if I built a custom tokenizer that returned all the suffix trees for a name: FTS3 can do prefix searches, MATCH 'a*'. Also, it aimed to support Prefix searches don't allow matching in the middle of words. For example, I want bert to match my name, roberts. So, I think I'd need to tokenize roberts as s, ts, ..., berts, oberts, ... etc. Then do a prefix match for bert* in order to see that roberts matches. Lucky, I don't need or care about any of the snippeting stuff, because I'm matching short strings (names). Cheers, Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE %key%) searches, would FTS3 with suffix-tree tokenizer be the fast way?
Have you not consider loading the whole rows into memory array and use simple string search or regexp? I'm sure 10,000 records could be search a blink. best regards, Radzi. On 6-Aug-2010, at 3:42 AM, Sam Roberts wrote: I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or sqlite expert, so I might be thinking about it all wrong. I have something like a (read-only) address book/rolodex, with interactive searching. As users type into the search box, I need to first know for each section how many rows match the substring typed so far. I only display the rows that are visible on screen. I have two queries: (A) I count the rows in a letter group. If they typed e: select substr(name,1,1), count(*) from my_table where name like '%e%' group by substr(name,1,1); A|94 B|118 C|131 ... This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows. Worse, when they type es, the search is as slow after they type s as when they typed e, even though the es rows are a sub-set of the rows that matched e. FTS3 only searches full terms/words by default, but I think if I built a custom tokenizer that returned all the suffix trees for a name: fu bar = [ r, ar, bar, bar, u bar, fu bar] That I could do rewrite query (A) like this: select substr(name,1,1), count(*) from my_table where name match 'e*' group by substr(name,1,1); Is this a reasonable approach? Is there a better way? Has somebody else done this? (B) I access specific rows within a letter group. For visible rows, I fetch them by offset into a letter group, so row 4 in the g section of names containing e would be: select * from my_table where name like g% and name like %e% order by name limit 1 offset 4; The performance for this is OK, right now, I think it's because the first LIKE can use the index, so the linear scan is over only a few hundred rows. Or it could be that the on-screen display of each row is slower than the DB search. I think it might become a problem, though. I'm not sure how I would rewrite this to use FTS3 if it turns out to be to slow for a larger DB, maybe a tokenizer that puts the first letter of the name as the first letter of every suffix? ___ 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] Substring (LIKE %key%) searches, would FTS3 with suffix-tree tokenizer be the fast way?
On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts vieuxt...@gmail.com wrote: On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess sh...@google.com wrote: On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts vieuxt...@gmail.com wrote: FTS3 only searches full terms/words by default, but I think if I built a custom tokenizer that returned all the suffix trees for a name: FTS3 can do prefix searches, MATCH 'a*'. Also, it aimed to support Prefix searches don't allow matching in the middle of words. For example, I want bert to match my name, roberts. Darn. Sorry, was only thinking with half my brain, and that half connected your problem up with some past idea. You're right, you'd need the tidbits to get at the interior substrings. That said, you should be able to pretty easily copy the current tokenizer and modify it to return multiple tokens at a single location. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users