maxscale rewrite filter with awk

Question

The following rewrite rule works as expected:

%%
regex_grammar: Awk
case_sensitive: false
ignore_whitespace: true
%
SELECT msg FROM mytable WHERE id = 123
%
SELECT msg FROM mytable WHERE id = sha1(123)

My question now is, how can I write this query with placeholder in regex_grammer: Awk. If I knew that, I would have a breakthrough for the next problem. My actual goal is to rewrite the following query:

Input:
SELECT msg FROM mytable WHERE id IN (123,456,769)

Output:
SELECT msg FROM mytable WHERE id IN (sha1(123),sha1(456),sha1(769))

I assume that this only works with regex_grammar Awk and not with Native. Am I right?

This is the only documentation that I found: https://mariadb.com/kb/en/mariadb-maxscale-2208-rewrite-filter/

I also accept other suggestions or tools! Maxscale looked the most promising solution to me.

Answer 1

The type of replacement you're doing requires repeated substitutions with no fixed amount of values. The rewrite filter is intended for simpler use-cases where the patterns are fixed and do not require complex matching.

However, this can still be done with the regexfilter in MaxScale that allows more free-form use of regular expressions. The following regexfilter configuration should handle simple values in an IN list and wrap them in a SHA1 function call.

[regex]
type=filter
module=regexfilter
match=/(?i)(IN\s+\(|,)\s*([^,]+)\s*/
replace=$1 SHA1($2)

Here's the regex101.com page I used to test it.

Note that this won't work with strings with embedded commas in them which means it's not really an universal solution.