R/factories.R

In sqlq: 'SQL' Query Builder

#' Create an SQL SELECT query.
#'
#' Create a QuerySelectFields instance to select a set of fields. The table name
#' and the list of fields are the only required parameters.
#'
#' @examples
#' # Here is a simple SELECT query:
#' make_select("books", fields = c("title", "author"))
#'
#' @param tabl A table name.
#' @param fields A character vector containing field names or a list of
#'               ExprField objects.
#' @param distinct If set to TRUE, add the distinct keyword.
#' @param limit Add a limit (integer value) to the number of records returned.
#' @param where Set a StmtWhere instance to add a where clause.
#' @param join Set a StmtJoin instance to add a join clause.
#' @return A SelectQuery instance.
#' @include Query.R
#' @export
make_select <- function(tabl, fields, distinct = FALSE, limit = NULL,
                        where = NULL, join = NULL) {
  chk::chk_string(tabl)
  chk::chk_true(chk::vld_character(fields) ||
                chk::vld_all(fields, chk::vld_is, "ExprField"))
  expr_fields <- (if (is.character(fields)) {
    lapply(fields, ExprField$new)
  } else {
    fields
  })
  select <- StmtSelectFields$new(expr_fields, distinct = distinct)
  query <- QuerySelect$new(select = select, from = StmtFrom$new(tabl))
  if (!is.null(limit) && limit > 0L)
    query$add(StmtLimit$new(limit))
  if (!is.null(where)) {
    chk::chk_is(where, "StmtWhere")
    query$add(where)
  }
  if (!is.null(join)) {
    chk::chk_is(join, "StmtJoin")
    query$add(join)
  }
  return(query)
}

#' Create an SQL SELECT query for all fields.
#'
#' Create a QuerySelectAll instance (i.e.: select *) to retrieve all fields of a
#' table.
#'
#' @examples
#' # Here is a simple SELECT * query:
#' make_select_all("books")
#'
#' @param tabl A table name.
#' @param distinct If set to TRUE, add the distinct keyword.
#' @param limit Add a limit (integer value) to the number of records returned.
#' @param where Set a StmtWhere instance to add a where clause.
#' @param join Set a StmtJoin instance to add a join clause.
#' @return A instance of QuerySelect.
#' @include Query.R
#' @export
make_select_all <- function(tabl, distinct = FALSE, limit = NULL, where = NULL,
                            join = NULL) {
  chk::chk_string(tabl)
  query <- QuerySelect$new(
    select = StmtSelectAll$new(distinct = distinct),
    from = StmtFrom$new(tabl)
  )
  if (!is.null(limit)) {
    query$add(StmtLimit$new(limit))
  }
  if (!is.null(where)) {
    chk::chk_is(where, "StmtWhere")
    query$add(where)
  }
  if (!is.null(join)) {
    chk::chk_is(join, "StmtJoin")
    query$add(join)
  }
  return(query)
}

#' Create a list of table fields.
#'
#' Create an ExprListFields instance.
#'
#' @examples
#' # To generate a list of fields:
#' fields <- make_fields(c('author', 'title', 'year'))
#'
#' @param fields A character vector containing field names.
#' @return An instance of ExprListFields.
#' @include ExprListFields.R
#' @export
make_fields <- function(fields) {
  return(ExprListFields$new(lapply(fields, ExprField$new)))
}

#' Create a SQL JOIN statement.
#'
#' Create a StmtJoin instance.
#'
#' @examples
#' # To generate a JOIN statement joining the "author_id" field of the "books"
#' # table with the "id" field of the "authors" table:
#' join <- make_join("author_id", "books", "id", "authors")
#'
#' @param field1 The first field on which to join.
#' @param table1 The table name of the first field.
#' @param field2 The second field on which to join.
#' @param table2 The table name of the second field (optional).
#' @param type The type of join to perform. One of "inner", "left", "right", or
#'              "full". Defaults to "inner".
#' @return An instance of StmtJoin.
#' @include StmtJoin.R
#' @export
make_join <- function(field1, table1, field2, table2=NULL,
                      type=c("inner", "left", "right", "full")) {
  chk::chk_string(field1)
  chk::chk_string(table1)
  chk::chk_string(field2)
  chk::chk_null_or(table2, vld=chk::vld_string)
  return(StmtJoin$new(ExprField$new(field1, table1),
                      ExprField$new(field2, table2), type=type))
}

