Difference between #3 and #2 of How to use nested DB transactions (MySQL 5+, PostgreSQL)

db, mysql, postgresqlpostgresql, transaction, nested

source code taken from: [PHP, PDO & Nested
Transactions](http://www.kennynet.co.uk/2008/12/02/php-pdo-nested-transactions/)

tested with: MySql 5.1.30

Problem:
Say there is service layer in an application, and one service may use others. No
each service deals will complex business logic will needs to wrap that into
transactions.

If there are services A and B here's how it might happen:

~~~
[php]
class ServiceA {
    public function transA() {
        $trans = Yii::app()->db->beginTransaction();
        // code
        Yii::app()->serviceB->transB();
        // code
        // exception handling and stuff
        $trans->commit();
    }
}

class ServiceB {
    public function transB() {
        $trans = Yii::app()->db->beginTransaction();
        // code
        // exception handling and stuff
        $trans->commit();
    }
}
~~~

With default Yii implementation, PDO will throw exception saying there already
is active transaction.

But there's a solution:)

First, you'll need to extend PDO class:
class and save it in your the protected/components directory:
~~~
[php]
class InsPDONestedPDO extends PDO {
    // Database drivers that support SAVEPOINTs.
    protected static $savepointTransactions = array("pgsql",
"mysql");

    // The current transaction level.
    protected $transLevel = 0;

    protected function nestable() {
        return in_array($this->getAttribute(PDO::ATTR_DRIVER_NAME),
                        self::$savepointTransactions);
    }

    public function beginTransaction() {
        if(!$this->nestable() || $this->transLevel == 0) {
            parent::beginTransaction();
        } else {
            $this->exec("SAVEPOINT LEVEL{$this->transLevel}");
        }

        $this->transLevel++;
    }

    public function commit() {
        $this->transLevel--;

        if(!$this->nestable() || $this->transLevel == 0) {
            parent::commit();
        } else {
            $this->exec("RELEASE SAVEPOINT
LEVEL{$this->transLevel}");
        }
    }

    public function rollBack() {
        $this->transLevel--;

        if(!$this->nestable() || $this->transLevel == 0) {
            parent::rollBack();
        } else {
            $this->exec("ROLLBACK TO SAVEPOINT
LEVEL{$this->transLevel}");
        }
    }
}
~~~

Then, you'll need to alter the behaviour of
[CDbConnection::createPdoInstance()]. There, change $pdoClass to your class
name (InsPDO inYou can do this example).by
making a subclass of it in protected/components/NestedDbConnection.php

There, change $pdoClass to your class name (NestedPDO in this
example):
~~~
[php]
protected function createPdoInstance()class NestedDbConnection
extends CDbConnection
{
    $pdoClass='InsPDO';
   
if(($pos=strpos($this->connectionString,':'))!==false)protected
function createPdoInstance()
    {
        $driver=strtolower(substr($this->connectionString,0,$pos));
        if($driver==='mssql'$pdoClass='NestedPdo';
        if(($pos=strpos($this->connectionString,':'))!==false)
        {
            $driver=strtolower(substr($this->connectionString,0,$pos));
            if($driver==='mssql' || $driver==='dblib')
            $pdoClass='CMssqlPdoAdapter';
              $pdoClass='CMssqlPdoAdapter';
        }
        return new $pdoClass($this->connectionString,$this->username,
                             $this->password,$this->attributes);
    }
    return new $pdoClass($this->connectionString,$this->username,
                                $this->password,$this->_attributes);
}
~~~

It'd probably be better OOPNote that $this->_attributes was
also changed to extend [CDbConnection], especially since
createPdoInstance is protected. But then either there are inconsistencies with
private/protected properties of [CDbConnection], or I didn't spent enough
time$this->attributes so the subclassing will work.

Now you can add the class name to explore how to properly extend
[CDbConnection].the db configuration array in
protected/config/main.php

~~~
[php]
'db'=>array(
            'class'=>'NestedDbConnection',
            'connectionString' => ...
        ),
~~~

That's it, there you go;)