"Marco Lauria" <mslauria@...> wrote in message
news:<5.0.2.1.2.20011125140828.03fb3eb0@...>...

> Hello All,
> I noticed a problem upgrading to Firebird RC1.
> I use live views ( views with triggers )
> in almost 70% of my FOrms....
> I upgraded to latest Firebird release
> and now every time I insert a record in the views (not in all)
> after every insert I get a rowsaffected of 0
> so that the IBObjects library thinks that we have not inserted any record
> and it throws an error
> while the record is instead inserted correctly.

This post gives me enough room to rant against any technology I can
remember, so where do I start?

First, observe this script:

SET SQL DIALECT 3;

CREATE DATABASE 'h:/proy/v_on_v.gdb';

CREATE TABLE T (A INTEGER);
CREATE TABLE U (Z INTEGER);

CREATE VIEW V (B) AS
select a from t;

Since V is based on T in the simplest possible way, V is updatable.

CREATE VIEW W (C) AS
select b from v;

Again, W is updatable, using the rules given in LangRef.

SQL> set count;

SQL> insert into w values(0);
Records affected: 1

Neat. I inserted one row and that number was returned by the API.
Unfortunately, as Jason knows well, if I insert into V, IB (not FB) says in
this case that you inserted three records. If you delete from V, IB will say
that you deleted three records and on update, you will appear as updating
three records. Now, do the same on W as above and you might get higher
values. The reason is that IB has flawed logic, counting every loop over the
routine that handles those operations, when indeed the low-level routines
EXT_* and VIO_* that modify data are called only when you have an external
file or a physical table... everybody knows in this list that views don't
have real storage. The code is called several times until it reaches a real
table. There may be several layers of views. IB will count every call,
blindly.

SET TERM ^ ;
CREATE TRIGGER WT FOR W
ACTIVE BEFORE INSERT POSITION 0
as begin insert into u values(new.c); end ^
SET TERM ; ^

SQL> set count;
SQL> insert into w values(-1);
Records affected: 1

I wrote a trigger that does the insertion. But we still get one row, since
really there was one operation.

SQL> select * from w;

C
============
0 => this is the previous
-1 => this is the current

SQL> ^Z

Now, we have a case with updatable views and triggers and you get one
operation. The key is "updatable". To be simple at the cost of being
inaccurate, each command you do is a request and every trigger on the
affected table/view is a sub-request that's stored on an array. When I fixed
the buggy behavior in FB, it was because IBO users and people that checked
the API directly were having problems with the "3 records affected" answer
from the engine. And I considered it a bug... not that I'm going to bend the
engine to please a client package. However, I logged with the bug
description the comment that triggers' operations aren't counted. The
updates, deletions and insertions of the triggers aren't added to your
request, they are simply lost. I could get them and return them as a whole
by means of another item in the isc_database_info call, but certainly not in
the current item used, because I would spoil the current behavior.

Observe that in the prior case, the behavior is already flawed: I wrote a
trigger for W. It should have OVERRIDDEN the normal behavior, not added to
the normal behavior. The value -1 shouldn't never be written to table t
because I have a trigger in the view. I told the trigger to write to the
physical table U instead, so the only behavior should have been only this:

SQL> select * from u;

Z
============
-1

Jim Starkey wrote that the original design was that when a trigger was
defined for a view, the trigger cancelled immediately the default behavior.
This makes sense. For tables, a trigger cannot change the semantics: it
can -at most- add to the semantics or stop the operation. A "before insert"
trigger can only stop the insertion, change the values that are going to be
stored or write to other tables. It can't turn the insertion into a
deletion, for example. But the beauty and power of triggers on views is that
they CAN change the semantics: a trigger in a view can turn a deletion on
such view into a log written in a table that has nothing to do with the base
tables of the view. However, with updatable views, today the engine still
does the original operation despite having a trigger... Jim planned one
behavior that was sensible, but you know, the company that has owned IB for
the latest 10 years did something "strange" (for not writing something
totally unpolite in this forum). Furthermore, some special views coupled
with triggers that I used to use on my lovely IB4.2 no longer work in IB6
(you can't insert data in them in any way!), so it seems that some
paw-coding was made after 1994. Since this behavior is flawed, I'm
determined to change it when I discover the right place in the code, so in
the future putting a B_I trigger on an updatable view will give you ZERO
direct insertions, unless I implement an item to get the sum of all
triggers' I/O operations that are defined for that view (should I count
AFTER triggers, too?).

Now, why you get zero today: I suspect you are using complex views that
aren't updatable, so to make them updatable, you needed to write triggers.
In this case, the operation cannot be done directly, so the trigger is the
only one doing real I/O operations. Since trigger operations aren't counted,
you get zero as returned by the API.

CREATE TABLE NU (Z INTEGER);

CREATE VIEW VNU (X, Y) AS
select p.a, q.a from t p join t q on p.a = q.a;

This view has a JOIN, hence is not updatable unless you write triggers.

SET TERM ^ ;
CREATE TRIGGER VNUT FOR VNU
ACTIVE BEFORE INSERT POSITION 0
as begin insert into nu values((new.x+new.y)*100); end ^
SET TERM ; ^

SQL> insert into vnu values(3,9);
Records affected: 0 => here's the catch, the trigger did the work.

