Creating ActiveRecord JDBC adapters

Overview

These are our thoughts, comments and shared experience from creating two ActiveRecord JDBC Adapter projects, for the SAP MaxDB and Sybase ASE databases. It is to be noted that we are neither JRuby, nor ActiveRecord experts, but we believe that the learning curve for starting an adapter project for your database is not that high at all. Moreover, it is fun and a very fruitful exploratory task.

We try to answer this question: what could be a possible road map for you, if you have a new database that you want to create an adapter for ?

Of course, a huge part of our knowledge here is thanks to the valuable talks, discussions and advices from experts in the JRuby community.

Background

Well, let's clarify the starting point. You have a Rails application, using ActiveRecord for its persistence layer, and you want it to work with your relational database. You are running on a JRuby environment.

Note: Everything that we are talking about here is within the context of the JRuby runtime. Database adapters and solutions for native MRI are out of scope.

For a prerequisite, you should have a JDBC driver that works with your database. This is a must. AR-JDBC will eventually use the JDBC protocol: it won't communicate with the db through some native connectors/protocols.

Note: Of course, knowing the basics of Ruby, ActiveRecord and its ORM conventions, Rails, JRuby is also assumed. Otherwise this article shouldn't be of big interest, or usefulness to you.

First, you would need to know well your database platform, in terms of its SQL dialect and specifics, quoting table and column names when they are used inside SQL queries, supported data types and keywords, etc. Those vary greatly from database to database.

Second, you need to get acquainted with the ActiveRecord JDBC adapter project. This is a framework that enables ActiveRecord applications to work on top of the JDBC driver for your database. You can think of it as some kind of ActiveRecord - JDBC translator: it manages to transform the ActiveRecord API usages in your Rails code to JDBC calls, that are being sent to your database. This is the place to mention that obviously these usages don't cover hard coded SQL. Any places in your Rails app where you resort from using the ActiveRecord API and code SQL statements directly won't get modified by AR-JDBC. Instead, they will go directly to the db as-they-are (isn't this what you want, after all ?). So if you have such application parts, then you should think of how to change their SQL code (if needed) so that it works with your db. Or you should rather try to migrate from using native SQL to calling the ORM API of the ActiveRecord framework, which will definitely increase the portability of your app. You could also check the Arel gem which provides means to construct complex SQL queries in a convenient DSL form.

Note: Virtually all object-relational mapping frameworks provide support to performing native SQL calls. If you come from the Java world, recall JPA for example.

You may start with a direct attempt to use AR-JDBC with your database. It produces some generic SQL code that may, or may not work with your database. If your application scenario is not that complex, it may work fine. If it doesn't work, then you need an adapter for your db platform.

Basically, the ActiveRecord JDBC Adapter (AR-JDBC for short) is really a great framework, which is made extensible so that other modules could be plugged in. Currently numerous database platforms are supported in that way, like MySQL, MS SQL, DB2, Derby, Oracle, Postgre, etc. They are distributed as separate gems, although the code of the AR-JDBC gem hosts some parts of them as well.

You shouldn't get scared from the complexity of all these things and concepts, or if you get stuck somewhere. You can perfectly learn from all of the already existing adapters (some of them heavily used and with production quality). Exploring those adapters and searching through their code, applying some analogy learning, running your app with them and the corresponding databases can give you a lot of insights and information. Stepping with a debugger into their code is another valuable option. Of course, from time to time you may need to google around certain issues and topics, in order to decide what to do. This is perfectly okay.

Getting Started

But how do you go for creating a new adapter ? Well, what we did was to start with a new gem, which was effectively an empty adapter. We created one gem just for wrapping the JDBC driver of the database - here, and one gem for the empty adapter itself. You can take a look, for example here, to see what an empty adapter looks like. In Ruby terms, it is just a set of Ruby modules and classes that inherit from AR-JDBC but don't really do anything special: they are either empty, or just call the super (parent) implementation.

The adapter itself is usually placed in the lib/arjdbc/your_db_name/adapter.rb file. The file connection_methods.rb on the same level is used for defining connection specifics. They will be later used when config/database.yml is configured to use your adapter. In case your adapter defines its own Arel visitor, it is to be found in file like lib/arel/visitors/your_db_name.rb.

The AR-JDBC project is comprised of two parts: a set of JRuby classes, modules, code, and a set of Java classes. Most of the time overriding the behavior of the Ruby parts in your sub-adapter will do. These Ruby parts represent a metamodel for the tables, columns, etc. and have a lot of places and methods where different interactions with the database are modeled. These are the places where you can put the logic specific for your db, that is, where you can override the implementation in the generic AR-JDBC adapter, if it doesn't work for you. Those places offer quite flexible means to control the SQL code that is eventually sent to your db. Inserts, selects, updates, deletes, everything is there. So are certain operations on tables, databases, indexes. To put it in other words, the adapter handles both DDLs and DMLs. Some of the functionality and possibilities you won't ever need to use ( and thus never have to understand ;) ).

