Chapter 4. Creating a virtual database as a Maven artifact

Procedure

1. From a text editor, create a POM file to define build dependencies. For example,

   <?xml version="1.0" encoding="UTF-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
     <groupId>org.teiid</groupId>
     <artifactId>dv-customer</artifactId>
     <name>dv-customer</name>
     <description>Demo project to showcase maven based vdb</description>
     <packaging>vdb</packaging>
     <version>1.0</version>
   
     <build>
       <plugins>
         <plugin>
           <groupId>org.teiid</groupId>
           <artifactId>vdb-plugin</artifactId>
           <version>1.2.0</version>
           <extensions>true</extensions>
           <executions>
             <execution>
               <goals>
                 <goal>vdb</goal>
               </goals>
             </execution>
           </executions>
         </plugin>
       </plugins>
     </build>
   </project>

2. Create a Maven project to import the virtual database definition from a DDL file. For example:

   vdb-project
   ├── pom.xml
   └── src
       └── main
           └── vdb
               └── vdb.ddl

3. If you do not already have one, create a DDL file to specify the structure of the virtual database, and save it with a .ddl extension to the /src/main/vdb directory of your project. For example vdb.ddl

   The following example shows a sample DDL file for a virtual database that uses a postgreSQL data source:

   Example: vdb.ddl

   CREATE DATABASE customer OPTIONS (ANNOTATION 'Customer VDB');
   USE DATABASE customer;
   
   CREATE FOREIGN DATA WRAPPER postgresql;
   CREATE SERVER sampledb TYPE 'NONE' FOREIGN DATA WRAPPER postgresql;
   
   CREATE SCHEMA accounts SERVER sampledb;
   CREATE VIRTUAL SCHEMA portfolio;
   
   SET SCHEMA accounts;
   IMPORT FOREIGN SCHEMA public FROM SERVER sampledb INTO accounts OPTIONS("importer.useFullSchemaName" 'false');
   
   SET SCHEMA portfolio;
   
   CREATE VIEW CustomerZip(id bigint PRIMARY KEY, name string, ssn string, zip string) AS
      SELECT c.ID as id, c.NAME as name, c.SSN as ssn, a.ZIP as zip
      FROM accounts.CUSTOMER c LEFT OUTER JOIN accounts.ADDRESS a
      ON c.ID = a.CUSTOMER_ID;

   For information about how to use DDL to define a virtual database, see DDL metadata for schema objects in the Data Virtualization Reference. Defining the complete DDL is beyond the scope of this document.

4. Build the virtual database artifact. Open a terminal window to the root folder of your Maven project, and type the following command:

   mvn clean install

   The command generates a ${project.name}-$2020.Q1.vdb file in your target repository.

5. Deploy the artifact to a remote repository by typing the following command:

   mvn clean install deploy