MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Marc Alff Date:July 6 2006 7:15pm
Subject:Contribution for Bug#11230 (comments in functions / SP / triggers)
-- revised
View as plain text  
Hi all

This is a revision from :
http://lists.mysql.com/internals/33710

The production code is unchanged.
The tests are now integrated with mysqltest,
thanks to the "--exec $MYSQL" feature.

See the attached patch, based on mysql-5.1-BK as of 2006-07-06.

Regards,
Marc Alff.


diff -Naur mysql-5.1-BK/client/mysql.cc mysql-5.1-comment/client/mysql.cc
--- mysql-5.1-BK/client/mysql.cc	2006-06-26 03:55:58.000000000 +0000
+++ mysql-5.1-comment/client/mysql.cc	2006-07-06 18:29:34.000000000 +0000
@@ -137,6 +137,7 @@
 	       default_charset_used= 0, opt_secure_auth= 0,
                default_pager_set= 0, opt_sigint_ignore= 0,
                show_warnings= 0, executing_query= 0, interrupted_query= 0;
+static my_bool preserve_comments= 0;
 static ulong opt_max_allowed_packet, opt_net_buffer_length;
 static uint verbose=0,opt_silent=0,opt_mysql_port=0, opt_local_infile=0;
 static my_string opt_mysql_unix_port=0;
@@ -746,6 +747,10 @@
   {"show-warnings", OPT_SHOW_WARNINGS, "Show warnings after every statement.",
     (gptr*) &show_warnings, (gptr*) &show_warnings, 0, GET_BOOL, NO_ARG, 
     0, 0, 0, 0, 0, 0},
+  {"comments", 'c', "Preserve comments. Send comments to the server."
+    " Comments are discarded by default, enable with --enable-comments",
+    (gptr*) &preserve_comments, (gptr*) &preserve_comments,
+    0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   { 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
 };
 
@@ -1083,17 +1088,12 @@
       status.exit_status=0;
       break;
     }
