Open
Description
Hello.
I tried to use yoyo-migrations in my project, however, when creating a schema with procedures using REPEAT, I received errors. Learning the sources led me to the sqlparse library.
There is a purged script from sqldump:
$ cat dump.sql
DROP FUNCTION IF EXISTS `test_function`;
DROP PROCEDURE IF EXISTS `test_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `test_function`() RETURNS int
READS SQL DATA
DETERMINISTIC
begin
declare test_var int default 1;
repeat
set test_var = test_var + 1;
until test_var < 10 end repeat;
return test_var;
end ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
begin
declare test_var int default 1;
repeat
set test_var = test_var + 1;
until test_var < 10 end repeat;
end ;;
DELIMITER ;
$ mysql -h 172.28.1.21 -u root -p test_db < dump.sql
Enter password:
$
The library does not parse correctly if you use a REPEAT construct:
$ /usr/bin/python3 sqlparse_test.py
+
DROP FUNCTION IF EXISTS `test_function`;
-
+
DROP PROCEDURE IF EXISTS `test_procedure`;
-
+
DELIMITER ;
-
+
;
-
+
CREATE DEFINER=`root`@`%` FUNCTION `test_function`() RETURNS int
READS SQL DATA
DETERMINISTIC
begin
declare test_var int default 1;
repeat
set test_var = test_var + 1;
until test_var < 10 end repeat;
-
+
return test_var;
-
+
end ;
-
+
;
-
+
CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
begin
declare test_var int default 1;
repeat
set test_var = test_var + 1;
until test_var < 10 end repeat;
-
+
end ;
-
+
;
-
+
DELIMITER ;
-
+
-
$
Source code of test:
#!/usr/bin/python3
import sys
import sqlparse
import os
sql = """
DROP FUNCTION IF EXISTS `test_function`;
DROP PROCEDURE IF EXISTS `test_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `test_function`() RETURNS int
READS SQL DATA
DETERMINISTIC
begin
declare test_var int default 1;
repeat
set test_var = test_var + 1;
until test_var < 10 end repeat;
return test_var;
end ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
begin
declare test_var int default 1;
repeat
set test_var = test_var + 1;
until test_var < 10 end repeat;
end ;;
DELIMITER ;
"""
for i in sqlparse.split(sql):
print('\n+\n' + i + '\n-')
If you delete a REPEAT loop, the procedure stops breaking into two parts:
$ /usr/bin/python3 /home/popov-aa/Mountpoints/SSD/Projects/yoyo/src/sqlparse_test.py
+
DROP FUNCTION IF EXISTS `test_function`;
-
+
DROP PROCEDURE IF EXISTS `test_procedure`;
-
+
DELIMITER ;
-
+
;
-
+
CREATE DEFINER=`root`@`%` FUNCTION `test_function`() RETURNS int
READS SQL DATA
DETERMINISTIC
begin
declare test_var int default 1;
return test_var;
end ;
-
+
;
-
+
CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
begin
declare test_var int default 1;
end ;
-
+
;
-
+
DELIMITER ;
-
+
-
Source code of second test:
#!/usr/bin/python3
import sys
import sqlparse
import os
sql = """
DROP FUNCTION IF EXISTS `test_function`;
DROP PROCEDURE IF EXISTS `test_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `test_function`() RETURNS int
READS SQL DATA
DETERMINISTIC
begin
declare test_var int default 1;
return test_var;
end ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
begin
declare test_var int default 1;
end ;;
DELIMITER ;
"""
for i in sqlparse.split(sql):
print('\n+\n' + i + '\n-')
I think it's a bug.
Metadata
Metadata
Assignees
Labels
No labels