f@0: library(tidyverse)
f@0: library(RSQLite)
f@0: 
f@0: 
f@0: #
f@0: get_excerpts <- function(db = "../db/gtzan.db"){
f@0: 
f@0:   con <- dbConnect(SQLite(), db)
f@0: 
f@0:   res <- dbGetQuery(
f@0:     con, "SELECT ex_id FROM excerpts")
f@0: 
f@0:   dbDisconnect(con)
f@0:   
f@0:   return(res)
f@0: 
f@0: }
f@0: 
f@0: #
f@0: get_excerpts_artists <- function(db = "../db/gtzan.db"){
f@0:   
f@0:   con <- dbConnect(RSQLite::SQLite(), db)
f@0:   
f@0:   res <- dbGetQuery(
f@0:     con, 
f@0:     "SELECT ex_id, artist_id FROM
f@0:     recordings_artists AS r INNER JOIN excerpts AS e
f@0:     ON r.rec_id = e.rec_id;")
f@0:   
f@0:   dbDisconnect(con)
f@0:   
f@0:   return(res)
f@0:   
f@0: }
f@0: 
f@0: .filter_by_class <- function(df,
f@0:   db = "../db/gtzan.db", classes = NULL){
f@0:   
f@0:   class_ids <- get_class_names(db = db) %>% 
f@0:     filter(class %in% classes) %>%
f@0:     select(class_id) %>%
f@0:     unlist()
f@0:   df %>% filter(class_id %in% class_ids)
f@0:   
f@0: }
f@0: 
f@0: #
f@0: get_excerpts_classes <- function(db = "../db/gtzan.db", 
f@0:                                  classes = NULL){
f@0:   
f@0:   con <- dbConnect(SQLite(), db)
f@0:   
f@0:   res <- dbGetQuery(
f@0:     con, 
f@0:     "SELECT ex_id, class_id, ex_id_class FROM
f@0:     excerpts_classes;")
f@0:   
f@0:   if (!is.null(classes)) 
f@0:     res <- .filter_by_class(res, db, classes)
f@0:   
f@0:   dbDisconnect(con)
f@0:   
f@0:   return(res)
f@0:   
f@0: }
f@0: 
f@0: #
f@0: get_class_names <- function(db = "../db/gtzan.db"){
f@0:   
f@0:   con <- dbConnect(SQLite(), db)
f@0:   
f@0:   res <- dbGetQuery(
f@0:     con, 
f@0:     "SELECT class_id, class FROM
f@0:     classes;")
f@0:   
f@0:   dbDisconnect(con)
f@0:   
f@0:   return(res)  
f@0:   
f@0: }
f@0: 
f@0: #
f@0: get_artists <- function(excerpts, unique_artists = T, 
f@0:                         db = "../db/gtzan.db"){
f@0:   
f@0:   excerpts_artists <- get_excerpts_artists(db = db)
f@0:   
f@0:   res <- 
f@0:     excerpts_artists %>% 
f@0:     inner_join(data.frame(ex_id = excerpts), by = c('ex_id')) %>% 
f@0:     select(artist_id) %>%
f@0:     unlist() %>%
f@0:     unname()
f@0:   
f@0:   if(unique_artists)
f@0:     return(unique(res))
f@0:   else
f@0:     return(res)
f@0:   
f@0: }
f@0: 
f@0: #
f@0: get_classes <- function(excerpts, unique_classes = T,
f@0:                         use_names = T,
f@0:                         db = "../db/gtzan.db"){
f@0:   
f@0:   excerpts_classes <- get_excerpts_classes(db = db)
f@0:   class_names <- get_class_names(db = db)
f@0:   
f@0:   res <-
f@0:     excerpts_classes %>% 
f@0:     inner_join(data.frame(ex_id = excerpts), by = c('ex_id')) %>%
f@0:     select(class_id)
f@0:   
f@0:   if(use_names)
f@0:     res <- res %>%
f@0:     inner_join(class_names, by = c('class_id')) %>%
f@0:     select(class) %>% 
f@0:     mutate(class = factor(class,
f@0:                           levels = unique(class_names$class)))
f@0:   else
f@0:     res <- res %>%
f@0:     mutate(class_id = factor(class_id,
f@0:                              levels = unique(class_names$class_id)))
f@0:   
f@0:   res <- res %>% unlist() %>% unname()
f@0:   
f@0:   if(unique_classes)
f@0:     return(unique(res))
f@0:   else
f@0:     return(res) 
f@0:   
f@0: }