QueryNamespace

Given a results specification and ConnectionHandler instance - this class allow queries to be namespaced within any tables specified within a list of pre-determined tables. This allows the encapsulation of queries, using specific table names in a consistent manner that is striaghtforward to maintain over time.

Public fields

tablePrefix

tablePrefix to use

Methods

Public methods

• QueryNamespace$new()

• QueryNamespace$setConnectionHandler()

• QueryNamespace$getConnectionHandler()

• QueryNamespace$addReplacementVariable()

• QueryNamespace$addTableSpecification()

• QueryNamespace$render()

• QueryNamespace$queryDb()

• QueryNamespace$executeSql()

• QueryNamespace$getVars()

• QueryNamespace$finalize()

• QueryNamespace$clone()

---

Method new()

initialize class

Usage

QueryNamespace$new(
  connectionHandler = NULL,
  tableSpecification = NULL,
  tablePrefix = "",
  ...
)

Arguments

connectionHandler

ConnectionHandler instance @seealsoConnectionHandler

tableSpecification

tableSpecification data.frame

tablePrefix

constant string to prefix all tables with

...

additional replacement variables e.g. database_schema, vocabulary_schema etc Set Connection Handler

---

Method setConnectionHandler()

set connection handler object for object

Usage

QueryNamespace$setConnectionHandler(connectionHandler)

Arguments

connectionHandler

ConnectionHandler instance Get connection handler

---

Method getConnectionHandler()

get connection handler obeject or throw error if not set

Usage

QueryNamespace$getConnectionHandler()

---

Method addReplacementVariable()

add a variable to automatically be replaced in query strings (e.g. @database_schema.@table_name becomes 'database_schema.table_1')

Usage

QueryNamespace$addReplacementVariable(key, value, replace = FALSE)

Arguments

key

variable name string (without @) to be replaced, eg. "table_name"

value

atomic value for replacement

replace

if a variable of the same key is found, overwrite it add table specification

---

Method addTableSpecification()

add a variable to automatically be replaced in query strings (e.g. @database_schema.@table_name becomes 'database_schema.table_1')

Usage

QueryNamespace$addTableSpecification(
  tableSpecification,
  useTablePrefix = TRUE,
  tablePrefix = self$tablePrefix,
  replace = TRUE
)

Arguments

tableSpecification

table specification data.frame conforming to column names tableName, columnName, dataType and primaryKey

useTablePrefix

prefix the results with the tablePrefix (TRUE)

tablePrefix

prefix string - defaults to class variable set during initialization

replace

replace existing variables of the same name Render

---

Method render()

Call to SqlRender::render replacing names stored in this class

Usage

QueryNamespace$render(sql, ...)

Arguments

sql

query string

...

additional variables to be passed to SqlRender::render - will overwrite anything in namespace query Sql

---

Method queryDb()

Call to

Usage

QueryNamespace$queryDb(sql, ...)

Arguments

sql

query string

...

additional variables to send to SqlRender::render execute Sql

---

Method executeSql()

Call to execute sql within namespaced queries

Usage

QueryNamespace$executeSql(sql, ...)

Arguments

sql

query string

...

additional variables to send to SqlRender::render get vars

---

Method getVars()

returns full list of variables that will be replaced Destruct object

Usage

QueryNamespace$getVars()

---

Method finalize()

Close connections etc

Usage

QueryNamespace$finalize()

---

Method clone()

The objects of this class are cloneable with this method.

Usage

QueryNamespace$clone(deep = FALSE)

Arguments

deep

Whether to make a deep clone.

Examples

library(ResultModelManager)

# Create some junk test data
connectionDetails <-
  DatabaseConnector::createConnectionDetails(
    server = "test_db.sqlite",
    dbms = "sqlite"
  )

conn <- DatabaseConnector::connect(connectionDetails)
#> Connecting using SQLite driver
DatabaseConnector::insertTable(
  connection = conn,
  tableName = "cd_cohort",
  data = data.frame(
    cohort_id = c(1, 2, 3),
    cohort_name = c("cohort one", "cohort two", "cohort three"),
    json = "{}",
    sql = "SELECT 1"
  )
)
#> Inserting data took 0.0108 secs
DatabaseConnector::disconnect(conn)

connectionHandler <- ConnectionHandler$new(connectionDetails = connectionDetails)
#> Connecting using SQLite driver
tableSpecification <- data.frame(
  tableName = "cohort",
  columnName = c(
    "cohort_id",
    "cohort_name",
    "json",
    "sql"
  ),
  primaryKey = c(TRUE, FALSE, FALSE, FALSE),
  dataType = c("int", "varchar", "varchar", "varchar")
)

cohortNamespace <- QueryNamespace$new(
  connectionHandler = connectionHandler,
  tableSpecification = tableSpecification,
  result_schema = "main",
  tablePrefix = "cd_"
)
sql <- "SELECT * FROM @result_schema.@cohort WHERE cohort_id = @cohort_id"
# Returns : "SELECT * FROM main.cd_cohort WHERE cohort_id = @cohort_id"
print(cohortNamespace$render(sql))
#> [1] "SELECT * FROM main.cd_cohort WHERE cohort_id = cd_cohort_id"
# Returns query result
result <- cohortNamespace$queryDb(sql, cohort_id = 1)
# cleanup test data
unlink("test_db.sqlite")