What do you guys think about adding support to handle database views in active record?
Cheers,
Gabriel Sobrinho
I’ve been using postgresql views in rails since at least Rails 2.x, maybe even the 1.x days.
Agreed. Using AR to access views is very useful. I would love it if schema.rb was view aware though.
It should work with them now. We don't do anything to specifically remove view support. Is there something specific you had in mind?
You can use views like a model on most database adapters AFAIK. There is no support for an easy way of migrating them, though.
+1 on that.
Supporting views in schema.rb and migrations is almost impossible to do though, we’d have to dump the exact SQL that’s in use or support arbitrary SQL round tripping. If we were going to dump the sql straight from the db that we’d have a poor-man’s version of the :sql schema_format we have already. If we build a round-trip capable SQL parser we’re clinically insane and should be institutionalised.
I don’t really see what more we need to support, can’t you just use :sql for the schema_format and you get all the functionality you want, and more?
One thing that I have recently found myself desiring is the ability to augment schema.rb with snippets of SQL.
For example, I use Postgres, and need to declare some case-insensitive indexes. This is not currently possible using Rails directly.
I don't want to switch to use the SQL schema dump format, because that's highly dependent on the database version. E.g. I am using Postgres 9, and another developer is using 8.4. We will probably end up overwriting each other's schema.sql files if we went this route.
But it would be nice to have, say, a db/schema_extra.sql where we could manually place additional stuff like our case-insensitive indexes or whatever.
Or views...
John,
Perhaps make a few directories like db/procedures, db/views, etc and just create a rake task in the db namespace that overrides or extends those in ActiveRecord's databases.rake? I've had to do this often with legacy databases in many forms. This github project talks about it for those coming from legacy DBs in SQL Server.
https://github.com/rails-sqlserver/AdventureWorks.Ruby#test-database-tasks
In some cases, I use a little rake extension called #alias_task when needing to override a particular task.
http://metaskills.net/2010/05/26/the-alias_method_chain-of-rake-override-rake-task/
In your case, since you want to stick with schema.rb, you could perhaps #alias_task on the db:schema:load task, then invoke it, then load up your procedures, views etc from the nested db directories? I would be open to hearing how others do this too, but I have always found working with special schemas and DDL's quite easy with ActiveRecord when you know the pressure points.
- Ken
In 3.2 and above, you can use schema_format = :sql to generate a structure.sql file instead of the schema.rb.
https://github.com/rails/rails/commit/43821bf3513c27913a4d861c83e11b0ad6299916
I’m finding using this all the time, as it keeps my views from the migrations, and it also allows me to activate postgres extensions directly from the migration.
Damien MATHIEU | Ingénieur logiciel
84, rue Chevreul | 69 007 Lyon
Tel. : +33 (0)6 88 42 00 15 | http://dmathieu.com
I have been using schema_format = :sql for adding postgres extensions and haven’t had any issues with it.
Anuj
I mean support for create and drop views in migrations and schema handle it.
Using sql schema format is an option but we can fall into cases where devs uses different versions of same database.
That’s not my case, so, I will write SQL commands in migrations and use sql schema format.
Thanks 