You are viewing a plain text version of this content. The canonical link for it is here.
Posted to java-user@lucene.apache.org by David Spencer <da...@tropo.com> on 2005/02/07 09:07:44 UTC
Configurable indexing of an RDBMS, has it been done before?
Many times I've written ad-hoc code that pulls in data from an RDBMS and
builds a Lucene index. The use case is a typical database-driven dynamic
website which would be a hassle to spider (say, due to tricky
authentication).
I had a feeling this had been done in a general manner but didn't see
any code in the sandbox, nor did any searches turn it up.
I've spent a few mins thinking this thru - what I'd expect is to be able
to configure is:
1. JDBC Driver + conn params
2. Query to do a 1 time full index
3. Query to show new records
4. Query to show changed records
5. Query to show deleted records
6. Query columns to Lucene Field name mapping
7. "Type" of each field name (e.g. the equivalent of the args to the
Field ctr)
So a simple example, taking item 2 is
query: "select url, name, body from foo"
(now the column to field mapping)
col 1 => url
col 2 => title
col 3 => contents
(now the field types for each named field)
url => Field( ...store=true, index=false)
title => Field( ...store=true, index=true)
contents => Field( ...store=false, index=true)
And voilla, nice, elegant, data driven indexing.
Does it exist?
Should it? :)
PS
I know in the more general form, "query" needs to be replaced by
"queries" above, and the updated query may need some time stamp variable
expansion, and possibly the queries need "paging" to deal w/ lamo DBs
like mysql that don't have cursors for large result sets...
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Configurable indexing of an RDBMS, has it been done before?
Posted by Aad Nales <aa...@rotterdam-cs.com>.
Not sure that I get everything:
In the framework that we have built we use a 'simple' object mapping
that connects a database table with an object and implicetely with a
cache. It is build on top of JDBC.
The key fields of the database are used to create a DbKey element a
simple array of Object's. Through our mapping layer you can read and
write objects as atomic elements the layer takes care of stuff like
inheritance and delegation. (e.g. our object model has Items, Versions
and States, reading an item creates an ItemBean, a VersionBean with the
current version and a StateBean with the Version most current state).
The framework foresees in a number of different applications e.g. Forum,
News, Questionaires, Pages and then some each inherited from Item. What
we needed was an indexing approach that could do the following:
1. When an Page, Questionnaire, NewsItem etc. was updated this needed to
be reflected in the search results directly.
2. Every now and then (say once per night) a batch update was needed.
During the creation of our search solution we encountered a number of
issues:
1. Double results.
When you execute a search every hit counts but if a forum thread
consists of 200 items then 200 hits on reply's does not really add to
the users feeling of service. We added the concept of 'key' field to the
index. Double results are filtered out only the highest hit is displayed.
2. Delegate objects
A thread consists of messages. The content of each message should be in
the index. In order to solve this we create detailers. A detailer is
called with a DbKey and returns a set of Objects. Each individual object
is parsed (by calling the detailer again) based on the rules that are
defined in search.xml (see previous mail).
3. Batch Jobs
To optimize the index now and then we need to reindex the whole thing.
This is done by executing a query and getting the DbKey elements. (This
by the way is done in a so called DbMap as spare implementation a
HashMap where objects are only loaded into the cache when a getValue()
is called on it's entry). The query is called on the Item 'table'. The
parsing is done by calling the apprioriate detailers per Item type.
Now coming back to our discussion.
- The cache/mapping layer does not care much for the type of database
since it is build on JDBC and does not use any stored procedures or
constraints other than primary keys.
- Searches are executed on an object that assures that no reader or
writers are active.
- The result of a search is given back as Map. This way the uri that is
created as part of the result can be completely ignored if your
application so pleases.
Eriks suggestions:
Per field analyzers and wrappers are not a problem and could very easily
be added to this framework.
Creating an object as a result is possible i guess, but does this not
defeat the purpose of a search index somewhat? The information in the
index especially when set against a database are to present those fields
that are interesting to be searched.
The second part i don't quite 'get' is how would the 'dot' mapping work
"company.president.name" for instance? I can see it writing to the index
but not creating object returning from a call? Or would this simply be a
key field that is then used as part of query? Using it to navigate an
object structure is quite feasible especially if you would create a key.
E.g. I would store a key in lucene called: "company.role.person" and a
related field with the csv values "XYZ, VP, Jenssen". Then if the
company 'object' can be derived from so kind of persistent object the
result of the query would be:
persistentObject.getCompany("XYZ").getRole("VP").getPerson("Jenssen");
The stuff we build so far would be able to cope with something like that
i guess, although quite some elements would still be missing. Using
Lucene this way more or less creates a 'unified' index.
Also: I have not been able to look a Squirrel.
Cheers,
Aad
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Configurable indexing of an RDBMS, has it been done before?
Posted by Erik Hatcher <er...@ehatchersolutions.com>.
I agree that it is a worthwhile contribution.
Some suggestions... allow the configuration to specify field boost
values, and analyzer(s). If analyzers are specified per-field, then
wrap then automatically with a PerFieldAnalyzerWrapper. Also, having a
facility to aggregate fields into a "contents"-like field would be nice
- though maybe this would be covered implicitly as part of the SQL
mapping with one of the columns being an aggregate column.
Perhaps the configuration aspect of it (XML mapping of expressions to
field details) could be generalized to work with an object graph as
well as SQL result sets. OGNL (www.ognl.org) makes expression language
glue and I can see it being used for mappings - for example the "name"
field could be mapped to "company.president.name", where "company" is
an object (or Map) with a "president" property, and so on.
Erik
On Feb 8, 2005, at 2:42 AM, Aad Nales wrote:
> If that is a general thought then I will plan for some time to put
> this in action.
>
> Cheers,
> Aad
>
>
> David Spencer wrote:
>
>> Nice, very similar to what I was thinking of, where the most
>> significant difference is probably just that I was thinking of a
>> batch indexer, not one embedded in a web container. Probably a
>> worthwhile contribution to the sandbox.
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: lucene-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Configurable indexing of an RDBMS, has it been done before?
Posted by Aad Nales <aa...@rotterdam-cs.com>.
If that is a general thought then I will plan for some time to put this
in action.
Cheers,
Aad
David Spencer wrote:
> Nice, very similar to what I was thinking of, where the most
> significant difference is probably just that I was thinking of a batch
> indexer, not one embedded in a web container. Probably a worthwhile
> contribution to the sandbox.
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Configurable indexing of an RDBMS, has it been done before?
Posted by David Spencer <da...@tropo.com>.
Nice, very similar to what I was thinking of, where the most significant
difference is probably just that I was thinking of a batch indexer, not
one embedded in a web container. Probably a worthwhile contribution to
the sandbox.
Aad Nales wrote:
> Yep,
>
> This is how we do it.
>
> We have a search.xml that maps database fields to search fields and a
> parameter part that describes the 'click for detailed result url' and
> the parameter names (based on the search fields). In this xml we also
> describe how the different fields should be stored we have for instance
> a number of large text fields for we use the unstored option.
>
> The framework that we have build around has an element that we call
> detailer. This detailer creates a lucene Document with the fields as
> specified in the search.xml
>
> To illustrate here is the code that specifies the detailer for a forum.
>
>
> -------------------------------------------------- XML
> ---------------------
> <documenttype id="FORUM" index="general" defaultfield="body">
> <fields>
> <field property="messageid" searchfield="messageid" type="unindexed"
> key="true"/>
> <field property="instanceid" searchfield="instanceid" type="unindexed" />
> <field property="subject" searchfield="title" type="split" maxwords="8" />
> <field property="body" searchfield="default" type="split" maxwords="20" />
> <field property="aka_username" searchfield="username" type="keyword" />
> <field property="modifiedDateAsDate" searchfield="modifieddate"
> type="keyword" />
> </fields>
> <action uri="/forum/viewMessage.do"
> image="/htmlarea/images/cops_insert_threadlink.gif">
> <parameter property="messageid" name="messageid"/>
> <parameter property="instanceid" name="instanceid"/>
> </action>
> <analyzer
> classname="org.apache.lucene.analysis.standard.StandardAnalyzer"/>
> </documenttype>
> -------------------------------- END XML -------------------
>
> Please note:
>
> Messageid is the keyfield here when we search the index we use a
> combined TYPE + KEY id to filter out double hits on the same document
> (not unusual in for instance a long forum thread).
>
> Per type of document we also specifify what picture to show in the
> result (image), and we specify in what index the result should be
> written and what the general search field is (if the user submits a
> query without search all and without a field specified).
>
> We have added the 'split' key word which makes it possbile to search a
> long text but only store a bit in the resulting hit.
>
> The reindex is pretty straightforward we build a series of detailers for
> all possible document types and we run through the database and call the
> right detailer from a HashMap.
>
> We have not included the JDBC stuff since the application is always
> running in Tomcat-Struts and since we cache most of the database reads.
> (a completely differnt story).
>
> Queries on new and changed records seem to only make sense if asked in a
> context of time. (Right?). We have not needed it yet. The mapping can be
> query from a singleton java class. (SearchConfiguration).
>
> We are currently adding functionality to store 'user structured data'
> best imagined as user defined input forms that are described in XML and
> are then stored as XML in the database. We query these documents using
> Lucene. These documents end up in the same index but this is quite
> manageable by using specialized detailers. For these document the type
> is more important then for the 'normally' stored documents. For this
> latter situation the search logic assumes that the query is
> appropriately configured by the application.
>
> I am not sure if this is the kind of solution that you are looking for,
> but everything we produce is 100% open source.
>
> Cheers,
> Aad
>
>
> David Spencer wrote:
>
>> Many times I've written ad-hoc code that pulls in data from an RDBMS
>> and builds a Lucene index. The use case is a typical database-driven
>> dynamic website which would be a hassle to spider (say, due to tricky
>> authentication).
>>
>> I had a feeling this had been done in a general manner but didn't see
>> any code in the sandbox, nor did any searches turn it up.
>>
>> I've spent a few mins thinking this thru - what I'd expect is to be
>> able to configure is:
>>
>> 1. JDBC Driver + conn params
>> 2. Query to do a 1 time full index
>> 3. Query to show new records
>> 4. Query to show changed records
>> 5. Query to show deleted records
>> 6. Query columns to Lucene Field name mapping
>> 7. "Type" of each field name (e.g. the equivalent of the args to the
>> Field ctr)
>>
>> So a simple example, taking item 2 is
>>
>> query: "select url, name, body from foo"
>>
>> (now the column to field mapping)
>> col 1 => url
>> col 2 => title
>> col 3 => contents
>>
>> (now the field types for each named field)
>>
>> url => Field( ...store=true, index=false)
>> title => Field( ...store=true, index=true)
>> contents => Field( ...store=false, index=true)
>>
>>
>>
>> And voilla, nice, elegant, data driven indexing.
>> Does it exist?
>> Should it? :)
>>
>> PS
>> I know in the more general form, "query" needs to be replaced by
>> "queries" above, and the updated query may need some time stamp
>> variable expansion, and possibly the queries need "paging" to deal w/
>> lamo DBs like mysql that don't have cursors for large result sets...
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: lucene-user-help@jakarta.apache.org
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: lucene-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Configurable indexing of an RDBMS, has it been done before?
Posted by Aad Nales <aa...@rotterdam-cs.com>.
Yep,
This is how we do it.
We have a search.xml that maps database fields to search fields and a
parameter part that describes the 'click for detailed result url' and
the parameter names (based on the search fields). In this xml we also
describe how the different fields should be stored we have for instance
a number of large text fields for we use the unstored option.
The framework that we have build around has an element that we call
detailer. This detailer creates a lucene Document with the fields as
specified in the search.xml
To illustrate here is the code that specifies the detailer for a forum.
-------------------------------------------------- XML ---------------------
<documenttype id="FORUM" index="general" defaultfield="body">
<fields>
<field property="messageid" searchfield="messageid" type="unindexed"
key="true"/>
<field property="instanceid" searchfield="instanceid" type="unindexed" />
<field property="subject" searchfield="title" type="split" maxwords="8" />
<field property="body" searchfield="default" type="split" maxwords="20" />
<field property="aka_username" searchfield="username" type="keyword" />
<field property="modifiedDateAsDate" searchfield="modifieddate"
type="keyword" />
</fields>
<action uri="/forum/viewMessage.do"
image="/htmlarea/images/cops_insert_threadlink.gif">
<parameter property="messageid" name="messageid"/>
<parameter property="instanceid" name="instanceid"/>
</action>
<analyzer
classname="org.apache.lucene.analysis.standard.StandardAnalyzer"/>
</documenttype>
-------------------------------- END XML -------------------
Please note:
Messageid is the keyfield here when we search the index we use a
combined TYPE + KEY id to filter out double hits on the same document
(not unusual in for instance a long forum thread).
Per type of document we also specifify what picture to show in the
result (image), and we specify in what index the result should be
written and what the general search field is (if the user submits a
query without search all and without a field specified).
We have added the 'split' key word which makes it possbile to search a
long text but only store a bit in the resulting hit.
The reindex is pretty straightforward we build a series of detailers for
all possible document types and we run through the database and call the
right detailer from a HashMap.
We have not included the JDBC stuff since the application is always
running in Tomcat-Struts and since we cache most of the database reads.
(a completely differnt story).
Queries on new and changed records seem to only make sense if asked in a
context of time. (Right?). We have not needed it yet. The mapping can be
query from a singleton java class. (SearchConfiguration).
We are currently adding functionality to store 'user structured data'
best imagined as user defined input forms that are described in XML and
are then stored as XML in the database. We query these documents using
Lucene. These documents end up in the same index but this is quite
manageable by using specialized detailers. For these document the type
is more important then for the 'normally' stored documents. For this
latter situation the search logic assumes that the query is
appropriately configured by the application.
I am not sure if this is the kind of solution that you are looking for,
but everything we produce is 100% open source.
Cheers,
Aad
David Spencer wrote:
> Many times I've written ad-hoc code that pulls in data from an RDBMS
> and builds a Lucene index. The use case is a typical database-driven
> dynamic website which would be a hassle to spider (say, due to tricky
> authentication).
>
> I had a feeling this had been done in a general manner but didn't see
> any code in the sandbox, nor did any searches turn it up.
>
> I've spent a few mins thinking this thru - what I'd expect is to be
> able to configure is:
>
> 1. JDBC Driver + conn params
> 2. Query to do a 1 time full index
> 3. Query to show new records
> 4. Query to show changed records
> 5. Query to show deleted records
> 6. Query columns to Lucene Field name mapping
> 7. "Type" of each field name (e.g. the equivalent of the args to the
> Field ctr)
>
> So a simple example, taking item 2 is
>
> query: "select url, name, body from foo"
>
> (now the column to field mapping)
> col 1 => url
> col 2 => title
> col 3 => contents
>
> (now the field types for each named field)
>
> url => Field( ...store=true, index=false)
> title => Field( ...store=true, index=true)
> contents => Field( ...store=false, index=true)
>
>
>
> And voilla, nice, elegant, data driven indexing.
> Does it exist?
> Should it? :)
>
> PS
> I know in the more general form, "query" needs to be replaced by
> "queries" above, and the updated query may need some time stamp
> variable expansion, and possibly the queries need "paging" to deal w/
> lamo DBs like mysql that don't have cursors for large result sets...
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: lucene-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org