R__functions.sql

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

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

        RETURN ROW_COUNT()-1;
    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, pStudentID);
    END IF;

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

    # add rate, or update old
    SET @isUpdated = FALSE;
    INSERT INTO rating_table (StudentID, TeacherID, SubmoduleID, Rate, Date)
        VALUES  ( pStudentID, 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 (StudentID, SubmoduleID, TeacherID, Rate, Action )
        VALUES  (pStudentID, 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;
    DECLARE vChecker INT DEFAULT -1;

    # try to find student with that account id
    SELECT students.ID, CONCAT(students.LastName,' ',students.FirstName,' ',students.SecondName)
        INTO vChecker, vUserFullName
        FROM students
        WHERE students.AccountID = pAccountID
        LIMIT 1;

    IF vChecker <= 0 THEN # try to find teacher with that account id
        SELECT teachers.ID, CONCAT(teachers.LastName,' ',teachers.FirstName,' ',teachers.SecondName)
            INTO vChecker, vUserFullName
            FROM teachers
            WHERE teachers.AccountID = pAccountID
            LIMIT 1;

        IF vChecker <= 0 THEN
            RETURN '';
        END IF;
    END IF;

    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 //

-- 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//

DROP FUNCTION IF EXISTS CreateAuthToken//
CREATE FUNCTION CreateAuthToken(
    pAccountID int(11),
    pRightMask int(11)
) RETURNS char(40) charset ascii
NO SQL
BEGIN
    DECLARE vCounter int(11) DEFAULT 666;
    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 vCounter > 0 DO BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET vCreated = FALSE;
        SET vToken = SHA1(RAND());
        INSERT INTO auth_tokens(Token, AccountID, Mask) VALUES (vToken, pAccountID, pRightMask);

        SET vCreated = TRUE;
        SET vCounter = vCounter - 1;
    END; END WHILE;

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

DELIMITER ;