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

I have two data structure dumps that I generated from MySQL workbench. Now I would like to add them or include them in a PHP script and use them as variables:

var $sourceStruct = ''; //structure dump of the reference database
var $destStruct = ''; //structure dump of database to update

I am following this script which compares two databases and synchronizes them but author did not give any explanation how to include the dumps in this script?

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);

Result:

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

Solution:

Well, if you just want to get the dumps into the variables then read the files with PHP's built-in file_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;
}

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

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();
    }
   

   becomes:

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

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);

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\'',
)