| Add the code shown in Listings 6.3, 6.4, and 6.5 to your ADF, either by typing it in manually or using the supplementary ADF, ApplicationDefinition-6.xml, as described in the "Adding Code to the ADF" section (p. 130) in Chapter 5. When your ADF is ready, run the update instructions from Chapter 5 to update your instance. After the update completes, you can test the scheduled subscription functionality of the application. In the prototype stage, the general technique for testing scheduled subscriptions is to enter some subscriptions that are scheduled to fire a few minutes into the future. You then submit events to ensure that the event chronicles contain an event history for the scheduled rules to operate against, and wait for the scheduled subscriptions to be evaluated. If you make refinements and need to test again, you generally enter a new set of scheduled subscriptions, again scheduled to fire a few minutes into the future. This is required because the first set of scheduled subscriptions won't fire again until the same time on the next day. Along with the source code for this book, I've included a tool called SubscriptionLoader that can be used to add subscriptions (event-triggered or scheduled) to any SQL-NS application. We'll use this tool now to submit scheduled subscriptions to the music store application. Because SubscriptionLoader is designed to work against any SQL-NS application (it's not tied to the music store application) you'll also be able to use it when testing your own SQL-NS applications. Note The SubscriptionLoader tool is built using the SQL-NS Subscription Management API, which you'll learn about in Chapter 7, and the Notification Services Management Objects (NMO) API, covered in Chapter 16, "Using Notification Services Management Objects." In Chapter 16, we'll look at the source code for the SubscriptionLoader tool.
Follow these steps to add scheduled subscriptions to the music store using SubscriptionLoader and exercise the scheduled rule: Caution Before you perform these steps, make sure that you restarted the service, NS$MusicStore, after the last update.
1. | Invoke SubscriptionLoader.exe from the C:\SQL-NS\Tools\bin directory on your development machine.
| 2. | In the Connect to SQL Server dialog box, enter the name of your SQL Server and select an authentication mode. If you select SQL Server Authentication, enter the login name and password of the development account you created in Chapter 2, "Getting Set Up." Click OK to connect.
| 3. | The main SubscriptionLoader dialog box is divided into four sections. In the first section, you can select the SQL-NS instance, application, and subscription class for which you want to enter subscriptions. Select the MusicStore instance, the SongAlerts application, and the NewSongByGenre subscription class. (Notice that the event-triggered subscription class, NewSongByArtist, is also offered as a choice.)
| 4. | In the next section of the SubscriptionLoader dialog box, you can select the ID of the subscriber for whom the subscription will be created. The drop-down list should show the four subscribers we added to the music store instance in Chapter 5. Select any of these subscribers.
| 5. | Below the subscriber selection is a data grid in which you enter values for the subscription fields. When you select a subscription class (as you did in step 3), the grid is populated with the names of the subscription fields in that subscription class. For the NewSongByGenre subscription class, you'll see the GenreName field shown. Enter the value Jazz for the GenreName field.
| 6. | The final section of the SubscriptionLoader dialog box lets you specify a subscription schedule. The controls in this section are disabled if you select a subscription class with only event-triggered rules, but because NewSongByGenre contains scheduled rules, these controls should be enabled. In the Schedule Start Time field, select a start time 2 or 3 minutes into the future. Select your local time zone in the Timezone field (it should be preset to your local time zone, but you can change it as necessary). Note that the value initially shown in the Schedule Start Time field is the time at which the program was startedthis value is not updated, so you may have to add several minutes to it if you've had the program open for a while.
| 7. | Click the Add Subscription button. You should see a confirmation dialog box telling you that the subscription was successfully added.
| 8. | In Management Studio, run the SubmitEvents.sql script again to submit some new events. Make sure you do this before the start time you specified for the scheduled subscription.
| 9. | Wait until about 30 seconds after the start time of your scheduled subscription and then examine the notifications output file (FileNotifications.txt). You may also see this file updated before the scheduled subscription is evaluated, because the events you submitted would also have matched the event triggered subscriptions previously entered. Make sure you examine the file after the scheduled subscription's evaluation time to see the notification that was generated for it.
|
Although scheduled subscriptions operate against event history, and you have at least one batch of events already in your event chronicle from previous tests, it's still necessary to submit a new batch of events, as you did in step 8 of the previous instructions. This is because the first time the subscriptions are evaluated, the match rule looks only at events in the chronicle with time stamps newer than the time the subscriptions were created. (Recall that the subscription chronicle is initialized with the creation time of the subscription as the last time it was processed.) Any events in the chronicle from before the scheduled subscriptions were created are ignored. Let's now take a look at the actual subscription data entered through the SubscriptionLoader tool and examine the contents of the chronicle tables to verify that our scheduled rule worked as expected. I intentionally avoided asking you to look at the subscriptions table immediately after adding the subscription earlier because you needed to complete the next steps before the subscription's start time was reached. 1. | From a new query window in Management Studio, execute the following query:
USE MusicStore GO SELECT * FROM [SongAlerts].[NSNewSongByGenreSubscriptions]
This returns the data in the subscriptions table for all the NewSongByGenre subscriptions in the application. From this, you can see the genre names specified in each subscription. You should see a row returned for the subscription you entered in SubscriptionLoader. The subscription schedules are stored separately from the rest of the subscription data, in a table called NSNewSongByGenreSchedules. You can examine this table with the following query:
SELECT * FROM [SongAlerts].[NSNewSongByGenreSchedules]
| 2. | Next, you can see the events in the event chronicle and the times they were added by executing the following query:
SELECT * FROM [SongAlerts].[SongAddedLog]
This shows you all rows in the event chronicle, some of which may be older than the subscriptions.
| 3. | Take a look at the notification generated by querying the notifications table. Execute the following query (this returns only the notifications in the last notification batch, which was generated as a result of running the scheduled rule):
SELECT * FROM [SongAlerts].[NSNewSongNotifications] WHERE NotificationBatchId = (SELECT MAX(NotificationBatchId) FROM [SongAlerts].[NSNewSongNotifications])
Looking at the subscription data and the song details for the song IDs in the event chronicle, you should be able to see how the notification was generated.
| 4. | The subscription chronicle should contain a row for each scheduled subscription that shows its last processing time. Run the following query to see the contents of the subscription chronicle:
SELECT * FROM [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes]
For our scheduled subscription, there should now be a row in this table that specifies the last time the subscription was processed.
|
|
