DBIx::SchemaChecksum - Generate and compare checksums of database schematas
my $sc = DBIx::SchemaChecksum->new( dsn => 'dbi:Pg:name=foo' ); print $sc->checksum;
When you're dealing with several instances of the same database (eg. developer, testing, stage, production), it is crucial to make sure that all databases use the same schema. This can be quite an hair-pulling experience, and this module should help you keep your hair (if you're already bald, it won't make your hair grow back, sorry...)
DBIx::SchemaChecksum connects to your database, gets schema information (tables, columns, primary keys, foreign keys) and generates a SHA1 digest. This digest can then be used to easily verify schema consistency across different databases.
Caveat: The same schema might produce different checksums on different database versions.
DBIx::SchemaChecksum works with PostgreSQL 8.3 and SQLite (but see below). I assume that thanks to the abstraction provided by the DBI it works with most databases. If you try DBIx::SchemaChecksum with different database systems, I'd love to hear some feedback...
DBI
DBD::SQLite doesn't really implement column_info, which is needed to generate the checksum. We use the monkey-patch included in http://rt.cpan.org/Public/Bug/Display.html?id=13631 to make it work
column_info
Please take a look at the scripts included in this distribution:
Calculates the checksum and prints it to STDOUT
Updates a schema based on the current checksum and SQL snippet files
Moose Object Builder which sets up the DB connection.
my $checksum = $sc->checksum;
Return the checksum (as a SHA1 digest)
my $schemadump = $self->schemadump;
Returns a string representation of the whole schema (as a Data::Dumper Dump).
$self->apply_sql_snippets( $starting_checksum );
Applies SQL snippets in the correct order to the DB. Checks if the checksum after applying the snippets is correct. If it isn't correct rolls back the last change (if your DB supports transactions...)
my $update_info = $self->build_update_path( '/path/to/sql/snippets' )
Builds the datastructure needed by apply_sql_update. build_update_path reads in all files ending in ".sql" in the directory passed in (or defaulting to $self->sqlsnippetdir). It builds something like a linked list of files, which are chained by their preSHA1sum and postSHA1sum.
build_update_path
$self->sqlsnippetdir
preSHA1sum
postSHA1sum
my ($pre, $post) = $self->get_checksums_from_snippet( $file );
Returns a list of the preSHA1sum and postSHA1sum for the given file.
The file has to contain this info in SQL comments, eg:
-- preSHA1sum: 89049e457886a86886a4fdf1f905b69250a8236c -- postSHA1sum: d9a02517255045167053ea92dace728e1389f8ca alter table foo add column bar;
All of this methods can also be set from the commandline. See MooseX::Getopts.
The database handle (DBH::db).
The dsn.
The user to use to connect to the DB.
The password to use to authenticate the user.
The database catalog searched for data. Not implemented by all DBs. See DBI::table_info
DBI::table_info
Default %.
%
An Arrayref containg names of schematas to include in checksum calculation. See DBI::table_info
What kind of tables to include in checksum calculation. See DBI::table_info
Default table.
table
Be verbose or not. Default: 0
Thomas Klausner, <domm at cpan.org>
<domm at cpan.org>
Please report any bugs or feature requests to bug-dbix-schemachecksum at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-SchemaChecksum. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.
bug-dbix-schemachecksum at rt.cpan.org
You can find documentation for this module with the perldoc command.
perldoc DBIx::SchemaChecksum
You can also look for information at:
RT: CPAN's request tracker
http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-SchemaChecksum
AnnoCPAN: Annotated CPAN documentation
http://annocpan.org/dist/DBIx-SchemaChecksum
CPAN Ratings
http://cpanratings.perl.org/d/DBIx-SchemaChecksum
Search CPAN
http://search.cpan.org/dist/DBIx-SchemaChecksum
Thanks to Klaus Ita and Armin Schreger for writing the core code. I just glued it together...
This module was written for revdev http://www.revdev.at, a nice litte software company run by Koki, Domm (http://search.cpan.org/~domm/) and Maros (http://search.cpan.org/~maros/).
Copyright 2008 Thomas Klausner, revdev.at, all rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
The full text of the license can be found in the LICENSE file included with this module.
To install DBIx::SchemaChecksum, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::SchemaChecksum
CPAN shell
perl -MCPAN -e shell install DBIx::SchemaChecksum
For more information on module installation, please visit the detailed CPAN module installation guide.