Functions

To distinguish custom functions from inbuilt functions which are spelt in ALL CAPS, custom functions are spelt in CamelCase. This is to distinguish functions from procedures, which are spelt using underscores. Note: functions that READ SQL DATA from tables are also spelt with underscores. Note: in MySQL function and procedure names are case-insensitive, but this convention aids comprehension.

Deterministic

Authorisation

IsEventScheduler()
Returns true if the user is the event scheduler account.
IsLocalCaller()
Returns true if the user is logged in from a local shell.
IsLocalRootCaller()
Returns true if the user is logged in from a local shell and is authenticated as 'root'.
IsRootCaller()
Returns true if the user is authenticated as 'root'.

Cryptograpy

GenerateSalt()
Returns a 64 character salt string generated using SHA2.
ComputeHash( $salt, $value )
Returns the hash produced by hashing $value with $salt.
MyDecrypt( $enckey, $value )
Returns $value decrypted using AES using a decryption key generated by SHA2 hashing $enckey after it has been DES encrypted using a keyfile on disk.
MyEncrypt( $enckey, $value )
Returns $value encrypted using AES using a encryption key generated by SHA2 hashing $enckey after it has been DES encrypted using a keyfile on disk.

Date and time functions

AsAppleTime( $datetime )
Returns the current date and time in 'YYYY-MM-DD+HHTmm:ss.000+10:00:00' format.
ConvertTo( $datetime )
Returns the passed $datetime with one hours subtracted.
ConvertToLocalTimeZone( $appletime )
Returns the $appletime converted to the local timezone.
ConvertWeekToDate( $year, $week )
Returns the date of the Monday within the passed week.
ConvertZoneToTime( $zone )
Returns the timezone with plus or minus stripped, e.g. '+10:00' to '10:00'.
DateOfNextDayname( $date, $dayname )
Returns the date of the next day whose name matches $dayname.
GetTime( $datetime )
Returns the time part of a datetime.
GetTimeZone()
Returns the current timezone offset from GMT.

Geo functions

DistanceBetween( $lat1, $lon1, $lat2, $lon2 )
Returns the distance in km between the pair of coordinates - deprecated use MetresBetween or KilometresBetween.
MetresBetween( $lat1, $lon1, $lat2, $lon2 )
Returns the distance in metres between the pair of coordinates.
KilometresBetween( $lat1, $lon1, $lat2, $lon2 )
Returns the distance in km between the pair of coordinates.

Parameter handling

CheckLimitOffset( $limit, $offset )
Ensures that $limit and $offset are positive integers by adjusting them to high number of they are not.

String conversion

DecodeHTMLEntities( $text )
Returns text with some html entities converted back to original special characters.

String lists

GetJth( $Text, $Delimiter, $J )
Return the $Jth bit of text from $Test when using $Delimiter.
ReplaceWith( $content, $delimiter, $equals, $dictionary )
Replaces variables in content with values from dictionary.

Value testing

IfEmpty( $text1, $text2 )
If $text1 is null or an empty string when trimmed of whitespace returns $text2; otherwise $text1.
IfNoDate( $date, $alternate )
If $date is null or '0' return $alternate; otherwise return $date.
IfNone( $date, $alternate )
If $date is null or '0' return $alternate; otherwise return $date.
IfVoid( $text1, $text2 )
If $text1 = '' then return $text2; otherwise return $text1.
IfZero( $id1, $id2 )
If $id1 is zero, then return $id2; otherwise return $id1.
IsNotEmpty( $text )
If $text is null or an empty string when trimmed of whitesapce returns FALSE; otherwise TRUE.
OrNull( $text1 )
If $text1 is null or an empty string when trimmed of whitespace returns NULL; otherwise $text1.

Non-deterministic

Base functions - Files

Base_Files_Exists_By_Kind( $sid, $USER, $kind )
Returns true if a file of the specified 'kind' exists; otherwise false.

Base functions - Groups

Base_Groups_Members_Contains( $GROUP_ID, $USER )
Returns true if the specified 'USER' is a member of the specified 'GROUP'.

Base functions - Users

Base_Users_Check_Password( $USER, $Password )
Returns true if the passed 'Password' is correct for the passed 'USER'; otherwise false.
Base_Users_Exists( $Email )
Returns true if a user with the specified 'Email' exists; otherwise false.
Base_Users_Get_Field( $USER, $field )
Returns 'type' from 'users_uids' table for the specified 'USER' if 'type' is specified; otherwise empty string.
Base_Users_Send_Resets_Exists( $token )
Returns true if passed 'token' is valid; otherwise false.
Base_Users_Verify_Credentials( $Email, $Password )
Returns true if the passed credentails are valid; otherwise false.

Misc

IsReadOnly()
Returns true if global variable 'readonly' is true.
LAST_INSERT_GUID()
Returns the session variable '@LAST_INSERT_GUID'.
ReadOnly()
Returns true if global variable 'readonly' is true.
Retrieve_Parameters_For( $database, $name )
Returns the 'param_list' associated with the procedure $name defined in the $database.

Implementations

Authorisation

IsEventScheduler 

DROP   FUNCTION IsEventScheduler;
DELIMITER //
CREATE FUNCTION IsEventScheduler
()
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    return (USER() = 'event_scheduler@localhost' );

END
//
DELIMITER ;

IsLocalCaller 

DROP   FUNCTION IsLocalCaller;
DELIMITER //
CREATE FUNCTION IsLocalCaller
()
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    return NOT USER() = 'public@localhost' AND ((USER() LIKE '%@localhost') OR IsRootCaller() OR IsEventScheduler());

END
//
DELIMITER ;

is_local_root_caller 

DROP   FUNCTION IsLocalRootCaller;
DELIMITER //
CREATE FUNCTION IsLocalRootCaller
()
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    return (USER() = 'root@localhost' );

END
//
DELIMITER ;

DROP   FUNCTION IsRootCaller;
DELIMITER //
CREATE FUNCTION IsRootCaller
()
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    return (USER() LIKE 'root@%' );

END
//
DELIMITER ;

