D
Table Data Source Synchronization

Ultra Search crawls database tables in the main Oracle9i database instance where Ultra Search is installed. Additionally, it can crawl remote databases if they have been linked to the main Oracle9i database. Remote databases are linked to the main Oracle9i instance with database links.

See Also: Oracle9i Database Administrator's Guide for instructions on how to create database links.

Ultra Search provides a logging mechanism to optimize crawling of table sources. Using this logging mechanism, only newly updated documents are revisited during the crawling process. If the source database is not an Oracle database, then you must perform a sequence of steps to use this feature.

Synchronizing Crawling of Oracle Databases

Before creating log tables and log triggers, make sure that the Ultra Search instance schema has the 'CREATE ANY TABLE' and 'CREATE ANY TRIGGER' system privileges. For tables in Oracle databases, data definition language (DDL) statements are provided to create the following:

Create Log Table

The log table stores changes that have occurred in the base table. The Ultra Search crawler uses the change information to figure out which rows need to be recrawled. For example, a log table generated by Ultra Search could be named WK$LOG.

The structure of the log table conforms to the following rules:

1. For every primary key column of the base table, a column must be created in the log table.
2. There can be up to only eight primary key columns in the base table.
3. Each column in the log table that corresponds to a primary key column must be named Kx, where x is a number from one to eight.
4. Each column in the log table that corresponds to a primary key column must be of type VARCHAR2(1000).
5. There must be exactly one column named "mark" that has type CHAR(1).
6. The column named "mark" must have a default value 'F'.

Example:

The base table employees has the following structure:

Column Name	Column Type
ID	NUMBER
NAME	VARCHAR2(200)
ADDRESS	VARCHAR2(400)
TELEPHONE	VARCHAR2(10)
USERNAME	VARCHAR2(24)

If the primary key of the employees table comprises of the ID and NAME columns, then a log table WK$LOG (name is generated on the fly) is created with the following structure:

Column Name	Column Type
K1	NUMBER
K2	VARCHAR2(200)

The SQL statement for creating the log table will be as follows:

CREATE TABLE WK$LOG(
K1 VARCHAR2(1000),
K2 VARCHAR2(1000),
MARK CHAR(1) default 'F')

Create Log Triggers

An INSERT trigger, UPDATE trigger and DELETE trigger are created. The Oracle trigger definitions are as follows:

INSERT Trigger Statement

Every time a row is inserted into the employees base table, the INSERT trigger inserts a row into the log table. The row in the log table records the new values of the id and the name into the k1 and k2 columns. An 'F' is inserted into the mark column to signal the crawler that work needs to be done for this row.

For example:

CREATE OR REPLACE TRIGGER wk$ins
AFTER INSERT ON employees
FOR EACH ROW

BEGIN
  INSERT INTO WK$LOG(k1,k2,mark)
    VALUES(:new.id,:new.name,'F');
END;

UPDATE Trigger Statement

Every time a row is updated in the employees base table, the UPDATE trigger inserts two rows into the log table. The first row in the log table records the old values of the id and the name into the k1 and k2 columns. An 'F' is inserted into the mark column to signal the crawler that work needs to be done for this row. The second row in the log table records the new values of the id and the name into the k1 and k2 columns.

For example:

CREATE OR REPLACE TRIGGER wk$upd
AFTER UPDATE ON employees
FOR EACH ROW

BEGIN
  INSERT INTO WK$LOG(k1,k2,mark)
    VALUES(:old.id,:old.name,'F'); 
  INSERT INTO WK$LOG(k1,mark)
    VALUES(:new.id,:new.name,'F');
END;

DELETE Trigger

Every time a row is deleted from the employees base table, the DELETE trigger inserts a row into the log table. The row in the log table records the old values of the id and the name into the k1 and k2 columns. An 'F' is inserted into the mark column to signal the crawler that work needs to be done for this row.

For example:

CREATE OR REPLACE TRIGGER wk$del
AFTER DELETE ON employees
FOR EACH ROW

BEGIN
  INSERT INTO WK$LOG(k1,k2,mark)
    VALUES(:old.id,:old.name,'F'); 
END;

Synchronizing Crawling of Non-Oracle Databases

For tables in non-Oracle remote databases, you must perform the following steps:

1. Manually create the log table yourself. The log table must conform to the rules for log tables described earlier. Also, they must reside in the same schema and database instance as the base table.
2. Create three triggers that record inserts, updates, and deletes on the base table. These triggers must exhibit the same behavior as the triggers described earlier for Oracle tables.
3. Associate the log table. When you have completed these tasks, choose the "Enable logging mechanism (non-Oracle tables)" option during the creation of an Ultra Search table data source. By choosing that option, the Ultra Search administration tool prompts you for the name of the log table in the remote database. Ultra Search associates this log table with the base table. Ultra Search assumes that you have correctly performed steps 1 and 2.