The Simplest SQL Notification Application: Stock Quotes
Date: Oct 8, 2004
Sample Chapter is provided courtesy of Sams.
In this chapter, you'll see your first SQL-NS application. Think of this chapter as a tour: My intent is simply to show you around the various facilities that the platform offers so that you get a feel for the application model and the process of coding to it.
We will look at code in this chapter, but simply for the purpose of understanding the concepts behind the application. A line-by-line explanation of the code at this stage would drown out the simpler picture that I'm trying to show.
That said, I will gloss over (or in some cases, completely ignore) some parts of the code you will see; I'll just highlight those pieces of the code that illustrate particularly important parts of the application model. Subsequent chapters will cover the rest in detail.
The SQL-NS Application Model
SQL-NS can be used to build a variety of notification applications with different uses and for different application domains. But, when viewed from the highest level, all these notification applications conform to the same basic model, shown in Figure 3.1.
Data enters the application from the outside world. This data can be pulled in by the application, or pushed in by an external source. In SQL-NS terms, each piece of data is referred to as an event, because it represents some happening in the outside world that may potentially be of interest to some subscribers. An event may be a new price for a stock, notice of a new traffic incident, or a gate change for a flight.
Figure 3.1 High-level view of a notification application.
The notification application maintains users' subscriptions; subscriptions are users' declarations of what kinds of events interest them. When events arrive, the application matches them with the subscriptions and produces a set of notifications. These notifications are delivered to the end users.
Events As Data
Events are just descriptions of some real-world "happening" that can be represented as data. For example, a change in the price of a stock (an event potentially of interest to a stock broker client) can be described as a piece of structured data containing a stock symbol field and a stock price field. A traffic incident event (for a traffic reporting application) might contain a field that describes the location of the incident and another describing the incident type (accident, road closure, weather warning, and so on).
Whatever the type of event, its description can be modeled as data. The structure of the data can be described with a schema that indicates the names of the fields and their data types. Given this schema, it's easy to construct a database table to store the event data. For example, stock events can be stored as rows in a table as shown in Figure 3.2.
Figure 3.2 Modeling stock events as rows in a table.
Subscriptions As Data
Thinking of events as data is usually quite natural, but subscriptions can be modeled as data too. Think of the subscriptions to the stock application. Let's say that all subscriptions will have the form
"Notify me when the price of stock S goes above price target T."
where S represents some stock symbol and T represents a price target. So an example subscription might be
"Notify me when the price of stock XYZ goes above price target $50.00."
When the form of all the subscriptions is fixed as such, an individual subscription can simply be represented as a pair of values for S and T. Such subscriptions could be stored in a table as shown in Figure 3.3.
Figure 3.3 Modeling stock subscriptions as rows in a table.
Each row identifies the subscriber and the stock symbol and price target of interest to them. For illustrative purposes, here the subscriber is just represented by a name, but in reality it may be a richer identifier.
NOTE
The stock quotes application we will build in this chapter supports the kinds of events and subscriptions discussed in the examples so far. The application allows subscribers to enter subscriptions for stocks in which they are interested. The application notifies them when those stocks cross the target prices that the subscribers specify.
For example, I could enter a subscription in this application that says, "Notify me when stock XYZ goes above $50." The application will receive a constant stream of stock price updates (representing changes in the market), and when an event indicating that the current price of XYZ is above $50 arrives, the application will send me a notification.
Matching Events with Subscriptions
As mentioned in Chapter 1, "An Overview of Notification Applications," the matching of events with subscriptions is the key function of the notification application. If the matching can be implemented efficiently, the application will scale to large volumes.
With events and subscriptions both represented as data, matching can be accomplished by means of a SQL join. Given the table structures in Figures 3.2 and 3.3 for events and subscriptionsand let's say that we called the events table Events and the subscriptions table Subscriptionsthe following SQL statement would determine the matches:
SELECT S.Subscriber, E.StockSymbol, E.StockPrice FROM Events E JOIN Subscriptions S ON E.StockSymbol = S.StockSymbol WHERE E.StockPrice >= S.PriceTarget
This joins the stock events table with the subscriptions table on stock symbol and then selects rows where the stock price in the event is greater than or equal to the stock price target specified in the subscription. The set of rows returned by this query represents the notifications to be sent. For the particular example data shown previously, Figure 3.4 shows the results of the query.
Figure 3.4 Results of matching events with subscriptions.
Note that only three of the four subscriptions matched: Jane's subscription specified a price target for PQS of 100.00, and because the stock price event for PQS said the price was only 95.30, the matching query did not return a row for Jane.
Each of the rows in the results table is the raw data for a notification to be sent. Thus, notifications too can be modeled as rows of data in a table. This data can later be packaged into a readable message and then delivered to the appropriate subscriber.
Scalability of the SQL-NS Application Model
The modeling of both events and subscriptions as data is a key innovation of SQL-NS and the basis for its capability to scale. Because both events and subscriptions are just rows in tables, SQL joins can be used to match them. In general, SQL joins are extremely efficient at matching large sets of data; more than 20 years of query processing and indexing innovation make this possible. As long as a reasonable join query can be written for a particular event and subscription schema (in most cases, one can), then the cost of matching (in terms of computing resources) will be low. Furthermore, this cost grows sublinearly with the amount of data. That is, if you double the number of events or subscriptions, the cost of matching does not double, but rather grows by some much smaller increment.
This model is different from that used by most other pub-sub systems. Most other systems model individual subscriptions as queries, rather than data. The simplest of these systems evaluates the subscription queries one-by-one for a given set of events. This strategy is expensive, and, as the number of subscriptions or events grows, the cost of evaluation grows linearly.
NOTE
To be absolutely clear, the SQL-NS application model uses queries to evaluate subscriptions. But, it does not model each individual subscription as a query. Instead, in the SQL-NS application model, there is one query for each subscription type. This query evaluates all subscriptions of that type at once. This is the key differentiator between SQL-NS and other pub-sub systems.
To improve performance, the more sophisticated of the systems that model subscriptions as queries try to analyze the subscription queries for similarity and then evaluate the similar parts in a set-oriented fashion. Query analysis can be difficult in general, but deducing similarity can be especially difficult when the queries can be arbitrarily complex. In many cases, systems that model subscriptions as queries can't do much better than one-at-a-time evaluation.
In the SQL-NS model of subscriptions as data, the application developer actually enforces the similarity between subscriptions. By fixing the form of the subscriptions, the developer is providing a subscription template. This template has parameters that can vary per subscription. Individual subscriptions are then just sets of values for these parameters. In the stock example, the subscription template defines the structure of the subscription query, and the individual subscriptions just supply values for the stock symbol and target price.
The obvious drawbacks to this approach are that all users' subscriptions must have the same structure, and the only kinds of subscriptions that users can enter are those for which the application developer has provided a template. It is true that modeling a subscription as a query, as other systems do, allows for greater end user flexibility because each user can enter any arbitrary subscription. For example, in a system that models subscriptions as queries, one user might have a simple subscription such as
"Notify me when the price of stock XYZ goes above price target 50.00."
and another might write a subscription query that says
"Notify me when the price of stock XYZ goes above price target 50 and the trading volume is greater than 100,000 and there are favorable news stories featuring stock symbol XYZ."
It's worth questioning whether such flexibility is really needed in your application. Do your users really need to be able to enter arbitrarily complex subscriptions? Does each user need to be able to enter a different kind of subscription? Or can you devise a set of subscription templates that cover the overwhelming majority of subscriptions users will want to enter? Note that SQL-NS subscription templates can be rich and support complex subscriptions like the preceding one. The template-based approach doesn't really restrict you as a developer: You can easily build complex subscription templates if you need them in your application. However, it does restrict your users: They can only create subscriptions for which you have provided templates.
SQL-NS allows you to support several types of subscriptions in a single application, by providing a template and a set of parameters for each subscription type. Experience has shown that in the vast majority of applications, developers can predict what subscriptions users will want to enter and create subscription classes for those. Doing so usually results in some powerful applications that are immediately useful to most users. Often, the lack of flexibility in authoring subscription queries is not even noticed by the end users.
Programming to the SQL-NS Application Model
In summary, the SQL-NS application model views events and subscriptions as data, and uses SQL joins to match them. As a developer building an application on the SQL-NS platform, you provide two things: schemas and logic that define the application's behavior and a component configuration that determines how the SQL-NS execution engine components actually run the application. Both the schemas and logic, as well as the component configuration, are provided in an XML document called an Application Definition File (ADF).
In the schemas and logic part of the ADF, you specify the schema for the events and subscriptions and the SQL join logic that matches them. Also, you specify a schema for the notification data that the join produces.
Each of the schemas is a description of the size and shape of a certain kind of data: You provide the names of the fields and their data types, much as you would if you were defining a SQL table. The schema for the events specifies what the data your application receives from the outside will look like. The schema for the subscription data describes the parameters to the subscription template. The schema for the notification data describes the fields that result from the join that matches events and subscriptions.
After the event and subscription data schemas are defined, you also provide the SQL join statements used to match them. In writing the join statement, you can apply operations to the event and subscription data to determine whether they match. Effectively, you are defining what a "match" means for your particular event and subscription types. In the previous stock example, a match meant that the stock symbol in the event was the same as the stock symbol in the subscription, and the stock price was equal to or greater than the target price specified in the subscription.
In the component configuration section of the ADF, you specify how the SQL-NS engine components should run your application. You can specify how the components are distributed across various servers, what resources they should use, and when they should run.
You compile your finished ADF using the SQL-NS compiler. This produces a database that is used to run the application, containing tables for events and subscriptions and stored procedures that execute the join logic you provided. Finally, you register an instance of the SQL-NS Windows Service to host the engine components that run your application. This service coordinates the various running components of your application and their interactions with the database, as shown in Figure 3.5.
Figure 3.5 The ADF is compiled into database structures, and a Windows Service runs the application.
NOTE
Figure 3.5 shows only the parts of the application created by compiling the ADF and registering an instance of the Windows Service. Other important parts of a complete, running applicationsuch as the systems that submit events, the subscription management interface, and the delivery systemsare not shown.
In this chapter, we're going to build the stock quotes application. We'll define the schema of the event data, the schema of the subscription data, the SQL logic that matches them, and the schema of the final notification data. Although there are other parts to the application, these are the parts we'll really focus on in this chapter because they form the application's core and are the most illustrative of the SQL-NS application model.
NOTE
To work through the steps in this chapter, you need to set up your development environment as described in Chapter 2, "Getting Set Up." If you have not already done this, go through the steps in that chapter before proceeding.
The source code for this chapter is located in the Chapter03 directory under the source code base directory. Several files are in the folder: Some are discussed here; others are explained in later chapters. For now, the file to focus on is the stock application's ADF, which can be found in the Stock folder under Chapter03. The file is called ApplicationDefinition.xml. Open this file in your XML editor and refer to it as you read through this chapter.
Building the Stock Application's ADF
The ADF for the stock application defines its schemas and logic, as well as its component configuration. The schemas describe the size and shape of events, subscriptions, and notifications; the logic defines how events and subscriptions are matched to produce notifications. The component configuration tells SQL-NS how to actually run the application.
Table 3.1 shows the high-level structure of the ADF (with the application-specific details removed). Think of this as a basic outline for every ADF you will ever write. A root <Application> element contains various other XML elements that define the parts of the application. This section describes the content that goes in each of these XML elements to create a real application.
NOTE
I've left some optional ADF elements out of Table 3.1 for clarity. These include the <History>, <Version>, <ApplicationExecutionSettings>, and <Database> elements. These elements are described in the SQL-NS Books Online, and some of them are discussed in Chapter 11, "Debugging Notification Generation"; Chapter 12, "Performance Tuning"; and Chapter 13, "Deploying a Notification Services Application."
Table 3.1 Basic Structure of the ADF
XML Element |
Purpose |
<?xml version="1.0" |
Standard XML header encoding="utf-8" ?> |
<Application xmlns:xsd=http://www.w3.org/2001/XMLSchema |
Root element that contains all of the application definition |
<EventClasses></EventClasses> |
Defines schemas for the events the application will receive |
<SubscriptionClasses></SubscriptionClasses> |
Defines schemas and matching logic for the subscriptions the application will support |
<NotificationClasses></NotificationClasses> |
Defines schemas for the notifications the application will send |
<Providers></Providers> |
Configures the application's event providers |
<Generator></Generator> |
Configures the generator component that matches events with subscriptions |
<Distributors></Distributors> |
Configures the distributor components that deliver notifications |
</Application> |
Closing tag for the root element |
The first three sub-elements under <Application>, <Event Classes>, <SubscriptionClasses>, and <NotificationClasses>, contain all the schemas and logic. The rest of the elements provide the component configuration.
The Completed ADF
Before delving into the details of each specific section, take a look at the completed ADF, shown in Listing 3.1. I'm including this here, even before I explain what any of it means, because I find that with almost any program, it helps to get a feel for the code by looking at it from beginning to end. Just by glancing at it, the ADF will probably make some sense to you, even without further explanation. The following sections describe the important parts in detail.
Listing 3.1 The Completed Stock ADF
<?xml version="1.0" encoding="utf-8" ?> <Application xmlns:xsd=" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ ApplicationDefinitionFileSchema"> <EventClasses> <EventClass> <EventClassName>StockPriceChange</EventClassName> <Schema> <Field> <FieldName>StockSymbol</FieldName> <FieldType>nvarchar(10)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>StockPrice</FieldName> <FieldType>decimal(10,2)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> </Schema> </EventClass> </EventClasses> <SubscriptionClasses> <SubscriptionClass> <SubscriptionClassName>StockPriceHitsTarget
</SubscriptionClassName> <Schema> <Field> <FieldName>StockSymbol</FieldName> <FieldType>nvarchar(10)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>StockPriceTarget</FieldName> <FieldType>decimal(10,2)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> </Schema> <EventRules> <EventRule> <RuleName>MatchStockPricesWithTargets</RuleName> <Action> SELECT dbo.StockAlertNotify( subscriptions.SubscriberId, N'DefaultDevice', N'en-US', events.StockSymbol, events.StockPrice, subscriptions.StockPriceTarget) FROM StockPriceChange events JOIN StockPriceHitsTarget subscriptions ON events.StockSymbol =
subscriptions.StockSymbol WHERE events.StockPrice >=
subscriptions.StockPriceTarget </Action> <EventClassName>StockPriceChange</EventClassName> </EventRule> </EventRules> </SubscriptionClass> </SubscriptionClasses> <NotificationClasses> <NotificationClass> <NotificationClassName>StockAlert</NotificationClassName> <Schema> <Fields> <Field> <FieldName>StockSymbol</FieldName> <FieldType>nvarchar(10)</FieldType> </Field> <Field> <FieldName>StockPrice</FieldName> <FieldType>decimal(10,2)</FieldType> </Field> <Field> <FieldName>StockPriceTarget</FieldName> <FieldType>decimal(10,2)</FieldType> </Field> </Fields> </Schema> <ContentFormatter> <ClassName>XsltFormatter</ClassName> <Arguments> <Argument> <Name>XsltBaseDirectoryPath</Name> <Value>%_ApplicationBaseDirectoryPath_%</Value> </Argument> <Argument> <Name>XsltFileName</Name> <Value>StockAlert.xslt</Value> </Argument> </Arguments> </ContentFormatter> <Protocols> <Protocol> <ProtocolName>File</ProtocolName> </Protocol> </Protocols> </NotificationClass> </NotificationClasses> <Providers> <HostedProvider> <ProviderName>StockEventProvider</ProviderName> <ClassName>FileSystemWatcherProvider</ClassName> <SystemName>%_NSServer_%</SystemName> <Arguments> <Argument> <Name>WatchDirectory</Name> <Value>%_ApplicationBaseDirectoryPath_%\ EventsWatchDirectory</Value> </Argument> <Argument> <Name>SchemaFile</Name> <Value>%_ApplicationBaseDirectoryPath_%\ StockPriceChangeEventSchema.xsd</Value> </Argument> <Argument> <Name>EventClassName</Name> <Value>StockPriceChange</Value> </Argument> </Arguments> </HostedProvider> </Providers> <Generator> <SystemName>%_NSServer_%</SystemName> </Generator> <Distributors> <Distributor> <SystemName>%_NSServer_%</SystemName> </Distributor> </Distributors> </Application>
Schemas and Logic
This section describes the ADF syntax used to define the event, subscription, and notification schemas. This section also shows how the matching logic, expressed as a SQL join, is specified in the ADF.
Event Schemas
For each type of event that the application receives (there can be more than one), you must declare an event class in the <EventClasses> element of the ADF.
The stock application uses only a single type of event: a change in the trading price of a stock. Within the <EventClasses> element of the ADF, you declare an <EventClass> for this type of event, as shown in Listing 3.2.
Listing 3.2 Declaration of the StockPriceChange Event Class
<EventClass> <EventClassName>StockPriceChange</EventClassName> <Schema> <Field> <FieldName>StockSymbol</FieldName> <FieldType>nvarchar(10)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>StockPrice</FieldName> <FieldType>decimal(10,2)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> </Schema> </EventClass>
The declaration provides a name, StockPriceChanges, for the event class and a schema for the event data. The schema declaration should look familiar to you if you've built database schemas before: The syntax is basically an XML version of a SQL CREATE TABLE statement. It declares a set of fields, specifying a data type and, optionally, a type modifier (such as not null) for each.
When building the application's database, the SQL-NS compiler processes this event class declaration and constructs the underlying table that ultimately stores the events. It creates a column for each of the fields declared and adds some other columns used for internal tracking.
Subscription Schemas
Just as you have to declare an event class for each type of event the application receives, you also have to declare a subscription class for each type of subscription that the application supports. This simple stock application has only one type of subscription: a request to be notified when a stock hits a particular price target. In the completed ADF, this subscription class declaration goes within the <SubscriptionClasses> element. Listing 3.3 shows the subscription class declaration.
Listing 3.3 Declaration of the StockPriceHitsTarget Subscription Class
<SubscriptionClass> <SubscriptionClassName>StockPriceHitsTarget</SubscriptionClassName> <Schema> <Field> <FieldName>StockSymbol</FieldName> <FieldType>nvarchar(10)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>StockPriceTarget</FieldName> <FieldType>decimal(10,2)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> </Schema> <EventRules> ... </EventRules> </SubscriptionClass>
The subscription class declaration has three subelements: a name, schema, and set of event rules (content not shown in the fragment in Listing 3.3). The schema subelement defines the size and shape of the subscription data. We've said that subscriptions of this class will have the form
"Notify me when the price of stock S goes above price target T."
The subscription data for each subscription then just consists of values for S and T. In the subscription class schema, we've given these fields the more descriptive names StockSymbol and StockPriceTarget and provided their data types and type modifiers.
The rules section, declared in the <EventRules> subelement, contains logic that matches events with subscriptions. This is discussed in the "Matching Logic" section.
Notification Schemas
The <EventClasses> and <SubscriptionClasses> elements define the schemas for the application's events and subscriptions. When the matching logic is applied, the result is a set of notification data that represents the notifications to be sent to subscribers. This notification data has a schema as well, and must be declared in the <NotificationClasses> element of the ADF. This section can contain schema definitions for several types of notifications, but because this example stock application just sends one type of notification, there is just a single <NotificationClass> declaration, shown in Listing 3.4.
Listing 3.4 Declaration of the StockAlert Notification Class
<NotificationClass> <NotificationClassName>StockAlert</NotificationClassName> <Schema> <Fields> <Field> <FieldName>StockSymbol</FieldName> <FieldType>nvarchar(10)</FieldType> </Field> <Field> <FieldName>StockPrice</FieldName> <FieldType>decimal(10,2)</FieldType> </Field> <Field> <FieldName>StockPriceTarget</FieldName> <FieldType>decimal(10,2)</FieldType> </Field> </Fields> </Schema> <ContentFormatter> ... </ContentFormatter> <Protocols> ... </Protocols> </NotificationClass>
Much like in the event and subscription class declarations, the <Schema> element provides the names and data types of the notification fields. The <ContentFormatter> section describes how the notification data is formatted for receipt by the subscriber, and the <Protocols> section declares which delivery protocols can be used to actually send the notifications. The <ContentFormatter> and <Protocols> elements are discussed in later chapters.
The StockAlert notification schema has three fields: the stock symbol, stock price, and stock price target. Each row of notification data produced by the matching join contains a value for each of these fields. The stock price field contains the current price of the stock (as indicated by the stock event), and the stock price target field contains the price target specified in the subscription. From this notification data, the application can synthesize formatted stock alert messages such as
"XYZ is now trading at: $55.55. This is greater than or equal to the target price of $50.00."
for delivery to the subscribers.
Matching Logic
The stock application's matching logic is specified in the <EventRules> section of the subscription class. This section contains one or more SQL statements that get executed when events arrive. Each of these SQL statements is called a rule and is declared in an <EventRule> element. Each rule specifies a name, an action (the SQL code that gets executed), and the name of the event class that triggers it.
Listing 3.5 shows the <EventRules> element of the StockPriceHitsTarget subscription class. It declares a single rule, MatchStockPricesWithTargets, that simply matches incoming events with StockPriceHitsTarget subscriptions. The <EventClassName> element of the rule declaration specifies the name of the triggering event class, in this case, StockPriceChange. This instructs the SQL-NS execution engine that, whenever events of the StockPriceChange event class arrive, this event rule must be fired.
Listing 3.5 Event Rules Declaration Within the StockPriceHitsTarget Subscription Class
<EventRules> <EventRule> <RuleName>MatchStockPricesWithTargets</RuleName> <Action> ... </Action> <EventClassName>StockPriceChange</EventClassName> </EventRule> </EventRules>
The matching logic is really specified in the rule's <Action> element. Listing 3.6 shows the contents of the <Action> element.
Listing 3.6 The SQL Matching Logic from the Event Rule's <Action> Element
SELECT dbo.StockAlertNotify( subscriptions.SubscriberId, N'DefaultDevice', N'en-US', events.StockSymbol, events.StockPrice, subscriptions.StockPriceTarget) FROM StockPriceChange events JOIN StockPriceHitsTarget subscriptions ON events.StockSymbol = subscriptions.StockSymbol WHERE events.StockPrice >= subscriptions.StockPriceTarget
This logic is just a SQL join that produces a set of rows that become notification data.
The first thing to look at in this join is the FROM clause. It selects from StockPriceChange joined with StockPriceHitsTarget. Note that these are just the names of the event class and subscription class declared earlier. SQL-NS allows you to use these names directly in the join statement, as though they were SQL tables containing the event and subscription data. StockPriceChange is given the table alias events, and StockPriceHitsTarget is given the table alias subscriptions for clarity. (The names "events" and "subscriptions" are not mandated by SQL-NS; you may use any aliases you want.) Note that the join is on the stock symbol field.
StockPriceChange and StockPriceHitsTarget are not in fact tables, but rather views that SQL-NS sets up at runtime. These views contain just the data against which the rule should operate: The events view contains only the events just submitted that have triggered the rule firing, and the subscriptions view contains only the active subscriptions. (In this simple application, all subscriptions are active, but you'll see in Chapter 6, "Completing the Application Prototype: Scheduled Subscriptions and Application State," and Chapter 7, "The SQL-NS Subscription Management API," that subscriptions can be disabled or scheduled to fire only at certain times.)
The WHERE clause of the join defines a filter that selects only the rows in which the stock price in the event is greater than or equal to the stock price target in the subscription. Note that the XML escape sequence, > is used in place of the > character because this statement appears within an XML document. Use of the > character directly would prevent the document from being well-formed XML.
The join defined in the FROM clause, along with the filter defined in the WHERE clause, implement the matching criteria; the rule defines what it means for an event to match a subscription. (The stock symbols must be the same, and the stock price greater than or equal to the price target.)
The SELECT clause of the join statement calls a function, StockAlertNotify(). It passes this function a set of arguments whose values come from the joined data. Among these arguments are values for the fields in the notification class. The StockAlertNotify() function is provided by SQL-NS: It is created specifically for the notification class, when the notification class is compiled. In fact, SQL-NS creates one such function per notification class, named <NotificationClassName>Notify(). In this application, the notification class name is StockAlert, so the name of the function is StockAlertNotify(). In SQL-NS terms, the function created for any notification class is referred to as the notify function.
Calling the notify function for a particular notification class causes a notification of that notification class to be queued for delivery. Calling the StockAlertNotify() function indicates that the result of a match by this rule should be a StockAlert notification. Internally, the notify function writes the data it is passed into a notification table and updates the internal SQL-NS state to tell the distribution components that the notification data is ready to be formatted and delivered.
The first three parameters to the notify function, as shown in Listing 3.6, are always the same. They are
-
The ID of the subscriber to receive the notification. (Note that even though the subscriber ID is not a declared field in the subscription class schema, it is always present in the subscription class view.)
-
The name of the subscriber's device to which the notification should be sent. (Note that subscriber devices are discussed in more detail in Chapter 7 and Chapter 10, "Delivery Protocols.")
-
The locale for which the notification data should be formatted.
The additional parameters to the notify function are obtained from the fields in the notification class declaration. There is one parameter for each declared field, with the same name and data type as that field. Values of these parameters in the notify function call become values for the corresponding notification fields. If you look at the call to the StockAlertNotify() notify function in Listing 3.6, you'll see that the subscriber ID is obtained from the subscription, the device name and locale are constants, two of the notification fields come from data in the event, and the third comes from data in the subscription.
Component Configuration and the Phases of Processing
Previous sections examined the event, subscription, and notification schemas and the associated rule logic. This section examines the component configuration elements of the ADF. Specifically, these include the <Providers>, <Generator>, and <Distributors> elements.
Each of the components configured in the ADF plays a specific role in the functioning of the application. Before looking at these components and how they're configured, it's important to understand the processing that happens inside a notification application.
SQL-NS separates the functions of a notification application into separate processing phases (see Figure 3.6):
-
Event collectionGathering events and submitting them to the application
-
Subscription managementCreation, deletion, and alteration of subscriptions
-
GenerationMatching events with subscriptions
-
DistributionRouting notifications to delivery systems
Each phase is considered independent and is handled by a separate component in the execution engine. The SQL-NS engine may run all these phases concurrently: While one batch of events is being collected, another may be being matched with subscriptions, and an older batch of notifications may be being distributed. The following subsections describe these phases of processing and the components that execute them.
Figure 3.6 Phases of processing in notification applications.
Event Collection
Event providers are components that collect events and submit them to notification applications. Event providers can gather event data from the outside world proactively, or act as sinks to which external event sources push information. For example, an event provider could be a component that constantly polls an external data source, or it could be a Web service that receives data passed to it by external callers.
An application can have several event providers, each potentially submitting events from a different event source. In the <Providers> element of the ADF, you declare which event providers your application will use and configure the options that control their operation.
SQL-NS provides several built-in event providers that can be used in an application without you having to write any code. You can also build your own event provider for your application that talks to a custom event source. Chapter 7 provides details on all the built-in event providers, as well as the process of building a custom event provider for your application.
Listing 3.7 shows the <Providers> element from the stock application's ADF.
Listing 3.7 The Event Provider Configuration in the ADF
<Providers> <HostedProvider> <ProviderName>StockEventProvider</ProviderName> <ClassName>FileSystemWatcherProvider</ClassName> <SystemName>%_NSServer_%</SystemName> <Arguments> <Argument> <Name>WatchDirectory</Name> <Value>%_ApplicationBaseDirectoryPath_%\ EventsWatchDirectory</Value> </Argument> <Argument> <Name>SchemaFile</Name> <Value>%_ApplicationBaseDirectoryPath_%\ StockPriceChangeEventSchema.xsd</Value> </Argument> <Argument> <Name>EventClassName</Name> <Value>StockPriceChange</Value> </Argument> </Arguments> </HostedProvider> </Providers>
This application uses a single hosted event provider to submit stock events. This event provider is declared and configured in a <HostedProvider> element. The configuration specifies a name for the event provider, the event provider class (which identifies a particular event provider implementation), the server on which it should run, and a set of runtime arguments that it gets passed at startup time.
NOTE
A hosted event provider is one that runs within the SQL-NS Windows Service; an application can also use nonhosted event providers that run as part of a standalone process. Chapter 8, "Event Providers," offers more details on both types and describes when it's appropriate to use each one.
The event provider class name tells the SQL-NS engine which event provider implementation to use. The class name can be either the name of a class that you implement (as described in Chapter 8) or the name of a "built-in" event provider class, provided by SQL-NS. In this case, the stock application uses the built-in event provider class called the FileSystemWatcher. This event provider works by monitoring a directory in the filesystem; when it sees a new XML file added to that directory, it opens the file and submits the data in it as events.
Subscription Management
Most notification applications provide users with a visual interface by which they can manipulate and manage their subscriptions. The form of this interface varies depending on the nature of the pub-sub system. A stock quote application might provide a Web site that a user can use to enter a subscription. A line-of-business application might provide a way to subscribe for notifications directly in its standard Windows user interface.
Whatever the form of the external interface, the implementation of the subscription management system uses an API provided by SQL-NS to insert subscriptions into the notification application. This API also provides facilities for modifying or deleting subscriptions.
There is no subscription management configuration in the ADF. The ADF just contains declarations of subscription classes, but the systems by which subscriptions of those subscription classes are created and submitted to the application are treated as standalone entities that are not configured in the ADF.
Generation
Generation is the phase during which events are matched with subscriptions to produce notifications. The generation component of an application is provided by SQL-NS, but the logic it uses to determine whether a particular event matches a subscription is provided by the application developer.
SQL-NS exposes a variety of options for controlling how generation occurs, including customizing batch sizes and specifying how and when matching logic is applied. These are configured in the ADF in the <Generator> element. Listing 3.8 shows the generator configuration for the stock application.
Listing 3.8 The Generator Configuration in the ADF
<Generator> <SystemName>%_NSServer_%</SystemName> </Generator>
In this simple example, the only configuration option supplied is the system name, which tells the SQL-NS engine on which machine the generator should run. Chapters 11 and 12 describe more options you can use to fine-tune generator operation.
Distribution
The distribution phase handles the formatting of notification data appropriately for a variety of delivery devices (email, cell phones, pagers, and so on) and the routing of those formatted notifications to delivery systems that get them to their final destinations.
An application can have one or more distributors, possibly running on different servers. These are configured in the ADF's <Distributors> element. Listing 3.9 shows the <Distributors> element from the stock application's ADF.
Listing 3.9 The Distributor Configuration in the ADF
<Distributors> <Distributor> <SystemName>%_NSServer_%</SystemName> </Distributor> </Distributors>
The stock application uses only one distributor, and this is declared in the single <Distributor> element. Again, because this is a simple example, the only configuration option specified is the system name: the computer on which the distributor should run. Chapter 12 describes additional distributor configuration options that you can specify.
CAUTION
If you are using the Standard Edition of SQL-NS, the event providers, generator, and distributor in your application must run on the same machine. This means that the <SystemName> element must have the same value in all the event provider, generator, and distributor declarations in your ADF. If you specify different system name values with SQL-NS Standard Edition, you'll get an error when compiling your application.
If you are using the Enterprise Edition of SQL-NS, you may configure the various components to run on different machines by specifying different values for the <SystemName> elements. This allows your application to scale out for better performance. Chapter 13 covers the additional configuration steps (beyond specifying system names in the ADF) required to enable a scale out deployment.
Specifying Other Parts of the Stock Application
The ADF defines the core of the application, but several other pieces are required to make it run. This section briefly describes those pieces as they pertain to the stock application.
The Instance
SQL-NS has the concept of instances, in much the same way that SQL Server does. A SQL-NS instance is a single, named configuration of SQL-NS that can host one or more applications. Each instance is an independent entity that can be started, stopped, and configured on its own. In SQL-NS, an instance is defined by an Instance Configuration File (ICF). The ICF defines the list of applications in the instance (here, just one, the stock application) and the set of delivery channels that applications in the instance can use to send notifications.
This sample's ICF creates an instance called "Chapter03". I've included the text of the ICF in Listing 3.10, but I will not go into an explanation of it here because most of the details are not relevant at this point. Chapter 4, "Instances and the Instance Configuration File," covers the instance concepts and all the elements of the ICF in detail.
Listing 3.10 The ICF That Defines the SQL-NS Instance That Hosts the Stock Application
<?xml version="1.0" encoding="utf-8"?> <NotificationServicesInstance xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ ConfigurationFileSchema"> <InstanceName>%_InstanceName_%</InstanceName> <SqlServerSystem>%_SQLServer_%</SqlServerSystem> <Applications> <Application> <ApplicationName>Stock</ApplicationName> <BaseDirectoryPath>%_InstanceBaseDirectoryPath_%\ Stock</BaseDirectoryPath> <ApplicationDefinitionFilePath>ApplicationDefinition.xml </ApplicationDefinitionFilePath> <Parameters> <Parameter> <Name>_NSServer_</Name> <Value>%_NSServer_%</Value> </Parameter> <Parameter> <Name>_ApplicationBaseDirectoryPath_</Name> <Value>%_InstanceBaseDirectoryPath_%\Stock</Value> </Parameter> </Parameters> </Application> </Applications> <DeliveryChannels> <DeliveryChannel> <DeliveryChannelName>FileChannel</DeliveryChannelName> <ProtocolName>File</ProtocolName> <Arguments> <Argument> <Name>FileName</Name> <Value>%_InstanceBaseDirectoryPath_%\Output\ Notifications\FileNotifications.txt</Value> </Argument> </Arguments> </DeliveryChannel> </DeliveryChannels> </NotificationServicesInstance>
Event Data
If this were a real application, it would use an event provider that would read and submit real stock data from one of many stock market data publishers. Because this is a sample application, and its purpose is to illustrate the core SQL-NS concepts, not the mechanics of reading from stock market data sources, we'll just submit some sample event data from a file. Chapter 8 covers how to build a real event provider and get events from real sources.
As mentioned in the "Event Collection" section, we'll use the built-in FileSystemWatcher event provider supplied by SQL-NS to get events into the application. The declaration of the FileSystemWatcher event provider shown in Listing 3.7 included a set of runtime arguments. These arguments tell it which filesystem directory to watch and provide a schema for the XML data files that will be dropped in that directory. For this simple stock example, we'll use a sample stock data file that contains a few stock events. Each event specifies a stock symbol and stock price, the fields required by the event class (as you saw in Listing 3.2). You can take a look at a sample event file to see what the data looks like:
NOTE
All instruction sets in this and future chapters that involve command-line operations begin with a step that instructs you to open a command prompt. This instruction is repeated each time just to ensure that every instruction set is self-contained. You can certainly re-use the same command prompt across different instruction sets if you choose.
-
Open a Notification Services Command Prompt on your development machine, and navigate to the sample data directory by typing the following command
-
Open the file EventData.xml in Notepad (or your XML editor):
cd /d C:\SQL-NS\Chapter03\Stock\SampleData
notepad EventData.xml
To submit a batch of events, simply copy this sample data file into the events watch directory.
Entering Subscriptions
Because this is just a sample, we won't build a full subscription management system (doing so would mean building either a Web application or a Win32 application, both of which would distract attention from the core SQL-NS concepts). Instead, we'll just use some VBScripts to enter subscriptions. Internally, these scripts actually use the same APIs that a real subscription management system would use; they just don't provide a fancy user interface.
NOTE
I'm not going to describe any of the details of the subscription management API until Chapter 7, but if you're curious, you can follow these instructions to view the code in the VBScripts we'll be using in this chapter:
-
Open a Notification Services Command Prompt on your development machine and navigate to the Chapter 3 scripts directory by typing the following command:
-
Open the file AddSubscribers.vbs in Notepad using the following command (or you can use another editor if you prefer). As the name suggests, this is the VBScript that adds a set of subscribers to the instance.
-
Go back to the command prompt and navigate to the Chapter03\ Stock\SampleData subdirectory using the following command:
-
Open the file AddStockSubscriptions in Notepad using the following command (or use any other editor if you prefer). This is the VBScript that adds a set of stock subscriptions to the application.
cd /d C:\SQL-NS\Chapter03\Scripts
notepad AddSubscribers.vbs
cd ..\Stock\SampleData
notepad AddStockSubscriptions.vbs
Seeing the Final Notifications
After raw notification data is generated, it gets formatted and then delivered. In this sample, we'll use the built-in XsltFormatter content formatter provided by SQL-NS that turns the raw notification into a readable message using an XSL transform. Even though the XSLT formatter is often used in real SQL-NS applications, I'll defer explaining how it works until Chapter 9, "Content Formatters."
On the delivery side, this sample application uses another SQL-NS built-in component: the File Delivery Protocol. This component "delivers" the notifications to a file. You can then check the output of the application simply by opening this file. In a real application, you'd probably use several different delivery protocols that could send the notification messages via email, or as text messages to cell phones, for example. Chapter 10 describes all the built-in SQL-NS delivery protocols and shows how you can build your own custom delivery protocol.
Running the Stock ApplicationIn this section, we'll compile, register, and enable the application; start its engine running; and then feed it sample data. We'll submit events and subscriptions as described earlier and then observe the notification data written to the output file.
Assuming that your development environment is set up as described in Chapter 2, you can run the provided scripts to get the application going. Chapter 4 goes into the details of what these scripts actually do.
NOTE
If you're using SQL Server authentication, many of these scripts prompt you for the SQL password of the development account you set up in Chapter 2.
-
Open a Notification Services Command Prompt on your development machine and navigate to the Chapter 3 scripts directory by typing the following command:
-
Run create.cmd.
-
Run register.cmd. You will be prompted for the password of the Windows account you created in Chapter 2 for running the SQL-NS engine. (Note that this is the password of the Windows account, not the SQL user you created if you're using SQL Server authentication.) If you're using SQL Server authentication, after entering the password for the Windows account, you will then be prompted for the SQL password of the test account you created in Chapter 2.
-
Run grant_permissions.cmd. If you're using SQL Server authentication, you will be prompted for the password for your SQL Server's system administrator account.
-
Run enable.cmd.
-
Start the SQL-NS engine using the following command. When you completed the register command in step 3, SQL-NS installed a Windows Service that hosts the execution engine for this instance. The service for any instance is always named NS$<InstanceName>, so for this instance, the service is named NS$Chapter03.
cd /d C:\SQL-NS\Chapter03\Scripts
net start NS$Chapter03
At this point, the application should be running, and we can start submitting subscriptions and events. If your service does not start correctly, refer to Chapter 15, "Troubleshooting SQL-NS Applications," for suggestions on how to diagnose the problem.
We'll use a pair of VBScripts that use the SQL-NS APIs to enter subscriber and subscription data. I've provided a batch file that invokes these VBScripts with the appropriate arguments. Chapter 7 examines the details of the API that these scripts employ.
-
Open a Notification Services Command Prompt on your development machine and navigate to the Chapter 3 scripts directory by typing the following command:
-
Run add_subscriber_subscriptions.cmd. If you're using SQL Server authentication, you will be prompted for the SQL password of the test account you set up in Chapter 2.
cd /d C:\SQL-NS\Chapter03\Scripts
For events, remember that this application uses the built-in FileSystemWatcher event provider, which watches a specific directory for new data files. It has been configured to watch the Stock\EventsWatchDirectory directory, so we'll copy a data file into this folder to kick things off. In the Stock\SampleData directory, a file called EventData.xml contains some sample stock price change events we can use for this purpose (you looked at this file earlier in this chapter).
-
Open a Notification Services Command Prompt on your development machine and navigate to the sample data directory by typing the following command:
-
Copy the file EventData.xml into the directory being watched for events using the following command:
cd /d C:\SQL-NS\Chapter03\Stock\SampleData
copy EventData.xml ..\EventsWatchDirectory
If you look at the Stock\EventsWatchDirectory folder, you'll see that after the event provider picks up the file (this should happen within a few seconds of copying the file into the directory), the file gets renamed to eventdata.xml.<timestamp>.done where <timestamp> represents the date and time the file was processed.
The event data in the sample file just submitted is designed to match some of the subscriptions added through the VBScript, so this data should generate notifications. The generation process may take up to a few minutes, because the generator repeatedly looks for new batches of events on a 1-minute interval. (This is, of course, configurable, and Chapter 12 describes how to change this setting.)
This application uses the built-in SQL-NS file delivery protocol, so the notification data will simply be written to a text file. The delivery protocol is configured to write notifications to a file called FileNotifications.txt in the Chapter03\Output\Notifications directory. Within a few minutes, this file should appear in this folder. Open it to see the text of the notifications generated for the sample events and subscriptions just submitted.
Inside the Running Application
When the SQL-NS compiler processed the ADF, it created a database structure from the information in it. When we registered the instance, SQL-NS installed a Windows Service to host its execution engine. These two pieces, the database and the Windows Service, make up the running application. This section examines both to illustrate how the application really works.
The Database
Connect to your SQL Server using Query Analyzer (or any other tool that lets you browse database objects):
-
Open Query Analyzer (from your Start menu, choose Programs, Microsoft SQL Server, Query Analyzer).
-
Log in to your SQL Server.
-
If the Object Browser (the tree view showing all the database objects on your SQL Server) is not showing, press F8 to make it visible.
In the Object Browser, observe that two new databases have been added: Chapter03NSMain and Chapter03Stock. The first is referred to as the instance database; it stores all the information shared among applications in the instance. SQL-NS always names the instance database <InstanceName>NSMain. Chapter03Stock is the application database and contains all the tables, views, stored procedures, and functions required to run the stock application. SQL-NS names application databases <InstanceName><ApplicationName>. So, for every instance, the SQL-NS compiler creates an instance database and one application database for each application within it.
For now, we'll ignore the instance database and focus on the application database (Chapter03Stock). If you expand the Chapter03Stock node in the Object Browser, you'll see many tables, stored procedures, and views (under the "User Tables", "Stored Procedures", and "Views" sub-nodes respectively). Many of these are just for internal use by the SQL-NS engine, so we won't cover them here. But I do want to show you a handful of these to give you an idea of what the compiler created from the information in the ADF.
Look for a table in the Chapter03Stock database called NSStockPriceChangeEvents. Expand the NSStock PriceChangeEvents node in the Object Browser tree and then expand the "Columns" sub-node beneath it. Looking at the columns in this table, you'll see that all the fields from the StockPriceChange event class declaration have become columns in this table. SQL-NS has also added its own extra columns that it uses for internal tracking. This table is used to store events and was created by the compiler from the event class definition. Similarly, you'll see a table called NSStockPriceHitsTargetSubscriptions that matches the schema of the StockPriceHitsTarget subscription class and is used to store subscriptions of that class. As you might expect, there is also an NSStockAlertNotifications table that stores StockAlert notification data.
Close the "User Tables" node under Chapter03Stock in the Object Explorer tree and expand the "Stored Procedures" node. Look at the NSFire1 stored procedure. You can right-click on it in the tree and choose Edit to see the text. This stored procedure contains some SQL-NS generated code but also contains the text of the <Action> element in your match rule declaration in the ADF. Look for a comment that says /** APPLICATION DEFINED RULE STARTS HERE **/. Immediately after this comment, you'll see your rule text. SQL-NS has compiled your rule into this stored procedure. The code before and after the rule text sets up and tears down the internal SQL-NS state required to run your rule. What this wrapper code actually does is not important to understand; it's all related to SQL-NS internals, which, as an application developer, you are insulated from.
The Windows Service
In addition to the tables and stored procedures just examined, the SQL-NS compiler also stores the component configuration information from the ADF in the database. On startup, the Windows Service connects to the database and reads this configuration. This includes the number and type of event providers, as well as the configuration of the generator and distributors. Using the configuration information, the service instantiates the appropriate execution components and starts them running.
In the case of our stock application, we had only one event provider: the FileSystemWatcher. Based on this configuration information, the service created an instance of the FileSystemWatcher component and passed it the startup parameters specified in the ADF. This event provider monitored the filesystem directory we specified, and when we copied an event data file into this directory, it read it and submitted the data as events. These events ended up in the events table in the database.
Our application had a single generator with the default configuration. On startup, the Windows Service instantiated a generator component that periodically checked the database for new batches of events. When our FileSystemWatcher event provider wrote events to the database, the generator processed them by executing the stored procedures that contained our rule logic. This resulted in notification data stored in the notification tables.
The single distributor we configured in our ADF was also represented by a running component in the Windows Service, instantiated on startup. This component periodically checked the database for new notifications to format and deliver. When the generator firing caused the notifications to be written to the notifications table, the distributor read them, passed them through the content formatter we specified, and then wrote them to the output file using the file delivery protocol.
Figure 3.7 shows the Windows Service and its interactions with the database.
Figure 3.7 The running Windows Service interacting with the database.
What Has the SQL-NS Platform Provided?
In its final form, the the notification application is a database application coordinated by a Windows Service. To build the application, we had to write a relatively small amount of code. In fact, all we provided were the schemas for events, subscriptions, and notifications; the matching logic; and component configuration. From this, SQL-NS constructed the database application, including the tables, views, and stored procedures, for us.
In the previous section, we examined the parts of the database that resulted from the information specified in the ADF. We did not look into the multitude of other supporting tables, views, stored procedures, and functions required by the running application. These include code that maintains state about the application and enables it to recover gracefully after an unplanned shutdown, support for garbage collection of old event and notification data that has been completely processed, and several reporting and debugging utilities that can provide insight into what the running application is actually doing. All these were created for us, and we do not have to understand how they are implemented.
Many other benefits of building this application on SQL-NS have not been visible because we've run the application on such a small scale. But in fact, the application as it stands will support large volumes of events and subscriptions. (The exact number depends on the hardware you're using, but even on a modest developer system, it's possible to run applications with tens of thousands of events and subscriptions.)
Apart from the supported data volume, another aspect of the application that has not been visible because of the limited way in which we've used it is its manageability and reporting capabilities. For example, using simple SQL-NS tools, we could disable certain components of the application while leaving others running. This would allow us to perform incremental hardware and software maintenance without a total shutdown of the system. We could also obtain reports about the exact numbers of events and subscriptions going through the system. Chapter 14, "Administering Notification Applications," examines how to do these tasks, but even in this simple application, these capabilities are present because of what the SQL-NS platform has provided.
Furthermore, the application can be maintained and extended easily. If we wanted to, say, add a new type of event or a new subscription class, all we would have to do is add the necessary schema and logic to the ADF and then run SQL-NS tools to update the application (Chapter 5 introduces the tools and techniques for doing this). Updating the application would automatically modify existing database entities and create the new ones necessary to support the extensions. If we were building the application from scratch, this type of extension would likely be much more difficult.
If you were building the application from scratch, you would also have to do vast amounts of testing to ensure the reliability and security of the execution engine. Using SQL-NS, you can be sure that the execution engine has been designed with reliability and security in mind, and has already gone through rigorous testing in these areas.
In summary, although building just the core functionality of the application from scratch might not take that much longer than it does with SQL-NS, all the supporting features that make the application truly rich would be time consuming to build and difficult to get just right. SQL-NS provides these capabilities for free. Using SQL-NS reduces the time to market for your application and increases its reliability, security, and scalability.
Extending the Stock Application
Although the core of the application built here looks much like it would if this were a real application, its inputs and outputs do not. We have been reading events from hard-coded event files, entering subscriptions through administrative scripts, and looking at notification output through text files.
To make this application real, we would have to add a real event provider that read from a real event source. We would build a full subscription management interface to allow users to manage their subscriptions, and we would add support for delivery to real devices. Later chapters show how to do all these tasks.
Apart from input and output pieces, there is work we would do on the core of the application to make it more real. One limitation of the way the application works today is that it stores no state. So, subscribers get notified of every stock price change that is above the price target value they specified in their subscription. If I had a subscription for XYZ stock with a price target of $50, I'd get notified when the stock hit $51, then again if it fluctuated to $51.25, again if it went back down to $51, and so on. For any user, this flood of notifications would quickly become annoying.
To deal with this, the application could store state about the last price I was notified of. It could then temporarily suspend notifications to me until the price went significantly above this value so that I would not be bothered by intermediate fluctuations. Maintaining state is a core part of the SQL-NS application model, and Chapter 6 covers this in more detail.
Also, all the subscriptions right now are event triggered. That is, they fire in response to an incoming event. We could also support scheduled subscriptions: subscriptions that fire at a particular time of day. This would allow me, for example, to enter a subscription that said, "Notify me of the price of stock S at 5:00 p.m. every weekday."
This type of subscription does not fire in response to any given stock price change event. Rather, stock price events come in continuously during the day, and the application would have to keep track of the latest price for XYZ. At 5:00 p.m., it would send me a notification message containing the latest price. This type of subscription is also supported directly in the SQL-NS application model, and in Chapter 6, you'll see how to implement it.
Cleaning Up the Instance and Application
When you're finished examining the instance and application created in this chapter, you can use the following steps to remove them from your system. These steps clean up the databases, registry keys, and other settings associated with the instance and application.
-
Open a Notification Services Command Prompt on your development machine and navigate to the Chapter 3 scripts directory by typing the following command:
-
Stop the service.
-
Close any Query Analyzer windows you still have connected to the instance or application databases.
-
Run delete.cmd. If you're using SQL Server authentication, you will be prompted for the SQL username and password for the development account you created in Chapter 2. If you're using Windows authentication, you will not be prompted for a username and password. The delete command will ask for confirmation before proceeding[md] enter y to confirm the operation.
-
Run unregister.cmd.
cd /d C:\SQL-NS\Chapter03\Scripts
net stop NS$Chapter03
Summary
In the SQL-NS application model, both events and subscriptions are just data. SQL-NS uses SQL joins to match the events and subscriptions, and because SQL Server can usually execute these efficiently, notification applications built to this model tend to scale well.
Building an application on SQL-NS involves writing an ADF: an XML document that describes the schemas, logic, and component configuration of the application. This chapter described the ADF for a sample application: one that sends stock price updates to subscribers. The information in this chapter should give you a feel for SQL-NS applications and what's involved in building them. Later chapters provide the details and teach you how to build your own applications.