Updatable view questions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Updatable view questions

Post by Darhazer »

Hi,

I have an application that is integrated with IPB.
I'm going to upgrade the IPB version to 3
The problem is that in IPB 3 members.id is renamed to members.member_id and this breaks all queries, that use the members table.

To avoid rewriting queries, I came up to this solution:

Code: Select all

rename table ibf_members to ibf_members_master;
 create view ibf_members as select *, member_id as id from ibf_members_master;
As far as I know, where there is 1:1 mapping you can insert, update and delete records, using the view as a reference. I've already tried with update statement. But I have no experience, so I have 2 questions:

* Are there any queries, that can fail, using this view
* How this will reflect to the performance of the site?
Last edited by Darhazer on Mon Aug 24, 2009 2:44 pm, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: View questions

Post by VladSun »

I think your view is not insertable, because you have two columns in the view referencing a single column in the table (member_id, id)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: View questions

Post by Darhazer »

yeah, just tried it:

Code: Select all

The target table ibf_members of the INSERT is not insertable-into
Any other ideas? Adding the fields to the table and using triggers to update corresponding fields? Or rewrite all queries?

P.S. With the non-insertable view I can alter only queries that inserts data in the table, so this is still possible solution. Question about possible update fails and performance issues remains.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: View questions

Post by VladSun »

I don't think that using VIEW is a good idea (because it's a *VIEW* ;) ).
Maybe using triggers is a good idea, but rewriting your queries is better ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Updatable view questions

Post by Darhazer »

What about performance if triggers are used?

Anyway, the topic is about updatable views and I found this in the manual:
To be more specific, a view is not updatable if it contains any of the following
...
* Multiple references to any column of a base table.
With respect to insertability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:
* There must be no duplicate view column names.
* The view must contain all columns in the base table that do not have a default value.
* The view columns must be simple column references and not derived columns. A derived column is one that is not a simple column reference but is derived from an expression.
It seems like duplicate columns (id, members_id refer to the same field) should prevent updates, but it works. Even this query works:
[sql]UPDATE ibf_members SET mgroup =4, member_group_id =3 WHERE name =  '***'[/sql]
(mgroup and member_group_id refer to one and the same column)

So my view contains all the fields in the base table. It have only simple column references. I'm still not clear why I can't insert, even when I provide all of the columns)

Actually on the question about queries that would fail:
INSERT DELAYED is not supported for views
and it's not translated into plain insert, as I would expect, but produce an error...

It was interesting research anyway and I hope this topic will be interesting for someone.
Thank you for your participation, tomorrow I'm starting to rewrite queries.
Post Reply