Better Moose Testing

综合技术 2018-07-13

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 = {




    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


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))){



          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

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

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(" ",
--    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;

责编内容 (源链)。感谢您的支持!


Start Your End-to-End Testing With Angular and Cyp... In this blog post, we will cover how to get started with end-to-end testing u...
Attempting to Run SQL on Linux Inside Windows Subs... Shawn Melton MVP and dbatools contributor last week had an issue running...
3 Quick SQL Developer Tips to Kick off 2018 I’m in Denver today, to present one of my favorite talks, SQL Developer Tips &am...
Sharding-JDBC 1.5.2 发布:开放测试引擎 Sharding-JDBC 1.5.2 正式发布。主要更新是SQL测试引擎的开放。 为了达到对于高质量的追求,Sharding-JDBC的测试覆盖率一直...
Building SQL ConstantCare®: Analyzing Query Plans XML Delenda Est Last week, I talked about how we’re going to be collecting qu...