The final component of bot functionality was setting up the database. This was really the thing that I wanted to gain some experience with and by setting up a database not in a rails application, I was able to understand some of the components a bit better. For my purposes, this database would be used to keep track of points. Often times, people will award other people points based on funny or interesting things they post. They’ll also subtract points for things but in the past, we’ve had no way to keep track of this. This bot added the following functionality: adding and subtracting points, telling a user what their score is when they ask, and printing out the leaderboard when asked.

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.

def nick_id(nick) 
  set = score.where(:nick => nick) 

  if set.empty?
    nil 
  else
    set.first[:id] 
  end
end

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)
  nick.downcase!
  if nick_id(nick)
    row = score.where(:id => nick_id(nick)).first
    updated_points = row[:points] + points      

    score.where(:id => nick_id(nick)).update(:points => updated_points)
  else
    score.insert(:nick => nick, :points => points) 
  end
end

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.

def lookup_score(nick)
  score.filter(:nick => nick).first[:points] 
rescue 
  nil
end

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.

require 'singleton'

class DB
  include Singleton
  
  def initialize(file = 'sqlite://bot.db')
    @db = Sequel.connect(file)  #setup the DB connection
  end

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.

def score
  @db[:score]
end

And that is how I set up the bot’s database.