Defining functions ?

[luna-duclos]

trafficstars

I have the following migrate file, I can't seem to get CREATE FUNCTION to work with multi-statement functions at all, even when using -- +migrate StatementBegin and -- +migrate StatementEnd

-- +migrate Up
-- UUID functions

-- +migrate StatementBegin
DELIMITER //
CREATE FUNCTION uuid_to_binary($Data VARCHAR(36)) RETURNS binary(16)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result BINARY(16) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Data = REPLACE($Data,'-','');
        SET $Result =
            CONCAT( UNHEX(SUBSTRING($Data,7,2)), UNHEX(SUBSTRING($Data,5,2)),
                    UNHEX(SUBSTRING($Data,3,2)), UNHEX(SUBSTRING($Data,1,2)),
                    UNHEX(SUBSTRING($Data,11,2)),UNHEX(SUBSTRING($Data,9,2)),
                    UNHEX(SUBSTRING($Data,15,2)),UNHEX(SUBSTRING($Data,13,2)),
                    UNHEX(SUBSTRING($Data,17,16)));
    END IF;
    RETURN $Result;
END//
-- +migrate StatementEnd

-- +migrate StatementBegin
DELIMITER //
CREATE FUNCTION binary_to_uuid($Data BINARY(16)) RETURNS char(36) CHARSET utf8
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result CHAR(36) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Result =
            CONCAT(
                HEX(SUBSTRING($Data,4,1)), HEX(SUBSTRING($Data,3,1)),
                HEX(SUBSTRING($Data,2,1)), HEX(SUBSTRING($Data,1,1)), '-',
                HEX(SUBSTRING($Data,6,1)), HEX(SUBSTRING($Data,5,1)), '-',
                HEX(SUBSTRING($Data,8,1)), HEX(SUBSTRING($Data,7,1)), '-',
                HEX(SUBSTRING($Data,9,2)), '-', HEX(SUBSTRING($Data,11,6)));
    END IF;
    RETURN $Result;
END//
-- +migrate StatementEnd

CREATE FUNCTION ordered_uuid() RETURNS binary(16)
NO SQL
RETURN uuid_to_binary(UUID());


-- +migrate Down
DROP FUNCTION ordered_uuid;
DROP FUNCTION uuid_to_binary;
DROP FUNCTION binary_to_uuid;

[rubenv]

It's been a long time since I touched this code, but I'm guessing this is caused by sqlparse (https://github.com/rubenv/sql-migrate/tree/master/sqlparse) not understanding function definitions.

In a migration we need to split each query and execute it separately. For now this is rather simplistic. To support functions, we'll either need to add a hack to make that work or get a full SQL-to-AST parser in there and do it properly.