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.
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.