PgPower

ActiveRecord extension to get more from PostgreSQL:

  • Create/drop schemas.
  • Set/remove comments on columns and tables.
  • Use foreign keys.
  • Use partial indexes.

Environment notes

It was tested with Rails 3.1.x and 3.2.x, Ruby 1.8.7 REE and 1.9.3.

Schemas

Create schema

In migrations you can use create_schema and drop_schema methods like this:

class ReplaceDemographySchemaWithPolitics < ActiveRecord::Migration
  def change
    drop_schema 'demography'
    create_schema 'politics'
  end
end

Create table

Use schema :schema option to specify schema name:

create_table "countries", :schema => "demography" do |t|
  # columns goes here
end

Move table to another schema

Move table countries from demography schema to public:

move_table_to_schema 'demography.countries', :public

Table and column comments

Provides the following methods to manage comments:

  • set_table_comment(table_name, comment)
  • remove_table_comment(table_name)
  • 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)

Examples

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.

remove_table_comment :phone_numbers

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.

Set comments:

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

Remove comments:

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

Foreign keys

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 pg_power.

If you do not want to generate an index, pass the :exclude_index => true option.

The syntax is compatible with foreigner:

Add foreign key from comments to posts using post_id column as key by default: add_foreign_key(:comments, :posts)

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: add_foreign_key('blog.comments', 'blog.posts')

Adds the index 'index_comments_on_post_id': add_foreign_key(:comments, :posts)

Does not add an index: add_foreign_key(:comments, :posts, :exclude_index => true)

Partial Indexes

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.

Examples

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.

Examples

Add an index to a column with a function

add_index(:comments, "lower(text)")

Tools

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

Running tests:

  • Configure spec/dummy/config/database.yml for development and test environments.
  • Run rake spec.
  • Make sure migrations don't raise exceptions and all specs pass.

TODO:

Add next syntax to create table:

create_table "table_name", :schema => "schema_name" do |t|
  # columns goes here
end

Support for JRuby:

  • Jdbc driver provides its own create_schema(schema, user) method - solve conflicts.

Credits

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.

Contributing

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.