For the past few months, we've been looking at the Facebook API, which makes it possible to integrate third-party applications into the popular social-networking site. Facebook is remarkable to users for the number of people already using it, as well as for the rapid pace at which new people are joining. But, it also is remarkable for software developers, who suddenly have been given access to a large number of users, into whose day-to-day Web experience they can add their own applications.

The nature of Facebook means that most developers are writing applications that are more frivolous than not. Thus, it's easy to find name-that-celebrity games, extensions to built-in Facebook functionality (such as, “SuperWall”) and various applications that ask questions, match people together and so forth. I expect we eventually will see some more serious applications created with the Facebook API, but that depends on the developer community. I would argue that the continued growth of Facebook applications depends on the ability of developers to profit from their work, but that is a business issue, rather than a technical one.

Regardless of what your application does, it probably will be quite boring if you cannot keep track of information about your users. This might strike you as strange—after all, if you are writing a Facebook application, shouldn't Facebook take care of the storage for you?

The answer is no. Although Facebook handles user authentication, gives you the ability to deploy your application within the Facebook site and even provides access to certain data about the currently logged-in user, it does not store data on your behalf. This means any data you want to store must be kept on your own server, in your own database.

This month, I explain how to create a simple application on Facebook that allows us to retrieve data from a user's Facebook profile or from our local relational database seamlessly. The key to this is the user's Facebook ID, which we will integrate into our own user database. Retrieving information about our user, or about any of their friends, will require a bit of thinking about where the data is stored. However, you will soon see that mixing data from different sources is not as difficult as it might sound at first, and it can lead to far more interesting applications.

Our application is going to be simple—a Facebook version of the famous “Hello, world” program that is the first lesson in oh-so-many books and classes. However, we'll add two simple twists: first, we will display the number of times that the user has visited our application to date. (So, on your fifth visit, you will be reminded that this is your fifth visit.) Moreover, you will be told how many times each of your friends has visited the site.

In a normal Web/database application, this would be quite straightforward. First, we would define a database to keep track of users, friends and visits. Then, we would write some code to keep track of logins. Finally, we would create a page that displayed the result of a join between the various pages to show when people had last visited. For example, we could structure our database tables like this:

CREATE TABLE People (
    id    SERIAL    NOT NULL,
    email_address    TEXT    NOT NULL,
    encrypted_password    TEXT    NOT NULL,

    PRIMARY KEY(id),
    UNIQUE(email_address)
);

CREATE TABLE Visits (
    person_id    INTEGER    NOT NULL  REFERENCES People,
    visited_at    TIMESTAMP NOT NULL    DEFAULT NOW(),

    UNIQUE(person_id, visited_at)
);

CREATE TABLE Friends (
    person_id    INTEGER    NOT NULL  REFERENCES People,
    friend_id    INTEGER     NOT NULL  REFERENCES People,

    UNIQUE(person_id, friend_id),
    CHECK(person_id <> friend_id)
);

Our first table, People, contains only a small number of columns, probably fewer than you would want in a real system. We keep track of the users' primary key (id), their e-mail addresses (which double as their login) and their encrypted passwords.

We keep track of each visit someone makes to our site in a separate table. We don't need to do this; it would be a bit easier and faster to have a number_of_visits column in the People table and then just increment that with each visit. But, keeping track of each visit means we have more flexibility in the future, from collecting usage statistics to stopping people from using our system too much.

Finally, we indicate friendship in our Friends table. Keeping track of friends is a slightly tricky business, because you want to assume that if A is a friend to B, then B also is a friend to A. We could do this, but it's easier in my book simply to enter two rows in the database, one for each direction. To retrieve the friends of A, whose ID is 1, we look in the Friends table for all of the values of friend_id where person_id = 1.

All of this seems pretty reasonable and straightforward, and it isn't hard to implement in any modern Web framework. But, if we want to implement the same functionality in a Facebook application, we have to consider that about half the database we just defined is going to be unnecessary. We don't need to worry about the Friends table, because that's something Facebook does quite well. And, we don't really need to worry about the People table either, as Facebook handles logins and authentication.

At the same time, we obviously can't use only the Friends table by itself. We need it to point to something, somewhere, so we can associate a visit with a user. How do we do that?

The answer is that instead of storing the users' information, we store their Facebook user IDs. Our People table, thus, will look like this:

CREATE TABLE People (
    id    SERIAL    NOT NULL,
    facebook_session_key    TEXT    NOT NULL,
    facebook_uid    TEXT    NOT NULL,

    PRIMARY KEY(id)
);

By storing the Facebook information in our database, we effectively hook our id column to what Facebook provides. But, how will we use this link?

The answer is that we don't really have to, if we use a plugin that handles the underlying details for us. I have used RFacebook for the past few months; this is a plugin for Ruby on Rails that makes it fairly easy to create a Facebook application using Rails. First, I create my models using the generate script that comes with Rails:

./script/generate model person facebook_session_key:string
↪facebook_uid:string

This creates a new model—that is, a Ruby class that represents a database table—called person.rb. Although this script doesn't create the model directly, it does create a migration file that defines our database table in Ruby:

class CreatePeople < ActiveRecord::Migration
  def self.up
    create_table :people do |t|
      t.column :facebook_session_key, :string
      t.column :facebook_uid, :string
    end
  end

  def self.down
    drop_table :people
  end
end

Assuming that our database is all set up, we can run the migration using the built-in rake tool (think make, but in Ruby):

rake db:migrate

The output tells us a bit of what's going on:

