A common task in the automation environment is to connect a database to a vCenter Orchestrator (vCO) for various reasons. Often there is build a straightforward solution (like in this example) in an action or workflow script inside of a workflow. But in terms of a state-of-the-art programming style, a solution using the CRUD pattern is desirable to achieve encapsulation. Also it is no good coding style to repeat the code for the database connection in every single action which connects to the database.
A solution to this would be writing a vCO-plugin encapsulating the database connection and written in Java code using common solutions like spring-data. The main drawback of this solution is the separation of development platforms. The business logic you are writing is developed in the vCO but the database connection is maintained in a Java IDE. So you have to design data transfer objects (DTOs) for the communication between the business code in vCO and the database connection inside the plugin. In case of a change of the database objects in the JAVA IDE their usage in the vCO also has to be changed. This cannot be a desirable solution at all because this is a common source of bugs. Often only the DTO implementation will be changed correctly leading to wrong communication between business code and database connection. So a better solution would be writing the database connection directly inside the vCO.
In one of our projects we developed a solution implementing the CRUD pattern. For each of the methods create, read, readAll, update and delete a single action is created to perform the specific database operation. Each of them consumes both the URL and credentials of the database servers as well as the name of the database table. In case of the create- or update-method additionally the JavaScript-DTO is consumed. The delete- or read-method even only need the id of the DTO. The following code listings show exemplary implementations of the CRUD methods for a SQL-database using the commonly used SQL-vCO-plugin (com.vmware.library.sql).
Assure static type system
In case of a SQL-database an assurance of a static type or at least the matching towards the definition of the DTO is necessary. In JavaScript and respectively in vCO this cannot easily be achieved. So some extra code is needed for this. This definition we propose to implement by one configuration per DTO inside the vCO. For each column in the database table, respectively the corresponding field in the DTO, an attribute in the configuration is created with the attribute’s name being the name of the database table’s column and the value being the name of the corresponding field of the DTO. For example:
| Name | Value |
|---|---|
| DB_TABLE_PERSON_FIRSTNAME | firstName |
| DB_TABLE_PERSON_LASTNAME | lastName |
| DB_TABLE_PERSON_EMAIL |
By that the SQL-table-schemas are introduced to the vCO without needing to write a special plugin describing the DTOs.
Afterwards the DTO fields can be wired as ConfigurationElements inside your workflow scripts or actions and used as follows
Extensions
A preferable extension to this would be the possibility to send custom select queries to the database server in a similar way. The following code snippet shows a readQuery-method doing exactly this.
NOTE: This script can be used to minimize duplicated code when implementing read and readAll operations by calling it inside of their implementations after creating the query. In the same way duplicated code in the create and update operations can be prevented.
Complete code
All code listings can be found on our gitHub repository.