---
title: "Untitled"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

# Accessing the text's sample flights DB.

```{r message = FALSE}
library(tidyverse)
library(DBI)
```

```{r}
library(mdsr)
db <- dbConnect_scidb("airlines")
flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")
airports <- tbl(db, "airports")
planes <- tbl(db, "planes")
```

# Accessing Ken's MariaDB version of the flights data

```{r}
db <- DBI::dbConnect(RMariaDB::MariaDB(), 
                     dbname = "airlines", 
                     host = "mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com", 
                     user = "mdsr_public", 
                     password = "ImhsmflMDSwR")
flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")
flights
carriers
```

# Loading my CS 262 PostgreSQL databases

Use one of these two configurations.

```{r echo = FALSE}
# For the cs262 monopoly database
DB_HOST <- "salt.db.elephantsql.com"
DB_USER <- "kfwlavxr"
DB_PASSWORD <- "FWeIbfINefN3Pg1cxb6UU3SWbDKvUWBf"
```

```{r echo = FALSE}
# For the cs262 spj database
DB_HOST <- "lallah.db.elephantsql.com"
DB_USER <- "fnujlqce"
DB_PASSWORD <- "hwlkhDFIuoO3VtCkU1ZLyu7MUs0Vr19p"
```

Load the data.

```{r}
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")
```

Query the data.

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

```{r}
part %>%
  left_join(supplier, by = c("supplierid" = "id")) %>%
  show_query()
```

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