FR: Query Rewriting using regular expressions

Slack Context link: https://planetscale.slack.com/archives/C02JPHAQHB7/p1701190291867839

This request is heavily inspired by features available in ProxySQL, which we use as a stop gap at times in Tech Solutions and Customer Engineering to work around various compatibility challenges for customers with limited ability/capacity to implement changes.

Many customers and prospects coming to us with legacy environments experience some or all of the below challenges:

• Application code can no longer be changed so queries can't be rewritten
• Team evaluating database choices is not the team with direct access to code
• Application code changes will only be considered when a POV has successfully been completed
• Prior reliance on MySQL features like lowercase_table_names, which break compatibility with Vitess (i.e. case insensitivity expectation on table names and table aliases)
• Queries use reserved keywords that were alright to use in MySQL 5.6/5.7, but have been blocked in MySQL 8
• Queries can't be individually adapted to use Boost (or specific query comments needed to help analyze a performance problem)
• Heavy analytical queries can't easily be sent over a separate connection to make use of replicas.

If we had a way to rewrite queries on the fly, based on individualized sets of rules per customer, we could overcome many of these challenges, and reduce the friction to initial adoption.

Core implementation goals might be:

• Toggling rewrite rules on/off altogether as a feature without deleting underlying configuration. 99% of users likely won't need this.
• Configuration store to enter individual rules, probably needing at least the following properties:
  • active (to individually toggle rules on or off)
  • priority (to decide the order in which rules are applied)
  • username (to filter incoming queries)
  • keyspace/database (to filter incoming queries)
  • match_digest (pattern applied against full query to see if the rule should be applied)
  • match_pattern (finding specifically the part of the query to be rewritten)
  • replace_pattern (preferably implementing regex transformations and ability to insert previously isolated groups, etc)
  • regex modifiers
  • target tablet type (would be great for rudimentary read/write splitting)

As discussed on Slack, we currently use ProxySQL to help offer a lot of this, and having this functionality built into pscale would offer a solution analogous to that without needing to resort to 3rd party tooling. However, if this were built straight into Edge, it would offer us the exact same benefits without requiring the customer to do anything.

Start by reviewing the psdbproxy repository and the issue's references to pscale, Edge, and ProxySQL to determine where query handling and configuration live. The issue does not name files, tests, or a settled scope; done would require an agreed design and implementation for rule storage, matching, rewriting, ordering, filtering, toggling, and tablet targeting.