Stored procedures and functions can be easily managed through Liquibase Pro edition. Are we doomed when using the Community one? Oh, no. As with any other database change, it can be done using the <SQL>
tag which can work with any SQL code. But Liquibase may complain on an SQL syntax error ...
Stored procedure in SQL
Any database engine has its own syntax for stored code like procedures, functions, and triggers. It makes it difficult for any parsing tools that try to be universal between various
DELIMITER //
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end //
DELIMITER ;
It is worth pointing out that by default SQL uses a semicolon (;) to separate statements in a batch or a script. As a stored procedure may contain multiple statements, they can be separated using semicolons. A problem begins with detecting the end of the stored procedure create statement - the parser may not be sure if a particular semicolon means the end of a statement inside the procedure or the end of the procedure.
That is the reason why the above script defines a new delimiter on the first line. Thanks to that, the parser stops treating further semicolons as delimiters and passes them to the database engine as any other character.
Stored procedure in Liquibase
Liquibase Community does not provide any custom tag for creating a stored procedure, but we can successfully use the <SQL
>
tag, which comes in handy when no special tag is available.
Failed 1st attempt
The thing is that the most straight-forward solution fails with SQL syntax error.
<changeSet id="test 1 - failed" author="DBA presents">
<sql>
DELIMITER //
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end //
DELIMITER ;
</sql>
</changeSet>
Unexpected error running Liquibase: 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 'DELIMITER //
create procedure some_proc (out title int)
' at line 1 [Failed SQL: (1064) DELIMITER //
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123]
Liquibase returned the error because the DELIMITER command is known to MySQL, not Liquibase, so it does not change the delimiter for Liquibase parser.
endDelimiter attribute
So maybe we should use a DELIMITER alternative that is actually known to Liquibase? Yes! That is an option. Although it is not a command, but the <SQL>
tag has the endDelimiter attribute that we can use. It can be set to the new delimiter string that we want to use. Here is a Liquibase change that does that:
<changeSet id="test 2 - endDelimiter" author="DBA presents">
<sql endDelimiter="//">
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end //
</sql>
</changeSet>
It works, but we can still make it better.
splitStatements attribute
When there is only one statement in the whole <SQL>
tag, why to even bother defining a new delimiter? We can simply tell Liquibase to not even try splitting the <SQL>
body to separate statements by setting splitStatements
to false
.
<changeSet id="test 2 - splitStatements" author="DBA presents">
<sql splitStatements="false">
create procedure some_proc (out title int)
begin
select count(1) into title from titles
where emp_no = 123;
end
</sql>
</changeSet>
By default the splitStatements
attribute is set to true
, so Liquibase tries to parse and split all code provided between <SQL>
tags. When set to false
, Liquibase does not split it, but sends the whole directly to the database engine.