spsDb {systemPipeShiny} | R 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

- `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'")

[Package systemPipeShiny version 1.4.0 Index]