DevTips: Comparing "Type" Values Between Environments

thread: 2 messages  |  last: about 8 years ago  |  started: tuesday, april 17, 2012, 4:20 pm pdt

#1  |  Mike Ho (San Diego, CA) United States of America Qcodo Administrator
Tuesday, April 17, 2012, 4:20 PM PDT

Hey all...

I know it's been a while since I've posted here... but I just wanted to give a quick “hi” to everyone, and also I wanted to start a new series of postings within the Official Blog forum that I want to begin to have:

Over the years of working with, consulting, and talking to development teams, there are common, deeper rooted questions that come up time and time again with regards to efficiently and effectively using Qcodo (as well as PHP overall) in a mature project dev cycle.  Things from architecture issues, code structure, maintenance and ongoing modularity, refactoring advice, etc... all issues that come up time and time again, especially on applications that have been up and running for months and years.

Since a lot of these things aren't really covered in any sort of in-depth tutorial (and quite frankly, since many of these things aren't really well suited for an in-depth tutorial), I figure I should start blogging about them from time to time...

And so I present to you our first posting of a series known as “DevTips”.

Comparing “Type” Values Between Environments

For our first entry, I wanted to talk about something that a lot of mature Qcodo applications run into on a very regular basis.  Specifically, most development teams are running (or if you're not, you should be running) separate environments between production and development, which means that they are maintaining two distinct databases.

In general, you want to keep the two database environments in sync (e.g. if you make data model changes on dev, you should make those same changes on production when you push the updated code to production).

The tough part of course is keeping track of what changed in dev, so that you know what should be applied to production.

I'll talk about the overall data model and tips on how to keep the model, itself, in sync.  But for now, I wanted to talk specifically about the “_type” tables.

I've got a really simple CLI-based PHP script that I call typediff that can be run from any POSIX-based command line (e.g. Mac OS X, Linux, Unix, or Cygwin) which basically takes the name of two different databases.  And all it does is compare all the various type tables, and inspects the actual values stored within those type tables, in each database you specify.

Then the next step is that it does a simple “diff” on the results of both, and it shows you what type values are missing from one vs. the other.

if (($_SERVER['argc'] != 3) && ($_SERVER['argc'] != 4)) {
        print (
'usage: typediff FROM_DB TO_DB [TABLES_TO_IGNORE]' "\r\n");
        exit (

ProcessDatabaseType($strDatabaseName$strTablesToIgnoreArray) {
$objDb = new mysqli('localhost''root'''$strDatabaseName);
$objTableResult $objDb->query('SHOW TABLES WHERE Tables_in_' $strDatabaseName " LIKE '%_type';");
$strToReturn null;

        while (
$objTableRow $objTableResult->fetch_array()) {
$strTableName $objTableRow['Tables_in_' $strDatabaseName];
            if (!
array_key_exists(strtolower($strTableName), $strTablesToIgnoreArray)) {
$objResult $objDb->query('SELECT * FROM ' $strTableName ' ORDER BY id;');
                while (
$objRow $objResult->fetch_array()) {
$strToReturn .= sprintf('%s[%s] = "%s"' "\r\n",


// Should we ignore any tables?
$strTablesToIgnoreArray = array();
    if (
array_key_exists(3$_SERVER['argv'])) {
        foreach (
explode(','$_SERVER['argv'][3]) as $strTableName)
$strTablesToIgnoreArray[strtolower($strTableName)] = true;

file_put_contents('/tmp/from.sql'ProcessDatabaseType($_SERVER['argv'][1], $strTablesToIgnoreArray));
file_put_contents('/tmp/to.sql'ProcessDatabaseType($_SERVER['argv'][2], $strTablesToIgnoreArray));
passthru('diff /tmp/from.sql /tmp/to.sql');

A few assumptions: you have your PHP cli executable located in /usr/local/bin/php (see line #1) and you have the CLI tool diff installed on your local machine.  Also, it assumes that you are accessing the database via localhost, using “root” as the username and no password.

A tool like this can help when you do a build/release to production to ensure your type tables are up to date on production as what you were developing against on dev.  A typical approach would be to get the latest copy of your production database locally onto your machine in dev, and then run the typediff between your local copy of prod and your local dev instance, and you should see the differences.

From there, it should be pretty simple/straightforward to write up a little SQL script to perform any INSERTs, UPDATEs or DELETEs in order to get your prod to match what you have in dev.

Hopefully this quick little utility will help folks when trying to deal with simply knowing what the differences are within the “_type” tables on two difference versions of the same database.

As always, please post with any comments or suggestions.

#2  |  roachlovesick United States of America
Tuesday, April 17, 2012, 8:19 PM PDT

Thanks for sharing, we'll put this to good use.

Copyright © 2005 - 2021, Quasidea Development, LLC
This open-source framework for PHP is released under the terms of The MIT License.