As in previous chapters, we continue developing the music store application in this chapter. In preparation for the material in this chapter, we need to make some changes to the application and re-create the SQL-NS instance that hosts it. The changes we need to make include removing all the extraneous event providers added in Chapter 8, "Event Providers," and modifying the subscription classes and match rules to accommodate locale and device-specific formatting. This section describes the specific changes that are needed and provides instructions on how to get the new instance set up. It also describes how we get events and subscriptions into the application for testing the code we add in this chapter. Cleaning Up the Old InstanceBefore you begin, make sure that you complete the cleanup steps from Chapter 8, described in the "Cleanup: Preparing for the Next Chapter" section (p. 304). This completely removes the instance created in Chapter 8 from your system and allows you to create a new instance to replace it. Changes to the ADFThis section describes the changes to the ADF that need to be made before we start looking at content formatters. These changes enable the scenarios that highlight key features of the content formatting infrastructure in SQL-NS. Later in this chapter, there will be other changes to make. Removal of Extra Event ProvidersIn Chapter 8, we added several event providers to the application to test different ways of submitting events, but most of these aren't needed. In fact, we really need only one event provider for the application to work. In this chapter, we remove all but the standalone event provider embedded in the AddSongs program. We use AddSongs to add song records and also to submit events. To clean up the event providers, we remove the unnecessary <HostedProvider> and <NonHostedProvider> declarations in the ADF. Because we're using only the embedded event provider in AddSongs, only its <NonHostedEventProvider> declaration should remain in the <Providers> element of the ADF. When we added the SQLProvider to the application in Chapter 8, we also added a chronicle that was used to store the last time the events query was run. Because this chronicle serves to support the SQLProvider, which we're not using anymore, we can remove this chronicle entirely in this chapter. Storing Device and Locale Information in SubscriptionsThe "Formatting for Different Languages and Device Types" section (p. 309) described how the locale and device information for a subscription can be stored in fields of the subscription class. This allows the subscriber to specify values for these fields, which the match rule can insert into the notifications view (instead of inserting hard-coded values as we have been doing). Implementing this in the music store application requires two modifications. First, we have to add locale and device name fields to the schemas of the subscription classes. Then we have to change the match rules to use the values of these fields from the subscription views. Listing 9.1 shows the changes to the subscription class schemas. Listing 9.1. Changes to the Subscription Class Schemas to Store Device and Locale Information
To each subscription class schema, two fields have been added: DeviceName and Locale. DeviceName refers to the name of a subscriber device, as stored in the subscriber devices table in the instance schema. The Locale field stores a subscriber locale code. The match rule inserts the values of these fields into the notifications view when it generates notifications. When the distributor picks up the notifications for formatting, it will obtain the device type from the subscriber device record, based on the value of the DeviceName field inserted into the notifications view, and pass this, along with the Locale value, to the content formatter. Note After making the changes shown in Listing 9.1, the subscription management interface (SMI) built in Chapter 7, "The SQL-NS Subscription Management API," will no longer work because it is not aware of the new subscription class fields. We will not maintain the SMI in this and the remaining chapters of this book because the SMI coding would distract from the new topics that these chapters cover. Instead, we'll use a T-SQL script to enter subscriptions for testing. However, it should be clear that none of the changes described here invalidate any of the general principles and techniques of SMI development described in Chapter 7. Given a good understanding of the material in Chapter 7, you should be equipped to make the required changes to the SMI should you choose to. Listing 9.2 shows the corresponding modifications to the match rules, to accommodate the new fields added to the subscription classes. Listing 9.2. Changes to the Match Rules to Pass the DeviceName and Locale Values from the Subscriptions View
Because they are part of the subscription class schemas, DeviceName and Locale become columns in the subscription views that the match rules use. Instead of using hard-coded values for the DeviceName and SubscriberLocale columns in the notifications view, the match rules now use the values in the subscription views. Adding Computed FieldsThe "Content Formatter Input and Output" section (p. 309) describes computed fields that form part of the input to content formatters. Up to this point, none of the examples have used computed fields. To examine how computed fields work, we add one to the NewSong notification class. Listing 9.3 shows the code that defines a new computed field. Listing 9.3. Declaration of a Computed Field in the NewSong Notification Class Declaration
Computed fields are defined in the <ComputedFields> element, immediately following the <Fields> element in which the standard notification fields are declared. Each <ComputedField> declaration specifies a field name and a SQL expression that computes the value of the field. To understand the kinds of expressions you can specify in the <SqlExpression> element, imagine a SELECT statement that selected all the fields defined in the notification class from the notifications table. A portion of this statement might look like SELECT SongTitle, ArtistName, AlbumTitle, Genre FROM NewSongNotifications... Now imagine that in addition to the notification fields, you want the SELECT statement to return additional computed values in the resultset. For example, if you wanted to obtain an additional column that held the artist name in uppercase, you could modify the select statement as follows: SELECT SongTitle, ArtistName, AlbumTitle, Genre, UPPER(ArtistName) AS UpperCaseArtistName FROM NewSongNotifications... The resultset from this statement would contain an additional column called UpperCaseArtistName that held the result of evaluating the SQL expression UPPER(ArtistName) for each row. Note UPPER() is a built-in SQL function that converts a string to uppercase. This is how computed fields work. In the <SqlExpression> element of a computed field definition, you can specify any expression that can legally appear in the SELECT clause of a SQL query. The expression can involve constants, fields in the notification class, and calls to SQL functions. The distributor incorporates these expressions in the SELECT statement it uses to read rows from the notifications table, in the same way that the UPPER(ArtistName) expression was used in the previous SELECT statement example. In the computed field shown in Listing 9.3, the expression calls the built-in SQL CONVERT() and GETDATE() functions to get the current date and time in a readable format. Computed fields provide a convenient way for you to use SQL functions to format some of the notification data, before the content formatter is even invoked. The distributor passes the values of the notification fields and the computed fields to the content formatter, so the results of the computed field expressions can be used to produce the final formatted notification. Creating the New InstanceSupplementary files containing the code shown in this chapter are located in the C:\SQL-NS\Chapters\09\SupplementaryFiles directory:
To work with the examples in this chapter, you'll need to re-create the music store instance on your system from scratch. Use the following instructions to prepare the source files and get the new instance running:
Submitting EventsAs described earlier, we will use the AddSongs program you saw in Chapter 8 to submit events. Refer back to the section "The AddSongs Program" (p. 243) in Chapter 8 for instructions on how to build and run the program. Because we want AddSongs to act as an event provider, check the Submit Events for Songs Added box whenever you use AddSongs in this chapter. Adding Subscribers and SubscriptionsTo test the code in this chapter, we need to add a set of subscribers and subscriptions that specify various locales and device types. Building a fully functional SMI to do this would be a distraction (most of the time would be spent building the appropriate user interface); instead, we'll use a simple T-SQL script to enter subscriber and subscription information. Open the script, C:\SQL-NS\Chapters\09\Scripts\AddSubscribersAndSubscriptions.sql in Management Studio and examine the code in it. The script inserts subscriber, subscriber device, and subscription data into the views in the instance and application schemas. Run the script now to insert the data (you must have completed the instructions in the "Creating the New Instance" section [p. 319] before you do this). Notice that the script creates one device called CellPhone and another called WorkEmail for each subscriber. The device names are tokens that can be displayed in an SMI to help users distinguish their devices. The device types for these devices are TextMessageDevice and Email, respectively. Two NewSongByArtist subscriptions are created for each subscriber. Each subscription is associated with a different device: One specifies WorkEmail as the target device, and the other specifies CellPhone. Each subscription also specifies a locale for which the notification should be formatted. The variations in the locale and device data enable us to test the locale and device-specific formatting facilities of SQL-NS, later in this chapter.
Later in this chapter, we will submit into the application events that match some of the subscriptions added in this section. We'll choose events that match subscriptions with a variety of locales and target device types to illustrate how the content formatter chooses the appropriate formatting. |