Teiid

https://teiid.github.io/teiid-documents/16.0.x/content
Admin
Teiid is a data virtualization system that provides a unified SQL interface to access and integrate...
Tokens:362,531
Snippets:3,472
Trust Score:8.3
Update:9 months ago
Show doc for...
Tokens:
Raw
### Example VDB with External Materialized View Configuration (XML)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/caching/External_Materialization

An example VDB XML configuration showcasing external materialized view options. It defines a source model, a virtual model with a 'Person' view configured for materialization, and a physical model for the materialized data. This setup uses TTL_SNAPSHOT for refresh and specifies options for managing the materialized view, including the status table and load number column.

```xml
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="example" version="1">
    <model name="Source">
        <source name="source" translator-name="h2" connection-jndi-name="java:/my-ds" />
    </model>

    <model name="ViewModel" type="VIRTUAL">
        <metadata type="DDL"><![CDATA[
          CREATE VIEW Person (
            id varchar,
            name varchar,
            dob date,
            PRIMARY KEY (id)
          ) OPTIONS (
            MATERIALIZED 'TRUE', UPDATABLE 'TRUE',
            MATERIALIZED_TABLE 'materialized.PersonCached',
            "teiid_rel:MATVIEW_TTL" 20000,
            "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
            "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
            "teiid_rel:MATVIEW_STATUS_TABLE" 'materialized.status'
          )
          AS
            SELECT p.id, p.name, p.dob FROM Source.Person AS p;
        ]]>
        </metadata>
    </model>

    <model name="materialized" type="PHYSICAL">
        <source name="matview" translator-name="h2" connection-jndi-name="java:/matview-ds" />
        <metadata type="DDL"><![CDATA[
          CREATE VIEW PersonCached (
            id varchar,
            name varchar,
            dob date,
            LoadNumber long,
            PRIMARY KEY (id)
          );
          CREATE TABLE status (
            VDBName varchar(50) not null,
            VDBVersion varchar(50) not null,
            SchemaName varchar(50) not null,
            Name varchar(256) not null,
            TargetSchemaName varchar(50),
            TargetName varchar(256) not null,
            Valid boolean not null,
            LoadState varchar(25) not null,
            Cardinality long,
            Updated timestamp not null,
            LoadNumber long not null,
            NodeName varchar(25) not null,
            StaleCount long,
            PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
          ) OPTIONS (UPDATABLE true);
        ]]>
        </metadata>
    </model>
</vdb>
```

--------------------------------

### Example: Using Local Temporary Tables (SQL)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_local-temporary-tables

Demonstrates a complete workflow for using local temporary tables. It includes creating a temporary table, loading data from multiple sources, inserting a manual record, and querying the temporary table combined with another source.

```sql
CREATE LOCAL TEMPORARY TABLE TEMP (a integer, b integer, c integer);
SELECT * INTO temp FROM Src1;
SELECT * INTO temp FROM Src2;
INSERT INTO temp VALUES (1,2,3);
SELECT a,b,c FROM Src3, temp WHERE Src3.a = temp.b;
```

--------------------------------

### Teiid Embedded Security Helper Implementation

Source: https://teiid.github.io/teiid-documents/16.0.x/content/embedded/Embedded_Guide

Demonstrates setting up authentication for Teiid Embedded Server using a custom `SecurityHelper`. This example utilizes user and role properties files for defining credentials and an application policy for matching the security domain.

```java
// Assume EmbeddedSecurityHelper is an implementation of org.teiid.security.SecurityHelper
EmbeddedSecurityHelper securityHelper = new EmbeddedSecurityHelper();

EmbeddedConfiguration config = new EmbeddedConfiguration();
config.setSecurityHelper(securityHelper);
config.setSecurityDomain("my-security-domain"); // Matches policy name in authentication.conf

EmbeddedServer server = new EmbeddedServer();
server.start(config);
```

--------------------------------

### Build PostgreSQL ODBC Driver from Source (*nix)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/client-dev/Installing_the_ODBC_Driver_Client

Commands to build and install the PostgreSQL ODBC driver from source files on *nix-based systems. This process involves extracting the tarball, navigating to the directory, configuring the build, compiling, and installing the driver. Superuser privileges may be required for the 'make install' step.

```bash
% tar -zxvf psqlodbc-xx.xx.xxxx.tar.gz
% cd psqlodbc-xx.xx.xxxx
% ./configure
% make
% make install
```

