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

2 files changed, 296 insertions, 5 deletions

diff --git a/tests/Makefile.am b/tests/Makefile.am
index 5d0e4627b69..0a86b330f18 100644
--- a/tests/Makefile.am
+++ b/tests/Makefile.am
@@ -35,7 +35,7 @@ INCLUDES =		-I$(top_srcdir)/include $(openssl_includes)
 LIBS =			@CLIENT_LIBS@
 LDADD =			@CLIENT_EXTRA_LDFLAGS@ ../libmysql/libmysqlclient.la
 client_test_LDADD=      $(LDADD) $(CXXLDFLAGS)
-client_test_SOURCES=    client_test.c
+client_test_SOURCES=    client_test.cc
 insert_test_DEPENDENCIES=	$(LIBRARIES) $(pkglib_LTLIBRARIES)
 select_test_DEPENDENCIES=	$(LIBRARIES) $(pkglib_LTLIBRARIES)
 
diff --git a/tests/client_test.c b/tests/client_test.cc
index 13f5a3ac852..36adbc381e1 100644
--- a/tests/client_test.c
+++ b/tests/client_test.cc
@@ -693,6 +693,240 @@ static void client_use_result()
 }
 
 
+/*
+  Accepts arbitrary number of queries and runs them against the database.
+  Used to fill tables for each test.
+*/
+
+void fill_tables(const char **query_list, unsigned query_count)
+{
+  int rc;
+  for (const char **query= query_list; query < query_list + query_count;
+       ++query)
+  {
+    rc= mysql_query(mysql, *query);
+    if (rc)
+    {
+      fprintf(stderr,
+              "fill_tables failed: query is\n"
+              "%s,\n"
+              "error: %s\n", *query, mysql_error(mysql));
+      exit(1);
+    }
+  }
+}
+
+/*
+  All state of fetch from one statement: statement handle, out buffers,
+  fetch position.
+  See fetch_n for for the only use case.
+*/
+
+struct Stmt_fetch
+{
+  enum { MAX_COLUMN_LENGTH= 255 };
+
+  Stmt_fetch() {}
+  ~Stmt_fetch();
+
+  void init(unsigned stmt_no_arg, const char *query_arg);
+  int fetch_row();
+
+  const char *query;
+  unsigned stmt_no;
+  MYSQL_STMT *stmt;
+  bool is_open;
+  MYSQL_BIND *bind_array;
+  char **out_data;
+  unsigned long *out_data_length;
+  unsigned column_count;
+  unsigned row_count;
+};
+
+/*
+  Create statement handle, prepare it with statement, execute and allocate
+  fetch buffers.
+*/
+
+void Stmt_fetch::init(unsigned stmt_no_arg, const char *query_arg)
+{
+  unsigned long type= CURSOR_TYPE_READ_ONLY;
+  int rc;
+  unsigned i;
+  MYSQL_RES *metadata;
+
+  /* Save query and statement number for error messages */
+  stmt_no= stmt_no_arg;
+  query= query_arg;
+
+  stmt= mysql_stmt_init(mysql);
+
+  rc= mysql_stmt_prepare(stmt, query, strlen(query));
+  if (rc)
+  {
+    fprintf(stderr,
+            "mysql_stmt_prepare of stmt %d failed:\n"
+            "query: %s\n"
+            "error: %s\n",
+            stmt_no, query, mysql_stmt_error(stmt));
+    exit(1);
+  }
+
+  /*
+    The attribute is sent to server on execute and asks to open read-only
+    for result set
+  */
+  mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) &type);
+
+  rc= mysql_stmt_execute(stmt);
+  if (rc)
+  {
+    fprintf(stderr,
+            "mysql_stmt_execute of stmt %d failed:\n"
+            "query: %s\n"
+            "error: %s\n",
+            stmt_no, query, mysql_stmt_error(stmt));
+    exit(1);
+  }
+
+  /* Find out total number of columns in result set */
+  metadata= mysql_stmt_result_metadata(stmt);
+  column_count= mysql_num_fields(metadata);
+  mysql_free_result(metadata);
+
+  /*
+    Now allocate bind handles and buffers for output data:
+    calloc memory to reduce number of MYSQL_BIND members we need to
+    set up.
+  */
+
+  bind_array= (MYSQL_BIND *) calloc(1, sizeof(MYSQL_BIND) * column_count);
+  out_data= (char **) calloc(1, sizeof(*out_data) * column_count);
+  out_data_length= (unsigned long *) calloc(1,
+                                       sizeof(*out_data_length) * column_count);
+
+  for (i= 0; i < column_count; ++i)
+  {
+    out_data[i]= (char *) calloc(1, MAX_COLUMN_LENGTH);
+    bind_array[i].buffer_type= MYSQL_TYPE_STRING;
+    bind_array[i].buffer= out_data[i];
+    bind_array[i].buffer_length= MAX_COLUMN_LENGTH;
+    bind_array[i].length= out_data_length + i;
+  }
+
+  mysql_stmt_bind_result(stmt, bind_array);
+
+  row_count= 0;
+  is_open= true;
+
+  /* Ready for reading rows */
+}
+
+
+/* Fetch and print one row from cursor */
+
+int Stmt_fetch::fetch_row()
+{
+  int rc;
+  unsigned i;
+
+  if ((rc= mysql_stmt_fetch(stmt)) == 0)
+  {
+    ++row_count;
+    printf("Stmt %d fetched row %d:\n", stmt_no, row_count);
+    for (i= 0; i < column_count; ++i)
+    {
+      out_data[i][out_data_length[i]]= '\0';
+      printf("column %d: %s\n", i+1, out_data[i]);
+    }
+  }
+  else
+    is_open= false;
+  return rc;
+}
+
+
+Stmt_fetch::~Stmt_fetch()
+{
+  unsigned i;
+
+  for (i= 0; i < column_count; ++i)
+    free(out_data[i]);
+  free(out_data);
+  free(bind_array);
+  mysql_stmt_close(stmt);
+}
+
+/* We need these to compile without libstdc++ */
+
+void *operator new[] (size_t sz)
+{
+  return (void *) malloc (sz ? sz : 1);
+}
+
+void operator delete[] (void *ptr) throw ()
+{
+  if (ptr)
+    free(ptr);
+}
+
+/*
+  For given array of queries, open query_count cursors and fetch
+  from them in simultaneous manner.
+  In case there was an error in one of the cursors, continue
+  reading from the rest.
+*/
+
+bool fetch_n(const char **query_list, unsigned query_count)
+{
+  unsigned open_statements= query_count;
+  unsigned i;
+  int rc, error_count= 0;
+  Stmt_fetch *stmt_array= new Stmt_fetch[query_count];
+  Stmt_fetch *stmt;
+
+  for (i= 0; i < query_count; ++i)
+  {
+    /* Init will exit(1) in case of error */
+    stmt_array[i].init(i, query_list[i]);
+  }
+
+  while (open_statements)
+  {
+    for (stmt= stmt_array; stmt < stmt_array + query_count; ++stmt)
+    {
+      if (stmt->is_open && (rc= stmt->fetch_row()))
+      {
+        --open_statements;
+        /*
+          We try to fetch from the rest of the statements in case of
+          error
+        */
+        if (rc != MYSQL_NO_DATA)
+        {
+          fprintf(stderr,
+                  "Got error reading rows from statement %d,\n"
+                  "query is: %s,\n"
+                  "error message: %s", stmt - stmt_array, stmt->query,
+                  mysql_stmt_error(stmt->stmt));
+          ++error_count;
+        }
+      }
+    }
+  }
+  if (error_count)
+    fprintf(stderr, "Fetch FAILED");
+  else
+  {
+    unsigned total_row_count= 0;
+    for (stmt= stmt_array; stmt < stmt_array + query_count; ++stmt)
+      total_row_count+= stmt->row_count;
+    printf("Success, total rows fetched: %d\n", total_row_count);
+  }
+  delete [] stmt_array;
+  return error_count != 0;
+}
+
 /* Separate thread query to test some cases */
 
 static my_bool thread_query(char *query)
