I want to point out to all of you an interesting video, which I will use as a basis to talk about an Informix extension that could find some application in MMORPGs. Check out this link (it’s a video):
As you would have observed from the video, this is a system designed to monitor real time energy usage inside a home – turn on the dryer and up spikes your energy meter! I would find this really useful at my home – it would tell me exactly how much each energy consuming device in my house is costing me – and then I can make adjustments to reduce my energy bill. Of course that might mean, I may end up limiting the up time of my computer, which in turn would mean I would be blogging less and .. – OK, never mind. I want to bring your attention back to what I think is an important and interesting aspect of this system – time based data capture. This data is what is characterized as time series data. Time series data are identified by timestamps and stored in a database. Many other examples come to mind in terms of time series data – stock prices, seismogram data, network performance monitoring, etc.
MMOGs can capture some interesting time series data. An example would be the possession of virtual assets over time by players. Among other uses, this data is also useful for instance, to validate a player’s claims of ownership of an asset or his current level – where someone calls into the game’s customer support and claims the loss of an asset or disputes that he was actually at a higher level. With time series data, support/service staff can backtrack possessions/levels to specific timestamps within a given day. Another really interesting (or creative ?) application that comes to mind is using time series data for “replays” – imagine being able to replay parts of the game back !!
This is where the power of the Informix TimeSeries extension comes into play. Instead of a row per timestamp, all entries related to the time series are entered into the same row of an Informix table. TimeSeries is what is known as an extended data type in Informix that has its own set of powerful analysis and manipulation routines. You could store time series data as row per timestamped entry – but this leads to poor performance and inefficient storage especially considering the fact that time elapsed data can really grow.
For the sake of simplicity let’s assume that you are tracking the position of a player. Let me state up front that the tables/examples used here are purely for illustrative purposes (meaning I haven’t put any emphasis on the actual organization of the data and I haven’t validated any syntax, etc, etc).
With Informix TimeSeries data type, this could be stored in the a table as shown:
|DATETIME YEAR TO FRACTION (5)||Point||CHAR|
As you can see, a TimeSeries data type allows you to keep a single row for every player irrespective of how many times assets change hands or how many times they change their position. This also helps save storage space.
To understand how this works, think of the TimeSeries column as a container for a row subtype. This subtype represents a structure of elements (t1, t2, t3, …). Each element represents a single set of data for a timestamp. In the positioning table example, this sub-type is labeled “pos_chg”. The TimeSeries data type acts as a constructor for this subtype as shown in the table: TimeSeries (pos_chg).
Again each element (t1, t2, t3, …) is another row datatype that begins with the timestamp column and has additional columns. The Timestamp column can be used as an index for the elements.
The creation and loading of time series data is more elaborate and I am not getting into it here – this is well documented in the Informix TimeSeries Guide. My intention here is to illustrate the power and flexibility of theTimeSeries extension.
Once created and loaded, a variety of SQL routines (as well as C and Java API access routines) can be applied to this data – select individual elements (by timestamp, offset, specific position); modify elements or set of elements by specified time range, at a given offset; retrieve subsets of data using aggregate functions; compare and perform arithmetic operations on a pair of timeseries data and much more. These functions demonstrate the real versatility of using the TimeSeries data type extension.
As I mentioned before, this type of information is extremely useful for validating customer claims or for replays of important parts of the game. And although I mentioned that I am not getting into all the details of creating and loading the TimeSeries, I am still going to give you a bonus (heck, it’s the holidays!!) – check out this article on IBM developerworks – it is a nice beginners article on how to create and use the Informix TimeSeries extension and it’s based on game data ! (well sort of , sports stats to be more accurate ).