1. Introduction

This document shows you how to model databases in PowerDesigner for use with OneWebSQL™ PD2Java. PowerDesigner is a database modeling tool. It helps you define and maintain the structure of your database. The primary advantages of using a modeling tool are:

Combining the PowerDesigner modeling tool with OneWebSQL™ PD2Java makes your code easy to maintain. Integration of OneWebSQL™ PD2Java with PowerDesigner enables you to keep your code and the database changes in synch, so that any database structure changes are updated immediately in the code.

2. Using PowerDesigner

2.1. Data models

PowerDesigner uses three types of data models:

OneWebSQL™ PD2Java takes PDM files as input and generates Java classes as output. It is important to ensure that complete information is provided in the PDM. Complete PDM information ensures that the corresponding Java classes are correctly generated by OneWebSQL™ PD2Java.

Tip: Start with CDM files
We find it useful to start by putting as much information as possible in a Conceptual Data Model, and then later generate the Physical Data Model from the CDM. PowerDesigner copies all the necessary OneWebSQL™ PD2Java settings between the models.

Some information, such as views and sequences, can only be stored in the Physical Data Model (PDM).

2.2. Recommended PowerDesigner settings

2.2.1. Use one "Data item" in many relations

  1. In the Conceptual Data Model, select Model Options...
  2. Under Data Item, uncheck both the Unique code and Allow reuse fields.

Now you are able to re-use the same attribute name in different tables. For example, if you want to have an id column in several tables, you need to uncheck both of the Data Item settings. Otherwise, every column would have to have a different, unique name.

2.2.2. Foreign key names

  1. Select Tools > Generate Physical Data Model...
  2. Go to the Detail tab.
  3. For the FK column name template, type %PARENT%_%COLUMN% in the provided field.

This setting is a convenient way to generate meaningful method names in your code. For example, a method which returns a book identifier would be called getBookId().

2.3. Naming conventions

All database names (tables, columns, views, etc.) should be in lower case, with underscores "_" separating words. OneWebSQL™ will then automatically map the database names to Java identifiers with proper Java naming conventions when you use it to generate your code.

Warning
Do not use non-ASCII characters, whitespace, or Java keywords in the names. Otherwise, the Java code generation will not work properly.

3. Basic modeling

3.1. Overview of modeling process

  1. Start with the Conceptual Data Model (CDM).
  2. Add entities (tables) and relationships between the tables in the CDM.
  3. Generate a Physical Data Model (PDM).
  4. Add views and sequences in the PDM.
  5. Generate Java code.
  6. If you later need to modify the tables or their relationships, you must first edit the CDM, then regenerate the PDM. Any objects defined in the PDM are preserved.

This diagram shows an overview of the modeling process.

3.1.1. Create new Conceptual Data Model

  1. Choose File > New Model... > Conceptual Data to create a new Conceptual Data Model (CDM).
  2. Right click on the name of the model and choose Model Options...
  3. From the Notation dropdown menu, choose Entity/Relationship.
  4. Under Data Item, uncheck both the Unique code and Allow reuse fields.

3.1.2. Generate Physical Data Model

  1. First, select Tools > Generate Physical Data Model...
  2. Now choose your DBMS. At the moment OneWebSQL™ PD2Java supports the following types of DBMS:
    • IBM DB2 UDB 8.x Common Server or higher
    • Microsoft SQL Server 2008 R2 or higher (experimental)
    • MySQL 4.0 or higher
    • ORACLE Version 10g or higher
    • PostgreSQL 7.3 or higher
  3. Click on the Detail tab. For the FK column name template, type in %PARENT%_%COLUMN% in the provided field.

3.2. Tables

Note
A Conceptual Data Diagram is an Entity Relationship Diagram.

Your database tables are generated from the Entities you create in the Conceptual Data Model. Your PDM tables are then translated into Java objects (see the Application Development Guide).

  1. In the Toolbox, select the Entity icon. This creates a new entity.

  2. Double-click the entity anywhere inside its borders. This opens the Entity Properties window.

  3. From the General tab, in the Name: field, fill in a name for the entity. Then in the Code: field, type the name you want to use for the Java classes corresponding to this table.

  4. Open the Attribute tab to add table columns. The Code value is used to generate the name of the corresponding Java attribute. The Data Type value is translated to the appropriate Java attribute type.

  5. Use the Notes tab to add any extra information about the table.

