Data Tools Open Source Overview for Eclipse Database Developer

Before you read this paper, you should already be familiar with the features and plug-ins in the org.eclipse.datatools.connectivity.* (Connectivity sub-project), org.eclipse.datatools.modelbase.sql (Modelbase sub-project) and org.eclipse.datatools.sqltools.* (SQL Tools sub-project) open source contribution in DTP. At a minimum, you should go through the tutorial that is posted on the Eclipse Web site. This document is intends to provide an overview for a database tool developer that describes how to use and extend the Data Tools open source contribution.

Topics covered:

Background

Features of Data Tools

Supporting new database types

1) Creating a new database vendor

2) Extending the catalog loader

3) Extension point for SQL parser

4) Extension point for DDL code generation

This is the first step in our open source offering and we do encourage public contribution to new features through the extension of SQL Scrapbook, Database Explorer and Browse/extract/load Data.

Background

What are SQLModel link and Database definition model link?

SQLModel is a meta-model based on the SQL 99/2003 specification, which in turn defines all database elements for the industry standard. A Database definition model derives from SQLModel, which implements all detailed database model elements. For example: tables; columns; user defined data types; relationships and constraints; stored procedures and their detailed parameters.

The Database definition model is then used as the base to set up a database-specific vendor document (XMI file). This vendor document is used as the source to define details about the database. For example, it can specify whether a database supports stored procedures or views. In addition, all data type support is also recorded in the vendor document. See the section below called Creating a new database vendor for details on how to create a vendor document for a new database vendor.

Features of the Data Tools open source contribution

Supporting new database types

Using the Derby open source database as our example plug-in, we will show you step by step how to extend the open source framework.

Creating a new database vendor

Plug-ins used:

org.eclipse.datatools.modelbase.sql (SQLModel)
org.eclipse.datatools.modelbase.dbdefinition (Database Definition Model)

Here are two typical scenarios for creating a new database vendor:

Scenario one: Altering the SQLModel definition

Typically, you would not need to alter the definition for SQLModel. However, if you do need to add new model definition to the SQLModel, then complete the following steps:

1. Change the SQLModel (Rose UML model) file to add your database element.

2. Use the Eclipse framework to generate a new EMF model.

3. Generate code to create a new Database Definition Model.

4. Continue with step one of Scenario two.

Scenario two: Adding a new database that is SQL99 compliant

An example of this scenario is adding the Derby open source database. This example is not realistic, because the Database Definition Model for Derby has already been provided as part of the open source contribution, but you can follow these same steps to add other database vendors.

1. Generate a vendor document for the Derby database. Use the attached Java program to create a new database vendor (XMI) document. The Java class uses the database definition model (org.eclipse.datatools.modelbase.dbdefinition) as a base, then walks through and sets each single model element (for example: column; view; trigger; constraint; and sequence). See the code snippet below for details. When it is finished, it will create a XMI document as your database vendor documentation. You should modify the content of this Java class to match features that your target database supports. For example, you would probably want to set the schema support to a value of ‘false’, if you are working on a MySQL database.

Java Class file for generating Vendor Primitive document

2. Create a plug-in for the Derby database definition. You can use the org.eclipse.wst.rdb.dbdefinition.derby plug-in as an example. In the plugin.xml file, you will need to include two extension points, databaseDefinition and uri_mapping, as follows:

<?xml version="1.0" encoding="UTF-8"?>

<?eclipse version="3.0"?>

<plugin

id=" org.eclipse.datatools.connectivity.dbdefinition.derby"

name="%pluginName"

version="1.0.0"

provider-name="Eclipse">

<extension

point=" org.eclipse.datatools.connectivity.core.databaseDefinition">

<definition

version="10.0"

product="Derby">

</definition>

</extension>

<extension

point="org.eclipse.emf.ecore.uri_mapping">

<mapping

source="Derby_10.0.xmi"

target="runtime/vendors/Derby_10.0/Derby_10.0.xmi">

</mapping>

</extension>

</plugin>

Important notes:

· The version number and product name are required in the databaseDefinition extension point.

