List:Internals« Previous MessageNext Message »
From:Patrick Galbraith Date:September 1 2005 2:02am
Subject:bk commit into 5.0 tree (patg:1.1945) BUG#9056
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of patg. When patg does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1945 05/09/01 04:02:31 patg@stripped +1 -0
  BUG #9056, Worklog #2795. Implemented Dumping of routines

  client/mysqldump.c
    1.194 05/09/01 04:02:12 patg@stripped +132 -2
    Bug #9056, Worklog #2795 Add Dumping of routines to mysqldump
    * Added --routines/-R to flag dumping of routines *default is 0, may need 
      to change
    * Added get_routines which is called by dump_all_tables_in_db if opt_routines set
    * Logic to print correct syntax if PROCEDURE or FUNCTION
    * Print out characteristics
    
    I do have a problem with FUNCTIONS. They cannot be enclosed by /*!50003 ... */
    like procedures can. I need to find out what the situation is with this.
    
    Tested with a schema which included triggers, tables, views, functions and 
    procedures, but dumping and restoring. Works nicely!

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	patg
# Host:	radha.local
# Root:	/Users/patg/mysql-build/mysql-5.0

--- 1.193/client/mysqldump.c	2005-07-31 11:55:58 +02:00
+++ 1.194/client/mysqldump.c	2005-09-01 04:02:12 +02:00
@@ -87,7 +87,7 @@
 		opt_single_transaction=0, opt_comments= 0, opt_compact= 0,
 		opt_hex_blob=0, opt_order_by_primary=0, opt_ignore=0,
                 opt_complete_insert= 0, opt_drop_database= 0,
-                opt_dump_triggers= 0;
+                opt_dump_triggers= 0, opt_routines=0;
 static ulong opt_max_allowed_packet, opt_net_buffer_length;
 static MYSQL mysql_connection,*sock=0;
 static my_bool insert_pat_inited=0;
@@ -335,6 +335,9 @@
   {"result-file", 'r',
    "Direct output to a given file. This option should be used in MSDOS, because it prevents new line '\\n' from being converted to '\\r\\n' (carriage return + line feed).",
    0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+  {"routines", 'R', "Dump routine data, CREATE commands are written in the dump.",
+     (gptr*) &opt_routines, (gptr*) &opt_routines, 0, GET_BOOL,
+     NO_ARG, 0, 0, 0, 0, 0, 0},
   {"set-charset", OPT_SET_CHARSET,
    "Add 'SET NAMES default_character_set' to the output. Enabled by default; suppress with --skip-set-charset.",
    (gptr*) &opt_set_charset, (gptr*) &opt_set_charset, 0, GET_BOOL, NO_ARG, 1,
@@ -1168,6 +1171,128 @@
   check_io(xml_file);
 }
 
+/*
+  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);
+}
 
 /*
   getTableStructure -- retrievs database structure, prints out corresponding
@@ -1176,7 +1301,6 @@
   RETURN
     number of fields in table, 0 if error
 */
-
 static uint get_table_structure(char *table, char *db)
 {
   MYSQL_RES  *tableRes;
@@ -2368,6 +2492,12 @@
       my_free(order_by, MYF(MY_ALLOW_ZERO_PTR));
       order_by= 0;
     }
+  }
+  /* if the user has selected --routines (wants stored procs) */
+  if (opt_routines &&
+      mysql_get_server_version(sock) >= 50009)
+  {
+    int retval= get_routines(database);
   }
   if (opt_xml)
   {
Thread
bk commit into 5.0 tree (patg:1.1945) BUG#9056Patrick Galbraith1 Sep