JDM: Jolie Database Manager for PostgreSQL database

Here we host a post from Danilo Sorano who collaborated with us and the Jolie team during his training period at Imola Informatica and italianaSoftware. The training period of Danilo is part of his studies at the University of Bologna. Danilo chose to work on a project about Jolie and databases. In particular, he contributed to develop a tool for automatically extracting a jolie service which facilitates the interactions with an existing database in PostgreSQL.

Congratulations to Danilo! His post follows:

JDM

Jolie Database Manager for PostgreSQL database by Danilo Sorano

The jolie database manager is a tool whose goal is to create a facilitator for the management of PostgresSQl data sources by using microservices. The tool can be get on github: https://github.com/jolie/db_connector

The main purpose of JDM is to simplify database management operations, specifically the simplifications of the insertion, modification, deletion and retrieval of data. The tool has been designed for preventing the user to write standard queries on tables and views which can be easily automatized.

Usage instructions:

1. Creating the database and tables / views. Only if you are creating a database from scratch. Otherwise skip this step. 
2. Start the JDM server running main_table_generator.ol inside folder server
3. Configure the information necessary to connect with the database (file config.ini)
4. Start the client running createDbService.ol inside folder client
5. In folder db_services all the files will be generated: Metadata extraction from the database and creation of the service for the database.

The database service is divided in two parts:

• Automatic service
• Custom service

The automatic part provides basic operations for the database:

• Create: INSERT query
• Update: UPDATE query
• Remove: DELETE query
• Get: SELECT query

The management of more complex operations such as JOIN between tables must be managed by creating view by the user.

The custom part allows for  the development of customized query on the database and it can be freely edited by the user. This function gives to the user the possibility of being able to create the most complex operations, for example, to manage nested query.

From an architectural point of view, the database service's main part  embeds the custom part and automatic one. This mechanism allows the two parties to be independent of each other, thus allowing the modification of the automatic part every time that, for example, is added to a view without going to change the custom part.

Example: Creation of a database service In order to explain how the tool works we will use the example contained in the folder "examples", where there is a file sql "e-commerce.sql" to be used to generate the test database. The file creates a database of a simple e-commerce with three tables. The three tables are:

• user (fiscal code, name, surname, email)
• product (bar code, product name, description, quantity)
• order (Order id, product id, user id, quantity)

The service can be used by changing the information in the file "config.ini” [db_connection] HOST=localhost DRIVER=postgresql PORT=5432 DATABASE=e-commerce USERNAME=postgres PASSWORD=postgres

Creating a service for a database

