view release on metacpan or  search on metacpan

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

$emp = HR::Employee->bless_from_DB({emp_id => 999, spouse_id => 888});
my $emp_spouse = $emp->spouse;
sqlLike('SELECT * ' .
        'FROM T_Employee ' .
        "WHERE ( emp_id = ? )", [888], 'spouse self-ref assoc.');


# meta-information about paths
my $emp_meta = HR->table('Employee')->metadm;
my %paths = $emp_meta->path;
while (my ($path_name, $path)= each %paths) {
  my $opp     = $path->opposite or next; # some paths like 'spouse' have no opp
  my $opp_opp = $opp->opposite;
  isa_ok($opp, 'DBIx::DataModel::Meta::Path', "opposite is a Path");
  isnt($path, $opp,                           "opposite is different");
  is($path, $opp_opp,                         "opposite of opposite")
}


#----------------------------------------------------------------------
# test Statement class
#----------------------------------------------------------------------


# stepwise combination of where criteria
my $statement = HR::Employee->activities(-where => {foo => [3, 4]});
$act = $statement->bind($emp)
                 ->select(-where => {foo => [4, 5]});
sqlLike('SELECT * FROM T_Activity '
        .  'WHERE ( emp_id = ? AND ( (     foo = ? OR foo = ? ) '
        .                           'AND ( foo = ? OR foo = ? )))',
        [999, 3, 4, 4, 5], "combined where");

# other combination involving nested arrayrefs
$statement = HR->table('Employee')->activities(-where => [foo => "bar", bar => "foo"]);
$act = $statement->bind($emp)
                 ->select(-where => [foobar => 123, barfoo => 456]);

sqlLike('SELECT * FROM T_Activity '
        .  'WHERE ( (     (foo = ?  OR bar = ?) '
        .            'AND (foobar = ? OR barfoo = ?)'
        .          ') AND emp_id = ? )',
        [qw/bar foo 123 456 999/], "combined where, arrayrefs");



# stepwise statement prepare/execute
$statement = HR::Employee->join(qw/activities department/);
$statement->refine(-where => {gender => 'F'});
$statement->refine(-where => {gender => {'!=' => 'M'}});
$statement->prepare;
my $row = $statement->execute($emp)->next;
sqlLike('SELECT * ' .
        'FROM T_Activity ' .
        'INNER JOIN T_Department ' .
        'ON T_Activity.dpt_id=T_Department.dpt_id ' .
        'WHERE (emp_id = ? AND gender = ? AND gender != ?)', [999, 'F', 'M'],
        'statement prepare/execute');


# -pre_exec / -post_exec callbacks
my %check_callbacks;
HR::Employee->select(-where => {foo=>'bar'},
      	   -pre_exec => sub {$check_callbacks{pre} = "was called"},
      	   -post_exec => sub {$check_callbacks{post} = "was called"},);
is_deeply(\%check_callbacks, {pre =>"was called", 
      			post => "was called" }, 'select, pre/post callbacks');

%check_callbacks = ();
HR::Employee->fetch(1234, {-pre_exec => sub {$check_callbacks{pre} = "was called"},
      		 -post_exec => sub {$check_callbacks{post} = "was called"}});
is_deeply(\%check_callbacks, {pre =>"was called", 
      			post => "was called" }, 'fetch, pre/post callbacks');


# nb_fetched_rows
HR->dbh->{mock_add_resultset} = [[qw/foo bar/], ([1, 2]) x 23];
$statement = HR->table('Employee')->select(-result_as  => 'statement');
$statement->all; # throw away the result -- this call is just to make sure the statement is finished
is $statement->nb_fetched_rows, 23, "nb_fetched_rows";


# page boundaries
HR->dbh->{mock_add_resultset} = [[qw/foo bar/], ([1, 2]) x 5];
$statement = HR->table('Employee')->select(
  -page_size  => 10,
  -page_index => 3,
  -result_as  => 'statement',
 );
$statement->all; # throw away the result -- this call is just to make sure the statement is finished
is_deeply [$statement->page_boundaries], [21, 25], "page boundaries";


# -union
my $stmt = HR->table('Employee')->select(
  -columns   => [qw/emp_id firstname lastname/],
  -where     => {d_birth => '01.01.1950'},
  -union     => [-where  => {d_spouse => '01.01.1950'}],
  -result_as => 'statement',
 );

my $rows = $stmt->all;
sqlLike(<<__EOSQL__, [qw/01.01.1950 01.01.1950/], "sql union");
  SELECT emp_id, firstname, lastname FROM T_Employee WHERE ( d_birth = ? ) 
  UNION 
  SELECT emp_id, firstname, lastname FROM T_Employee WHERE ( d_spouse = ? )
__EOSQL__


my $n = $stmt->row_count;
sqlLike(<<__EOSQL__, [qw/01.01.1950 01.01.1950/], "sql count from union");
  SELECT COUNT(*) FROM (
    SELECT emp_id, firstname, lastname FROM T_Employee WHERE ( d_birth = ? )
    UNION 
    SELECT emp_id, firstname, lastname FROM T_Employee WHERE ( d_spouse = ? )
  ) AS count_wrapper
__EOSQL__



# -union_all
HR->table('Employee')->select(
  -columns   => [qw/emp_id firstname lastname/],
  -where     => {d_birth => {'>=' => '01.01.1950'}},
  -union_all => [-where  => {d_spouse => {'>=' => '02.02.1950'}}],
 );
sqlLike(<<__EOSQL__, [qw/01.01.1950 02.02.1950/], "sql union all");
  SELECT emp_id, firstname, lastname FROM T_Employee WHERE ( d_birth >= ? ) 
  UNION ALL
  SELECT emp_id, firstname, lastname FROM T_Employee WHERE ( d_spouse >= ? )
__EOSQL__