Sharing rows among several subscriptions

Data Replication with SQL Remote
PART 2. Replication Design for SQL Remote
CHAPTER 8. SQL Remote Design for Adaptive Server Enterprise

Sharing rows among several subscriptions

There are cases where a row may need to be included in several subscriptions. For example, if instead of the many-to-one relationship between customers and sales representatives that we had above, we may have a many-to-many relationship.

The Policy example

The Policy database illustrates why and how to partition tables when there is a many-to-many relationship in the database.

Example database

Here is a simple database that illustrates the problem.

The Policy table has a row for each of a set of policies. Each policy is drawn up for a customer by a particular sales representative. There is a many-to-many relationship between customers and sales representatives, and there may be several policies drawn up between a particular rep/customer pair.

Any row in the Customer table may need to be shared with none, one, or several sales representatives.

Solving the problem

To support this case, you need to write triggers to build a comma-delimited list of values to store in a redundant subscription-list column of the Customer table, and include this column as the subscription column when adding the Customer table to the publication. The row is shared with any subscription for which the subscription value matches any of the values in the subscription-list column.

The database, with the subscription-list column included, is as follows:

Adaptive Server Enterprise VARCHAR columns are limited to 255 characters, and this limits the number of values that can be stored in the comma-delimited list.

Table definitions

The table definitions are as follows:

CREATE TABLE SalesRep (
   rep_key CHAR( 12 ) NOT NULL,
   name CHAR( 40 ) NOT NULL,
   PRIMARY KEY ( rep_key )
)
go
CREATE TABLE Customer (
   cust_key CHAR( 12 ) NOT NULL,
   name CHAR( 40 ) NOT NULL,
   subscription_list VARCHAR( 255 ) NULL,
   PRIMARY KEY ( cust_key )
)
go
CREATE TABLE Policy (
   policy_key   INTEGER NOT NULL,
   cust_key CHAR( 12 ) NOT NULL,
   rep_key CHAR( 12 ) NOT NULL,
   FOREIGN KEY   ( cust_key )
   REFERENCES Customer (cust_key ),
   FOREIGN KEY (rep_key )
   REFERENCES SalesRep ( rep_key ),
   PRIMARY KEY   (policy_key)
)

Notes:

The subscription_list column in the Customer table allows NULLs so that customers can be added who do not have any sales representatives in the subscription_list column.

The publication

The publication for this database can be created by the following set of statements:

//Mark the tables for replication
exec sp_add_remote_table 'SalesRep'
exec sp_add_remote_table 'Policy'
exec sp_add_remote_table 'Customer'
go

// Create an empty publication
exec sp_create_publication 'SalesRepData'

//Add the Sales Rep table to the publication
exec sp_add_article 'SalesRepData', 'SalesRep'

//Add the Policy table to the publication
exec sp_add_article 'SalesRepData', 'Policy', NULL, 'rep_key'

// Add the Customer table to the publication.
// Subscribe by the subscription_list column
// Exclude the subscription_list column
exec sp_add_article 'SalesRepData', 'Customer', NULL, 'subscription_list'
exec sp_add_article_col 'SalesRepData', 'Customer', 'cust_key'
exec sp_add_article_col 'SalesRepData', 'Customer', 'name'
go

The subscriptions

Subscriptions to this publication take the following form:

exec sp_subscription 'create', 'SalesRepData', 'userID', 'rep_key'
go

where userID identifies the subscriber, and rep_key is the subscription column, which is the value of the rep_key column in the SalesRep table.

Maintaining the subscription-list column

You need to write a procedure and a set of triggers to maintain the subscription-list column added to the Customer table. This section describes these objects.

Stored procedure

The following procedure is used to build the subscription-list column, and is called from the triggers that maintain the subscription_list column.

CREATE PROCEDURE SubscribeCustomer @cust_key CHAR(12) 
AS
BEGIN
   --  Rep returns the list of reps for customer @cust_key 
   DECLARE Rep CURSOR FOR
      SELECT DISTINCT RTRIM( rep_key )
      FROM Policy
      WHERE cust_key = @cust_key
   DECLARE @rep_key    CHAR(12)
   DECLARE @subscription_list   VARCHAR(255)
   
   -- build comma-separated list of rep_key 
   -- values for this Customer
   OPEN Rep
   FETCH Rep INTO @rep_key
   IF @@sqlstatus = 0 BEGIN
      SELECT @subscription_list = @rep_key
      WHILE 1=1 BEGIN
         FETCH Rep INTO @rep_key
         IF @@sqlstatus != 0 BREAK
         SELECT @subscription_list = 
            @subscription_list + ',' + @rep_key
      END
   END
   ELSE BEGIN
      SELECT @subscription_list = ''
   END
   
   -- update the subscription_list in the 
   -- Customer table
   UPDATE Customer
   SET subscription_list = @subscription_list
   WHERE cust_key = @cust_key
END

Notes:

