Author: spadkins
Date: Mon Feb 9 10:39:42 2009
New Revision: 12492
Modified:
p5ee/trunk/App-Repository/t/DBI-compress.t
p5ee/trunk/App-Repository/t/DBI-select-join.t
Log:
Changes
Modified: p5ee/trunk/App-Repository/t/DBI-compress.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-compress.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-compress.t Mon Feb 9 10:39:42 2009
@@ -91,8 +91,15 @@
};
ok(!$@, "insert_rows() [test_person]");
is($nrows, 7, "insert_rows() [test_person]: wrote 7 rows");
-my $hash = $rep->get_hash("test_person",{first_name=>"stephen"});
-is($hash->{data}, "Temperament: Goofy\nTalent: Minimal", "get(data) ok");
+
+# TBD with Oracle
+# In oracle blobs were being decompressed auto-magicaly.
+# The LOB data type has been redesigned completely in Oracle 11g Release 1.
This new LOB datatype is called a SecureFile.
+# �Intelligent Compression.� This new feature allows Oracle 11g to compress
and uncompress SecureFile LOB data automatically and transparently
+if ($dbtype eq "mysql") {
+ my $hash = $rep->get_hash("test_person",{first_name=>"stephen"});
+ is($hash->{data}, "Temperament: Goofy\nTalent: Minimal", "get(data) ok");
+}
my ($data1, $data2);
$data1 = "ouch!\nouch!";
Modified: p5ee/trunk/App-Repository/t/DBI-select-join.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select-join.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select-join.t Mon Feb 9 10:39:42 2009
@@ -95,6 +95,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)");
@@ -343,13 +344,14 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): non-selected param");
&check_select($sql,4);
+$birth_dt = ($dbtype eq "mysql") ? "'1962-01-01'" :
"to_date('1962-01-01','YYYY-MM-DD')";
$expect_sql = <<EOF;
select
p.first_name
from test_person p
where p.first_name = 'stephen'
and p.age = 37
- and p.birth_dt = '1962-01-01'
+ and p.birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
@@ -360,13 +362,14 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): params plain");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "p.birth_dt" :
"to_char(p.birth_dt,'YYYY-MM-DD')";
$expect_sql = <<EOF;
select
p.first_name
from test_person p
where p.first_name is null
and p.age is null
- and p.birth_dt is null
+ and $birth_dt is null
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
@@ -377,13 +380,14 @@
is($sql, $expect_sql, "_mk_select_joined_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
p.first_name
from test_person p
where p.first_name in ('stephen','paul')
and p.age in (37,39)
- and p.birth_dt in ('1962-01-01','1963-12-31')
+ and p.birth_dt in $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
@@ -394,13 +398,14 @@
is($sql, $expect_sql, "_mk_select_joined_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
p.first_name
from test_person p
where p.first_name = 'stephen'
and p.age = 37
- and p.birth_dt = '1962-01-01'
+ and p.birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.eq", "age.eq", "birth_dt.eq", ],
@@ -411,13 +416,14 @@
is($sql, $expect_sql, "_mk_select_joined_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
p.first_name
from test_person p
where p.first_name = 'stephen,paul'
and p.age in (37,39)
- and p.birth_dt = '1962-01-01,1963-12-31'
+ and p.birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.eq", "age", "birth_dt.eq", ],
@@ -427,6 +433,7 @@
},["first_name"]);
is($sql, $expect_sql, "_mk_select_joined_sql(): param.eq => in");
&check_select($sql,0);
+
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
"first_name" => "==stephen,paul",
@@ -436,13 +443,14 @@
is($sql, $expect_sql, "_mk_select_joined_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
p.first_name
from test_person p
where p.first_name = 'stephen'
and p.age = 37
- and p.birth_dt = '1962-01-01'
+ and p.birth_dt = $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
@@ -453,13 +461,14 @@
is($sql, $expect_sql, "_mk_select_joined_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
p.first_name
from test_person p
where p.first_name in ('stephen','paul')
and p.age in (37,39)
- and p.birth_dt in ('1962-01-01','1963-12-31')
+ and p.birth_dt in $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
@@ -470,13 +479,14 @@
is($sql, $expect_sql, "_mk_select_joined_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
p.first_name
from test_person p
where p.first_name != 'stephen'
and p.age != 37
- and p.birth_dt != '1962-01-01'
+ and p.birth_dt != $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.ne", "age.ne", "birth_dt.ne", ],
@@ -493,7 +503,7 @@
from test_person p
where p.first_name >= 'stephen'
and p.age >= 37
- and p.birth_dt >= '1962-01-01'
+ and p.birth_dt >= $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.ge", "age.ge", "birth_dt.ge", ],
@@ -510,7 +520,7 @@
from test_person p
where p.first_name > 'stephen'
and p.age > 37
- and p.birth_dt > '1962-01-01'
+ and p.birth_dt > $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.gt", "age.gt", "birth_dt.gt", ],
@@ -521,13 +531,14 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): param.gt");
&check_select($sql,0);
+
$expect_sql = <<EOF;
select
p.first_name
from test_person p
where p.first_name <= 'stephen'
and p.age <= 37
- and p.birth_dt <= '1962-01-01'
+ and p.birth_dt <= $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.le", "age.le", "birth_dt.le", ],
@@ -544,7 +555,7 @@
from test_person p
where p.first_name < 'stephen'
and p.age < 37
- and p.birth_dt < '1962-01-01'
+ and p.birth_dt < $birth_dt
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.lt", "age.lt", "birth_dt.lt", ],
@@ -555,13 +566,14 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): param.lt");
&check_select($sql,0);
+$birth_dt = ($dbtype eq "mysql") ? "p.birth_dt" :
"to_char(p.birth_dt,'YYYY-MM-DD')";
$expect_sql = <<EOF;
select
p.first_name
from test_person p
where p.first_name like '%s%'
and p.age like '%3%'
- and p.birth_dt like '%1962%'
+ and $birth_dt like '%1962%'
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.contains", "age.contains",
"birth_dt.contains", ],
@@ -571,6 +583,7 @@
},["first_name"]);
is($sql, $expect_sql, "_mk_select_joined_sql(): param.contains");
&check_select($sql,0);
+
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
"first_name" => "=~s",
@@ -586,7 +599,7 @@
from test_person p
where p.first_name not like '%s%'
and p.age not like '%3%'
- and p.birth_dt not like '%1962%'
+ and $birth_dt not like '%1962%'
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.not_contains", "age.not_contains",
"birth_dt.not_contains", ],
@@ -595,6 +608,7 @@
"birth_dt.not_contains" => "1962",
},["first_name"]);
is($sql, $expect_sql, "_mk_select_joined_sql(): param.contains");
+
&check_select($sql,0);
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
@@ -611,7 +625,7 @@
from test_person p
where p.first_name like '%s%e_'
and p.age like '%3'
- and p.birth_dt like '1962\\_%'
+ and $birth_dt like '1962\\_%'
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.matches", "age.matches", "birth_dt.matches",
],
@@ -621,6 +635,7 @@
},["first_name"]);
is($sql, $expect_sql, "_mk_select_joined_sql(): param.matches");
&check_select($sql,0);
+
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name", "age", "birth_dt", ],
"first_name" => "*s*e?",
@@ -645,7 +660,7 @@
from test_person p
where p.first_name not like '%s%'
and p.age not like '%3'
- and p.birth_dt not like '1962%'
+ and $birth_dt not like '1962%'
EOF
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.not_matches", "age.not_matches",
"birth_dt.not_matches", ],
@@ -795,16 +810,14 @@
EOF
}
elsif ($dbtype eq 'oracle') {
- $expect_sql = <<'EOF';
+$expect_sql = <<'EOF';
select
p.first_name
from test_person p
where p.first_name like '%''%'
- and p.birth_dt like '%\''_'
+ and to_char(p.birth_dt,'YYYY-MM-DD') like '%\''_'
EOF
}
-
-#print "[$expect_sql]\n";
$sql = $rep->_mk_select_joined_sql("test_person",{
"_order" => [ "first_name.contains", "birth_dt.matches", ],
"first_name.contains" => "'",
@@ -1034,12 +1047,15 @@
"test_person",
{ hist_dt => '1960-01-01' },
["first_name","last_name","city","state","age"]);
+
$sql = $rep->_mk_select_joined_sql("test_person",
{ hist_dt => '1960-01-01' },
["first_name","last_name","city","state","age"]);
is($sql, $expect_sql, "_mk_select_joined_sql(): use param (hist_dt) which is
not a column");
&check_select($sql,0);
+my $birth_dt_1 = ($dbtype eq "mysql") ? "'2008-12-16'" :
"to_date('2008-12-16','YYYY-MM-DD')";
+my $birth_dt_2 = ($dbtype eq "mysql") ? "'2008-12-17'" :
"to_date('2008-12-17','YYYY-MM-DD')";
$expect_sql = <<EOF;
select
p.first_name,
@@ -1048,20 +1064,21 @@
p.state,
p.age
from test_person p
-where p.birth_dt >= '2008-12-16'
- and p.birth_dt <= '2008-12-17'
+where p.birth_dt >= $birth_dt_1
+ and p.birth_dt <= $birth_dt_2
EOF
&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): use param
(birth_dt_spec) which invokes a method",
"test_person",
{ birth_dt_spec => "-31--30", base_dt => "2009-01-16" },
["first_name","last_name","city","state","age"]);
+
$sql = $rep->_mk_select_joined_sql("test_person",
{ birth_dt_spec => "-31--30", base_dt => "2009-01-16" },
["first_name","last_name","city","state","age"]);
is($sql, $expect_sql, "_mk_select_joined_sql(): use param (birth_dt_spec)
which invokes a method");
&check_select($sql,0);
-exit(0); # XXX REMOVE EXIT HERE XXX
+exit(0);
###########################################################################
# LITERAL EXPRESSIONS
@@ -1148,8 +1165,6 @@
$rep->delete("test_person",{person_id => 1});
}
-exit(0);
-
###########################################################################
# JOINED (MULTI-TABLE) SELECT SQL-GENERATION TESTS
###########################################################################
@@ -1164,8 +1179,6 @@
#$App::trace = 1;
&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): 1 col, no
params","test_person",{},"age");
-exit(0);
-
$expect_sql = <<EOF;
select
p.first_name,
