The client/server development model prevalent in the mid-1990's resulted in extremely easy-to-build rich GUI applications that interacted directly with a relational database. 4GL tools such as Visual Basic and PowerBuilder let even junior developers visually compose both the presentation and most of the backend data binding. While this made for impressive Rapid Application Development (RAD) productivity, the client/server architecture was severely challenged when dealing with real-time environments where the data changes rapidly and applications require visibility to the correct data at all times. As a result, client applications were forced to poll the database continuously to check for changes.

The same is true in today's browser-based or Java Swing-based multi-tier applications, where the user is forced to issue a screen refresh to view the latest state. Real-time applications such as a trader desktop where the screens are continuously refreshed are still sophisticated proprietary applications that require specialized application design to push events from backend servers to the GUI clients. Such applications result in hundreds or even thousands of views like this: Maintain a continuous view of all Intel and Dell orders placed today and notify me when AMD moves up or down by 5%.

However, today a new "push-based" architecture enables data changes to be monitored continuously in a backend data management system and changes continuously pushed to client applications, maintaining a real-time view at all times.

Traditional Databases Are Passive
Most complex GUI screens use complex SQL - multi-table joins, column aggregations, and multiple predicates for filtering, grouping, etc. to construct the dataset being viewed. Consider a real-time application like a financial stock monitoring program or a traffic management system with hundreds of concurrent clients with equally large numbers of complex queries that are continuously being executed once every second. The traditional relational database that's built for storing data efficiently and guaranteeing consistency won't be able to cope with this demand. Relational databases are passive, executing queries on sitting data only. Today's complex applications, however, require a system that can very efficiently execute queries as data streams in.

A SQL Continuous Query Engine - An Active Data Management System
By building a database engine designed specifically so queries can remain standing and active - or continuous - the scalability, reactivity, and organization of multi-tiered data-centric applications can be radically altered. Continuous queries (CQ) let users get new results from a database without having to issue the same query repeatedly.

Queries no longer have to be reissued to refresh result sets, logic that has to execute in response to complex changes in a data model can actively register interest directly from the source, and business logic can be safely co-located with application data in a relational model without scalability limitations.

Continuous querying technology works through an engine that efficiently groups and filters predicates from large numbers of queries, enabling several key things to happen:

• When the server first gets a continuous query, it not only replies with an initial result set, but it analyzes the query predicates (selection criteria) to group it logically with other similar queries.
• The engine can then quickly identify what continuous queries are affected by any given data modification (an insert, update, or delete against the relationally structured operational data).
• The engine can send only the deltas to each CQ client needed to update its existing result set, in effect exactly the data necessary for the client to hold a materialized view of data from the server.

The inherent power of this technology lies in both its simplicity and natural scalability. With an in-memory database and some very simple extensions to existing query languages, we're suddenly capable of building a middle-tier that combines all of a database's operational benefits and none of its limitations. At the same time, we can build application server clients that express interest in data through conventional queries without having to trade performance for data currency or functional sophistication for development effort.

Where Does It Apply?
While the focus of this article is to illustrate the power of CQ to provide real-time view maintenance in graphical user interfaces, the power of this paradigm is well beyond this. Continuous querying is part of a new data management paradigm called stream data processing (see the References section below for further information) and can be used to monitor multiple streaming sources of data, analyze these streams for patterns of interest, and respond instantaneously. The sources of the data could be disparate - RFID sensor events, events from business applications across an enterprise, external sources, etc.

If the applicability of the technology were to be characterized in two points they would be:

1. Data is changing very rapidly and decisions have to be made instantaneously.
2. The system can analyze hundreds to thousands of patterns (rules or query predicates) with thousands of events pouring in every second.

Leveraging CQ in a Financial Order Tracking System
A great example of a system with stringent real-time data requirements is a securities trading order-tracking system. Diverse event streams such as customer orders, order executions, and market data quotes must be combined into a continuously updated view provided to multiple end users. Early implementations - built at a time of much smaller order volumes and slower workflows - used client/server architectures that required GUIs to poll the database for updates.