· The 2^nd extension point uri_mapping is dependant on the vendor document file, which is formed by concatenating the product name and version name with an underscore. For example, Derby_10.0.xmi. Therefore, you must save your vendor document in the runtime/vendors/Derby_10.0/Derby_10.0.xmi folder of the org.eclipse.datatools.connectivity.dbdefinition.derby.derby plug-in.

Extending the catalog loader

The catalog loader provides native JDBC catalog loading for all databases in the open source contribution. This means that a fixed set of database elements, such as schemas, tables, views, and relationships are loaded by querying the JDBC metadata. You can enhance the default catalog loader to process additional model elements that your target database supports. A common approach for this is to execute a specific query on the database server to retrieve preparatory information from the database system catalog.

To develop a catalog loader plug-in for Derby (org.eclipse.datatools.connectivity.derby), you would implement a catalog provider plug-in with following three required plug-ins:

org.eclipse.datatools.modelbase.sql

org.eclipse.datatools.modelbase.dbdefinition

org.eclipse.datatools.connectivity.core

Extension points:

org.eclipse.datatools.connectivity.core.catalog	for catalog loading
org.eclipse.datatools.connectivity.server.ui.ServerExplorerInitializationProvider	Contribute a new JDBC Driver for Derby

Your plugin.xml file should look like this:

<?xml version="1.0" encoding="UTF-8"?>

<?eclipse version="3.0"?>

<plugin

id=" org.eclipse.datatools.connectivity.derby"

name="Cloudscape Plug-in"

version="1.0.0"

provider-name="Eclipse"

class=" org.eclipse.datatools.connectivity.derby.DerbyPlugin">

</library>

</runtime>

</requires>

<extension

point=" org.eclipse.datatools.connectivity.core.catalog">

<catalog

version="10.0"

product="Derby"

provider=" org.eclipse.datatools.connectivity.derby.catalog.DerbyCatalogProvider">

</catalog>

</extension>

Finally, you can follow the JDBC provider implementation in the org.eclipse.datatools.connectivity.core plug-in (in the \src\org\eclipse\datatools\connectivity\internal\core\rte\jdbc package, all JDBC* classes such as JDBCDatabase, JDBCTable, JDBCView, and JDBCSchema) to implement the catalog loader for Derby. Or refer to the classes in org.eclipse.datatools.connectivity.derby plug-in, catalog package.

Extension point for the SQL parser

The extension point to implement a SQL Parser for reverse engineering or catalog loading from the DDL Script is defined in the org.eclipse.datatools.connectivity.core plug-in, in the schema folder ddlParser.exsd.

org.eclipse.datatools.connectivity.core.ddlParser

Sample plugin.xml:

……

<extension

point="org.eclipse.datatools.connectivity.core.ddlParser">

<parser

version="10.0"

product="Derby"

<!—You need to add this class to Derby plug-in -->

class="org.eclipse.datatools.connectivity.derby.ddl.DerbyDdlParser">

</parser>

</extension>

…….

The DerbyDdlEngineeringProvider class implements the following interface.

public interface DDLParser {

public Database[] parse(String fileName, IProgressMonitor progressMonitor);

}

Extension point for DDL Code generation

The extension point to implement DDL Code Generation (for forward engineering) is defined in the org.eclipse.datatools.connectivity.core plug-in, in the schema folder ddlGeneration.exsd.

org.eclipse.datatools.connectivity.core.DDLGeneration

Sample plugin.xml:

……

<extension

point="org.eclipse.datatools.connectitivy.core.ddlGeneration">

<generator

product="Derby"

version="10.0"

<!—You need to add this to Derby plug-in -->

class="org.eclipse.datatools.connectivity.derby.ddl.DerbyDdlGenerator">

</generator>

</extension>

…….

The DerbyDdlGenerator class implements the following interface.

public interface DDLGenerator {

public String[] generateDDL(SQLObject[] elements, IProgressMonitor progressMonitor);

public String[] createSQLObjects(SQLObject[] elements, boolean quoteIdentifiers, boolean

qualifyNames, IProgressMonitor progressMonitor);

public String[] dropSQLObjects(SQLObject[] elements, boolean quoteIdentifiers, boolean qualifyNames, IProgressMonitor progressMonitor);

public EngineeringOption[] getOptions();

public EngineeringOptionCategory[] getOptionCategories();

}