rubenv / rubenv/sql-migrate

Defining functions ?

Open
#28 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Go
Stars
3.4k
Forks
292
PR merge metrics
No merged PRs in 30d

Description

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;

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the supplied migration containing the multi-statement CREATE FUNCTION definitions and the migrate StatementBegin/StatementEnd directives. Determine why the function statements fail and verify the migration succeeds, including the Down statements that drop the functions.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.