SQL> select * from nu;

Z
============
1200 => here's the trigger writing 100(new.x+new.y)

In this case, since the view is not updatable, "t" (the base table) has
still the values 0 and -1 only. This should be the case even with updatable
views when one puts a trigger. The other solution was offered by Diane
Brown: take a non-standard syntax (borrowed from Oracle) and tag views
explicitly as non-updatable when defining them. I still think that we are
fine with Jim's original purpose: putting a trigger cancels the default
operation on the view.

Now, counting the trigger's real I/O operations may be a new API call item
to get further information (it's not very hard to do that in the engine),
but IT'S NOT THE SOLUTION for your problem. First, view's triggers are the
only triggers that can change the semantics of operations: they can convert
an insert in a view into an update on a table and such table doesn't have to
be the base table of the view: if you do an insert and are expecting to get
"one record was inserted" from the API, it may not be the case if the
trigger did three updates or deletions in different tables. Second, if you
have a hierarchy of views (like the above example, where W is based on V),
your trigger may update a real table or produce an update on another view,
generating indirectly the activation of other triggers and so on. Third,
since triggers are procedural, you can write conditional code that may or
may not update tables or views or may update 10 tables for each simple
operation on a view you do from your program. Worse, as you may discover,
having an updatable or non-updatable view can change the landscape of what's
reported. As if you don't have enough, you should know that cascade FKs are
implemented through triggers, so what does one do in such case? Including
those special triggers in the count of real records touched or ignoring
them?

Since Borland still doesn't fix IB behavior, IBO has trouble with the
"single record" check on IB with views. As why IBX doesn't have problems,
it's simply because IBX doesn't check such condition, I guess. You drive a
big truck, press the accelerator and don't pay attention to any semaphore in
a crowded city. If smaller vehicles or people don't go out of your path, you
crush them. This may the sensation when you use IBX with some operations...
it doesn't care about a couple of things. (Keep it simple, but not so
simple??? That's each developer's decision among the different VCL-based
IB/FB connectivity packages that are offered.)

Now, in the other side, IBO's check is well intentioned but works only in
tables and simple cases with views. For the general case, this check should
be taken away or a property should be offered to disable the check. A
compromise solution for most cases would be to offer a property that sets
the number that you expect to be returned from the API. By default, this
would be ONE for the same operation that you are performing (if you do an
INSERT, you expect one insertion) as it works in tables, but for views you
would have to customize it. However, for complex cases with conditional
operations, you would need to disable the check altogether. Simply put,
there's no way the engine can guess what the client app wants as the number
of returned operations and there's no way IBO can know in practice what the
engine will do: IBO would have to parse every trigger in your view hierarchy
(what if your triggers call procedures?) and estimate the number of real I/O
operations and even so it would fail if your trigger's code does
modifications with conditional code (is the client-side package going to
emulate JRD to know in advance what will be done and returned?). As a
physicist said (Eisenberg?), you either enhance the precision in the
estimation of a particle's position or its velocity, but not both at the
same time. The cost to make IBO guess the most complex cases is unreasonably
high and after such effort, you still don't have 100% confidence that the
estimation will match the engine's returned values.

In a few words: fix IBO. The current check is fine with tables. The check
doesn't and won't work with views in more than the simplest case, since
their triggers can do anything unrelated to the original operation. Only the
developer knows what he/she did with his/her views. I only stopped the
server from counting fake operations that weren't happening as real I/O
operations over records (and so, avoid "inflating" the number of records
inserted, deleted or modified that are returned from the API). Furthermore,
remember that IBO doesn't detect mandatory fields in views: you should set
the REQUIRED flag yourself. It's nearly impossible (almost senseless) to ask
a client-side package for the detection of mandatory fields in views
automatically, BTW. Here's the original bug fix for "Rows affected incorrect
with trigger":

http://sourceforge.net/tracker/index.php?func=detail&aid=436462&group_id=902
8&atid=109028
You should know that triggers never have been counted among the I/O
operations returned. I didn't change that. I only put a check to ensure the
counters are updated only when EXT_store or VIO_store are called for
insertions (and the equivalent for deletions and updates), for external
tables or records in the db, respectively.

Unless you integrate IBO and FB in a monolithic construction and so offer a
direct competition of Access (db + GUI) or encapsulate the whole engine into
IBO classes and make IBO a competitor for DataSnap (MIDAS) and
client-datasets, the problem of detecting exactly how many operations
happened on a view is a superflous point. That number can't be used to tell
whether a client-side operation was successful or not with a view. I know
IBO does that to guarantee that by mistake people do not step into more than
one row and mangle data. For example, missing the PK to identify a record,
so you delete more than one row because you aren't using a combination of
columns that make the field uniquely identifiable (and this happens with
views because they don't have a real PK if you don't select meaningful key
columns). But again, the check's only valid with tables, not views. Hope
Jason will see the point here. It's impossible to please everybody.

And about automatically updatable views as they work currently in the
engine, I could say a couple of technical things, but rests to say to I
consider them the equivalent of having an unexpected meeeting with the
Gestapo at 3 AM while trying to escape from the KGB and eluding the CIA that
wants you, too but taking care that the maffia doesn't get wind of you
before the secret policies. A real nightmare.

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://www.cvalde.com - http://www.firebirdSQL.org