talkingCode

Using Arel without Rails

Arel is a Relational Algebra for Ruby – a handy way to create SQL queries programmatically without requiring a full ORM. I’ve been doing some work (generating reports) that requires complex queries but between which there’s enough shared functionality to warrant some modularity in the query generation.

Cutting to the chase… Arel requires a database connection. It prefers ActiveRecord (which is probably overkill), but you do at least not have to create all the Model objects normally associated with Rails:

#!/usr/bin/ruby
require 'thread'  # RubyGems/ActiveRecord compatibility fix
require 'rubygems'
require 'active_record'
require 'arel'

@config = # Load some DB config

# Connect to the database
ActiveRecord::Base.establish_connection(
  :adapter => 'mysql',
  :host => @config[:host],
  :username => @config[:username],
  :password => @config[:password],
  :database => @config[:database]
)

# Setup the engine for Arel
Arel::Table.engine = Arel::Sql::Engine.new(ActiveRecord::Base)

# Create an Arel table - should match a table in your database
@users = Arel::Table.new(:users)

all of which is easy enough, completely undocumented, and only requires reading the code of most of the library.

Now, though, you can go ahead and write some fun queries:

@gadgets = Arel::Table.new(:gadgets)
query = @users.project(@users[:id].count).
        join(@gadgets).on(@gadgets[:creator_id].eq(@users[:id])).
        project(@gadgets[:name]).
        group(@gadgets[:name]).
        order(@gadgets[:name].asc)

puts query.to_sql

which outputs the very reassuring “SELECT COUNT(`users`.`id`), `gadgets`.`name` FROM `users` INNER JOIN `gadgets` ON `gadgets`.`creator_id` = `users`.`id` GROUP BY `gadgets`.`name` ORDER BY `gadgets`.`name` ASC”. You can even execute it:

puts "Gadget\tUsers"
ActiveRecord::Base.connection.execute(query.to_sql).each_hash do |row|
  puts [ row["name"], row["count"] ].join("\t")
end

Simple as that. The library has some issues, and no documentation, but I’ve been playing with it on my fork over at Github and it seems pretty okay.

Enjoy

Import into MySQL from CSV/Excel, Ruby

You have some data in a CSV file (or a spreadsheet that you’ve dumped to CSV) that you’d like to load into a MySQL database. Nothing very interesting to say about this except that faffing looking up both sets of docs is tedious for what are quite simple bits of code and a fairly occasional task. (If you spend a lot of your time dealing with CSV / Excel files, you’ve probably made some bad decisions in life, but at least you’ll know this by heart )

The source file is a list of resistor values – column headings ‘Value’ (read product code), ‘Resistor A’, ‘Resistor B’, ‘Resistor C’. The CSV looks something like:

0,1.1,27,180
7,4.7,22,47
21,2.2,18,4.7

The table looks something like:

CREATE TABLE resistor_configs (
  id INT AUTO_INCREMENT,
  value INT,
  resistor_a FLOAT,
  resistor_b FLOAT,
  resistor_c FLOAT,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8

…and the Ruby runs as follows:

#!/usr/bin/ruby
#
require 'csv'
require 'mysql'

my = Mysql::init()
# You can do any SSL stuff before the real_connect
# args: hostname, username, password, database
my.real_connect("localhost", "root", "", "products_development")

my.query("DELETE FROM resistor_configs")

CSV.open('/tmp/resistors.csv', 'r') do |row|
  # No escaping here, because I trust the input file. You may not
  my.query("INSERT INTO resistor_configs" +
               "(value, resistor_a, resistor_b, resistor_c)" +
               "VALUES (#{row[0]}, #{row[1]}," +
               "#{row[2]}, #{row[3]})")
end

There you have it. Nothing very clever, but easier to copy and paste than to read the fine manual.

ERROR 2026 (HY000): SSL connection error – the joy of MySQL SSL on Debian

OpenSSL has some issues. It can’t be linked against GPL software, and Debian only includes free software (in its main archive). So when, in order to encrypt communications to your MySQL server, you issued the magic:

mysql> GRANT ALL PRIVILEGES ON database.* TO 'someuser'@'%' IDENTIFIED BY 'somepassword' REQUIRE SSL;


And tried to connect to the server (an empty certificate suffices for this purpose) with the rune:

# mysql -u someuser -psomepassword -h my.server.com database --ssl --ssl-ca=/dev/null

you might well have been frustrated to see the cryptic

ERROR 2026 (HY000): SSL connection error

Sucks to be you. (N.B. In order for ‘REQUIRE SSL’ to have any effect, you need to have enabled SSL on the server. See /etc/mysql/my.cnf) There are at least two possible causes. One is that the certificates you’ve generated for the server are in some way broken, and that can be true on any system. The other, which plagues the current Debain packages (5.0.32-7etch1 at time of writing) is the OpenSSL linking issue in the client. So what’s to be done? Well the long and the short of it is that if you’re on Debian, you’re at least going to have to recompile the mysql-server package with OpenSSL support, depressing as that undoubtedly is. For reasons of hygiene in linking, we’ll need to do this in a chroot. Don’t worry – it won’t hurt a bit:

cd /usr/local
mkdir chroot
debootstrap etch chroot
# Make yourself a drink.
mount -t proc none chroot/proc/
chroot chroot
# If you've not already got a 'src' URL:
echo deb-src http://ftp.uk.debian.org/debian etch main >> /etc/apt/sources.list
apt-get update
apt-get install devscripts
# At this point, you may start to see
# 'perl: warning: Setting locale failed.'
# If so...
apt-get install locales
dpkg-reconfigure locales
# ... and select the missing locale.
# Doesn't really hurt if you don't do that though.
cd /usr/src
apt-get build-dep mysql-server
apt-get source mysql-server
cd mysql-dfsg-5.0-5.0.32/
# either ...
wget http://talkingcode.co.uk/wp-content/2007/11/patch.txt
patch -p0 < patch.txt
# ... or change the line 'without-openssl' in debian/rules to 'with-openssl'
# and 'with-yassl' to 'without-yassl'
apt-get install libssl-dev
# Change the version:
debchange -v 5.0.32-7etch1+ssl-1 "Added SSL"
dpkg-buildpackage
# Time to go get another drink. Consider getting a biscuit too.
cd ..
ls *ssl*.deb
echo "That's handy"
exit

So now you have your SSL enabled packages, it's a simple matter of installing them on the target machine:

dpkg -i *.deb

(though you could reasonably skip installing the server if you don't need it).

And there you have it - you should now be able to connect over SSL to your server (if your certificates are okay).

If you want to connect from a Python or Perl script using SSL, you're going to need to install the fresh .debs inside the chroot and recompile the appropriate Python and Perl MySQL binding packages in the same chroot so as to make them link the modified libmysqlclient.