Author: spadkins
Date: Mon Feb 9 11:10:37 2009
New Revision: 12493
Modified:
p5ee/trunk/App-Repository/t/DBI-select.t
Log:
Changes
Modified: p5ee/trunk/App-Repository/t/DBI-select.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select.t Mon Feb 9 11:10:37 2009
@@ -26,6 +26,7 @@
use strict;
my $dbtype = $App::options{dbtype} || "mysql";
+my $birth_dt;
if (!$App::options{dbuser}) {
ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy
to app.conf in 't' directory)");
@@ -190,13 +191,14 @@
is($sql, $expect_sql, "_mk_select_sql(): non-selected param");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "'1962-01-01'" :
"to_date('1962-01-01','YYYY-MM-DD')";
$expect_sql = <<EOF;
select
first_name
from test_person
where first_name = 'stephen'
and age = 37
- and birth_dt = '1962-01-01'
+ and birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
@@ -224,13 +226,14 @@
is($sql, $expect_sql, "_mk_select_sql(): params (bind vars)");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "('1962-01-01','1963-12-31')" :
"(to_date('1962-01-01','YYYY-MM-DD'),to_date('1963-12-31','YYYY-MM-DD'))";
$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')
+ and birth_dt in $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
@@ -241,13 +244,14 @@
is($sql, $expect_sql, "_mk_select_sql(): params auto_in");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "'1962-01-01'" :
"to_date('1962-01-01','YYYY-MM-DD')";
$expect_sql = <<EOF;
select
first_name
from test_person
where first_name = 'stephen'
and age = 37
- and birth_dt = '1962-01-01'
+ and birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.eq", "age.eq", "birth_dt.eq", ],
@@ -258,13 +262,14 @@
is($sql, $expect_sql, "_mk_select_sql(): param.eq");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "'1962-01-01,1963-12-31'" :
"to_date('1962-01-01,1963-12-31','YYYY-MM-DD')";
$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'
+ and birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.eq", "age", "birth_dt.eq", ],
@@ -274,6 +279,7 @@
},["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",
@@ -283,13 +289,14 @@
is($sql, $expect_sql, "_mk_select_sql(): param.eq => in (inferred)");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "'1962-01-01'" :
"to_date('1962-01-01','YYYY-MM-DD')";
$expect_sql = <<EOF;
select
first_name
from test_person
where first_name = 'stephen'
and age = 37
- and birth_dt = '1962-01-01'
+ and birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
@@ -300,13 +307,14 @@
is($sql, $expect_sql, "_mk_select_sql(): param.in => eq");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "('1962-01-01','1963-12-31')" :
"(to_date('1962-01-01','YYYY-MM-DD'),to_date('1963-12-31','YYYY-MM-DD'))";
$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')
+ and birth_dt in $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
@@ -317,13 +325,14 @@
is($sql, $expect_sql, "_mk_select_sql(): param.in");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "'1962-01-01'" :
"to_date('1962-01-01','YYYY-MM-DD')";
$expect_sql = <<EOF;
select
first_name
from test_person
where first_name != 'stephen'
and age != 37
- and birth_dt != '1962-01-01'
+ and birth_dt != $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.ne", "age.ne", "birth_dt.ne", ],
@@ -340,7 +349,7 @@
from test_person
where first_name >= 'stephen'
and age >= 37
- and birth_dt >= '1962-01-01'
+ and birth_dt >= $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.ge", "age.ge", "birth_dt.ge", ],
@@ -357,7 +366,7 @@
from test_person
where first_name > 'stephen'
and age > 37
- and birth_dt > '1962-01-01'
+ and birth_dt > $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.gt", "age.gt", "birth_dt.gt", ],
@@ -374,7 +383,7 @@
from test_person
where first_name <= 'stephen'
and age <= 37
- and birth_dt <= '1962-01-01'
+ and birth_dt <= $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.le", "age.le", "birth_dt.le", ],
@@ -391,7 +400,7 @@
from test_person
where first_name < 'stephen'
and age < 37
- and birth_dt < '1962-01-01'
+ and birth_dt < $birth_dt
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.lt", "age.lt", "birth_dt.lt", ],
@@ -402,13 +411,14 @@
is($sql, $expect_sql, "_mk_select_sql(): param.lt");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "birth_dt" :
"to_char(birth_dt,'YYYY-MM-DD')";
$expect_sql = <<EOF;
select
first_name
from test_person
where first_name like '%s%'
and age like '%3%'
- and birth_dt like '%1962%'
+ and $birth_dt like '%1962%'
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.contains", "age.contains",
"birth_dt.contains", ],
@@ -433,7 +443,7 @@
from test_person
where first_name not like '%s%'
and age not like '%3%'
- and birth_dt not like '%1962%'
+ 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", ],
@@ -443,6 +453,7 @@
},["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",
@@ -458,7 +469,7 @@
from test_person
where first_name like '%s%e_'
and age like '%3'
- and birth_dt like '1962\\_%'
+ and $birth_dt like '1962\\_%'
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.matches", "age.matches", "birth_dt.matches",
],
@@ -468,6 +479,7 @@
},["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?",
@@ -492,7 +504,7 @@
from test_person
where first_name not like '%s%'
and age not like '%3'
- and birth_dt not like '1962%'
+ 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", ],
@@ -657,7 +669,7 @@
first_name
from test_person
where first_name like '%''%'
- and birth_dt like '%\''_'
+ and to_char(birth_dt,'YYYY-MM-DD') like '%\''_'
EOF
#print "[$expect_sql]\n";
$sql = $rep->_mk_select_sql("test_person",{
