Indexing and searching complex data types

[Tim Dudgeon <td...@informaticsmatters.com>]

I'm wondering if its possible to index and search complex data types in 
Derby. Something along the lines of Oracle cartridge:
http://wiki.oracle.com/page/Data+Cartridge

Scenaio would be that I have a complex blob of data (e.g. could be 
stored in a BLOB, CLOB, VARCHAR column) and wand to be able to create 
custom index type and allow it to be searched. I know how to decompose 
the object into something that can be searched using Java code.

I see various things that are somewhat along these lines, but no obvious 
solution:

User defined types: but can't be indexed
Derby-style table functions: but unclear how these could be used
Java Functions: but unclear how these can be used in a where clause, and 
can't take BLOB, CLOB params.

I've also seen some discussion on mailing lists on spatial functions, 
but nothing that really helps me.

Thanks
Tim

Re: Indexing and searching complex data types

[Tim Dudgeon <td...@informaticsmatters.com>]

On 20/03/2011 15:38, Bryan Pendleton wrote:
> Perhaps the Derby generated columns feature would be of help.
>
> You could write some code that would generate the search values
> by processing your blob of data, then build an index on the
> generated search terms.

Hi Bryan, thanks for the response.
That would be a solution to one aspect, but I'm not sure it helps me 
search as the search would not be simple "select ... from ... where ..." 
query using the generated columns, as Java code is also needed as part 
of the search algorithm.
The oracle cartridge based solution is to allow a function to be defined 
that can be used in the where part of the expression. Something like this:

select ... from .. where my_search_op(indexed_col, criteria ...) = 1

The my_search_op() function performs the search (using the generated 
custom index) and in effect works out which rows match (returned value 
or 1) and which rows don't match (returned value of 0).

I'm imagining this is a very non-standard need and probably not 
possible. But I can hope.

Tim

Re: Indexing and searching complex data types

[Bryan Pendleton <bp...@gmail.com>]

Perhaps the Derby generated columns feature would be of help.

You could write some code that would generate the search values
by processing your blob of data, then build an index on the
generated search terms.

Knut Anders has a nice writeup of generated columns here:
http://blogs.sun.com/kah/entry/derby_10_5_preview_generated

And you can find more reference material about generated columns
in the Derby docs at http://db.apache.org/derby/docs/10.7/
(which seem to be down at the moment, otherwise I'd give you
a better link as the reference, but look in the reference
guide for the CREATE TABLE column definition pages.

thanks,

bryan