I have begun work on the next set of features for MDB2. I just implemented a feature inspired by the popular ADODB abstraction layer. John calls it smart transactions, while I am currently planning on calling it nested transactions. However I will wait until Lorenzo has some time to look at things to see if we can fit native nested transactions in there or not. Interbase/Firedbird really shines in this department.

I have written up a little script to show the current implementation available in CVS:

<?php

$mdb2 =& MDB2::singleton($dsn);

$mdb2->setOption('debug', true);

$query = "DELETE FROM autoinc";
$result = $mdb2->exec($query);

$query = "SELECT id FROM autoinc";
$result = $mdb2->queryCol($query);
var_dump($result);

$query = "INSERT INTO autoinc (id) VALUES (?)";
$stmt = $mdb2->prepare($query);

$mdb2->beginNestedTransaction();
$stmt->execute(array(1));
$mdb2->beginNestedTransaction();    # ignored
$query = "SELECT id FROM autoinc";
$result = $mdb2->queryCol($query);
var_dump($result);
if (!empty($result)) {
    $error = $mdb2->raiseError(MDB2_ERROR, null, null, 'kaboom');
    $mdb2->failNestedTransaction();
    var_dump('fail');
}

if(($error = $mdb2->getNestedTransactionError())) {
    var_dump($error->getUserinfo());
}
$mdb2->completeNestedTransaction(); # ignored
$stmt->execute(array(2));
$mdb2->completeNestedTransaction();

$query = "SELECT id FROM autoinc";
$result = $mdb2->queryCol($query);
var_dump($result);

var_dump($mdb2->getDebugOutput());

?>

As you can see the idea is to maintain a counter of opened nested transactions and only really rollback or commit once that counter is decremented to 1 with a completeNestedTransaction() call. If along the way you decide that the transaction should be rolled back you can do so by calling failNestedTransaction() and optionally passing some value you expect to get returns when calling getNestedTransactionError(). This way you can write modular code that opens transactions in inner layers without running into problems on RDBMS that do not support true nested transactions.

While I was working on the transaction stuff I also decided to add a method setTransactionIsolation() to set the transaction isolation level. This method allows to set to isolation level to one of the 4 SQL92 levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE). If a lower level is not implemented MDB2 will automatically use a higher level that is implemented. If you are unware of the implementations of this I recommend picking up your favorite SQL book or web ressource to read up on this if you plan to be using transactions with concurrency. While you are at it you should probably also read up on MVCC, which is implemented in Firebird, InnoDB, Oracle and PostgreSQL. Note that PostgreSQL works a bit different since they do not move data to a rollback segment and instead just append the new data and mark the old data as stale. This is the reason why they need VACCUM.

Aside from that I have added a ton of todo items to the package.php file of MDB2. Topics range from improving the debugging capabilities of MDB2, to expanding the function abstraction and adding FOREIGN KEY support. Another one is adding a new package that implements the ActiveRecord pattern on top of MDB2. A big one is also adding a PDO based driver for SQLite in order to support SQLite 3. Patches welcome :)

Smarty transations (or whatever you will call it) - that's one sweet feature. We currently deploy all on MDB2 - or switch to it. It's really a charm!

Thanks for that.

Although 'smart' probably refers to the ability to ignore the feature if not supported, I suggest to keep 'Nested' as it is clearly explains what a user can expext (if the database supports it) - calling it 'smart' seems to be not smart enough ;-)

Ahm.. I actually think about a MDB2_Util package or similar name for some high level stuff like ActiveRecord and other things like a schema registry - but I have to finish my semester's examn first in the next 3 Weeks...

OK

Perhaps this has been posted before but can I ask if there are any decent tutorials on using MDB2?

The Pear documentation is woefully lacking and going through the api docs is a nightmare for those who are perhaps more casual in there programming nature.

Lukas can you point me to any comprehensive tutorials pls?

BTW I love using the MDB2 but really want to learn more than just the basics (in particular looking for info on LIMIT queries and iterating through result sets).

Thanks

I try to collect any good tutorials I stumble across on my wiki:
http://oss.backendmedia.com/MDB2/DocumenTation

I think there was also an article in one of the recent editions of the international php magazine.

Finally the test suite is getting more and more comprehensive. It atleast shows how to use a fair number of the methods in practice.

Unfortunately the documentation efforts have been a three man show uptil now: me, myself and I. Being very available to answer questions via email, irc and my wiki. So if the information is not in one of those tutorials just contact me and I will explain it. However if only every 5th person I explain something would contribute this knowledge as documentation .. *sigh*