[TYPO3-core] RFC #15457: Add support for prepared queries [summary and pending questions]

[Xavier Perseguers]

Hi,

I summarize this message because a +1 by testing is still missing (from 
anybody). In addition/in regard to that point, there are a few decisions 
that should be agreed on.


Currently, we have:

+1 by reading from Helmut
+1 by reading, reviewing and testing from Ernesto


Before having the final +1 by testing, please answer those 
questions/remarks, let me possibly make a new version and then I'd 
kindly ask another +1 by testing by anybody and a new +1 by testing from 
Ernesto (or either Masi or Steffen if Ernesto is unable to do it in the 
very near future).

> Problem:
> Having support for prepared queries would allow to implement an
> efficient caching mechanism when using DBAL which would result in a huge
> speed improvement.
>
> It will provide a PDO-like syntax which could, in a second phase, allow
> TYPO3 to issue real prepared statements to the underlying DBMS. In
> addition, this RFC provides a DB abstraction that is object oriented,
> meaning, it is not necessary anymore to deal with MySQL pointers
>
> How prepared queries could then be used:
> Idea is to rewrite Core queries using prepared queries once this support
> has been made available.

sample #1 (named parameter)
====================================
$statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'pages', 
'pid = :pid');
$statement->execute(array(':uid' => 2));
while (($row = $statement->fetch()) !== FALSE) {
	// ...
}
$statement->free();


sample #2 (question mark parameter)
====================================
$statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('uid, title', 
'pages', 'pid = ?');

$pid = 1;
$statement->bindParam(1, $pid);
$statement->execute();
$pagesWithPid1 = $statement->fetchAll();
$statement->free();

// New execution of the prepared query and explicitly setting the type 
of parameter
$pid = 4;
$statement->bindParam(1, $pid, t3lib_db_PreparedStatement::PARAM_INT);
$statement->execute();
$pagesWithPid4 = $statement->fetchAll();
$statement->free();


Questions / Remarks from Jigal:

 > PARAM_AUTOTYPE is not present in PDO, so this will require extra
 > code when using PDO itself

After a few tests, Using type PARAM_STR instead seems fine and works (by 
testing) with numerical columns such as pid or uid. And it behaves like PDO.

Same problem happens with PARAM_FLOAT which does not exist in PDO and 
could be handled as PARAM_STR. PARAM_INT should stay as it is (now with 
latest patch), that is, an integer!


 > Do not default PARAM_STR

I do not agree. Either we keep PARAM_AUTOTYPE and as such do not default 
to PARAM_STR or behave like PDO but forcing the user to provide a 
datatype means:

1) bindValues() and execute($params) will be completely unusable as user 
should then provide an array within an array for parameters
2) Would not behave like PDO and then is not in line with your wish to 
have an API as close as possible to PDO ;-)


 > Remove method bindValues()

I do not see the downside of having it if we default to PARAM_STR 
instead of the would-not-exist-anymore PARAM_AUTOTYPE. This way, it 
would behave as when providing parameters in method execute(). But I 
find it a bit cleaner than forcing user to pass multiple parameters to 
execute() (which s/he could still do).


 > Named markers are only replaced once in PDO.

The remarks is to allow or not such query:

$statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('uid, title', 
'pages', 'uid = :id OR pid = :id');
$statement->bindValue(':id', 43);

where :id is replaced twice. In PDO this is not the case. Still, I find 
it handy and as such would like to keep it.


 > fetch* functions in PDO default to FETCH_BOTH; is there a reason to 
do something else?

Have you real use-cases where you rely on having both indexed- and 
named-based keys in your resultset? How is your code using 
$GLOBALS['TYPO3_DB'] currently written to work like that?


 > In PDO this is not possible to support both named and positionned 
markers as it results in exception SQLSTATE[HY093] being thrown.

=> We have no way in native MySQL to detect that both types are used in 
query. What we could do is see which type is used with first call to 
bindValue() and stick to that type for all subsequent calls...


Remarks from me:

Regarding your ancient "PDO" class. Did not test but we could use 
something like you did:

if (!is_numeric($key)) {
	// replace the marker (not preceeded by a word character or a ':' but 
followed
	// by a word boundary)
	$this->sql = preg_replace('/(?<![\w:])'.$key.'\b/', PDO::quote($val, 
$data_type), $this->sql);
}

instead of str_replace() when using native MySQL (when using DBAL we 
have a proper way of replacing both question marks and named markers).


Regarding constant values. It does not cost anything to use the real 
internal value of PDO instead of sequential value. As such I'd like to 
have it as in PDO (PARAM_INT, PARAM_BOOL, PARAM_STR).


Possible support for INSERT, UPDATE, ... may be added in future but will 
definitively not with this RFC.


Thanks for reading so far, thanks in advance for your remarks ;-)

Xavier