--------------------------------

### Example EXPLAIN Statement Usage

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_explain-statement

An example demonstrating how to use the EXPLAIN statement with the ANALYZE option to get a text-formatted plan from an actual execution of a select query.

```sql
EXPLAIN (analyze) select * from really_complicated_view
```

--------------------------------

### Simple Node.js Teiid Data Access Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/client-dev/Node_Integration

This Node.js code snippet demonstrates how to connect to a Teiid VDB using the 'pg' package and execute a SQL query. It requires the 'pg' package to be installed. The example connects to a 'northwind' VDB and queries the 'Customers' table, then logs the results and closes the connection. Connection details can also be managed via environment variables.

```javascript
const { Client } = require('pg')
const client = new Client({
    user: 'user',
    host: 'localhost',
    database: 'northwind',
    password: 'secretpassword',
    port: 35432,
})
client.connect()

client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers', (err, res) => {
    console.log(err, res)
    client.end()
})
```

--------------------------------

### Declare Statement Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

An example of a DECLARE statement, showing how to declare a string variable named 'x' and initialize it with the value 'a'.

```SQL-like
DECLARE STRING x = 'a'
```

--------------------------------

### Teiid Example Command Statements

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_command-statement

Examples demonstrating the execution of SELECT and INSERT statements using Teiid's command statement syntax.

```sql
SELECT * FROM MySchema.MyTable WHERE ColA > 100 WITHOUT RETURN;
INSERT INTO MySchema.MyTable (ColA,ColB) VALUES (50, 'hi');
```

--------------------------------

### Install Keycloak Wildfly Adapter

Source: https://teiid.github.io/teiid-documents/16.0.x/content/security/OAuth2_Based_Security_For_OData_Using_KeyCloak

This command installs the Keycloak adapter for Wildfly by unzipping the distribution over the Teiid server installation. Ensure you replace `${version}` with the actual version of the Keycloak adapter.

```bash
cd $WILDFLY_HOME
unzip keycloak-wildfly-adapter-dist-${version}.zip
```

--------------------------------

### Example Foreign Table Creation

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_ddl-metadata-for-schema-objects

Provides concrete examples of creating foreign tables in Teiid. The first example shows a simple 'Customer' table, while the second demonstrates an 'Order' table with a foreign key constraint and options.

```sql
CREATE FOREIGN TABLE Customer (
    id integer PRIMARY KEY,
    firstname varchar(25),
    lastname varchar(25),
    dob timestamp);

CREATE FOREIGN TABLE Order (
    id integer PRIMARY KEY,
    customerid integer OPTIONS(ANNOTATION 'Customer primary key'),
    saledate date,
    amount decimal(25,4),
    CONSTRAINT CUSTOMER_FK FOREIGN KEY(customerid) REFERENCES Customer(id)
 ) OPTIONS(UPDATABLE true, ANNOTATION 'Orders Table');

```

--------------------------------

### Example users.properties file

Source: https://teiid.github.io/teiid-documents/16.0.x/content/security/LoginModules

An example of a users.properties file used by the UsersRolesLoginModule. It maps usernames to their corresponding passwords. Each line follows the format 'username=password'.

```properties
# A users.properties file for use with the UsersRolesLoginModule
# username=password

fred=password
george=password
...
```

--------------------------------

### Teiid Basic WITH Clause Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_with-clause

A simple example demonstrating the usage of the WITH clause to define a common table 'n' and then select from it.

```sql
WITH n (x) AS (select col from tbl) select x from n, n as n1
```

--------------------------------

### Named Parameter List Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

An example of a named parameter list, showing how to pass arguments to a procedure by name. 'param1' is assigned 'x' and 'param2' is assigned 1.

```SQL-like
param1 => 'x', param2 => 1
```

--------------------------------

### Verify Installed ODBC Drivers on Linux/Unix

Source: https://teiid.github.io/teiid-documents/16.0.x/content/client-dev/Configuring_the_Data_Source_Name_DSN

This command lists all the ODBC drivers currently installed on a Linux or Unix system, which is useful for verifying the PostgreSQL driver installation.

```bash
odbcinst -q -d
```

--------------------------------

### Customer Document Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_couchbase-translator

Example of a Customer document structure in Couchbase, including attributes like ID, Name, SavedAddresses (an array), and type.

