Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

A Ruby script that dumps all data from all accessible tables in a database/schema to the output stream in a format suitable for diffing.

branch: master

Fetching latest commit…

Octocat-spinner-32-eaf2f5

Cannot retrieve the latest commit at this time

Octocat-spinner-32 LICENSE
Octocat-spinner-32 README.rdoc
Octocat-spinner-32 schemadump
README.rdoc

Schema Dump

Dumps all data from all accessible tables in a database/schema to the output stream in a format suitable for diffing.

Installation

Install Git and Ruby.

Install RBI and Trollop:

gem install dbi
gem install trollop

Clone this project.

cd ~
git clone http://github.com/garysweaver/schema-dump.git

Then add the following to your .bash_profile, or whatever you want:

#schema-dump
export PATH=$PATH:~/schema-dump

Restart Terminal.app or whatever, and then test by doing:

schemadump -h

Usage

       schemadump [options]

where [options] are:

       --driver-url, -r <s>:   DBI driver URL. e.g.
                               DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD
             --user, -u <s>:   database username. e.g. jdoe
         --password, -p <s>:   database password. e.g. secret
--table-names-query, -t <s>:   SQL query to use to get full listing of
                               tables. e.g. "select TABLE_NAME from
                               USER_TABLES"
                --quiet, -q:   quiet. does not output progress indicator
                --debug, -d:   outputs debugging information
              --version, -v:   Print version and exit
                 --help, -h:   Show this message

Examples

MySQL

schemadump -r DBI:Mysql:TESTDB:localhost -u jdoe -p secret

Oracle

schemadump -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret

Sample Output

$ schemadump -r DBI:OCI8://db.acme.org:1234/ACMETEST -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'

Diff Schemas

By using schemadump, directing output to file, then sorting, you can diff the two sorted dumps.

Example:

$ schemadump -r DBI:OCI8://db.acme.org:1234/ACMETEST -u jdoe -p secret > test
$ sort test > test-sorted
$ schemadump -r DBI:OCI8://db.acme.org:3456/ACMEPROD -u jdoe -p secret > prod
$ sort prod > prod-sorted
$ diff test-sorted prod-sorted

By sorting via the sort command, CLOBs, etc. can be sorted after they've been converted to string and outputted. You could do this by getting the column names and doing order by on all the columns in the script, but I think this is just as easy, and may be faster.

License

Copyright © 2011 Gary S. Weaver, released under the MIT license.

Something went wrong with that request. Please try again.