Giter Site home page Giter Site logo

patientslikeme / postgres_ext Goto Github PK

View Code? Open in Web Editor NEW

This project forked from davyjoneslocker/postgres_ext

0.0 49.0 0.0 272 KB

Adds support for missing PostgreSQL data types to ActiveRecord.

License: MIT License

JavaScript 0.84% Ruby 99.16%

postgres_ext's Introduction

PostgresExt

Adds support for missing PostgreSQL data types to ActiveRecord.

Build Status Code Climate

Roadmap

  • Arel support for INET, CIDR and Array related where clauses
  • Backport HStore code from Rails 4.0

Installation

Add this line to your application's Gemfile:

gem 'postgres_ext'

And then execute:

$ bundle

Or install it yourself as:

$ gem install postgres_ext

Usage

Just require 'postgres_ext' and use ActiveRecord as you normally would! postgres_ext extends ActiveRecord's data type handling.

Usage Notes

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.

Migration/Schema.rb support

INET

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

CIDR

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

MACADDR

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

UUID

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

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

Type Casting support

INET and CIDR

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> 

Arrays

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

Querying PostgreSQL datatypes

Arrays

&& - Array Overlap operator

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}'

ANY or ALL functions

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.

INET/CIDR

<< -- Contained within operator

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'

Indexes

Index types

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

Where clauses

Postgres_ext allows you to specify a where clause at index creation.

add_index :table_name, :column, :where => 'column < 50'

Authors

Dan McClain twitter github

postgres_ext's People

Contributors

danmcclain avatar skandragon avatar bcardarella avatar duggiefresh avatar alno avatar gilltots avatar mariovisic avatar legendetm avatar

Watchers

Nat Budin avatar Michael Berkowitz avatar Chris Turner avatar Thijs de Vries avatar Cris Necochea avatar Doug Marttila avatar David Madrigal avatar Shimon Rura avatar Adam Darowski avatar Thariq Shihipar avatar Stuart Garner avatar  avatar  avatar Bryan Alves avatar Jon Sagotsky avatar James Cloos avatar Alex Bromley avatar Jeremy Gilbert avatar Morgan Wigmanich avatar BBoyle avatar Aniket Schneider avatar  avatar Ian Macomber avatar Margaret Parsa avatar Scott I avatar  avatar  avatar Cory Farinella avatar Bob Moore avatar Evan Lloyd avatar Alexander Golden avatar  avatar Stephanie Habib avatar Steph Eaneff avatar Jonathan Wauhkonen avatar  avatar  avatar  avatar Robert Basso avatar  avatar Henry Morgan avatar  avatar Vince DeVendra avatar  avatar  avatar  avatar  avatar Soo Bin Yang avatar Lisa Z. Truong avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    ๐Ÿ–– Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. ๐Ÿ“Š๐Ÿ“ˆ๐ŸŽ‰

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google โค๏ธ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.