The Scheduled Subscription Class

In this section, we implement the subscription class for the scheduled subscriptions. We begin by designing the subscription class schema and then build a subscription chronicle to keep state information about each subscription. After we have the subscription schema and chronicle defined, we can use these to implement the match rule that generates notifications.

Schema for the Subscriptions by Genre

When we designed the schema for the event-triggered subscription class (see the "Schema for the Subscriptions by Artist" section, p. 140, in Chapter 5), we began by thinking about how a user would express a subscription in words. This helped identify the subscription class fields. This technique can also be used to design the scheduled subscription class. For the subscriptions by genre, the subscription statement looks like this:

"Notify me of any new songs of genre G that have been added to the music store."

The only variable in the statement is the genre, "G." Thus, the subscription class schema needs only a single string field to hold the genre name. Listing 6.3 shows the declaration of the subscription class for the subscriptions by genre, called NewSongByGenre.

Listing 6.3. Declaration of the Scheduled Subscription Class Schema

 <Application>   ...   <SubscriptionClasses>     <SubscriptionClass>       <SubscriptionClassName>NewSongByArtist</SubscriptionClassName>       ...     </SubscriptionClass>     <SubscriptionClass>       <SubscriptionClassName>NewSongByGenre</SubscriptionClassName>       <Schema>         <Field>           <FieldName>GenreName</FieldName>           <FieldType>NVARCHAR(255)</FieldType>           <FieldTypeMods>NOT NULL</FieldTypeMods>         </Field>       </Schema>       ...     </SubscriptionClass>   </SubscriptionClasses>   ... </Application> 

To the contents of the existing <SubscriptionClasses> element, we've added a new <SubscriptionClass> element that holds the declaration of our new subscription class. The <SubscriptionClassName> element specifies that the name of the subscription class is NewSongByGenre, and the <Schema> element defines the single field, GenreName, that stores the name of the genre in each subscription. When the subscription class is compiled, SQL-NS creates a table to store the subscription data. Following the convention we saw earlier, this table is called NSNewSongByGenreSubscriptions and contains a column for the subscription class field declared, as well as some columns used by SQL-NS internally. As you might expect, the SQL-NS compiler also creates a view over the NSNewSongByGenreSubscriptions table, called NewSongByGenre (the same name as the subscription class), to be used in subscription matching. We'll look at this view in the "Matching Logic for Scheduled Subscriptions" section, later in this chapter.

At this point, you might be wondering where the schedule information for each subscription is stored because the subscription class schema only defines a field for the genre name. Scheduled subscriptions are a built-in feature of SQL-NS, so it automatically provides all the data structures necessary for storing schedule information. SQL-NS also provides an easy way to specify a schedule when creating a subscription using the SQL-NS Subscription Management API. We'll look at this API in Chapter 7, "The SQL-NS Subscription Management API," and the internal schedule data in Chapter 11, "Debugging Notification Generation."

Subscription State

When we built the event-triggered subscription class, we had to define the subscription class schema and the notification class before we could write the match rules. For the scheduled subscription class we're now building, we will reuse the same notification class that we used before. Although the subscription data and matching criteria are different, the resulting notifications will be of the same type as those generated for the subscriptions by artist. So we have the subscription schema and notification class defined, but before we can write the match rules, we have to define the subscription state that the application has to keep. The match rules use this state information when evaluating the subscriptions against the event history.

Recall that the event chronicle implemented earlier keeps a history of all events that the application has seen. When the scheduled subscriptions are evaluated, the genres in the subscriptions are compared to the genres of the songs in the event history to determine the matches. At any given time, different subscriptions will have been evaluated against different parts of the event history. Each subscription can have a different schedule, so subscriptions can be evaluated at different times, and some are evaluated more often than others. Each time a particular subscription is evaluated, the matching logic should look only at events in the event history that arrived after the last time that subscription was evaluated. Otherwise, the application would keep regenerating notifications for old events that it had already matched.

