First, Chris and I chose a database. We decided to go with Sequel because it looked pretty interesting and simple to set up. I set up the connection first that created the database but then moved that into a separate file. This way I have the option to run the create table script only when I want to create a new database table. Then I set the database connection as a global constant like this
DB = Sequel.connect('sqlite://bot.rb')to get things going and set out to write the related methods.
The first method looks to see if a nickname is already in the database or if we need to add it and acts on that.
set = score.where(:nick => nick)
So, we set the variable set which find the list of every score that has the nickname nick (nick being whatever the person’s nickname is). Then, if it doesn’t find anything, then it will return nil. Otherwise, the methods grabs the id of the first matching nick it finds with that name and returns it.
The second method inserts or updates the score.
def insert_or_update_score(nick, points)
row = score.where(:id => nick_id(nick)).first
updated_points = row[:points] + points
score.where(:id => nick_id(nick)).update(:points => updated_points)
score.insert(:nick => nick, :points => points)
So, first I need to make sure all the nicks are being evaluated in the same way. I discovered this when testing and found that two different nicks would be saved in the database based on if they were capitalized or not. Then, this method needs to fetch a record, then increment the number and then save it back. If there is no nick, then it creates the new nick in the database and gives it points.
Finally, I wanted to be able to lookup the score and if someone has no points then the method throws an exception.
score.filter(:nick => nick).first[:points]
I added a rescue so if there are any exceptions, the rescue catches the exception and returns nil. Chris warned me that when looking at future projects, the rescue option can be dangerous because it can hide serious errors but for our purposes, using rescue is perfectly acceptable.
THEN WE REFACTORED!
Setting the database connection as a global constant is not a good idea and having all of these methods in the main file just make it messy and unorganized. First, I changed the database connection to an instance variable by putting it into a singleton class and initializing it.
def initialize(file = 'sqlite://bot.db')
@db = Sequel.connect(file) #setup the DB connection
This meant that I also had to change all of the
DB[:score]instances in the code because it was now
@db. Then, instead of just putting @db in each place, I extracted it into a method that does it for us.
And that is how I set up the bot’s database.