sql-dump-schema-diff

This is a command line tool written in Python. It displays all the differences between two database schemas. The schema are provided as SQL dump.

1. ExampleInputdb1.sql: sql dump of a database with a single table: 'person' CREATE TABLE `person` (
`last name` varchar(50) NOT NULL,
`date_of_birth` varchar(45) NOT NULL,
PRIMARY KEY (`last name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;db2.sql: sql dump of the same database after adding a new table 'car' and modified fields in the 'person' table. CREATE TABLE `car` (
`color` varchar(50) NOT NULL,
`number_plate` varchar(50) NOT NULL,
`brand` varchar(50) NOT NULL,
PRIMARY KEY (`number_plate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `person` (
`last name` varchar(50) NOT NULL,
`date_of_birth` datetime NOT NULL,
`first_name` varchar(45) NOT NULL,
PRIMARY KEY (`last name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Commandpython compdb.py db1.sql db2.sqlOutput% FIELD: person.date_of_birth
`date_of_birth` varchar(45) NOT NULL
=>
`date_of_birth` datetime NOT NULL
+ FIELD: person.`first_name` varchar(45) NOT NULL
+ TABLE: car
3 difference(s).2. Notation%: difference +: new field or table -: missing field or table 3. LimitationsCurrently works only with MySQL dumps Obviously this tool cannot detect when the name of a field or a table has changed! 4. MotivationThis script was originally written to help me keep track of the evolution of Django models (hence the Python implementation). Before upgrading a staging server, I would compare the SQL dump from my development database (generated with mysqldump -d or with python manage.py sql) with the dump from my staging server.

Note that more sophisticated tools have been developed for this purpose, search for 'django schema evolution' on any search engine to find them.

This tool was developed as part of a UML to Django conversion project.

5. FeedbackYour comments and contributions are welcome.

Tags

  mysql schema-evolution python compare evolution diff database django

Share