In decisionpatterns/sql.template: Light-weight, mustache-based SQL templating

SQLTemplate is an application of Mustache"logic-less templates" to Structured Query Language (SQL) with a few added added enhancments. In additions to the benefits of Mustache, SQL templates provides a features to (de)activate parts of a SQL Template. This features promotes the query reuse and sharing by different users, programs, languages and applications.

Features

SQLXXX has the following FEATURES.

• Files are:
• plain text
• valid SQL -- run as-is, as a default query
• Multi-purpose; simple logic allow for use from multiple languages/applications
• Portable to different application and languages that use the SQLXXX syntax
• Reduce inconsistencies across applications
• Patterned after existing standards
• SQL
• Mustache
• STRICT or PERMISSIVE: can warn or raise errors if constraints are not supplied

Key Functions

• sql_read : Read SQL statement from a connection or string
• sql_render : like whisker render, parse the template and substitute values from key-value pairs
• sql_activate : Activate (uncomment) part(s) of a SQL template
• sql_strip_comments : Strip comments from a SQL STATEMENT

Background

Strictly speaking any SQL template solution needs to provide several capabilities:

• parameter substitution
• variable checking
• conditional logic / query activation ...
• e.g. only print if
• ? Only print line if variable substitutes
• Default is strict: check vars / prior to substitutubs

Parameter Subsitution

Mustache (via the whisker/whisker.tools packages) is the easiest and probably most ubiquitous way to do parameter substitution.

SQL Template:
    SELECT * 
    FROM table 
    WHERE name = {{name}}

Hash:
    {"name": "foo"}

Parameter substitution will not work if: - foo valid: performs substition - foo missing: raises error - foo FALSE:

# These are really tests 
tmpl <- "SELECT * FROM TABLE WHERE name = '{{name}}'"
data <- fromJSON('{"name": "foo"}')

data1 = list( name = "foo" )
data2 = list( name = FALSE )
data3 = list() 
data4 = list( other_name = "roger") 

whisker.render(tmpl,data1)  # Shown  # Works 
whisker.render(tmpl,data2)  # FALSE not-shown
whisker.render(tmpl,data3)  # DOES not-sexist
whisker.render(tmpl,data4)  # No substitution

(Un)Masking, unmasking, activating

The second part of a sql template needs to support conditional logic, namely turning on or off logic. This can surprisingly be done with

To get whisker to work as a template engine, we need to have a convention for using comments to indicate

Template:
  SELECT
    A  
    --#foo:, B  -- RENDERS IF DEFINED OTHERWISE NOT
  FROM table 1

Hash:
   { "foo" : true }

Here, two things special are done.

tmpl <- "
      SELECT
        A,  
        --foo: B,  -- RENDERS IF DEFINED OTHERWISE NOT
      FROM table 1
"

sql.template::sql_render( tmpl, list(foo=TRUE) )

Poor interface

sql.template is poorly designed. It requires two arguments to sql_render to render text:

• tags = ... determine what is uncommented
• data determines that is rendered

It may be possible to perform both in one step by using only the data argument. If the value is exists/than the line should be uncommented.

Order of operations

Current: Uncomment, substitute, strip ...

Proposed: Preform all substitutions first! substitute uncomment * strip remaining comments (optional)

Functionality

sql.template provides for the following functionality:

• Uncommenting, especially SELECT clause to select more columns WHERE clause to filter rows

• Parameter substitute using mustache

• BOTH

Uncommenting

Uncommenting can be activated if/when a value exists in data. For example:

• Evaluate a condition in line to true in /{{TRUE}}: some sql / --> some sql
  --{{TRUE}}: some sql --> some sql

  If whisker.render if the variable does not exist, nothing is inserted --r: --> --: (which is a nice way to indicate that nothing was substituted.)

Parameterized comments are uncommented if the value is evaluated.

Uses separet parameters:

/*{{filter_a}}: AND a = {{a}} */
--{{filter_b}}: AND b = {{b}}


data=list( filter_a=TRUE, filter_b=FALSE )

Using the parameter alone

This is the perfered method

--{{r}}: AND r = {{r}}
/*r: ... */

data = list( r = 1 )

NOTE: list( r=FALSE ) will also get rendered.

Required Changes

Order of operations

Currently, sql.template uncommented then substitutes. This will have to change.

Alternatives

• No parameters, parameter sets/unsets the SQL statement

  SELECT name /{{TRUE}}, date/ FROM TABLE

  WHERE 1 = 1 AND NAME = {{NAME}}

• Parameter can be filled -> gets filled

• Multiple parameters defined.

Most desirable

The most desirable situation is to have a SQL parser that can turn a basic SQL statement into a code structure that can subsequently get modified.

"Select * from table" %>% where( name = "Fred" )

I don't believe that dplyr provides this interface. sql.template uncommenting is a work-around this feature.

DPLY vs SQLTemplate

One comment that is often made is why not just use dplyr. To understand the rational, it is important to make some distinction between dplyr and SQLTemplate.

| dplyr | SQLTemple | |----------------------------------|------------------------------| | Build query using logic pipeline | Build Query in text template | |-----------------------------------|------------------------------| | Non-transportable code (as of now)| Language/Application agnostic | | just a standard |

DPLYR

• Build your query through a logic pipeline.
• Non-transportable code (as of now)

SQLMustache

• Language Independent
• logic-late

• Runs and parses as-is.

  SELECT name --addr: addr FROM person --addr: INNER JOIN address on personId WHERE 1=1 --addr: AND addr like '%MOCKING BIRD%'

TRUTHY VALUES exists, ! NULL, is not empty or NA

decisionpatterns/sql.template documentation built on July 6, 2020, 8:41 a.m.