db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-590) How to integrate Derby with Lucene API?
Date Tue, 18 Feb 2014 00:25:20 GMT

     [ https://issues.apache.org/jira/browse/DERBY-590?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Rick Hillegas updated DERBY-590:

    Attachment: derby-590-01-ag-publicAccessToLuceneRoutines.diff

Attaching derby-590-01-ag-publicAccessToLuceneRoutines.diff. This patch builds on Andrew's
approach, wiring Lucene support into Derby's SQL authorization scheme. If this approach looks
promising, I will write additional regression tests to verify the claims I make about this

Before discussing what this patch does, let me recap what it does NOT do:

1) It does NOT encrypt Lucene indexes if the database is encrypted.

2) It does NOT work with in-memory databases.

3) It does NOT work with backup/restore.

In addition, I have changed the meaning of the updateIndex() procedure and lost the very useful
meaning which it used to have. There are at least two important application profiles which
have different needs from an index-updating procedure:

i) Update-intensive: These applications do not want to incur the performance hit of re-indexing
a document every time it changes. These applications may work best with a bulk-reindexing
cron job which executes when user activity is low. This is the use-case supported by the revised
updateIndex() procedure: the Lucene index is dropped and completely recreated although schema
objects connected to it are not bounced.

ii) Read-intensive: These applications can incur the performance hit of re-indexing a document
every time it changes. These applications are well served by the trigger-driven usage of updateIndex()
provided in Andrew's original patch.

I think that it may be possible to recover Andrew's original functionality. But this requires
more familiarity with Lucene than I can claim. Maybe Andrew can propose some ideas and we
can converge on a solution.

So much for the caveats. Here are the changes made by the new patch:

A) The layout of the Lucene support directory is changed. There is now a subdirectory for
each schema which has indexed tables in it. Under that directory, there is a subdirectory
for each table which has indexed columns. Under the table-specific directory, there is a subdirectory
for each text column which is indexed. Those are the leaf directories which contain the actual
Lucene files. So the directory structure looks like this:


B) The luceneQuery() table function has been changed to be context aware. See the work recently
done on https://issues.apache.org/jira/browse/DERBY-6117. Users no longer invoke luceneQuery()
directly. Instead, for each indexed column, we create a table function specific to that column.
The table function has the name $schemaName.$tableName__$columnName. This is the crucial change
which allows us to wire Lucene support up to Derby's SQL authorization. The schema owner can
grant EXECUTE privilege on the column-specific table function. In addition, the underlying
context-aware luceneQuery() machinery starts out by issuing a select against the text column
and the key columns of the table. This ensures that users must enjoy SELECT privilege on all
of those columns in order to run the column-specific table function.

C) A text column can be indexed only if the table has a primary key. The primary key can have
multiple columns in it and the columns can be any indexable datatype. The column-specific
table function returns a data set which includes the whole primary key plus the Lucene document
id plus the rank number (the relevance of that document according to Lucene's calculations).
This makes it easy to join the table function to the original table in order to obtain more
extensive results.

D) The arguments to the Lucene support procedures are now case-insensitive SQL identifiers
rather than case-sensitive strings. This is a departure from the convention followed by most
Derby system procedures, but I think the departure is welcome and long overdue.

E) The Lucene support has been moved out of the tools jar and into the engine jar. This fixes
the big surprise I recorded on https://issues.apache.org/jira/browse/DERBY-6470. This change
also made it possible to use IdUtil in order to implement the semantics described above in

F) It is assumed that the Lucene jars will be checked into the Derby source tree and that
the Lucene support classes will always be built. However, there is still no plan to include
Lucene jars in Derby binary distributions. Users who want to enable the optional Lucene support
will have to install the Lucene jars themselves.

G) In general, the following convention has been followed for each supported operation: Transactional
writes are performed before any calls are made to Lucene. This means that if the Lucene calls
raise an error, then the transactional writes are rolled back. I think that following a consistent
convention like this will make it easier for users to reason about how Lucene support behaves.

H) Miscellaneous improvements have been made in the areas of code factoring and integration
with the Java security manager.

Here's how you use the revised tool:

Let's say that you have a table with this shape...

create table lucenetest.titles
    ID int generated always as identity primary key,
    ISBN varchar(16),
    PRINTISBN varchar(16),
    title varchar(1024),
    subtitle varchar(1024),
    author varchar(1024),
    series varchar(1024),
    publisher varchar(1024),
    collections varchar(128),
    collections2 varchar(128)

The DBO loads the Lucene support tool as follows...

call syscs_util.syscs_register_tool( 'luceneSupport', true );

This creates the LuceneSupport schema, containing the following procedures and functions...

create procedure LuceneSupport.createIndex
    schemaname varchar( 128 ),
    tablename varchar( 128 ),
    textcolumn varchar( 128 )
