Learn R Programming
RSQL (version 0.1.3)

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

Description

This class is intended to simplify SQL commands.

Arguments

Public fields

driver

driver name

db.name

database name

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

last.query

The last query

last.rs

The last resultset

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

Methods

Public methods

Method new()

Initializes a connection

Usage

RSQL.class$new(
  drv,
  dbname,
  user = NULL,
  password = NULL,
  host = NULL,
  port = NULL
)

Arguments

drv

driver name

dbname

database name

user

user name

password

password

host

host name

port

port number

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_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,
  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,
  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 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.

Examples

Run this code
# NOT RUN {
library(RSQL)
db.name <- getMtcarsdbPath(copy = TRUE)
rsql <- createRSQL(drv = RSQLite::SQLite(), dbname = db.name)
where_values_df <- data.frame(carb = 8, stringsAsFactors = FALSE)
select_sql <- rsql$gen_select(
 select_fields = "*", #c("wt", "qsec"),
 table = "mtcars",
 where_values = where_values_df)
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, stringsAsFactors = FALSE)
select_sql <- rsql$gen_select(
  select_fields = "*", #c("wt", "qsec"),
 table = "mtcars",
 where_values = where_values_df)
mtcars.observed <- rsql$execute_select(select_sql)
mtcars.observed

# }

Run the code above in your browser using DataLab