== Access OpenApi based Rest Service using SQL

This guide shows how add `OpenApi` or `Swagger` based Rest service in your the Teiid Spring Boot application as a source, such that the data from this Rest service can be read as part of the VDB and integrated with other sources or just accessed using the SQL. 

The virtual database at startup time will fetch the api document and parse and make all the api available as SQL stored procedures for you to execute.

To use the service in the application you need following dependencies to your pom.xml file.

[source, xml]
----
<dependency>
    <groupId>org.teiid</groupId>
    <artifactId>spring-data-rest</artifactId>
</dependency>
<dependency>
    <groupId>org.teiid</groupId>
    <artifactId>translator-swagger</artifactId>
</dependency>
----

To represent the connection to the Rest service, add the following Java class to your application. The package name assumed as `org.example`.

[source,java]
.*src/main/java/org/example/DataSources.java*
----
package org.example;

@Configuration
public class DataSources {
    @ConfigurationProperties(prefix = "spring.teiid.rest.oservice")
    @Bean
    public RestConnectionFactory oservice() {
        return new RestConnectionFactory();
    }
}
----

Note: Please note that the name `oservice` in the annotation and as method name must match exactly to that of the `SERVER` defined in the VDB file. See example below.

In above code `spring.teiid.rest` defines the prefix of properties that can be used to configure the connection. Each property is postfixed with unique name like `oservice` as in below example to identify the name of the connection then followed by the property name.

[source, text]
.*API Endpoint*
```
spring.teiid.rest.oservice.importer.metadataUrl=http://localhost:8080/openapi.json
```

The virtual database at startup time will use the above link to fetch the api document and parse and make all the api available as SQL stored procedures for you to execute.

To configure a REST service with different security profiles use the following properties

[source, text]
.*No Security*
----
spring.teiid.rest.oservice.endpoint=<endpoint>
----


[source, text]
.*Http Basic Authentication*
----
spring.teiid.rest.oservice.securityType=http-basic
spring.teiid.rest.oservice.userName = <user>
spring.teiid.rest.oservice.password = <password>
spring.teiid.rest.oservice.endpoint=<endpoint>
----


[source, text]
.*OAuth2/Openid-Connect Authentication*
----
spring.teiid.rest.oservice.securityType=openid-connect
spring.teiid.rest.oservice.clientId=<client-id>
spring.teiid.rest.oservice.clientSecret=<client-secret>
spring.teiid.rest.oservice.userName = <user>
spring.teiid.rest.oservice.password = <password>
spring.teiid.rest.oservice.refreshToken=<token>
spring.teiid.rest.oservice.authorizeUrl=<authurl>
spring.teiid.rest.oservice.accessTokenUrl=<access-tken-utl>
spring.teiid.rest.oservice.endpoint=<endpoint>
----

Note: in above either `userName` and `password` are required or you can supply the `refreshToken`. If both are supplied, `refreshToken` is given preference.

=== VDB Example
With above the connection is configured, but to use that connection as source in the VDB, one need to configure VDB like below.

[source,SQL, numbered]
----
CREATE DATABASE customer OPTIONS (ANNOTATION 'Customer VDB');
USE DATABASE customer;
SET NAMESPACE 'http://teiid.org/rest' AS REST;

CREATE FOREIGN DATA WRAPPER openapi;
CREATE SERVER oservice TYPE 'oservice' FOREIGN DATA WRAPPER openapi;
CREATE SCHEMA myservice SERVER oservice;
IMPORT FOREIGN SCHEMA myservice FROM SERVER oservice INTO portfolio;

----

The above VDB when deployed will have all the API available as stored procedures, which you can execute as

```
exec myApi('foo', 'bar');
or 
select X.* from (exec myApi('foo', 'bar') as X);
```