== CreatePeople: migrating ======================
-- create_table(:people)
NOTICE:  CREATE TABLE will create
    implicit sequence "people_id_seq" for serial column "people.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create
    implicit index "people_pkey" for table "people"
 -> 0.1939s
== CreatePeople: migrated (0.1944s) =============

The advantage of using rake and migrations is that we can modify our migrations file, change our database definitions, and move forward and backward in time through our database design. Migrations mean that you can keep track of your changes to the database and automatically upgrade (or downgrade) the database to the latest version without losing data. And, sure enough, if we look at our database, we see that it has three columns, just as we defined.

Next, we create another model for our visits table:

./script/generate model visit person_id:integer 
 ↪visited_at:timestamp

We migrate the database to the latest version:

rake db:migrate

And, sure enough, we have a visits table with a person_id column. Unfortunately, because Rails migrations are written in a generic language, there isn't any built-in support for handling foreign keys and other referential integrity checks. So, the table, as we defined it above, would have indicated that person_id always must point to a value.

Also note that the default model-generation script allows null values. We could go into the migration file and change this, but we will ignore it for now.

Now that we have a place for the Facebook information in our People table, we need to tell Rails to put it there. We do this by adding the line:

acts_as_facebook_user

in the model file, person.rb. By default, it will be almost empty, indicating that we will use ActiveRecord's defaults to work with our database table via Ruby:

class Person < ActiveRecord::Base
end

When we're done, our class will look like this:

class Person < ActiveRecord::Base
    acts_as_facebook_user
end

In our controller file (which I'm sneakily reusing from what we did last month, modifying the facebook method in the hello controller), I've modified the method to read:

def facebook
  render :text => "hi"
end

Because my application is called rmlljatf, I can go to the following URL: http://apps.facebook.com/rmlljatf/, and see my “hi” at the top of the page. After loading this page, I then look at my People table and find...that nothing has changed. After all, I told the system to create the table, but I didn't actually do anything with it! For that to happen, I need to use the built-in fbsession object, which gives me access to Facebook information. I then can say:

def facebook
  person = Person.find_or_create_by_facebook_session(fbsession)
  render :text => "hi"
end

And, sure enough, reloading the page creates a row in our People table.

Next, I modify my method to add a row to our visits table. I can do that with the following:

def facebook
  person = Person.find_or_create_by_facebook_session(fbsession)

  Visit.create(:person_id => person.id,
                      :visited_at => Time.now()).save!

  render :text => "hi"
end

Once I've modified the facebook method in this way, each visit to the site does indeed add another row to the visits table.

Now we should produce some output, indicating exactly how many visits the person has made to the site. For this, we create a view (facebook.rhtml), which can display things more easily:

<p>This is your <%= @number_of_visits.ordinalize %> visit.</p>

This short view displays the instance variable @number_of_visits and puts it into ordinal form, which is convenient. However, this means we need to set @number_of_visits in the facebook method. We do this by adding the following line:

@number_of_visits =
  ↪Visit.count(:conditions => ["person_id = ?", person.id])

In other words, we grab the current user's ID. We then use that ID, along with a built-in ActiveRecord value, to sum up the number of visits the user has made to the site.

Finally, it's time to introduce the Facebook magic. We know, from last month, that we can display the current user's Facebook friends without too much trouble; we use fbsession to grab a list of friends (and specific pieces of information about those friends), and then iterate over them, displaying them however we like.

Now, we do the same thing, but we also create a hash, @friends_visits, in which the key will be the Facebook user ID (uid), and the value will be the number of visits by that person. We give our hash a default value of 0, in case we try to retrieve a key that does not exist. We also use a bit of exception handling to ensure that we can handle null results. The final version of the facebook method looks like this:

def facebook
  person = Person.find_or_create_by_facebook_session(fbsession)
  Visit.create(:person_id => person.id,
               :visited_at => Time.now())

  # Count the number of visits
  @number_of_visits =
    Visit.count(:conditions => ["person_id = ?", person.id])

  @friend_uids = fbsession.friends_get.uid_list

  # Get info about friends from Facebook
  @friends_info =
    fbsession.users_getInfo(:uids => @friend_uids,
                            :fields => ["first_name", "last_name"])

  # Keep track of friend visits to the site
  @friends_visits = Hash.new(0)
  @friends_info.user_list.each do |userInfo|
    begin
      friend = Person.find_by_facebook_uid(userInfo.uid)

      @friends_visits[userInfo.uid] =
        Visit.count(:conditions => ["person_id = ?", friend.id])
    rescue
      next
    end
  end

end

In other words, we grab friend information via fbsession. We then iterate over each friend, getting its Facebook uid. With that UID—which we have in our People table, in the facebook_uid column—we can get the person's database ID, and then use that to find the person's number of visits.

With this in place, we can rewrite the view as follows to include friend information:

<p>This is your <%= @number_of_visits.ordinalize %> visit.</p>

<% @friends_info.user_list.each do |userInfo| %>
<ul>
      <li><fb:name uid="<%= userInfo.uid -%>" target="_blank" />
            <fb:profile-pic uid="<%= userInfo.uid -%>" linked="true" />
        <%= @friends_visits[userInfo.uid] %> visit(s)</li>
</ul>
<% end %>

Sure enough, when you visit the page, it tells you how many times you have visited, as well as how many times each friend has visited.

Facebook's API gives us the opportunity to think about how we can structure an application that doesn't have access to some of the data. This application doesn't have any authentication information about users, and it can get only particular pieces of data about them. But, because we have an id column, we can use it to store data on our local server and then join that data with what comes from Facebook seamlessly.