Friday, August 26, 2011

Dump Database or Schema to a Diffable Text File with Schema Dump

Just wrote a new tool called Schema Dump that dumps a database or schema data to a text file that you can then use the sort and diff (compare) commands with to do a diff of changes between two schemas or databases. Pretty simple script using Ruby DBI and trollop.

Some examples of schemadump command-line:

$ schemadump -r DBI:OCI8:// -u jdoe -p secret

SAMPLE_TABLE_A: '123', 'Joe'
SAMPLE_TABLE_B: '123', '234'
SAMPLE_TABLE_B: '123', '235'
SAMPLE_TABLE_C: '234', 'Kia', 'Sorento', '345'
SAMPLE_TABLE_C: '235', 'Kia', 'Sportage', '346'
SAMPLE_TABLE_D: '345', 'Red'
SAMPLE_TABLE_D: '346', 'Green'
$ schemadump -r DBI:OCI8:// -u jdoe -p secret > test
$ sort test > test-sorted
$ schemadump -r DBI:OCI8:// -u jdoe -p secret > prod
$ sort prod > prod-sorted
$ diff test-sorted prod-sorted

No comments: