WL#2795: mysqldump option to dump routines

See BUG#9056

there're different tasks to solve:

   - backup. in this case sql dump should be able to restore db to the
     exact state it was before

   - migrate. There were bugreports that views are dumped together with
     the db name, and they cannot be restored to another db. The same
     arguing could be applied to the routine dump if it will include
     definer.

 problems:

   - a mere user cannot access mysql.proc table to get routine data,
     though he can use SHOW CREATE PROCEDURE|FUNCTION and
     SHOW PROCEDURE|FUNCTION STATUS

   - a mere user cannot insert into mysql.proc, he can only use
     CREATE PROCEDURE|FUNCTION, and this way he can only create routines
     with himself as a definer. (good for "migrate" task, bad for
     "backup")

Solution:

  command line option --routines

  if it's specified, mysqldump uses SHOW commands are used to get
  routine data, CREATE commands are written in the dump. No need to
  bother about timestamps or definer. We assume "mere user" mode and
  "migrate" task.

  if it's not specified, mysqldump does not dump routines or does
  anything special about them at all. User can dump mysql.proc table
  directly (if he has enough privileges to access it), and INSERT will
  go into the dump to restore routines - all the routine attributes,
  including timestamps and definer will be restored. It corresponds to the
  "DBA" mode and "backup" task.

The difference between these modes and tasks is not exactly trivial, so
as a part of this WL, documentation team should not only get a
description of what new command line option does ("dumps routines"), but
also when, how, and who should use it (these "modes" and "tasks").

Todos:

* Add opt_routines to get_options
* Add get_routines function which gets routine body, parameters, return values, 
  characteristics, and other fields that are needed to print out routines
* Logic to deal with FUNCTION or PROCEDURES (syntax of)
* Logic to deal with characteristics
* Add logic that calls get_routines from dump_all_tables_in_db when opt_routines
is set


Problems/Issues:

* Functions give syntax errors when inclosed in /*!5000whatever ... */ comments:

mysql> /*!50003 create FUNCTION foo_func(a int) RETURNS INT RETURN a+a */ //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '/' at
line 1


mysql> create FUNCTION foo_func(a int) RETURNS INT RETURN a+a //
Query OK, 0 rows affected (0.00 sec)


Procs, no problem:

mysql> /*!50003 create PROCEDURE foo_proc(INOUT a INT) BEGIN select a+a into a;
END */ //
Query OK, 0 rows affected (0.00 sec)


So, I have designed it so FUNCTIONs are not within /* for now. This can be easily
modified.

Copyright (c) 2001-2007 by MySQL AB. All rights reserved.

Added call to dump_all_tables_in_db:

/* if the user has selected --routines (wants stored procs) */
  if (opt_routines &&
      mysql_get_server_version(sock) >= 50009)
  {
    int retval= get_routines(database);
  }


Get routines implementation:

/*
  get_routines -- retrievs list of routines for a given db, and prints out 
  the CREATE PROCEDURE definition into the output (the dump).

  This function has logic to print the appropriate syntax depending on whether
  this is a procedure or functions

  RETURN
    1 succes, 0 if error
*/
static uint get_routines (char *db)
{
  MYSQL_RES  *routine_res;
  MYSQL_ROW  row;
  char       query_buff[512];
  FILE       *sql_file = md_result_file;
  /*
    18, length of "NOT DETERMINISTC", 73 length of 64+"comment ", and newline,
    and possible 's
  */
  char name_buff[NAME_LEN+3], deterministic[18], comment[75], comment_buff[64],
       data_access_buff[18];

  DBUG_ENTER("get_routines");
  DBUG_PRINT("enter", ("db: %s", db));

  mysql_query(sock,"LOCK TABLES mysql.proc WRITE");

  /* nice comments */
  if (!opt_xml && opt_comments)
    fprintf(sql_file, "\n--\n-- Dumping routines for database %s\n--\n", db);

  /* obtain the list of procedures from mysql.proc table */
  my_snprintf(query_buff, sizeof(query_buff),
              "SELECT\n\
sql_mode, name, type, language, is_deterministic, param_list,body, returns,\n\
  sql_data_access, is_deterministic, comment\n\
FROM mysql.proc\n\
WHERE db=%s", quote_for_like(db, name_buff));

  if (mysql_query_with_error_report(sock, &routine_res, query_buff))
  {
    if (path)
      my_fclose(sql_file, MYF(MY_WME));
    safe_exit(EX_MYSQLERR);
    DBUG_RETURN(0);
  }
  /* set the delimiter to // for routine def */
  if (mysql_num_rows(routine_res))
    fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n\
DELIMITER //;\n");

  while ((row=mysql_fetch_row(routine_res)))
  {
    fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\"*/ //\n", row[0] /*
sql_mode */);

    /* set deterministic or not */
    if (!strcmp(row[4], "YES"))
      sprintf(deterministic, "DETERMINISTIC");
    else
      sprintf(deterministic, "NOT DETERMINISTIC");

    /* set the comment or empty string */
    if (strlen(row[10]))
    {
      mysql_real_escape_string(sock, comment_buff, row[10], strlen(row[10]));
      sprintf(comment,"COMMENT '%s'\n", comment_buff);
    }
    else
      sprintf(comment, "");
 /* if I had a way to do row[8] =~ s/_/ /g :) */
    if (!strcmp(row[8], "CONTAINS_SQL"))
      sprintf(data_access_buff, "CONTAINS SQL");
    else if (!strcmp(row[8], "NO_SQL"))
      sprintf(data_access_buff, "NO SQL");
    else if (!strcmp(row[8], "READS_SQL_DATA"))
      sprintf(data_access_buff, "READS SQL DATA");
    else
      sprintf(data_access_buff, "MODIFIES SQL DATA");

    /* this is a stored proc */
    if (!strcmp(row[2],"PROCEDURE"))
    {
      fprintf(sql_file, "/*!50003 CREATE PROCEDURE %s (%s)\n\
LANGUAGE %s %s %s\n%s%s */ //\n\n",
              quote_name(row[1], name_buff, 0), /* name */
              row[5], /* param list */
              row[3], /* language */
              deterministic, /* deterministic or not */
              data_access_buff, /* sql_data_access */
              comment,
              row[6] /* body */);
    }
    /* else a function */
else
    {
#if FIGURE_OUT_COMMENTS_FOR_FUNCTIONS
      fprintf(sql_file, "/*!50003 CREATE FUNCTION %s (%s) RETURNS %s\n\
LANGUAGE %s %s %s\n%s%s; */ //\n\n",
#endif
      fprintf(sql_file, "CREATE FUNCTION %s (%s) RETURNS %s\n\
LANGUAGE %s %s %s\n%s%s; //\n\n",
              quote_name(row[1], name_buff, 0), /* name */
              row[5], /* param list */
              row[7], /* returns */
              row[3], /* language */
              deterministic, /* deterministic or not */
              data_access_buff, /* sql_data_access */
              comment,
              row[6] /* body */ );
    }
  }
  /* set the delimiter back to ';' */
  if (mysql_num_rows(routine_res))
    fprintf(sql_file,
"DELIMITER ;//\n\
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;");
  mysql_free_result(routine_res);
  mysql_query(sock,"UNLOCK TABLES mysql.proc");
  DBUG_RETURN(1);
}