Overview of User-Defined Matching Logic

This section provides an overview of the concepts related to user-defined logic in SQL-NS applications. As you'll see, the user-defined logic model is really an extension of the SQL-NS application model you already know. Applications that use user-defined logic look much the same as those that use developer-defined logicthe few important differences are highlighted here.

Contrasting Developer-Defined Logic and User-Defined Logic

In applications based on developer-defined logic, users are limited to the subscription templates that developers provide in the various subscription classes. To take our music store application as an example, users can subscribe for new song notifications only by artist or by genre, because the logic we implemented matches only on these fields. Users cannot, for example, subscribe by a combination of artist and genre, or by album name, because the application contains no logic that can evaluate such criteria.

User-defined logic lifts this restriction by allowing users to freely construct custom matching logic at subscription time. Returning to the music store example, with user-defined logic, one user could choose to subscribe by song title, another by a combination of album name and genre, and yet another by the number of words in the artist name. Each of these subscriptions would specify its own matching logicthe logic in the first subscription says that a match occurs when the song title in the event matches a particular value, the second says that the album name and genre must equal a pair of specified values, and the third says that the result of a function that counts the words in the artist name must be greater than a specified value.

The important point about user-defined logic is not the sophistication of the custom matching criteria (all the preceding examples could easily have been implemented in developer-defined match rules). Instead, the point is that an almost unlimited variety of subscriptions can be created without a developer first defining templates for their matching logic. The developer just defines a single subscription class and configures it to rely on user-defined matching logic in each subscription. With this model, the developer need not try to anticipate all the kinds of subscriptions that users will want.

The flexibility of the user-defined logic model comes at the expense of scalability. Because each subscription may have unique logic, matching is more expensive than when developer-defined logic is used. With developer-defined logic, all subscriptions of a given subscription class can be evaluated with a single query. User-defined logic may require several queries to evaluate all subscriptions. SQL-NS does perform some optimizations, such as evaluating subscriptions together with structurally similar matching logic, but even so, user-defined logic is generally the less performant of the two models.

Note

For a description of how SQL queries can be used to efficiently evaluate user-defined logic, see the sidebar "Evaluating User-Defined Logic with Queries" (p. 45) in Chapter 3, "The Simplest Notification Application: Stock Quotes."

However, the performance cost of user-defined logic usually isn't prohibitive. Only the very largest applications (those that process millions of events and subscriptions each day) may find performance unacceptable. In most small- to medium-sized applications (and even some fairly large ones), the cost of using user-defined logic is tolerable and a worthwhile trade-off for the flexibility gains.

Note

Empirical evidence indicates that the maximum throughput of an application that uses user-defined logic is about one-fifth that of the equivalent application built with developer-defined logic. Although this fraction may seem alarming, remember that it's onefifth of the incredibly high maximum throughput that SQL-NS normally offers. Few applications actually come close to utilizing the maximum throughput of the SQL-NS engine, so the reduction is less significant than it may seem.

The one-fifth number is a rough guideline, based on a representative set of performance tests. The relative performance of user-defined and developer-defined logic in any given application depends on a number of factors, including the schema of the event data, the average complexity of the subscriptions, and the extent to which subscriptions differ in structure from one another.

Supporting User-Defined Logic in SQL-NS Applications

SQL-NS applications that support user-defined logic are built in essentially the same way as those that support only developer-defined logic. They are defined in ADFs, compiled into database objects, and run by the SQL-NS engine. Their subscription management interfaces (SMIs), built with the SQL-NS Subscription Management API, create and record subscription data in the application database tables.

Support for user-defined logic is specified in an application's subscription classes. Those subscription classes that support custom matching logic define special kinds of event and scheduled rules. Rather than describing developer-defined matching logic, these rules configure the use of user-defined logic: they specify the data over which user-defined logic can be expressed, the security context for the execution of that logic, and the means by which the matches generated by the logic are transformed into notifications. In all other aspects, subscription classes that support user-defined logic are no different from other subscription classes: they can have subscription fields, chronicles, index-creation statements and may specify filegroups for physical storage.

Note

A single application can have some subscription classes that support only developerdefined logic and others that support user-defined logic.

When a user creates a subscription of a subscription class that supports user-defined logic, she must specify the matching logic she wants, in addition to values for any subscription class fields. The matching logic is specified as a Boolean condition over some input data.

You can think of the condition as a logical expression that must evaluate to true for the input data to match the subscription. The input data is usually an event or, in the case of scheduled subscriptions, data stored in a chronicle table. The particular source of input data is defined in the configuration information within the subscription class's rules. The section "Expressing User-Defined Logic with Conditions" (p. 595), explains conditions in more detail and describes the kinds of user-defined logic that can be expressed in the SQL-NS condition model.

The user interface for specifying condition-based matching logic is implemented in the application's SMI. The particular user interface for creating and managing conditions varies from application to application; the SMI designer is free to choose the most appropriate presentation mechanism. But whatever its interface, every SMI uses the SQL-NS Subscription Management API to manage the underlying subscription data. The API provides classes and methods for representing conditions that encapsulate user-defined logic.

In the following section, we look at the ADF elements used to define subscription classes with rules based on user-defined logic. The extensions to the SQL-NS Subscription Management API for user-defined logic are covered in the section "Building Subscription Management Interfaces for User-Defined Logic," (p. 606).

Defining Subscription Classes that Support User-Defined Logic

As you've seen in the sample ADFs in previous chapters, a typical subscription class definition specifies the subscription class name, a set of data fields, event or scheduled rules, and, optionally, chronicles. When you want to support user-defined logic in a subscription class, the only thing that changes is the nature of the rules. Before looking at the ADF elements used to specify user-defined logic, let's briefly recap the basic ideas about rules as we've used them so far (in subscription classes based on developer-defined logic).

The purpose of every rule is to define how notifications are generated from subscriptions. Rules can be of two types: event triggered and scheduled. Event triggered rules are evaluated whenever events of a specified event class (known as the triggering event class) arrive; the logic of an event rule states how events of the triggering event class are matched against subscriptions to form notifications. Scheduled rules are evaluated at the schedule times specified in subscriptions; they describe how scheduled subscriptions are matched, usually against chronicled data, to produce notifications.

A typical event rule declaration has the following form:

 <SubscriptionClass>   ...   <EventRules>     <EventRule>       <RuleName>...Rule Name...</RuleName>       <Action>         ...SQL Statement...       </Action>       <EventClassName>...Triggering Event Class Name...</EventClassName>     </EventRule>   </EventRules>   ... </SubscriptionClass> 

The three elements within the <EventRule> declaration specify the rule name, an action SQL statement, and the name of the triggering event class.

Scheduled rule declarations look similar:

 <SubscriptionClass>   ...   <ScheduledRules>     <ScheduledRule>       <RuleName>...Rule Name...</RuleName>       <Action>         ...SQL Statement...       </Action>     </ScheduledRule>   </ScheduledRules>   ... </SubscriptionClass> 

