While working on the PDO integration, I came up with the following questions (I guess more will follow soon :P)

PHPUnit:
As I write PDODatabase.php totally anew, I guess it would be a good start to use PHPUnit there (with full code-coverage), possibly adding it elsewhere later if it's working well.
For the implementation, there will be an additional class / file PDODatabaseTest which will contain all tests, but PDODatabase will be the same with and without the unit testing (so no design-by-contract approach).
Is that ok or does anyone want it to be done differently (for example using design-by-contract)?

CHECK / REPAIR table:
Although I did some searching, I could not find the equivalent of MySQL's REPAIR and CHECK in PostgreSQL and MS SQL. So I guess for these databases checkAndRepairTable / runTableCheckCommand will just trigger an error and do nothing or are there better suggestions?

Hi Philipp,

I think it's good if PDODatabaseTest is completely separated from the PDODatabase class since the test code doesn't need to be shipped with the standard distribution.

Regarding the CHECK/REPAIR TABLE commands I think they are rather proprietary and it's the same question as with some other SQL statements. How do you abstract e.g. the IFNULL, CONCAT, LIMIT, or TOP statements or the date formats?

That's the real advantage of using a library like ADOdb (which can also be used with the PDO driver). PDO doesn't offer any of those abstractions, it's just a consistent interface for accessing databases.

Another question is if you will add support for prepared statements?
This would result in huge security benefits and (luck for you :-)) it is the only feature that PDO emulates for drivers that don't support them (PDO).

Thanks for the quick answer :-)

That's exactly what I thought with the testing, so if that's ok with everyone I'll do it like that.

Right, PDO is just an interface (we didn't want to introduce another layer of abstraction, dependency of other software,...).
With different SQL statements it really depends on a case by case basis. Most things can be done or worked around (for example LIMIT is used by MySQL and PostgreSQL, while MS SQL uses TOP [but it doesn't understand OFFSET], MySQL and PostgreSQL use SELECT VERSION() and MS SQL does it with SELECT @@VERSION,...). So generally I try to make it work, but with CHECK / REPAIR I just couldn't find anything appropriate. If that doesn't exist (wanted to ask if anyone knew anything first ;-) ), I will simply have to leave it empty for MS SQL and PostgreSQL, if there is nothing else...

Prepared statements will be used / I'm currently writing some. And I'm trying to get them right to avoid SQL injections...
$dbConn->prepare("CREATE DATABASE " . $database) *ggg*

Great! If you support prepared statements they will automatically avoid SQL injections..

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// or

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

no need to escape $name or $value..

Maybe the easiest thing would be to add a parameter to Database::query() so that you can pass an array with values (indexed or associative array).
To keep the whole stuff backwards compatible you could assign a default value of null to that new parameter..

<quote>If you support prepared statements they will automatically avoid SQL injections..</quote>
If done correctly - I only say $dbConn->prepare("CREATE DATABASE " . $database) :P

I'm currently using $stmt->bindParam(':name', $name); (don't really like the numbering).

FYI, Sam has worked on some test runners using PHPUnit - I'm not sure if he's informed you separately.

Take a look at http://doc.silverstripe.com/doku.php?id=automated-testing for more info - but your basic idea is exactly right - except you'd extend SapphireTest instead of the PHPUnit class (SapphireTest will give you a number of handy utility functions to deal specifically with doing testing in SilverStripe).

HTH.