Re: [sqlite] Does SQLite have a fulltext search like MySQL?
On 7/11/05, Henry Miller [EMAIL PROTECTED] wrote: For full text search I find the lucene/clucene http://lucene.apache.org/ http://sourceforge.net/projects/clucene/ to be good solutions. The licenses isn't quite as easy as sqlite, but they should be good enough for everyone. Clucene is actually written in C++. There is another project lucene4C - http://incubator.apache.org/lucene4c/ - but this too has transitioned from a completely C implementation to a wrapper over a gcj compiled java version. Using either of these implementations as a reference, it is possible to use lucene to provide all the services mentioned in this mail inside the DB itself http://www.mail-archive.com/sqlite-users@sqlite.org/msg00993.html Lucene could be used to build the column index and this index can be queried later using extensions to SQL like CONTAINS. I would like to contribute / test code for this - but before that I'd like to know if there is a good case for doing this. /Siddharth
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
On Tue, Jul 12, 2005 at 01:26:19PM +0530, Siddharth Vijayakrishnan wrote: On 7/11/05, Henry Miller [EMAIL PROTECTED] wrote: For full text search I find the lucene/clucene http://lucene.apache.org/ http://sourceforge.net/projects/clucene/ to be good solutions. The licenses isn't quite as easy as sqlite, but they should be good enough for everyone. There is also swish-e, http://swish-e.org, which is written in C. Afaict it is faster in searching and building the fulltext index than mysql, but has some minor problems with index updates. lothar
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
On 7/5/2005 at 17:48 Michael Grice wrote: If not, are there plans to add this? I'm just a NOOB, looking to see what all SQLite can do for me, before I go too far down this road. I was on vacation last week... For full text search I find the lucene/clucene http://lucene.apache.org/ http://sourceforge.net/projects/clucene/ to be good solutions. The licenses isn't quite as easy as sqlite, but they should be good enough for everyone. They don't do sql, but most people who want full text search want a google like interface, which is easy (almost trivial) to provide. For structured data sqlite is much better. For unstructured text lucene is the best I have found.
RE: [sqlite] Does SQLite have a fulltext search like MySQL?
SQLite doesn't have a free text search capability - the Like and Glob functions are not free text just simple pattern matching on the scanned text. Performance is very poor because there is no suitable index. It's not a simple task to create free text searching - you have to create an inverted index for every (or all) columns you want to search on, then you have to find a mechanism to combine that in the SQL parser. The inverted index means creating a grammar parser and a language specific stemmer to produce terms with their associated proximity info. The same parser/stemmer is then used to produce your search terms from the SQL statement. I've been creating such a beast for a while with some nice results. The project stems from an idea of creating published DVD searchable databases of library content, but I haven't worked on it for ages. I was hoping to get it to a point where I could offer it up to the SQLite community as a side project for development as an add-in to the core code. At the moment, it's got a VB front end to a VS6.0 DLL but the idea is to make it an extension of the sqlite3.exe. Is there anyone out there that would like to get involved in this? If there is, I'll happily provide a precise of how it works, what's been done and what there is still to do. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Jim Dodgen Sent: 06 July 2005 03:48 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Does SQLite have a fulltext search like MySQL? look at the glob command it works like like except uses the unix file name globbing style of matching not quite a grep select * from table where a glob '[abc]foobar*'; At 06:47 PM 7/5/2005, you wrote: like this? select * from table where a like '%abc%'; SQLite also makes it easy to write your own functions. That way you can define other matching algorithms (eg Jaro-Winkler). Roger
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
On 7/6/05, Steve O'Hara [EMAIL PROTECTED] wrote: SQLite doesn't have a free text search capability - the Like and Glob functions are not free text just simple pattern matching on the scanned text. Performance is very poor because there is no suitable index. It's not a simple task to create free text searching - you have to create an inverted index for every (or all) columns you want to search on, then you have to find a mechanism to combine that in the SQL parser. The inverted index means creating a grammar parser and a language specific stemmer to produce terms with their associated proximity info. The same parser/stemmer is then used to produce your search terms from the SQL statement. I've been creating such a beast for a while with some nice results. The project stems from an idea of creating published DVD searchable databases of library content, but I haven't worked on it for ages. I was hoping to get it to a point where I could offer it up to the SQLite community as a side project for development as an add-in to the core code. At the moment, it's got a VB front end to a VS6.0 DLL but the idea is to make it an extension of the sqlite3.exe. Is there anyone out there that would like to get involved in this? If there is, I'll happily provide a precise of how it works, what's been done and what there is still to do. What is the dll written in? It might be easy to port to C as source others can include in their programs as a user defined function.
RE: [sqlite] Does SQLite have a fulltext search like MySQL?
It's written in C already... MS VS 6 Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Jay Sprenkle Sent: 06 July 2005 16:31 To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] Does SQLite have a fulltext search like MySQL? On 7/6/05, Steve O'Hara [EMAIL PROTECTED] wrote: SQLite doesn't have a free text search capability - the Like and Glob functions are not free text just simple pattern matching on the scanned text. Performance is very poor because there is no suitable index. It's not a simple task to create free text searching - you have to create an inverted index for every (or all) columns you want to search on, then you have to find a mechanism to combine that in the SQL parser. The inverted index means creating a grammar parser and a language specific stemmer to produce terms with their associated proximity info. The same parser/stemmer is then used to produce your search terms from the SQL statement. I've been creating such a beast for a while with some nice results. The project stems from an idea of creating published DVD searchable databases of library content, but I haven't worked on it for ages. I was hoping to get it to a point where I could offer it up to the SQLite community as a side project for development as an add-in to the core code. At the moment, it's got a VB front end to a VS6.0 DLL but the idea is to make it an extension of the sqlite3.exe. Is there anyone out there that would like to get involved in this? If there is, I'll happily provide a precise of how it works, what's been done and what there is still to do. What is the dll written in? It might be easy to port to C as source others can include in their programs as a user defined function.
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
On 5 Jul 2005, at 17:48, Michael Grice wrote: If not, are there plans to add this? What language are you planning to use? Perl has a bunch of full text search modules that implement FTS on top of any DB. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] Does SQLite have a fulltext search like MySQL?
If not, are there plans to add this? I'm just a NOOB, looking to see what all SQLite can do for me, before I go too far down this road. Thx.
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
On Tuesday 05 July 2005 04:48 pm, Michael Grice wrote: If not, are there plans to add this? I'm just a NOOB, looking to see what all SQLite can do for me, before I go too far down this road. Thx. like this? select * from table where a like '%abc%';
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
From: Stephen Leaf [EMAIL PROTECTED] Organization: SMiLeaf To: sqlite-users@sqlite.org Date: Tue, 5 Jul 2005 18:06:39 -0500 Subject: Re: [sqlite] Does SQLite have a fulltext search like MySQL? On Tuesday 05 July 2005 04:48 pm, Michael Grice wrote: If not, are there plans to add this? I'm just a NOOB, looking to see what all SQLite can do for me, before I go too far down this road. Thx. like this? select * from table where a like '%abc%'; SQLite does not support full-text searches. This has been discussed on the list before. I don't know of any plans to add it, but maybe someone more familiar with develop- ment plans can give you a better answer; or you could search the archives: http://www.mail-archive.com/sqlite-users@sqlite.org/ Regards
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
like this? select * from table where a like '%abc%'; SQLite also makes it easy to write your own functions. That way you can define other matching algorithms (eg Jaro-Winkler). Roger
Re: [sqlite] Does SQLite have a fulltext search like MySQL?
look at the glob command it works like like except uses the unix file name globbing style of matching not quite a grep select * from table where a glob '[abc]foobar*'; At 06:47 PM 7/5/2005, you wrote: like this? select * from table where a like '%abc%'; SQLite also makes it easy to write your own functions. That way you can define other matching algorithms (eg Jaro-Winkler). Roger
