Base Schema

Users mobiles internal procedures

Base_Users_Mobiles_Replace is called when a user's mobile phone number is set.
Base_Users_Mobiles_Enrolments_Create is called from the mobile device to enrol a phone
Once an SMS is received, Base_Users_Mobiles_Enrolments_Confirm is called from mobile device to confirm phone.

Note: currently, as notifications is implemented in the user project, that must occur there.


DROP   PROCEDURE Base_Users_Mobiles_Replace;
DELIMITER //
CREATE PROCEDURE Base_Users_Mobiles_Replace
(
    $Sid                    CHAR(64),
    $USER                   INT,
    $user_mobile            CHAR(20)
)
SQL SECURITY DEFINER
BEGIN

SET $user_mobile = Internationalise_Mobile( $user_mobile );

CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );

IF @@read_only THEN

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';

ELSEIF FALSE THEN

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Users_Mobiles_Replace';

ELSE

    REPLACE INTO base_users_mobiles
    (  USER,  user_mobile,  user_mobile_device_id )
    VALUES
    ( $USER, $user_mobile,                     '' );

END IF;

END
//
DELIMITER ;


DROP   PROCEDURE Base_Users_Mobiles_Enrolments_Create;
DELIMITER //
CREATE PROCEDURE Base_Users_Mobiles_Enrolments_Create
(
    $user_mobile            CHAR(20),
    $user_mobile_device_id  CHAR(255)
)
SQL SECURITY DEFINER
BEGIN

DECLARE $USER      INT      DEFAULT  0;
DECLARE $sms_code  CHAR(4)  DEFAULT '';

SET $sms_code    = SUBSTRING( GenerateSalt(), 1, 4 );
SET $user_mobile = Internationalise_Mobile( $user_mobile );

SELECT USER INTO $USER FROM base_users_mobiles WHERE user_mobile = $user_mobile;

REPLACE INTO base_users_mobiles_enrolments
(  USER,  user_mobile,  user_mobile_device_id,  sms_code,  sms_code_created )
VALUES
( $USER, $user_mobile, $user_mobile_device_id, $sms_code,             NOW() );

END
//
DELIMITER ;


DROP   PROCEDURE Base_Users_Mobiles_Enrolments_Confirm;
DELIMITER //
CREATE PROCEDURE Base_Users_Mobiles_Enrolments_Confirm
(
    $user_mobile            CHAR(20),
    $user_mobile_device_id  CHAR(255),
    $sms_code               CHAR(20)
)
SQL SECURITY DEFINER
BEGIN

DECLARE $USER      INT       DEFAULT  0;
DECLARE $verified  BOOL      DEFAULT  0;
DECLARE $salt      CHAR(64)  DEFAULT '';
DECLARE $token     CHAR(64)  DEFAULT '';

SET $user_mobile = Internationalise_Mobile( $user_mobile );

SELECT USER INTO $USER FROM base_users_mobiles WHERE user_mobile = $user_mobile;

SET $verified = EXISTS
(
    SELECT *
    FROM   base_users_mobiles_enrolments
    WHERE  USER                  = $USER
    AND    user_mobile           = $user_mobile
    AND    user_mobile_device_id = $user_mobile_device_id
    AND    sms_code              = $sms_code
    AND    sms_code_verified     = 0
);

IF NOT $verified THEN

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_PARAMETERS IN Base_Users_Mobiles_Enrolments_Confirm';

ELSE

    #
    #   Finalise enrolment
    #

    UPDATE base_users_mobiles_enrolments
    SET
        sms_code_verified = NOW()

    WHERE USER                  = $USER
    AND   user_mobile           = $user_mobile
    AND   user_mobile_device_id = $user_mobile_device_id;

    #
    #   Generate mobile authentication token
    #

    SET $salt  = GenerateSalt();
    SET $token = GenerateSalt();

    UPDATE base_users_mobiles
    SET
        user_mobile_device_id   = $user_mobile_device_id,
        user_mobile_salt        = $salt,
        user_mobile_hash        = ComputeHash( $salt, $token )

    WHERE USER                  = $USER
    AND   user_mobile           = $user_mobile;

    SELECT $token AS token;

END IF;

END
//
DELIMITER ;