Table Data Source synchronization
The Ultra Search Crawler can crawl database tables residing in
the main Oracle 9i database instance where Ultra Search is installed.
Additionally, the Ultra Search Crawler can also crawl remote databases
if these remote databases have been linked to the main Oracle9i
database. Remote databases are linked to the main Oracle9i instance
via database links. Please refer to the Oracle9i Reference documentation
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 will be revisited during the crawling process. If the
source database is not an Oracle database, then you will need
to perform a sequence of manual steps to use this feature.
Oracle databases
For tables residing in Oracle databases, we will provide the
following Data Definition Language (DDL) commands to create the
following:
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 may be named as follows:
WK$LOG.
The structure of the log table will conform to the following
rules:-
- For every primary key column of the base table, a column
must be created in the log table.
- There can be only up to 8 primary key columns in the base
table.
- Each column in the log table that corresponds to a primary
key column must be named Kx where x is a number from
1 to 8.
- Each column in the log table that corresponds to a primary
key column must be of type VARCHAR2(1000).
- There must be exactly one column named "mark"
that has type CHAR(1).
- 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'
)
Log triggers
An insert trigger, update trigger and delete trigger are created.
The Oracle trigger definitions would be as follows:
| Insert
Trigger Statement: |
create
or replace trigger wk$ins
after insert on EMPLOYEES
for each row
begin
insert into WK$LOG(KI,K2,MARK)
values(:new.ID,:new.NAME,'F');
end; |
| Update
Trigger Statement: |
create or replace trigger wk$upd
after update on EMPLOYEES
for each row
begin
insert into WK$LOG(KI,K2,MARK)
values(:old.ID,:old.NAME,'F');
insert into WK$LOG(k1,mark)
values(:new.ID,:new.NAME,'F');
end;
|
| Delete
Trigger Statement: |
create or replace trigger
wk$del
after delete on EMPLOYEES
for each row
begin
insert into WK$LOG(KI,K2,MARK)
values(:old.ID,:old.NAME,'F');
end; |
Explanation:
The table above describes the Oracle syntax for creating three
triggers.
Insert trigger
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.
Update trigger
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.
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.
Non-Oracle databases
For tables residing in non-Oracle remote databases, you must
perform the following steps:-
Step 1: Manually create the log table yourself. The log table
must:-
- conform to the rules for log tables described above.
- reside in the same schema and database instance as the base
table.
Step 2: You must then create three triggers that record inserts,
updates and deletes on the base table.
These triggers must exhibit the same behavior as the triggers
described above for Oracle tables.
Step 3: Associate the log table
When you have completed these tasks, you may then 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 will prompt
you for the name of the log table in the remote database.
Ultra Search will associate this log table with the base table.
Ultra Search assumes that you have correctly performed Steps 1
and 2.
|
