Understanding pureQuery, Part 2: Assist class modelers with data modeling

Learn how the features of pureQuery can assist you as an object-oriented developer to define a set of database relational artifacts using traditional class modeling.

Azadeh Ahadian (azadeh@us.ibm.com), Eclipse Application Development Tools , IBM

Photo: Azadeh Ahadian 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.



17 January 2008

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.


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.

Figure 1. Conceptual view of the class model
Conceptual view of the class model

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.

Figure 2. Display the Java class in the IDE's Java editor
Display the Java class in the IDE's Java editor

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).

Figure 3. pureQuery's DDL generation option
pureQuery's DDL generation option

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

Figure 4. The DDL generated and displayed in the console view of the Eclipse IDE
The DDL generated and displayed in the console view of the Eclipse IDE

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).

Figure 5. Switch to the data perspective view
Switch to the data perspective view

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

Figure 6. Create a Data Developer Project
Create a Data Developer Project

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.

Figure 7. Choose to create a new script file
Choose to create a new script file
Figure 8. Create a new script file called BankingDDL
Create a new script file called BankingDDL

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.

Figure 9. Execute the DDL command through the Run SQL option
Execute the DDL command through the Run SQL option

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

Figure 10. Results of DDL execution are displayed in the Data Output view
Results of DDL execution are displayed in the Data Output view

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

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Java technology, SOA and web services
ArticleID=282307
ArticleTitle=Understanding pureQuery, Part 2: Assist class modelers with data modeling
publish-date=01172008