```json
{
  "ID": "Customer_12345",
  "Name": "John Doe",
  "SavedAddresses": [
    "123 Main St.",
    "456 1st Ave"
  ],
  "type": "Customer"
}
```

--------------------------------

### Assignment Statement Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

An example of an assignment statement, demonstrating how to assign the string value 'b' to a variable named 'x'.

```SQL-like
x = 'b'
```

--------------------------------

### While Statement Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

An example of a WHILE loop, illustrating the syntax for conditional execution. The loop continues as long as the condition (var) is true.

```SQL-like
WHILE (var) BEGIN ... END
```

--------------------------------

### Return Statement Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

An example of a RETURN statement, showing how to return a value from a procedure. In this case, it returns the integer value 1.

```SQL-like
RETURN 1
```

--------------------------------

### Set Clause List Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

An example of a set clause list, showing how to define multiple assignments for an UPDATE statement. Here, 'col1' is set to 'x' and 'col2' to 'y'.

```SQL-like
col1 = 'x', col2 = 'y' ...
```

--------------------------------

### Order Document Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_couchbase-translator

Example of an Order document structure in Couchbase, featuring attributes like CreditCard (an object), CustomerID, Items (an array of objects), Name, and type.

```json
{
  "CreditCard": {
    "CVN": 123,
    "CardNumber": "4111 1111 1111 111",
    "Expiry": "12/12",
    "Type": "Visa"
  },
  "CustomerID": "Customer_12345",
  "Items": [
    {
      "ItemID": 89123,
      "Quantity": 1
    },
    {
      "ItemID": 92312,
      "Quantity": 5
    }
  ],
  "Name": "Air Ticket",
  "type": "Order"
}
```

--------------------------------

### Get Start Point of LineString with ST_StartPoint

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_spatial-functions

Extracts the start Point geometry of a LineString. Returns null if the input geometry is not a LineString.

```sql
ST_StartPoint(geom)
```

--------------------------------

### Apply General and Specific Source Hints in Teiid

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_federated-optimizations

Provides examples of applying source hints in Teiid queries. The first example shows a general hint, while the second demonstrates combining a general hint with a source-specific hint for a particular data source like Oracle.

```sql
SELECT /*+ sh:'general hint' */ ...
```

```sql
SELECT /*+ sh KEEP ALIASES:'general hint' my-oracle:'oracle hint' */ ...
```

--------------------------------

### Example Text-Based Query Plan with Statistics

Source: https://teiid.github.io/teiid-documents/16.0.x/content/admin/Diagnosing_Issues

This snippet demonstrates the structure of a query plan presented in a text format, including detailed statistics for each node. It highlights key metrics like 'Node Output Rows' and 'Node Next Batch Process Time' which are crucial for performance tuning.

```text
ProjectNode
  + Relational Node ID:6
  + Output Columns:x (double)
  + Statistics:
    0: Node Output Rows: 6
    1: Node Next Batch Process Time: 2
    2: Node Cumulative Next Batch Process Time: 2
    3: Node Cumulative Process Time: 2
    4: Node Next Batch Calls: 8
    5: Node Blocks: 7
  + Cost Estimates:Estimated Node Cardinality: -1.0
  + Child 0:
    AccessNode
      + Relational Node ID:7
      + Output Columns
      + Statistics:
        0: Node Output Rows: 6
        1: Node Next Batch Process Time: 0
        2: Node Cumulative Next Batch Process Time: 0
        3: Node Cumulative Process Time: 0
        4: Node Next Batch Calls: 2
        5: Node Blocks: 1
...
```

--------------------------------

### Example: Generate Teiid Translator Project

Source: https://teiid.github.io/teiid-documents/16.0.x/content/dev/Archetype_Template_Translator_Project

This is an example command demonstrating how to generate a Teiid translator project with specific values for the parameters. After execution, Maven will prompt for confirmation of the properties.

```bash
mvn archetype:generate \
  -DarchetypeGroupId=org.teiid.arche-types \
  -DarchetypeArtifactId=translator-archetype \
  -DarchetypeVersion= \
  -DgroupId=org.example \
  -DartifactId=translator-type \
  -Dpackage=org.example.translator.type \
  -Dversion=0.0.1-SNAPSHOT \
  -Dtranslator-type=type \
  -Dtranslator-name=Type \
  -Dteiid-version=16.0.0
```

--------------------------------

### Boolean Primary - LIKE Predicate Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

