fsync(2)

I developed a Rails application at work and I needed to export that data for import into another system. The other system is running Oracle 11g. I am using MySQL. I read extensively on SQL Loader and provided the data that way, but I also wanted to provide another loader that would use sequences correctly, etc. instead of just assuming a reserved set of ID values for imported data. I also wanted this to work on any reasonable platform.

I need to use JRuby for another project already because I need to interface directly with some Java code, so I decided to use JRuby here as well so that it wold just work on multiple platforms. I also decided to go with DataMapper as it seemed appropriate for this job. I’ve not used DataMapper before, and I had one problem in particular with using a sequence for a primary key. There is a decent amount of documentation for DataMapper, but there could be more :-) I saw some posts about doing something like this:

property :id,                     Serial,   :sequence => 'ECA_REQUEST_S'

I kept getting an ArgumentError error however. I fixed that by putting the following in my code:

# This is required so that we can specify a :sequence argument for the Serial
# primary key on the ECA_REQUEST_T table. Normally this option would cause
# an ArgumentError error to be raised from line 826 in the following file:
#
# Note: prepend <Ruby Dir>/lib/ruby/gems/<version>/gems to all paths here.
#
# dm-core-1.0.0/lib/dm-core/property.rb
#
# It is clear in the following file:
#
# dm-oracle-adapter-1.0.0/lib/dm-oracle-adapter/adapter.rb
#
# that :sequence is definitely used by the Oracle adapter (plus, it actually
# does work - imagine that :-) I am not sure how else this is done in
# DataMapper. I'd love to know, because this seems weird, but it definitely
# works right.
DataMapper::Property.accept_options(:sequence)

The comments pretty much say it all. I don’t know if there is a better or more correct way to allow the :sequence to be specified like I needed, but the code above worked fine. Nothing else I did seemed to work, but I would like to know if I am just missing something.

This was quite an adventure. My first attempt went from MySQL to YAML to SQLite and then finally into Oracle. I changed the code and now the process goes from MySQL to SQLite and then finally into Oracle. I have to store the data in some sort of secondary storage in this case, so I can’t just go between databases directly. I also have to convert between the two schemas. It turns out that this was a great learning experience for using DataMapper, but it also helped out because I could compare the loads from both methods. I discovered a minor character set issue that I was able to deal with because of this work.

I am impressed with DataMapper, but I’ll still use ActiveRecord in my Rails projects for now. There could be some more documentation about dealing with legacy databases and the specific issue I ran into, but when isn’t that true? Don’t forget to copy your Oracle JDBC driver to the JRuby lib subdirectory as well. That hung me up for a few minutes, and once again I learned that sometimes you should just look at the source code the error message reports instead of jumping directly to Google. This was a ton of work, it probably won’t be used, but it was fun and I learned something. That alone is awesome. I love this stuff.