Nested transactions or other refactoring method

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: Nested transactions or other refactoring method

Post by koen.h »

If it starts getting complicated we're probably on the wrong track. At least that how I approach things. I've used the composite pattern here. This article suggests it may not be a good approach for saving into a db:

http://devzone.zend.com/node/view/id/8
Another issue to bear in mind when deploying the Composite pattern is that of storage and retrieval. Tree structures do not lend themselves well to relational databases.
Seperating save into a save and insert method might work. Every component of country call country->save() when asked to save, and country->save() starts the transaction (and closes/rolls back), loops through the tree and asks everyone to insert. It feels strange though.

edit: you also might need to mark objects in the tree as dirty to prevent duplication.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Nested transactions or other refactoring method

Post by matthijs »

The composite might be worth looking into. Thanks for the link, I'll take a look maybe I can use some concepts.
Tree structures do not lend themselves well to relational databases.
I'm not sure if we are dealing with a real tree here. But I do understand now how it's all very awkward to fit my objects into the tables of the normalized db. Sometimes I long back to my nice little spreadsheets which would be so easy to use :)

And can you imagine that the save() is only one method? What about update? Then I have to repeat the whole mess of logic again.

And with update it gets even more complicated. Because at first, you have user A who saves climb A (route X in Area Y and Country Z). So the climb is saved to the climb table, the route X is saved to the routes table, the Area Y to the areas table and country Z to the countries table.

But now user B comes by, adds a climb, and coincidentally for the exact same route! No problem, I have build in checks for that in the save methods. So after saving the climb for this user, we end up with 2 rows in the climbs table (one for the climb of user A, one for the climb of user B), and 1 row in the routes table, 1 in areas and 1 in countries. All happy.

But now user A comes back and wants to update his climb. He made a mistake and wants to change the name of the route. Now I have to make sure that the original route does stay in the table, and a new one is added, because the climb of the second user must stay intact.

And these are just a few issues, there's probably some more situations I haven't thought about yet. Arghh... never would have thought this would be so complicated...
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: Nested transactions or other refactoring method

Post by koen.h »

Another solution is to use a helper class.

Code: Select all

 
class City {
 public function save()
 {
   $conn->beginTransaction();
   try {
     GeoHelper::saveCity();
     GeoHelper::saveCountry();
   }
   catch (Exception $e) {
            $conn->rollBack();
   }
 }
}
 
That would solve sql duplication. This class could contain methods to check if a city or country doesn't already exist. You can pass the necessary variables to it (saveCity($city, $conn)) to your liking.

There's probably some database pattern for your problem. I don't know that many of them.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Nested transactions or other refactoring method

Post by matthijs »

Yes, that's a solution I was also thinking about last night. Try and extract the most common (duplicated) queries and have a single "manager" of some kind (your helper) figure it out with the transactions.
wei
Forum Contributor
Posts: 140
Joined: Wed Jul 12, 2006 12:18 am

Re: Nested transactions or other refactoring method

Post by wei »

i think the simplest way to create a business layer that coordinates the transactions. That is, extract out the transaction logic. e.g.

Code: Select all

 
class LocalityUpdateWorkflow
{
     private $_conn;
     
     public function __construct($conn)
    {
            $this->_conn = $conn;
    }
 
    public function execute($country, $region, $city)
    {
            $tx = $this->_conn->beginTransaction();
            try
            {
                 $country->save();
                 if(!$region->save())
                      $tx->rollback();
                 $city->save();
                 ...//etc
              }catch(DbConnectionException $e)
              {
                   $tx->rollback();
                    throw new DbTransactionException($e);
             }
    }
}
 
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Nested transactions or other refactoring method

Post by matthijs »

This is an old thread of mine but I found the answer to my problem so I thought I share it with you.

Browsing through the code of EzComponents I discovered that they extend PDO and do exactly what I want:

Code: Select all

 
class MyPdo extends PDO {
    
    protected $transactionNestingLevel = 0;
    protected $transactionErrorFlag = false;
    
