Monday, October 06, 2008

JDBC driver for Coherence

Okay this might be a crazy idea but as Coherence is a data source it was worth writing a JDBC driver for it. Of course spending time to write a complete SQL engine would be too much without any real motivation. But a simple proof of concept was on the cards. So here you go:

First thing is the client:


public static void main (String [] args) {
// -- Create a URL: hostname and port being of the proxy
String url = "jdbc:coh:@localhost:9099";
String sql = "SELECT * FROM Positions";

try {
// -- Load the Coherence Database Driver
Class.forName("com.ezduck.driver.GridDriver");
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
stmt.executeQuery(sql);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}


Now the Driver:

public class GridDriver implements Driver {

static {
try {
DriverManager.registerDriver(new GridDriver ());
} catch (SQLException e) {
// TODO
}
}
// -- "jdbc:coh:@localhost:9099"
public GridDriver() {
}

public Connection connect(String url, Properties info) {
int hostIndex = url.indexOf("@") + 1;
int portIndex = url.lastIndexOf(":");
if (hostIndex < 0 || portIndex < 0) {
throw new IllegalStateException ("Driver URL incorrect");
}
String hostName = url.substring(hostIndex, portIndex);
String port = url.substring(portIndex + 1);
return new GridConnection (hostName, port);
}
...
}


Hmm Now the Connection:

public class GridConnection implements Connection {

public GridConnection(String hostName, String port) {
ConfigurableCacheFactory factory = CacheFactory.getConfigurableCacheFactory();
XmlElement cc = new SimpleElement("cache-config");

XmlElement sMap = cc.addElement("caching-scheme-mapping");
XmlElement cMap = sMap.addElement ("cache-mapping");
cMap.addElement("cache-name").setString("*");
cMap.addElement("scheme-name").setString("extend-dist");

XmlElement rcs =
cc.addElement("caching-schemes").addElement("remote-cache-scheme");
rcs.addElement("scheme-name").setString ("extend-dist");
rcs.addElement("service-name").setString("ExtendTcpCacheService");
XmlElement xE =
rcs.addElement("initiator-config").addElement("tcp-initiator")
.addElement("remote-addresses");
XmlElement rAs = xE.addElement("socket-address");
XmlElement add = rAs.addElement("address");
add.setString(hostName);
XmlElement portId = rAs.addElement("port");
portId.setString(port);

// -- Now set the new XmlElement
factory.setConfig(cc);
// -- And of course you need to restart the Cache Service thread
factory.ensureService("ExtendTcpCacheService").stop();
factory.ensureService("ExtendTcpCacheService").start();
}

public Statement createStatement() {
return new GridStatement();
}

.......
}


Then the Statement:

public class GridStatement implements Statement {

public GridStatement() {
}

public ResultSet executeQuery(String sql) {
String command = sql.substring(0, sql.indexOf (" "));
try {
// -- There must be a way to recognize the SQL Command
ICommand cObject = (ICommand) Beans.instantiate(
this.getClass().getClassLoader(),
this.getClass().getPackage().getName() + "." +
command.toUpperCase().trim());
Set set = (Set) cObject.execute (cObject, sql);
// -- A utility to parse the SQL
Map map = SQLUtil.parseSelect(sql);
String cacheName = (String) map.get ("tables");
// -- And last the ResultSet
ResultSet rS = new GridResultSet (set, cacheName);

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
.......
}


And before the ResultSet, lets see a half cooked SQL SELECT:

public class SELECT implements ICommand {
String token[];

// -- Select * From Dist-A where name = 'A';

public SELECT() {
}

public Object execute(Object obj, String command) {
Map map = SQLUtil.parseSelect(command);
NamedCache nCache = CacheFactory.getCache((String)map.get("tables"));
String clauses = (String)map.get("clauses");
// -- Lets demonstrate the Select * before going crazy
if (clauses == null) {
return nCache.entrySet(AlwaysFilter.INSTANCE);
}
return null;
}
}


Last but not least - The ResultSet:

public class GridResultSet implements ResultSet {

private Iterator iter;
private Set set;
private String cacheName;
private InvocableMap.Entry entry;

public GridResultSet(Set set, String cacheName) {
this.set = set;
this.cacheName = cacheName;
iter = set.iterator();
}

public boolean next() {
entry = (InvocableMap.Entry) iter.next();
return iter.hasNext();
}
......
}


So here you go - another weekend project for Coherence!

6 comments:

Robert Varga said...

Hi Ashish,

a small problem with the next() method.

ResultSet.next() should return true if it was able to step to the next row of the result set, not that there is one AFTER stepping to the next row.

So ResultSet.next() should be:

public boolean next() {
if (iter.hasNext() ) {
entry = (InvocableMap.Entry) iter.next();
return true;
}
return false;
}

Btw, it seems that the command patterns stuff is in the wild, therefore I have to pester Brian to send the code to me, too...

Best regards,

Robert

ashish said...

Hi Robert,
I am going to create a new Incubator project and checkin the code to svn. Feel free to contribute. And thanks for fixing the next () method. I do have one implementation kinda ready for SELECT command but of course there will be more.

-Ashish

Robert Varga said...

Hi Ashish,

I don't yet know how to access the Incubator project for the JDBC driver but I guess there could be some integration possibilities between the POF serializer generator and the JDBC driver allowing the JDBC driver to leverage metadata generated by the generator to phrase select statements selecting attributes from the classes annotated with @PofSerialized.

Best regards,

Robert

Robert Varga said...

Hi Ashish,

I don't yet know how to access the Incubator project for the JDBC driver but I guess there could be some integration possibilities between the POF serializer generator and the JDBC driver allowing the JDBC driver to leverage metadata generated by the generator to phrase select statements selecting attributes from the classes annotated with @PofSerialized.

Best regards,

Robert

ashish said...

Do you have login on coherence.oracle.com? If you do you would see the project. The source is also available from svn. Also look at Denis Felcey's implementation. A little different but it uses a lexr for the metadata.

Robert Varga said...

Hi Ashish,

I do have an account to coherence.oracle.com migrated from a while ago (username robvarga) but still I don't see the JDBC driver project, nor do I see any references to it on the pages, and finally I don't know the SVN url either.

Probably it has to do something with that I am not an Oracle emloyee.

Best regards,

Robert