FFI Talk at MountainWest RubyConf 2009 (2009-03-14)
I presented my talk on FFI at MountainWest RubyConf yesterday. Lots of good comments from folks, thank you all. Here's all the links from my talk and a copy of the presentation on SlideShare.
- FFI Project at Kenai
- Charles Nutter's announcement
- Wayne Meissner's blog
- Ilya Grigorik's post
- Eric Hodel's post
Crate Goes to Scotland (2009-01-18)
If you are interested in making standalone web applications, Crate and I are
heading to Scotland to talk about that very subject. I'll be talking about
what you need to do package a stand alone webapplication in Crate.
As part of that effort here is a bufix release of Crate. Several bugs have been found in Crate over the past weeks, as a result, I have updated Amagalite to 0.7.1 and Crate to 0.2.1
With these releases I have also updated the Packaging an Application With Crate tutorial
Writing SQL Functions in Ruby (2009-01-10)
I just released Amalgalite 0.6.0 with a couple of major enhancements. The biggest of which is the ability to write your own scalar and aggregate SQL functions in Ruby. You may not know them by that name, but that's the proper terminology according to SQLite.
I'm going to do a brief overview of what these function types are and then show how to write a scalar and an aggregate SQL function for SQLite using Amalgalite.
Scalar SQL Functions
Scalar SQL Functions are the functions used to do some sort of data transformation, or some test on a piece of data. They return a one value, based upon the input parameter(s). In the following statement the length() function is a scalar function.
SELECT name, length( name ) FROM country;
These pages give the complete list of built-in scalar functions in SQLite.
There are lots of functions in there that you probably recognize from general SQL usage: hex(), coalesce(), lower(), trim(), etc.
Aggregate SQL Functions
Aggregate SQL Functions are the functions used to do, well, aggregation of data
across rows. They return a single value, based upon multiple rows of data.
You use aggregate functions when you use GROUP BY
SQL clauses. In this code
example, the sum() function is an aggregate function.
SELECT country, sum( population ) FROM subcountry GROUP BY country;
Aggregate Functions lists the built-in aggregate functions for SQLite.
Writing a Scalar SQL Function
Sometimes you get to a point doing database work that you want your own scalar function, or you know of a scalar function in some other database that you want to use with Amalgalite. For instance the sha1() function is available in PostgreSQL and MySQL, so lets add it for use in our SQLite databases.
Functions are defined with respect to database instances in Amalgalite. They must be defined each time the database is opened.
require 'rubygems' require 'amalgalite' # # Create a database and a contrived test table # db = Amalgalite::Database.new( ":memory:" ) db.execute( "CREATE TABLE test( filename, line_number, data)" ) # # add some data # require 'open-uri' db.prepare("INSERT INTO test( filename, line_number, data ) VALUES( ?, ?, ? )") do |stmt| lines = open("http://redmine.ruby-lang.org/repositories/entry/ruby-18/README.EXT?format=raw").readlines lines.each_with_index do |line, i| stmt.execute( "README.EXT", i + 1, line ) end end # # Define the sha1 SQL function # require 'digest/sha1' db.define_function( 'sha1' ) do |x| Digest::SHA1.hexdigest( x.to_s ) end
And that is it. We just defined the sha1(t) SQL function for use in this database. We told SQLite that there is now a function available sha1 that takes a single argument x. Let's use it.
rows = db.execute("SELECT sha1('I love ruby')") puts rows.first[0] # => d729b72eceebbc2c690ca9b586edd895d8bd4405
And use it on some actual table data too.
db.execute("SELECT data, sha1( data ) as sha1 FROM test") do |row| print "#{row['sha1']}: #{row['data']}" end
Also look at the rdoc for Amalgalite::Database#define_function and Amalgalite::Function for other ways to implement a scalar function.
And a more complete example ships as examples/define_function.rb with Amalgalite.
Writing an Aggregate SQL Function
Aggregate functions are possibly more rare to write, but no less effective. An implementation of an aggregate function is different in that it has 2 methods and needs to save state. Therefore it is implemented as a class. Lets implement a wordcount(s) aggregate that counts the number of words.
# Implementation of the wordcount( s ) SQL aggregate class WordCount < Amalgalite::Aggregate def initialize @name = 'wordcount' @arity = 1 @word_count = 0 end def step( str ) @word_count += str.strip.split(/\s+/).size end def finalize return @word_count end end # define the aggregate in the database db.define_aggregate( 'wordcount', WordCount )
We always define an aggregate as a class. The rules regarding the class are listed in the rdoc for Amalgalite::Aggregate.
The way it works is that for each row that is grouped into a single output row has the step() method called once, and then the finalize() method is called one time at the end. A new instance of the class implementing the aggregate is instantiated every time the aggregate is used in a statement.
row = db.execute("SELECT filename, wordcount( data ) as wc FROM test GROUP BY filename").first puts "#{row['filename']}: #{row['wc']}" # => README.EXT: 4736 db.close
Also read the rdoc for Amalgalite::Aggregate and look at another example examples/define_aggregate.rb that ships with Amalgalite.
Now go define some crazy useful functions.
Next