As trading volumes grew and firms realized that they could gain a competitive advantage with faster trading systems, we started seeing trading application servers that could publish real-time updates to trader GUIs. Implementing this functionality efficiently - with predictable low-latency and high throughput - required a much more sophisticated development model than client/server could provide and so the effort to build, maintain, and operate these systems grew quickly. The example in Figure 1 strips functionality to its bare essentials to illustrate the relative merits of conventional and CQ architectures.

Data Model
For simplicity's sake, our example uses only two tables - Orders and Quotes, as shown in Table 1.

The Orders table simply tracks the orders clients have sent you and the number of shares filled in each one. The Quotes table keeps track of market activity for any symbol that exists in the Orders table, so the GUI displays market activity associated with the order to the responsible broker. This means that we have three event streams coming into the system: orders from clients, order fills from a stock exchange, and market data quotes from a quote feed. These three event streams must be coalesced into a coherent view within the Order Tracking System. Naturally, in the real world these systems are much more sophisticated - requiring transactions, many more data entities and streams, and complex user interactions, but we'll keep things simple and explain later how the example may be extrapolated to additional complexity.

We'll describe the components and options for this use case in three sections. The first shows how to build a simple database publisher application for quotes and orders. The second shows how you might build a client application using plain JDBC, or with the addition of database and JMS queues, and third how you'd build a client application with Continuous Querying. Note that the full source code and pre-configured runtime configurations for the DataPublisher CQExampleClient applications are available for download at www.gemstone.com/download/.

A Simple Data Publisher for an Order Tracking System
The Data Publisher application is quite straightforward. It instantiates Order and Quote generation simulators and handles their events by "publishing" them in a JDBC database with SQL Insert and Update statements.

Let's take a closer look at the Publisher application by inspecting some of its code. The classes we need to understand are SimpleDataPublisher, SimpleQuoteGenerator, and SimpleOrderGenerator.

• SimpleOrderGenerator has a pre-defined list of Orders and Customers. Its constructor has only one argument - a listener that implements the methods onNewOrder(SimpleOrder o) and onOrderUpdate(SimpleOrder o). In the constructor it creates a basket of 63 orders (which results in onNewOrder() callbacks), and then spawns a thread to gradually fill the orders by incrementing the fillQty property of each one.
• SimpleQuoteGenerator generates simple market data quote streams in response to calls to its addSymbol (String symbol) method. It's constructed with a listener that implements onQuote ( SimpleQuote q ), and it spawns a thread that randomly updates each Symbol's quote at a steady rate.

SimpleDataPublisher connects to a JDBC database with a JDBC driver, either creates or clears out the Quote and Order tables with DDL/DML statements, and then uses the two generator classes as event sources. Each generator callback event (onQuote(), onNewOrder() and onOrderUpdate()) fires logic to perform a SQL INSERT or UPDATE to the CQ server, thus creating or modifying records in the Quote and Order tables. It contains only the three following members:

Connection c = null; // The JDBC Connection to the CQ Server
SimpleQuoteGenerator quoteGenerator = new SimpleQuoteGenerator ( this );
SimpleOrderGenerator orderGenerator = new SimpleOrderGenerator ( this );

The constructor of SimpleDataPublisher looks like Listing 2.

Using a handy helper class to hide the boring details, the code gets a handle on a valid JDBC Connection object, creates or clears the necessary tables, initializes the OrderGenerator (which causes an onNewOrder() callback for each new order), and then starts the QuoteGenerator and OrderGenerator threads to initiate the event streams.

Note that for each new order created, SimpleDataPublisher calls the SimpeQuoteGenerator.addSymbol() method to register a new Symbol for quote generation - thus making sure that we get quotes for each symbol handled by system (duplicate symbols are ignored).

Each time SimpleDataPublisher gets a callback invocation, it creates a SQL statement and submits it to the CQ Engine. For example, when onUpdatedOrder() fires, the logic in Listing 1 executes:

You can see that this is a very simple O/R mapping exercise plus JDBC database interaction logic. The logic that executes in response to onQuote() and onNewOrder() is fundamentally the same - and uses SQL/JDBC to update or insert a table record. Once started, SimpleExamplePublisher continues to update the order and quote records in response to the callbacks it's getting from the generators. This is a good time to reflect on the how this fits with your existing experience - the type of code above already exists in most systems, but how often does it serve as BOTH a database update and a notification to systems that have previously queried the updated record?