#' Create a BETWEEN expression.
#'
#' Create an ExprBetween instance.
#'
#' @examples
#' # To generate a BETWEEN expression checking if the "year" field is between
#' # 1990 and 2000:
#' between <- make_between("year", 1990, 2000)
#'
#' @param field A character value or an ExprField instance representing the
#' field to check.
#' @param low An atomic single value or an ExprValue instance representing the
#' lower bound.
#' @param high An atomic single value of an ExprValue instance representing the
#' upper bound.
#' @return An instance of ExprBetween.
#' @include ExprBetween.R
#' @export
make_between <- function(field, low, high) {

  # Convert args
  if (is.character(field))
    field <- ExprField$new(field)
  if (is.atomic(low))
    low <- ExprValue$new(low)
  if (is.atomic(high))
    high <- ExprValue$new(high)

  # Check args
  chk::chk_is(field, "ExprField")
  chk::chk_is(low, "ExprValue")
  chk::chk_is(high, "ExprValue")

  return(ExprBetween$new(field, low, high))
}

#' Create a WHERE clause.
#'
#' Create a StmtWhere instance.
#'
#' @examples
#' # To generate a WHERE clause checking if the "year" field is greater than
#' # 2000:
#' where <- make_where(ExprBinOp$new(ExprField$new("year"), ">",
#'                                   ExprValue$new(2000)))
#'
#' @param cond An Expr instance representing the condition for the WHERE clause.
#' @return An instance of StmtWhere.
#' @include StmtWhere.R
#' @export
make_where <- function(cond) {
  chk::chk_is(cond, "Expr")
  return(StmtWhere$new(cond))
}

#' Create a list of SQL values.
#'
#' Create an ExprListValues instance using a list.
#' Useful when building an SQL list of values of mixed types, to use for
#' instance with INSERT statement to define the row of values to insert.
#'
#' @examples
#' # To generate a list of values:
#' row <- make_row(list('John Smith', 'Memories', 1999))
#'
#' @param values A list/vector containing values.
#' @return An instance of ExprListValues.
#' @include ExprListValues.R
#' @export
make_row <- function(values) {
  chk::chk_list(values)
  return(ExprListValues$new(lapply(values, ExprValue$new)))
}

#' Create a list of SQL values.
#'
#' Create an ExprListValues instance using a vector.
#' Useful when building an SQL list of values of identical type, to use with the
#' IN operator.
#'
#' @examples
#' # To generate a list of values from a vector:
#' values <- make_values(c(1999, 2012, 2014))
#'
#' @param values A list/vector containing values.
#' @return An instance of ExprListValues.
#' @include ExprListValues.R
#' @export
make_values <- function(values) {
  chk::chk_vector(values)
  chk::chk_false(chk::vld_list(values))
  return(ExprListValues$new(lapply(values, ExprValue$new)))
}

#' Create a list of rows of values
#'
#' Create a StmtValues instance.
#'
#' @examples
#' # To generate a VALUES statement with two rows:
#' rows <- make_rows(list(list('John Smith', 'Memories', 1999),
#'                        list('Barbara', 'My Life', 2010)))
#'
#' @param values A list of lists/vectors of values, each reprensenting a row.
#' @return An instance of StmtValues.
#' @export
make_rows <- function(values) {
  chk::chk_list(values)
  return(StmtValues$new(lapply(values, make_row)))
}

