MySQL Internals Result Postprocessing Procedures
← Back to MySQL Internals overview page
Contents |
[edit] Writing a Procedure
Note: this section is not about SQL stored procedures but about MySQL procedures like PROCEDURE ANALYSE. For internal information on SQL stored procedures see the Stored Programs section in this wiki.
[edit] Extend class Procedure
Each new procedure needs to extend the Procedure class. For a minimal dummy procedure that doesn't actually change the result set it would look like this:
class proc_dummy: public Procedure
{
}
In a real procedure you'd extend at least some of the member functions below:
[edit] Constructor
Prototype: n/a
The class constructors prototype signature is completely up to you. The only place where objects are instantiated is your own init callback.
To initialize your derived procedure object you have to pass on the select_result pointer the init callback was called with to the base class constructor together with a flag parameter which specifies what kind of procedure you are going to implement. So a minimal constructor would look like this:
your_proc::your_proc(select_result *res)
:Procedure(res, PROC_NO_SORT)
{
}
Possible flag values are PROC_NO_SORT and PROC_GROUP. I have no real idea yet what the two flags are doing but found that for simple procedures PROC_NO_SORT seems to be the right flag to use.
See also the initialization callback section further below.
[edit] change_columns()
Prototype: virtual bool change_columns(List<Item> &field_list);
Here you can change the structure of the result field list, e.g. you can add fields to the field_list or replace the queries result fields by something completely different alltogether (like PROCEDURE ANALYSE() does).
An example that adds an INTEGER field at the end of the field list:
bool proc_rownum::change_columns(List<Item> &field_list)
{
DBUG_ENTER("proc_rownum::change_columns");
// create a new column item
row_num_column = new Item_proc_int("RowNum");
// and attach it to the column list
field_list.push_back(row_num_column);
DBUG_RETURN(0);
}
[edit] send_row()
Prototype: virtual int send_row(List<Item> &fields);
This member is called for every result row in the original result set. Whatever you do here is up to you, it is important to note though that to pass on the result row to the client you have to call result->send_data() yourself.
PROCEDURE ANALYSE() for example does not send any data here, it only produces result rows after aggregating information across all result rows so its send_row() member only aggregates but doesn't send anything.
A simple example which modifies the result value for a single field in the field list before sending it on to the client:
int proc_rownum::send_row(List<Item> &field_list __attribute__((unused)))
{
DBUG_ENTER("proc_rownum::send_row");
// increment row count and set its new value in result row
row_num_column->set(++row_num);
// now send the modified results
if (result->send_data(field_list))
DBUG_RETURN(-1);
DBUG_RETURN(0);
}
[edit] add()
Prototype: virtual void add(void);
This member function is called once for every source row for a GROUP BY query.
See also end_group().
[edit] end_group()
Prototype: virtual void end_group(void);
This member function is called whenever the end of a group in a GROUP BY is detected, it is called after the call to add() for the last source row in the group but before sending the actual aggregated result row for the group with send_row().
[edit] end_of_records()
Prototype: virtual bool end_of_records(void);
This member function is called at the very end after all result rows have been processed with calls to send_row(). This is where you can send extra summary result rows as e.g. PROCEDURE ANALYSE() does.
[edit] Initialization callback
The initialization callback is registered together with the procedure name in the sql_procs array in procedure.cc (see also the "Reqiured server code patches" section)
The initialization callback prototype signature looks like this:
Procedure *(*init)(THD *thd,ORDER *param,select_result *result,
List<Item> &field_list);
In the initialization callback you usually just create and return an instance of your derived Procedure class:
// Create and register the actual procedure object
Procedure *proc_rownum_init(THD *thd,
ORDER *param,
select_result *result,
List<Item> &field_list)
{
DBUG_ENTER("proc_rownum_init");
proc_rownum *pc = new proc_rownum(result);
DBUG_RETURN(pc);
}
[edit] Calling sequence
The init callback is always called first at the beginning of a query, followed by a call to the change_columns(). The end_of_records() member is always called last at the very end.
For simple non-grouping queries only send_row() is called once for each result row.
For grouping queries add() is called once for each source row. end_group() is called once at the end of each group followed by a call to send_row().
You cann use the CALLTRACE() procedure to check in which sequence the member functions are called on any query. This procedure will send one result row for each member function call, every row contains a single text field with one of the values add, end_group, end_of_records or send_row.
[edit] Required server code patches
Currently procedures can only be compiled into the server staticly. There is no dynamic procedure for loading them dynamicly yet like we have for UDFs and plugins.
So you have to perform the following steps to register your procedure with the server:
- register the .cc and .h file in the mysqld_SOURCES list in sql/Makefile.am, e.g. right after the line that the sql_analyse.* files are on:
diff -ruN 5.0/sql/Makefile.am 5.0-myproc/sql/Makefile.am --- 5.0/sql/Makefile.am 2007-08-09 12:11:16.000000000 +0200 +++ 5.0-myproc/sql/Makefile.am 2007-08-09 00:22:19.000000000 +0200 @@ -94,6 +94,7 @@ sql_db.cc sql_table.cc sql_rename.cc sql_crypt.cc \ sql_load.cc mf_iocache.cc field_conv.cc sql_show.cc \ sql_udf.cc sql_analyse.cc sql_analyse.h sql_cache.cc \ + procedure_rownum.cc procedure_rownum.h \ slave.cc sql_repl.cc sql_union.cc sql_derived.cc \ client.c sql_client.cc mini_client_errors.c pack.c\ stacktrace.c repl_failsafe.h repl_failsafe.cc \
- register the .cc source file in the sqlsources list in libmysqld/Makefile.am
diff -ruN 5.0/libmysqld/Makefile.am 5.0-myproc/libmysqld/Makefile.am --- 5.0/libmysqld/Makefile.am 2007-08-09 12:18:16.000000000 +0200 +++ 5.0-myproc/libmysqld/Makefile.am 2007-08-09 00:21:55.000000000 +0200 @@ -54,6 +54,7 @@ opt_sum.cc procedure.cc records.cc sql_acl.cc \ sql_load.cc discover.cc sql_locale.cc \ sql_analyse.cc sql_base.cc sql_cache.cc sql_class.cc \ + procedure_rownum.cc \ sql_crypt.cc sql_db.cc sql_delete.cc sql_error.cc sql_insert.cc \ sql_lex.cc sql_list.cc sql_manager.cc sql_map.cc sql_parse.cc \ sql_prepare.cc sql_derived.cc sql_rename.cc \
- include your .h file in sql/procedure.cc
diff -ruN 5.0/sql/procedure.cc 5.0-myproc/sql/procedure.cc --- 5.0/sql/procedure.cc 2007-08-09 12:11:16.000000000 +0200 +++ 5.0-myproc/sql/procedure.cc 2007-08-09 00:21:04.000000000 +0200 @@ -23,6 +23,7 @@ #include "mysql_priv.h" #include "procedure.h" #include "sql_analyse.h" // Includes procedure +#include "procedure_rownum.h" // Includes procedure #ifdef USE_PROC_RANGE #include "proc_range.h" #endif
- register your procedures init callback in the sql_procs array in sql/procedure.cc
diff -ruN 5.0/sql/procedure.cc 5.0-myproc/sql/procedure.cc
--- 5.0/sql/procedure.cc 2007-08-09 12:11:16.000000000 +0200
+++ 5.0-myproc/sql/procedure.cc 2007-08-09 00:21:04.000000000 +0200
@@ -37,6 +38,7 @@
{ "split_count",proc_count_range_init }, // Internal procedure at TCX
{ "matris_ranges",proc_matris_range_init }, // Internal procedure at TCX
#endif
+ { "rownum", proc_rownum_init }, // Add RowNum column to result
{ "analyse",proc_analyse_init } // Analyse a result
};
- run automake and autoconf to regenerate the Makefile.in
files and the configure script
- run configure or refresh your previous configuration by running config.status
- run make
- if your build fails with don't know how to make xxx.cc required by xxx.o in the libmysqld/ directory you might need to create a symlink from libmysqld/xxx.cc to sql/xxx.cc yourself. The build system is not too clever about creating these symlinks for files added after the first compile ...
[edit] Examples
[edit] PROCEDURE ROWNUM() - adding a RowNum pseudo column to a result set
A patch against current MySQL 5.0 (should work with MySQL 5.1, too).
Example:
mysql> SELECT User, Host FROM mysql.user PROCEDURE ROWNUM();
+------+-----------+--------+ | User | Host | RowNum | +------+-----------+--------+ | root | 127.0.0.1 | 0 | | root | linux | 1 | | root | localhost | 2 | +------+-----------+--------+ 3 rows in set (0.00 sec)
How to apply:
- download the patch
- in the MySQL 5.0 source directory to patch -p1 < proc-rownum-1.0.0.patch
- run automake and autoconf to regenerate configure
- run ./configure as usual or refresh your previous configuration by running ./config.status
- run make and make install to compile and install the server
- restart the server
- try appending PROCEDURE ROWNUM() to a query
[edit] PROCEDURE CALLTRACE() - simple trace of procedure member function calls
A patch against current MySQL 5.0 (should work with MySQL 5.1, too).
This procedure will replace the actual query results with one row per procedure object member function call, so showing the sequence these functions are called on a result set.
Example:
mysql> select * from t2; +------+------+ | i | j | +------+------+ | 1 | 1 | | 2 | 1 | | 2 | 2 | | 3 | 1 | | 3 | 2 | | 3 | 3 | +------+------+ 6 rows in set (0.00 sec) mysql> select i from t2 group by i procedure calltrace(); +----------------+ | Call | +----------------+ | add | | end_group | | send_row | | add | | add | | end_group | | send_row | | add | | add | | add | | end_group | | send_row | | end_of_records | +----------------+ 13 rows in set (0.00 sec)
How to apply: see the PROCEDURE ROWNUM() example abouve, the patch for this procedure needs to be applied in exactly the same way.