Port of cursors to be pushed into 5.0 tree:

- client side part is simple and may be considered stable
- server side part now just joggles with THD state to save execution
  state and has no additional locking wisdom.
  Lot's of it are to be rewritten.


include/mysql.h:
  Cursor patch to push into the main tree, client library part (considered 
  stable):
  - new statement attribute STMT_ATTR_CURSOR_TYPE
  - MYSQL_STMT::flags to store statement cursor type
  - MYSQL_STMT::server_status to store server status (i. e. if the server
  was able to open a cursor for this query).
include/mysql_com.h:
  Cursor patch to push into the main tree, client library part (considered 
  stable):
  - new COMmand, COM_FETCH, to fetch K rows from read-only cursor.
    By design should support scrollable cursors as well.
  - a few new server statuses:
    SERVER_STATUS_CURSOR_EXISTS is sent by server in reply to COM_EXECUTE,
    when cursor was successfully opened for this query
    SERVER_STATUS_LAST_ROW_SENT is sent along with the last row to prevent one
    more round trip just for finding out that all rows were fetched from 
    this cursor (this is server mem savier also).
  - and finally, all possible values of STMT_ATTR_CURSOR_TYPE, 
    while now we support only CURSORT_TYPE_NO_CURSOR and 
    CURSOR_TYPE_READ_ONLY
libmysql/libmysql.c:
  Cursor patch to push into the main tree, client library part (considered 
  stable):
  - simple additions to mysql_stmt_fetch implementation to read data 
    from an opened cursor: we can read up to iteration count rows per
    one request; read rows are buffered in the same way as rows of
    mysql_stmt_store_result.
  - now send stmt->flags to server to let him now if we wish to have 
    a cursor for this statement.
  - support for setting/getting statement cursor type.
libmysqld/examples/Makefile.am:
  Testing cursors was originally implemented in C++. Now when these tests
  go into client_test, it's time to convert it to C++ as well.
libmysqld/lib_sql.cc:
  - cleanup: send_fields flags are now named.
sql/ha_innodb.cc:
  - cleanup: send_fields flags are now named.
sql/mysql_priv.h:
  - cursors support: declaration for server-side handler of COM_FETCH
sql/protocol.cc:
  - cleanup: send_fields flags are now named.
  - we can't anymore assert that field_types[field_pos] is sensible:
    if we have COM_EXCUTE(stmt1), COM_EXECUTE(stmt2), COM_FETCH(stmt1)
    field_types[field_pos] will point to fields of stmt2.
sql/protocol.h:
  - cleanup: send_fields flag_s_ are now named.
sql/protocol_cursor.cc:
  - cleanup: send_fields flags are now named.
sql/repl_failsafe.cc:
  - cleanup: send_fields flags are now named.
sql/slave.cc:
  - cleanup: send_fields flags are now named.
sql/sp.cc:
  - cleanup: send_fields flags are now named.
sql/sp_head.cc:
  - cleanup: send_fields flags are now named.
sql/sql_acl.cc:
  - cleanup: send_fields flags are now named.
sql/sql_class.cc:
  - cleanup: send_fields flags are now named.
sql/sql_class.h:
  - cleanup: send_fields flags are now named.
sql/sql_error.cc:
  - cleanup: send_fields flags are now named.
sql/sql_handler.cc:
  - cleanup: send_fields flags are now named.
sql/sql_help.cc:
  - cleanup: send_fields flags are now named.
sql/sql_parse.cc:
  Server side support for cursors:
  - handle COM_FETCH
  - enforce assumption that whenever we free thd->free_list, 
    we reset it to zero. This way it's much easier to handle free_list
    in prepared statements implementation.
sql/sql_prepare.cc:
  Server side support for cursors:
  - implementation of mysql_stmt_fetch (fetch some rows from open cursor).
  - management of cursors memory is quite tricky now.
  - execute_stmt can't be reused anymore in mysql_stmt_execute and 
    mysql_sql_stmt_execute
