DBI_tables_create {shiny.reglog}

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

See Also

Other RegLog databases: gsheet_tables_create(), mongo_tables_create()

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("[e-mail]", "[e-mail]", "[e-mail]", "[e-mail]"))

# 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)