WL#8776: Query Rewrite Plugin: Support insert, update and delete statements

The Rewriter query plugin currently only support SELECT statements as patterns. The purpose of this worklog is to expand the Rewriter plugin to support INSERT, UPDATE and DELETE statements. This expansion should be an addition of features and should not break existing functionality in the Rewriter plugin, or the mysql server.

User documentation for the Rewriter plugin:
http://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html

Functional requirements

• F1: The plugin shall accept any form of INSERT statement, with support for parameter markers, for its pattern and match it to identical incoming queries.

• F2: The plugin shall accept any form of UPDATE statement, with support for parameter markers, for its pattern and match it to identical incoming queries.

• F3: The plugin shall accept any form of DELETE statement, with support for parameter markers, for its pattern and match it to identical incoming queries.

To use INSERT, UPDATE and DELETE statements in the Rewriter plugin the user has to specify such a statement in the rule table. This is done by inserting rules into the Rewriters table named rewrite_rules. This works just like it does for SELECT statements; the user must specify a pattern and a replacement in order to create a rewrite rule. The pattern and the replacement does not have to be of the same type, for example the following rule is valid:

 Pattern:     DELETE FROM t1 WHERE col = ?
 Replacement: UPDATE t1 SET col = NULL WHERE col = ? 

General

The Rewriter plugin itself contained everything it needed to handle SELECT statements. The only thing missing to be able to support other statements were that the Rewriter depended on the normalized form of the query, and the visitor that was used to visit the parse trees of the queries only supported SELECT statements. This was not located in the plugin itself, but in the server code. Therefore the plugin did not need any changes other than to accept other kinds of statements.

Rewriter plugin changes

The only functional change done to the Rewriter plugin was that the check that accepts the statement type was expanded to allow INSERT, UPDATE and DELETE statements. To accommodate for the new statements being added, some enums, error messages and function names were changed.

Files changed
path: plugin/rewriter/

• messages.h
  
• rewriter.cc
  
• rule.cc
  
• rule.h
  
• services.cc
  
• services.h

Parser service changes

The server's plugin interface was changed to support more statements. The parser service tells the plugins the type of the current query, but it previously would only return the type "SELECT statement" or "other". This was expanded to support INSERT, UPDATE and DELETE statements.

Files changed

• include/mysql/service_parser.h
  
• sql/parser_service.cc

SQL lex

Most of the work done to support the new features was done in the sql_lex files. This is where the entire queries are printed and thus where the plugin gets the normalized form of the queries. These files also contain the visitor that visits all the elements of the queries. Both the visitor and the print function had to be modified to support statements other than SELECT.

Files changed

• sql/sql_lex.cc
  
• sql/sql_lex.h

SELECT_LEX::print()

The print previously only supported SELECT statements, so it had to be modified in order to to be able to get the normalized form of INSERT, UPDATE and DELETE statements.

The print function was split into smaller functions, like print_from_clause() and print_where_cond(), to be able to reuse this functionality when printing other statements. A switch statement was then added to differentiate the queries, and a function for each supported query was added. Additional functions to print different parts of queries was added as needed. Functions like print_update_list() and print_insert_fields() are examples of functions that were added to support UPDATE and INSERT statements.

SELECT_LEX::accept()

The accept method takes a visitor and walks all the items of the query with this visitor. This function previously only visited SELECT statements, but since SELECT, INSERT, UPDATE and DELETE statements have different query elements in different orders, the accept function had to be modified to support this.

The accept function was split similar to the print function and functions were added to accept the different statement types. The added functions were accept_select(), accept_insert() and accept_update(); DELETE statements were similar enough to SELECT statements to just use the same function.