Index by title

Date index


Modelio SQL Designer User Guide –

Introduction

Modeling a database

Conceptual Model

Logical Model

From the conceptual model to the logical model

Generating and reversing SQL scripts

Integration with the Hibernate Designer module


The Introduction topic

The Introduction topic of the Modelio SQL Designer user guide presents this module and explains how to install it in a Modelio project. Information on SQL database support is also provided.

The Introduction topic contains the following sections:


The Modeling A Database topic

The Modeling A Database topic of the Modelio SQL Designer user guide explains how to build conceptual and logical models.

For the conceptual model, this topic presents the creation of persistent classes, identifiers, properties and associations, and describes the different types of inheritance relationships that are supported.

For the logical model, you will find all the information you need on the creation of tables, field, primary keys and foreign keys.

The Modeling A Database topic contains the following sections:

Conceptual Model**
Logical Model

The “From the conceptual model to the logical model” topic

The “From the conceptual model to the logical model” topic of the Modelio SQL Designer user guide explains model transformations (transformation of a conceptual model into a logical model, and transformation of a logical model into a conceptual model), and presents the mapping rules that apply.

The “From the conceptual model to the logical model” topic contains the following sections:


The “Generating and Reversing SQL Scripts” topic

The “Generating and Reversing SQL Scripts” topic of the Modelio SQL Designer user guide explains the procedures used when generating and reversing SQL scripts.

The “Generating and Reversing SQL Scripts” topic contains the following sections:


The “Integration with the Hibernate Designer module” topic

The “Integration with the Hibernate Designer module” topic of the Modelio SQL Designer user guide explains how to work when using the Hibernate Designer and SQL Designer modules in conjunction.

The “Integration with the Hibernate Designer module” topic contains the following sections:


Mapping rules for associations

Data Model Table Model
Association Cardinality
One To One Unidirectional on Primary Key 1..1 Primary Key
One To One Unidirectional on Foreign Key 1..1 Foreign Key
One To One Unidirectional on Join Table 1..1 Join Table Foreign primary keys of the Unique join table
One To One Bidirectional on Primary Key 1..1 Primary Key
One To One Bidirectional on Foreign Key 1..1 Foreign Key Unique foreign keys
One To One Bidirectional on Join Table 1..1 Join Table Foreign primary keys of the Unique join table
One To Many Unidirectional on Foreign Key 1..* Foreign Key
One To Many Unidirectional on Join Table 1..* Join Table A foreign primary key of the Unique join table
Many To One Unidirectional on Foreign Key *..1 Foreign Key
Many To One Unidirectional on Join Table *..1 Join Table Foreign primary keys of the Unique join table
Many To One Bidirectional on Foreign Key 1..* Foreign Key
Many To One Bidirectional on Join Table 1..* Join Table Foreign primary keys of the Unique join table
Many To Many Unidirectional *..* Join Table
Many To Many Bidirectional *..* Join Table

Mapping rules for inheritance relationships

One table per class hierarchy

One table per child class

The SQL Designer module supports a variant of this strategy (one table per child class using a discriminator), which leads to the following constraints:

One table per concrete class

One table per concrete class using implicit polymorphism


Mapping rules for types

The tables below describe the mapping rules applicable to different types of data of attributes during data model/table model transformations.

Note: It is possible to modify the default mapping rules applied to types during transformations. To do this, simply modify the xml files present in the \res\mapping\ directory of the module’s resources (for example: <source type=“boolean” target=“VARCHAR2” length=“1” precision=“” scale=“”/>).

SQL92

SQL92 Type Java Type
VARCHAR2 java.lang.String
VARCHAR java.lang.String
CHAR char
FLOAT float
BIT byte
INTEGER java.lang.Integer
DECIMAL java.lang.Integer
TIME java.sql.Time
CHARACTER VARYING char
CHAR VARYING char
NATIONAL CHARACTER char
NATIONAL CHAR char
NATIONAL CHARACTER VARYING char
NATIONAL CHAR VARYING char
NCHAR char
NCHAR VARYING char
BIT VARYING byte
TIMESTAMP java.sql.Timestamp
NUMERIC java.lang.Integer
DEC java.lang.Integer
NUMBER java.lang.Integer
INTERVAL java.lang.String
YEAR java.lang.String
MONTH java.lang.String
DAY java.lang.String
HOUR java.lang.String
MINUTE java.lang.String
SECOND java.lang.String
DATE java.util.Date

MySQL

