Ruby Database Interaction

There are a lot of ways to interact with a database in Ruby.  Since Sinatra uses DataMapper, we will use that method for our examples.

Everybody has a MySQL account on the Asterisk server.  Your username and database is your Net ID, and your password is in a file called “sqlpwd” in your home directory.

#!/usr/bin/ruby
require 'data_mapper'

DataMapper.setup(:default, {
 :adapter => 'mysql',
 :host => 'localhost',
 :username => 'ck987' ,
 :password => 'xxxxxx',
 :database => 'ck987'})

class Caller
  include DataMapper::Resource

  property :id, Serial # An auto-increment integer key
  property :caller_id, String # A varchar type string, for short strings
  property :created_at, DateTime # A DateTime, for any date you might like.
end

# Automatically create the tables if they don't exist
DataMapper.auto_upgrade!
# Finish setup
DataMapper.finalize

new_call=Caller.create(:caller_id => "16466429990", :created_at => Time.now)
new_call.save()

The above example saves to a TABLE in your database named callers with a column called caller_id. By including “DataMapper.auto_upgrade!” our script will create the table if it doesn’t already exist.  The code inserts one row into the database table callers and sets the caller_id equal to 16466429990.

It’s important to note that DataMapper will pluralize your class name.  So “class User” becomes the “users” table in the database, and “class Caller” becomes the “callers” table.

Here is an example that pulls all of the caller_id values out of the table:

#!/usr/bin/ruby
require 'data_mapper'

DataMapper.setup(:default, {
 :adapter  => 'mysql',
 :host     => 'localhost',
 :username => 'ck987' ,
 :password => 'xxxxxxx',
 :database => 'ck987'})

class Caller
  include DataMapper::Resource

  property :id,         Serial
  property :caller_id,  String
  property :created_at, DateTime
end

# Finish setup
DataMapper.finalize

calls= Caller.all
calls.each_with_index do |call, i|
  puts "Row: #{i+1}  value: #{call.caller_id}"
end