Chapter 4. Using Maven Spring Boot to create virtual databases

Use Maven to generate a Java shell project that you can then modify.

Create and deploy secret objects to store values for your environment variables.

Although secrets exist primarily to protect sensitive data by obscuring the value of a property, you can use them to store the value of any property.

- name: SPRING_DATASOURCE_SAMPLEDB_PASSWORD
 valueFrom:
    secretKeyRef:
      name: postgresql
      key: database-password

Use secret objects to securely store sensitive information about how your virtual database connects to its data sources.

For OpenShift to open a connection to a data source on behalf your virtual database application, it must pass login credentials and other information to the data source. Typically, to maintain security administrators limit access to database credentials, and do not expose credentials to developers directly. To enable developers indirect access to credentials, it’s possible to deploy secret objects on OpenShift to securely store and pass credentials.

Use secrets in combination with environment variables. Rather than specify static values directly in your configuration files, you can configure OpenShift to retrieve values for environment variables from secret objects. When a key in an environment variable refers to a secret object, to obtain the key value, OpenShift examines the secret to find a token that has a name that matches the key name. It extracts the token value and then passes it to the environment variable.

For example, the following environment variable is set to retrieve the value for the database-user key from a secret object that has the name postgresql.

- name: SPRING_DATASOURCE_SAMPLEDB_USERNAME
  valueFrom:
     secretKeyRef:
       name: postgresql
       key: database-user

When the data virtualization service needs to retrieve the value for the preceding environment variable, it accesses the secret object with the name postgresql and reads the value of the database-user key.

In the following secret object, the database-user token is assigned the value bob. OpenShift passes that value to the environment variable.

apiVersion: v1
kind: Secret
metadata:
  name: postgresql
type: Opaque
stringData:
  database-user: bob

To provide your Java shell project with the details that are required to build the project, edit the pom.xml file to specify project dependencies, plugins to run, and build profiles.

Some properties in pom.xml are common to all data source types. For example, the Teiid Spring Boot starter and the Spring Boot Maven plugin are required to connect to both a postgreSQL database and a Salesforce database. Other properties, such as the drivers that a data source requires, are specific to individual data sources.

Use the sample pom.xml file in the Teiid OpenShift repository as the basis for your own file. The sample file contains settings for a postgreSQL database, but the settings in the <dependencyManagement>, <build>, and <profiles> elements apply to any data source. The build resource must be set to the Spring Boot Maven plugin. This plugin, spring-boot-maven-plugin, converts the virtual database schema project into a Spring Boot executable Uber JAR file that becomes the basis for an OpenShift container image. The OpenShift profile element must be set to use the Fabric8 Maven plugin (<artifactId>fabric8-maven-plugin</artifactId>), which helps to build a container image from the executable JAR, and optionally deploy it into OpenShift.

Modify the values in the <dependencies> element as needed to enable connectivity to the data sources that you want to use.

After you complete changes to the pom.xml file, you’re ready to define the structure of your virtual database. You define a virtual database through a text-based DDL file. You can supply an existing DDL file, if you have one, or you can create one.

If you want to create your own DDL file you can use the sample DDL file in the Teiid OpenShift repository as a guide. But be mindful that the entries in your DDL file are unique to your environment and must include settings that are specific to the data sources that you use.

The design of a DDL file can be complex and is beyond the scope of this documentation. For more information about using SQL in DDL files that support data virtualization, see the Teiid Reference Guide.

DDL files contain SQL commands that describe and define the structure of the virtual database, such as its views or schema, procedures, functions, tables, records, columns, and other fields.

When you are ready to build your virtual database, the data virtualization service reads the information in the DDL file and uses that information to generate the virtual database image.

Structures that you define in the DDL file are converted to relational database tables. Translators in the data virtualization service import and convert data from your data sources, and use the data to create views in the virtual database.

A typical DDL file define database elements by using statements similar to those in the following list:

In the application library of your virtual database project, create a Java application file, Application.java, and a data source class file, DataSources.java.

