How to implement a custom database Metadata provider

From Wiki

Introduction

In order to develop applications that require to publish or manipulate content stored into a relational database, a JDBC connection to one or more relational database system must be available.

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

The databases supported by WebRatio are:

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

• All tables of 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 provides a Metadata provider used to retrieve the metadata of database. The provider is used by the Refresh command in order to show the tables, columns, views and procedures information in the Outline and in the properties view.

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

This article will explain step by step how to implement the metadata provider for FrontBase. The process to implement other metadata providers is the same.

Adding a new plug-in

It is necessary to extend WebRatio creating a new plug-in that will be included in your WebRatio configuration. Follow this 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 as name com.webratio.commons.db.frontbase then click the Next button.

• Deselect the two checkboxes in the Plug-in Options and click Finish

• It will be opened the Plug-in Manifest Editor where you can edit the properties of the new Plug-in.
• Click on the Dependencies tab and click on 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 click Ok in the opened dialog.
• Save the changes.

• Click on the Extensions tab and click 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 this 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, specially for case sensitive databases.
• Save changes.

How to implement the FBJMetaDataProvider class

After you have created and configured the new database type, it is necessary 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".

How to use the metadata provider

The metadata provider is now ready to be used in your WebRatio configuration. In order to use the new created plug-in you have to run a new WebRatio 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 click the Run button.
• A new WebRatio will be opened.
• When Webratio opens, it is asked to reactivate the WebRatio because you are initializing a new Workspace. To retrieve the activation information, in the WebRatio 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 follows the activation process.

In order to test the new metadata provider download the Acme Front Base Project and import it in your workspace using the Import WebRatio 5 Project wizard. In your administration tool for Front Base create a new database named acme, with user acme and password acme and start it. In WebRatio, configure the database as follows:

• 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 succeed under the database node appears a Metadata node that is void because the database doesn't contains tables yet. If the refresh fails control if the configuration is correct and refresh again. If the refresh fails each time it is necessary to debug 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 click ok. When the script has been executed, the database is refreshed. Now you can expand the metadata node and see all the metadata of your database.

You are ready to generate and run the application.

How to debug the metadata provider

The metadata retrievement is executed by an external process that loads the driver class. Therefore it is not possible to use the Eclipse debug. It is necessary to 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 by WebRatio.

How to export the plug-in

The plug-in can be exported and integrated in 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 it will be created a Plugins directory 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.