---
title: "Database Query Demo"
author: "Keith VanderLinden"
date: "Spring, 2022"
output: html_document
---

```{r echo=FALSE}
# the login information for the SQJ database
DB_HOST <- "lallah.db.elephantsql.com"
DB_USER <- "fnujlqce"
DB_PASSWORD <- "hwlkhDFIuoO3VtCkU1ZLyu7MUs0Vr19p"
```

This document demonstrates data queries using both `dplyr` and SQL commands. The data is from a toy suppliers-parts-jobs database running on a cloud, SQL data service. 

```{r warning=FALSE, message=FALSE}
library(tidyverse)
```

## Loading the Database

We start by establishing a *connection* to the SPJ database currently stored on [ElephantSQL](https://www.elephantsql.com/), a cloud-based database system. ElephantSql hosts this database in a manner similar to the AWS-hosted database used in the textbook. And we connect to it in same manner.

```{r}
library(DBI)

db <- DBI::dbConnect(RPostgres::Postgres(), 
                     host = DB_HOST, 
                     port = 5432,
                     user = DB_USER,
                     password = DB_PASSWORD)

dbListTables(db)

supplier <- tbl(db, "supplier")
part <- tbl(db, "part")
job <- tbl(db, "job")
partjob <- tbl(db, "partjob")
```

As a reminder, here is the SQL schema.

![SPJ Schema](images/spj-schema.png)

## Querying Data

Queries retrieve data from databases. As with `dplyr` wrangling, the nature of the query depends on the structure of the underlying data.

### Single-Table Queries

First, we retrieve the data from a single table (`part`). Note that the result acts like a tibble but is actually taken directly from the cloud-based, PostgreSQL database (see the &ldquo;Source&rdquo; and &ldquo;Database:&rdquo; values).

```{r}
part %>% 
  show_query()
part
```

We include both the retrieved data and the SQL command used to do the retrieval. You can compare and contrast the equivalent queries in `dplyr` and SQL. 

We can run (basically) the same SQL query directly in the cloud service. Note the use of `*` to query all fields.

```{sql eval=FALSE}
SELECT * 
  FROM Part
```
 
Note also that if we modify the data on the cloud service, the local query results change as well. Students should not modify this shared cloud resource.

### Multiple-Table Queries: 1-M

Second, we retrieve the data from a 1-to-many join. In the SPJ database, a supplier can supply many parts, but a part must come from one supplier. This may not be realistic, but for the sake of the example we assume that this is a business rule, perhaps intended to ensure the consistency of parts used on jobs.

```{r}
part %>%
  left_join(supplier, 
            by = c("supplierid" = "id"),
            suffix = c(".part", ".supplier"),
            ) %>%
  select(name.part, price, name.supplier) %>%
  show_query()
part %>%
  left_join(supplier, 
            by = c("supplierid" = "id"),
             suffix = c(".part", ".supplier"),
            ) %>%
  select(name.part, price, name.supplier)
```
Note, that the output of this query includes the sort of redundant data we&rsquo;d prefer to avoid in our database structures, but that this redundancy is being automatically produced by the query --- the underlying database is *normalized*.

Here&rsquo;s a cleaned up, SQL-standard version of this join query.

```{sql eval = FALSE}
SELECT Part.name AS PartName, price, Supplier.name as SupplierName 
  FROM Part
  JOIN Supplier ON supplier.ID = Part.supplierID
```

Note that the SQL `AS` command performs the same basic renaming function performed by the `dplyr` `suffix` argument.

### Multiple-Table Queries: M-M

Finally, we retrieve data from a many-to-many join. In the SPJ database, a part can be supplied to many different jobs, and a job can use many different parts. 

```{r}
partjob %>%
  inner_join(part, 
             by = c("partid" = "id"),
             ) %>%
  inner_join(job, 
             by = c("jobid" = "id"),
             suffix = c(".part", ".job"),
             ) %>%
  select(name.part, price, name.job, quantity, address, date) %>%
  show_query()
partjob %>%
  inner_join(part, 
             by = c("partid" = "id"),
             ) %>%
  inner_join(job, 
             by = c("jobid" = "id"),
             suffix = c(".part", ".job"),
             ) %>%
  select(name.part, price, name.job, quantity, address, date)
```

Here&rsquo;s a cleaned up, SQL-standard version of this join query. 

```{sql eval = FALSE}
SELECT Part.name AS PartName, price, Job.name as JobName, quantity, address, date
  FROM PartJob
  JOIN Part ON PartJob.partID = Part.ID
  JOIN Job ON PartJob.jobID = Job.ID
```

## Summary

This document has demoed the most common data query types using both `dplyr` and SQL.
