The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

sqlstrings

Overview

Writing SQL code in R files is tedious for longer queries and statements and comes with certain disadvantages, such as losing syntax highlighting and autocomplete. With the help of sqlstrings, we can bulk read SQL code from a folder or a file and load it into an R list. The elements of the list are populated with the individual SQL statements and queries. Before that, the SQL code must be annotated with a special name comment.

Installation

# Install released version from CRAN
install.packages("sqlstrings")

# Install developed version from GitHub
devtools::install_github("wiwirebell/sqlstrings")

Example

Let’s assume, we have a ./sql/ folder, containing files with the following code:

-- name: create_tab1
create table tab1 (
  id integer primary key,
  city text unique not null,        -- comment
  pop integer
);

-- name: insert_tab1
insert into tab1 values 
  (1, 'Berlin', 3),
  (2, 'Paris', 2),
  (3, 'London', 8);

-- name: select_count
select count(*) from tab1;

In R, we can load the SQL code into a list and its named elements will contain the individual queries:

library(sqlstrings)
s <- generate_sql_strings("./sql/")
print(s)

## > print(s)
## $create_tab1
## [1] "create table tab1 (\n    id integer primary key,\n    city text unique not null,        -- comment\n    pop integer\n  );"
## 
## $insert_tab1
## [1] "insert into tab1 values \n    (1, 'Berlin', 3),\n    (2, 'Paris', 2),\n    (3, 'London', 8);"
## 
## $select_count
## [1] "select count(*) from tab1;"

Once the list is created, we can use the list, for example, with DBI:

library(DBI)

# setup connection...

dbExecute(con, s$create_tab1)
dbExecute(con, s$insert_tab1)
dbGetQuery(con, s$select_count)

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.