view release on metacpan or  search on metacpan

t/v2_Dbix-DataModel.t  view on Meta::CPAN

     activities => {d_end => {"<" => '01.01.2001'}},
     dpt        => {dpt_head => 999},
   },
 );
$expected_sql_bind[0] = <<__EOSQL__;
     SELECT * FROM T_Employee 
       LEFT OUTER JOIN T_Activity
         ON T_Employee.emp_id = T_Activity.emp_id AND d_end < ?
       LEFT OUTER JOIN T_Department AS dpt
         ON T_Activity.dpt_id = dpt.dpt_id AND dpt_head = ?
       WHERE dpt_name = ? AND firstname = ?
__EOSQL__
sqlLike(@expected_sql_bind, 'where_on with table alias');


# proper error message if not used on a join
{ eval { HR->table('Employee')->select(-where_on => {Foo => 'bar'}); };
  my $err = $@;
  like $err, qr/where_on/, 'error message when the source for -where_on is not a join' ;
}


# proper error message if wrong table in -where_on
{ eval {HR->join(qw/Employee activities => department|dpt/)->select(
             -where    => {firstname => 'Hector', dpt_name  => 'Music'},
             -where_on => {BAD_TABLE => {d_end => {"<" => '01.01.2001'}}},
            ); };

  my $err = $@;
  like $err, qr/where_on.*?BAD_TABLE/, 'error message for bad table in -where_on' ;
}






#----------------------------------------------------------------------
# test insert() method
#----------------------------------------------------------------------

# plain insertion using arrayref syntax
my ($bach_id, $berlioz_id, $monteverdi_id) = 
  HR::Employee->insert([qw/ firstname    lastname   /],
                       [qw/ Johann       Bach       /],
                       [qw/ Hector       Berlioz    /],
                       [qw/ Claudio      Monteverdi /]);
my $insert_sql = 'INSERT INTO T_Employee (firstname, lastname) VALUES (?, ?)';
sqlLike($insert_sql, [qw/ Johann       Bach       /],
        $insert_sql, [qw/ Hector       Berlioz    /],
        $insert_sql, [qw/ Claudio      Monteverdi /],
        'insert with arrayref syntax');

# insertion into related class
$emp->insert_into_activities({d_begin =>'2000-01-01', d_end => '2000-02-02'});
sqlLike('INSERT INTO T_Activity (d_begin, d_end, emp_id) ' .
          'VALUES (?, ?, ?)', ['2000-01-01', '2000-02-02', 999],
          'add_to_activities');


# cascaded inserts
my $tree = {firstname  => "Johann Sebastian",  
            lastname   => "Bach",
            activities => [{d_begin  => '01.01.1707',
                            d_end    => '01.07.1720',
                            dpt_code => 'Maria-Barbara'},
                           {d_begin  => '01.12.1721',
                            d_end    => '18.07.1750',
                            dpt_code => 'Anna-Magdalena'}]};
my $emp_id = HR::Employee->insert(clone($tree));
my $sql_insert_activity = 'INSERT INTO T_Activity (d_begin, d_end, '
                        . 'dpt_code, emp_id) VALUES (?, ?, ?, ?)';
sqlLike('INSERT INTO T_Employee (firstname, lastname) VALUES (?, ?)',
        ["Johann Sebastian", "Bach"],
        $sql_insert_activity, 
        ['1707-01-01', '1720-07-01', 'Maria-Barbara', $emp_id],
        $sql_insert_activity, 
        ['1721-12-01', '1750-07-18', 'Anna-Magdalena', $emp_id],
        "cascaded insert");

# option -returning => {}
$dbh->{mock_start_insert_id} = 10;
$result   = HR::Employee->insert(clone($tree), -returning => {});
my $expected = { emp_id     => 10, 
                 activities => [{act_id => 11}, {act_id => 12}]};
is_deeply($result, $expected,  "results from -returning => {}");

# insert with literal SQL
$emp_id = HR::Employee->insert({
  birthdate  => \["TO_DATE(?, 'DD.MM.YYYY')", "10.09.1659"],
  firstname  => "Henry",
  lastname   => "Purcell",
 });
sqlLike( q[INSERT INTO T_Employee (birthdate, firstname, lastname) ]
        .q[VALUES (TO_DATE(?, 'DD.MM.YYYY'), ?, ?)],
        ["10.09.1659", "Henry", "Purcell"],
        "insert with SQL function");



#----------------------------------------------------------------------
# test update() method
#----------------------------------------------------------------------

# syntax $class->update($pk, {...})
HR::Employee->update(999, {firstname => 'toto', 
                           d_modif => '02.09.2005',
                           d_birth => '01.01.1950',
                           last_login => '01.09.2005'});
sqlLike('UPDATE T_Employee SET d_birth = ?, firstname = ? '.
        'WHERE (emp_id = ?)', ['1950-01-01', 'toto', 999], 'update');


  # -set / -where / -ident
  HR->table('Employee')->update(
    -set   => {foo => 123},
    -where => {baz => {">" => {-ident => 'buz'}}},
   );
  sqlLike("UPDATE T_Employee SET foo = ? WHERE baz > buz",
          [123],
          "update(-set => .., -where => { ... -ident})");



# syntax $class->update($obj)
HR::Employee->update(     {firstname => 'toto', 
      		 d_modif => '02.09.2005',
      		 d_birth => '01.01.1950',
      		 last_login => '01.09.2005',
      		 emp_id => 999});
sqlLike('UPDATE T_Employee SET d_birth = ?, firstname = ? '.
        'WHERE (emp_id = ?)', ['1950-01-01', 'toto', 999], 'update2');


# syntax $instance->update()
$emp = HR::Employee->bless_from_DB({emp_id    => 999,
                                    firstname => 'Joseph',
                                    lastname  => 'BODIN DE BOISMORTIER',
                                    d_birth   => '1775-12-16'});