Create RegLog-valid database tables with DBI

Usage

DBI_tables_create(
  conn,
  account_name = "account",
  reset_code_name = "reset_code",
  use_log = FALSE,
  log_name = "logs",
  user_data = NULL,
  hash_passwords = FALSE,
  verbose = TRUE
)

Arguments

conn: DBI connection object
account_name: Name of the table for storing user accounts credentials. Defaults to 'account'. Mandatory table.
reset_code_name: Name of the table for storing generated password reset codes. Defaults to 'reset_code'. Mandatory table.
use_log: Should the table for keeping RegLogServer logs be also created? Defaults to FALSE
log_name: Name of the table for storing logs from RegLogServer object. Used only if use_log = TRUE. Defaults to logs
user_data: If you wish to import existing user database, you can input data.frame with that table in this argument. It should contain columns: username, password, email. Defaults to NULL.
hash_passwords: If you are importing table of users upon tables creation, you can also specify if the password should be hashed using scrypt::hashPassword. Defaults to FALSE. If you have unhashed passwords in imported table, set this option to TRUE.
verbose: Boolean specific if the actions made by function should be printed back to the console. Defaults to TRUE.

Value

List with results of the creation

Details

Currently, the function is tested and working correctly for SQLite, MySQL, MariaDB and PostrgreSQL databases. If you want to use another DBI-supported database, you need to create tables in other ways.

Created tables should have following structure:

account (default name)
- id: integer, primary key, auto-increment
- username: varchar(255), NOT NULL, unique key
- password: varchar(255), NOT NULL
- email: varchar(255), NOT NULL, unique key
- create_time: datetime, NOT NULL
- update_time: datetime, NOT NULL
reset_code (default name)
- id: integer, primary key, auto-increment
- user_id: integer, NOT NULL, key
- reset_code: varchar(10), NOT NULL
- used: tinyint, NOT NULL
- create_time: datetime, NOT NULL
- update_time: datetime, NOT NULL
logs (default name, optional)
- id: integer, primary key, auto-increment
- time: datetime, NOT NULL
- session: varchar(255), NOT NULL
- direction: varchar(255), NOT NULL
- type: varchar(255), NOT NULL
- note: varchar(255)

Examples

library(shiny.reglog)

# create a tenporary SQLite database
conn <- DBI::dbConnect(
  RSQLite::SQLite(),
  dbname = ":memory:"
)

# mockup user data
user_data <- 
  data.frame(username = c("Whatever", "Hanuka", "Helsinki", "How_come"), 
             password = c("&f5*MSYj^niDt=V'3.[dyEX.C/", "%}&B[fs\\}5PKE@,*+V\\tx9\"at]", 
                    "35z*ofW\\'G_8,@vCC`]~?e$Jm%", "s:;r_eLn?-D6;oA-=\"^R(-Ew<x"), 
             email = c("what@mil.com", "hehe@soso.so", "nider@what.no", "crazzz@simpsy.com"))

# create the tables and input the data (hashing the passwords in the process)
DBI_tables_create(conn = conn,
                  user_data = user_data,
                  hash_passwords = TRUE,
                  verbose = FALSE)
#> $account
#> $account$table_name
#> [1] "account"
#> 
#> $account$result
#> [1] TRUE
#> 
#> $account$data_import
#> [1] 4
#> 
#> 
#> $reset_code
#> $reset_code$table_name
#> [1] "reset_code"
#> 
#> $reset_code$result
#> [1] TRUE
#> 
#> 

# check generater tables
DBI::dbListTables(conn = conn)
#> [1] "account"    "reset_code"

# check the "user" table for user data
DBI::dbReadTable(conn = conn,
                 "account")
#>   id username
#> 1  1 Whatever
#> 2  2   Hanuka
#> 3  3 Helsinki
#> 4  4 How_come
#>                                                                                                                           password
#> 1 c2NyeXB0ABAAAAAIAAAAAZUPJpeSq7KFCj8M/Dya0BtM4vTpD5/Erh2IRIcAfAQXKwf3PhK0ZBHhb+mjcLwXQuaK0bw5zFLBpu8yi98Xk3bJYr+m2iXHAtJa5+jOJ5kY
#> 2 c2NyeXB0ABAAAAAIAAAAAbD3l4cHvlQ9F2CT3Mx/zfRSYz0pk1iCKo9RJo9/bD9UyP4OUwoRgHrc6ab9DcgjXeA1Jy4J27OFpEwxc4ET96iKPjoWQo/an6lAqx2LfcVI
#> 3 c2NyeXB0ABAAAAAIAAAAASV5om9+9/T862bhS7M04xV7AHFLLtzp67rCj3taFvhYenhO4m5/RVot8WC22Z4oP0vlROlhbx2J88h+6JDvuMJbWYZEn403C1XTyIZzmJ2Z
#> 4 c2NyeXB0ABAAAAAIAAAAAc0gLbtkhC3ruuqA3UWlx+B+3B4t2CMoH1lzOq99GKn9wmkwFMtM2K9BsJNjtgOU3WNGIwFcp0HCiBKZTaO87Aq8MZYhBTiCb0BJWEWP/agz
#>               email             create_time             update_time
#> 1      what@mil.com 2022-08-31 18:02:54.305 2022-08-31 18:02:54.305
#> 2      hehe@soso.so 2022-08-31 18:02:54.305 2022-08-31 18:02:54.305
#> 3     nider@what.no 2022-08-31 18:02:54.305 2022-08-31 18:02:54.305
#> 4 crazzz@simpsy.com 2022-08-31 18:02:54.305 2022-08-31 18:02:54.305

# disconnect
DBI::dbDisconnect(conn = conn)