databene

 
  • Increase font size
  • Default font size
  • Decrease font size

Mad For DB

Mad for DB stands for "Migration ADvisor For DataBases" or Mad4DB in short. It analyzes the differences between two database schemas, automatically finds out which changes require individual migration effort and advises migrators which concrete datasets need fixing. The main benefit of the tool is to quickly filter out database changes which are not relevant for the task of data migration, automatically verify existing data if it matches newconstraints and provide a summary sorted by severity of the change.

Installation

See Installing Mad For DB for installation instructions.

Execution

Before executing Mad For DB, create configuration files for the database schemas to be compared as described in the DB Sanity documentation, e.g. db1 and db2.

Then you can compare the two schemas by calling Mad For DB: Open a text console and type (for the databases db1 and db2 mentioned above):

mad4db db1 db2

on Windows systems or

mad4db.sh db1 db2

on Unix-like systems.

Mad For DB will then load the database structure information of both systems and compare them. While the comparation only takes a few seconds, the actual import from the database may take some minutes, on very large Oracle systems, even one to two hours. However, when Mad For DB has finished, it creates n HTML report and opens it in the systems default browser.

Evaluation

It consists of three main parts:

  1. A valuated report, ordered by the severity of the change
  2. A Diff view which groups tables into four categories: Changed, deleted, created and unchanged tables
  3. A DB Sanity report in which existing data is examined, if it can be migrated without preprocessing

Severity Report

The severity report lists changes ordered by their severity, explaining which risk results from a change and what user action is necessary:

 

Diff View

The diff view provides an element comparison view over two levels of detail, first a schema diff with a table summary:

For each table, a table view is available which lists all details of the table. For changed tables, a detail diff view is available:

 

DB Sanity Report

For new constraints on existing data structures (or constraints made more restrictive), Mad For DB uses DB Sanity to check if the existing data matches the new constraint:

 

CSV Summary Report

A summary of the Mad For DB report is provided inta-separated CSV format:

 

Command LineOptions

short optionlong option
description
-x <arg>
--excludetables <arg>
exclude tables that match a regular expression
-c
--cache
cache database meta data
-v
--version
print out Mad4DB's version number and exit
-l <arg>
--limit <arg>
limit the number of reported issue datasets
-a <arg>
--appversion <arg>
generate DB Sanity Checks with this version no
-i <arg>
--ignore <arg>
-i i: ignore indexes
-i s: ignore sequences
-i is: ignore indexes and sequences

 

Change Type Categories

Category
Semantic
Consequences
AUGMENTATION
A required element has been added
technical and semantical migration necessary
EXTENSION
An optional element has been added
no technical migration necessary, but usually a semantic migration/generation
DELETION
Element has been deleted
verify manually to make sure it is not a misinterpreted change
PASSED
The existing data matches the new constraint
no worries
REORGANIZATION
Element has been reorganized
no worries
EASE
Element has been simplified
no worries

 

Recognized Change types

AUGMENTATION
created table with mandatory reference from remaining table
created primary key column
created mandatory unique column
created mandatory foreign key column
created mandatory column

EXTENSION
created table
created optional unique column
created optional foreign key column
created optional column
created mandatory foreign key column with default value
created mandatory column with default value

DELETION
deleted table
deleted column
deleted sequence

RESTRICTION
created primary key constraint
created unique constraint
created not null constraint
created foreign key constraint
created check constraint
changed check constraint (MAD4DB is not yet able to understand the severity of the change)
changed column type, size and/or precision
added columns to foreign key constraint
removed columns from primary key constraint
removed columns from unique key constraint

REORGANIZATION
created index
renamed index
changed column order
deleted index
index made unique (check by associated unique constraint)
index no more unique
changed column type, size and/or precision
added columns to unique constraint
changed default value
created sequence
sequence settings changed
deleted sequence

EASE
changed column type, size and/or precision
dropped primary key constraint
dropped unique constraint
dropped not null constraint
dropped foreign key constraint
dropped check constraint
added columns to primary key constraint
added columns to unique constraint
removed columns from foreign key constraint
 

Changes not (yet) recognized

renamed table
renamed sequence