DBI_tables_create: Create RegLog-valid database tables with DBI
In shiny.reglog: Optional Login and Registration Module System for ShinyApps

DBI_tables_create

R Documentation

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
)

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)

Value

List with results of the creation

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)

# check generater tables
DBI::dbListTables(conn = conn)

# check the "user" table for user data
DBI::dbReadTable(conn = conn,
                 "account")

# disconnect
DBI::dbDisconnect(conn = conn)

shiny.reglog documentation built on Aug. 31, 2022, 1:06 a.m.