Adding Transaction support to the PDO Clases

[tapper]

It appears that I've successfully added Transaction support (beginTransaction, commit, rollback) to the resulting PDO class objects.

I've not taken the time to refactor this into the source that generates the class object.  I've just altered the resulting class object.  I've only got a few to build and I don't have the time at this point to change the source.

But, it's pretty easy and only took me about 60 minutes to figure it out for the base Database class and then it takes just 5 to 10 minutes to update each sub class (what is generated for your specific table).

So - if anyone has any input or thoughts on it, I'd love to hear about it.

Cheers!

Here's the changes....

1) Add the following to class.database.php

        //tpaulsme: 2012-07-07 - Added transactional support
        /**
        * <b>pdo_beginTransaction</b> class.
        * @author Php Object Generator/Todd Paulsmeyer
        * @version 3.0 / PHP5.1
        * @see http://www.phpobjectgenerator.com/
        * @copyright Free for personal & commercial use. (Offered under the BSD license)
        */
        public static function pdo_beginTransaction($connection)
    {
        try
        {
           
            $connection->beginTransaction();
           
            return  true;
        }
        catch (PDOException $e)
        {
            return false;
        }
    }
        //tpaulsme: 2012-07-07 - Added transactional support
        /**
        * <b>pdo_commit</b> class.
        * @author Php Object Generator/Todd Paulsmeyer
        * @version 3.0 / PHP5.1
        * @see http://www.phpobjectgenerator.com/
        * @copyright Free for personal & commercial use. (Offered under the BSD license)
        */
        public static function pdo_commit($connection)
    {
        try
        {
           
            $connection->commit();
           
            return  true;
        }
        catch (PDOException $e)
        {
            return false;
        }
    }       
        //tpaulsme: 2012-07-07 - Added transactional support
        /**
        * <b>pdo_rollBack</b> class.
        * @author Php Object Generator/Todd Paulsmeyer
        * @version 3.0 / PHP5.1
        * @see http://www.phpobjectgenerator.com/
        * @copyright Free for personal & commercial use. (Offered under the BSD license)
        */
        public static function pdo_rollBack($connection)
    {
        try
        {
           
            $connection->rollBack();
           
            return  true;
        }
        catch (PDOException $e)
        {
            return false;
        }
    }
       

Next, you have to make it so that each time you call an "operation" that a new connection is not created, but rather you use the existing one.  This has two areas of changes.  A) class.database.php and B) the class.yourtable.php that POG generates for you.

2) Inside class.database.php, simply change the name from Database() to Databse_Connection() [for clarity] and propagate that name change.  You'll also change the Connect() function as shown...

private function Database_Connection()
    {
        $databaseName = $GLOBALS['configuration']['db'];
        $driver = $GLOBALS['configuration']['pdoDriver'];
        $serverName = $GLOBALS['configuration']['host'];
        $databaseUser = $GLOBALS['configuration']['user'];
        $databasePassword = $GLOBALS['configuration']['pass'];
        $databasePort = $GLOBALS['configuration']['port'];
        if (!isset($this->connection))
        {
            $this->connection = new PDO($driver.':host='.$serverName.';port='.$databasePort.';dbname='.$databaseName, $databaseUser, $databasePassword);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        if (!$this->connection)
        {
            throw new Exception('I cannot connect to the database. Please edit configuration.php with your database configuration.');
        }
    }
       
      
           public static function Connect()
    {
        static $database = null;
        if (!isset($database))
        {

//change below

            $database = new Database();
            $database->Database_Connection();
        }
        return $database->connection;
    }

3) Inside your table specific code, change everything that references a $connection to first check to see if it's null = and to pass in a null one by default.  This way, all your old code will still work.

Thus, the first part of each of these functions will change...

function Get($user_signupid, $connection = null)
    {
        if (is_null($connection)){
                    $connection = Database::Connect();
                }
function GetList($fcv_array = array(), $sortBy='', $ascending=true, $limit='', $connection = null)
    {
        if (is_null($connection)){
                    $connection = Database::Connect();
                }

function Save($connection = null)
    {
            if (is_null($connection)){
        $connection = Database::Connect();
            }

function SaveNew($connection = null)
    {
        $this->user_signupid = '';
        return $this->Save($connection);
    }

function Delete($connection = null)
    {
            if (is_null($connection)){
        $connection = Database::Connect();
            }

function DeleteList($fcv_array, $connection = null)
    {
        if (sizeof($fcv_array) > 0)
        {
            if (is_null($connection)){
                            $connection = Database::Connect();
                        }

4) To use it.. IMPORTANT: Good error handling (try-catch) still needs to be built into it - but the sample looks like it works.

<?php


require_once($_SERVER['DOCUMENT_ROOT']."/objects/class.database.php");
require_once($_SERVER['DOCUMENT_ROOT']."/objects/class.user_signup.php");
require_once($_SERVER['DOCUMENT_ROOT']."/pog_configuration.php");


$db = new Database();

$connection = $db->Connect();

$db->pdo_beginTransaction($connection);

$user_signup = new user_signup();

$user_signup->user_id = "1";
$user_signup->email = "todd at home dot com";
$user_signup->user_date_time_signup = "2012-07-07 07:07:07 pm";
$user_signup->user_date_time_conf_email_sent = "2012-06-06 06:06:06 pm";
$user_signup->Save($connection)

$db->pdo_commit($connection);
echo "sb Saved - no rollback";
//$db->pdo_rollBack($connection);
//echo "user not saved - sb rolled back";

?>