The constraint_validations extension is designed to easily create database constraints inside create_table and alter_table blocks. It also adds relevant metadata about the constraints to a separate table, which the constraint_validations model plugin uses to setup automatic validations.
To use this extension, you first need to load it into the database:
Note that you should only need to do this when modifying the constraint validations (i.e. when migrating). You should probably not load this extension in general application code.
You also need to make sure to add the metadata table for the automatic validations. By default, this table is called sequel_constraint_validations.
This table should only be created once. For new applications, you generally want to create it first, before creating any other application tables.
Because migrations instance_exec the up and down blocks on a database, using this extension in a migration can be done via:
Sequel.migration do up do extension(:constraint_validations) # ... end down do extension(:constraint_validations) # ... end end
However, note that you cannot use change migrations with this extension, you need to use separate up/down migrations.
The API for creating the constraints with automatic validations is similar to the validation_helpers model plugin API. However, instead of having separate validates_* methods, it just adds a validate method that accepts a block to the schema generators. Like the create_table and alter_table blocks, this block is instance_execed and offers its own DSL. Example:
DB.create_table(:table) do Integer :id String :name validate do presence :id min_length 5, :name end end
instance_exec is used in this case because create_table and alter_table already use instance_exec, so losing access to the surrounding receiver is not an issue.
Here's a breakdown of the constraints created for each constraint validation method:
|All constraints except unique unless :allow_nil is true||
CHECK column IS NOT NULL
CHECK trim(column) != ''
CHECK char_length(column) = 5
CHECK char_length(column) >= 5
CHECK char_length(column) <= 5
CHECK char_length(column) >= 3 AND char_length(column) <= 5
CHECK char_length(column) >= 3 AND char_length(column) < 5
CHECK column ~ 'foo\d+'
CHECK column ~* 'foo\d+'
CHECK column LIKE 'foo%' ESCAPE ''
CHECK column ILIKE 'foo%' ESCAPE ''
|includes ['a', 'b']||
CHECK column IN ('a', 'b')
|includes [1, 2]||
CHECK column IN (1, 2)
CHECK column >= 3 AND column <= 5
CHECK column >= 3 AND column < 5
|operator :>, 1||
CHECK column > 1
|operator :>=, 2||
CHECK column >= 2
|operator :<, “M”||
CHECK column < 'M'
|operator :<=, 'K'||
CHECK column <= 'K'
There are some additional API differences:
Only the :message and :allow_nil options are respected. The :allow_blank and :allow_missing options are not respected.
A new option, :name, is respected, for providing the name of the constraint. It is highly recommended that you provide a name for all constraint validations, as otherwise, it is difficult to drop the constraints later.
The includes validation only supports an array of strings, and array of integers, and a range of integers.
There are like and ilike validations, which are similar to the format validation but use a case sensitive or case insensitive LIKE pattern. LIKE patters are very simple, so many regexp patterns cannot be expressed by them, but only a couple databases (PostgreSQL and MySQL) support regexp patterns.
The operator validation only supports >, >=, <, and <= operators, and the argument must be a string or an integer.
When using the unique validation, column names cannot have embedded commas. For similar reasons, when using an includes validation with an array of strings, none of the strings in the array can have embedded commas.
The unique validation does not support an arbitrary number of columns. For a single column, just the symbol should be used, and for an array of columns, an array of symbols should be used. There is no support for creating two separate unique validations for separate columns in a single call.
A drop method can be called with a constraint name in a alter_table validate block to drop an existing constraint and the related validation metadata.
While it is allowed to create a presence constraint with :allow_nil set to true, doing so does not create a constraint unless the column has
Note that this extension has the following issues on certain databases:
MySQL does not support check constraints (they are parsed but ignored), so using this extension does not actually set up constraints on MySQL, except for the unique constraint. It can still be used on MySQL to add the validation metadata so that the plugin can setup automatic validations.
On SQLite, adding constraints to a table is not supported, so it must be emulated by dropping the table and recreating it with the constraints. If you want to use this plugin on SQLite with an alter_table block, you should drop all constraint validation metadata using
drop_constraint_validations_for(:table=>'table'), and then readd all constraints you want to use inside the alter table block, making no other changes inside the alter_table block.
Dropping a table will automatically delete all constraint validations for that table. However, altering a table (e.g. to drop a column) will not currently make any changes to the constraint validations metadata.