spsDb: SPS database functions

spsDbR Documentation

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


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.