# 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:

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

Parent topic: Using the luceneSupport optional tool
Related reference
Updating an index
Querying an index
Dropping an index
Listing indexes