Continuous Query Streaming App

My Final Project

For my final project, I had to create an application that would be able to display and search a database that was continuous. This was in partnership with a professor at my University, and done as an independent study.

How a continuous query stream works requires a basic knowledge of databases and SQL. I will use general MySQL terms to relate how a user would interact with a continuous query stream.

In a regular MySQL query, if you wanted to select say, all the information of a person, you would go SELECT * FROM Person. The results you get are in the form of a table. What you see is what you get.

A continuous query differs in that the results are always being updated.

So when you see results of information from a Person, if more information is added to the data base, if you were to query it again, more results will show.

Think of Continuous query streams like looking at stock market prices. For one company alone, every second the price of stock fluctuates up and down until the stock market closes. Thousands of transactions are made based on those prices, and in return those prices change based on those transactions.

The challenges in applications though are:

1. Storage challenges - How would manage 100,000 data points? We can't store every single data point in the data lifetime, unlike a non-continuous stream
2. Performance - The speed at which we can access and display the data for accuracy
3. What if the data point we are looking for hasn't arrived yet?
4. Having a straightforward UI design

In order to combat this, for storage challenges, we decided to add a "Last Accessed" value date and implement FIFO (First In First Out) approach to removing data. So whenever you query the stream, the "Last Accessed" date would update. Then, the oldest "Last Accessed" information would be removed from the stream.

So, in the application, the user can define the amount of queries they want to hold. If they say, "I want to hold 300 queries", when the amount hits 301, it goes to the oldest entry and removes it.

For the second challenge of performance, it was very difficult to have the stream just refresh whenever new data arrived. Some data arrived in batches, some didn't arrive for 5 seconds, other points arrived in quick succession. It wasn't convenient for the user. Sometimes, the data came in so fast that as a user, we would have been unable to see the data that arrived in the first place.

To combat this, I implemented a "Pause/Resume" button. This allowed the user to pause the stream and view the data. The query would still be streaming in, but would be held in a "cache" so that when the query was resumed, only the newest data (since paused) would update.

Additionally, a refresh of 5 seconds was implement. In 5 seconds, the application would check to see if new data had arrived, and update as a single batch.

Third, what would the application do if the information I was querying did not yet exist? It the stream currently does not have an entry, but may in the future, how would the application deal with this?

A "saved query" list was created to handle this situation. If a query searched returns 0 results, the user will be asked "would you like to save this query?" and if yes, be placed on the list. The user is then free to execute other queries.

While other queries are executing, it will also search for queries in the "saved queries" list. If any results do appear, a notification and highlight on the list is shown. The user can then select the saved query and execute it, and the result will be shown.

Finally, the UI challenges meant that many UI aspects needed to be dynamic. Column selection, search specification, table names and so on. This was a challenge in itself. Dropbox with check marks was implemented for column selection, and dynamic input boxes generated relative to each column selected.

Demo and Code Link?

For proprietary reasons (in which it belongs to the University) I cannot disclose a demo or code, but it is hosted privately on GitHub.

How I built it

Built using JFX and Java. The hardest part about building this project was trying to keep the clone clean and separated between the UI and the back end, as well as dealing with the large (and fast) amounts of data constantly streaming in.

The original idea was to have the application on a mobile device, so although the application was designed to be ported to mobile (and challenges of mobile taken into account), the application was actually built on Desktop. This was due to my familiarity with Java Desktop and speed at which I could develop an application.