Application.java is the main Spring Boot application file that bootstraps the virtual database application. DataSources.java adds @Bean methods for each data source that you want to use as an input to your virtual database. The Java class serves as a resource adapter, which provides access to the data source.

When the virtual database starts, the virtualization service reads the metadata and generates an internal model from it. The service then uses that model to read and write to the virtual database. Entities within a data source become available in the virtual database for users to access as tables. For example, if you use a Salesforce database as a data source, then SObjects in Salesforce become available as tables in the virtual database.

The Datasources.java file adds a class to represent a connection to a data source. The file also establishes a prefix in the ConfigurationProperties argument (spring.datasource.sampledb). This prefix must be used in the names of data source properties that you specify in the application.properties file.

You can define multiple data sources in Datasources.java by adding multiple classes, each with its own prefix designation. In each case you must add corresponding entries to the DDL file and to the properties or deployment configuration files.

To associate the Java bean with the data source that is defined in your DDL file, the bean name must be the same as the name of the SERVER and resource-name properties in the DDL file. For example, the following sample file establishes a connection to a PostgreSQL database called sampledb, which is the name that is assigned in the DDL file to the data source SERVER object and to its resource-name definition.

package com.example;

import javax.sql.DataSource;

import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DataSources {

    @ConfigurationProperties(prefix = "spring.datasource.sampledb") 1
    @Bean
    public DataSource sampledb() {    2
        return DataSourceBuilder.create().build();
    }

}

You define static properties for your virtual database application in an application.properties file in the /src/main/resource directory. Static properties are configuration settings that remain constant across different environments. After you deploy a virtual database on OpenShift, any modifications that you make to the application.properties file are not effective unless you rebuild and redeploy your virtual database.

At minimum the application.properties file must contain a value for the teiid.vdb-file property, which names the DDL file that defines the structure of the the virtual database. For example, teiid.vdb-file=customer-vdb.ddl.

You can also use the application.properties file to define other properties, such as data source properties, including their names, their drivers, and the URLs, user names, and passwords that are required to connect to them. Using static properties to assign values for these can be useful in a test environment. But if you deploy your virtual database in multiple OpenShift environments, it’s best to use environment variables to dynamically assign unique values for each environment. For more information about using environment variables in your virtual database configuration, see Section 4.12, “Setting the deployment configuration”.

If you define data source properties in the application.properties file, you must prefix the configuration properties string that you specified in the Datasources.java file. The prefix establishes a connection between the properties and the Java class. For example, if you establish the configuration properties prefix spring.datasource.sampledb in the Datasources.java file, then you must precede the names of the properties that you define in your application.properties file with that string. For example,

spring.datasource.sampledb.username=<username>
spring.datasource.sampledb.password=<password>

The following application.properties file includes settings for connecting to a postgreSQL database. You might use a file similar to this for testing purposes, but for an OpenShift deployment, it’s best to specify data source properties in a deploymentconfig.yml file.

You can specify source-specfic properties for other types of data sources. For more information, see Section 4.13, “Connection settings for other data sources”.

spring.datasource.sampledb.jdbc-url=jdbc:postgresql://localhost/sampledb 1 2
spring.datasource.sampledb.username=user 3
spring.datasource.sampledb.password=user
spring.datasource.sampledb.driver-class-name=org.postgresql.Driver 4
spring.datasource.sampledb.platform=sampledb 5

# VDB location
teiid.vdb-file=customer-vdb.ddl 6

# true to allow JDBC connections
teiid.jdbc-enable=true

#logging.level.org.teiid=DEBUG 7

A deployment configuration file stores settings that govern how the Fabric8 Maven plugin builds and deploys the container image for your virtual database.

The deploymentconfig.yml file can also define environment variables for the properties that are required to configure data sources for your virtual databases. The environment variables that you define in the deploymentconfig.yml file map to properties in the application.properties file. But unlike settings in the properties file, the settings that you define in the deploymentconfig.yml file are dynamic. That is, if you add an environment variable or change its value, you do not have to rebuild the virtual database service to put the change into effect.

