Creating an index

The luceneSupport optional tool lets you use Apache Lucene to perform full-text indexing and searching of the contents of Derby text columns.

After the luceneSupport tool has been loaded, a user can index a text column in a table or view which that user owns. If SQL authorization is enabled, then the database owner is the only account which can index a text column in another user's table. The following procedure makes this possible:

LUCENESUPPORT.CREATEINDEX
(
    SCHEMANAME VARCHAR( 128 ),
    TABLENAME VARCHAR( 128 ),
    TEXTCOLUMN VARCHAR( 128 ),
    INDEXDESCRIPTORMAKER VARCHAR( 32672 ),
    KEYCOLUMNS VARCHAR( 32672 ) ...
)

The procedure parameters are as follows:

The keys and the text column cannot have the following names:

CREATEINDEX creates a table function named $TABLENAME__$TEXTCOLUMN in the $SCHEMANAME schema. Querying an index describes this table function in greater detail.

Example

-- index the POEMTEXT column of the POEMS table,
-- using its primary key and the default IndexDescriptor maker
CALL LUCENESUPPORT.CREATEINDEX( 'ruth', 'poems', 'poemText', null );

-- index the POEMVIEW view, using POEMID and VERSIONSTAMP as keys
-- and a custom IndexDescriptor
CALL LUCENESUPPORT.CREATEINDEX
(
    'ruth', 'poemView', 'poemText',
    'myapp.MyIndexDescriptor.makeMe',
    'poemID', 'versionStamp'
);
Related reference
Updating an index
Querying an index
Dropping an index
Listing indexes
Running the luceneSupport tool with a security manager