# first we get the packages we need:
library(DBI)
library(RSQLite)

# now we open the connection:

con = dbConnect(SQLite(), dbname="roman.db")

# we can see what's inside, eg, what tables are in the database?
alltables = dbListTables(con)
alltables



# write the query to get the information you want

myQuery <- dbSendQuery(con, "SELECT * FROM amphi WHERE chronogroup = 'flavian'")

# pass that information to an R object. The n = -1 bit means grab everything until there's nothing left to grab. Otherwise, you can specify how many rows etc.

my_data <- dbFetch(myQuery, n = -1)

# You will get some warning messages as R makes sure that the data in each column is of the same type.

# Now to view your dataframe, we just ask:

my_data

# now that we're done, clear cache 
# so that we don't eat up all the memory
dbClearResult(myQuery)

# now carry on and begin manipulating my_data
# for more information see
# http://tiffanytimbers.com/querying-sqlite-databases-from-r/
# also perhaps this https://www.r-bloggers.com/using-sqlite-in-r/

# let's make a new dataframe with just the elevation and capacity columns
# you could have done this with an SQL query from our original database.
# here we're just going to extract from the dataframe we created above

amphi_stats <- subset(my_data, select=c("id","elevation","capacity"))

#"create new datafram called amph_stats and pass into it a subset of my_data, returning the array id, elevation, capacity"

amphi_stats

# a simple x,y plot
# we can call for the columns in the dataframe by refering to the name of the frame and using the $ to specify the column therein.
plot(amphi_stats$elevation, amphi_stats$capacity)

# but that's ugly. let's make it pretty 
# and write the result to file.

# Get the input values. 
input <- amphi_stats[,c('elevation','capacity')]

# Give the chart file a name.
png(file = "R-scatterplot.png")

# Plot the chart for cars with weight between 2.5 to 5 and mileage between 15 and 30.
plot(x = input$elevation,y = input$capacity,
   xlab = "Elevation",
   ylab = "Capacity", 
   xlim = c(0,1000),
   ylim = c(500,60000),
   main = "Elevation vs Capacity in Flavian Amphitheatres"
)
	 
# Save the file.
dev.off()