Download Links:
v2.0.2 for CC 1.481 - 1.5 (MC 1.4.6 & MC 1.4.7):
- Dropbox: ccDB_2.0.2_mc1.4.7.zip
Changelog:
Spoiler
2.0.2- Removed ComputerCraft version check in "mcmod.info" (they have conflicting version schemas)
- Removed "beta" since nobody has reported any errors so far
- Fixed: Trying to connect the peripheral to a computer with a cable (from Xfel's Peripheral Cables) was causing a crash.
- Ported to MC 1.4.6 & MC 1.4.7
- Namechange from ccMySQL to ccDB
- Added ability to write to databases (not just read-only like in 1.0) using the update functions.
- Connection to any DBMS that comes with a JDBC 4 driver.
- Multiple database connections.
- Lua-side API 'ccDB' for easy creation and access of connections.
- Overhaul of internal procedures.
What can I do with it?
[indent=1]Connect to JDBC-capable databases and then execute queries, read and write data, etc. like e.g.:
executeQuery( "SELECT * FROM accounts.users WHERE password = 'secret'" )
getString( "surname" ) -> returns the element at column-label "surname" of the current row as a String
getBoolean( 3 ) -> returns the element at column-index 3 of the current row as a Boolean
updateString( "nickname", "lamarr" )
Or look at the example in the screenshot above! :)/>[/indent]
Recipe: (subject to change, I'm open for suggestions)
[indent=1] OR
Or in ComputerCraft's creative tab.[/indent]
Requirements:
Spoiler
You need these things:- This peripheral (download at the top)
- The JDBC v4 drivers for the DBMSes you want to connect to. Examples:
- MySQL - http://www.mysql.com/downloads/connector/j/
- PostgreSQL - http://jdbc.postgresql.org/download.html
- A database to connect to
- Either an account to a database, or know how to setup your own database.
- Know SQL or how to educate yourself about it.
- Read the installation instructions, if you want to know how to install it.
- Read "Available functions" and "General Usage", if you want to know how to connect to a database and issue commands.
Installation:
- Download the necessary JDBC 4 drivers. Each driver should be a .JAR file. Store them to a location of your choice.
- Next, if you use a batch file to launch your MC via Minecraft.jar or a launcher where you can add startup-arguments for MC: Put the .JAR file into a directory of your choosing (but not the mods folder). Then add this to your MC startup arguments:
- As an easier alternative, simply put the .JAR driver file into your Minecraft's mods folder. If you are on Windows, then it should be %appdata%\.minecraft\mods\ I recommend doing it the former way though, as that loads the drivers together with Minecraft instead of having Forge inject them during the mod loading process.
- Download this peripheral, it's a .ZIP file. Don't extract it, simply put it into the mods folder as well.
- Done :)/>
-Djdbc.drivers=com.mysql.jdbc.Driver -Xbootclasspath/a:C:\\mysql-connector-java-5.1.22-bin.jar
Obviously, change com.mysql.jdbc.Driver to the package name of the JDBC driver you're using (see installation docs of the respective driver).
Also change C:\\mysql-connector-java-5.1.22-bin.jar to the path and name you've stored your JDBC .JAR file.
To load multiple drivers, separate them with a colon, like this: -Djdbc.drivers=com.mysql.jdbc.Driver:org.postgresql.Driver
Also add the location of each JDBC driver as a separate -Xbootclasspath/a:LOCATION, where LOCATION is the absolute path to the respective driver.
Lastly, keep in mind that the java command line parameters are case-sensitive!
Available functions:
Spoiler
It essentially works like the Java-methods of the java.sql package, with the exception of as few methods which I had to customize in order to make them work with ComputerCraft's Lua.Lua API Methods:
ccDB.getNewConnection( url, user, password ) - Establishes a new database connection. Returns a table through which all functions of the connection can be accessed.
ccDB.disconnectAll( ) - Disconnects all database connections.
ccDB.getVersion( ) - Returns the peripheral version as a string.
ccDB.getDebug( ) - Returns true if debug mode is enabled; false otherwise. (default = false)
ccDB.setDebug( true/false ) - Enables debug mode, which prints stack traces (very detailed error messages) to the Forge logfile.
The rest of these functions are available for each created connection-object. Since a connection-object is a lua table, call these functions via a colon, like e.g. myDB:disconnect(), where "myDB" is the connection-object and "disconnect()" one of the following methods.
Database-Object Methods:
These methods are available after a query has been executed.
Since they are essentially direct mappings of their Java counterparts, I refer you to the official javadocs, which contain a list of the available methods:
Java API - ResultSet
Java API - ResultSetMetaData
From these lists, the following methods are not (yet) supported:
Spoiler
- clearWarnings
- close
- getArray
- getAsciiStream
- getBigDecimal
- getBinaryStream
- getBlob
- getByte
- getBytes
- getCharacterStream
- getClob
- getMetaData - Redundant, since I already included its functions directly.
- getNCharacterStream
- getNClob
- getObject
- getRef
- getRowId
- getSQLXML
- getStatement
- getTimestamp
- getURL
- getUnicodeStream
- getWarnings
- isClosed
- rowUpdated
- updateArray
- updateAsciiStream
- updateBigDecimal
- updateBinaryStream
- updateBlob
- updateByte
- updateBytes
- updateCharacterStream
- updateClob
- updateNCharacterStream
- updateNClob
- updateObject
- updateRef
- updateRowId
- updateSQLXML
- updateTimestamp
- getColumnClassName
Customized methods:
updateLong( columnIndex, longValue ) - Works like its Java equivalent, except that it expects 'longValue' to be a string containing the long value.
updateLong( columnLabel, longValue ) - Works like its Java equivalent, except that it expects 'longValue' to be a string containing the long value.
getDate( columnIndex ) - Works like its Java equivalent, except that it returns a string with the date in yyyy-mm-dd format.
getDate( columnLabel ) - Works like its Java equivalent, except that it returns a string with the date in yyyy-mm-dd format.
getDateMS( columnIndex ) - Works like its Java equivalent, except that it returns the date in milliseconds since January 1st 1970.
getDateMS( columnLabel ) - Works like its Java equivalent, except that it returns the date in milliseconds since January 1st 1970.
updateDate( columnIndex, date ) - Works like its Java equivalent, except that it expects 'date' to be a string with milliseconds since January 1st 1970.
updateDate( columnLabel, date ) - Works like its Java equivalent, except that it expects 'date' to be a string with milliseconds since January 1st 1970.
getTime( columnIndex ) - Works like its Java equivalent, except that it returns a string with the time in hh:mm:ss format.
getTime( columnLabel ) - Works like its Java equivalent, except that it returns a string with the date in hh:mm:ss format.
getTimeMS( columnIndex ) - Works like its Java equivalent, except that it returns the time in milliseconds.
getTimeMS( columnLabel ) - Works like its Java equivalent, except that it returns the time in milliseconds.
updateTime( columnIndex, time ) - Works like its Java equivalent, except that it expects 'time' to be a string with milliseconds.
updateTime( columnLabel, time ) - Works like its Java equivalent, except that it expects 'time' to be a string with milliseconds.
Additional methods:
getRowCount( ) - Returns the amount of rows contained within a ResultSet.
getRowElements( ) - Returns all elements in the current row as a string-table. (N.B.: Determine the amount of elements with getColumnCount.)
executeQuery( sql ) - Executes the SQL statement (string), which creates a ResultSet per connection, i.e. overwrites the connection's old ResultSet everytime a new query is executed.
disconnect( ) - Disconnects the database connection on which it was called.
General usage:
Spoiler
For a quick example, take a look at the screenshot above. It shows the table of a database and in the background some in-game commands on that table.Generally, you'd go about it like this (requires a database & a valid user account!):
- Place down the Connector Block next to the computer you want to use it with.
- Turn on or restart the computer.
- Make sure the ccDB API has been loaded (The variable 'ccDB' should exist as a table).
- Create a database connection:
- Ok, now we're all setup and can finally execute a query on the connection we've just established:
- Now you can call any of the listed ResultSet and MetaData functions (See 'Available Functions') to access content from the ResultSet (i.e. the results of the query), like e.g.:
- If you want to fire off another query, goto step 5. This will overwrite your current ResultSet with a new one.
- If you're all done and want to close this connection, type:
db = ccDB.connect( "jdbc:mysql://localhost:3306", "admin", "secretpassword" )
For MySQL JDBC the addresses to the server always begin with jdbc:mysql:// followed by the server's HOSTNAME or IP address, in this example localhost.
This is followed by a colon, after which you enter the port of the server. Since 3306 is the default you could just as well leave it out though (as shown in the next example).
The next two strings are the username and password with which you want to connect to the server.
Note that you could already choose a specific database to connect to on the MySQL-Server simply by putting its name after the URL like this:
db = ccDB.connect( "jdbc:mysql://localhost/accounts", "admin", "secretpassword" )
This way you wouldn't need to enter, for example, accounts.articles for every SQL statement but simply articles.
For PostgreSQL the connection string starts like this:
jdbc:postgresql://
In any case though: Refer to the documentation of the specific JDBC driver you're using in order to find out the required connection string!
db:executeQuery( "SELECT * FROM accounts.articles" )
…where accounts would be the database and articles the table you want to access on the MySQL-Server.
If the query was successful (indicated both by text-output and events), then a ResultSet will have been created which contains all of the results in table form.
Also notice that I used a colon to call the function. All functions of a connection-object are accessed with a colon, NOT with a dot!
Only the API functions of ccDB are called with a dot.
db:getString("lastname")
This would return the content of column "lastname" of the current row as a string.
db:disconnect()
You can also close all connections by either typing…
ccDB.disconnectAll()
… or rebooting / shutting down the computer, or by destroying the Connector-Block.Bugs:
Spoiler
I've been only able to test it in a very small-scale way, so there might be some bugs or oversights I haven't yet encountered and therefore wasn't able to account for.If think you found a bug and want to report it, then please make sure to include instructions for how to reproduce it!
What helps a lot is if you turn on the debug mode, so that a stack trace of all errors will be printed to the Forge log file.
You can activate the debug mode in-game once connected to the peripheral via setDebug( true )
The Forge log file with the latest log-entries is always ForgeModLoader-*-0.log which you'll find in your .minecraft folder.
Plans for the future:
Spoiler
I'm planning on releasing the source-code, but not until the code is…- cleaned-up
- 'settled', i.e. relatively bug-free and no major changes in sight
- properly documented
- A video demonstrating some simple examples (heavily depends on my free time).
- An example program, showing a possible use-case. ( A basic entrance access control with an in-game Server-Client architecture )
- "Maybe": Ability to hold multiple ResultSets, not just one.
- "Maybe": Ability to set up prepared statements. (Not sure if this is really needed though.)
- A Bukkit-Version - Not planned (yet). Maybe at the end of may (2013) when I'm through with my exams.
"[acronym='Trololo']License[/acronym]":
Spoiler
WTFPLDO WHAT THE FUCK YOU WANT TO PUBLIC LICENSE
Version 2, December 2004
Copyright (C) 2004 Sam Hocevar <sam@hocevar.net>
Everyone is permitted to copy and distribute verbatim or modified
copies of this license document, and changing it is allowed as long
as the name is changed.
DO WHAT THE FUCK YOU WANT TO PUBLIC LICENSE
TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION
0. You just DO WHAT THE FUCK YOU WANT TO.
That being said, it's always nice to be asked for permission or at least given a notice, solely because I'm curious to see what other people create.
Also it always feels much nicer not to be plagiarized for all the hard work one has put into this, as I guess anyone who ever created something can relate to.
Basically: Do WTF you want, just don't be a dick, k? ^_^/>
Have fun! :)/>
Cheers,
Espen
Old versions (not supported):
Spoiler
v2.0.1 beta for CC 1.481+ : Dropboxv2.0 beta for CC 1.46+ : Dropbox
This version only supports reading from MySQL databases. No writing, no other DBMS.
v1.0 alpha for CC 1.47 : Dropbox
v1.0 alpha for CC 1.46 : Dropbox
v1.0 alpha for CC 1.42 - 1.45 : Dropbox
Compatible with [topic='738']Xfel's Peripheral Cables[/topic] v1.1.2 - 1.2.1