The generic AR-JDBC adapter is to be found in this file. Take a look for a while to examine its API, and to figure out what methods are defined there. Most of the time overriding some of them in your adapter will fix your problem. You can check the general Arel visitor here. Again, you may want to explore its API, and the mechanism it provides for SQL code modification. Combined with learning from what other adapters do on this level (Derby, SQL Server, etc.), this will certainly get you further into the game.

Still, if nothing else helps you in your situation, you can resort to using the Java part of the AR-JDBC framework. Basically, it consists of a detailed class which handles the communication with the db on the JDBC level. You can directly operate on the JDBC connection level, analyze the statements, modify them, convert back and forth from JRuby to Java, etc. This is the lowermost level after which the execution flow leaves the adapter, enters the JDBC driver and then travels to the db. So this is the last layer/chance where you could affect the SQL code being produced. Take a look at this class here, and its public methods API. Some of the current adapters make use of this mechanism, and provide their own subclasses of the RubyJdbcConnection class (Oracle, SQLite3, MySQL, etc.). Note the interesting way that particular public methods from this Java class are exposed and visible for the Ruby part, via using a special JRuby annotation:

@JRubyMethod(name = "indexes")
public IRubyObject indexes(ThreadContext context, IRubyObject tableName, IRubyObject name, IRubyObject schemaName) {
    return indexes(context, toStringOrNull(tableName), toStringOrNull(name), toStringOrNull(schemaName));
}

Examples of such trans-language calls can be found here:

def indexes(table, name = nil)
  @connection.indexes(table, name, @connection.connection.meta_data.user_name)
end

The indexes method called here exists nowhere in the Ruby world, rather it is coming from the Java code shown above.

So you see that the @connection plays a central role in the adapter concepts, sometimes you use it directly, sometimes not.

How do you use this Java part ? Again, inheritance: you can inherit from the generic AR-JDBC class, and plugin your extension class via a special JRuby service load mechanism. Here is how to achieve this: check BasicLibraryService and LoadService classes. For dealing with the logistics of the Java parts of your gem, you can see how we have done it here. This description refers to the Java part of the Sybase adapter, which can be found here.

Basically, your adapter evolves in executing iterations of the following kind: take some application, configure it to use your adapter, and try to run it successfully against your database. This includes running the db:migrate task, for getting started. Once you have your db schema populated, you start your application and execute some business logic with it - according to the scenarios that you wish to cover. If some problem occurs, most probably it will be related to a wrong SQL statement being sent to the db. Now step aside from all this Ruby stuff, and take some time to figure out what the correct SQL code should be. You may need to check some documentation for your database. When you get to know the places where the SQL statement should be modified in order to work for your db, you will have to find where in the AR-JDBC Ruby API you can apply such modification. Next, you apply it and then ... try again.

Of course, we are not stating that every possible issue should be solvable in this fashion, but truth is that most are. Sometimes the needed change is very small, sometimes not: like having to introduce a new module, a couple of methods, etc. Sometimes one single iteration of that kind can take days of research and experiments, until you get it working. But in general this looks like the right direction to go, and we recommend it.

Eventually the targeted scenarios for your app will be running fine. Next, if you wish you may try the same steps with another Rails app, etc. Of course, the adapter will get more and more mature in time, depending heavily on the number of applications and scenarios that you have enabled successfully with it. This iterative strategy is also recommended by Ola Bini in his book Practical JRuby on Rails Web 2.0 Projects.

Let's take a look at some real world examples of issues that we had, and how we solved them.