Introduction

The first article in this series "pureQuery: IBM's new paradigm for writing Java database applications" (developerWorks, August 2007), discusses the basics of the technology and how database access and manipulation are performed using an objectization process based on Java™ code generation. In this article, you'll learn about one of the specific features of pureQuery that can provide great assistance to an object-oriented developer in defining a set of database relational artifacts using traditional class modeling. While pureQuery is not a modeling tool, and should not (and in fact cannot) be compared with such powerful enterprise data modeling and integration design tools as IBM Rational Data Architect (RDA), you can think of it as poor man's approach to performing simple physical data modeling through the Eclipse IDE, through the creation of Java classes.

Data modeling vs. class modeling

Data modeling is the process of creating abstract artifacts that describe how data is represented, stored, and used. In the context of relational information management development environments, data modeling in effect is the process of exploring and organizing data-centric structures that can be used for defining high-level conceptual and physical data models. These structures are then implemented in a relational database management system.

It sometimes is argued that data modeling is different from class modeling, based on the fact that data modeling strictly focuses on data, as opposed to class models, where both the behavior and data aspects of the underlying domain are explored. However, from the standpoint of an object-oriented approach, data modeling is very similar to class modeling.

In data modeling, you identify entity types (for example, tables) that conceptually are similar to object-orientation's concept of classes representing a collection of similar objects (noting that classes have both data and behavior defined through operations, whereas entity types just have data). The data modeling process is then followed by assigning data entity attributes (such as columns, which are a database's equivalent of class attributes and their respective data types) to the identified table(s). This is again similar to class modeling where you identify classes and assign attributes and operations to each class.

In terms of operations, and in the context of object orientation, you can think of stored procedures as conceptually similar to global methods that are implemented by the underlying database. These stored procedures may not (and in most cases will not) act on data contained in one single table. This would imply that stored procedures are not similar to class methods since they are not part of tables. However, they can be viewed and modeled as a part of a master class (namely the database itself) that encompasses all other classes (such as tables).

In short, conceptually the objectives in a data modeling development paradigm is to design data tables and their assigned data columns and types, and to define the relationships between the tables, with each table belonging to a designated internal database schema. Furthermore, in addition to defining and organizing the data, data modeling does implicitly or explicitly impose constraints on the data placed within the underlying structure(s). Once the modeling is completed, the implementation would mean the actual creation of the entities, and their relationships in a given RDBMS.

So how does IBM's pureQuery technology come into play?

IBM offers a state-of-the-art enterprise data modeling and integration design tool known as IBM Rational Data Architect (RDA). RDA is the complete solution for simplification of data modeling and integration design, enabling architects to discover, model, visualize, and relate diverse and distributed data assets. pureQuery in no way should be mistaken with what RDA offers, but simply as a utility that can quickly provide assistance to Eclipse object-oriented (OO) developers for the creation of simple database artifacts mirroring their well-defined Java classes.

Note: pureQuery is part of IBM’s Data Studio product family that can be downloaded free of charge (see the Resources section). You need to purchase Data Studio pureQuery Runtime in order to deploy applications written with pureQuery.

pureQuery's object model approach for encapsulating the relational attributes and operations provides a unique reverse-engineering framework that can be utilized to significantly provide assistance in performing physical data modeling. This approach is based on designing a set of Java classes in the Eclipse IDE that define a conceptual relational network through the object model they represent. Based on the given conceptual underlying object model, with each class attribute to represent their counterpart entity types (such as table and column names and their respective data types and primary keys), pureQuery auto-generates the required DDL statements for the creation of relational entities. It does this through a simple intuitive point-and-click UI approach. A developer, in turn, can further fine-tune (or add to) the generated DDL statements. This would be to further introduce or refine the relational relationships (for example, foreign keys and other constraints) in order to rapidly carry out the actual implementation and execution of the desired data-oriented structures in the underlying RDBMS.

Back to top

A case study: Class modeling transformed into physical data modeling

Using a step-by-step walk-through, this section illustrates how pureQuery can significantly assist a developer with an object-oriented point of view to conceptually perform data modeling. It includes the option to auto-execute to carry out the creation of the actual underlying desired physical data entities.

It is assumed that you have read part 1 of this series, and thus has a basic understanding of pureQuery technology.

Before you start the walk-through, make sure you have installed the following:

• DB2® for Linux®, UNIX®, and Windows® Version 9.1 and the SAMPLE database that comes with the installation (see the Resources section)
• IBM Data Studio Version 9.5 (see the Resources section)

Turn class modeling into relational artifacts

In this example, you first define a simple class model (see Figure 1), and then illustrate how pureQuery, through the auto-generation of respective DDL commands for each class, can be utilized to assist developers in the implementation and execution of physical data modeling, as expressed earlier.

Step 1. Define your class model

In this example, three Java classes have been defined, as depicted in Figure 2. Note the usage of the @Id annotation in the defined classes. This particular annotation indicates to the pureQuery's DDL code generation system to properly generate the CREATE INDEX statement for defining the primary key. In the future releases of pureQuery, there will be other annotations that will further improve the auto DDL generation process to auto-generate different types of DDL statements.

Step 2. Have pureQuery auto-generate the DDL commands for each of the classes

Select the Java class and display it in the IDE's Java editor. Simply bring up the pop-up context menu by right-clicking anywhere in the class editor area. Then select the pureQuery Assist menu option, and from there the Generate DDL sub-menu option (see Figure 3).