#' Create an SQL INSERT INTO query.
#'
#' Create a QueryInsert instance.
#'
#' @examples
#' # To generate a simple INSERT query:
#' values <- list(list('John Smith', 'Memories', 1999),
#'                list('Barbara', 'My Life', 2010))
#' insert <- make_insert(tabl = 'books', fields = c('author', 'title', 'year'),
#'                       values = values)
#'
#' @param tabl A table name.
#' @param fields A character vector containing field names.
#' @param values A list of lists/vectors of values, each reprensenting a row to
#' insert.
#' @return An instance of QueryInsert.
#' @include QueryInsert.R
#' @export
make_insert <- function(tabl, fields, values) {
  insert <- StmtInsert$new(tabl = tabl, fields = make_fields(fields))
  if (!chk::vld_is(values, 'StmtValues'))
    values <- make_rows(values)
  query <- QueryInsert$new(insert = insert, values = values)
  return(query)
}

#' Create an SQL CREATE TABLE query.
#'
#' Create a QueryCreate instance.
#'
#' @examples
#' # To generate the CREATE query for creating a simple table for listing books:
#' fields_def <- list(ExprFieldDef$new('id', 'integer', primary=TRUE),
#'                    ExprFieldDef$new('title', 'varchar(200)', nullable=FALSE),
#'                    ExprFieldDef$new('author', 'varchar(80)', nullable=FALSE))
#' create <- make_create_table(tabl = 'books', fields_def = fields_def)
#'
#' @param tabl Name of the new table
#' @param fields_def An list of ExprFieldDef instances.
#' @return An instance of QueryCreate.
#' @include QueryCreate.R
#' @export
make_create_table <- function(tabl, fields_def) {
  create <- StmtCreate$new(tabl = tabl, fields_def = fields_def)
  return(QueryCreate$new(create))
}

#' Create an SQL DELETE FROM query.
#'
#' Create a QueryDelete instance.
#'
#' @examples
#' # Create a simple DELETE query for deleting some old books:
#' where <- StmtWhere$new(ExprBinOp$new(
#'   ExprField$new("year"), "<",
#'   ExprValue$new(2015)
#' ))
#' delete <- make_delete(tabl = "books", where = where)
#'
#' @param tabl Name of the new table
#' @param where Set a StmtWhere instance to add a where clause.
#' @return An instance of QueryDelete.
#' @include QueryCreate.R
#' @export
make_delete <- function(tabl, where = NULL) {
  delete <- QueryDelete$new(StmtDelete$new(tabl))
  if (! is.null(where))
    delete$add(where)
  return(delete)
}

#' Create an SQL SET statement.
#'
#' Create a StmtSet instance.
#'
#' @examples
#' # To generate a SET statement for setting the "price" and "old" fields:
#' set <- make_set(price = 9.50, old = TRUE)
#'
#' @param ... Named arguments, each representing a field name and its value.
#' @return An instance of StmtSet.
#' @include StmtSet.R
#' @export
make_set <- function(...) {
  named_args <- list(...)
  set <- StmtSet$new()
  for (name in names(named_args))
    set$add_field(ExprField$new(name), ExprValue$new(named_args[[name]]))
  return(set)
}

#' Create an SQL UPDATE query.
#'
#' Create a QueryUpdate instance.
#'
#' @examples
#' # Generate a simple update query:
#' where <- StmtWhere$new(ExprBinOp$new(
#'   ExprField$new("year"), "<",
#'   ExprValue$new(2010)
#' ))
#' set <- make_set(price = 9.50, old = TRUE)
#' update <- make_update('books', set = set, where = where)$toString()
#'
#' @param tabl A table name.
#' @param set A StmtSet instance containing the fields to update.
#' @param where A StmtWhere instance to add a where clause (optional).
#' @return An instance of QueryUpdate.
#' @include QueryUpdate.R StmtSet.R StmtWhere.R
#' @export
make_update <- function(tabl, set, where = NULL) {
  chk::chk_is(set, "StmtSet")
  up <- StmtUpdate$new(tabl)
  query <- QueryUpdate$new(up, set = set)
  if (!is.null(where)) {
    chk::chk_is(where, "StmtWhere")
    query$add(where)
  }
  return(query)
}