SchemaCrawler

SchemaCrawler is a tool and an API dealing with SQL database schemas information.

../_images/diagram_example.jpg

Features

The tool provides:

  • extracted information about database schemas in various formats (e.g. text, json, csv, html5)
  • documentation generation with ER diagrams generated by graphviz,
  • lint like quality insurance,
  • grep/diff like support.

The API allows to access to SQL databases in an homogeneous way.

Installation

To install SchemaCrawler:

  • Create a directory that will contain the software (e.g. %SCRIBESTOOLS%\SchemaCrawler).
  • Download schemacrawler-12.06.03-main.zip web.
  • Extract the content of the archive in the target directory.
  • As a result you will 3 sub directories _ivy, _schemacrawler, examples.
  • Add the directory to the system PATH

The sc scripts provided (“sc” stands for SchemaCrawler) assume that this command will run in the _schemacrawler directory. In order to remove this constraints replaces the content of _schemacrawler\sc.cmd by:

@SET DIR=%~dp0
@java -classpath %DIR%/lib/*;%SC% schemacrawler.Main %*

On unix replace the content of _schemacrawler\sc.sh by:

#!/bin/sh
SC=`dirname $0`
java -cp $(echo $SC/lib/*.jar | tr ' ' ':') schemacrawler.Main $*

SchemaCrawler is written in java but supports scripting with different languages (). In order to use Python for Scripting the following commands should be executed (assuming that the directory is %SCRIBESTOOLS%\SchemaCrawler):

cd %SCRIBESTOOLS%\SchemaCrawler\_ivy
download.cmd python     # use download.sh on unix

Launching SchemaCrawler

The simplest test to installation is to type the following:

cd %SCRIBESTOOLS%\SchemaCrawler\_schemacrawler    #  to be adapted if necessary
schemacrawler --version
schemacrawler --help

This should display the version of SchemaCrawler and then the help of the command line.

To go further the directory examples contains many examples of usage. A HSQLDB database server is provided for testing purposes. To launch this example database server open a shell window and type the following commands:

cd %SCRIBESTOOLS%\SchemaCrawler\examples     # directory to be adapted if necessary
StartDatabase.cmd                           # .sh on unix

At that level the test server should be running in this window (and the window color may have changed).

Open another shell and try:

cd %SCRIBESTOOLS%\SchemaCrawler\_schemacrawler     # to be adapted
sc.cmd -server=hsqldb -database=schemacrawler -password= -infolevel=detail -command=schema

schemacrawler -server=hsqldb -database=schemacrawler -password= -infolevel=detail -command=schema

If you have a sqlite database you can also try:

schemacrawler -server sqlite -database <path to the sqlite file>\db.sqlite3 -infolevel=maximum -password=  -command schema

Documentation

The documentation of the command line interface is available via the command:

schemacrawler -help

The detail of the API is documented through SchemaCrawler javadocs.

Examples

The directory examples of the installation contains examples of use for the command line interface, for scripting or for the API.