Given that I’m in the lab full-time lately, and most everyone else has a lot of other commitments now due to the semester having started, I’ve found myself with a bit of spare worktime. I wanted to do something productive while I wait for an opportunity to collaborate with Rob and Daniel on making all of the RTMg User Interfaces cohesive. I’ve had an idea in mind for an app that I’d like that involves my dorky tabletop gaming hobbies. As I was thinking about that app after work one day, I realized that it is actually a great opportunity to develop a potentially useful custom database technique for Android. Without going into detail about my own personal interests in having this sort of capability, I thought I’d post about what I’m hoping to accomplish. Check it out behind the read more!

Basically what I want to create is a means for an Android user to make customized tables in their phone. Now, by this I don’t mean something like Excel or Calc (which already have many implementations on Android). Rather, what I mean is a queryable, SQL-ish database that doesn’t require any ‘icky’ technical knowledge on the part of the user. Ideally, users specify a ‘classification’ of data which designates the different fields they would like in their database. The user is then able to populate his or her database with various elements that they can later query.

As an example of how this would be used, a user who is a Football fan could create the classification ‘Teams’ with fields ‘Wins’ and ‘Losses’. He could then add several Teams elements: Chargers, Saints, Cardinals, etc each with their current Win and Loss totals. Once the database is created, if the user wants to compare information about teams in his database, he simply queries the database for the team by its name. The information is then displayed in a table that appears on his phone, and he is able to add as many element ‘instances’ as he’d like. He can do a side-by-side comparison of every team in the NFL, a comparison of the top two teams, or just pull up his favorite team by itself to brag to a friend about how well they’re doing.

This example is obviously a bit dilute and less meaningful than any real-world application would be, but it illustrates the basic concepts behind what I’m trying to do. The beauty of this type of project is that it has a large amount of possible uses including, but not limited to, bioinformatics Android stuff that the lab might want to do in the future. There are definitely some difficulties involved in what I’m trying to achieve, especially in bending the nature of the SQLite database that Android uses. Basically, the main issue is that (as far as I undestand it – I’m learning SQL as I go…) the database expects that I know ahead of time what fields I want to put my data into. SQLite makes it very convenient to create databases if I know what I want when I compile – deciding what I want in the database at runtime is a bit trickier.

My first approach to the problem is just to use some type of hybrid solution wherein I tell SQL that I just want two fields: a title, and a json string. This allows me to leverage all the convenience, speed, and robustness of SQLite while simultaneously cheating in the ability to change what I store on the fly. What I’ll have to develop is a system for writing and parsing meaningful json objects to describe what users tell me they want in their database. I will then connect this to a (hopefully) easy to use, friendly GUI which asks the users what data fields they want and helps them populate their database. The final step is to create a pretty display layout to show the user their data.

I have a hunch that the last bit, writing a way to display the data, will be more application-specific and probably not a part of the final package. There could, I suppose, be a generic version of this application that tries to handle all types of data, but I would imagine that such an application would be too broad and not very useful. Alternatively, I imagine that developers would be able to use such a library by applying it to more specific uses. This would allow them to leverage some knowledge about the subject matter to display the data in a more meaningful way.

Ultimately, it may not be a project that I finish, depending on whether our RTMg stuff starts picking up speed again. If I do get a semi-functional version of my weird little hybrid database though, I can see how it will very easily end up being very convenient for something bioinformatics related in the future.