## Some simple examples related the Chapter 16 concepts:

# Reading in a .csv file from an external location and creating a data frame 'BP_full':

BP_full <- readr::read_csv(file="https://people.stat.sc.edu/hitchcock/Table6_8.csv")

# Creating a connection to a database driver:

con <- dbConnect(RSQLite::SQLite(), ":memory:")

# Here, ":memory:" is a special path that creates an in-memory database.
# Other database drivers require more details about the host, user, etc.

# Writing the data frame to a table in the database:
dbWriteTable(con, "presBP", BP_full)

# reading (and printing) the table:
dbReadTable(con, "presBP")

# Creating another table that is an exact copy of the 'presBP' table
# before we start updating 'presBP'

dbExecute(con, "CREATE TABLE presBPcopy AS SELECT * FROM presBP")
dbReadTable(con, "presBPcopy")

### Now doing some updates and alterations on the original 'presBP' table:

# Inserting a new row into the table:

dbSendQuery(conn = con,
            "INSERT INTO presBP
         VALUES ('DJT', 'before', 120, 60, '1/15/2016')")

dbReadTable(con, "presBP")



# Doing individual updates on the table:

dbSendQuery(conn = con,
            "UPDATE presBP
             SET sbp= 122
             WHERE subject='DJT' ")

dbReadTable(con, "presBP")

dbSendQuery(conn = con,
            "UPDATE presBP
             SET sbp= 123, dbp=62
             WHERE subject='DJT' ")

dbReadTable(con, "presBP")

# Adding a column to the table and filling in values:

#dbGetQuery(con, "ALTER TABLE presBP ADD COLUMN party TEXT") # Gives warning, better to use 'dbExecute':

dbExecute(con, "ALTER TABLE presBP ADD COLUMN party TEXT")

dbGetQuery(con, "SELECT * FROM presBP")

dbSendQuery(conn = con,
            "UPDATE presBP
             SET party='Rep'
             WHERE subject='DJT' OR subject='GWB' ")

dbSendQuery(conn = con,
            "UPDATE presBP
             SET party='Dem'
             WHERE subject!='DJT' AND subject!='GWB' ")

dbGetQuery(con, "SELECT * FROM presBP")