1. Creation of PostgreSql database and its tables and views (It's possible using the e-commerce example.
2. Starting the Tool for generate the service (main_table_generator.ol)
3. Change the information in the config.ini file.

WINDOWS

• Start the script server TableGenerator.bat
• Start the script client CreateDbService.bat
• If everything went well the service is created

OTHER OS

• Go in the folder "server" and run the jolie file "main_table_generator.ol
• Go in the folder "client" and run the jolie file "createDbService.ol
• If the creation of the service is successful, the server print the message: “Database table generation is finished with SUCCESS”.

Using the service of the example The generated service for the example of the e-commerce is divided into two parts:

• automatic
• custom

The automatic part is represented by the file main_automatic_ecommerce.ol which contains the operations:

• createuser, createproduct and createorder
• getUser, getproduct and getOrder
• updateUser, updateproduct and updateorder
• removeuser, removeproduct and removeorder.

On the other hand, the custom part as mentioned above, allows the user to create his own customized operations. Example of a client File try_operations.ol in the example, shows the usage of the basic operations of the automatic part. The file is structred as it follows:

1. Importing of the interface and the location
   include"../db_services/e_commerce_handler_service/automatic_service/public/interfaces/includes.iol"
   include "../db_services/e_commerce_handler_service/locations.iol"
   
2. Creation of an output port for the service
   outputPort Test {
   Location: e_commerce
   Protocol: sodep
   Interfaces: userInterface, orderInterface, productInterface
   }
   Note that the default location is "socket://localhost:9100" taht is contained inside the file locations.iol
3. Inside the main you can make the call to the service operations. 

The Filter Before introducing the individual operations it is better to explain the filter field. The filter field is a FilterType type which is defined inside "automatic_service/public/types/e_commerceDatabaseCommonTypes.iol" type ExpressionFilterType: void { .eq?: bool .gt?: bool .lt?: bool .gteq?: bool .lteq?: bool .noteq?: bool } type FilterType: void { .column_name: string .column_value: any .expression: ExpressionFilterType .and_operator?: bool .or_operator?: bool } The filter field is important to define the where clause of the query:.

• column_value: value of the colum
• column_name: name of the column of the expression
• expression: the operator used to check the value, this is an ExpressionFilterType
• and_operator: we define this field only if we want to concatenate another expression, in this case an AND operator
• or_operator: we define this field only if we want to concatenate another expression, in this case an OR operator

The ExpressionFilterType define the operator:

• lteq: "<="
• eq: "="
• gt: ">"
• lt: "<"
• gteq: ">="
• noteq: "!="

As an example, let us suppose to define the following where clause "WHERE 'product_name' = 'Fried Chicken' AND quantita >= 10": getproductRequest.filter.column_name = "product_name"; getproductRequest.filter.column_value = "Fried Chicken"; getproductRequest.filter.expression.eq = true; getproductRequest.filter.and_operator = true; getproductRequest.filter[1].column_name = "quantity"; getproductRequest.filter[1].column_value = 10; getproductRequest.filter[1].expression.gteq = true; Example of a Get operation To select one or more row in the product table of the e-commerce example we use the operation getproduct getproduct( getproductRequest )( getproductResponse ) throws SQLException SQLServerException It is worth noting that the fields inside the request type is only the filter fields. type getproductRequest:void { .filter*:FilterType } In the response message we will get the rows of the select query type getproductRowType:void { .id_product:int .product_name:string .description:string .quantity:long } type getproductResponse:void { .row*:getproductRowType } This operation select one or more row inside the product table. Here an example of the call: /*Get of the all rows of “Fried Chicken” with the quantity equal or greater then ten*/ getproductRequest.filter.column_name = "product_name"; getproductRequest.filter.column_value = "Fried Chicken"; getproductRequest.filter.expression.eq = true; getproductRequest.filter.and_operator = true; getproductRequest.filter[1].column_name = "quantity"; getproductRequest.filter[1].column_value = 10; getproductRequest.filter[1].expression.gteq = true; getproduct@Test(getproductRequest)( response ); Example of a Create operation To insert a row in the user table we use createuser operation createuser( createuserRequest )( createuserResponse ) throws SQLException SQLServerException - createuserRequest The fields inside the request type are the fields of the user table. type createuserRequest:void { .fiscalcode:string .name:string .surname:string .email:string } - response: createuserResponse it's a void type This operation insert a row inside the user table, to understand better how it’s work we show an example: /*The user John Silver is inserted inside the user table*/ createuserRequest.fiscalcode = "1"; createuserRequest.name = "John"; createuserRequest.surname = "Silver"; createuserRequest.email = "john.silver@mail.com"; createuser@Test(createuserRequest)(); Example of a Remove operation To insert a row in the user table we use removeuser operation removeuser( removeuserRequest )( removeuserResponse ) throws SQLException SQLServerException - removeuserRequest There is only one filter fields, where specify the condition of WHERE clause type removeuserRequest:void { .filter*:FilterType } - response: removeuseResponse it's a void type This operation remove one or more row inside the user table, to understand better how it’s work we show an example: /*In this case we remove the user John Silver*/ removeuserRequest.filter.column_name = "surname"; removeuserRequest.filter.column_value = "Silver"; removeuserRequest.filter.expression.eq = true; removeuser@Test(removeuserRequest)(); Example of an Update operation To update one or more rows in the user table we use updateuser operation updateuser( updateuserRequest )( updateuserResponse ) throws SQLException SQLServerException - updateuserRequest The fields inside the request type are the fields of the user table and filter field type updateuserRequest:void { .fiscalcode?:string .name?:string .surname?:string .email?:string .filter*:FilterType } - response: updateuserResponse it's a void type This operation update one or more row inside the user table, to understand better how it’s work we show an example: /*Changing the surname of John from “Silver” to “Smith”*/ updateuserRequest.surname = "Smith"; updateuserRequest.filter.column_name = "surname"; updateuserRequest.filter.column_value = "Silver"; updateuserRequest.filter.expression.eq = true; updateuser@Test(updateuserRequest)(); Future features

• Ability to integrate the service, for more Database (Postgres, MySql ecc...).
• Possibility to extend the management of most types and especially more complex.

Contact

• Danilo Sorano's LinkedIn Profile 

1. C

2.