3.2.1. List of corresponding database and Java types

Database to Java mappings are shown in the table below.

Database type Java type
DECIMAL java.math.BigDecimal
NUMERIC java.math.BigDecimal
CHAR java.lang.String
VARCHAR java.lang.String
INTEGER java.lang.Integer
BIGINT java.lang.Long
REAL java.lang.Float
FLOAT java.lang.Double
DOUBLE java.lang.Double
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp

Note that database types are mapped to their object counterparts, so NULL values are handled correctly.

3.2.2. Primary keys

If you want a column to be part of a table's primary key, check the P checkbox in the Attribute tab.

It is possible to design a table with no primary key. Since this is useful only in rare situations, you need to confirm that this is really what you want. In the Notes tab for the table in question, add:

has_no_pk=true

Note about the generated Java code
The Java code for tables will contain methods for retrieving rows using primary key columns.

Do not change the order of primary key columns in the model
For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the primary key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

Tip: order of columns in the key
To see the order of columns in the key, open the table Properties. Go to the Keys tab. Find the key and open Key Properties window (Alt+Enter). Go to the Columns tab.

3.2.3. Alternative keys

  1. To add a new alternative key, go to the the Attributes tab.
  2. Click the Create Identifier icon. The icon looks like a yellow key.

  3. The Identifier Properties window opens.
  4. Click on the Attributes tab.
  5. Select the column(s) for the new alternative key.

    Note about the generated Java code
    The Java code for tables with alternative keys will contain methods for retrieving rows using those alternative key columns.

    Do not change the order of key columns in the model
    For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

    Tip: order of columns in the key
    To see the order of columns in the key, open the table Properties. Go to the Keys tab. Find the key and open Key Properties window (Alt+Enter). Go to the Columns tab.

3.3. Relationships between tables

The CDM uses relationships between entities in place of foreign keys; appropriate foreign keys are then generated in the PDM stage. This section covers how to specify the relationships between entities. You may specify:

To create relationships between tables, select the Relationship icon in the Toolbox.

Click and drag the mouse to draw a line between related entities. First click anywhere inside the first entity. Then, while holding the mouse button down, draw a line to the second entity. Release the mouse once the cursor is inside the second entity.

3.3.1. Self-references

You may create self-references, that is, references from a table to itself. For example, you may want to have a category tree like the one shown below:

Self-references work the same way as other relationships between tables. However, you must give names to the roles in the relationship.

  1. Double-click on the line connecting the entity to itself.
  2. Go to the Cardinalities tab.
  3. In both Role name: fields, fill in names for the relationship roles.

3.3.2. Multiple relationships between tables

If you have more than one relationship between two tables, you must name the roles in the relationship. Roles are required for multiple relationships between tables.

  1. Double-click on the line connecting the two entities.
  2. Go to the Cardinalities tab.
  3. In both Role name: fields, fill in names for the relationship roles.

3.4. Views

It's a good practice to create a new view for every query that is executed often. For example, if you want to display an HTML table that combines three different database tables, it's more efficient in the long run to create a new view for it than to build JOINs every time.

Views are added in the Physical Data Model.

  1. To create a new view, select the View icon from the Physical Diagram section of the Toolbox.

  2. Double-click on the view to open the View Properties window.

  3. Type in the SQL query you want to associate with the view. Specify the list of columns. Even if you want to list all the columns, do not use an asterisk *.

  4. In the Columns tab, verify that PowerDesigner has correctly parsed the query. Make sure the Code and Data Type values are correct. If the types are not right, fix them manually.

3.4.1. Primary keys in views

  1. You can add a new primary key to the view in the Notes tab. For example, to add a new key named pk which uses the id column of the view, type:
    key.pk=id
    
    Alternately, to create a multicolumn key which uses the id and author columns, type:
    key.pk=id,author
    
    The general syntax is
    key.<name_of_key>=<list_of_columns>
    
  2. To specify that the new key is the primary key, type:
    PK=pk
    
    The general syntax for the primary key is
    PK=<name_of_key>
    
    Note that the name of the key is case-sensitive.

Note about the generated Java code
The Java code for views with a primary key will have methods to retrieve view rows using the primary key column(s).