The boolean primary can include various predicates, such as the LIKE predicate for pattern matching. It allows filtering based on string patterns. For example, 'col LIKE \'a%\'' selects rows where the 'col' column starts with the letter 'a'.

```sql
col LIKE 'a%'
```

--------------------------------

### Create View with Materialization Options

Source: https://teiid.github.io/teiid-documents/16.0.x/content/caching/External_Materialization

Example of creating a view with options to enable materialization, specify the cached table, and set properties for time-to-live and management.

```sql
CREATE VIEW Person (
    id varchar,
    name varchar,
    dob date,
    PRIMARY KEY (id)
  ) OPTIONS (
    MATERIALIZED 'TRUE',
    UPDATABLE 'TRUE',
    MATERIALIZED_TABLE 'materialized.PersonCached',
    "teiid_rel:MATVIEW_TTL" 20000,
    "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
    "teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
    "teiid_rel:MATVIEW_STATUS_TABLE" 'materialized.status'
  )
  AS
    SELECT p.id, p.name, p.dob FROM Source.Person AS p;
```

--------------------------------

### Get Single Document by ID with Teiid

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_couchbase-translator

Example of using the getDocument procedure in Teiid to retrieve a specific Couchbase document by its exact ID. This procedure requires the document ID and the keyspace name.

```sql
call getDocument('customer-1', 'test')
```

--------------------------------

### Get Documents by Pattern with Teiid

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_couchbase-translator

Example of using the getDocuments procedure in Teiid to retrieve Couchbase documents matching a given ID pattern. It requires the document ID pattern and the keyspace name.

```sql
call getDocuments('customer%', 'test')
```

--------------------------------

### Add Translator with Pre-initialized ExecutionFactory (Java)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/embedded/Embedded_Guide

Registers a pre-initialized translator instance with the EmbeddedServer using a specific name. The ExecutionFactory's start() method must have been called prior to this.

```java
H2ExecutionFactory ef = new H2ExecutionFactory()
ef.setSupportsDirectQueryProcedure(true);
ef.start();
es.addTranslator("translator-h2", ef);
```

--------------------------------

### Calling InvokeHTTP Procedure - Teiid Web Services Translator

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_web-services-translator

Shows an example of calling the 'invokeHttp' procedure using named parameter syntax, specifying the HTTP method as 'GET'.

```sql
call invokeHttp(action=>'GET')
```

--------------------------------

### Executing a Custom Visitor with Teiid's Language Framework

Source: https://teiid.github.io/teiid-documents/16.0.x/content/dev/Command_Language

Illustrates how to execute a custom visitor (e.g., MyVisitor) on a language object tree using Teiid's DelegatingHierarchyVisitor for pre-order traversal. It shows how to initialize the visitor and retrieve collected state after visitation.

```java
// Get object tree
LanguageObject objectTree = …

// Create your visitor initialize as necessary
MyVisitor visitor = new MyVisitor();

// Call the visitor using pre-order visitation
DelegatingHierarchyVisitor.preOrderVisit(visitor, objectTree);

// Retrieve state collected while visiting
int count = visitor.getCount();
```

--------------------------------

### VDB Zip Deployment Structure Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_developing-vdb

Illustrates the standard directory structure for a VDB zip deployment. It shows the placement of the main vdb.xml file, DDL schemas, and external libraries (JARs).

```text
/META-INF
   vdb.xml
/ddl
   schema1.ddl
/lib
   some-udf.jar
```

--------------------------------

### Calling Invoke Procedure - Teiid Web Services Translator

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_web-services-translator

Demonstrates how to call the 'invoke' procedure using named parameter syntax for clarity. This example specifies the binding as 'HTTP' and the action as 'GET'.

```sql
call invoke(binding=>'HTTP', action=>'GET')
```

--------------------------------

### SQL Equivalent for Data Access (Teiid OData)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/client-dev/OData4_Support

Provides the equivalent SQL query for accessing the 'customers' table in the 'NW' model within the 'northwind' VDB, corresponding to the HTTP GET example.

```sql
SELECT * FROM NW.customers
```

--------------------------------

### Display Query Plan using SET/SHOW Statements

Source: https://teiid.github.io/teiid-documents/16.0.x/content/admin/Diagnosing_Issues

This method allows you to display the query plan by enabling the SHOWPLAN option and then executing a SELECT statement followed by a SHOW PLAN command. It's a straightforward way to inspect the plan during development and debugging.

