# add libraries
library(RSQLite)

# connect to SQLite database (campaign.db)
db <- dbConnect(SQLite(), "/Users/ericgarcia/Desktop/campaign.db")

# send super long query to sqlite
res <- dbSendQuery(db, "
SELECT c.np word_phrase, b_num barack_count, (m_num * 1.89) mccain_count 
FROM common AS c 
LEFT OUTER JOIN 
(SELECT c.np mccain_np, COUNT(c.np) AS m_num FROM mccain AS m, common AS c WHERE lower(m.np) = c.np GROUP BY c.np ORDER BY c.np) 
ON c.np = mccain_np 
LEFT OUTER JOIN 
(SELECT c.np barack_np, COUNT(c.np) AS b_num FROM barack AS b, common AS c WHERE lower(b.np) = c.np GROUP BY c.np ORDER BY c.np) 
ON c.np = barack_np 
ORDER BY b_num DESC LIMIT 0,20"
)
# put retrieved into data
data <- fetch(res, n=-1)

# add an index column (1-20)
data <- data[1:20,,]

# make barack and mccain counts numeric
barack_count <- as.numeric(barack_count)
mccain_count <- as.numeric(mccain_count)

# colors
par("bg"="white")
par("ps"=13)
par("fg"="#303333")

# construct barplots
barplot(data$barack_count, horiz=TRUE, space=0, col="#cde5ff", xlim=c(-400,400))
barplot(-data$mccain_count, horiz=TRUE, space=0, col="#ffcdcd", add=TRUE)

# add axis for and label the graph with each word phrase
axis(2, at=1:20, labels=data$word_phrase, pos=0, 
col.axis="black", las=2, tick=FALSE, hadj=.5, padj=1.5, mgp=c(3,-1.9,0))

# add legend
legend(-300, 10, c("Obama", "McCain"),
fill=c("#cde5ff", "#ffcdcd"), bg="white")
par("ps"=10)
title("Word Frequency: Obama and McCain's Online Blogs")