Adds support for missing PostgreSQL data types to ActiveRecord.
- Arel support for INET, CIDR and Array related where clauses
- Backport HStore code from Rails 4.0
Add this line to your application's Gemfile:
gem 'postgres_ext'
And then execute:
$ bundle
Or install it yourself as:
$ gem install postgres_ext
Just require 'postgres_ext'
and use ActiveRecord as you normally would! postgres_ext extends
ActiveRecord's data type handling.
Avoid the use of in place operators (ie Array#<<
). These changes are
not tracked by Rails (this issue)
explains why). In place modifications also modify the default object.
Assuming we have the following model:
create_table :items do |t|
t.string :names, :array => true, :default => []
end
class Item < ActiveRecord::Base
end
The following will modify the default value of the names attribute.
a = Item.new
a.names << 'foo'
b = Item.new
puts b.names
# => ['foo']
The supported way of modifying a.names
:
a = Item.new
a.names += ['foo']
b = Item.new
puts b.names
# => []
As a result, in place operators are discouraged and will not be supported in postgres_ext at this time.
create_table :testing do |t|
t.inet :inet_column
# or
t.inet :inet_column_1, :inet_column_2
# or
t.column :inet_column, :inet
end
create_table :testing do |t|
t.cidr :cidr_column
# or
t.cidr :cidr_column_1, :cidr_column_2
# or
t.column :cidr_column, :cidr
end
create_table :testing do |t|
t.macaddr :macaddr_column
# or
t.macaddr :macaddr_column_1, :macaddr_column_2
# or
t.column :macaddr_column, :macaddr
end
create_table :testing do |t|
t.uuid :uuid_column
# or
t.uuid :uuid_column_1, :uuid_column_2
# or
t.column :uuid_column, :uuid
end
Arrays are created from any ActiveRecord supported datatype (including ones added by postgre_ext), and respect length constraints
create_table :testing do |t|
t.integer :int_array, :array => true
# integer[]
t.integer :int_array, :array => true, :limit => 2
# smallint[]
t.string :macaddr_column_1, :array => true, :limit => 30
# char varying(30)[]
end
INET and CIDR values are converted to IPAddr objects when retrieved from the database, or set as a string.
create_table :inet_examples do |t|
t.inet :ip_address
end
class InetExample < ActiveRecord::Base
end
inetExample = InetExample.new
inetExample.ip_address = '127.0.0.0/24'
inetExample.ip_address
# => #<IPAddr: IPv4:127.0.0.0/255.255.255.0>
inetExample.save
inet_2 = InetExample.first
inet_2.ip_address
# => #<IPAddr: IPv4:127.0.0.0/255.255.255.0>
Array values can be set with Array objects. Any array stored in the database will be converted to a properly casted array of values on the way out.
create_table :people do |t|
t.integer :favorite_numbers, :array => true
end
class Person < ActiveRecord::Base
end
person = Person.new
person.favorite_numbers = [1,2,3]
person.favorite_numbers
# => [1,2,3]
person.save
person_2 = Person.first
person_2.favorite_numbers
# => [1,2,3]
person_2.favorite_numbers.first.class
# => Fixnum
PostgreSQL implements the &&
operator, known as the overlap operator,
for arrays. The overlap operator returns t
(true) when two arrays have
one or more elements in common.
ARRAY[1,2,3] && ARRAY[4,5,6]
-- f
ARRAY[1,2,3] && ARRAY[3,5,6]
-- t
Postgres_ext defines array_overlap
, an Arel
predicate for the &&
operator.
user_arel = User.arel_table
# Execute the query
User.where(user_arel[:tags].array_overlap(['one','two']))
# => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"tags\" && '{one,two}'
When querying array columns, you have the ability to see if a predicate
apply's to either any element in the array, or all elements of the
array. The syntax for these predicates are slightly different then the
normal where
syntax in PostgreSQL. To see if an array contains the
string 'test'
in any location, you would write the following in SQL
SELECT *
FROM users
WHERE 'test' = ANY(users.tags)
Notice that the column is on the right hand side of the predicate,
instead of the left, because we have to call the ANY
function on that
column.
We can generate the above query using Arel and generating the Node manually. We would use the following to accompish this:
user_arel = User.arel_table
any_tags_function = Arel::Nodes::NamedFunction.new('ANY', [user_arel[:tags]])
predicate = Arel::Nodes::Equality.new('test', any_tags_function)
# Execute the query
User.where(predicate)
#=> SELECT \"users\".* FROM \"users\" WHERE 'test' = ANY(\"users\".\"tags\")
The ALL version of this same predicate can be generated by swap 'ANY'
for 'ALL'
in the named function.
PostgreSQL defines the <<
, or contained within operator for INET and
CIDR datatypes. The <<
operator returns t
(true) if a INET or CIDR
address is contained within the given subnet.
inet '192.168.1.6' << inet '10.0.0.0/24'
-- f
inet '192.168.1.6' << inet '192.168.1.0/24'
-- t
Postgres_ext defines contained_within
, an Arel
predicate for the <<
operator.
user_arel = User.arel_table
# Execute the query
User.where(user_arel[:ip_address].contained_witin('127.0.0.1/24'))
# => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" << '127.0.0.1/24'
Postgres_ext allows you to specify index type and index operator class at index creation.
add_index :table_name, :column, :index_type => :gin
add_index :table_name, :column, :index_type => :gin, :index_opclass => :gin_trgm_ops
Postgres_ext allows you to specify a where clause at index creation.
add_index :table_name, :column, :where => 'column < 50'