- Notifications
You must be signed in to change notification settings - Fork0
Benjaminhu/php-mysql-diff
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
MySQL Schema Diff - Comparison / Migration Script Generation
This is a fork ofcamcima/php-mysql-diff.
MySQL Utilities includes a similar tool,mysqldiff, that is absolutelyhorrible! The purpose of this project is to provide a tool that is simple to use, reliable and fast.
To install PHP MySQL Diff, install Composer and issue the following command:
$ ./composer.phar global require benjaminhu/php-mysql-diffThen, make sure you have ~/.composer/vendor/bin in your PATH, and you're good to go:
export PATH="$PATH:$HOME/.composer/vendor/bin"You can update PHP MySQL Diff through this command:
$ ./composer.phar global update benjaminhu/php-mysql-diffPHP MySQL Diff works with database creations scripts created bymysqldump, which is part of the MySQL distribution. In order to generate a database creation script, use the following command:
$ mysqldump -h hostname -u username -p -d dbname > outputfile.sqlThis tool may not work with creation scripts generated by other means because it relies on finely tuned regular expressions that could not work if the file format is different.
I chose to work with database creation scripts instead of working by connecting to the databases directly because it's more portable that way and you can work offline. In the future, I might develop the option to fetch the information directly from the databaseINFORMATION_SCHEMA table.
$ php-mysql-diff diff <from> <to> [-i <ignore-tables-file>]wherefrom is the path to the initial database creation script andto is the path to the target database creation script.
Ignore Tables
Use the-i option to ignore tables during comparison. The file format is a list of regular expressions to match the table names to be ignored, one per line.
Example:
/^employee.+//^catalog$//^test[\d]$/Output File
The output will be like this:
PHP MySQL Diff 1.0.0----------------------------------------• Parsing initial database ...... ✓• Parsing target database ....... ✓• Comparing databases ........... ✓FROM tests\fixtures\sakila.sql TO tests\fixtures\sakila_new.sql▲ table "test3" is in the TO database but not in the FROM database▼ table "test1" is in the FROM database but not in the TO database► table "test2" has a different schema ▲ column "new_field" is in the TO database but not in the FROM database ► column "id" has a different definition FROM `id` int(11) NOT NULL AUTO_INCREMENT TO `id` int(10) NOT NULL AUTO_INCREMENT ► column "fk" has a different definition FROM `fk` int(10) NOT NULL TO `fk` int(10) ► column "val" has a different definition FROM `val` decimal(10,2) NOT NULL TO `val` decimal(11,3) NOT NULL ► column "texto" has a different definition FROM `texto` varchar(60) DEFAULT NULL TO `texto` char(60) NOT NULL DEFAULT 'default' ► primary key has a different definition FROM PRIMARY KEY (`id`) TO PRIMARY KEY (`id`,`new_field`) ▲ foreign key "FK__test3" is in the TO database but not in the FROM database ▼ foreign key "FK__test1" is in the FROM database but not in the TO database ► index "FK__test1" has a different definition FROM KEY `FK__test1` (`fk`) TO UNIQUE KEY `FK__test1` (`datade`)Diff completed!▲ = only present in the TO database
► = different definitions between FROM and TO databases
▼ = only present in the FROM database
$ php-mysql-diff migrate <from> <to> [-o <output-file>] [-i <ignore-tables-file>] [-p]wherefrom is the path to the initial database creation script andto is the path to the target database creation script.
Ignore Tables
Use the-i option to ignore tables during comparison. The file format is a list of regular expressions to match the table names to be ignored, one per line.
Example:
/^employee.+//^catalog$//^test[\d]$/Output File
If the-o option is not used, the migration script will be output to thestdout.
The output (with the-o option) will be like this:
PHP MySQL Diff 1.0.0----------------------------------------• Parsing initial database ...... ✓• Parsing target database ....... ✓• Comparing databases ........... ✓• Generating migration script ... ✓• Writing output file ........... ✓Migration script generated!and the migration script will look like this:
# Disable Foreign Keys CheckSET FOREIGN_KEY_CHECKS=0;SET SQL_MODE='';# Deleted Tables-- deleted table `test1`DROPTABLE`test1`;# Changed Tables-- changed table `test2`ALTERTABLE`test2` DROPPRIMARY KEY, DROPFOREIGN KEY`FK__test1`,DROPINDEX`FK__test1`, CHANGE COLUMN`id``id`int(10)NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN`fk``fk`int(10) AFTER`id`, CHANGE COLUMN`val``val`decimal(11,3)NOT NULL AFTER`fk`, CHANGE COLUMN`texto``texto`char(60)NOT NULL DEFAULT'default' AFTER`val`, ADD COLUMN`new_field`int(10) AFTER`datade`, ADDPRIMARY KEY (`id`,`new_field`), ADD UNIQUE KEY`FK__test1` (`datade`), ADDCONSTRAINT`FK__test3`FOREIGN KEY (`fk`)REFERENCES`test3` (`id`);# New Tables-- new table `test3`CREATETABLE `test3` (`id`int(11)NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;# Disable Foreign Keys CheckSET FOREIGN_KEY_CHECKS=1;
Display Progress
For long running migrations, it is recommended to use the-p option to display the progress of the running migration.
Feel free to send your contributions as PR. Make sure you update/write new tests to support your contribution. Please follow PSR-2.
About
MySQL Schema Diff - Comparison / Migration Script Generation
Resources
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Packages0
Uh oh!
There was an error while loading.Please reload this page.
Languages
- PHP100.0%