Do not change the order of primary key columns in the model
For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the primary key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

3.4.2. Alternative keys in views

You can add a new alternative key to the view in the Notes tab. For example, to add a new key named ak which uses the isbn column of the view, type:

key.ak=isbn

Additional Example: If you want to create a multicolumn key which uses the id and author columns, type:

key.ak2=id,author

The general syntax is

key.<name_of_key>=<list_of_columns>

Note about the generated Java code
The Java code for views with an alternative key will have methods to retrieve view rows via the alternative key column(s).

Do not change the order of key columns in the model
For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

4. Customizing the model with object properties

You can customize the Java code that will be generated by setting additional properties in the Notes for an object. Properties can be set for:

For each of these, you should first double-click the object you want to modify. This opens its properties window. Then, go to the Notes tab. You should specify properties in the Java properties format:

<key>=<value>

Comments start at the beginning of a line with the pound sign #.

# This is a comment
package=com.example # This is NOT a comment

4.1. Java package

You can specify that all Java classes generated for a certain model's tables and views should become part of a specific Java package. In the Notes tab for the model, type:

package=com.example

If you don't specify the package in the model, the default Java package will be used. For information on how to set the default Java package, see the Code Generation Guide.

You can also change the Java package setting for an entity (table) or a view. This will override the package property set in the model. In the Notes tab for the table or view, type:

package=com.example.mytable

4.2. Sequences

Tables and views with a single-column integer primary key (i.e., a key which can be translated to the type java.lang.Byte, java.lang.Short, java.lang.Integer, or java.lang.Long) are handled in a special way. If the value of the key is not provided, then insert methods in the DAO will read the value of the key from a sequence. By default, the name of this sequence is <table_name>_seq. You may change the name of this sequence. In the Notes tab for the entity (table) or view, type

sequence=<name_of_sequence>

Make sure that the sequence exists in the database. To add the sequence in the Physical Data Model:

  1. Select Model > Sequences to open the List of Sequences window.
  2. Click the Add a Row tool and type a name for the new sequence.

  3. Double-click the arrow to the left of the new sequence. This opens its Sequence Properties window.
  4. Click the Physical Options tab and enter any appropriate parameters.

  5. (Optional) To associate the sequence with the column, double-click the column entry. Then, in the General tab, specify the name of the sequence.

4.2.1. Disabling auto-increment for the primary key

You can switch off auto-increment for the primary key value. To do this, go to the Notes tab for the table or view (do NOT open the Notes tab for the primary key column itself). Type:

dont_generate_get_next_id=true

4.3. Change Java class for a column

You can change the Java class which is be assigned to a column. In the Notes tab for the column, type:

column.class=<java-type>

The <java-type> value can be one of the following:

You must use the fully qualified name of the type, exactly as it is shown above.

The values are retrieved from the database by an appropriate ResultSet method, such as getBoolean(int) for the java.lang.Boolean type. Make sure that the corresponding ResultSet method works with an underlying database type.

4.3.1. Using Booleans in a database with no Boolean types

This feature is most useful when the database does not have Boolean datatypes (as in Oracle) but the attribute is a logical value. In the Notes tab, set the column.class property to java.lang.Boolean.

column.class=java.lang.Boolean

Database values and their corresponding Java Boolean values are shown in the table below:

Database type Database value Java value
CHAR / VARCHAR "0" false
CHAR / VARCHAR "1" true
CHAR / VARCHAR other unspecified
BIT / TINYINT / SMALLINT/ INTEGER / BIGINT 0 false
BIT / TINYINT / SMALLINT/ INTEGER / BIGINT 1 true
BIT / TINYINT / SMALLINT/ INTEGER / BIGINT other unspecified

4.4. Hide column in toString() method

OneWebSQL™ PD2Java generates a Java bean for every table and view in the database schema. By default the bean's toString() method prints all non-LOB columns. Sometimes this behavior is not desirable, most often for security reasons. For example, your column may contain the hash of a user's password. If you don't want a certain column to be used in the toString() method, then in the Notes tab for the column, type:

dont_generate_to_string=true

5. Large objects (BLOBs/CLOBs)