MySQL Type Java Type
BIT byte
TINYINT short
SMALLINT short
MEDIUMINT Int
INT Int
INTEGER Int
BIGINT Long
REAL Float
DOUBLE Double
FLOAT Float
DECIMAL Float
NUMERIC float
DATE java.util.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
DATETIME java.util.Date
YEAR java.lang.String
CHAR Char
VARCHAR java.lang.String
BINARY java.lang.String
TINYBLOB java.sql.Blob
BLOB java.sql.Blob
MEDIUMBLOB java.sql.Blob
LONGBLOB java.sql.Blob
TINYTEXT java.lang.String
TEXT java.lang.String
MEDIUMTEXT java.lang.String
LONGTEXT java.lang.String
ENUM java.lang.String
SET java.lang.String
SPATIAL_TYPE java.lang.String
VARBINARY java.lang.String

Oracle11G

ORACLE11G Type Java Type
CHAR Char
NCHAR Char
VARCHAR java.lang.String
VARCHAR2 java.lang.String
LONG VARCHAR java.lang.String
NVARCHAR2 java.lang.String
LONG long
RAW java.lang.String
LONG RAW Java.lang.String
NUMBER float
NUMERIC float
FLOAT float
DEC java.lang.Integer
DECIMAL java.lang.Integer
INTEGER Int
INT int
DATE java.util.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
REAL float
DOUBLE PRECISION double
TINYINT short
SMALLINT short
BIGINT long
CLOB java.sql.Clob
BIT byte
BINARY java.lang.String
VARBINARY java.lang.String
LONG VARBINARY java.lang.String
BLOB java.sql.Blob
ROWID java.sql.String

SQLServer 2008

SQLServer 2008 Type Java Type
BIT Byte
TINYINT short
SMALLINT integer
INT integer
DECIMAL java.lang.Integer
BIGINT long
NUMERIC long
SMALLMONEY short
MONEY integer
FLOAT float
REAL float
SMALLDATETIME java.util.Date
DATETIME java.util.Date
TIME Java.sql.Time
DATETIME2 java.util.Date
DATETIMEOFFSET java.util.Date
CHAR char
VARCHAR java.lang.String
TEXT java.lang.String
NCHAR java.lang.String
NVARCHAR java.lang.String
NTEXT java.lang.String
BINARY java.lang.String
VARBINARY java.lang.String
IMAGE java.lang.String
SQL_VARIANT java.lang.String
TIMESTAMP java.sql.Timestamp
UNIQUEIDENTIFIER java.lang.String
HIERARCHYID java.lang.String
XML java.lang.String
GEOGRAPHY java.lang.String
GEOMETRY java.lang.String
UDT java.lang.String

Transforming the conceptual model into a logical model

The SQL Designer module provides a feature used to transform a conceptual model (or data model)  into a logical model (or table model).

To create a table model from the current datamodel, the “Transformation: Data model to table model ” command, available on “DataModel ” type elements, is used.


Transforming a logical model into a conceptual model

The SQL Designer module provides a feature used to transform a logical model (or table model) into a conceptual model (or data model).

The “Transformation: Table model to data model ” command, available on a “DataBase ” type element, is used to create a data model from the current table model.


Generating SQL script files

The SQL generator

The SQL Designer module provides services used to generate SQL scripts from table models.

The generated SQL script uses the characteristics of an SQL database schema (create database, create table, alter table …) and takes into account all the constraints described at table model level.

Generating an SQL script from a table model

To generate an SQL script file:

2

The “Generator: SQL Script” command of the SQL Designer module

3

Window in which you can enter a generation path

Reversing SQL script files

The SQL reverse

The SQL Designer module provides reverse features for SQL script files limited to the database schema (they use the data definition language), in other words “CREATE TABLE”, “ALTER TABLE” and “DROP TABLE”.
The SQL Designer module recognizes four grammars according to the data definition language being used (Oracle11g, Mysql, Sqlserver2008, SQL92 and Firebird).
The data definition language is used to declare a database such as the creation of tables and the addition of constraints.

We have the following types of declaration:
«CREATE TABLE TEST_TABLE_NOM(NOM VARCHAR(25),PRENOM VARCHAR(30))»
«ALTER TABLE TEST_TABLE_NOM ADD ADRESSE VARCHAR(50)»
Neither the data manipulation language nor the data control language is managed.

Reversing an SQL script file

To reverse an SQL script file:

2

The “Reverse: SQL Script” command of the SQL Designer module

3

Window in which you can enter the path of the file to be reversed

The data model : a common model

The Hibernate Designer and SQL Designer modules interact through the data model (or conceptual model) which is common to both modules.


