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 wrote: > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess wrote: >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts wrote: >>> FTS3 only searches full terms/words by default, but I think if I built a

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

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 wrote: > On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts 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: > >

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

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

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 = (ch<0x80) ? 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

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 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

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 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

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 >

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 = (ch<0x80) ? 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

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

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. 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

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 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 >

[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

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 wrote: > Paul Sanderson 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

Re: [sqlite] copying only new rows from one table to another

Paul Sanderson 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

[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

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 : > I'm only a heavy user (!= programmer) so if you have an extension for

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

Re: [sqlite] Slowdown when adding terms to query

Edward Hawke 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

Re: [sqlite] Slowdown when adding terms to query

On Fri, Aug 6, 2010 at 5:23 AM, Edward Hawke wrote: > > 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

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

[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

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

[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 inclusion. Patch attached to fix this problem. Thanks, Alan. ___ sqlite-users mailing list

Re: [sqlite] sqlite3-dbf - converter of XBase / FoxPro tables to SQLite

Alexey Pechnikov 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] 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