R/create_schema.R

In Tychobra/polishedapi: Authentication and Administration API for Shiny apps

#' create_schema
#'
#' create all the tables in the "polished" schema.  The "polished" schema contains the following tables.
#'  - accounts
#'  - users
#'  - apps
#'  - app_users
#'  - sessions
#'  - daily_sessions
#'
#' @param conn the `RPostgres` database connection.  Only `RPostgres` is supported.
#' @param schema the name of the schema
#'
#' @importFrom DBI dbExecute
#'
#' @export
#'
#'
create_schema <- function(conn, schema = "polished") {

  # For UUID generate
  DBI::dbExecute(conn, 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')
  # for encryption
  DBI::dbExecute(conn, 'CREATE EXTENSION IF NOT EXISTS pgcrypto;')

  create_accounts_table_query <- paste0("CREATE TABLE ", schema, ".accounts (
    uid                            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email                          TEXT UNIQUE,
    polished_key                   TEXT,
    hashed_polished_key            TEXT,
    created_at                     TIMESTAMPTZ NOT NULL DEFAULT NOW()
  )")

  create_users_table_query <- paste0("CREATE TABLE ", schema, ".users (
    uid                            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    account_uid                    UUID REFERENCES polished.accounts(uid),
    email                          TEXT,
    created_by                     TEXT NOT NULL,
    created_at                     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_by                    TEXT NOT NULL,
    modified_at                    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (account_uid, email)
  )")


  create_apps_table_query <- paste0("CREATE TABLE ", schema, ".apps (
    uid                   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    account_uid           UUID REFERENCES polished.accounts(uid),
    app_name              TEXT,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (account_uid, app_name)
  )")

  create_app_users_table_query <- paste0("CREATE TABLE ", schema, ".app_users (
    uid                   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    account_uid           UUID REFERENCES polished.accounts(uid),
    app_uid               UUID REFERENCES polished.apps(uid) ON DELETE CASCADE,
    user_uid              UUID REFERENCES polished.users(uid) ON DELETE CASCADE,
    is_admin              BOOLEAN NOT NULL,
    created_by            TEXT NOT NULL,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_by           TEXT NOT NULL,
    modified_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (account_uid, app_uid, user_uid)
  )")


  create_sessions_table_query <- paste0("CREATE TABLE ", schema, ".sessions (
    uid                   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    account_uid           UUID REFERENCES polished.accounts(uid) ON DELETE CASCADE,
    user_uid              UUID REFERENCES polished.users(uid) ON DELETE CASCADE,
    email                 TEXT,
    email_verified        BOOLEAN,
    hashed_cookie         TEXT,
    signed_in_as          TEXT,
    app_uid               TEXT,
    is_active             BOOLEAN DEFAULT true,
    is_signed_in          BOOLEAN DEFAULT true,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
  )")

  create_daily_sessions_table_query <- paste0("CREATE TABLE ", schema, ".daily_sessions (
    uid                     UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    account_uid             UUID,
    app_uid                 UUID,
    date_                   DATE,
    user_uid                UUID,
    n_sessions              INTEGER
  );")

  DBI::dbExecute(conn, paste0("CREATE SCHEMA IF NOT EXISTS ", schema))
  DBI::dbExecute(conn, paste0("DROP TABLE IF EXISTS ", schema, ".accounts CASCADE"))
  DBI::dbExecute(conn, paste0("DROP TABLE IF EXISTS ", schema, ".users CASCADE"))
  DBI::dbExecute(conn, paste0("DROP TABLE IF EXISTS ", schema, ".apps CASCADE"))
  DBI::dbExecute(conn, paste0("DROP TABLE IF EXISTS ", schema, ".app_users CASCADE"))
  DBI::dbExecute(conn, paste0("DROP TABLE IF EXISTS ", schema, ".sessions"))
  DBI::dbExecute(conn, paste0("DROP TABLE IF EXISTS ", schema, ".daily_sessions"))

  DBI::dbExecute(conn, create_accounts_table_query)
  DBI::dbExecute(conn, create_users_table_query)
  DBI::dbExecute(conn, create_apps_table_query)
  DBI::dbExecute(conn, create_app_users_table_query)
  DBI::dbExecute(conn, create_sessions_table_query)
  DBI::dbExecute(conn, create_daily_sessions_table_query)
}