PDO: determining if a transaction is active

I am currently working on some code for my CMS that creates a site
folder, then creates all the necessary child folders inside it. The
method that creates folders needs to insert into 2 tables so it wraps
the operation in a transaction. (psuedocode below)

public function createItem ($parent)
{
$this -> database -> beginTransaction ();
$this -> database -> query ('INSERT INTO items (param1,
param2 ... ) values (val1, val2 ... )');
$newId = myMethodForGettingInsertId ();
$this -> database -> query ('INSERT INTO folders (id, param3 ... )
values ($newId, val3 ... )');
$this -> database -> commit ();
return ($newId);
}

(the real code is obviously a lot more sophisticated and has all the
error checking and rollbacks and what have you but the mock up code
below is clearer regarding the intent of the method)

In my site creating class I have something along the lines of

public function createItem ($parent)
{
$this -> database -> beginTransaction ();
$newId = parent::createItem ($parent);
parent::createItem ($newId, 'images');
parent::createItem ($newId, 'css');
parent::createItem ($newId, 'other_stuff');
$this -> database -> commit ();
}

This obviously causes an exception. I can take the transaction code
out of my folder class but the creation of a folder should be atomic.
I could leave it out of the site creation class, but I really do need
site creation to be atomic as well.

Is there a way of determining that I am inside a transaction before
attempting to start one?

Re: PDO: determining if a transaction is active

On Dec 18, 5:31 pm, Gordon wrote:
> I am currently working on some code for my CMS that creates a site
> folder, then creates all the necessary child folders inside it. The
> method that creates folders needs to insert into 2 tables so it wraps
> the operation in a transaction. (psuedocode below)
>
> public function createItem ($parent)
> {
> $this -> database -> beginTransaction ();
> $this -> database -> query ('INSERT INTO items (param1,
> param2 ... ) values (val1, val2 ... )');
> $newId = myMethodForGettingInsertId ();
> $this -> database -> query ('INSERT INTO folders (id, param3 ... )
> values ($newId, val3 ... )');
> $this -> database -> commit ();
> return ($newId);
>
> }
>
> (the real code is obviously a lot more sophisticated and has all the
> error checking and rollbacks and what have you but the mock up code
> below is clearer regarding the intent of the method)
>
> In my site creating class I have something along the lines of
>
> public function createItem ($parent)
> {
> $this -> database -> beginTransaction ();
> $newId = parent::createItem ($parent);
> parent::createItem ($newId, 'images');
> parent::createItem ($newId, 'css');
> parent::createItem ($newId, 'other_stuff');
> $this -> database -> commit ();
>
> }
>
> This obviously causes an exception. I can take the transaction code
> out of my folder class but the creation of a folder should be atomic.
> I could leave it out of the site creation class, but I really do need
> site creation to be atomic as well.
>
> Is there a way of determining that I am inside a transaction before
> attempting to start one?

Only if you keep track of it yourself, write your own
beginTransaction() and commit() methods and use a transaction count
variable.

All calls to your own beginTransaction() and commit() methods
increment/decrement the transaction counter respectively. Only the
first call to beginTransaction calls $this->database->beginTransaction
and $this->database->commit() is only called when the transaction
counter reaches 0 again.

Re: PDO: determining if a transaction is active

On Dec 19, 12:33 pm, Tim Hunt wrote:
> On Dec 18, 5:31 pm, Gordon wrote:
>
>
>
> > I am currently working on some code for my CMS that creates a site
> > folder, then creates all the necessary child folders inside it. The
> > method that creates folders needs to insert into 2 tables so it wraps
> > the operation in a transaction. (psuedocode below)
>
> > public function createItem ($parent)
> > {
> > $this -> database -> beginTransaction ();
> > $this -> database -> query ('INSERT INTO items (param1,
> > param2 ... ) values (val1, val2 ... )');
> > $newId = myMethodForGettingInsertId ();
> > $this -> database -> query ('INSERT INTO folders (id, param3 ... )
> > values ($newId, val3 ... )');
> > $this -> database -> commit ();
> > return ($newId);
>
> > }
>
> > (the real code is obviously a lot more sophisticated and has all the
> > error checking and rollbacks and what have you but the mock up code
> > below is clearer regarding the intent of the method)
>
> > In my site creating class I have something along the lines of
>
> > public function createItem ($parent)
> > {
> > $this -> database -> beginTransaction ();
> > $newId = parent::createItem ($parent);
> > parent::createItem ($newId, 'images');
> > parent::createItem ($newId, 'css');
> > parent::createItem ($newId, 'other_stuff');
> > $this -> database -> commit ();
>
> > }
>
> > This obviously causes an exception. I can take the transaction code
> > out of my folder class but the creation of a folder should be atomic.
> > I could leave it out of the site creation class, but I really do need
> > site creation to be atomic as well.
>
> > Is there a way of determining that I am inside a transaction before
> > attempting to start one?
>
> Only if you keep track of it yourself, write your own
> beginTransaction() and commit() methods and use a transaction count
> variable.
>
> All calls to your own beginTransaction() and commit() methods
> increment/decrement the transaction counter respectively. Only the
> first call to beginTransaction calls $this->database->beginTransaction
> and $this->database->commit() is only called when the transaction
> counter reaches 0 again.

Yes, that sounds like a cunning plan. I can add that into my PDO
wrapper easily enough I think. Thanks for the help.

Re: PDO: determining if a transaction is active

"Gordon" wrote in message
news:94987e96-200d-4909-ab1a-f75049c41da6@t1g2000pra.googleg roups.com...
> On Dec 19, 12:33 pm, Tim Hunt wrote:
>> On Dec 18, 5:31 pm, Gordon wrote:
>>
>>
>>
>> > I am currently working on some code for my CMS that creates a site
>> > folder, then creates all the necessary child folders inside it. The
>> > method that creates folders needs to insert into 2 tables so it wraps
>> > the operation in a transaction. (psuedocode below)
>>
>> > public function createItem ($parent)
>> > {
>> > $this -> database -> beginTransaction ();
>> > $this -> database -> query ('INSERT INTO items (param1,
>> > param2 ... ) values (val1, val2 ... )');
>> > $newId = myMethodForGettingInsertId ();
>> > $this -> database -> query ('INSERT INTO folders (id, param3 ... )
>> > values ($newId, val3 ... )');
>> > $this -> database -> commit ();
>> > return ($newId);
>>
>> > }
>>
>> > (the real code is obviously a lot more sophisticated and has all the
>> > error checking and rollbacks and what have you but the mock up code
>> > below is clearer regarding the intent of the method)
>>
>> > In my site creating class I have something along the lines of
>>
>> > public function createItem ($parent)
>> > {
>> > $this -> database -> beginTransaction ();
>> > $newId = parent::createItem ($parent);
>> > parent::createItem ($newId, 'images');
>> > parent::createItem ($newId, 'css');
>> > parent::createItem ($newId, 'other_stuff');
>> > $this -> database -> commit ();
>>
>> > }
>>
>> > This obviously causes an exception. I can take the transaction code
>> > out of my folder class but the creation of a folder should be atomic.
>> > I could leave it out of the site creation class, but I really do need
>> > site creation to be atomic as well.
>>
>> > Is there a way of determining that I am inside a transaction before
>> > attempting to start one?
>>
>> Only if you keep track of it yourself, write your own
>> beginTransaction() and commit() methods and use a transaction count
>> variable.
>>
>> All calls to your own beginTransaction() and commit() methods
>> increment/decrement the transaction counter respectively. Only the
>> first call to beginTransaction calls $this->database->beginTransaction
>> and $this->database->commit() is only called when the transaction
>> counter reaches 0 again.
>
> Yes, that sounds like a cunning plan. I can add that into my PDO
> wrapper easily enough I think. Thanks for the help.

being careful to note that you can rollback an entire set of transactional
statments on a per-connection basis as well as rolling back individual
statements.

Re: PDO: determining if a transaction is active

On Dec 19, 4:55 pm, "Steve" wrote:
> "Gordon" wrote in message
>
> news:94987e96-200d-4909-ab1a-f75049c41da6@t1g2000pra.googleg roups.com...
>
>
>
> > On Dec 19, 12:33 pm, Tim Hunt wrote:
> >> On Dec 18, 5:31 pm, Gordon wrote:
>
> >> > I am currently working on some code for my CMS that creates a site
> >> > folder, then creates all the necessary child folders inside it. The
> >> > method that creates folders needs to insert into 2 tables so it wraps
> >> > the operation in a transaction. (psuedocode below)
>
> >> > public function createItem ($parent)
> >> > {
> >> > $this -> database -> beginTransaction ();
> >> > $this -> database -> query ('INSERT INTO items (param1,
> >> > param2 ... ) values (val1, val2 ... )');
> >> > $newId = myMethodForGettingInsertId ();
> >> > $this -> database -> query ('INSERT INTO folders (id, param3 ... )
> >> > values ($newId, val3 ... )');
> >> > $this -> database -> commit ();
> >> > return ($newId);
>
> >> > }
>
> >> > (the real code is obviously a lot more sophisticated and has all the
> >> > error checking and rollbacks and what have you but the mock up code
> >> > below is clearer regarding the intent of the method)
>
> >> > In my site creating class I have something along the lines of
>
> >> > public function createItem ($parent)
> >> > {
> >> > $this -> database -> beginTransaction ();
> >> > $newId = parent::createItem ($parent);
> >> > parent::createItem ($newId, 'images');
> >> > parent::createItem ($newId, 'css');
> >> > parent::createItem ($newId, 'other_stuff');
> >> > $this -> database -> commit ();
>
> >> > }
>
> >> > This obviously causes an exception. I can take the transaction code
> >> > out of my folder class but the creation of a folder should be atomic.
> >> > I could leave it out of the site creation class, but I really do need
> >> > site creation to be atomic as well.
>
> >> > Is there a way of determining that I am inside a transaction before
> >> > attempting to start one?
>
> >> Only if you keep track of it yourself, write your own
> >> beginTransaction() and commit() methods and use a transaction count
> >> variable.
>
> >> All calls to your own beginTransaction() and commit() methods
> >> increment/decrement the transaction counter respectively. Only the
> >> first call to beginTransaction calls $this->database->beginTransaction
> >> and $this->database->commit() is only called when the transaction
> >> counter reaches 0 again.
>
> > Yes, that sounds like a cunning plan. I can add that into my PDO
> > wrapper easily enough I think. Thanks for the help.
>
> being careful to note that you can rollback an entire set of transactional
> statments on a per-connection basis as well as rolling back individual
> statements.

In the end I decided that the transaction counting approach would be
problematic because when do you commit and when do you roll back? In
the end I went for an approach where the parent class will try to
initiate the transaction itself and assume it's already in one if it
fails to do so.

The createItem method in CmsItem does no transaction checking because
CmsItem is abstract and so that version of the method will never be
called from outside a descendant class.

class CmsDir extends CmsItem
// Directory (folder) management
{
public function createItem ($title, $path, $summary, $keywords,
$notes, $parent, $type = self::TYPE_DIR)
// Create a new directory
{
static $preparedQuery = false;
// Prepare query
if (!$preparedQuery)
{
$query = 'INSERT INTO cms_dirs (
itm_id,
tpl_id
) VALUES (
?, 0
);';
$preparedQuery = $this -> database -> prepare ($query);
}
// Sites can only be created in the root and folders must have a
parent
if ((($parent != 0) && ($type == self::TYPE_DIR))
|| (($parent == 0) && (($type == self::TYPE_SITE))))
{
// Start a transaction if we're not already in one
try
{
$this -> database -> beginTransaction ();
$transacting = true;
}
catch (Exception $e)
{
$transacting = false;
}
// Create item entry
if ($newId = parent::createItem ($title, $path, $summary,
$keywords, $notes, $parent, $type))
{
// Create directory table entry
if (($preparedQuery -> execute (array ($newId)))
&& ($preparedQuery -> rowCount () == 1))
{
// Success
if ($transacting)
{
$this -> database -> commit ();
}
return ($newId);
}
else
{
// Couldn't create directory table entry
if ($transacting)
{
$this -> database -> rollback ();
}
}
}
else
{
// Couldn't create item table entry
if ($transacting)
{
$this -> database -> rollback ();
}
}
}
else
{
// Type and parent are incompatible
}
}
}

class CmsSite extends CmsDir
// Wrapper for dirs the deal with the special case of sites
{
public function createItem ($title, $path, $summary, $keywords,
$notes)
// Create a new site
{
$this -> database -> beginTransaction ();
// Create the site item
if ($newId = parent::createItem ($title, $path, $summary, $keywords,
$notes, 0, self::TYPE_SITE))
{
// Create support folders for this site
$assetDir = new CmsDir (0, $this -> database, $this -> user);
$styleDir = new CmsDir (0, $this -> database, $this -> user);
$scriptDir = new CmsDir (0, $this -> database, $this -> user);
$imageDir = new CmsDir (0, $this -> database, $this -> user);
if (($assetDir -> createItem ('Site assets', 'assets', '', '', '',
$newId))
&& ($styleDir -> createItem ('Stylesheets', 'css', '', '', '',
$newId))
&& ($scriptDir -> createItem ('Javascript', 'js', '', '', '',
$newId))
&& ($imageDir -> createItem ('Images', 'ui', '', '', '', $newId)))
{
$this -> database -> commit ();
return ($newId);
}
}
else
{
$this -> database -> rollback ();
}
}
}