Event State

The logic implemented in the music store application so far is completely stateless. When events arrive, they get evaluated against the subscriptions, and notifications are generated. Nothing about the processing of the events and subscriptions affects the processing of future events and subscriptions because the application maintains no memory of its past work.

Note

SQL-NS does keep track of which events have been processed (so that they are not processed again), but this is not part of the application logic.

Adding scheduled subscriptions requires the application to maintain state. Because the scheduled subscriptions are not necessarily evaluated at the time that events arrive, the application has to "remember" all the events it sees so that when the scheduled subscriptions are evaluated, they have the appropriate data to work against. In this section, we'll build the logic necessary to maintain event history. Without this, after events are matched against the event-triggered subscriptions, they are effectively forgotten. Although the event data is persisted in the events table, it isn't made available to the matching logic again, and it could be garbage collected at any time.

Event Chronicles

SQL-NS allows applications to maintain state in chronicles. A chronicle is a table (or set of tables) defined by an application to hold its state data. SQL-NS provides places in the ADF for you to declare chronicle tables and a set of chronicle rules that run at specific times to keep the chronicles up-to-date.

SQL-NS supports chronicles of two types: event chronicles and subscription chronicles. This section describes the event chronicles required to keep the event history for the music store application. The "Subscription State" section (p. 170) covers subscription chronicles.

Event chronicles are declared as part of an event class, and the chronicle rules are run whenever new events of that event class are submitted. When designing an event chronicle, you must decide on the schema of the chronicle tables and the logic of the chronicle rules that maintain the data in those tables.

The purpose of the event chronicle in the music store application is to keep a history, or log, of all the events seen. Each entry in the log represents a single event and must contain all the data in the event. Thus, the chronicle table needs a column for each field in the event class schema. Also, the chronicle table should keep a time stamp for each event, indicating when it was added to the chronicle. This time stamp allows the matching logic for the scheduled subscriptions to distinguish old events it has already seen from new events it has yet to process.

Declaring Event Chronicles

In the SongAdded event class declaration, we declare an event chronicle called SongAddedChronicle. This chronicle consists of a single table that stores the song ID (the only field in the event class schema) and time stamp of each song event. The event chronicle rule, described in the following section, inserts new rows into this table whenever new events are submitted.

Listing 6.1 shows the SongAddedChronicle declaration in the SongAdded event class.

Listing 6.1. Declaration of the Event Chronicle

 <Application>   ...   <EventClasses>     <EventClass>       <EventClassName>SongAdded</EventClassName>       <Schema>         ...       </Schema>       <Chronicles>         <Chronicle>           <ChronicleName>SongAddedChronicle</ChronicleName>           <SqlSchema>             <SqlStatement>               --Handle update by dropping the table               --first 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 = 'SongAddedLog'                       AND so.type = 'U'                       AND sc.name = 'SongAlerts'               )                   DROP TABLE [SongAlerts].[SongAddedLog]             </SqlStatement>             <SqlStatement>               -- Create the table.               CREATE TABLE [SongAlerts].[SongAddedLog]               (                   SongId      INT         NOT NULL,                   TimeAdded   DATETIME    NOT NULL               )             </SqlStatement>           </SqlSchema>         </Chronicle>       </Chronicles>       ...     </EventClass>   </EventClasses>   ... </Application> 

Within the <EventClass> element is a <Chronicles> element that may contain one or more <Chronicle> elements. Each <Chronicle> element declares a single chronicle. For each chronicle, the <ChronicleName> element defines the chronicle name, and the <SqlSchema> element defines the SQL code that creates the chronicle tables.

Note that the chronicle declarations require you to explicitly specify the CREATE TABLE statements. This is different from the event schema declaration, for example, in which you specify a set of fields and the SQL-NS compiler generates the appropriate CREATE TABLE statement to create the events table. The reason for this difference is that, unlike the events tables, chronicle tables are completely outside the scope of the SQL-NS engine. For chronicles, you can create whatever tables you need, following any schema and structure you deem appropriate, because only your logic manipulates these tables. In contrast, the events table that the SQL-NS compiler generates from your field declarations is used by the SQL-NS engine and must have certain columns required for internal tracking and coordination.

The <SqlSchema> element can have several <SqlStatement> elements within it. Each of these statements is executed by the SQL-NS compiler in a separate batch, in the order in which they appear in the ADF. You should use separate <SqlStatement> elements whenever you need to separate SQL statements that cannot be run together in a single batch. For example, if you need to create two tables for your chronicle, each should appear in a separate <SqlStatement> element because SQL does not allow you to run two CREATE TABLE statements in a single batch.

Caution

Any statements that would need to be separated with a GO directive in tools such as Query Analyzer should be placed in separate <SqlStatement> elements. SQL-NS does not support the use of GO in any of the SQL code in the ADF.

