SPS database functions

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()
#> [SPS-INFO] 2021-09-18 02:42:28 Created SPS database method container
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Default SPS-db found and is working
mydb$createDb()
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Creating SPS db...
#> [SPS-INFO] 2021-09-18 02:42:28 Db write the meta table
#> [SPS-INFO] 2021-09-18 02:42:28 Db write the raw table
#> [SPS-INFO] 2021-09-18 02:42:28 Key generated and stored in db
#> [SPS-INFO] 2021-09-18 02:42:28 Db create admin account
#> [SPS-DANGER] 2021-09-18 02:42:28 Done, Db created at 'config/sps.db'. DO NOT share this file with others or upload to open access domains.
#> [SPS-INFO] 2021-09-18 02:42:28 Key md5 e0451d6976e6f8e3430b6253cb7d97d9
mydb$queryValue("sps_meta")
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Query sent
#>            info          value
#> 1 creation_date 20210918024228
mydb$queryInsert("sps_meta", value = "'new1', '1'")
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Inerted 1 rows
mydb$queryValue("sps_meta")
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Query sent
#>            info          value
#> 1 creation_date 20210918024228
#> 2          new1              1
mydb$queryInsert("sps_meta", value = c("'new2'", "'2'"))
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Inerted 1 rows
mydb$queryValue("sps_meta")
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Query sent
#>            info          value
#> 1 creation_date 20210918024228
#> 2          new1              1
#> 3          new2              2
mydb$queryUpdate("sps_meta", value = '234',
                 col = "value", WHERE = "info = 'new1'")
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Updated 1 rows
mydb$queryValue("sps_meta")
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Query sent
#>            info          value
#> 1 creation_date 20210918024228
#> 2          new1            234
#> 3          new2              2
if (FALSE) {
    library(dplyr)
    mydb$queryValueDp(
        "sps_meta",
        dp_expr="filter(., info %in% c('new1', 'new2') %>% select(2)")
}
mydb$queryDel("sps_meta", WHERE = "value = '234'")
#> [SPS-INFO] 2021-09-18 02:42:28 Db connected
#> [SPS-INFO] 2021-09-18 02:42:28 Deleted 1 rows