```sql
SET SHOWPLAN ON
SELECT ...
SHOW PLAN
```

--------------------------------

### Example roles.properties file

Source: https://teiid.github.io/teiid-documents/16.0.x/content/security/LoginModules

An example of a roles.properties file used by the UsersRolesLoginModule. It maps usernames to a comma-separated list of their assigned roles. Each line follows the format 'username=role1,role2,...'.

```properties
# A roles.properties file for use with the UsersRolesLoginModule
# username=role1,role2,...

data_role_1=fred,sally
data_role_2=george
```

--------------------------------

### Teiid Resource Adapter Configuration for OAuth

Source: https://teiid.github.io/teiid-documents/16.0.x/content/admin/OAuth_Authentication_With_REST_Based_Services

Example configuration for a Teiid resource adapter (webservice3) to connect to a web service using OAuth security. This snippet shows how to define the connection factory and link it to a previously configured security domain.

```xml
<resource-adapter id="webservice3">
    <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>
    <transaction-support>NoTransaction</transaction-support>
    <connection-definitions>
        <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/googleDS" enabled="true" use-java-context="true" pool-name="teiid-ws-ds">
            <config-property name="SecurityType">
                OAuth
            </config-property>
            <security>
                <security-domain>oauth2-security</security-domain>
            </security>
        </connection-definition>
    </connection-definitions>
</resource-adapter>
```

--------------------------------

### Create Example for Direct Query Procedure

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_salesforce-translators

Shows how to use the 'native' direct query procedure for creating records in Salesforce. This example specifies the table type and attributes, with positional matching for attribute values.

```sql
SELECT x.* FROM
 (call sf_source.native('create;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
```

--------------------------------

### Define Table with Extension Metadata Properties (DDL)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_microsoft-excel-translator

This DDL example illustrates defining a table using extension metadata properties for the Excel translator. It specifies the Excel document name, the starting row for records, and the cell number to use for a particular column.

```sql
CREATE DATABASE excelvdb;
USE DATABASE excelvdb;

```

--------------------------------

### LIKE_REGEX Predicate - Regular Expression Match

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

This predicate uses regular expressions for pattern matching, offering more advanced text searching capabilities than the standard LIKE operator. The example 'LIKE_REGEX \'a.*b\'' matches strings that start with 'a', are followed by any characters (including none), and end with 'b'.

```sql
LIKE_REGEX 'a.*b'
```

--------------------------------

### Identifying Pushdown Inhibition in Query Plan

Source: https://teiid.github.io/teiid-documents/16.0.x/content/admin/Diagnosing_Issues

This example illustrates how a query plan might look when pushdown is inhibited. The 'SelectNode' contains criteria that, instead of being pushed down to the 'AccessNode', remain at a higher level, indicating a potential performance bottleneck.

```xml
<node name="SelectNode">...<property name="Criteria"><value>pm1.g1.e2 = 1</value>
  <node name="AccessNode">...<property name="Query"><value>SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1</value>
```

--------------------------------

### MATCH Predicate - LIKE Pattern Matching

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

The MATCH predicate, often using LIKE, performs pattern matching on strings. It supports the ESCAPE keyword to include wildcard characters within the pattern. The example 'LIKE \'a_\'' matches strings that start with 'a' followed by any single character.

```sql
LIKE 'a_'
```

--------------------------------

### LIMIT Clause Syntax Examples (SQL)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_limit-clause

Provides examples of the standard LIMIT clause syntax in SQL, demonstrating how to specify a limit, an offset with a limit, and the SQL 2008 standard OFFSET/FETCH FIRST syntax.

```sql
LIMIT [offset,] limit
```

```sql
LIMIT limit OFFSET offset
```

```sql
[OFFSET offset ROW|ROWS] [FETCH FIRST|NEXT [limit] ROW|ROWS ONLY]
```

--------------------------------

### Implicit Local Temporary Table Creation (SQL)

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_local-temporary-tables

Creates a local temporary table implicitly by referencing it in an INSERT or SELECT INTO statement. If the table does not exist, it is created with columns derived from the provided values or query results. Table names for implicit creation must start with '#'.

```sql
INSERT INTO #name (column, ...) VALUES (value, ...)
```

```sql
INSERT INTO #name [(column, ...)] select c1, c2 from t
```

--------------------------------

### Delimited Statement Example

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

An example of a delimited statement, which is a SQL statement terminated by a semicolon. This specific example shows a SELECT statement.

