Rails Devs for Data Integrity: How to gracefully handle database key violations

April 24, 2009
Join the club! Rails Devs for Data Integrity

Join the club! Rails Devs for Data Integrity

I am a Rails Developer and I believe in data integrity. There I said it! And look, there are lots of people in the club. Jer on Rails had an awesome post in support for foreign keys a while back.

After my talk on supercharging ActiveRecord to behave in an enterprise environment at the MySQL conference, a lot of folks were interested in how to get around the Rails ActiveRecord errors that appear when one starts deifying the Rails Way and uses foreign keys and unique indexes on the database. Well I thought about it and came up with not only a list but a yet another new plugin rails_devs_for_data_integrity to help deal with some of the errors. So when someone tells you not to use foreign keys, don’t listen and join the Rails Devs for Data Integrity! Here is how to have em, and still make pretty Rails apps.

Super list….

(more…)

9

I lost my java googles at mysqlconf 2009

April 22, 2009

If you find them, please send them to my highly available sharding cluster.

The last programmer I hired told me I accidentally deleted all the java googles in my script folder. He was charging me an arm and a leg to fix it.
Apparently it all needs to be converted to a Norton Anti-Virus file so it can be searchable with a SEO blog widget, so i have to reprogram the flash or upsize it with PHP.

I just can’t afford to keep paying him to fix this problem.
This may lead to other opportunities for the right person.
Actual craigslist post

Whoever actually registered javagoogles.com gets 3 thumbs up.

0

MySQL Conf 09: Taking ActiveRecord to the Next Level

April 22, 2009



fat_giraffeToday I present Taking Active Record To the Next Level at the MySQL Conference and Expo! There is a lot of excitement in the Rails world due to Rails 3 merge with Merb which will provide framework agnosticism for the people. However, good, old, (sometimes not playing nice with MySQL) ActiveRecord is still the defacto despite the cool newcomers. Don’t worry. I bashed my head against the wall with it for 3 years so you don’t have to. There are lots of tips and tricks for unleashing the power of MySQL and whipping ActiveRecord into enterprise ready shape. Oh, and also included are some really cool pictures like the giraffe on the left.

1

ActiveRecord on Steroids: Optimize queries using Ar-Extensions 0.9.1

April 21, 2009

legosThe 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'},  :o 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:

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.

1

ar-extensions 0.9.1 supports INSERT SELECT for ActiveRecord

April 21, 2009

In addition to some other super duper features in version 0.9.1 just released, ar-extensions plugin (and gem) now supports INSERT SELECT functionality for MySQL to move records from one or more tables into another. Instead of querying eleventy billion records, possibly running out of memory, and then so slowly inserting records one by one, ar-extensions extends ActiveRecord to do it all under the covers in one transaction without writing custom SQL.

# Insert one copy of each book into the shopping cart. 
  CartItem.insert_select(:from => :book,
                         :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now],
                         :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at]})

# GENERATED SQL example (MySQL):
# INSERT INTO `cart_items` ( `book_id`, `shopping_cart_id`, `copies`, `updated_at`, `created_at` )
# SELECT books.id, '134', 1, '2009-03-02 18:28:25', now() FROM `books`

Using the :keywords, :pre_sql, and :post_sql the  insert_select options, support all mysql options available to insert and select statements including

  • IGNORE
  • ON DUPLICATE KEY UPDATE
  • LOW_PRIORITY
  • SQL_CACHE
  • DELAY

before and after comments and anything you can imagine you want to customize.

To install:

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

For more documentation, refer to  insert_select rdocs and main rdocs.

3

Rails Migrations, MySQL, unsigned integers, primary keys, and a lot of fun times

April 7, 2009
Customize your primary keys. Pic by Kerri on flickr.

Customize your primary keys. Pic by Kerri on flickr.

Although the standard MySQL type for id columns is an unsigned int, Rails has no support for them and uses regular signed int(11) as the default for primary keys. In addition, most of my tables are less than 16 million rows (max rows for an unsigned mediumint) and so it saves space to use smaller sized numeric types such as smallint or mediumint to save space.