The event chronicle keeps a time stamp for each event that indicates when that event was seen. For each subscription, we need to keep a time stamp that stores the last time it was evaluated. We can then compare the time stamp of any event in the event chronicle against the time stamp kept for the subscription to determine whether the event occurred before or after the last time the subscription was evaluated.

Just as event chronicles keep event state, SQL-NS supports subscription chronicles that hold subscription state. For our subscriptions by genre, we can create a subscription chronicle that stores a time stamp for each subscription that represents the last time the subscription was evaluated.

Event chronicles are declared as part of the event class with which they were associated. As you might expect, subscription chronicles are declared as part of the subscription class. Listing 6.4 shows the chronicle declaration in the NewSongByGenre subscription class.

Listing 6.4. Declaration of the Subscription Class Chronicle

 <Application>   ...   <SubscriptionClasses>     <SubscriptionClass>       <SubscriptionClassName>NewSongByArtist</SubscriptionClassName>       ...     </SubscriptionClass>     <SubscriptionClass>       <SubscriptionClassName>NewSongByGenre</SubscriptionClassName>       <Schema>         ...       </Schema>       ...       <Chronicles>         <Chronicle>           <ChronicleName>NewSongByGenreChronicle</ChronicleName>           <SqlSchema>             <SqlStatement>               --Handle update by dropping the table               --first if it exists.               IF EXISTS (                  SELECT so.name                  FROM sys.objects so                  JOIN sys.schemas sc ON so.schema_id = sc.schema_id                  WHERE so.name = 'NewSongByGenreSubscriptionProcessingTimes'                     AND so.type = 'U'                     AND sc.name = 'SongAlerts'               )                  DROP TABLE [SongAlerts].[NewSongByGenreSubscription   ProcessingTimes]             </SqlStatement>             <SqlStatement>               -- Create the table.               CREATE TABLE [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes]           (                   SubscriptionId     BIGINT    NOT NULL,                   LastProcessingTime DATETIME  NOT NULL               )             </SqlStatement>           </SqlSchema>         </Chronicle>       </Chronicles>     </SubscriptionClass>   </SubscriptionClasses>   ... </Application> 

This subscription chronicle declaration has essentially the same structure as the event chronicle declaration you saw in Listing 6.1. The <ChronicleName> element provides the name of the chronicle (NewSongByGenreChronicle in this case), and the <SqlSchema> element provides the SQL statements that create the chronicle tables. The SQL-NS compiler executes each SQL statement in its own batch, in the order specified.

As in the event chronicle, the <SqlSchema> element contains two <SqlStatement> elements: the first contains a SQL statement that drops the chronicle table if it already exists; the second SQL statement creates the chronicle table. Again, this is to handle the case where the SQL-NS update tools rerun the chronicle statements against a database in which the chronicle table has previously been created. The chronicle table, NewSongByGenreSubscriptionProcessingTimes, has two columns: one for the subscription ID and one for the last processing time. For each subscription in the application, there will be a row in this table.

Unlike the event chronicle, which had a <ChronicleRules> element, there is no dedicated element in which subscription chronicle rules are defined. Instead, the subscription chronicle rules are declared along with the match rules in a single ADF element. The reason for this is that in many applications (ours included), the subscription chronicle logic is actually an inseparable part of the match logic. Parts of the chronicle update logic run before the matches are evaluated; others after. By allowing you to write the chronicle update logic along with the matching logic, SQL-NS gives you complete flexibility in the ordering and interleaving of these rules. If the chronicle rules were defined separately, it would be difficult for SQL-NS to provide sufficiently flexible execution ordering options to meet the needs of all applications. In the next section, when we look at the match rules for the subscription class, we'll also look at the code that updates the subscription chronicle.

Matching Logic for Scheduled Subscriptions

At this stage, we have all the elements necessary to write the match rules for the scheduled subscription class: the subscription schema, event state, subscription state, and notification class. Before we examine the code for the rule, it's important to understand the data available when the rule fires.

