view release on metacpan or  search on metacpan

t/DBI-select.t  view on Meta::CPAN

    my $expected_rows = shift;
    my $msg = shift;
    my $sql = $rep->_mk_select_joined_sql(@_);
    is($sql,$expected_sql,"$msg - sql");

    my ($rows, $reprows);
    eval {
        $rows = $dbh->selectall_arrayref($sql);
    };
    is($@,"","$msg - sql ok");

    if (defined $expected_rows) {
        is(($#$rows + 1), $expected_rows, "$msg - num rows $expected_rows");
    }

    eval {
        $reprows = $rep->get_rows(@_);
    };
    is($@,"","$msg - get_rows() ok");
    is_deeply($reprows,$rows,"$msg - data same");
    return($sql);
}

my ($sql, $expect_sql);
###########################################################################
# RAW (SINGLE-TABLE) SELECT SQL-GENERATION TESTS
###########################################################################

$expect_sql = <<EOF;
select
   age
from test_person
EOF
$sql = $rep->_mk_select_sql("test_person",{},"age");
is($sql, $expect_sql, "_mk_select_sql(): 1 col, no params");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person",{},["age"]);
is($sql, $expect_sql, "_mk_select_sql(): 1 col as array, no params");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   age
from test_person
where person_id = 1
EOF
$sql = $rep->_mk_select_sql("test_person",1,"age");
is($sql, $expect_sql, "_mk_select_sql(): key");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person",1,["age"]);
is($sql, $expect_sql, "_mk_select_sql(): key (again)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   age
from test_person
where person_id is null
EOF
$sql = $rep->_mk_select_sql("test_person",undef,"age");
is($sql, $expect_sql, "_mk_select_sql(): by key (bind vars)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   age
from test_person
where age = 37
EOF
$sql = $rep->_mk_select_sql("test_person",{age => 37},"age");
is($sql, $expect_sql, "_mk_select_sql(): param");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   age
from test_person
where gender = 'M'
EOF
$sql = $rep->_mk_select_sql("test_person",{gender => "M"},"age");
is($sql, $expect_sql, "_mk_select_sql(): non-selected param");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name = 'stephen'
  and age = 37
  and birth_dt = '1962-01-01'
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "stephen",
        "age" => "37",
        "birth_dt" => "1962-01-01",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): params plain");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name is null
  and age is null
  and birth_dt is null
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => undef,
        "age" => undef,
        "birth_dt" => undef,
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): params (bind vars)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name in ('stephen','paul')
  and age in (37,39)
  and birth_dt in ('1962-01-01','1963-12-31')
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "stephen,paul",
        "age" => "37,39",
        "birth_dt" => "1962-01-01,1963-12-31",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): params auto_in");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name = 'stephen'
  and age = 37
  and birth_dt = '1962-01-01'
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.eq", "age.eq", "birth_dt.eq", ],
        "first_name.eq" => "stephen",
        "age.eq" => "37",
        "birth_dt.eq" => "1962-01-01",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.eq");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name = 'stephen,paul'
  and age in (37,39)
  and birth_dt = '1962-01-01,1963-12-31'
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.eq", "age", "birth_dt.eq", ],
        "first_name.eq" => "stephen,paul",
        "age" => "37,39",
        "birth_dt.eq" => "1962-01-01,1963-12-31",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.eq => in");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "==stephen,paul",
        "age" => "=37,39",
        "birth_dt" => "==1962-01-01,1963-12-31",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.eq => in (inferred)");
&check_select($sql,0);

t/DBI-select.t  view on Meta::CPAN

    ["_or", age => ">14",
      ["_not", first_name => "*A*"],
      ["_and", state => "GA,CA", "age.le" => 2]],
    ["first_name","state","age"]);
$sql = $rep->_mk_select_sql("test_person",
                            [age => ">14", first_name => "*A*"],
                            ["first_name","state","age"]);
is($sql, $expect_sql, "_mk_select_sql(): verbatim");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   t1.first_name,
   t1.state,
   t1.age
from test_person
where not (not(age > 14)
  and not (first_name like '%A%')
  and not (state in ('GA','CA') and
           age <= 2))
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): ordercols, directions",
    "test_person",
    ["_not", age => ">14",
      ["_not_or", first_name => "*A*"],
      ["_not_and", state => ["GA","CA"], "age.le" => 2]],
    ["first_name","state","age"]);
$sql = $rep->_mk_select_sql("test_person",
                            [age => ">14", first_name => "*A*"],
                            ["first_name","state","age"]);
is($sql, $expect_sql, "_mk_select_sql(): verbatim");
&check_select($sql,0);

&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): 1 col as array, no params","test_person",{},["age"]);

$expect_sql = <<EOF;
select
   t1.age cn13,
   t1.person_id cn0
from
   test_person t1
EOF
&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): auto_extend","test_person",{},"age",{auto_extend=>1});

$expect_sql = <<EOF;
select
   t1.age cn13
from
   test_person t1
where t1.person_id = 1
EOF
&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): key","test_person",1,"age");

#$expect_sql = <<EOF;
#select
#   t1.age cn13
#from
#   test_person t1
#where t1.person_id is null
#EOF
#&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): by key (bind vars)","test_person",undef,"age");

$expect_sql = <<EOF;
select
   t1.age cn13
from
   test_person t1
where t1.age = 37
EOF
&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): param","test_person",{age => 37},"age");

$expect_sql = <<EOF;
select
   t1.age cn13
from
   test_person t1
where t1.gender = 'M'
EOF
&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): non-selected param","test_person",{gender => "M"},"age");

$expect_sql = <<EOF;
select
   t1.first_name cn1
from
   test_person t1
where t1.first_name = 'stephen'
  and t1.age = 37
  and t1.birth_dt = '1962-01-01'
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): params plain",
    "test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "stephen",
        "age" => "37",
        "birth_dt" => "1962-01-01",
    },["first_name"]);

$expect_sql = <<EOF;
select
   t1.first_name cn1
from
   test_person t1
where t1.first_name is null
  and t1.age is null
  and t1.birth_dt is null
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): params (bind vars)",
    "test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => undef,
        "age" => undef,
        "birth_dt" => undef,
    },["first_name"]);

$expect_sql = <<EOF;
select
   t1.first_name cn1
from
   test_person t1
where t1.first_name in ('stephen','paul')
  and t1.age in (37,39)
  and t1.birth_dt in ('1962-01-01','1963-12-31')
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): params auto_in",
    "test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "stephen,paul",
        "age" => "37,39",
        "birth_dt" => "1962-01-01,1963-12-31",
    },["first_name"]);

$expect_sql = <<EOF;
select
   t1.first_name cn1
from
   test_person t1
where t1.first_name = 'stephen'
  and t1.age = 37
  and t1.birth_dt = '1962-01-01'
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.eq",
    "test_person",{
        "_order" => [ "first_name.eq", "age.eq", "birth_dt.eq", ],
        "first_name.eq" => "stephen",
        "age.eq" => "37",
        "birth_dt.eq" => "1962-01-01",
    },["first_name"]);

$expect_sql = <<EOF;
select
   t1.first_name cn1
from
   test_person t1
where t1.first_name in ('stephen','paul')
  and t1.age in (37,39)
  and t1.birth_dt in ('1962-01-01','1963-12-31')
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.eq => in",
    "test_person",{
        "_order" => [ "first_name.eq", "age.eq", "birth_dt.eq", ],
        "first_name.eq" => "stephen,paul",
        "age.eq" => "37,39",
        "birth_dt.eq" => "1962-01-01,1963-12-31",
    },["first_name"]);

$expect_sql = <<EOF;
select