Slowly gaining trust... Could do with more eyes looking at it to check its asynchronous credentials, and spot weak points or cracks...
For Vert-x 1.3.1, please use v1.2 of this mod
As it's a snapshot, you might need to rm -rf ~/.m2/repository/io/vertx/ to get maven to refresh to latest jars and get them all aligned...
See the current tests for it in action though...
Default config:
{
address : "com.bloidonia.jdbcpersistor",
// JDBC connection settings
driver : "org.hsqldb.jdbcDriver",
url : "jdbc:hsqldb:mem:test",
username : "",
password : "",
// Values 'yes', 'no', or 'maybe'. Defaults to 'no'. See https://issues.apache.org/jira/browse/DBUTILS-101
// 'maybe' means that instance of the JdbcProcessor will switch to 'yes' if an SQLException is caught
pmdKnownBroken : "no",
// Pool settings
minpool : 5,
maxpool : 20,
acquire : 5,
// Defaults
batchtimeout : 5000,
transactiontimeout : 10000
}
When the mod is loaded successfully, it will send a message:
{ status: "ok" }
To the address in the config with .ready appended to the end.
This means you can do:
var persistorConfig = { address: 'test.persistor', url: 'jdbc:hsqldb:mem:' + vertx.generateUUID() + '?shutdown=true' }
var readyAddress = persistorConfig.address + '.ready'
var readyHandler = function( msg ) {
if( msg.status === 'ok' ) {
eb.unregisterHandler( readyAddress, readyHandler ) ;
// MOD IS READY TO GO!!!
}
} ;
// This will get called by the jdbc-persistor when it has installed the work-queue
eb.registerHandler( readyAddress, readyHandler ) ;
vertx.deployModule('com.bloidonia.jdbc-persistor-v1.2', persistorConfig, 1, function() {} ) ;
And when the readyHandler is called, you know your work-queue is up and running.
You can also add the following properties to the config object to test the connection (see the c3p0 documentation for these properties here)
c3p0.automaticTestTable
c3p0.idleConnectionTestPeriod
c3p0.preferredTestQuery
c3p0.testConnectionOnCheckin
c3p0.testConnectionOnCheckout
Currently attempts to support:
This is a quick doc describing how I want it work, so I don't change my mind halfway through coding it ;-)
It may not be a description of how it currently works...
The execute action is for running parameterless SQL which does not generate a ResultSet
{
action: "execute",
stmt: "CREATE TABLE test ( id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL," +
" name VARCHAR(80), age INTEGER, CONSTRAINT testid PRIMARY KEY ( id ) )"
}
{
status: "ok"
}
or
{
status: "error",
message: <message>
}
Call some SQL that generates a ResultSet.
Takes an optional list of lists (same order as the ? placeholders) as parameters to the query.
{
action: "select",
stmt: "SELECT * FROM xxx"
}
or
{
action: "select",
stmt: "SELECT * FROM xxx WHERE a=? AND b=?",
values: [ [ 10, 20 ], ... ]
}
One of:
{
status: "ok",
result: [ { "NAME":"a", "AGE":32 }, ... ]
}
{
status: "error",
message: <message>
}
If you want the resulting list of maps to be batched into a certain number of results, you can add the parameters batchsize (default -1 for no batching) and batchtimeout (in ms: default 10000) to your query message.
ie; consider a table with 5 rows:
| tim | | sarah | | alan | | cerys | | si |
If we execute:
{
action: "select",
stmt: "SELECT NAME FROM TABLENAME",
batchsize: 3,
batchtimeout: 10000
}
Then the first response will be:
{
status: "more-exist",
result: [ { "NAME":"tim" }, { "NAME":"sarah" }, { "NAME":"alan" } ]
}
Along with a handler to fetch the rest of the results. A call to this will result in the response:
{
status: "ok",
result: [ { "NAME":"cerys" }, { "NAME":"si" } ]
}
The ok specifying that we have reached the end of this batch. If you do not call this batch handler within batchtimeout milliseconds, the ResultSet, Statement and Connection will be closed (unless you are inside a transaction (see below), in which case the Connection will remain open).
Takes an optional list of lists (same order as the ? placeholders) as parameters to the query.
Returns the primary keys generated by the insert.
You may also pass the optional parameters batchsize and batchtimeout if you want these keys returned in batches as with select
{
action: "insert",
stmt: "INSERT INTO xxx( a, b ) VALUES( ?, ? )",
values: [ [ 10, 20 ], ... ]
}
One of:
{
status: "ok",
result: [ { "ID":1 }, { "ID":2 }, ... ]
updated: <nrows>
}
or
{
status: "error",
message: <message>
}
Inserting timestamps can be done by specifying them as Strings in the correct Timestamp format, ie in a table defined by:
CREATE TABLE test ( id INTEGER, time TIMESTAMP )
We can insert data using:
{
action: "insert",
stmt: "INSERT INTO test( id, time ) VALUES( ?, ? )",
values: [ [ 1, '2013-02-14 12:30:44' ], ... ]
}
{
action: "update"
stmt: "UPDATE xxx SET( a=?, b=? ) WHERE c=?",
values: [ [ 10, 20, 30 ], ... ]
}
{
status: "ok",
updated: <nrows>
}
or
{
status: "error",
message: <message>
}
This starts an SQL transaction, and returns a handler to execute any of the above messages inside.
After each response, if no reply is heard for more than timout milliseconds (default 10000), then the transaction is rolled back and the connection is closed.
Once you are done with a transaction, then handler needs to be sent a commit or rollback message (see below)
{
action: "transaction",
timeout: 10000
}
{
status:"ok"
}
or
{
status: "error",
message: <message>
}
Inform the Transaction handler to commit any changes to the connection, and close the connection.
{
action: "commit"
}
{
status:"ok"
}
or
{
status: "error",
message: <message>
}
Inform the Transaction handler to rollback any changes to the connection, and close the connection.
{
action: "rollback"
}
{
status: "ok"
}
or
{
status: "error",
message: <message>
}
Get the current status of the pool
{
action: "pool-status"
}
{
status: "ok",
connections: 10,
idle: 10,
busy: 0,
orphans: 0
}
or
{
status: "error",
message: <message>
}