perlancar

About the series: perlancar's 2014 Advent Calendar: Introduction to a selection of 24 modules which I published in 2014. Table of contents.

Frankly, I was a bit surprised when searching for an existing CPAN module for this task and found none. There is DBIx::Compare, but it is for database contents and not structure (it can compare two tables' field names though). Hence DBIx::Diff::Struct. An example:

-- db1:
CREATE TABLE t1 (i INT);
CREATE TABLE t2 (a INT, i1 INT, f1 FLOAT NOT NULL, d1 DECIMAL(10,3), s1 VARCHAR(10));

-- db2:

CREATE TABLE t2 (b INT, i1 FLOAT, f1 FLOAT NULL, d1 DECIMAL(12,3), s1 VARCHAR(20));

CREATE TABLE t3 (i INT);

# code

use Data::Dump;

use DBI;

use DBIx::Diff::Struct qw(diff_db_struct);

my $dbh1 = DBI->connect(...);

my $dbh2 = DBI->connect(...);

dd(diff_db_struct($dbh1, $dbh2));

# result

{

    added_tables    => ['main.t3'],

    deleted_tables  => ['main.t1'],

    modified_tables => {

        'main.t2' => {

            added_columns    => ['b'],

            deleted_columns  => ['a'],

            modified_columns => {

                f1 => {

                    old_nullable => 0,

                    new_nullable => 1,

                },

                i1 => {

                    old_type => 'INT',

                    new_type => 'FLOAT',

                },

            },

        },

    },

}