# `DBI`, `odbc`, and RDBMS systems

## Installing Drivers

There are two generic packages that are useful to establish a database connection with.

-   `DBI`: Database Interface
-   `odbc`: Open Database Connectivity

The prior provides a unified frontend for working with databases while the later provides connection drivers.

```{r install-db-pkgs, eval = FALSE}
install.packages(c("DBI", "odbc"))
```

From here, we'll want to also install DBI-compliant implementations for each database engine we are interested in using. To date, there are:

-   [RSQLite](https://cran.r-project.org/package=RSQLite)
-   [bigrquery](https://cran.r-project.org/package=bigrquery)
-   [RMySQL](https://cran.r-project.org/package=RMySQL)
-   [RMariaDB](https://cran.r-project.org/package=RMariaDB)
-   [RPostgreSQL](https://cran.r-project.org/package=RPostgreSQL)
-   [ROracle](https://cran.r-project.org/package=ROracle)

For these kinds of databases, we'll need to connect in using the `odbc` package. This will require ODBC drivers to be installed on your local computer. Installation for each driver is operating system specific (e.g. different instructions for Windows, macOS, Linux...). An overview for each platform is provided at: <https://solutions.posit.co/connections/db/best-practices/drivers/>

```{r db-pkgs, eval = FALSE}
# Install DBI-compliant database packages
install.packages(c(
  "RSQLite",
  "bigrquery",
  "RMySQL",
  "RMariaDB",
  "ROracle",
  "RPostgres"
))

# Install a `dplyr` to SQL translator package
install.packages("dbplyr")
```

# SQL

## Establish Database Connection

Here we establish a SQLite database.

```{r}
#| label: unlink-database-if-exists
#| echo: false
unlink("my_db.sqlite")
```

```{r}
#| label: connect-db
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "my_db.sqlite")
```

### Aside: Creating a SQL Code Chunk

Now, Quarto Documents allows for other languages to be used through "code engines."

For example, we can use SQL by saying `sql` instead of `r`, c.f.

``` 
#| connection: db
SELECT 1
```

Yields:

```{sql}
#| label: demo-sql-chunk
#| connection: db
SELECT 1
```

Note: Each `sql` code chunk must have a connection supplied to it based on a value set using *R*. Thus, throughout the code chunks below, you will see:

    #| connection: db

The `db` command comes from the variable being established above.

### Aside: SQL Comments

Just like with BASH, we can create comments that are not read by a computer. SQL comments use two minus signs (`--`) followed by the comment. For example, we could have a code chunk with:

``` 
#| label: sql-chunk-with-comment
#| connection: db

-- This is a SQL comment

SELECT "Hello World"
```

The output would look like so:

```{sql}
#| label: sql-chunk-with-comment
#| connection: db

-- This is a SQL comment
SELECT "Hello World"
```

## CREATE TABLE: Creating a Database

With this being said, we'll use the `sql` code chunk option. Each code chunk must have only *one* command. Since we're trying to create three different tables, we'll create **three** different SQL tables: `Students`, `Courses`, and \`Grades.

``` 
#| label: create-students-table
#| connection: db
CREATE TABLE Students (
   id INTEGER,
   firstname TEXT,
   lastname TEXT,
   age REAL,
   instate INTEGER,
   PRIMARY KEY (id)
);
```

```{sql}
#| label: create-students-table-for-real
#| connection: db
#| echo: false
CREATE TABLE Students (
   id INTEGER,
   firstname TEXT,
   lastname TEXT,
   age REAL,
   instate INTEGER,
   PRIMARY KEY (id)
);
```

### Aside: Not specifying `sql`

Make sure to specify the appropriate code engine immediately after the curly bracket.

Notice: `{r}` is shown instead of `{sql}`

Failure to do so, will result in:

> Error: unexpected symbol in "CREATE TABLE"

### CREATE TABLE: Creating `Courses` and \`Grades

Now, back to creating the other two tables...

```{sql}
#| label: create-courses-table
#| connection: db
CREATE TABLE Courses (
   course_id TEXT,
   acronym TEXT,
   PRIMARY KEY (course_id)
);
```

```{sql}
#| label: create-grades-table
#| connection: db
CREATE TABLE Grades (
   student_id INTEGER,
   course_id TEXT,
   grade TEXT,
   FOREIGN KEY (student_id) REFERENCES Students(id),
   FOREIGN KEY (course_id) REFERENCES Courses(course_id),
   PRIMARY KEY (student_id, course_id)
);
```

## View Properties of Database

Let's check by viewing properties of the tables

```{r}
#| label: view-properties-of-db
## In R 

# The names of the tables.
DBI::dbListTables(db)

# The variable names for the Grades Table.
DBI::dbListFields(db, "Grades") 
```

**Note:** These tables are currently empty. If we look at the data for each table, we're missing them!

```{r}
#| label: verify-in-r-empty-table
dbReadTable(db, "Students")
```

## INSERT: Inserting Values

Next, we'll populate the table with values.

```{sql}
#| label: populate-students-table
#| connection: db

-- Populate Table
INSERT INTO Students VALUES
       (1, "Billy", "Joe", 23, 0),
       (2, "Theodore", "Squirrel", 25, 1),
       (3, "Keeya", "Nod", 21, 0);

```

```{sql}
#| label: populate-courses-table
#| connection: db

INSERT INTO Courses VALUES
       ("STAT385", "SPM"),
       ("STAT432", "BSL"),
       ("HIST100", "GH");
```

```{sql}
#| label: populate-grades-table
#| connection: db

INSERT INTO Grades VALUES
       (1, "STAT385", "A+"),
       (2, "STAT432", "A-"),
       (1, "HIST100", "A"),
       (3, "STAT385", "B+");
```

### Aside: Blocking two inserts data

If multiple `INSERT` statements are used with the same data, then the insert statement will be *rejected* due to the primary key requirement of unique data being present. That is, re-running the code chunk populating `Students` table

```{sql}
#| label: populate-students-twice
#| connection: db
#| eval: false

INSERT INTO Students VALUES
       (1, "Billy", "Joe", 23, 0),
       (2, "Theodore", "Squirrel", 25, 1),
       (3, "Keeya", "Nod", 21, 0);
```

Yields:

> UNIQUE constraint failed: Students.id

We can verify that the table was filled by looking at the contents in *R*.

```{r}
#| label: pull-data-into-r
my_local_Students_table = dbReadTable(db, "Students")

my_local_Students_table
```

## UPDATE: Modifying Values

Next, we can modify the data is stored using `UPDATE`

```{sql}
#| label: show-table-students-for-update
#| connection: db

UPDATE Students 
SET firstname = "James"
WHERE id = 1;
```

```{sql}
#| label: show-table-grades-for-update
#| connection: db
UPDATE Grades 
SET grade = "B"
WHERE student_id > 1;
```

### Your Turn: Updating a Value

Change the ages of anyone greater than 22 to 18 in the Students table.

```{sql}
#| label: modify-students-table
#| connection: db
#| eval: false

UPDATE Students 
SET age = 18
WHERE age > 22;
```

Verify change:

```{sql}
#| label: verify-modified-students-table
#| connection: db
SELECT * FROM Students;
```

## DELETE: Removing Records

Here, we're aiming to delete a row or record permanently from a Table.

::: {.callout-important}} Great care should be exercised here. :::

```{sql}
#| label: delete-records-students-table
#| connection: db
#| eval: false

DELETE FROM Students 
WHERE id = 2;
```

## SELECT: Selecting ALL values

Now, let's retrieve ALL the values from the `Student` table in the database.

```{sql}
#| label: select-student-table-records
#| connection: db

-- Retrieve all fields from the table
SELECT
  * 
FROM
  Students;
```

### Aside: Case Insensitive Commands

In *SQL*-land, there is a preference to SPEAK IN CAPITAL LETTERS. However, *SQL* is largely case insensitive. Thus, the same query in lower letters will also work

```{sql}
#| label: select-student-table-records-lower-case
#| connection: db

select
  * 
from
  Students;
```

### SELECT: Select two fields

From here, let's select only two fields from the `Students` data base.

```{sql}
#| label: select-student-table-two-fields
#| connection: db

-- Retrieve only the firstname and lastname from the table
SELECT 
  firstname, lastname
FROM 
  Students;
```

### SELECT: Select two fields

Now, let's try our hand a computation involving the average age inside of the `Students` data base.

```{sql}
#| label: select-student-table-mean
#| connection: db

-- Compute a mean of variable   
SELECT 
  AVG(age) as MeanAge
FROM
  Students;
```

### SELECT with R: Equivalents Operations

We can repeat the above selections using R. First, we'll have to export the data into R

```{r}
Students = DBI::dbReadTable(db, "Students")
```

To obtain all the data, we can type out the name of the `data.frame`

```{r}
Students
```

Retrieving variables from a `data.frame`.

```{r}

Students[, c("firstname", "lastname")]
```

The `dplyr` way would be:

```{r}
#| eval: false
library("dplyr")

Students %>% 
   select(firstname, lastname)
```

Create a summary statistic

```{r}
MeanAge = mean(Students$age)
```

And with `dplyr`...

```{r}
#| eval: false
Students %>% 
   summarise(MeanAge = mean(age))
```

## WHERE: Subsetting the Initial Data

We request only the instate students that are stored with `instate = 1` (equivalent to `TRUE`).

```{sql}
#| label: count-students-table-in-state
#| connection: db

SELECT 
    *
FROM
    Students
WHERE 
    instate = 1;
```

Alternatively, we can count how many students are out of state by using `instate = 0` (equivalent to `FALSE`).

```{sql}
#| label: count-students-table-out-of-state
#| connection: db

SELECT 
    COUNT(*) as NInstate
FROM
    Students
WHERE 
    instate = 0;
```

### GROUP BY: Aggregating by Group

Instead of performing two separate queries to identify group populations, we could instead *aggregate* information by membership in a group.

```{sql}
#| label: group-by-student-table-count-students
#| connection: db

SELECT 
    COUNT(*) as NInstate
FROM
    Students
GROUP BY
    instate
ORDER BY 
    NInstate ASC;
```

### HAVING: Subsetting a Group

Perform a subset on the grouped data based on the new results

```{sql}
#| label: having-student-table-restriction
#| connection: db
SELECT 
    COUNT(*) as NInstate
FROM
    Students
GROUP BY
    instate;
HAVING 
    NInstate > 1;
```

## DISTINCT: Select distinct

Finally, we can obtain distinct values by using the `DISTINCT` keyword neext to the field of interest.

```{sql}
#| label: distinct-student-table
#| connection: db

SELECT 
    COUNT(DISTINCT student_id) as NStudents
FROM
    Grades
```
