Creating an index The luceneSupport optional tool lets you use Apache Lucene to perform full-text indexing and searching of the contents of text columns. luceneSupport optional toolcreating 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.

    Language codes supported by the Lucene pluginThis table lists the languages and corresponding 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

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