Merge lp:~posulliv/maria/query-rewrite into lp:maria/5.3

[Padraig O'Sullivan]

This branch contains a new plugin point for query rewriting that I'd like to have considered. It allows someone to develop a plugin which can modify the text of a query before it is parsed.

A very simple explanation of my use-case is to transform a query with joins into a query on virtual tables that contains no joins. I need this transformation to be transparent to the user who can submit their original queries as before.

I picked this point for the plugin as its much easier for me to parse the query myself than to work with the parse tree produced by Maria. As Sergei mentioned on the MySQL internals list, a better plugin point may be after the query is parsed. I am open to working on this if someone can provide me with some help? Basically, what is the simplest structure I can pass to a plugin that represents the parse tree? Also, how can I obtain documentation or examples of how to modify the parse tree?

One possibility I was thinking of is to keep the current plugin points I have and also add another method to the rewriting interface which takes the parse tree as a parameter.

Thanks a lot,
Padraig

[Michael Widenius (monty)]

Hi!

>>>>> "Padraig" == Padraig O'Sullivan <email address hidden> writes:

Padraig> Padraig O'Sullivan has proposed merging lp:~posulliv/maria/query-rewrite into lp:maria.
Padraig> Requested reviews:
Padraig> Maria-captains (maria-captains)

Padraig> This branch contains a new plugin point for query rewriting that I'd like to have considered. It allows someone to develop a plugin which can modify the text of a query before it is parsed.

Padraig> A very simple explanation of my use-case is to transform a query with joins into a query on virtual tables that contains no joins. I need this transformation to be transparent to the user who can submit their original queries as before.

Padraig> I picked this point for the plugin as its much easier for me to parse the query myself than to work with the parse tree produced by Maria. As Sergei mentioned on the MySQL internals list, a better plugin point may be after the query is parsed. I am open to working on this if someone can provide me with some help? Basically, what is the simplest structure I can pass to a plugin that represents the parse tree? Also, how can I obtain documentation or examples of how to modify the parse tree?

Padraig> One possibility I was thinking of is to keep the current plugin points I have and also add another method to the rewriting interface which takes the parse tree as a parameter.

Igor, could you give some directions to Padraig how to solve his
problem in the nicest possible way?

Regards,
Monty

Unmerged revisions

2820. By Padraig O'Sullivan on 2010-10-15

Added another plugin point for query rewrite which allows the parse tree to be rewritten.

2819. By Padraig O'Sullivan on 2010-10-14

Simple version of query rewrite hook where query text is passed to rewrite hook. Also added a simple plugin which does nothing interesting except convert the query to lower case.

=== modified file 'include/mysql/plugin.h'
--- include/mysql/plugin.h	2010-06-01 19:52:20 +0000
+++ include/mysql/plugin.h	2010-10-15 16:31:17 +0000
@@ -71,9 +71,10 @@
 #define MYSQL_DAEMON_PLUGIN          3  /* The daemon/raw plugin type */
 #define MYSQL_INFORMATION_SCHEMA_PLUGIN  4  /* The I_S plugin type */
 #define MYSQL_AUDIT_PLUGIN           5  /* The Audit plugin type        */
-#define MYSQL_REPLICATION_PLUGIN     6	/* The replication plugin type */
-#define MYSQL_AUTHENTICATION_PLUGIN  7  /* The authentication plugin type */
-#define MYSQL_MAX_PLUGIN_TYPE_NUM    8  /* The number of plugin types   */
+#define MYSQL_QUERY_REWRITE_PLUGIN   6	/* The query rewrite plugin type */
+#define MYSQL_REPLICATION_PLUGIN     7	/* The replication plugin type */
+#define MYSQL_AUTHENTICATION_PLUGIN  8  /* The authentication plugin type */
+#define MYSQL_MAX_PLUGIN_TYPE_NUM    9  /* The number of plugin types   */
 
 /* We use the following strings to define licenses for plugins */
 #define PLUGIN_LICENSE_PROPRIETARY 0

=== added file 'include/mysql/plugin_query_rewrite.h'
--- include/mysql/plugin_query_rewrite.h	1970-01-01 00:00:00 +0000
+++ include/mysql/plugin_query_rewrite.h	2010-10-15 16:31:17 +0000
@@ -0,0 +1,57 @@
+/* Copyright (C) 2010 Akiban Technologies
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+
+#ifndef _my_query_rewrite_h
+#define _my_query_rewrite_h
+
+#include <stdint.h>
+
+/*************************************************************************
+  API for query rewrite plugin. (MYSQL_QUERY_REWRITE_PLUGIN)
+*/
+
+#include "plugin.h"
+
+#define MYSQL_QUERY_REWRITE_INTERFACE_VERSION 0x0200
+
+typedef struct st_lex LEX;
+
+/*************************************************************************
+  Here we define the descriptor structure, that is referred from
+  st_mysql_plugin.
+
+  rewrite_query() is invoked before a query is parsed allowing a plugin
+  the opporunity to rewrite the query string passed as a parameter
+  to the function
+
+  free_rewritten_query() is invoked only if a plugin rewrote the text of a query.
+  It frees any memory a plugin allocated for the rewritten query.
+
+  rewrite_parse_tree() is invoked after a query is parsed by MySQL. It
+  gives a rewriting plugin the opportunity to modify the parse tree
+  generated by MySQL. This allows a plugin developer to rewrite a query
+  without having to worry about parsing a query.
+*/
+
+struct st_mysql_query_rewrite
+{
+  int interface_version;
+  char* (*rewrite_query)(const char* schema, size_t schema_len, const char* query, size_t orig_query_len, size_t* rewritten_query_len);
+  void (*free_rewritten_query)(char* query);
+  void (*rewrite_parse_tree)(MYSQL_THD thd, LEX* parse_tree);
+};
+
+
+#endif /* _my_query_rewrite_h */

=== added directory 'plugin/rewrite_lower'
=== added file 'plugin/rewrite_lower/CMakeLists.txt'
--- plugin/rewrite_lower/CMakeLists.txt	1970-01-01 00:00:00 +0000
+++ plugin/rewrite_lower/CMakeLists.txt	2010-10-15 16:31:17 +0000
@@ -0,0 +1,17 @@
+# Copyright (c) 2010, Padraig O'Sullivan. All rights reserved.
+# 
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+# 
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+# 
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
+
+MYSQL_ADD_PLUGIN(rewrite_lower rewrite_lower.cc
+  MODULE_ONLY MODULE_OUTPUT_NAME "rewrite_lower")

=== added file 'plugin/rewrite_lower/Makefile.am'
--- plugin/rewrite_lower/Makefile.am	1970-01-01 00:00:00 +0000
+++ plugin/rewrite_lower/Makefile.am	2010-10-15 16:31:17 +0000
@@ -0,0 +1,32 @@
+# Copyright (C) 2010 Padraig O'Sullivan
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
+
+## Makefile.am for query rewrite plugin that converts a query to lower case
+
+pkgplugindir =		$(pkglibdir)/plugin
+INCLUDES =              -I$(top_srcdir)/include \
+			-I$(top_srcdir)/sql \
+			-I$(top_srcdir)/regex \
+			-I$(srcdir)
+
+#noinst_HEADERS = rewrite_lower.h
+
+pkgplugin_LTLIBRARIES =	rewrite_lower.la
+rewrite_lower_la_LDFLAGS =	-module -rpath $(pkgplugindir) -L$(top_builddir)/libservices -lmysqlservices -lstdc++
+rewrite_lower_la_CXXFLAGS=	$(AM_CXXFLAGS) -DMYSQL_DYNAMIC_PLUGIN
+rewrite_lower_la_CFLAGS =	$(AM_CFLAGS) -DMYSQL_DYNAMIC_PLUGIN
+rewrite_lower_la_SOURCES = rewrite_lower.cc
+
+EXTRA_DIST=		plugin.in CMakeLists.txt

=== added file 'plugin/rewrite_lower/plug.in'
--- plugin/rewrite_lower/plug.in	1970-01-01 00:00:00 +0000
+++ plugin/rewrite_lower/plug.in	2010-10-15 16:31:17 +0000
@@ -0,0 +1,4 @@
+MYSQL_PLUGIN(rewrite_lower,[Convert query to lower-case Query Rewriting Plugin],
+        [Example query rewriting plugin.])
+MYSQL_PLUGIN_DYNAMIC(rewrite_lower,   [rewrite_lower.la])
+MYSQL_PLUGIN_STATIC(rewrite_lower,  [librewritelower.a])

=== added file 'plugin/rewrite_lower/rewrite_lower.cc'
--- plugin/rewrite_lower/rewrite_lower.cc	1970-01-01 00:00:00 +0000
+++ plugin/rewrite_lower/rewrite_lower.cc	2010-10-15 16:31:17 +0000
@@ -0,0 +1,84 @@
+/* Copyright (C) 2010 Padraig O'Sullivan
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA */
+
+#include <ctype.h>
+#include <string.h>
+
+#include <mysql/plugin.h>
+#include <mysql/plugin_query_rewrite.h>
+
+using namespace std;
+
+static char* rewrite_lower(const char*,
+						   size_t,
+						   const char* query,
+						   size_t original_query_len,
+                           size_t* rewritten_query_len)
+{
+  char* rewritten_query= new char[original_query_len];
+  memcpy(rewritten_query, query, original_query_len);
+  while (*rewritten_query++ = tolower(*rewritten_query));
+  *rewritten_query_len= original_query_len;
+  return (rewritten_query - (original_query_len + 1));
+}
+
+
+static void free_rewritten_query(char* query)
+{
+  delete [] query;
+}
+
+
+static void null_parse_rewrite(MYSQL_THD, LEX*)
+{
+}
+
+
+static int rewrite_lower_plugin_init(void *)
+{
+  return 0;
+}
+
+static int rewrite_lower_plugin_deinit(void *)
+{
+  return 0;
+}
+
+static struct st_mysql_query_rewrite rewrite_lower_descriptor= {
+  MYSQL_QUERY_REWRITE_INTERFACE_VERSION, /* interface version */
+  rewrite_lower,                         /* rewrite function */
+  free_rewritten_query,                  /* free allocated memory for rewritten query */
+  null_parse_rewrite                     /* rewrite the parse tree (do nothing) */
+};
+
+/*
+  Plugin library descriptor
+*/
+mysql_declare_plugin(rewrite_lower)
+{
+  MYSQL_QUERY_REWRITE_PLUGIN,
+  &rewrite_lower_descriptor,
+  "rewrite_lower",
+  "Padraig O'Sullivan",
+  "Simple example query rewriting plugin",
+  PLUGIN_LICENSE_GPL,
+  rewrite_lower_plugin_init, /* Plugin Init */
+  rewrite_lower_plugin_deinit, /* Plugin Deinit */
+  0x0100 /* 1.0 */,
+  NULL,	/* status variables */
+  NULL,	/* system variables */
+  NULL  /* config options   */
+}
+mysql_declare_plugin_end;

=== modified file 'sql/Makefile.am'
--- sql/Makefile.am	2010-07-10 10:37:30 +0000
+++ sql/Makefile.am	2010-10-15 16:31:17 +0000
@@ -56,6 +56,7 @@
 			sql_map.h sql_string.h unireg.h \
 			sql_error.h field.h handler.h mysqld_suffix.h \
 			sql_profile.h \
+			sql_query_rewrite.h \
 			ha_ndbcluster.h ha_ndbcluster_cond.h \
 			ha_ndbcluster_binlog.h ha_ndbcluster_tables.h \
 			ha_partition.h rpl_constants.h \
@@ -99,6 +100,7 @@
                         sql_join_cache.cc \
 			sql_base.cc table.cc sql_select.cc sql_insert.cc \
 			sql_profile.cc \
+			sql_query_rewrite.cc \
 			sql_prepare.cc sql_error.cc sql_locale.cc \
 			sql_update.cc sql_delete.cc uniques.cc sql_do.cc \
 			procedure.cc sql_test.cc \

=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc	2010-06-26 10:05:41 +0000
+++ sql/sql_parse.cc	2010-10-15 16:31:17 +0000
@@ -27,6 +27,7 @@
 #include "sp_cache.h"
 #include "events.h"
 #include "sql_trigger.h"
+#include "sql_query_rewrite.h"
 
 #ifdef WITH_MARIA_STORAGE_ENGINE
 #include "../storage/maria/ha_maria.h"
@@ -1171,6 +1172,7 @@
     /* 'b' stands for 'buffer' parameter', special for 'my_snprintf' */
     const char* end_of_stmt= NULL;
 
+    mysql_rewrite_raw_query(thd);
     general_log_write(thd, command, thd->query(), thd->query_length());
     DBUG_PRINT("query",("%-.4096s",thd->query()));
     
@@ -6003,6 +6005,7 @@
       {
 	if (! thd->is_error())
 	{
+          mysql_rewrite_parse_tree(thd);
           /*
             Binlog logs a string starting from thd->query and having length
             thd->query_length; so we set thd->query_length correctly (to not

=== modified file 'sql/sql_plugin.cc'
--- sql/sql_plugin.cc	2010-06-01 19:52:20 +0000
+++ sql/sql_plugin.cc	2010-10-15 16:31:17 +0000
@@ -49,6 +49,7 @@
   { C_STRING_WITH_LEN("DAEMON") },
   { C_STRING_WITH_LEN("INFORMATION SCHEMA") },
   { C_STRING_WITH_LEN("AUDIT") },
+  { C_STRING_WITH_LEN("QUERY REWRITE") },
   { C_STRING_WITH_LEN("REPLICATION") },
   { C_STRING_WITH_LEN("AUTHENTICATION") }
 };
@@ -56,6 +57,9 @@
 extern int initialize_schema_table(st_plugin_int *plugin);
 extern int finalize_schema_table(st_plugin_int *plugin);
 
+extern int initialize_query_rewrite_plugin(st_plugin_int *plugin);
+extern int finalize_query_rewrite_plugin(st_plugin_int *plugin);
+
 /*
   The number of elements in both plugin_type_initialize and
   plugin_type_deinitialize should equal to the number of plugins
@@ -63,12 +67,12 @@
 */
 plugin_type_init plugin_type_initialize[MYSQL_MAX_PLUGIN_TYPE_NUM]=
 {
-  0,ha_initialize_handlerton,0,0,initialize_schema_table, 0, 0, 0
+  0,ha_initialize_handlerton,0,0,initialize_schema_table, 0, initialize_query_rewrite_plugin, 0, 0
 };
 
 plugin_type_init plugin_type_deinitialize[MYSQL_MAX_PLUGIN_TYPE_NUM]=
 {
-  0,ha_finalize_handlerton,0,0,finalize_schema_table, 0, 0, 0
+  0,ha_finalize_handlerton,0,0,finalize_schema_table, 0, finalize_query_rewrite_plugin, 0, 0
 };
 
 #ifdef HAVE_DLOPEN

=== added file 'sql/sql_query_rewrite.cc'
--- sql/sql_query_rewrite.cc	1970-01-01 00:00:00 +0000
+++ sql/sql_query_rewrite.cc	2010-10-15 16:31:17 +0000
@@ -0,0 +1,166 @@
+/* Copyright (c) 2010, Padraig O'Sullivan. All rights reserved.
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software Foundation,
+   51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
+
+#include "mysql_priv.h"
+#include "sql_query_rewrite.h"
+
+extern int initialize_query_rewrite_plugin(st_plugin_int *plugin);
+extern int finalize_query_rewrite_plugin(st_plugin_int *plugin);
+
+#ifndef EMBEDDED_LIBRARY
+
+/**
+  Rewrites a query by invoking the plugin's query_rewrite method.  
+
+  @param[in] thd
+  @param[in] plugin
+  @param[in] arg
+
+  @retval FALSE  always
+*/
+static my_bool query_text_rewrite(THD *thd, 
+                                 plugin_ref plugin, 
+                                 void *)
+{
+  st_mysql_query_rewrite *data= plugin_data(plugin, struct st_mysql_query_rewrite *);
+
+  /* rewrite the query */
+  if (data)
+  {
+    size_t rewritten_query_len= 0;
+    char *rewritten_query= data->rewrite_query(thd->db, thd->db_length, thd->query(), thd->query_length(), &rewritten_query_len);
+    if (rewritten_query != NULL && rewritten_query_len > 0)
+    {
+      alloc_query(thd, rewritten_query, rewritten_query_len);
+      data->free_rewritten_query(rewritten_query);
+    }
+  }
+
+  return 0;
+}
+
+
+/**
+  Rewrites a query by invoking the plugin's query_rewrite method.  
+
+  @param[in] thd
+  @param[in] plugin
+  @param[in] arg
+
+  @retval FALSE  always
+*/
+static my_bool parse_tree_rewrite(THD *thd, 
+                                  plugin_ref plugin, 
+                                  void *)
+{
+  st_mysql_query_rewrite *data= plugin_data(plugin, struct st_mysql_query_rewrite *);
+
+  /* rewrite the parse tree */
+  if (data)
+  {
+    data->rewrite_parse_tree(thd, thd->lex);
+  }
+
+  return 0;
+}
+
+void mysql_rewrite_raw_query(THD *thd)
+{
+  plugin_foreach(thd, 
+                 query_text_rewrite, 
+                 MYSQL_QUERY_REWRITE_PLUGIN, 
+                 NULL);
+}
+
+
+void mysql_rewrite_parse_tree(THD *thd)
+{
+  plugin_foreach(thd, 
+                 parse_tree_rewrite, 
+                 MYSQL_QUERY_REWRITE_PLUGIN, 
+                 NULL);
+}
+
+
+/**
+  Initialize a query rewrite plug-in
+  
+  @param[in] plugin
+
+  @retval FALSE  OK
+  @retval TRUE   There was an error.
+*/
+int initialize_query_rewrite_plugin(st_plugin_int *plugin)
+{
+  if (plugin->plugin->init && plugin->plugin->init(NULL))
+  {
+    sql_print_error("Plugin '%s' init function returned error.",
+                    plugin->name.str);
+    return 1;
+  }
+
+  /* Make the interface info more easily accessible */
+  plugin->data= plugin->plugin->info;
+  
+  return 0;
+}
+
+
+/**
+  Finalize a query rewrite plug-in
+  
+  @param[in] plugin
+
+  @retval FALSE  OK
+  @retval TRUE   There was an error.
+*/
+int finalize_query_rewrite_plugin(st_plugin_int *plugin)
+{
+  if (plugin->plugin->deinit && plugin->plugin->deinit(NULL))
+  {
+    DBUG_PRINT("warning", ("Plugin '%s' deinit function returned error.",
+                            plugin->name.str));
+    DBUG_EXECUTE("finalize_query_rewrite_plugin", return 1; );
+  }
+  
+  plugin->data= NULL;
+  return 0;
+}
+
+#else /* EMBEDDED_LIBRARY */
+
+
+void mysql_rewrite_raw_query(THD *thd)
+{
+}
+
+
+void mysql_rewrite_parse_tree(THD *thd)
+{
+}
+
+
+int initialize_query_rewrite_plugin(st_plugin_int *plugin)
+{
+  return 1;
+}
+
+
+int finalize_query_rewrite_plugin(st_plugin_int *plugin)
+{
+  return 1;
+}
+
+#endif /* EMBEDDED_LIBRARY */

=== added file 'sql/sql_query_rewrite.h'
--- sql/sql_query_rewrite.h	1970-01-01 00:00:00 +0000
+++ sql/sql_query_rewrite.h	2010-10-15 16:31:17 +0000
@@ -0,0 +1,36 @@
+/* Copyright (c) 2010, Padraig O'Sullivan. All rights reserved.
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software Foundation,
+   51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
+
+#ifndef SQL_QUERY_REWRITE_INCLUDED
+#define SQL_QUERY_REWRITE_INCLUDED
+
+#include <mysql/plugin_query_rewrite.h>
+
+class THD;
+
+/** 
+  Call query rewrite plugins on the raw query text
+  @param[in] thd thread with the query to be rewritten
+ */
+void mysql_rewrite_raw_query(THD *thd);
+
+
+/**
+  Call query rewrite plugins on the parse tree
+  @param[in] thd thread with the parse tree to be rewritten
+ */
+void mysql_rewrite_parse_tree(THD *thd);
+
+#endif /* SQL_QUERY_REWRITE_INCLUDED */