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).

Features:

• for plugin devs, allow sys admins to toggle between MySQL and SQLite to suit their own configuration, without you chaining your 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

Used By:

• MOC Playtime Tracker
• CodeBlocks - pending publication
• Fizziks - soon to be released
• ReginBlock - soon to be released

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 'playtime tracker')
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.

Permissions:

N/A - This plugin has no permissions since it is all about plugins accessing data.

Configuration:

A single config.yml file is created. It's sole option is "debug: false". If set to true, it spews debug messages.

API Calls and Usage:

Initialization:

In your onEnable function, get this plugin:

PluginManager pm = this.getServer().getPluginManager();
this.dbplugin = (MOCDBLib) pm.getPlugin("MOCDBLib");

Create a database connection with this plugin. This will read in/create the configuration file (see below):

this.dbconnector = this.dbplugin.getMineCraftDB(<your plugin name>, <a Logger object>);

NOTE: The plugin name is where this will place the config file... this might be problematic.

Ensure that a table we will use exists in the database. Here, the table's name is "table1" and it is initialized (if it does not exist) as having an 'id' and 'txt' attributes.
NOTE: This is just a helper function. You can call SQL directly to do the same thing.

this.dbconnector.ensureTable("table1","id integer, txt varchar(50)");

Perform an SQL query:

This is a simple SQL query that returns a ResultSet object.

ResultSet rs = this.dbconnector.sqlSafeQuery("SELECT * FROM table1 WHERE id=2");

This is an SQL query that does the exact same query as above but uses Java's PreparedStatement to avoid SQL injection attacks.
NOTE: PreparedStatements, in their "set" methods, have a starting index of 1, not 0.
NOTE: The above query is safe anyway, since it is a static string and not relying on user input or a passed parameter which could inject an SQL attack.

public void performACall(int _id) {
    java.sql.PreparedStatement ps = this.dbconnector.prepareStatement("SELECT * FROM table1 WHERE id=?");
    ps.setInt(1,_id);
    ResultSet rs = this.dbconnector.sqlQuery(ps);
    ...
} 

Verify a Table and its Columns Exist

This is useful for when you change your database schemas between versions of a plugin and want to try to not blow up existing data of users. This is a good thing to do. :)

String[][] mycols= { {"id","integer"}, { "player",  "varchar(50) not null"}, {"world", "varchar(50) not null"} };
...
this.dbconnector.updateSafeQuery( dbconnector.createTableIfNotExistsString("MyTable", mycols, "id"));
MySQLLib mysqldb = (MySQLLib) dbconnector;
if ( mysqldb.checkForColumnInTable(mycols[0][0],"MyTable") == false )
  mysqldb.createColumnInTable(mycols[0][0],mycols[0][1],"MyTable");

Full API:

Here are the function calls you should be using.

 // Manage connections
 Connection getConnection();
 Boolean checkConnection();
 Boolean closeConnection();
 
 // Perform assumed to be safe queries
 ResultSet sqlSafeQuery(String query);
 int       insertSafeQuery(String query);
 int       updateSafeQuery(String query);
 int       deleteSafeQuery(String query);

  // If you want the keys of the affected tables returned
  ResultSet insertSafeQueryAndKeys(String query);
  ResultSet updateSafeQueryAndKeys(String query);
  ResultSet deleteSafeQueryAndKeys(String query); 


 // Call the safer prepared statements
 PreparedStatement prepareStatement(String _query);
 ResultSet sqlQuery(PreparedStatement query);
 int       insertQuery(PreparedStatement query);
 int       updateQuery(PreparedStatement query);
 int       deleteQuery(PreparedStatement query);
 PreparedStatement prepareStatementAndKeys(String _query);
    //   ex.  PreparedStatement ps = dbconnector.prepareStatementAndKeys( ... );
    //             ...
    //          ResultSet rss = ps.getGeneratedKeys(); 

 // Manage Tables
 Boolean checkTable(String table);
 Boolean wipeTable(String table);
 Boolean ensureTable(String _tblname, String _tblcolumns);

 // Utility
 String createTableString(String _tablename, String[][] _cols, String _keys );
 String createTableIfNotExistsString(String _tablename, String[][] _cols, String _keys );

 // MySQL Only
 Boolean checkForTable(String _table);
 Boolean checkForColumnInTable(String _column, String _table);
 Boolean createColumnInTable(String _colname, String _coldesc, String _table)

NOTE: Regarding insert/update/delete queries, and why they are separate. I was following the SQLLight plugin's convention, and honestly, I like splitting it up like this. However, the underlying SQL call for all is the same. So, you could send insert SQL to a delete SQL w/o incident.

Some Pointers

This is a simple method to avoid typos by letting your compiler (or development environment) catch SQL errors. Store your database columns as an array and enums. NOTE: Please let me know if my hand typed code below works.

	static enum TCOLS { col1, col2 };  // using enums helps keep all the string usage consistent as there is no compile time checks otherwise
	static String TABLENAME = "MyTable";
	static String TABLEKEYS = TCOLS.col1+","+TCOLS.col2;
	static String[][] TABLECOLS= {
            { TCOLS.col1.toString, "integer DEFAULT 0" },
	    { TCOLS.col2.toString, "text DEFAULT ''" } };
	  ...
	String tableinitstring = DBConnector.createTableIfNotExistsString(TABLENAME,TABLECOLS,TABLEKEYS);
	DBConnector dbc = MOCDBLib.getConnector().sqlSafeQuery(tableinitstring);
        ...
        // ensure the table columns exist, if the table already exists (i.e. updating to a new plugin version with different columns)
        TCOLS[] tc = TCOLS.values();
	for(int i=0;i<tc.length;i++) {
	    if ( mysqldb.checkForColumnInTable(tc[i].toString(),TABLENAME) == false )
	    {
	        // does not exists, so creating.
		mysqldb.createColumnInTable(TABLECOLS[i][0],TABLECOLS[i][1],TABLENAME);
	    }
	}

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:

• this is a plugin so many plugins can use it
• updating is easier (it does not require cracking open plugins and inserting SQLLite into that jar file)
• safer queries as it helps you avoid SQL Injection attacks
• MySQL and SQLite APIs are merged so as a programmer, you don't notice the difference between which db solution you are using
• totally different code organization
• few extra API calls to help you out
• configuration is simpler