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 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:
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 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 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:
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 |
The SQL Designer module supports a variant of this strategy (one table per child class using a discriminator), which leads to the following constraints:
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 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 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 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 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 |
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.
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.
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.
To generate an SQL script file:
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.
To reverse an SQL script file:
The Hibernate Designer and SQL Designer modules interact through the data model (or conceptual model) which is common to both modules.
A large-scale application is being developed. This application uses a database whose architecture plays a strategic role in the development that is underway.
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.
A Java application is being developed, and the persistence of a part of the application’s data model must be ensured.
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.
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 is an extension of the Modelio modeler, dedicated to the generation of Hibernate mapping files.
The Hibernate Designer module includes the following features:
You have an existing application which uses a database, and you want to modernize this application.
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
The SQL Designer depends on the Persistent Profile module. This means that the Persistent Profile module must be installed first.
Welcome to the SQL Designer user guide!
The SQL Designer module is a Modelio extension dedicated to generating and reversing SQL script files.
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.
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:
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.
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:
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 |
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:
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.
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):
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.
They are two methods for obtaining an SQL persistence 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:
DataModel (avaialable on Package UML): Creation of the root of the conceptual model.
DataModel (available on
DataModel /
DataModel): Creation of a package in the conceptual model.
Entity: (available on
DataModel /
DataModel): Creation of a persistent class.
Identifier: (available on
Entity): Creation of an attribute that will serve as the identifier of a persistent class.
Attribute (available on
Entity): Creation of an attribute for a persistent class.
Relationship (available on
Entity): Creation of an association between two persistent classes.
Hierarchy (available on
Entity): Creation of a generalization link between two persistent classes.
Persistent Diagram
(available on
DataModel /
DataModel): Creation of a diagram dedicated to conceptual SQL modeling.
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.
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:
Foreign keys are created using the “ Create a Foreign Key” command on a table.
The following properties can be defined for a foreign key in the SQL tab:
Foreign primary keys are created using the “ Create a foreign primary key” command on a table.
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.
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:
Persistent Name: Name of the SQL table to which the identifier will be mapped. If this field is left empty, Modelio will consider the name of the SQL field to be identical to that of the identifier.
Persistent Type: The SQL type of the identifier.
Generator: The name of the generator associated with this identifier.
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 ;…
native: Delegates identifier generation to the database.
increment: Generates identifiers of long, short or int type which are only unique if no other process inserts data into the same table. Should not be used in a clustered environment.
identity: Uses the DB2, MySQL, MS SQL Server, Sybase and HypersonicSQL identity column. The returned identifier is of long, short or int type.
sequence: Uses sequences in DB2, PostgreSQL, Oracle, SAP DB, McKoi or a generator in Interbase. The returned identifier is of long, short or int type.
hilo: Uses a hi/lo algorithm to efficiently generate identifiers of long, short or int type, taking as “hi” value source a table and a column (by default, SQL_unique_key and next_hi respectively). The hi/lo algorithm only generates unique identifiers for a particular database.
seqhilo: Uses a hi/lo algorithm to efficiently generate identifiers of long, short or int type, given a base sequence name.
uuid: Uses a UUID 128 bits type algorithm to generate identifiers of string type, which are unique within the network (the IP address is used). The UUID is encoded in a string of 32 hexadecimal numbers. guid Uses a GUID string generated by the database for MS SQL Server and MySQL.
assigned: Lets the application assign an identifier to the object before the save() method is called. This is the default strategy if no <generator> is specified.
select: Retrieves a primary key assigned by a trigger by selecting the line by any unique key.
foreign: Uses the identifier of an associated object. Normally used in conjunction with a <one-to-one> association on the primary key.
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.
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.
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 |
---|---|---|
![]() ![]() ![]() ![]() ![]() | UML package | Creates the root of the SQL table model. |
![]() |
![]() | Creates a table. |
![]() |
![]() | Creates a primary key for a table. |
![]() |
![]() | Creates an attribute for a table. |
![]() |
![]() | Creates a foreign key by specifying a primary key of another table. |
![]() |
![]() | Creates an attribute which is the primary key of a table and the foreign key of another. |
![]() |
![]() | Creates a foreign key directed to another table. |
![]() |
![]() | Creates a foreign key directed to another table. However, this attribute is also the primary key of the table. |
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:
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:
Name: Name of the primary key
Key: Indicates the type of the attribut (PrimaryKey)
Type: The SQL type of the primary key
Default value: A default value for the primary key (editable field)
Not null: The SQL NOT NULL property
Autogenerated: The SQL AUTO_INCREMENT property
Unique: The SQL UNIQUE property
Null: The SQL NULL property
Check: The SQL CHECK 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:
Persistent Name: Name of the SQL field to which the Property will be mapped. If this field remains empty, Modelio considers that the name of the SQL field is identical to that of the attribute.
Persistent Type: The SQL type of the Property.
Unique: Boolean: Inidicates whether or not the Property is unique.
Not Null: Boolean: Indicates whether or not the Property can take the value “null”.
Generated: Indicates that the value of this Property is generated by the database.
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:
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.
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:
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:
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:
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:
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.
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.