byterock

Its back-fix code day here in the Moose-pen

In yesterday's post-ette I ranted on about how much of a smarty pants I was to use a 'param' in as the type for my 'message' part of a case statement. That was working fine until I blundered across this perfectly valid SQL

SELECT StockID, OnOrderQuantity,
   CASE
     WHEN Stock - OnOrderQuantity > 30 THEN  'Over Stocked'
     WHEN Stock - OnOrderQuantity > 0 AND Stock-OnOrderQuantity <=30 THEN 'Stocked'
     ELSE Stock - OnOrderQuantity
   END
FROM Stock;

The 'else' in this case is an 'expression' and if I prototyped this out like this

 whens => [
            {
                left       => { expression=>'-',
                                     left=>{name  => 'Stock'},
                                     right=>{name =>'OnOrderQuanity'} },
                right      => { value => '30' },
                operator   => '>',
                message => { value => 'Over Stocked' }
            },
            [
                {
                    left     => { expression=>'-',
                                     left=>{name  => 'Stock'},
                                     right=>{name =>'OnOrderQuanity'} },
                    right    => { value => '0' },
                    operator => '>',
                },
                {
                    condition  => 'and',
                    left       => { expression=>'-',
                                     left=>{name  => 'Stock'},
                                     right=>{name =>'OnOrderQuanity'} },
                    right      => { value => '30' },
                    operator   => '<=',
                    message => { value => 'Stocked' }
                },
            ],
            { message => { value => 'Stock - OnOrderQuantity' } },
        ]

and that final message would fail as it is a param not an expression, and my send sql would look like this

CASE
     WHEN Stock - OnOrderQuantity > ? THEN  ?
     WHEN Stock - OnOrderQuantity > ? AND Stock-OnOrderQuantity <=? THEN ?
     ELSE ?
   END

Which would not work as expected as what would be returned by that final 'else' is the steing 'Stock – OnOrderQuantity' not a value like -1 , -10 etc.

I need to be able to do this;

{ message => { expression=>'-',

                                      left=>{name  => 'Stock'},

                                     right=>{name =>'OnOrderQuanity'}  

                       }

 },

Doing a little more playing about with SQL I see that my 'message' field can be any valid field even a another case statement. So a little revision is in order me thinks;

I think I need only that 'message' attribute a little to make this work and I will go with just this change for now

++has 'message' => (

has 'statement' => (

--            isa      => 'Param',

++          isa      => 'Expression|Param|Element|Function|Case',

            is       => 'rw',

            # required => 1,

        );

I only changed the name of the attribute and expanded its 'isa' to include all the differing types including a Case.

Next after this a quick change to the test case to account for that attributes name change and a run of the test and I get a full pass.

Now add in a bunch of test cases for those four new types I will not bore you with all of that code as things when fine until I had to add a 'Case' into the a 'statement' attribute

$case = Database::Accessor::Case->new(

    {

        whens => [

            {

                left      => { name  => 'Price', },

                right     => { value => '10' },

                operator  => '<',

                statement => { name  => 'price' }

            },

            {

                statement => {

                    whens => [

                        {

                            left      => { name  => 'Price', },

                            right     => { value => '10' },

                            operator  => '<',

                            statement => { name  => 'price' }

                        },

                        { statement => { name => 'price' } }

                    ]

                }

            }

        ]

    }

);

$last = pop( @{ $case->whens } );

ok( ref( $last->statement ) eq 'Database::Accessor::Case',

    "last statment is an Case" );

and got this result;

Attribute (name) is required at D:\GitHub\database-accessor\lib/Database/Accessor/Types.pm line 178# Looks like your test exited with 255 just after 13.

To fix this one I will have to extend that '_element_coerce' sub in my 'Database::Accessor::Types' class to include that new 'Case' class;

    elsif ( exists( $hash->{value} ) || exists( $hash->{param} ) ) {

        $object = Database::Accessor::Param->new( %{$hash} );

    }

++    elsif ( exists( $hash->{whens} ))  {

++        $object = Database::Accessor::Case->new( %{$hash} );

++    }

    else {

        $object = Database::Accessor::Element->new( %{$hash} );

and now I am getting a full pass even with a final test where I use all the classes in a 'case'

So maybe I am ready to move onto Driver::DBI??