| Title: | 'SQL' Query Builder |
|---|---|
| Description: | Allows to build complex 'SQL' (Structured Query Language) queries dynamically. Classes and/or factory functions are used to produce a syntax tree from which the final character string is generated. Strings and identifiers are automatically quoted using the right quotes, using either ANSI (American National Standards Institute) quoting or the quoting style of an existing database connector. Style can be configured to set uppercase/lowercase for keywords, remove unnecessary spaces, or omit optional keywords. |
| Authors: | Pierrick Roger [aut, cre] (ORCID: <https://orcid.org/0000-0001-8177-4873>) |
| Maintainer: | Pierrick Roger <[email protected]> |
| License: | AGPL-3 |
| Version: | 1.0.1 |
| Built: | 2026-05-15 07:07:00 UTC |
| Source: | https://github.com/cran/sqlq |
Allows to build complex 'SQL' (Structured Query Language) queries dynamically. Classes and/or factory functions are used to produce a syntax tree from which the final character string is generated. Strings and identifiers are automatically quoted using the right quotes, using either ANSI (American National Standards Institute) quoting or the quoting style of an existing database connector. Style can be configured to set uppercase/lowercase for keywords, remove unnecessary spaces, or omit optional keywords.
sqlq package.
sqlq simplifies the creation of SQL queries, and ensure identifiers and string values are correctly quoted.
Global options used by sqlq:
sqlq_always_quote: If set to TRUE, token identifiers (table and column names) will always be quoted.
sqlq_conn: Set the database connector to use for quoting identifiers and values. Default is DBI::ANSI().
sqlq_omit_kwd: If set to TRUE, optional SQL keywords (like INNER or OUTER) will be omitted.
sqlq_spaces: If set to FALSE, try to avoid non-necessary spaces (e.g.: around operators or after a comma).
sqlq_uppercase: If set to FALSE, SQL keywords and alphabetical operators (e.g.: OR, AND, ...) will be written in lowercase.
Maintainer: Pierrick Roger [email protected] (ORCID)
options(sqlq_uppercase = FALSE)options(sqlq_uppercase = FALSE)
If global option sqlq_case is set to "lower", put the string in lowercase, if it is set to "upper", put the string in uppercase. Otherwise the string is not changed.
apply_case(s)apply_case(s)
s |
The string whose case must be changed. |
The string in the right case.
This abstract class represents an SQL expression.
sqlq::Statement -> Expr
clone()
The objects of this class are cloneable with this method.
Expr$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example provided, as this class is abstract.# No example provided, as this class is abstract.
This class represents an SQL BETWEEN expression.
This class represents an SQL BETWEEN expression.
Used to generate SQL expression BETWEEN / AND.
sqlq::Statement -> sqlq::Expr -> ExprBetween
new()
Initializer.
ExprBetween$new(field, low, high)
fieldAn ExprField instance representing the field to check.
lowAn ExprValue instance representing the lower bound.
highAn ExprValue instance representing the upper bound.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprBetween$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprBetween$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate "i BETWEEN 1 AND 10": ExprBetween$new(ExprField$new("i"), ExprValue$new(1L), ExprValue$new(10L))# To generate "i BETWEEN 1 AND 10": ExprBetween$new(ExprField$new("i"), ExprValue$new(1L), ExprValue$new(10L))
This class represents an SQL binary operator.
This class represents an SQL binary operator.
Used to generate SQL expressions involving a binary operator like in "a / 10".
sqlq::Statement -> sqlq::Expr -> sqlq::ExprComp -> ExprBinOp
new()
Initializer.
ExprBinOp$new(lexpr, op, rexpr, ...)
lexprAn Expr instance for the left part.
opThe binary operator, as a string.
rexprAn Expr instance for the right part.
...Arguments to pass to parent class.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprBinOp$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprBinOp$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate "a / 10": ExprBinOp$new(ExprField$new("a"), "/", ExprValue$new(10))# To generate "a / 10": ExprBinOp$new(ExprField$new("a"), "/", ExprValue$new(10))
This class represents an SQL logical operator.
This class represents an SQL logical operator.
Used to generate SQL expressions involving a commutative binary operator like in "a + 10 + b".
sqlq::Statement -> sqlq::Expr -> sqlq::ExprComp -> ExprCommOp
new()
Initializer.
ExprCommOp$new(op, expr = NULL)
opThe logical operator, as a string.
exprA list of logical expressions.
Nothing.
add()
Add an SQL expression to the logical operator.
ExprCommOp$add(expr)
exprA Expr instance.
Nothing.
nb_expr()
Returns the number of expressions.
ExprCommOp$nb_expr()
The number of expressions in this logical operator.
getTokens()
Generates the list of tokens representing this statement.
ExprCommOp$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprCommOp$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate "a + 10 + b": ExprCommOp$new("+", list(ExprField$new("a"), ExprValue$new(10), ExprField$new("b")))# To generate "a + 10 + b": ExprCommOp$new("+", list(ExprField$new("a"), ExprValue$new(10), ExprField$new("b")))
Composed Expression class.
Composed Expression class.
This abstract class is used as a parent class for ExprBinOp and ExprCommOp.
sqlq::Statement -> sqlq::Expr -> ExprComp
new()
Initializer.
ExprComp$new(paren = TRUE)
parenSet to TRUE to enable parenthesis around the expression.
Nothing.
enableParenthesis()
Disable parenthesis around expression.
ExprComp$enableParenthesis(enabled)
enabledSet to TRUE to enable parenthesis and FALSE to disable them.
Nothing.
clone()
The objects of this class are cloneable with this method.
ExprComp$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example provided, as this class is abstract.# No example provided, as this class is abstract.
This class represents an SQL field.
This class represents an SQL field.
Used to define a field to be used inside a SELECT or UPDATE statement.
sqlq::Statement -> sqlq::Expr -> ExprField
new()
Initializer.
ExprField$new(name, tabl = NULL)
nameThe field name.
tablThe table name.
Nothing.
getTable()
Return the associted table.
ExprField$getTable()
The associated table, as a character value, NA if no table is defined.
getTokens()
Generate the list of tokens representing this statement.
ExprField$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprField$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate the reference to field "title" in table "books": ExprField$new("title", tabl="books")# To generate the reference to field "title" in table "books": ExprField$new("title", tabl="books")
Table field definition.
Table field definition.
Used to define a field when creating a table.
sqlq::Statement -> sqlq::Expr -> ExprFieldDef
new()
Initializer.
ExprFieldDef$new(name, type, primary = FALSE, nullable = TRUE)
nameThe field name.
typeThe field's type (integer, date, varchar(...), ...).
primarySet to TRUE if the field is a PRIMARY KEY.
nullableSet to FALSE of the field does not accept NULL values.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprFieldDef$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprFieldDef$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate the definition of a field named "title": ExprFieldDef$new("title", "TEXT", nullable = FALSE)# To generate the definition of a field named "title": ExprFieldDef$new("title", "TEXT", nullable = FALSE)
This class represents the IS NOT NULL test.
This class represents the IS NOT NULL test.
Used to test if a field is NOT NULL inside a WHERE clause.
sqlq::Statement -> sqlq::Expr -> sqlq::ExprComp -> ExprIsNotNull
new()
Initializer.
ExprIsNotNull$new(expr, ...)
exprThe Expr instance to test.
...Arguments to pass to parent class.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprIsNotNull$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprIsNotNull$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate a NOT NULL test: ExprIsNotNull$new(ExprField$new("title"))# To generate a NOT NULL test: ExprIsNotNull$new(ExprField$new("title"))
This class represents the IS NULL test.
This class represents the IS NULL test.
Used to test if a field is NULL inside a WHERE clause.
sqlq::Statement -> sqlq::Expr -> sqlq::ExprComp -> ExprIsNull
new()
Initializer.
ExprIsNull$new(expr, ...)
exprThe Expr instance to test.
...Arguments to pass to parent class.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprIsNull$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprIsNull$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate a NULL test: ExprIsNull$new(ExprField$new("title"))# To generate a NULL test: ExprIsNull$new(ExprField$new("title"))
This class represents an SQL list.
This class represents an SQL list.
An abstract class to represent a list. Used by ExprListValues and ExprListFields.
sqlq::Statement -> sqlq::Expr -> ExprList
new()
Initializer.
ExprList$new(expr)
exprA list of Expr instances.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprList$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprList$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example provided, as this class is abstract.# No example provided, as this class is abstract.
This class represents a list of fields.
This class represents a list of fields.
Used to define a list of ExprField instances for the INSERT query.
sqlq::Statement -> sqlq::Expr -> sqlq::ExprList -> ExprListFields
new()
Initializer.
ExprListFields$new(fields)
fieldsA list of ExprField instances.
Nothing.
clone()
The objects of this class are cloneable with this method.
ExprListFields$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate the list of fields "id", "title", "year": ExprListFields$new(list(ExprField$new("id"), ExprField$new("title"), ExprField$new("year")))# To generate the list of fields "id", "title", "year": ExprListFields$new(list(ExprField$new("id"), ExprField$new("title"), ExprField$new("year")))
This class represents a list of values.
This class represents a list of values.
Used to define a list of ExprValue instances for the INSERT query.
sqlq::Statement -> sqlq::Expr -> sqlq::ExprList -> ExprListValues
new()
Initializer.
ExprListValues$new(values)
valuesA list of ExprValue instances.
Nothing.
clone()
The objects of this class are cloneable with this method.
ExprListValues$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate the list of values 1234, "The River", "1965": ExprListValues$new(list(ExprValue$new(1234), ExprValue$new("The River"), ExprValue$new(1965)))# To generate the list of values 1234, "The River", "1965": ExprListValues$new(list(ExprValue$new(1234), ExprValue$new("The River"), ExprValue$new(1965)))
This class represents an SQL unary operator.
This class represents an SQL unary operator.
Used to generate SQL expressions involving an unary operator like in "NOT flag".
sqlq::Statement -> sqlq::Expr -> sqlq::ExprComp -> ExprUnaryOp
new()
Initializer.
ExprUnaryOp$new(op, expr, ...)
opThe unary operator, as a string.
exprAn Expr instance.
...Arguments to pass to parent class.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprUnaryOp$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprUnaryOp$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate "NOT flag": ExprUnaryOp$new("not", ExprField$new("flag"))# To generate "NOT flag": ExprUnaryOp$new("not", ExprField$new("flag"))
This class represents an SQL value.
This class represents an SQL value.
Used to reprensent an SQL value.
sqlq::Statement -> sqlq::Expr -> ExprValue
new()
Initializer.
ExprValue$new(value)
valueThe value.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
ExprValue$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
ExprValue$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate the integer value 30: ExprValue$new(30L) # To generate the string value "abcd": ExprValue$new("abcd")# To generate the integer value 30: ExprValue$new(30L) # To generate the string value "abcd": ExprValue$new("abcd")
Create an ExprBetween instance.
make_between(field, low, high)make_between(field, low, high)
field |
A character value or an ExprField instance representing the field to check. |
low |
An atomic single value or an ExprValue instance representing the lower bound. |
high |
An atomic single value of an ExprValue instance representing the upper bound. |
An instance of ExprBetween.
# To generate a BETWEEN expression checking if the "year" field is between # 1990 and 2000: between <- make_between("year", 1990, 2000)# To generate a BETWEEN expression checking if the "year" field is between # 1990 and 2000: between <- make_between("year", 1990, 2000)
Create a QueryCreate instance.
make_create_table(tabl, fields_def)make_create_table(tabl, fields_def)
tabl |
Name of the new table |
fields_def |
An list of ExprFieldDef instances. |
An instance of QueryCreate.
# 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)# 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)
Create a QueryDelete instance.
make_delete(tabl, where = NULL)make_delete(tabl, where = NULL)
tabl |
Name of the new table |
where |
Set a StmtWhere instance to add a where clause. |
An instance of QueryDelete.
# 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)# 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)
Create an ExprListFields instance.
make_fields(fields)make_fields(fields)
fields |
A character vector containing field names. |
An instance of ExprListFields.
# To generate a list of fields: fields <- make_fields(c('author', 'title', 'year'))# To generate a list of fields: fields <- make_fields(c('author', 'title', 'year'))
Create a QueryInsert instance.
make_insert(tabl, fields, values)make_insert(tabl, fields, values)
tabl |
A table name. |
fields |
A character vector containing field names. |
values |
A list of lists/vectors of values, each reprensenting a row to insert. |
An instance of QueryInsert.
# 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)# 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)
Create a StmtJoin instance.
make_join( field1, table1, field2, table2 = NULL, type = c("inner", "left", "right", "full") )make_join( field1, table1, field2, table2 = NULL, type = c("inner", "left", "right", "full") )
field1 |
The first field on which to join. |
table1 |
The table name of the first field. |
field2 |
The second field on which to join. |
table2 |
The table name of the second field (optional). |
type |
The type of join to perform. One of "inner", "left", "right", or "full". Defaults to "inner". |
An instance of StmtJoin.
# 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")# 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")
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.
make_row(values)make_row(values)
values |
A list/vector containing values. |
An instance of ExprListValues.
# To generate a list of values: row <- make_row(list('John Smith', 'Memories', 1999))# To generate a list of values: row <- make_row(list('John Smith', 'Memories', 1999))
Create a StmtValues instance.
make_rows(values)make_rows(values)
values |
A list of lists/vectors of values, each reprensenting a row. |
An instance of StmtValues.
# To generate a VALUES statement with two rows: rows <- make_rows(list(list('John Smith', 'Memories', 1999), list('Barbara', 'My Life', 2010)))# To generate a VALUES statement with two rows: rows <- make_rows(list(list('John Smith', 'Memories', 1999), list('Barbara', 'My Life', 2010)))
Create a QuerySelectFields instance to select a set of fields. The table name and the list of fields are the only required parameters.
make_select( tabl, fields, distinct = FALSE, limit = NULL, where = NULL, join = NULL )make_select( tabl, fields, distinct = FALSE, limit = NULL, where = NULL, join = NULL )
tabl |
A table name. |
fields |
A character vector containing field names or a list of ExprField objects. |
distinct |
If set to TRUE, add the distinct keyword. |
limit |
Add a limit (integer value) to the number of records returned. |
where |
Set a StmtWhere instance to add a where clause. |
join |
Set a StmtJoin instance to add a join clause. |
A SelectQuery instance.
# Here is a simple SELECT query: make_select("books", fields = c("title", "author"))# Here is a simple SELECT query: make_select("books", fields = c("title", "author"))
Create a QuerySelectAll instance (i.e.: select *) to retrieve all fields of a table.
make_select_all( tabl, distinct = FALSE, limit = NULL, where = NULL, join = NULL )make_select_all( tabl, distinct = FALSE, limit = NULL, where = NULL, join = NULL )
tabl |
A table name. |
distinct |
If set to TRUE, add the distinct keyword. |
limit |
Add a limit (integer value) to the number of records returned. |
where |
Set a StmtWhere instance to add a where clause. |
join |
Set a StmtJoin instance to add a join clause. |
A instance of QuerySelect.
# Here is a simple SELECT * query: make_select_all("books")# Here is a simple SELECT * query: make_select_all("books")
Create a StmtSet instance.
make_set(...)make_set(...)
... |
Named arguments, each representing a field name and its value. |
An instance of StmtSet.
# To generate a SET statement for setting the "price" and "old" fields: set <- make_set(price = 9.50, old = TRUE)# To generate a SET statement for setting the "price" and "old" fields: set <- make_set(price = 9.50, old = TRUE)
Create a QueryUpdate instance.
make_update(tabl, set, where = NULL)make_update(tabl, set, where = NULL)
tabl |
A table name. |
set |
A StmtSet instance containing the fields to update. |
where |
A StmtWhere instance to add a where clause (optional). |
An instance of QueryUpdate.
# 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()# 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()
Create an ExprListValues instance using a vector. Useful when building an SQL list of values of identical type, to use with the IN operator.
make_values(values)make_values(values)
values |
A list/vector containing values. |
An instance of ExprListValues.
# To generate a list of values from a vector: values <- make_values(c(1999, 2012, 2014))# To generate a list of values from a vector: values <- make_values(c(1999, 2012, 2014))
Create a StmtWhere instance.
make_where(cond)make_where(cond)
cond |
An Expr instance representing the condition for the WHERE clause. |
An instance of StmtWhere.
# 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)))# 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)))
This class handles an SQL Query.
This class handles an SQL Query.
This class represents an SQL query.
new()
Initializer.
Query$new(stmts)
stmtsA character vector of statement class names. It describes the accepted statements and their order, using wildcards to indicate if a statement is optional, or if it is allowed to occur multiple times. Example: c("Select", "From", "Join*", "Where?", "Limit?")
Nothing.
add()
Add a statement.
Query$add(stmt)
stmtThe statement to add.
Nothing.
toString()
Generates the string representation of this query.
Query$toString()
A string containing the full SQL query.
clone()
The objects of this class are cloneable with this method.
Query$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example provided, as this class is abstract.# No example provided, as this class is abstract.
Create query.
Create query.
This class represents an SQL CREATE TABLE query. See the function make_create_table() to create more easily a QueryCreate object.
sqlq::Query -> QueryCreate
new()
Initializer.
QueryCreate$new(create)
createA StmtCreate instance.
Nothing.
clone()
The objects of this class are cloneable with this method.
QueryCreate$clone(deep = FALSE)
deepWhether to make a deep clone.
# 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 <- QueryCreate$new(StmtCreate$new(tabl = 'books', fields_def = fields_def))# 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 <- QueryCreate$new(StmtCreate$new(tabl = 'books', fields_def = fields_def))
Delete query.
Delete query.
This class represents an SQL SELECT query. See the function make_delete() to create more easily a QueryDelete object.
sqlq::Query -> QueryDelete
new()
Initializer.
QueryDelete$new(delete)
deleteA StmtDelete instance.
Nothing.
clone()
The objects of this class are cloneable with this method.
QueryDelete$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create a simple DELETE query for deleting some old books: where <- StmtWhere$new(ExprBinOp$new( ExprField$new("year"), "<", ExprValue$new(2015) )) delete <- QueryDelete$new(StmtDelete$new('books')) delete$add(where)# Create a simple DELETE query for deleting some old books: where <- StmtWhere$new(ExprBinOp$new( ExprField$new("year"), "<", ExprValue$new(2015) )) delete <- QueryDelete$new(StmtDelete$new('books')) delete$add(where)
Insert query.
Insert query.
This class represents an SQL SELECT query. See the make_insert() factory function to create more easily an INSERT query object.
sqlq::Query -> QueryInsert
new()
Initializer.
QueryInsert$new(insert, values)
insertA StmtInsert instance.
valuesA StmtValues instance.
Nothing.
clone()
The objects of this class are cloneable with this method.
QueryInsert$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate a simple INSERT query: fields <- c('author', 'title', 'year') insert <- StmtInsert$new(tabl = 'books', fields = make_fields(fields)) values <- make_rows(list(list('John Smith', 'Memories', 1999), list('Barbara', 'My Life', 2010))) insert <- QueryInsert$new(insert = insert, values = values)# To generate a simple INSERT query: fields <- c('author', 'title', 'year') insert <- StmtInsert$new(tabl = 'books', fields = make_fields(fields)) values <- make_rows(list(list('John Smith', 'Memories', 1999), list('Barbara', 'My Life', 2010))) insert <- QueryInsert$new(insert = insert, values = values)
Class for the SELECT query.
Class for the SELECT query.
This class represents an SQL SELECT query. See make_select() and make_select_all() factory functions to create more easily a SELECT query.
sqlq::Query -> QuerySelect
new()
Initializer.
QuerySelect$new(select, from)
selectA StmtSelect instance.
fromA StmtFrom instance.
Nothing.
clone()
The objects of this class are cloneable with this method.
QuerySelect$clone(deep = FALSE)
deepWhether to make a deep clone.
# Here is a simple SELECT * query: select <- QuerySelect$new(select = StmtSelectAll$new(), from = StmtFrom$new("books"))# Here is a simple SELECT * query: select <- QuerySelect$new(select = StmtSelectAll$new(), from = StmtFrom$new("books"))
Update Query.
Update Query.
This class represents an SQL UPDATE query. See the make_update() factory function to create more easily an UPDATE query object.
sqlq::Query -> QueryUpdate
new()
Initializer.
QueryUpdate$new(up, set)
upA StmtUpdate instance.
setA StmtSet instance.
Nothing.
clone()
The objects of this class are cloneable with this method.
QueryUpdate$clone(deep = FALSE)
deepWhether to make a deep clone.
# To 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 <- QueryUpdate$new(StmtUpdate$new('books'), set = set) update$add(where)# To 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 <- QueryUpdate$new(StmtUpdate$new('books'), set = set) update$add(where)
Identifiers are quoted only if it contains at least one non-alphanumeric character.
quote_ids(ids)quote_ids(ids)
ids |
Character vector of identifiers to quote. |
A character vector containing the same identifiers, quoted if necessary.
Quote character values inside a vector or list. If other values are found inside the list or vector, they are converted to character values.
quote_values(values)quote_values(values)
values |
Vector or list of values. |
A character vector containing the same values, converted. All character values are quoted.
Abstract class that represents an SQL statement.
Abstract class that represents an SQL statement.
This abstract class represents an SQL statement (FROM, SELECT, WHERE, ...). Note that expressions (Expr class) are a particular type of Statement in sqlq.
new()
Initializer
Statement$new()
Nothing.
getTokens()
Generates the list of tokens representing this statement.
Statement$getTokens()
A list of Token objects.
toString()
Generates the string representation of this statement.
Statement$toString()
A string containing the SQL expression.
clone()
The objects of this class are cloneable with this method.
Statement$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example provided, as this class is abstract.# No example provided, as this class is abstract.
CREATE TABLE statement.
CREATE TABLE statement.
sqlq::Statement -> StmtCreate
new()
Initializer.
StmtCreate$new(tabl, fields_def)
tablA table name.
fields_defAn instance of ExprListFields
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtCreate$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtCreate$clone(deep = FALSE)
deepWhether to make a deep clone.
# To generate a simple CREATE TABLE statement: fields_def <- list(ExprFieldDef$new('id', 'integer', primary=TRUE), ExprFieldDef$new('title', 'varchar(200)', nullable=FALSE), ExprFieldDef$new('author', 'varchar(80)', nullable=FALSE)) StmtCreate$new(tabl = 'books', fields_def = fields_def)# To generate a simple CREATE TABLE statement: fields_def <- list(ExprFieldDef$new('id', 'integer', primary=TRUE), ExprFieldDef$new('title', 'varchar(200)', nullable=FALSE), ExprFieldDef$new('author', 'varchar(80)', nullable=FALSE)) StmtCreate$new(tabl = 'books', fields_def = fields_def)
DELETE FROM statement.
DELETE FROM statement.
sqlq::Statement -> StmtDelete
new()
Initializer.
StmtDelete$new(tabl)
tablA table name.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtDelete$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtDelete$clone(deep = FALSE)
deepWhether to make a deep clone.
# Simple DELETE statement: StmtDelete$new('books')# Simple DELETE statement: StmtDelete$new('books')
SQL From statement.
SQL From statement.
sqlq::Statement -> StmtFrom
new()
Initializer.
StmtFrom$new(tabl)
tablA table name.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtFrom$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtFrom$clone(deep = FALSE)
deepWhether to make a deep clone.
# Example a FROM statement: StmtFrom$new('books')# Example a FROM statement: StmtFrom$new('books')
INSERT INTO statement.
INSERT INTO statement.
sqlq::Statement -> StmtInsert
new()
Initializer.
StmtInsert$new(tabl, fields)
tablA table name.
fieldsAn instance of ExprListFields
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtInsert$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtInsert$clone(deep = FALSE)
deepWhether to make a deep clone.
# Simple INSERT statement: fields <- c('author', 'title', 'year') insert <- StmtInsert$new(tabl = 'books', fields = make_fields(fields))# Simple INSERT statement: fields <- c('author', 'title', 'year') insert <- StmtInsert$new(tabl = 'books', fields = make_fields(fields))
SQL JOIN statement.
SQL JOIN statement.
This class represents a SQL JOIN statement. It requires two fields on which to join, and the type of join to perform (inner, left, right, or full). The table on which to join is determined by looking at the two fields in order and using the first table name available.
sqlq::Statement -> StmtJoin
new()
Initializer. To determine the table on which to join, we look at the both fields in order and use the first table name available.
StmtJoin$new(field1, field2, type = c("inner", "left", "right", "full"))field1The first field on which to join.
field2The second field on which to join.
typeThe type of join to perform. One of "inner", "left", "right", or "full". Defaults to "inner".
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtJoin$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtJoin$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create an inner join (default join type) between table 'foo' and table # 'bar': join <- StmtJoin$new(ExprField$new("id", "foo"), ExprField$new("foo_id", "bar")) # Create a left join between table 'foo' and table 'bar': join <- StmtJoin$new(ExprField$new("id", "foo"), ExprField$new("foo_id", "bar"), type = "left")# Create an inner join (default join type) between table 'foo' and table # 'bar': join <- StmtJoin$new(ExprField$new("id", "foo"), ExprField$new("foo_id", "bar")) # Create a left join between table 'foo' and table 'bar': join <- StmtJoin$new(ExprField$new("id", "foo"), ExprField$new("foo_id", "bar"), type = "left")
LIMIT statement.
LIMIT statement.
This class represents a SQL LIMIT statement. It requires a single integer limit value.
sqlq::Statement -> StmtLimit
new()
Initializer
StmtLimit$new(limit)
limitThe integer limit.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtLimit$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtLimit$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create a LIMIT statement with a limit of 10: limit <- StmtLimit$new(10L) # Use the created LIMIT statement inside a SELECT query: query <- QuerySelect$new(StmtSelectAll$new(), from = StmtFrom$new("books")) query$add(limit)# Create a LIMIT statement with a limit of 10: limit <- StmtLimit$new(10L) # Use the created LIMIT statement inside a SELECT query: query <- QuerySelect$new(StmtSelectAll$new(), from = StmtFrom$new("books")) query$add(limit)
Abstract SELECT statement.
Abstract SELECT statement.
This is an abstract class representing a SQL SELECT statement. It is
inherited by concrete classes StmtSelectAll and StmtSelectFields.
sqlq::Statement -> StmtSelect
new()
Initializer
StmtSelect$new(distinct = FALSE)
distinctSet to TRUE enable distinct keyword and remove
duplicate results.
Nothing.
clone()
The objects of this class are cloneable with this method.
StmtSelect$clone(deep = FALSE)
deepWhether to make a deep clone.
StmtSelectAll, StmtSelectFields
# No example provided, as this class is abstract.# No example provided, as this class is abstract.
SELECT * statement.
SELECT * statement.
This class represents a SQL SELECT * statement. It can be used to select
all fields from a table, with optional distinct keyword to remove duplicate
results.
sqlq::Statement -> sqlq::StmtSelect -> StmtSelectAll
new()
Initializer
StmtSelectAll$new(distinct = FALSE)
distinctSet to TRUE enable distinct keyword and remove
duplicate results.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtSelectAll$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtSelectAll$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create a SELECT * statement: select_all <- StmtSelectAll$new() # Use the created SELECT * statement inside a SELECT query: query <- QuerySelect$new(select = select_all, from = StmtFrom$new("books")) # Create a SELECT DISTINCT * statement: select_distinct_all <- StmtSelectAll$new(distinct = TRUE)# Create a SELECT * statement: select_all <- StmtSelectAll$new() # Use the created SELECT * statement inside a SELECT query: query <- QuerySelect$new(select = select_all, from = StmtFrom$new("books")) # Create a SELECT DISTINCT * statement: select_distinct_all <- StmtSelectAll$new(distinct = TRUE)
SELECT fields statement.
SELECT fields statement.
This class represents a SQL SELECT statement with specific fields. It
requires a list of ExprField instances representing the fields to select,
with optional distinct keyword to remove duplicate results.
sqlq::Statement -> sqlq::StmtSelect -> StmtSelectFields
new()
Initializer
StmtSelectFields$new(fields, distinct = FALSE)
fieldsA list of ExprField instances.
distinctSet to TRUE enable distinct keyword and remove
duplicate results.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtSelectFields$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtSelectFields$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create a SELECT statement with specific fields: field1 <- ExprField$new("title", "books") field2 <- ExprField$new("name", "authors") select_fields <- StmtSelectFields$new(fields = list(field1, field2)) # Use the created SELECT statement inside a SELECT query: query <- QuerySelect$new(select = select_fields, from = StmtFrom$new("books"))# Create a SELECT statement with specific fields: field1 <- ExprField$new("title", "books") field2 <- ExprField$new("name", "authors") select_fields <- StmtSelectFields$new(fields = list(field1, field2)) # Use the created SELECT statement inside a SELECT query: query <- QuerySelect$new(select = select_fields, from = StmtFrom$new("books"))
SET statement.
SET statement.
This class represents a SQL SET statement, used in UPDATE queries to set
field values. It can hold one or more field/value pairs.
The factory function make_set() can be used to create
a SET statement more easily.
sqlq::Statement -> StmtSet
new()
Initializer.
StmtSet$new()
Nothing.
add_field()
Add a field/value pair.
StmtSet$add_field(field, value)
fieldThe field, as an ExprField instance.
valueThe value to set, as an Expr instance.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtSet$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtSet$clone(deep = FALSE)
deepWhether to make a deep clone.
make_set()
# Create a SET statement with a single field/value pair: set_stmt <- StmtSet$new() set_stmt$add_field(ExprField$new("price"), ExprValue$new(9.50)) # Use the created SET statement inside an UPDATE query: query <- QueryUpdate$new(StmtUpdate$new("books"), set = set_stmt)# Create a SET statement with a single field/value pair: set_stmt <- StmtSet$new() set_stmt$add_field(ExprField$new("price"), ExprValue$new(9.50)) # Use the created SET statement inside an UPDATE query: query <- QueryUpdate$new(StmtUpdate$new("books"), set = set_stmt)
UPDATE statement.
UPDATE statement.
This class represents a SQL UPDATE statement. It requires a table name.
sqlq::Statement -> StmtUpdate
new()
Initializer.
StmtUpdate$new(tabl)
tablA table name.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtUpdate$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtUpdate$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create an UPDATE statement for table 'books': update <- StmtUpdate$new("books")# Create an UPDATE statement for table 'books': update <- StmtUpdate$new("books")
VALUES statement.
VALUES statement.
This class represents a SQL VALUES statement, used when inserting multiple rows.
sqlq::Statement -> StmtValues
new()
Initializer.
StmtValues$new(values)
valuesAn instance of ExprListValues
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtValues$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtValues$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create a VALUES statement with two rows: row1 <- ExprListValues$new(list(ExprValue$new("abc"), ExprValue$new(123))) row2 <- ExprListValues$new(list(ExprValue$new("def"), ExprValue$new(456))) values <- StmtValues$new(list(row1, row2))# Create a VALUES statement with two rows: row1 <- ExprListValues$new(list(ExprValue$new("abc"), ExprValue$new(123))) row2 <- ExprListValues$new(list(ExprValue$new("def"), ExprValue$new(456))) values <- StmtValues$new(list(row1, row2))
SQL WHERE statement.
SQL WHERE statement.
This class represents a SQL WHERE statement, used to filter results in SELECT, UPDATE, and DELETE statements.
sqlq::Statement -> StmtWhere
new()
Initializer.
StmtWhere$new(expr)
exprThe expression to evaluate.
Nothing.
getTokens()
Generates the list of tokens representing this statement.
StmtWhere$getTokens()
A list of Token objects.
clone()
The objects of this class are cloneable with this method.
StmtWhere$clone(deep = FALSE)
deepWhether to make a deep clone.
# Create a WHERE statement with a simple expression: expr <- ExprBinOp$new(ExprValue$new("age"), ">=", ExprValue$new(18)) where <- StmtWhere$new(expr) # Use the created WHERE statement inside a SELECT query: query <- QuerySelect$new(StmtSelectAll$new(), from = StmtFrom$new("users")) query$add(where)# Create a WHERE statement with a simple expression: expr <- ExprBinOp$new(ExprValue$new("age"), ">=", ExprValue$new(18)) where <- StmtWhere$new(expr) # Use the created WHERE statement inside a SELECT query: query <- QuerySelect$new(StmtSelectAll$new(), from = StmtFrom$new("users")) query$add(where)
Abstract Token class.
Abstract Token class.
This is an abstract class representing a SQL token. It is inherited by
concrete token classes such as TokenValue and TokenIdentifier.
toString()
Convert this object into a string.
Token$toString()
A character value.
clone()
The objects of this class are cloneable with this method.
Token$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example provided, as this class is abstract.# No example provided, as this class is abstract.
Empty token class.
Empty token class.
This class represents an empty SQL token. It is used in situations where a token is required by the structure of the code, but no actual SQL code needs to be generated.
sqlq::Token -> TokenEmpty
new()
Initializer.
TokenEmpty$new()
Nothing.
toString()
Converts into a string.
TokenEmpty$toString()
A string containing the SQL expression.
clone()
The objects of this class are cloneable with this method.
TokenEmpty$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example since this class is not exported.# No example since this class is not exported.
TokenIdentifier class.
TokenIdentifier class.
This class represents a SQL identifier token, such as a table or column name.
sqlq::Token -> TokenIdentifier
new()
Initializer.
TokenIdentifier$new(id)
idThe identifier.
Nothing.
toString()
Converts into a string.
TokenIdentifier$toString()
A string containing the SQL expression.
clone()
The objects of this class are cloneable with this method.
TokenIdentifier$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example since this class is not exported.# No example since this class is not exported.
TokenKeyword class.
TokenKeyword class.
Represents an SQL keyword such as SELECT, FROM, WHERE, etc.
sqlq::Token -> TokenKeyword
new()
Initializer.
TokenKeyword$new(kwd)
kwdThe keyword.
Nothing.
toString()
Converts into a string.
TokenKeyword$toString()
A string containing the SQL expression.
clone()
The objects of this class are cloneable with this method.
TokenKeyword$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example since this class is not exported.# No example since this class is not exported.
TokenSymbol class.
TokenSymbol class.
Represents a SQL symbol such as *, +, -, /, =, <, >, etc.
sqlq::Token -> TokenSymbol
new()
Initializer.
TokenSymbol$new(symbol)
symbolThe symbol.
Nothing.
toString()
Converts into a string.
TokenSymbol$toString()
A string containing the SQL expression.
clone()
The objects of this class are cloneable with this method.
TokenSymbol$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example since this class is not exported.# No example since this class is not exported.
Token value class.
Token value class.
Represents a SQL value such as a number or a string.
sqlq::Token -> TokenValue
new()
Initializer.
TokenValue$new(value)
valueThe value.
Nothing.
toString()
Converts into a string.
TokenValue$toString()
A string containing the SQL expression.
clone()
The objects of this class are cloneable with this method.
TokenValue$clone(deep = FALSE)
deepWhether to make a deep clone.
# No example since this class is not exported.# No example since this class is not exported.