library("data.table")
library("RSQLite")
library("caret")

# ----- Prepare database -------------------------------------------------------

db <- dbConnect(SQLite(), dbname="../input/database.sqlite")
dbListTables(db)

# ----- Utitlies ---------------------------------------------------------------

# Define constants to improve readability of large number
thousand <- 1000
million  <- thousand * thousand 
billion  <- thousand * million

# Runs the query, fetches the given number of entries and returns a
# data.table
fetch  <- function(db, query, n = -1) {
  result <- dbSendQuery(db, query)
  data <- dbFetch(result, n)
  dbClearResult(result)
  return(as.data.table(data))
}

# Select contextual Ads (OnjectType=3), results in 190.157.735 entries
# Warning: Takes a few minutes
sample <- fetch(db, 
    "select * from trainSearchStream 
        inner join searchinfo on trainSearchStream.SearchID=searchinfo.SearchID 
        inner join userinfo on searchinfo.UserID=userinfo.UserID 
        inner join adsinfo on trainSearchStream.AdID=adsinfo.AdID
        inner join Category on adsinfo.CategoryID=Category.CategoryID
        inner join Location on adsinfo.LocationID=Location.LocationID
        ", 1500)
head(sample)