Comparing Database Structures

Overview

(Deutschsprachige Anleitung in liesmich.txt)

• Djembe helps in keeping the structures of several database for development, testing, integration and production synchronized
  
• Database independent due to the usage of JDBC drivers
  
• Compare databases offline
  
• Export results to Excel
  
• Structures are saved as XML, thus allowing any further processing
  
• usable via command line or with its own GUI
  

Environment:

• Java 6 or newer
  
• JDBC Driver (drivers for Oracle, MS SQL Server, Postgres, MySQL, Derby/JavaDB and Firebird were tested successfully)
  

Verified for Windows XP SP3 and openSUSE 11.

The main two components are:

• The scanner analyzes database structures and saves them as xml files.
  
• The comparer reads previously stored xml scans and writes the comparison result as plain text log files, respectively presenting the result as GUI table.
  

Both components are configured via plain text property files:

• file suffix .djs for scanners
  
• file suffix .djc for comparisons
  

Currently, the following database objects are processed:

• Tables with columns and privileges
  
• Views with columns and privileges
  
• Columns with type, length, and nullable flag
  
• Sequences/generators with privileges
  
• Oracle synonyms
  
• Trigger (without source code so far)
  
• Primary- and foreign keys
  
• Indexes
  
• Types
  

(Privilege, type, trigger and other vendor specific scanners are not part of the
free standard edition)

Usage

For installation, just copy the application folder to the desired place and add the required JDBC drivers (to the "lib" folder usually).
Please don't forget to copy your license key "djembe-license.txt" into the root folder of the application.

java -jar djembe.jar 
-ui | 
-scan [scan-properties] | 
-compare [compare-properties]

-ui
Launches the graphical user interface of Djembe.

-scan
Reads a database and stores its structure as xml file.

-compare
Compares two databases.

Examples:
java -jar djembe.jar -scan scan-my-db.djs
java -jar djembe.jar -compare compare-my-dbs.djc

Under Windows, you can use "djembe.exe" or "djembe.bat", under linux "djembe.sh" for running the graphical user interface as well.


Privilege markup in scans and comparison results:
[grantor] -> [grantee]: [privilege]


Column Aliases:
They allow to look at different data types as synonyms causing no difference alert.
A typical example would be to equalize Oracle's specific "varchar2" and the standard "varchar".
You can set up as many mappings as you like, each one comma separated:

[mapping-1], [mapping-2], ..., [mapping-n]

Data types and flags are separated by spaces:

[Leading data type] [-is] [-id] [Alias-1] ... [Alias-n]

Flags:
-id ignore digits
-is ignore size

Example:
"int4 -id -is number numeric, varchar2 varchar"

Fields of type "int4" are put together with "number" and "numeric".
Both size and number of digits are ignored.
Additionally, data types "varchar2" and "varchar" are taken as identical.

Useful mappings:
varchar2 varchar,
int4 -id -is number numeric,
boolean -id -is char,
date -id -is timestamp,
blob -is bytea,
bpchar char,
numeric number

Scanner-Properties

url =             (database URL to analyze)
alias =           (short database name, free to choose)
driver =          (JDBC driver class)
driverUrl =       (JDBC driver URL)
user =            (database user, might need administrator rights !)
schema =          (database schema)
filter =          (name filter for tables, views, etc.)
scanner.classes = (comma separated list of scanner classes to run)
replace =         (automatically replaces written xml result file, true oder false)
exportName =      (name pattern of the written xml file)

Folder "templates" contains several sample property files for various databases like Oracle, Postgres or MySql.

Comparer-Properties

comparer.log.charset =           (charset of the generated log file)
comparer.ignoreIndexNames =      (ignore index names, true oder false)
comparer.log.name =              (name pattern of the generated log file)
comparer.ignoreForeignKeyNames = (ignore foreign key names, true oder false)
comparer.ignorePrivileges =      (ignore privileges, true oder false)
comparer.ignorePrimaryKeyNames = (ignore primary key names, true oder false)
comparer.ignoreColumnTypes =     (ignore data types, true oder false)
comparer.ignoreSchemas =         (ignore schemas, true oder false)
comparer.database.1 =            (first, previously scanned database as xml)
comparer.database.2 =            (second, previously scanned database as xml)
comparer.log.replace =           (automatically overwrite the generated log file, true oder false)
comparer.column.aliases =        (mappings for column aliases)
comparer.log.separator =         (separator char of the generated log file)

Available Scanner

default.TableScanner
default.ViewScanner
royalty.TypeScanner

oracle.SequenceScanner
oracle.SynonymScanner
oracle.TriggerScanner

postgres.SequenceScanner
postgres.DomainScanner
postgres.TriggerScanner

firebird.GeneratorScanner
firebird.TriggerScanner

mysql.TriggerScanner

derby.SynonymScanner

File Suffixes

.djs = scanner configuration
.djc = comparison configuration
.djd = database dump

Get it here:

Downloads

Soon to come:
Professional version with more scanners for privileges, types, triggers and other vendor specific objects.