A spike on using postgresql's HSTORE to store metadata

spike.md

A spike on using postgresql's HSTORE to store metadata on the Cover model (Exmu project)

These are the migration to start with.

The first one activate the extension HSTORE and adds an hstore column 'metadata' on the covers table

class AddMetadataToCovers < ActiveRecord::Migration
  def change
    execute 'CREATE EXTENSION hstore'
    add_column :covers, :metadata, :hstore
  end
end

The second one adds a special index (see http://www.postgresql.org/docs/9.1/static/hstore.html)

class IndexCoversMetadata < ActiveRecord::Migration
  def up
    execute "CREATE INDEX covers_metadata ON covers USING GIN(metadata)"
  end

  def down
    execute "DROP INDEX covers_metadata"
  end
end

Then you may put all your metadata logic into a module, in app/models/concerns/metadata.rb

module Metadata
  extend ActiveSupport::Concern

  included do
    # metadata accessor definitions 
    store_accessor :metadata, :copyright, :title, :subtitle, :enabled, :importance, :language

    # validations
    validates :importance, inclusion: { in: 0..999, message: "%{value} is not a valid importance value" }
    validates :language, inclusion: { in: I18n.available_locales.map(&:to_s) }

    # some useful finders
    %w(copyright title subtitle enabled importance language).each do |key|
      scope "has_#{key}", lambda { |value| where("metadata @> (? => ?)", key, value) }
    end

    # check which records define a specific metadata
    scope "has_key", lambda { |value| where("metadata ? :key", :key => value) }

  end

  def enabled?
    enabled
  end

end

And then you include the module in the Cover model

class Cover < ActiveRecord::Base
  include Flex::ModelIndexer
  flex.sync self

  include Metadata

  # more stuff
  # ... 
end

All done.

Here are some commands on the rails console.

Create a Cover

> Cover.create!(title: "a title", subtitle: "a subtitle", language: "en", importance: 200, enabled: true)
   (0.3ms)  BEGIN
  SQL (31.2ms)  INSERT INTO "covers" ("created_at", "metadata", "updated_at") VALUES ($1, $2, $3) RETURNING "id"  [["created_at", Sat, 01 Feb 2014 23:19:06 CET +01:00], ["metadata", {"title"=>"a title", "subtitle"=>"a subtitle", "language"=>"en", "importance"=>200, "enabled"=>true}], ["updated_at", Sat, 01 Feb 2014 23:19:06 CET +01:00]]

Searching with postgresql

2.1.0 :007 > Cover.where("metadata -> 'importance' = '200'")
  Cover Load (0.7ms)  SELECT "covers".* FROM "covers" WHERE (metadata -> 'importance' = '200')
 => #<ActiveRecord::Relation [#<Cover id: 1, title: nil, subtitle: nil, created_at: "2014-01-31 22:08:41", updated_at: "2014-01-31 22:09:13", image: nil, metadata: {"header"=>"bella", "language"=>"en", "importance"=>"200"}>, #<Cover id: 3, title: "a title", subtitle: nil, created_at: "2014-02-01 20:43:05", updated_at: "2014-02-01 20:44:16", image: nil, metadata: {"header"=>"my head!", "enabled"=>"true", "language"=>"en", "subtitle"=>"a subtitle", "importance"=>"200"}>, #<Cover id: 4, title: "a title", subtitle: nil, created_at: "2014-02-01 20:46:51", updated_at: "2014-02-01 20:47:07", image: nil, metadata: {"header"=>"my head!", "enabled"=>"true", "language"=>"en", "subtitle"=>"a subtitle", "importance"=>"200"}>]>

2.1.0 :005 > Cover.where("(metadata -> 'importance')::int > 100")
  Cover Load (0.7ms)  SELECT "covers".* FROM "covers" WHERE ((metadata -> 'importance')::int > 100)
 => #<ActiveRecord::Relation [#<Cover id: 1, title: nil, subtitle: nil, created_at: "2014-01-31 22:08:41", updated_at: "2014-01-31 22:09:13", image: nil, metadata: {"header"=>"bella", "language"=>"en", "importance"=>"200"}>, #<Cover id: 3, title: "a title", subtitle: nil, created_at: "2014-02-01 20:43:05", updated_at: "2014-02-01 20:44:16", image: nil, metadata: {"header"=>"my head!", "enabled"=>"true", "language"=>"en", "subtitle"=>"a subtitle", "importance"=>"200"}>, #<Cover id: 4, title: "a title", subtitle: nil, created_at: "2014-02-01 20:46:51", updated_at: "2014-02-01 20:47:07", image: nil, metadata: {"header"=>"my head!", "enabled"=>"true", "language"=>"en", "subtitle"=>"a subtitle", "importance"=>"200"}>]>

 2.1.0 :013 > Cover.where("(metadata -> 'enabled')::boolean = true")
  Cover Load (0.7ms)  SELECT "covers".* FROM "covers" WHERE ((metadata -> 'enabled')::boolean = true)
 => #<ActiveRecord::Relation [#<Cover id: 3, title: "a title", subtitle: nil, created_at: "2014-02-01 20:43:05", updated_at: "2014-02-01 20:44:16", image: nil, metadata: {"header"=>"my head!", "enabled"=>"true", "language"=>"en", "subtitle"=>"a subtitle", "importance"=>"200"}>, #<Cover id: 4, title: "a title", subtitle: nil, created_at: "2014-02-01 20:46:51", updated_at: "2014-02-01 20:47:07", image: nil, metadata: {"header"=>"my head!", "enabled"=>"true", "language"=>"en", "subtitle"=>"a subtitle", "importance"=>"200"}>]>

2.1.0 :003 > Cover.where("metadata -> 'header' like '%ella'")
 Cover Load (99.7ms)  SELECT
...

Searching with Elasticsearc

2.1.0 :003 > Search.terms(:importance => 140).first
 FLEX-INFO  Rendered Flex::Scope::Query.get from: (irb):3:in `irb_binding'
 FLEX-DEBUG  :request:
 FLEX-DEBUG    :method: GET
 FLEX-DEBUG    :path: "/spike_development/_search?size=1"
 FLEX-DEBUG    :data:
 FLEX-DEBUG      query:
 FLEX-DEBUG        query_string:
 FLEX-DEBUG          query: "*"
 FLEX-DEBUG      filter:
 FLEX-DEBUG        and:
 FLEX-DEBUG        - term:
 FLEX-DEBUG            importance: 140
 => {"_index"=>"spike_development", "_type"=>"video", "_id"=>"4", "_score"=>1.0, "_source"=>{"metadata"=>{"importance"=>140, "language"=>"en"}, "created_at"=>"2014-02-01T22:37:46.603+01:00", "updated_at"=>"2014-02-01T22:37:46.603+01:00"}}