For performance reasons, the fields corresponding to binary objects are not generated in Java beans. If a table contains binary objects, then appropriate access methods are generated in DAOs. This is why you cannot have a BLOB/CLOB column which is NOT NULL. Otherwise, you will not be able to insert a Java bean into the database.

OneWebSQL™ PD2Java supports two kinds of binary objects: binary objects (BLOBs), and character objects (CLOBs). For each kind of object, a different set of access methods is generated. For details about the generated methods, see the DAO Guide.

5.1. PostgreSQL

PostgreSQL has two different methods of storing binary data:

The BYTEA columns are mapped to byte[] columns by OneWebSQL™ PD2Java, and are stored in Java bean. OID columns are treated as BLOB columns (see also section Large objects (BLOBs/CLOBs) before). OneWebSQL™ PD2Java does not, by default, delete OID objects when it deletes the corresponding table. If you want this to happen, you should create a trigger which deletes the binary data when the corresponding table row is deleted.

Here is some sample code showing how to do this:

create trigger <trigger_name>
after delete or update on <table_name>
for each row execute procedure delete_linked_blobs();

The body of the function delete_linked_blobs() is as follows:

create or replace function delete_linked_blobs() returns trigger as $delete_linked_blobs$
begin
    if tg_op = 'DELETE' then
        perform lo_unlink(old.content);
    elsif tg_op='UPDATE' and (old.content != new.content) then
        perform lo_unlink(old.content);
    end if;
    return old;
end;
$delete_linked_blobs$ language plpgsql;

The code can also be found in the distribution archive in file sql/postgresql-create-function-delete_linked_blobs.

6. Using database functions

You may invoke both built-in and user-defined database functions in your queries. For details on how to invoke a function, see the Query Language Guide. However, queries which invoke database-stored functions do not use the full power of OneWebSQL™ PD2Java type-checking. If your database schema changes, OneWebSQL™ PD2Java will not detect changes in database-stored functions.

7. Inheritance

OneWebSQL™ PD2Java does not support table inheritance. There are some ways to model inheritance in a database:

The structure of the generated code will reflect the structure of your database. You will not have inheritance in your code. You can create views which represent objects of a certain class in the hierarchy. However, the view will not be part of the Java inheritance hierarchy.

8. Limitations

8.1. Supported databases

At the moment OneWebSQL™ PD2Java supports the following databases:

8.1.1. Microsoft SQL Server

Microsoft SQL Server does not have sequences. OneWebSQL™ reads the next id with the help of get_next_id() stored procedure. You have to create this procedure when you create the database structure. Here is some sample code which shows you how to create the procedure. The code can also be found in the distribution archive in the file sql/mssql-create-get_next_id.sql.

/*==============================================================*/
/* Procedure used to obtain id for new records                  */
/*==============================================================*/
CREATE PROCEDURE get_next_id @table_name VARCHAR(255), @next_id INTEGER
OUTPUT AS
    DECLARE next_id_crsr CURSOR
            FOR SELECT next_id FROM tech_max_id WHERE table_name =
@table_name FOR UPDATE OF next_id
    OPEN next_id_crsr
    FETCH next_id_crsr INTO @next_id
    IF 
FETCH_STATUS = 0 UPDATE tech_max_id SET next_id = next_id + 1 WHERE CURRENT OF next_id_crsr ELSE BEGIN SELECT @next_id = 1 INSERT INTO tech_max_id (table_name, next_id) VALUES (@table_name, 2) END CLOSE next_id_crsr DEALLOCATE next_id_crsr go

8.1.2. MySQL

MySQL does not have sequences. OneWebSQL™ reads the id values from an auxiliary table tech_max_id. You have to create this table when you create the database structure. Here is some sample code which shows you how to create the table. The code can also be found in the distribution archive in the file sql/mysql-create-table-tech_max_id.sql.

create table tech_max_id (
 table_name varchar(255) primary key not null,
 next_id int not null
);

8.1.3. PostgreSQL

If you want to use the LO feature in PostgreSQL, make sure you delete the corresponding objects (see previous section). The code can which shows how to do it can be found in the distribution archive in file sql/postgresql-create-function-delete_linked_blobs.

8.2. Inheritance

OneWebSQL™ PD2Java does not support Java object inheritance.

8.3. NOT NULL Large Object fields

You cannot have NOT NULL Large Object fields in the database.