Lazy SQL Programming

Helper functions to build SQL statements for dbGetQuery or dbSendQuery under program control. They are intended to increase speed of coding and to reduce coding errors. Arguments are carefully checked, in particular SQL identifiers such as names of tables or columns. More patterns will be added as required.


lazysql

Travis-CI Build Status AppVeyor Build Status Coverage Status

Lazy SQL programming.

Helper functions to build SQL statements under program control for use with dbGetQuery or dbSendQuery. They are intended to increase speed of coding and to reduce coding errors. Arguments are carefully checked, in particular SQL identifiers such as names of tables or columns.

Currently implemented are:

  • date_between Create SQL string to select date between two given dates.
  • in_condition Create SQL string to select values included (or not included) in a set of given values.
  • natural_key Create SQL string for joining on matching natural keys.

More patterns will be added as required.

Examples

date1 <- as.Date("2016-02-22")
date2 <- as.Date("2016-02-11")
 
# SQL expression for date range
(sql_expr1 <- lazysql::date_between("STD_1", c(date1, date2)))
#> [1] "STD_1 between to_date('2016-02-11', 'yyyy-mm-dd') and to_date('2016-02-22', 'yyyy-mm-dd')"
 
# SQL expression for single date
(sql_expr2 <- lazysql::date_between("STD_1", date1))
#> [1] "STD_1 between to_date('2016-02-22', 'yyyy-mm-dd') and to_date('2016-02-22', 'yyyy-mm-dd')"
 
# sample SQL statements
paste("select * from TEST_TABLE where", sql_expr1)
#> [1] "select * from TEST_TABLE where STD_1 between to_date('2016-02-11', 'yyyy-mm-dd') and to_date('2016-02-22', 'yyyy-mm-dd')"
 
paste("select * from TEST_TABLE where", sql_expr2)
#> [1] "select * from TEST_TABLE where STD_1 between to_date('2016-02-22', 'yyyy-mm-dd') and to_date('2016-02-22', 'yyyy-mm-dd')"

in_condition

# SQL expressions
lazysql::in_condition("COL_1", 1:3)
#> [1] "COL_1  in (1, 2, 3)"
 
lazysql::in_condition("COL_1", 1:3, "not")
#> [1] "COL_1 not in (1, 2, 3)"
 
lazysql::in_condition("COL_1", LETTERS[2:3])
#> [1] "COL_1  in ('B', 'C')"
 
lazysql::in_condition("COL_1", LETTERS[2:3], "not")
#> [1] "COL_1 not in ('B', 'C')"

natural_key

# SQL expression
(sql_expr <- lazysql::natural_key(c("TAB1", "tab_2"),c("COL1", "col_2")))
#> [1] "TAB1.COL1 = tab_2.COL1 and TAB1.col_2 = tab_2.col_2"
 
# sample SQL JOIN statement
paste("select * from TAB1, TAB2 where", sql_expr)
#> [1] "select * from TAB1, TAB2 where TAB1.COL1 = tab_2.COL1 and TAB1.col_2 = tab_2.col_2"

Installation

Get the development version from github:

if (!"devtools" %in% installed.packages()) install.packages("devtools")
devtools::install_github("UweBlock/lazysql")

CRAN release is in preparation.

Contributor Code of Conduct

Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.

News

lazysql 0.1.3

  • Add cran-comments.md file.
  • Add CONDUCT.md file (Contributor Code of Conduct).
  • Improve description of purpose of package in DESCRIPTION and README.md files.
  • Give better explanation of validity checks of parameters in function documentation.
  • Export valid_identifier_regex() again to explain validity check of SQL table names and column names.

lazysql 0.1.2

  • Replace shQuote because it returns escaped double quotes on Windows instead of single quotes.
  • Fix issues caused by changes in new version v1.7.2 of package checkmate.
  • Remove dependency on package assertive.base.
  • Declare global variables to avoid notes from CRAN check wenn using . from package magrittr.
  • Update documentation.
  • Add continuous integration with travis-ci, appveyor, and coveralls.

lazysql 0.1.1

  • Don't allow choices to contain quotes in in_condition().
  • Update documentation and examples.
  • Add a NEWS.md file to track changes to the package.
  • Add a README.Rmd file.

lazysql 0.1.0

  • Initial release

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.

install.packages("lazysql")

0.1.3 by Uwe Block, 3 years ago


https://github.com/UweBlock/lazysql


Report a bug at https://github.com/UweBlock/lazysql/issues


Browse source code at https://github.com/cran/lazysql


Authors: Uwe Block [aut, cre]


Documentation:   PDF Manual  


MIT + file LICENSE license


Imports checkmate, magrittr, plyr

Suggests testthat


See at CRAN