SQL query indexing

We will present here a simple example of indexing a SQL query.

Configuration

Indexing class

com.axemble.axvdocsearch.extensions.base.implementation.BaseSQLIndexExtension

Custom Tags « system »

Parameter Name	Description
ID
REFERENCE
DATASOURCE_NAME	Name of a data source to execute the SQL query If empty : the Process database is used by default.
SQLQUERY	SQL query to be executed Please note: a constraint is present at this level: The SQL query must not end with a GROUPBY, ORDER BY, HAVING clause. However, it can obviously end with a FROM, [?] JOIN, WHERE clause.
ID_SQL_COLUMN_NAME	Name of the SQL column for the ID
REFERENCE_SQL_COLUMN_NAME	Name of the SQL column for the reference
TITLE_SQL_COLUMN_NAME	Name of SQL column for title
DESCRIPTION_SQL_COLUMN_NAME	Name of SQL column for description
CREATIONDATE_SQL_COLUMN_NAME	Name of SQL column for creation date
URI_SQL_COLUMN_NAME	Name of SQL column for URI
HYPERLINK_SQL_COLUMN_NAME	Name of SQL column for hyperlink
MODIFICATIONDATE_SQL_COLUMN_NAME	Name of SQL column for incremental indexing
MUST_FETCH_SIZE	Indicates if we should execute the SQL query with a fetchSize (buffering when reading the query) “true” by default

An example of XML configuration

Here is a possible XML configuration:

<index name="IndexSQL"
         label="LG_IndexSQL"
         controller="com.axemble.axvdocsearch.core.controllers.implementation.LuceneController"
         extension="com.axemble.axvdocsearch.extensions.base.implementation.BaseSQLIndexExtension"
         indexStorePath="axvdocsearch\IndexSQL"
         updateOnIndexedDocuments="true"
         locales="fr" >

    <parameters>
        <parameter key="SQLQUERY" value="SELECT * FROM DIR_USER INNER JOIN DIR_DIRECTORY_OBJECT ON DIR_DIRECTORY_OBJECT.DIR_DIRECTORY_OBJECT_ID = DIR_USER.DIR_USER_ID" />
        <parameter key="ID_SQL_COLUMN_NAME" value="dir_user_id" />
        <parameter key="REFERENCE_SQL_COLUMN_NAME" value="login" />
        <parameter key="TITLE_SQL_COLUMN_NAME" value="lastname" />
        <parameter key="DESCRIPTION_SQL_COLUMN_NAME" value="description" />
        <parameter key="CREATIONDATE_SQL_COLUMN_NAME" value="created" />
    </parameters>

    <customtag name="EMAIL" type="text" />
    <customtag name="FIRSTNAME" type="text" />
</index>

Source : https://wiki.myvdoc.net/xwiki/bin/view/Dev+Floor/SQLIndexExtension