view db/access_db.R @ 0:205974c9568c tip

Initial commit. Predictions not included for lack of space.

library(tidyverse)
library(RSQLite)


#
get_excerpts <- function(db = "../db/gtzan.db"){

  con <- dbConnect(SQLite(), db)

  res <- dbGetQuery(
    con, "SELECT ex_id FROM excerpts")

  dbDisconnect(con)
  
  return(res)

}

#
get_excerpts_artists <- function(db = "../db/gtzan.db"){
  
  con <- dbConnect(RSQLite::SQLite(), db)
  
  res <- dbGetQuery(
    con, 
    "SELECT ex_id, artist_id FROM
    recordings_artists AS r INNER JOIN excerpts AS e
    ON r.rec_id = e.rec_id;")
  
  dbDisconnect(con)
  
  return(res)
  
}

.filter_by_class <- function(df,
  db = "../db/gtzan.db", classes = NULL){
  
  class_ids <- get_class_names(db = db) %>% 
    filter(class %in% classes) %>%
    select(class_id) %>%
    unlist()
  df %>% filter(class_id %in% class_ids)
  
}

#
get_excerpts_classes <- function(db = "../db/gtzan.db", 
                                 classes = NULL){
  
  con <- dbConnect(SQLite(), db)
  
  res <- dbGetQuery(
    con, 
    "SELECT ex_id, class_id, ex_id_class FROM
    excerpts_classes;")
  
  if (!is.null(classes)) 
    res <- .filter_by_class(res, db, classes)
  
  dbDisconnect(con)
  
  return(res)
  
}

#
get_class_names <- function(db = "../db/gtzan.db"){
  
  con <- dbConnect(SQLite(), db)
  
  res <- dbGetQuery(
    con, 
    "SELECT class_id, class FROM
    classes;")
  
  dbDisconnect(con)
  
  return(res)  
  
}

#
get_artists <- function(excerpts, unique_artists = T, 
                        db = "../db/gtzan.db"){
  
  excerpts_artists <- get_excerpts_artists(db = db)
  
  res <- 
    excerpts_artists %>% 
    inner_join(data.frame(ex_id = excerpts), by = c('ex_id')) %>% 
    select(artist_id) %>%
    unlist() %>%
    unname()
  
  if(unique_artists)
    return(unique(res))
  else
    return(res)
  
}

#
get_classes <- function(excerpts, unique_classes = T,
                        use_names = T,
                        db = "../db/gtzan.db"){
  
  excerpts_classes <- get_excerpts_classes(db = db)
  class_names <- get_class_names(db = db)
  
  res <-
    excerpts_classes %>% 
    inner_join(data.frame(ex_id = excerpts), by = c('ex_id')) %>%
    select(class_id)
  
  if(use_names)
    res <- res %>%
    inner_join(class_names, by = c('class_id')) %>%
    select(class) %>% 
    mutate(class = factor(class,
                          levels = unique(class_names$class)))
  else
    res <- res %>%
    mutate(class_id = factor(class_id,
                             levels = unique(class_names$class_id)))
  
  res <- res %>% unlist() %>% unname()
  
  if(unique_classes)
    return(unique(res))
  else
    return(res) 
  
}