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 {schema_associations} and {schema_validations}
<img src=“https://secure.travis-ci.org/lomba/schema_plus.png”/> <img src=“https://gemnasium.com/lomba/schema_plus.png” alt=“Dependency Status” />
SchemaPlus supports all combinations of:
-
rails 3.2
-
PostgreSQL, MySQL (using mysql or mysql2 gem), or SQLite3 (using sqlite3 3.7.7 which has foreign key support)
-
MRI ruby 1.9.2 or 1.9.3
Note: As of version 1.0.0, SchemaPlus no longer supports rails 2.3, 3.0 and 3.1 and ruby 1.8.7. The last version to support them was 0.4.1.
Install from rubygems.org via
$ gem install "schema_plus"
or in a Gemfile
gem "schema_plus"
Here some examples that show off the high points. For full details see the RDoc documentation.
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 you can specify your indexes when you define each column, with options as desired
# More DRY way... create_table :parts do |t| t.string :name, index: true t.string :product_code, index: { unique: true } end
The options hash can include an index name:
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 }
And you can specify index orders:
t.string :first_name t.string :last_name, index: { with: :first_name, order: { first_name: :desc, last_name: :asc}}
As a convenient shorthand, the :unique option can be specified as
t.string :product_code, index: :unique
which is equivalent to
t.string :product_code, index: { unique: true }
If you’re using Postgresql, SchemaPlus provides support for conditions, expressions, index methods, and case-insensitive indexes:
t.string :last_name, index: { conditions: 'deleted_at IS NULL' } t.string :last_name, index: { expression: 'upper(last_name)' } t.string :last_name, index: { kind: 'hash' } t.string :last_name, index: { case_sensitive: false } # shorthand for expression: 'lower(last_name)'
These features are available also in ActiveRecord::Migration.add_index. See doc at SchemaPlus::ActiveRecord::ConnectionAdapters::PostgresqlAdapter and SchemaPlus::ActiveRecord::ConnectionAdapters::IndexDefinition
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
SchemaPlus also tidies some index-related behavior:
-
Rails’ various db adapters have inconsistent behavior regarding an attempt to create a duplicate index: some quietly ignore the attempt, some raise an error. SchemaPlus regularizes the behavor to ignore the attempt for all db adapters.
-
If you rename a table, indexes named using rails’ automatic naming convention will be renamed correspondingly.
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.
SchemaPlus also creates foreign key constraints for rails’ t.references or t.belongs_to, which take the singular of the referenced table name and implicitly create the column suffixed with _id.
You can explicitly specify whether or not to generate a foreign key constraint, and specify or override automatic options, using the :foreign_key keyword
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_id, foreign_key: true # same as default automatic behavior t.integer :author, foreign_key: true # non-conventional column name needs to force creation, table name is assumed to be 'authors' t.integer :author_id, foreign_key: false # don't create a constraint t.integer :author_id, foreign_key: { references: :authors } # same as automatic behavior t.integer :author, foreign_key: { reference: :authors} # same default name t.integer :author_id, foreign_key: { references: [:authors, :id] } # same as automatic behavior t.integer :author_id, foreign_key: { references: :people } # override table name t.integer :author_id, foreign_key: { references: [:people, :ssn] } # override table name and key t.integer :author_id, foreign_key: { references: nil } # don't create a constraint t.integer :author_id, foreign_key: { name: "my_fk" } # override default generated constraint name t.integer :author_id, foreign_key: { on_delete: :cascade } t.integer :author_id, foreign_key: { on_update: :set_null } t.integer :author_id, foreign_key: { deferrable: true }
Of course the options can be combined, e.g.
t.integer :author_id, foreign_key: { name: "my_fk", on_delete: :no_action }
As a shorthand, all options except :name can be specified without placing them in a hash, e.g.
t.integer :author_id, on_delete: :cascade t.integer :author_id, references: nil
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.
SchemaPlus provides support for creating and dropping views. In a migration, a view can be created using a rails relation or literal sql:
create_view :posts_commented_by_staff, Post.joins(comment: user).where(users: {role: 'staff'}).uniq create_view :uncommented_posts, "SELECT * FROM posts LEFT OUTER JOIN comments ON comments.post_id = posts.id WHERE comments.id IS NULL"
And can be dropped:
drop_view :posts_commented_by_staff drop_view :uncommented_posts
ActiveRecord works with views the same as with ordinary tables. That is, for the above views you can define
class PostCommentedByStaff < ActiveRecord::Base table_name = "posts_commented_by_staff" end class UncommentedPost < ActiveRecord::Base end
SchemaPlus allows defaults to be set using expressions or constant values:
t.datetime :seen_at, default: { expr: 'NOW()' } t.datetime :seen_at, default: { value: "2011-12-11 00:00:00" }
Note that in MySQL only the TIMESTAMP column data type accepts SQL column defaults and Rails uses DATETIME, so expressions can’t be used with MySQL.
The standard syntax will still work as usual:
t.datetime :seen_at, default: "2011-12-11 00:00:00"
Also, as a convenience
t.datetime :seen_at, default: :now
resolves to:
NOW() # PostgreSQL (DATETIME('now')) # SQLite3 invalid # MySQL
SchemaPlus introduces a constant ActiveRecord::DB_DEFAULT that you can use to explicitly instruct the database to use the column default value (or expression). For example:
Post.create(category: ActiveRecord::DB_DEFAULT) post.update_attributes(category: ActiveRecord::DB_DEFAULT)
(Without ActiveRecord::DB_DEFAULT, you can update a value to NULL but not to its default value.)
Note that after updating, you would need to reload a record to replace ActiveRecord::DB_DEFAULT with the value assigned by the database.
Note also that Sqlite3 does not support ActiveRecord::DB_DEFAULT; attempting to use it will raise ActiveRecord::StatementInvalid
When dumping schema.rb, SchemaPlus orders the views and tables in the schema dump alphabetically, but subject to the requirement that each table or view be defined before those that depend on it. This allows all foreign key constraints to be defined within the scope of the table definition. (Unless there are cyclical dependencies, in which case some foreign keys constraints must be defined later.)
Also, when dumping schema.rb, SchemaPlus dumps explicit foreign key definition statements rather than relying on the auto-creation behavior, for maximum clarity and for independence from global config. And correspondingly, when loading a schema, i.e. with the context of ActiveRecord::Schema.define, SchemaPlus ensures that auto creation of foreign key constraints is turned off regardless of the global setting. But if for some reason you are creating your schema.rb file by hand, and would like to take advantage of auto-creation of foreign key constraints, you can re-enable it:
ActiveRecord::Schema.define do
SchemaPlus.config.foreign_keys.auto_create = true
SchemaPlus.config.foreign_keys.auto_index = true
create_table ...etc...
end
-
Bug fix: More robust handling of foreign keys with schema namespaces
-
README cleanups (thanks to github.com/denispeplin)
-
Now raises ArgumentError if index has both :case_sensitive => false and an :expression
-
Now creates consistent default name for foreign key constraints
-
Bug fix: respect :length keyword for index (thanks to github.com/teleological )
-
Bug fix: renaming table with multiple foreign key constraints (thanks to github.com/teleological )
-
Bug fix: don’t dump :case_sensitive => false for index with an expression that includes “lower(name)”.
-
Bug fix: Properly dump multi-column case-insensitive indexes
-
No longer support rails < 3.2 and ruby < 1.9
-
New feature: specify foreign key constraints using :foreign_key => { … }, motivated in particular to support :name (suggested by github.com/daniele-m)
-
New feature: create view using ActiveRecord relation
-
New feature:
ActiveRecord::DB_DEFAULT(suggested by github.com/zaadjis) -
New feature: renaming a table renames its indexes and constraints correspondingly.
-
Bug fix for postgres :kind index attribute (thanks to github.com/eugenebolshakov)
-
Sort fks in dump for stability (thanks to github.com/zephyr-dev)
-
Bug fix: change_column should maintain foreign key constraints even when config.foreign_keys.auto_create is false
-
Bug fix: quote default expressions in schema dump (thanks to github.com/jonleighton)
-
Bug fix: when removing a foreign key constraint, remove its auto-generated index.
-
Bug fix: SchemaDumper.ignore_tables needs to support regexps (suggested by github.com/mtalcott)
-
Bug fix: More robust handling of Postgresql schema_search path (suggested by github.com/mtalcott)
-
Bug fix: Only get index, view, and foreign key information from current schema (thanks to github.com/bhavinkamani)
-
0.4.1 - Bug fix: don’t attempt foreign key creation for t.belongs_to … :polymorphic => true
-
0.4.0 - Add :force for create_view (suggested by greglazarev). cleanups by betelgeuse
-
0.3.4 - Bug fix: regression causing :default => false to be ignored
-
0.3.3 - Bug fix: properly handle boolean defaults in mysql
-
0.3.2 - Bug fix: make sure rake db:schema:load initializes schema_plus
-
0.3.1 - Bug fix for PostgreSQL schema dump after change_column_default(… nil)
-
0.3.0 - Add :default => expressions (Thanks to Luke Saunders). support rails 3.2 and ruby 1.9.3
-
0.2.1 - Suppress duplicate add_indexes. compatibility with rails 3.2.0.rc2
-
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)
-
Ronen Barzel (github.com/ronen)
-
François Beausoleil (github.com/francois)
-
Greg Barnett (github.com/greg-barnett)
-
Luke Saunders (github.com/lukesaunders)
-
-
SchemaPlus was created in 2011 by Michał Łomnicki and Ronen Barzel
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. You’ll need db users with permissions to create and access databases (schema_plus_test and schema_plus_test2). Default user is ‘postgres’ for Postgresql and ‘schema_plus’ for MySQL; you can change them via
$ export POSTGRES_DB_USER = pgusername $ export MYSQL_DB_USER = mysqlusername
Then:
$ cd schema_plus $ bundle install $ rake create_databases $ ./runspecs --install # do this once, it runs 'bundle install' for all versions $ ./runspecs # as many times as you like
See ./runspecs --help for more options. In particular, to run rspec on a specific file or example (rather than running the full suite) you can do, e.g.
$ ./runspecs [other options] --rspec -- spec/migration_spec.rb -e 'default name'
Code coverage results will be in coverage/index.html – it should be at 100% coverage if you’re running against all databases
This gem is released under the MIT license.