Within the <ScheduledRule> element, only a rule name and action are declared. Because scheduled rules are not triggered by events, no triggering event class name is specified.

Note

The <EventRule> element is explained in detail in the section "Declaring Match Rules in the ADF" (p. 155) in Chapter 5, "Designing and Prototyping an Application." The <ScheduledRule> element is covered in the section "Matching Logic for Scheduled Subscriptions" (p. 172), in Chapter 6, "Completing the Application Prototype: Scheduled Subscriptions and Application State."

In both event and scheduled rule declarations, developer-defined matching logic is specified in the <Action> element. The logic takes the form of a SQL statement that expresses the matching criteria.

In this section, we introduce rules that match based on user-defined logic, instead of developer-defined logic. These rules are declared using the same <EventRule> and <ScheduledRule> elements, but the new <ConditionAction> element is used in place of the <Action> element. <ConditionAction> defines the kinds of conditions that subscribers can use to specify custom matching logic and how matches determined by that userdefined logic result in notifications.

The use of <ConditionAction> instead of <Action> is the only structural difference between the declarations of rules based on user-defined logic and those based on developer-defined logic. Conceptually, rules declared with the <ConditionAction> element still define how notifications are generated from subscriptions. They just don't specify the matching logic within the rule definition. Instead, they rely on user-defined logic to do the matching. In the following section, we look in detail at the <ConditionAction> element and its subelements.

The <ConditionAction> Element

The <ConditionAction> element specifies several properties related to the structure and processing of user-defined conditions. Before we look at those properties in detail, it's important to understand where the <ConditionAction> element fits among the other elements in a rule declaration.

The <ConditionAction> element can be used within the <EventRule> element as follows:

 <SubscriptionClass>   ...   <EventRules>     <EventRule>       <RuleName>...Rule Name...</RuleName>       <ConditionAction>         ...       </ConditionAction>       <EventClassName>...Triggering Event Class Name...</EventClassName>     </EventRule>   </EventRules>   ... </SubscriptionClass> 

As you can see, <ConditionAction> appears in place of the <Action> element. The parts of the event rule declaration that specify the rule name and the triggering event class name remain unchanged.

The <ConditionAction> element can also take the place of the <Action> element within a <ScheduledRule> declaration, to create a schedule rule that performs matching based on user-defined logic:

 <SubscriptionClass>   ...   <ScheduledRules>     <ScheduledRule>       <RuleName>...Rule Name...</RuleName>       <ConditionAction>         ...       </ConditionAction>     </ScheduledRule>   </ScheduledRules>   ... </SubscriptionClass> 

Caution

A single rule may not declare both an <Action> element and a <ConditionAction> element. In other words, you must choose either developer-defined logic or user-defined logic for a particular rule. Within a single subscription class, however, you can declare some rules with <Action> elements and others with <ConditionAction> elements, though this is rarely done in practice.

Whether used in an event rule or a scheduled rule, the <ConditionAction> element has the following internal structure:

 <ConditionAction>   <SqlLogin>...Login Name...</SqlLogin>   <SqlUser>...User Name...</SqlUser>   <InputName>...Table or View Name...</InputName>   <InputSchema>...Schema Name...</InputSchema>   <SqlExpression>     ...SQL Statement...   </SqlExpression> </ConditionAction> 

Within the <ConditionAction> element, the <SqlLogin> and <SqlUser> subelements specify the security context in which user-defined logic is executed when the SQL-NS engine evaluates the rule. These elements and the related security concepts are explained in the section "Securing User-Defined Logic" (p. 593).

The <InputName> and <InputSchema> elements define the input data over which userdefined condition logic can be written. The <InputName> element specifies the name of a table or view in the application's database and the <InputSchema> element specifies the name of the schema containing that table or view.

In event rules, user-defined conditions are usually expressed over the triggering event class's event data. Thus, the <InputName> element usually specifies the name of the triggering event class's event view (which is always the event class name) and the <InputSchema> element specifies the application schema name. In the case of scheduled rules, the input data for user-defined conditions usually comes from a chronicle, so the <InputName> name specifies the chronicle table name and the <InputSchema> element specifies the containing schema.

Note

In general, a rule's input data can come from any table or view in the application database. Although in most cases event rules take their input from event views and scheduled rules from chronicles, this is not a requirement.

The <SqlExpression> element within <ConditionAction> specifies a SQL statement that inserts rows into the notifications view, based on the matches determined by the userdefined logic. To understand the job of this SQL statement, we need to go back to an example of developer-defined logic and take a closer look at the SQL statement from a match rule's <Action> element. Figure 18.1 shows a dissection of the SQL statement taken from the music store application's event triggered match rule.

Figure 18.1. A dissection of the developer-defined SQL statement from a match rule.

As Figure 18.1 shows, the SQL statement can be divided into three parts. The part shown at the bottom of Figure 18.1 joins the input views based on the matching criteria. The result of these joins is a set of rows that represent the raw matches. The middle part of the statement in Figure 18.1 projects the selected data into a form that matches the schema of the target notification class. The part of the statement shown at the top of the figure inserts the projected data into the notification class's notifications view.

You can think of the three parts of the statement as performing three distinct functions:

1.	Select the matching data.
2.	Project the matching data into a form that corresponds to the schema of the target notification class.
3.	Insert the projected data into the notifications view of the target notification class.

Conceptually, rules based on user-defined logic need to perform these same three functions. But they differ from rules based on developer-defined logic in the mechanism used to select the matching data (the first function in the preceding list).

When evaluating rules based on user-defined logic, SQL-NS engine evaluates each subscription condition against each row in the input table or view. Those subscriptions with conditions that evaluate to true for a given input row are said to match that input row.

Note

For the sake of simplicity, you can imagine the subscription conditions being evaluated one at a time, for each row in the input table or view. However, in practice, SQL-NS uses a more efficient batching strategy to evaluate many conditions at a time. The final results of this evaluation strategy are the same as if the conditions had been evaluated one at a time.

SQL-NS makes the results of the condition evaluation available in a view. This view, referred to as the matches view, contains one row for every match between the subscriptions and the inputs. Each row in the matches view contains all the data from a pair of matching input and subscription rows. Thus, the schema of the matches view is a combination of the schemas of the input table or view and the subscription class view. For matches based on user-defined logic, the data in the matches view roughly corresponds to the results of the joins shown at the bottom of Figure 18.1; it represents the raw matches between the subscriptions and the inputs. The matches view is illustrated in Figure 18.2.

Figure 18.2. The matches view is populated with the results of subscription condition evaluation.

Data in the Matches View

To understand the contents of the matches view, the following definition may help:

Consider each row in the input table or view to be a tuple, I, of the following form:

   I = (i1, i2, ..., iN) 

The values i1, i2,..., and iN correspond to columns in the input table or view.

