Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: Database event question


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Apr 3, 2018 11:54 AM Last Post By: Jeffrey Eib, Sr. Threads: [ Previous | Next ]
Jeffrey Eib, Sr.

Posts: 5
Registered: 4/25/05
Database event question  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 27, 2018 8:36 AM
We am using Firebird ver. 2.5 as our DB server, but I think that this question would apply to any DBMS that supports events. My applications are being written in Delphi XE8.

As an example for my purpose, we keep out customer's data in a master/detail setup, master table CUSTOMER_INFO, detail tables ADDRESSES, CONTACTS, and PHONES. When a user is viewing a customer's information in an application, and another user modifies some data for that customer, we want to use Firebird POST_EVENT to notify the original user that the data being displayed is no longer current and to refresh the on-screen view. My problem is that any or all of the tables may be modified, and I don't want multiple notifications popping up on the user's screen for each table being changed.

Is there a way to either, 1) prevent multiple events being broadcast for what is essentially one change, or 2) detect from within an application when multiple events are bring broadcast from the same transaction? I would prefer a way to confine the number of events to just one for any change in the master/detail table structure to minimize network traffic.

Thanks in advance for any help.

Jeffrey
Peter Below

Posts: 1,227
Registered: 12/16/99
Re: Database event question  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 27, 2018 10:48 AM   in response to: Jeffrey Eib, Sr. in response to: Jeffrey Eib, Sr.
Jeffrey Eib, Sr. wrote:

We am using Firebird ver. 2.5 as our DB server, but I think that this
question would apply to any DBMS that supports events. My
applications are being written in Delphi XE8.

As an example for my purpose, we keep out customer's data in a
master/detail setup, master table CUSTOMER_INFO, detail tables
ADDRESSES, CONTACTS, and PHONES. When a user is viewing a customer's
information in an application, and another user modifies some data
for that customer, we want to use Firebird POST_EVENT to notify the
original user that the data being displayed is no longer current and
to refresh the on-screen view. My problem is that any or all of the
tables may be modified, and I don't want mu ltiple notifications
popping up on the user's screen for each table being changed.

Is there a way to either, 1) prevent multiple events being broadcast
for what is essentially one change, or 2) detect from within an
application when multiple events are bring broadcast from the same
transaction? I would prefer a way to confine the number of events to
just one for any change in the master/detail table structure to
minimize network traffic.

My only experience with this kind of setup is with Oracle, so what I
write below may not be applicable to your case.

Oracle has a package DBMS_ALERT that can be used to sent notifications
to attached sessions (in fact they have to actively look for them, but
that is an implementation detail). If several alerts for the same topic
are posted inside a transaction only the last of them will be delivered
when the transaction commits, all previous ones are lost.

My database has a table into which the triggers for update, delete, and
insert for the individual tables will deposit a record containing the
table name, the operation, the affected record's primary key (I use
base-64 encoded GUIDs, but any other type of key will do, as long as
all tables use the same data type here), and the transaction number.
Then the trigger posts the alert, sending the transaction number as
part of the alert message.

A client attached in another session can then use the transaction
number from the alert to find the records in the transaction table that
describe the changes done in the transaction, use the primary key to
check if the modified record was one it currently has on display, and
reload it, if necessary.

By the way, in my scenario the primary keys are used as object
identifiers in the data model, and they are unique across all tables
of the database, not only inside one table. That makes the client-side
check a bit easier to handle, since the name of the table that was
modified can be ignored at first.

--
Peter Below
TeamB
Jeffrey Eib, Sr.

Posts: 5
Registered: 4/25/05
Re: Database event question  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 27, 2018 11:25 AM   in response to: Peter Below in response to: Peter Below
Peter Below wrote:

My only experience with this kind of setup is with Oracle, so what I
write below may not be applicable to your case.

Oracle has a package DBMS_ALERT that can be used to sent notifications
to attached sessions (in fact they have to actively look for them, but
that is an implementation detail). If several alerts for the same topic
are posted inside a transaction only the last of them will be delivered
when the transaction commits, all previous ones are lost.

My database has a table into which the triggers for update, delete, and
insert for the individual tables will deposit a record containing the
table name, the operation, the affected record's primary key (I use
base-64 encoded GUIDs, but any other type of key will do, as long as
all tables use the same data type here), and the transaction number.
Then the trigger posts the alert, sending the transaction number as
part of the alert message.

A client attached in another session can then use the transaction
number from the alert to find the records in the transaction table that
describe the changes done in the transaction, use the primary key to
check if the modified record was one it currently has on display, and
reload it, if necessary.

By the way, in my scenario the primary keys are used as object
identifiers in the data model, and they are unique across all tables
of the database, not only inside one table. That makes the client-side
check a bit easier to handle, since the name of the table that was
modified can be ignored at first.

--
Peter Below
TeamB

Peter,

Thank you for your answer. The use of a table to keep track of alerts is intriguing, and I will have to think on it further.

Unfortunately, in Firebird, events are broadcast with only the name of the event, as defined in the initiation of the alert. No other information can be included with the alert. For example, if a customer is inserted, an alert can be broadcast in the after_insert trigger like this:

