Database metadata model API

Description

Issue 133989.

Multiple components in the IDE require access to a representation of the database metadata:

• SQL code completion
• Database Explorer
• Code completion in Hibernate mapping files
• Code completion of database metadata in the JPA support (removed currently, but might be resurrected when JPA 2.0 support is implemented)
• JPA entity classes from database wizard
• Visual SQL editor
• Visual web

These components today either do their own database metadata management or use dbschema. Both approaches affect the memory footprint and speed of the IDE, since database metadata are retrieved and cached multiple times. Moreover, dbschema has longstanding issues such as unreliable code, exception swallowing and too verbose serialization format.

This is a proposal to introduce a model for database metadata usable by all components across the IDE.

What's wrong with DatabaseMetaData?

There are a couple of reasons not to use java.sql.DatabaseMetaData directly:

• No object-oriented access. DatabaseMetaData is returns its information in the form of a ResultSet, but it is more confortable for clients to get a hierarchical structure of objects (given a table object, it can be easily queried for columns, primary key, foreign keys, etc.). Moreover, the ResultSet is more difficult to process than the result of a method like Schema.getTables(). The lifecycle of the ResultSet need to be controlled by the client, unlike the structures returned by the model (these are cached by the model).

• No unified way to extract metadata that are not supported by DatabaseMetaData. A separate way to obtain this information would have to be defined, possibly leading to a more complex API (or extending the semantics of the DatabaseMetaData methods.

• The model would probably need to be an interface extending DatabaseMetaData. Both JDBC 3.0 and 4.0 made incompatible changes to DatabaseMetaData. Chances are it will be modified again in subsequent versions. This makes it difficult to maintain the implementation of this interface which must build on two versions of the JDK.

Goals

Public API In the long run, the model should provide a public API usable by any IDE component. Therefore backward compatibility and correctness are strong requirements. However, because of the short implementation time, it is not a goal for the API to have a stability level higher than friend in NetBeans 6.5.

Thread safety The model should be thread-safe.

Single model per connection There should be a single model instance per a given Database Explorer connection. There should be a way to obtain a model for the database connection, and that model should be cached by the infrastructure.

Lazy access The model should retrieve the metadata in a lazy manner. For each call into the model, only the needed metadata are retrieved. For example, when a client requests the list of tables, only the table names are retrieved.

Stub model for unit tests It should be possible to create a stub/mock implementation of the model (one that doesn't necessarily delegate to DatabaseConnection or java.sql.Connection) for use in unit tests.

Non-goals

No write support This is a read-only model. Client changes to the model and/or modification of the database structure are out of scope.

No listener support This is not an all-purpose model, at least not for the NetBeans 6.5 timeframe. No change events should be fired from the model, even when the model does detect a change in the metadata.

Use cases

The main use case is to retrieve a model for a given connection and query it for database objects.

java.sql.Connection conn = ... // usually retrieved from the DB Explorer.
MetadataModel model = MetadataModel.forConnection(conn);
List<Catalog> catalogs = model.getCatalogs();
// Similarly call Catalog.getSchemas(), Schema.getTables(), etc.

After 6.5, when the model API becomes public, there should be a simpler way to retrieve a model for a DB Explorer connection:

DatabaseConnection dbconn = ...
MetadataModel model = dbconn.getMetadataModel();

This is not doable if the model API is friend, because the DB Explorer API (which is public) would be linking to a non-public API.

Further use cases, design, etc. will be added later, when a prototype is created.