Treat each subscription as a tuple, S:

   S = (s1, s2, ..., sN) 

The values, s1, s2,..., and sN correspond to columns in the subscription class view (derived from the fields in the subscription class schema).

Each subscription also has an associated user-defined condition, C. This can be thought of as a Boolean function that operates over input tuples:

   C(I)  {true | false} 

For any given input tuple, I, C evaluates to either true or false.

At the time of rule evaluation, for every combination of I and S, the SQL-NS engine evaluates C. SQL-NS populates the matches view with a tuple, (i1, i2,..., iN, s1, s2,..., sN), if and only if C is true when evaluated against I.

Columns in the matches view that are taken from the input are prefixed with "Input." and those taken from the subscriptions view are prefixed "Subscription.". For example, if the input is a table with columns, A, B, and C, and the subscription class has fields, F1, F2, and F3, the matches view contains the columns Input.A, Input.B, Input.C, Subscription.F1, Subscription.F2, and Subscription.F3. This naming scheme prevents column name collisions and clearly indicates the source of the data in each column of the matches view.

Note

Don't be confused by the use of the period (.) in the names of the matches view's columns. SQL Server considers the period a valid character in column names. The period does not have any special meaning, as it does in languages such as C#when used in a column name, it's just another character.

The SQL-NS compiler creates a dedicated matches view in the application schema for each rule that has a <ConditionAction> element. The name of the matches view is always the rule name.

By populating the matches view, SQL-NS takes care of selecting the matching data (the function fulfilled by the joins shown at the bottom of Figure 18.1) for rules based on userdefined logic. However, the other functions depicted in the figure, projecting the matching data into the form of the notification class schema and inserting it into the notifications view, still need to be performed. This is the job of the SQL expression defined in the <SqlExpression> subelement in the <ConditionAction> element. The SQL expression selects data from the matches view, projecting the appropriate columns, and inserts it into the notifications view. SQL expressions in <ConditionAction> elements generally have the following form:

 INSERT INTO [<SchemaName>].[<NotificationClassName>] SELECT  [Subscription.SubscriberId],         [...device name...],         [...locale...],         [Subscription.<Field1>],         [Subscription.<Field2>],         ...         [Subscription.<FieldN>],         [Input.<Field1>],         [Input.<Field2>],         ...         [Input.<FieldN>], FROM    [<SchemaName>].[<RuleName>] 

The SQL expression selects from the rule's matches view (which is named the same as the rule) and inserts into a notifications view (named the same as the corresponding notification class). Recall that all notification views have SubscriberId, DeviceName, and SubscriberLocale as their first three columns, so the first three items in the select list provide these values.

Notice that the subscriber ID is taken from the Subscription.SubscriberId column in the matches view. Because every subscription class view always has a SubscriberId column, every matches view always has a Subscription.SubscriberId column.

The device name and locale can come from various sources, as you've seen in previous chapter's examples. In the simplest of applications, the values for these columns are hardcoded. In other applications, they may come from subscription class fields, in which case their values could be selected from the corresponding "Subscription." columns in the matches view.

The remaining columns in the select list correspond to the remaining fields in the notification class schema. Values can be taken from fields in the matches view, given as constants, or computed by SQL expressions.

Note

The SQL expression in the <ConditionAction> element can perform further filtering of the rows in the matches view if additional, developer-defined matching criteria need to be applied, beyond the user-defined matching logic. Such filtering could be implemented in a WHERE clause on the SELECT statement in the SQL expression.

Together, the user-defined subscription conditions, the SQL-NS engine components that populate the matches view, and the SQL statement specified in the <ConditionAction> element perform all the processing necessary to generate notifications. This is equivalent to the processing normally done by the SQL statement in a developer-defined rule's <Action> element.

Processing of Rules with <ConditionAction> Elements

The introduction of user-defined logic into the SQL-NS application model sometimes causes confusion about when rules are evaluated and what data is available at evaluation time. It's a common misconception that the processing of rules with user-defined logic is triggered by changes in the data in the input table or view. This is not the case. Another common misconception is that the generator scopes data in the input table or view, much like it does in the event and subscription views. Again, this is not the case.

To understand how rule processing really works, we need to revisit the quantum processing model. Recall that the generator divides time into discrete, fixed-length intervals called quantums. It processes quantums one by one, in time order. During the processing of a quantum, the generator executes the application's event and scheduled rules over the data that is in scope for the quantum.

In the case of developer-defined logic, the SQL statements in the rules are written against views of the event and subscription data. Before executing the rules, the generator populates these views with the events and subscriptions the rules should process during the current quantum. Only the event batches that were committed between the quantum's start and end times are included in the event views. On the subscription side, all event triggered subscriptions are included in the subscription views, but only those scheduled subscriptions due to be evaluated between the quantum's start and end times are included.

Note

For a thorough description of the basic quantum processing model, see the section "Inside the Notification Generator" (p. 395) in Chapter 11, "Debugging Notification Generation."

User-defined logic does not change the fundamental quantum processing model. Within a quantum, the generator populates event and subscription data in the views according to the quantum's start and end times and uses the same algorithm as before to choose event and scheduled rules to be evaluated.

Event rules are selected based on the event classes of the event batches that are in scope during a quantum. Every event rule that lists one of these event classes as its triggering event class is selected. Scheduled rules are selected based on the subscription classes of the scheduled subscriptions that are in scope. All scheduled rules in these subscription classes are selected. The precise ordering of the event and scheduled rule execution depends on the ordering model selected. See the section "Ordering Models for Rule Execution," (p. 401), in Chapter 11, for a detailed explanation of the supported ordering models.

When a rule defined with a <ConditionAction> element is executed, the associated user-defined conditions are evaluated against the data in the input table or view. It's important to understand that at the time of execution, the generator just treats this input table or view as a source of data. It does not specifically scope the data or build views over it in any way. If the input happens to be one of the event views, the data has already been scoped. In some cases (such as the example we look at later in this chapter), the condition input is a view defined as a join between the events view and some other table or view. In these cases, the data in the input view is effectively scoped as a result of the data in the event view being scoped. However, the generator doesn't manipulate the input data directly. In the case that the input is a table, all data in the table is in scope.

The results of condition evaluation are populated in the matches view, as illustrated in Figure 18.2. Thereafter, the SQL expression defined in the rule's <ConditionAction> element is executed. This inserts rows into some notifications view, based on the data in the matches view. This completes the processing of the rule.

To summarize, the generator uses the standard quantum model to scope event and subscription data. Selection of rules to process within a quantum is based purely on the events and subscriptions in scope, not the data in the input tables or views. At evaluation time, the input table or view serves only as a source of data for the user-defined conditions.

Securing User-Defined Logic

As shown in the earlier section "The <ConditionAction> Element" (p. 586), a rule's <ConditionAction> element contains <SqlLogin> and <SqlUser> subelements. These elements are used to specify a SQL login name and username that define the security context in which user-defined logic executes when the rule is evaluated.

