Title: | Lazy SQL Programming |
---|---|
Description: | Helper functions to build SQL statements under program control for dbGetQuery, dbSendQuery, sqldf, etc. 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. |
Authors: | Uwe Block [aut, cre] |
Maintainer: | Uwe Block <[email protected]> |
License: | MIT + file LICENSE |
Version: | 0.1.3.9000 |
Built: | 2024-10-23 04:14:08 UTC |
Source: | https://github.com/uweblock/lazysql |
Create string with SQL BETWEEN
expression for WHERE
clause to select dates
within the given range.
date_between(column_name, date_range)
date_between(column_name, date_range)
column_name |
[character(1)] |
date_range |
[Date(1:2)] |
column_name
must be a valid SQL identifier. It is validated to conform to
the regular expression returned by valid_identifier_regex
.
Character string to be used in SQL statement.
Uwe Block
date1 <- as.Date("2016-02-22") date2 <- as.Date("2016-02-11") # SQL expression for a date range (sql_expr1 <- lazysql::date_between("STD_1", c(date1, date2))) # SQL expression for a single date (sql_expr2 <- lazysql::date_between("STD_1", date1)) # sample SQL statements paste("select * from TEST_TABLE where", sql_expr1) paste("select * from TEST_TABLE where", sql_expr2)
date1 <- as.Date("2016-02-22") date2 <- as.Date("2016-02-11") # SQL expression for a date range (sql_expr1 <- lazysql::date_between("STD_1", c(date1, date2))) # SQL expression for a single date (sql_expr2 <- lazysql::date_between("STD_1", date1)) # sample SQL statements paste("select * from TEST_TABLE where", sql_expr1) paste("select * from TEST_TABLE where", sql_expr2)
Create string with SQL IN
expression for WHERE
clause to select values
included in a set of given values.
in_condition(column_name, choices, negation = c("", "not"))
in_condition(column_name, choices, negation = c("", "not"))
column_name |
[character(1)] |
choices |
[character(1:Inf)] or [integer(1:Inf)] |
negation |
[character(1)] |
column_name
must be a valid SQL identifier. It is validated to conform to
the regular expression returned by valid_identifier_regex
.
Character string to be used in SQL statement.
Uwe Block
# SQL expressions lazysql::in_condition("COL_1", 1:3) lazysql::in_condition("COL_1", 1:3, "not") lazysql::in_condition("COL_1", LETTERS[2:3]) lazysql::in_condition("COL_1", LETTERS[2:3], "not")
# SQL expressions lazysql::in_condition("COL_1", 1:3) lazysql::in_condition("COL_1", 1:3, "not") lazysql::in_condition("COL_1", LETTERS[2:3]) lazysql::in_condition("COL_1", LETTERS[2:3], "not")
Helper functions to build SQL statements under program control
for dbGetQuery
, dbSendQuery
,
sqldf
, etc.
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.
Uwe Block
date_between
,
in_condition
,
natural_key
Create string with SQL expressions for WHERE
clause
to join two tables on the given columns.
natural_key(table_names, key_columns)
natural_key(table_names, key_columns)
table_names |
[character(2)] |
key_columns |
[character(1:Inf)] |
The names of tables and key columns must be valid SQL identifiers.
They are validated to conform to
the regular expression returned by valid_identifier_regex
.
The SQL string is created in 3 steps:
Combine table names with key names, eg, "PRL.FLIGHT_NR
".
Create logical expressions, eg, "PRL.FLIGHT_NR = PRL_SSR.FLIGHT_NR
"
Concatenate logical expressions by "and"
to form final SQL esxpression.
Character string to be used in SQL statement.
The current implementation assumes that key columns have the same names in both tables.
Uwe Block
# SQL expression (sql_expr <- lazysql::natural_key(c("TAB1", "tab_2"),c("COL1", "col_2"))) # sample SQL JOIN statement paste("select * from TAB1, TAB2 where", sql_expr)
# SQL expression (sql_expr <- lazysql::natural_key(c("TAB1", "tab_2"),c("COL1", "col_2"))) # sample SQL JOIN statement paste("select * from TAB1, TAB2 where", sql_expr)
Returns a regular expression to validate unquoted SQL identifiers.
valid_identifier_regex()
valid_identifier_regex()
Valid SQL identifiers must begin with an alphabetic character followed by
alphanumeric characters or underscores "_
".
Character string with regular expression.
The current implementation doesn't allow any other special characters in SQL identfiers or quoted SQL identifiers for safety reasons. In future releases, valid SQL identifiers might be defined depending on the target database system.
Uwe Block
ORACLE Database SQL Language Reference.
lazysql::valid_identifier_regex()
lazysql::valid_identifier_regex()