How to implement a custom database Metadata provider

by Michela Frigerio
14,764 views Published on Oct 24, 2011
Applies to: All versions
Table of contents

Introduction

In order to develop Applications that publish or manipulate content stored in a relational database, a JDBC connection to one or more relational database systems must be available.

Applications generated with WebRatio leverage the popular Hibernate O/R mapping framework, and therefore WebRatio supports various databases and their different versions.

The databases supported by WebRatio are:

DATABASE VERSION
Apache Derby 10.x
DB2

AS400, OS/390 – Type 2 App Driver, Type 2 App Driver, Type 3 Net Driver, Universal Driver

Informix  
Microsoft SQL Server 2000, 2005, 2008
MySQL InnoDB, MyISAM
ODBC  
Oracle 8i, 9i, 10g, 11g
PostgreSQL 7.x, 8.x, 9.x
Sybase Adaptive Server 15.x
Teradata  

Each relational database system has its own mechanisms for storing metadata. Examples of relational-database metadata include:

  • All tables in the database, their names, sizes, and number of rows in each table.
  • All columns of the database, what tables they are used in, and the type of data stored in each column.

For each database, WebRatio has a Metadata provider used to retrieve the metadata from the database. The provider is used by the "Refresh" command and shows the tables, columns, views and procedure information in the Outline View and in the Properties View.

 

WebRatio does not support all the databases that are supported by Hibernate Therefore we offer the ability to integrate a new type of database into your applications and to implement a metadata provider for it.

This article explains how to implement the metadata provider for FrontBase. The process for implementing other metadata providers is the same.

Adding a new plug-in

It is necessary to extend WebRatio by creating a new plug-in that will be included in your WebRatio configuration. Follow these steps in order to create the new plug-in:

  • Create a new Plug-in Project using the File > New > Project > Plug-in Project command.

  • Set the project name as "com.webratio.commons.db.frontbase" then press "Next".

  • Deselect the two checkboxes in the Plug-in Options and press "Finish".

Then press the "Yes" button, when prompted, to open associated perspective.

  • This will open the Plug-in Manifest Editor where you can edit the properties of the new Plug-in.
  • Click on the "Dependencies" tab and press the "Add.." button to specify the list of plug-ins required for the operation of this plug-in.
  • Choose "com.webratio.commons.db" and "com.webratio.commons.libs" and then press "OK" in the opened dialog.
  • Save the changes.

You will need to add the class "FBJMetaDataProvider" on which the plug-in is based.

  1. Add a package in the folder com.webratio.commons.db.frontbase/src by right-clicking New > Package and naming it "com.webratio.commons.db.frontbase".
  2. Add the Java Class FBJMetaDataProvider in this folder with the properties shown in the following image:

Back at the Plug-in perspective, follow these steps:

  • Click on the "Extensions" tab and press on "Add.." botton and select "com.webratio.commons.db.dbTypes" extension.
  • Right-click on the new added extension and choose the New > dbType option.

  • Set these properties in order to configure the new db type:
    • Name = FrontBase: the name of the new database type.
    • driverClass = com.frontbase.jdbc.FBJDriver: the class of JDBC driver.
    • sampleURL = jdbc:FrontBase://host/database: the sample for the URL to connect to the database.
    • metaDataProviderClass = com.webratio.commons.db.frontbase.FBJMetaDataProvider. In order to create the class, click on the hyperlink and configure the class as shown in the image below.
    • sqlDialectClass = org.hibernate.dialect.FrontBaseDialect: the class for the SQL dialect used by Hibernate.
    • driverFileNames = frontbasejdbc.jar: the name of the package where the driver class is stored.
    • caseStyle = lower. The style used by WebRatio in order to execute query. Pay attention in setting it, especially for case-sensitive databases.
  • Save the changes.

How to implement the FBJMetaDataProvider class

After you have created and configured the new database type, you need to implement the FBJMetaDataProvider class. The class should appear as follows.

package com.webratio.commons.db.frontbase;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import com.webratio.commons.db.metadata.AbstractMetaDataProvider;
import com.webratio.commons.db.metadata.IColumn;
import com.webratio.commons.db.metadata.IMetaDataProvider;
import com.webratio.commons.db.metadata.ITable;

