SQLite or XML storage


  • BINNED

    So I decided to learn something new on my free time (god knows my college won't help me there) so I dived into QT Creator as I like software that's multiplatform and all that jazz. As a learning project I picked something simple that doesn't reinvent the wheel: a calculator utility to help freaks such as myself who play EVE Online from time to time. Basically, in game you can mine various types of ore that is refined into various minerals. The app is supposed to calculate the amount of minerals you get from ore and the amount of money you can get for those minerals if you sell them. Pretty basic and boring stuff really.

    Being that I actually want to LEARN something here I don't want to half-ass this thing and use the best practices I can. Where I got stuck at this point is storing information about what ore refines into what. 3 obvious solutions come to mind:

    1. just hardcode it all (yeah, right...)
    2. create a XML file storing the data
    3. use a SQLite DB

    I started off with XML as an obvious choice but let me tell you, that's a LOT of data to input. Then I remembered that you can get a dump of EVE's DB (only game items of course, no user-specific data) freely and legally. But this, of course, contains way more data than I need, so I was thinking of fetching the entire DB on first run, querying it for the stuff that I need and generating my own smaller version of the DB. Or I could just release it with already trimmed DB. I could also parse it into XML in the same manner of course, and just provide the XML with the app without the need for any database libraries in the app itself.



    There is also a matter of being able to toggle visibility of any ore type or mineral (rarely will you actually need ALL of them). With DB it's a simple and fast operation of querying for visible items only and then spawning forms I fetched the data for in the UI. With XML, wherever the visibility data is saved (either as an attribute or in a separated settings file) I will still need to parse the entire XML file.



    Which brings me to user settings:


    1. if DB is used, put them in the same DB as everything else or create a separate DB?
    2. keep user settings as an XML file for an easy way to move your settings from computer to computer whatever format the rest of the data is in?



      To make matters even more complicated, your character skills affect some of the calculations and entering them by hand, especially if you have multiple characters is a pain. Fortunately, EVE provides an API to fetch character data. Which comes in an XML file. Which means, whatever I do I will need to do some XML parsing anyway (so it will be dependent on the XML parsing library no matter what I do), and then decide on how to store that data as well.



      I know I pushed it too far for such a simple app but as I said, it's mostly a learning experience. For an app at this scale it's not really that important, but I'm interested in how it would be done best in a real-world business project. None of the data sets are large enough for the DB approach to have that much of a significant impact, yet the data is already available in that form.



      P.S. - I have to add line breaks myself? Seriously?


  • I've never used QT Creator, but it's C++, right? If I were doing this project, I'd probably build it in something like RealBasic, or even Adobe Air (if you're confident you can make a non-WTF UI.) RealBasic used to (I haven't used it in years, fair warning) have its own internal DB which was based on SQLite.

    On XML vs. DB, I'd definitely go with SQLite. You can distribute its .exe right alongside your project (or just link it in to your program itself), and the database files are just files you can copy around or do whatever with. You can give them a custom extension so it looks like they're something more fancy than a SQLite database. Tons of huge projects already take this approach, like Firefox.

    When you say "user settings", do you mean "the computer user" or "the game character?" Either way, I'd probably put them in a separate DB if only to save the disk space. (Why store the database of recipes twice?) Make sure the user settings can refer to the recipe database cleanly, that is, by friendly name instead of ID number. Otherwise you might break your save files if you add or remove recipes.

    Odds are, whatever toolset you're using, it already has an XML parser in it somewhere. I don't see potentially adding one as an issue.

    Edit: of course you could just make the thing as a web-app and charge access for it, or run ads.



  • Honestly, if I were in your situation I'd just go XML all the way. It'll reduce the amount of technologies you have to work with, which makes things simpler, you can just store the downloaded data in a cache directory (on *NIX that should be ~/.cache/program_name/) and have the program download it as needed, and give the option to force a download of the data. Qt (note casing. QT is QuickTime) should have an XML parsing system already, so your fears of having to parse the entire XML are slightly mitigated (SQLite isn't much more efficient). As for the transfer of settings, settings, application data and cached data all have their own directories anyways (~/.config/program_name/ or ~/.program_name, /usr/(local/)share/program_name/ and ~/.cache/program_name/ respectively on *NIX), if you follow each platform's guide-lines on where things should be placed you'll be fine.


  • BINNED

    @Lingerance said:

    Honestly, if I were in your situation I'd just go XML all the way. It'll reduce the amount of technologies you have to work with, which makes things simpler, you can just store the downloaded data in a cache directory (on *NIX that should be ~/.cache/program_name/) and have the program download it as needed, and give the option to force a download of the data. Qt (note casing. QT is QuickTime) should have an XML parsing system already, so your fears of having to parse the entire XML are slightly mitigated (SQLite isn't much more efficient). As for the transfer of settings, settings, application data and cached data all have their own directories anyways (~/.config/program_name/ or ~/.program_name, /usr/(local/)share/program_name/ and ~/.cache/program_name/ respectively on *NIX), if you follow each platform's guide-lines on where things should be placed you'll be fine.




    Exactly my line of reasoning there pulling on the XML side. I wasn't sure about performance of SQLite as I never used it until now except once when I had to transfer data from SQLite to MySLQ for a web app, but since that was a pretty big data set I just made a script that chewed the data and went away from the computer so I didn't really get the chance to gauge it's performance (TRWTF is I couldn't find a simple way to do it automagically so I put together a quick and dirty PHP script to do the job for me).



    I'm still kinda torn on this tbh, I'll see if I get any more opinions on this, it's not like I'm on a deadline here :)



    On a side note - any handy way to determine the preferred settings folder on Windows that's reliable on both XP and Vista/7 like $HOME on *NIX?



    And I know it's Qt, I guess my finger just kinda sticks on shift since I'm used to typing a long-ish amounts of text that way in all caps, I have some kind of aversion towards using caps lock.

    br/>
    EDIT: yeah, don't do me any favors CS, I wouldn't want you to insert linebreaks on your own... Kudos to regular posters dealing with this editor I guess...



  • I'd use Visual Basic Express -- parse the big dataset down to just the relevant informatio, store that data as XML, and bring it into the application as dataset and use LINQ to do perform the parsing/querying in the application.  But that's just because those are the tools I'm most familiar with.  Never liked C or it's derivatives (+, ++, #).



  • @Medezark said:

    I'd use Visual Basic Express -- parse the big dataset down to just the relevant informatio, store that data as XML, and bring it into the application as dataset and use LINQ to do perform the parsing/querying in the application.  But that's just because those are the tools I'm most familiar with.  Never liked C or it's derivatives (+, ++, #).

    Cross-platform was in the requirements, so VB won't work in this case. Qt Creator (note I got capitalization right!) can do cross-platform, but it's an awful lot of work compared to something like Filemaker or RealBasic, and your UI will probably come out looking funky on any OS other than the development OS. (Unless the development OS is Linux, then it'll look funky everywhere.) Of course, RealBasic doesn't solve that problem, but development goes enough faster that you can use the rest of your time tweaking the UI for each platform to not suck.

    Honestly, the more I think about it, the more I think a web app is the way to go. Then you can use a nice slick language, have your cross-platform cake, and get rid of all the headaches if distributing installers, patches et al. You can still opensource it up, or give control to somebody else.



  • @blakeyrat said:

    @Medezark said:

    I'd use Visual Basic Express -- parse the big dataset down to just the relevant informatio, store that data as XML, and bring it into the application as dataset and use LINQ to do perform the parsing/querying in the application.  But that's just because those are the tools I'm most familiar with.  Never liked C or it's derivatives (+, ++, #).

    Cross-platform was in the requirements, so VB won't work in this case. Qt Creator (note I got capitalization right!) can do cross-platform, but it's an awful lot of work compared to something like Filemaker or RealBasic, and your UI will probably come out looking funky on any OS other than the development OS. (Unless the development OS is Linux, then it'll look funky everywhere.) Of course, RealBasic doesn't solve that problem, but development goes enough faster that you can use the rest of your time tweaking the UI for each platform to not suck.

    Honestly, the more I think about it, the more I think a web app is the way to go. Then you can use a nice slick language, have your cross-platform cake, and get rid of all the headaches if distributing installers, patches et al. You can still opensource it up, or give control to somebody else.

    You're right, the OP did say that they liked "multi-platform".  My answer then -- MonoDevelop.



  • @Medezark said:

    You're right, the OP did say that they liked "multi-platform".  My answer then -- MonoDevelop.

    Then you have to re-write the UI twice. Not impossible, but much more annoying than the alternatives.

    Edit: or three times, if Mono has a UI framework for OS X. Of course, if Mono *doesn't* have a UI for OS X, then your app is X11, and sucks ass on that platform... so not an ideal solution, IMO.



  • @blakeyrat said:

    @Medezark said:
    You're right, the OP did say that they liked "multi-platform".  My answer then -- MonoDevelop.
    Then you have to re-write the UI twice. Not impossible, but much more annoying than the alternatives.

    Edit: or three times, if Mono has a UI framework for OS X. Of course, if Mono *doesn't* have a UI for OS X, then your app is X11, and sucks ass on that platform... so not an ideal solution, IMO.

    Ok, I give up.


  • BINNED

    I have to correct you there blakeyrat, Qt will actually use the native control set of whatever OS it's compiled on (I am not 100% sure about Mac but I don't know why it wouldn't). I compiled it in my Win XP VM and it looks perfectly native. I did not try it under 7 (for some reason it ran awfully slow in VmWare so I went back to XP). It even looks great under Gnome using GTK+ theme for Qt4.



    Mono will be inconsistent since it uses GTK# on all platforms (I only tried it under Linux so I can't be 100% sure about that but that's the impression I got from tutorials and documentation).



    That said, some minor tweaks are still needed between Linux and Windows. Windows version of qmake took issue with using %Lf as a format for long double for example. But nothing major was broken in neither code nor GUI.



    I pretty much settled on XML as a personal preference of having configuration files that can be edited without any additional software if anyone wants to do it for any reason. The amount of data is pretty small so I don't expect to run into any performance problems with it. Thanks for feedback everyone.



  • @Onyx said:

    I have to correct you there blakeyrat, Qt will actually use the native control set of whatever OS it's compiled on

    Yes it will, but that's only half the battle. You also have to lay-out the controls differently for each OS, ensure the fonts are correct (although Qt might do that), etc.

    Plus you're stuck with C++. Meaning you'll be spending half your development time just dealing with C++ ass-pains and not writing useful code.

    @Onyx said:

    Mono will be inconsistent since it uses GTK# on all platforms (I only tried it under Linux so I can't be 100% sure about that but that's the impression I got from tutorials and documentation).

    GTK+ is crap. You're better off just coding a GUI separately on each platform.

    @Onyx said:

    But nothing major was broken in neither code nor GUI.

    If it works, it works. I have my doubts, but you've used it and I haven't, so.


  • BINNED

    @blakeyrat said:

    Plus you're stuck with C++. Meaning you'll be spending half your development time just dealing with C++ ass-pains and not writing useful code.

    They actually ironed out a lot of C++'s usual annoyances. Editor will change "." into "->" by itself where needed as you type and built-in classes are pretty nice.

    Only 2 annoying things I bumped into are no classes for usual types like int, float etc. (so no ToString() and similar methods for those) and using list templates produces some annoyingly ugly code such as:
    lineEdits = group->findChildren<QLineEdit*>();

    They even implemented foreach as a preprocessor macro. It's quite possible C++ will rear it's ugly head as I dig more into it but so far it's not that bad...


Log in to reply