RSQL.class: The class that provides the SQL functionality.

In rOpenStats/rsql: Database Agnostic Package to Generate and Process 'SQL' Queries in R

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

• RSQL.class$new()

• RSQL.class$checkConnection()

• RSQL.class$connect()

• RSQL.class$setupResultClassFromDriver()

• RSQL.class$setupRegexp()

• RSQL.class$finalize()

• RSQL.class$checkEntitiesNames()

• RSQL.class$gen_select()

• RSQL.class$gen_insert()

• RSQL.class$gen_update()

• RSQL.class$gen_delete()

• RSQL.class$execute_select()

• RSQL.class$execute_update()

• RSQL.class$execute_insert()

• RSQL.class$execute_get_insert()

• RSQL.class$execute_command()

• RSQL.class$execute_delete()

• RSQL.class$retrieve()

• RSQL.class$retrieve_insert()

• RSQL.class$clearLastResult()

• RSQL.class$getSummary()

• RSQL.class$disconnect()

• RSQL.class$clone()

---

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

rOpenStats/rsql documentation built on Aug. 22, 2023, 2:16 a.m.