DBIx::SchemaChecksum - Generate and compare checksums of database schematas
version 1.006
my $sc = DBIx::SchemaChecksum->new( dbh => $dbh ); print $sc->checksum; # Or use the included script, scs.pl
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 gets schema information (tables, columns, primary keys, foreign keys and some more depending on your DBD) 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 is tested with PostgreSQL 8.3 to 9.1 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
Please take a look at the "scs.pl" in bin script included in this distribution.
You can find more information on the rational, usage & implementation in the slides for my talk at the Austrian Perl Workshop 2012, available here: http://domm.plix.at/talks/dbix_schemachecksum.html
my $sha1_hex = $self->checksum();
Gets the schemadump and runs it through Digest::SHA1, returning the current checksum.
my $schemadump = $self->schemadump;
Returns a string representation of the whole schema (as a Data::Dumper Dump).
Lazy Moose attribute.
Internal method to build schemadump. Keep out!
my $hashref = $self->_build_schemadump_schema( $schema );
This is the main entry point for checksum calculations per schema. Method-modifiy it if you need to alter the complete schema data structure before/after checksumming.
Returns a HashRef like:
{ tables => $hash_ref }
my $hashref = $self->_build_schemadump_tables( $schema );
Iterate through all tables in a schema, calling _build_schemadump_table for each table and collecting the results in a HashRef
my $hashref = $self->_build_schemadump_table( $schema, $table );
Get metadata on a table (columns, primary keys & foreign keys) via DBI introspection.
This is a good place to method-modify if you need some special processing for your database
Returns a hashref like
{ columns => $data, primary_keys => $data, foreign_keys => $data, }
my $hashref = $self->_build_schemadump_column( $schema, $table, $column, $raw_dbi_data );
Does some cleanup on the data returned by DBI.
my $update_info = $self->update_path
Lazy Moose attribute that returns the datastructure needed by apply_sql_update.
_build_update_path reads in all files ending in ".sql" in $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( $filename );
Returns a list of the preSHA1sum and postSHA1sum for the given file in sqlnippetdir.
sqlnippetdir
The file has to contain this info in SQL comments, eg:
-- preSHA1sum: 89049e457886a86886a4fdf1f905b69250a8236c -- postSHA1sum: d9a02517255045167053ea92dace728e1389f8ca alter table foo add column bar;
Database handle (DBH::db). Moose attribute
The database catalog searched for data. Not implemented by all DBs. See DBI::table_info
DBI::table_info
Default %.
%
Moose attribute
An Arrayref containg names of schematas to include in checksum calculation. See DBI::table_info
Path to the directory where the sql change files are stored.
Be verbose or not. Default: 0
Additional options for the specific database driver.
bin/scs.pl for a commandline frontend powered by MooseX::App
bin/scs.pl
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/).
Thomas Klausner <domm@cpan.org>
Maroš Kollár <maros@cpan.org>
Klaus Ita <koki@worstofall.com>
This software is copyright (c) 2012 by Thomas Klausner, Maroš Kollár, Klaus Ita.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
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.