Ruby FFI example using #ffi_lib

Ruby FFI is a cross-VM library for calling foreign functions (i.e. C or C++).  It isn’t obvious from the introductory blog posts how you specify which library to use, but the answer isn’t hard to find in the source.
Examples speak louder than words:
require 'rubygems'
require 'ffi'

class MDB
  extend FFI::Library
  # The lib name gets spackled with platform-specific 
  # prefix and suffix. On Mac OS X, e.g., the ffi_lib
  # name turns into 'libmdb.dylib'
  ffi_lib 'mdb'
  # Who needs enum, anyway?
  attach_function :mdb_init, [], :void
  attach_function :mdb_exit, [], :void
  # In the libmdb headers, you'll find that this function
  # actually returns a pointer to an MDBHandle struct.  
  # FFI::Struct would likely help out here, but just
  # calling the return result a :pointer works for now.
  attach_function :mdb_open, [ :string, :int], :pointer
  attach_function :mdb_close, [ :pointer], :void 
    db = MDB.mdb_open( path, MDB::NOFLAGS)
    yield db
  attach_function :mdb_dump_catalog, [:pointer, :int], :pointer
end'mdb_files/sample.mdb') do |db|
  MDB.mdb_dump_catalog(db, MDB::MDB_TABLE)

ActiveMDB is on GitHub

ActiveMDB development, such as it is, will now take place on GitHub.

How to compile mdbtools on Mac OS X 10.4 and 10.5

Update: MacPorts appears to have a working port of mdbtools now.


You’ll need MacPorts, the mdbtools source, and a simple patch. Use macports to install glib2, libtool, and automake:

port install glib2 libtool automake

One commenter reported that he had to upgrade version 2.5.35 of flex. I had no trouble with the version of flex included with Leopard, viz. 2.5.33.

MDB Tools source

You can get the mdbtools source from CVS, via the instructions at the sourceforge site, and my patch here.

Alternatively, use a git repo I started because CVS makes baby Theanthropos cry:

git clone git:// mdbtools && make && make install

cd into the mdbtools directory and run Pass any configuration args to, and it will pass them along to configure. /usr/local is the default prefix. The options below set the install location, enable compilation of the mdb-sql tool, but not gmdb2, the Gnome MDB File Viewer and debugger.

./ --prefix=/users/Matthew/local --enable-sql --disable-gmdb2
make install

Assume that make and install work: you can test the results like so:

mdb-ver /path/to/thingy.mdb
mdb-tables /path/to/thingy.mdb
mdb-schema /path/to/thingy.mdb

How to use ActiveMDB in Ruby on Rails

First, let me say: You probably shouldn’t use ActiveMDB in Rails. ActiveMDB is intended for exploration and for exciting action-movie narrow escapes from Access databases.


If you really, really need to, though, here’s how:

Install MDB Tools

Install the ActiveMDB gem:
gem install activemdb

Require the library somewhere. ./config/environment.rb might work
require 'active_mdb'

In a model file (e.g. ./app/models/windows_malware.rb) create a model that subclasses ActiveMDB::Base.
Set the path to the .mdb file and the name of the table.

class WindowsVirus < ActiveMDB::Base
  set_mdb_file '/var/db/windows_support.mdb'
  set_table_name 'Windows_Virises'

You can use the ActiveMDB model in your controllers much like you would an ActiveRecord model. The only find methods at the time of this writing are find_all and find_first. These methods take a hash that specifies the conditions for the WHERE clause. The keys to the hash are symbols representing the field names in the Access database. ActiveMDB will let you use downcased-underscored versions of the field names from the db. E.g. you can use :executable_name for an Access field “Executable Name”. When the field type is text or char, the WHERE conditions use LIKE with wildcards before and after the search value.

viruses = WindowsVirus.find_all :executable_name => 'virus.exe', :severity => 2

Once you have an instance of an ActiveMDB class, you can use the same Rails-like field names as methods to retrieve attributes:

return unless viruses.first.executable_name =~ /exe/

