```{r}
library(RSQLite)
library(tidyverse)

if(!"discoCopy.db" %in% list.files("./")){
  file.copy("./disco.db", "./discoCopy.db")}

#o ./ indica q o meu qmd está na mesma pasta q o dataset
#esse if faz o código rodar se a cópia ainda não existir
#ent ele retorna true se a cópia foi feita na hora e não retorna nada se a cópia
#já tiver feita
```

```{r}
db <- dbConnect(SQLite(), "./discoCopy.db") #conectando o r ao sql
```

```{r}
dbListTables(db) #vendo quais tabelas estão no dataset
```

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

#cria uma tabela vazia uma vez, ent se executar dnv vai dar erro
```

```{r}
dbListFields(db, 'instruments') #vendo as colunas da tabela instruments

# dbExecute(db, "DROP TABLE instruments") #removendo a tabela instruments (comentado por conta de chunk depois q precisa dessa tabela)
```

```{r}
dbListTables(db) #vendo o dataset sem instruments
```

```{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}
#o código abaixo é um exemplo de como um usuário malicioso pode invadir o
#dataset e modificá-lo, destruindo o banco de dados

#aname <- "Gilberto Gil'; DROP TABLE 'albums"
```

```{r}
#essas são funções q executam queries com segurança

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)

```

```{r}
dbListFields(db, 'instruments') #vendo as colunas de instruments

#inserindo Eu Tu Eles: AlbumId 85 em instruments
sql = paste('SELECT TrackId, Name FROM tracks', 'WHERE AlbumId = 85')
dbGetQuery(db, sql) %>% head

dbExecute(db, "INSERT INTO instruments VALUES ('85', '1075', 0, 1, 0),
('85', '1078', 0, 1, 0); ")

dbGetQuery(db, "SELECT * FROM instruments")

```

```{r}
#incluindo a tabela (data frame) mtcars no nosso dataset 
dbWriteTable(db, "mtcars", mtcars)
dbListTables(db)
```

```{r}
#note que o atributo rownames (marcas dos carros) foi perdido, mas há um
#parâmetro row.names em dbWriteTable

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

```{r}
#o parâmetro append concatena uma tabela nova a dados existentes
theAvgCar <- mtcars %>%
summarise_all(function(x) round(mean(x), 2))
theAvgCar

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

```{r}
#o parâmetro overwrite sobrescreve a tabela
dbWriteTable(db, "mtcars", mtcars, overwrite = TRUE)
dbGetQuery(db, "SELECT * FROM mtcars") %>% tail(3)
```

```{r}
#os códigos abaixo lêem os dados em chunks
res <- dbSendQuery(db, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
chunk <- dbFetch(res, n = 5)
print(nrow(chunk))}

dbClearResult(res)
```

```{r}
#encerrando as conexões com dbDisconnect() e removenedo a cópia q fizemos do
#dataset disco.db
dbDisconnect(db)
if("discoCopy.db" %in% list.files("../dados/")){
file.remove("../dados/discoCopy.db")}
```

```{r}
#criando uma base de dados
airports <- read_csv("./airports.csv", col_types = "cccccdd")
airlines <- read_csv("./airlines.csv", col_types = "cc")
air <- dbConnect(SQLite(), dbname = "./air.db")
dbWriteTable(air, name = "airports", airports)
dbWriteTable(air, name = "airlines", airlines)
dbListTables(air)
```

```{r}
#destruindo a conexão e a tabela
dbDisconnect(air)
if("air.db" %in% list.files("./")){
file.remove("./air.db")}
```

```{r}
#o pacote dbplyr estende algumas funcionalidades do dplyr a dados que
#estão armazenados em um bancos de dados externo
library(dbplyr)
db <- dbConnect(SQLite(), "./disco.db") #original
tracks <- tbl(db, "tracks") #dplyr
tracks %>% head(3)

```

```{r}
#esse código faz um resumo estatístico por álbum da tabela tracks com os
#verbos do dplyr disponiveis, mas q secretamente são comandos de SQLite
meanTracks <- tracks %>%
group_by(AlbumId) %>%
summarise(AvLen = mean(Milliseconds, na.rm = TRUE),
AvCost = mean(UnitPrice, na.rm = TRUE))
meanTracks

meanTracks %>% show_query()
```

```{r}
#uando você decidir o que precisa, pode usar o comando collect()
mT <- meanTracks %>% collect()
mT
```

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