CODEWITHSUNDEEP
ruby-on-rails-3postgresqldatabase-designactiverecord
Jay Godse
Jay Godse

Reputation: 15503

ActiveRecord + postgresql + data constraints

I would like to use ActiveRecord in my Rails application, but I would like to encode data constraints in the PostgreSQL database instead of in the ActiveRecord models.

For example, if I have a model generated by a scaffold, 

 rails generate scaffold User name:string email:string yearofbirth:integer

I'll want to make sure that the name is reasonably long. e.g. 

 class User < ActiveRecord::Base
   validates :name, :length => { :maximum => 140 }
   validates_uniqueness_of :name
   validates_numericality_of :yearofbirth, :greater_than_or_equal_to => 1900
 end

These validations are nice because ActiveRecord will throw up some nice error messages to the view if one tries to add in bad data.

However, I would like to encode the rules in the database itself, and have the database throw up the same nice looking and informative error messages to ActiveRecord, which will then pass them to the view layer. For example, 

 create table "Users" (
   id integer unique, 
   name varchar(140) unique check(length(name)<140), 
   email varchar(255),
   yearofbirth integer check (yearofbirth >= 1900) 
 );

What do I need to do in PostgreSQL or ActiveRecord to make this happen? When I tried it with the pgAdmin III tool, it threw up error messages, but I would like to customize them.

Also, is there a way to encode these database rules right into the database migration files in Ruby?

I appreciate any help you can give me.

Upvotes: 4

Views: 878

Answers (3)

Ludovic Kuty
Ludovic Kuty

Reputation: 4954

Here is an example using Oracle (not Postgresql) but the idea is the same. Only the catalog differs. Note that I named my constraint "table_column_nn" for a not null constraint. The solution below could probably be improved.

Say the model Author representing an Author should not have a NULL name.

class Author < ActiveRecord::Base
  has_and_belongs_to_many :books

  r = ActiveRecord::Base.connection.select_one("select CONSTRAINT_NAME from user_constraints where TABLE_NAME = 'AUTHORS' and constraint_name like '%_NN'")
  m = r["constraint_name"].match(/^[^_]*_([^_]*)_NN$/)
  if !m.nil?
    column = m[1]
    validates column.downcase.to_sym, :presence => true
  end
end

The SQL script to setup the schema is:

create table authors (
    id integer primary key,
    name varchar2(100) constraint authors_name_nn not null,
    updated_at timestamp with local time zone,
    created_at timestamp with local time zone
);

The SQL request alone on the DB gives this:

select constraint_name, search_condition from user_constraints where table_name = 'AUTHORS' and constraint_name like '%_NN';
CONSTRAINT_NAME     SEARCH_CONDITION
---------------     ----------------------
AUTHORS_NAME_NN      "NOM" IS NOT NULL

1 row selected.

Upvotes: 1

m4risU
m4risU

Reputation: 1241

In fact you should have table Users, In other case you can get many more problems. Of course you can define other inflection, but it is simply wrong and not railsway.

class User
  ...
end

then create table Users ...

Upvotes: 1

Peter Eisentraut
Peter Eisentraut

Reputation: 36739

The only thing wrong with the above CREATE TABLE statement is that USER is a reserved word and must be quoted. Therefore,

CREATE TABLE "User" ( ... );

will work. (Choose the capitalization of the name that you intended.)

Upvotes: 1

Related Questions