Developing a new application that includes a database

Context

A large-scale application is being developed. This application uses a database whose architecture plays a strategic role in the development that is underway.

Solution

You have just produced your application’s Java data model, your database’s SQL script and the Hibernate mapping files used to ensure object / relational mapping between the Java objects and the database.


Ensuring the persistence of a Java data model

Context

A Java application is being developed, and the persistence of a part of the application’s data model must be ensured.

Solution

You have just produced your application’s Java data model, your database’s SQL script and the Hibernate mapping files used to ensure object/relational mapping between the Java objects and the database.


Integration with the Hibernate Designer module

Introduction

When used in conjunction with the Hibernate Designer module, the SQL Designer module enables you to control all aspects related to the data persistence of a Java J2EE application.

The Hibernate Designer module ensures the management of “Java data model” and object/relational mapping aspects, while the SQL Designer module handles the database modeling aspects.

The Hibernate Designer module

The Hibernate Designer module is an extension of the Modelio modeler, dedicated to the generation of Hibernate mapping files.

The Hibernate Designer module includes the following features:


Modernizing an existing application that includes a database

Context

You have an existing application which uses a database, and you want to modernize this application.

Solution


Other features: SQL/Hibernate wizards

When the Hibernate Designer and SQL Designer modules are deployed in the same database, five new wizards covering the services provided by both modules are available.


The five new wizards providing the services linked to the Hibernate Designer and SQL Designer modules


Installing SQL Designer

The SQL Designer depends on the Persistent Profile module. This means that the Persistent Profile module must be installed first.

2

Installing the SQL Designer module in a Modelio project

Introducing SQL Designer

Welcome to the SQL Designer user guide!

The SQL Designer module is a Modelio extension dedicated to generating and reversing SQL script files.

2

The SQL Designer interface

The SQL Designer module provides the following features:

Note:  In order to get the most out of Hibernate Designer, you must be familiar with Modelio. If this is not the case, have a look at the Modelio user guides and learn how to use our tool.


SQL databases supported

The SQL Designer module supports the SQL92 norm.

Because of the numerous variations that exist between the different SQL grammars supported by the various RDBMS tools on the market, the SQL Designer module ensures compatibility with the SQL grammars used by the most commonly used RDBMS tools:


Creating an association

Associations

Associations are used to manifest links between classes in the persistence model. The mapping rules applied to an association vary according to the cardinality of the association, its aspect (unidirectional or bidirectional) and the type of SQL mapping chosen.

Modifying the cardinality of an association

An association has two roles, one at each of its extremities. The cardinality of an association depends on the cardinality of these two roles.

To modify the cardinality of a role:

Relationship between the cardinality of roles and the cardinality of the association

Role 1 Role 2 Association
0..1 0..1 One To One
1..1 1..1 One To One | Not Null
0..1 0..* One To Many
1..1 1..* One To Many | Not Null
0..* 0..1 Many To One
1..* 1..1 Many To One | Not Null
0..* 0..* Many To Many
1..* 1..* Many To Many | Not Null

Defining foreign key names

According to the type of mapping chosen for the association, the names of the foreign keys used in the SQL model must be configured.

To modify the names of foreign keys:

Defining the name of a foreign key

Defining the name of the join table

If you choose to map an association to a join table at SQL model level, the name of the join table is defined in the following way: * If the “Persistent Name” property of the association is defined, the join table will be named after this property. * If this is not the case, the join table will be named from the name of the association.

Types supported at conceptual level

The SQL Designer module currently supports the following types for identifiers and properties:

The SQL Designer module currently supports the following types of collections (defined at association role level):


Creating an SQL conceptual model

Introduction

The SQL Designer module provides services for modeling databases at the conceptual (data model) and logical (table model) levels.

The term conceptual model refers here to a UML class model that has been annotated ready for the generation of SQL script files. This model also serves as the data model for the Hibernate Designer module.

Creating an SQL conceptual model

They are two methods for obtaining an SQL persistence model:

Manually creating a new model

The SQL Designer module comes with a set of commands for use when building a class model annotated for the generation of SQL script files.

List of commands:

Transforming an existing class model

An existing UML class model can be transformed into an SQL persistence model. To do this, simply select the package containing the classes that you want to make persistent and then check the “Persistent” tickbox in the SQL tab.

If you uncheck this tickbox, then an SQL model will be transformed into a class model.

The “Persistent” property

Creating a field

Fields are created using the “ Create a field” command on a table.

The following properties can be defined for a field in the SQL tab:


Creating a foreign key

