Result set batch filter - MySQL

A result set batch filter gets invoked whenever a result set is being sent by the database server on a connection on which the batch parameters have been set.

Result set batch filters can modify the result set, hide or add rows, modify specific values, etc... The only limitations are typically that you do not want to confuse the database client, which may expects data to look a certain way.

Each packet represents a batch of rows in a result set. The exact number of rows depends on the settings specified in the connection parameters.

Parameters

Schema pattern

Optional. If specified, only execute the filter if the result set contains at least one column from a table in the specified schema(s). If the given value starts with regex: then the rest of the value is compiled as a Java regular expression.

For instance: regex:(accounting)|(acct_\p{Alnum}+)

Table pattern

Optional. If specified, only execute the filter if the result set contains at least one column from the specified table(s). If the given value starts with regex: then the rest of the value is compiled as a Java regular expression.

For instance: regex:(customers)|(cust_[0-9]+)

Query pattern

Optional. If specified, a regular expression that specifies which SQL query should be active for this filter to be executed.

For instance: ^select.+from (customers)|(cust_[0-9]+).*

Client IPs

A list of IP addresses (IP4 and/or IP6) and/or regular expressions for IP addresses.

Example:

12.34.56.78
1234:5678:90ab::01
regex:98\.76\..*
regex:9876:5432:.*

Users

A list of user names and/or regular expressions for user names.

Examples:

jdoe, mcortez, achang

regex:[a-z]+_ES

Context

The usual context.packet variable will always be a row batch object.

Examples

Modify one row, and add a clone of that row to the batch:

let batch = context.packet;

for (let row of batch) {

if (row["first_name"] && row["first_name"] === "Juliet") {

row.last_name += " MODIFIED";

let dup = row.clone();

dup.first_name = "Clone of Juliet";

batch.push(dup);

}

}

Remove certain rows:

let batch = context.packet;

for (let row of batch) {

if (row.balance && row.balance > 1000) {

row.remove();

}

}