Special security mechanisms are needed because user-defined logic is potentially dangerous. In essence, a user-defined subscription condition is really a piece of code: it defines a predicate that will be executed. Supporting user-defined logic means, therefore, that you're providing external, possibly untrusted, users with a way to inject code into your application. When used properly, this provides legitimate users with a flexible means to customize subscription matching. But malicious users might try to inject harmful code via subscription conditions. If they succeed in doing this, they might gain access to privileged information, bring your application down, or induce it to act on their behalf. The support for user-defined logic in SQL-NS was designed with these security concerns in mind and includes several layers of safeguards to prevent subscription conditions from being used in harmful ways.

First, as you'll see in the section "Expressing User-Defined Logic with Conditions" (p. 595) only safe, read-only operations can be used in condition expressions. Also, the APIs and execution mechanisms for user-defined logic treat all user-supplied data values with suspicion. These values are never directly inserted into executable statements. This greatly reduces the possibility of code injection attacks that might transform safe operations into harmful ones. And all these mechanisms are implemented on top of the already stringent role-based security model used throughout SQL-NS.

As a final safeguard, SQL-NS allows you to configure the engine to run user-defined logic in a low-privilege security context. The SQL-NS engine should already be configured to execute all database operations as a low-privileged account, but you can define an even more restricted security context for user-defined logic (one that doesn't even have permissions to access the internal objects needed by the SQL-NS engine). If this mechanism is used properly, then, even if a malicious user was able to circumvent other safeguards and introduce harmful code into the application by means of a subscription condition, that code will be executed in the context of a user account that lacks the privileges to perform harmful operations.

You control the security context for user-defined condition evaluation via the <SqlLogin> and <SqlUser> elements in the <ConditionAction> declaration. The <SqlLogin> element specifies the name of a login account on the SQL Server. The <SqlUser> specifies a user account in the application's database.

At compile time, the login name specified in the <SqlLogin> element must already exist on the server. In contrast, the username specified in the <SqlUser> element must not exist in the application's database. The SQL-NS compiler creates the user account and associates it with the given login. At runtime, the SQL-NS engine impersonates the specified user before evaluating condition logic. All user-defined logic is then executed in the security context of this user.

Note

Each rule can have its own security context for user-defined logic. The <SqlLogin> and <SqlUser> elements are defined within the <ConditionAction> element, on a per-rule basis. These elements can have different values in different rules.

After compiling the application, you will need to grant any required permissions to the user account you specified in the <SqlUser> element (although the SQL-NS compiler creates the account, it does not grant it any permissions). Typically, very few permissions are needed. In fact, this account usually needs only SELECT permissions on the rule's input table or view. By keeping the account's privileges to a minimum, you can effectively create a sandbox around the user-defined logic, preventing it from executing any harmful operations.

Note

This is a somewhat unusual use of impersonation. Typically, server applications impersonate specific users to temporarily elevate their privileges to perform user-specific operations. Here, impersonation is used to lower the privileges of the SQL-NS engine before executing user-defined logic.

Caution

It is your responsibility to ensure that the login account you specify in the <SqlLogin> element has only the minimum required permissions. Through impersonation, SQL-NS will execute user-defined logic in the context of whatever account you specify in this element. It does not in any way validate that this account is low privileged. If you specify a login with elevated privileges (for example, an account that is a system administrator or has been granted other system permissions), user-defined logic will run with all the corresponding permissions.

Expressing User-Defined Logic with Conditions

SQL-NS applications that support user-defined logic have one or more rules defined with the <ConditionAction> element. When users create subscriptions of the associated subscription classes, they specify their matching logic in terms of conditions on the input data specified in the <ConditionAction> element's <InputName> and <InputSchema> subelements. SQL-NS defines a particular model for expressing conditions. In this section, we take a detailed look at the kinds of matching logic that can be expressed with the SQL-NS condition model.

The SQL-NS Condition Model

As you saw in the applications we looked at in previous chapters, developer-defined matching logic is expressed in the form of T-SQL statements. This is an acceptable way for developers to define matching logic, but it's not reasonable to expect end users to write T-SQL statements to express the custom matching logic they want. Instead, SQL-NS provides a simplified, yet powerful, model for expressing user-defined matching logic in terms that users can understand. As the previous sections of this chapter have mentioned, this model is based on conditions.

When creating a subscription, users are given a description of the input data in the table or view specified in a rule's <ConditionAction> element. They are then asked to specify a Boolean condition expression that, when evaluated against a row of the input data, determines whether a match has occurred. This condition is stored as part of the subscription, along with the regular subscription data (like the values of the subscription fields).

At runtime, the condition is evaluated against the rows in the input table or view. If a subscription's condition evaluates to true for a particular input row, a match has occurred between the subscription and that input row. A new row will be populated in the matches view, containing the data in the input row and the subscription.

As a basis for some examples, imagine that we extended our music store sample application to support user-defined logic. In the ADF, we would declare a subscription class with one event rule, defined with a <ConditionAction> element. The rule's <ConditionAction> element declares its input to be a view over all the properties of the songs submitted as song added events. The view therefore has columns SongId, SongTitle, ArtistName, AlbumTitle, Genre, and DateAdded. (Ignore, for the moment, the question of how such a view might be defined or populated.) Subscriptions of this subscription class express userdefined logic in terms of conditions over this input view.

To create a subscription that matches song added events when the artist name is Miles Davis and the album title is "Kind of Blue," a subscriber could define a condition as follows:

 ArtistName = "Miles Davis" AND AlbumTitle = "Kind of Blue" 

A subscription that matches song events only when the genre is not "Rock" or "Hip Hop" could express its matching criteria with the condition:

 NOT(Genre = "Rock" OR Genre = "Hip Hop") 

Although we can't expect users to write T-SQL statements to express their matching logic, we do ask them to specify conditions such as these. The difference may not be immediately apparent, especially because conditions look a lot like the WHERE clauses in T-SQL queries. But writing conditions is fundamentally simpler than writing T-SQL queries because conditions are always defined over a single row of input data. Writing a query, in contrast, requires reasoning about sets of data, and most users don't think this way. Reducing the definition of matching logic to condition expressions over single inputs makes the model accessible to a large base of users. Furthermore, users rarely actually write condition expressions in text (like the examples shown in this section). Instead, they usually construct the conditions using a rich user interface that simplifies the process.

Note

Other systems that ask users to define conditions over single inputs include search engines and rule builders (like those found in many rich email clients). The popularity of these systems suggests that users generally are capable of expressing meaningful logic through conditions.

The SQL-NS condition model consists of a few basic constructs that can be composed to express complex conditions. This section showed a few simple examples of conditions that can be constructed with this model. The following section explains all the supported condition constructs and the ways they can be composed.

Types of Conditions

As you saw from the previous section's examples, conditions are just Boolean predicates. These predicates are constructed from terms, relational operators, and logical operators. This section outlines the types of allowed terms and operators, as well as the ways they can be combined to form complex conditions.

To begin, let's consider the terms (or values) in condition expressions. Terms can be one of the following:

• A constant value, such as the number 5 or the string "Miles Davis".

• A field in the input data, such as the ArtistName column in a view of song properties.

• An arithmetic expression, such as 5 + 2, involving two other terms and the binary arithmetic operators (addition, subtraction, multiplication, and division).

• The result of a call to a scalar-valued SQL function using other terms as values for the function's arguments; for example, if NumberOfWords() is a SQL function that returns the number of words in a string argument, NumberOfWords(ArtistName) can be a term.

• A computed property of the input data, as determined by a user-defined property rulefor now, don't be concerned with this type of term; property rules are explained in the section "Using User-Defined Rules to Express Subscription Conditions," (p. 598).

Terms can be composed recursively in arithmetic expressions and function calls. In other words, the individual terms in an arithmetic expression term can themselves be arithmetic expression terms involving simpler terms. For example, the term 5 + (52) is an arithmetic expression term involving two terms: the constant value, 5, and the simpler arithmetic expression term, 52. Similarly, in a functional call term, the terms used as values for the function's arguments may be arithmetic expressions or the results of other function calls.

The SQL-NS condition model defines a set of leaf conditions that can be expressed using the terms in the preceding list. Think of these leaf conditions as templates for the fundamental predicates that can appear in condition expressions. Leaf conditions are the simplest kinds of conditions (so named because they're always expressed in terms of the leaves of a condition expression tree). The supported leaf conditions are

• Simple leaf conditions that compare two terms with the relational operators =, >, >=, <, <=, LIKE, and != (not equals); for example, ArtistName = "Miles Davis".

• Is-null leaf conditions that test whether a term is null; for example, if Genre is a nullable field in the input view, IsNull(Genre) returns true if the value of the Genre field is null in the input row.

• Boolean leaf conditions that test whether a Boolean term's value is true; for example, if the input contains the Boolean field IsHitSong (defined to be of the SQL bit data type), the value of this field can be used as a Boolean leaf condition.

• Between leaf conditions that determine whether a term's value lies between the values of two other terms; for example, BETWEEN(DateAdded, "1/1/1980", "12/31/1989") is true if the value of the DateAdded field is between January 1, 1980 and December 31, 1989.

• Link leaf conditions that determine whether rows in another table, related to the input table or view in some way, satisfy the criteria defined by a user-defined filter rule. Ignore this type of condition for now; filter rules and link leaf conditions are explained in the section "Using User-Defined Rules to Express Subscription Conditions," (p. 598).

Any of the term types can be used in simple and between leaf conditions. For example, a simple leaf condition may compare an arithmetic expression term with a constant value term. Is-null leaf conditions are generally used only with terms that are nullable fields in the input data. Boolean leaf conditions can be used with any term that evaluates to a Boolean value.

A subscription condition can consist of a single leaf condition or a combination of several conditions using the logical operators AND, OR, and NOT. The SQL-NS condition model defines the following compound conditions that combine other conditions:

• AND conditions compute the logical AND of the results of several other conditions.

• OR conditions compute the logical OR of the results of several other conditions.

• NOT conditions negate the result of another condition.

As you might expect, compound conditions are also composable recursively. In other words, compound conditions can be formed by logically combining the results of leaf conditions or other compound conditions. For example, consider the following compound condition:

 (ArtistName = "Miles Davis") OR ((Genre = "Jazz") AND  (BETWEEN(DateAdded, "1/1/1980", "12/31/1989"))) 

This condition contains three leaf conditions: ArtistName = "Miles Davis", Genre = "Jazz", and BETWEEN(DateAdded, "1/1/1980", "12/31/1989"). It has two compound conditions: one OR condition and one AND condition. The OR condition is defined in terms of the first leaf condition and the compound AND condition. The AND condition is defined in terms of a simple leaf condition and a between leaf condition.

Because the SQL-NS condition model does not limit the extent to which terms and conditions can be composed recursively, some very complex conditions can be formed from the few rather simple primitive constructs. As described in the section "Building Subscription Management Interfaces for User-Defined Logic" (p. 606), each of the primitive constructs is represented by a class in the extended SQL-NS Subscription Management API. When building an SMI that allows users to create subscriptions with conditions, you use this API to represent the condition definitions.

Using User-Defined Rules to Express Subscription Conditions

As alluded to in the previous section, some types of terms and conditions make reference to user-defined rules. These user-defined rules are simply reusable pieces of user-defined logic that can be applied across several subscription conditions. Think of a user-defined rule as a part of the logic in a condition, broken out into a separate, reusable piece.

Note

Although the same term, "rule", is used, do not confuse user-defined rules with rules defined in the ADF. A user-defined rule is a piece of reusable user-defined logic that is referenced in subscription conditions. A rule in the ADF defines how notifications are generated from subscriptions, either via developer-defined or user-defined logic.

The SQL-NS condition model supports two kinds of user-defined rules:

• Property rules that define computed properties of a condition's input data.

• Filter rules that can be used with link leaf conditions to determine whether the rows in a table or view related to a condition's input (via foreign keys) satisfy some criteria.

These rule types are discussed in detail in the following sections. Note that the topic of user-defined rules is considered an advanced concept within the area of user-defined logic. If the material in this section appears somewhat confusing, don't be concerned. When we look at some concrete examples in a later section, "Creating User-Defined Rules to Build Conditions," (p. 634) the concepts presented here will become easier to understand.

Property Rules

A property rule is a unit of user-defined logic that computes a single, scalar value, based on a row of input data. Property rules are so named because their results can be thought of as computed properties of the input row. In fact, the SQL-NS condition model allows a property rule's result value to be used as a term in a condition expression, as though it was a field of the input data.

Property rules are associated with an input type, which can be any table or view. A property rule definition consists of an ordered list of statements of the following form:

 IF <Condition> THEN <Value> 

The <Condition> part is a condition over the rule's input data. It is defined using the same constructs as subscription conditions. For example, the condition might be a compound condition that ANDs together two simple leaf conditions that compare fields in the input. The <Value> part of the property rule statement represents a result value and can be defined using any of the terms types supported for condition expressions. For example, the <Value> could be a constant, a field in the input, or an arithmetic expression.

Note

All the support condition and term types are described in the section "Types of Conditions" (p. 596).

When a property rule is evaluated, each of its statements is evaluated, in order. The first statement in which the <Condition> evaluates to true determines the rule's result. The <Value> defined in this statement becomes the final result value for the rule. If no statement's condition evaluates to true, the property rule's value is null.

Note

You can think of the statements in a property rule as being ordered by priority. Earlier statements have higher priority than later statements. If more than one statement's condition evaluates to true, the result value is taken from the earliest statement.

A property rule can be used as a term in any subscription condition based on the same input type. The term's value is the result of evaluating the property rule against the subscription condition's input row. Because property rules are defined independently, they can be reused across several subscription conditions.

Let's continue with the extended music store application described in the previous section and look at an example of a property rule. In this application, the subscription conditions are defined over an input type that contains the properties of songs submitted as song added events. A user can define a property rule over this input that computes a new song property: a Boolean value that indicates whether a song is suitable for dancing. We call this rule CanDance and define it as follows:

 CanDance:     IF Genre = "Hip Hop" THEN TRUE     IF Genre = "Rock" THEN FALSE     IF Genre = "Jazz" THEN FALSE 

This rule computes the CanDance property of a song, based on user-defined logic involving the song's genre (one of the fields in the input view). The rule definition consists of three statements, each of which determines a result for the computed property when the genre is a particular value. The rule's logic says that CanDance is true when the genre is "Hip Hop", but false when it is "Rock" or "Jazz".

With the CanDance rule defined, it can be used to write a subscription condition. Suppose that a subscriber wants to be notified whenever new songs she can dance to are added to the music store. She could define a subscription with the following condition:

 CanDance = TRUE 

The CanDance property can also be used alongside other existing properties of the input to define more complex conditions. If the subscriber wanted to be notified if a song is suitable for dancing or if the song's artist is Miles Davis, she could define her subscription condition as follows:

 (canDance = TRUE) OR (ArtistName = "Miles Davis") 

In the case of the CanDance property rule example, the rule's statements define Boolean results. This is not a requirementproperty rules can be used to define results of any scalar type. Also, property statements need not have constant results, as in this example. Results can be values obtained from existing fields of the input, arithmetic expressions, or any of the other supported term types. In fact, the result of a property rule statement can be the result of invoking another property rule. This kind of composition is possible because a statement's result is just a term and a property rule can be used anywhere a term is expected.

Property rule composition is also possible in another way: the condition in any property rule statement may be defined using a value computed by another property rule. Again, this is possible because property rules can be used as terms, and the conditions in property rules are formed from terms in the standard ways.

Note

For one property rule to be used in the definition of another property rule (either in a statement's condition or result value), the two property rules must have the same input type because they will always be evaluated against the same input row.

The benefit of property rules may not be clear from the simple example given in this section. After all, the logic defined in the property rule could be easily defined directly in the subscription conditions. However, property rules do offer two unique advantages. The first is reusability: the same property rule can be referenced in different subscriptions. Without property rules, the equivalent logic would need to be duplicated in the definition of each subscription condition. The second advantage is that property rules provide a convenient logical abstraction that makes it easier for users to construct complex conditions. When defining a property rule, users only need to think about the logic related to the property it computes. Thereafter, when thinking about subscription conditions, they can regard the computed property as a given, without worrying about its logical definition every time they use it.

Filter Rules and Link Leaf Conditions

As you've seen in the preceding examples, subscription conditions are defined over rows of data in an input table or view. The columns in the input table or view can be used as terms in condition expressions. However, some situations require that conditions be expressed over data that is related to the input, but not stored in the input table or view. Conditions like these can be expressed using filter rules and link leaf conditions. Link leaf conditions join the data in a subscription's input to the data in another table, and filter rules define the criteria that the rows in that other table must meet.

To set the stage for a practical example, consider a stock notification application, similar in concept to the one we looked at in Chapter 3, but designed to work with user-defined logic. Events in the application are stock price changes, each consisting of a stock symbol and a stock price. Imagine that the application has a subscription class with a rule based on user-defined logic. The rule's <ConditionAction> element specifies that the input data for subscription conditions is the stock price change event view. Given this context, it's easy to imagine various subscription conditions that could be written in terms of the stock symbol and stock price in the input data.

But suppose the application also keeps other information about stocks in separate tables. One of these tables stores information about the companies identified by the stock symbols, including the company names and addresses. This table has columns for company name, stock symbol, and company address. A relationship exists between the data in this table and the data in the events view: the two could be joined on their stock symbol columns in a SQL query. This relationship between the events view and the company information table is illustrated in Figure 18.3.

Figure 18.3. Relationship between the stock price change events view and the company information table.

By means of filter rules and link leaf conditions, subscribers can actually define subscription conditions over the stock price change events that take into account the related data in the company information table. Suppose a subscriber wants to get notified about all stock price changes for companies located in the states of Washington and Oregon. Because the company location is not stored in the events view (the condition input), this condition can't be written using only the columns in the input.

To implement this condition, the subscriber breaks the logic into two pieces. The first piece defines a filter over the rows in the company information table that selects only those companies located in Washington and Oregon. This filter is expressed as a filter rule. The second piece of the logic defines the subscription condition by stating that a stock price change event is a match if the related row in the company information table satisfies the filter. This is expressed with a link leaf condition.

Filter rules are associated with an input table or view. They consist of a series of user-defined statements that specify the conditions under which rows in the input should be included or excluded from a resultset. Each statement in a filter rule has the following form:

 IF <Condition> THEN <Action> 

As you probably expect by now, the <Condition> is expressed using any of the supported constructs described in the section "Types of Conditions" (p. 596). Note that this condition is expressed over the input of the filter rule, which may not be the same as the input of the subscription condition in which the filter rule will later be used. In our current example, the input table of the filter rule is the company information table, but the input to the subscription condition is the stock price change events view.

The <Action> specified in each filter rule statement is either include or exclude. The include action means that if a row satisfies the statement's condition, it is included in the filter's resultset. The exclude action means the opposite: if the condition is satisfied by a given row, that row is excluded from the resultset. If a particular row satisfies the conditions in more than one statement and those statements specify conflicting actions, the statement that defines the include action wins. Stated another way, an include action always overrides any exclude actions for the same row.

Note

You can think of a filter rule as a user-defined query. But unlike standard SQL queries in which filtering logic must be expressed in a single, possibly complex, predicate, filter rules use a series of simple filter statements to specify which rows to include or exclude from the resultset. This approach tends to be easier for most end users to understand.

Returning to our stock application example, the filter rule that selects companies in Washington and Oregon can be stated as follows:

 CompaniesInWashingtonAndOregon:     IF State = WA THEN Include     IF State = OR THEN Include 

With the filter rule defined, we can write the link leaf condition that implements the matching criteria for the subscription (match stock price changes for companies located in the states of Washington and Oregon). A link leaf condition joins its input row with a target table or view and then checks whether the related rows in that target table or view appear in the resultset of a filter rule. The target table or view is the input to the filter rule.

The definition of a link leaf condition has the following elements:

• A link operator

• A filter rule

• A series of join clauses that relate the condition input to the input of the filter rule

The link operator defines how many of the related rows in the target table or view must be in resultset of the filter rule to satisfy the condition. Two link operators are supported: ANY and EVERY. If the ANY operator is used, the link leaf condition is satisfied if any of the rows in the target table or view that relate to the input row are in the resultset of the filter rule. As its name suggests, the EVERY operator requires that every row in the target table or view that is related to the input be in the resultset of the filter rule. When the condition input and the target table or view have a one-to-one relationship, the ANY and EVERY operators are equivalent. However, if the relationship is one-to-many, the distinction between the two operators is meaningful.

Note

The two link operators give different results in the case in which no rows in the target table or view are related to the condition's input row. In this case, link leaf conditions that specify the EVERY operator are trivially satisfied. However, link leaf conditions that specify the ANY operator are not satisfied.

The join clauses specified in the link leaf condition define exactly how the input row relates to the table or view over which the filter rule is defined. To understand the content of the link leaf condition join clauses, imagine you had to write a SQL query that joined the condition's input table or view with the filter rule's input table or view. Continuing the example of our stock application, the query that joins the stock price change events view (the input to the subscription conditions) and the company information table (the input to the filter rule) might be written as follows:

 SELECT ... FROM  StockPriceChange     JOIN CompanyInformation         ON StockPriceChange.StockSymbol = CompanyInformation.StockSymbol 

The JOIN clause in this query specifies the columns that relate the StockPriceChange view to the CompanyInformation table. Join clauses in link leaf conditions are similar, but rather than specifying actual join predicates in T-SQL syntax, they specify pairs of columns, one in each of the tables or views to be joined. It is assumed that these columns join the two tables or views via equality predicates. In the preceding example, the join clause can be expressed as the pair of columns (StockPriceChange.StockSymbol, CompanyInformation.StockSymbol).

With this information, the complete link leaf condition that defines the subscription's match criteria can be written as follows:

 LinkLeafCondition(     LinkOperator: Any,     FilterRule: CompaniesInWashingtonAndOregon     JoinClauses:         (StockPriceChange.StockSymbol, CompanyInformation.StockSymbol) ) 

The link leaf condition specifies the link operator ANY, the filter rule we defined earlier, CompaniesInWashingtonAndOregon, and a join clause that specifies the columns used to relate the condition's input view with the filter rule's input table. When this condition is evaluated against an input event row, the join clauses are used to determine the related rows in the CompanyInformation table, the filter rule's input. These rows are then checked against the rows that result from evaluating the filter rule. If any row related to the input is in the filter rule's resultset, the condition is satisfied. Because the filter rule selects only companies in Washington and Oregon, the condition is satisfied only for event rows in which the stock symbol refers to a company in Washington or Oregon. This fulfills the original intent of the subscription.

In this example, the input view can be joined directly to the target table. In other situations, the join requires one or more intermediate tables. For example, imagine that the company information table was split into two tables: a CompanyStockSymbols table that just stores the stock symbols for each company and a CompanyDetails table that stores the rest of the information (the company name and address details). The columns in the CompanyStockSymbols table are CompanyID and StockSymbol; the columns in the CompanyDetails table are CompanyID, CompanyName, Address, City, State, and Zip.

Given this schema, the filter rule that selects companies in Washington and Oregon would need to be defined over the CompanyDetails table (because this is where the address information is stored). However, this table cannot be joined directly to the stock price change events view because no common column exists: the CompanyDetails table does not store stock symbols and the stock price change events do not include company IDs. In this case, the join needs to involve the intermediate table, CompanyStockSymbols. The stock price change events view can be joined to CompanyStockSymbols on stock symbol, which can then be joined to CompanyDetails on company ID. In this case, a set of two join clauses is needed: (StockPriceChange.StockSymbol, CompanyStockSymbols.StockSymbol) and (CompanyStockSymbols.CompanyID, CompanyDetails.CompanyID). You can think of these join clauses as forming a chain between the input view and the target table. This is illustrated in Figure 18.4.

Figure 18.4. Using an intermediate table to join the condition input and the input of the associated filter rule.

Tip

If a link leaf condition and its associated filter rule are defined against the same input table or view, no join clauses are needed.

Link leaf conditions support arbitrarily long chains of join clauses. The first column in the first join clause must be a column in the condition's input and the second column in the last join clause must be a column in the filter rule's input. In each join clause in between, the first column must be in the same table or view as the second column of the previous join clause.

Note

SQL-NS does not explicitly define a limit on the number of join clauses in a link leaf condition. However, in practice, the length will be limited by the maximum query length supported by SQL Server. This should not be a concerncases that require more than four or five join clauses (well within the number supported) are exceedingly rare.

In the section "Creating Filter Rules and Link Leaf Conditions" (p. 638), we look at the code required to create a filter rule and link leaf condition in the context of a real SQL-NS application. The example presented in that section illustrates the concepts introduced here.

Building Subscription Management Interfaces for User-Defined Logic

The SMIs to SQL-NS applications that support user-defined logic must provide users with a way to construct conditions at the time they create subscriptions. The user interface for building condition expressions depends on the particular application. Some applications provide something like a query-builder interface; others present user-interface controls that allow users to drag and drop elements into condition templates. Some advanced applications even allow users to specify condition logic using a textual syntax.

Whatever the form of the user interface, SMIs that support user-defined logic use the SQL-NS Subscription Management API to create and manage subscription records that include user-defined conditions. In Chapter 7, "The SQL-NS Subscription Management API," we looked at the basic API used to manage subscriptions in applications based on developer-defined logic. In this chapter, we examine the extensions to that API for user-defined logic.

This section provides an overview of the API and the patterns used to program with it. In the later section "Building the Music Store Application with User-Defined Matching Logic," we look at practical code examples based on the API described here.

Note

As usual, the coverage in this book is not intended to be a complete class reference. Reference documentation for all the classes, methods, and properties described here is available in the SQL-NS Books Online.

The Condition API

The API for constructing subscriptions with user-defined conditions is an extension of the SQL-NS Subscription Management API covered in Chapter 7. When building an SMI for an application based on user-defined logic, the same basic classes in that API, Subscriber, SubscriberDevice, and Subscription, are used to represent subscribers, subscriptions, and subscriber devices. The new classes and methods introduced in this section are used to represent user-defined conditions.

When creating subscriptions, the SMI code creates Subscription objects in the usual way, initializes their basic properties, and sets their subscription fields. When these subscriptions are of subscription classes with rules based on user-defined logic, two new properties must also be set on the Subscription objects: RuleName and Condition. The RuleName property is set to the name of a rule defined in the subscription class with a <ConditionAction> element. The Condition property is set to a condition object that represents the user's subscription condition. When the Add() method is called on a Subscription object that has these properties set, the condition logic specified in the condition object is validated and then stored in the database, along with the rest of the subscription information.

Condition objects are constructed from various classes that the API provides to represent the elements of a user-defined condition. The SMI code creates instances of these classes and assembles them into condition structures, based on the choices the user makes in the SMI's user interface. The design of the user interface controls and the techniques for gathering information from them are outside the scope of this chapter. Here we focus on the specific classes in the API used to represent conditions. These can be used with any user-interface implementation.

The type of the Subscription class's Condition property is the class Condition. Condition is an abstract base class from which several concrete condition classes are derived. Each of these condition classes represents one of the types of conditions described in the section "Types of Conditions," (p. 596). Specifically, the API defines the following condition classes to represent the various leaf conditions:

• SimpleLeafCondition represents simple leaf conditions.

• IsNullLeafCondition represents is-null leaf conditions.

• BooleanLeafCondition represents Boolean leaf conditions.

• BetweenLeafCondition represents between leaf conditions.

• LinkLeafCondition represents link leaf conditions.

To construct a simple leaf condition, for example, the SMI code creates an instance of the SimpleLeafCondition class and sets its properties to specify the various subelements of a simple leaf condition expression.

Compound conditions are represented by the classes AndCondition, OrCondition, and NotCondition. Instances of AndCondition and OrCondition are initialized with lists of other condition objects that represent the subconditions to be combined with the AND or OR operators. NotCondition objects are initialized with a single other condition object representing the condition to be negated.

Terms in condition expressions are represented by value objects. Value objects are instances of classes that inherit from the abstract base class Argument. The API provides a concrete class (derived from Argument) for each supported term type described in the earlier section "Types of Conditions" (p. 596). Specifically, the following value classes are available:

• ConstantValue represents terms that are constants.

• FieldValue represents terms that are fields of the input data.

• ExpressionValue represents terms that are arithmetic expressions.

• FunctionValue represents terms whose values are obtained by calling SQL functions.

• RuleValue represents terms whose values are defined by user-defined property rules.

Objects of these classes are initialized with values specific to the types of terms they represent. For example, ConstantValue objects are initialized with the literal values they express. FieldValue objects are initialized with the names of fields in the input data.

Value objects are used with condition objects to fully specify condition expressions. For example, a simple leaf condition that represents an equality comparison between a field in the input and a constant value is manifested as a SimpleLeafCondition object, initialized with a FieldValue object, the equality operator, and a ConstantValue object.

The operators that apply to the various condition types are represented by enumerations. For example, the relational operators supported by simple leaf conditions are represented by members of the SimpleOperator enumeration. Similarly, the link operators used in link leaf conditions are represented by members of the LinkLeafOperator enumeration. When initializing SimpleLeafCondition or LinkLeafCondition objects, the values in the corresponding enumerations are used to specify the operators.

The two kinds of user-defined rules in the SQL-NS condition model are represented by the classes PropertyRule and FilterRule, respectively. These classes encapsulate the definitions of each type of rule. PropertyRule objects are initialized with a set of PropertyStatement objects, each representing one of the statements that make up a property rule definition. Similarly, FilterRule objects are initialized with FilterStatement objects that represent filter rule statements.

As described in the section "Using User-Defined Rules to Express Subscription Conditions" (p. 598), both property rule statements and filter rule statements contain conditions. As you would expect, these are represented programmatically with the same condition classes used to represent subscription conditions. Both the PropertyStatement and FilterStatement classes have Condition properties of the base Condition type that can be set to instances of any of the concrete condition classes described in this section.

Because each property statement in a property rule defines a result value, the PropertyStatement class has a Result property. The type of this property is the abstract base class Argument, from which all the value classes derive. Thus, the Result property can be set to an object of any of the value classes.

Filter statements each specify an include or exclude action. On the FilterStatement class, this is represented by the Action property. The type of this property is the FilterAction enumeration, which has the members Include and Exclude. When creating a FilterStatement object, its Action property can be set to one of these two values.

The classes that make up the Condition API are defined in a separate namespace and implemented in a separate assembly from the rest of the SQL-NS API. All the condition, value, rule, and rule statement classes described in this section (as well as the associated enumerations) are defined in the Microsoft.SqlServer.NotificationServices.Rules namespace. Code files that use the Condition API classes must either declare this namespace in a using directive or use the namespace to fully qualify the class names. The implementation of the Condition API classes is packaged into the Microsoft.SqlServer.NotificationServices.Rules.dll assembly. Projects that use the Condition API must reference this assembly.

Because some of the classes in the standard SQL-NS Subscription Management API, such as Subscription, Subscriber, and SubscriberDevice, are also needed in most applications that use the Condition API, the main SQL-NS namespace, Microsoft.SqlServer.NotificationServices, is generally referenced alongside the Microsoft.SqlServer.NotificationServices.Rules namespace. Accordingly, the main SQL-NS assembly, Microsoft.SqlServer.NotificationServices.dll is also usually referenced in addition to the assembly containing the Condition API.

In the section "The AddSubscriptions Program" (p. 617), later in this chapter, we look at some code samples that make use of the classes described in this section. Those samples illustrate the common patterns in which these classes are used.

Composition of Classes in the Condition API

The "Types of Conditions" section (p. 596) explains how various elements of condition expressions can be composed to form more complex expressions. This composition capability is reflected in the design of the Condition API classes.

Several of the condition classes have properties that refer to other condition objects. For example, the AndCondition class is initialized with a list of condition objects representing conditions to be combined with the AND operator. In the Condition API, references to condition objects are always defined in terms of the Condition abstract base class. In the example of the AndCondition class, the list of child conditions is defined as an array of Condition objects.

Because all the concrete condition classes derive from the common base class Condition, references to Condition can always be fulfilled with an object of any of these concrete classes. Returning to the example of the AndCondition class, the array of child condition objects can be populated with several objects of the various leaf condition classes (SimpleLeafCondition, IsNullLeafCondition, BooleanLeafCondition, BetweenLeafCondition, and LinkLeafCondition). Furthermore, because the compound condition classes are themselves descendants of the base Condition class, instances of these classes can also be used as child condition objects in other compound conditions. So the array of condition objects in an AndCondition object, for example, may include other AndCondition objects, each of which composes several other condition objects. This kind of composition can be repeated to form increasingly complex condition expressions.

Like the condition classes, the value classes also derive from a common base class, Argument. Therefore, the same type of composition based on polymorphism is possible with values. References to value objects in the various methods and properties on the API classes are defined in terms of the base type, Argument. These references can be set to objects of any of the concrete value classes. For example, the ExpressionValue class has two Argument properties that represent the two values to combine in an arithmetic expression. These Argument properties can be set to objects of any of the value classes, including other ExpressionValue objects. Thus, an ExpressionValue object can represent complex expressions consisting of combinations of other expressions, constants, field values, rule values, and function values. This ExpressionValue object can then be used in condition objects, in any place a value object is needed.

Caution

Composition introduces the possibility of cycles in a condition or term definition. For example, a condition object may reference a property rule object which references the same condition object. When constructing condition objects, you should be careful not to introduce cycles of this kind. SQL-NS checks condition definitions for cycles when the associated subscriptions are added to the database. If a cycle is detected, adding the subscription fails.