byterock

Its go back and fix day here in the Moose-Pen

In a recent post I mentioned that I went out and read though a number of SQL syntax books to make sure I was doing things correctly in which SQL clauses (Where, Sort, Link) goes where. That go me to thinking was the system I was using to test the SQL on a real DB using standard SQL?

Normally I test with Oracle App Express as it work well on a Windows box and has a nice UI but I just noticed that I have some odd SQL in one of my test cases 15_alias.t;

I was starting with this hash;

my $in_hash = {

    da_compose_only=>1,

    update_requires_condition=>0,

    delete_requires_condition=>0,

    view     => { name => 'people',

                  alias=> 'sys_users' },

    elements => [

        {

            name => 'last_name',

        },

        {

            name => 'first_name',

        },

    ],  

};

And from my playing about I would generate this SQL

INSERT INTO people sys_users ( sys_users.first_name, sys_users.last_name ) VALUES( ?, ? )

using the 'sys_users' as an alias. Now this works fine on SQL Command page of Oracle App express and that is what I coded Driver::DBI to do.

However, from what I read in a number of docs, even the Oracle ones I did not find any reference to one being able to use an alias in an insert. I seems this is just a very old quirk in the Oracle SQL*Plus command line parser. It works when that interface is used but actually fails on a normal DBI call with Perl. So an alias on an insert is not part of standard SQL so I better review my tests and when I check something on a DB will stop using SQL*Plus and stick with a pure Perl app.

Since I had to re-write most of the code in the test I took the opportunity to expand on my 'Tests::Utils::sql_param_ok' sub. I expanded to to to all four CRUD actions on a given set. So now my test hash-ref looks like this

my $tests = [{

    caption =>'Basic Table Alias',

    create  =>{container=>{first_name=>'Bill',

                           last_name =>'Bloggings'},

               sql      =>"INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )",

               params   =>['Bill','Bloggings']},

               

    retrieve=>{sql      =>"SELECT sys_users.last_name, sys_users.first_name FROM people sys_users"},

    update  =>{container=>{first_name=>'Robert'},

               sql      =>"UPDATE people SET first_name = ?"},

               params   =>['robert'],

    delete  =>{sql      =>"DELETE FROM people"},

}];

and a test call like this

$utils->sql_param_ok($in_hash,$tests);

I did do a compete re-write of the sub to;

sub sql_param_ok {

    my $self = shift;

    my ( $in_hash, $tests ) = @_;

    foreach my $test ( @{$tests} ) {

        foreach my $action ((qw(create retrieve update delete))){

          next 

            unless(exists($test->{$action}));

          my $sub_test = $test->{$action};

          if ( exists( $sub_test->{index} ) ) {

            $in_hash->{ $sub_test->{key} }->[ $sub_test->{index} ] =

              $sub_test->{ $sub_test->{key} };

          }

          elsif ( exists( $sub_test->{key} ) ) {

            $in_hash->{ $sub_test->{key} } = $sub_test->{ $sub_test->{key} };

          }

          my $da = Database::Accessor->new($in_hash);

          $da->$action( $self->connect(), $sub_test->{container});        

          my $ok = ok(

            $da->result()->query() eq $sub_test->{sql},

            $test->{caption} . " $action SQL correct"

          );

          unless ($ok) {

            diag(   "Expected SQL--> "

                  . $sub_test->{sql}

                  . "\nGenerated SQL-> "

                  . $da->result()->query() );

          }

          cmp_deeply( $da->result()->params, $sub_test->{params},

            $test->{caption} . " $action params correct" )

            if ( exists( $sub_test->{params} ) );

        }

    }

}

Which will generate a bunch of fails for me until I update the tests but I will do those updates later.

As for the SQL I have gone with the simplest solution to my problem taking UPDATE for example, this

UPDATE people sys_users SET sys_users.first_name = ?

and this

UPDATE people SET people.first_name = ?

works in Oracle but fails in a number of other DB but

UPDATE people SET first_name = ?

works in all of them. So that is what I am going with.

Now I have to fix these three fails;

not ok 1 - Basic Table Alias SQL correct … not ok 4 - Basic Table Alias SQL correct not ok 5 - Basic Table Alias SQL correct

The changes to Driver::DBI turned out to be rather simple; First I had to modify the '_field_sql' sub a little

sub _field_sql {
  my $self = shift;
--  my ($element,$use_alias) = @_;
++  my ($element,$use_view) = @_; 
…
--    my $sql = $element->view
++    my $sql = $element->name;
++    $sql = $element->view
           ."."
--           .$element->name;
--    $sql .= join(" ",
--                 "",
--                 Database::Accessor::Driver::DBI::SQL::AS, 
--                 $element->alias())
--       if ($element->alias and $use_alias );
++           .$element->name
++      if ($use_view );
    return $sql;
  } 

and the '_elements_sql ' sub a little

...
 --push(@fields,$self->_field_sql($field,1));
++    my $sql = $self->_field_sql($field,1);
++    $sql .= join(" ",
++         "",
++         Database::Accessor::Driver::DBI::SQL::AS, 
++         $field->alias())
++      if ($field->alias());
     push(@fields,$sql); 

and a little change to the '_table_sql' sub

sub _table_sql {
  my $self = shift;
--  my ($view) = @_;
++  my ($view,$use_alias) = @_;
  my $sql = $view->name;
  $sql = join(" ",
               $view->name,
               $view->alias)
--    if $view->alias();
++    if ($use_alias and $view->alias());
  return $sql;              
} 

and now I am getting what I want;

ok 1 - Basic Table Alias create SQL correct ok 2 - Basic Table Alias create params correct ok 3 - Basic Table Alias retrieve SQL correct ok 4 - Basic Table Alias update SQL correct ok 5 - Basic Table Alias delete SQL correct ok 5 - Basic Table Alias SQL correct

onto other things;