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

Initial commit. Predictions not included for lack of space.

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/access_db.R	Sat Jun 29 18:45:50 2019 +0100
@@ -0,0 +1,134 @@
+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) 
+  
+}