An explanation of SQLite, debunking myths.

Discussion in 'Resources' started by tehbeard, Jan 7, 2011.

Thread Status:
Not open for further replies.
  1. Offline

    tehbeard

    This is mainly an attempt to explain SQLite to everyone here, so as to stop the flood of flatfile vs. Database threads.

    Wikipeida article: http://en.wikipedia.org/wiki/SQLite

    What is SQLite: SQLite is a database management system that is very lightweight database system compared to "full" systems like mysql. (<1MB DLL/SO)

    But I don't want to install a server daemon/I can't: You don't have to, SQLite is a programming library, you simply link to it to use it, no servers, no establishing a secure network connection, just open("file.db") .

    But databases are complex! I just want to store simple variables: This is a valid point, I have no doubt that either flat files will be accomodated for (unlikely) or a utility class will be coded by someone to keep any "complicated" database code from being duplicated/nessecary.

    Any more questions, please ask them here, I will try to help.

    lets try to keep discussion of flatfile vs. Database in one thread.

    I would also like to say I have no bias over flat vs db, so don't go flaming. I'm merely trying to keep the community informed.
     
  2. Offline

    feverdream

    I think using a sql api when its not needed is bloated, and wastes memory.

    Nobody has hundreds of thousands of configuration key/values; I don't see the point of using this when it just takes up more ram than a simple hashmap/dictionary of key/value pairs would.

    Then again I come from a heavy embedded devices development background; I may be biased and my idea of a lot of ram may be different from yours. That said, I think users should have the choice to run "slim" systems.
     
  3. Offline

    croxis

    As mentioned, SQLite is a small library, less than a meg of ram.

    Minecraft is anything but slim by the way. A small server (approx 6 people) fills up 512 megs of ram without much of an issue. 1 Meg for SQLite is quite insignificant.

    Also the embedded device paradigm belongs there, with embedded devices. I would also not try and shove gnome or kde onto a smart phone.
     
    Mentioum likes this.
  4. Offline

    fffizzz

    is there anything like phpMyAdmin for manipulating these?
     
  5. Offline

    feverdream

    Thats another issue I don't like. I should be able to open a config file in vi - emacs priests go diaf ;) - and edit it.
     
  6. Offline

    LynxofCP

    Yep, there sure are. I don't use SQLite myself often, but I did go hunting for an editor once and stumbled across http://sqlitebrowser.sourceforge.net/
    There is a big list of managers maintained by the SQLite website at http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

    I'm all for having SQLite as a database backend as it can be useful for more than just configuration files. There are quite a few plugins out there that I believe benefit from an SQL backend, such as Stats and Achievements, having a unified place for configuration isn't a bad thing.

    I'm not sure what the developer's intentions are, but I don't think that the Bukkit API itself should restrict itself to any particular database or database API if we can avoid it, although I'm hesitant to suggest a complete data access API. But the reason I say this is because for example, if the Bukkit API were to explicitly use JDBC objects in the method signatures, it would prevent the API from being portable to servers that are not written in Java.
     
  7. Offline

    feverdream

    A fast look at these optiosn shows NONE of them can be used for a server that is remote that doesnt require a full LAMP stack to use.

    Like I said, bloated and useless. If I cant manage it remotely as my server is dedicated, its useless to me.
     
  8. Offline

    \\slashies

    For a lot of things loading a configuration file is fine. In fact one commenter suggested putting everything into a common configuration file. I like the idea, but only for configs.

    What I think is poor is taking flatfiles with you everywhere. Every time you want to track something create a new config file? That quickly grows out of hand and then you end up in concurrency hell as different things want to write to the same file. Lag spike for at least one person on a good day, or some pretty terrible side effects on a bad day.

    feverdream had a terribly good point, using a hashmap. Now you have to serialize it because the server is shutting down, or an arbitrary timer went off to commit your changes to the disk. If you don't mind threads perhaps a writer thread is more your style. Then things start to get out of hand again again as you need to keep track of more and more threads and small changes, one per plugin.

    Databases are around because they are fast, someone else made them right, and they are better than anything you can come up with. They just are. They've been doing it since before most of us were born (Hello 70 year old minecraft programmer! Tell your generation sorry that we turned your hard work into 4chan!)

    Keep configuration condensed into a few sane locations, then use a DB. If you prefer a hashtable's simplicity (they really are BLUSHINGLY easy to use and good at their job), then here: https://github.com/doublebackslash/sqLiteKeyValueStor

    I'm writing it with minecraft in mind, and it works. Right now. It's just a thin Key->value store (like a hashtable) with namespaces so that different plugins (or multiple parts of one plugin) can have freedom and simplicity in choosing their keys (player.getName() comes to mind). SQLite backed, thread safe, separate writer thread to get things on disk as fast as possible while returning from an insert or update only a few microseconds slower than using a raw hashtable.

    The only caveat is that is uses Java serialization, so you have to watch the versions of your objects and possibly write a translator / updater to be run on your objects if something changes which native serialization can't handle. You can do this without your users having to know (say, when you load the plugin).

    By tonight I might even have the non-fully cached version ready so you can specify an upper limit on the number of values stored in memory, using a bloom filter to save disk access to negative returns.

    I know this is the second time I've posted almost the same thing, but I'm tired of having a hundred config files in the minecraft directory and I really REALLY hate writing Create Read Update Destroy functions one at a time. I think you should too.

    If you think there is something better out there that is as easy to use (or you like it better, that is fine too!) post it. Lets just agree flatfiles suck and make it easy for everyone to use a database (programmers and suers) without having to go 3 layers deep into DB hell (remember when you didn't know anything about databases?)

    Thoughts? Feelings? Random streams of profanity?
     
  9. Offline

    LynxofCP

    I'm not sure that's entirely true. A few of them have a dependancy on PHP, but that doesn't neccessarily dictate that it has to be a Linux server running Apache and MySQL (which would defeat the purpose)

    So it seems to me that you are looking for a non-web based method of doing configuration. If you're running a server that doesn't have a gui that you can access, then you're limited to using the SQL command line client (which is very light on dependencies). Based on your responses so far, I would hazard to guess that you're not much interested in learning SQLite's syntax which is fair enough, I understand that. But me? I'd rather use a system that allows me to represent my settings as they truely are, rather than having a configuration file that a colon out of place means that I have to restart my server to solve the problem (a problem I had with Achievement's overcomplicated configuration file before it moved to SQL as an example)

    Edit:
    Nice post slashies. That sums up my feelings quite well, but I do understand feverdream's desire for command line configuration too. But perhaps this is a burden that plugin developers should take on and allow configuration from within their plugins?
     
  10. Offline

    feverdream

    Well if its random string sof profanity your looking for... ;)

    I envision serialization to disk to be that "end of server process" thing that doesn't really effect users. Config files are just singleton objects anyway, so why not keep them in memory to be used as needed? Its only a few k/v pairs.

    Adding a single hashtable full of hashtables - insert we put a hashtable in your hashatable so you can search while you search meme here - to store config - not log - data just makes sense to me. Its a fast data structure, and as long as each plugin uses a different name and the core common bukkit config has is own key, things should be fine.

    I can see some rare corner cases where a db would be needed - a general bb style block destroy/place log db, etc - but to be honest, that shouldn't be stored in the config anyway as that would be very plugin specific.

    And no I don't want a GUI of any type for configuration, I do everything over ssh like everybody else who uses a remotely co-located linux server thats been locked down for security.
     
  11. Offline

    \\slashies

    I thought of that too, just stuff the whole thing into a file using and end of process runnable and call it good. I still would have a lot of files that way, but making a directory isn't hard. I could live with that. What I couldn't live with were crashes. I couldn't trust the users' hard earned progress to not come to an OOM error, or a random glitch (we ARE on the bleeding edge here, after all)

    I'm with you 100% on not effecting users, though. That should be priority 1.
     
  12. Offline

    Joshua Burt

  13. Offline

    phondeux

    Can I access the SQLite database from bukkit and simulataneously have my cgi scripts from the webserver parsing it for web page results of in-game activity? My app is going to have a killboard.
     
  14. Offline

    \\slashies

  15. Offline

    NathanWolf

    Hopefully it doesn't seem like I'm going around cross-post-jacking with Persistence, but if you are at all interested in using data in your plugins, I really urge you to check it out!

    You don't have to deal with SQL at all.

    I even plan on implementing a flat file data store at some point (YML), if you're really dead set on it.

    You can use Java annotations to mark up your existing data classes, and then use get/put/remove/getAll accessors to store and retrieve instances. You can mark up primitives, objects, lists, lists of objects, enums... and I think that's about it. You must specify one and only one id field (again, using annotation markup), and there are some other options in the annotations for tweaking things like auto-generated column names.

    Everything is cached in-memory, and now that I know about BukkitScheduler, I'm going to implement incremental auto-save.

    It's still WIP, but it's already being used in NetherGate and ProtectedDoors- the latter is not my plugin, so I actually already have one "client"- pretty psyched about that :)

    The other cool thing about Persistence is that it's a form of cross-plugin communication. If you make your DAO's public, any other plugin can get/put/remove them via persistence. Since it's all cached in-memory, everybody gets the same instances.

    Comments always welcome, of course!
    --- merged: Feb 11, 2011 5:06 PM ---
    Regarding my post above, webbukkit integration is "on the list" for Persistence- meaning you could install an additional plugin (along with webbukkit and Persistence), and then have an admin page where you can view all of your data.

    Note that you can already do this in Persistence with "persist list" in-game or via the server console.

    You can explore all the global data, as well as any data used by any Persistence-enabled plugins.

    "/persist list global.player" and "/persist list global.player.PlayerName" actually make a great "free" player tracking tool :)

    The first command lists the id (name) of every player that has ever been on your system (that Persistence knows about).

    The second command lists the fields of the global PlayerData DAO, which has some handy stuff like last/first login, last disconnect, last known location/orientation, etc.
    --- merged: Feb 11, 2011 5:07 PM ---
    Sorry, I didn't know this was around- hope I don't step on any toes here! :D
     
  16. Offline

    Joshua Burt

    There are many solutions to implementing a data layer for your application layer (in this case a plugin). Some are more appropriate than others given your project requirements and goals. :)
     
  17. Offline

    phondeux

  18. Offline

    \\slashies

    Sorry about the broken link. Long story short is yes they can access it both at the same time, but it does DB level locking while a read is open. That means that from the time you start your query to the time you close the resultset everything else is waiting on you.

    Just keep that in mind and close the resultset early and often and you should be pleased with the results. Also: cache in memory wherever you can.
     
Thread Status:
Not open for further replies.

Share This Page