dimanche 16 octobre 2011

Reference data management in Alkemy Companion

The Alkemy companion can be declined in several kind of applications. Of course, here you have the Android app. Before that, the model of this application was tested as a web app. And you can also imagine a desktop app, an iPhone app, or whatever. There could be a lot of work adapting the software from Java to Objective-C or any other language. That is known and unavoidable. But in all those versions, the data should remain the same. So how can we carry the data from one context to the other with a minimum of transformation, none should be the best ?

First, let see the problem. As a webapp, the Alkemy data is usually stored in a database. This data, all the game references (profiles, abilities, descriptions...) should be available on startup. Even as a webapp, we should consider distributing the code, which means the reference data. If we impose the technology (Java), we shouldn't impose the database. Adding such a constraint would prevent the usage of our application. In an Open Source project, we could count on the implication of the community to translate the scripts from a database system to another. But that work should be made the easiest possible. An other constraint to mention is : how to deal with location ? A web application may display its informations in any language depending on the client, but if the application on a device, you may want to load only the data in the language of the device.

Fortunately, database systems offer a great functionality for this. People usually rely on the database dumps which generate huge SQL queries. This is not the best practice. For data portability, first, we should have a good designed database structure. That design will impose the structure creation queries which are specific to each database management system. For the Alkemy applications, we have something like :

faction(id, name)
profile(id, name, title, factionKey)

The model above does of course not show all data. Writing structural queries is pretty simple, even with the constraints. What about the data ? Well lets organize them as follow : lets write a first text file with lets say

"rk","Khaliman republic"
"tj","Empire of the Jade Triad"
"na","Aurlok Nation"
"ka","Kingdom of Avalon"

And a second with
"rkc01","Cheikh Araoui Ibn Khalid","Khaliman diplomat","rk"
"rkc02","Kabircheikh Hakim Ibn Khalid","Khaliman ambassador","rk"
"rkc03","Dahlia Ibn Malikh","rk"
"tjc01","Captain Lee Ping of the Xi-Yì militia","tj"
On MySql, you can now use the LOAD DATA INFILE statement. This will give, for the factions, something like :
LOAD DATA INFILE faction.txt INTO TABLE faction FIELDS TERMINATED BY ',' ENCLOSED BY '"'

Write another query for profiles.txt and the faction table. Do the same for every file and table. This way, you can load your data more efficiently into the tables, even shortcutting foreign key constraints by issuing a foreign_key_check to 0 prior to the command. This is also a portable command since, for example, the syntaxe is the same for Oracle.

But another benefit is for location, if you want to load only the local data. Write as many localized, and so identified files as needed. The easiest management way is to add the local in the name. During the initialization of the database, simply check the local and load the corresponding file. Here you are, simple and easy.

Now, lets say that you write an Android App. The specifications ask to use the onCreate and onUpgrade functions and load the data into the SQLite database. Instead hard coding queries, you can write only one parameterized query and parse the text file. I18n may is dealt the same way. The benefit of this approach on a mobile device is that you can use the insertion function independently of the data storage source. You can read the data from a local file, may this be shipped with the application or downloaded afterward, but you can also gather data from lets say webservices in the format you want.

So, remember, reference data should never be stuck to its transporter. If you plan to use it in a variety of platforms, favorise a format independent of any platform,and think of all the ways you can load complement data to your datasource.