The code in Listing 6.1 uses two <SqlStatement> elements. The first checks whether the chronicle table already exists and, if so, drops it. The second SQL statement creates the table using standard CREATE TABLE syntax. The statement that drops the table if it exists is necessary because of the way chronicle declarations are handled by the SQL-NS update tools. If you change the chronicle declaration and then update the instance, the SQL-NS compiler reruns all the code in the <SqlStatement> elements. Without the code to first drop the chronicle table if it exists, the CREATE TABLE statement could fail when executed a second time.

Caution

Although dropping the existing chronicle table allows it to be re-created on an update, think carefully about what this means for your application. Dropping the table results in any chronicled data being lost. When you update your application, if you need to preserve the data in your chronicles, you will need to back it up to another location and restore it after the update completes.

The table created to hold the chronicle data is called SongAddedLog (because it maintains a log of all SongAdded events). Data is added to the log table by the chronicle rule, as described in the next section.

Note

The schema qualified name of the chronicle table is SongAlerts.SongAddedLog because it is created in the SongAlerts schema. The schema name is specified directly in the CREATE TABLE statement that created the chronicle table. (There is no dedicated ADF element for specifying the schema name for chronicles.) In this example, the chronicle table is created in the same schema in which the SQL-NS compiler creates the other application database objects (we specified SongAlerts as the value for the <SchemaName> element in the ADF's <Database> element earlier). SQL-NS does not require you to create chronicle tables in the same schema as the other application database objects, but it's common practice to do so because chronicles are really part of the application.

Because the chronicle table has a schema qualified name, the check for its existence (in the first <SqlStatement> in the chronicle's definition) requires a join between the two system catalog views, sys.objects and sys.schemas. The sys.objects view provides a list of all the objects in the database, but does not include their schema names. The join with sys.schemas on the schema_id column obtains the schema name for each object.

Event Chronicle Rules

Event chronicle rules are SQL statements that are run when events arrive. They update the event chronicles with the appropriate data, based on the content of the events. Listing 6.2 shows the chronicle rule used to update the SongAddedLog chronicle table.

Listing 6.2. Declaration of the Event Chronicle Rule

 <Application>   ...   <EventClasses>     <EventClass>       <EventClassName>SongAdded</EventClassName>       <Schema>         ...       </Schema>       <Chronicles>         ...       </Chronicles>       <ChronicleRule>         <RuleName>UpdateSongAddedLog</RuleName>         <Action>           INSERT INTO [SongAlerts].[SongAddedLog](SongId, TimeAdded)           SELECT events.SongId, GETUTCDATE()           FROM   [SongAlerts].[SongAdded] events         </Action>       </ChronicleRule>     </EventClass>   </EventClasses>   ... </Application> 

The <ChronicleRule> element appears after the <Chronicle> declaration. It specifies a rule name in the <RuleName> element and a SQL statement in the <Action> element. Before the SQL-NS engine runs the rule's <Action> statement, it first constructs a view over the data in the events table that contains just the events currently being processed. The view name is the same as the name of the event class (SongAdded in this case). This is the same view that the event-triggered match rule (defined in Listing 5.9 in Chapter 5, p. 155) runs against.

The chronicle rule inserts rows into the SongAddedLog table by selecting all the song IDs from the SongAdded events view. It obtains the time stamp by calling the built-in SQL GETUTCDATE() function.

Testing the Event Chronicle

Add the code shown in Listings 6.1 and 6.2 to your ADF, either by typing it in manually or using the supplementary ADF, ApplicationDefinition-5.xml, as described in the "Adding Code to the ADF" section (p. 130) in Chapter 5. When your ADF is ready, update your instance as you did before, by running the update instructions listed in the "Making Updates to an Instance and Its Applications" section (p. 131) in Chapter 5.

When the update completes, you can examine the MusicStore database and see that the SongAddedLog table is created. Now let's submit a batch of events and observe the chronicle rule executing:

1.	Open the C:\SQL-NS\Samples\MusicStore\MusicStore.ssmssln solution in Management Studio (if you don't already have it open).
2.	Open the SubmitEvents.sql script (you'll find it in the Solution Explorer, under the Queries folder in the MusicStore project).
3.	Run the SubmitEvents.sql script.
4.	Wait about 30 seconds. This is necessary because the event chronicle rule is run by the generator, which is configured to look for new event batches every 15 seconds. Depending on when in the generator's processing cycle you submit the events, it may take up to 30 seconds for the events to be processed.
5.	Open a new query window and execute the following query: USE MusicStore GO SELECT * FROM [SongAlerts].[SongAddedLog] This returns a set of rows that show the event data just added to the chronicle table by the event chronicle rule.

Assuming that the subscriptions that you added earlier are still in your database, a set of notifications will be generated because the event-triggered subscription match rules are also run against the events you submitted (you'll see these notifications in the output file, C:\SQL-NS\Samples\MusicStore\FileNotifications.txt). Note that SQL-NS always runs an event class's event chronicle rules before it runs any event-triggered match rules. Because of this, your chronicles always are updated before the match rules are run, and you can use the chronicle data in the match rules if appropriate. (The event-triggered match rules we have in our application at this point do not use the chronicle data.)