<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><title>R: The class that provides the SQL functionality.</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="R.css" />
</head><body>

<table width="100%" summary="page for RSQL.class"><tr><td>RSQL.class</td><td style="text-align: right;">R Documentation</td></tr></table>

<h2>The class that provides the SQL functionality.</h2>

<h3>Description</h3>

<p>This class is intended to simplify SQL commands.
</p>


<h3>Public fields</h3>

<div class="r6-fields">

<dl>
<dt><code>driver</code></dt><dd><p>driver  name</p>
</dd>
<dt><code>db.name</code></dt><dd><p>database name</p>
</dd>
<dt><code>user</code></dt><dd><p>db user</p>
</dd>
<dt><code>password</code></dt><dd><p>db password</p>
</dd>
<dt><code>host</code></dt><dd><p>db host</p>
</dd>
<dt><code>port</code></dt><dd><p>db port</p>
</dd>
<dt><code>available.functions</code></dt><dd><p>for generating select expressions</p>
</dd>
<dt><code>entity.field.regexp</code></dt><dd><p>for scrape a field or table expression</p>
</dd>
<dt><code>entity.select.regexp</code></dt><dd><p>for scrape a select expressions expression</p>
</dd>
<dt><code>conn</code></dt><dd><p>The connection handler</p>
</dd>
<dt><code>valid.conn</code></dt><dd><p>Checks if connection is valid</p>
</dd>
<dt><code>last.query</code></dt><dd><p>The last query</p>
</dd>
<dt><code>last.rs</code></dt><dd><p>The last resultset</p>
</dd>
<dt><code>results.class</code></dt><dd><p>Expected class for db results for running dbClearResult</p>
</dd>
<dt><code>select.counter</code></dt><dd><p>An instance select counter</p>
</dd>
<dt><code>insert.counter</code></dt><dd><p>An instance insert counter</p>
</dd>
<dt><code>update.counter</code></dt><dd><p>An instance update counter</p>
</dd>
<dt><code>delete.counter</code></dt><dd><p>An instance delete counter</p>
</dd>
<dt><code>command.counter</code></dt><dd><p>An instance command counter</p>
</dd>
<dt><code>clear.rs.counter</code></dt><dd><p>An instance clear.rs.counter</p>
</dd>
<dt><code>logger</code></dt><dd><p>is conigured logger for current class</p>
</dd>
</dl>

</div>


<h3>Methods</h3>



<h4>Public methods</h4>


<ul>
<li> <p><a href="#method-new"><code>RSQL.class$new()</code></a>
</p>
</li>
<li> <p><a href="#method-connect"><code>RSQL.class$connect()</code></a>
</p>
</li>
<li> <p><a href="#method-setupResultClassFromDriver"><code>RSQL.class$setupResultClassFromDriver()</code></a>
</p>
</li>
<li> <p><a href="#method-setupRegexp"><code>RSQL.class$setupRegexp()</code></a>
</p>
</li>
<li> <p><a href="#method-finalize"><code>RSQL.class$finalize()</code></a>
</p>
</li>
<li> <p><a href="#method-checkEntitiesNames"><code>RSQL.class$checkEntitiesNames()</code></a>
</p>
</li>
<li> <p><a href="#method-gen_select"><code>RSQL.class$gen_select()</code></a>
</p>
</li>
<li> <p><a href="#method-gen_insert"><code>RSQL.class$gen_insert()</code></a>
</p>
</li>
<li> <p><a href="#method-gen_update"><code>RSQL.class$gen_update()</code></a>
</p>
</li>
<li> <p><a href="#method-gen_delete"><code>RSQL.class$gen_delete()</code></a>
</p>
</li>
<li> <p><a href="#method-execute_select"><code>RSQL.class$execute_select()</code></a>
</p>
</li>
<li> <p><a href="#method-execute_update"><code>RSQL.class$execute_update()</code></a>
</p>
</li>
<li> <p><a href="#method-execute_insert"><code>RSQL.class$execute_insert()</code></a>
</p>
</li>
<li> <p><a href="#method-execute_get_insert"><code>RSQL.class$execute_get_insert()</code></a>
</p>
</li>
<li> <p><a href="#method-execute_command"><code>RSQL.class$execute_command()</code></a>
</p>
</li>
<li> <p><a href="#method-execute_delete"><code>RSQL.class$execute_delete()</code></a>
</p>
</li>
<li> <p><a href="#method-retrieve"><code>RSQL.class$retrieve()</code></a>
</p>
</li>
<li> <p><a href="#method-retrieve_insert"><code>RSQL.class$retrieve_insert()</code></a>
</p>
</li>
<li> <p><a href="#method-clearLastResult"><code>RSQL.class$clearLastResult()</code></a>
</p>
</li>
<li> <p><a href="#method-getSummary"><code>RSQL.class$getSummary()</code></a>
</p>
</li>
<li> <p><a href="#method-disconnect"><code>RSQL.class$disconnect()</code></a>
</p>
</li>
<li> <p><a href="#method-clone"><code>RSQL.class$clone()</code></a>
</p>
</li></ul>


