How to add a structure dump of a MySQL database to a variable in PHP?

Solution:

Usage:

var $sourceStruct = 'CREATE TABLE ...'; //add structure dump of the reference database here
var $destStruct = 'CREATE TABLE ...'; //add structure dump of database to update

$updater = new dbStructUpdater();
$updates = $updater->getUpdates($sourceStruct, $destStruct);
Copy code

Result:

$updates == array (
    [0]=>"ALTER TABLE `b` MODIFY `name` varchar(255) NOT NULL",
    ...
)
Copy code

Solution:

Well, if you just want to get the dumps into the variables then read the files with PHP's built-infile_get_contents() function like this:

var $sourceStruct = file_get_contents('source_dump.sql') or die('Could not read source SQL dump!');
var $destStruct = file_get_contents('destination_dump.sql') or die('Could not read destination SQL dump!');

$updater = new dbStructUpdater();
$updates = $updater->getUpdates($sourceStruct, $destStruct);

foreach ($updates as $update) {
  print $update . ';' . PHP_EOL;
}

Copy code

This should generate all the SQL queries to update your destination database.

My test with the lib

Alternative lib available with Composer

On my side, the problem was that I didn't get the library on PHPClasses because I didn't want to create an account. So I looked on Google to find this same library elsewhere and I got it available for installation with Composer at this place: https://packagist.org/packages/eliasfarah/db-struct-sync

I created a new folder and inside it I run this command:

composer require eliasfarah/db-struct-sync
Copy code

This downloaded the project and created the following content:

• composer.json
• composer.lock
• vendor/
  • autoload.php <- the file to normally include.
  • composer/
  • eliasfarah/
    • db-struct-sync/
      • composer.json
      • dbStruct.php <- the file to correct.
      • nbproject/
      • tests/
        • compareSql_1_left.sql
        • compareSql_1_right.sql
        • left_1.sql
        • right_1.sql
        • testCase.php
        • update_1.sql
        • viewClasses.sql
        • viewClassesSplit.php

If you haven't got Composer

You could download the source code from GitHub: https://github.com/eliasfarah/dbStructSync

The edits on dbStruct.php

As it didn't work with my PHP 7 installation, I did some manual changes (not a good idea if we want to update the lib but it just was a test for your question). You could create a patch and add it to your own composer.json so that it's applied after installation.

1. The short opening tag at line 1 should be replaced by the long one:<? becomes<?php

2. At line 63, the class constructor should be renamed:

   /**
    * Constructor
    * @access public
    */
    function dbStructUpdater()
    {
        $this->init();
    }
   Copy code

   becomes:

   /**
    * Constructor
    * @access public
    */
    function __construct()
    {
        $this->init();
    }
   Copy code

My test's source code

I didn't have any MySQL dump so I used the ones provided by the tests in the library itself.

I wanted to use the Composer's autoload file like we usually do but it didn't seem to work, probably because the lib is old and doesn't respect common conventions. So I directly included the PHP file like this:

<?php

// Normal way to go not working :-(
//include 'vendor/autoload.php';

// Quick and dirty solution to have the class.
include 'vendor/eliasfarah/db-struct-sync/dbStruct.php';

$src = file_get_contents('vendor/eliasfarah/db-struct-sync/tests/left_1.sql');
$dest = file_get_contents('vendor/eliasfarah/db-struct-sync/tests/right_1.sql');

$updater = new dbStructUpdater();

$queries = $updater->getUpdates($src, $dest);

var_export($queries);
Copy code

The output

array (
  0 => 'ALTER TABLE `personalizer_config` MODIFY `cookyExpires` varchar(20) NOT NULL default \'30\'',
  1 => 'ALTER TABLE `personalizer_config` ADD `cron_id` varchar(255) NOT NULL',
  2 => 'ALTER TABLE `personalizer_config` MODIFY `enable_roles` tinyint(4) NOT NULL',
  3 => 'ALTER TABLE `personalizer_config` MODIFY `group_table` varchar(255) NOT NULL default \'group\'',
  4 => 'ALTER TABLE `personalizer_config` MODIFY `ldapswitch` tinyint(4) NOT NULL',
  5 => 'ALTER TABLE `personalizer_config` MODIFY `masterPassword` varchar(32) NOT NULL',
  6 => 'ALTER TABLE `personalizer_config` MODIFY `relations_table` varchar(255) NOT NULL default \'relations\'',
  7 => 'ALTER TABLE `personalizer_config` MODIFY `std_group_tpl_none` varchar(255) NOT NULL',
  8 => 'ALTER TABLE `personalizer_config` MODIFY `std_group_tpl_own` varchar(100) NOT NULL',
  9 => 'ALTER TABLE `personalizer_config` MODIFY `std_group_tpl_sub` varchar(255) NOT NULL',
  10 => 'ALTER TABLE `personalizer_config` MODIFY `stdCutOuts` varchar(255) NOT NULL',
  11 => 'ALTER TABLE `personalizer_config` MODIFY `stdUserFields` varchar(255) NOT NULL',
  12 => 'ALTER TABLE `personalizer_config` MODIFY `user_table` varchar(255) NOT NULL default \'user\'',
)
Copy code