view release on metacpan or  search on metacpan

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

                                 [qw/1 1bis/],
                                ];
  $hashref = HR->join(qw/Employee activities/)->select(
    -resultAs => [hashref => qw/emp_id foo/],
   );
  is_deeply($hashref, {1 => {1      => {emp_id => 1, foo => 1},
                             '1bis' => {emp_id => 1, foo => '1bis'}},
                       2 => {2      => {emp_id => 2, foo => 2}}},
              'resultAs => [hashref => @cols]');

  $dbh->{mock_clear_history} = 1;
  $dbh->{mock_add_resultset} = [ [qw/emp_id act_id/],
                                 [qw/1 1/], 
                                 [qw/2 2/],
                                 [qw/1 1bis/],
                                ];

  SKIP: {
    skip "THINK: semantics of ->primary_key for a join", 1;
    $hashref = HR->join(qw/Employee activities/)->select(
      -resultAs => 'hashref'
     );
    is_deeply($hashref, {1 => {1      => {emp_id => 1, act_id => 1},
                               '1bis' => {emp_id => 1, act_id => '1bis'}},
                         2 => {2      => {emp_id => 2, act_id => 2}}},
                'resultAs => "hashref"');
  };


  # subquery
  my $subquery = HR::Employee->select(
    -columns  => 'emp_id',
    -where    => {d_birth => {-between => [1950, 1980]}},
    -resultAs => 'subquery',
   );
  $act = HR::Activity->select(-where => {emp_id => {-not_in => $subquery}});
  sqlLike('SELECT * FROM T_Activity WHERE emp_id NOT IN '
            . '(SELECT emp_id FROM T_Employee WHERE d_birth BETWEEN ? AND ?)',
          [1950, 1980],
         'subquery');

  # 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');


  # test 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(dclone($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");

  # test the -returning => {} option
  $dbh->{mock_start_insert_id} = 10;
  $result   = HR::Employee->insert(dclone($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");

  HR::MyView->select({c3 => 22});

  sqlLike('SELECT DISTINCT column1 AS c1, t2.column2 AS c2 ' .
	  'FROM Table1 AS t1 LEFT OUTER JOIN Table2 AS t2 '.
	  'ON t1.fk=t2.pk ' .
	  'WHERE (c1 = ? AND c2 LIKE ? AND c3 = ?)',
	     ['foo', 'bar%', 22], 'HR::MyView');

  my $view = HR->join(qw/Employee activities department/);
  $view->select("lastname, dpt_name", {gender => 'F'});

  sqlLike('SELECT lastname, dpt_name ' .
	  'FROM T_Employee LEFT OUTER JOIN T_Activity ' .
	  'ON T_Employee.emp_id=T_Activity.emp_id ' .
	  'LEFT OUTER JOIN T_Department ' .
	  'ON T_Activity.dpt_id=T_Department.dpt_id ' .
	  'WHERE (gender = ?)',
             ['F'], 'join');


  my $view2 = HR->join(qw/Employee <=> activities => department/);
  $view2->select("lastname, dpt_name", {gender => 'F'});

  sqlLike('SELECT lastname, dpt_name ' .
	  'FROM T_Employee INNER JOIN T_Activity ' .
	  'ON T_Employee.emp_id=T_Activity.emp_id ' .
	  'LEFT OUTER JOIN T_Department ' .
	  'ON T_Activity.dpt_id=T_Department.dpt_id ' .
	  'WHERE (gender = ?)', ['F'], 'join with explicit roles');




  my $view3 = HR->join(qw/Activity employee department/);
  $view3->select("lastname, dpt_name", {gender => 'F'});

  sqlLike('SELECT lastname, dpt_name ' .
	  'FROM T_Activity INNER JOIN T_Employee ' .
	  'ON T_Activity.emp_id=T_Employee.emp_id ' .
	  'INNER JOIN T_Department ' .
	  'ON T_Activity.dpt_id=T_Department.dpt_id ' .