List:Internals« Previous MessageNext Message »
From:Patrick Galbraith Date:September 3 2005 11:35pm
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/04 01:34:58 patg@stripped +3 -0
  BUG #9056 Added --routines flag to mysqldump to enable mysqldump to be able
  to dump PROCEDURES and FUNCTIONS.

  mysql-test/t/mysqldump.test
    1.56 05/09/04 01:34:44 patg@stripped +81 -0
    BUG #9056 New tests for mysqldump --routines

  mysql-test/r/mysqldump.result
    1.62 05/09/04 01:34:43 patg@stripped +58 -0
    BUG #9056 new test results for mysqldump --routines

  client/mysqldump.c
    1.196 05/09/04 01:34:43 patg@stripped +127 -2
    BUG #9056, added dumping of functions/procs to mysqldump
    - added opt_routines (--routines/-R)
    - added dump_routines_for_db, function that dumps proc and funcs
    - added calling of dump_routines to dump_all_tables_in_db and dump_selected_tables

# 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.bug9056

--- 1.195/client/mysqldump.c	2005-08-26 14:45:19 +02:00
+++ 1.196/client/mysqldump.c	2005-09-04 01:34:43 +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;
@@ -339,6 +339,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 routines FUNCTIONS and PROCEDURES.",
+     (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,
@@ -600,6 +603,9 @@
 				    MYF(MY_WME))))
       exit(1);
     break;
+  case 'R':
+    opt_routines= 1;
+    break;
   case 'W':
 #ifdef __WIN__
     opt_protocol = MYSQL_PROTOCOL_PIPE;
@@ -1157,7 +1163,7 @@
   uint i;
   MYSQL_FIELD *field;
   ulong *lengths= mysql_fetch_lengths(tableRes);
-  
+
   fprintf(xml_file, "\t\t<%s", row_name);
   check_io(xml_file);
   mysql_field_seek(tableRes, 0);
@@ -1177,6 +1183,112 @@
   check_io(xml_file);
 }
 
