Result set filter - MySQL

A result set filter gets invoked whenever a result set is being sent by the database server, if the connection is not set up to use batches.

Result set 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 expects data to look a certain way.

Each packet represents a row in a result set.

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]+).*

Column pattern

Optional. If specified, a semicolon-separated list of <name>=<value> pairs, where name is the name of a column, and value is a string representation of a value.

For instance: country=US;state=MN;county=Hennepin

columnsAndOr

Defaults to and. If Column pattern was specified, and there is more than one <name>=<value> pair, specify whether these should be and'ed or or'ed.

Query pattern

Optional. If specified, specifies which SQL query should be active for this filter to be executed. If the given value starts with regex: then the rest of the value is compiled as a Java regular expression.

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

Action

What to do if the row matches. The default is code, which executes the JavaScript code, the other option is hide, in which case the row will be ignored and not passed on to the database client.

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.

Context

The usual context.packet variable will always be a ResultsetRow packet.

Examples

Replacing the contents of the first_name column with its initial:

context.packet.first_name = context.packet.first_name.substring(0,1) + ".";

Removing the value in the salary column:

delete context.packet.salary;