Creating Subscriptions to Change Views

Go Up to Change Views

To establish interest in observing changed data on a set of tables beyond the natural boundary of a database connection, a subscription must be created on a list of tables (base tables or views):

Syntax for CREATE SUBSCRIPTION

 CREATE SUBSCRIPTION <subscription_name> ON< 
      <table_name>[(column_name_comma_list)][FOR ROW (CHANGE | {INSERT, UPDATE, DELETE})]
      [, <table_name>[(column_name_comma_list)][FOR ROW (CHANGE | {INSERT, UPDATE, DELETE})] ...] 
      [DESCRIPTION user-description];

• The FOR ROW clause describes what types of row modifications will cause column-level changes to be tracked for the subscription.
• If the FOR ROW clause is omitted, then the behavior defaults to FOR ROW (CHANGE). The CHANGE option tracks INSERT and UPDATE data changing operations and returns a row as soon as any tracked columns changes value.
• When INSERT, UPDATE, and DELETE are specified, then change status on every tracked column is determined. This is called "deep record introspection" and the CHANGE option is called "shallow record introspection". It is expected that Change Views performance is faster with the CHANGE option, but that the INSERT, UPDATE, and DELETE combinations are more complete. DELETE is not tracked by default, unless explicitly specified.

• Deep record introspection is required when it is necessary to account for every tracked column and to know exactly which operation(s) caused a column's value change.
• Shallow record introspection is for the use case where it is only necessary to know that one or more columns changed and not how every tracked column value changed or if they even changed value at all; it indicates that the row changed somehow with respect to the subscription.

• If a table alone is specified then all columns of the table are tracked.
• If only a subset of columns is desired to be tracked, then an optional list of columns can be specified by the subscription. For example,

Sample for CREATE SUBSCRIPTION

CREATE SUBSCRIPTION sub_employee_changes ON EMPLOYEE (EMP_NO, DEPT_NO, SALARY) DESCRIPTION  'Subscribe to changes in EMPLOYEE table';
 
CREATE SUBSCRIPTION sub_customer_deletes ON CUSTOMER FOR ROW (DELETE) DESCRIPTION 'Subscribe to deletes in CUSTOMER table';
 
CREATE SUBSCRIPTION sub_various_changes
 ON EMPLOYEE FOR ROW (INSERT, UPDATE, DELETE),
   CUSTOMER FOR ROW (INSERT, UPDATE, DELETE),
   SALES FOR ROW (UPDATE),
   DEPARTMENT (LOCATION) FOR ROW (UPDATE)
 DESCRIPTION 'Subscribe to various changes on multiple tables';

An optional list of columns is specified for the EMPLOYEE table so that only changes on those columns are tracked. Since no FOR ROW clause is specified for EMPLOYEE table, the default for FOR ROW is the CHANGE option, which causes all insert, update, and delete changes are tracked by the subscription and that any change will return the row. The CUSTOMER table clause specifies that only row deletions are tracked.

DROP SUBSCRIPTION

Note: This new function was introduced with InterBase XE7 Update 1.

To eliminate interest in observing a set of change views, a subscription must be dropped.

• If RESTRICT is specified, then a check of existing subscribers is performed. If there are subscribers, then an error is returned without dropping the subscription.
• If CASCADE is specified, then all subscribers of this subscription are also dropped.
• If neither RESTRICT nor CASCADE is specified, then RESTRICT is assumed.

Syntax for DROP SUBSCRIPTION

DROP SUBSCRIPTION <subscription_name> [RESTRICT | CASCADE];

Grant Subscribe

A user is then granted SUBSCRIBE privilege to subscribe to the subscription in order to track changes on the listed tables:

Syntax for GRANT SUBSCRIBE

GRANT SUBSCRIBE ON SUBSCRIPTION <subscription_name> TO <user_name>; REVOKE SUBSCRIBE ON SUBSCRIPTION <subscription_name> FROM <user_name>;

Set Subscription

To set a subscription as active, an application issues a SET SUBSCRIPTION statement. The SET SUBSCRIPTION statement allows multiple subscriptions to be activated and includes an AT clause to denote a destination or device name as a recipient of subscribed changes. The subscriber user name is implied by the user identity of the database connection.

The notion of multiple subscriptions against the same schema object for a user, via the AT clause, is motivated by two observations:

• First, each subscription for a user might connote a separate device among many that have a disconnected interest in a change set that is queried independently at different times for different purposes.
• Second, some multiuser applications use pooled database connections under the umbrella of a single user name (e.g. CRM_User or even SYSDBA). In these cases, an alternate identifier must be provided to distinguish which subscription should be used to query a change set. That additional identifier can be loosely thought of as a destination or a "device name".

The SET SUBSCRIPTION statement activates/deactivates a subscription for a subscriber and binds/unbinds that subscription to the transaction that executed the command. All subscribed tables in a subscription behave as change views for table references in user-level queries when executed by a transaction with bound subscriptions. At transaction termination, the subscription is necessarily unbound, so it is necessary to issue a SET SUBSCRIPTION in subsequent transactions if a change view is still wanted.

At transaction commit, the subscriber transaction state is updated to allow the subscriber to see changes that occur after the commit, but not the former changes. A transaction rollback keeps the subscriber transaction state unchanged, so the change view still sees the former changes but cannot see newer changes. If a transaction does not read any subscribed tables from a bound subscription then the subscriber transaction context for that subscription is not updated.

SET SUBSCRIPTION; syntax and example

SET SUBSCRIPTION [<subscription_name> [, <subscription_name> ...]] [AT <destination>] {ACTIVE | INACTIVE}; SET SUBSCRIPTION sub_employee_changes, sub_customer_deletes AT 'smartphone_123' ACTIVE; SELECT EMP_NO, DEPT_NO, SALARY FROM EMPLOYEE; SELECT * FROM CUSTOMER; COMMIT or COMMIT RETAIN;

This example activates two subscription and returns changed data sets from the subscribed tables.

• The COMMIT updates all subscriptions for schema objects referenced during the transaction to set the last observed timestamp and transaction context.
• The COMMIT RETAIN does not change the last observed state and maintains the current snapshot as always. The subscription is unbound for the transaction at commit, which makes any subsequent queries against subscribed schema objects return normal data sets, without regard to changed data status. Any number of subscriptions can be activated simultaneously by a transaction.

Next:

• Statement Execution