sql/sql_repl.cc:
  - cleanup: send_fields flags are now named.
sql/sql_select.cc:
  Server side support for cursors:
  - implementation of Cursor::open, Cursor::fetch (buggy when it comes to
    non-equi joins), cursor cleanups.
  - -4 -3 -0 constants indicating return value of sub_select and end_send are
    to be renamed to something more readable:
    it turned out to be not so simple, so it should come with the other patch.
sql/sql_select.h:
  Server side support for cursors:
  - declaration of Cursor class.
  - JOIN::fetch_limit contains runtime value of rows fetched via cursor.
sql/sql_show.cc:
  - cleanup: send_fields flags are now named.
sql/sql_table.cc:
  - cleanup: send_fields flags are now named.
sql/sql_union.cc:
  - if there was a cursor, don't cleanup unit: we'll need it to fetch
    the rest of the rows.
tests/Makefile.am:
  Now client_test is in C++.
tests/client_test.cc:
  A few elementary tests for cursors.
BitKeeper/etc/ignore:
  Added libmysqld/examples/client_test.cc to the ignore list

1 files changed, 138 insertions, 16 deletions

diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index ed5e75a3622..9d9a0dd73e2 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -75,6 +75,8 @@ Long data handling:
 #ifdef EMBEDDED_LIBRARY
 /* include MYSQL_BIND headers */
 #include <mysql.h>