The procedure takes a Customer key as input argument.
Rep is a cursor for a query that lists each of the Sales Representatives with which the customer has a contract.
The WHILE loop builds a VARCHAR(255) variable holding the comma-separated list of Sales Representatives.
The UPDATE subscription_list column of the Customer

Triggers

The following trigger updates the subscription_list column of the Customer table when a row is inserted into the Policy table.

CREATE TRIGGER InsPolicy
ON Policy
FOR INSERT
AS
BEGIN
   -- Cust returns those customers inserted
   DECLARE Cust CURSOR FOR
      SELECT DISTINCT cust_key
      FROM inserted
   DECLARE @cust_key CHAR(12)
   
   OPEN Cust
   -- Update the rep list for each Customer 
   -- with a new rep
   WHILE 1=1 BEGIN
      FETCH Cust INTO @cust_key
      IF @@sqlstatus != 0 BREAK
      EXEC SubscribeCustomer @cust_key
   END
END

The following trigger updates the subscription_list column of the Customer table when a row is deleted from the Policy table.

CREATE TRIGGER DelPolicy
ON Policy
FOR DELETE
AS
BEGIN
   -- Cust returns those customers deleted
   DECLARE Cust CURSOR FOR
      SELECT DISTINCT cust_key
      FROM deleted
   DECLARE @cust_key CHAR(12)
   
   OPEN Cust
   -- Update the rep list for each Customer 
   -- losing a rep
   WHILE 1=1 BEGIN
      FETCH Cust INTO @cust_key
      IF @@sqlstatus != 0 BREAK
      EXEC SubscribeCustomer @cust_key 
   END
END

Excluding the subscription-list column from the publication

The subscription-list column should be excluded from the publication, as inclusion of the column leads to excessive updates being replicated.

For example, consider what happens if there are many policies per customer. If a new Sales Representative is assigned to a customer, a trigger fires to update the subscription-list column in the Customer table. If the subscription-list column is part of the publication, then one update for each policy will be replicated to all sales reps that are assigned to this customer.

Triggers at the consolidated database only

The values in the subscription-list column are maintained by triggers. These triggers fire at the consolidated database when the triggering inserts or updates are applied by the Message Agent. The triggers must be excluded from the remote databases, as they maintain a column that does not exist.

You can use the sp_user_extraction_hook procedure to exclude only certain triggers from a remote database on extraction. The procedure is called as the final part of an extraction. By default, it is empty.

To customize the extraction procedure to omit certain triggers:

Ensure the quoted_identifier option is set to ON:
```
set quoted_identifier on
go
```
Any temporary tables referenced in the procedure must exist, or the CREATE PROCEDURE statement will fail. The temporary tables referenced in the following procedure are available in the ssremote.sql script. Copy any required table definitions from the script and execute the CREATE TABLE statements, so they exist on the current connection, before creating the procedure.

Create the following procedure:

CREATE PROCEDURE sp_user_extraction_hook 
AS
BEGIN
  -- We do not want to extract the INSERT and 
  -- DELETE triggers created on the Policy table 
  -- that maintain the subscription_list
  -- column, since we do not include that 
  -- column in the publication.
  -- If these objects were extracted the 
  -- INSERTs would fail on the remote database
  -- since they reference a column 
  -- ( subscription_list ) that does not exist.
  DELETE FROM #systrigger
  WHERE table_id = object_id( 'Policy' )
  -- Do not create any procedures 
  DELETE FROM #sysprocedure
  WHERE proc_name = 'SubscribeCustomer'
END
go

Using the Subscribe_by_remote option with many-to-many relationships

When the Subscribe_by_remote option is ON, operations that arrive from remote databases on rows with a subscribe by value of NULL or '' will assume the remote user is subscribed to the row. By default, the Subscribe_by_remote option is set to ON. In most cases, this setting is the desired setting.

The Subscribe_by_remote option solves a problem that otherwise would arise with publications including the Policy example. This section describes how the option automatically avoids the problem.

The database uses a subscription-list column for the Customer table, because each Customer may belong to several Sales Reps:

Marc Dill is a Sales Rep who has just arranged a policy with a new customer. He inserts a new Customer row and also inserts a row in the Policy table to assign the new Customer to himself. Assuming that the subscription-list column is not included in the publication, the operation at Marc's remote database is as follows:

As the INSERT of the Customer row is carried out by the Message Agent at the consolidated database, Adaptive Server Enterprise records the subscription value in the transaction log, at the time of the INSERT.

Later, when the Message Agent scans the log, it builds a list of subscribers to the new row, using the subscription value stored in the log, and Marc Dill is not on that list. If Subscribe_by_remote were set to OFF, the result would be that the new Customer is sent back to Marc Dill as a DELETE operation.

As long as Subscribe_by_remote is set to ON, the Message Agent assumes that, as the subscription-list column is NULL, the row belongs to the Sales Rep that inserted it. As a result, the INSERT is not replicated back to Marc Dill, and the replication system is intact.

You can use a trigger, which executes after the INSERT, to maintain the subscription-list column.