```SQL-like
SELECT * FROM tbl;
```

--------------------------------

### Create Database

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/BNF_for_SQL_Grammar

Creates a new database with an optional version string and configuration options.

```sql
CREATE DATABASE foo OPTIONS(x 'y')
```

```sql
CREATE DATABASE bar VERSION '1.0'
```

--------------------------------

### Teiid Data Role Creation Examples

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_ddl-deployment-mode

Illustrates how to create data roles using the DDL syntax. The examples show creating a role with specific enterprise roles and another role that is available to any authenticated user.

```sql
CREATE ROLE readWrite WITH FOREIGN ROLE developer,analyst;

CREATE ROLE readOnly WITH ANY AUTHENTICATED;
```

--------------------------------

### Teiid OPTION Clause Usage Examples

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_option-clause

Demonstrates the basic syntax for using the OPTION keyword in Teiid to specify table dependencies and cache behavior. These examples showcase how to apply MAKEDEP and NOCACHE options.

```sql
OPTION MAKEDEP table1
```

```sql
OPTION NOCACHE
```

--------------------------------

### Teiid Assignment Statement Examples

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_assignment-statement

Provides practical examples of Teiid assignment statements. The first example shows assigning a string literal to a variable, while the second demonstrates assigning the result of a SQL query to a variable.

```Teiid
myString = 'Thank you';
```

```Teiid
VARIABLES.x = (SELECT Column1 FROM MySchema.MyTable);
```

--------------------------------

### Teiid POLICY Examples

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_ddl-deployment-mode

Provides examples of creating Teiid policies to enforce specific data access conditions. These examples show overriding previous permissions for a more privileged user and restricting row access based on user-specific criteria.

```sql
CREATE POLICY policyRoleAOrders ON test.Orders TO RoleA USING (amount < 1000 and amount >=1000);

GRANT SELECT ON TABLE test.CustomerOrders TO RoleA;
CREATE POLICY policyCustomerOrders ON test.CustomerOrders TO RoleA USING (name = user());
```

--------------------------------

### Java PreParser Implementation and Service Configuration

Source: https://teiid.github.io/teiid-documents/16.0.x/content/dev/PreParser

Provides an example of a custom Java PreParser implementation using the org.teiid.PreParser interface. It also shows how to configure the pre-parser globally by creating an org.teiid.PreParser file in META-INF/services.

```java
import org.teiid.PreParser;
import org.teiid.api.CommandContext;

package com.something;

public class CustomPreParser implements PreParser {

    @Override
    public String preParse(String command, CommandContext context) {
        //manipulate the command
        return command;
    }
}
```

```properties
com.something.CustomPreParser
```

--------------------------------

### Example VDB XML for Reuse

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_vdb-reuse

Demonstrates how to configure VDB reuse using the 'import-vdb' tag in the vdb.xml file. It shows how to import a 'common' VDB and create a virtual view that references a table from the imported VDB. The 'imported-model.visible' property controls the visibility of imported models.

```xml
<vdb name="reuse" version="1">

    <property name="imported-model.visible" value="false"/>

    <import-vdb name="common" version="1" import-data-policies="false"/>

    <model visible="true" type="VIRTUAL" name="new-model">
         <metadata type = "DDL"><![CDATA[
              CREATE VIEW x (
                y varchar
                ) AS
                  select * from imported-model.tbl;
         ]]>
         </metadata>
    </model>
</vdb>
```

--------------------------------

### Install Keycloak SAML Adapter for Teiid

Source: https://teiid.github.io/teiid-documents/16.0.x/content/security/SAML_Based_Security_For_OData_Using_KeyCloak

Installs the Keycloak SAML adapter for WildFly, which is necessary for Teiid to integrate with Keycloak for SAML authentication. This involves unzipping the adapter distribution over the Teiid server installation directory.

```bash
#!/bin/bash
cd $WILDFLY_HOME
unzip keycloak-saml-wildfly-adapter-dist-${version}.zip
```

--------------------------------

### Teiid OAuth 2.0 Utility Script Execution and Output

Source: https://teiid.github.io/teiid-documents/16.0.x/content/admin/OAuth_Authentication_With_REST_Based_Services

Demonstrates the execution of the teiid-oauth-util.sh script for OAuth 2.0 authentication and the resulting XML fragment for security domain configuration. This utility helps generate access tokens and configuration snippets.

