ActiveRecord extension to get more from PostgreSQL:
- Create/drop schemas.
- Set/remove comments on columns and tables.
- Use foreign keys.
- Use partial indexes.
- Run index creation concurrently.
It was tested with Rails 3.1.x and 3.2.x, Ruby 1.8.7 REE and 1.9.3.
NOTE: JRuby is not yet supported. The current ActiveRecord JDBC adapter has its own Rails4-compatible method named "create_schema" which conflicts with this gem.
In migrations you can use
drop_schema methods like this:
class ReplaceDemographySchemaWithPolitics < ActiveRecord::Migration def change drop_schema 'demography' create_schema 'politics' end end
:schema option to specify schema name:
create_table "countries", :schema => "demography" do |t| # columns goes here end
Move table to another schema
demography schema to
move_table_to_schema 'demography.countries', :public
Table and column comments
Provides the following methods to manage comments:
- set_table_comment(table_name, comment)
- set_column_comment(table_name, column_name, comment)
- remove_column_comment(table_name, column_name, comment)
- set_column_comments(table_name, comments)
- remove_column_comments(table_name, *comments)
Set a comment on the given table.
set_table_comment :phone_numbers, 'This table stores phone numbers that conform to the North American Numbering Plan.'
Sets a comment on a given column of a given table.
set_column_comment :phone_numbers, :npa, 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.'
Removes any comment from the given table.
Removes any comment from the given column of a given table.
remove_column_comment :phone_numbers, :npa
Set comments on multiple columns in the table.
set_column_comments :phone_numbers, :npa => 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.', :nxx => 'Central Office Number'
Remove comments from multiple columns in the table.
remove_column_comments :phone_numbers, :npa, :nxx
PgPower also adds extra methods to change_table.
change_table :phone_numbers do |t| t.set_table_comment 'This table stores phone numbers that conform to the North American Numbering Plan.' t.set_column_comment :npa, 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.' end change_table :phone_numbers do |t| t.set_column_comments :npa => 'Numbering Plan Area Code - Allowed ranges: [2-9] for first digit, [0-9] for second and third digit.', :nxx => 'Central Office Number' end
change_table :phone_numbers do |t| t.remove_table_comment t.remove_column_comment :npa end change_table :phone_numbers do |t| t.remove_column_comments :npa, :nxx end
We imported some code of foreigner gem and patched it to be schema-aware. We also added support for index auto-generation.
You should disable
foreigner in your Gemfile if you want to use
If you do not want to generate an index, pass the :exclude_index => true option.
The syntax is compatible with
Add foreign key from
post_id column as key by default:
Specify key explicitly:
add_foreign_key(:comments, :posts, :column => :blog_post_id)
Specify name of foreign key constraint:
add_foreign_key(:comments, :posts, :name => "comments_posts_fk")
It works with schemas as expected:
Adds the index 'index_comments_on_post_id':
Does not add an index:
add_foreign_key(:comments, :posts, :exclude_index => true)
We used a Rails 4.x pull request as a starting point, backported to Rails 3.1.x and patched it to be schema-aware.
Add a partial index to a table
add_index(:comments, [:country_id, :user_id], :where => 'active')
Add a partial index to a schema table
add_index('blog.comments', :user_id, :where => 'active')
Indexes on Expressions
PostgreSQL supports indexes on expressions. Right now, only basic functional expressions are supported.
Add an index to a column with a function
You can also specify index access method
create_extension 'btree_gist' create_extension 'fuzzystrmatch' add_index(:comments, 'dmetaphone(author)', :using => 'gist')
Concurrent index creation
PostgreSQL supports concurent index creation. We added that feature to migration DSL on index and foreign keys creation.
Add an index concurrently to a table
add_index :table, :column_id, :concurrently => true
Add an index concurrently along with foreign key
add_foreign_key :table1, :table2, :column => :column_id, :concurrent_index => true
Loading/Unloading postgresql extension modules
Please note. CREATE/DROP EXTENSION command has been introduced in postgresql 9.1 only. So this functionality will not be available for the previous versions.
Load fuzzystrmatch extension module and create its objects in schema public:
Load version 1.0 of the btree_gist extension module and create its objects in schema demography.
create_extension "btree_gist", :schema_name => "demography", :version => "1.0"
Unload extension module:
Version 1.6.0 introduces experimental support for creating views. This API should only be used with the understanding that it is preliminary 'alpha' at best.
create_view "demography.citizens_view", "select * from demography.citizens"
PgPower::Tools provides number of useful methods:
PgPower::Tools.create_schema "services" # => create new PG schema "services" PgPower::Tools.create_schema "nets" # => create new PG schema "nets" PgPower::Tools.drop_schema "services" # => remove the PG schema "services" PgPower::Tools.schemas # => ["public", "information_schema", "nets"] PgPower::Tools.index_exists?(table, columns, options) # => returns true if an index exists for the given params
If you are using rails 3.x, use previous pg_power version:
gem 'pg_power', '~> 1.6.4'
- Ensure your postgresql has postgres-contrib (Ubuntu) package installed. Tests depend on btree_gist and fuzzystrmatch extensions
- If on Mac, see below for installing contrib packages
spec/dummy/config/database.ymlfor development and test environments.
- Make sure migrations don't raise exceptions and all specs pass.
Installing contrib packages on Mac OS X:
- This assumes you are using MacPorts to install Postgres. If using homebrew or the Postgres App, you will need to adjust the instructions accordingly (please add to this README when you do)
- Assuming you installed with default options (including auto-clean), you will need to rebuild the postgresql port and keep the build files
- sudo port -k -s build postgresql91
- (adjust the version number above appropriately)
- Now you can make and install the btree_gist and any other contrib modules
port work postgresql91/postgresql-9.1.7/contrib/btree_gist
- (again, you may need to adjust the version number to your specific version)
- sudo make all
- sudo make install
Support for JRuby:
- Jdbc driver provides its own
create_schema(schema, user)method - solve conflicts.
- Potapov Sergey - schema support
- Arthur Shagall - thanks for pg_comment
- Matthew Higgins - thanks for foreigner, which was used as a base for the foreign key support
- Artem Ignatyev - extension modules load/unload support
- Marcelo Silveira - thanks for rails partial index support that was backported into this gem
Copyright and License
- Copyright (c) 2012 TMX Credit.
- Initial foreign key code taken from foreigner, Copyright (c) 2009 Matthew Higgins
- pg_comment Copyright (c) 2011 Arthur Shagall
- Partial index Copyright (c) 2012 Marcelo Silveira
Released under the MIT License. See the MIT-LICENSE file for more details.
Contributions are welcome. However, before issuing a pull request, please make sure of the following:
- All specs are passing (under both ree and 1.9.3)
- Any new features have test coverage.
- Anything that breaks backward compatibility has a very good reason for doing so.