Inspired by Rob Anderton’s patch which was dismissed as an uncommon requirement, I built yet another plugin to allow customization of primary keys, support unsigned integers and allow users to specify column width for numeric types.

script/plugin install http://github.com/blythedunham/mysql_migration_optimizer/tree/master
Rails ignores signed! pic by alex suarez flikr

Rails ignores signs! pic by alex suarez flikr

Specifying Column Width and Unsigned Integers

The MySQL connection adapter was ignoring the :precision and :scale options and so I just loaded em up to allow unsigned numeric types and allow column width to be specified which was supported on postgresql but not MySQL.

add_column :giraffe, :beer_count, :integer, :limit => 2, :precision => :unsigned, :scale => 4
#SQL: ALTER TABLE `giraffe` ADD `beer_count` smallint(4) UNSIGNED

(more…)

0

When to :select and :include your rubies and rails

April 6, 2009
Every blog post could use a finger monkey

Every blog post could use a finger monkey

The vanilla rails ActiveRecord finders do not allow us to specify the :select clause when associations are eager loaded with the :include option. There has been ticket after ticket on the rails site the proposed  patches were rejected on the grounds that an alternative, such as ActiveRecordContext (a fantasic plugin by the way) should be used instead.
From the database perspective, selecting fewer columns can give  huge performance boosts in some situations especially when the selected fields are indexed. However, often when joined tables are sparse (many base table records are pointing to the same joined table records), you might be better off running two queries: one on the base table, followed by a second on the join table with the collected foreign keys. To illustrate this, an employee has a fairly unique address while she shares her position in the company with several others.

Employee.find :all, :include => :position

would probably be less efficient than

employees = Employee.find :all

positions = Positions.find :all, :conditions => ['id in (?)', employees.collect(&:id)]

employees.each{|employee| employee.position = positions.select {|p| p.to_param == employee.posistion_id}

This is exactly what active record context does but without the messy details. In addition, the records are cached so any subsequent references to the associations hit the cache instead of rerunning a query.

(more…)

0

Introducing … Static Record Cache plugin

March 31, 2009

Static Record Cache permanently caches ActiveRecord data for classes which contain small amounts of static data (data that rarely changes). In addition to caching queries on Ids, methods like find_by_name will use cache hits rather than new queries to retrieve data. Static Record Cache is designed to work with or without the built in  query cache, but by contrast permanently stores the actual record across all contexts and avoids the overhead of instantiating new ActiveRecord objects.

Install

script/plugin install git://github.com/blythedunham/static_record_cache.git

Documentation

class SomeStaticClass < ActiveRecord::Base
  acts_as_static_record
end

#cache hits
SomeStaticClass.find_by_name('blah')
SomeStaticClass.find_by_id(5)
SomeStaticClass.find :all
assoc_class.some_static_class

One can specify the cache key for faster lookups and also customize the finder SQL for the class. More documentation can be found in the rdocs.

(more…)

0

Eager loading Select Plugin: when :select plays nice with :include

March 26, 2009

With ActiveRecord::Base.find, :select is ignored when :include is specified. There’s been many a ticket and proposed but denied patch and google discussion to provide this functionality.

eload-select plugin to the rescue!

Employee.find :all, :select => 'addresses.city, address.state, employees.*',
                    :include => :address

The advantages of the eload-select plugin:

  1. Works with aliases and database functions
  2. Employee.find :first,
      :select => 'now() as current_time, addresses.city, DATE(addresses.created_at) as addresses.created_at, employee.*'
      :include => :address
  3. Plays nice with other plugins like ar-extensions
  4. Selects columns on the base table (some options force you to select all of the base table)
  5. Employee.find :all, :select => 'addresses.city, employees.name, employees.start_date',
                        :include => :address
script/plugin install git://github.com/blythedunham/eload-select.git

And your off! Recently updated to work with all versions of rails including 2.3.

2

Why are you still using IE6?

March 19, 2009
Tags: , ,

IE6 is the root of all evil. Writing CSS is pretty bad, but dealing with IE6 makes me want to kill myself.

Joe Lifrieri splash screens provide relevant messages to people who still think using IE6 is acceptible. Awesome.

0