search

HornsAndHooves / pg_saurus

ActiveRecord extension to get more from PostgreSQL.
Other
27 stars 12 forks source link

readme

PgSaurus

Build Status Code Climate

An ActiveRecord extension to get more from PostgreSQL:

PgSaurus is a fork of PgPower.

More information

Environment notes

PgSaurus v4 was tested with Rails 5.2 and Ruby 2.4. For Rails 4.2, use PgSaurus v3. For Rails 4.1, use PgSaurus v2.5+. Older versions of Rails are not supported.

NOTE: JRuby is not supported.

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'

    drop_schema_if_exists('demography')
    create_schema_if_not_exists('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:

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

PgSaurus also adds extra table methods to the change_table block.

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

PgSaurus v3 augments Rails 4.2's foreign key methods with:

When you create a foreign key PgSaurus automatically creates an index. If you do not want to generate an index, pass the exclude_index: true option. The syntax is compatible with Rails 4.2's foreign key handling methods.

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)

Note that removing a foreign key does not drop the index of the foreign key column. If you want to remove the index, pass in the remove_index: true option.

remove_foreign_key(:comments, column: :post_id, remove_index: true)

Migration notes - upgrading from Rails 4.2

PgSaurus v4.X requires Rails 5. Rails 5.2 is recommended. You can use the new Rails 5 semantics to create comments and indexes inline. You also need to use the index order options using the Rails 5 semantics.

# THIS FAILS
add_index :books, ["author_id DESC NULLS FIRST", "publisher_id DESC NULLS LAST"],
          name: "books_author_id_and_publisher_id"

# DO THIS INSTEAD
add_index :books, ["author_id", "publisher_id"],
          name: "books_author_id_and_publisher_id",
          order: { author_id: "DESC NULLS FIRST", publisher_id: "DESC NULLS LAST" }

Migration notes - upgrading from Rails 4.1

PgSaurus v3.X now uses the Rails 4.2 semantics for add_foreign_key and remove_foreign_key. See http://api.rubyonrails.org/v4.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html

A few things have changed. The most breaking change is that the syntax remove_foreign_key :from_table, :to_table, options no longer works.

#THIS FAILS
remove_foreign_key :comments, :posts, remove_index: true

#DO THIS INSTEAD
remove_foreign_key :comments, column: :post_id, remove_index: true

For adding foreign keys, the :dependent option is replaced with :on_delete and :on_update. The :delete value is replaced with :cascade.

#OLD STYLE - NO LONGER WORKS
add_foreign_key :comments, :posts, dependent: :delete

#NEW STYLE - DO THIS INSTEAD
add_foreign_key :comments, :posts, on_delete: :cascade

Partial Indexes

Rails 4.x pull request was used as a starting point to patch it to be schema-aware.

Examples

Add a partial index to a table:

add_index(:comments, [:country_id, :user_id, :category], where: "category IN ('foo', 'bar')")

Add a partial index to a schema-qualified table:

add_index('blog.comments', :status, where: "status = '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)")

You can also specify the index access method:

create_extension 'btree_gist'
create_extension 'fuzzystrmatch'
add_index(:comments, 'dmetaphone(author)', using: 'gist')

Indexes with operator classes

Specifying an operator class on each column of an index is supported.

Examples

Add an index with a custom ops class:

add_index(:books, "title varchar_pattern_ops")

Concurrent index creation

PostgreSQL supports concurrent index creation. PgSaurus supports that feature by adding support to the migration DSL on index and foreign key creation.

Examples

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

PostgreSQL ships with a number of extension modules. PgSaurus provides some tools to load and unload such modules using migrations.

Please note: CREATE/DROP EXTENSION command was introduced in PostgreSQL 9.1. So this functionality is not available in previous versions.

Examples

Load fuzzystrmatch extension module; and create its objects in schema public:

create_extension "fuzzystrmatch"

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 an extension module:

drop_extension "fuzzystrmatch"

Views

PgSaurus v1.6.0 introduced experimental support for creating views. This API should only be used with the understanding that it is preliminary 'alpha' at best.

Examples

create_view "demography.citizens_view", "select * from demography.citizens"

drop_view "demography.citizens_view"

Roles

If you want to execute a migration as a specific PostgreSQL role you can use the set_role method:

class CreateRockBands < ActiveRecord::Migration
  set_role "rocker"

  def change
    create_table :rock_bands do |t|
      # create columns
    end
  end
end

Technically it is equivalent to the following:

class CreateRockBands < ActiveRecord::Migration
  def change
    execute "SET ROLE rocker"
    create_table :rock_bands do |t|
      # create columns
    end
  ensure
    execute "RESET ROLE"
  end
end

You may force all migrations to have set_role, for this, configure PgSaurus with ensure_role_set = true:

PgSaurus.configure do |config|
  config.ensure_role_set = true
end

Functions

You can create, list, and drop functions.

Examples

Create a function:

pets_not_empty_function = <<-SQL
BEGIN
  IF (SELECT COUNT(*) FROM pets) > 0
  THEN
    RETURN true;
  ELSE
    RETURN false;
  END IF;
END;
SQL

# Arguments are: function_name, return_type, function_definition, options (currently, only :schema and :volatility)
create_function 'pets_not_empty()', :boolean, pets_not_empty_function, schema: 'public', volatility: 'stable'

Drop a function:

drop_function 'pets_not_empty()'

Get a list of defined functions:

ActiveRecord::Base.connection.functions

Triggers

You can create and remove triggers on tables and views.

Examples

Create a trigger:

create_trigger :pets,                           # Table or view name
               :pets_not_empty_trigger_proc,    # Procedure name. Parentheses are optional if you have no arguments.
               'AFTER INSERT',                  # Trigger event
               for_each: 'ROW',                 # Can be a row or a statement. Default is row.
               schema: 'public',                # Optional schema name
               constraint: true,                # Sets whether the trigger is a constraint. Default is false.
               deferrable: true,                # Sets whether the trigger is immediate or deferrable. Default is immediate.
               initially_deferred: true,        # Sets whether the trigger is initially deferred. Default is immediate.
                                                # Only relevant if the trigger is deferrable.
               condition: "new.name = 'fluffy'" # Optional when condition. Default is none.

Drop a trigger:

remove_trigger :pets, :pets_not_empty_trigger_proc

Get a list of defined triggers on a table or view:

ActiveRecord::Base.connection.triggers

Tools

PgSaurus::Tools provides a number of useful methods for managing schemas, etc.:

PgSaurus::Tools.create_schema "services"                 # => create new PG schema "services"
PgSaurus::Tools.create_schema "nets"                     # => create new PG schema "nets"
PgSaurus::Tools.drop_schema "services"                   # => remove the PG schema "services"
PgSaurus::Tools.create_schema_if_not_exists "nets"       # => Does nothing -- schema "nets" already exists
PgSaurus::Tools.drop_schema_if_exists "services"         # => Does nothing -- schema "services" doesn't exist
PgSaurus::Tools.schemas                                  # => ["public", "information_schema", "nets"]
PgSaurus::Tools.index_exists?(table, columns, options)   # => returns true if an index exists for the given params

Running tests

Installing contrib packages on Mac OS X:

TODO

Support for Rails 6+

Possible support for JRuby:

Credits

Copyright and License

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: