Creating an index

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:

• SCHEMANAME: The SQL identifier of the schema which holds the table or view. This argument is case-insensitive unless you double-quote it.
• TABLENAME: The SQL identifier of the table or view (also case-insensitive).
• TEXTCOLUMN: The SQL identifier of the text column being indexed (also case-insensitive). The column must have a character datatype.
• INDEXDESCRIPTORMAKER: If the argument is not null, this is the full name of a zero-argument static, public method which creates an org.apache.derby.optional.api.IndexDescriptor. If the argument is null, the index is created using the default maker method, org.apache.derby.optional.api.LuceneUtils.defaultIndexDescriptor. An org.apache.derby.optional.api.IndexDescriptor specifies the following:
  • The analyzer to use when parsing text into indexable terms
  • The names of the indexed fields which can be queried later on
  • The subclass of org.apache.lucene.queryparser.classic.QueryParser which should be used when querying the index later on

  The default org.apache.derby.optional.api.IndexDescriptor supplies one field name (luceneTextField) along with an instance of org.apache.lucene.queryparser.classic.MultiFieldQueryParser as its QueryParser. In addition, the default org.apache.derby.optional.api.IndexDescriptor attempts to find a Lucene-supplied analyzer matching the default language of the database. Matches are found for the languages listed in the following table. Note that the Chinese analyzer was deprecated, so for Chinese, the plugin uses the StandardAnalyzer instead.

  Table 1. Language codes supported by the Lucene plugin

  Language	Language Code
  Arabic	ar
  Armenian	hy
  Basque	eu
  Brazilian	br
  Bulgarian	bg
  Catalan	ca
  Czech	cz
  Danish	da
  Dutch	nl
  English	en
  Finnish	fi
  French	fr
  Galician	gl
  German	de
  Greek	el
  Hindi	hi
  Hungarian	hu
  Indonesian	id
  Irish	ga
  Italian	it
  Latvian	lv
  Norwegian	no
  Persian	fa
  Portuguese	pt
  Romanian	ro
  Russian	ru
  Spanish	es
  Swedish	sv
  Thai	th
  Turkish	tr

  Derby supplies another utility method which instantiates the default Lucene analyzer; this utility method is called org.apache.derby.optional.api.LuceneUtils.standardAnalyzer, and it materializes an org.apache.lucene.analysis.standard.StandardAnalyzer.

• KEYCOLUMNS: This is an optional list of SQL identifiers for other columns in the table or view. The values of these columns are stored in the text index for use in joining Lucene results back to the original data. If the KEYCOLUMNS are omitted, TABLENAME must identify a base table with a primary key; in this case, the whole primary key is stored in the text index for joining later.

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

• DOCUMENTID
• SCORE

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'
);