public class FBJMetaDataProvider extends AbstractMetaDataProvider 
                                 implements IMetaDataProvider {

	@Override
	protected boolean isAutoIncrement(ResultSet resultSet, ITable table, 
                                          IColumn column) throws SQLException {
		return false;
	}

	@Override
	protected boolean isAutoIncrement(ResultSetMetaData resultSet, 
                                         int index, ITable table, 
                                         IColumn column) throws SQLException {
		return false;
	}
}

Let's complete the class with the constructor and the method to retrieve the database schemas.

    public FBJMetaDataProvider() {
        super();
    }

    protected List readSchemaNames() {
        List schemaNames = super.readSchemaNames();
        for (Iterator i = schemaNames.iterator(); i.hasNext();) {
            String name = (String) i.next();
            if ((name != null) && (name.equals("INFORMATION_SCHEMA"))) {
                i.remove();
            }
        }
        return schemaNames;
    }

The readSchemaNames() method is able to read, from the database metadata, all the schema names. Note that we want to exclude from the list the system schema "INFORMATION_SCHEMA".

Also add java.util.* to the import libraries.

How to use the metadata provider

The metadata provider is now ready to be used in your WebRatio configuration. To use the new created plug-in, you have to run a new WebRatio session including the new plug-in.

  • Open the dialog to lanch a new configuration using the Run > Run.. command.
  • Right-click on Eclipse Application and choose the New option. Then press the Run button.
  • A new WebRatio will be opened.
  • When Webratio opens, you are asked to reactivate the WebRatio because you are initializing a new Workspace. To retrieve the activation information, in the WebRatio session where you implement the plug-in, open the Licence Information dialog using the Help > License Information. Copy Name and Serial in the activation dialog and follow the activation process.

To test the new metadata provider, use and modify the Acme Project. In your administration tool for Front Base create a new database named acme, with user acme and password acme

  • Name = Acme database
  • Type = FrontBase: The new created database type appears in the list of supported types thanks to the new plug-in
  • URL = jdbc:FrontBase://localhost/acme
  • Username = acme
  • Password = acme
  • Default Schema = ACME

Now you can load the metadata. Right-click on the Database node in the Outline and choose the Refresh action. Since we do not include the frontbase.jar in the available drivers, the refresh will not succeed because it is not able to find the correct driver to connect to the database. Download, extract, and copy the frontbasejdbc.jar file under the drivers folder of your WebRatio installation following the instructions in the shown dialog.

Refresh again and if the refresh succeeds, under the database node a Metadata node appears that is void because the database doesn't yet contain tables yet. If the refresh fails control if the configuration is correct and refresh again. If the refresh fails each time, you will need to test the metadata provider code (see next section).

To prepare and populate the database, execute the script PrepareDB that you can find in the DBScripts folder of the example project. Right click on the database node and choose the Execute SQL script... action. Choose the DBScripts/PrepareDB.sql file and press OK. When the script has been executed, the database is refreshed. Now you can expand the metadata node and view all the metadata of your database.

 

You are ready to generate and run the application.

How to test the metadata provider

The metadata retrieval is executed by an external process that loads the driver class. Therefore it is not possible to use the Eclipse dedicated view. Instead, use the System.out.println()method in the provider class. The strings printed are visualized in the console when you refresh the database. The advantage of executing an external process is that it is not necessary to relaunch the configuration each time you modify the source code, except when required to do so by WebRatio.

How to export the plug-in

The plug-in can be exported and integrated into your installation. Follow the instructions below to export the plug-in:

  • Right-click on the plug-in and choose the Export action. In the dialog choose Plug-in Development > Deployable plug-ins and fragments and click Next.
  • Select the destination directory and click Finish. In the destination directory a Pluginsdirectory will be created that contains the JAR file of the created plug-in.
  • Copy the JAR file into the WebRatio/plugins folder of your WebRatio installation.
  • Restart WebRatio: the plug-in is now available without launching another configuration.