post_event 'CUSTOMERADDED';

Anyone who is interested in customer inserts would register to watch for that specific event. The only other piece of information that is sent with the alert is a count figure reflecting how many times the alert has been broadcast. Unfortunately, I have not been able to find out when the count figure recycles back to zero, so I am not ready to rely on that just yet.

Fortunately, it is possible to make an alert a little more specific when updating or deleting a record. I can broadcast an alert that a specific customer (or order or invoice) has been updated by concatenating the primary key of the record being updated to the alert name:

post_event 'CUSTOMERCHANGED-986456012';

In the application, if a user is viewing a specific customer, the application can easily register to watch for changes to only the customer being viewed by looking for the alert specific to the customer.

I guess I could generate a separate event for each table:

post_event 'ADDRESSCHANGED-986456012';
post_event 'CONTACTCHANGED-986456012';
post_event 'PHONECHANGED-986456012';

and have my application register for all of them. Then the application can check to see if the particular customer number is already flagged as being changed and react accordingly. This would not be a problem for a small number of master/detail tables such as the ones I have been using here. However, if the number of detail tables increases to dozens or more, this can become particularly complex and difficult to maintain. It would also create a lot of network traffic that I am trying to avoid.

Again, thank you for your answer.

Jeffrey

Edited by: Jeffrey Eib, Sr. on Mar 27, 2018 11:26 AM
Jeffrey Eib, Sr.

Posts: 5
Registered: 4/25/05
Re: Database event question  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 3, 2018 11:50 AM   in response to: Jeffrey Eib, Sr. in response to: Jeffrey Eib, Sr.
I have come up with a partial solution to my problem, but it leaves me with another one.

First for my solution. I added a column to my Customer_info table: ChangeFlag integer.

When no changes are being done to the customer's data, in neither the master nor the detail tables, I assign a value of 1 to this field. When a change is made to the master table, the specifications states that the value of this field should be set to null to indicate that the data for the customer is being changed. The after_update trigger of the detail tables updates the master table (Customer_info) by setting the ChangeFlag to null.

update customer_info set changeflag = null where (customer_num = new.customer_num);

A before_update trigger of the master table, upon receiving a value of null for the ChangeFlag field, changes that value to -1 if and only if the old value of the field is 1.

if ((old.changeflag = 1) and (new.changeflag is null)) then
new.changeflag = -1;

The after_update trigger of the master table then compares the old and new values of the ChangeFlag field. If the old value is 1, and the new value is -1, an alert is broadcast.

if ((old.changeflag = 1) and (new.changeflag = -1)) then
begin
post_event 'CUSTCHANGE-' || trim(new.customer_num);
end

The result is that a customer change event will occur only the first time that the ChangeFlag is set to null. This will prevent multiple alerts being broadcast for the entire master/detail family.

Now for the new question. I am trying to figure out a way to have the ChangeFlag value automatically reset to 1 without having to include in the specification that a query to set the ChangeFlag value to 1 will have to be run after any and all changes to the Customer master/detail family.

Opinions? Ideas? Am I heading in the wrong direction with my solution? Does anyone have a better way of accomplishing this?

Thanks in advance for any help.

Jeffrey Eib

Jeffrey Eib, Sr. wrote:

Peter,

Thank you for your answer. The use of a table to keep track of alerts is intriguing, and I will have to think on it further.

Unfortunately, in Firebird, events are broadcast with only the name of the event, as defined in the initiation of the alert. No other information can be included with the alert. For example, if a customer is inserted, an alert can be broadcast in the after_insert trigger like this:

post_event 'CUSTOMERADDED';

Anyone who is interested in customer inserts would register to watch for that specific event. The only other piece of information that is sent with the alert is a count figure reflecting how many times the alert has been broadcast. Unfortunately, I have not been able to find out when the count figure recycles back to zero, so I am not ready to rely on that just yet.

Fortunately, it is possible to make an alert a little more specific when updating or deleting a record. I can broadcast an alert that a specific customer (or order or invoice) has been updated by concatenating the primary key of the record being updated to the alert name:

post_event 'CUSTOMERCHANGED-986456012';

In the application, if a user is viewing a specific customer, the application can easily register to watch for changes to only the customer being viewed by looking for the alert specific to the customer.

I guess I could generate a separate event for each table:

post_event 'ADDRESSCHANGED-986456012';
post_event 'CONTACTCHANGED-986456012';
post_event 'PHONECHANGED-986456012';

and have my application register for all of them. Then the application can check to see if the particular customer number is already flagged as being changed and react accordingly. This would not be a problem for a small number of master/detail tables such as the ones I have been using here. However, if the number of detail tables increases to dozens or more, this can become particularly complex and difficult to maintain. It would also create a lot of network traffic that I am trying to avoid.

Again, thank you for your answer.

Jeffrey

Edited by: Jeffrey Eib, Sr. on Mar 27, 2018 11:26 AM

Edited by: Jeffrey Eib, Sr. on Apr 3, 2018 11:54 AM
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02