Building the Music Store Application with User-Defined Matching Logic


In this section, we look at a working example of a SQL-NS application based on user-defined logic. The application is a modified version of the music store sample we developed in previous chapters. It provides the same type of functionalitynotifications about new songs added to the music store catalogbut allows its subscribers to define their own matching criteria. Unlike the previous version of the application, in which the developer-defined matching logic supports only subscriptions based on artist name or genre, the version of the application we build in this chapter lets subscribers create subscriptions with arbitrary matching logic based on song properties.

The complete code for this new version of the music store sample is located in the directory C:\SQL-NS\Samples\UserDefinedLogic. The files in this directory are organized into a similar structure as the ones in the previous music store sample. In the sample directory, you'll find an ICF, ADF, and supporting build scripts. The sample directory also contains a Visual Studio solution with the source code for a program that adds subscriptions to the application using the Condition API described in the previous section. In the sections that follow, we take a detailed look at the application's code in the ADF, the program that adds subscriptions, and the scripts used to configure the security context in which the application executes user-defined logic.

Defining the Sample Application

The music store application was first defined in Chapter 5. The version of the application we create in this chapter is simply a modification of this previous version. Much of the code in the ADF is the same across both versions, so if you've read through Chapter 5, you'll already be familiar with much of the code in this chapter's ADF. This section highlights the changes to the ADF that were made to accommodate user-defined logic.

In Chapter 5, the application was built incrementally as each new SQL-NS concept was introduced. Because you're probably familiar with all the basic SQL-NS concepts by now, this chapter's sample is presented in a completed form. The various sections of the ADF code are discussed one at a time in the following sections, but we do not build the application incrementally.

This chapter's sample application is presented in prototype form, at about the same stage of maturity as the prototype at the end of Chapter 5: its basic functionality works, but it isn't connected to real event sources or delivery systems. The techniques covered in Chapters 6 through 10 for advancing a prototype toward production readiness could be applied to the prototype developed in this chapter, as they were to the previous version.

The following are the key differences between this chapter's ADF and the one we had developed by the end of Chapter 5:

  • A new event chronicle has been added to serve as the input to user-defined subscription conditions.

  • The application defines a single subscription class that supports only user-defined matching logic.

  • The match rule in the subscription class is defined with a <ConditionAction> element.

We look at each of these differences in the subsections that follow.

Note

The ICF for this chapter's sample is essentially the same as the one used in Chapter 5. The only difference is in the default value defined for the _InstanceBaseDirectoryPath_ parameter. Because nothing substantive has changed in the ICF, it isn't discussed in detail in this chapter. If you're interested, you can examine the ICF by opening the file C:\SQL-NS\Samples\UserDefinedLogic\InstanceConfiguration.xml. Refer to Chapter 4, "Working with SQL-NS Instances," for detailed information on the ICF elements.


Creating the Input: The SongAddedDetails View

In the music store application, the events represent the addition of new songs to the music store catalog. In the original design, we chose to define the SongAdded event class with a single data field containing the song ID (see the section "Events," p. 135, in Chapter 5 for details). This minimized the duplication of data; because the music store catalog already stored all the song properties, it wasn't necessary to copy them into the event data. In the previous version of the application, we could easily obtain all the song properties by joining the event data with the music store catalog's SongDetails view in the developer-defined match rule.

When we move to user-defined logic, this events design is somewhat more troublesome. Conceptually, events are the inputs to the user-defined subscription conditions. We want our subscribers to be able to construct meaningful conditions over these inputs, but if the input data contains just the song IDs, this isn't really possible. Most interesting matching logic can be expressed only in terms of the other song properties (such as the song title, artist name, album title, and genre).

To deal with this situation, we have three options:

  1. Leave the design as it is and require that all subscription conditions be written as link leaf conditions that join the event data with the SongDetails view and then use user-defined filter rules over SongDetails to express the matching criteria.

  2. Abandon the goal of minimizing data duplication and expand the event class definition to include all the song properties.

  3. Define a new view to serve as the condition input. This view joins the events view with the SongDetails view and thus provides all the song properties of the songs referenced in the event data.

The first option is undesirable because of the complexity it would add to the development of both the application and its SMI. The second option is undesirable because of its negative performance implications. That leaves the third option, which requires a little extra code up front, but then allows the rest of the application development to proceed in a straightforward manner. In this section, we look at the ADF code required to implement this option.

Listing 18.1 shows the definition of a new chronicle, SongAddedDetails, in the SongAdded event class. This chronicle is the view that will be used as the input to our subscription conditions.

Listing 18.1. Definition of the SongAddedDetails View as an Event Chronicle

 <Application>   ...   <EventClasses>     <EventClass>       <EventClassName>SongAdded</EventClassName>       <Schema>         <Field>          <FieldName>SongId</FieldName>          <FieldType>INT</FieldType>          <FieldTypeMods>NOT NULL</FieldTypeMods>         </Field>       </Schema>       <Chronicles>         <Chronicle>           <ChronicleName>SongAddedDetails</ChronicleName>           <SqlSchema>             <SqlStatement>               -- Drop the view 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 = 'SongAddedDetails'                       AND so.type = 'U'                       AND sc.name = 'SongAlerts')               DROP VIEW [SongAlerts].[SongAddedDetails]             </SqlStatement>             <SqlStatement>               -- Create the view               CREATE VIEW [SongAlerts].[SongAddedDetails] AS               SELECT e.SongId AS SongId,                      sd.SongTitle AS SongTitle,                      sd.ArtistName AS ArtistName,                      sd.AlbumTitle AS AlbumTitle,                      sd.GenreName AS Genre,                      sd.DateAdded AS DateAdded               FROM   [SongAlerts].[SongAdded] e               JOIN   [Catalog].[SongDetails] sd                   ON e.SongId = sd.SongId             </SqlStatement>           </SqlSchema>         </Chronicle>       </Chronicles>     </EventClass>   </EventClasses>   ... </Application> 

