December 2010 Archives

Migrating 76 tables out of MS-SQL. Thanks DBIx::Class!

Just another day at the office moving databases into MySQL. 10 lines of DBIx::Class::Schema::Loader make_schema_at() and I'm done. :)

Usually.

Unfortunately today the table names "Order" and "Service-tier2" blow up:

Bad table or view 'Order', ignoring: DBIx::Class::Schema::Loader::make_schema_at(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Order WHERE ( 1 = 0 )' at line 1 [for Statement "SELECT * FROM Order WHERE ( 1 = 0 )"] at refresh_schema.pl line 9

Apparently "Order" is semi-reserved by MySQL, and dashes are trouble:

SELECT * FROM Order              # syntax error
SELECT * FROM `Order`            # works
SELECT * FROM Service-tier2      # syntax error
SELECT * FROM `Service-tier2`    # works

Another point for irc.perl.org #dbix-class, as ilmari pointed me to quote_char as the cure for what ailed me.

use strict;
use DBIx::Class::Schema::Loader qw( make_schema_at );
make_schema_at(
   "P3::Schema", {
      dump_directory     => ".",
   },
   [ "dbi:mysql:protec", $user, $password, {
      quote_char => '`'
   } ],
);

Beauty. I'm still hitting this new warning because the column name 'can' (Client Access Number) collides with UNIVERSAL::can():

Column can in table ClContact collides with an inherited method.
See "COLUMN ACCESSOR COLLISIONS" in perldoc DBIx::Class::Schema::Loader::Base .

Oops! :) But that's an easy work-around in my Result/ class. I just have to pick anything that's not reserved:

"can",
{ accessor => "cant", data_type => "varchar", is_nullable => 0, size => 10 },

So to access that column I now call $clcontact->cant(). Why? Because now I can. :)

FIXED: DBICSL can't 'can'

One of my client's schemas contains a table which contains a column named 'can'. In his context this is a convenient abbreviation for 'Client Access Number'. In Perl however, ->can() is already spoken for. Since DBIx::Class::Schema::Loader creates an accessor method for each column of each table in your database, things went sideways on me today when I tried to /users/jhannah_mutation_grid/2010/12/index.html

About Jay @ Mutation Grid

user-pic Perl / web / database development since 1995. Contact us for your next project.