When it comes to using database views then most of us think that it is difficult to use them with Rails or we may not be able to use ActiveRecord with it. Since I experimented with it in the past so I thought why not share it with you folks.
There are several situations when we would like to use database views with our Rails application like:
Situation 1:
If we have a complex SQL query that we don’t want to code in your Rails app due to performance related or any other issues.
Situation 2:
When our database guys have already spent a great amount of time writing useful views that can be used in the current app
Situation 3:
When we have some repeated query to run in every select statement to fetch data from every table. For instance
Select * from table_name where created_on < today;
In that case we would prefer to have a view with this query and call that view.
In all the above situations, it is pretty easy to use our database views with Rails.
How?
Simply create your model as you would for a table and it will work fine just like your model for a database table.
Interesting thing is, apart from viewing data using views I can even update, delete and insert data in my database tables using my views with all those ActiveRecord/ORM niceties (in Sybase) as long as each database view consists of only one table. If you are using more than one table in your database views then you can only view data but cannot do delete, update and insert operation.
I think this is really useful if we have a database that do not conform to Rails conventions but we would like to follow those conventions without changing the current database if other applications depend on it as well. Simply create views for each table in the database and use those views for our models instead of actual tables.
Those single table views (in other words updatable views) will allow us to follow Rails database conventions without changing our existing database tables while also keeping our database guys happy ;o)
(Though we also have other options if we are not following rails conventions)
NOTE: I have tried updatable views only with Sybase and would love to know about your experience with other databases.
Sign up and be a part of SPhred.com and don’t forget to invite your friends ;o)
Hi Nasir,
thanks for sharing this. I am actually thinking about using views for a different reason: Different users in my app can see different sections of data, even in just the one table. So I thought that creating different views of that table will act a security mechanism for me, hiding the data that I can’t show to certain users.
Regarding the updatable views, although I haven’t tested it yet, I remember from my DB lectures in college that a view can be updated as long as you have all the primary keys of all the tables involved in the view in the view itself. This would also apply to all the “mandatory” fields in the tables involved.
cheers,
José
Hi Jose,
Absolutely right, that’s one of the main reasons behind using database views and thanks for pointing it out.
my friend, you are a God. I’ve been looking at how to use views and stored procedures in Rails for quite sometime and you’re right, there is no documentation out there.
My point in using views is to mash together databases. Using linked servers in SQL Server you can call two different database in a view and combine the results with a union all.
Thanks once again.
No luck for me on SQLLite –
>> OtherPerson.find(:first).save
ActiveRecord::StatementInvalid: SQLite3::SQLException: cannot modify other_people because it is a view
Looking around it appears that it may be possible with a trigger on the view, but that’s too much maintenance for my current needs.
Oops, misspelled SQLite as SQLLite
Never tried it with SQLite so can’t comment on that …..
Hi Nasir,
been doing the same thing on MySQL 5.0 and so far I’ve had no problem either. And yes, views are updatable too on MySQL.
cheers!
Jean-Marc
Hi Nasir,
I am having problems accessing existing database views. These views are based on tables being modified by a different application. eg of a view name is ‘__CC___LONDON_STAFF’ .
I know that these views don’t adhere to the normal rails convention but these wern’t created by me. i can map the models to similar named tables using the set_table_name method, but this doesn’t seem to work with the views.
any suggestions would be a great help. I am using Ms-sql server 2000 as the database, and am using rail 2..
many thanks,
abhi
Abhi
I think the first step would be to create a simple view yourself and check whether it is working or not?
For instance, for table Article or article create a view with a different name called articles and corresponding model Article.
See what happens then?
Hi Nasir,
I tried creating two different test views using sql enterprise manager. I based one of the views on a single table and the other one on two related tables.. I tried creating a scaffold for each view at a time and then tried migrating to the current version.
it kept failing saying that a table with that name already exisits. For some reason it was creating a new table with an id and timestamp info.
i had a look at the schema.rb
I had a look at the schema.rb file.
it is listing all the tables but not listing any of the views?
i am not sure it is accessing any of the views from the database.
I am not sure why do you need to run the migration for view if your table is already in the database and you are only referring to the view.
Can I suggest that once your view and model is ready then simply fire up the console and check whether you can do CRUD operations on your view/model?
If that works then that means it is fine.
I would also advise to remove any migration file created when you create a model based on the view rather than the table because there is no point in running migration for views or there is?
hi got it working thanks
Me trying to migrate to the current version was causing the problem. I removed the migration file that was created and it seems to work like a charm. Thanks for your help.. much appreciated.
abhi
hi Nasir,
I am back with a small problem. The view I was trying at access has a field called Job-Title’. Notice that its a hyphen and not an underscore here.
In the index.html.erb file I can manage to display all the other fields using the for loop apart from this one.
Think ruby associates hyphen as minus operator. When I tried displaying t.Job_Title and reloading the page it came up with undefined method ”Job’
Is there a way to display this column values? If I render it using xml I can see the Job-Title being displayed.
I know i can create an additional column in the view with a suitable jobtitle name but was interested to know if there was some way to deal with such situations.
thanks,
abhi
Yes there is a way for this, instead of t.Job-Title, use t['Job-Title']
thanks bud,
your a star…
cheers,
abhi
Hey Nasir,
I never fancied using views in Rails. But I have a situation where I could try it and I am going to do that now after reading this post.
Thanks for the post
–
Manik
Blog
Company
The problem that I have encountered with using views is that Rails has no language independent way of creating them, so it ignores the fact that it is a view and not a table when it generates the schema.rb file upon migration.
I use raw SQL (MySQL) to create the view in the migration, but the schema.rb file gets generated with code (create_table) that creates a table, not a view.
This screws up my tests because when the test database is generated from the schema.rb file, a table is created instead of a view, and as a result of this no data is accessible through that table.
I agree with you that views would be a really nice thing to be able to use in certain situations, but unless I can figure out a way to use them without screwing up the testing process I don’t think I will be able to use them.