QueryNamespace {ResultModelManager}R Documentation

QueryNamespace

Description

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

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)
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"
  )
)
DatabaseConnector::disconnect(conn)

connectionHandler <- ConnectionHandler$new(connectionDetails = connectionDetails)
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))
# Returns query result
result <- cohortNamespace$queryDb(sql, cohort_id = 1)
# cleanup test data
unlink("test_db.sqlite")
[Package ResultModelManager version 0.5.9 Index]