DBI_tables_create: Create RegLog-valid database tables with DBI

Description

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
)

Value

List with results of the creation

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.

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

Run this code

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