Data Resource - SQL Request

About

A SQL request is a request against a SQL database. It's the runtime data resource form of SQL.

In a SQL request processing, Tabulify will:

parse the Sql scripts (a file or string)
extract the SQL statements
execute them one by one
and returns the result

Type

Sql scripts can be stored in

sql_files - one file, one script
any other_resources where one record is one script

SQL Files

SQL files will become SQL Request if they are represented in an runtime data uri

For instance:

(top-5-clients.sql@cd)@sqlite

where:

top-5-clients.sql is a sql file
located in the current directory (cd)
and should be executed against a sqlite database

Other resources

Sql scripts are generally stored in a SQL File but you can get them from any resource.

If the resource has:

only one column, this column is supposed to store the SQL Scripts
otherwise we search for the column named sql

Example:

a view where all scripts are returned in the sql column

(statements_view@sqlite)@sqlite

a select where all scripts are returned in the sql column

((select_statement.sql@cd)@sqlite)@sqlite

a csv file where all statements are on one line

(statements.csv@cd)@sqlite

List

Tabulify supports all SQL Statements:

Data Manipulation Language (DML) - ie SELECT/UPDATE/INSERT/MERGE statement
Data Definition Language (DDL) - ie CREATE/ALTER/DROP statement
SQL Procedures- ie CALL/DO/DECLARE/BEGIN/END statement

Manifest

Example of kind manifest

kind: sql-request
spec:
  data-uri: (script.sql@cd)@database
  data-def:
    parameters:
      - value: value1
        direction: IN
        type: varchar
      - value: value2
        direction: OUT
        type: integer
      - ...
    strict-execution: true

where:

data-uri is an runtime data-uri
data-def is a data definition that accepts
- parameters - A list of parameters if the script contains SQL Parameterized Statement
- strict-execution: if true, the execution will terminate when a SQL statement returns an error. Default to the global tabulify value.

Execution

See Runtime Execution

Limitation: 1 SQL Request, 1 Result Set

Tabulify is based on resource chaining (ie one resource, one response). Therefore, a SQL Request should return only one result set.

It means that in a SQL Request, you:

cannot have:
- multiple Select statements
- multiple DML statements that are using the RETURNING clause
- multiple Sql procedure with OUT parameters
can have:
- multiple DML statements that are NOT using the RETURNING clause
- multiple DDL statements
- multiple DCL statements (GRANT/REVOKE)
- multiple Sql procedure without OUT parameters

If you want to execute a SQL Request with this limitation, you need to split the statements before running them in a pipeline

Note that not all database supports returning multiple result set. For instance, SQL Server does not support it.

Note

Execution Warnings

When executing a query, the database engine may return warnings.

A warning may come from:

the database engine
the user code (For example, the sql server print statement create a warning)

Tabulify sends this warnings to the standard error stream so that:

the user will see them in a terminal
the warnings will not interfere with the normal standard output stream

Why Request?

When creating this runtime, we needed to give it a name.

We went through the following names in order:

query because this is the most common name used in the documentation
script because we wanted it to be the counterpart of the file system script
fetch because we wanted to indicate that it was a remote call with a result

They were not selected:

query because in the SQL world, it's an alias for a SELECT statement as seen in the executeQuery method.
script because it has a local meaning and does not reflect the fact that this is a call to a remote service
fetch because it's a web word and is not well-known in the data world.

Finally, we ended up with request because:

it reflects the fact that this is a call to a remote service
the result given by the java driver is known as a result set