Automatic db diff generation

[raveren]

Hey, excellent project and all, but you should be aware of the existence of this tool:

https://bitbucket.org/idler/mmp/wiki/Home

how about incorporating the two!

Edit: the project page is now at

https://github.com/idler/MMP/

[dsferruzza]

I agree! This feature would be really useful.

It is true that it also exists in Adminer, but having this main feature integrated in dbv would make it unstoppable!

[raveren]

The mysql guys get all the fun toys, while I develop postgres now :)

Nevertheless, good luck on this project, I had implemented the MMP tool in my previous projects so can vouch for it's awesomeness :)

[chanmix51]

I have given a look at a Postgres implementation but Pg does not feature a SHOW CREATE command. This makes the project a bit harder.

[victorstanciu]

@chanmix51 Hmm. Perhaps we can rely on pg_dump, invoking it via shell_exec?

[raveren]

For the record, I use apgdiff for pg_dump structure comparison and diff generation. Works great.

[abfan1127]

this would be a good way to detect schema differences: www.raymondhill.net/finediff/

[jlgy]

To see if we have a difference beetween the DB and the disk, we have add the following code:

DBV.php:

protected function _getSchema()
    {
        $return = array();
        $database = $this->_getAdapter()->getSchema();
        $disk = $this->_getDiskSchema();

        if (count($database)) {
            foreach ($database as $item) {
                $return[$item]['database'] = true;
            }
        }

        if (count($disk)) {
            foreach ($disk as $item) {
                $return[$item]['disk'] = true;
            }
        }

        if (count($database)) {
            foreach ($database as $item) {
                if( isset($return[$item]['database']) && $return[$item]['database'] && isset($return[$item]['disk'])  && $return[$item]['disk']) {

                    $diff = false;

                    if(file_exists(DBV_SCHEMA_PATH."/".$item.".sql")) {
                        $diskFile = md5(file_get_contents(DBV_SCHEMA_PATH."/".$item.".sql"));

                        $sql =  md5($this->_getAdapter()->getSchemaObject($item));

                        if($diskFile !== $sql) {
                            $diff = true;
                        }



                    }
                    $return[$item]['diff'] = $diff; 
                }
            }
        }


        ksort($return);
        return $return;
    }

schema.php ( you should find easly where to insert the code):

<th style="text-align: center; width: 50px;"><?php echo __('Diff'); ?></th>

and

<td style="text-align: center;" data-role="diff">
                            <?php if (isset($flags['diff']) && $flags['diff']) { ?>
                                <span class="label label-important"><?php echo __('YES'); ?></span>
                            <?php } else { ?>
                                <span class="label label-success"><?php echo __('NO'); ?></span>
                            <?php } ?>
                        </td>

A first step....