Environment variables and their corresponding properties have similar names, but they are formatted differently. Separators in the property names are converted from dots or dashes to underscores, and alphabetic characters are converted to uppercase.

Because you commonly deploy virtual databases to multiple OpenShift environments, for example a staging and a production environment, you typically specify different data source properties in each environment. For example, the login credentials for accessing a data source in your staging environment probably differ from the credentials that you need to access the data source in the production environment. To define unique values in each environment, you can use environment variables.

The environment variables in deploymentconfig.yml replace any static properties that you might set in the application.properties file. If a property is defined in both files, the value in the deploymentconfig.yml file takes precedence.

You use a single version of the file across environments and use secret objects to isolate the unique details of each environment. Instead of specifying static values for environment variables directly in the file, you can store the values for each deployment environment in secret objects that are unique to each environment. The value of each environment variable in the file contains only a key reference, which specifies the name of a secret object, and the name of a token in the secret. The token stores the actual value. At runtime, environment variables retrieve their values from the tokens.

By using secrets to store the values of your environment variables, you can use a single version of the deploymentconfig.yml across environments. The secret objects that you deploy in each environment must have the same name, but each secret object contains token values that are specific to its environment.

You set the deployment configuration by editing a deploymentconfiguration.yml file. You can define environment variables in the file for each data source that the virtual database uses.

To enable a virtual database to connect to a data source, you must provide connection details such as the name of the data source, the driver to use, the user credentials and so forth. You specify these settings across several files.

The sample files in the Teiid OpenShift repository, or elsewhere in this documentation provide configuration information that is consistent with using a postgreSQL database as the data source for your virtual database. If you want to a different data source, you must modify settings in the postgreSQL versions of the following files:

When the virtual database service starts, the data virtualization service scans the application’s packages for dependency annotations and uses the information to build the metadata to create the virtual database and deploy it to the OpenShift server.

<dependency>
   <groupId>org.teiid</groupId>
   <artifactId>teiid-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.teiid.spring</groupId>
    <artifactId>spring-data-salesforce</artifactId>
</dependency>

package org.teiid.spring.example;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.teiid.spring.data.salesforce.SalesforceConfiguration;
import org.teiid.spring.data.salesforce.SalesforceConnectionFactory;

@Configuration
public class DataSources {

    @Bean
    public SalesforceConnectionFactory accounts(SalesforceConfiguration config) {
        return new SalesforceConnectionFactory(config);
    }

    @Bean
    @ConfigurationProperties("spring.teiid.data.salesforce")
    public SalesforceConfiguration salesforceConfig() {
        return new SalesforceConfiguration();
    }
}

<dependency>
   <groupId>org.teiid</groupId>
   <artifactId>teiid-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.teiid.spring</groupId>
    <artifactId>spring-data-google</artifactId>
</dependency>

@Configuration
public class DataSources {

    @Bean
    public SpreadsheetConnectionFactory accounts(SpreadSheetConfiguration config) {
        return new SpreadsheetConnectionFactory(config);
    }

    @Bean
    @ConfigurationProperties("spring.teiid.data.google.sheets")
    public SpreadSheetConfiguration sheetsConfig() {
        return new SpreadSheetConfiguration();
    }
}

After you complete the configuration tasks for your virtual database, you are ready to run Maven to build the project.

The Spring Boot Maven plugin creates a self-contained Uber JAR that includes all of the application code and dependencies in a single JAR file. The resulting JAR file serves as the basis for an OpenShift image. The OpenShift profile includes a Fabric8 Maven plugin that compiles the current build with the Uber JAR to generate an image that you can deploy to OpenShift.

After you make certain changes to the virtual database configuration, for example adding protocol services or routes for the virtual database, you must re-compile the project to update the deployed image.

mvn clean install -Popenshift -Dfabric8.namespace=`oc project -q`