+/*
+  dump_routines_for_db
+  -- 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 0 succes, 1 if error
+*/
+
+static uint dump_routines_for_db (char *db)
+{
+  MYSQL_RES  *routine_res= NULL;
+  MYSQL_RES  *routine_list_res= NULL;
+  MYSQL_ROW  row, routine_list_row;
+  char       query_buff[512], routine_type[10];
+  char       db_name_buff[NAME_LEN+3], name_buff[NAME_LEN+3];
+  char       *routine_name;
+  char       **routine_list;
+  int        i;
+  FILE       *sql_file = md_result_file;
+
+  DBUG_ENTER("dump_routines_for_db");
+
+  mysql_real_escape_string(sock, db_name_buff, db, strlen(db));
+  DBUG_PRINT("enter", ("db: '%s'", db_name_buff));
+
+  /* nice comments */
+  if (opt_comments)
+    fprintf(sql_file, "\n--\n-- Dumping routines for database '%s'\n--\n", db);
+  mysql_query(sock, "LOCK TABLES mysql.proc READ");
+
+  /* 0, retrieve and dump functions, 1, procedures */
+  for (i=0; i <= 1; i++)
+  {
+    my_snprintf(routine_type, sizeof(routine_type),
+                  "%s", i == 0 ? "FUNCTION" : "PROCEDURE");
+
+    my_snprintf(query_buff, sizeof(query_buff),
+                "SHOW %s STATUS WHERE Db = '%s'",
+                routine_type, db_name_buff);
+    mysql_query(sock, query_buff);
+
+    if (!(routine_list_res= mysql_store_result(sock)))
+      DBUG_RETURN(1);
+
+    if (mysql_num_rows(routine_list_res))
+    {
+      fprintf(sql_file, "\n/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n");
+      fprintf(sql_file, "DELIMITER //\n");
+
+      while((routine_list_row= mysql_fetch_row(routine_list_res)))
+      {
+        DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type, name_buff));
+        mysql_real_escape_string(sock, name_buff,
+                                 routine_list_row[1], strlen(routine_list_row[1]));
+        my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE %s %s",
+                    routine_type, 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(1);
+        }
+
+        while ((row=mysql_fetch_row(routine_res)))
+        {
+          /*
+            the user can see routine names, but NOT the routine body of other
+            routines that are not the creator of!
+          */
+          DBUG_PRINT("info",("length of body for %s row[2] '%s' is %d",
+                             name_buff, row[2], strlen(row[2])));
+          if (strlen(row[2]))
+          {
+            fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=\"%s\"*/ //\n",
+                    row[1] /* sql_mode */);
+
+            if (opt_drop)
+              fprintf(sql_file, "/*!50003 DROP %s IF EXISTS %s */ //\n",
+                      routine_type, name_buff);
+            /*
+              the i==0 is temporary until we can figure out why functions
+              can't be in comments
+            */
+            /* create proc/func body */;
+            fprintf(sql_file, i == 0 ? "%s //\n" : "/*!50003 %s */ //\n", row[2]);
+          }
+        } /* end of routine printing */
+      } /* end of list of routines */
+      /* set the delimiter back to ';' */
+      fprintf(sql_file, "DELIMITER ;\n");
+      fprintf(sql_file, "/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;\n");
+      mysql_free_result(routine_res);
+      routine_res=NULL;
+    }
+    mysql_free_result(routine_list_res);
+    routine_list_res=NULL;
+  } /* end of for i (0 .. 1)  */
+
+  mysql_query(sock, "UNLOCK TABLES");
+  DBUG_RETURN(0);
+}
 
 /*
   getTableStructure -- retrievs database structure, prints out corresponding
@@ -2378,6 +2490,12 @@
       order_by= 0;
     }
   }
+  if (opt_routines && !opt_xml &&
+      mysql_get_server_version(sock) >= 50009)
+  {
+    DBUG_PRINT("info", ("Dumping routines for database %s", database));
+    dump_routines_for_db(database);
+  }
   if (opt_xml)
   {
     fputs("</database>\n", md_result_file);
@@ -2579,6 +2697,13 @@
       table_name= hash_element(&dump_tables, i);
       get_view_structure(table_name, db);
     }
+  }
+  /* obtain dump of routines (procs/functions) */
+  if (opt_routines  && !opt_xml &&
+      mysql_get_server_version(sock) >= 50009)
+  {
+    DBUG_PRINT("info", ("Dumping routines for database %s", db));
+    dump_routines_for_db(db);
   }
   hash_free(&dump_tables);
   my_free(order_by, MYF(MY_ALLOW_ZERO_PTR));

--- 1.61/mysql-test/r/mysqldump.result	2005-07-30 08:24:47 +02:00
+++ 1.62/mysql-test/r/mysqldump.result	2005-09-04 01:34:43 +02:00
@@ -1875,3 +1875,61 @@
 end if;
 end	BEFORE	#	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
 DROP TABLE t1, t2;
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES(1);
+INSERT INTO t1 VALUES(2);
+INSERT INTO t1 VALUES(3);
+INSERT INTO t1 VALUES(4);
+INSERT INTO t1 VALUES(5);
+DROP FUNCTION IF EXISTS bug9056_func1;
+CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11)
+RETURN a+b //
+CREATE PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
+BEGIN SELECT a+b INTO c; end  //
+DROP FUNCTION IF EXISTS bug9056_func2 //
+create function bug9056_func2(f1 char binary) returns char binary
+begin
+set f1= concat( 'hello', f1 );
+return f1;
+end //
+DROP PROCEDURE IF EXISTS bug9056_proc2 //
+CREATE PROCEDURE bug9056_proc2(OUT a INT) 
+BEGIN 
+select sum(id) from t1 into a; 
+END //
+SELECT db, name, type, definer, param_list, body 
+FROM mysql.proc
+WHERE db = 'test';
+db	name	type	definer	param_list	body
+test	bug9056_func1	FUNCTION	root@localhost	a INT, b INT	RETURN a+b
+test	bug9056_func2	FUNCTION	root@localhost	f1 char binary	begin
+set f1= concat( 'hello', f1 );
+return f1;
+end
+test	bug9056_proc1	PROCEDURE	root@localhost	IN a INT, IN b INT, OUT c INT	BEGIN SELECT a+b INTO c; end
+test	bug9056_proc2	PROCEDURE	root@localhost	OUT a INT	BEGIN 
+select sum(id) from t1 into a; 
+END
+DROP PROCEDURE IF EXISTS bug9056_func1;
+DROP PROCEDURE IF EXISTS bug9056_func2;
+DROP PROCEDURE IF EXISTS bug9056_proc1;
+DROP PROCEDURE IF EXISTS bug9056_proc2;
+drop table t1;
+SELECT db, name, type, definer, param_list, body 
+FROM mysql.proc
+WHERE db = 'test';
+db	name	type	definer	param_list	body
+test	bug9056_func1	FUNCTION	root@localhost	a INT, b INT	RETURN a+b
+test	bug9056_func2	FUNCTION	root@localhost	f1 char binary	begin
+set f1= concat( 'hello', f1 );
+return f1;
+end
+test	bug9056_proc1	PROCEDURE	root@localhost	IN a INT, IN b INT, OUT c INT	BEGIN SELECT a+b INTO c; end
+test	bug9056_proc2	PROCEDURE	root@localhost	OUT a INT	BEGIN 
+select sum(id) from t1 into a; 
+END
+DROP PROCEDURE IF EXISTS bug9056_func1;
+DROP PROCEDURE IF EXISTS bug9056_func2;
+DROP PROCEDURE IF EXISTS bug9056_proc1;
+DROP PROCEDURE IF EXISTS bug9056_proc2;
+drop table t1;