@@ -10043,7 +10277,7 @@ static void test_view()
   int rc, i;
   MYSQL_BIND      bind[1];
   char            str_data[50];
-  long            length = 0L;
+  ulong           length = 0L;
   long            is_null = 0L;
   const char *query=
     "SELECT COUNT(*) FROM v1 WHERE `SERVERNAME`=?";
@@ -10141,7 +10375,7 @@ static void test_view_2where()
   int rc, i;
   MYSQL_BIND      bind[8];
   char            parms[8][100];
-  long            length[8];
+  ulong           length[8];
   const char *query= "SELECT `RELID` ,`REPORT` ,`HANDLE` ,`LOG_GROUP` ,`USERNAME` ,`VARIANT` ,`TYPE` ,`VERSION` ,`ERFDAT` ,`ERFTIME` ,`ERFNAME` ,`AEDAT` ,`AETIME` ,`AENAME` ,`DEPENDVARS` ,`INACTIVE` FROM `V_LTDX` WHERE `MANDT` = ? AND `RELID` = ? AND `REPORT` = ? AND `HANDLE` = ? AND `LOG_GROUP` = ? AND `USERNAME` IN ( ? , ? ) AND `TYPE` = ?";
 
   myheader("test_view_2where");
@@ -10189,7 +10423,7 @@ static void test_view_star()
   int rc, i;
   MYSQL_BIND      bind[8];
   char            parms[8][100];
