[INACTIVE][DEV] RDBMScore - v0.0.11 - MySQL Database Support Plugin [Feb21 Recommended Build]

Discussion in 'Inactive/Unsupported Plugins' started by Joshua Burt, Jan 28, 2011.

  1. Offline

    Joshua Burt

    Features
    • MySQL database connectivity support
    • UPDATE, INSERT, DELETE and SELECT statements supported
    • Stored Procedures and Functions Supported
    • Auto-reconnect after connection time-out or close
    Requirements

    Installation Instructions

    • Ensure the Connector/J jar (named this: mysql-connector-java-bin.jar) is within the root of your craftbukkit installation. The driver can be shared amongst other MySQL based plugins and doesn't need to be updated if one is already present.
    • Remove older versions of the plugin if present within your plugins directory
    • Copy the rdbmscore.jar binary into the plugins directory
    • Start your craftbukkit installation and monitor server startup for failures.
    Usages Instructions

    • Currently the rdbmscore has no mechanism that allows for direct interaction [from a player/user] perspective. Other plugins act as clients to this plugin and interact directly though the assertion and response of bukkit events.
    Artifacts


    Plugin - Required​
    Client Plugin Example - Optional​
    Source - Optional​
    Change Log

    Version 0.0.11
    • Build with Bukkit (Feb 21 Recommended Build)
    • Tested Against CraftBukkit (Build 423: Feb21 Recommended Build)
    Version 0.0.10-400​
    • Build against bukkit (400)
    • Tested against craftbukkit (53/428)
    • Updated to support newest [craft]bukkit release.
    Version 0.0.10-177​
    • Built against bukkit-0.0.1-SNAPSHOT.jar (Build 177)
    • Testing against craftbukkit-0.0.1-SNAPSHOT.jar (Build 361)
    • Stored Procedures/Functions Supported
      • Ensure you grant EXECUTE permissions on the user account if using this.
    • Other minor changes
    Version 0.0.9-132​
    Built against bukkit-0.0.1-SNAPSHOT.jar (Build 132)​
    Testing against craftbukkit-0.0.1-SNAPSHOT.jar (Build 298)​

    Connection marshaling improvements​
    Auto reconnect when database connections have gone stale, or closed - client plugins no longer need to provide this logic.​

    Packaging improvements​
    Dependencies:​
    The JDBC driver can now be simply referenced from the root of the craftbukkit directory. This brings dependencies in line with other deployment layouts. If you currently use MySQL for any other plugin; such as BigBrother, the dependent library can be shared. No need to provide additional drivers!​

    Version 0.0.5 - Code refactoring, built/tested against [bukkit-105][craftbukkit-231]​
    Version 0.0.4 - Updated to newest build of [bukkit #87] / [craftbukkit #173]​
    Version 0.0.3 - Added client registry to allow for concurrent database connections from multiple plugins​
    Version 0.0.2 - UPDATE, INSERT, DELETE and SELECT statements now supported​
    Version 0.0.1 - Initial code drop, basic query support​
     
  2. Offline

    spelmyst

    Can you provide an example of how to use this?
    I really don't want to work on a plug-in without MySQL support.
     
  3. Offline

    Joshua Burt

    It's meant to provide database functionality using bukkit's native event model. Your application would assert events when it needed some type of database action and then listen for the response as an event.


    Say you want to create a plugin that allows you to pull your configuration from a database on startup, or to record or store your current location in the map in a database for later use. You'd do something like this ..

    1. Assert a RDBMScoreDBConnect Event (probably within your plugin's start up)
    Your plugin's onEnable() would probably include something like this:
    Code:
            // assert our DB Connection Event..
            this.assertDBConnectEvent("minecraftServer", "minecraftServer",
                    "mysql", "localhost", "3306", "minecraftServer",
                    "com.mysql.jdbc.Driver");
    
    
    .. somewhere else in your plugin class ..
    Code:
        public void assertDBConnectEvent(String userName, String password,
                String dbms, String serverName, String portNumber, String database,
                String driver) {
            RDBMScoreDBConnectEvent rcdce = new RDBMScoreDBConnectEvent(
                    pdfFile.getName(), userName, password, dbms, serverName,
                    portNumber, database, driver);
            MyPluginClass.server.getPluginManager().callEvent(rcdce);
        }
    
    Or you could be fancy and pull it from a properties file or the like. In either case the database plugin will catch this event from you and establish the connection to the MySQL database on your behalf.

    2. Assert a RDBMScoreDBQuery Event (retrieve or store the relavant data though SQL) somewhere. You pass in the SQL for the query.
    Code:
        public void assertQueryEvent(String query) {
            RDBMScoreQueryEvent rcqe = new RDBMScoreQueryEvent(pdfFile.getName(),
                    query);
            RDBMSlogger.server.getPluginManager().callEvent(rcqe);
        }
    
    The above code is passing it's plugin name into the event to mark itself as the owner of the request. But you could in theory put in any plugin name if you wanted to issue SQL and have the results sent somewhere else ..


    3. Listen for the responding RDBMScoreQueryResultEvent in your listener class to handle the results

    You'd regsiter an event listener for custom events in your main plugin class:
    Code:
            pm.registerEvent(Event.Type.CUSTOM_EVENT, this.TemplateEventListener,
                    Event.Priority.Normal, this);
    
    Your listener class would be declared something like this:
    Code:
    package com.company.bukkit.plugin;
    
    import java.sql.SQLException;
    
    import javax.sql.rowset.CachedRowSet;
    
    import net.thelandofnod.bukkit.plugin.rdbmscore.RDBMScoreQueryResultEvent;
    import com.company.bukkit.plugin.Template.applicationState;
    
    import org.bukkit.event.CustomEventListener;
    import org.bukkit.event.Event;
    
    //  implements Listener
    public class TemplateEventListener extends CustomEventListener {
        CachedRowSet crs = null;
        private final Template plugin;
    
        public TemplateEventListener(Template instance) {
            plugin = instance;
        }
    
        @Override
        public void onCustomEvent(Event customEvent) {
            if (customEvent instanceof RDBMScoreQueryResultEvent) {
                if (((RDBMScoreQueryResultEvent) customEvent).getOwnerPlugin()
                        .equals(plugin.pdfFile.getName())) {
                    onRDBMScoreQueryResultEvent((RDBMScoreQueryResultEvent) customEvent);
                    ((RDBMScoreQueryResultEvent) customEvent).setCancelled(true);
                } else {
                    // this is the correct event, but we are not the owner so ignore
                    System.out
                            .println("Template caught RDBMScoreQueryResultEvent, but ignored it, it's not the owner.");
                }
            } else if (customEvent instanceof TemplateInitEvent) {
                onTemplateInitEvent((TemplateInitEvent) customEvent);
                ((TemplateInitEvent) customEvent).setCancelled(true);
    
            }
        }
    
        private void onTemplateInitEvent(TemplateInitEvent customEvent) {
            // lets get our set of events to monitor for ..
            String eventSelectionQuery = "select eventName from event_type";
            plugin.assertQueryEvent(eventSelectionQuery);
    
        }
    
        private void onRDBMScoreQueryResultEvent(RDBMScoreQueryResultEvent event) {
    
            System.out.println("onRDBMScoreQueryResultEvent caught by Template");
    
            if (plugin.getCurrentState() == applicationState.INIT) {
                // then we are attempting to read out run-time event listeners
    
                if (event.isExceptionCaught()) {
                    // then we caught an exception
                    System.out.println(event.getExceptionLog());
                } else {
                    if (event.getEffectedRowCount() > -1) {
                        // then this was either an update, insert, or delete
                        System.out.println(event.getEffectedRowCount()
                                + " rows effected.");
                    } else {
                        // else this was a select statement
                        crs = (event).getCrs();
                        try {
    
                            while (crs.next()) {
                                // process your configuration data
    
                            }
                            plugin.setCurrentState(applicationState.NORMAL);
    
                        } catch (SQLException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    
    }
    
    
    There are four events used by this plugin and yours. You need to bring the rdbmscore_plugin.jar into your project and then reference it as a library to gain access to the needed classes.

    RDBMScoreDBConnectEvent
    Assert this event to establish your plugin's database connection

    RDBMScoreDBDisconnectEvent
    Assert this event to disconnect your plugin's database connection

    RDBMScoreQueryEvent
    Assert this event when you want to issue some type of command to your database

    RDBMScoreQueryResultEvent
    Listen for these events and process the database payload if any. These events have an ownerPlugin value which stores the intended recipient plugin for the event. You can check it to determine if it's yours.
    --- merged: Feb 5, 2011 11:38 PM ---

    See this project for a real-world example of another plugin which uses this one:
    http://forums.bukkit.org/threads/ch...-receive-mail-with-off-line-players-231.3498/
     
  4. Offline

    yottabyte

    Man, this is annoying. I can't get the mysql connector to work. [​IMG] How come you can't just have it in the root Bukkit folder like say, BigBrother. Bleh
     
  5. Offline

    Joshua Burt

    What's New Notes - Version 0.0.9-132

    Built against bukkit-0.0.1-SNAPSHOT.jar (Build 132)
    Testing against craftbukkit-0.0.1-SNAPSHOT.jar (Build 298)

    Connection marshaling improvements
    Auto reconnect when database connections have gone stale, or closed - client plugins no longer need to provide this logic.

    Packaging improvements
    Dependencies:
    The JDBC driver can now be simply referenced from the root of the craftbukkit directory. This brings dependencies in line with other deployment layouts. If you currently use MySQL for any other plugin; such as BigBrother, the dependent library can be shared. No need to provide additional drivers!
     
  6. Offline

    yottabyte

    Thank you very much, will be very useful :)
     
  7. Offline

    ssechaud

    I have verified the username and password are correct and have permission in phpmyadmin.

    java.sql.SQLException: Access denied for user 'minecraft'@'localhost' (using password: YES)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
    at java.sql.DriverManager.getConnection(DriverManager.java:620)
    at java.sql.DriverManager.getConnection(DriverManager.java:169)
    at net.thelandofnod.bukkit.plugin.rdbmscore.RDBMScore.getConnection(Unknown Source)
    at net.thelandofnod.bukkit.plugin.rdbmscore.RDBMScore.assertDBConnectEvent(Unknown Source)
    at net.thelandofnod.bukkit.plugin.rdbmscore.RDBMScoreEventListener.onRDBMScoreDBConnectEvent(Unknown Source)
    at net.thelandofnod.bukkit.plugin.rdbmscore.RDBMScoreEventListener.onCustomEvent(Unknown Source)
    at org.bukkit.plugin.java.JavaPluginLoader$52.execute(JavaPluginLoader.java:392)
    at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:60)
    at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:214)
    at net.thelandofnod.bukkit.plugin.postoffice.PostOffice.assertDBConnectEvent(Unknown Source)
    at net.thelandofnod.bukkit.plugin.postoffice.PostOffice.onEnable(Unknown Source)
    at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:135)
    at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:410)
    at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:175)
    at org.bukkit.craftbukkit.CraftServer.loadPlugin(CraftServer.java:74)
    at org.bukkit.craftbukkit.CraftServer.loadPlugins(CraftServer.java:55)
    at net.minecraft.server.MinecraftServer.e(MinecraftServer.java:171)
    at net.minecraft.server.MinecraftServer.c(MinecraftServer.java:158)
    at net.minecraft.server.MinecraftServer.d(MinecraftServer.java:110)
    at net.minecraft.server.MinecraftServer.run(MinecraftServer.java:209)
    at net.minecraft.server.ThreadServerApplication.run(SourceFile:512)
     
  8. Offline

    Joshua Burt

    Where your postoffice.properties file looks like this (or other connection configuration mechanism):

    Code:
    db.username = CraftBukkitServiceAccount
    db.password = PASSWORD
    db.rdbms = mysql
    db.servername = localhost
    db.portnumber = 3306
    db.database = minecraftServerDB
    db.driver = com.mysql.jdbc.Driver
    
    What happens when you try to connect to mysql manually from the command line / shell?
    Code:
    mysql --user=CraftBukkitServiceAccount --password=PASSWORD --database=minecraftServerDB
    
    If it throws an error about permissions then I'd check out the schema privileges.
    Login with the MySQL Administration utility or whatever your interface of choice is and confirm them. :)

    Does the user account 'CraftBukkitServiceAccount' have atleast these permissions on your minecraftServerDB:
    SELECT
    INSERT
    UPDATE
    DELETE

    And of-course none of these (special cases aside):
    CREATE
    DROP
    GRANT
    ALTER
    CREATE_VIEW
    CREATE_ROUTINE
    ALTER_ROUTINE

    Can you manually query the database 'minecraftServerDB' by issuing sql (after connecting as above):
    select * from po_package;
    In this case you'd already have a table 'po_package' within your specified schema 'minecraftServerDB'.
    Creation script if you need one: http://dl.dropbox.com/u/19821084/021011/postoffice/SQL/po_packages.sql


    ..Or there could be other factors, such as firewall settings.
    Can/do other applications connect to your databases? Check and make sure that nothing is blocking you. In this case I don't suspect this is going on since the error indicated access denied - rather than some type of connection issue. :)

    Good Luck!
     
  9. Offline

    ssechaud

    I can login to mysql via terminal fine, username, password and privileges are all working fine. It's well strange. Table schema has all been done with the sql scripts provided. It really should be working.

    Update: I specified the IP and it works, must be something silly restricting access on localhost or something, which is weird cause localhost works for other stuff on the server...
     
  10. Offline

    Joshua Burt

    What's New Notes - Version 0.0.10-177
    • Built against bukkit-0.0.1-SNAPSHOT.jar (Build 177)
    • Testing against craftbukkit-0.0.1-SNAPSHOT.jar (Build 361)
    • Stored Procedures/Functions Supported
    Ensure you grant EXECUTE permissions on the user account if using this.​
    • Other minor changes
    --- merged: Feb 21, 2011 10:38 PM ---
    rdbmscore-0.0.10-400
    Build against bukkit (400)
    Tested against craftbukkit (53/428)

    Plugin updated to support newest [craft]bukkit release.
     
  11. Offline

    Joshua Burt

  12. Offline

    dslip

    Author,

    Please support the auto-updating plugin CraftBukkitUpToDate. To do so please provide a permanent direct link to the author of CraftBukkitUpToDate.

    The below is information they provide
    Show Spoiler

    My Plugin didn't supported, how i get it to work with CButD?
    Give me a permanent link to your Plugin, i will add it. That's all no changes at your SourceCode or something else, all I need is a permanent link to the newest version of your Plugin. A good place for this is http://www.dropbox.com/ or for OpenSource https://github.com/.


    I really enjoy using your plugin and would like to see it work well with the above listed 'updater' plugin.
     
  13. Offline

    Plague

    considered unsupported
     

Share This Page