sql_render: Render mustache-style SQL template

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

Description

Read SQL and substitute parameters, use tags to allow for programatic functionality.

Usage

sql_render(
  sql,
  data = NULL,
  tags = getOption("sql.tags"),
  strip.comments = getOption("sql.strip.comments", TRUE),
  render = TRUE,
  ...
)

Arguments

sql	either a connection or character string; see sql() and sql_read()
data	environment, list or named vector. Locations to search for values to substitute into the query. The default, NULL finds the names from the call stack/search path.
tags	character; list of tags to uncomment. See sql_activate(). The default, NULL does not perform any uncommenting.
strip.comments	logical; whether to strip comments from sql; The Default is taken from the global option sql.strip.comments or else it is TRUE
render	logical; whether to make a template replacement using whisker.render (DEFAULT:TRUE)
...	aditional arguments supplied to sql_read()

Details

sql_render renders a SQL template by optionally uncommenting and/or stripping comments followed by substituting template variables using whisker. The process occurs in that order.

When set to FALSE, render will prevent rendering of the template and pass-through the SQL unaltered. This is useful mainly for debugging.

Value

sql; a SQL statement that can then be passed to a DATABASE connection

Uncommenting

See sql_activate().

tags are used to uncomment SQL comments using. The default is provided by global option sql.tags. If unset or NULL, no uncommenting is performed. If an empty character string, "", is provided, all comments are uncommented.

Comment stripping

strip.comments controls the stripping of comments. It is performed by sql_strip_comments(). Since comment stripping occurs after uncommenting, previously uncommented lines are not stripped from the SQL. The default to strip comments since 1) most operations are believed to be automatic in nature and 2) any unfound template variables result in a warning courtesy of whisker.tools. Thus stripping comments means warnings only occur only from evaluated SQL and not comments.

Variable Substitution

SQL is rendered it using whisker.render using the values provided in data or the parent.frame by default. data can be a list or environment that provides the values to be substituted. If a varaible is not found, an warning is given.

Note

sql_render makes no attempt to determine if resulting SQL is valid SQL.

See Also

\link{sql_read`} \cr whisker.render'

Examples

  sql = "select * from {{table}}"
  table = "table1"
  sql_render( sql )

  table = "table_1"
  sql_render( sql )
  sql_render( "select * from {{table}} --r: where ROWNUM < 10 ")

  # with magrittr:
  ## Not run: 
    sql %>% sql_render( c( table = "table4" ) )
  
## End(Not run)

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