-  long            length[8];
+  ulong           length[8];
   const char *query= "SELECT * FROM vt1 WHERE a IN (?,?)";
 
   myheader("test_view_star");
@@ -10338,7 +10572,7 @@ static void test_view_insert_fields()
 {
   MYSQL_STMT	*stmt;
   char		parm[11][1000];
-  long		l[11];
+  ulong         l[11];
   int		rc, i;
   MYSQL_BIND	bind[11];
   const char    *query= "INSERT INTO `v1` ( `K1C4` ,`K2C4` ,`K3C4` ,`K4N4` ,`F1C4` ,`F2I4` ,`F3N5` ,`F7F8` ,`F6N4` ,`F5C8` ,`F9D8` ) VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )";
@@ -10390,6 +10624,61 @@ static void test_view_insert_fields()
 
 }
 
+
+static void test_basic_cursors()
+{
+  myheader("test_basic_cursors");
+  const char *basic_tables[]=
+  {
+    "DROP TABLE IF EXISTS t1, t2",
+
+    "CREATE TABLE t1 "
+    "(id INTEGER NOT NULL PRIMARY KEY, "
+    " name VARCHAR(20) NOT NULL)",
+
+    "INSERT INTO t1 (id, name) VALUES "
+    "  (2, 'Ja'), (3, 'Ede'), "
+    "  (4, 'Haag'), (5, 'Kabul'), "
+    "  (6, 'Almere'), (7, 'Utrecht'), "
+    "  (8, 'Qandahar'), (9, 'Amsterdam'), "
+    "  (10, 'Amersfoort'), (11, 'Constantine')",
+
+    "CREATE TABLE t2 "
+    "(id INTEGER NOT NULL PRIMARY KEY, "
+    " name VARCHAR(20) NOT NULL)",
+
+    "INSERT INTO t2 (id, name) VALUES "
+    "  (4, 'Guam'), (5, 'Aruba'), "
+    "  (6, 'Angola'), (7, 'Albania'), "
+    "  (8, 'Anguilla'), (9, 'Argentina'), "
+    "  (10, 'Azerbaijan'), (11, 'Afghanistan'), "
+    "  (12, 'Burkina Faso'), (13, 'Faroe Islands')"
+  };
+
+  fill_tables(basic_tables, sizeof(basic_tables)/sizeof(*basic_tables));
+
+  const char *queries[]=
+  {
+    "SELECT * FROM t1",
+    "SELECT * FROM t2"
+  };
+
+  fetch_n(queries, sizeof(queries)/sizeof(*queries));
+}
+
+
+static void test_cursors_with_union()
+{
+  myheader("test_cursors_with_union");
+
+  const char *queries[]=
+  {
+    "SELECT t1.name FROM t1 UNION SELECT t2.name FROM t2",
+    "SELECT t1.id FROM t1 WHERE t1.id < 5"
+  };
+  fetch_n(queries, sizeof(queries)/sizeof(*queries));
+}
+
 /*
   Read and parse arguments and MySQL options from my.cnf
 */
@@ -10694,6 +10983,8 @@ int main(int argc, char **argv)
     test_view_insert();     /* inserting in VIEW without field list */
     test_left_join_view();  /* left join on VIEW with WHERE condition */
     test_view_insert_fields(); /* insert into VIOEW with fields list */
+    test_basic_cursors();
+    test_cursors_with_union();
     /*
       XXX: PLEASE RUN THIS PROGRAM UNDER VALGRIND AND VERIFY THAT YOUR TEST
       DOESN'T CONTAIN WARNINGS/ERRORS BEFORE YOU PUSH.