This class is intended to simplify SQL commands.
ken4rab
driverdriver name
db.namedatabase name
userdb user
passworddb password
hostdb host
portdb port
available.functionsfor generating select expressions
entity.field.regexpfor scrape a field or table expression
entity.select.regexpfor scrape a select expressions expression
connThe connection handler
valid.connChecks if connection is valid
last.queryThe last query
last.rsThe last resultset
results.classExpected class for db results for running dbClearResult
select.counterAn instance select counter
insert.counterAn instance insert counter
update.counterAn instance update counter
delete.counterAn instance delete counter
command.counterAn instance command counter
clear.rs.counterAn instance clear.rs.counter
tzlocal timezone
loggeris conigured logger for current class
new()Initializes a connection
RSQL.class$new(
drv,
dbname,
user = NULL,
password = NULL,
host = NULL,
port = NULL,
tz = Sys.timezone()
)drvdriver name
dbnamedatabase name
useruser name
passwordpassword
hosthost name
portport number
tzactual time zone
checkConnection()Function which check if db connection is valid
RSQL.class$checkConnection()conn object
connect()Function which connects to database
RSQL.class$connect()conn object
setupResultClassFromDriver()Infer ResultsClass from corresponding driver. Implemented for SQLiteDriver & PqConnection
RSQL.class$setupResultClassFromDriver()RSQL object
setupRegexp()initialize regexp for scraping entities
RSQL.class$setupRegexp(force = FALSE)forceforce setup?
regexp for scraping select expressions
finalize()Class destructor
RSQL.class$finalize()
checkEntitiesNames()Checks if an entity exists
RSQL.class$checkEntitiesNames(entities, entity.type)entitiesentities to check
entity.typeentity type to check against
gen_select()Generates a select
RSQL.class$gen_select(
select_fields,
table,
where_fields = names(where_values),
where_values = NULL,
group_by = c(),
order_by = c(),
top = 0,
distinct = FALSE
)select_fieldsfields to be selected
tabletable to select from
where_fieldsfields in the where clause
where_valuesvalues to the fields on the where clause
group_byfields to group by
order_byfields to order by
topwhere does the resultset starts?
distinctprovides a way to select distinct rows
gen_insert()Generate insert statement
RSQL.class$gen_insert(table, values_df, insert_fields = names(values_df))tableThe table to insert into
values_dfThe values to insert. Must be defined as data.frame of values
insert_fieldsthe fields to insert into
gen_update()Generate insert statement
RSQL.class$gen_update(
table,
update_fields = names(values),
values,
where_fields = names(where_values),
where_values = NULL
)tablethe table to insert into
update_fieldsthe fields to update
valuesthe values to update
where_fieldsa where clause to the insert
where_valuesthe values to add to the where clause
gen_delete()Generate a delete statement
RSQL.class$gen_delete(
table,
where_fields = names(where_values),
where_values = NULL
)tablethe table to insert into
where_fieldsa where clause to the insert
where_valuesthe fields to add to the where clause
execute_select()Performs an execution on the database
RSQL.class$execute_select(sql_select)sql_selectthe sql select statement to perform
execute_update()Performs an update on the database
RSQL.class$execute_update(sql_update)sql_updatethe sql update statement to perform
execute_insert()Performs an insert on the database
RSQL.class$execute_insert(sql_insert)sql_insertthe sql insert statement to perform
execute_get_insert()Performs a select statement, if not exists, executes insert statement
RSQL.class$execute_get_insert(sql_select, sql_insert)sql_selectthe sql select statement to perform
sql_insertthe sql insert statement to perform
execute_command()Performs a command on the database
RSQL.class$execute_command(sql_command)sql_commandthe sql statement to perform
execute_delete()Performs an deletion on the database
RSQL.class$execute_delete(sql_delete)sql_deletethe sql delete statement to perform
retrieve()Performs an insert on the database. This is a composite function
RSQL.class$retrieve(
table,
fields_uk = names(values_uk),
values_uk,
fields = names(values),
values = NULL,
field_id = "id"
)tableThe table
fields_ukThe fields unique key
values_ukThe values unique key
fieldsThe fields (Not used. Included for compatibility)
valuesThe values (Not used. Included for compatibility)
field_idThe field of the serial id
retrieve_insert()Obtain id if object exists on the database. Insert object if not.
RSQL.class$retrieve_insert(
table,
fields_uk = names(values_uk),
values_uk,
fields = names(values),
values = data.frame(),
field_id = "id"
)tableThe table
fields_ukThe fields unique key
values_ukThe values unique key
fieldsThe fields
valuesThe values
field_idThe field of the serial id
clearLastResult()clearLast Result for avoiding nasty warning getSummary
RSQL.class$clearLastResult()
getSummary()get RSQL summary string
RSQL.class$getSummary()
disconnect()Disconnects the instance from the database
RSQL.class$disconnect()
clone()The objects of this class are cloneable with this method.
RSQL.class$clone(deep = FALSE)deepWhether to make a deep clone.
library(RSQL)
library(RSQLite)
db.name <- getMtcarsdbPath(copy = TRUE)
rsql <- createRSQL(drv = RSQLite::SQLite(), dbname = db.name)
select_sql <- rsql$gen_select(
select_fields = "*", # c("wt", "qsec"),
table = "mtcars",
where_values = data.frame(carb = 8)
)
mtcars.observed <- rsql$execute_select(select_sql)
mtcars.observed
mtcars.new <- mtcars.observed
mtcars.new$carb <- 9
insert_sql <- rsql$gen_insert(table = "mtcars", values_df = mtcars.new)
rsql$execute_insert(sql_insert = insert_sql)
where_values_df <- data.frame(carb = 9)
select_sql <- rsql$gen_select(
select_fields = "*", # c("wt", "qsec"),
table = "mtcars",
where_values = data.frame(carb = 8)
)
mtcars.observed <- rsql$execute_select(select_sql)
mtcars.observed
Run the code above in your browser using DataLab