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
Schema for the Subscriptions by
<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
element, we've added a new
element that holds the declaration of our new subscription class. The
element specifies that the name of the subscription class is
, and the
element defines the single field,
, 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
and contains a column for the subscription class field declared, as well as some
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."
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
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.
<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
As in the event chronicle, the
element contains two
elements: the first contains a SQL statement that
Unlike the event chronicle, which had a
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
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,
, 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
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.
[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 >= chron.LastProcessingTime AND eventlog.TimeAdded < @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:
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
columns in the subscriptions view, respectively. These columns are provided by SQL-NS; for complete documentation of the columns available in the subscriptions view,
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.
clause filters out events that should not be evaluated at the current time. The condition specifies that the value in the
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
escape sequences, which are required because the SQL greater than and less than operators,
, are reserved
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
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.