+#else
+#include <mysql_com.h>
 #endif
 
 /******************************************************************************
@@ -107,7 +109,7 @@ public:
 };
 
 static void execute_stmt(THD *thd, Prepared_statement *stmt,
-                         String *expanded_query, bool set_context);
+                         String *expanded_query);
 
 /******************************************************************************
   Implementation
@@ -166,7 +168,8 @@ static bool send_prep_stmt(Prepared_statement *stmt, uint columns)
   return my_net_write(net, buff, sizeof(buff)) || 
          (stmt->param_count &&
           stmt->thd->protocol_simple.send_fields((List<Item> *)
-                                                 &stmt->lex->param_list, 0)) ||
+                                                 &stmt->lex->param_list,
+                                                 Protocol::SEND_EOF)) ||
          net_flush(net);
   return 0;
 }
@@ -1098,7 +1101,8 @@ static int mysql_test_select(Prepared_statement *stmt,
     if (!text_protocol)
     {
       if (send_prep_stmt(stmt, lex->select_lex.item_list.elements) ||
-        thd->protocol_simple.send_fields(&lex->select_lex.item_list, 0)
+        thd->protocol_simple.send_fields(&lex->select_lex.item_list,
+                                         Protocol::SEND_EOF)
 #ifndef EMBEDDED_LIBRARY
           || net_flush(&thd->net)
 #endif
@@ -1476,6 +1480,12 @@ static int send_prepare_results(Prepared_statement *stmt, bool text_protocol)
   case SQLCOM_SHOW_GRANTS:
   case SQLCOM_DROP_TABLE:
   case SQLCOM_RENAME_TABLE:
+  case SQLCOM_ALTER_TABLE:
+  case SQLCOM_COMMIT:
+  case SQLCOM_CREATE_INDEX:
+  case SQLCOM_DROP_INDEX:
+  case SQLCOM_ROLLBACK:
+  case SQLCOM_TRUNCATE:
     break;
 
   default:
@@ -1756,6 +1766,7 @@ static void reset_stmt_params(Prepared_statement *stmt)
 void mysql_stmt_execute(THD *thd, char *packet, uint packet_length)
 {
   ulong stmt_id= uint4korr(packet);
+  ulong flags= (ulong) ((uchar) packet[4]);
   /*
     Query text for binary log, or empty string if the query is not put into
     binary log.
@@ -1782,6 +1793,28 @@ void mysql_stmt_execute(THD *thd, char *packet, uint packet_length)
     DBUG_VOID_RETURN;
   }
 
+  if (flags & (ulong) CURSOR_TYPE_READ_ONLY)
+  {
+    if (stmt->lex->result)
+    {
+      /*
+        If lex->result is set in the parser, this is not a SELECT
+        statement: we can't open a cursor for it.
+      */
+      flags= 0;
+    }
+    else
+    {
+      if (!stmt->cursor &&
+          !(stmt->cursor= new (&stmt->mem_root) Cursor()))
+      {
+        send_error(thd, ER_OUT_OF_RESOURCES);
+        DBUG_VOID_RETURN;
+      }
+      /* If lex->result is set, mysql_execute_command will use it */
+      stmt->lex->result= &stmt->cursor->result;
+    }
+  }
 #ifndef EMBEDDED_LIBRARY
   if (stmt->param_count)
   {
@@ -1800,16 +1833,55 @@ void mysql_stmt_execute(THD *thd, char *packet, uint packet_length)
   if (stmt->param_count && stmt->set_params_data(stmt, &expanded_query))
     goto set_params_data_err;
 #endif
+  thd->stmt_backup.set_statement(thd);
+  thd->set_statement(stmt);
   thd->current_arena= stmt;
+  reset_stmt_for_execute(thd, stmt->lex);
+  /* From now cursors assume that thd->mem_root is clean */
+  if (expanded_query.length() &&
+      alloc_query(thd, (char *)expanded_query.ptr(),
+                  expanded_query.length()+1))
+  {
+    my_error(ER_OUTOFMEMORY, 0, expanded_query.length());
+    goto err;
+  }
+
   thd->protocol= &thd->protocol_prep;           // Switch to binary protocol
-  execute_stmt(thd, stmt, &expanded_query, true);
+  if (!(specialflag & SPECIAL_NO_PRIOR))
+    my_pthread_setprio(pthread_self(),QUERY_PRIOR);
+  mysql_execute_command(thd);
+  if (!(specialflag & SPECIAL_NO_PRIOR))
+    my_pthread_setprio(pthread_self(), WAIT_PRIOR);
   thd->protocol= &thd->protocol_simple;         // Use normal protocol
+
+  if (flags & (ulong) CURSOR_TYPE_READ_ONLY)
+  {
+    if (stmt->cursor->is_open())
+      stmt->cursor->init_from_thd(thd);
+    thd->set_item_arena(&thd->stmt_backup);
+  }
+  else
+  {
+    thd->lex->unit.cleanup();
+    cleanup_items(stmt->free_list);
+    reset_stmt_params(stmt);
+    close_thread_tables(thd);                   /* to close derived tables */
+    /*
+      Free items that were created during this execution of the PS by
+      query optimizer.
+    */
+    free_items(thd->free_list);
+    thd->free_list= 0;
+  }
+
+  thd->set_statement(&thd->stmt_backup);
   thd->current_arena= 0;
   DBUG_VOID_RETURN;
 
 set_params_data_err:
   reset_stmt_params(stmt);
   my_error(ER_WRONG_ARGUMENTS, MYF(0), "mysql_stmt_execute");
+err:
   send_error(thd);
   DBUG_VOID_RETURN;
 }
@@ -1845,7 +1917,6 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name)
     DBUG_VOID_RETURN;
   }
 
-  thd->free_list= NULL;
   thd->stmt_backup.set_statement(thd);
   thd->set_statement(stmt);
   if (stmt->set_params_from_vars(stmt,
@@ -1856,7 +1927,7 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name)
     send_error(thd);
   }
   thd->current_arena= stmt;
-  execute_stmt(thd, stmt, &expanded_query, false);
+  execute_stmt(thd, stmt, &expanded_query);
   thd->current_arena= 0;
   DBUG_VOID_RETURN;
 }
@@ -1872,20 +1943,13 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name)
                      placeholders replaced with actual values. Otherwise empty
                      string.
   NOTES
