RSQL.class {RSQL}R Documentation

The class that provides the SQL functionality.

Description

This class is intended to simplify SQL commands.

Public fields

driver

driver name

db.name

database name

user

db user

password

db password

host

db host

port

db port

available.functions

for generating select expressions

entity.field.regexp

for scrape a field or table expression

entity.select.regexp

for scrape a select expressions expression

conn

The connection handler

valid.conn

Checks if connection is valid

last.query

The last query

last.rs

The last resultset

results.class

Expected class for db results for running dbClearResult

select.counter

An instance select counter

insert.counter

An instance insert counter

update.counter

An instance update counter

delete.counter

An instance delete counter

command.counter

An instance command counter

clear.rs.counter

An instance clear.rs.counter

tz

local timezone

logger

is conigured logger for current class

Methods

Public methods

Method new()

Initializes a connection

Usage
RSQL.class$new(
  drv,
  dbname,
  user = NULL,
  password = NULL,
  host = NULL,
  port = NULL,
  tz = Sys.timezone()
)
Arguments
drv

driver name

dbname

database name

user

user name

password

password

host

host name

port

port number

tz

actual time zone

Method checkConnection()

Function which check if db connection is valid

Usage
RSQL.class$checkConnection()
Returns

conn object

Method connect()

Function which connects to database

Usage
RSQL.class$connect()
Returns

conn object

Method setupResultClassFromDriver()

Infer ResultsClass from corresponding driver. Implemented for SQLiteDriver & PqConnection

Usage
RSQL.class$setupResultClassFromDriver()
Returns

RSQL object

Method setupRegexp()

initialize regexp for scraping entities

Usage
RSQL.class$setupRegexp(force = FALSE)
Arguments
force

force setup?

Returns

regexp for scraping select expressions

Method finalize()

Class destructor

Usage
RSQL.class$finalize()

Method checkEntitiesNames()

Checks if an entity exists

Usage
RSQL.class$checkEntitiesNames(entities, entity.type)
Arguments
entities

entities to check

entity.type

entity type to check against

Method gen_select()

Generates a select

Usage
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
)
Arguments
select_fields

fields to be selected

table

table to select from

where_fields

fields in the where clause

where_values

values to the fields on the where clause

group_by

fields to group by

order_by

fields to order by

top

where does the resultset starts?

distinct

provides a way to select distinct rows

Method gen_insert()

Generate insert statement

Usage
RSQL.class$gen_insert(table, values_df, insert_fields = names(values_df))
Arguments
table

The table to insert into

values_df

The values to insert. Must be defined as data.frame of values

insert_fields

the fields to insert into

Method gen_update()

Generate insert statement

Usage
RSQL.class$gen_update(
  table,
  update_fields = names(values),
  values,
  where_fields = names(where_values),
  where_values = NULL
)
Arguments
table

the table to insert into

update_fields

the fields to update

values

the values to update

where_fields

a where clause to the insert

where_values

the values to add to the where clause

Method gen_delete()

Generate a delete statement

Usage
RSQL.class$gen_delete(
  table,
  where_fields = names(where_values),
  where_values = NULL
)
Arguments
table

the table to insert into

where_fields

a where clause to the insert

where_values

the fields to add to the where clause

Method execute_select()

Performs an execution on the database

Usage
RSQL.class$execute_select(sql_select)
Arguments
sql_select

the sql select statement to perform

Method execute_update()

Performs an update on the database

Usage
RSQL.class$execute_update(sql_update)
Arguments
sql_update

the sql update statement to perform

Method execute_insert()

Performs an insert on the database

Usage
RSQL.class$execute_insert(sql_insert)
Arguments
sql_insert

the sql insert statement to perform

Method execute_get_insert()

Performs a select statement, if not exists, executes insert statement

Usage
RSQL.class$execute_get_insert(sql_select, sql_insert)
Arguments
sql_select

the sql select statement to perform

sql_insert

the sql insert statement to perform

Method execute_command()

Performs a command on the database

Usage
RSQL.class$execute_command(sql_command)
Arguments
sql_command

the sql statement to perform

Method execute_delete()

Performs an deletion on the database

Usage
RSQL.class$execute_delete(sql_delete)
Arguments
sql_delete

the sql delete statement to perform

Method retrieve()

Performs an insert on the database. This is a composite function

Usage
RSQL.class$retrieve(
  table,
  fields_uk = names(values_uk),
  values_uk,
  fields = names(values),
  values = NULL,
  field_id = "id"
)
Arguments
table

The table

fields_uk

The fields unique key

values_uk

The values unique key

fields

The fields (Not used. Included for compatibility)

values

The values (Not used. Included for compatibility)

field_id

The field of the serial id

Method retrieve_insert()

Obtain id if object exists on the database. Insert object if not.

Usage
RSQL.class$retrieve_insert(
  table,
  fields_uk = names(values_uk),
  values_uk,
  fields = names(values),
  values = data.frame(),
  field_id = "id"
)
Arguments
table

The table

fields_uk

The fields unique key

values_uk

The values unique key

fields

The fields

values

The values

field_id

The field of the serial id

Method clearLastResult()

clearLast Result for avoiding nasty warning getSummary

Usage
RSQL.class$clearLastResult()

Method getSummary()

get RSQL summary string

Usage
RSQL.class$getSummary()

Method disconnect()

Disconnects the instance from the database

Usage
RSQL.class$disconnect()

Method clone()

The objects of this class are cloneable with this method.

Usage
RSQL.class$clone(deep = FALSE)
Arguments
deep

Whether to make a deep clone.

Author(s)

ken4rab

Examples

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
[Package RSQL version 0.2.2 Index]