[DEV] MOCDBLib v5 - MySQL/SQLite simplified API and DB Config [1240]

Discussion in 'Inactive/Unsupported Plugins' started by cwingrav, Sep 15, 2011.

  1. Offline

    cwingrav

    A simple database plugin to help plugins create persistence in either MySQL and SQLite databases. It is configurable via a simple text file (DBLib.properties) and helps with the creation of safe queries by an API that helps you use Java's method of getting around SQL injection attacks. Once you choose MySQL or SQLite, your queries are basically agnostic to that choice (unless you use SQL specific calls to MySQL or SQLite).

    Download:
    MOCDBLib.jar

    Features:
    • toggle between MySQL and SQLite w/o changing code
    • manages DB configuration
    • use same plugin for all DB handling in all scripts that use DB calls
    • helps you write safer SQL queries that avoid SQL injection attacks
    Changelog:
    - see project page

    Coding Help:
    see the project site: https://sites.google.com/a/cwingrav.com/moc-plugins/plugins/mocdblib

    Installation and Configuration:
    Drop this into the plugins directory of your bukkit server. It will create a default DBLib.properties file the first time it runs, selecting SQLite by default. The DBLib.properties file is created in the plugin's folder so : "$CRAFTBUKKIT-HOME/plugins/<your plugin name>/DBLib.properties".

    example config:
    (MySQL on the current machine with default port and password 1234 in table 'playtimetracker')
    Code:
        MySQL=true
        host=localhost
        database=playtimetracker
        username=root
        password=1234
        port=3306
    attributes in the DBLib.properties file:
    MySQL: If true, connects to MySQL. If false, uses SQLite.​
    database: The actual database's name that is created inside the MySQL server or SQLite file.​
    MySQL Only:
    host: The machine it looks to connect to. "localhost" refers to the server's machine.​
    username: The name of the login used to MySQL. Remember that for MySQL, "root" is not "root" on your machine, but "root" for the database.​
    password: The password used to login as the username on the host.​
    port: The port to connect to on the server machine to find the MySQL server. I.e. where the MySQL server is listening. By default, and if this property is omitted, it uses the default MySQL port of 3306.​


    MOCDBLib Vs SQLLite:
    Thanks to the SQLLight project many of the original ideas. If you are used to that code, you will easily learn this.
    Advantages over SQLLite project:
    1. this is a plugin so many plugins can use it
    2. updating is easier (it does not require cracking open plugins and inserting SQLLite into that jar file)
    3. safer queries as it helps you avoid SQL Injection attacks
    4. MySQL and SQLite APIs are merged so as a programmer, you don't notice the difference between which db solution you are using
    5. totally different code organization
    6. few extra API calls to help you out
    7. configuration is simpler
     
    sddddgjd likes this.
  2. Offline

    cwingrav

    Updated the API on the project page and hotpatched the jar file. Code posted shortly.
     
  3. Offline

    Plague

    this is not GENeral, but DEVelopment, also you are missing CB tag
     
  4. Offline

    cwingrav

    Ok, moved to DEV and added the [1060] tag.

    Let me know what it takes to move from submitted to released. Cheers.
     
  5. Offline

    cwingrav

    Update coming shortly.... haven't looked in detail but does not seem like there are any bugs in the 1.8 version.
     
  6. Offline

    cwingrav

    Minor update for SQLite specific issues.
     
  7. Offline

    PaintballPuppy

    This is awesome! So it basically creates a MySQL for you?
     
  8. Offline

    cwingrav

    It handles the MySQL or SQLite connections. You then write SQL and send that to the connection. There are differences in MySQL and SQLite SQL unfortunately... However, I'm writing an API for that so you substitute for those non-generic SQL issues. It also makes a common way for users to configure their plugins.

    I use it in my plugins and such so it will be maintained and new features will be found/added/etc.
     
  9. Offline

    herghost

    Can you provide an example of an MySQL Select Query? I am having some problems in understanding the logic.

    Basically I want to check to see if a player is in the database when they login@

    Code:
    public void onPlayerJoin(PlayerJoinEvent event)
        {
        Player p = event.getPlayer();
       //this is where I am stuck! query = select * from table where name = p.getName();
       //if result = null the insert player name
    
    
    Thanks for any hints!
     
  10. Offline

    cwingrav

    Well, this java should create the string. SQL is just a string.
    String q = "Select * from PT_timelog where player='"+p.getName()+"';";
    ResultSet rs = this.dbconnector.sqlQuery(q);

    See here for more info: https://sites.google.com/a/cwingrav.com/moc-plugins/plugins/mocdblib
    For working with ResultSet: http://www.google.com/search?client=safari&rls=en&q=java ResultSet&ie=UTF-8&oe=UTF-8

    It really just handles the connections and using Java's SQL classes. SQL is a very complex language, simple for most things, but it gets difficult quickly. I'm trying to handle SQL differences between MySQL and SQLite, but that is a major problem with all SQL derivates. For most things, they are the same syntax. Let me know if you find differences and I will do my part to assist.

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 19, 2016
  11. Offline

    mattylight

    Excellent work! Can you provide the source for this?
     
  12. Offline

    GeekPlaya

    This is great! Please do not abandon this project as I will make a YouTube tutorial video involving it.

    One question: dbplugin cannot be resolved or is not a field
     
  13. Offline

    cwingrav

    Will do shortly. I wanna add some upgraded code first.

    Sounds good. What are you doing to get the "dbplugin cannot be resolved or is not a field"?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 19, 2016
  14. Offline

    GeekPlaya

    When I import your JAR file and add that line that you tell me to in the coding, it gives me that.
     
  15. Offline

    DaRe

    How does wipeTable work? I tried to use it but it has no effect at all =/.
    My version of usage:

    Code:
    boolean result =  ((DBConnector) this.dbconnector).wipeTable("userlist");
     

Share This Page