Compare versions of dbs and tables

[lampstax]

Hey guys,

 

I have three db servers ( live and beta and dev ).

 

Obviously as the name suggest, each serves a different purpose, and while the db structure are very similar, all three db have differences in them.  Some more obvious than other.  A new table in dev thats not yet in beta or live is easier to spot than a int value in some table has been changed from smallint to medium int.

 

I need to find a way to compare two dbs or alteast two tables and find all the differences among them (atleast structurally, but data differences too would be just great). 

 

I could spend time building my own code that queries information_schema, but something tells me that I would be reinventing the wheel.

 

Could any of you fine gents point me to a script / tool that does this ?

 

Thanks ahead of time.

[roopurt18]

I don't know of any tool or script that does this, so my suggestion would be to write a small program that queries information schema and does this.

 

Keeping the database schema consistent between multiple environments can be a pain in the neck.  For a long time I had a $version.sql file that accompanied each version and performed any database structure changes.  This eventually presented me with two problems:

 

1) As program versions increase you will be left with many SQL script files.  Without an automated update process, updating an installation from version 2.0.5 to 2.0.37 can become a huge hassle.

 

2) Sometimes it is convenient to run program code in-between operations on the database structure.  In this case, the update process turns into:

  + Run $version.a.sql

  + Run update program

  + Run $version.b.sql

  + Rinse and repeat as necessary

 

The whole system quickly becomes unmanageable.

 

I recently began experimenting with solutions to this problem and my most recent incarnation is to do this:

 

1) Add a table to the database named application with a column db_version.  This table should only ever have one row (enforce with triggers if possible) and essentially contains meta-information about the application.

2) application.db_version can be numeric or text, whichever you prefer.

 

Now create a class:

<?php
class DatabaseUpdater {
  /**
  * Call this function to update the database.  If the database is in need of updates, they will be applied.
  * Otherwise nothing occurs.
  *
  * @param bool $test_only
  */
  public function Update( $test_only = true ) {
      $db = new PDO(); // create your database handle, tell it to throw exceptions
      
      echo 'Applying database updates...' . "\n";
      if( $test_only ) {
          echo "TESTING ONLY -- NOTHING COMMITTED\n";
      }
      
      try {
          $db->beginTransaction();
          $t = $this->apply_1( $test_only, $db );
          $t = $t && $this->apply_2( $test_only, $db );
          $t = $t && $this->apply_3( $test_only, $db );
      } catch( Exception $ex ) {
          $t = false;
      }

      try {      
          if( $test_only === false && $t === true ) {
              echo "Attempting commit...";
              $t = $db->commit();
              echo "yes\n";
          }else{
              $db->rollBack();
          }
      } catch( Exception $ex ) {
          echo "no\n";
      }
      
      echo 'Database updates are a ' . ($t ? 'SUCCESS' : 'FAILURE') . "\n";
      if( $test_only ) {
          echo "Run again with \$test_only = false to apply the updates.\n";
      }
  }
  
  /**
  * Applies version 1 of database.
  * 
  * @param bool $test_only
  * @return bool
  */
  private function apply_1( $test_only, PDO $db ) {
      echo "Apply version 1...";
      try {
          $q = $db->query( "select count(*) as n from information_schema.tables where table_name='application'" );
          if( ! $q ) throw new Excpetion( "can't count" );
          $row = $q->fetchObject();
          if( ! $row ) throw new Exception( "no row" );
          if( ! isset( $row->n ) ) throw new Exception( "no count" );
          if( ((int)$row->n) === 1 ) echo "yes\n";
          else throw new Exception( "application exists already" );
      } catch( Exception $ex ) {
          echo "no\n";
          return false;
      }
      
      try {
          ob_start();
?>
-- This is our sql upgrade script
create table application (
    db_version integer
);

create table users (
    username varchar( 32 ),
    password varchar( 40 )
);

-- create a trigger on application that allows only one row to exist

-- now insert the database version into application
insert into application( db_version ) values ( 1 );
<?php
          $sql = ob_get_clean();
          $db->query( $sql );
      } catch( Exception $ex ) {
          return false;
      }
      return true;
  }
  
