view release on metacpan or  search on metacpan

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

$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.le", "age.le", "birth_dt.le", ],
        "first_name.le" => "stephen",
        "age.le" => "37",
        "birth_dt.le" => "1962-01-01",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.le");
&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.lt", "age.lt", "birth_dt.lt", ],
        "first_name.lt" => "stephen",
        "age.lt" => "37",
        "birth_dt.lt" => "1962-01-01",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.lt");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name like '%s%'
  and age like '%3%'
  and birth_dt like '%1962%'
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.contains", "age.contains", "birth_dt.contains", ],
        "first_name.contains" => "s",
        "age.contains" => "3",
        "birth_dt.contains" => "1962",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.contains");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "=~s",
        "age" => "=~3",
        "birth_dt" => "~1962",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.contains (inferred)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name not like '%s%'
  and age not like '%3%'
  and birth_dt not like '%1962%'
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.not_contains", "age.not_contains", "birth_dt.not_contains", ],
        "first_name.not_contains" => "s",
        "age.not_contains" => "3",
        "birth_dt.not_contains" => "1962",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.contains");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "!~s",
        "age" => "!~3",
        "birth_dt" => "!~1962",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.not_contains (inferred)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name like '%s%e_'
  and age like '%3'
  and birth_dt like '1962\\_%'
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.matches", "age.matches", "birth_dt.matches", ],
        "first_name.matches" => "*s*e?",
        "age.matches" => "*3",
        "birth_dt.matches" => "1962_*",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.matches");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "*s*e?",
        "age" => "*3",
        "birth_dt" => "1962_*",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.matches (inferred)");
&check_select($sql,0);

$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name", "age", "birth_dt", ],
        "first_name" => "*s*e?",
        "age" => "*3",
        "birth_dt" => "1962_*",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.matches (inferred)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name
from test_person
where first_name not like '%s%'
  and age not like '%3'
  and birth_dt not like '1962%'
EOF
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.not_matches", "age.not_matches", "birth_dt.not_matches", ],
        "first_name.not_matches" => "*s*",
        "age.not_matches" => "*3",
        "birth_dt.not_matches" => "1962*",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.not_matches");
&check_select($sql,0);

# this doesn't work yet, but that's ok
#$sql = $rep->_mk_select_sql("test_person",{
#        "_order" => [ "first_name", "age", "birth_dt", ],
#        "first_name" => "!*s*",
#        "age" => "!*3",
#        "birth_dt" => "!1962*",
#    },["first_name"]);
#is($sql, $expect_sql, "_mk_select_sql(): param.not_matches (inferred)");
#&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name,
   last_name,
   age
from test_person
where age >= 37
limit 1
EOF
$sql = $rep->_mk_select_sql("test_person",{"age.ge" => 37},["first_name","last_name","age"],{startrow => 1, endrow => 1});
is($sql, $expect_sql, "_mk_select_sql(): cols, endrow");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name,
   last_name,
   city,
   state,
   age
from test_person
order by
   last_name asc,
   city,
   address,
   gender desc,
   first_name
EOF
$sql = $rep->_mk_select_sql("test_person",{},["first_name","last_name","city","state","age"],
    {ordercols=>["last_name","city","address","gender","first_name"],
     directions=>{last_name=>"ASC",city=>"",address=>undef,gender=>"Desc"}});
is($sql, $expect_sql, "_mk_select_sql(): ordercols, directions");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name,
   last_name,
   city,
   state,
   age
from test_person
where age in (14,15,16,17,18)
EOF
$sql = $rep->_mk_select_sql("test_person",
                            {"age.verbatim" => "age in (14,15,16,17,18)"},
                            ["first_name","last_name","city","state","age"]);
is($sql, $expect_sql, "_mk_select_sql(): verbatim");
&check_select($sql,0);

###########################################################################
# NULL CONDITIONS (AND "IN")
###########################################################################

$expect_sql = <<EOF;
select
   gender
from test_person
where age is null
EOF
$sql = $rep->_mk_select_sql("test_person", { age => "NULL", }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is null (by 'NULL')");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person", { age => undef, }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is null (by undef)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   gender
from test_person
where age is not null
EOF
$sql = $rep->_mk_select_sql("test_person", { age => "!NULL", }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by '!NULL')");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person", { "age.ne" => undef, }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by .ne undef)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   gender
from test_person
where first_name is not null
EOF
$sql = $rep->_mk_select_sql("test_person", { first_name => "!NULL", }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by '!NULL')");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person", { "first_name.ne" => undef, }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by .ne undef)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   gender
from test_person
where (first_name not in ('stephen','keith') and first_name is not null)
EOF
$sql = $rep->_mk_select_sql("test_person", { first_name => "!stephen,keith,NULL", }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): not in and not null (by '!stephen,keith,NULL')");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person", { "first_name.not_in" => "stephen,keith,NULL", }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by .not_in 'stephen,keith,NULL')");
&check_select($sql,0);

$expect_sql = <<'EOF';
select
   first_name
from test_person
where first_name like '%\'%'
  and birth_dt like '%\\\'_'
EOF
#print "[$expect_sql]\n";
$sql = $rep->_mk_select_sql("test_person",{
        "_order" => [ "first_name.contains", "birth_dt.matches", ],
        "first_name.contains" => "'",
        "birth_dt.matches" => "*\\'?",
    },["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.contains (proper quoting of ' and \\' required)");
&check_select($sql,0);

exit(0);

$expect_sql = <<EOF;
select
   gender
from test_person
where first_name is not null
EOF
$sql = $rep->_mk_select_sql("test_person", { age => "!NULL", }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by '!NULL')");
&check_select($sql,0);
$sql = $rep->_mk_select_sql("test_person", { "age.ne" => undef, }, ["gender"]);
is($sql, $expect_sql, "_mk_select_sql(): is not null (by .ne undef)");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name,
   last_name
from test_person
where (age in (14,15,16) or age is null)
EOF
$sql = $rep->_mk_select_sql("test_person",
                            {"age" => "14,15,16,NULL"},
                            ["first_name","last_name"]);
is($sql, $expect_sql, "_mk_select_sql(): ,NULL");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   first_name,
   last_name
from test_person
where (age in (14,15,16) or age is null)
EOF
$sql = $rep->_mk_select_sql("test_person",
                            {"age" => "NULL,14,15,16"},
                            ["first_name","last_name"]);
is($sql, $expect_sql, "_mk_select_sql(): NULL,");
&check_select($sql,0);

$expect_sql = <<EOF;

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

                            ["first_name","state","age"]);
is($sql, $expect_sql, "_mk_select_sql(): OR conditions with [] value");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   t1.first_name,
   t1.state,
   t1.age
from test_person
where age > 14
  and first_name like '%A%'
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): square bracket [] params",
    "test_person",
    [age => ">14", first_name => "*A*"],
    ["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(): square bracket [] params");
&check_select($sql,0);

$expect_sql = <<EOF;
select
   t1.first_name,
   t1.state,
   t1.age
from test_person
where age > 14
   or not (first_name like '%A%')
   or (state in ('GA','CA') and
       age <= 2)
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): ordercols, directions",
    "test_person",
    ["_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'