Snow Giraffe Tech

The newly released ar-extensions version 0.9.1 includes a handful of new goodies I ported and tweaked and merged from now deprecated arperftoolkit, my original toolbox of ActiveRecord tricks we’ve been using at Spongecell for over two years. The database agnostic  ar-extensions plugin and gem by Zach Dennis extends and enhances the functionality of ActiveRecord to provide developers with a bag of tricks to help scale, optimize, and customize Rails interaction with the database.

script/plugin install git://github.com/zdennis/ar-extensions.git
gem install ar-extensions

While the original functionality of database agnostic ar-extensions is to import (bulk insert) many records quickly, my favorite new features for 0.9.1 are the new find and save options which allow developers to quickly customize queries without writing SQL for the MySQL database.

Find

For instance, suppose the wrong index is used to perform your favorite query. This use to mean that the developer had to dig through the logs, find the generated query, and rewrite the entire query with the correct index in SQL and execute it with ActiveRecord::Base.find_by_sql. With ar-extensions, its just another parameter to the find method. Similarly, leveraging MySQL functionality such as SQL_CACHE, HIGH PRIORITY, and LOCK IN SHARED MODE is just as easy.

Beer.find :all, :index_hint => 'USE INDEX (uk_beer_name)'
#SQL: SELECT * from `beers` USE INDEX (uk_beer_name)

Beer.find :all, :keywords => ' SQL_CACHE HIGH_PRIORITY'
#SQL: SELECT SQL_CACHE HIGH_PRIORITY * from `beers`  

Beer.find :all, :post_sql => ' FOR UPDATE', :pre_sql => '/* My little comment*/'
#SQL: /* My little comment*/ SELECT * from `beers` FOR UPDATE 

Finder SQL Exposed

And if that’s not enough to take advantage of MySQL query options, finder_sql_to_string will send the query string back and you can gsub your heart out before execution. It’s also a great to use for debugging purposes.

sql = Beer.send :finder_sql_to_string, :conditions => ['flavor like ?', 'delicious']
Beer.find_by_sql sql.gsub('WHERE', 'where /* I heart lowercase */')

Save and Create

As for save and create, the new options provide more control over database inserts and updates, especially when unique keys are involved.

Animal.create!({:name => 'Jerry Giraffe'},   n_duplicate_key_update => [:password])
#INSERT INTO animals (`name`, `fav_beer`, `password`) VALUES('Jerry Giraffe', 'Pabst', NULL) ON DUPLICATE KEY UPDATE `animals`.`password`=VALUES(`password`)

animal.save(:keywords => 'LOW_PRIORITY', :ignore => true,
            :pre_sql => "/* Now I know where this query is coming from within my Rails code!*/")
#/* Now I know where this query is coming from within my Rails code!*/ UPDATE LOW_PRIORITY IGNORE `animals` SET `fav_beer` = 'Pabst', `password` = 'frenchfry', `name` = 'Party Giraffe' WHERE `id` = 1

In addition to these spiffy new features, ar-extensions provides support for all of these:

• (new) create and save options rdoc
• (new) find options rdoc
• (new) insert_select from one table to another rdoc
• (new) find and count unions rdoc
• (new) delete options rdoc
• import (bulk insert) with synchronization rdoc
• csv export rdoc
• better finder hash support rdoc
• disabling foreign keys rdoc
• fulltext search support rdoc
• temporary table manipulation rdoc

That’s a lot of rope! If you do by chance hang yourself, please send bugs, comments and even patches to the new github issue tracker.

Filed under:
ActiveRecord, MySQL, Plugins, Ruby on Rails, ar-extensions by blythe