DROP   PROCEDURE Base_Files_Retrieve_By_Token;
DELIMITER //
CREATE PROCEDURE Base_Files_Retrieve_By_Token
(
    $Sid                             TEXT,
    $token                           TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN

    IF "" = TRIM( $token ) THEN

        CALL Warning( 'Base_Files_Retrieve_By_Token: INVALID_TOKEN' );

    ELSE

        SELECT   *
        FROM     base_files
        WHERE    token = $token
        ORDER BY version DESC
        LIMIT    1;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Files_Replace;
DELIMITER //
CREATE PROCEDURE Base_Files_Replace
(
  $Sid                             CHAR(64),
  $FILE                             INT(11),
  $USER                             INT(11),
  $kind                            CHAR(30),
  $original_filename               CHAR(255),
  $filename                        CHAR(255),
  $filetype                        CHAR(99),
  $filesize                        CHAR(45),
  $fileextension                   CHAR(10),
  $base64                      LONGBLOB
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $FILE  INT   DEFAULT  0;
    DECLARE $salt  TEXT  DEFAULT '';
    DECLARE $token TEXT  DEFAULT '';

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

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSEIF NOT( $USER = @USER OR "ADMIN" = @idtype OR IsLocalCaller() ) THEN

        CALL Notice( 'Base_Files_Replace: INVALID_AUTHORISATION' );

    ELSE

        SET $token = GenerateSalt();

        #
        #   Ensure unique token
        #

        WHILE EXISTS( SELECT * FROM base_files WHERE token=$token ) DO
            SET $token = GenerateSalt();
        END WHILE;

        SET $base64 = REPLACE( $base64, ' ', '+' );

        REPLACE INTO base_files
        (  FILE,  USER,  version,  kind,  original_filename,  filename,  filetype,  filesize,  fileextension,  salt,  token,  base64 )
        VALUES
        ( $FILE, $USER,    NOW(), $kind, $original_filename, $filename, $filetype, $filesize, $fileextension,     0, $token, $base64 );

        IF NOT $FILE THEN
            SELECT LAST_INSERT_ID() INTO $FILE;
        END IF;

        SELECT $FILE AS FILE;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Files_Retrieve_Info_By_Kind;
DELIMITER //
CREATE PROCEDURE Base_Files_Retrieve_Info_By_Kind
(
  $Sid                             CHAR(64),
  $USER                             INT(11),
  $kind                            CHAR(30)
)
SQL SECURITY INVOKER
BEGIN

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

    IF NOT( $USER = @USER OR "ADMIN" = @idtype ) THEN

        CALL Notice( 'Base_Files_Retrieve_Info_By_Kind; INVALID_AUTHORISATION' );

    ELSE

        SELECT   *
        FROM     view_files
        WHERE    USER = $USER
        AND      kind = $kind
        ORDER BY version DESC;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Files_Retrieve;
DELIMITER //
CREATE PROCEDURE Base_Files_Retrieve
(
  $Sid                             CHAR(64),
  $FILE                             INT(11)
)
SQL SECURITY INVOKER
BEGIN

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

    IF NOT( $USER = @USER OR "ADMIN" = @idtype ) THEN

        CALL Notice( 'Base_Files_Retrieve; INVALID_AUTHORISATION' );

    ELSE

        SELECT   *
        FROM     base_files
        WHERE    USER = @USER
        AND      FILE = $FILE
        ORDER BY version DESC
        LIMIT    1;

    END IF;

END
//
DELIMITER ;