Plugin Suggestion: Database Abstraction

Discussion in 'Spigot Plugin Development' started by smellyonionman, Sep 23, 2018.

?

How many plugins on your server support using MySQL?

Poll closed Oct 21, 2018.
  1. 1

    40.0%
  2. 2

    0 vote(s)
    0.0%
  3. 3

    20.0%
  4. 4

    0 vote(s)
    0.0%
  5. 5+

    40.0%
  1. Hey folks, I was reading some problems others were having with their SQL and I've thought about my own, I want to suggest an idea so more experienced developers can evaluate its veracity.

    I think there are many use cases for a plugin which will act like / spoof a MySQL server, accepting input as MySQL and outputting the code based on its config settings.

    Config options could include forcing a table prefix, forcing different plugins to use their own tables (when some just don't at all,) support for plugins that require UseSSL=true, and maybe converting that SQL to a flatfile.

    When you run shops, block loggers, ban plugins, and a host of others that often use or require storage it becomes a mishmash of flat files alongside poorly organized tables and rows. I'd appreciate a layer of abstraction to help me organize this all better, and a plugin would bring the configuration for such a solution to one convenient place.

    I was warned it would be a RAM and storage Wh*** but maybe there are other opinions. I am sure it will require advanced knowledge of database security to prevent injection or other things I'm not qualified to speak on. So I defer to you!
     
  2. Optic_Fusion1

    Resource Staff

    This isn't a resource so remove the resource tag
     
  3. Senmori

    Senmori Retired Resource Staff
    Retired Patron

    Ideally, your storage solution (MySQL, YAML, sqlite, etc) should not be exposed. As well, the ideal solution is to have some sort of interface define what you want to store (i.e. a method to store stats) and the storage solution will translate that into the appropriate language.

    What you're suggesting is something that many people do, and when done correctly, makes migrating from one storage solution to another super simple.
     
  4. Awesome, thanks for your thoughts Senmori. Could you provide an example of what you mean by Interface? I have yet to get familiar with any software that can juggle MySQL, YAML, and SQLite. But it doesn't sound like you're talking about something that will run on a Minecraft server.

    I was hoping to evoke a solution for server admins who get one (or no) database(s) from their mc host and know little about web design or how to set up their own SQL servers. They won't be able to modify the MySQL.ini file in any way, for example (or is it .conf, I have no recollection.)
     
  5. Senmori

    Senmori Retired Resource Staff
    Retired Patron

    Yeah, so when I say interface I mean a Java interface. All your plugin should see is that interface. It shouldn't matter how you save the data, just that it gets saved.

    Code (Java):

    public interface StorageSolution {

        void saveStats(Player player);
    }
     
    And you would have an implementation of that (in this case we'll use YAML, but it can be anything)
    Code (Java):

    public class YamlStorageSolution implements StorageSolution {

       public void saveStats(Player player) {
          savePlayerStats(player);
       }

       private void savePlayerStats(Player player) {
           ... do the actual saving here
       }
    }
     
    You could then replace your entire storage solution with another and your plugin(s) would not even care.
    You could also do that with your configurations, if you know what data you need then create an interface for it.

    Too often I see plugins using both YAML and SQL in the same class when that makes no sense. Seperate your logic and you'll find it's much easier to maintain and improve.

    You could also take in input from command-line doing this method if you really wanted to, it's not hard to adapt good abstraction to many situations.