<hr>
<a id="method-new"></a>



<h4>Method <code>new()</code></h4>

<p>Initializes a connection
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$new(
  drv,
  dbname,
  user = NULL,
  password = NULL,
  host = NULL,
  port = NULL
)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>drv</code></dt><dd><p>driver name</p>
</dd>
<dt><code>dbname</code></dt><dd><p>database name</p>
</dd>
<dt><code>user</code></dt><dd><p>user name</p>
</dd>
<dt><code>password</code></dt><dd><p>password</p>
</dd>
<dt><code>host</code></dt><dd><p>host name</p>
</dd>
<dt><code>port</code></dt><dd><p>port number</p>
</dd>
</dl>

</div>


<hr>
<a id="method-connect"></a>



<h4>Method <code>connect()</code></h4>

<p>Function which connects to database
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$connect()</pre></div>



<h5>Returns</h5>

<p>conn object
</p>


<hr>
<a id="method-setupResultClassFromDriver"></a>



<h4>Method <code>setupResultClassFromDriver()</code></h4>

<p>Infer ResultsClass from corresponding driver. Implemented for SQLiteDriver &amp; PqConnection
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$setupResultClassFromDriver()</pre></div>



<h5>Returns</h5>

<p>RSQL object
</p>


<hr>
<a id="method-setupRegexp"></a>



<h4>Method <code>setupRegexp()</code></h4>

<p>initialize regexp for scraping entities
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$setupRegexp(force = FALSE)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>force</code></dt><dd><p>force setup?</p>
</dd>
</dl>

</div>



<h5>Returns</h5>

<p>regexp for scraping select expressions
</p>


<hr>
<a id="method-finalize"></a>



<h4>Method <code>finalize()</code></h4>

<p>Class destructor
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$finalize()</pre></div>


<hr>
<a id="method-checkEntitiesNames"></a>



<h4>Method <code>checkEntitiesNames()</code></h4>

<p>Checks if an entity exists
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$checkEntitiesNames(entities, entity.type)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>entities</code></dt><dd><p>entities to check</p>
</dd>
<dt><code>entity.type</code></dt><dd><p>entity type to check against</p>
</dd>
</dl>

</div>


<hr>
<a id="method-gen_select"></a>



<h4>Method <code>gen_select()</code></h4>

<p>Generates a select
</p>


<h5>Usage</h5>

<div class="r"><pre>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
)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>select_fields</code></dt><dd><p>fields to be selected</p>
</dd>
<dt><code>table</code></dt><dd><p>table to select from</p>
</dd>
<dt><code>where_fields</code></dt><dd><p>fields in the where clause</p>
</dd>
<dt><code>where_values</code></dt><dd><p>values to the fields on the where clause</p>
</dd>
<dt><code>group_by</code></dt><dd><p>fields to group by</p>
</dd>
<dt><code>order_by</code></dt><dd><p>fields to order by</p>
</dd>
<dt><code>top</code></dt><dd><p>where does the resultset starts?</p>
</dd>
<dt><code>distinct</code></dt><dd><p>provides a way to select distinct rows</p>
</dd>
</dl>

