Subject Re: [ib-support] Again on the updatable views with triggers
Author Jason Wharton

> Same as 0 may have someone wondering what happened really, -1 only serves
> the code that's prepared to handle it. I think only IBO is. Worse, code
> cares about possible inconsequences may panic when facing -1 because the
> idea of having -1 is not sensible for the developer that wasn't explained
> that this is a convention.

The only reason I was asking for -1 to be used was because I thought in the
case of views I was only going to be able to pick from 3 static values to
represented an "I don't know" condition. 0 was definitely the worst because
it would break everything and -1 wasn't really much better but at least it
properly conveyed "I don't know". 1 was actually the best of the three but
still an awful crock.

Please know that none of the above continue to have my recommendation. There
is a proper context we can return a "Yes, I do know" answer from. I'll
elaborate on it below and it isn't "blind".

> Blindly counting every pass as IB does is not the solution either, because
> it causes "multiple rows on singleton <action>" concerns.

No it doesn't. Not if you understand what I am saying correctly.
In fact, it solves it in the cases I can think of where views are concerned.

> I hope Ann will implement the virtual operation concept successfully,
> because I don't see the change straightforward, as the code lacks enough
> context information. When facing a table, one needs to know that there was
> previous iteration that handled the view and hence ignore the operation
> the counting purpose. I can only imagine a new flag in the request itself
> because the indirect recursive calls can be so complex that we could end
> the same problem than the ambiguity detection code (Ann knows what I mean)
> if we wanted to handle them with extra function parameters.

From the sounds of things rows affected is poorly implemented at best.
It sounds to me like it is plugged into the wrong area...
I suppose if I am going to argue any further on this I probably better have
you line me out to look at some code.
> I'm only worried if those cases should be considered and what they should
> return:
> insert into <view>
> select <something> from <table>

No problem here at all, just return the number of records the select walks.

> insert into <view>
> select <something> from <proc>

No problem here either, just return the number of records the select walks.

This illustrates my point perfectly. RowsAffected isn't about the number of
physical tweaks to raw records but rather the number of records walked
through in the process of a statements operations.

In short, you need to count iterations of records, not an inventory of
tweaks to records in raw tables.

> There seems to be clarity that a massive update/delete on a view should
> cause a counter that's more than one, right?

Most definitely, you get a count of however many rows the view walks through
in the process of the update.

update myview
set acol = :aval
where akey between 10 and 19

This would return 10 rows affected if there were consecutive records to meet
the criteria. Triggers may have actually done inserts or whatever, we don't
care. It is just important to know if the update actually walked through
anything or not.

Another case. Lets say a record with akey = 5 doesn't exist.

update myview
set acol = :aval
where akey = 5

Rows walked would return 0 which is the desirable thing to know. If the
record was there it would return 1.

Remember, it is rows operated upon or rows walked through, during the
operation that is being counted. This will always make a view act the same
as a table and it makes it so you don't have to keep a tricky inventory of
what the triggers are actually doing. That isn't the concern since this is
all supposed to be internal "protected" implementation details. The client
only ever needs to know if the row was there to be operated upon and how
many. Not what actually happened to physical records.

I know for certain now the right way to go is to report rows operated with.
When dealing with tables directly the count of rows walked through is going
to be the same as the actual rows affected at the physical level. When
extending into the realm of views, I think it is totally appropriate to use
the exact same system of counting rows walked through and ignore what the
triggers actually do.

Please let me know the minute this makes sense to you so I can get some rest
from this whole ordeal.

FWIW, I think it would be VERY useful to have a trigger/stored procedure
language enhancement such that you could do something like this:





Jason Wharton
CPS - Mesa AZ