Ruby Database Connectivity

After mostly doing RoR work for the last several months, lately I've had to write a few command line scripts that interact with MySQL.

I came across an article by Steve Litt on Troubleshooters.com, which I found useful for comparing the mysql driver, dbi, and ActiveRecord:

Ruby Database Connectivity

And here a few articles by Paul DuBois on Ruby and database connectivity:

Using the Ruby MySQL Module

Using the Ruby DBI Module

My First RubyGem

I rewrote and converted one of my old sourceforge projects from C++ to a Ruby Gem:

http://delaycalc.rubyforge.org/

Nothing too earth shattering, but it's certainly nice to get an open source project on Rubyforge....

If you need to calculate delay times for digital delay processors for a given bpm, then please give it a go:

$ sudo gem install delaycalc

RubyGem - HighLine

I was looking for a way to prompt a user for input on the console and ran across the RubyGem HighLine. It does a lot more than this, but here's how to capture STDIN from the console:

#!/usr/local/bin/ruby
require 'rubygems'
require 'highline/import'

username = ask("Enter your username:  ") { |q| q.echo = true }
password = ask("Enter your password:  ") { |q| q.echo = "*" }

Here's the output on the console:

$ ruby highline.rb 
Enter your username:  doug
Enter your password:  ******

Get it here:

HighLine

or simply install the gem the normal way:

$ sudo gem install highline

Ruby on Rails - Legacy MySQL Database

I'm rewriting a company admin tool written in mod_perl (Catalyst) and replacing it with a new one using Ruby on Rails. At the moment, I have to keep the legacy database/schema. Most of the old tables don't use auto-incrementing keys, which means I won't get the Rails Active::Record magic and I need to work around the keys.

Here's an example table:

mysql> describe clients;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| client_abbrv | varchar(5) | NO | PRI | | |
| client_name | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+

To get this to work with Rails, I had to make a few small changes.

I made one small change in the view, which was setting the name of the primary key field. I changed "client_abbrv" to "id" in the form:

app/views/client/new.rhtml

<%= error_messages_for 'client' %>

  <fieldset>
    <legend>Add a Client
    <% form_tag :action => "create" do %>
      <p>
        <label for="client_client_name">Client Name:
        <%= text_field 'client', 'client_name'  %>
      </p>
      <p>
        <label for="client_id">Client Abbrv:
        <%= text_field 'client', 'id' %>
      </p>
      <p>
      <%= submit_tag 'Add' %>
      </p>
    <% end %>
  </fieldset>

For the model, I had set the primary key with set_primary_key.

app/models/clients.rb

class Client < ActiveRecord::Base
set_primary_key "client_abbrv"
end

Luckily, the table was already pluralized. If it had been "client," then I would have had to add this line:

def self.table_name() "client" end

For the controller, I had to change the create method:

app/controllers/client_controller.rb

def create
  @client = Client.new
  @client.id = params[:client][:id]
  @client.client_name = params[:client][:client_name]
  if @client.save
    flash[:notice] = "Client was successfully created."
    redirect_to :action => 'list'
  else
    render :action => 'new'
  end
end

That replaced the old create method where I had the normal:

@client = Client.new(params[:client])

When using set_primary_key, you insert data using "id" and typically select data using the actual field name, "client_abbrv" in this case.

Here's what the controller is doing for the create method:

dsparling-imbps-computer:~/my/rubydev/uclick-admin dsparlingimbp$ script/console 
Loading development environment.
>> client = Client.new
=> #<Client:0x32b5e14 @new_record=true, @attributes={"client_name"=>nil}>
>> client.id = "abc"
=> "abc"
>> client.client_name = "ABC Company"
=> "ABC Company"
>> client.valid?
=> true
>> client.save
=> true

Ruby on Rails - script/console

A nice and easy way to check you validation rules...

With a table called users with fields user_name, password, and email, we can add simple validation to the model User.

validates_uniqueness_of :user_name, :email
validates_length_of :user_name, :within => 4..20
validates_length_of :password, :within => 4..20
validates_length_of :email, :maximum => 50
validates_format_of :user_name,
                    :with => /^[A-Z0-9_]*$/i,
                    :message => "must contain only letters, " +
                                      "numbers and underscores"
validates_format_of :email,
                    :with => /@/,
                    :message => "must be a valid email address"

Then using script/console, we can check our validation:

First, try to add a bad record:

$ script/console
Loading development environment.
>> user = User.new(:user_name => "pooh bear",
?> :password => "pb",
?> :email => "poohbear_at_100akerwood.com")
=> #"pooh bear", "password"=>"pb", "email"=>"poohbear_at_100akerwood.com"}>
>> user.save
=> false

Then you can inspect the errors individually with errors.on method:

>> user.errors.on(:user_name)
=> "must contain only letters, numbers and underscores"
>> user.errors.on(:password)
=> "is too short (minimum is 4 characters)"
>> user.errors.on(:email)
=> "must be a valid email address"

or all at once with errors.full_messages method:

>> user.errors.full_messages
=> ["Screen name must contain only letters, numbers and underscores", "Password is too short (minimum is 4 characters)", "Email must be a valid email address"]
>>  

Viewing Ruby Gem documentation

1) Command line

$ ri <gem_name>

2) Browser (http://localhost:8808)

$ gem_server

Sending E-mail with Ruby Net::SMTP

All you need to know is here:
http://www.ruby-doc.org/stdlib/libdoc/net/smtp/rdoc/classes/Net/SMTP.html

def send_email(from, from_alias, to, to_alias, subject, message)

  msg = <<EOF
From: #{from_alias} <#{from}>
To: #{to_alias} <#{to}>
Subject: #{subject}
 
#{message}
EOF

  Net::SMTP.start('localhost') do |smtp|
    smtp.send_message(msg, from, to)
  end
 
end

Or if your mail server is on another machine:

  Net::SMTP.start(mail_server, port, helo_domain) do |smtp|
    smtp.send_message msg, from, to
  end

For example:

smtp.send_message('your.smtp.server', 25, 'mail.from.domain')

Ruby Blog

I'm long-time Perl programmer (Perl/mod_perl) and I've been having a blast moving to Ruby and Ruby on Rails, both at work and for fun. I've decided to create a Ruby specific blog where I can keep my little tidbits of Ruby knowledge. So here we go...