Pre-registrations
Pre-registrations exported procedures
Base_Preregistrations_Replace
This procedure does not require authorisation because it is generally expected only non-authenticated users would want pre-register. The 'info' field can be used to store any arbitrary data associated with the email.
DROP PROCEDURE Base_Preregistrations_Replace;
DELIMITER //
CREATE PROCEDURE Base_Preregistrations_Replace
(
$sid TEXT, # expecting csrf token
$name TEXT,
$email TEXT,
$mobile TEXT,
$info TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT Base_Web_Connections_Verify( $sid ) THEN
CALL Notice( 'Base_Preregistrations_Replace: INVALID_CSRF_TOKEN' );
ELSE BEGIN
DECLARE $PREREGISTRATION_ID INT DEFAULT 0;
CALL spgen_base_preregistrations_replace
(
$PREREGISTRATION_ID,
$name,
$email,
$mobile,
$info,
GenerateSalt(),
NOW(), # created
0, # sent
0 # confirmed
);
END; END IF;
END
//
DELIMITER ;
Base_Preregistrations_Confirm
DROP PROCEDURE Base_Preregistrations_Confirm;
DELIMITER //
CREATE PROCEDURE Base_Preregistrations_Confirm
(
$token TEXT
)
SQL SECURITY INVOKER
COMMENT 'PUBLIC'
BEGIN
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF "" = TRIM( $token ) THEN
CALL Notice( 'Base_Preregistrations_Confirm: INVALID_TOKEN' );
ELSE
UPDATE base_preregistrations
SET confirmed = NOW()
WHERE confirmed = 0
AND token = $token;
END IF;
END
//
DELIMITER ;
Pre-registrations internal procedures
Base_Preregistrations_Unsent
Deprecated. Will be replaced with an event triggered procedure that creates notifications for unsent pre-registration notifications.
DROP PROCEDURE Base_Preregistrations_Unsent;
DELIMITER //
CREATE PROCEDURE Base_Preregistrations_Unsent
(
$Sid CHAR(64)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
IF 'LOCAL' = $Sid AND IsLocalCaller() THEN
SELECT *, email AS TID FROM base_preregistrations WHERE confirmation_sent=0;
END IF;
END IF;
END
//
DELIMITER ;
Preregistrations_Sent
Deprecated. See above.
DROP PROCEDURE Base_Preregistrations_Sent;
DELIMITER //
CREATE PROCEDURE Base_Preregistrations_Sent
(
$Sid TEXT,
$TID CHAR(99)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSE
IF 'LOCAL' = $Sid AND IsLocalCaller() THEN
UPDATE base_preregistrations SET confirmation_sent=NOW() WHERE email=$TID;
END IF;
END IF;
END
//
DELIMITER ;