Rolling back file moves, folder deletes and mysql queries

Question

This has been bugging me all day and there is no end in sight. When the user of my php application adds a new update and something goes wrong, I need to be able to undo a complex batch of mixed commands. They can be mysql update and insert queries, file deletes and folder renaming and creations.

I can track the status of all insert commands and undo them if an error is thrown. But how do I do this with the update statements? Is there a smart way (some design pattern?) to keep track of such changes both in the file structure and the database?

My database tables are MyISAM. It would be easy to just convert everything to InnoDB, so that I can use transactions. That way I would only have to deal with the file and folder operations. Unfortunately, I cannot assume that all clients have InnoDB support. It would also require me to convert many tables in my database to InnoDB, which I am hesitant to do.

Answer 1

PDO's rowcount() returns eftected rows on updates. mysqli's afftected_rows does the same

I'm by clients you mean clients whose servers you will be placing this application on. If you weren't to require innoDB on the servers you'd have to do some more coding to rollback changes on MyISAM tables.

The best way would be to modularize everything into functions (or class methods)

pseudo code:

function updateThisThing() {

    if ( !updateTable() ) {
        rollbackUpdateTable();
        return false;
    }

    if ( !updateFiles() ) {
        rollbackUpdateFiles();
        return false;
    }

    // more update statements

    return true

}

Answer 2

If you're absolutely stuck with MyISAM, you should see if the code can be arranged so that UPDATES are the very last thing performed. If an error occurs before then, no UPDATEs will be made.

If that's not feasible, you'll have to lock the pertinent tables, grab the current records, update them. If error, restore with grabbed records. Unlock tables.

Not very practical which is why there's InnoDB (as you know).

I think that's the basis of this module which you can check out:

http://www.deepbluesky.com/blog/-/myisam-transactions_20/

Answer 3

Have you looked into the Unit of Work pattern?

Here's a really roughshod example of how you might get started.

The basic UnitOfWork container.

class UnitOfWork
{
  protected $entities = array();
  protected $completed = array();

  final public function addEntity( IWorkUnitEntity $entity )
  {
    $this->entities[] = $entity;
  }

  final public function execute()
  {
    try {
      foreach ( $this->entities as $entity )
      {
        $entity->execute();
        $completed[] = $entity;
      }
    }
    catch ( UnitOfWorkRollbackException $e )
    {
      $this->rollbackCompleted();
    }

    return $this->commitAll();
  }

  protected function rollbackCompleted()
  {
    while ( $entity = array_pop( $this->completed ) )
    {
      $entity->rollback();
    }
  }

  protected function commitAll()
  {
    try {
      foreach ( $this->entities as $entity )
      {
        $entity->commit();
      }
    }
    catch ( UnitOfWorkRollbackException $e )
    {
      $this->rollbackCompleted();
      return false;
    }
    return true;
  }
}

A couple extras to help it along

class UnitOfWorkRollbackException extends Exception {};

interface IWorkUnitEntity
{
  public function execute();
  public function rollback();
}

Now, an example of a work entity

class FileMoverEntity implements IWorkUnitEntity
{
  protected
      $source
    , $destination
    , $newName
  ;

  public function __construct( $source, $destination, $newName = null )
  {
    $this->source = $source;
    $this->destination = dirname( $destination );
    $this->newName = $newName;
  }

  public function execute()
  {
    if ( is_readable( $this->source ) && is_writable( $this->destination ) )
    {
      return true;
    }
    throw new UnitOfWorkRollbackException( 'File cannot be moved' );
  }

  public function commit()
  {
    $filename = ( null === $this->newName )
      ? basename( $this->source )
      : $this->newName
    ;
    if ( !rename( $this->source, $this->destination . DIRECTORY_SEPARATOR . $filename ) )
    {
      throw new UnitOfWorkRollbackException( 'File move failed' );
    }
  }

  public function rollback()
  {
    // Nothing to do here since the file doesn't actually move until commit()
  }
}

Putting it all together.

$UoW = new UnitOfWork();

$UoW->addEntity( new FileMoverEntity( '/tmp/foo', '/home/me', 'profile.jpg' ) );
$UoW->addEntity( new FileMoverEntity( '/tmp/bar', '/root', 'profile.jpg' ) );

if ( $UoW->execute() )
{
  // all operations successful
}

Now, I didn't do some things you'd want to here - like keeping track of which exceptions were thrown so the client script can access that info - but I think you get the idea. And of course you can go on to make work entities for all sorts of operations - DB updates, API calls, whatever.

In terms of connecting to a database without transaction-safe tables - I don't have any insight.