Once selected, the DDL is properly generated and displayed in the console view of the Eclipse IDE, as shown in Figure 4.

Step 3. Save the generated DDL in a file (such as a Notepad file) by cutting and pasting it from the console view.

Note: You need to make minor changes to the generated DDLs to reflect the proper data types (for example, a Java string variable that is defined as a column of type VARCHAR needs to be modified to VARCHAR(255).

Step 4. Repeat Step 2 for all other classes and append the newly generated DDL commands to the previously saved ones.

The complete DDL commands are listed as noted below:

CREATE TABLE ACCOUNT (
ACCOUNTID VARCHAR(255) NOT NULL,
BALANCE DECIMAL(9 , 2) NOT NULL,
BEGINBALANCE DECIMAL(9 , 2) NOT NULL,
BEGINBALANCETIMESTAMP VARCHAR(255) NOT NULL,
CREDITLINE DECIMAL(9 , 2) NOT NULL,
CUSTOMERIDS VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(255) NOT NULL,
TYPE VARCHAR(255) NOT NULL
)
DATA CAPTURE NONE ;
ALTER TABLE ACCOUNT ADD CONSTRAINT ACCOUNTPK PRIMARY KEY (ACCOUNTID);

CREATE TABLE BANKCUSTOMER (
CITY VARCHAR(255) NOT NULL,
CUSTOMERID VARCHAR(255) NOT NULL,
EMAIL VARCHAR(255) NOT NULL,
FIRSTNAME VARCHAR(255) NOT NULL,
LASTNAME VARCHAR(255) NOT NULL,
MIDDLEINITIAL VARCHAR(255) NOT NULL,
PHONE VARCHAR(255) NOT NULL,
STATE VARCHAR(255) NOT NULL,
STREET VARCHAR(255) NOT NULL,
ZIP VARCHAR(255) NOT NULL
)
DATA CAPTURE NONE ;
ALTER TABLE BANKCUSTOMER ADD CONSTRAINT BANKCUSTOMERPK PRIMARY KEY (CUSTOMERID);

CREATE TABLE ACCOUNTCUSTOMERREF (
ACCOUNTID VARCHAR(255) NOT NULL,
CUSTOMERID VARCHAR(255) NOT NULL
)
DATA CAPTURE NONE ;

DDL enhancement and execution

In this section, the file containing the generated DDLs is further edited to add the required foreign key statements. This is in order to define the tables relationships:

ALTER TABLE ACCOUNTCUSTOMERREF ADD CONSTRAINT ACCOUNTFK
FOREIGN KEY (ACCOUNTID) REFERENCES ACCOUNT ON DELETE RESTRICT;

ALTER TABLE ACCOUNTCUSTOMERREF ADD CONSTRAINT CUSTOMERFK
FOREIGN KEY (CUSTOMERID) REFERENCES BANKCUSTOMER ON DELETE RESTRICT;

The next step is executing all the DDL statements. In order to do this, you can utilize Data Workbench's RUN SQL option, as noted in the steps below.

Step 5. Create a Data Developer Project

Switch to Eclipse IDE's data perspective view (see Figure 5).

Then create a Data Development Project and call it DataDevProject, as you see in Figure 6.

Step 6. Create a new SQL script file

Navigate to SQL Script > New > SQL or XQuery Script. A dialog window is displayed in which you can specify a file name (such as BankingDDL), as shown in Figures 7 and 8.

Step 7. Copy and paste the DDL commands to the BankingDDL file

From your notepad where you have saved all the generated DDLs, copy and paste into the BankingDDL script file. Then, through the context menu, select the RUN SQL option (see Figure 9), which results in the execution of the DDL command and hence the creation of the relational tables that directly reflect the Java classes.

As the DDL commands are executed, the results are displayed in the Data Output view of the IDE, as shown in Figure 10.

Back to top

Conclusion

IBM's pureQuery is a unique technology that can offer significant assistance and increase productivity to developers whose domain expertise is in traditional object-orientation and yet want to do physical data modeling. This technology, through a simple UI paradigm, provides the means for the auto-generation of base DDL script(s) for further enhancements to transform an object model hierarchy into RDBMS physical data artifacts.

Resources

Learn

• IBM Data Studio pureQuery Runtime: Learn more about this high-performance Java data access platform..
  
  
• "pureQuery: IBM's new paradigm for writing Java database applications" (IBM developerWorks, August 2007): Read part 1 of this series to get a foundation in pureQuery.
  
  
• "Diretriz: Physical Data Modeling" (OpenUP, April 2007): Get a foundation in data modeling.
  
  
• "Modeling for Knowledge Discovery": Read about how the static behavior of systems can be modeled in a manner that encourages the reuse and extension of models.
  
  
• Data Modeling 101: Find a discussion of data modeling in the context of agile database development.
  
  
• IBM Rational Data Architect: Learn more about this enterprise data modeling and integration design tool.
  
  
• developerWorks resource page for IBM Data Studio: Read articles and tutorials and connect to other resources to expand your Data Studio skills.
  
  
• Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download IBM Data Studio.
  
  
• Download a free trial version of DB2 Enterprise 9.
  
  
• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Azadeh Ahadian is a software developer in the Information Management tool organization at IBM’s Silicon Valley Lab in San Jose, Calif. She has a Master's degree with more than eight years of industry experience in software application and tools development. Azadeh has been a key contributor and a member of the pureQuery design time team.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• A case study: Class modeling transformed into physical data modeling
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers