Hi Darin - It looks like MySQL supports a few different ways of converting text into nulls. As you pointed out, I can't find documentation about turning empty strings into nulls. However, it looks like you can convert \N (or NULL, depending on some other settings) into a null. See http://dev.mysql.com/doc/refman/6.0/en/load-data.html starting with "Handling of NULL values varies ...".
Similarly, by default Postgres treats \N as a null, although it can be configured to act differently. See http://www.postgresql.org/docs/8.3/interactive/sql-copy.html starting with "null string". Maybe the best way to handle this would be to make AW output nulls as \N instead of empty strings. Would you be willing to look into that? I agree it's best to have AW-ETL work the same for both adapters. Best, Jacob ----- "Darin Wilson" <[EMAIL PROTECTED]> wrote: > Hi Jacob- > > I finally got around to testing your fixes. Unfortunately, MySQL does > not > convert empty strings to nulls out of the box, nor could I find a way > to > make it support this behavior. (This was after a small amount of > googling > around; there may be an answer somewhere that I just couldn't find - > maybe > someone here knows?). > > I'll keep poking around a little bit and see if I can see a way around > this > - it would be a bummer if the ETL framework exhibited radically > different > behavior depending on which adapter it was using. > > Darin > > > > > From: Jacob Maine <[EMAIL PROTECTED]> > > Date: Fri, 17 Oct 2008 11:07:53 -0700 (PDT) > > To: Darin Wilson <[EMAIL PROTECTED]> > > Cc: <activewarehouse-discuss@rubyforge.org> > > Subject: Re: [Activewarehouse-discuss] would the real codebase > please stand > > up? :) > > > > Hi Darin - > > > > Please pull from my adapter_extensions branch > > (http://github.com/mainej/adapter_extensions/tree) or wait until > Anthony pulls > > my changes. Unfortunately, I'm not sure that will fix > > test_bulk_import_with_empties. I can't be sure because I don't have > MySQL > > installed, and don't have time to do that right now. > > > > The change I made to adapter_extensions allows Postgres to import > empty > > strings as nulls. That's what the test is about. I put the test in > for both > > adapters, hoping that MySQL would do the right thing out of the box. > If it's > > not working, I suggest either making your proposed change, or > looking at > > adapter_extensions to see if you can get MySQL to handle empty > strings in a > > more useful way. I'll look at this stuff more this weekend to make > sure my > > system is up-to-date and tests are passing for Postgres. > > > > Hopefully with two groups developing for Postgres and MySQL, we'll > get AW & AW > > ETL back into a stable state. > > > > Best, > > Jacob > > > > > > ----- Darin Wilson <[EMAIL PROTECTED]> wrote: > >> I just pulled the latest from your github repo and ran the ETL test > suite > >> locally against MySQL. I got 10 errors that were about SCDs, so I'm > assuming > >> those were the same ones you mentioned in your last message(?). > >> > >> There was one other error: > >> > >> 1) Failure: > >> test_bulk_import_with_empties(ProcessorTest) > [./test/processor_test.rb:19]: > >> <ActiveRecord::StatementInvalid> exception expected but none was > thrown. > >> > >> It turns out that MySQL is much less strict about what goes into > the bulk > >> loader - if you try to load a row with an empty value into a column > that > >> disallows null, MySQL just skips it without an error. Nice! :) > >> > >> I'm not sure how you'd like to handle that. I tried poking around a > bit to > >> see if there's a way to force MySQL into being more strict, but I > couldn't > >> get anything to work reliably. The simplest fix would be to do > something > >> like this in the test: > >> > >> unless ETL::Engine.connection(:data_warehouse).class == > >> ActiveRecord::ConnectionAdapters::MysqlAdapter > >> assert_raise(ActiveRecord::StatementInvalid) { > >> do_bulk_import('bulk_import_with_empties.txt') } > >> end > >> > >> which sort of invalidates the test, but it doesn't seem like > there's a way > >> to make this test actually work with MySQL. Your call. ;) > >> > >> Darin > >> > >> > >>> From: Anthony Eden <[EMAIL PROTECTED]> > >>> Date: Thu, 16 Oct 2008 15:23:04 -0400 > >>> To: Darin Wilson <[EMAIL PROTECTED]> > >>> Cc: <activewarehouse-discuss@rubyforge.org> > >>> Subject: Re: [Activewarehouse-discuss] would the real codebase > please stand > >>> up? :) > >>> > >>> I think I have most of the fixes to ETL in for the 2.1 changes. > I've > >>> started changing over some of the tests to use shoulda 1.x, so if > you > >>> want to run the built in tests then you'll need the shoulda gem. > There > >>> are currently two failing tests related to the SCD > implementation. > >>> Jacob, was that an area you worked on? If so could you take a look > at > >>> the failing tests? Also, I currently have tested with Postgres > locally > >>> but not MySQL, so if someone wants to fight through the test suite > on > >>> MySQL, be my guest. :-) > >>> > >>> Thanks. > >>> > >>> -Anthony > >>> > >>> On Thu, Oct 16, 2008 at 12:11 PM, Darin Wilson > >>> <[EMAIL PROTECTED]> wrote: > >>>> Cool - that is what I was going to start working on. :) > >>>> > >>>> Please let us know when your 2.1 fixes to ETL are checked in - > I'd like to > >>>> try it out with our ETL (and I have a bunch of test cases for > it). > >>>> > >>>> Thanks! > >>>> Darin > >>>> > >>>>> From: Anthony Eden <[EMAIL PROTECTED]> > >>>>> Date: Thu, 16 Oct 2008 06:59:41 -0400 > >>>>> To: Marty Haught <[EMAIL PROTECTED]> > >>>>> Cc: <activewarehouse-discuss@rubyforge.org> > >>>>> Subject: Re: [Activewarehouse-discuss] would the real codebase > please > >>>>> stand > >>>>> up? :) > >>>>> > >>>>> I've started the upgrade to 2.1. I've already modified > ActiveWarehouse > >>>>> ETL to handle the new migration process and I have it running > right > >>>>> now under 2.1. Once I get past my first ETL load on the project > I'm > >>>>> working on I'll move onto the AW plugin. > >>>>> > >>>>> -Anthony > >>>>> _______________________________________________ > >>>>> Activewarehouse-discuss mailing list > >>>>> Activewarehouse-discuss@rubyforge.org > >>>>> http://rubyforge.org/mailman/listinfo/activewarehouse-discuss > >>>> > >>>> > >>>> _______________________________________________ > >>>> Activewarehouse-discuss mailing list > >>>> Activewarehouse-discuss@rubyforge.org > >>>> http://rubyforge.org/mailman/listinfo/activewarehouse-discuss > >>>> > >> > >> > >> _______________________________________________ > >> Activewarehouse-discuss mailing list > >> Activewarehouse-discuss@rubyforge.org > >> http://rubyforge.org/mailman/listinfo/activewarehouse-discuss > > > > > _______________________________________________ > Activewarehouse-discuss mailing list > Activewarehouse-discuss@rubyforge.org > http://rubyforge.org/mailman/listinfo/activewarehouse-discuss _______________________________________________ Activewarehouse-discuss mailing list Activewarehouse-discuss@rubyforge.org http://rubyforge.org/mailman/listinfo/activewarehouse-discuss