ActiveMDB: Ruby MDB Tools on Linux and Mac OS X

This may shock my target audience, but there exist people who actually use Access, as well as other Microsoft products. In my day job, I continually acquire responsibility for more and more Access applications, and I can’t arbitrarily make my users stop using them.

It didn’t take me long to decide not to actually learn Access. Microsoft has done at least a half-decent job of making a user interface that builds interfaces to relational databases, but the tool is too powerful for the users. Some of the schemas out there make baby Matthew cry.

Plus, this was in the days Before Parallels. Which meant that I had to use Windows when to coax information out of Access. That’s not cool.

Plus also, as well, one of my projects was to build an online member directory for an entity which uses an Access app for member data in the office. Extra-whiny “That’s not cool.” I want the info out of Access and into a datastore of my choosing, and I want to use Ruby.

Google found me Brian Bruns’s MDB Tools, which lets users of sane platforms retrieve a few kinds of things from an Access .mdb file. Yes, I could have used whatever ODBC layer Microsoft provides, but that still means running a Windows box. MDB Tools may be read only, but the point is to escape from the abusive relationship, not to keep feeding it.

MDB Tools comprises a library for accessing the data and metadata in mdb files and some shell utilities for using the library. So far I have only used the tools, but I hope someday to write a Ruby extension to use LibMDB. This would require that I learn C. I have some books.

Until then, there’s ActiveMDB, available as a gem on Ruby forge. Like this:

sudo gem install activemdb

ActiveMDB is a thin wrapper around MDB Tools that, as the name suggests, is a slavish imitation of the features of ActiveRecord that I needed that weren’t too much trouble to implement. Version 0.1.0 was usable. I used it. Don’t use it.

Not that you would, because I just released 0.2.0, which is more usable by a not insignificant margin. This version follows the pattern of SomeThing::Base class that you subclass to make models of your tables.

To use ActiveMDB, unless you collect error messages, you really ought to install MDB Tools, which may mean compile. I had some very minor difficulties compiling it on Mac OS X 10.4.x, which I will detail in a separate post. Also, I just this moment realized that ActiveMDB assumes the MDB Tools utilities are in your path. ActiveMDB relies on mdb-sql, mdb-tables, and mdb-ver.

If your mdb file is a black box, by which I mean you don’t know the table names or schemas, you can poke around a little with the methods in the MDBTools module:

mdb_tables('Good Movies.mdb') 
  #=> ['Movies', 'Directers', 'Acters', 'Nude Seens']

describe_table('Good Movies.mdb', 'Acters') 
  #=> [ {"Size"=>"100", "Column Name"=>"Their_Name","Type" => "Text"},
  #     {"Size"=>"100", "Column Name"=>"Hair_Color","Type" => "Text"}
  #     {"Size"=>"100", "Column Name"=>"Acter_Id","Type" => "Text"}]

Once you know your table names and whether the tables possess anything approaching a usable primary key, you create a model class and specify the mdb file. If you don’t set a table name, ActiveMDB uses the same pattern as ActiveRecord to assume one. Right now, you only need to set a primary key if you plan to call the #count method, because mdb-sql doesn’t do aggregate functions and I totally faked it.

class Actor < ActiveMDB::Base
    set_mdb_file 'Good Movies.mdb'
    set_table_name 'Acters'
    set_primary_key 'Acter_Id'

Then you can call the find_first or find_all method. My needs are still really simple, so there’s only one way to specify your WHERE clause, and it goes like this:

@mal = Actor.find_first :Their_Name => 'Nathan Fillion', 
:Hair_Color => 'Brown'
@could_be_saffron = Actor.find_all :Hair_Color => 'Orange'

The resulting Actor instances have methods named after the columns, verbatim. Thus you’d have to call @could_be_saffron.Hair_Color, which is not cool.

The find_all and find_first methods do LIKE ‘%thingy%’ in the WHERE clause. This blows up with Booleans, so I’ve started working on some type casting (by which I mean ripping off the way ActiveRecord does it).