The chronicle definition consists of two SQL statements. The first one drops the SongAddedDetails view if it already exists. (As in other chronicles you've seen, this handles the case in which updating the instance causes the chronicle statements to be rerun.) The second statement creates the view. Notice that the view definition is a join between the SongAdded events view and the SongDetails view in the music store catalog schema. The columns selected in the view definition include all the song properties.

This isn't really a chronicle in the traditional sense. Usually chronicles are tables and store data maintained by chronicle rules. The chronicle we've created here doesn't have any chronicle rules because it doesn't actually store any data that needs to be maintained. It is just a view over existing data. We've taken advantage of the fact that SQL-NS allows any valid T-SQL statements within a chronicle's definition. We've just used the chronicle as a convenient place to define the view and have the SQL-NS compiler create it at the time the application is compiled.

Because the schema of the new view, SongAddedDetails, is the same as the music store catalog's SongDetails view that existed previously, you might wonder why the new view is needed. The answer is about scoping. The SongDetails view returns data about all songs in the music store catalog. If our subscription conditions used this view as their source of input data, they would always be evaluated against every song in the music store catalog. Clearly, this is the wrong behavior.

The correct behavior is for the subscription conditions to be evaluated against only the song data for the SongAdded events being processed. At runtime, the SQL-NS engine takes care of populating the SongAdded events view with only the event data that each rule evaluation should see. Because the SongAddedDetails view joins with this events view, it always returns song data for only the in-scope events. Using this view as the input for the subscription conditions means that they are naturally evaluated against the correct data.

Defining the Subscription Class for Subscriptions with User-Defined Logic

In Chapter 5's version of the music store application, we defined a subscription class called NewSongByArtist to represent subscriptions by artist name. Later, we added a NewSongByGenre subscription class for subscriptions by genre. In the new version of the application, we don't define the matching criteria at development time so we don't need specialized subscription classes. Instead, we define a single, generic subscription class from which users can create subscriptions by artist, by genre, or by any other matching criteria they choose.

The definition of this subscription class, called NewSongsOfInterest, is shown in Listing 18.2.

Listing 18.2. Definition of the NewSongsOfInterest Subscription Class

 <Application>   ...   <SubscriptionClasses>     <SubscriptionClass>       <SubscriptionClassName>NewSongsOfInterest</SubscriptionClassName>       <Schema>         <Field>           <FieldName>DeviceName</FieldName>           <FieldType>NVARCHAR(255)</FieldType>           <FieldTypeMods>NOT NULL</FieldTypeMods>         </Field>         <Field>           <FieldName>Locale</FieldName>           <FieldType>NVARCHAR(10)</FieldType>           <FieldTypeMods>NOT NULL</FieldTypeMods>         </Field>       </Schema>       <EventRules>         <EventRule>           <RuleName>MatchNewSongsOfInterest</RuleName>           <ConditionAction>             ...           </ConditionAction>           <EventClassName>SongAdded</EventClassName>         </EventRule>       </EventRules>     </SubscriptionClass>   </SubscriptionClasses>   ... </Application> 

The old NewSongByArtist and NewSongByGenre subscription classes defined fields that were used in the developer-defined matching logic (ArtistName and GenreName fields, respectively). These aren't needed in our new subscription class because parameters like these can be defined directly in each user's condition logic. Instead, our subscription class defines two fields that aren't related to matching logic: DeviceName and Locale. These fields specify the name of the target subscriber device and locale for each subscription. In Chapter 5, our music store application didn't have these fields, but we added them in Chapter 9, "Content Formatters," when we added support for device and locale-specific formatting. We could have left these fields out of this chapter's sample because we don't do any device or locale-specific formatting, but they are included simply to illustrate that subscription classes based on user-defined logic can define fields.

Our subscription class declaration contains a single event rule, MatchNewSongsOfInterest, that is defined with a <ConditionAction> element. Notice that the SongAdded event class is specified as the triggering event class for this rule. The rule's <ConditionAction> element is described in detail in the following section.

Implementing the <ConditionAction> Element

Listing 18.3 shows the definition of the <ConditionAction> element of the MatchNewSongsOfInterest event rule.

Listing 18.3. Definition of the <ConditionAction> Element in the MatchNewSongsOfInterest Event Rule

 <Application>   ...   <SubscriptionClasses>     <SubscriptionClass>       <SubscriptionClassName>NewSongsOfInterest</SubscriptionClassName>       ...       <EventRules>         <EventRule>           <RuleName>MatchNewSongsOfInterest</RuleName>           <ConditionAction>             <SqlLogin>LowPrivEvaluator</SqlLogin>             <SqlUser>LowPrivEvaluator</SqlUser>             <InputName>SongAddedDetails</InputName>             <InputSchema>SongAlerts</InputSchema>             <SqlExpression>               INSERT INTO [SongAlerts].[NewSong]               SELECT  [Subscription.SubscriberId],                       [Subscription.DeviceName],                       [Subscription.Locale],                       [Input.SongTitle],                       [Input.ArtistName],                       [Input.AlbumTitle],                       [Input.Genre]               FROM    [SongAlerts].[MatchNewSongsOfInterest]             </SqlExpression>           </ConditionAction>           <EventClassName>SongAdded</EventClassName>         </EventRule>       </EventRules>     </SubscriptionClass>   </SubscriptionClasses>   ... </Application> 

As described in the earlier section "Securing User-Defined Logic" (p. 593), the <SqlUser> element specifies the user to impersonate when executing user-defined logic for the rule. The <SqlLogin> element specifies the associated server login. In this example, both the login name and username are LowPrivEvaluator. This is the name of a new, low-privilege server login we will create before we compile the application. The SQL-NS compiler will then create a user account of the same name in the application's database. When we grant database permissions to the SQL-NS engine, we'll also grant this user account the minimum permissions it needs to be able to evaluate user-defined logic. The section "Security Configuration for User-Defined Logic," (p. 641) describes the configuration steps needed to create the LowPrivEvaluator login and grant the necessary permissions to the associated user account.

The <InputName> and <InputSchema> elements in the <ConditionAction> declaration specify the name of the input view and the name of its containing schema. The <InputName> element specifies the value SongAddedDetails: the name of the view defined in Listing 18.1. The <InputSchema> element specifies the name of the application schema, SongAlerts, in which the view was created.

Finally, the <ConditionAction>'s <SqlExpression> element defines the SQL statement that inserts rows into the notifications view based on the matched data in the matches view. As described earlier, in the section "The <ConditionAction> Element" (p. 586), the name of the matches view is the rule name. Here, the rule name is MatchNewSongsOfInterest, so the SQL expression selects from a matches view of the same name, in the application schema.

As illustrated in Figure 18.2, the matches view contains a combination of the columns in the input table or view and the fields of the subscription class. Here the input is the SongAddedDetails viewall the columns from this view are included in the matches view, their names prefaced with Input.. Our subscription class defines two fields, DeviceName and Locale, and these are included in the matches view as the columns Subscription.DeviceName and Subscription.Locale.

The SQL expression in the <ConditionAction> element inserts into the NewSong notification class's notifications view, obtaining the subscriber ID, device name, and locale from the subscription columns in the matches view and the rest of the song properties from the input columns. As you can see from this example, the SQL expression is actually quite simple because all the complex logic associated with matching has already been performed. The SQL statement specified here consumes the results of this matching from the matches view.

Note

The NewSong notification class in this chapter's application is the same as the one defined in Chapter 5. You can refer to the section "The Output of the Application: Notifications" (p. 143) in Chapter 5 for a detailed description of this notification class.


The ADF content shown in Listings 18.1, 18.2, and 18.3 is already in the sample's ADF, C:\SQL-NS\Samples\UserDefinedLogic\SongAlerts\ApplicationDefinition.xml. Instructions for compiling and testing this code are provided in the section "Testing the Music Store Application with User-Defined Logic" (p. 643). But before we get there, we first look at the application's subscription management code in the following section.

The AddSubscriptions Program

In this section, we take a detailed look at a program called AddSubscriptions that takes the place of a real SMI for our sample application. AddSubscriptions uses the same SQL-NS Subscription Management API that a real SMI would use (including the user-defined logic extensions described in the section, "The Condition API," p. 607). However, AddSubscriptions lacks a user interfaceit is a command-line program that creates a set of subscriptions based on hard-coded values. These subscriptions illustrate the various aspects of the Condition API and are representative of the kinds of subscriptions users might create. Looking at a simple program like AddSubscriptions, instead of a fully functional SMI, allows us to focus on small code samples specific to the Condition API, rather than the details of a particular user-interface implementation.

Note

When you look at the code for the AddSubscriptions program, you'll see that the condition definitions, though created using the Condition API, are hard-coded into the program. In a real SMI, the condition structures and the values in them would be determined by a user, via the appropriate user-interface controls. The SMI code would translate these into the appropriate Condition API objects.

This sample might give the impression that the Condition API is simply a programmatic way to specify developer-defined logic. This is not the case. The Condition API is intended for use in code that dynamically creates conditions based on user input.


The AddSubscriptions Visual Studio Solution

The code for the AddSubscriptions program is defined in the Visual Studio solution, C:\SQL-NS\Samples\UserDefinedLogic\AddSubscriptions\AddSubscriptions.sln. Open this solution in Visual Studio so that you can refer to the source files as you read through this section.

The source code in the solution is divided into two main files:

  • Program.cs contains the main driver code for the application. In this section, we take a detailed look at the code in this file.

  • Arguments.cs contains code relating to processing of the program's command-line arguments. This code is not relevant to the Condition API or user-defined logic in general, so it isn't covered here.

An outline of the code in Program.cs shown in Listing 18.4.

Listing 18.4. Structure of the Main AddSubscriptions Program Code

 ... using Microsoft.SqlServer.NotificationServices; using Microsoft.SqlServer.NotificationServices.Rules; using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; namespace AddSubscriptions {    ...    class Program    {        ...        private static string sqlServer;        private static AuthenticationMode authenticationMode;        private static string sqlUserName;        private static string sqlPassword;        static int Main(string[] args)        {            ...Argument processing code...            try            {                ...Establish database connections and related objects...                   ...Add subscriptions...            }            catch (NSException nsEx)            {                ...Handle Notification Services exceptions...            }            catch (Exception ex)            {                ...Handle general exceptions...            }            ...        }        ...Helper methods...    } } 

The using declarations at the top of the file specify both the main notification services namespace and the namespace containing the classes in the Condition API, Microsoft.SqlServer.NotificationServices.Rules. The two other namespaces declared, Microsoft.SqlServer.Management.Common and Microsoft.SqlServer.Management.Smo, contain classes in the SMO library. These namespaces are explained in detail in the section "NMO API Assemblies and Namespaces" (p. 532) in Chapter 16, "Using Notification Services Management Objects." They are needed here because the AddSubscriptions code creates some SMO objects, as described in the section "Obtaining Needed SMO Objects" (p. 622).

All these namespaces are available because the program's solution references the appropriate assemblies. In Visual Studio, if you expand the project's References node in the Solution Explorer tree, you'll see the following assembly references (in addition to the usual system assembly references):

  • Microsoft.SqlServer.ConnectionInfo.dll

  • Microsoft.SqlServer.NotificationServices.dll

  • Microsoft.SqlServer.NotificationServices.Rules.dll

  • Microsoft.SqlServer.Smo.dll

The first and last assemblies in this list contain the needed SMO classes. The other two provide all the SQL-NS-specific APIs. Programs that make use of the Condition API usually require all these assembly references.

Returning to Listing 18.4, notice that the main code for the program is implemented in the Program class. This class declares four private member variables that correspond to the command-line arguments the program expects: the name of the SQL Server containing the music store database, the authentication mode to use when connecting to it, and a SQL username and password to use for SQL Server authentication.

Within the Program class's Main() method (the program's entry point), the first part of the code handles the processing of the command-line arguments. This code parses and validates the arguments and then stores their values in the four private member variables shown in Listing 18.4. The argument processing code is not shown in Listing 18.4 or discussed in this chapter. You can look at this code in the source file if you choose, but for the purposes of this discussion, you can just assume that the values of the command-line arguments end up in the four private member variables shown.

