droprow.js Filter

The droprow filter can be used to selectively filter out transactions based on matching column values at the ROW level.

Pre-configured filter name	droprow
JavaScript Filter File	tungsten-replicator/support/filters-javascript/droprow.js
Property prefix	replicator.filter.droprow
Stage compatibility	binlog-to-q, q-to-dbms
tpm Option compatibility	svc-extractor-filters, svc-applier-filters
Data compatibility	Row Events

Parameters

Parameter	Type	Default	Description
definitionsFile	Filename	~/droprow.json	Location of the definitions file for row filtering.
matchCase	Boolean	true	Controls whether text based comparisons are Case Sensitive (true) or not (false)
rule	String	matchany	Valid Values : matchany | matchall. If more than one column/value pair are defined, this property will determine whether there must be a match for all columns (matchall) or only one (matchany).

The filter is available by default as droprow, and the filter is configured through a JSON file that defines the list of column/values to match in a row to be dropped.

This filter has the following requirements and caveats:

• The filter relies on the colnames filter being enabled.
• The filter will only work on ROW events, therefore the source database needs to be running in Row-Based binary logging mode.
• The filter will only compare values as part of an INSERT statement and NEW values as part of an UPDATE statement.
• Rows that are affected by UPDATE and DELETE statements where the values are part of the WHERE clause will not be removed from THL.
• If the filter is applied to tables with Foreign Keys, be sure to include all tables in the hierarchy as part of the filter to avoid referential integrity errors.

To enable the filter:

shell> vi /etc/tungsten/tungsten.ini

[servicename]
...
svc-extractor-filters=colnames,droprow
property=replicator.filter.droprow.definitionsFile=/opt/continuent/share/droprow.json
...

shell> tpm update

Additional parameters that override the defaults can be also supplied, for example:

shell> vi /etc/tungsten/tungsten.ini

[servicename]
...
svc-extractor-filters=colnames,droprow
property=replicator.filter.droprow.definitionsFile=/opt/continuent/share/droprow.json
property=replicator.filter.droprow.rule=matchall
...

shell> tpm update

A sample configuration file is provided in tungsten-replicator/support/filters-config/droprow.json. The format of the file is a JSON array of schema/table/column/value specifications. The match rule can also be supplied on a per table basis as per the examples below. If not supplied the global default for the filter, as described above, will be used:

[
{
  "schema": "vip",
  "table": "clients",
  "rule": "matchall",
  "columns": [
    {
      "column": "city",
      "value": "London"
    },
    {
      "column": "country",
      "value": "UK"
    }
  ]
}
]

Where:

• schema specifies the name of the schema on which to apply the filtering.
• table specifies the name of the table on which to apply the filtering.
• rule specifies the matching rule to apply the filtering. Setting the value in the JSON will override the global default specified in the main configuration (See above).
• matchany(default) will remove rows if only one condition matches. Equivalent to an OR condition.
• matchall will remove rows if only all conditions match. Equivalent to an AND condition.
• columns is an array of column/values to be matched.
• column is the name of the column in the table to match.
• value is the Value of the column to match. When Rule is matchany this can also be supplied as an array of Values (See second example below).

For example:

[
{
  "schema": "vip",
  "table": "customers",
  "rule": "matchany",
  "columns": [
    {
      "column": "country",
      "value": [ "Australia", "UK" ]
    }
  ]
}
]

The above example filters rows from the customers table, in the vip schema where the country column is either "Australia" OR "UK"