dbav - db automatic versioning and patches

PURPOSE:

When developing an application which utilizes a database, usually some changes in the database structure are needed. It becomes a problem, when the application is developed and implemented in the production at same time. What's more - there may be many versions of the same application, connected to databases with various structures,

The process of tracking differences between databases and introducing appropriate changes is quite painfull. Usually it is done by means of puttingset of .sql files in some directory and applying them to the database, depending from which version to which it's being upgraded.

SOLUTION:

The application should automatically introduce changes in a database right after connection to it, without requiring the user to perform any upgrade.

ASSUMPTIONS:

  • AdoDB library is used for connection to the database
  • Class takes the already connected db (adodb) object as an argument to the constructor

    dbav object does not parse and compare all SQL statements (patches), but just stores the hash(es)/checksum(s) of statement(s)/version(s) which should be applied. If it turns out that updates are needed ( based on the checksums and information from the database ), relevant changes are introduced into the db (and checksums are stored there).

  • Class must be able to run in the slave mode, to use in backends connected to application, which cannot share the codebase.

    In that mode class does not have information about statements inside, but only about the version. In that mode it should allow or disallow connecting to db, but it will not upgrade the db (because it does not have the relevant information).

  • Changes can be minor or major. A slave mode connection can have different minor version and still remain compatible (like in Linux libraries - in almost each case an application developed with 0.1.2 api can run with 0.1.3 or 0.1.1 library

    API:

    <?
    class DbAuVer {
    
       /**
         @param: $db - AdoDB object
         @param: $my_used_version - the required version of database, used only in the ‘slave’ mode
       */
       public __construct($db,$my_used_version=null);
    
       /**
          check if the application can connect to the db, do upgrades if necessary
          throw an exception if connection is disallowed
       */
       public check();
    
       /**
          method to override in subclass, 'patches' definitions will be stored here
       */
       protected definePatches();
    
    
       /**
         method to call from setPatches - define 'patch' definition
    
         @param $id - identifier of version related to this patch
         @param $major - it's a major version upgrade
         @param $sql - sql query to run/patch content
         @param $params - array of parameters to query with '?' placeholders
       */
       protected definePatch($id,$major,$sql,$params);
    
        /**
         * Method to call from definePatches - define 'cutoff version' definition. Versions less or equal to cuver aren't compared. 
         *
         * @param $version - identifier of cutoff version
       */
       protected defineCutOffVer($version);
    
    }
    ?>
    

    EXAMPLE OF USAGE:

    1. let us say we have the following table in a database:
      CREATE TABLE foo(id SERIAL,bar varchar);
      

      and the following php code (a.php)

      <?
      class myver extends DbAuVer {
          function definePatches() {
              $this->definePatch("0.0.0",true,"");//just to define initial version
          };
      }
      
      
      //initialisation of database connection,
      $db = AdoNewConnection(.......);
      
      $au = new myver($db);
      $au->check();//here the control table will be stored into database, as it's first run of db.
      
      $db->execute("INSERT INTO foo(bar) VALUES('foobar')");
              
      ?>
      

      There is also a corresponding script (b.php, working in the slave mode), which, for example, contains the following data:

      <?
              $db = AdoNewConnection(......);
              $au = new DbAuVer($db,"0.0.0");
              $au->check();//the script connects to database and detects version “0.0.0”, so it’s ok, we can continue
              
              $res = $db->execute("SELECT bar FROM foo ORDER BY id DESC LIMIT 1");
              $o = $res->fetchNextObj();
              print "last bar from foo is '".$o->bar."'\n";
      ?>
      
    2. But after that, we notice that bar should not be null. So, a.php is changed as follows ...
      <?
          class myver extends DbAuVer {
              function definePatches() {
                  $this->definePatch("0.0.0",true,"");//just to define initial version
                  $this->definePatch("0.0.1",false/*this is a minor change*/,
                                              "ALTER TABLE foo ALTER COLUMN bar SET NOT NULL");
              };
          }
          (...)
      ?>
      

      When b.php is ran, it detects that the db version is 0.0.1 . However, it will not throw an exception, because 0.0.1 is minor, and the last major version (0.0.0) is equal to that specified in the constructor.

    3. And now it is time for some really major changes. We think that the table name foo is not good and we want to change it to something, so we add the following line in definePatches():
      <?
                $this->definePatch("0.1.0",true,
                                     "ALTER TABLE foo RENAME TO something");
      ?>
      

      When ran, it alters/renames the table name foo to something, and stores info about the most recent version of db schema (0.1.0).

      Now, when the slave is running, it detects that the db is no longer compatible with it. $au->check() throws an exception, so that the developer is warned that either he is connecting it to a wrong database or there is a need to adapt the script to the new db version. He does the latter, and so b.php looks like this:

              
      <?
          $db = AdoNewConnection(......);
          $au = new DbAuVer($db,"0.1.0");
          $au->check();//script connects to database and detects version “0.0.0”, so it’s ok, we can continue
          
          $res = $db->execute("SELECT bar FROM something ORDER BY id DESC LIMIT 1");
          $o = $res->fetchNextObj();
          print "last bar from something is '".$o->bar."'\n";
      ?>
      
    4. And what if another developer forked code in a very early phase and changed the db schema in a slightly different way, so that his a.php looks like that:
      <?
          class myver extends DbAuVer {
              function definePatches() {
                  $this->definePatch("0.0.0",true,"");//just to define initial version
                  $this->definePatch("0.0.1",false/*this is a minor change*/,
                                  "ALTER TABLE foo ALTER COLUMN bar SET DEFAULT 'empty'");
                  $this->definePatch("0.1.0",true,
                                   "ALTER TABLE foo RENAME COLUMN bar TO description");
              };
          }
      
      
              //initialisation of database connection,
              $db = AdoNewConnection(.......);
      
              $au = new myver($db);
              
              $au->check();
              
              $db->execute("INSERT INTO foo(description) VALUES('description value')");
      ?>
      

      If he connects this script to our db without checks, it will generate an error because we have no description column in the foo table. Moreover, we have no foo table, but its name is something now. Performing $au->check() will raise an error - incompatible base.

      One could say 'hey, but it will check if 0.1.0 is up to date, his version is 0.1.0, so it will think that it is connecting to a proper db'. Wrong!. It will compare not only version names, but also the checksums of sql statements. If they are different - it is a clear sign that it is not the version we are trying to connect to. In other words - the script detects that there is a *major* version of the database (with checksum of "ALTER TABLE foo RENAME TO something") , which it does not have in its patches.

    Download:

    See below for a downloadable tarball.

AttachmentSize
dbav-20100723.tgz817.11 KB