Connecting the Database

Domain Modeling with WebRatio Platform
2,713 views Published on Feb 28, 2014 | Time 20 min
Applies to: 7.2 or higher

Learn how to speed up your productivity by letting WebRatio Platform create physical elements on the database starting from your Domain Model, or letting it extract the Domain Model starting from an existing database structure. Forget about writing SQL by hand! WebRatio Platform helps you generate the instructions required to transpose the Domain Model elements on the database in terms of tables, views, and columns. The procedure that translates instructions to and from the Domain Model is called synchronization. It allows you to export your Domain Model to the database, or to import elements from the database to the Domain Model. Also, the Domain Model synchronization process is database type-aware, which means that the SQL code uses the convention of the specific type of database you are using.

Transcript

Table of contents

Prerequisites

In order to understand the content of this lesson, make sure you watched the following lessons:

Introduction

This lesson shows you how to synchronize the Domain Model, created in WebRatio Platform, with a relational database. Learn how to speed up your productivity by letting WebRatio Platform create physical elements on the database starting from your Domain Model, or letting it extract the Domain Model starting from an existing database structure.

Avoid Boring Task

Forget about writing SQL by hand! WebRatio Platform helps you generate the instructions required to transpose the Domain Model elements on the database in terms of tables, views, and columns. The procedure that translates instructions to and from the Domain model is called synchronization. It allows you to export your Domain Model to the database, or to import elements from the database to the Domain Model. Also, the Domain Model synchronization process is database type-aware, which means that the SQL code uses the conventions of the specific type of database you are using.

Adding a Database

The first thing to do when using synchronization is to add a database connection to the Web Project. You can have different database connections, each referring to a subset of the Domain Model.

Right-click on the work area of the Domain Model and choose "Add", and then choose the "Database" option. Select a meaningful name for the database, such "CRM". The name is just a reminder about which database connection is set on a specific element.

Then choose the database type. Move to the Properties View and click on the "Type" drop-down property. As you can see, you can choose from a set of predefined options, which are the most common database types you may want to use.

Once you choose the database type, configure the connection properties. Copy the value of the "Sample URL" property, which shows the structure of the connection string for the specific database type, and paste it into the "URL" property. Replace the "host" placeholder with the IP address of the workstation hosting the database, the "port" placeholder with the port number on which the database engine is running, and the "database" placeholder with the name of the database to which you want to connect. Remember that the angular brackets are part of the placeholders, and they must be removed.

Now you have to specify the "Username" and "Password" for connecting to the database. To save the password in the project with encryption, you just have to flag the corresponding checkbox placed inside the "Password" property.

Testing the Database Connection

Now let's test the database connection. Right-click on the "Database" node from the Outline View and select the "Refresh" option. The first time you make a connection with a database of a specific type, WebRatio Platform asks you to provide the JDBC driver to use for the connection.

The JDBC driver is a library that is used by Java applications to connect to databases. Each database type needs its own library. In this example we need the PostgreSQL JDBC driver. All drivers can be downloaded from the Internet. Once you download the driver, you can click on the anchor shown in the dialog and then select the driver from your hard drive. Then the connection test is performed.

If everything works correctly, you should see a new element nested into the "Database" node in the Outline View. This element is named "Metadata" and it contains the database structure retrieved using the connection. The "Metadata" element is empty if you have a newly created database.

Database Connection Errors

If WebRatio Platform is not able to connect to the database, an Error element is nested into the database node in the Outline View. If this happens, select the database node from the Outline View and look at the stack trace of the error shown in the database's Properties View.

The most common errors are related to authentication issues. These kinds of issues are due to wrong parameters setting on the "Database" node of the Domain Model. If the host or port is wrong, the error shown will be "Connection attempt failed". The stack trace of the error will show also the cause of the issue that is the "Unknown Host".

Another frequent error is the wrong port. In this case, the error shown is "Connection refused". The stack trace asks you to check the host name and port.

If the database name is wrong, an error such as "the database does not exists" will be shown.

The errors related to wrong username or password are shown as "password authentication failed for user".

Synchronization

Once you add the database connection, you are ready to make the synchronization. The synchronization process consists of creating a binding between the logic layer, represented by the Domain Model, and the physical layer, which is the database. In WebRatio Platform, the binding information is called mapping. The synchronization process compares the Domain Model with the database structure and shows the result in a dedicated dialog. The result of the process varies, depending on the database status.

Usually you may have two different situations:

  • You modeled everything in the Domain Model but the relational database is empty because it is new. In this case, you want to export the model to the database. This process is named "Forward Engineering".
  • You have to work with an existing database, which contains all the objects you have to use in the Domain Model. So you have to design the Domain Model exactly in the way in which it is in the database. This process is named "Reverse Engineering".

Let’s see, in detail, how each process works.

Forward Engineering

