## Database Query

## By Jeff Hebert, 7/15/2015

## Used query from amineteffal https://www.kaggle.com/amineteffal/avito-context-ad-clicks/script-aminos-2/run/26133


##
## Load Packages
##

library("RSQLite")



##
## Connect to database and pull data
##


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

print("Reading Database")
train <- dbGetQuery(db, "SELECT trainSearchStream.SearchID,trainSearchStream.AdID,trainSearchStream.Position,trainSearchStream.objectType, trainSearchStream.HistCTR,trainSearchStream.IsClick,SearchInfo.SearchDate,SearchInfo.SearchQuery,SearchInfo.IsUserLoggedOn,AdsInfo.Price,AdsInfo.LocationID,AdsInfo.CategoryID FROM trainSearchStream, SearchInfo, UserInfo, AdsInfo where trainSearchStream.SearchID = SearchInfo.SearchID and SearchInfo.UserID=UserInfo.UserID and trainSearchStream.AdID=AdsInfo.AdID and AdsInfo.IsContext=1 limit 3000000")
#test <- dbGetQuery(db, "SELECT testSearchStream.TestId, testSearchStream.Position, testSearchStream.HistCTR,AdsInfo.Price,AdsInfo.LocationID,AdsInfo.CategoryID, SearchInfo.UserID,SearchInfo.IsUserLoggedOn FROM testSearchStream, SearchInfo, AdsInfo where testSearchStream.SearchID = SearchInfo.SearchID and testSearchStream.AdID=AdsInfo.AdID and AdsInfo.IsContext=1 ")

print("Train Data Dimensions")
dim(train)
#dim(test)

## Save data files to explore offline
write.csv(train, "train.csv", row.names=FALSE)
#write.csv(test, "test.csv", row.names=FALSE)