## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

## ----setup--------------------------------------------------------------------
library(RSQLite.toolkit)

## ----getdata------------------------------------------------------------------
library("piggyback", quietly = TRUE)
pb_download(file = "DOSE_V2.10.zip", dest = tempdir(),
            repo = "fab-algo/RSQLite.toolkit-tests", tag = "latest")

unzip(zipfile = file.path(tempdir(), "DOSE_V2.10.zip"), exdir = tempdir())
dir(file.path(tempdir(), "DOSE_V2.10"))

data_file <- file.path(tempdir(), "DOSE_V2.10/DOSE_V2.10.csv")

## ----inspect_file2------------------------------------------------------------
n_rows <- length(count.fields(data_file))
n_rows

## ----dbcon--------------------------------------------------------------------
dbcon <- dbConnect(RSQLite::SQLite(), file.path(tempdir(), "tests.sqlite"))

## ----label=loaddata1, error=TRUE----------------------------------------------
try({
## do not run: error
dbTableFromDSV(input_file = data_file, dbcon = dbcon, table_name = "DOSE")
})

## ----schema-------------------------------------------------------------------
f_schema1 <- file_schema_dsv(input_file = data_file,
                             quote = "\"", na.strings = "",
                             comment.char = "", fileEncoding = "UTF-8")

## ----schema_head--------------------------------------------------------------
f_schema1$schema[1:8, ]

## ----loaddata2----------------------------------------------------------------
dbTableFromDSV(input_file = data_file, dbcon = dbcon, table_name = "DOSE",
               drop_table = TRUE, quote = "\"", na.strings = "",
               comment.char = "", fileEncoding = "UTF-8")

## ----check_db-----------------------------------------------------------------
dbListTables(dbcon)

dbListFields(dbcon, "DOSE")[1:8]

dbGetQuery(dbcon, "SELECT COUNT(*) AS n_records FROM DOSE")

## ----getdata2-----------------------------------------------------------------
library("piggyback", quietly = TRUE)
pb_download(file = "Blockchain_Banking_Scopus_Dataset_2015_2025.zip",
            dest = tempdir(), repo = "fab-algo/RSQLite.toolkit-tests",
            tag = "latest")

unzip(zipfile = file.path(tempdir(),
                          "Blockchain_Banking_Scopus_Dataset_2015_2025.zip"),
      exdir = file.path(tempdir(), "Blockchain"))
dir(file.path(tempdir(), "Blockchain"))

data_file <- file.path(tempdir(),
                       "Blockchain/Blockchain_Banking_Scopus_Dataset_2015_2025.csv") # nolint

## ----inspect_colnames---------------------------------------------------------
f_schema2 <- file_schema_dsv(input_file = data_file,
                             quote = "\"", na.strings = "",
                             comment.char = "", fileEncoding = "UTF-8")

f_schema2$schema[1:10, ]

## ----inspect_colnames2--------------------------------------------------------
f_schema2 <- file_schema_dsv(input_file = data_file,
                             quote = "\"", na.strings = "",
                             id_quote_method = "SQL_SERVER",
                             comment.char = "", fileEncoding = "UTF-8")

f_schema2$schema[1:10, ]

## ----loaddata3----------------------------------------------------------------
dbTableFromDSV(input_file = data_file,
               dbcon = dbcon, table_name = "BLOCKCHAIN_BANKING",
               quote = "\"", na.strings = "",
               comment.char = "", fileEncoding = "UTF-8",
               id_quote_method = "SQL_SERVER",
               drop_table = TRUE)

## ----check_db2----------------------------------------------------------------
dbListTables(dbcon)

dbListFields(dbcon, "BLOCKCHAIN_BANKING")[1:8]

dbGetQuery(dbcon, "SELECT COUNT(*) AS n_records FROM BLOCKCHAIN_BANKING")

## ----close_db-----------------------------------------------------------------

dbDisconnect(dbcon)