Examples of JDBC Database Connector Configurations

This section gives examples of JDBC connector configurations.

This page discusses:

Index Cleanup
Accumulation
Configuring Trigger-Based Synchronization with Two Checkpoints

Index Cleanup

You can perform a query in All URIs Query to detect entries that were deleted from the database, so it can replicate these deletions in the index. Write this query to go through the primary keys of all the tables crawled by Exalead CloudView.

The following example shows a two table database to index with Exalead CloudView.

In SQL table 2, the productid field is a foreign key of the productid field stored in SQL table 1. The goal is to configure Exalead CloudView to index one product for each language langid.

For example, the SQL query:

SELECT products.productid,attributes.langid FROM products.attributes WHERE 
products.productid = attributes.productid

This selects all the rows corresponding to the union of the two primary keys productid and attrid. This query costs relatively little, and it allows CloudView to quickly mirror deleted rows from the database.

The field names that make up these keys must be the same as the names returned by the main query.

Accumulation is not performed in this step. When a record belonging to an accumulated document is removed from the database, the document is left unchanged in the index.

Accumulation

The following example shows how the accumulation affects the indexing of a database table where productinfo contains many rows for each product.

Each row details a product specification that has been translated into many languages.

The goal is to accumulate all product specifications into a single document for each language in Exalead CloudView index. To do this, the Initial query must sort the results on the productid field and the langid field with the following SQL query:

SELECT * FROM productinfo ORDER BY productinfo.productid,productinfo.langid

Note: For accumulation to work, write the query to sort the database results on the fields defined as primary keys for this table.

Set the productid field and the langid field to be the primary keys for the table (as the primary key of the SQL table is of no search value). In this scenario, there may be more than two rows that have the same value for the primary key (productid, langid) to be indexed.

When you set the processor to First Value as Meta then only the first value for the name and value fields are kept.

When you set the processor to Multiple Metas then the name and value fields are merged for the rows.

Configuring Trigger-Based Synchronization with Two Checkpoints

To make sure that scanning operations in progress do not miss any record, you can define two checkpoints for your trigger-based synchronization.

For this example, we use the database located in <INSTALLDIR>/docs/sample_database/data.db.

1. Customize the Database

We first customize the database to:

Add a product_order_log table.
Create 3 triggers for update, create, and delete operations.

Run the following SQL queries:

DROP TABLE IF EXISTS product_order_log;
CREATE TABLE product_order_log ("id" INTEGER PRIMARY KEY NOT NULL , "event_date"
DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "action" CHAR);
DROP TRIGGER IF EXISTS product_order_update_trigger;
DROP TRIGGER IF EXISTS product_order_create_trigger;
DROP TRIGGER IF EXISTS product_order_delete_trigger;
CREATE TRIGGER product_order_update_trigger UPDATE ON product_order
BEGIN
INSERT INTO product_order_log ("id", "action") VALUES (old.id, "U");
END;
CREATE TRIGGER product_order_create_trigger INSERT ON product_order
BEGIN
INSERT INTO product_order_log ("id", "action") VALUES (new.id, "C");
END;
CREATE TRIGGER product_order_delete_trigger DELETE ON product_order
BEGIN
INSERT INTO product_order_log ("id", "action") VALUES (old.id, "D");
END;

2. Configure Connection Parameters

Configure your connection parameters as follows:

Driver: org.sqlite.JDBC
Connection string: jdbc:sqlite:<INSTALLDIR>/docs/sample_database/data.db
User: <leave empty>
Password: <leave empty>

3. Configure Query Parameters

To make sure that all operations are taken into account, two checkpoints values are used:

The last scan
The next scan

Configure your query parameters as follows:

Synchronization mode: Trigger-based incremental synchronization
Checkpoint query: This query computes the checkpoint value based on the current date and time in the database.

Use the following query:
```
SELECT datetime('now') FROM product_order limit 1
```
Incremental variable: This variable corresponds to the last checkpoint value (that is, computed during the last scan)

Enter: previousCheckPoint
Incremental start variable: This variable corresponds to the next checkpoint value (that is, computed during the next scan)

Enter: currentCheckPoint

4. Configure Initial Query Parameters

Configure your initial query parameters as follows:

Initial query: Synchronize the entire table.

Use the following query:

SELECT id, date, quantity, customer_id, product_id, store_id FROM product_order

Initial post queries: Delete triggers whose event date is before the initial scan run.

Use the following query:
```
DELETE FROM product_order_log WHERE event_date < '$(currentCheckPoint)'
```

5. Configure Incremental Query Parameters

Configure your incremental query parameters as follows:

Incremental delete queries: Compute which documents need to be deleted.

Use the following query:

SELECT id FROM product_order_log WHERE event_date between '$(previousCheckPoint)' AND
'$(currentCheckPoint)' AND action='D'

Incremental query: Compute which documents have been created or updated since the last scan.

Use the following query:

SELECT p.id id, p.date date, p.quantity quantity, p.customer_id customer_id,
p.product_id product_id, p.store_id store_id FROM product_order as p,
product_order_log as l WHERE p.id=l.id AND l.event_date between
'$(previousCheckPoint)' AND '$(currentCheckPoint)' AND l.action in ('C', 'U')

Incremental pre queries: Delete triggers whose event date is before the last scan run.

Use the following query:
```
DELETE FROM product_order_log WHERE event_date < '$(previousCheckPoint)'
```