</div>


<hr>
<a id="method-gen_insert"></a>



<h4>Method <code>gen_insert()</code></h4>

<p>Generate  insert statement
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$gen_insert(table, values_df, insert_fields = names(values_df))</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>table</code></dt><dd><p>The table to insert into</p>
</dd>
<dt><code>values_df</code></dt><dd><p>The values to insert. Must be defined as data.frame of values</p>
</dd>
<dt><code>insert_fields</code></dt><dd><p>the fields to insert into</p>
</dd>
</dl>

</div>


<hr>
<a id="method-gen_update"></a>



<h4>Method <code>gen_update()</code></h4>

<p>Generate  insert statement
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$gen_update(
  table,
  update_fields = names(values),
  values,
  where_fields = names(where_values),
  where_values = NULL
)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>table</code></dt><dd><p>the table to insert into</p>
</dd>
<dt><code>update_fields</code></dt><dd><p>the fields to update</p>
</dd>
<dt><code>values</code></dt><dd><p>the values to update</p>
</dd>
<dt><code>where_fields</code></dt><dd><p>a where clause to the insert</p>
</dd>
<dt><code>where_values</code></dt><dd><p>the values to add to the where clause</p>
</dd>
</dl>

</div>


<hr>
<a id="method-gen_delete"></a>



<h4>Method <code>gen_delete()</code></h4>

<p>Generate a delete statement
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$gen_delete(
  table,
  where_fields = names(where_values),
  where_values = NULL
)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>table</code></dt><dd><p>the table to insert into</p>
</dd>
<dt><code>where_fields</code></dt><dd><p>a where clause to the insert</p>
</dd>
<dt><code>where_values</code></dt><dd><p>the fields to add to the where clause</p>
</dd>
</dl>

</div>


<hr>
<a id="method-execute_select"></a>



<h4>Method <code>execute_select()</code></h4>

<p>Performs an execution on the database
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$execute_select(sql_select)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>sql_select</code></dt><dd><p>the sql select statement to perform</p>
</dd>
</dl>

</div>


<hr>
<a id="method-execute_update"></a>



<h4>Method <code>execute_update()</code></h4>

<p>Performs an update on the database
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$execute_update(sql_update)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>sql_update</code></dt><dd><p>the sql update statement to perform</p>
</dd>
</dl>

</div>


<hr>
<a id="method-execute_insert"></a>



<h4>Method <code>execute_insert()</code></h4>

<p>Performs an insert on the database
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$execute_insert(sql_insert)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>sql_insert</code></dt><dd><p>the sql insert statement to perform</p>
</dd>
</dl>

</div>


<hr>
<a id="method-execute_get_insert"></a>



<h4>Method <code>execute_get_insert()</code></h4>

<p>Performs a select statement, if not exists, executes insert statement
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$execute_get_insert(sql_select, sql_insert)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>sql_select</code></dt><dd><p>the sql select statement to perform</p>
</dd>
<dt><code>sql_insert</code></dt><dd><p>the sql insert statement to perform</p>
</dd>
</dl>

</div>


<hr>
<a id="method-execute_command"></a>



<h4>Method <code>execute_command()</code></h4>

<p>Performs a command on the database
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$execute_command(sql_command)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>sql_command</code></dt><dd><p>the sql statement to perform</p>
</dd>
</dl>

</div>


<hr>
<a id="method-execute_delete"></a>



<h4>Method <code>execute_delete()</code></h4>

<p>Performs an deletion on the database
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$execute_delete(sql_delete)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>sql_delete</code></dt><dd><p>the sql delete statement to perform</p>
</dd>
</dl>

</div>