Foreign keys

Foreign keys are created using the “ Create a Foreign Key” command on a table.

3

Creating a foreign key

The following properties can be defined for a foreign key in the SQL tab:

Foreign primary keys

Foreign primary keys are created using the “ Create a foreign primary key” command on a table.


Creating an identifier

Simple identifiers

An identifier is an attribute of a persistent class enabling this class to be identified in a unique way. In most cases, identifiers are mapped as SQL model primary keys.

An identifier belonging to a persistent class

To transform a persistent model attribute into an identifier, just check the “Identifier” tickbox.

You can define the following properties for an identifier in the SQL tab:

Generators

A generator is used to generate unique identifiers for instances of persistent classes.

The “Generator” property on an identifier assigns a type of generator to the indentifier in question. Some generators require parameters. The “Parameters” property on an identifier is used to enter these parameters using the following syntax: paramname1, paramvalue1 ; paramname2, paramvalue2 ;…

Composite identifiers

If a persistent class has more than one identifier, then we refer to composite identifiers. These compositie identifiers are mapped on composite primary keys in the SQL model.

The presence of a composite identifier results in a number of constraints: your persistent classes must overload the equals() and hashCode() methods. They must also implement the Serializable interface.

If these methods are missing, the Hibernate Designer module will automatically create the skeletons of these operations during Java generation. It will also add an implementation link to the Serializable interface where necessary.


Creating an SQL logical model

Introduction

The logical model (or table model) is a relational model of SQL tables.

Since the SQL Designer module supports four variations of SQL grammar (SQL92, ORACLE11G, MYSQL, SQLServer 2008 and Firebird), there are therefore four types of logical model, one per type of grammar supported.

Creating an SQL logical model

The SQL Designer module provides a set of commands used to create a model of SQL tables in preparation for the generation of SQL script files.

Command Available on Result
Database SQL92
DataBase ORACLE11G
DataBase MYSQL
DataBase SQLServer
DataBase Firebird
UML package Creates the root of the SQL table model.
Table DataBase Creates a table.
Field primary key Table Creates a primary key for a table.
Field Table Creates an attribute for a table.
Field foreign key Table Creates a foreign key by specifying a primary key of another table.
Field foreign primary key Table Creates an attribute which is the primary key of a table and the foreign key of another.
Foreign key Table Creates a foreign key directed to another table.
Foreign key primary key Table Creates a foreign key directed to another table. However, this attribute is also the primary key of the table.

Creating a persistent class

Persistent classes are created using the  “Create Entity” command available on DataModel type elements.

In most cases, a persistent class will be mapped to a table in the SQL model.

For a persistent class, you can define the following properties in the SQL tab:


Creating a primary key

Primary keys are created using the “ Create a Primary Key” command on a table.

The following properties can be defined for a primary key in the SQL tab:


Creating a Property

The Property element declares a property of the class in the JavaBean sense.

An identifier can be transformed into a property by deactivating the “Identifier” property.

The following properties can be defined for a property in the SQL tab:


Creating a table

Tables are created using the “ Create a Table”  command on a Database.

The following constraints apply to tables:

The following properties can be defined for a table in the SQL tab:


Inheritance relationships

Support of inheritance relationships

The SQL Designer module supports inheritance relationships between persistent classes. The sections which follow deal with four inheritance mapping strategies supported by Hibernate:

The type of mapping applied to a given inheritance link can be configured in the SQL tab on the inheritance link in question.

One table per class hierarchy

Using this strategy, the set of persistent classes in the inheritance hierarchy is mapped to a single table of the SQL model.

Constraints at persistence model level:

One table per child class

Using this strategy, each of the persistent classes in the inheritance hierarchy is mapped to a table of the SQL model.

Constraints at persistence model level:

The SQL Designer module supports a variant of this strategy (one table per child class using a discriminator), which leads to the following constraints:

One table per concrete class

Using this strategy, each of the non-abstract persistent classes of the inheritance hierarchy is mapped to a table of the SQL model.

Constraints at persistence model level:

One table per concrete class using implicit polymorphism

This is a variation on the previous strategy. Each of the non-abstract persistent classes of the inheritance hierarchy is mapped to a table of the SQL model.

Constraints at persistence model level:


SQL Designer properties

Several properties used in the generation of SQL script files can be configured on elements in the conceptual model. These properties can be edited in the SQL tab.

2

The SQL Designer properties tab

Note: Certain property windows are made up of several pages for a single selected model element.
You can select the desired property page in the dropdown menu on the top line of the SQL tab.