SchemaCrawler

SchemaCrawler

Schemacrawler.png
Developer(s)	Sualeh Fatehi
Stable release	8.0 / February 3, 2010
Written in	Java
Operating system	Cross-platform
Development status	Active
License	LGPL/ GPL
Website	http://schemacrawler.sourceforge.net

SchemaCrawler is an open-source Java API that makes working with database metadata as easy as working with plain old Java objects.

SchemaCrawler is also a command-line tool to output your database schema and data in a readable form. The output is designed to be diff-ed with previous versions of your database schema.

SchemaCrawler API

Java programmers need to access database metadata

• in order to dynamically generate SQL statements
• when programmatically determining the capabilities of a given RDBMS
• when finding the names and types of tables and columns in the database

Programmers can obtain database metadata using JDBC, but with the raw JDBC API database metadata is returned as result sets, not Java objects. Also, programmers are still responsible for managing resources, mapping into object structures, and handling exceptions. This makes using the JDBC API very cumbersome when it comes to metadata. Furthermore, the JDBC API is not very consistent. For example, to find the type of a table, you would look at the TABLE_TYPE, which has a string value, but for procedures, PROCEDURE_TYPE is an integer. An another example, is the getCatalogs() call, which returns a result set with exactly one column, in contrast to getStringFunctions() which returns a string containing the list of function names, separated by commas.

SchemaCrawler attempts to solve some of these problems by providing an API that is consistent and usable. Database metadata is provided in the form of plain old Java objects (POJOs). Some examples of the consistency and usability of the SchemaCrawler API are that:

• Table is an object that has a collection of Column objects, without requiring you to make additional calls
• There is a getType() method, whether on a Column object, a Table object, or a Procedure object, which helps with consistency, and ease of use
• Lists are always Java lists - java.util.List
• You don't worry about database resources or exception handling
• You can use standard Java programming idioms - for example, you can access the Table object from a Column object using getParent()

SchemaCrawler is free and open-source API available under the LGPL license. SchemaCrawler is written in Java, making it operating system agnostic. Since it leverages JDBC, it is also database independent. It deliberately doesn't have any RDBMS-specific code. SchemaCrawler allows you to compare structures between two different database servers, or even two different database systems, from different vendors.

The sample code below demonstrates just how easy it is to use SchemaCrawler:

final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
final Database database = SchemaCrawlerUtility.getDatabase(connection, options);
for (final Schema schema: database.getSchemas())
{
  System.out.println(schema);
  for (final Table table: schema.getTables())
  {
    System.out.println("o--> " + table);
    for (final Column column: table.getColumns())
    {
      System.out.println("     o--> " + column);
    }
  }
}

SchemaCrawler Command Line

SchemaCrawler comes with a set of command line tools that allow database metadata to be output as plain text, XHTML, or comma-separated values(CSV). The XHTML output is a combination of valid XML (that can be manipulated by XML tools or XSLT), and HTML that can be viewed in a browser. All formats are designed to be easy to diff, or find differences with other schemas that may have been output in the same format.

SchemaCrawler has grep functionality that allows you to search for table and column names using regular expressions. SchemaCrawler is capable of creating entity-relationship diagrams in DOT Language, which GraphViz can convert into schema diagrams. SchemaCrawler has powerful scripting ability, using JavaScript. A live connection is provided to the JavaScript context to allow you to select from or even modify your database. Examples are provided for all of these with the download.

SchemaCrawler is integrated with, and allows you to write templates to generate SQL scripts or any other text output, using templating engines, such as Apache Velocity or FreeMarker. However, you will need to download Apache Velocity or FreeMarker separately, since these are not part of the SchemaCrawler download.

External links

• SchemaCrawler
• Java API Makes Database Metadata as Easily Accessible as POJOs