-    if (!in_string && (line[0] == '#' ||
-		       (line[0] == '-' && line[1] == '-') ||
-		       line[0] == 0))
-      continue;					// Skip comment lines
-
     /*
       Check if line is a mysql command line
       (We want to allow help, print and clear anywhere at line start
     */
     if ((named_cmds || glob_buffer.is_empty())
-	&& !in_string && (com=find_command(line,0)))
+	&& !ml_comment && !in_string && (com=find_command(line,0)))
     {
       if ((*com->func)(&glob_buffer,line) > 0)
 	break;
@@ -1211,15 +1211,22 @@
 
   for (pos=out=line ; (inchar= (uchar) *pos) ; pos++)
   {
-    if (my_isspace(charset_info,inchar) && out == line && 
-        buffer.is_empty())
-      continue;
+    if (! preserve_comments)
+    {
+      // Skip spaces at the beggining of a statement
+      if (my_isspace(charset_info,inchar)
+          && (out == line)
+          && buffer.is_empty())
+        continue;
+    }
+
 #ifdef USE_MB
+    // Accept multi-byte characters as-is
     int length;
     if (use_mb(charset_info) &&
         (length= my_ismbchar(charset_info, pos, end_of_line)))
     {
-      if (!*ml_comment)
+      if (!*ml_comment || preserve_comments)
       {
         while (length--)
           *out++ = *pos++;
@@ -1230,9 +1237,10 @@
       continue;
     }
 #endif
+
     if (!*ml_comment && inchar == '\\')
     {
-      // Found possbile one character command like \c
+      // Found possible one character command like \c
 
       if (!(inchar = (uchar) *++pos))
 	break;				// readline adds one '\'
@@ -1244,8 +1252,13 @@
       }
       if ((com=find_command(NullS,(char) inchar)))
       {
-	const String tmp(line,(uint) (out-line), charset_info);
-	buffer.append(tmp);
+        // Flush previously accepted characters
+        if (out != line)
+        {
+          buffer.append(line, (uint) (out-line));
+          out = line;
+        }
+
 	if ((*com->func)(&buffer,pos-1) > 0)
 	  DBUG_RETURN(1);                       // Quit
 	if (com->takes_params)
@@ -1259,7 +1272,6 @@
 	  else 
 	    pos+= delimiter_length - 1; // Point at last delim char
 	}
-	out=line;
       }
       else
       {
@@ -1271,27 +1283,77 @@
 	continue;
       }
     }
-    else if (!*ml_comment && !*in_string &&
-             (*pos == *delimiter && is_prefix(pos + 1, delimiter + 1) ||
-              buffer.length() == 0 && (out - line) >= 9 &&
-              !my_strcasecmp(charset_info, line, "delimiter")))
-    {					
-      uint old_delimiter_length= delimiter_length;
+    else if (   !*ml_comment
+             && !*in_string
+             && is_prefix(pos, "delimiter")
+             && my_isspace(charset_info, pos[9]))
+    {
+      // Flush previously accepted characters
       if (out != line)
-	buffer.append(line, (uint) (out - line));	// Add this line
-      if ((com= find_command(buffer.c_ptr(), 0)))
       {
-        if (com->func == com_delimiter)
+        buffer.append(line, (uint) (out-line));
+        out = line;
+      }
+
+      // Flush possible comments in the buffer
+      if ( ! buffer.is_empty())
+      {
+        if (com_go(&buffer, 0) > 0)             // < 0 is not fatal
+          DBUG_RETURN(1);
+        buffer.length(0);
+      }
+
+      /*
+        Delimiter wants the get rest of the given line as argument to
+        allow one to change ';' to ';;' and back
+      */
+      buffer.append(pos);
+      if (com_delimiter(& buffer, pos) > 0)
+        DBUG_RETURN(1);                       // Quit
+
+      buffer.length(0);
+      break;
+    }
+    else if (   !*ml_comment
+             && !*in_string
+             && is_prefix(pos, delimiter))
+    {
+      // Found a statement. Continue parsing after the delimiter
+      pos+= delimiter_length ;
+
+      if (preserve_comments)
+      {
+        while (my_isspace(charset_info, *pos))
         {
-          /*
-            Delimiter wants the get rest of the given line as argument to
-            allow one to change ';' to ';;' and back
-          */
-          char *end= strend(pos);
-          buffer.append(pos, (uint) (end - pos));
-          /* Ensure pos will point at \0 after the pos+= below */
-          pos= end - old_delimiter_length + 1;
+          *out ++ = *pos ++;
         }
+      }
+
+      // Flush previously accepted characters
+      if (out != line)
+      {
+        buffer.append(line, (uint) (out-line));
+        out = line;
+      }
+
+      if (  preserve_comments
+          && (  (*pos == '#')
+             || (   (*pos == '-')
+                 && (pos[1] == '-')
+                 && my_isspace(charset_info, pos[2])
+                )
+             )
+         )
+      {
+        // Add trailing single line comments to this statement
+        buffer.append(pos);
+        pos+= strlen(pos);
+      }
+
+      pos--;
+
+      if ((com= find_command(buffer.c_ptr(), 0)))
+      {
 	if ((*com->func)(&buffer, buffer.c_ptr()) > 0)
 	  DBUG_RETURN(1);                       // Quit
       }
@@ -1301,17 +1363,42 @@
 	  DBUG_RETURN(1);
       }
       buffer.length(0);
-      out= line;
-      pos+= old_delimiter_length - 1;
     }
-    else if (!*ml_comment && (!*in_string && (inchar == '#' ||
-			      inchar == '-' && pos[1] == '-' &&
-			      my_isspace(charset_info,pos[2]))))
-      break;					// comment to end of line
+    else if (   !*ml_comment
+             && !*in_string
+             && (   (inchar == '#')
+                 || (   (inchar == '-')
+                     && (pos[1] == '-')
+                     && my_isspace(charset_info,pos[2]))
+                )
+            )
+    {
+      // Flush previously accepted characters
+      if (out != line)
+      {
+        buffer.append(line, (uint) (out-line));
+        out = line;
+      }
+
+      // comment to end of line
+      if (preserve_comments)
+      {
+        buffer.append(pos);
+      }
+      break;
+    }
     else if (!*in_string && inchar == '/' && *(pos+1) == '*' &&
 	     *(pos+2) != '!')
     {
-      pos++;
+      if (preserve_comments)
+      {
+        *out++ = *pos++ ;                       // copy '/'
+        *out++ = *pos ;                         // copy '*'
+      }
+      else
+      {
+        pos++;
+      }
       *ml_comment= 1;
       if (out != line)
       {
@@ -1321,10 +1408,25 @@
     }
     else if (*ml_comment && inchar == '*' && *(pos + 1) == '/')
     {
-      pos++;
+      if (preserve_comments)
+      {
+        *out++ = *pos++ ;                       // copy '*'
+        *out++ = *pos ;                         // copy '/'
+      }
+      else
+      {
+        pos++;
+      }
       *ml_comment= 0;
+      if (out != line)
+      {
+        buffer.append(line,(uint) (out-line));
+        out=line;
+      }
+      // Consumed a 2 chars or more, and will add 1 at most,
+      // so using the 'line' buffer to edit data in place is ok.
       need_space= 1;
-    }      
+    }
     else
     {						// Add found char to buffer
       if (inchar == *in_string)
@@ -1332,14 +1434,14 @@
       else if (!*ml_comment && !*in_string &&
 	       (inchar == '\'' || inchar == '"' || inchar == '`'))
 	*in_string= (char) inchar;
-      if (!*ml_comment)
+      if (!*ml_comment || preserve_comments)
       {
         if (need_space && !my_isspace(charset_info, (char)inchar))
         {
           *out++= ' ';
-          need_space= 0;
         }
-	*out++= (char) inchar;
+        need_space= 0;
+        *out++= (char) inchar;
       }
     }
   }
