Sophisticated Asterisk Development with Adhearsion
Pages: 1, 2, 3, 4

Database Integration

Though immensely successful in the web development space for serving dynamic content, database integration has always been an underused possibility for driving dynamic voice applications with Asterisk. Most Asterisk applications that do accomplish this outsource the complexity to a PHP or Perl AGI script because the extensions.conf or AEL grammars are simply impractical for the level of sophistication this requires.

Adhearsion uses a database integration library developed by the makers of the Ruby on Rails framework called ActiveRecord. With ActiveRecord, the end user seldom, if ever, writes SQL statements. Instead, the developer accesses the database just like any Ruby object. Because Ruby allows such flexible dynamism, the access to the database looks and feels quite natural. Additionally, ActiveRecord abstracts the differences between database management systems, making your database access implementation agnostic.

Without going too much into the internals of ActiveRecord and more sophisticated uses of it, let us consider the following simple MySQL schema:

CREATE TABLE groups (
    `id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
    `description` varchar(255) DEFAULT NULL,
    `hourly_rate` decimal DEFAULT NULL
);

CREATE TABLE customers (
    `id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
    `name` varchar(255) DEFAULT NULL,
    `phone_number` varchar(10) DEFAULT NULL,
    `usage_this_month` int(11) DEFAULT 0,
    `group_id` int(11) DEFAULT NULL
);

In practice we would obviously store much more information about the customer and keep the service usage information in a database-driven call detail record but this degree of simplicity helps demonstrate ActiveRecord fundamentals more effectively.

To connect Adhearsion with this database, one simply specifies the database access information in a YAML configuration file like so:

adapter: mysql
host: localhost
database: adhearsion
username: root
password: pass

This tells Adhearsion how to connect to the database, but how we access information in the tables depends on how we model our ActiveRecord objects. Since an object is an instance of a class, we write a class definition to wrap around each table. We define simple properties and relationships in the class with the superclass's methods.

Here are two classes we may use with the aforementioned tables:

class Customer < ActiveRecord::Base
  belongs_to :group

  validates_presence_of   :name, :phone_number
  validates_uniqueness_of :phone_number
  validates_associated    :group

  def total_bill
    self.group.hourly_rate * self.usage_this_month / 1.hour
  end
end

class Group < ActiveRecord::Base
  has_many :customers
  validates_presence_of :description, :hourly_rate  
end

From just this small amount of information, ActiveRecord can make a lot of logical inferences. When these classes interpret, ActiveRecord assumes the table names to be customers and groups respectively by lowercasing the classes' names and making them plural. If this convention is not desired, the author can easily override it. Additionally, at interpretation time, ActiveRecord actually peeks into the database's columns and makes available many new dynamically created methods.

The belongs_to and has_many methods in this example define relationships between Customers and Groups. Notice again how ActiveRecord uses pluralization to make the code more expressive in the has_many :customers line. From this example we also see several validations—policies, which ActiveRecord will enforce. When creating a new Customer we must provide a name and phone_number at the bare minimum. No two phone numbers can conflict. Every Customer must have a Group. Every Group must have a description and hourly_rate. These help both the developer and the database stay on track.

Also, notice the total_bill method in the Customer class. On any Customer object we extract from the database, we can call this method which multiplies the hourly_rate value of the group to which the Customer belongs by the Customer's own phone usage this month (in seconds).

Below are a few examples that may clarify the usefulness of having Ruby objects abstract database logic.

everyone = Customer.find :all

jay = Customer.find_by_name "Jay Phillips"
jay.phone_number # Performs a SELECT statement
jay.total_bill   # Performs arithmetic on several SELECT statements
jay.group.customers.average :usage_this_month

jay.group.destroy
jay.group = Group.create :description => "New cool group!",
                         :hourly_rate => 1.23
jay.save

Because the database integration here becomes much more natural, Asterisk dial plans becomes much more expressive as well. The following is an example dial plan of a service provider that imposes a time limit on outgoing calls using information from the database.

# Let's assume we're offering VoIP service to customers
# whom we can identify with their callerid. 
service {
  # The line of code below performs an SQL SELECT
  # statement on our database. The find_by_phone_number()
  # method was created automatically because ActiveRecord
  # found a phone_number column in the database. Adhearsion
  # creates the "callerid" variable for us.
  caller = Customer.find_by_phone_number callerid

  usage = caller.usage_this_month
  if usage >= 100.hours
    play "sorry-cant-let-you-do-that"
  else
    play %w'to-hear-your-account-balance press-1 
            otherwise wait-moment'
    choice = wait_for_digit 3.seconds

    if choice == 1
      charge = usage / 60.0 * caller.group.hourly_rate
      play %W"your-account will-reflect-charge-of $#{charge}
              this month for #{usage / 60} minutes and
              #{usage % 60} seconds"
    end

    # We can also write back to the "usage_this_month"
    # property of "caller". When the time method finishes,
    # the database will be updated for this caller.
    caller.usage_this_month += time do
      # Code in this block is timed.
      dial IAX/'main-trunk'/extension
    end
    caller.save
  end
}

Robust database integration like this through Adhearsion brings new ease to developing for and managing a PBX. Centrally persistent information allows Asterisk to integrate with other services cleanly while empowering more valuable services whose needs are beyond that of traditional Asterisk development technologies.

Pages: 1, 2, 3, 4

Next Page