#345 Hstore pro

Apr 24, 2012 | 10 minutes | Tools, Plugins

With hstore you can add schema-less data to PostgreSQL. This allows you to store model attributes without creating separate database columns. Watch how to do this using the activerecord-postgres-hstore gem.

Click to Play Video ▶

Download:
source codeProject Files in Zip (85.5 KB)
mp4Full Size H.264 Video (21.3 MB)
m4vSmaller H.264 Video (11.5 MB)
webmFull Size VP8 Video (13.6 MB)
ogvFull Size Theora Video (25.4 MB)

Browse Source Code

Resources

Update February 6, 2013: As of PostgreSQL 9.2.1, the => hstore operator is deprecated. You should use the hstore(?, ?) function instead. The code below has been updated for this.

Update April 26, 2012: It seems in newer versions of the activerecord-postgres-hstore gem it is necessary to add a serialize call to the model, like this:

          models/product.rb
        
class Product < ActiveRecord::Base
  serialize :properties, ActiveRecord::Coders::Hstore
  # ...
end

This also means it is compatible with the store_accessor method.

          config/application.rb
        
config.active_record.schema_format = :sql

          Gemfile
        
gem 'activerecord-postgres-hstore'

          terminal
        
rails g hstore:setup
rails g migration add_properties_to_products properties:hstore
rails g migration index_products_properties
rake db:migrate
rails c

          rails console
        
p = Product.first
p.properties = {rating: "PG-13", runtime: 107}
p.save!
p.reload.properties
p.properties.object_id
p.properties["runtime"] = 123
p.properties = { ... }
Product.where("properties -> 'rating' = 'PG-13'")
Product.where("properties -> 'rating' LIKE '%G%'")
Product.where("(properties -> 'runtime')::int > 100")
Product.where("properties @> hstore('rating', 'PG-13')")
Product.has_rating("R")

          migrations/*index_products_properties.rb
        
def up
  execute "CREATE INDEX products_properties ON products USING GIN(properties)"
end

def down
  execute "DROP INDEX products_properties"
end

          models/product.rb
        
class Product < ActiveRecord::Base
  attr_accessible :name, :category, :price, :description
  validates_numericality_of :runtime, allow_blank: true
  
  %w[author rating runtime].each do |key|
    attr_accessible key
    scope "has_#{key}", lambda { |value| where("properties @> hstore(?, ?)", key, value) }
    
    define_method(key) do
      properties && properties[key]
    end
  
    define_method("#{key}=") do |value|
      self.properties = (properties || {}).merge(key => value)
    end
  end
end