```{r}
#| label: setup
#| include: false
library(mosaic)   
library(tidyverse)
library(mdsr)
library(DBI)
library(RMySQL)
```

## Introduction

This document is intended to describe how to access data from a MySQL database using R.
It utilizes a database of wideband acoustic immitance variables from humans with normal hearing (see https://projectreporter.nih.gov/project_info_description.cfm?aid=8769352&icde=30039221&ddparam=&ddvalue=&ddsub=&cr=10&csb=default&cs=ASC for more details).

A relevant paper on the topic of data management and databases in R can be found at http://chance.amstat.org/2015/04/setting-the-stage.

See also 

- https://nicholasjhorton.github.io/K12-Data-Tools/icerm.html.
- https://hardin47.netlify.app/courses/sds261-sql
- https://beanumber.github.io/tidy-databases/
- https://mdsr-book.github.io/mdsr3e/15-sqlI.html


## Accessing data from a database using SQL commands

First I will demonstrate how to access data using SQL (structured query language) commands and the `dbGetQuery()` function.
We begin by setting up a connection 
to the database.

```{r}
library(mosaic)
library(tidyverse)
db <- DBI::dbConnect(
  RMySQL::MySQL(), 
  dbname = "wai", 
  host = "scidb.smith.edu", 
  user = "waiuser", 
  password = "smith_waiDB"
)
```

Next a series of SQL queries can be sent to the database.
These return R dataframes.

```{r}
dbGetQuery(db, "SHOW TABLES")
dbGetQuery(db, "EXPLAIN PI_Info")
dbGetQuery(db, "EXPLAIN Subjects")
dbGetQuery(db, "EXPLAIN Measurements")
first_ten <- dbGetQuery(db, "SELECT * from Measurements LIMIT 10")
first_ten
```

## Accessing a database using dplyr commands

Alternatively, a connection can be made to the server by creating a series of dplyr table objects.  

```{r}
Measurements <- tbl(db, "Measurements")
PI_Info <- tbl(db, "PI_Info")
Subjects <- tbl(db, "Subjects")
```

#### Let's explore the `PI_Info` table.
```{r}
PI_Info |> 
  collect() |> 
  summarise(total = n())
PI_Info |> 
  collect() |> 
  glimpse()   # collect() is a bad idea when dealing with large tables!
```

#### Let's explore the `Subjects` table.
```{r}
Subjects |> 
  collect()  # be careful with collect() with large tables!
```

#### Let's explore the `Measurements` table.

```{r}
#| error: true
Measurements |>
  summarise(total = n())
```

#### Let's download the data from a given subject from the Rosowski_2012 study

```{r}
onesubj <- Measurements |>
  filter(Identifier == "Rosowski_2012", SubjectNumber == 3) |>
  collect() |>
  mutate(SessionNum = as.factor(Session))
head(onesubj)
```

Finally we can plot the absorbance as a function of frequency, stratified by ear.


```{r}
ggplot(
  data = onesubj, 
  aes(x = Frequency, y = Absorbance)) + 
    geom_point() +
    aes(colour = Ear) + 
    scale_x_log10() + 
    labs(title = "Absorbance by ear Rosowski subject 3")
```



CHALLENGE #1: In groups of two: write SQL code to calculate which study has the largest number of measurements (rows)?
Which study has the largest number of subjects?

```{r}

```

CHALLENGE #2: In groups of two: write SQL code to find how many frequencies are measured in the Voss_1994 study as compared to the Voss_2010 study.

```{r}

```



```{r}
dbDisconnect(db) # please be polite and close the connection
```
