how to define a SQL functions with PHP?

Question

With mySQL/MariaDB you can define your own function for the SQL server (mySQL-manual).
If you create such a function it is available in later queries (until it is dropped / server reset? / only for that user? / ...)

As I want to use such a function in my PHP code I want to make sure the function is available. So I want to define the function from PHP. In this way the function is on every server my PHP code is executed even if I have no access to the DB-server with a CLI.

While I can define the function in a SQL-console (mysql), all attempts from PHP fail.

As I build an extension for TYPO3 I first tried the queryBuilder, which failed with syntax error. (probably the doctrine layer was not prepared for the very special syntax used for creating functions).

Then I used mysqli and all statements returned no error. but the functions were not created. :(

$levenshteinFunction = <<<'LEVENSHTEIN_FUNCTION'
CREATE FUNCTION LEVENSHTEIN(s1 VARCHAR(255) CHARACTER SET utf8, s2 VARCHAR(255) CHARACTER SET utf8)
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
    DECLARE s1_char CHAR CHARACTER SET utf8;
    -- max strlen=255 for this function
    DECLARE cv0, cv1 VARBINARY(256);

    SET s1_len = CHAR_LENGTH(s1),
        s2_len = CHAR_LENGTH(s2),
        cv1 = 0x00,
        j = 1,
        i = 1,
        c = 0;

    IF (s1 = s2) THEN
      RETURN (0);
    ELSEIF (s1_len = 0) THEN
      RETURN (s2_len);
    ELSEIF (s2_len = 0) THEN
      RETURN (s1_len);
    END IF;

    WHILE (j <= s2_len) DO
        SET cv1 = CONCAT(cv1, CHAR(j)),
          j = j + 1;
    END WHILE;

    WHILE (i <= s1_len) DO
        SET s1_char = SUBSTRING(s1, i, 1),
          c = i,
          cv0 = CHAR(i),
          j = 1;

      WHILE (j <= s2_len) DO
          SET c = c + 1,
            cost = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);

        SET c_temp = ORD(SUBSTRING(cv1, j, 1)) + cost;
        IF (c > c_temp) THEN
          SET c = c_temp;
        END IF;

        SET c_temp = ORD(SUBSTRING(cv1, j+1, 1)) + 1;
        IF (c > c_temp) THEN
          SET c = c_temp;
        END IF;

        SET cv0 = CONCAT(cv0, CHAR(c)),
            j = j + 1;
      END WHILE;

      SET cv1 = cv0,
          i = i + 1;
    END WHILE;

    RETURN (c);
  END $$
LEVENSHTEIN_FUNCTION;
                
$levenshteinratioFunction = <<<'LEVENSHTEINRATIO_FUNCTION'
CREATE FUNCTION LEVENSHTEINRATIO( s1 VARCHAR(255), s2 VARCHAR(255) )
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, max_len INT;
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
    IF s1_len > s2_len THEN
      SET max_len = s1_len;
    ELSE
      SET max_len = s2_len;
    END IF;
    RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
  END $$
LEVENSHTEINRATIO_FUNCTION;

$LS = [
    'show function status;',
    'drop function IF EXISTS levenshtein ;',
    'drop function IF EXISTS levenshteinratio ;',
    'DELIMITER $$',
    $levenshteinFunction,
    'DELIMITER ;',
    'DELIMITER $$',
    $levenshteinratioFunction,
    'DELIMITER ;',
    'show function status;'
];

/*
// first attempt
$output ='';
foreach ($LS as  $lsStatement) {
    $output .= '*** '.$lsStatement.' ***'.PHP_EOL;
    $levenshteinQuery = $this->createQuery();
    $levenshteinQuery->statement($lsStatement,[]);
    $result = $levenshteinQuery->execute();
    $output .= print_r($result->toArray(), false). PHP_EOL.PHP_EOL;
    //\TYPO3\CMS\Core\Utility\DebugUtility::debug($result,'result of '.substr($lsStatement,0,30));
    unset($levenshteinQuery);
}
echo $output;
die();
*/

$mysqliConnection = new \mysqli(
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['host'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['user'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['password'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['dbname']
);

$output = '';
foreach ($LS as  $lsStatement) {
    $output .= '*** '.$lsStatement.' ***'.PHP_EOL;
    $result = $mysqliConnection->query($lsStatement);
    $output .= print_r($result, true). PHP_EOL.PHP_EOL;
}
$mysqliConnection->close();
echo '<pre>'.PHP_EOL.$output.'</pre>';
die();

This is a debug-output with termination (die) as the later query failed anyway with the error undefined function levenshtein.

as said: the later query works if I define the functions in a SQL CLI and without recreation of the function.

Answer 1

The DELIMITER command is not recognized by the server-side SQL parser, it is one of the builtin commands for the MySQL client. It is pre-parsed by the client, not sent to the server. So you can't use DELIMITER when you execute SQL via API.

The purpose of DELIMITER is to clarify where the separation between SQL statements, when a statement such as CREATE FUNCTION contains semicolon characters. The delimiter is how the client detects the end of the statement, so it doesn't end the statement at the first ; it sees within the body of the function you're trying to define.

You don't need to resolve this ambiguity if you use the API. Just submit the whole CREATE FUNCTION statement, up to the last END, and it executes.

Your code shows running a series of statements like DROP FUNCTION and so on as a multi-query. You can't do this when defining stored routines.

Why not? The MySQL API does support multi-query.

Because MySQL's multi-query doesn't support DELIMITER. SQL statements in multi-query mode are separated by ; only, and this is not configurable.

https://dev.mysql.com/doc/c-api/8.0/en/c-api-multiple-queries.html

MySQL also supports the execution of a string containing multiple statements separated by semicolon (;) characters.

So effectively, you can't use multi-query to define stored routines that have compound statements, because these need semicolons within the body of the routine, not as the terminator of the whole CREATE statement.

You don't need multi-query anyway, because you should just execute each statement individually, in separate API calls. It's a few more lines of code, but it's a moot point because this is the only way to execute these statements.

You also asked:

If you create such a function it is available in later queries (until it is dropped / server reset? / only for that user? / ...)

Stored functions are persistent, like tables. They will stay in your MySQL instance until you DROP FUNCTION, or if you drop the whole schema in which the function is defined. Restarting the MySQL Server does not drop the function.

Functions are available for any user who has privileges to access the schema in which the function is defined.

A comment above says you shouldn't create functions in your application code. I think this is overstating it. You may create functions in application code, for example if you want an application that always ensures the function exists. But you would probably use CREATE FUNCTION IF NOT EXISTS ... so it only creates the function once.

Typically, most applications don't include this. They are designed to assume you have created tables and functions prior to the application running. This is usually a good idea, because it reduces the amount of code in the application.

Answer 2

You could use the connection class to build your own queries. This is sometimes helpful if you want to use MySQL functions that are not supported by the QueryBuilder. See an example: https://github.com/in2code-de/lux/blob/develop/Classes/Domain/Repository/SearchRepository.php#L53