Procedure pr_AddOutscalingEvent

Introduction

When records of database tables on the Publishing servers are added, altered or removed, this event should be communicated to all listening Subscribers. To this purpose, the table osEventQueue is designed for this type of communication.

Instead of roughly inserting records into the table osEventQueue, the stored procedure pr_AddOutscalingEvent is provided with Smartsite iXperion. Since there are several rules considering usage of osEventQueue (e.g. exceptional tables), this stored procedure will handle these rules in order for the invoker to just state which table or view needs to be sent to the Outscaling event as well as the affected records, either single, ranged or comma separated.

Note that stored procedure pr_AddOutscalingEvent absolutely takes no single action if no Outscaling implemention is detected. Therefore, it is absolutely save to invoke this stored procedure when using correct parameters.

Parameters

Stored procedure pr_AddOutscalingEvent has two to five parameters:

• Mandatory parameters:
  • @instance - corresponds with the Smartsite iXperion's instance name which can be found in the registry: {sitename}/InstanceID
  • @tableName - which is the name of the table (or view) to be synchronized
• Optional parameters:
  • @key (default: 'ALL') specifies which rows of the table/view need to be synchronized. 'ALL' stands for entire table
  • @caller (default: NULL) specifies the name to identify the invoker. Purely to be used for auditing purposes
  • @verbose (default: 1) specifies the level of verbosity during the procedure's invocation

@instance

It is necessary to enter the Smartsite iXperion instance name in order for the cms publisher(s) to determine if the message concerns him/them. Entering a wrong instance name may result to incorrect or incomplete synchronization and may even corrupt the subscriber's database.

@tablename

This is quite a trivial parameter since a cms publisher has to know which table or view needs to be synchronized. As an exception, table Contents never needs to be Outscaled, thus can not be entered as a value for the @tableName parameter.

@key

This parameter probably is the most complex one. The easiest form is a single integer which represents the primary key of the table's row which needs to be outscaled. Note that the table must exactly have one primary key column.

Another possibility for @key is a comma-delimited list of such primary keys. Again, the targeted table must have no less or more than exactly one primary key. The stored procedure will determine this primary key by itself.

Examples for single integer or comma-delimited list of primary keys:

SQL

Copy Code

/* Single integer or comma-delimited values can only be applied to tables with exactly one primary key */ /* Perfectly suitable to outscale deleted records */ EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'SomeTable', '5,6,7,10'; EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'AnyTable', 100; -- Or invoking with more verbosity (explicitly naming variables): EXEC pr_AddOutscalingEvent   @instance = 'EMPTYSIX.CMS',   @tableName = 'FileTypes',   @key = '1,2,3';

Suppose, a range of primary keys need to be outscaled while it is certain (or unnecessary) no rows have been deleted. In this case usage of the key-range-feature is recommended. A key-range can be defined by a string consisting of two integers delimited with a dash (-) or a string containing only one integer suffixed with a dash. The latter should be used when all records from and above a certain primary key value are to be outscaled. Note that this mode is not capable to detect deleted records, thus deleted records will not be synchronized! This mode also requires a table with exactly one primary key.

SQL	Copy Code
/* Usage of key-ranged values can only be applied to tables with exactly one primary key */ /* NOT suitable to outscale deleted records */ EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'MyTable', '4-9'; EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'SomeTable', '6-'; EXEC pr_AddOutscalingEvent   @instance = 'EMPTYSIX.CMS',   @tableName = 'FileTypes',   @key = '3-9';

Another quite simple mode is Outscaling the entire table. This can be achieved implicitly by not supplying any value for @key or explicitly by passing the sting 'ALL' for @key when invoking the procedure:

SQL	Copy Code
/* Outscale entire tables; that is: all records within a table */ /* Perfectly suitable to outscale deleted records */ EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'MimeTypes'; EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'MyTable', 'ALL'; EXEC pr_AddOutscalingEvent @instance = 'EMPTYSIX.CMS', @tableName = 'FileTypes';

Suppose, it is easier (or perhaps otherwise impossible) to determine the record(s) to be outscaled by a value of a column. This procedure supports usage of key-value-pairs to handle such cases. Typically a key-value-pair is desired with unique columns like Code or UserName. However, the stored procedure is perfectly capable to outscale multiple records if more than one record answer to this key-value-pair. For instance, for table FileTypes when entering FileExtension as key and html as its value, an EmptySix implementation would result to outscaling two records: Web Page and XHTML Web Page.

Note that key and value are delimited with := as can be seen in the examples:

SQL	Copy Code
/* Usage of key-value pair can outscale one or more records */ /* NOT suitable to outscale deleted records */ EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'AllUsers', 'LoginName:=GUEST'; EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'FileTypes', 'Code:=JPG'; EXEC pr_AddOutscalingEvent     @instance = 'EMPTYSIX.CMS',     @tableName = 'FileTypes',     @key = 'FileExtension:=html';

The last mode is the so-called short-hand key-value-pair which allows the invoker to ommit Code:= compared to the regular key-value-pair mode:

SQL	Copy Code
/* The short-hand key-value-pair allows invoker to ommit Code:= */ /* NOT suitable to outscale deleted records */ EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'SomeTable', 'SOME_CODE'; EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'FileTypes', 'GIF'; EXEC pr_AddOutscalingEvent     @instance = 'EMPTYSIX.CMS',     @tableName = 'FileTypes',     @key = 'GIF';

@caller

In order to identify the invoker of this stored procedure, a string (max 100 characters) can be entered as the @caller parameter. Typically, when a script invokes this procedure, the file name of this script is entered as @caller. It should be good practice to enter your own name when invoking in SMSS or Smartsite manager. The default value is NULL which resolves to 'pr_AddOutscalingEvent'.

@verbose

During invokation it is possible to determine the level of verbosity:

• -1: Do not print progress information or errors.
• 0: Do not print progress information.
• 1: Display diagnostic and progress information. (default)
• 2: Display all information for debugging purposes.

SQL	Copy Code
/* Invoking pr_AddOutscalingEvent with all of its parameters */ EXEC pr_AddOutscalingEvent 'EMPTYSIX.CMS', 'FileTypes', 'GIF', 'UpdateScript.sql', 1; -- or: EXEC pr_AddOutscalingEvent     @instance = 'EMPTYSIX.CMS',     @tableName = 'FileTypes',     @key = 'GIF',     @caller = 'UpdateScript.sql',     @verbose = 1;

Exceptions

Table Contents has a built-in outscaling mechanism (using activation date). Don't use this or any other procedure to outscale Contents entries.

Table ContentTypes can only be deleted by code. Not found integer keys will fail.

Table AllUsers has LoginName built in as a short-hand key-value.