-  Caller must set parameter values and thd::protocol.
-  thd->free_list is assumed to be garbage.
+    Caller must set parameter values and thd::protocol.
 */
 
 static void execute_stmt(THD *thd, Prepared_statement *stmt,
-                         String *expanded_query, bool set_context)
+                         String *expanded_query)
 {
   DBUG_ENTER("execute_stmt");
-  if (set_context)
-  {
-    thd->free_list= NULL;
-    thd->stmt_backup.set_statement(thd);
-    thd->set_statement(stmt);
-  }
   reset_stmt_for_execute(thd, stmt->lex);
 
   if (expanded_query->length() &&
@@ -1899,22 +1963,77 @@ static void execute_stmt(THD *thd, Prepared_statement *stmt,
   if (!(specialflag & SPECIAL_NO_PRIOR))
     my_pthread_setprio(pthread_self(),QUERY_PRIOR);
   mysql_execute_command(thd);
-  thd->lex->unit.cleanup();
   if (!(specialflag & SPECIAL_NO_PRIOR))
     my_pthread_setprio(pthread_self(), WAIT_PRIOR);
 
+  thd->lex->unit.cleanup();
   cleanup_items(stmt->free_list);
   reset_stmt_params(stmt);
   close_thread_tables(thd);                    // to close derived tables
   thd->set_statement(&thd->stmt_backup);
   /* Free Items that were created during this execution of the PS. */
   free_items(thd->free_list);
+  /*
+    In the rest of prepared statements code we assume that free_list
+    never points to garbage: keep this predicate true.
+  */
   thd->free_list= 0;
   DBUG_VOID_RETURN;
 }
 
 
 /*
+  COM_FETCH handler: fetches requested amount of rows from cursor
+  SYNOPSIS
+*/
+
+void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length)
+{
+  /* assume there is always place for 8-16 bytes */
+  ulong stmt_id= uint4korr(packet);
+  ulong num_rows= uint4korr(packet+=4);
+  Statement *stmt;
+  int error;
+
+  DBUG_ENTER("mysql_stmt_fetch");
+
+  if (!(stmt= thd->stmt_map.find(stmt_id)) ||
+      !stmt->cursor ||
+      !stmt->cursor->is_open())
+  {
+    my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), stmt_id, "fetch");
+    send_error(thd);
+    DBUG_VOID_RETURN;
+  }
+
+  thd->stmt_backup.set_statement(thd);
+  thd->stmt_backup.set_item_arena(thd);
+  thd->set_statement(stmt);
+  stmt->cursor->init_thd(thd);
+
+  if (!(specialflag & SPECIAL_NO_PRIOR))
+    my_pthread_setprio(pthread_self(), QUERY_PRIOR);
+
+  thd->protocol= &thd->protocol_prep;		// Switch to binary protocol
+  error= stmt->cursor->fetch(num_rows);
+  thd->protocol= &thd->protocol_simple;         // Use normal protocol
+
+  if (!(specialflag & SPECIAL_NO_PRIOR))
+    my_pthread_setprio(pthread_self(), WAIT_PRIOR);
+
+  /* Restore THD state */
+  stmt->cursor->reset_thd(thd);
+  thd->set_statement(&thd->stmt_backup);
+  thd->set_item_arena(&thd->stmt_backup);
+
+  if (error && error != -4)
+    send_error(thd, ER_OUT_OF_RESOURCES);
+
+  DBUG_VOID_RETURN;
+}
+
+
+/*
   Reset a prepared statement in case there was a recoverable error.
   SYNOPSIS
     mysql_stmt_reset()
@@ -2084,8 +2203,11 @@ void Prepared_statement::setup_set_params()
   }
 }
 
+
 Prepared_statement::~Prepared_statement()
 {
+  if (cursor)
+    cursor->Cursor::~Cursor();
   free_items(free_list);
 }