Each time this rule is run, SQL-NS populates the subscription class view, NewSongByGenre, with only those subscriptions whose schedules specify that they should be evaluated at that time. The code that determines which subscriptions should be included in the view (based on their schedules) is provided by SQL-NS. This means that match rules need not include any special logic to evaluate subscription schedules: rules can operate against the subscriptions view and they will see only the subscriptions that should be evaluated. This scoping of subscription data based on schedules is similar to the way SQL-NS scopes event data in the events view to only those events that need to be processed.

Because scheduled rules aren't associated with an event class and are not triggered by event arrival, SQL-NS does not set up an events view as it did for the event-triggered rules. The only event data available to the rule is that which it can obtain from the event chronicles.

Listing 6.5 shows the declaration of the match rules for the NewSongByGenre subscription class.

Listing 6.5. Declaration of the Subscription Class Match Rules

[View full width]

 <Application>   ...   <SubscriptionClasses>     <SubscriptionClass>       <SubscriptionClassName>NewSongByArtist</SubscriptionClassName>       ...     </SubscriptionClass>     <SubscriptionClass>       <SubscriptionClassName>NewSongByGenre</SubscriptionClassName>       <Schema>         ...       </Schema>       <ScheduledRules>         <ScheduledRule>           <RuleName>MatchNewSongsByGenre</RuleName>           <Action>             -- Store the current processing time for use later.             DECLARE @CurrentProcessingTime DATETIME             SELECT @CurrentProcessingTime = GETUTCDATE()             --Insert rows into Processing Times chronicle             --for new subscriptions.             INSERT  INTO [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] (SubscriptionId, LastProcessingTime)             SELECT  subscriptions.SubscriptionId, subscriptions.Created             FROM    [SongAlerts].[NewSongByGenre] subscriptions             WHERE   NOT EXISTS (                 FROM    [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes]chron                 SELECT  chron.SubscriptionId                 WHERE   chron.SubscriptionId = subscriptions.SubscriptionId             )             --Do the match.             INSERT INTO [SongAlerts].[NewSong]             SELECT subscriptions.SubscriberId,                    N'DefaultDevice',                    N'en-US',                    songs.SongTitle,                    songs.ArtistName,                    songs.AlbumTitle,                    songs.GenreName             FROM  [SongAlerts].[SongAddedLog] eventlog             JOIN    [Catalog].[SongDetails] songs                 ON eventlog.SongId = songs.SongId             JOIN    [SongAlerts].[NewSongByGenre] subscriptions                 ON subscriptions.GenreName = songs.GenreName             JOIN    [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] chron                 ON subscriptions.SubscriptionId = chron.SubscriptionId             WHERE eventlog.TimeAdded &gt;= chron.LastProcessingTime                 AND eventlog.TimeAdded &lt; @CurrentProcessingTime             --Update Processing Time Chronicle for those             --subscriptions just processed.             UPDATE  [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes]             SET     LastProcessingTime = @CurrentProcessingTime             FROM    [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] chron             JOIN    [SongAlerts].[NewSongByGenre] subscriptions                 ON chron.SubscriptionId = subscriptions.SubscriptionId           </Action>         </ScheduledRule>       </ScheduledRules>       <Chronicles>         ...       </Chronicles>     </SubscriptionClass>   </SubscriptionClasses>   ... </Application> 

The match rules for scheduled subscriptions are declared in the <ScheduledRules> element (recall that match rules for event-triggered subscriptions are defined in the <EventRules> element). Each rule is declared in its own <ScheduledRule> element, which contains the subelements <RuleName> and <Action>. Scheduled rules do not have an <EventClassName> element as the event rules do because scheduled rules are not triggered by events. Instead, scheduled rules are executed on a periodic basis, whenever scheduled subscriptions are due to be evaluated.

The rule's logic is specified in the <Action> element. The rule logic in Listing 6.5 really does three things:

• Inserts rows into the subscription chronicle for new subscriptions that have never been processed before

• Matches subscriptions with the events in the event history that arrived between the last processing time and the current processing time

