#slide 2
```{r}
library(RSQLite)
library(tidyverse)

if(!"discoCopy.db" %in% list.files(".")){
file.copy("disco.db"
,
"discoCopy.db")
} # Modificaremos esse arquivo
```

```{r}
db <- dbConnect(SQLite(),
"discoCopy.db")
```

#slide 4
```{r}
dbListTables(db)
```

```{r}
dbExecute(db,
"CREATE TABLE instruments
(AlbumId INTEGER,
TrackId INTEGER,
ElectricGuitar INTEGER,
Singer INTEGER,
Trumpet INTEGER)")
```

#slide 5
```{r}
dbListFields(db,
'instruments')
```

```{r}
dbExecute(db,
"DROP TABLE instruments")
```

```{r}
dbListTables(db)
```

#slide 6
```{r}
aname = "Gilberto Gil"
sql = paste0("SELECT ArtistId FROM artists "
,
"WHERE Name = '"
, aname,
"'")
aId = dbGetQuery(db, sql)
sql = paste('SELECT Title FROM albums'
,
'WHERE ArtistId ='
, aId)
dbGetQuery(db, sql)
```

```{r}
aname <- "Gilberto Gil'; DROP TABLE 'albums"

```

#slide 7
```{r}
sql = paste("SELECT ArtistId FROM artists"
,
"WHERE Name = ?")
query <- dbSendQuery(db, sql)
dbBind(query, list("Gilberto Gil"))
aId <- dbFetch(query)
dbClearResult(query)
# Segundo passo interno, não deve causar problema
sql = paste('SELECT Title FROM albums'
,
'WHERE ArtistId ='
, aId)
dbGetQuery(db, sql)
```

#slide 8
```{r}
dbExecute(db,
"CREATE TABLE instruments
(AlbumId INTEGER,
TrackId INTEGER,
ElectricGuitar INTEGER,
Singer INTEGER,
Trumpet INTEGER)")
```

```{r}
dbListFields(db,
'instruments')
```

```{r}
# Eu Tu Eles: AlbumId 85,
sql = paste('SELECT TrackId, Name FROM tracks'
,
'WHERE AlbumId = 85')
dbGetQuery(db, sql) %>% head
```

#slide 9
```{r}
dbExecute(db,
"INSERT INTO instruments
VALUES ('85'
,
'1075'
, 0, 1, 0),
('85'
,
'1078'
, 0, 1, 0); ")
```

```{r}
dbGetQuery(db,
"SELECT * FROM instruments")

```

#slide 10
```{r}
dbWriteTable(db,
"mtcars"
, mtcars)
dbListTables(db)
```

```{r}
dbGetQuery(db,
"SELECT * FROM mtcars") %>% head(3)
```

#slide 11
```{r}
theAvgCar <- mtcars %>%
summarise_all(function(x) round(mean(x), 2))
theAvgCar
```

```{r}
dbWriteTable(db,
"mtcars"
, theAvgCar, append = TRUE)
dbGetQuery(db,
"SELECT * FROM mtcars") %>% tail(3)
```

#slide 12
```{r}
dbWriteTable(db,
"mtcars"
, mtcars, overwrite = TRUE)
dbGetQuery(db,
"SELECT * FROM mtcars") %>% tail(3)

```

#slide 13
```{r}
res <- dbSendQuery(db,
"SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
chunk <- dbFetch(res, n = 5)
print(nrow(chunk))
}

```

```{r}
dbClearResult(res)
```

#slide 14
```{r}
dbDisconnect(db)
```

```{r}
if("discoCopy.db" %in% list.files(".")){
file.remove("discoCopy.db")
}

```

#slide 15
```{r}
airports <- read_csv("airports.csv"
, col_types = "cccccdd")
airlines <- read_csv("airlines.csv"
, col_types = "cc")
```

```{r}
air <- dbConnect(SQLite(), dbname="air.db")
dbWriteTable(air, name = "airports"
, airports)
dbWriteTable(air, name = "airlines"
, airlines)
dbListTables(air)
```

#slide 16
```{r}
dbDisconnect(air)
if("air.db" %in% list.files(".")){
file.remove("air.db")
}

```

#slide 17
```{r}
library(dbplyr)
```

```{r}
db <- dbConnect(SQLite(),
"disco.db") # original
tracks <- tbl(db,
"tracks") # dplyr
tracks %>% head(3)
```

#slide 18
```{r}
meanTracks <- tracks %>%
group_by(AlbumId) %>%
summarise(AvLen = mean(Milliseconds, na.rm = TRUE),
AvCost = mean(UnitPrice, na.rm = TRUE))
meanTracks

```

#slide 19
```{r}
meanTracks %>% show_query()

```

#slide 20
```{r}
mT <- meanTracks %>% collect()
mT
```

```{r}
dbDisconnect(db)
```