@@ -1349,7 +1451,7 @@
     uint length=(uint) (out-line);
     if (buffer.length() + length >= buffer.alloced_length())
       buffer.realloc(buffer.length()+length+IO_SIZE);
-    if (!(*ml_comment) && buffer.append(line,length))
+    if ((!*ml_comment || preserve_comments) && buffer.append(line,length))
       DBUG_RETURN(1);
   }
   DBUG_RETURN(0);
diff -Naur mysql-5.1-BK/mysql-test/r/comments-51.result
mysql-5.1-comment/mysql-test/r/comments-51.result
--- mysql-5.1-BK/mysql-test/r/comments-51.result	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-comment/mysql-test/r/comments-51.result	2006-07-06 18:29:33.000000000 +0000
@@ -0,0 +1,61 @@
+drop table if exists t1;
+drop function if exists foofct;
+drop procedure if exists empty;
+drop procedure if exists foosp;
+drop procedure if exists nicesp;
+drop trigger if exists t1_empty;
+drop trigger if exists t1_bi;
+"Pass 1 : --disable-comments"
+1
+1
+2
+2
+foofct("call 1")
+call 1
+Function	sql_mode	Create Function
+foofct		CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS
char(20)\nreturn\n\n\n\nx
+foofct("call 2")
+call 2
+Function	sql_mode	Create Function
+foofct		CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS
char(20)\nbegin\n  \n  \n  \n\n  \n\n  \n  return x;\nend
+Procedure	sql_mode	Create Procedure
+empty		CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend
+id	data
+foo	42
+Procedure	sql_mode	Create Procedure
+foosp		CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into
test.t1\n\n\n\n\n  \n\n  \n  values ("foo", 42)
+Procedure	sql_mode	Create Procedure
+nicesp		CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n  \n  declare
b int;\n  declare c float;\n\n  \n  \n\n  \nend
+Db	Name	Create Trigger
+test	t1_empty	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_empty` AFTER DELETE
ON `test`.`t1` FOR EACH ROW begin\nend
+Db	Name	Create Trigger
+test	t1_bi	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_bi` BEFORE INSERT ON
`test`.`t1` FOR EACH ROW begin\n\n\n\n  \n  declare b int;\n  declare c float;\n\n  \n 
\n\n  \n  set NEW.data := 12;\nend
+id	data
+trig	12
+"Pass 2 : --enable-comments"
+1
+1
+2
+2
+foofct("call 1")
+call 1
+Function	sql_mode	Create Function
+foofct		CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS
char(20)\nreturn\n-- comment 1a\n# comment 1b\n/* comment 1c */\nx # after body, on same
line
+foofct("call 2")
+call 2
+Function	sql_mode	Create Function
+foofct		CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS
char(20)\nbegin\n  -- comment 1a\n  # comment 1b\n  /*\n     comment 1c\n  */\n\n  --
empty line below\n\n  -- empty line above\n  return x;\nend
+Procedure	sql_mode	Create Procedure
+empty		CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend
+id	data
+foo	42
+Procedure	sql_mode	Create Procedure
+foosp		CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n##
These comments are part of the procedure body, and should be kept.\n# Comment 2a\n--
Comment 2b\n/* Comment 2c */\n  -- empty line below\n\n  -- empty line above\n  values
("foo", 42) # comment 3, still part of the body
+Procedure	sql_mode	Create Procedure
+nicesp		CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n  -- declare
some variables here\n  declare b int;\n  declare c float;\n\n  -- do more stuff here\n  --
commented nicely and so on\n\n  -- famous last words ...\nend
+Db	Name	Create Trigger
+test	t1_empty	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_empty` AFTER DELETE
ON `test`.`t1` FOR EACH ROW begin\nend
+Db	Name	Create Trigger
+test	t1_bi	CREATE DEFINER = 'root'@'localhost' TRIGGER `test`.`t1_bi` BEFORE INSERT ON
`test`.`t1` FOR EACH ROW begin\n# comment 1a\n-- comment 1b\n/*\n   comment 1c\n*/\n  --
declare some variables here\n  declare b int;\n  declare c float;\n\n  -- do more stuff
here\n  -- commented nicely and so on\n\n  -- famous last words ...\n  set NEW.data :=
12;\nend
+id	data
+trig	12
diff -Naur mysql-5.1-BK/mysql-test/t/comments-51.sql
mysql-5.1-comment/mysql-test/t/comments-51.sql
--- mysql-5.1-BK/mysql-test/t/comments-51.sql	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-comment/mysql-test/t/comments-51.sql	2006-07-06 18:29:33.000000000 +0000
@@ -0,0 +1,241 @@
+##============================================================================
+## This file is part of MySQL.
+## Copyright (c) 2006, MySQL AB.
+##
+## This program is free software; you can redistribute it and/or
+## modify it under the terms of the GNU General Public License
+## as published by the Free Software Foundation; either version 2
+## of the License, or (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with this program; if not, write to the Free Software
+## Foundation, Inc., 51 Franklin Street, Fifth Floor,
+## Boston, MA 02110-1301, USA.
+##============================================================================
+
+## Written by Marc Alff.
+
+##============================================================================
+## Notes
+##============================================================================
+
+# Test case for Bug#11230
+
+# The point of this test is to make sure that '#', '-- ' and '/* ... */'
+# comments, as well as empty lines, are sent from the client to the server.
+# This is to ensure better error reporting, and to keep comments in the code
+# for stored procedures / functions / triggers (Bug#11230).
+# As a result, be careful when editing comments in this script, they do
+# matter.
+#
+# Also, note that this is a script for **mysql**, not mysqltest.
+# This is critical, as the mysqltest client interprets comments differently.
+
+##============================================================================
+## Setup
+##============================================================================
+
+## See comments-51.test for initial cleanup
+
+# Test tables
+#
+# t1 is reused throughout the file, and dropped at the end.
+#
+drop table if exists t1;
+create table t1 (
+  id   char(16) not null default '',
+  data int not null
+);
+
+##============================================================================
+## Comments outside statements
+##============================================================================
+
+# Ignored 1a
+-- Ignored 1b
+/*
+   Ignored 1c
+*/
+
+select 1;
+
+##============================================================================
+## Comments inside statements
+##============================================================================
+
+select # comment 1a
+# comment 2a
+-- comment 2b
+/*
+   comment 2c
+*/
+2
+; # not strictly inside, but on same line
+# ignored
+
+##============================================================================
+## Comments inside functions
+##============================================================================
+
+drop function if exists foofct ;
+
+create function foofct (x char(20))
+returns char(20)
+/* not inside the body yet */
+return
+-- comment 1a
+# comment 1b
+/* comment 1c */
+x; # after body, on same line
+
+select foofct("call 1");
+
+show create function foofct;
+drop function foofct;
+
+delimiter |
+
+create function foofct(x char(20))
+returns char(20)
+begin
+  -- comment 1a
+  # comment 1b
+  /*
+     comment 1c
+  */
+
+  -- empty line below
+
+  -- empty line above
+  return x;
+end|
+
+delimiter ;
+
+select foofct("call 2");
+
+show create function foofct;
+drop function foofct;
+
+##============================================================================
+## Comments inside stored procedures
+##============================================================================
+
+# Empty statement
+drop procedure if exists empty;
+create procedure empty()
+begin
+end;
+
+call empty();
+show create procedure empty;
+drop procedure empty;
+
+drop procedure if exists foosp;
+
+## These comments are before the create, and will be lost
+# Comment 1a
+-- Comment 1b
+/*
+   Comment 1c
+ */
+create procedure foosp()
+/* Comment not quiet in the body yet */
+  insert into test.t1
+## These comments are part of the procedure body, and should be kept.
+# Comment 2a
+-- Comment 2b
+/* Comment 2c */
+  -- empty line below
+
+  -- empty line above
+  values ("foo", 42); # comment 3, still part of the body
+## After the ';', therefore not part of the body
+# comment 4a
+-- Comment 4b
+/*
+   Comment 4c
+ */
+
+call foosp();
+select * from t1;
+delete from t1;
+show create procedure foosp;
+drop procedure foosp;
+
+drop procedure if exists nicesp;
+
+delimiter |
+
+create procedure nicesp(a int)
+begin
+  -- declare some variables here
+  declare b int;
+  declare c float;
+
+  -- do more stuff here
+  -- commented nicely and so on
+
+  -- famous last words ...
+end|
+
+delimiter ;
+
+show create procedure nicesp;
+drop procedure nicesp;
+
+##============================================================================
+## Comments inside triggers
+##============================================================================
+
+drop trigger if exists t1_empty;
+
+create trigger t1_empty after delete on t1
+for each row
+begin
+end;
+
+show create trigger t1_empty;
+
+drop trigger if exists t1_bi;
+
+delimiter |
+
+create trigger t1_bi before insert on t1
+for each row
+begin
+# comment 1a
+-- comment 1b
+/*
+   comment 1c
+*/
+  -- declare some variables here
+  declare b int;
+  declare c float;
+
+  -- do more stuff here
+  -- commented nicely and so on
+
+  -- famous last words ...
+  set NEW.data := 12;
+end|
+
+delimiter ;
+
+show create trigger t1_bi;
+
+# also make sure the trigger still works
+insert into t1(id) value ("trig");
+select * from t1;
+
+##============================================================================
+## Cleanup
+##============================================================================
+
+drop table t1;
+
diff -Naur mysql-5.1-BK/mysql-test/t/comments-51.test
mysql-5.1-comment/mysql-test/t/comments-51.test
--- mysql-5.1-BK/mysql-test/t/comments-51.test	1970-01-01 00:00:00.000000000 +0000
+++ mysql-5.1-comment/mysql-test/t/comments-51.test	2006-07-06 18:29:33.000000000 +0000
@@ -0,0 +1,26 @@
+# This test should work in embedded server after we fix mysqltest
+-- source include/not_embedded.inc
+#
+# Testing the MySQL command line client(mysql)
+#
+
+# See the content of comments-51.sql
+# Set the test database to a known state before running the tests.
+--disable_warnings
+drop table if exists t1;
+drop function if exists foofct;
+drop procedure if exists empty;
+drop procedure if exists foosp;
+drop procedure if exists nicesp;
+drop trigger if exists t1_empty;
+drop trigger if exists t1_bi;
+--enable_warnings
+
+# Test without comments
+--echo "Pass 1 : --disable-comments"
+--exec $MYSQL --disable-comments test 2>&1 < "./t/comments-51.sql"
+
+# Test with comments
+--echo "Pass 2 : --enable-comments"
+--exec $MYSQL --enable-comments test 2>&1 < "./t/comments-51.sql"
+

Thread
Contribution for Bug#11230 (comments in functions / SP / triggers)-- revisedMarc Alff6 Jul