--- 1.55/mysql-test/t/mysqldump.test	2005-07-29 22:40:00 +02:00
+++ 1.56/mysql-test/t/mysqldump.test	2005-09-04 01:34:44 +02:00
@@ -762,3 +762,84 @@
 --replace_column 6 #
 show triggers;
 DROP TABLE t1, t2;
+
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES(1);
+INSERT INTO t1 VALUES(2);
+INSERT INTO t1 VALUES(3);
+INSERT INTO t1 VALUES(4);
+INSERT INTO t1 VALUES(5);
+--disable_warnings
+DROP FUNCTION IF EXISTS bug9056_func1;
+DELIMITER //;
+--enable_warnings
+CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11)
+RETURN a+b //
+CREATE PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT)
+BEGIN SELECT a+b INTO c; end  //
+
+--disable_warnings
+DROP FUNCTION IF EXISTS bug9056_func2 //
+--enable_warnings
+
+create function bug9056_func2(f1 char binary) returns char binary
+begin
+  set f1= concat( 'hello', f1 );
+  return f1;
+end //
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug9056_proc2 //
+--enable_warnings
+CREATE PROCEDURE bug9056_proc2(OUT a INT) 
+BEGIN 
+  select sum(id) from t1 into a; 
+END //
+
+DELIMITER ;//
+
+# just to see what you've created
+# this will not work because of the timestamps!
+# show procedure status;
+#show create procedure bug9056_proc1;
+#show create procedure bug9056_proc2;
+#show function status;
+#show create function bug9056_func1;
+#show create function bug9056_func2;
+SELECT db, name, type, definer, param_list, body 
+FROM mysql.proc
+WHERE db = 'test';
+
+# Dump the DB and ROUTINES 
+--exec $MYSQL_DUMP --skip-comments --routines --databases test > var/tmp/mysqldump.sql
+# ok, now blow it all away
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug9056_func1;
+DROP PROCEDURE IF EXISTS bug9056_func2;
+DROP PROCEDURE IF EXISTS bug9056_proc1;
+DROP PROCEDURE IF EXISTS bug9056_proc2;
+drop table t1;
+--enable-warnings
+
+# Now, restore
+--exec $MYSQL test < var/tmp/mysqldump.sql
+
+# Check that the routines have been reloaded
+# this will not work because of the timestamps!
+#show procedure status;
+#show create procedure bug9056_proc1;
+#show create procedure bug9056_proc2;
+#show function status;
+#show create function bug9056_func1;
+#show create function bug9056_func2;
+SELECT db, name, type, definer, param_list, body 
+FROM mysql.proc
+WHERE db = 'test';
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug9056_func1;
+DROP PROCEDURE IF EXISTS bug9056_func2;
+DROP PROCEDURE IF EXISTS bug9056_proc1;
+DROP PROCEDURE IF EXISTS bug9056_proc2;
+drop table t1;
+--enable-warnings
Thread
bk commit into 5.0 tree (patg:1.1945) BUG#9056Patrick Galbraith4 Sep