This is an R package and Java library for rendering parameterized SQL, and translating it to different SQL dialects.
Clone or download
Latest commit b87d04f Aug 17, 2018
Permalink
Failed to load latest commit information.
.settings Now using JRE6 for maximum compatibility. Sep 19, 2014
R Fixed camelCaseToSnakeCase to insert underscore prior to numbers. May 24, 2018
docs Increasing version number. Updating roxygen and pkgdown. Preparing fo… Aug 17, 2018
extras Increasing version number. Updating roxygen and pkgdown. Preparing fo… Aug 17, 2018
inst Regenerated jar Aug 17, 2018
java Changing order of oracle translation rules to prevent added quotes (f… May 22, 2018
man Increasing version number. Updating roxygen and pkgdown. Preparing fo… Aug 17, 2018
tests Merge branch 'master' into oracle_table_aliases Aug 16, 2018
vignettes Adding boolean operators to vignette Aug 13, 2018
.Rbuildignore Adding auto-tagging Mar 19, 2018
.classpath Preparing for CRAN submission Sep 15, 2017
.gitignore ignore IntelliJ settings Jul 23, 2015
.project Ported from C++ to Java for simultaneous deployment in Node servers. Sep 18, 2014
.travis.yml Fixing auto-tagging Mar 27, 2018
DESCRIPTION Increasing version number. Updating roxygen and pkgdown. Preparing fo… Aug 17, 2018
NAMESPACE Tidied up function that launches the Shiny app. Dependencies are in s… May 23, 2017
NEWS Increasing version number. Updating roxygen and pkgdown. Preparing fo… Aug 17, 2018
README.md Preparing for CRAN release May 28, 2018
SqlRender.Rproj Remove '--no-multiarch'. Jun 13, 2017
compare_versions Changing operator to detect version increase Apr 19, 2018
cran-comments.md Increasing version number. Updating roxygen and pkgdown. Preparing fo… Aug 17, 2018
deploy.sh better drat commit message Feb 10, 2016
license_header_stub.txt Preparing for CRAN submission Aug 7, 2016
pom.xml Updating version number in pom Aug 17, 2018

README.md

SqlRender

CRAN_Status_Badge CRAN_Status_Badge

Introduction

This is an R package for rendering parameterized SQL, and translating it to different SQL dialects. SqlRender can also be used as a stand-alone Java library and a command-line executable.

Features

  • Supports a simple markup syntax for making SQL parameterized, and renders parameterized SQL (containing the markup syntax) to executable SQL
  • The syntax supports defining default parameter values
  • The syntax supports if-then-else structures
  • Has functions for translating SQL from one dialect (Microsoft SQL Server) to other dialects (Oracle, PostgreSQL, Amazon RedShift, Impala, IBM Netezza, Google BigQuery, and Microsoft PDW)
  • Can be used as R package, Java library, or as stand-alone executable through a command-line interface

Examples

This exampe shows the use of parameters, as well as SqlRender's {if} ? {then} : {else} syntax:

sql <- renderSql("SELECT * FROM @a; {@b != ''}?{USE @b;}", a = "my_table", b = "my_schema")$sql

will produce the variable sql containing this value:

"SELECT * FROM my_table; USE my_schema;"

subsequently running this code

sql <- translateSql(sql, "sql server", "oracle")$sql

will produce the variable sql containing this value:

"SELECT * FROM my_table; ALTER SESSION SET current_schema =  my_schema;"

Technology

The SqlRender package is an R package wrapped around a Java library. The rJava package is used as interface.

The Java library is available as a JAR file.

System Requirements

Running the package requires R with the package rJava installed. Also requires Java 1.6 or higher.

Dependencies

  • There are no dependencies.

Getting Started

R package

In R, to install the latest stable version, install from CRAN:

install.packages("SqlRender")

To install the latest development version, install from GitHub:

install.packages("devtools")
devtools::install_github("ohdsi/SqlRender")

Once installed, you can try out SqlRender in a Shiny app that comes with the package:

library(SqlRender)
launchSqlRenderDeveloper()

Java library

You can fetch the JAR file in the inst/java folder of this repository, or use Maven:

  1. First add the SqlRender repository so that maven can find and download the SqlRender artifact automatically:
<repositories>
	<repository>
		<id>ohdsi</id>
		<name>repo.ohdsi.org</name>
		<url>http://repo.ohdsi.org:8085/nexus/content/repositories/releases</url>
	</repository>
	<repository>
		<id>ohdsi.snapshots</id>
		<name>repo.ohdsi.org-snapshots</name>
		<url>http://repo.ohdsi.org:8085/nexus/content/repositories/snapshots</url>
		<releases>
			<enabled>false</enabled>
		</releases>
		<snapshots>
			<enabled>true</enabled>
		</snapshots>
	</repository>
</repositories>

2: Include the SqlRender dependency in your pom.xml

<dependency>
	<groupId>org.ohdsi.sql</groupId>
	<artifactId>SqlRender</artifactId>
	<version>1.0.0-SNAPSHOT</version>
</dependency>

Command-line executable

You can fetch the JAR file in the inst/java folder of this repository, or use Maven as described above. Run this from the command line to get a list of options:

java -jar SqlRender.jar ?

Getting Involved

License

SqlRender is licensed under Apache License 2.0

Development

SqlRender is being developed in R Studio.

Development status

Build Status codecov.io

Stable. The code is actively being used in several projects.

Acknowledgements

  • This project is supported in part through the National Science Foundation grant IIS 1251151.