parameter style java modifies sql data language java
external name 'org.apache.derby.impl.optional.lucene.LuceneSupport.createIndex';

create procedure LuceneSupport.dropIndex
    schemaname varchar( 128 ),
	tablename varchar( 128 ),
	textcolumn varchar( 128 )
parameter style java modifies sql data language java
external name 'org.apache.derby.impl.optional.lucene.LuceneSupport.dropIndex';

create procedure LuceneSupport.updateIndex
    schemaname varchar( 128 ),
	tablename varchar( 128 ),
	textcolumn varchar( 128 )
parameter style java reads sql data language java
external name 'org.apache.derby.impl.optional.lucene.LuceneSupport.updateIndex';

create function LuceneSupport.listIndexes() returns table
    id int,
	schemaname char( 128 ),
	tablename char( 128 ),
	columnname char( 128 ),
	lastupdated timestamp
language java parameter style DERBY_JDBC_RESULT_SET contains sql
external name 'org.apache.derby.impl.optional.lucene.LuceneSupport.listIndexes'

The LUCENETEST user can then index a text column as follows. The DBO can do this too. However,
no-one else can index the column. That is because the createIndex() procedure attempts to
create a table function in the lucenetest schema. Other users do not enjoy that privilege...

call LuceneSupport.createIndex( 'lucenetest', 'titles', 'title' );

...which creates the following directory structure...


...and the following column-specific table function:

create function lucenetest.titles__title( query varchar( 32672 ), rankCutoff double )
returns table
    ID int,
    documentID int,
	rank double
language java parameter style derby_jdbc_result_set contains sql
external name 'org.apache.derby.impl.optional.lucene.LuceneSupport.luceneQuery';

The LUCENETEST user can then query the Lucene index and join it to the original table as follows...

select title, author, publisher, documentID, rank
from lucenetest.titles t, table ( lucenetest.titles__title( 'grapes', 0 ) ) l
where t.id = l.id;

The LUCENETEST user (or the DBO but no-one else) can drop the Lucene index as follows...

call LuceneSupport.dropIndex( 'lucenetest', 'titles','title' );

...which drops the lucene/LUCENETEST/TITLES/TITLE directory. Directory deletion cascades up.
That is, if there are no more indexed columns in lucenetest.titles, then we also drop lucene/LUCENETEST/TITLES.
And we drop lucene/LUCENETEST if there are no more indexed tables in the lucenetest schema.

The DBO (and only the DBO) can unload Lucene support as follows...

call syscs_util.syscs_register_tool( 'luceneSupport', false );

...which drops all procedures and table functions created by the tool. This command also drops
the lucene subdirectory and everything under it.

Touches the following files:


M       java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java

Adds the Lucene support package to the list of Derby packages whose entry points can be bound
to user-defined routines.


A       java/engine/org/apache/derby/impl/optional
A       java/engine/org/apache/derby/impl/optional/lucene
A       java/engine/org/apache/derby/impl/optional/lucene/LuceneQueryVTI.java
A       java/engine/org/apache/derby/impl/optional/lucene/LuceneSupport.java
A       java/engine/org/apache/derby/impl/optional/lucene/LuceneListIndexesVTI.java
A       java/engine/org/apache/derby/impl/optional/lucene/build.xml
M       java/engine/org/apache/derby/impl/build.xml
M       java/engine/org/apache/derby/catalog/Java5SystemProcedures.java

The Lucene support optional tool.


M       java/engine/org/apache/derby/vti/VTITemplate.java

Some additional support for context-aware table functions. This support allows the function
to query the JDBC metadata for the shape of the ResultSet it returns.


M       java/engine/org/apache/derby/loc/messages.xml
M       java/shared/org/apache/derby/shared/common/reference/SQLState.java

New error messages for Lucene support.


M       build.xml
A       tools/java/lucene-analyzers-common-4.5.0.jar
A       tools/java/lucene-queryparser-4.5.0.jar
A       tools/java/lucene-core-4.5.0.jar
M       tools/ant/properties/extrapath.properties
M       tools/jar/extraDBMSclasses.properties

Miscellaneous build machinery.


A       tools/release/notices/lucene.txt

The Lucene notice file which must be included in the Derby NOTICE file if we are to ship the
lucene jars in Derby source distributions.


M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/LuceneSupportTest.java

Initial tests.

> How to integrate Derby with Lucene API?
> ---------------------------------------
>                 Key: DERBY-590
>                 URL: https://issues.apache.org/jira/browse/DERBY-590
>             Project: Derby
>          Issue Type: Improvement
>          Components: Documentation, SQL
>            Reporter: Abhijeet Mahesh
>              Labels: derby_triage10_11
>         Attachments: derby-590-01-ag-publicAccessToLuceneRoutines.diff, lucene_demo.diff,
> In order to use derby with lucene API what should be the steps to be taken? 

This message was sent by Atlassian JIRA

View raw message