    public function __construct( $dbParams, $dsn ) {
    $user          = null;
        $pass          = null;
        $driverOptions = null;
        foreach ( $dbParams as $key => $val ) {
            switch ( $key ) {
                case 'user':
                case 'username':
                    $user = $val;
                    break;
                case 'pass':
                case 'password':
                    $pass = $val;
                    break;
                case 'driver-opts':
                    $driverOptions = $val;
                    break;
            }
        }
    parent::__construct( $dsn, $user, $pass, $driverOptions );
        $this->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $this->setAttribute( PDO::ATTR_CASE, PDO::CASE_LOWER );
    }
    
    public function beginTransaction() {
        $retval = true;
        if ( $this->transactionNestingLevel == 0 ) {
            $retval = parent::beginTransaction();
        }
        // else NOP
        $this->transactionNestingLevel++;
        return $retval;
    }
 
    public function commit() {
        if ( $this->transactionNestingLevel <= 0 ) {
            $this->transactionNestingLevel = 0;
            throw new ezcDbTransactionException( "commit() called before beginTransaction()." );
        }
 
        $retval = true;
        if ( $this->transactionNestingLevel == 1 ) {
            if ( $this->transactionErrorFlag ) {
                parent::rollback();
                $this->transactionErrorFlag = false; // reset error flag
                $retval = false;
            } else {
                parent::commit();
            }
        }
        // else NOP
        $this->transactionNestingLevel--;
        return $retval;
    }
 
    public function rollback() {
        if ( $this->transactionNestingLevel <= 0 ) {
            $this->transactionNestingLevel = 0;
            throw new ezcDbTransactionException( "rollback() called without previous beginTransaction()." );
        }
        if ( $this->transactionNestingLevel == 1 ) {
            parent::rollback();
            $this->transactionErrorFlag = false; // reset error flag
        } else {
            // set the error flag, so that if there is outermost commit
            // then ROLLBACK will be done instead of COMMIT
            $this->transactionErrorFlag = true;
        }
        $this->transactionNestingLevel--;
        return true;
    }
}
 
As can be seen, the beginTransaction(), commit() and rollBack() methods keep track of what level of transaction there is, and in case of an error make sure everything is rolled back neatly. Even in nested transactions, which aren't possible otherwise (using PDO and mysql)

So now I can finally do something like this:

Code: Select all

 
class Routeclimb {
  public function save(){
    $this->dbh->beginTransaction();
    try {
      // first save a route and get it's id
      $route = new Route;
      $result = $route->save($somevars);
      // then use that id in the query to save the routeclimb
      $sql = "INSERT INTO `routeclimbs` .. $someothervars and $result ";
      // .. etc
      $this->dbh->commit();
    } catch () {
      // rollback and handle error
      $this->dbh->rollBack();
    }
  }
}
class Route {
  public function save(){
    $this->dbh->beginTransaction();
    try {
      // first save an area and get it's id
      $area = new Area;
      $result = $area->save($somevars);
      //  then use that id in the query to save the route
      $sql = "INSERT INTO `routes` ..  $someothervars and $result";
      // .. etc
      $this->dbh->commit();
    } catch () {
      // rollback and handle error
       $this->dbh->rollBack();
    }
  }
}
class Area {
  public function save(){
    $this->dbh->beginTransaction();
    try {
      // first save a country and get it's id
      $country = new Country;
      $result = $country->save($somevars);
      //  then use that id in the query to save the area
      $sql = "INSERT INTO `areas` .. $someothervars and $result";
      // .. etc
      $this->dbh->commit();
    } catch () {
      // rollback and handle error
      $this->dbh->rollBack();
    }
  }
}
class Country {
  public function save(){
    // etc
  }
}
 
As you see, I can now re-use the save() methods from the other classes AND use transactions in all models. Otherwise I would have to repeat the same queries over and over again in all classes. For example the Routeclimb class save() would need a query to save the country, one for the area, one for the route and of course for the routeclimb itself. The Route class save() method would need a query to save the country, area and route.

So what do you guys think? I can see this being useful in more cases. Say you have a model for Blogposts, which should be able to save to the Blog table, to the Author table, to the Tags table. All within a transaction. But you would also have a separate Tags model with a save method with a transaction of its own.
Post Reply