gem-hunter
Language: Ruby
Goodbye serialize, hello hstore. Speed up hashes in the database.
If you are using Rails 4 you don't need this gem as ActiveRecord 4 provides HStore type support out of the box. ActiveRecord will see your HStore column and do all of the work for you. Additional code is no longer needed.
You can test it with a migration like this:
class CreateTest < ActiveRecord::Migration def change create_table :tests do |t| t.hstore :data end end end
Its model:
class Test < ActiveRecord::Base # before Rails 4, we'd have to this here: # serialize :data, ActiveRecord::Coders::Hstore end
Then you can use the hash field straight away:
irb(main):003:0> t = Test.new data: {a: 1, b:2} => #<Test id: nil, data: {"a"=>"1", "b"=>"2"}> irb(main):004:0> t.save! (0.3ms) BEGIN SQL (2.3ms) INSERT INTO "tests" ("data") VALUES ($1) RETURNING "id" [["data", "\"a\"=>\"1\",\"b\"=>\"2\""]] (0.5ms) COMMIT => true irb(main):005:0> t => #<Test id: 1, data: {"a"=>"1", "b"=>"2"}> irb(main):006:0> t.data => {"a"=>"1", "b"=>"2"} irb(main):007:0> t.data['a'] => "1"
For more information take a look here
Add settings to users, like in rails-settings or HasEasy.
class User < ActiveRecord::Base serialize :settings, ActiveRecord::Coders::Hstore end user = User.create settings: {theme: 'navy'} user.settings['theme']
Postgresql 8.4+ with contrib and Rails 3.1+ (If you want to try on older rails versions I recommend the 0.6 and ealier versions of this gem)
On Ubuntu, this is easy: sudo apt-get install postgresql-contrib-9.1
On Mac you have a couple of options:
brew install postgresHstore is a PostgreSQL contrib type, check it out first.
Then, just add this to your Gemfile:
gem 'activerecord-postgres-hstore'
And run your bundler:
bundle install
Now you need to create a migration that adds hstore support for your PostgreSQL database:
rails g hstore:setup
Run it:
rake db:migrate
Finally you can create your own tables using hstore type. It’s easy:
rails g model Person name:string data:hstore
rake db:migrate
You’re done. Well, not yet. Don’t forget to add indexes. Like this:
CREATE INDEX people_gist_data ON people USING GIST(data);
or
CREATE INDEX people_gin_data ON people USING GIN(data);
This gem provides some functions to generate this kind of index inside your migrations. For the model Person we could create an index (defaults to type GIST) over the data field with this migration:
class AddIndexToPeople < ActiveRecord::Migration def change add_hstore_index :people, :data end end
To understand the difference between the two types of indexes take a look at PostgreSQL docs.
This gem only provides a custom serialization coder. If you want to use it just put in your Gemfile:
gem 'activerecord-postgres-hstore'
Now add a line (for each hstore column) on the model you have your hstore columns. Assuming a model called Person, with a data field on it, the code should look like:
class Person < ActiveRecord::Base serialize :data, ActiveRecord::Coders::Hstore end
This way, you will automatically start with an empty hash that you can write attributes to.
irb(main):001:0> person = Person.new => #<Person id: nil, name: nil, data: {}, created_at: nil, updated_at: nil> irb(main):002:0> person.data['favorite_color'] = 'blue' => "blue"
Now you just need to learn a little bit of new sqls for selecting stuff (creating and updating is transparent). Find records that contains a key named 'foo’:
Person.where("data ? 'foo'")
Find records where 'foo’ is equal to 'bar’:
Person.where("data -> 'foo' = 'bar'")
This same sql is at least twice as fast (using indexes) if you do it that way:
Person.where("data @> 'foo=>bar'")
Find records where 'foo’ is not equal to 'bar’:
Person.where("data -> 'foo' <> 'bar'")
Find records where 'foo’ is like 'bar’:
Person.where("data -> 'foo' LIKE '%bar%'")
If you need to delete a key in a record, you can do it that way:
person.destroy_key(:data, :foo)
This way you’ll also save the record:
person.destroy_key!(:data, :foo)
The destroy_key method returns 'self’, so you can chain it:
person.destroy_key(:data, :foo).destroy_key(:data, :bar).save
But there is a shortcuts for that:
person.destroy_keys(:data, :foo, :bar)
And finally, if you need to delete keys in many rows, you can:
Person.delete_key(:data, :foo)
and with many keys:
Person.delete_keys(:data, :foo, :bar)
hstore keys and values have to be strings. This means true will become "true" and 42 will become "42" after you save the record. Only nil values are preserved.
It is also confusing when querying:
Person.where("data -> 'foo' = :value", value: true).to_sql #=> SELECT "people".* FROM "people" WHERE ("data -> 'foo' = 't'") # notice 't'
To avoid the above, make sure all named parameters are strings:
Person.where("data -> 'foo' = :value", value: some_var.to_s)
Have fun.
To have hstore enabled when you load your database schema (as happens in rake db:test:prepare), you have two options.
The first option is creating a template database with hstore installed and set the template option in database.yml to that database. If you use the template1 database for this you don't even need to set the template option, but the extension will be installed in all your databases from now on by default. To install the extension in your template1 database you could simply run:
psql -d template1 -c 'create extension hstore;'
The second option is to uncomment or add the following line in config/application.rb
config.active_record.schema_format = :sql
This will change your schema dumps from Ruby to SQL. If you're unsure about the implications of this change, we suggest reading this Rails Guide.
You can use issues in github for that. Or else you can reach us at twitter: @dbiazus or @joaomilho
Copyright © 2010 Juan Maiz. See LICENSE for details.