```shell
$./teiid-oauth-util.sh
Select type of OAuth authentication
1) OAuth 1.0A
2) OAuth 2.0

2
=== OAuth 2.0 Workflow ===

Enter the Client ID = 10-xxxjb.apps.googleusercontent.com

Enter the Client Secret = 3L6-xxx-v9xxDlznWq-o

Enter the User Authorization URL = https://accounts.google.com/o/oauth2/auth

Enter scope (hit enter for none) = profile

Cut & Paste the URL in a web browser, and Authticate
Authorize URL  = https://accounts.google.com/o/oauth2/auth?client_id=10-xxxjb.apps.googleusercontent.com&scope=profile&response_type=code&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&state=Auth+URL

Enter Token Secret (Auth Code, Pin) from previous step = 4/z-RT632cr2hf_vYoXd06yIM-xxxxx

Enter the Access Token URL = https://www.googleapis.com/oauth2/v3/token

Refresh Token=1/xxxx_5qzAF52j-EmN2U

Add the following XML into your standalone-teiid.xml file in security-domains subsystem,
and configure data source securty to this domain

<security-domain name="oauth2-security">
    <authentication>
        <login-module code="org.teiid.jboss.oauth.OAuth20LoginModule" flag="required" module="org.jboss.teiid.web.cxf">
            <module-option name="client-id" value="10-xxxjb.apps.googleusercontent.com"/>
            <module-option name="client-secret" value="3L6-xxx-v9xxDlznWq-o"/>
            <module-option name="refresh-token" value="1/xxxx_5qzAF52j-EmN2U"/>
            <module-option name="access-token-uri" value="https://www.googleapis.com/oauth2/v3/token"/>
        </login-module>
    </authentication>
</security-domain>
```

--------------------------------

### JSONTABLE Examples

Source: https://teiid.github.io/teiid-documents/16.0.x/content/reference/r_jsontable

Provides practical examples of using the JSONTABLE function for different querying scenarios.

```APIDOC
## JSONTABLE Examples

### Example 1: Simple Path
Selects data with a simple JsonPath.
```sql
select * from jsontable('{"a": {"id":1}}}', '$.a' COLUMNS id integer) x
```

### Example 2: Nested Table
Uses JSONTABLE as a nested table in a FROM clause.
```sql
select x.* from t, jsontable(t.doc, '$.x.y' COLUMNS first string, second FOR ORDINALITY) x
```

### Example 3: Complicated Paths and Array Processing
Demonstrates handling arrays and using relative paths with array length.
```sql
select x.* from jsontable('[{"firstName": "John", "lastName": "Wayne", "children": []}, {"firstName": "John", "lastName": "Adams", "children":["Sue","Bob"]}]', '$.*' COLUMNS familyName string path '@.lastName', children integer path '@.children.length()' ) x
```
```

--------------------------------

### Deploy WAR to JDV Server using CLI

Source: https://teiid.github.io/teiid-documents/16.0.x/content/security/OAuth2_Based_Security_For_OData_Using_KeyCloak

These commands illustrate deploying the new WAR file to a JDV Server (version 6.3 or greater) using the JBoss CLI. It includes undeploying the old version and deploying the new one, or using a deployment overlay for more granular control. Adjust versions and paths as necessary.

```bash
undeploy teiid-olingo-odata4.war
deploy teiid-web-security/odata-oauth-keycloak/target/teiid-odata-oauth-keycloak-{version}.war
```

```bash
deployment-overlay add --name=myOverlay --content=/WEB-INF/web.xml=teiid-web-security/odata-oauth-keycloak/src/main/webapp/WEB-INF/web.xml,/WEB-INF/jboss-web.xml=teiid-web-security/odata-oauth-keycloak/src/main/webapp/WEB-INF/jboss-web.xml,/META-INF/MANIFEST.MF=teiid-web-security/odata-oauth-keycloak/src/main/webapp/META-INF/MANIFEST.MF,/WEB-INF/keycloak.json=teiid-web-security/odata-oauth-keycloak/src/main/webapp/WEB-INF/keycloak.json /WEB-INF/lib/teiid-odata-oauth-keycloak-{version}.jar=teiid-web-security/odata-oauth-keycloak/src/main/webapp/WEB-INF/lib/teiid-odata-oauth-keycloak-{version}.jar --deployments=teiid-olingo-odata4.war --redeploy-affected
```
Tokens:
Raw