<hr>
<a id="method-retrieve"></a>



<h4>Method <code>retrieve()</code></h4>

<p>Performs an insert on the database. This is a composite function
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$retrieve(
  table,
  fields_uk = names(values_uk),
  values_uk,
  fields = names(values),
  values = NULL,
  field_id = "id"
)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>table</code></dt><dd><p>The table</p>
</dd>
<dt><code>fields_uk</code></dt><dd><p>The fields unique key</p>
</dd>
<dt><code>values_uk</code></dt><dd><p>The values unique key</p>
</dd>
<dt><code>fields</code></dt><dd><p>The fields (Not used. Included for compatibility)</p>
</dd>
<dt><code>values</code></dt><dd><p>The values (Not used. Included for compatibility)</p>
</dd>
<dt><code>field_id</code></dt><dd><p>The field of the serial id</p>
</dd>
</dl>

</div>


<hr>
<a id="method-retrieve_insert"></a>



<h4>Method <code>retrieve_insert()</code></h4>

<p>Obtain id if object exists on the database. Insert object if not.
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$retrieve_insert(
  table,
  fields_uk = names(values_uk),
  values_uk,
  fields = names(values),
  values = data.frame(),
  field_id = "id"
)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>table</code></dt><dd><p>The table</p>
</dd>
<dt><code>fields_uk</code></dt><dd><p>The fields unique key</p>
</dd>
<dt><code>values_uk</code></dt><dd><p>The values unique key</p>
</dd>
<dt><code>fields</code></dt><dd><p>The fields</p>
</dd>
<dt><code>values</code></dt><dd><p>The values</p>
</dd>
<dt><code>field_id</code></dt><dd><p>The field of the serial id</p>
</dd>
</dl>

</div>


<hr>
<a id="method-clearLastResult"></a>



<h4>Method <code>clearLastResult()</code></h4>

<p>clearLast Result for avoiding nasty warning
getSummary
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$clearLastResult()</pre></div>


<hr>
<a id="method-getSummary"></a>



<h4>Method <code>getSummary()</code></h4>

<p>get RSQL summary string
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$getSummary()</pre></div>


<hr>
<a id="method-disconnect"></a>



<h4>Method <code>disconnect()</code></h4>

<p>Disconnects the instance from the database
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$disconnect()</pre></div>


<hr>
<a id="method-clone"></a>



<h4>Method <code>clone()</code></h4>

<p>The objects of this class are cloneable with this method.
</p>


<h5>Usage</h5>

<div class="r"><pre>RSQL.class$clone(deep = FALSE)</pre></div>



<h5>Arguments</h5>

<div class="arguments">

<dl>
<dt><code>deep</code></dt><dd><p>Whether to make a deep clone.</p>
</dd>
</dl>

</div>




<h3>Author(s)</h3>

<p>ken4rab
</p>


<h3>Examples</h3>

<pre>
library(RSQL)
library(RSQLite)
db.name &lt;- getMtcarsdbPath(copy = TRUE)
rsql &lt;- createRSQL(drv = RSQLite::SQLite(), dbname = db.name)
select_sql &lt;- rsql$gen_select(
  select_fields = "*", # c("wt", "qsec"),
  table = "mtcars",
  where_values = data.frame(carb = 8)
)
mtcars.observed &lt;- rsql$execute_select(select_sql)
mtcars.observed

mtcars.new &lt;- mtcars.observed
mtcars.new$carb &lt;- 9
insert_sql &lt;- rsql$gen_insert(table = "mtcars", values_df = mtcars.new)
rsql$execute_insert(sql_insert = insert_sql)

where_values_df &lt;- data.frame(carb = 9)
select_sql &lt;- rsql$gen_select(
  select_fields = "*", # c("wt", "qsec"),
  table = "mtcars",
  where_values = data.frame(carb = 8)
)
mtcars.observed &lt;- rsql$execute_select(select_sql)
mtcars.observed
</pre>


</body></html>
