Will’s Web Miscellany

It seems to be a question that comes up fairly often, and it is pretty easy:

$dbh->{TraceLevel} = "SQL";

A while back carl and I had a 99 bottles of beer on the wall one-liner competition in Ruby. I just found this in my home directory and so am claiming it as my entry:

def b(c)"#{c} bottle#{c>1?"s":""} of beer on the wall"end;99.downto(1){|x|$>< <2?"Go to the store and buy some more":"Take one down and pass it around"} #{b (x-1>0?x-1:99)}.

“}

It weighs in at a hefty 206 characters and I know it is possible to get it smaller than this, but it is way too much of a time-sink for me to try.

The Ruby Net::IMAP module has a bit of a warty interface, but it does work. It could do with a much nicer interface (perhaps a Net::IMAP::Simple equivalent?) and better docs. Here is a working example:

#!/usr/bin/env ruby

require 'rubygems'
require 'net/imap'
require_gem 'rmail'

imap = Net::IMAP.new('host')
imap.login('username', 'password')
imap.examine('inbox')
imap.search(["SUBJECT", "Some text to look for", "SINCE", "11-Sep-2005"]).each do |message_id|
message = RMail::Parser.read(imap.fetch(message_id, ["BODY[]“])[0].attr['BODY[]‘])
puts message.header.subject
end

Not too difficult once you work out what you need to be doing.

Quite often I need to take a string from an HTML form, such as “one two three” and search a database field for each of the words contained in the string, eg:

SELECT * FROM table WHERE text ilike '%one%' AND text ilike '%two%' AND text ilike '%three%'

Obviously the strings need quoting, or I am going to be vulnerable to SQL injection attacks. In perl I used to do something like this:

join(" AND ", map {"field ilike " . $dbh->quote("%" . $_ . "%")} qw/one two three/)

I was hoping that ActiveRecord would have a nice way of doing this automatically when passed a string or array of strings, but there doesn’t seem to be one. Instead I came up with this:

@items = Item.find(:all, :conditions => @params["search"].split.map{|x| “item ilike ” + Item.quote(”%” + x + “%”)}.join(” AND “))

The interesting bit broken out looks like this:

"some string seperated by spaces".split.map{|x| "item ilike " + Item.quote("%" + x + "%")}.join(" AND ")

In particular the #quote method of the Item object: Item.quote("%" + x + "%")

The #quote method depends on a PostgreSQL database adaptor being used. The MySQL adapter has a #quote_string method that looks like it does the same thing, but I have not experimented with it.

It would be nice to know if there was a better way of doing this, particularly one that was independent of the database adaptor.

Thanks to leeo on IRC who pointed out that #quote is a method of AbstractConnector (from which both the PG and MySql addapers inherit)