• Updates the last processing times in the subscription chronicle for the subscriptions just evaluated

The first operation is handled by an INSERT statement that puts new rows into the NewSongByGenreSubscriptionProcessingTimes chronicle table. It selects those rows from the subscriptions view where the subscription ID does not exist in the chronicle table already. This is the case for new subscriptions that have never been processed before and therefore don't have a last processing time recorded. For each of these subscriptions, it inserts a row into the chronicle table that provides the subscription's creation time as the last time it was processed. This effectively initializes the chronicle record for each new subscription.

Note that the subscription ID and creation time values are obtained from the SubscriptionId and Created columns in the subscriptions view, respectively. These columns are provided by SQL-NS; for complete documentation of the columns available in the subscriptions view, consult the SQL-NS Books Online.

The next statement in the rule logic does the actual matching. It generates notifications by inserting rows into the notifications view of the NewSong notification class. The statement does a four-way join between the following:

• The event chronicle table, SongAddedLog, which provides the event history

• The SongDetails view in the music store catalog, which provides the full properties of the songs

• The NewSongByGenre subscriptions view, which contains the data for the subscriptions being evaluated

• The subscription chronicle table, NewSongByGenreSubscriptionProcessingTimes, which provides the last time each subscription was evaluated

When the subscriptions view is joined, the join condition matches the genre name specified in the subscription with the genre in the song details. Thus, only rows where the genres match are returned. This implements the fundamental matching criterion for the subscriptions by genre.

The WHERE clause filters out events that should not be evaluated at the current time. The condition specifies that the value in the TimeAdded column in the event chronicle must be greater than or equal to the last processing time recorded in the subscription chronicle and less than the current processing time. Note the use of the &gt; and &lt; escape sequences, which are required because the SQL greater than and less than operators, > and <, are reserved characters in XML.

The last statement in the rule updates the subscription class chronicle. For every subscription in the subscription class view, the statement updates the last processing time to the current processing time. This value will be used to filter out old events the next time the rule fires.

The current processing time is obtained at the beginning of the match rule (using the built-in SQL function GETUTCDATE()) and is stored in the local variable @CurrentProcessingTime. The value of this variable is used when filtering events from the event chronicle and updating the subscription chronicle's last processing times. Using a single time value like this, recorded at the beginning of the operation, avoids race conditions. Had we simply called GETUTCDATE() each time we needed the current time, the event chronicle filter and update of the subscription chronicle records may have been executed with different time values. Any events that might have arrived and been added to the event chronicle between these times would have been lost.

Notice that the scheduled rule logic does not have to examine the subscription schedules at all. Because SQL-NS determines which subscriptions are due to be evaluated and places only those subscriptions' data in the subscriptions view, the scheduled rules can just join with this view and do not need to consider the actual schedule data in any way. This makes scheduled logic easy to write and is a key benefit of using SQL-NS.

Using Event Rules and Scheduled Rules In The Same Subscription Class

SQL-NS allows you to define both event rules and scheduled rules in the same subscription class. This is useful if you want to create a single subscription class that supports both scheduled and event-triggered subscriptions.

When writing event and scheduled rules for the same subscription class, it's important to be aware that both kinds of rules execute against the same subscriptions view. The subscriptions view always contains the data from both the scheduled and the event-triggered subscriptions (regardless of which type of rule is firing).

For the event rules to execute only against the event-triggered subscriptions, and the scheduled rules only against the scheduled subscriptions, the rules must distinguish the types of subscriptions themselves. This can be done by means of the ScheduleId column that SQL-NS includes in the subscriptions view of any subscription class that has scheduled rules.

The ScheduleId column is NULL for rows that represent event-triggered subscriptions, and non-NULL for scheduled subscriptions. The event rule can include a ScheduleId IS NULL predicate in its WHERE clause to filter the scheduled subscriptions. Similarly, the scheduled rule can include a ScheduleId IS NOT NULL predicate in its WHERE clause.