spsDb: SPS database functions

In systemPipeR/systemPipeShiny: systemPipeShiny: An Interactive Framework for Workflow Management and Visualization

SPS database functions

Description

Initiate this container at global level. Methods in this class can help admin to manage general information of SPS. For now it stores some meta data, the encryption key pairs and the account info. You can use this database to store other useful things, like user password hash, IP, browsing info ...

A SQLite database by default is created inside config directory. If not, you can use createDb method to create one. On initiation, this class checks if the default db is there and gives warnings if not.

One instance of this class is created by the spsAccount super class in global.R, normal users don't need to change anything.

Methods

Public methods

• spsDb$new()

• spsDb$createDb()

• spsDb$queryValue()

• spsDb$queryValueDp()

• spsDb$queryUpdate()

• spsDb$queryDel()

• spsDb$queryInsert()

• spsDb$clone()

---

Method new()

initialize a new class object

Usage

spsDb$new()

---

Method createDb()

Create a SPS database

Usage

spsDb$createDb(db_name = "config/sps.db")

Arguments

db_name

database path, you need to manually create parent directory if not exists

---

Method queryValue()

Query database

Usage

spsDb$queryValue(table, SELECT = "*", WHERE = "1", db_name = "config/sps.db")

Arguments

table

table name

SELECT

SQL select grammar

WHERE

SQL where grammar

db_name

database path

Returns

query result, usually a dataframe

---

Method queryValueDp()

Query database with dplyr grammar

Only supports simple selections, like comparison, %in%, between(), is.na(), etc. Advanced selections like wildcard, using outside dplyr functions like ⁠[stringr::str_detect()]⁠, ⁠[base::grepl()]⁠ are not supported.

Usage

spsDb$queryValueDp(
  table,
  dp_expr = "select(., everything())",
  db_name = "config/sps.db"
)

Arguments

table

table name

dp_expr

dplyr chained expression, must use '.' in first component of the chain expression

db_name

database path

Returns

query result, usually a tibble

---

Method queryUpdate()

update(modify) the value in db

Usage

spsDb$queryUpdate(table, value, col, WHERE = "1", db_name = "config/sps.db")

Arguments

table

table name

value

new value

col

which column

WHERE

SQL where statement, conditions to select rows

db_name

database path

---

Method queryDel()

delete value in db

Usage

spsDb$queryDel(table, WHERE = "1", db_name = "config/sps.db")

Arguments

table

table name

WHERE

SQL where statement, conditions to select rows

db_name

database path

---

Method queryInsert()

Insert value to db

Usage

spsDb$queryInsert(table, value, db_name = "config/sps.db")

Arguments

table

table name

value

new values for the entire row, collect all values from all columns in a vector.

db_name

database path

---

Method clone()

The objects of this class are cloneable with this method.

Usage

spsDb$clone(deep = FALSE)

Arguments

deep

Whether to make a deep clone.

Examples

dir.create("config", showWarnings = FALSE)
mydb <- spsDb$new()
mydb$createDb()
mydb$queryValue("sps_meta")
mydb$queryInsert("sps_meta", value = "'new1', '1'")
mydb$queryValue("sps_meta")
mydb$queryInsert("sps_meta", value = c("'new2'", "'2'"))
mydb$queryValue("sps_meta")
mydb$queryUpdate("sps_meta", value = '234',
                 col = "value", WHERE = "info = 'new1'")
mydb$queryValue("sps_meta")
## Not run: 
    library(dplyr)
    mydb$queryValueDp(
        "sps_meta",
        dp_expr="filter(., info %in% c('new1', 'new2') %>% select(2)")

## End(Not run)
mydb$queryDel("sps_meta", WHERE = "value = '234'")

systemPipeR/systemPipeShiny documentation built on Oct. 17, 2023, 3:40 a.m.