R__functions.sql

        RETURN -1;
    END IF;

    UPDATE submodules
        SET submodules.Name = pName
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN 0;
END //

DROP FUNCTION IF EXISTS ChangeSubmoduleDescription//
CREATE FUNCTION ChangeSubmoduleDescription (
    pTeacherID INT,
    pSubmoduleID INT,
    pDescription VARCHAR(200) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vIsLocked INT DEFAULT -1;

    SELECT disciplines.IsLocked INTO vIsLocked
        FROM view_roadmap
        INNER JOIN disciplines ON disciplines.ID = view_roadmap.DisciplineID
        WHERE   view_roadmap.SubmoduleID = pSubmoduleID AND disciplines.AuthorID = pTeacherID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    UPDATE submodules
        SET submodules.Description  = pDescription
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN 0;
END //

DROP FUNCTION IF EXISTS DeleteSubmodule//
CREATE FUNCTION DeleteSubmodule (
    pTeacherID INT,
    pSubmoduleID INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vIsLocked, vModuleID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SELECT modules.ID, disciplines.IsLocked
        INTO vModuleID, vIsLocked
        FROM submodules
        INNER JOIN modules ON modules.ID = submodules.ModuleID
        INNER JOIN disciplines ON modules.DisciplineID = disciplines.ID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    # handler will catch constraint violation
    DELETE FROM submodules
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;

    # restore continuous ordering
    SET @counter = 0;
    UPDATE submodules
        SET submodules.OrderNum = (@counter := @counter + 1)
        WHERE   submodules.ModuleID = vModuleID
        ORDER BY submodules.OrderNum ASC;

    RETURN 0;
END //

DROP FUNCTION IF EXISTS AddSubmoduleUnsafe//
CREATE FUNCTION AddSubmoduleUnsafe (
    pModuleID INT,
    pMaxRate INT,
    pName VARCHAR(200) CHARSET utf8,
    pDescription VARCHAR(200) CHARSET utf8,
    pControlType VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vOrderNum, vIsLocked INT DEFAULT -1;
    DECLARE vDescription VARCHAR(200) CHARSET utf8 DEFAULT NULL;
    # DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # check discipline lock
    SELECT disciplines.IsLocked INTO vIsLocked
        FROM modules
        INNER JOIN disciplines ON disciplines.ID = modules.DisciplineID
        WHERE modules.ID = pModuleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -2;
    END IF;

    # get free order
    SET vOrderNum = COALESCE((
        SELECT MAX(submodules.OrderNum) FROM submodules
        WHERE submodules.ModuleID = pModuleID LIMIT 1
    ), 0) + 1;

    SET vDescription = IF(vDescription = '', NULL, pDescription);
    INSERT INTO submodules (ModuleID, MaxRate, OrderNum, Name, Description, Type) VALUES
        (pModuleID, pMaxRate, vOrderNum, pName, vDescription, pControlType);
    RETURN  LAST_INSERT_ID();
END //

DROP FUNCTION IF EXISTS AddSubmodule//
CREATE FUNCTION AddSubmodule (
    pTeacherID INT,
    pModuleID INT,
    pMaxRate INT,
    pName VARCHAR(200) CHARSET utf8,
    pDescription VARCHAR(200) CHARSET utf8,
    pControlType VARCHAR(30) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vIsLocked BOOLEAN DEFAULT TRUE;
    # DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # check author and discipline lock
    SELECT NOT disciplines.AuthorID <=> pTeacherID INTO vIsLocked
        FROM modules
        INNER JOIN disciplines ON  disciplines.ID = modules.DisciplineID
        WHERE modules.ID = pModuleID
        LIMIT 1;
    IF vIsLocked THEN
        RETURN -2;
    END IF;

    RETURN AddSubmoduleUnsafe(pModuleID, pMaxRate, pName, pDescription, pControlType);
END //

DROP FUNCTION IF EXISTS SwapSubmoduleOrder//
CREATE FUNCTION SwapSubmoduleOrder (
    pTeacherID INT,
    pSubmoduleID1 INT,
    pSubmoduleID2 INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID, vOrder1, vOrder2,
            vModule1, vModule2 INT DEFAULT -1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID,
            disciplines.ID
        INTO vOrder1, vModule1, vDisciplineID
        FROM submodules
        INNER JOIN modules        ON  submodules.ModuleID = modules.ID
        INNER JOIN disciplines    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID1
        LIMIT 1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID
        INTO vOrder2, vModule2
        FROM submodules
        INNER JOIN modules        ON  submodules.ModuleID = modules.ID
        INNER JOIN disciplines    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID2
        LIMIT 1;

    # check, that modules exists and belong to one discipline
    IF  vModule1 <= 0 OR vModule1 != vModule2 OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # swap
    UPDATE submodules
        SET submodules.OrderNum     = 271828
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    UPDATE submodules
        SET submodules.OrderNum     = vOrder1
        WHERE submodules.ID = pSubmoduleID2
        LIMIT 1;
    UPDATE submodules
        SET submodules.OrderNum     = vOrder2
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    RETURN 0;
END //

# -------------------------------------------------------------------------------------------
# Label: rating
# -------------------------------------------------------------------------------------------

-- get id of student's last exam with rate
-- first exam submodule, if don't rated yet
DROP FUNCTION IF EXISTS GetExamRateID//
CREATE FUNCTION GetExamRateID (
    pRecordBook INT,
    pDisciplineID INT
) RETURNS int(11)
READS SQL DATA
RETURN COALESCE((
    SELECT view_roadmap.SubmoduleID FROM view_roadmap
    LEFT JOIN rating_table AS rt on rt.SubmoduleID = view_roadmap.SubmoduleID AND rt.RecordBookID = pRecordBook
        WHERE view_roadmap.DisciplineID = pDisciplineID AND view_roadmap.ModuleType = 'exam'
    ORDER BY (rt.Rate IS NOT NULL) DESC, view_roadmap.SubmoduleOrderNum DESC
    LIMIT 1
), -1)//

# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION CalculateMaxRateForExtra (
    pDisciplineID INT,
    pRecordBookID INT
) RETURNS int(11)
READS SQL DATA
BEGIN
    DECLARE vExamType enum('exam', 'credit', 'grading_credit') DEFAULT NULL;
    DECLARE vDiscID, vLim, vResult INT DEFAULT 0;

    # get disc type
    SET vExamType = GetDisciplineProperty(pDisciplineID, 'type');

    # submodule doesn't exists
    IF vExamType IS NULL OR vExamType <= 0 THEN
        RETURN -1;
    END IF;
    SET vLim = IF(vExamType = 'exam', 38, 60);

    SELECT SUM(IF(view_roadmap.ModuleType = 'regular', rating_table.Rate, 0)) INTO vResult
        FROM view_roadmap
        LEFT JOIN rating_table ON rating_table.RecordBookID = pRecordBookID AND
                                    rating_table.SubmoduleID = view_roadmap.SubmoduleID
        WHERE view_roadmap.DisciplineID = pDisciplineID
        LIMIT 1;
    RETURN vLim - vResult;
END //

DROP FUNCTION IF EXISTS SetStudentRate//
DROP FUNCTION IF EXISTS Discipline_SetRate//
CREATE FUNCTION Discipline_SetRate (
    pTeacherID INT,
    pRecordBookID INT,
    pSubmoduleID INT,
    pRate INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vDisciplineID, vMaxRate, vModuleType INT DEFAULT -1;
    DECLARE vIsOver, vIsLocked, vIsUsed BOOLEAN DEFAULT FALSE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF pRate < 0 THEN
        INSERT INTO logs_rating (RecordBookID, SubmoduleID, TeacherID, Rate, Action ) VALUES
            (pRecordBookID, pSubmoduleID, pTeacherID, pRate, 'delete');

        # TODO: extract method log rate
        DELETE FROM rating_table
            WHERE   rating_table.RecordBookID = pRecordBookID AND
                    rating_table.SubmoduleID = pSubmoduleID
            LIMIT 1;

        RETURN 0;
    END IF;

    SET vIsOver = TRUE;
    SELECT  disciplines.ID,
            disciplines.IsLocked,
            disciplines.Milestone,
            submodules.IsUsed,
            submodules.maxRate,
            modules.Type
        INTO vDisciplineID, vIsLocked, vIsOver, vIsUsed, vMaxRate, vModuleType
        FROM submodules
        INNER JOIN modules                ON  submodules.ModuleID = modules.ID
        INNER JOIN disciplines            ON  modules.DisciplineID = disciplines.ID
        WHERE   submodules.ID = pSubmoduleID
        LIMIT 1;

    # correct max rate for extra module
    IF vModuleType = 4 THEN # 4 - extra
        SET vMaxRate = CalculateMaxRateForExtra(vDisciplineID, pRecordBookID);
    END IF;

    # 1) check rights
    IF NOT InternalIsStudentAttached(pRecordBookID, vDisciplineID) OR
       NOT InternalIsTeacherBound(pTeacherID, vDisciplineID) THEN
        RETURN 1;
    END IF;
    # 2) check, you can't rate regular and bonus after milestone
    IF (vIsOver AND (vModuleType = 1 OR vModuleType = 3)) THEN # 1 - regular, 3 - bonus
        RETURN 2;
    END IF;
    # 3) check, max rate exceeding
    IF  pRate > vMaxRate THEN
        RETURN 3;
    END IF;

    # add rate, or update old
    SET @isUpdated = FALSE;
    INSERT INTO rating_table (RecordBookID, TeacherID, SubmoduleID, Rate, Date)
        VALUES  (pRecordBookID, pTeacherID, pSubmoduleID, pRate, CURDATE() )
        ON DUPLICATE KEY UPDATE
            rating_table.TeacherID  = (@isUpdated := pTeacherID),
            rating_table.Rate       = pRate,
            rating_table.Date       = CURDATE();

    # log rate
    INSERT INTO logs_rating (RecordBookID, SubmoduleID, TeacherID, Rate, Action )
        VALUES (pRecordBookID, pSubmoduleID, pTeacherID, pRate, IF(@isUpdated, 'change', 'add') );

    # lock discipline for structure editing
    IF NOT vIsLocked THEN
        UPDATE disciplines
            SET disciplines.IsLocked = TRUE
            WHERE disciplines.ID = vDisciplineID
            LIMIT 1;
    END IF;

    # add submodule to max rate counting (see triggers)
    IF NOT vIsUsed THEN
        UPDATE submodules
            SET submodules.IsUsed = TRUE
            WHERE submodules.ID = pSubmoduleID
            LIMIT 1;
    END IF;
    RETURN 0;
END //

# -------------------------------------------------------------------------------------------
# Label: requests
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS SetRequestStatus//
CREATE FUNCTION SetRequestStatus (
    pRequestID INT,
    pStatus enum('opened','processed','closed')
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    UPDATE requests
        SET requests.Status = pStatus
        WHERE requests.ID = pRequestID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//

-- todo: does it work? HasImage field not found
DROP FUNCTION IF EXISTS CreateRequest//
CREATE FUNCTION CreateRequest (
    pAccountID INT,
    pTitle VARCHAR(50) CHARSET utf8,
    pDescription TEXT CHARSET utf8,
    pImage BOOLEAN
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO requests (AccountID, Title, Description, Status, HasImage)
    VALUES  (pAccountID, pTitle, pDescription, 'opened', pImage);
    RETURN LAST_INSERT_ID();
END//

DROP FUNCTION IF EXISTS UpdateRequest//
CREATE FUNCTION UpdateRequest (
    pRequestID INT,
    pTitle VARCHAR(50) CHARSET utf8,
    pDescription TEXT CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    UPDATE requests
        SET requests.Description = pDescription,
            requests.Title = pTitle,
            requests.Date = NOW()
        WHERE   requests.ID = pRequestID AND
                requests.Description IS NULL AND
                requests.Title IS NULL
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//

# return StudentID or TeacherID depending on UserRoleID
DROP FUNCTION IF EXISTS GetUserStudentOrTeacherID//
CREATE FUNCTION GetUserStudentOrTeacherID (
    pAccountID INT(11),
    pUserRoleID INT(11)
) RETURNS INT(11)
READS SQL DATA
BEGIN
    DECLARE vID INT DEFAULT -1;

    IF pUserRoleID = GetUserRole('student') THEN
        SELECT students.ID
        INTO vID
        FROM students
        WHERE students.AccountID = pAccountID
        LIMIT 1;
    ELSE
        SELECT teachers.ID
        INTO vID
        FROM teachers
        WHERE teachers.AccountID = pAccountID
        LIMIT 1;
    END IF;

    RETURN vID;
END//

# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS CreateRecoveryToken//
CREATE FUNCTION CreateRecoveryToken (
    pAccountOrEMail VARCHAR(255) CHARSET utf8,
    pToken VARCHAR(100) CHARSET utf8
) RETURNS VARCHAR(255) charset utf8
NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;
    DECLARE vUserFullName TEXT charset utf8;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -2;

    # get account ID
    SELECT accounts.ID INTO vAccountID
        FROM accounts
        WHERE accounts.EMail = pAccountOrEMail OR
              accounts.Login = pAccountOrEMail
        LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN '';
    END IF;

    SET vUserFullName = GetUserFullNameByAccountID(vAccountID);
    IF vUserFullName IS NULL OR vUserFullName = '' THEN
        RETURN '';
    END IF;

    # transform all unused recovery tokens into used
    UPDATE recovery_tokens
    SET recovery_tokens.isUsed = 1
    WHERE   recovery_tokens.isUsed = 0 AND
            recovery_tokens.AccountID = vAccountID;

    # handle catch constraints violations
    INSERT INTO recovery_tokens
        ( AccountID, Token )
        VALUES  (vAccountID, pToken);
    RETURN vUserFullName;
END//

DROP FUNCTION IF EXISTS GetUserFullNameByAccountID//
CREATE FUNCTION GetUserFullNameByAccountID (
    pAccountID INT(11)
) RETURNS VARCHAR(255) charset utf8
READS SQL DATA
BEGIN
    DECLARE vUserFullName VARCHAR(255) charset utf8;

    SELECT CONCAT(accounts.LastName,' ',accounts.FirstName,' ',accounts.SecondName)
        INTO vUserFullName
        FROM accounts
        WHERE ID = pAccountID
        LIMIT 1;

    RETURN vUserFullName;
END//

DROP FUNCTION IF EXISTS UseRecoveryToken//
CREATE FUNCTION UseRecoveryToken (
    pToken VARCHAR(100) CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT -1;

    # set token used
    UPDATE recovery_tokens
        SET recovery_tokens.IsUsed = 1
        WHERE recovery_tokens.Token = pToken
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//

# -------------------------------------------------------------------------------------------
# Label: authorization
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS SignIn//
CREATE FUNCTION SignIn (
    pLoginOrMail VARCHAR(255) CHARSET utf8,
    pPassword  VARCHAR(64) CHARSET utf8
) RETURNS int(11) # account id
NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;

    #check account existence
    SELECT accounts.ID INTO vAccountID FROM accounts
        WHERE   accounts.Password = SHA1(pPassword) AND
                (accounts.Login = pLoginOrMail OR accounts.EMail = pLoginOrMail)
        LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN -1;
    END IF;

    INSERT INTO logs_signin (AccountID) VALUES (vAccountID);
    RETURN vAccountID;
END //

DROP FUNCTION IF EXISTS SignInByOpenID//
CREATE FUNCTION SignInByOpenID (
    pGlobalKey VARCHAR(255) CHARSET utf8
) RETURNS int(11) # account id
NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;

    #check account existence
    SELECT accounts.ID INTO vAccountID FROM accounts
    WHERE concat('st-', accounts.ExternalID) = pGlobalKey
        OR  concat('ss-', accounts.INILA) = pGlobalKey
    LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN -1;
    END IF;

    INSERT INTO logs_signin (AccountID) VALUES (vAccountID);
    RETURN vAccountID;
END //

DROP FUNCTION IF EXISTS SignInTeacherByOpenID//
CREATE FUNCTION SignInTeacherByOpenID (
    pGlobalKey VARCHAR(255) CHARSET utf8
) RETURNS int(11) # account id
NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;

    #check account existence
    SELECT accounts.ID INTO vAccountID
    FROM accounts
    JOIN teachers ON accounts.ID = teachers.AccountID
    WHERE teachers.INILA = pGlobalKey
    LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN -1;
    END IF;

    INSERT INTO logs_signin (AccountID) VALUES (vAccountID);
    RETURN vAccountID;
END //

-- returns:
-- -1 : invalid token
-- >0 : accountID
DROP FUNCTION IF EXISTS SignInByToken//
CREATE FUNCTION SignInByToken (pToken char(40) charset ascii) RETURNS int(11) # account id
NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;

    SELECT auth_tokens.AccountID INTO vAccountID
        FROM auth_tokens
        WHERE auth_tokens.Token = pToken
        LIMIT 1;

    IF vAccountID = -1 THEN
        RETURN -1; -- token not found
    END IF;

    UPDATE auth_tokens
        SET Accessed = CURRENT_TIMESTAMP
        WHERE auth_tokens.Token = pToken
        LIMIT 1;

    INSERT INTO logs_signin(AccountID) VALUES (vAccountID);
    RETURN vAccountID;
END //

DROP FUNCTION IF EXISTS DeleteAuthToken//
CREATE FUNCTION DeleteAuthToken(pToken char(40) CHARSET ascii) RETURNS int(11)
NO SQL
BEGIN
    DELETE FROM auth_tokens
        WHERE auth_tokens.Token = pToken;
    RETURN ROW_COUNT()-1;
END//

-- returns NULL if failed, token otherwise
DROP FUNCTION IF EXISTS CreateAuthToken//
CREATE FUNCTION CreateAuthToken(
    pAccountID int(11),
    pDescription varchar(60) CHARACTER SET utf8,
    pRightMask int(11)
) RETURNS char(40) charset ascii
NO SQL
BEGIN
    DECLARE vTries int(11) DEFAULT 13; -- number of tries to generate unique token
    DECLARE vCreated boolean DEFAULT FALSE;
    DECLARE vSeed int(11) DEFAULT FLOOR(4294967296 * RAND(CURRENT_TIMESTAMP ^ LAST_INSERT_ID() ^ (pAccountID << 10)));
    DECLARE vToken char(40) charset ascii DEFAULT SHA1(vSeed);

    WHILE NOT vCreated AND vTries > 0 DO BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET vCreated = FALSE;
        SET vToken = SHA1(RAND());
        SET vTries = vTries - 1;
        SET vCreated = TRUE;
        
        INSERT INTO auth_tokens(Token, AccountID, Description, Mask) VALUES (vToken, pAccountID, pDescription, pRightMask);
    END; END WHILE;

    RETURN IF(vCreated, vToken, NULL);
END//

# -------------------------------------------------------------------------------------------
# Label: roles
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS SetRole//
CREATE FUNCTION `SetRole` (
    `pID` INT,
    `pRole` INT
) RETURNS tinyint(1)
NO SQL
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN 0;
        UPDATE `accounts`
        SET accounts.UserRoleID = pRole
        WHERE accounts.ID = pID;
        RETURN 1;
    END //

# -------------------------------------------------------------------------------------------
# Label: text marks
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS GetTextMark//
CREATE FUNCTION `GetTextMark`(isAfterExam char(1), examType varchar(20), rate INT) RETURNS varchar(100) CHARSET utf8
NO SQL
    BEGIN
        declare markTextId int;
        declare markText varchar(100) charset utf8;
        if examType = 'credit' then
            if rate < 60 then
                set markTextId = 6;#незачет
            else
                set markTextId = 7;#зачет
            end if;
        elseif examType = 'gradingcredit' then
            if rate >= 85 then
                set markTextId = 5;#неуд.
            elseif rate >= 70 then
                set markTextId = 4;#хорошо
            elseif rate >= 60 then
                set markTextId = 3;#удовл.
            else
                set markTextId = 2;#неуд.
            end if;
        elseif isAfterExam = '0' then
            if rate < 38 then
                set markTextId = 1;#недопуск
            else
                return "";
            end if;
        else
            if rate >= 85 then
                set markTextId = 5;#неуд.
            elseif rate >= 70 then
                set markTextId = 4;#хорошо
            elseif rate >= 60 then
                set markTextId = 3;#удовл.
            else
                set markTextId = 2;#неуд.
            end if;
        end if;
        select name from text_mark where id = markTextId into markText;
        return markText;
    END//

-- Вспомогательные строковые функции

DROP FUNCTION IF EXISTS levenshtein//
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
    RETURNS INT
DETERMINISTIC
    BEGIN
        DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
        DECLARE s1_char CHAR;
        -- max strlen=255
        DECLARE cv0, cv1 VARBINARY(256);
        SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
        IF s1 = s2 THEN
            RETURN 0;
        ELSEIF s1_len = 0 THEN
            RETURN s2_len;
        ELSEIF s2_len = 0 THEN
            RETURN s1_len;
        ELSE
            WHILE j <= s2_len DO
                SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
            END WHILE;
            WHILE i <= s1_len DO
                SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
                WHILE j <= s2_len DO
                    SET c = c + 1;
                    IF s1_char = SUBSTRING(s2, j, 1) THEN
                        SET cost = 0; ELSE SET cost = 1;
                    END IF;
                    SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
                    IF c > c_temp THEN SET c = c_temp; END IF;
                    SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
                    IF c > c_temp THEN
                        SET c = c_temp;
                    END IF;
                    SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
                END WHILE;
                SET cv1 = cv0, i = i + 1;
            END WHILE;
        END IF;
        RETURN c;
    END//

DROP FUNCTION IF EXISTS levenshtein_ratio//
CREATE FUNCTION levenshtein_ratio( s1 VARCHAR(255), s2 VARCHAR(255) )
    RETURNS INT
DETERMINISTIC
    BEGIN
        DECLARE s1_len, s2_len, max_len INT;
        SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
        IF s1_len > s2_len THEN
            SET max_len = s1_len;
        ELSE
            SET max_len = s2_len;
        END IF;
        RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
    END//

DELIMITER ;