Using and Extending the Data Tools Platform
Brian Fitzpatrick (Sybase) Linda Chan (Actuate)
Brian Payton (IBM)
Welcome!
Goals for this tutorial:
Get a working knowledge of DTP Tooling
Get a glimpse into how to enable or extend database support for a particular vendor
Get familiar with some DTP APIs
Use ODA to extend DTP for a non-database data source
See how to expand the SQL Query Model and Parser for a particular DB
And finally see how DTP is being leveraged in other Eclipse projects
Before we begin…
What do you hope to get out of the talk today?
What are your goals? What do you know already about DTP?
Beginning DTP
What is the Data Tools Platform (DTP)?
Important Concepts in the Tooling
Connection Profiles & Driver Definitions
Available Perspectives for DTP
Database Development
Database Debug
Basic Components
Data Source Explorer (DSE)
SQL Scrapbook
SQL Results View
Creating a Database Connection in DTP
In the Data Source Explorer, right-click on “Database Connections” and select “New…”
Specify your database type, click Next
If no driver available, click the New Driver Definition button
Select a driver template, set your jar paths, click OK
Customize database properties, click Finish
Expanding and Exploring the Connected Profile
Once you connect to a profile, it starts to load the model a level at a time
So you can expand and see catalogs, schemas, tables, procedures, and so on – to represent what your database (and the DTP implementation for that database) supports
Using the SQL Scrapbook to Execute Queries
Right-click on a database Connection Profile and select “Open SQL Scrapbook”
Type your query, select it, and select “Execute Selected Text”
How to Use the SQL Query Builder from the SQL Scrapbook
To create more complicated queries, you can use the SQL Query Builder (SQB), which simplifies query creation by allowing you to click, drag, and drop to customize the SQL
To open the SQB, right-click and select “Edit in SQL Query Builder”
Right-click where it says
“To add a table…” and select
“Add Table…”
Select one or more tables to use in the query and click OK
Then you can select columns, specify conditions, and so on
Executing a Query from the SQB
Once you’ve customized your query in the SQB, right-click in the SQL text area at the top and select “Run SQL” to show the
results
Interacting with Stored Procedures and UDFs
In SQL, you can define and call stored procedures in user- defined functions if your database supports them. DTP allows you to call them as well.
In a Derby database, you can call the SQLTABLES procedure:
call SYSIBM.SQLTABLES(null, 'APP', null, null, null);
Stored Procedures and UDFs continued…
You can also create and then use user-defined functions…
In a Derby database, try this:
create function getSystemProperty(name varchar(128)) returns varchar(128) language java external name
'java.lang.System.getProperty' parameter style java no sql;
Creating Tables and Modifying Content
In addition to simply running SQL, you can use DTP tools to create tables and view/modify their data
In the Data Source Explorer, connect to and drill down into a database connection profile. On the “Tables” folder in the tree, right-click and select “New Table…”
The New Table wizard creates DDL that you can then execute to create the table.
Creating Tables and Modifying Content, continued
Execute the table DDL by right-clicking and selecting “Execute All” – this will run the SQL and create the table.
Then right-click on the “Tables” node in the tree, right-click and select “Refresh” to repopulate the table list.
Once you have a table, you can edit its content by right-clicking and selecting “Data->Edit”, which opens a tabular window for editing table data that you can use for data entry
When you’re done, right-click in the editor and select Save from the popup menu to persist your changes
Creating Tables and Modifying Content, continued
Another way of loading data is by CSV file. To load a table from a CSV file, right-click on a table and select Data->Load…
Specify the input file, the delimiter, and the string delimiter, and click Finish.
Tooling Exercise…
Set up a new Derby connection profile and connect to a local database
Connect to the connection profile
Create a new Product table with three columns – product_id (varchar), name (varchar), inventory (number)
Create a new Orders table with three columns – order_id (varchar), date (date/time), product_id (varchar), number_ordered (number)
Import data from the Products.csv and Orders.csv files into your new tables
In the SQL Scrapbook, create a query (in the text editor or the SQB) to correlate information from the Product and Orders tables to get a
readable report of what products were ordered on a given date (product name, date, number ordered)
Extending DTP
Now that you have a bit of knowledge about what DTP can do for you… How do you leverage and extend it?
A few possible avenues…
Use DTP components in your own applications and simply leverage what’s already there
Extend DTP into new Data Sources and Databases (“enabling”
new databases, adding custom items to the tree, adding custom SQL syntax to what DTP already knows, etc.)
Take advantage of DTP APIs to utilize core DTP functionality under the covers
“Enabling” a New Database
Rather than spend a long time on this topic, I’ll direct you to a series of blog posts I wrote while I worked through “enabling” SQLite for DTP Enablement.
Not all databases will require all of this, but it’s not difficult to extend the existing frameworks
Driver Framework: http://fitzdtp.blogspot.com/2008/06/dtp-driver- framework-repost.html
Custom Driver Template: http://fitzdtp.blogspot.com/2008/06/how-do-you- add-your-own-custom-driver.html
Adding a Custom Catalog Loader: http://fitzdtp.blogspot.com/2008/08/dtp- sqlite-support-continued-on-to.html
Creating the SQLite Connection Profile (minus UI):
http://fitzdtp.blogspot.com/2008/08/creating-actual-sqlite-connection.html
Creating the SQLite Connection Profile UI Bits:
http://fitzdtp.blogspot.com/2008/08/creating-sqlite-connection-profile-ui.html
Leveraging DTP APIs
Though we have many cool tools in DTP, you may find that you don’t need or want to use them. In these cases, you may still be able to take advantage of DTP APIs.
There are too many APIs to really talk about in the time allowed, so I’m just going to hit upon a few of the high-level ones
In this section, we’re going to use DTP APIs to create a
connection profile and write a utility class that takes a file, loads it into memory, and loads a table from the file
Consider that APIs could also be used to map/replicate data from one database to another via a combination of JDBC and DTP
APIs for Creating Connection Profiles…
There are two types of connection profiles…
persisted
and transient.
Accessing Existing Connection Profiles (Persisted)
Access to existing connection profiles (persisted or transient) is done using the ProfileManager class
(org.eclipse.datatools.connectivity.ProfileManager)
If you know the name of your connection profile, you can find it pretty easily
IConnectionProfile myProfile =
ProfileManager.getInstance().getProfileByName(“MyProfile”);
Creating a Transient Connection Profile
In the second case, where you may not want to use the whole connection profile UI framework but want to leverage other components, you use the Transient API
These are the typical JDBC properties you think of…
String profileUserName = "";
String profilePassword = "";
String profileURL = "jdbc:derby:C:\\DerbyDatabases\\MyDB;create=true";
String profileDriverClass = "org.apache.derby.jdbc.EmbeddedDriver";
String profileJarList = "C:\\Derby10.2.2\\db-derby-10.2.2.0-bin\\lib\\derby.jar";
Then there’s the DTP-specific properties you need…
String providerID =
"org.eclipse.datatools.connectivity.db.derby.embedded.connectionProfile";
String vendor = "Derby";
String version = "10.1";
Transient Profile, continued
So once you have your properties, you can do this:
Properties baseProperties = new Properties();
baseProperties.setProperty( IDriverMgmtConstants.PROP_DEFN_JARLIST, jarList );
baseProperties.setProperty(IJDBCConnectionProfileConstants.DRIVER_CLASS_PROP_ID, driverClass);
baseProperties.setProperty(IJDBCConnectionProfileConstants.URL_PROP_ID, driverURL);
baseProperties.setProperty(IJDBCConnectionProfileConstants.USERNAME_PROP_ID, userName);
baseProperties.setProperty(IJDBCConnectionProfileConstants.PASSWORD_PROP_ID, password);
baseProperties.setProperty(IJDBCConnectionProfileConstants.DATABASE_VENDOR_PROP_ID, vendor);
baseProperties.setProperty(IJDBCConnectionProfileConstants.DATABASE_VERSION_PROP_ID, version);
baseProperties.setProperty( IJDBCConnectionProfileConstants.SAVE_PASSWORD_PROP_ID, String.valueOf( true ) );
ProfileManager pm = ProfileManager.getInstance();
IConnectionProfile icp = pm.createTransientProfile(providerID, baseProperties);
Transient Profile, continued…
So the upside to the Transient profile is that you don’t have to pre-define it in the UI
The downside is that you have some additional properties to figure out.
This is *new* API for Galileo. There are other changes to help with getting the correct vendor and version in Galileo so you can hand it a JDBC connection you managed yourself, it gets the properties it needs, and then returns the correct vendor, and version.
For now, you can use the provider ID of the Generic JDBC profile (org.eclipse.datatools.connectivity.db.generic.connectionProfile)
Using your Connection Profile reference
Now that you have an IConnectionProfile instance, what can you do with it?
If it’s not connected, you can connect to it:
IConnectionProfile profile =
ProfileManager.getInstance().getProfileByName("myprofile");
IStatus status = profile.connect();
if (status.equals(IStatus.OK)) { // success
} else {
// failure :(
if (status.getException() != null) {
status.getException().printStackTrace();
} }
Using your Connection Profile reference…
So now that you have a connected profile, you can get the JDBC connection from the ProfileConnectionManager:
public java.sql.Connection getJavaConnectionForProfile (IConnectionProfile profile) {
IConnection connection = ProfileConnectionManager.
getProfileConnectionManagerInstance().getConnection(profile,
"java.sql.Connection");
if (connection != null) {
return (java.sql.Connection) connection.getRawConnection();
}
return null;
}
Using the Connection…
From there, you can do anything you might have done with any other JDBC connection:
try{
Statement st = connection.createStatement();
String statement = “select * from mytable”;
int val = st.executeUpdate(statement);
}
catch (SQLException s){
// exception }
Other good API bits…
You can also get the SQL model from a database connection.
First, you have to get the ConnectionInfo class, which acts as a bridge between the JDBC connection and the SQL models
IManagedConnection managedConnection = ((IConnectionProfile)profile).getManagedConnection(
"org.eclipse.datatools.connectivity.sqm.core.connection.ConnectionInfo");
if (managedConnection != null) { try {
ConnectionInfo connectionInfo = (ConnectionInfo) managedConnection.getConnection().getRawConnection();
if (connectionInfo != null) {
Database database = connectionInfo.getSharedDatabase();
// do something with the database reference…
}
} catch (Exception e) {
e.printStackTrace();
} }
This is how to get the SQL Model’s root Database node for your connection profile
Using the Database reference…
Once you have the Database reference, you can drill into your database and get the abstracted SQL structure, which you can then use to develop more flexible SQL calls for a variety of
reasons…
Imagine being able to connect to two databases and providing a tool that allows you to map data between disparate tables…
Imagine writing tooling or a set of APIs that could be used to inject data via DTP’s abstracted models
Imagine abstracting a data source class for a given table or set of tables that would hide SQL complexity and allow a developer to more easily add/remove/edit data via simple calls
The sky’s the limit!
API Exercise…
Using the base plug-in provided, in the action’s run method, dive into the connection profile and print a few things to the console for the Product table in the App schema of our Derby database…
MyDatabase.getCatalogs() returns an EList of catalogs. Derby has a dummy Catalog object, so you’ll have to get it and then go
deeper ( (Catalog)MyDatabase.getCatalogs().get(0) )
MyCatalog.getSchemas() returns an EList of schemas you can iterate through until you find APP
Once you find the Schema, MySchema.getTables() returns an EList of tables you can iterate through until you find Product
MyTable.getColumns() returns an EList of columns…
And so on… simply work down the hierarchy and show a list of the columns in the Product table
In Summary… Tools + API = Flexibility
DTP’s tooling provides useful database and data source
functionality that’s also extendable and reusable in your own code
And DTP’s APIs allow you to go deeper than the tooling to do many cool things with JDBC, the SQL model, and your own tools/APIs
More DTP Information
Main DataTools site at http://www.eclipse.org/datatools
Newsgroup - news://news.eclipse.org/eclipse.dtp
Mailing list – http://dev.eclipse.org/mailman/listinfo/dtp-dev
Wiki - http://wiki.eclipse.org/index.php/Data_Tools_Platform_Project
Bugzilla for bug or enhancement requests
Feel free to ask questions!
DTP Open Data Access (ODA) Framework
Extend DTP for Non Relational Database Data Sources
Linda Chan – Actuate® Corporation
Topics
What is Open Data Access1 (ODA)?
Overview – ODA Framework
Enabling non-RDBMS data source with an ODA Data Provider
Demo/Exercise: creating ODA provider plug-ins
Open Data Access Framework
Enables data access to any data source
• Provides an abstraction for accessing heterogeneous data sources
• Highly scalable data retrieval and easy end-user experience
• Built using familiar Eclipse extension points Extends & customizes Applications Systems
• Applications can provide custom data driver and query builder for accessing their data
Provides proven framework
• BIRT’s built-in ODA data connectors
• Commercial products’ ODA data connectors
Overview – Open Data Access Architecture
ODA Data Driver
ODA Query Builder
Application Client Tools BIRT Report Designer
Data Sources
Application Server
ODA Runtime Interfaces ODA Design
Interfaces
Data Access & Query Definition
Run-Time Data Access Design-Time Query Builder
Overview – Open Data Access Framework
Run-time
Public API defines a set of Java™ interfaces that allow heterogeneous data to be accessed in a generic way
Design-time
Allows data source-specific user interface to be embedded in any ODA-compliant consumer application
ODA Consumer Helper
Built-in support for any ODA-compliant consumer application to consume data from any ODA data provider
ODA Run-time Framework
Stable run-time API since DTP 0.9
Integrates with DTP Connection Profile and JDBC™ Databases framework
Implementation wraps data source-specific APIs
Run-time plug-in implements
extension points
org.eclipse.datatools.connectivity.oda.dataSource
org.eclipse.datatools.connectivity.connectionProfile
ODA run-time API interfaces
org.eclipse.datatools.connectivity.oda package
ODA Run-time API Interfaces
Defines the primary run-time operations of an ODA data provider to access and retrieve data from a data source
Java interfaces
JDBC-like, extended to support additional capabilities of non- RDBMS data sources
Emphasis on scalable data retrieval
Main run-time operations
Data Source Connection – IConnection
Establishes a live connection to any type of data source
Obtains provider capabilities for each type of data set queries
Creates one or multiple concurrent data set-specific queries
ODA Run-time API Interfaces
Main run-time operations (cont’d)
Data Set Query – IQuery, IAdvancedQuery
Prepares and executes a data set-specific query text
command, e.g. XPath, MDX, SQL, Stored Procedure calls
Handles one or more sets of data rows, i.e. result sets, retrieved by a single data set query
Query Parameters – IParameterMetaData, IParameterRowSet
Provides run-time metadata of parameters specified in a prepared query
Handles scalar and complex input/output parameters
Result Sets – IResultSet, IResultSetMetaData
Fetches tabular data rows
Allows sequential or concurrent access to multiple result sets
ODA Design-time Framework
Eclipse Modeling Framework (EMF) model-based design-time interfaces
communicate connection properties, query and parameter definitions to an ODA consumer application
Integrates with DTP Data Source Explorer view
ODA Design-time Framework
ODA Design Session model
ODA Designer UI Plug-in
Implements
extension points
org.eclipse.datatools.connectivity.connectionProfile
org.eclipse.ui.propertyPages
org.eclipse.datatools.connectivity.oda.design.ui.dataSource
org.eclipse.datatools.connectivity.ui.connectionProfileImage (optional)
customizes ODA Designer UI pages
org.eclipse.datatools.connectivity.oda.design.ui.wizards package
Communicates its Data Source and Data Set design definitions in an ODA Design Session model
ODA Design Session model
Model based on Eclipse Modeling Framework (EMF)
org.eclipse.datatools.connectivity.oda.design package
Allows customized data source and query builders to design the slice of data to access at run-time
Communicates connection information, query and parameter definitions to an ODA consumer application
Transient Objects
root element: org.eclipse datatools.connectivity.oda.design.OdaDesignSession
ODA design-time consumer application, e.g. BIRT Report Designer, initiates an ODA design session
Consumes a data provider’s UI page contributions
Adapts an edited data access design to host-specific design components
Provides persistent services for editing designs
Build a Custom ODA Data Provider
From scratch
PDE New Plug-in Project Wizard
Demo / Exercise
Extends an existing ODA provider
driverBridge extension point
org.eclipse.datatools.connectivity.oda.consumer.driverBridge
Build a Custom ODA Data Provider – from Scratch
PDE New Plug-in Project Wizard
Demo / Exercise
Extends an Existing ODA Data Provider
Modify/enhance behavior of an existing ODA data provider
org.eclipse.datatools.connectivity.oda.consumer.driverBridge extension point
Ideal for minor enhancements
preserves existing ODA extension ID
Supports “chained” bridges
DriverBridge Extension Point
ODA Consumer Application (e.g. BIRT)
ODA Runtime Interfaces
oda.consumer.helper
Driver Bridge
driverTypeÆ driver class name
Driver Bridge
bridgeIdÆ oda.dataSource extension id
Enhance an Existing ODA Data Provider
DriverBridge extension implements
Extension Point
org.eclipse.datatools.connectivity.oda.consumer.driverBridge
Sample driverBridge extension element
<bridge
driverType="org.eclipse.birt.report.data.oda.jdbc.OdaJdbcDri ver"
bridgeId="org.eclipse.birt.report.data.testjdbc">
</bridge>
Bridge driver is handed its underlying driver instance
IDriver.setAppContext( Map )
Key: OdaDriver.ODA_BRIDGED_DRIVER
Value: underlying driver instance
May use own API between Bridge and underlying Driver instances
ODA Data Providers
Out-of-the-Box ODA Data Providers
CSV data file
org.eclipse.datatools.connectivity.oda.flatfile* plug-ins
XML data
org.eclipse.datatools.enablement.oda.xml* plug-ins
Web Services
org.eclipse.datatools.enablement.oda.ws* plug-ins
JDBC Drivers – SQL Query Builder & SQL Textual Editor
org.eclipse.birt.report.data.oda.jdbc* plug-ins
DTP Incubation Project
ECore ODA Data Provider (Bugzilla 132958)
org.eclipse.datatools.enablement.oda.ecore* plug-ins
Separate incubator download package
Welcomes contribution
What is New in ODA – DTP 1.7 Release
ODA API Enhancements
Java Object data type
Availability of Data Set Property and Input Parameter Values prior to Query Preparation
Cancel a Query Execution
Connection-level Locale Setting
Consumer Resource Identifiers
http://wiki.eclipse.org/Galileo_DTP_ODA_API_Changes_Spec
Dynamic Query Result Specification API – Experimental API
Filtering
Result Projection
Row Ordering
Resources
ODA Overview document
link on DTP Connectivity home page
http://wiki.eclipse.org/Connectivity
Developers' Guide for Connectivity Frameworks
DTP Help Contents – Javadoc™ and Extension Points’ Schema
Presentation slides
https://www.eclipsecon.org/submissions/2009/view_talk.php?id=686
Open Data Access Framework
Discussions, Feedbacks, Q&As
Using and Extending the Data Tools Platform SQL Query Model and Parser
Brian Payton (IBM)
Agenda
Introduction and background
Characteristics
What is it good for?
Tour of the Query Model and Parser components
Query Model
Query Parser
Parser Factory
Post-parse Processors
SQL Source Writer
Query Model Helpers
Extending the Query Model and Parser
Introduction and background
The Data Studio Query Model and Parser is a system for
representing, analyzing, manipulating, and generating SQL query statements in client-side database applications.
Originally created for the SQL Assist component of IBM® DB2 Control Center, later used in IBM® WebSphere® Studio
Application Developer, IBM Rational® Application Developer, and IBM Data Studio Developer.
Donated by IBM to the Eclipse Data Tool Platform project 2006 and used in the DTP SQL Query Builder component.
Characteristics
Deep coverage of SQL DML statements (Query, Insert, Update, Delete)
But DML only!
Extensible, componentized system
Extension points to register extensions for different databases
Parser grammar inheritance
Model is based on EMF technology
Parser is based on LPG (LALR Parser Generator) technology
SourceForge project, but available in Eclipse through Orbit
Think of the model as an SQL AST (Abstract Syntax Tree) on steriods!
What is it good for?
Provides underlying support for the SQL Query Builder
Makes “round-trip” editing possible
Can do SQL Syntax validation outside of the database
Enables query analysis
Tour of the Query Model and Parser components
Query Model
Represent the essential structure of a query statement
Query Parser
Validate SQL source syntax and generate the query model
Parser Factory
Set of methods used by the parser to generate the model
Post-parse Processors
Validate semantics and modify the model after parsing
SQL Source Writer
Generate SQL source from the model
Model Helpers
Query Model
Represents the essential syntactic structure of a query statement as an object model
Covers DML statements
SELECT, INSERT, UPDATE, DELETE
Implemented using EMF (Eclipse Modeling Facility)
Derived from object model diagrams created using Rational Rose
Extends and complements the SQL Model
SQL Model is the foundation for the Data Studio tool set
SQL Model models the database catalog while the Query Model
Query Model - plugins
org.eclipse.datatools.modelbase.sql.query
Base query model
org.eclipse.datatools.modelbase.sql.xml.query
SQL/XML extension
Depends on:
org.eclipse.datatools.modelbase.sql
Query Model – EMF Model
Java code is generated directly from the Rose model using EMF (Eclipse Modeling Facility)
Each Rose diagram class becomes a Java class
Model classes extend EMF EObject class
EMF handles details of getters, setters, collections, notifications
Each Rose diagram class becomes two EMF classes: an interface class and an implementation (Impl) class
Generated code can be customized, and changes won't be lost when regenerated
Query Parser
Parses a SQL DML statement and generates an instance of the Query Model
Implemented using LPG (LALR Parser Generator) from IBM Research
Parser is generated from BNF-like grammar files
Like the Query Model, is actually an extendable set of parsers:
base SQL, SQL/XML
Base SQL query grammar derived from ISO SQL 2003
Parser Factory
Set of methods used by the parser to create a Query Model instance
Each parser plug-in has a parser factory class
Used in the “action” clauses of the LPG grammar file rules
In turn the parser factory methods call model factory methods to create individual model elements
Model factory code is generated by EMF, parser factory methods are hand-coded
Post-parse Processors
Query Parser provides a mechanism to allow clients to modify a generated Query Model after the Parser Factory creates it but before it is returned as a parse result
“Post-parse processors” are attached to the parser, register what model elements they are interested in, and after the parse get a chance to modify the model
Two default post-parse processors are provided:
DataTypeResolver
Tries to determine datatypes of value expressions in the model using available information
TableReferenceResolver
Replaces place-holder table references in generated model with
SQL Source Writer
Generates SQL source from a Query Model instance
Together with the Query Parser, provides “round-trip” capability from SQL to model and back
Like the model and parser, is configurable and extendable
Walks the model tree and uses reflection to find the right source writer method for each model element
Query Model Helpers
Query Model Helpers are a set of classes and methods that provide useful utility functions for the Query Model
DataTypeHelper
JoinHelper
StatementHelper
TableHelper
ValueExpressionHelper
Used both for analyzing and manipulating the model. Examples:
Find information, such as all table references or all effective result columns
Modify the model, such as remove a table and all column references for that table from a query
Extending the Query Model and Parser
Process:
Analyze the grammar feature you want to add. Is it specific to one database type, or common to several?
Modify the appropriate to add the new feature
Getting this right is not easy! Modeling is hard work.
Regenerate the EMF model
Add Parser Factory methods for the new model elements
Modify the appropriate parser grammar to cover the new syntax
Update the SQL Source Writer to generate SQL for the new syntax
Update the Query Model Helper classes as needed
Update the JUnit tests for the parser
Make sure it all works!
Fun with the interactive tester
Along with JUnit tests, the test plugins for the Query Parsers include an interactive testing tool
In org.eclipse.datatools.sqltools.parsers.sql, it's SQLQueryParserInteractiveTest
A command-line tool, works with the Eclipse console view
Type in a SQL statement, and it displays the generated model elements, then the SQL generated back out from the model
DTP Adopters
Eclipse Projects
Business Intelligence and Reporting Tools (BIRT)
SQL Query Builder
Database Enablement
Connection Profile Management
Open Data Access
http://www.eclipse.org/birt
Web Tools Project (WTP) – EJB or Dali JPA Tools development
http://www.eclipse.org/webtools
Other Adopters
http://www.eclipse.org/datatools/community.php
Legal Notices
Actuate is a registered trademark of Actuate Corporation and/or its affiliates in the U.S. and certain other countries.
IBM, Rational, Rational Rose, and WebSphere are registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Sybase and Adaptive Server are registered trademarks of Sybase, Inc. or its subsidiaries.
Java and all Java-based trademarks are trademarks of Sun
Microsystems, Inc. in the United States, other countries, or both.
Other company, product, or service names may be trademarks or service marks of others.
