Two Database Engines, One Table

May 7, 2012 / By David Perry

Tags: , , , ,

My esteemed coworker Yanick¬†Knows Perl. Let me put that in perspective: I know perl, but he Knows it deeply enough to merit the capitalization. So when he said that something I did is noteworthy — that is, in fact, worth blogging — I listened. So here I am, and here it goes!

Our flagship tool, Support Track, is steadily migrating over to use DBIx::Class to read and manipulate our databases. This is a very useful tool, for many reasons that can be better explained by others. One of these reasons is that, thanks to the magic of SQLite, it lets us write unit test scripts, and other quick prototyping codes, without needing to set up a heavy database server to run against.

However, Support Track is powered by Oracle, not SQLite, and while DBIx::Class abstracts most of the differences out of our code, it can’t completely eliminate them. How do we overcome the syntactic differences?

In the beginning

We had a quick-and-dirty “solution” in place, wherein we would stick boilerplate like the following at the top of each test script:

BEGIN {
   # necessary to get past the schemas in the table
   for my $name ( Pythian::SupportTrack::Schema->sources ) {
       my $table = Pythian::SupportTrack::Schema->source($name);
       my $table_name = $table->name;
       $table->name( $table_name ) if $table_name =~ s/./_/g;
   }
}

This BEGIN block simply replaces any period with an underscore in table names. This is to work around the first and most obvious discrepancy between the two database engines: Oracle uses a schema.tablename convention for specifying its tables, but SQLite doesn’t like periods in its table names.

That bit of code does the job, but it’s not small. Worse, it’s copied at the top of each of our test scripts, which is anathema to the notion of code reuse. We use libraries in our “real” code, but apparently not in our unit test scripts.

This is unacceptable, in my mind.

Change will do you good

I ran into another discrepancy between Oracle and SQLite, and I felt dirty just adding it to the boilerplate of my test script. So, I created a Perl module to encapsulate it. I’ve tentatively named it Schema::Oracle2SQLite, and it lives in our t/lib/ directory, neighbor to the test scripts themselves.

So what was that second discrepancy? It’s in how auto-increment columns are described in DBIx::Class::ResultSource. DBIx::Class::Schema::Loader saw an auto-increment column in our Oracle database and generated code like this for our schema class:

__PACKAGE__->add_column(
  "pd_id",
  {
    data_type => "NUMBER",
    default_value => undef,
    is_nullable => 0,
    size => 126,
    auto_nextval => 1,
    sequence =>"pd_id_seq"
  }
);

But when I created a test script against the table with that column, I got a less-than-helpful error message:

DBIx::Class::Schema::txn_do(): Can't locate object method "_sequence_fetch" via package "DBIx::Class::Storage::DBI::SQLite" at /.../DBIx/Class/Storage/DBI.pm line 1621.

As it turns out, SQLite doesn’t support named sequences like Oracle, and so DBIx::Class throws a fit. It prefers this syntax for specifying auto-increment columns. (Note that I’m not changing the data-type info):

__PACKAGE__->add_column(
  "pd_id",
  {
    data_type => "NUMBER",
    default_value => undef,
    is_nullable => 0,
    size => 126,
    is_auto_increment => 1
  }
);

So, there’s another change that needs to be made in every test script. Or, I could make it in Schema::Oracle2SQLite. I did the latter, so the module now looks like this:

package Schema::Oracle2SQLite;

=comment

Include this module at the top of your tests to deal with inconsistencies
in data management between Oracle (for which the product is written) and
SQLite (which is used for unit tests).

=cut

use 5.10.0;
use strict;
use warnings;

# This would be in a BEGIN block for normal test files.
sub _setup_schema {
    my ($class, $schema_obj) = @_;
    for my $name ( $schema_obj->sources ) {
        my $table = $schema_obj->source($name);

        # necessary to get past the schemas in the table
        # (this part should look familiar to you the reader)
        my $table_name = $table->name;
        $table->name( $table_name ) if $table_name =~ s/./_/g;

        # necessary to work around differing syntax for sequences
        for my $col_name ($table->columns) {
            my $col_info = $table->column_info($col_name);
            if( $col_info->{auto_nextval} && $col_info->{sequence} ) {
                delete $col_info->{auto_nextval};
                delete $col_info->{sequence};
                $col_info->{is_auto_increment} = 1;
                $table->column_info( $col_name, $col_info );
            }
        }
    }
}

sub import {
    my $class = shift;

    for my $module (@_) {
        eval "require $module";
        $class->_setup_schema($module);
    }
}

1;

If you didn’t spot the fix, the short version is that it unsets the column attributes, which makes Oracle tick and SQLite explode, and replaces them with the SQLite-friendly is_auto_increment attribute. And it does this while reducing our per-test-script boilerplate to two lines:

use lib 't/lib';
use Schema::Oracle2SQLite 'Pythian::SupportTrack::Schema';

That’s win-win, in my opinion.

Undocumented Victory

Underlying this optimization and transformation is something subtle, yet mind-blowing (at least for me): You can actually modify column definitions after the fact. There’s nothing in the documentation of DBIx::Class::ResultSource that hints that $table->name() or $table->column_info() can be used as setters; they’re only referred to as getters.

And really, why would anyone need them or care about them? If you need the definition changed, why not just change the .pm file where they’re defined? Now that we have code that should support different database engines, I know why.

Final Notes

Of course, just as the syntax differs, the functioning of the database engines is going to differ in key areas as well. The unit tests that leverage SQLite are no replacement for proper integration testing on an Oracle database. But they are a handy way for us to verify that our code behaves as it should, and when it doesn’t, to catch the problems before they reach the integration testing phase.

One more thing: I implied in passing that the data type definitions also differ between Oracle and SQLite. Where I have the Oracle standard NUMBER format above, SQLite would be happy with int. In practice, we haven’t hit a situation yet where this difference has become important.

But if it ever does, I’ll only have to change it in one place.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>