DROP   FUNCTION TranslateToOrgUsername;
DELIMITER //
CREATE FUNCTION TranslateToOrgUsername
(
    $username  TEXT,
    $org_code  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $inbox        TEXT  DEFAULT '';
    DECLARE $domain       TEXT  DEFAULT '';
    DECLARE $provisional  TEXT  DEFAULT '';

    SET $org_code    = TRIM( $org_code                   );
    SET $inbox       = TRIM( GetJth( $username, '@', 1 ) );
    SET $domain      = TRIM( GetJth( $username, '@', 2 ) );
    SET $provisional = CONCAT( $inbox, '+', $org_code, '@', $domain );

    IF "" != $org_code AND "" != $inbox AND "" != $domain AND EXISTS
    (
        SELECT *
        FROM   base_users
        WHERE  user_deleted = 0
        AND
        (
            email             = $provisional
            OR
            email_provisional = $provisional
        )
    )
    THEN

        SET $username = $provisional;

    END IF;

    return $username;

END
//
DELIMITER ;

Cryptography

GenerateSalt 

DROP   FUNCTION GenerateSalt;
DELIMITER //
CREATE FUNCTION GenerateSalt
()
RETURNS CHAR(64)
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE salt CHAR(64);

SET salt = RAND();

SET salt = SHA2( salt, 256 );

return salt;

END
//
DELIMITER ;

ComputeHash 

DROP   FUNCTION ComputeHash;
DELIMITER //
CREATE FUNCTION ComputeHash
(
  salt           CHAR(64),
  value          TEXT
)
RETURNS CHAR(64)
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE enckey TEXT;
DECLARE string TEXT;
DECLARE hash   CHAR(64);

#
#   This value has now been hardcoded to deal with the deprecation
#   of DES_ENCRYPT, which used to allow mingled encryption with an external key.
#   This should be the value that would have been used if no external key was used.
#

SET enckey = "a514e1386e06661c1ca48b87dbada4d010b8149adca8b827c7fc23e2c49ad061";
SET string = CONCAT( enckey, salt, value );
SET hash   = SHA2( string, 256 );

return hash;

END
//
DELIMITER ;

MyDecrypt 

DROP   FUNCTION MyDecrypt;
DELIMITER //
CREATE FUNCTION MyDecrypt
(
  enckey            TEXT,
  encvalue          TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE hashkey TEXT;

IF "" != encvalue THEN

  #
  #   This value has now been hardcoded to deal with the deprecation
  #   of DES_ENCRYPT, which used to allow mingled encryption with an external key.
  #   This should be the value that would have been used if no external key was used.
  #

  SET hashkey = "a514e1386e06661c1ca48b87dbada4d010b8149adca8b827c7fc23e2c49ad061";

  return AES_DECRYPT( UNHEX( encvalue ), hashkey );

ELSE

  return "";

END IF;

END
//
DELIMITER ;

MyEncrypt 

DROP   FUNCTION MyEncrypt;
DELIMITER //
CREATE FUNCTION MyEncrypt
(
  $enckey         TEXT,
  $value          TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE $hashkey TEXT;

IF "" != $value THEN

    #
    #   This value has now been hardcoded to deal with the deprecation
    #   of DES_ENCRYPT, which used to allow mingled encryption with an external key.
    #   This should be the value that would have been used if no external key was used.
    #

    SET $hashkey = "a514e1386e06661c1ca48b87dbada4d010b8149adca8b827c7fc23e2c49ad061";

    return HEX( AES_ENCRYPT( $value, $hashkey ) );

ELSE

    return "";

END IF;

END
//
DELIMITER ;

RandomNumber 

DROP   FUNCTION RandomNumber;
DELIMITER //
CREATE FUNCTION RandomNumber
(
    $max  INT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $text  TEXT  DEFAULT '';
    DECLARE $len   INT   DEFAULT  0;

    SET $text = $max;
    SET $len  = LENGTH( $text );
    SET $text = FLOOR( RAND() * $max );

    return LPAD( $text, $len, '0' );

END
//
DELIMITER ;

Old_ComputeHash 

DROP   FUNCTION Old_ComputeHash;
DELIMITER //
CREATE FUNCTION Old_ComputeHash
(
  $salt                             INT(11),
  $value                           CHAR(99)
)
RETURNS CHAR(16)
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

return MD5( concat($value, $salt) );

END
//
DELIMITER ;

Date and time functions

GetTimeZone 

DROP   FUNCTION GetTimeZone;
DELIMITER //
CREATE FUNCTION GetTimeZone
()
RETURNS CHAR(6)
DETERMINISTIC
BEGIN

DECLARE $diff TIME DEFAULT 0;

SET $diff = TIMEDIFF(NOW(), UTC_TIMESTAMP);

IF 0 <= $diff THEN
  return CONCAT( "+", $diff );
ELSE
  return $diff;
END IF;

END
//
DELIMITER ;

AsAppleTime 

DROP   FUNCTION AsAppleTime;
DELIMITER //
CREATE FUNCTION AsAppleTime
(
  $datetime DATETIME
)
RETURNS TEXT
DETERMINISTIC
BEGIN

return CONCAT( DATE( $datetime ), "T", TIME( $datetime ), ".000", GetTimeZone() );

END
//
DELIMITER ;

ConvertZoneToTime 

DROP   FUNCTION ConvertZoneToTime;
DELIMITER //
CREATE FUNCTION ConvertZoneToTime
(
  $zone CHAR(6)
)
RETURNS TIME
DETERMINISTIC
BEGIN

return CONVERT( REPLACE( $zone, "+", " " ), TIME );

END
//
DELIMITER ;

ConvertTo 

DROP   FUNCTION ConvertTo;
DELIMITER //
CREATE FUNCTION ConvertTo
(
  $datetime DATETIME
)
RETURNS DATETIME
DETERMINISTIC
BEGIN

DECLARE $dx DATETIME DEFAULT 0;

SET $dx = DATE_SUB( $datetime, INTERVAL 1 HOUR );

return $dx;

END
//
DELIMITER ;

ConvertToLocalTimeZone 

DROP   FUNCTION ConvertToLocalTimeZone;
DELIMITER //
CREATE FUNCTION ConvertToLocalTimeZone
(
  $appletime CHAR(29)
)
RETURNS DATETIME
DETERMINISTIC
BEGIN

# 2014-12-30T10:00:00.000+10:00

DECLARE $len           INT;
DECLARE $tmp      CHAR(29);
DECLARE $datetime DATETIME;
DECLARE $zone      CHAR(6);
DECLARE $test         TEXT;

SET $zone = "";
SET $test = "";

IF "" != $appletime THEN

  SET $len = LENGTH( $appletime );

  IF 10 = $len THEN

    SET $datetime = CONVERT( $appletime, DATETIME );
    SET $test     = CONCAT_WS( "|", $datetime );

  ELSEIF 19 = $len OR 23 = $len THEN

    SET $tmp      = REPLACE( $appletime, "T", " " );
    SET $tmp      = SUBSTRING( $tmp, 1, 19 );
    SET $datetime = CONVERT( $tmp, DATETIME );
    SET $test     = CONCAT_WS( "|", $datetime );

  ELSEIF 19 = $len OR 23 = $len THEN

    SET $tmp      = REPLACE( $appletime, "T", " " );
    SET $tmp      = SUBSTRING( $tmp, 1, 19 );
    SET $datetime = CONVERT( $tmp, DATETIME );
    SET $test     = CONCAT_WS( "|", $datetime );

  ELSEIF 25 <= $len THEN

    SET $tmp      = REPLACE( $appletime, "T", " " );
    SET $tmp      = SUBSTRING( $tmp, 1, 19 );
    SET $datetime = CONVERT( $tmp, DATETIME );
    SET $zone     = SUBSTRING( $appletime, -6 );
    SET $zone     = REPLACE( $zone, ' ', '+' );
    SET $datetime = CONVERT_TZ( $datetime, $zone, GetTimeZone() );
    SET $test     = CONCAT_WS( "|", $tmp, $datetime, $zone, GetTimeZone() );

    #IF NULL = $datetime THEN
    #  SET $datetime = 0;
    #END IF;

  ELSE

    SET $test = "Wrong length";
    SET $datetime = 1;

  END IF;

ELSE

  SET $datetime = 2;

END IF;

return $datetime;

END
//
DELIMITER ;

ConvertToUTCTimeZone 

DROP   FUNCTION ConvertToUTCTimeZone;
DELIMITER //
CREATE FUNCTION ConvertToUTCTimeZone
(
    $datetimezone  TEXT
)
RETURNS DATETIME
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $sys_datetime  DATETIME DEFAULT 0;
    DECLARE $utc_datetime  DATETIME DEFAULT 0;

    SET $sys_datetime = ConvertToLocalTimeZone( $datetimezone );
    SET $utc_datetime = CONVERT_TZ( $sys_datetime, '+10:00', '+00:00' );

    return $utc_datetime;

END
//
DELIMITER ;

ConvertWeekToDate 

DROP   FUNCTION ConvertWeekToDate;
DELIMITER //
CREATE FUNCTION ConvertWeekToDate
(
  $year YEAR,
  $week INT(2)
)
RETURNS DATE
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE $date_string   TEXT;
DECLARE $start_of_week DATE;

SET $date_string = CONCAT( $year, $week, " MONDAY" );

return STR_TO_DATE( $date_string, '%X%V %W' );

END
//
DELIMITER ;

GetTime 

DROP   FUNCTION GetTime;
DELIMITER //
CREATE FUNCTION GetTime
(
   $datetime TEXT
)
RETURNS TIME
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE $time TIME DEFAULT 0;
DECLARE $one  CHAR(10);
DECLARE $two  CHAR(10);
DECLARE $use  CHAR(10);

SET $one = GET_JTH( $datetime, " ", 1 );
SET $two = GET_JTH( $datetime, " ", 2 );

IF "" != $two THEN

    SET $use = $two;

ELSE

    SET $use = $one;

END IF;

IF 4 = LENGTH( $use ) THEN

    SET $time = CONCAT( SUBSTR( $use, 1, 2 ), ":", SUBSTR( $use, 3, 2 ), ":00" );

ELSEIF 5 = LENGTH( $use ) THEN

    SET $time = CONCAT( $use, ":00" );

ELSEIF 8 = LENGTH( $use ) THEN

    SET $time = $use;

ELSE

    SET $time = "12:59:59";

END IF;

return $time;

END
//
DELIMITER ;

DateOfNextDayname 

DROP   FUNCTION DateOfNextDayname;
DELIMITER //
CREATE FUNCTION DateOfNextDayname
(
    $date         DATE,
    $dayname      CHAR(3)
)
RETURNS DATE
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

#
# YEARWEEK( 2017-02-08 ) = '201706'
#
# STR_TO_DATE( CONCAT( '201706', ' ', 'Wed' ), '%X%V %a' ) = '2017-02-08'
#
# DATE_ADD( '2017-02-08', INTERVAL 1 WEEK ) = '2017-02-15'
#

DECLARE $date_of_dayname_in_this_week  DATE  DEFAULT 0;
DECLARE $date_of_next_dayname          DATE  DEFAULT 0;

SET $date_of_dayname_in_this_week = STR_TO_DATE( CONCAT( YEARWEEK( $date ), ' ', $dayname ), '%X%V %a' );

IF $date <= $date_of_dayname_in_this_week THEN

    SET $date_of_next_dayname = $date_of_dayname_in_this_week;

ELSE

    SET $date_of_next_dayname = STR_TO_DATE( CONCAT( YEARWEEK( DATE_ADD( $date, INTERVAL 1 WEEK ) ), ' ', $dayname ), '%X%V %a' );

END IF;

return $date_of_next_dayname;

END
//
DELIMITER ;

Geo functions

Finds the distance (in kilometers) between two points.

Based on: http:www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Deprecated use MetresBetween or KilometresBetween 

DROP   FUNCTION DistanceBetween;
DELIMITER //
CREATE FUNCTION DistanceBetween
(
  $lat1  FLOAT,
  $lon1  FLOAT,
  $lat2  FLOAT,
  $lon2  FLOAT
)
RETURNS FLOAT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE $radius_earth_km INT DEFAULT 6371;

DECLARE $distance FLOAT;
DECLARE $sin1     FLOAT;
DECLARE $sin2     FLOAT;
DECLARE $cos1     FLOAT;
DECLARE $cos2     FLOAT;
DECLARE $power1   FLOAT;
DECLARE $power2   FLOAT;

SET $sin1 = SIN( ($lat1 - $lat2) * pi()/180/2);
SET $sin2 = SIN( ($lon1 - $lon2) * pi()/180/2);

SET $cos1 = COS( $lat1 * pi()/180);
SET $cos2 = COS( $lat2 * pi()/180);

SET $power1 = POWER( $sin1, 2 );
SET $power2 = POWER( $sin2, 2 );

SET $distance = $radius_earth_km * 2 * ASIN( SQRT( $power1 + $cos1 * $cos2 * $power2 ) );

return $distance;

END
//
DELIMITER ;

MetresBetween 

DROP   FUNCTION MetresBetween;
DELIMITER //
CREATE FUNCTION MetresBetween
(
  $lat1  FLOAT,
  $lon1  FLOAT,
  $lat2  FLOAT,
  $lon2  FLOAT
)
RETURNS INT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

return CEIL( KilometresBetween( $lat1, $lon1, $lat2, $lon2 ) * 1000 );

END
//
DELIMITER ;

KilometresBetween 

DROP   FUNCTION KilometresBetween;
DELIMITER //
CREATE FUNCTION KilometresBetween
(
  $lat1  FLOAT,
  $lon1  FLOAT,
  $lat2  FLOAT,
  $lon2  FLOAT
)
RETURNS FLOAT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

return DistanceBetween( $lat1, $lon1, $lat2, $lon2 );

END
//
DELIMITER ;

String functions

Base64Decode 

DROP   FUNCTION Base64Decode;
DELIMITER //
CREATE FUNCTION Base64Decode
(
    $text  LONGTEXT
)
RETURNS LONGTEXT
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN

    return CAST( FROM_BASE64( $text ) AS CHAR );

END
//
DELIMITER ;

CallToString 

DROP   FUNCTION CallToString;
DELIMITER //
CREATE FUNCTION CallToString
(
    $sep   CHAR,
    $list  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $ret    TEXT  DEFAULT '';
    DECLARE $next   TEXT  DEFAULT '';
    DECLARE $i      INT   DEFAULT  0;
    DECLARE $count  INT   DEFAULT  0;

    #
    #   Determine the number of separators and therefore parameters.
    #
    SET $count = LENGTH( $list ) - LENGTH( REPLACE( $list, $sep, "" ) ) + 1;

    WHILE $i < $count
    DO

        SET $i = $i + 1;

        SET $next = GetJth( $list, $sep, $i );

        IF $i = 1 THEN
            IF $next = "" THEN
                SET $ret = NULL;                                # NULL
            ELSE
                SET $ret = CONCAT( $ret, $next, '(' );          # Magic(
            END IF;
        ELSE
            IF $i = $count AND $i = 2 THEN
                SET $ret = CONCAT( $ret, ")" );                 # Magic()
            ELSEIF $i = 2 THEN
                SET $ret = CONCAT( $ret, " '", $next, "'" );    # Magic( 'XXX'
            ELSEIF $i > 2 AND $i < $count THEN
                SET $ret = CONCAT( $ret, ", '", $next, "'" );   # Magic( 'XXX', 'YYY'
            ELSEIF $i = $count THEN
                SET $ret = CONCAT( $ret, ", '", $next, "' )" ); # Magic( 'XXX', 'YYY' )
            END IF;
        END IF;

    END WHILE;

    return $ret;

END
//
DELIMITER ;

DecodeHTMLEntities 

DROP   FUNCTION DecodeHTMLEntities;
DELIMITER //
CREATE FUNCTION DecodeHTMLEntities
(
    $text LONGTEXT
)
RETURNS LONGTEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

SET $text = REPLACE( $text, '&', '!!AMP!!' );

IF INSTR( $text, '&' ) THEN
    SET $text = REPLACE( $text, ''',  "'" );
    SET $text = REPLACE( $text, ''',  "'" );
    SET $text = REPLACE( $text, '’', "'" );
    SET $text = REPLACE( $text, '"',  '"' );
    SET $text = REPLACE( $text, '<',    '<' );
    SET $text = REPLACE( $text, '>',    '>' );
    SET $text = REPLACE( $text, ' ',  ' ' );
END IF;

SET $text = REPLACE( $text, '!!AMP!!', '&' );

return $text;

END
//
DELIMITER ;

Empty 

DROP   FUNCTION Empty;
DELIMITER //
CREATE FUNCTION Empty
(
    $text  LONGTEXT
)
RETURNS BOOL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN

    return ISNULL( $text ) OR "" = TRIM( $text );

END
//
DELIMITER ;

Get Jth

MySQL SQL Stored Procedures do not provide support for any kind of arrays (other than creating tables), so, unsurprisingly, there are no array handling functions.

To get around this limitation, strings with delimiters can be used to store lists of items. The 'GET_JTH' function below returns such an item from the a list.

Note, the first element of a list corresponds to the index 1. This is to remain consistent with other MySQL functions that treat 1 as the first element. 

DROP   FUNCTION GetJth;
DELIMITER //
CREATE FUNCTION GetJth
(
  $Text                   LONGTEXT,
  $Delimiter              TEXT,
  $I                      INT(11)
)
RETURNS LONGTEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

DECLARE $tmp      LONGTEXT  DEFAULT '';
DECLARE $ret      LONGTEXT  DEFAULT '';
DECLARE $test     LONGTEXT  DEFAULT '';

IF 0 = LENGTH( $Delimiter ) THEN

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

END IF;

#
#    Compare whether substring returned is same for i and i-1.
#    If so have run out of components, set return as "".
#

SET $tmp  = SUBSTRING_INDEX( $Text, $Delimiter, $I );
SET $test = SUBSTRING_INDEX( $Text, $Delimiter, $I - 1 );

IF $tmp != $test THEN

    SET $ret = SUBSTRING_INDEX( $tmp, $Delimiter, -1 );

END IF;

RETURN $ret;

END
//
DELIMITER ;

ReplaceWith

Replace variables names adorned with percentage characters (i.e., %variable%) with the corresponding values present within the string encoded dictionary. The encoding of the dictionary is: 

DICTIONARY := KEYPAIR, [DELIMITER, KEYPAIR]
KEYPAIR    := KEY, EQUALS, VALUE

Where KEY and VALUE can contain any ascii character except the DELIMITER, or EQUALS.

For example: 

SELECT ReplaceWith( "Dear %given_name% %family_name%, Please following the link below.", ",", "=", "given_name=Daniel,family_name=Bradley" );

Or 

SELECT ReplaceWith( "Dear %given_name% %family_name%, Please following the link below.", "|", ":", "given_name:Daniel|family_name:Bradley" );

DROP   FUNCTION ReplaceWith;
DELIMITER //
CREATE FUNCTION ReplaceWith
(
    $content     LONGTEXT,
    $delimiter   TEXT,
    $equals      TEXT,
    $dictionary  LONGTEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $keypair  LONGTEXT  DEFAULT '';
    DECLARE $key      LONGTEXT  DEFAULT '';
    DECLARE $val      LONGTEXT  DEFAULT '';
    DECLARE $i        INT       DEFAULT  1;

lp: WHILE TRUE DO

        SET $keypair = GetJth( $dictionary, $delimiter, $i ); 

        IF "" = $keypair THEN

            LEAVE lp;

        ELSE

            SET $key = CONCAT( '%', TRIM( GetJth( $keypair, $equals, 1 ) ), '%' );
            SET $val =              TRIM( GetJth( $keypair, $equals, 2 ) );

            SET $content = REPLACE( $content, $key, $val );

        END IF;

        SET $i = $i + 1;

    END WHILE;

    return $content;

END
//
DELIMITER ;

DROP   FUNCTION ExtractUser;
DELIMITER //
CREATE FUNCTION ExtractUser
(
    $email  TEXT
)
RETURNS TEXT CHARSET latin1
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    SET $email = LOWER( $email );
    SET $email = SUBSTRING_INDEX( $email, '@', 1 );

    return CONCAT( '%', $email, '%' );

END
//
DELIMITER ;

DROP   FUNCTION CalculatePasswordStrength;
DELIMITER //
CREATE FUNCTION CalculatePasswordStrength
(
    $password  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $strength ENUM( "NONE", "WEAK", "MEDIUM", "STRONG", "ULTRA" );
    DECLARE $length   INT  DEFAULT 0;

    SET $strength = "NONE";
    SET $length   = LENGTH( $password );

    IF     $length > 20 THEN  SET $strength = "ULTRA";
    ELSEIF $length > 15 THEN  SET $strength = "STRONG";
    ELSEIF $length >  7 THEN  SET $strength = "MEDIUM";
    ELSE                      SET $strength = "WEAK";
    END IF;

    #
    #   Test for username in password
    #
    #IF EXISTS( SELECT * FROM base_users WHERE NOT email='' AND LOWER( $password ) LIKE ExtractUser( email ) ) THEN
    #    SET $strength = "WEAK";
    #END IF;

    IF "MEDIUM" = $strength THEN

        #
        #   Optional site/policy specific tests
        #

        #
        #   Test for uppercase
        #
        #
        # IF BINARY $password = BINARY UPPER( $password )  THEN
        #     SET $strength = "WEAK";
        # END IF;

        #
        #   Test for lowercase
        #
        #
        # IF BINARY $password = BINARY LOWER( $password ) THEN
        #    SET $strength = "WEAK";
        # END IF;

        #
        #   Test for numeral
        #
        #
        # IF NOT $password REGEXP '[0-9]' THEN
        #    SET $strength = "WEAK";
        # END IF;

        #
        #   Test for symbol
        #
        #
        # IF NOT $password REGEXP '~|`|!|@|#|\\$|%|\\^|&|\\*|\\(|\\)|_|-|\\+|=|\\{|\\}|\\[|\\]|\\||:|;|"|\'|<|,|>|\\.|\\?|/' THEN
        #    SET $strength = "WEAK";
        # END IF;

        SET @nop = 0;

    END IF;

    return $strength;

END
//
DELIMITER ;

DROP   FUNCTION CalculatePasswordStrength_Old;
DELIMITER //
CREATE FUNCTION CalculatePasswordStrength_Old
(
    $password  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $strength ENUM( "NONE", "WEAK", "MEDIUM", "STRONG", "ULTRA" );
    DECLARE $length   INT  DEFAULT 0;

    SET $length = LENGTH( $password );

    IF     $length > 20 THEN  SET $strength = "ULTRA";
    ELSEIF $length > 15 THEN  SET $strength = "STRONG";
    ELSEIF $length >  5 THEN  SET $strength = "MEDIUM";
    ELSE                      SET $strength = "WEAK";
    END IF;

    return $strength;

END
//
DELIMITER ;

DROP   FUNCTION Title;
DELIMITER //
CREATE FUNCTION Title
(
    $text  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $title  TEXT  DEFAULT '';
    DECLARE $word   TEXT  DEFAULT '';
    DECLARE $i      INT   DEFAULT  0;

    SET $title = "";

    REPEAT

        SET $i    = $i + 1;
        SET $word = GetJth( $text, ' ', $i );

        SET $title = CONCAT( $title, ' ', SUBSTRING( $word, 1, 1 ), LOWER( SUBSTRING( $word, 2 ) ) );

    UNTIL "" = $word END REPEAT;

    return TRIM( $title );

END
//
DELIMITER ;

DROP FUNCTION   ParseInt;
DELIMITER //
CREATE FUNCTION ParseInt
(
    $number  TEXT
)
RETURNS INT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    SET $number = TRIM( $number );

    IF "" = $number THEN
    
        return NULL;
    
    ELSE

        SET $number = TRIM( LEADING '0' FROM $number );

        IF "" = $number THEN
            return 0;
        ELSE
            SET $number = CONVERT( $number, SIGNED );

            IF $number = 0 THEN
                return NULL;
            ELSE
                return $number;
            END IF;
        END IF;

    END IF;

END
//
DELIMITER ;

DROP   FUNCTION JSONExtractPath;
DELIMITER //
CREATE FUNCTION JSONExtractPath
(
    $text  TEXT,
    $path  TEXT
)
RETURNS TEXT
READS SQL DATA
BEGIN

    DECLARE $value  TEXT  DEFAULT NULL;
    DECLARE $json   JSON;

    IF JSON_VALID( $text ) THEN
        SET $json  = $text;
        SET $value = JSON_UNQUOTE( JSON_EXTRACT( $json, $path ) );
    END IF;

    return $value;

END
//
DELIMITER ;

DROP   FUNCTION ReplaceFirst;
DELIMITER //
CREATE FUNCTION ReplaceFirst
(
    $input    TEXT,
    $search   TEXT,
    $replace  TEXT
)
RETURNS TEXT
DETERMINISTIC
BEGIN

    DECLARE $pos INT  DEFAULT 0;

    SET $pos = LOCATE( $search, $input );

    IF 0 = $pos THEN

        RETURN $input;

    ELSE

        RETURN
        CONCAT
        (
            SUBSTRING( $input, 1, $pos - 1 ),
            $replace,
            SUBSTRING( $input, $pos + LENGTH( $search ) )
        );
    
    END IF;

END
//
DELIMITER ;

DROP   FUNCTION Slice;
DELIMITER //
CREATE FUNCTION Slice
(
    $text       TEXT,
    $delimiter  TEXT,
    $first      INT,
    $last       INT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $tmp    TEXT  DEFAULT   '';
    DECLARE $value  TEXT  DEFAULT NULL;
    DECLARE $i      INT   DEFAULT    1;

    REPEAT

        IF $first <= $i AND $i <= $last THEN

            SET $tmp = GetJth( $text, $delimiter, $i );

            IF LENGTH( $tmp ) THEN
                SET $value = CONCAT_WS( $delimiter, $value, $tmp );
            END IF;

        END IF;

        SET $i = $i + 1;

    UNTIL $i > $last END REPEAT;

    return $value;

END
//
DELIMITER ;

Throw functions

Below we define various functions that will throw an sqlexception, which will most likely be passed back to user code.

The intent of this is to allow the separation of more serious issues to be identified that should be rectified immediately, and those issues related to user error.

The following functions will throw a user defined sql exception with the following values:

Throw and Info are synonimous and should be used for user errors that are expected to be propagated back to the user.

Notice should be used for INVALID_AUTHORISATION type errors that are mainly due to the user being inactive and their session expirying.

Warning should be used for when a call's parameters don't pass validation. Ideally, the front-end will be modified to do the validation to prevent the situation from occurring.

Error should be used for unexpected errors that need to be addressed immediately, such as when:

  1. An APIKey is invalid or of the wrong type
  2. An unexpected state is encountered
  3. An unexpected case option is passed
  4. ???

Throw 

DROP   PROCEDURE Throw;
DELIMITER //
CREATE PROCEDURE Throw
(
    $message  TEXT
)
SQL SECURITY INVOKER
BEGIN

    SET @message  = $message;
    SET @severity = "Unknown";

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = $message;

END
//
DELIMITER ;

Info 

DROP   PROCEDURE Info;
DELIMITER //
CREATE PROCEDURE Info
(
    $message  TEXT
)
SQL SECURITY INVOKER
BEGIN

    SET @message  = $message;
    SET @severity = "Info";

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = $message;

END
//
DELIMITER ;

Notice 

DROP   PROCEDURE Notice;
DELIMITER //
CREATE PROCEDURE Notice
(
    $message  TEXT
)
SQL SECURITY INVOKER
BEGIN

    SET @message  = $message;
    SET @severity = "Notice";

    SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = $message;

END
//
DELIMITER ;

Warning 

DROP   PROCEDURE Warning;
DELIMITER //
CREATE PROCEDURE Warning
(
    $message  TEXT
)
SQL SECURITY INVOKER
BEGIN

    SET @message  = $message;
    SET @severity = "Warning";

    SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = $message;

END
//
DELIMITER ;

Error 

DROP   PROCEDURE Error;
DELIMITER //
CREATE PROCEDURE Error
(
    $message  TEXT
)
SQL SECURITY INVOKER
BEGIN

    SET @message  = $message;
    SET @severity = "Error";

    SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = $message;

END
//
DELIMITER ;

Value testing functions

CheckLimitOffset 

DROP   PROCEDURE CheckLimitOffset;
DELIMITER //
CREATE PROCEDURE CheckLimitOffset
(
INOUT $limit                       INT(11),
INOUT $offset                      INT(11)
)
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN

IF "" = $limit THEN
  SET $limit = 1000000;
END IF;

IF "" = $offset THEN
  SET $offset = 0;
END IF;

END
//
DELIMITER ;

IfEmpty 

DROP   FUNCTION IfEmpty;
DELIMITER //
CREATE FUNCTION IfEmpty
(
  $text1          TEXT,
  $text2          TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF ISNULL($text1) OR '' = TRIM($text1) THEN

    return $text2;

ELSE

    return $text1;

END IF;

END
//
DELIMITER ;

IfNoDate 

DROP   FUNCTION IfNoDate;
DELIMITER //
CREATE FUNCTION IfNoDate
(
  $date       DATE,
  $alternate  DATE
)
RETURNS DATE
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF 0 = $date OR ISNULL($date) THEN

    return $alternate;

ELSE

    return $date;

END IF;

END
//
DELIMITER ;

IfNone 

DROP   FUNCTION IfNone;
DELIMITER //
CREATE FUNCTION IfNone
(
  $date       DATETIME,
  $alternate  DATETIME
)
RETURNS DATETIME
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF 0 = $date OR ISNULL($date) THEN

    return $alternate;

ELSE

    return $date;

END IF;

END
//
DELIMITER ;

IfVoid 

DROP   FUNCTION IfVoid;
DELIMITER //
CREATE FUNCTION IfVoid
(
  $text1          TEXT,
  $text2          TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF '' = $text1 THEN

    return $text2;

ELSE

    return $text1;

END IF;

END
//
DELIMITER ;

IfZero 

DROP   FUNCTION IfZero;
DELIMITER //
CREATE FUNCTION IfZero
(
  $id1         INT(11),
  $id2         INT(11)
)
RETURNS INT(11)
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF 0 = $id1 THEN

    return $id2;

ELSE

    return $id1;

END IF;

END
//
DELIMITER ;

IsDate 

DROP   FUNCTION IsDate;
DELIMITER //
CREATE FUNCTION IsDate
(
  $date  DATE
)
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF ISNULL( $date ) OR 0 = $date THEN

    return FALSE;

ELSE

    return TRUE;

END IF;

END
//
DELIMITER ;

IsDateTime 

DROP   FUNCTION IsDateTime;
DELIMITER //
CREATE FUNCTION IsDateTime
(
  $datetime  TIMESTAMP
)
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF ISNULL( $datetime ) OR 0 = $datetime THEN

    return FALSE;

ELSE

    return TRUE;

END IF;

END
//
DELIMITER ;

IsNotEmpty

If $text is null or an empty string when trimmed of whitesapce returns TRUE; otherwise FALSE. 

DROP   FUNCTION IsNotEmpty;
DELIMITER //
CREATE FUNCTION IsNotEmpty
(
  $text           TEXT
)
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    return NOT( ISNULL($text) OR '' = TRIM($text) );

END
//
DELIMITER ;

IsTime 

DROP   FUNCTION IsTime;
DELIMITER //
CREATE FUNCTION IsTime
(
  $time  TIME
)
RETURNS BOOL
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF ISNULL( $time ) THEN

    return FALSE;

ELSE

    return TRUE;

END IF;

END
//
DELIMITER ;

OrNull 

DROP   FUNCTION OrNull;
DELIMITER //
CREATE FUNCTION OrNull
(
  $text         TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

IF '' = TRIM( $text ) THEN

    return NULL;

ELSE

    return $text;

END IF;

END
//
DELIMITER ;

Base functions

Files functions

Base_Files_Exists_By_Kind 

DROP   FUNCTION Base_Files_Exists_By_Kind;
DELIMITER //
CREATE FUNCTION Base_Files_Exists_By_Kind
(
  $Sid                             CHAR(64),
  $USER                             INT(11),
  $kind                            CHAR(30)
)
RETURNS BOOL
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

IF @@read_only THEN

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

ELSE

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

    IF $USER = @USER OR "ADMIN" = @idtype THEN

        return EXISTS( SELECT * FROM base_files WHERE USER=$USER AND kind=$kind );

    ELSE

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

    END IF;

END IF;

END
//
DELIMITER ;

Groups Functions

Base_Groups_Members_Contains 

DROP   FUNCTION Base_Groups_Members_Contains;
DELIMITER //
CREATE FUNCTION Base_Groups_Members_Contains
(
    $GROUP_ID          INT,
    $USER              INT
)
RETURNS BOOL
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

return EXISTS( SELECT * FROM base_groups_members WHERE GROUP_ID=$GROUP_ID AND USER=$USER );

END
//
DELIMITER ;

Base_GroupID_From_USER 

DROP   FUNCTION Base_GroupID_From_USER;
DELIMITER //
CREATE FUNCTION Base_GroupID_From_USER
(
$USER                    INT
)
RETURNS INT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $GROUP_ID  INT  DEFAULT 0;

    SELECT GROUP_ID
    INTO  $GROUP_ID
    FROM   base_groups_members
    WHERE  USER = $USER
    ORDER BY USER
    LIMIT    1;


return $value;

END
//
DELIMITER ;

Places Functions

Base Places Addresses Match 

DROP   FUNCTION Base_Places_Addresses_Match;
DELIMITER //
CREATE FUNCTION Base_Places_Addresses_Match
(
    $PLACE1_ID          INT,
    $PLACE2_ID          INT
)
RETURNS BOOL
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $PLACE_ID       INT   DEFAULT  0;
    DECLARE $street_number  TEXT  DEFAULT '';
    DECLARE $street         TEXT  DEFAULT '';
    DECLARE $suburb         TEXT  DEFAULT '';
    DECLARE $city           TEXT  DEFAULT '';
    DECLARE $state          TEXT  DEFAULT '';
    DECLARE $country        TEXT  DEFAULT '';
    DECLARE $postal_code    TEXT  DEFAULT '';

    SELECT PLACE_ID,  street_number,  street,  suburb,  city,  state,  country,  postal_code
    INTO  $PLACE_ID, $street_number, $street, $suburb, $city, $state, $country, $postal_code
    FROM   base_places
    WHERE  PLACE_ID = $PLACE1_ID;

    return EXISTS
    (
        SELECT *
        FROM   base_places
        WHERE  PLACE_ID      = $PLACE2_ID
        AND    street_number = $street_number
        AND    street        = $street
        AND    suburb        = $suburb
        AND    city          = $city
        AND    state         = $state
        AND    country       = $country
        AND    postal_code   = $postal_code
    );

END
//
DELIMITER ;

Presets Functions

Base Presets Get Value 

DROP   FUNCTION Base_Presets_Get_Value;
DELIMITER //
CREATE FUNCTION Base_Presets_Get_Value
(
  $ORG_ID       INT,
  $preset_code  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $preset_value  TEXT  DEFAULT NULL;

    SELECT
        IF
        (
            preset_allow_override AND NOT Is_Empty( org_preset_override_value ),
            org_preset_override_value,
            preset_value
        )
    INTO  $preset_value
    FROM   view_base_organisations_presets
    WHERE  ORG_ID      = $ORG_ID
    AND    preset_code = $preset_code;

    IF ISNULL( $preset_value ) THEN

        SELECT    preset_value
        INTO     $preset_value
        FROM      base_presets_values
        LEFT JOIN base_presets USING (PRESET_ID)
        WHERE     preset_code = $preset_code
        AND       preset_value_default;

    END IF;

    return $preset_value;

END
//
DELIMITER ;

Templates Functions

Base_Templates_Retrieve_Txt 

DROP   FUNCTION Base_Templates_Retrieve_Txt;
DELIMITER //
CREATE FUNCTION Base_Templates_Retrieve_Txt
(
    $template_name          TEXT
)
RETURNS TEXT
READS SQL DATA
SQL SECURITY INVOKER
BEGIN

    DECLARE $template  TEXT  DEFAULT  '';

    SELECT   CAST( FROM_BASE64( template_txt64 ) AS CHAR )
    INTO    $template
    FROM     base_templates
    WHERE    template_deleted = 0
    AND      template_name    = $template_name
    ORDER BY TEMPLATE_ID DESC
    LIMIT    1;

    return $template;

END
//
DELIMITER ;

Base_Templates_Retrieve_Htm 

DROP   FUNCTION Base_Templates_Retrieve_Htm;
DELIMITER //
CREATE FUNCTION Base_Templates_Retrieve_Htm
(
    $template_name          TEXT
)
RETURNS TEXT
READS SQL DATA
SQL SECURITY INVOKER
BEGIN

    DECLARE $template  TEXT  DEFAULT  '';

    SELECT   CAST( FROM_BASE64( template_htm64 ) AS CHAR )
    INTO    $template
    FROM     base_templates
    WHERE    template_deleted = 0
    AND      template_name    = $template_name
    ORDER BY TEMPLATE_ID DESC
    LIMIT    1;

    return $template;

END
//
DELIMITER ;

Users Functions

Base_Users_Check_Password 

DROP   FUNCTION Base_Users_Check_Password;
DELIMITER //
CREATE FUNCTION Base_Users_Check_Password
(
  $USER                             INT(11),
  $Password                        CHAR(99)
)
RETURNS BOOLEAN
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

DECLARE $valid BOOLEAN DEFAULT FALSE;
DECLARE $email TEXT;

IF @@read_only THEN

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

ELSE

    SELECT email INTO $email FROM base_users WHERE USER=$USER;

    SET $valid = users_verify_credentials( $email, $Password );

END IF;

return $valid;

END
//
DELIMITER ;

Base_Users_Exists 

DROP   FUNCTION Base_Users_Exists;
DELIMITER //
CREATE FUNCTION Base_Users_Exists
(
    $Email CHAR(99)
)
RETURNS BOOLEAN
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    return Exists( SELECT email FROM base_users WHERE user_deleted=0 AND email=$Email );

END
//
DELIMITER ;

Base_Users_Get_Field 

DROP   FUNCTION Base_Users_Get_Field;
DELIMITER //
CREATE FUNCTION Base_Users_Get_Field
(
$USER                    INT(11),
$field                  CHAR(99)
)
RETURNS TEXT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

DECLARE $value TEXT DEFAULT '';

CASE $field
WHEN 'type' THEN SELECT type INTO $value FROM base_users_uids WHERE USER=$USER;
END CASE;

return $value;

END
//
DELIMITER ;

Base_Users_Send_Resets_Exists 

DROP   FUNCTION Base_Users_Send_Resets_Exists;
DELIMITER //
CREATE FUNCTION Base_Users_Send_Resets_Exists
(
  $token      CHAR(64)
)
RETURNS BOOL
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

DECLARE $exists BOOLEAN DEFAULT FALSE;

IF @@read_only THEN

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

ELSE

  SET $exists = EXISTS( SELECT * FROM base_users_send_resets WHERE token=$token );

END IF;

return $exists;

END
//
DELIMITER ;

Base_Users_Send_Resets_Exists 

DROP   FUNCTION Base_Users_Send_Resets_Get_User;
DELIMITER //
CREATE FUNCTION Base_Users_Send_Resets_Get_User
(
  $token      CHAR(64)
)
RETURNS INT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $USER  INT  DEFAULT 0;

    SELECT   USER
    INTO    $USER
    FROM     base_users_send_resets
    WHERE    token=$token
    ORDER BY USER
    LIMIT    1;

    return $USER;

END
//
DELIMITER ;

Base_Users_Verify_Credentials 

DROP   FUNCTION Base_Users_Verify_Credentials;
DELIMITER //
CREATE FUNCTION Base_Users_Verify_Credentials
(
  $Email              CHAR(99),
  $Password           CHAR(99)
)
RETURNS BOOL
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

DECLARE $ret    BOOL;
DECLARE $salt   TEXT;
DECLARE $phash1 TEXT;
DECLARE $phash2 TEXT;

SET $ret = False;

IF @@read_only THEN

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

ELSE

    IF EXISTS( SELECT * FROM base_users WHERE email=$Email ) THEN

        SELECT user_salt     INTO $salt   FROM base_users WHERE email=$Email;
        SELECT password_hash INTO $phash1 FROM base_users WHERE email=$Email;

        SET $phash2 = ComputeHash( $salt, $Password );

        IF $phash1 = $phash2 THEN
            SET $ret = True;
        END IF;

    END IF;

END IF;

return $ret;

END
//
DELIMITER ;

Base_Users_Sessions_Resolve_Sid 

DROP   FUNCTION Base_Users_Sessions_Resolve_Sid;
DELIMITER //
CREATE FUNCTION Base_Users_Sessions_Resolve_Sid
(
    $sid_at_ip  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $sid  TEXT  DEFAULT '';
    DECLARE $Xid  TEXT  DEFAULT '';

    SET $Xid = SUBSTRING_INDEX( $sid_at_ip, '@', 1 );

    SELECT sid INTO $sid
    FROM   base_users_sessions
    WHERE
    (
        sid=$Xid AND ('' = csrf OR IsLocalCaller())
    )
    OR
    (
        (SUBSTRING( sid, 1, 32 ) = SUBSTRING( $Xid, 1, 32 ))
        AND
        (
            (SUBSTRING( csrf, 1, 32 ) = SUBSTRING( $Xid, 33, 32 ))
            OR
            EXISTS
            (
                SELECT *
                FROM   base_web_connections
                WHERE  SUBSTRING( connection_csrf_token, 1, 32 ) = SUBSTRING( $Xid, 33, 32 )
                AND    NOW() < connection_csrf_expiry
            )
        )
    );

    return $sid;

END
//
DELIMITER ;

Base_Users_Sessions_Resolve_Sid 

DROP   FUNCTION Base_Users_Sessions_Resolve_User;
DELIMITER //
CREATE FUNCTION Base_Users_Sessions_Resolve_User
(
    $sid  TEXT
)
RETURNS INT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $AUTH_USER  INT  DEFAULT 0;

    SELECT   AUTH_USER
    INTO    $AUTH_USER
    FROM     base_users_sessions
    WHERE    sid = $Sid
    ORDER BY created
    LIMIT    1;

    return $AUTH_USER;

END
//
DELIMITER ;

Base_Users_Sessions_Verify 

DROP   FUNCTION Base_Users_Sessions_Verify;
DELIMITER //
CREATE FUNCTION Base_Users_Sessions_Verify
(
    $Sid TEXT
)
RETURNS BOOLEAN
READS SQL DATA
SQL SECURITY INVOKER
BEGIN

    DECLARE $expiry INT;
    DECLARE $now    INT;
    DECLARE $ret    BOOL DEFAULT FALSE;

    SET $now    = UNIX_TIMESTAMP();
    SET $ret    = False;

    SELECT expiry INTO $expiry FROM base_users_sessions WHERE sid=$Sid;

    IF $now < $expiry THEN
        SET $ret = True;
    END IF;

    return $ret;

END
//
DELIMITER ;

Base_Users_User_From_EmailProvisional 

DROP   FUNCTION Base_Users_User_From_EmailProvisional;
DELIMITER //
CREATE FUNCTION Base_Users_User_From_EmailProvisional
(
    $email_provisional  TEXT
)
RETURNS INT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $USER  INT  DEFAULT 0;

    SELECT   USER
    INTO    $USER
    FROM     base_users
    WHERE    user_deleted      = 0
    AND      email_provisional = $email_provisional
    ORDER BY USER
    LIMIT    1;

    return $USER;

END
//
DELIMITER ;

Base_Web_Connections_Verify 

DROP   FUNCTION Base_Web_Connections_Verify;
DELIMITER //
CREATE FUNCTION Base_Web_Connections_Verify
(
    $sid_at_ip  TEXT
)
RETURNS TEXT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $sid  TEXT  DEFAULT '';

    SET $sid = GetJth( $sid_at_ip, '@', 1 );

    RETURN EXISTS
    (
        SELECT *
        FROM   base_web_connections
        WHERE  connection_csrf_token = $sid
    );

END
//
DELIMITER ;

Miscellaneous functions

Assert 

DROP   PROCEDURE Assert;
DELIMITER //
CREATE PROCEDURE Assert
(
    $proc   TEXT,
    $type   ENUM( 'INT', 'DATE', 'DATETIME', 'TEXT', 'TIME' ),
    $name   TEXT,
    $value  TEXT
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $throw  BOOL  DEFAULT 0;

    CASE $type
    WHEN 'INT'      THEN IF 0 = $value                      THEN SET $throw = TRUE; END IF;
    WHEN 'DATE'     THEN IF 0 = Date_Decode( $value )       THEN SET $throw = TRUE; END IF;
    WHEN 'DATETIME' THEN IF 0 = $value                      THEN SET $throw = TRUE; END IF;
    WHEN 'TEXT'     THEN IF "" = TRIM( $value )             THEN SET $throw = TRUE; END IF;
    WHEN 'TIME'     THEN IF ISNULL( Time_Decode( $value ) ) THEN SET $throw = TRUE; END IF;
    WHEN 'TRUE'     THEN IF "1" = $value                    THEN SET $throw = TRUE; END IF;
    END CASE;

    IF $throw THEN

        SET @assert = CONCAT( $proc, ": INVALID_PARAMETERS(", $name, ")" ); 

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @assert;

    END IF;

END
//
DELIMITER ;

Assert_Email_Available 

DROP   FUNCTION Assert_Email_Available;
DELIMITER //
CREATE FUNCTION Assert_Email_Available
(
    $email          TEXT
)
RETURNS BOOLEAN
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    IF EXISTS
    (
        SELECT *
        FROM   base_users
        WHERE  user_deleted = 0
        AND     
        (
            TRIM( email )             LIKE TRIM( $email )
            OR
            TRIM( email_provisional ) LIKE TRIM( $email )
        )
    )
    THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sorry, that email is already associated with an account';

    ELSE

        return TRUE;

    END IF;

END
//
DELIMITER ;

Authorise_App_APIKey 

DROP   FUNCTION Authorise_App_APIKey;
DELIMITER //
CREATE FUNCTION Authorise_App_APIKey
(
    $apikey         TEXT,
    $apikey_type    TEXT
)
RETURNS BOOL
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $apikey_part  TEXT  DEFAULT '';
    DECLARE $ip_address   TEXT  DEFAULT '';

    SET $apikey_part = GetJth( $apikey, '@', 1 );
    SET $ip_address  = GetJth( $apikey, '@', 2 );

    return EXISTS
    (
        SELECT *
        FROM   base_apikeys
        WHERE  apikey_type = $apikey_type
        AND    apikey = TRIM( $apikey_part )
        AND   (ip_address = '' OR ip_address LIKE CONCAT( '%[', $ip_address, ']%' ))
    );

END
//
DELIMITER ;

IsReadOnly 

DROP   FUNCTION IsReadOnly;
DELIMITER //
CREATE FUNCTION IsReadOnly
()
RETURNS BOOLEAN
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

DECLARE $read_only BOOLEAN DEFAULT 0;

SELECT @@global.read_only INTO $read_only;

return $read_only;

END
//
DELIMITER ;

LAST_INSERT_GUID 

DROP   FUNCTION LAST_INSERT_GUID;
DELIMITER //
CREATE FUNCTION LAST_INSERT_GUID
(
    $text TEXT
)
RETURNS CHAR(36)
SQL SECURITY INVOKER
NO SQL
BEGIN

    IF IsNotEmpty( $text ) THEN
        SET @LAST_INSERT_GUID = $text;
    END IF;

    return @LAST_INSERT_GUID;

END
//
DELIMITER ;

ReadOnly 

DROP   FUNCTION ReadOnly;
DELIMITER //
CREATE FUNCTION ReadOnly
()
RETURNS BOOL
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

DECLARE $readonly BOOLEAN DEFAULT 0;

IF @@read_only THEN

  SET $readonly = 1;

END IF;

return $readonly;

END
//
DELIMITER ;

DROP   FUNCTION Retrieve_Parameters_For;
DELIMITER //
CREATE FUNCTION Retrieve_Parameters_For
(
  $database                   CHAR(64),
  $name                       CHAR(99)
)
RETURNS TEXT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    return RetrieveParametersFor( $database, $name );

END
//
DELIMITER ;

RetrieveParametersFor

Retrieve_Parameters_For 

DROP   FUNCTION RetrieveParametersFor;
DELIMITER //
CREATE FUNCTION RetrieveParametersFor
(
  $database                   CHAR(64),
  $name                       CHAR(99)
)
RETURNS TEXT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $ret  TEXT  DEFAULT NULL;
    DECLARE $ver  INT   DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR 1142  -- ER_TABLEACCESS_DENIED_ERROR
    BEGIN
        return "DEACTIVATED";
    END;

    IF $database != DATABASE() THEN
        return "DATABASE_MISMATCH";
    END IF;

    SET $ver = GetJth( @@VERSION, '.', 1 );

    IF 5 = $ver OR @@VERSION LIKE '%Maria%' THEN

        SELECT
          param_list

        INTO
          $ret

        FROM mysql.proc
        WHERE db            =  $database
        AND   name          =  $name
        AND   type          = 'PROCEDURE'
        AND   security_type = 'DEFINER'
        AND
        (
            (
                NOT JSON_VALID( COMMENT )
                AND
                (
                    comment LIKE "EXPORT%"
                    OR
                    comment LIKE "OPEN%"
                    OR
                    comment LIKE "PUBLIC%"
                )
            )
            OR
            (
                JSON_VALID( COMMENT )
                AND
                (
                    JSONExtractPath( COMMENT, '$.Security' ) = 'Export'
                    OR
                    JSONExtractPath( COMMENT, '$.Security' ) = 'EXPORT'
                    OR
                    JSONExtractPath( COMMENT, '$.Security' ) = 'Public'
                    OR
                    JSONExtractPath( COMMENT, '$.Security' ) = 'PUBLIC'
                )
            )
        )
        ORDER BY modified DESC LIMIT 1;

        return $ret;

    ELSE
        return RetrieveParametersFor_Slow( $database, $name );
    END IF;

END
//
DELIMITER ;

DROP   FUNCTION RetrieveParametersFor_Slow;
DELIMITER //
CREATE FUNCTION RetrieveParametersFor_Slow
(
    $database  CHAR(64),
    $name      CHAR(99)
)
RETURNS TEXT
SQL SECURITY INVOKER
READS SQL DATA
BEGIN

    DECLARE $ret  TEXT  DEFAULT NULL;

    IF EXISTS
    (
        SELECT *
        FROM   information_schema.ROUTINES
        WHERE  ROUTINE_SCHEMA  = $database
        AND    ROUTINE_NAME    = $name
        AND    ROUTINE_TYPE    = 'PROCEDURE'
        AND    SECURITY_TYPE   = 'DEFINER'
        AND
        (
            ROUTINE_COMMENT = 'EXPORT'
            OR
            ROUTINE_COMMENT = 'PUBLIC'
        )
    )
    THEN

        SET SESSION group_concat_max_len = 102400;

        SELECT
            GROUP_CONCAT( parameter ORDER BY ORDINAL_POSITION ) INTO $ret

        FROM
        (
            SELECT
                SPECIFIC_SCHEMA,
                SPECIFIC_NAME,
                ORDINAL_POSITION,
                CONCAT( ' ', PARAMETER_NAME, ' ', UPPER( DATA_TYPE ) ) AS parameter

            FROM  information_schema.parameters
            WHERE SPECIFIC_SCHEMA  = $database
            AND   SPECIFIC_NAME    = $name
            AND   ORDINAL_POSITION > 0 # Ignore return
            ORDER BY ORDINAL_POSITION

        ) AS S0
        GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME;

    END IF;

    return $ret;

END
//
DELIMITER ;

RoundUp 

DROP   FUNCTION RoundUp;
DELIMITER //
CREATE FUNCTION RoundUp
(
    $value   DOUBLE,
    $places  INT
)
RETURNS DOUBLE
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

    DECLARE $increment  DOUBLE  DEFAULT 0;
    DECLARE $working    DOUBLE  DEFAULT 0;

    SET $increment = 1 / POW( 10, $places );
    SET $working   = TRUNCATE( $value, $places );

    IF $value > $working THEN
        return TRUNCATE( $working + $increment, $places );
    ELSE
        return $working;
    END IF;

END
//
DELIMITER ;

Profile 

DROP   PROCEDURE Enable_Profiling;
DELIMITER //
CREATE PROCEDURE Enable_Profiling
()
SQL SECURITY INVOKER
BEGIN

    UPDATE performance_schema.setup_instruments
    SET
        ENABLED = 'YES',
        TIMED   = 'YES';

    UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES';

END
//
DELIMITER ;

DROP   PROCEDURE Profile_Call;
DELIMITER //
CREATE PROCEDURE Profile_Call
(
    $call  TEXT
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $THREAD_ID   BIGINT(20) unsigned  DEFAULT 0;
    DECLARE $EVENT_ID    BIGINT(20) unsigned  DEFAULT 0;
    DECLARE $TIMER_START BIGINT(20) unsigned  DEFAULT 0;
    DECLARE $TIMER_END   BIGINT(20) unsigned  DEFAULT 0;

    SELECT   THREAD_ID,  EVENT_ID,  TIMER_START,  TIMER_END
    INTO    $THREAD_ID, $EVENT_ID, $TIMER_START, $TIMER_END
    FROM     performance_schema.events_statements_history_long
    WHERE    CURRENT_SCHEMA = DATABASE()
    AND      event_name     = "statement/sql/call_procedure"
    AND      SQL_TEXT LIKE CONCAT( $call, '%' )
    AND      ISNULL(NESTING_EVENT_ID)
    ORDER BY EVENT_ID DESC
    LIMIT    1;

    SELECT $THREAD_ID, $EVENT_ID, $TIMER_START, $TIMER_END;

    SELECT
        THREAD_ID,
        EVENT_ID,
        END_EVENT_ID,
        EVENT_NAME,
        TIMER_START,
        TIMER_END,
        CONCAT( TIMER_WAIT/1000000000, "ms" ) AS ms,
        OBJECT_NAME,
        NESTING_EVENT_ID,
        NESTING_EVENT_TYPE,
        NESTING_EVENT_LEVEL,
        REPLACE( SUBSTRING( SQL_TEXT, 1, 50 ), '\n', ' ' ) AS Text

    FROM     performance_schema.events_statements_history_long
    WHERE    CURRENT_SCHEMA =  DATABASE()
    AND      event_name     = "statement/sql/call_procedure"
    AND      THREAD_ID      = $THREAD_ID
    AND     $TIMER_START   <=  TIMER_START
    AND      TIMER_END     <= $TIMER_END
    ORDER BY EVENT_ID;

END
//
DELIMITER ;

DROP   PROCEDURE Profile_Parent;
DELIMITER //
CREATE PROCEDURE Profile_Parent
(
    $EVENT_ID  BIGINT
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $THREAD_ID   BIGINT(20) unsigned  DEFAULT 0;
    DECLARE $EVENT_ID    BIGINT(20) unsigned  DEFAULT 0;
    DECLARE $TIMER_START BIGINT(20) unsigned  DEFAULT 0;
    DECLARE $TIMER_END   BIGINT(20) unsigned  DEFAULT 0;

    SELECT   THREAD_ID,  EVENT_ID,  TIMER_START,  TIMER_END, NESTING_EVENT_ID
    FROM     performance_schema.events_statements_history_long
    WHERE    EVENT_ID = $EVENT_ID;

END
//
DELIMITER ;

DROP   PROCEDURE ShowViewDependants;
DELIMITER //
CREATE PROCEDURE ShowViewDependants
(
    $view  TEXT
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $pattern  TEXT  DEFAULT '';

    SET $pattern = CONCAT( '%`', $view, '`%' );

    SELECT TABLE_NAME
    FROM   information_schema.VIEWS
    WHERE  TABLE_SCHEMA    =    DATABASE()
    AND    VIEW_DEFINITION LIKE $pattern
    ;

END
//
DELIMITER ;

DROP   PROCEDURE FindFunction;
DELIMITER //
CREATE PROCEDURE FindFunction
(
    $pattern  TEXT
)
SQL SECURITY INVOKER
BEGIN

    SELECT
        db,
        name,
        Base64Decode( body )
        
    FROM   mysql.proc
    WHERE  type = 'FUNCTION'
    AND    db   = DATABASE()
    AND    name LIKE $pattern
    ;

END
//
DELIMITER ;

DROP   PROCEDURE FindFunctions;
DELIMITER //
CREATE PROCEDURE FindFunctions
(
    $pattern  TEXT
)
SQL SECURITY INVOKER
BEGIN

    SELECT
        db,
        comment,
        security_type,
        sql_data_access,
        is_deterministic,
        name,
        returns
        
    FROM   mysql.proc
    WHERE  type = 'FUNCTION'
    AND    db   = DATABASE()
    AND    name LIKE $pattern
    ;

END
//
DELIMITER ;

DROP   PROCEDURE FindProcedures;
DELIMITER //
CREATE PROCEDURE FindProcedures
(
    $pattern TEXT
)
SQL SECURITY INVOKER
BEGIN

    SELECT
        db,
        comment,
        security_type,
        name

    FROM   mysql.proc
    WHERE  db   =  DATABASE()
    AND    type = 'PROCEDURE'
    AND    name LIKE $pattern
    ORDER BY comment, security_type, name;

END
//
DELIMITER ;