After processing the command-line arguments, the code within the Main() method's try-catch block implements the main functionality of the program. The first part of this code establishes the database connections and other objects needed to construct subscriptions and conditions later. This code is described in the "Connecting to the SongAlerts Application" section (p. 620) and in the "Obtaining Needed SMO Objects" section (p. 622).

The remaining code creates several subscriptions with a variety of conditions. This is the code that illustrates the use of the Condition API. In the sections that follow, we examine this code in detail.

Connecting to the SongAlerts Application

As you saw in previous chapters, creating subscriptions with the SQL-NS Subscription Management API requires connections to the instance and application. These connections are represented by objects of the NSInstance and NSApplication classes. Listing 18.5 shows the code in the AddSubscriptions program that establishes an NSInstance object for the MusicStore instance and an NSApplication object for its SongAlerts application.

Listing 18.5. Code That Creates the NSInstance and NSApplication Objects in the AddSubscriptions Program

 class Program {     ...     private const string InstanceName = "MusicStore";     private const string ApplicationName = "SongAlerts";     ...     private static AuthenticationMode authenticationMode;     private static string sqlUserName;     private static string sqlPassword;     static int Main(string[] args)     {         ...         try         {             // Create NSInstance and NSApplication objects.             NSInstance nsInstance;             NSApplication nsApplication;             if (AuthenticationMode.WindowsAuthentication ==                 authenticationMode)             {                 nsInstance = new NSInstance(InstanceName);             }             else             {                 nsInstance = new NSInstance(                     InstanceName,                     sqlUserName,                     sqlPassword);             }             nsApplication = new NSApplication(                 nsInstance,                 ApplicationName);             ...         }        ...     }     ... } 

The code declares NSInstance and NSApplication objects as local variables and then proceeds to instantiate them. If Windows Authentication is used (as indicated by the value of the authenticationMode member variable, set based on the program's command-line arguments) the NSInstance object is instantiated with the constructor that takes only the instance name. If SQL Server Authentication is used, the NSInstance object is created with the constructor that takes the instance name as well as a SQL username and password. In both cases, the instance name value is obtained from a string constant.

After creating the NSInstance object, the code creates the NSApplication object. The NSApplication constructor takes a reference to the NSInstance object and the application name. Like the instance name, the application name is also obtained from a string constant.

Obtaining Needed SMO Objects

As described in Chapter 16, SMO is an API that provides programmatic access to database management capabilities. Classes in the SMO API can be used to manage SQL Server entities, such as databases, tables, and security roles. In addition to performing management operations, SMO classes can also be used to reflect over existing SQL Server entities. For example, SMO can be used to enumerate and discover the properties of the databases on a particular SQL Server, as well as the objects (such as tables, views, functions, and stored procedures) within each database.

In the SQL-NS Condition API, references to SQL Server entities are generally represented by instances of their corresponding SMO classes. For example, the FunctionValue class, which represents condition terms that use SQL functions, has a property that identifies the SQL function to call. This property is defined as an object of the SMO class UserDefinedFunction. Using the SMO API, we can obtain a UserDefinedFunction object for any SQL function in the database and use this to initialize a FunctionValue object.

Similarly, the PropertyRule and FilterRule classes have properties that specify the table or view to use as the input type. These are initialized using SMO Table or View objects. These Table and View objects can be obtained by enumerating the contents of a database using other SMO classes.

The code in Listing 18.6 shows creation of the SMO objects needed to construct conditions later in the AddSubscriptions program. For the sake of simplicity, these objects are all instantiated in one place, rather than spread throughout the code that builds the conditions.

Listing 18.6. Code That Creates SMO Objects in the AddSubscriptions Program

 class Program {     ...     private const string DatabaseName = "MusicStore";     private const string SchemaName = "SongAlerts";     ...     private const string EventViewName = "SongAddedDetails";     private const string CatalogSchemaName = "Catalog";     private const string ArtistsTableName = "Artists";     private const string SongsTableName = "Songs";     private const string AlbumsTableName = "Albums";     ...     private static string sqlServer;     private static AuthenticationMode authenticationMode;     private static string sqlUserName;     private static string sqlPassword;     static int Main(string[] args)     {         ...         try         {             ...             // Create SMO Server and Database objects.             ServerConnection serverConnection = new ServerConnection();             serverConnection.ServerInstance = sqlServer;             if (AuthenticationMode.SqlServerAuthentication ==                authenticationMode)             {                 serverConnection.LoginSecure = false;                 serverConnection.Login = sqlUserName;                 serverConnection.Password = sqlPassword;             }             Server server = new Server(serverConnection);             Database musicStoreDB = server.Databases[DatabaseName];             // Obtain SMO objects for the database objects used             // later when defining rules.             View songAddedDetailsView =                 musicStoreDB.Views[EventViewName, SchemaName];             Table songsTable =                 musicStoreDB.Tables[                     SongsTableName,                     CatalogSchemaName];             Table artistsTable =                 musicStoreDB.Tables[                     ArtistsTableName,                     CatalogSchemaName];             Table albumsTable =                 musicStoreDB.Tables[                     AlbumsTableName,                     CatalogSchemaName];             ...         }         ...     }     ... } 

The code begins by creating an SMO ServerConnection object to represent a connection to the SQL Server. If SQL Server Authentication is used, the ServerConnection object is initialized with the required connection information. Based on the ServerConnection object, the code then creates a Server object to represent the server itself. This pattern is common to most SMO and NMO programs.

With the Server object established, the code obtains an SMO Database object representing the music store database. The object is obtained by accessing the Server object's Databases collection, using the database name as a key. Given this Database object, the code then proceeds to obtain four more SMO objects that represent tables and views within the music store database. Following a similar pattern, these objects are obtained from collections on the Database object, using the appropriate names as keys.

The four objects obtained represent the SongAddedDetails view (described in the section, "Creating the Input: The SongAddedDetails View," p. 612) and the songs, artists, and albums tables in the music store catalog. The View object is obtained from the Database object's Views collection and the Table objects are obtained from its Tables collection. When accessing the Tables and Views collections, both the object name and the name of its containing schema are given as keys.

As you'll see in subsequent sections, the SMO objects obtained here are used in the condition definitions that follow. Specifically, the View object representing SongAddedDetails view is used to define the input type for a property rule and the Table objects are used to define the input type for a filter rule and the join clauses in its associated link leaf condition.

Creating Subscription Objects

Each subscription that the AddSubscriptions program creates is represented by a Subscription object. For each subscription, the program creates the Subscription object, sets its basic properties, and then sets its condition. It then calls the Subscription object's Add() method to record the subscription in the database.

The creation of the subscription object and the setting of its basic properties (like the subscriber ID and the subscription fields) is the same for each subscription the program creates. For this reason, the creation and basic initialization of the subscription objects is broken out into a helper method. The main AddSubscriptions code calls this method to obtain a new Subscription object for each new subscription. Because the Subscription object obtained from the helper method is already initialized with the basic properties, the main AddSubscriptions code can simply focus on the setting of the subscription condition.

Listing 18.7 shows the implementation of the CreateSubscriptionObject() helper method. The code in this method is similar to the code for creating subscriptions that you saw in Chapter 7.

Listing 18.7. The CreateSubscriptionObject() Helper Method

 class Program {     ...     private const string SubscriptionClassName = "NewSongsOfInterest";     private const string RuleName = "MatchNewSongsOfInterest";     ...     private static Subscription CreateSubscriptionObject(         NSApplication nsApplication,         string subscriberId,         string deviceName,         string locale)     {         Subscription subscription;         subscription = new Subscription(             nsApplication,             SubscriptionClassName);         subscription.SubscriberId = subscriberId;         subscription.Enabled = true;         subscription["DeviceName"] = deviceName;         subscription["Locale"] = locale;         subscription.RuleName = RuleName;         return subscription;     }     ... } 

CreateSubscriptionObject() takes four arguments: an NSApplication object representing the application, the subscriber ID value to set in the Subscription object, and the values to use for the subscription's DeviceName and Locale fields. It creates a new Subscription object, passing the NSApplication object and the subscription class name (obtained from a string constant) to the Subscription constructor. It then sets the object's SubscriberId property to the value passed in, sets the Enabled property to true, and then sets the subscription fields to the given values.

Finally, the code sets the Subscription object's RuleName property. Setting the RuleName property associates the subscription with a particular rule in the subscription class. The specified rule's <ConditionAction> element defines the input type for the conditions that can be associated with the Subscription object. In this case, the RuleName property is set to the name of the event rule in our subscription class.

Note

Setting the RuleName property is necessary only if the Subscription object's Condition property will also be set. In Subscription objects that don't have user-defined condition logic (those that represent subscriptions based on developer-defined logic), neither RuleName nor Condition need to be set.


After its basic properties have been set, the Subscription object is returned. Notice that the helper method does not set the object's Condition property or call its Add() method. Both of those things are done outside the helper method, by the main AddSubscriptions code. The sections that follow illustrate how the Condition properties on Subscription objects created by this helper method can be set to a variety of condition objects.

Creating Subscriptions with Simple Conditions

To begin the series of condition examples, let's look at a subscription with a simple leaf condition. Listing 18.8 shows the code that creates such a subscription.

Listing 18.8. Creating a Subscription with a Simple Condition

 class Program {     ...     static int Main(string[] args)     {         ...         try         {            ...            Subscription subscription;            subscription = CreateSubscriptionObject(                nsApplication,                "Bob",                "DefaultDevice",                "en-US");            // Define condition:            // ArtistName = "Miles Davis"            subscription.Condition = new SimpleLeafCondition(                new FieldValue("ArtistName"),                SimpleOperator.Equals,                new ConstantValue("Miles Davis"));            // Add the subscription to the database.            subscription.Add();            ...         }         ...     }     ... } 

The code calls the helper method CreateSubscriptionObject() to create a new subscription object. A reference to this object is stored in the local variable subscription. As you saw in Listing 18.7, objects returned from CreateSubscriptionObject() have had their basic properties set. All that remains is to set the Condition property appropriately and then add the subscription to the database by calling its Add() method.

Note

The AddSubscriptions program does not create subscriber and subscriber device records. In our sample application, subscriber and subscriber device records are created via a T-SQL script. (Instructions for running this script are provided later in this chapter, in the section, "Testing the Music Store Application with User-Defined Logic," p. 643.) The script creates eight subscriber records, with the subscriber IDs "Bob", "Alex", "Mary", "Emily", "Joe", "Jane", "David", and "Lisa". It also creates an associated subscriber device for each subscriber named, "DefaultDevice". The AddSubscriptions program assumes the existence of these subscriber and subscriber device records.


In Listing 18.8, the Subscription object's Condition property is set to a new SimpleLeafCondition object. The SimpleLeafCondition constructor takes three arguments: left and right operands and a relational operator. In this example, the left operand is a new FieldValue object, referencing the ArtistName field in the input data. The operator is the relational operator, equals. This is indicated by means of the Equals member of the SimpleOperator enumeration. The condition's right operand is a ConstantValue object representing the literal string, "Miles Davis". Thus, the SimpleLeafCondition implements the condition expression ArtistName = "Miles Davis".

After the condition is defined, the Subscription object's Add() method is called. Internally, the Add() method validates the condition and records all the subscription information (including the condition definition) in the database.

Notice how the FieldValue and ConstantValue objects in Listing 18.8 are created inline with the call to the SimpleLeafCondition constructor. This is a common pattern in code that constructs conditions: subobjects that make up a condition expression are often created directly within the statement that creates the main condition object.

All condition classes also support an alternative initialization pattern: they can be instantiated with parameterless constructors and then initialized via their various public properties. For example, the condition in Listing 18.8 could have been created by instantiating a SimpleLeafCondition object with its parameterless constructor and then setting the object's Left, Operator, and Right properties. However, this technique requires you to remember which properties need to be set to fully initialize the object. It's easy to create an object and then forget to initialize one of its properties, leading to a failure at the time the subscription is added to the database. I personally believe the method shown in Listing 18.8 (creating and initializing the condition object with a constructor that takes parameters) is less error prone. The signature of the constructor defines the required initialization parameters; failure to provide any of these parameters will result in an error at compile time.

Creating Subscriptions with Compound Conditions

In this section, we look at some examples of compound conditions. Compound conditions combine simpler conditions with the logical operators AND, OR, and NOT. Listing 18.9 shows the code that creates a compound condition that combines two simple leaf conditions with the OR operator.

Listing 18.9. Creating a Subscription with a Compound OR Condition

 class Program {     ...     static int Main(string[] args)     {         ...         try         {            ...            subscription = CreateSubscriptionObject(                nsApplication,                "Alex",                "DefaultDevice",                "en-US");            // Define condition:            // Genre = "Jazz" OR Genre = "Hip Hop"            subscription.Condition = new OrCondition(                new SimpleLeafCondition(                    new FieldValue("Genre"),                    SimpleOperator.Equals,                    new ConstantValue("Jazz")),                new SimpleLeafCondition(                    new FieldValue("Genre"),                    SimpleOperator.Equals,                    new ConstantValue("Hip Hop")));            // Add the subscription to the database.            subscription.Add();            ...         }         ...     }     ... } 

Like before, a new subscription object is obtained by calling the CreateSubscriptionObject() helper method. The new Subscription object's Condition property is then set to a new OrCondition object. The OrCondition constructor takes a variable length list of parameters, each of which is a condition object to include in the OR expression. In this example, two SimpleLeafConditions are passed to the OrCondition constructor. These SimpleLeafCondition objects are created inline with the call to the constructor. Like the example in the previous section (shown in Listing 18.8), each simple leaf condition in this example compares a field of the input to a constant value, with the equality operator.

The condition object created in Listing 18.9 expresses the condition (Genre = "Jazz") OR (Genre = "Hip Hop"). This is the program's first example of a subscription that could not have been expressed in the previous version of the music store application.

The code in Listing 18.10 creates another compound condition. This time, two simple leaf conditions are combined with the AND operator.

Listing 18.10. Creating a Subscription with a Compound AND Condition

 class Program {     ...     static int Main(string[] args)     {         ...         try         {             ...             subscription = CreateSubscriptionObject(                 nsApplication,                 "Emily",                 "DefaultDevice",                 "en-US");             // Define condition:             // ArtistName = "Dido" AND AlbumTitle = "Life for Rent"             subscription.Condition = new AndCondition(                 new SimpleLeafCondition(                     new FieldValue("ArtistName"),                     SimpleOperator.Equals,                     new ConstantValue("Dido")),                 new SimpleLeafCondition(                     new FieldValue("AlbumTitle"),                     SimpleOperator.Equals,                     new ConstantValue("Life for Rent")));             // Add the subscription to the database.             subscription.Add();             ...         }        ...     }     ... } 

The condition object assigned to the Subscription object's Condition property is an instance of the AndCondition class. Like the OrCondition we looked at in the previous example, the AndCondition constructor takes a variable number of condition objects. The AndCondition object represents the compound condition formed by combining the conditions expressed by these child condition objects with the logical AND operator. The code in Listing 18.10 creates an AndCondition condition object that expresses the condition (ArtistName = "Dido") AND (AlbumTitle = "Life For Rent").

Listing 18.11 shows an example of a compound condition based on the NOT operator. It also illustrates how compound conditions can be composed.

Listing 18.11. Creating a Subscription with a NOT Condition

 class Program {     ...     static int Main(string[] args)     {         ...         try         {             ...             subscription = CreateSubscriptionObject(                 nsApplication,                 "Joe",                 "DefaultDevice",                 "en-US");             // Define condition:             // NOT(ArtistName = "Dido"             //     AND AlbumTitle = "Life for Rent"))             subscription.Condition = new NotCondition(                 new AndCondition(                     new SimpleLeafCondition(                         new FieldValue("ArtistName"),                         SimpleOperator.Equals,                         new ConstantValue("Dido")),                     new SimpleLeafCondition(                         new FieldValue("AlbumTitle"),                         SimpleOperator.Equals,                         new ConstantValue("Life for Rent"))));             // Add the subscription to the database.             subscription.Add();             ...         }         ...     }     ... } 

In Listing 18.11, the Subscription object's Condition property is set to a new NotCondition object. The NotCondition constructor takes a single child condition object representing the condition expression to be negated. Because the type of the NotCondition constructor's parameter is the base class Condition (from which all condition classes are derived) it can take a reference to any condition objecteven another compound condition. Here, the child condition is an AndCondition object. In fact, the definition of this AndCondition object is the same as the one in the previous example. Therefore, the condition created in Listing 18.11 is the negation of the condition created in Listing 18.10. The subscription created in Listing 18.11 will be matched against precisely those songs that do not match the subscription in Listing 18.10.

Note

Listing 18.11 actually creates four condition objects in one statement. It's important to understand the structure of this statement to see how these condition objects form the final condition expression.

At the top level is the NotCondition object. Its constructor takes a single child condition object. In this example, because the child condition is an AndCondition, created inline within the call to the NotCondition constructor, it may appear as though more than one condition is being passed to the NotCondition constructor. This is not the case; the NotCondition constructor takes just the one AndCondition object. The AndCondition constructor is passed two child conditions (both of them SimpleLeafCondition objects). These are also created inline. The two SimpleLeafCondition objects are children of the single AndCondition object, which is the only child of the NotCondition object.


Using Function and Expression Values

In all the examples so far, only constant values and field values have been used. In this section, we look at an example of a condition defined with function values and expression values.

The code in Listing 18.12 creates a condition expression that compares the result of a function call with the result of an arithmetic expression. This is a somewhat contrived example, but it illustrates how these kinds of values can be used.

Listing 18.12. Using Function and Expression Values to Define a Condition

 class Program {     ...     static int Main(string[] args)     {         ...         try         {             ...             subscription = CreateSubscriptionObject(                 nsApplication,                 "Jane",                 "DefaultDevice",                 "en-US");             // Define condition:             // NumberOfWords(ArtistName) >= 1+2             subscription.Condition = new SimpleLeafCondition(                 new FunctionValue(                     musicStoreDB.UserDefinedFunctions[                         "NumberOfWords",                         CatalogSchemaName],                     new FieldValue("ArtistName")),                 SimpleOperator.GreaterThanOrEqualTo,                 new ExpressionValue(                     new ConstantValue(1),                     ExpressionOperator.Addition,                     new ConstantValue(2)));             // Add the subscription to the database.             subscription.Add();             ...         }         ...     }     ... } 

This code sets the Subscription object's Condition property to a SimpleLeafCondition object. Structurally, this simple leaf condition is like the ones you've seen in previous examples: it compares one value to another with a relational operator. The difference is that in this example, the values are not just constants or field values.

Like before, the two values are created inline with the call to the SimpleLeafCondition constructor. The first value is an object of the FunctionValue class. The FunctionValue constructor takes the SMO UserDefinedFunction object representing the function to call. This is obtained from the UserDefinedFunctions collection on the Database object created earlier (see the section "Obtaining Needed SMO Objects," p. 622). In this example, the function name is NumberOfWords and it's defined in the music store catalog schema. Both the function name and schema name are used as keys into the Database object's UserDefinedFunctions collection.

The function, NumberOfWords, is defined in the script that creates the music store database. It takes a string argument and returns a count of the number of words in it. For example, given the string "Life for Rent", the function would return the number 3. It's not important to go into the implementation of this function, but if you're curious, you can look for it in the script C:\SQL-NS\Samples\UserDefinedLogic\CreateMusicStore.sql.

In addition to the UserDefinedFunction object that identifies the SQL function to call, the FunctionValue constructor takes a variable length list of Argument objects that specify values for the function's arguments. Because all value classes derive from the base class Argument, any value object can be specified in this list. The NumberOfWords function expects a single argument, so the FunctionValue constructor is passed just one value object in this example. This is a FieldValue object referencing the ArtistName field. Thus, our FunctionValue object represents a call to the NumberOfWords function, passing the value of the ArtistName field in the input data as the function's argument. When evaluated, this returns the number of words in the artist name.

The operator specified as the second argument to the SimpleLeafCondition constructor in Listing 18.12 is the GreaterThanOrEqualTo member of the SimpleOperator enumeration. This corresponds to the relational operator greater than or equal to (>=).

The third argument passed to the SimpleLeafCondition constructor represents the right operand in the condition expression. In this example, an ExpressionValue object is passed. The ExpressionValue constructor takes two value objects to use as operands and an arithmetic operator. From these, it forms an arithmetic expression. The constructor's operand arguments are of type Argument, so any value object can be used. Here, two ConstantValue objects, representing the constants 1 and 2, respectively, are passed. The arithmetic operator is specified using the ExpressionOperator enumeration. In this example, the Addition member of the enumeration is used, which indicates that the two operands should be added together.

By assembling all the pieces described here, our subscription's condition object represents the expression NumberOfWords(ArtistName) >= (1 + 2). This will match all songs in which the artist name consists of three or more words. As mentioned earlier, this is a contrived example, but it does illustrate how function and expression values can be specifiedand how user-defined logic can provide users a way to create some really strange subscriptions!

Creating User-Defined Rules to Build Conditions

This section shows the use of user-defined rules to create conditions. Two examples are shown: one that uses a property rule and another that uses a filter rule.

Although these examples each create only one condition with each rule, remember that user-defined rules represent reusable logic. A single rule can be incorporated into several different conditions. This allows the logic defined by the rule to be reused without being duplicated in each condition definition.

Creating Property Rules

As described in the earlier section "Property Rules" (p. 599), a property rule computes a scalar-valued result from a row of input data. The results of property rules can be treated like computed properties of the input over which they're defined. Property rules are created independently and can be used as terms in one or more condition expressions.

Earlier, we looked at a conceptual example of a property rule that defines a CanDance property of songs. Listing 18.13 shows code that actually creates this property rule using classes in the Condition API and then uses it in a subscription condition.

Listing 18.13. Using a Property Rule in a Condition

 class Program {     ...     static int Main(string[] args)     {         ...         try         {             ...             subscription = CreateSubscriptionObject(                 nsApplication,                 "David",                 "DefaultDevice",                 "en-US");               // The condition for this subscription uses a property             // rule, so we construct that rule here.             // Create the rule that defines the  "canDance"             // property.             PropertyRule canDanceRule = new PropertyRule();             canDanceRule.InputType =                 new ViewInputType(songAddedDetailsView);             // Add statement that says the value is true, if the             // Genre = "Hip Hop".             canDanceRule.PropertyStatements.Add(                 new PropertyStatement(                     new SimpleLeafCondition(                         new FieldValue("Genre"),                         SimpleOperator.Equals,                         new ConstantValue("Hip Hop")),                     new ConstantValue(true)));             // Add statement that says the value is false, if the             // Genre = "Rock".             canDanceRule.PropertyStatements.Add(                 new PropertyStatement(                     new SimpleLeafCondition(                         new FieldValue("Genre"),                         SimpleOperator.Equals,                         new ConstantValue("Rock")),                     new ConstantValue(false)));             // Add statement that says the value is false, if the             // Genre = "Jazz".             canDanceRule.PropertyStatements.Add(                 new PropertyStatement(                     new SimpleLeafCondition(                         new FieldValue("Genre"),                         SimpleOperator.Equals,                         new ConstantValue("Jazz")),                     new ConstantValue(false)));             // Define condition:             // canDance property rule's value is true.             subscription.Condition = new BooleanLeafCondition(                 new RuleValue(canDanceRule));             // Add the subscription to the database.             subscription.Add();             ...         }         ...     }     ... } 

Property rules are represented by objects of the PropertyRule class. The code in Listing 18.13 creates a new PropertyRule object for our CanDance rule and specifies the rule's input type by setting the object's InputType property.

Property rules must have the same input type as the conditions in which they are used. Because our subscription conditions use the SongAddedDetails view as their input type, this view must also be the input type for our property rule. This is specified by setting the PropertyRule object's InputType property to a new ViewInputType object, initialized with the SMO View object for the SongAddedDetails view. This SMO View object was obtained earlier, in the code shown in Listing 18.6.

ViewInputType is one of two classes in the Condition API that can be used to specify input types for user-defined rules. As this example shows, its constructor takes an SMO View object representing the input view. The other input type class is TableInputType, which, as its name suggests, is used to represent inputs that are tables. The TableInputType class is used in the filter rule example in the next section.

After specifying the rule's input type, the code in Listing 18.13 defines the rule's three property statements. It creates a new PropertyStatement object for each statement and adds it to the PropertyStatements collection on the PropertyRule object. This associates the statements with the rule. The PropertyStatement objects are created inline with the call to the collection's Add() method.

Caution

Remember that statements in a property rule are ordered: if more than one statement's condition evaluates to true for a given input, the result value is taken from the earliest statement. Therefore, the order in which PropertyStatement objects are added to the PropertyStatements collection is significant.


Recall from the "Property Rules" section (p. 599) that property statements have the following form:

 IF <Condition> THEN <Value> 


In the API, this form is reflected in the PropertyStatement objects: each PropertyStatement object contains a condition object and a result value object. These can be the same kinds of condition and value objects used in subscription conditions. In Listing 18.13, each PropertyStatement object is created with a SimpleLeafCondition object (expressing a simple predicate over the Genre field in the input) and a ConstantValue object representing a Boolean constant result. These condition and value objects are created within the call to the PropertyStatement constructor.

In this example, the same kind of condition is used in each statement, but this is not a requirement. Different statements in a property rule can have different kinds of condition objects. The same is true of the statements' result value objects. For example, some statements may have ConstantValue result objects and others may have FieldValue or ExpressionValue result objects.

Property rules can be referenced in condition expressions by means of RuleValue objects. RuleValue is a value class, derived from the Argument base class, like all other value classes. A RuleValue object is initialized with a PropertyRule object and then represents the result of that property rule.

Because RuleValue is just another value class, RuleValue objects can be used in any place a value object is needed. Depending on the type of value returned by the particular property rule, some contexts make more sense than others. In this example, the property rule returns Boolean values, so it makes sense to use the RuleValue object where Booleans are expected. Had we defined a property rule that returns integers, it could have been referenced in RuleValue objects within arithmetic expressions.

The code in Listing 18.13 uses our property rule to create a Boolean leaf condition for the subscription. The condition object is an instance of the BooleanLeafCondition class, which represents condition expressions that consist of a single Boolean value. BooleanLeafCondition objects can be initialized with any value object that represents a Boolean value. Here the value object is a RuleValue object initialized with our CanDance property rule. Because the property rule returns Booleans, it can be used within a BooleanLeafCondition.

Note

The equivalent condition logic could have been achieved by creating a SimpleLeafCondition that compares our property rule's value to the constant value, true. The BooleanLeafCondition is a slightly more compact way of representing the same logic.


As this example shows, after the property rule is defined, its value can be used like a field of the input. And because property rules are reusable, we can use the values they compute in several subscriptions or in the definition of other rules. To do this in code, we could create additional RuleValue objects that all refer to the same PropertyRule object.

Creating Filter Rules and Link Leaf Conditions

The earlier section "Filter Rules and Link Leaf Conditions" (p. 601) describes how filter rules can be used with link leaf conditions to express logic over data in tables or views other than a condition's input. The example described in that section was based on a stock notification application.

In this section, we look at an example of a filter rule and link leaf condition within the context of the music store application. In this example, we create a subscription that matches all songs by a subscriber's favorite artists. Instead of hard-coding the logic that determines which artists are favorites directly into the subscription condition, we can break it out into a filter rule. This filter rule is defined over the Artists table in the music store catalog schema. The filter rule's statements define which rows in the Artists table should be included in the set of favorites, based on a number of conditions.

The filter rule is then used in a link leaf condition in the subscription. The link leaf condition specifies a set of join clauses that link the condition's input, the SongAddedDetails view, with the input to the filter rule, the Artists table. Given a particular input row, if any of the related rows in the Artists table are in the set of favorites defined by the filter rule, the condition is satisfied.

Admittedly, this isn't the most compelling example because the only information in the Artists table is the artist name and this is already available for each song in the SongAddedDetails view. The example would be more compelling if the Artists table contained other properties of artists that could be used in the filter rule's conditions to define what it means for an artist to be a favorite. Regardless, this example still serves to illustrate the use of the Condition API to create filter rules and link leaf conditions. Listing 18.14 shows the corresponding code.

Listing 18.14. Using a Filter Rule with a Link Leaf Condition

 class Program {     ...     static int Main(string[] args)     {         ...         try         {             ...             subscription = CreateSubscriptionObject(                 nsApplication,                 "Lisa",                 "DefaultDevice",                 "en-US");             // Create a rule that defines my favorite artists.             FilterRule favoriteArtists = new FilterRule();             favoriteArtists.InputType =                 new TableInputType(artistsTable);               // Add a statement that includes Miles Davis.             favoriteArtists.FilterStatements.Add(                 new FilterStatement(                     new SimpleLeafCondition(                         new FieldValue("Name"),                         SimpleOperator.Equals,                         new ConstantValue("Miles Davis")),                     FilterAction.Include));             // Add a statement that includes Black Eyed Peas.             favoriteArtists.FilterStatements.Add(                 new FilterStatement(                     new SimpleLeafCondition(                         new FieldValue("Name"),                         SimpleOperator.Equals,                         new ConstantValue("Black Eyed Peas")),                     FilterAction.Include));             // Add a statement that excludes Dido.             favoriteArtists.FilterStatements.Add(                 new FilterStatement(                     new SimpleLeafCondition(                         new FieldValue("Name"),                         SimpleOperator.Equals,                         new ConstantValue("Dido")),                     FilterAction.Exclude));             subscription.Condition = new LinkLeafCondition(                 LinkLeafOperator.Any,                 favoriteArtists,                 new JoinClause(                     songAddedDetailsView.Columns["SongId"],                     songsTable.Columns["Id"]),                 new JoinClause(                     songsTable.Columns["AlbumId"],                     albumsTable.Columns["Id"]),                 new JoinClause(                     albumsTable.Columns["ArtistId"],                     artistsTable.Columns["Id"]));             // Add the subscription to the database.             subscription.Add();               ...         }         ...     }     ... } 

Filter rules are represented by instances of the FilterRule class. This code creates a new FilterRule object for our filter rule and sets its InputType property. The input type for the rule is the Artists table, so the InputType property is set to a TableInputType object, initialized with the SMO Table object for the Artists table. This SMO Table object was obtained earlier, in the code shown in Listing 18.6.

The code then creates FilterStatement objects to represent the statements in the filter rule definition. Each FilterStatement object is added to the FilterStatements collection on the FilterRule object.

Filter rule statements have the following form:

 IF <Condition> THEN <Action> 


In the API, this form is reflected in the FilterStatement class, which defines Condition and Action properties. A FilterStatement object's Condition property can be set to any of the supported condition objects in the Condition API. Its Action property can be set to the values defined in the FilterAction enumeration: Include and Exclude.

The filter rule in this example consists of the following three statements:

 IF Name = "Miles Davis" THEN Include IF Name = "Black Eyed Peas" THEN Include IF Name = "Dido" THEN Exclude 


In the Artists table, the artist names are stored in the Name column. The conditions in these statements are therefore implemented by comparing the Name field in each input row with a particular artist name. The actions specify whether to include or exclude rows with those particular artist names. The first two statements specify that the artists Miles Davis and Black Eyed Peas should be included. The last statement says that the artist Dido should be excluded.

In Listing 18.14, the condition object and action value for each statement are specified as arguments to the FilterStatement constructor. The statements' conditions are all SimpleLeafCondition objects. Their actions are specified using the members of the FilterAction enumeration.

After creating all the filter rule's statements, the code creates the LinkLeafCondition object in which the filter rule is used. The LinkLeafCondition constructor takes the link operator (specified as a member of the LinkLeafOperator enumeration), the FilterRule object, and a series of JoinClause objects representing the join clauses that link the condition's input to the filter rule's input. The LinkLeafCondition constructor accepts a variable number of JoinClause objectsyou can pass as many as are needed to establish the link between the two inputs.

In this example, the link operator ANY is specified using the LinkLeafOperator.Any value. Because the relationship between the SongAddedDetails view and the Artists table is one-to-one (every song has only one artist in our simplified music store schema), we could also have used the LinkLeafOperator.Every operator without changing the logic of the condition.

The FilterRule object passed to the LinkLeafCondition constructor is the one we just created. A reference to the FilterRule was kept in the local variable favoriteArtists. This local variable is now passed as the second argument to the LinkLeafCondition constructor. If we needed to create subsequent subscription conditions that refer to the same filter rule, we could reuse the same FilterRule object.

The join clauses passed to the LinkLeafCondition constructor link the SongAddedDetails view to the Artists table via the Songs and Albums tables. The link chain proceeds as follows:

  1. The SongAddedDetails view is joined with the Songs table based on song ID. In the SongAddedDetails view, the song ID column is called SongId; in the Songs table, it is called Id.

  2. The Songs table is joined with the Albums table on album ID. In the Songs table, the join column is AlbumId; in the Albums table, it is Id.

  3. The Albums table is joined with the Artists table on artist ID. The join column in the Albums table is ArtistId; in the Artists table, it is Id.

The JoinClause objects, created inline during the call to the LinkLeafCondition constructor, are each initialized with a pair of columns. In each pair, the first column is in the current table in the link chain and the second column is in the next table in the chain. Because the chain starts with the condition's input, the first column is from the SongAddedDetails view. The columns are specified as SMO Column objects, obtained from the Columns collections on the various SMO View and Table objects we created earlier.

The LinkLeafCondition object is assigned to the Subscription object's Condition property and then the Subscription object's Add() method is called. In the Add() method, the Subscription object validates all the elements of the link leaf condition, including the filter rule and the join clauses, before recording them in the application database.

Security Configuration for User-Defined Logic

In this section, we look at the configuration steps required to implement a low-privilege sandbox for user-defined logic in our sample application. As you saw in Listing 18.3, the <SqlLogin> element in the event rule's <ConditionAction> declaration specifies the value "LowPrivEvaluator". When the SQL-NS compiler creates the user account it will use for impersonation during condition evaluation, it will associate the account with a server login of this name. Therefore, we need to create this login before compiling the application.

In our sample application, the server login is created by the script C:\SQL-NS\Samples\UserDefinedLogic\CreateLowPrivEvaluatorLogin.sql. One of the setup steps in the instructions for testing the sample invokes a batch file that executes this script. Listing 18.15 shows the code in this script.

Listing 18.15. Creating a Low-Privileged Login

 USE [Master] GO IF NOT EXISTS (     SELECT name     FROM sys.syslogins     WHERE name = 'LowPrivEvaluator' )     CREATE LOGIN [LowPrivEvaluator] WITH PASSWORD='lowPrivilege1234!' GO 

This code first checks whether a login with the name "LowPrivEvaluator" already exists. It creates the login, using a CREATE LOGIN statement, only if the login doesn't already exist. The login is created with a password, meaning that it authenticates via SQL Server Authentication. Even if your system generally uses Windows Authentication, configuring this low privileged login to use SQL Server Authentication keeps things simple. To use Windows Authentication, you would need a Windows account to associate with the login.

Caution

The password for the LowPrivEvaluator login is hard-coded in the CreateLowPrivEvaluatorLogin.sql script. Although this account is intended to have very low privileges, you may still want to remove the password from the script (after executing it, as you'll be instructed to do in the following section) or change it to a different value. Note that the login created by this script is dropped by the sample's cleanup script.


At compile time, the SQL-NS compiler checks for the existence of the login specified in the <SqlLogin> element. Creating the server login ensures that the application will compile successfully. During the compilation, the compiler will create a user account in the application's database with the username specified in the <SqlUser> element of the <ConditionAction> declaration. In our sample application, the <SqlUser> element specifies the username LowPrivEvaluator. Initially, this account will have no permissions (the compiler does not grant it any). After the application is compiled, we must explicitly grant this account the permissions it needs. This must be done before the application is started, otherwise condition evaluation will fail.

Note

In this example, the user account and the server login with which it is associated have the same name, LowPrivEvaluator. This is not a requirementyou may specify different values for the <SqlLogin> and <SqlUser> elements.


In general, the user account specified in a <ConditionAction> element's <SqlUser> subelement needs only SELECT permissions on the tables and views referenced in subscription conditions. These tables and views include the condition input (as specified in the <InputName> element within the <ConditionAction>), as well as the inputs of any property rules and filter rules referenced in the subscription conditions. In addition, SELECT permissions are needed on all the tables and views referenced in join clauses in any link leaf conditions.

In the case of our application, the SongAddedDetails view is the input type for the subscription conditions and the one property rule. The AddSubscriptions program defines a filter rule over the music store catalog's Artists table, and the join clauses in the link leaf condition that consumes this filter rule reference the Songs and Artists tables. Therefore, we need to grant the condition evaluation user account SELECT permissions on the SongAddedDetails view, as well as the Songs, Albums, and Artists tables.

Permissions are granted in the sample's SQLPermissions.sql script. This script includes the statements that grant the usual permissions to the SQL-NS engine account, as well as those that grant permissions to the LowPrivEvaluator account. The latter statements are shown in Listing 18.16.

Listing 18.16. Granting Permissions to the Low Privilege User

 GRANT SELECT ON [SongAlerts].[SongAddedDetails] TO [LowPrivEvaluator] GRANT SELECT ON [Catalog].[Songs] TO [LowPrivEvaluator] GRANT SELECT ON [Catalog].[Albums] TO [LowPrivEvaluator] GRANT SELECT ON [Catalog].[Artists] TO [LowPrivEvaluator] 

When we test the application in the following section, one of the steps you'll run invokes the SQLPermissions.sql script to grant all these permissions.

Testing the Music Store Application with User-Defined Logic

Now that you've seen all the important code in the application and the associated configuration scripts, it's time to compile the application and test it with some sample data. Use the following instructions to do this:

1.

Open a Notification Services Command Prompt and navigate to the scripts directory for the old version of the music store application, C:\SQL-NS\Samples\MusicStore\Scripts.

 cd /d C:\SQL-NS\Samples\MusicStore\Scripts 


2.

Run cleanup.cmd to remove any previous version of the music store application that you may have created on your system in previous chapters. This is necessary because the new version of the sample in this chapter uses the same instance and application name as the old version.

3.

Navigate to the scripts directory for the new version of the sample, C:\SQL-NS\Samples\UserDefinedLogic\Scripts.

 cd /d C:\SQL-NS\Samples\UserDefinedLogic\Scripts 


4.

Run create_low_priv_evaluator_login.cmd. This executes the statements shown in Listing 18.15 that create the low-privilege login if it doesn't already exist.

5.

Run create_music_store_database.cmd to create the music store database and populate it with data.

6.

Run create.cmd to create the SQL-NS instance.

7.

Run register.cmd to register the instance.

8.

Run enable.cmd to enable the instance.

9.

Run grant_permissions.cmd to grant the required database permissions, including those needed by the low-privilege user account. This executes the statements shown in Listing 18.16.

10.

Start the SQL-NS Windows service for the instance by issuing the following command:

 net start NS$MusicStore 


11.

Execute the SQL script C:\SQL-NS\Samples\UserDefinedLogic\AddSubscribers.sql in Management Studio. This creates the subscriber and subscriber device records.

12.

If you have not already opened the AddSubscriptions Visual Studio solution, open it now. The solution file is C:\SQL-NS\Samples\UserDefinedLogic\AddSubscriptions\AddSubscriptions.sln.

13.

Build the AddSubscriptions solution in Visual Studio.

14.

Return to the command prompt and invoke addsubscriptions.cmd from the sample's scripts directory (C:\SQL-NS\Samples\UserDefinedLogic\Scripts). This batch file invokes the AddSubcriptions program with the appropriate command-line arguments for your environment.

15.

Execute the SQL script C:\SQL-NS\Samples\UserDefinedLogic\SubmitEvents.sql in Management Studio. This submits a set of test events.

16.

Watch the notifications output file C:\SQL-NS\Samples\UserDefinedlogic\FileNotifications.txt. Within 30 seconds, a set of notifications will be written to this file.

The notifications written to the file are generated based on the user-defined conditions created by the AddSubscriptions program. Notice that the notifications look like the ones generated by the previous version of the music store application. This shows that the modifications introduced in this chapter have only changed the mechanism used to determine whether an event matches a subscription. The rest of the application, including the processing of generated notifications, works as it did before.

Note

You can verify that the correct notifications were generated by checking the event data submitted in step 15 with the conditions defined in Listings 18.8 through 18.14. If you look at the SubmitEvents.sql script, you'll see that it submits events with song IDs 1, 6, 12, and 14. You can query the SongDetails view in the music store catalog to see the data associated with these song IDs.

The AddSubscriptions program creates only one subscription for each subscriber. You can correlate the subscription conditions defined in Listings 18.8 through 18.14 with the generated notifications based on subscriber ID. Compare the subscriber ID value passed to the CreateSubscriptionObject() helper method in each listing with the subscriber IDs written to the output file with each notification.


Note

When you're done working with the sample instance, you can remove it from your system by running the cleanup.cmd script from the scripts directory C:\SQL-NS\Samples\UserDefinedLogic\Scripts.





Microsoft SQL Server 2005 Notification Services
Microsoft SQL Server 2005 Notification Services
ISBN: 0672327791
EAN: 2147483647
Year: 2006
Pages: 166
Authors: Shyam Pather

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net