The DbResources are basically PL-SQL code which is loaded the first time the replication is started. These are loaded by the java code and executed as jdbc statements. They are read from a set of sql files which is specific for each database vendor. There is a directory for each vendor. These sub-directories reside under org/xmlBlaster/contrib/replication/setup and have the name of the database vendor. For example postgres or oracle. In these directories the important files are:
bootstrap.sql which is the file containing resources needed for the replication. This file exist for each database vendor.
createDropAlter.sql contains the triggers (and possibly functions) needed to detect CREATE, ALTER and DROP actions. This is only needed for such databases which have the possibility to add a trigger to the schema, i.e. for such which can detect by means of an event that a table has been created, dropped or altered. This is the case for oracle. Postgres on the other hand can not detect such changes. For this reason it does not have such a file.
The resources consist of the following type of database objects:
Tables
Views
Sequences
Functions
Triggers
These resources are database vendor specific. This means that their content can be different between oracle and postgres. Some of the resources can even be specific to a certain vendor, that is, they can exist for postgres but not for oracle or vice versa.
Since there can be several replications, i.e. several DbWatchers coexisting in the same database and even in the same schema, several sets of DbResources can coexist in the same database or schema. For this reason, each replication is identified by its replication.prefix attribute. This prefix will be used for the name of each DbResource. So when we speak of the ITEMS table, and we have defined a replication.prefix to be REPL_ (which by the way is the default), the real name of the resource will be REPL_ITEMS . For the sake of simplicity however we will continue to refer to it to ITEMS in this document.
There are several tables involved in the replication. The following is a short explanation. The tables are sorted in their importance order:
This is the most important table in the replication. It is the interface between the synchronous PL-SQL part of the replication and the asynchronous java DbWatcher. It is used as a queue where changes are temporary stored before the DbWatcher will pick them up and distribute them.
Table 2.1. The Items table
| Column name | Type | Description |
|---|---|---|
| REPL_KEY | NUMBER | This is a monotone increasing positive number produced by a sequence when the transaction is initiated. If a transaction is rolled back, the REPL_KEY is lost and a hole in the sequence occurs. This is the only primary key of the table. It uniquely identifies a replication change. As a replication change we understand a single operation which has to be replicated. Can never be null. |
| TRANS_KEY | VARCHAR | A string identifying a transaction within which operations to be replicated are executed. Since several operations can be executed within one single transaction, several entries in the table could share the same value. The syntax of the content depends on the database vendor. At distribution step, the DbWatcher will send entries belonging to the same transaction in the same message. Shall never be null. |
| DBID | VARCHAR | String identifying the database used as the source in the replication. Can be null and is currently not directly used for replication purposes. |
| TABLENAME | VARCHAR | The name of the table to which the entry to be processed belongs. It is the relative name of the table, that is, it does not contain the name of the schema nor the catalog. Shall never be null. |
| GUID | VARCHAR | A String identifying the unique id of the entry. It is currently used for some database vendors for specific cases. It is for example used in oracle when a table containing a LONG column has to be replicated. It could also be used to increase performance on the slave side when storing the replicas. Its content is vendor specific and can be null. |
| DB_ACTION | VARCHAR | The action to be replicated. Is either CREATE, DROP, ALTER, INSERT, UPDATE, DELETE. Is never null and is not database vendor specific. |
| DB_CATALOG | VARCHAR | The catalog to which the change belongs. Can be null and is currently not used. |
| DB_SCHEMA | VARCHAR | The schema to which the change belongs. Can be null if the database vendor does not support schemas. Is extensively used for oracle. |
| CONTENT | CLOB | This is an xml literal stored as a clob and following the syntax specified in the DbWatcher schema. This can be null depending on the ACTION performed. It is only filled on INSERT and UPDATE. In some rare cases it is null even for such operations, in which case the content must be retrieved via the GUID in asynchronous way by the DbWatcher. |
| OLDCONTENT | CLOB | This is the old content of the entry before the change. It is not null only on DELETE and UPDATE. It is used by the DbWriter to find the entry to be deleted or updated. In all other cases it is null. |
| VERSION | VARCHAR | A String identifying the version of the replication software used. Can be null. It is currently unused. |
This table contains the information on the tables which have to be replicated. This information is read from the configuration file. The properties of interest are the ones starting with tables..
Table 2.2. The Tables Table
| Column name | Type | Description |
|---|---|---|
| CATALOGNAME | VARCHAR | The name of the catalog to which the table to watch belongs. It is part of the primary key. Can never be null. If the vendor does not use catalogs, then it is a space character. |
| SCHEMANAME | VARCHAR | The name of the schema to which the table to watch belongs. It is part of the primary key. Can never be null. If the vendor does not use schemas, then it is a space character. |
| TABLENAME | VARCHAR | The name of the table to be watched. It is part of the primary key. Can never be null. |
| ACTIONS | CHAR(3) | A combination with a maximum of three characters being the upper case first letter of the action on which the trigger should be fired. Actions are Insert (I), Update (U) and Delete (D). So for example allowed entries are 'UID' or 'UI' or 'I'. If null the trigger will fire on all three actions. |
| STATUS | VARCHAR | A string identifying the status of the replication. When the entry is added to the table this is set to 'CREATING', meaning it will create the trigger but the operation is not successfully finished yet. A trigger on this table creates an entry in the ITEMS table. The DbWatcher will take the entry and add the Trigger if needed. Once the trigger has been successfully created, this STATUS flag changes to OK. This flag is also used when it must decide if the trigger has to be added. If it is not OK, it is assumed to be incorrectly added and is added again. |
| REPL_KEY | NUMBER | This is the number retrieved by the replication sequence. This will replace the sequence attribute passed in the configuration. This way the sequence information is made persistent also for entries which did not have a specific sequence on their configuration. The REPL_KEY ensures that the same sequence is loaded on each startup of the XmlBlaster. Can never be null. |
| TRIGGER_NAME | VARCHAR | The name of the trigger to be executed. This comes from the configuration and shall never be null. |
| DEBUG | NUMBER | This is currently either 0 or 1. In future further debug levels could be allowed. If 1, then debug output is written to the DEBUG_TABLE table. Can not be null. Zero means no debugging will be done for this table. |
This table has only one field RELNAME which contains the relative name of the table. It is a kind of set. If a tablename is in this table, then it is currently replicated. This is only used for vendors which can not have a trigger on the schema, i.e. which can not detect CREATE, DROP or ALTER actions on a table. In such cases, these entries -which are a snapshot- are compared by the DbWatcher with the ones of a view, which is a living dynamic content. When there are differences a change (either CREATE, ALTER or DROP) is detected.
REPL_KEY and LINE are the only columns. The first is filled by the sequence and the second is the content of the debug. This table is filled by the function debug.
Table 2.3. The Longs_Table Table
| Column name | Type | Description |
|---|---|---|
| REPL_KEY | NUMBER | This is a monotone increasing positive number produced by a sequence when the transaction is initiated. If a transaction is rolled back, the REPL_KEY is lost and a hole in the sequence occurs. This is the (only) primary key of the table. It uniquely identifies a replication change. As a replication change we understand a single operation which has to be replicated. Can never be null. |
| CONTENT | CLOB | The content of the entry to be replicated. This is filled by the java code in the DbWatcher thread. It is only used in ORACLE for tables containing at least one column being of the type LONG. In other cases this table has no significance. |
There is currently only one view used for replication purposes: COLS_VIEW. This is only needed for database vendors which do not have any possibility to add triggers which detect a table alteration. For such databases detection of these actions must occur by polling. Since the DbWatcher already polls, this detection is done by him too, before the ITEMS table is checked, a comparison between this view and a snapshot table of the same parameters taken in the previous sweep detects changed occured between the two sweeps. An example of a database where this view is used is postgres.
Currently the sole sequence used is SEQ which is used every time a unique number is needed. Its main purpose is to create the REPL_KEY which is the unique key for the ITEMS table. It is also used for the sequence of the TABLES table and for the DEBUG_TABLE table.
The functions used are several and can be different for different database vendor. We will shortly describe here the most important ones and such which are common to all vendors.
Table 2.4. Description of major functions
| Function Name | Description |
|---|---|
| NEEDS_PROT | Is invoked to determine wether the string needs base64 protection or CDATA protection. |
| COL2XML | Returns a CLOB object containing an xml literal representing the content of all columns of an entry. |
| INCREMENT | Increments the counter for the unique keys and returns one. |