You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <de...@db.apache.org> on 2005/07/26 23:58:18 UTC
[jira] Created: (DERBY-472) Full Text Indexing / Full Text Search
Full Text Indexing / Full Text Search
-------------------------------------
Key: DERBY-472
URL: http://issues.apache.org/jira/browse/DERBY-472
Project: Derby
Type: New Feature
Components: SQL
Versions: 10.0.2.0
Environment: All environments
Reporter: Rick Hillegas
Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
1) Do we also want to support text search on XML columns?
2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636662#action_12636662 ]
Rick Hillegas commented on DERBY-472:
-------------------------------------
Hi Suran,
I think that this would be a great contribution. It might be good to start out by defining what you mean by Lucene/Derby integration. Here are some possibilities. Full disclosure: it has been a long time since I looked at Lucene and I have not prototyped any of the possibilities listed below:
1) Be able to run Lucene queries against text stored in Derby.
I believe that this has been working for a long time. Lucene can index documents that are stored inside RDBMSes, including Derby. Documents are persisted in Derby and the customer application periodically tells Lucene to re-index those documents (say once a night). The application runs queries against the Lucene indexes and then retrieves the relevant documents from Derby when the customer wants to drill down.
If that's all you want to do, then you may just need to write a primer, explaining how a developer would set up a combined Derby/Lucene installation.
2) Be able to do (1) and join Lucene results against other Derby data.
I believe that you can do this today without modifying Lucene or Derby. You need to do something like the following:
a) Write triggers which update the Lucene indexes when the customer inserts or updates a document.
b) Write a table function which is handed a Lucene query. The table function returns the relevant document ids. Those ids can then be joined back to Derby tables to retrieve documents.
No doubt there are a lot of interesting issues involved with keeping Derby and Lucene in sync.
The table function might look like this:
create function luceneQuery
(
queryText varchar( 32672 ),
documentDomain varchar( 32672 ),
rankCutoff double
)
returns table
(
documentDomain varchar( 32672 ),
documentID int,
rank double
)
language java
parameter style DERBY_JDBC_RESULT_SET
contains sql
external name 'LuceneSupport.luceneQuery'
Then you could pose Derby queries like this:
select reports.id, reports.document, luceneResults.rank
from docSchema.reports,
table( luceneQuery( '+Apache +Derby -hat', 'docSchema.reports', 0.8 ) ) luceneResults
where reports.id = luceneResults.documentID
order by luceneResults.rank
If that's good enough, then your task might just be providing a template trigger and table function plus writing a whitepaper explaining how to wire the templates into an application.
3) Be able to do (2) with great performance.
It may be that (2) has some performance or scalability problems. Lucene or Derby might need to be enhanced to make (2) perform well.
4) Be able to do (3) easily.
A DBA might want help in setting up a combined Derby/Lucene installation. A DBA might want to backup and restore the Derby and Lucene databases together. Making this easily usable might require writing some tools and/or putting some hooks into Lucene and Derby.
5) Something else. What's on your mind?
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: https://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12682251#action_12682251 ]
Knut Anders Hatlen commented on DERBY-472:
------------------------------------------
I'm not aware of any documentation of the replication protocol, but my understanding is that it is just using ObjectOutput.writeObject()/ObjectInput.readObject() to transport ReplicationMessage objects (http://db.apache.org/derby/javadoc/engine/org/apache/derby/impl/store/replication/net/ReplicationMessage.html). The interesting messages are the ones where type=TYPE_LOG. Those messages contains the transaction log records, which have the same format as the files in the log directory in the database. The replication protocol doesn't know the meaning of the transaction log records, it just forwards the raw bytes to the recovery subsystem. I think the format of the transaction logs is also based on writeObject()/readObject(), so the easiest way to learn the protocol is probably to study the writeExternal() and readExternal() methods of the different log operation classes listed at the bottom of this page: http://db.apache.org/derby/papers/recovery.html (of course, you don't need to know the exact format if you use readObject()/writeObject() yourself).
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: https://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Dan Scott (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12498658 ]
Dan Scott commented on DERBY-472:
---------------------------------
Has anyone looked at this (old, now) patch for Lucene integration into Derby:
http://issues.apache.org/jira/browse/LUCENE-434
Full-text search would be really nice, and Lucene is clearly the way to go.
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: https://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-472?page=comments#action_12355899 ]
Rick Hillegas commented on DERBY-472:
-------------------------------------
The following wiki page tracks this evolving proposal: http://wiki.apache.org/db-derby/LuceneIntegration.
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: http://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12753211#action_12753211 ]
Rick Hillegas commented on DERBY-472:
-------------------------------------
A little more information on standards which apply to this work. The ANSI/ISO SQL standard does address full-text search in part 2 of the section titled SQL/MM (SQL Multimedia and Application Packages). The approach centers on a special full-text datatype, which is an ADT. The ADT has many methods defined on it, some of which return BOOLEAN values. SQL/MM is part of the 1999 version of the standard. I asked the ANSI SQL committee if anyone on the committee had implemented the spec. No-one replied. I think that means that no major vendor has implemented this part of the SQL spec.
You can get a copy of part 2 of SQL/MM here: http://www.wiscorp.com/SQLStandards.html
At this time, I don't recommend building Derby text search capabilities on top of the SQL standard--for these reasons:
1) I question the usefulness of a spec which has not been implemented by any major vendor.
2) Derby does not currently support ADTs.
3) Derby does not currently support a BOOLEAN datatype.
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: https://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "geoff hendrey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12681873#action_12681873 ]
geoff hendrey commented on DERBY-472:
-------------------------------------
as background on my interest in this, I just finished a three month project implementing search with lucene on 1,950,000 documents. It works fantastically, but requires careful local specific configuration.
Based on what I learned I would recommend a loose coupling in which a server can listen on the wire for tx logs like the ones shipped to the asynch replication server. The server simply receives the tx log and indexes the information in lucene. I could code the listener if you have docs on the replication protocol.
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: https://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by Manish Khettry <ma...@gmail.com>.
> > 2) Would there be work in the optimizer to get it to recognize that
> > there was an index that helped with the CONTAINS() function. Is this
> > the sort of work needed for DERBY-455 creation of indexes on expressions?
>
> Possibly.
>
> Dan.
>
I would think that each secondary access method would have a set of
functions that could be used to read from the index. Much like BTree
has <, <=, = and others, a text index would have CONTAINS (maybe
others).
The optimizer would have to know that CONTAINS is one of the functions
supported by the index and to consider the access path when
considering query plans.
Manish
Re: [jira] Commented: (DERBY-472) Full Text Indexing / Full Text
Search
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Mike Matrigali wrote:
> Dan, do you have a vision on how such an integration would work.
Not at any detailed level, I just believe it would *not* make sense for
the Derby project to re-invent text indexing and searching when there's
a well tested fully functioning Java text search engine as a Apache
project. I do remember I could get the another similar text engine
hacked into Cloudscape and supporting queries with ranking in a few
hours. If I had to invent text indexing as well it would have taken years.
> I understand using the libraries to process the CONTAINS function. You
> mentioned you tried some prototyping, did you put the actual index into
> an existing derby table or did you store the index separate?
In that prototype I just stored the index in separate files, no
transactional support.
>
> My questions are:
>
> 1) would actual index be stored in a derby style page container, which
> would somehow map the index structure that lucene expected? Does the
> lucene architecture map to a row locked multi-user environment?
http://wiki.apache.org/jakarta-lucene/LuceneFAQ#head-acc3c9bd1758a9794b93cd1a028cf95b0fddaf86
> 2) Would there be work in the optimizer to get it to recognize that
> there was an index that helped with the CONTAINS() function. Is this
> the sort of work needed for DERBY-455 creation of indexes on expressions?
Possibly.
Dan.
Re: [jira] Commented: (DERBY-472) Full Text Indexing / Full Text
Search
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Dan, do you have a vision on how such an integration would work. I
understand using the libraries to process the CONTAINS function. You
mentioned you tried some prototyping, did you put the actual index into
an existing derby table or did you store the index separate?
My questions are:
1) would actual index be stored in a derby style page container, which
would somehow map the index structure that lucene expected? Does the
lucene architecture map to a row locked multi-user environment?
2) Would there be work in the optimizer to get it to recognize that
there was an index that helped with the CONTAINS() function. Is this
the sort of work needed for DERBY-455 creation of indexes on expressions?
Daniel John Debrunner (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-472?page=comments#action_12316814 ]
>
> Daniel John Debrunner commented on DERBY-472:
> ---------------------------------------------
>
> Rather than invent text indexing, Derby should use the Apache text search library - Lucene.
>
> http://lucene.apache.org/
>
> Then you would have a text query language like Google, complete with language support etc.
>
> SELECT * FOM ARTICLES where CONTAINS('+Apache +Derby -hat') where rank > 0.8 order by rank
>
> or something like that.
>
>
>>Full Text Indexing / Full Text Search
>>-------------------------------------
>>
>> Key: DERBY-472
>> URL: http://issues.apache.org/jira/browse/DERBY-472
>> Project: Derby
>> Type: New Feature
>> Components: SQL
>> Versions: 10.0.2.0
>> Environment: All environments
>> Reporter: Rick Hillegas
>
>
>>Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
>>Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
>>1) Do we also want to support text search on XML columns?
>>2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
>>3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
>>4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
>
>
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-472?page=comments#action_12316814 ]
Daniel John Debrunner commented on DERBY-472:
---------------------------------------------
Rather than invent text indexing, Derby should use the Apache text search library - Lucene.
http://lucene.apache.org/
Then you would have a text query language like Google, complete with language support etc.
SELECT * FOM ARTICLES where CONTAINS('+Apache +Derby -hat') where rank > 0.8 order by rank
or something like that.
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: http://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636111#action_12636111 ]
Suran Jayathilaka commented on DERBY-472:
-----------------------------------------
Hi,
The postgraduate course I'm following requires us to complete a development/research project which will approximately span the duration of one year. Having had a wonderful experience with working with the Derby community for my Google Summer of Code project, I am considering taking up some feature addition to Derby as my degree project.
After discussing a bit with Kathey Marsden, I think this issue, Lucene integration for Derby for full text indexing / full text search might be a good issue to take up.
But I am not quite clear on the amount of workload/timeframes that might be required to see it to completion.
Therefor, I would be grateful if I could get some input as to how this task could be broken up into viable subtasks, and a brief idea of what they might involve.
Thanks.
Suran
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: https://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-472?page=all ]
Rick Hillegas updated DERBY-472:
--------------------------------
Comment: was deleted
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: http://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-472?page=comments#action_12355900 ]
Rick Hillegas commented on DERBY-472:
-------------------------------------
The following wiki page tracks this evolving proposal: http://wiki.apache.org/db-derby/LuceneIntegration.
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: http://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12680140#action_12680140 ]
Rick Hillegas commented on DERBY-472:
-------------------------------------
Geoff Hendrey describes benefits he'd like to see from a Lucene integration: http://www.nabble.com/vote-for-Lucene-integration!-td22407382.html#a22407382
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: https://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-472) Full Text Indexing / Full Text Search
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-472?page=comments#action_12330027 ]
Daniel John Debrunner commented on DERBY-472:
---------------------------------------------
I'm hoping the work I'm doing in DERBY-571 and beyond will provide the framework for integrating lucene into Derby.
With the query,
SELECT * FROM ARTICLES where CONTAINS(summary, '+Apache +Derby -hat') where rank > 0.8 order by rank
I'm thinking that the CONTAINS function would map to a vritual table (implementation of java..sql.ResultSet) that took the arguments and ran against the lucene index.
> Full Text Indexing / Full Text Search
> -------------------------------------
>
> Key: DERBY-472
> URL: http://issues.apache.org/jira/browse/DERBY-472
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.0
> Environment: All environments
> Reporter: Rick Hillegas
>
> Efficiently support full text search of string datatyped columns. Mag Gam raised this issue on the user's mailing list on 24 July 2005; the email thread is titled 'Full Text Indexing'. Mag wants to see something akin to the functionality in tsearch2 (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/). Dan points out that we may be able to re-use index building technology exposed by the apache Lucene project (http://lucene.apache.org/).
> Presumably we want to build inverted indexes on all string datatyped columns: CHAR, VARCHAR, LONG VARCHAR, CLOB,, and their national variants (when they are implemented). We should consider the following additional issues when specifying this feature:
> 1) Do we also want to support text search on XML columns?
> 2) Which human languages do we support initially? Each language has its own rules for lexing words and its own list of "noise" words which should not be indexed. Hopefully, we can plug-in some existing packages of lexers and noise filters. We should encourage users to donate additional lexers/fitlers.
> 3) The CREATE INDEX syntax (for these new inverted indexes) should let us bind a lexing human language to a string-datatyped column.
> 4) How do we express the search condition? For case-sensitive searches we can get away with boolean expressions built out of standard LIKE clauses. However, in my opinion, case-sensitive searches are an edge case. The more useful situation is a case-insensitive search. Can we get away with introducing a non-standard function here or do we need to push a proposal through the standards commitees? Even more useful and non-standard are fuzzy searches, which tolerate bad spellers.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira