Page 1 of 1

Updatable view questions

Posted: Sat Aug 22, 2009 1:18 pm
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?

Re: View questions

Posted: Sat Aug 22, 2009 2:16 pm
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)

Re: View questions

Posted: Sat Aug 22, 2009 2:32 pm
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.

Re: View questions

Posted: Sun Aug 23, 2009 2:46 am
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 ;)

Re: Updatable view questions

Posted: Mon Aug 24, 2009 5:50 pm
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.