Rewrite the maxscale filter using awk

Question

The following rewrite rules work 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 to write this query using placeholders in regex_grammer:Awk. If I know this, I can make a breakthrough on 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 think this only works with regex_grammar Awk, not Native. Am I right?

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

I'm open to other suggestions or tools too! Maxscale is the most promising solution for me.

Answer 1

The replacement type you are doing requires repeated replacement, and the number of values ​​is not fixed. Rewrite filters are suitable for simpler use cases where the pattern is fixed and complex matching is not required.

However, this can still be done via the regexfilter in MaxScale, which allows for more liberal use of regular expressions. The following regex filter configuration should handle simple values ​​from the IN list and wrap them in a SHA1 function call.

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

This is the regex101.com page I used to test it.

Note that this does not work with strings with embedded commas, meaning it is not really a general solution.