Rails_on_pg: PostgreSQL views, functions, triggers, FKs for rails migrations | Hacker News Reader

Rails_on_pg: PostgreSQL views, functions, triggers, FKs for rails migrations

github.com

31 pointsspidaman9 comments

Can anyone clue me in on how this is helpful (not slagging, just generally curious). Like I don't see how this:

  create_view :active_patients do |view|
    view.select 'p.patient_id as id' ,'p.id as visit_id'  
    view.from 'patients as p'  
    view.join 'left join demographics d on d.visit_id=v.id'
    view.conditions 'p.status'=>'active','p.name' => 'John' #or "p.status='active' and p.name='John'"
  end

is easier than:

  create or replace view patient.active_patients as
  select p.patient_id as id,  p.id as visit_id
  from patients p
  left join demographics d on d.visit_id=v.id
  where p.status='active' and p.name = 'John';

I don't use rails, and the ORM we use generates in reverse, so we define our database using SQL and then generate the model layer from that. We use a lot of views, stored procs and postgresql features such as table inheritance, which no ORM seems to handle going the forward route.

Is it because you can't do straight SQL in rail's migration stuff?

Thanks.

Rails migration syntax is intended to be database neutral. That is one of the main reasons to use it. The migration functionality also generates a database neutral 'schema.rb' file, which is intended to be the canonical representation of your application's current schema. This makes it possible for a new developer to simply run "rake db:create db:schema:load" and start working.

Since this particular plugin is aimed directly at postgres, I am not sure it really adds a great deal of value. It might be useful if it maintained the schema.rb file properly, but it does not.

So (at least IMO), I don't think this plugin is all that helpful. You can run straight SQL in rails migrations (and we do that whenever we need anything postgres specific).

10-4 thanks.

Leaves me wondering why database neutrality is so important that you miss a lot of optimal features of a specific database for it. I can't think of one time in the last 15 years where I've moved a database from one particular database software to another (well not entirely true, I've moved around a few but always on Sybase derivatives, ie Sybase -> SQL Server).

You can do straight SQL in migrations, and when I'm working with PostgreSQL I usually do. I don't really see the need or desirability to fully abstract SQL away in Ruby: either you'll have to avoid using some of the features of SQL, or you'll create a DSL to describe SQL which will end up being more verbose than SQL is anyway.

Personally, I use Rails migrations as a versioning system for my DB schema, but within the migrations itself, it's just a straight "execute" call with the SQL to create, alter, or drop the tables as required.

I've been using sexy_pg_constraints with good success for a while. Doesn't do functions like this though.

http://github.com/maxim/sexy_pg_constraints

You do have to switch to sql export for the schema which is kind of a bummer because it's not supported as well by Rails as the "db independent" (aka lowest common denominator) schema.rb file.

Anyone know if it keeps db/schema.rb in sync?

I just tested it with views, functions and a foreign key. None of those things ended up in schema.rb.

The redhillonrails core plugin keeps foreign key constraints in sync in schema.rb. No such luck for triggers or views. But no reason someone couldn't add that functionality.

I thought this was about Paul.

Reply on news.ycombinator.com