  /**
  * Applies version 2 of database.
  * 
  * @param bool $test_only
  * @return bool
  */
  private function apply_2( $test_only, PDO $db ) {
      echo "Apply version 2...";
      try {
          $q = $db->query( "select count(*) as n from application where db_version=1" );
          if( ! $q ) throw new Excpetion( "can't count" );
          $row = $q->fetchObject();
          if( ! $row ) throw new Exception( "no row" );
          if( ! isset( $row->n ) ) throw new Exception( "no count" );
          if( ((int)$row->n) === 1 ) echo "yes\n";
          else throw new Exception( "wrong db version" );
      } catch( Exception $ex ) {
          echo "no\n";
          return false;
      }
      
      try {
          ob_start();
?>
-- This is our sql upgrade script
alter table users add column first_name varchar( 36 );
alter table users add column last_name varchar( 36 );

update application set db_version=2 where 1=1;
<?php
          $sql = ob_get_clean();
          $db->query( $sql );
      } catch( Exception $ex ) {
          return false;
      }
      return true;
  }
  
  /**
  * Applies version 3 of database.
  * 
  * @param bool $test_only
  * @return bool
  */
  private function apply_3( $test_only, PDO $db ) {
      echo "Apply version 3...";
      try {
          $q = $db->query( "select count(*) as n from application where db_version=2" );
          if( ! $q ) throw new Excpetion( "can't count" );
          $row = $q->fetchObject();
          if( ! $row ) throw new Exception( "no row" );
          if( ! isset( $row->n ) ) throw new Exception( "no count" );
          if( ((int)$row->n) === 1 ) echo "yes\n";
          else throw new Exception( "wrong db version" );
      } catch( Exception $ex ) {
          echo "no\n";
          return false;
      }
      
      try {
          ob_start();
?>
-- Update the db in some manner

update application set db_version=3 where 1=1;
<?php
          $sql = ob_get_clean();
          $db->query( $sql );
      } catch( Exception $ex ) {
          return false;
      }
      return true;
  }
}
?>

 

Quick note:  This is not my real code.  I typed it up real quick so don't copy and paste it expecting it to work.  Use it as a template for the idea I'm talking about.

 

The purpose of this class is to have a single public method Update() that will call many private methods actually responsible for updating the database schema: apply_1(), apply_2(), apply_3(), etc.

 

The main update method can be run in test_only or commit mode.  In either case, a database handle is created and a transaction is opened.  Then each apply_*() method is called in sequential order.  Each individual method is responsible for determining if it should run.  The first method will check if the application table exists; if it does not then it assumes an empty database and will create the initial schema.  As the last statement the application table should be updated with the current version of the database.  Each successive apply_*() method is intended to run off a specific value in application.db_version.  If application.db_version() is not the correct value, then the apply_*() method ends early and does nothing.  Finally, if all apply_*() methods returned successfully and the run mode is commit, the transaction is committed.  Otherwise the transaction is rolled back and nothing occurs to the database.

 

In this way, you can plop this code into any environment and call a single method to update your database to the correct working version.  There's no more SQL script files to manage and apply manually.  You can easily inject PHP code into the update process.  All in all it's much less error prone and more manageable, for me at least.

 

(edit) I expect some day I may run into an issue with the number of statements allowed within a single transaction. 

[lampstax]

Wow, thanks for the detailed reply.

 

I think thats a good idea, almost like db 'versioning', which is really exactly what I need.  I'm surprise that there's nothing out there like that already (commercial or open source).

 

Might be a bit too time consuming at this stage of the project though, so I'll see if we can actually allocate some time and put some version of that tool on the to build list.

 

Thanks

[fenway]

Or you could `diff` the frm files.