SchemaPlus
Overview
SchemaPlus is an ActiveRecord extension that provides enhanced capabilities for schema definition and querying, including: enhanced and more DRY index capabilities, support and automation for foreign key constraints, and support for views.
For added rails DRYness see also the gems href="http://rubygems.org/gems/schema_associations"> and href="http://rubygems.org/gems/schema_validations">
Compatibility
SchemaPlus supports all combinations of:
-
rails 2.3, 3.0, or 3.1
-
MRI ruby 1.8.7 or 1.9.2
-
PostgreSQL, MySQL (using mysql or mysql2 gem), or SQLite3 (using sqlite3 3.7.7 which has foreign key support)
Support for rails 2.3 will likely be dropped starting in version 0.2.x.
Installation
Install from rubygems.org via
$ gem install "schema_plus"
or in a Gemfile
gem "schema_plus"
Features
Here some examples that show off the high points. For full details see the RDoc documentation.
Indexes
With standard rails migrations, you specify indexes separately from the table definition:
# Standard Rails approach...
create_table :parts do |t|
t.string :name
t.string :product_code
end
add_index :parts, :name # index repeats table and column names and is defined separately
add_index :parts, :product_code, :unique => true
But with SchemaPlus rather than specify your outside your table definition you can specify your indexes when you define each column:
# More DRY way...
create_table :parts do |t|
t.string :name, :index => true
t.string :product_code, :index => :unique
end
Options can be provided index using a hash, for example:
t.string :product_code, :index => { :unique => true, :name => "my_index_name" }
You can also create multi-column indexes, for example:
t.string :first_name
t.string :last_name, :index => { :with => :first_name }
t.string :country_code
t.string :area_code
t.string :local_number :index => { :with => [:country_code, :area_code], :unique => true }
If you’re using Postgresql, SchemaPlus provides support for conditions, expressions, index methods, and case-insensitive indexes; see doc at SchemaPlus::ActiveRecord::ConnectionAdapters::PostgresqlAdapter and SchemaPlus::ActiveRecord::ConnectionAdapters::IndexDefinition
And when you query column information using ActiveRecord::Base#columns, SchemaPlus analogously provides index information relevant to each column: which indexes reference the column, whether the column must be unique, etc. See doc at SchemaPlus::ActiveRecord::ConnectionAdapters::Column
Foreign Key Constraints
SchemaPlus adds support for foreign key constraints. In fact, for the common convention that you name a column with suffix _id
to indicate that it’s a foreign key, SchemaPlus automatically defines the appropriate constraint.
You can explicitly specify foreign key constraints, or override the automatic ones, using the :references
option to specify the table name (and optionally that table’s key column name, if it’s not id
).
Here are some examples:
t.integer :author_id # automatically references table 'authors', key id
t.integer :parent_id # special name parent_id automatically references its own table (for tree nodes)
t.integer :author, :references => :authors # non-conventional column name needs :references for a constraint
t.integer :author_id, :refences => :authors # same as automatic behavior
t.integer :author_id, :refences => [:authors, :id] # same as automatic behavior
t.integer :author_id, :references => :people # override table name
t.integer :author_id, :references => [:people, :ssn] # override table name and key
t.integer :author_id, :referencs => nil # don't create a constraint
You can also modify the behavior using :on_delete
, :on_update
, and :deferrable
t.integer :author_id, :on_delete => :cascade
The foreign key behavior can be configured globally (see Config) or per-table (see create_table).
To examine your foreign key constraints, connection.foreign_keys returns a list of foreign key constraints defined for a given table, and connection.reverse_foreign_keys returns a list of foreign key constraints that reference a given table. See SchemaPlus::ActiveRecord::ConnectionAdapters::ForeignKeyDefinition.
Views
SchemaPlus provides support for creating and dropping views. For example:
create_view :uncommented_posts, "SELECT * FROM posts LEFT OUTER JOIN comments ON comments.post_id = posts.id WHERE comments.id IS NULL"
drop_view :uncommented_posts
ActiveRecord works with views the same as with ordinary tables. That is, for the above view you can define
class UncommentedPosts < ActiveRecord::Base
end
History
-
SchemaPlus is derived from several “Red Hill On Rails” plugins originally created by harukizaemon (github.com/harukizaemon) with later contributions from
-
Michał Łomnicki (github.com/mlomnicki)
-
François Beausoleil (github.com/francois)
-
Greg Barnett (github.com/greg-barnett)
-
Ronen Barzel (github.com/ronen)
-
-
SchemaPlus was created in 2011 by Michał Łomnicki and Ronen Barzel
Testing
SchemaPlus is tested using rspec and rvm, with some hackery to test against multiple versions of rails and ruby and db adapters. To run the tests, after you’ve forked & cloned: Make sure you have Postgresql and MySQL running. Create database user “schema_plus” with permissions for database “schema_plus_unittest”. Then:
$ cd schema_plus
$ bundle install
$ rake postgresql:build_databases
$ rake mysql:build_databases
$ ./runspecs --install # do this once to install gem dependencies for all versions (slow)
$ ./runspecs # as many times as you like
See ./runspecs --help
for more options. You can also manually pick a specific version of rails and ruby to use, such as:
$ rvm use 1.9.2
$ export SCHEMA_ASSOCIATIONS_RAILS_VERSION=3.1
$ bundle update --local rails mysql2 # different versions of rails require different mysql2's
$ rake spec
And you can run the specs for a specific adapter:
$ rake postgresql:spec # to run postgresql tests only
$ rake mysql:spec # to run mysql tests only
$ rake mysql2:spec # to run mysql2 tests only
$ rake sqlite3:spec # to run sqlite3 tests only
If you’re running ruby 1.9.2, code coverage results will be in coverage/index.html – it should be at 100% coverage.
License
This gem is released under the MIT license.