The "Forward Engineering" process is used when you start working on a new project that needs also a new database, or when the database does not exist at all. In this case you model all Entities, Attributes, and Relationships you need in the Domain Model and then you export everything to the database.

Consider as an example the "Company" entity in the Domain Model. You can understand that you need to use synchronization because all the elements belonging to the "Company" entity have a question mark inside the icon. The question mark indicates that there is no reference to any element in the database for the current model element. So you need to make the synchronization.

Before proceeding, check whether the Domain Model contains problems. Right-click in the Domain Model work area and select "Find" and then "Find Model Problems". This option makes WebRatio Platform search for errors in the Domain Model. If the model is correct you will obtain a list of problems reminding you that the "Company" entity and its Attributes are not mapped on the database.

Now you can start the synchronization process. Right-click on the "database" node in the Outline View and select the "Synchronize" option to start the synchronization process. In this case, the result of the synchronization process is a dialog showing the "Company" entity as the element to be exported from the Domain Model to the database. The "Company" element is highlighted with a gray arrow that indicates that it is an object not present in the database.

Right-click on the "Company" entity item and select the "Export to Database" option. Then press the "Next" button. Now you will see a dialog showing the SQL code necessary to create the company table with all the related columns. The dialog shows you three different options to handle the generated SQL code: "Save Only", "Save and Execute", and "Execute Only".

The "Save and Execute" option, which is the default, saves the SQL script in a file stored in the "DB Script" folder of the Web Project, and then executes the SQL script. WebRatio Platform proposes a default name for the file, which is the current timestamp. It is important to give meaningful names to these files since you may need to execute them in the future.

The "Execute only" option executes the script without saving it. Using this option is not a good practice, because you are not tracking the changes applied to the database.

The "Save only" option saves the script without executing it. This option is a good choice when you want to make changes to the SQL script in order to optimize it. WebRatio Platform gives you the chance to execute the file by right-clicking on the file from the WebRatio Explorer View and selecting the "Execute SQL Script Option".

For this example, just leave the default selection as is and click "Finish" to confirm. WebRatio Platform executes the SQL scripts and you may notice that all the icons related to the "Company" attributes no longer have the question marks. This means that WebRatio Platform knows the referenced element on the database for each Domain Model element. The SQL code is executed in a "transaction" mode if the database supports it. The transaction mode lets you keep the database in a consistent state even if an error occurs in the SQL code execution, provided that the database type supports this feature.

SQL Scripts

It’s important to organize the SQL scripts in a smart way in order to be able to execute them as needed. This organization effort is necessary because in the development phase you will not use the production database but a development database, and so you have to redo all the changes on the production database at deployment time.

The suggested practice is to create a new folder inside the project folder. For example you can create a "SQL" folder inside the WebProject folder and use this folder to keep all the sql scripts in the same place.

Reverse Engineering

The "Reverse Engineering" process is used when you want to create the Domain Model corresponding to the elements contained in an existing database. In this case, you should not design the Domain Model by hand because you can "import" the structure of the elements in the Domain Model starting from the existing database elements. At the end of this process, you can obtain a draft of the Domain Model to use. You need to complete it by setting primary keys and data types in case WebRatio Platform is not able to automatically set them.

Let’s suppose that the "CRM" database has the "Purchases" table you want to use in the Web Project. Since the table has many columns you do not want to create the Entity by hand and so you use the synchronization process, this time in the Reverse way.

Right-click on the "database" node in the Outline View and select the "Synchronize" option to start the synchronization process. Assuming that there are no other changes pending in the Domain Model, the synchronization dialog shows the "Purchases" table defined in the database but not referenced in the Domain Model. Right-click on the table and select the "Import into Model" option. Press "Next" to open the SQL Statements dialog that will be left empty since there are no SQL queries needed. Click "Finish" to complete the procedure.

The result is the import of the "Purchase" table inside the Domain Model, and now the "Purchase" entity will be available for use by the IFML components.

Manual Mapping

Usually when you work on the Domain Model you have a situation that is somewhere between the forward and reverse engineering processes shown before. The synchronization process allows you to take care of both situations. You just have to look in the dialog in order to understand the current situation for each model element and to choose the actions to perform. It's always possible to manually define the mapping for elements in the Domain Model.

The "Manual mapping" method is useful when you need to map items created in the database to already-existing domain model items. An example may be mapping a database view (pre-existing in the database) to an entity in the Domain Model. Each Domain Model element has a particular part of its properties view dedicated to mapping. Here you can find and change all the information related to the item mapping, such as the Database, the Schema, Table, and so on. To manually map an element you must be connected to the database.

Creating Manual Mapping

Let's suppose you want to map the new "Employee" entity to an existing table on the database.

Select the Entity and move to the Properties View. Select the "Mapping" tab. Then choose, in order, the desired database, schema, and table. Once you have mapped the Entity you can map each Attribute defined in the Entity.

Select each Attribute and move to the Properties View in the "Mapping" tab to assign the desired column of the table to the Attribute.