[svn:p5ee] r12061 - p5ee/trunk/App-Repository/t
[email protected] Mon, 10 Nov 2008 15:23:53 -0800 (PST)
| Newsgroups | perl.cvs.p5ee |
|---|---|
| Message-ID | <[email protected]> |
Author: spadkins
Date: Mon Nov 10 15:23:53 2008
New Revision: 12061
Added:
p5ee/trunk/App-Repository/t/DBI-import-ora.t (contents, props changed)
p5ee/trunk/App-Repository/t/DBI-insert-ora.t (contents, props changed)
p5ee/trunk/App-Repository/t/DBI-select-join.t (contents, props changed)
p5ee/trunk/App-Repository/t/DBI-select-ora.t (contents, props changed)
p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm (contents, props changed)
p5ee/trunk/App-Repository/t/SO-SQLTranslator.t (contents, props changed)
Log:
Oracle support under development
Added: p5ee/trunk/App-Repository/t/DBI-import-ora.t
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/t/DBI-import-ora.t Mon Nov 10 15:23:53 2008
@@ -0,0 +1,85 @@
+#!/usr/local/bin/perl -w
+
+use App::Options (
+ options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
+ option => {
+ dbclass => { default => "App::Repository::MySQL", },
+ dbdriver => { default => "mysql", },
+ dbhost => { default => "localhost", },
+ dbname => { default => "test", },
+ dbuser => { default => "", },
+ dbpass => { default => "", },
+ },
+);
+
+use Test::More qw(no_plan);
+use lib "../App-Context/lib";
+use lib "../../App-Context/lib";
+use lib "lib";
+use lib "../lib";
+use lib ".";
+use lib "t";
+
+use_ok("App");
+use_ok("App::Repository");
+use RepositoryTestUtils qw(create_table_test_person drop_table_test_person);
+use strict;
+
+if (!$App::options{dbuser}) {
+ ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
+ exit(0);
+}
+
+my $context = App->context(
+ conf_file => "",
+ conf => {
+ Repository => {
+ default => {
+ class => $App::options{dbclass},
+ dbdriver => $App::options{dbdriver},
+ dbhost => $App::options{dbhost},
+ dbname => $App::options{dbname},
+ dbuser => $App::options{dbuser},
+ dbpass => $App::options{dbpass},
+ table => {
+ test_person => {
+ primary_key => ["person_id"],
+ },
+ },
+ },
+ },
+ },
+ debug_sql => $App::options{debug_sql},
+ trace => $App::options{trace},
+);
+
+# my $options= $context->{options};
+# print "OPTIONS: {", join("|", %$options), "}\n";
+
+my $rep = $context->repository();
+&drop_table_test_person($rep);
+&create_table_test_person($rep);
+
+my $t_dir = "t";
+$t_dir = "." if (! -d $t_dir);
+
+{
+ $rep->_load_rep_metadata();
+
+ is($rep->insert_rows("test_person", ["age","first_name","gender","state"],
+ [[39,"stephen", "M","GA"],
+ [37,"susan", "F","GA"]]),2,
+ "insert rows (2 rows, primary key included)");
+ is($rep->get("test_person",1,"first_name"), "stephen", "1st row got in [stephen]");
+ is($rep->get("test_person",2,"first_name"), "susan", "2nd row got in [susan]");
+
+ is($rep->import_rows("test_person", ["age","first_name","gender","state"],
+ "$t_dir/files/DBI-import.01.dat", {field_sep => "|", import_method => "insert"}),
+ 120,
+ "import from file [files/DBI-import.01.dat]");
+ is($rep->get("test_person",3,"first_name"), "mike", "3rd row got in [mike]");
+ is($rep->get("test_person",4,"first_name"), "mary", "4th row got in [mary]");
+ is($rep->get("test_person",5,"first_name"), "maxwell", "5th row got in [maxwell]");
+ is($rep->get("test_person",6,"first_name"), "myrtle", "6th row got in [myrtle]");
+}
+
Added: p5ee/trunk/App-Repository/t/DBI-insert-ora.t
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/t/DBI-insert-ora.t Mon Nov 10 15:23:53 2008
@@ -0,0 +1,242 @@
+#!/usr/local/bin/perl -w
+
+use App::Options (
+ options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
+ option => {
+ dbclass => { default => "App::Repository::MySQL", },
+ dbdriver => { default => "mysql", },
+ dbhost => { default => "localhost", },
+ dbname => { default => "test", },
+ dbuser => { default => "", },
+ dbpass => { default => "", },
+ },
+);
+
+use Test::More qw(no_plan);
+use lib "../App-Context/lib";
+use lib "../../App-Context/lib";
+use lib "lib";
+use lib "../lib";
+use lib ".";
+use lib "t";
+
+use_ok("App");
+use_ok("App::Repository");
+use RepositoryTestUtils qw(create_table_test_person drop_table_test_person populate_table_test_person);
+use strict;
+
+if (!$App::options{dbuser}) {
+ ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
+ exit(0);
+}
+
+my $context = App->context(
+ conf_file => "",
+ conf => {
+ Repository => {
+ default => {
+ class => $App::options{dbclass},
+ dbdriver => $App::options{dbdriver},
+ dbhost => $App::options{dbhost},
+ dbname => $App::options{dbname},
+ dbuser => $App::options{dbuser},
+ dbpass => $App::options{dbpass},
+ table => {
+ test_person => {
+ primary_key => ["person_id"],
+ },
+ },
+ },
+ },
+ },
+ debug_sql => $App::options{debug_sql},
+ trace => $App::options{trace},
+);
+
+my $db = $context->repository();
+&drop_table_test_person($db);
+&create_table_test_person($db);
+
+{
+ $db->_load_rep_metadata();
+
+ my $dbtype = $App::options{dbtype};
+ if ($dbtype ne "oracle") {
+ ok(1, "These tests are only for Oracle");
+ }
+ else {
+ ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
+ [39,"stephen", "M","GA"]),
+ "insert row (primary key included)");
+ ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
+ [37,"susan", "F","GA"]),
+ "insert row (primary key excluded, auto_increment)");
+ ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
+ [6,"maryalice","F","GA"]),
+ "insert row (primary key included, null)");
+ ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
+ [3,"paul", "M","GA"]),
+ "insert row (primary key included, 0)");
+ ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
+ [1,"christine","F","GA"]),
+ "insert again");
+ ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
+ [45,"tim", "M","GA"]),
+ "insert again");
+ ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
+ [39,"keith", "M","GA"]),
+ "insert again");
+
+ ok($db->insert("test_person", {
+ # person_id => 8,
+ age => 35,
+ first_name => "alex",
+ gender => "M",
+ state => "GA",
+ }),
+ "insert hash");
+ eval {
+ $db->insert_row("test_person", {
+ person_id => 8,
+ age => 35,
+ first_name => "alex",
+ gender => "M",
+ state => "GA",
+ });
+ };
+ ok($@, "insert dup hash fails");
+ ok($db->insert("test_person", undef, {
+ # person_id => 9,
+ age => 35,
+ first_name => "alex",
+ gender => "M",
+ state => "GA",
+ }),
+ "insert hash in 2nd pos");
+ ok($db->insert("test_person", ["age","first_name","gender","state"], {
+ # person_id => 10,
+ age => 35,
+ first_name => "alex",
+ gender => "M",
+ state => "GA",
+ }),
+ "insert hash in 2nd pos w/ col spec");
+ eval {
+ $db->insert_row("test_person", undef, {
+ person_id => 10,
+ age => 35,
+ first_name => "alex",
+ gender => "M",
+ state => "GA",
+ });
+ };
+ ok($@, "insert dup hash in 2nd pos fails");
+
+ ok($db->insert("test_person", undef, {
+ # person_id => 11,
+ age => 999,
+ first_name => '%@$\\\'',
+ gender => "M",
+ state => "GA",
+ }),
+ "insert \\ and ' and \\' seems to work");
+ is($db->get("test_person",11,"first_name"),'%@$\\\'', "yep. putting weird chars in first_name worked.");
+
+ my $new_hashes =
+ [{ age=>39, first_name=>"stephen", gender=>"M", state=>"GA", foo=>"bar"},
+ { age=>37, first_name=>"susan", gender=>"F", state=>"GA", foo=>"bar"},
+ { age=>6, first_name=>"maryalice", gender=>"F", state=>"GA", foo=>"bar"},
+ { age=>3, first_name=>"paul", gender=>"M", state=>"GA", foo=>"bar"},
+ { age=>1, first_name=>"christine", gender=>"F", state=>"GA", foo=>"bar"},
+ { age=>45, first_name=>"tim", gender=>"M", state=>"GA", foo=>"bar"},
+ { age=>39, first_name=>"keith", gender=>"M", state=>"GA", foo=>"bar"},];
+
+ my $new_rows =
+ [[39,"stephen", "M","GA"],
+ [37,"susan", "F","GA"],
+ [6,"maryalice", "F","GA"],
+ [3,"paul", "M","GA"],
+ [1,"christine", "F","GA"],
+ [45,"tim", "M","GA"],
+ [39,"keith", "M","GA"],];
+
+ my $dup_rows =
+ [[1, 39,"stephen", "M","GA"],
+ [2, 37,"susan", "F","GA"],
+ [3, 6,"maryalice", "F","GA"],
+ [4, 3,"paul", "M","GA"],
+ [5, 1,"christine", "F","GA"],
+ [6, 45,"tim", "M","GA"],
+ [7, 39,"keith", "M","GA"],];
+
+ my ($expect_sql, $sql);
+# $expect_sql = <<EOF;
+#insert into test_person
+# (age, first_name, gender, state)
+#values
+# (39, 'stephen', 'M', 'GA'),
+# (37, 'susan', 'F', 'GA'),
+# (6, 'maryalice', 'F', 'GA'),
+# (3, 'paul', 'M', 'GA'),
+# (1, 'christine', 'F', 'GA'),
+# (45, 'tim', 'M', 'GA'),
+# (39, 'keith', 'M', 'GA')
+#EOF
+# $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_rows);
+# is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert");
+# $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_hashes);
+# is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert (from hashes)");
+
+# $expect_sql = <<EOF;
+#replace into test_person
+# (age, first_name, gender, state)
+#values
+# (39, 'stephen', 'M', 'GA'),
+# (37, 'susan', 'F', 'GA'),
+# (6, 'maryalice', 'F', 'GA'),
+# (3, 'paul', 'M', 'GA'),
+# (1, 'christine', 'F', 'GA'),
+# (45, 'tim', 'M', 'GA'),
+# (39, 'keith', 'M', 'GA')
+#EOF
+# $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_rows, { replace => 1 });
+# is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk replace");
+
+# $expect_sql = <<EOF;
+#insert into test_person
+# (person_id, age, first_name, gender, state)
+#values
+# (1, 39, 'stephen', 'M', 'GA'),
+# (2, 37, 'susan', 'F', 'GA'),
+# (3, 6, 'maryalice', 'F', 'GA'),
+# (4, 3, 'paul', 'M', 'GA'),
+# (5, 1, 'christine', 'F', 'GA'),
+# (6, 45, 'tim', 'M', 'GA'),
+# (7, 39, 'keith', 'M', 'GA')
+#on duplicate key update
+# person_id = values(person_id),
+# age = values(age),
+# first_name = values(first_name),
+# gender = values(gender),
+# state = values(state)
+#EOF
+# $sql = $db->_mk_insert_rows_sql("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1 });
+# is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert/update");
+
+ #######################################
+ my ($nrows);
+ $nrows = $db->insert_rows("test_person", ["age","first_name","gender","state"], $new_rows);
+ is($nrows, 7, "insert_rows(): 7 rows, bulk insert");
+ #$nrows = $db->insert_rows("test_person", ["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1 });
+ #is($nrows, 7, "insert_rows(): 7 rows, bulk replace");
+ #$nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1 });
+ #is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update");
+ #$nrows = $db->insert_rows("test_person", ["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1, maxrows => 4 });
+ #is($nrows, 7, "insert_rows(): 7 rows, bulk replace (4 at a time)");
+ #$nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1, maxrows => 4 });
+ #is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update (4 at a time)");
+ }
+}
+
+exit 0;
+
Added: p5ee/trunk/App-Repository/t/DBI-select-join.t
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/t/DBI-select-join.t Mon Nov 10 15:23:53 2008
@@ -0,0 +1,1409 @@
+#!/usr/local/bin/perl -w
+
+use App::Options (
+ options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
+ option => {
+ dbclass => { default => "App::Repository::MySQL", },
+ dbdriver => { default => "mysql", },
+ dbhost => { default => "localhost", },
+ dbname => { default => "test", },
+ dbuser => { default => "", },
+ dbpass => { default => "", },
+ },
+);
+
+use Test::More qw(no_plan);
+use lib "../App-Context/lib";
+use lib "../../App-Context/lib";
+use lib "lib";
+use lib "../lib";
+use lib ".";
+use lib "t";
+
+use App;
+use App::Repository;
+use RepositoryTestUtils qw(create_table drop_table populate_table);
+use strict;
+
+my $dbtype = $App::options{dbtype} || "mysql";
+
+if (!$App::options{dbuser}) {
+ ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
+ exit(0);
+}
+
+my $context = App->context(
+ conf_file => "",
+ conf => {
+ Repository => {
+ default => {
+ class => $App::options{dbclass},
+ dbdriver => $App::options{dbdriver},
+ dbhost => $App::options{dbhost},
+ dbname => $App::options{dbname},
+ dbuser => $App::options{dbuser},
+ dbpass => $App::options{dbpass},
+ table => {
+ test_country => { },
+ test_city => {
+ primary_key => ["city_cd"],
+ alternate_key => [["city_id"]],
+ alias => "c",
+ tablealiases => [ "c", "ctry" ],
+ tablealias => {
+ c => { table => "test_city", },
+ ctry => {
+ table => "test_country",
+ dependencies => [ "c" ],
+ joincriteria => "ctry.country = c.country",
+ },
+ },
+ column => {
+ country_nm => { dbexpr => "ctry.country_nm", },
+ },
+ },
+ test_person => {
+ alias => "p",
+ tablealiases => [ "p", "c", "ctry" ],
+ tablealias => {
+ c => {
+ table => "test_city",
+ dependencies => [ "p" ],
+ joincriteria => "c.city_cd = p.city_cd",
+ },
+ ctry => {
+ table => "test_country",
+ dependencies => [ "p" ],
+ joincriteria => "ctry.country = p.country",
+ },
+ },
+ column => {
+ country_nm => { dbexpr => "ctry.country_nm", },
+ gender => { alias => "gnd", },
+ },
+ },
+ test_hotel => {
+ primary_key => ["prop_id"],
+ alternate_key => [["city_cd", "prop_cd"]],
+ alias => "h",
+ tablealias => {
+ hc => {
+ table => "test_country",
+ dependencies => ["h"],
+ joincriteria => "h.chain_cd = hp.chain_cd",
+ },
+ },
+ column => {
+ city_nm => {
+ alias => "ctyname",
+ dbexpr => "cty.city_nm",
+ },
+ },
+ },
+ test_bkg => { },
+ },
+ default_date_format => "YYYY-MM-DD",
+ },
+ },
+ },
+ debug_sql => $App::options{debug_sql},
+ trace => $App::options{trace},
+);
+
+my $rep = $context->repository();
+
+&drop_table($rep, "test_person");
+&create_table($rep, "test_person");
+&populate_table($rep, "test_person");
+
+&drop_table($rep, "test_country");
+&create_table($rep, "test_country");
+&populate_table($rep, "test_country");
+
+&drop_table($rep, "test_city");
+&create_table($rep, "test_city");
+&populate_table($rep, "test_city");
+
+&drop_table($rep, "test_hotel_prop");
+&create_table($rep, "test_hotel_prop");
+&populate_table($rep, "test_hotel_prop");
+
+&drop_table($rep, "test_hotel_bkg");
+&create_table($rep, "test_hotel_bkg");
+&populate_table($rep, "test_hotel_bkg");
+
+sub check_select {
+ my ($sql, $expected_rows, $debug) = @_;
+
+ my ($rows, $reprows);
+ eval {
+ $rows = $rep->_do($sql);
+ };
+ is($@,"","sql ok");
+ if ($debug) {
+ print $sql;
+ print "ROWS [", ($#$rows + 1), "]\n";
+ foreach my $row (@$rows) {
+ print "ROW [", join("|", @$row), "]\n";
+ }
+ }
+
+ if (defined $expected_rows) {
+ is(($#$rows + 1), $expected_rows, "num rows $expected_rows");
+ }
+}
+
+# &test_get_rows($expect_sql,0,"_mk_select_joined_sql(): 1 col, no params","test_person",{},"age");
+sub test_get_rows {
+ my $expected_sql = shift;
+ 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 = $rep->_do($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);
+###########################################################################
+# TRUE JOINED MULTI-TABLE SELECT SQL-GENERATION TESTS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ ctry.country_nm
+from test_person p
+ inner join test_country ctry on ctry.country = p.country
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{},"country_nm");
+is($sql, $expect_sql, "_mk_select_joined_sql(): person.country_nm");
+&check_select($sql,7);
+
+exit;
+
+#$sql = $rep->_mk_select_joined_sql("test_person",{},["age"]);
+#is($sql, $expect_sql, "_mk_select_joined_sql(): 1 col as array, no params");
+#&check_select($sql,7);
+
+###########################################################################
+# "JOINED" SINGLE-TABLE SELECT SQL-GENERATION TESTS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ p.age
+from test_person p
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{},"age");
+is($sql, $expect_sql, "_mk_select_joined_sql(): 1 col, no params");
+&check_select($sql,7);
+$sql = $rep->_mk_select_joined_sql("test_person",{},["age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): 1 col as array, no params");
+&check_select($sql,7);
+
+$expect_sql = <<EOF;
+select
+ p.age
+from test_person p
+where p.person_id = 1
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",1,"age");
+is($sql, $expect_sql, "_mk_select_joined_sql(): key");
+&check_select($sql,1);
+$sql = $rep->_mk_select_joined_sql("test_person",1,["age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): key (again)");
+&check_select($sql,1);
+
+$expect_sql = <<EOF;
+select
+ p.age
+from test_person p
+where p.person_id is null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",undef,"age");
+is($sql, $expect_sql, "_mk_select_joined_sql(): by key (bind vars)");
+if ($dbtype ne "mysql") {
+ &check_select($sql,0); # there appears to be a bug in MySQL here
+}
+
+$expect_sql = <<EOF;
+select
+ p.age
+from test_person p
+where p.age = 37
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{age => 37},"age");
+is($sql, $expect_sql, "_mk_select_joined_sql(): param");
+&check_select($sql,1);
+
+$expect_sql = <<EOF;
+select
+ p.age
+from test_person p
+where p.gender = 'M'
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{gender => "M"},"age");
+is($sql, $expect_sql, "_mk_select_joined_sql(): non-selected param");
+&check_select($sql,4);
+
+$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'
+EOF
+$sql = $rep->_mk_select_joined_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_joined_sql(): params plain");
+&check_select($sql,0);
+
+$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
+EOF
+$sql = $rep->_mk_select_joined_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_joined_sql(): params (bind vars)");
+&check_select($sql,0);
+
+$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')
+EOF
+$sql = $rep->_mk_select_joined_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_joined_sql(): params auto_in");
+&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'
+EOF
+$sql = $rep->_mk_select_joined_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_joined_sql(): param.eq");
+&check_select($sql,0);
+
+$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'
+EOF
+$sql = $rep->_mk_select_joined_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_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",
+ "age" => "=37,39",
+ "birth_dt" => "==1962-01-01,1963-12-31",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.eq => in (inferred)");
+&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'
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{
+ "_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen",
+ "age.in" => "37",
+ "birth_dt.in" => "1962-01-01",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.in => eq");
+&check_select($sql,0);
+
+$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')
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{
+ "_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen,paul",
+ "age.in" => "37,39",
+ "birth_dt.in" => "1962-01-01,1963-12-31",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.in");
+&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'
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{
+ "_order" => [ "first_name.ne", "age.ne", "birth_dt.ne", ],
+ "first_name.ne" => "stephen",
+ "age.ne" => "37",
+ "birth_dt.ne" => "1962-01-01",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.ne");
+&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'
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{
+ "_order" => [ "first_name.ge", "age.ge", "birth_dt.ge", ],
+ "first_name.ge" => "stephen",
+ "age.ge" => "37",
+ "birth_dt.ge" => "1962-01-01",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.ge");
+&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'
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",{
+ "_order" => [ "first_name.gt", "age.gt", "birth_dt.gt", ],
+ "first_name.gt" => "stephen",
+ "age.gt" => "37",
+ "birth_dt.gt" => "1962-01-01",
+ },["first_name"]);
+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'
+EOF
+$sql = $rep->_mk_select_joined_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_joined_sql(): param.le");
+&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'
+EOF
+$sql = $rep->_mk_select_joined_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_joined_sql(): param.lt");
+&check_select($sql,0);
+
+$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%'
+EOF
+$sql = $rep->_mk_select_joined_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_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",
+ "age" => "=~3",
+ "birth_dt" => "~1962",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.contains (inferred)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name
+from test_person p
+where p.first_name not like '%s%'
+ and p.age not like '%3%'
+ and p.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", ],
+ "first_name.not_contains" => "s",
+ "age.not_contains" => "3",
+ "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", ],
+ "first_name" => "!~s",
+ "age" => "!~3",
+ "birth_dt" => "!~1962",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.not_contains (inferred)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name
+from test_person p
+where p.first_name like '%s%e_'
+ and p.age like '%3'
+ and p.birth_dt like '1962\\_%'
+EOF
+$sql = $rep->_mk_select_joined_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_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?",
+ "age" => "*3",
+ "birth_dt" => "1962_*",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.matches (inferred)");
+&check_select($sql,0);
+
+$sql = $rep->_mk_select_joined_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_joined_sql(): param.matches (inferred)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name
+from test_person p
+where p.first_name not like '%s%'
+ and p.age not like '%3'
+ and p.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", ],
+ "first_name.not_matches" => "*s*",
+ "age.not_matches" => "*3",
+ "birth_dt.not_matches" => "1962*",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.not_matches");
+&check_select($sql,0);
+
+# this doesn't work yet, but that's ok
+#$sql = $rep->_mk_select_joined_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_joined_sql(): param.not_matches (inferred)");
+#&check_select($sql,0);
+
+if ($dbtype eq 'mysql') {
+ $expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.age
+from test_person p
+where p.age >= 37
+limit 1
+EOF
+}
+elsif ($dbtype eq 'oracle') {
+ $expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.age
+from test_person p
+where p.age >= 37
+ and rownum >= 1
+ and rownum <= 1
+EOF
+}
+$sql = $rep->_mk_select_joined_sql("test_person",{"age.ge" => 37},["first_name","last_name","age"],{startrow => 1, endrow => 1});
+is($sql, $expect_sql, "_mk_select_joined_sql(): cols, endrow");
+&check_select($sql,1);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.city,
+ p.state,
+ p.age
+from test_person p
+order by
+ p.last_name asc,
+ p.city,
+ p.address,
+ p.gender desc,
+ p.first_name
+EOF
+$sql = $rep->_mk_select_joined_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_joined_sql(): ordercols, directions");
+&check_select($sql,7);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.city,
+ p.state,
+ p.age
+from test_person p
+where p.age in (14,15,16,17,18)
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age.verbatim" => "p.age in (14,15,16,17,18)"},
+ ["first_name","last_name","city","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): verbatim");
+&check_select($sql,0);
+
+###########################################################################
+# NULL CONDITIONS (AND "IN")
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd
+from test_person p
+where p.age is null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person", { age => "NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is null (by 'NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_joined_sql("test_person", { age => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is null (by undef)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd
+from test_person p
+where p.age is not null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person", { age => "!NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is not null (by '!NULL')");
+&check_select($sql,7);
+$sql = $rep->_mk_select_joined_sql("test_person", { "age.ne" => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is not null (by .ne undef)");
+&check_select($sql,7);
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd
+from test_person p
+where p.first_name is not null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person", { first_name => "!NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is not null (by '!NULL')");
+&check_select($sql,7);
+$sql = $rep->_mk_select_joined_sql("test_person", { "first_name.ne" => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is not null (by .ne undef)");
+&check_select($sql,7);
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd
+from test_person p
+where (p.first_name not in ('stephen','keith') and p.first_name is not null)
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person", { first_name => "!stephen,keith,NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): not in and not null (by '!stephen,keith,NULL')");
+&check_select($sql,5);
+$sql = $rep->_mk_select_joined_sql("test_person", { "first_name.not_in" => "stephen,keith,NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is not null (by .not_in 'stephen,keith,NULL')");
+&check_select($sql,5);
+
+if ($dbtype eq 'mysql') {
+ $expect_sql = <<'EOF';
+select
+ p.first_name
+from test_person p
+where p.first_name like '%\'%'
+ and p.birth_dt like '%\\\'_'
+EOF
+}
+elsif ($dbtype eq 'oracle') {
+ $expect_sql = <<'EOF';
+select
+ p.first_name
+from test_person p
+where p.first_name like '%''%'
+ and p.birth_dt like '%\''_'
+EOF
+}
+
+#print "[$expect_sql]\n";
+$sql = $rep->_mk_select_joined_sql("test_person",{
+ "_order" => [ "first_name.contains", "birth_dt.matches", ],
+ "first_name.contains" => "'",
+ "birth_dt.matches" => "*\\'?",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): param.contains (proper quoting of ' and \\' required)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd
+from test_person p
+where p.age is not null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person", { age => "!NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is not null (by '!NULL')");
+&check_select($sql,7);
+$sql = $rep->_mk_select_joined_sql("test_person", { "age.ne" => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is not null (by .ne undef)");
+&check_select($sql,7);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+where (p.age in (14,15,16) or p.age is null)
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age" => "14,15,16,NULL"},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): ,NULL");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+where (p.age in (14,15,16) or p.age is null)
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age" => "NULL,14,15,16"},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): NULL,");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+where (p.age in (14,15,16) or p.age is null)
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age" => "14,15,NULL,16"},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): ,NULL,");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+where p.age is null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age" => "NULL"},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): NULL");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+where p.age is null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age" => undef},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): undef (NULL)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+where p.first_name = ''
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"first_name" => ""},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): \"\" (use literal as string)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age" => ""},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): \"\" (ALL)");
+&check_select($sql,7);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name
+from test_person p
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age" => "ALL"},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): explicit ALL adds nothing to the where clause");
+&check_select($sql,7);
+
+$expect_sql = <<EOF;
+select distinct
+ p.gender as gnd
+from test_person p
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {},
+ ["gender"],
+ {distinct => 1});
+is($sql, $expect_sql, "_mk_select_joined_sql(): distinct");
+&check_select($sql,2);
+
+###########################################################################
+# NEW REPOPS CONDITIONS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd
+from test_person p
+where p.age is null
+EOF
+$sql = $rep->_mk_select_joined_sql("test_person", { age => "NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is null (by 'NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_joined_sql("test_person", { age => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): is null (by undef)");
+&check_select($sql,0);
+exit(0); # XXX REMOVE EXIT HERE XXX
+
+###########################################################################
+# LITERAL EXPRESSIONS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd gnd,
+ max(age) max_age_
+from
+ test_person p
+group by
+ p.gnd
+order by
+ p.gnd
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): literal aggregation function",
+ "test_person",
+ {},
+ ["gender","max(age)"],
+ { group_by => ["gender"], order_by => ["gender"] });
+
+$expect_sql = <<EOF;
+select
+ p.gender as gnd gnd,
+ 2*age _2_age
+from
+ test_person p
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): literal aggregation function",
+ "test_person",
+ {},
+ ["gender","2*age"]);
+
+###########################################################################
+# EXCEPTIONS
+###########################################################################
+
+{
+ my ($rows, $row);
+ open(SAVE, ">&STDERR");
+ open(STDERR, "/dev/null");
+
+ $rows = [];
+ eval {
+ $rows = $rep->get_rows("table_y", {}, ["x"]);
+ };
+ ok($@ =~ /fail/, "get_rows(): bad SQL causes exception");
+
+ $rows = [];
+ eval {
+ $rows = $rep->get_row("table_y", {}, ["x"]);
+ };
+ ok($@ =~ /fail/, "get_row(): bad SQL causes exception");
+
+ $rep->insert("test_person",["person_id","last_name","first_name"],[1,"Stephen","Adkins"]);
+
+ $rep->_disconnect();
+ $rows = [];
+ eval {
+ $rows = $rep->get_rows("test_person", {}, ["person_id"]);
+ };
+ ok($#$rows == 0, "get_rows(): reconnect because rep was _disconnect()ed");
+
+ $rep->{dbh}{mysql_auto_reconnect} = 0;
+ $rep->{dbh}->disconnect();
+ $rows = [];
+ eval {
+ $rows = $rep->get_rows("test_person", {}, ["person_id"]);
+ };
+ ok($#$rows == 0, "get_rows(): reconnect because dbh was disconnect()ed");
+
+ $rep->{dbh}{mysql_auto_reconnect} = 0;
+ $rep->{dbh}->disconnect();
+ $row = undef;
+ eval {
+ $row = $rep->get_row("test_person", {person_id => 1}, ["person_id"]);
+ };
+ ok(defined $row && $#$row == 0, "get_row(): reconnect because dbh was disconnect()ed");
+
+ open(STDERR, ">&SAVE");
+ close(SAVE);
+
+ $rep->delete("test_person",{person_id => 1});
+}
+
+exit(0);
+
+###########################################################################
+# JOINED (MULTI-TABLE) SELECT SQL-GENERATION TESTS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ p.age cn13
+from
+ test_person p
+EOF
+#$App::trace = 1;
+#$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,
+ p.state,
+ p.age
+from test_person p
+where (p.age in (14,15) or p.age > 18 or p.age is null)
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): OR conditions with [] value",
+ "test_person",
+ {age => [14, 15, ">18", undef]},
+ ["first_name","state","age"]);
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {age => [14,15,">18",undef]},
+ ["first_name","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): OR conditions with [] value");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.state,
+ p.age
+from test_person p
+where p.age > 14
+ and p.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_joined_sql("test_person",
+ [age => ">14", first_name => "*A*"],
+ ["first_name","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): square bracket [] params");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.state,
+ p.age
+from test_person p
+where p.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_joined_sql("test_person",
+ [age => ">14", first_name => "*A*"],
+ ["first_name","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): verbatim");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.state,
+ p.age
+from test_person p
+where not (not(p.age > 14)
+ and not (p.first_name like '%A%')
+ and not (p.state in ('GA','CA') and
+ p.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_joined_sql("test_person",
+ [age => ">14", first_name => "*A*"],
+ ["first_name","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_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
+ p.age cn13,
+ p.person_id cn0
+from
+ test_person p
+EOF
+&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): auto_extend","test_person",{},"age",{auto_extend=>1});
+
+$expect_sql = <<EOF;
+select
+ p.age cn13
+from
+ test_person p
+where p.p.person_id = 1
+EOF
+&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): key","test_person",1,"age");
+
+#$expect_sql = <<EOF;
+#select
+# p.age cn13
+#from
+# test_person p
+#where p.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
+ p.age cn13
+from
+ test_person p
+where p.p.age = 37
+EOF
+&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): param","test_person",{age => 37},"age");
+
+$expect_sql = <<EOF;
+select
+ p.age cn13
+from
+ test_person p
+where p.p.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
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name = 'stephen'
+ and p.age = 37
+ and p.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
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name is null
+ and p.age is null
+ and p.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
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name in ('stephen','paul')
+ and p.age in (37,39)
+ and p.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
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name = 'stephen'
+ and p.age = 37
+ and p.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
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name in ('stephen','paul')
+ and p.age in (37,39)
+ and p.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
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name = 'stephen'
+ and p.age = 37
+ and p.birth_dt = '1962-01-01'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.in => eq",
+ "test_person",{
+ "_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen",
+ "age.in" => "37",
+ "birth_dt.in" => "1962-01-01",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name in ('stephen','paul')
+ and p.age in (37,39)
+ and p.birth_dt in ('1962-01-01','1963-12-31')
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.in",
+ "test_person",{
+ "_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen,paul",
+ "age.in" => "37,39",
+ "birth_dt.in" => "1962-01-01,1963-12-31",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name != 'stephen'
+ and p.age != 37
+ and p.birth_dt != '1962-01-01'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.ne",
+ "test_person",{
+ "_order" => [ "first_name.ne", "age.ne", "birth_dt.ne", ],
+ "first_name.ne" => "stephen",
+ "age.ne" => "37",
+ "birth_dt.ne" => "1962-01-01",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name >= 'stephen'
+ and p.age >= 37
+ and p.birth_dt >= '1962-01-01'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.ge",
+ "test_person",{
+ "_order" => [ "first_name.ge", "age.ge", "birth_dt.ge", ],
+ "first_name.ge" => "stephen",
+ "age.ge" => "37",
+ "birth_dt.ge" => "1962-01-01",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name > 'stephen'
+ and p.age > 37
+ and p.birth_dt > '1962-01-01'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.gt",
+ "test_person",{
+ "_order" => [ "first_name.gt", "age.gt", "birth_dt.gt", ],
+ "first_name.gt" => "stephen",
+ "age.gt" => "37",
+ "birth_dt.gt" => "1962-01-01",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name <= 'stephen'
+ and p.age <= 37
+ and p.birth_dt <= '1962-01-01'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.le",
+ "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"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name < 'stephen'
+ and p.age < 37
+ and p.birth_dt < '1962-01-01'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.lt",
+ "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"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name like '%s%'
+ and p.age like '%3%'
+ and p.birth_dt like '%1962%'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.contains",
+ "test_person",{
+ "_order" => [ "first_name.contains", "age.contains", "birth_dt.contains", ],
+ "first_name.contains" => "s",
+ "age.contains" => "3",
+ "birth_dt.contains" => "1962",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1
+from
+ test_person p
+where p.p.first_name like '%s%'
+ and p.age like '%3'
+ and p.birth_dt like '1962%'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.matches",
+ "test_person",{
+ "_order" => [ "first_name.matches", "age.matches", "birth_dt.matches", ],
+ "first_name.matches" => "*s*",
+ "age.matches" => "*3",
+ "birth_dt.matches" => "1962*",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1,
+ p.last_name cn2,
+ p.age cn13
+from
+ test_person p
+where p.p.age >= 37
+limit 1
+EOF
+&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): cols, endrow", "test_person",{"age.ge" => 37},["first_name","last_name","age"],{startrow => 1, endrow => 1});
+
+$expect_sql = <<EOF;
+select
+ p.first_name cn1,
+ p.last_name cn2,
+ p.city cn4,
+ p.state cn5,
+ p.age cn13
+from
+ test_person p
+order by
+ cn2 asc,
+ cn4,
+ p.address,
+ p.gender desc,
+ cn1
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): ordercols, directions",
+ "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"}});
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.city,
+ p.state,
+ p.age
+from test_person p
+where p.age in (14,15,16,17,18)
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): verbatim (boo. hiss. evil.)",
+ "test_person",
+ {"age.verbatim" => "p.age in (14,15,16,17,18)"},
+ ["first_name","last_name","city","state","age"]);
+$sql = $rep->_mk_select_joined_sql("test_person",
+ {"age.verbatim" => "p.age in (14,15,16,17,18)"},
+ ["first_name","last_name","city","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): verbatim (boo. hiss. evil.)");
+&check_select($sql,0);
+
+# &drop_table($rep, "test_person");
+# &drop_table($rep, "test_country");
+# &drop_table($rep, "test_city");
+# &drop_table($rep, "test_hotel_prop");
+# &drop_table($rep, "test_hotel_bkg");
+
Added: p5ee/trunk/App-Repository/t/DBI-select-ora.t
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/t/DBI-select-ora.t Mon Nov 10 15:23:53 2008
@@ -0,0 +1,1333 @@
+#!/usr/local/bin/perl -w
+
+use App::Options (
+ options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
+ option => {
+ dbclass => { default => "App::Repository::MySQL", },
+ dbdriver => { default => "mysql", },
+ dbhost => { default => "localhost", },
+ dbname => { default => "test", },
+ dbuser => { default => "", },
+ dbpass => { default => "", },
+ },
+);
+
+use Test::More qw(no_plan);
+use lib "../App-Context/lib";
+use lib "../../App-Context/lib";
+use lib "lib";
+use lib "../lib";
+use lib ".";
+use lib "t";
+
+use App;
+use App::Repository;
+use RepositoryTestUtils qw(create_table_test_person drop_table_test_person);
+use strict;
+
+my $dbtype = $App::options{dbtype} || "mysql";
+if ($dbtype ne "oracle") {
+ ok(1, "These tests are only for Oracle");
+ exit(0);
+}
+
+if (!$App::options{dbuser}) {
+ ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
+ exit(0);
+}
+
+my $context = App->context(
+ conf_file => "",
+ conf => {
+ Repository => {
+ default => {
+ class => $App::options{dbclass},
+ dbdriver => $App::options{dbdriver},
+ dbhost => $App::options{dbhost},
+ dbname => $App::options{dbname},
+ dbuser => $App::options{dbuser},
+ dbpass => $App::options{dbpass},
+ table => {
+ test_person => {
+ primary_key => ["person_id"],
+ column => {
+ gender => {
+ alias => "gnd",
+ },
+ },
+ },
+ },
+ default_date_format => "YYYY-MM-DD",
+ },
+ },
+ },
+ debug_sql => $App::options{debug_sql},
+ trace => $App::options{trace},
+);
+
+my $rep = $context->repository();
+&drop_table_test_person($rep);
+&create_table_test_person($rep);
+
+sub check_select {
+ my ($sql, $expected_rows, $debug) = @_;
+
+ my ($rows, $reprows);
+ eval {
+ $rows = $rep->_do($sql);
+ };
+ is($@,"","sql ok");
+ if ($debug) {
+ print $sql;
+ print "ROWS [", ($#$rows + 1), "]\n";
+ foreach my $row (@$rows) {
+ print "ROW [", join("|", @$row), "]\n";
+ }
+ }
+
+ if (defined $expected_rows) {
+ is(($#$rows + 1), $expected_rows, "num rows $expected_rows");
+ }
+}
+
+# &test_get_rows($expect_sql,0,"_mk_select_joined_sql(): 1 col, no params","test_person",{},"age");
+sub test_get_rows {
+ my $expected_sql = shift;
+ 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 = $rep->_do($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);
+
+$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.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen",
+ "age.in" => "37",
+ "birth_dt.in" => "1962-01-01",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.in => eq");
+&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.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen,paul",
+ "age.in" => "37,39",
+ "birth_dt.in" => "1962-01-01,1963-12-31",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.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.ne", "age.ne", "birth_dt.ne", ],
+ "first_name.ne" => "stephen",
+ "age.ne" => "37",
+ "birth_dt.ne" => "1962-01-01",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.ne");
+&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.ge", "age.ge", "birth_dt.ge", ],
+ "first_name.ge" => "stephen",
+ "age.ge" => "37",
+ "birth_dt.ge" => "1962-01-01",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.ge");
+&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.gt", "age.gt", "birth_dt.gt", ],
+ "first_name.gt" => "stephen",
+ "age.gt" => "37",
+ "birth_dt.gt" => "1962-01-01",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.gt");
+&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.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);
+
+if ($dbtype eq 'mysql') {
+$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);
+}
+
+if ($dbtype eq 'oracle') {
+$expect_sql = <<EOF;
+select
+ first_name,
+ last_name,
+ age
+from test_person
+where age >= 37
+ and rownum >= 1
+ and rownum <= 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);
+
+if ($dbtype eq 'mysql') {
+$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);
+}
+
+if ($dbtype eq 'oracle') {
+$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);
+}
+
+$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
+ 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;
+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,NULL,16"},
+ ["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 is null
+EOF
+$sql = $rep->_mk_select_sql("test_person",
+ {"age" => "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 is null
+EOF
+$sql = $rep->_mk_select_sql("test_person",
+ {"age" => undef},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): undef (NULL)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ first_name,
+ last_name
+from test_person
+where first_name = ''
+EOF
+$sql = $rep->_mk_select_sql("test_person",
+ {"first_name" => ""},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): \"\" (use literal as string)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ first_name,
+ last_name
+from test_person
+EOF
+$sql = $rep->_mk_select_sql("test_person",
+ {"age" => ""},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): \"\" (ALL)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ first_name,
+ last_name
+from test_person
+EOF
+$sql = $rep->_mk_select_sql("test_person",
+ {"age" => "ALL"},
+ ["first_name","last_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): explicit ALL adds nothing to the where clause");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select distinct
+ gender
+from test_person
+EOF
+$sql = $rep->_mk_select_sql("test_person",
+ {},
+ ["gender"],
+ {distinct => 1});
+is($sql, $expect_sql, "_mk_select_sql(): distinct");
+&check_select($sql,0);
+
+###########################################################################
+# NEW REPOPS CONDITIONS
+###########################################################################
+
+$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);
+
+###########################################################################
+# LITERAL EXPRESSIONS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ t1.gender as gnd,
+ max(age) as max_age_
+from
+ test_person t1
+group by
+ t1.gender
+order by
+ gnd
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): literal aggregation function",
+ "test_person",
+ {},
+ ["gender","max(age)"],
+ { group_by => ["gender"], order_by => ["gender"] });
+
+$expect_sql = <<EOF;
+select
+ t1.gender as gnd,
+ 2*age as _2_age
+from
+ test_person t1
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): literal aggregation function",
+ "test_person",
+ {},
+ ["gender","2*age"]);
+
+###########################################################################
+# EXCEPTIONS
+###########################################################################
+
+exit;
+
+{
+ my ($rows, $row);
+ open(SAVE, ">&STDERR");
+ open(STDERR, "/dev/null");
+
+ $rows = [];
+ eval {
+ $rows = $rep->get_rows("table_y", {}, ["x"]);
+ };
+ ok($@ =~ /fail/, "get_rows(): bad SQL causes exception");
+
+ $rows = [];
+ eval {
+ $rows = $rep->get_row("table_y", {}, ["x"]);
+ };
+ ok($@ =~ /fail/, "get_row(): bad SQL causes exception");
+
+ $rep->insert("test_person",["person_id","last_name","first_name"],[1,"Stephen","Adkins"]);
+
+ $rep->_disconnect();
+ $rows = [];
+ eval {
+ $rows = $rep->get_rows("test_person", {}, ["person_id"]);
+ };
+ ok($#$rows == 0, "get_rows(): reconnect because rep was _disconnect()ed");
+
+ $rep->{dbh}{mysql_auto_reconnect} = 0;
+ $rep->{dbh}->disconnect();
+ $rows = [];
+ eval {
+ $rows = $rep->get_rows("test_person", {}, ["person_id"]);
+ };
+ ok($#$rows == 0, "get_rows(): reconnect because dbh was disconnect()ed");
+
+ $rep->{dbh}{mysql_auto_reconnect} = 0;
+ $rep->{dbh}->disconnect();
+ $row = undef;
+ eval {
+ $row = $rep->get_row("test_person", {person_id => 1}, ["person_id"]);
+ };
+ ok(defined $row && $#$row == 0, "get_row(): reconnect because dbh was disconnect()ed");
+
+ open(STDERR, ">&SAVE");
+ close(SAVE);
+
+ $rep->delete("test_person",{person_id => 1});
+}
+
+exit(0);
+
+###########################################################################
+# JOINED (MULTI-TABLE) SELECT SQL-GENERATION TESTS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ t1.age cn13
+from
+ test_person t1
+EOF
+#$App::trace = 1;
+#$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
+ t1.first_name,
+ t1.state,
+ t1.age
+from test_person
+where (age in (14,15) or age > 18 or age is null)
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): OR conditions with [] value",
+ "test_person",
+ {age => [14, 15, ">18", undef]},
+ ["first_name","state","age"]);
+$sql = $rep->_mk_select_sql("test_person",
+ {age => [14,15,">18",undef]},
+ ["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'
+ 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
+ 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.in => eq",
+ "test_person",{
+ "_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen",
+ "age.in" => "37",
+ "birth_dt.in" => "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.in",
+ "test_person",{
+ "_order" => [ "first_name.in", "age.in", "birth_dt.in", ],
+ "first_name.in" => "stephen,paul",
+ "age.in" => "37,39",
+ "birth_dt.in" => "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.ne",
+ "test_person",{
+ "_order" => [ "first_name.ne", "age.ne", "birth_dt.ne", ],
+ "first_name.ne" => "stephen",
+ "age.ne" => "37",
+ "birth_dt.ne" => "1962-01-01",
+ },["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.ge",
+ "test_person",{
+ "_order" => [ "first_name.ge", "age.ge", "birth_dt.ge", ],
+ "first_name.ge" => "stephen",
+ "age.ge" => "37",
+ "birth_dt.ge" => "1962-01-01",
+ },["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.gt",
+ "test_person",{
+ "_order" => [ "first_name.gt", "age.gt", "birth_dt.gt", ],
+ "first_name.gt" => "stephen",
+ "age.gt" => "37",
+ "birth_dt.gt" => "1962-01-01",
+ },["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.le",
+ "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"]);
+
+$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.lt",
+ "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"]);
+
+$expect_sql = <<EOF;
+select
+ t1.first_name cn1
+from
+ test_person t1
+where t1.first_name like '%s%'
+ and t1.age like '%3%'
+ and t1.birth_dt like '%1962%'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.contains",
+ "test_person",{
+ "_order" => [ "first_name.contains", "age.contains", "birth_dt.contains", ],
+ "first_name.contains" => "s",
+ "age.contains" => "3",
+ "birth_dt.contains" => "1962",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ t1.first_name cn1
+from
+ test_person t1
+where t1.first_name like '%s%'
+ and t1.age like '%3'
+ and t1.birth_dt like '1962%'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): param.matches",
+ "test_person",{
+ "_order" => [ "first_name.matches", "age.matches", "birth_dt.matches", ],
+ "first_name.matches" => "*s*",
+ "age.matches" => "*3",
+ "birth_dt.matches" => "1962*",
+ },["first_name"]);
+
+$expect_sql = <<EOF;
+select
+ t1.first_name cn1,
+ t1.last_name cn2,
+ t1.age cn13
+from
+ test_person t1
+where t1.age >= 37
+limit 1
+EOF
+&test_get_rows($expect_sql,0,"_mk_select_joined_sql(): cols, endrow", "test_person",{"age.ge" => 37},["first_name","last_name","age"],{startrow => 1, endrow => 1});
+
+$expect_sql = <<EOF;
+select
+ t1.first_name cn1,
+ t1.last_name cn2,
+ t1.city cn4,
+ t1.state cn5,
+ t1.age cn13
+from
+ test_person t1
+order by
+ cn2 asc,
+ cn4,
+ t1.address,
+ t1.gender desc,
+ cn1
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): ordercols, directions",
+ "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"}});
+
+$expect_sql = <<EOF;
+select
+ t1.first_name,
+ t1.last_name,
+ t1.city,
+ t1.state,
+ t1.age
+from test_person
+where age in (14,15,16,17,18)
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): verbatim (boo. hiss. evil.)",
+ "test_person",
+ {"age.verbatim" => "age in (14,15,16,17,18)"},
+ ["first_name","last_name","city","state","age"]);
+$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 (boo. hiss. evil.)");
+&check_select($sql,0);
+
+exit 0;
+
Added: p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm Mon Nov 10 15:23:53 2008
@@ -0,0 +1,441 @@
+
+#############################################################################
+# $Id: HotelUtils.pm 25141 2008-08-19 15:38:16Z asawczyn $
+#############################################################################
+
+package RepositoryTestUtils;
+
+use strict;
+use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
+
+require Exporter;
+
+@ISA = qw(Exporter);
+
+@EXPORT = qw(
+);
+
+@EXPORT_OK = qw(
+ create_table_test_person
+ drop_table_test_person
+ populate_table_test_person
+ create_table_test_app_cache
+ drop_table_test_app_cache
+ create_table
+ drop_table
+ populate_table
+);
+
+use App;
+use App::Repository;
+
+sub create_table_test_person {
+ &App::sub_entry if ($App::trace);
+ my ($rep) = @_;
+ my $dbh = $rep->{dbh};
+
+ my ($ddl);
+ my $dbtype = $App::options{dbtype} || "mysql";
+
+ my $autoincrement = "";
+ my $suffix = "";
+ if ($dbtype eq "mysql") {
+ $autoincrement = " auto_increment";
+ $suffix = " ENGINE=InnoDB DEFAULT CHARSET=latin1";
+ }
+
+ my $double_type = ($dbtype eq "mysql") ? 'double' : 'float';
+ my $datetime_type = ($dbtype eq "mysql") ? 'datetime' : 'date';
+
+ $ddl = <<EOF;
+create table test_person (
+ person_id integer not null$autoincrement,
+ first_name varchar(99) null,
+ last_name varchar(99) null,
+ address varchar(99) null,
+ city varchar(99) null,
+ state varchar(99) null,
+ zip varchar(10) null,
+ country char(2) null,
+ home_phone varchar(99) null,
+ work_phone varchar(99) null,
+ email_address varchar(99) null,
+ gender char(1) null,
+ birth_dt date null,
+ age integer null,
+ data blob null,
+ chess_rating float null,
+ modify_dttm timestamp null,
+ change_dttm $datetime_type null,
+ deci_col decimal null,
+ double_col $double_type null,
+ long_col long varchar null,
+ primary key (person_id)
+)$suffix
+EOF
+ $dbh->do($ddl);
+ $ddl = "create index person_ie1 on test_person (last_name, first_name)";
+ $dbh->do($ddl);
+ if ($dbtype eq "oracle") {
+ $ddl = <<EOF;
+create sequence test_person_seq start with 1 increment by 1 nomaxvalue nocycle cache 200
+EOF
+ $dbh->do($ddl);
+ $ddl = <<EOF;
+CREATE OR REPLACE TRIGGER tib_test_person
+BEFORE INSERT ON test_person
+FOR EACH ROW
+WHEN (new.person_id IS NULL or new.person_id = 0)
+BEGIN
+ SELECT test_person_seq.NEXTVAL
+ INTO :new.person_id
+ FROM DUAL;
+END tib_test_person;
+EOF
+ $dbh->do($ddl);
+ }
+
+ &App::sub_exit() if ($App::trace);
+}
+
+sub drop_table_test_person {
+ &App::sub_entry if ($App::trace);
+ my ($rep) = @_;
+ my $dbh = $rep->{dbh};
+
+ my $dbtype = $App::options{dbtype} || "mysql";
+
+ eval { $dbh->do("drop table test_person"); };
+ #warn $@ if ($@);
+ if ($dbtype eq "oracle") {
+ eval { $dbh->do("drop sequence test_person_seq"); };
+ }
+
+ &App::sub_exit() if ($App::trace);
+}
+
+sub populate_table_test_person {
+ &App::sub_entry if ($App::trace);
+ my ($rep) = @_;
+ my $dbh = $rep->{dbh};
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state) values (1,39,'stephen', 'M','GA')");
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state) values (2,37,'susan', 'F','GA')");
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state) values (3, 6,'maryalice','F','GA')");
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state) values (4, 3,'paul', 'M','GA')");
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state) values (5, 1,'christine','F','GA')");
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state) values (6,45,'tim', 'M','GA')");
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state) values (7,39,'keith', 'M','GA')");
+ &App::sub_exit() if ($App::trace);
+}
+
+sub create_table_test_app_cache {
+ my ($rep) = @_;
+ my $dbh = $rep->{dbh};
+ my $dbtype = $App::options{dbtype} || "mysql";
+
+ my $suffix = "";
+ my $CURRENT_TIMESTAMP = "";
+ my $datetime = "date";
+ my $longblob = "blob";
+ if ($dbtype eq "mysql") {
+ $suffix = "ENGINE=InnoDB DEFAULT CHARSET=latin1";
+ $CURRENT_TIMESTAMP = "default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
+ $datetime = "datetime";
+ $longblob = "longblob";
+ }
+
+ eval { $dbh->do("drop table test_app_cache"); };
+ my $ddl = <<EOF;
+create table test_app_cache (
+ cache_type varchar(16) not null,
+ cache_key varchar(40) not null,
+ generate_dttm $datetime default null,
+ serializer varchar(12) default null,
+ serialization_args varchar(64) default null,
+ data $longblob,
+ modify_dttm timestamp not null $CURRENT_TIMESTAMP,
+ primary key (cache_type,cache_key)
+) $suffix
+EOF
+ $dbh->do($ddl);
+ $ddl = "create index test_app_cache_ie1 on test_app_cache (modify_dttm)";
+ $dbh->do($ddl);
+
+ if ($dbtype eq 'oracle') {
+ $ddl = <<EOF;
+CREATE OR REPLACE TRIGGER tib_test_app_cache
+BEFORE INSERT ON test_app_cache
+FOR EACH ROW
+WHEN (new.modify_dttm IS NULL)
+BEGIN
+ SELECT sysdate
+ INTO :new.modify_dttm
+ FROM DUAL;
+END tib_test_app_cache;
+EOF
+ $dbh->do($ddl);
+ }
+}
+
+sub drop_table_test_app_cache {
+ &App::sub_entry if ($App::trace);
+ my ($rep) = @_;
+ my $dbh = $rep->{dbh};
+ my $dbtype = $App::options{dbtype} || "mysql";
+ eval { $dbh->do("drop table test_app_cache"); };
+ &App::sub_exit() if ($App::trace);
+}
+
+######################################################################################################
+# GENERIC DATABASE TABLE GENERATION
+######################################################################################################
+
+my (%table_schema, %table_index, %table_autoid_column, %table_data);
+
+$table_schema{test_person} = <<EOF;
+create table test_person (
+ person_id integer not null AUTOINCREMENT,
+ first_name varchar(99) null,
+ last_name varchar(99) null,
+ address varchar(99) null,
+ city varchar(99) null,
+ state varchar(99) null,
+ zip varchar(10) null,
+ country char(2) null,
+ home_phone varchar(99) null,
+ work_phone varchar(99) null,
+ email_address varchar(99) null,
+ gender char(1) null,
+ birth_dt date null,
+ age integer null,
+ data blob null,
+ chess_rating float null,
+ modify_dttm timestamp null,
+ change_dttm DATETIME null,
+ deci_col decimal null,
+ double_col DOUBLE null,
+ long_col long varchar null,
+ primary key (person_id)
+)SUFFIX
+EOF
+$table_index{test_person} = [
+ "create index test_person_ie1 on test_person (last_name, first_name)",
+];
+$table_autoid_column{test_person} = "person_id";
+$table_data{test_person} = [
+ "insert into test_person (age,first_name,gender,state,country) values (39,'stephen', 'M','GA','US')",
+ "insert into test_person (age,first_name,gender,state,country) values (37,'susan', 'F','GA','US')",
+ "insert into test_person (age,first_name,gender,state,country) values ( 6,'maryalice','F','GA','US')",
+ "insert into test_person (age,first_name,gender,state,country) values ( 3,'paul', 'M','GA','US')",
+ "insert into test_person (age,first_name,gender,state,country) values ( 1,'christine','F','GA','US')",
+ "insert into test_person (age,first_name,gender,state,country) values (45,'tim', 'M','GA','US')",
+ "insert into test_person (age,first_name,gender,state,country) values (39,'keith', 'M','GA','US')",
+];
+
+$table_schema{test_country} = <<EOF;
+create table test_country (
+ country_id integer not null AUTOINCREMENT,
+ country char(2) not null,
+ country_nm varchar(64) not null,
+ primary key (country_id)
+)SUFFIX
+EOF
+$table_index{test_country} = [
+ "create index test_country_ie1 on test_country (country)",
+ "create index test_country_ie2 on test_country (country_nm)",
+];
+$table_autoid_column{test_country} = "country_id";
+$table_data{test_country} = [
+ "insert into test_country (country, country_nm) values ('AU','AUSTRALIA')",
+ "insert into test_country (country, country_nm) values ('BR','BRAZIL')",
+ "insert into test_country (country, country_nm) values ('CN','CHINA')",
+ "insert into test_country (country, country_nm) values ('DE','GERMANY')",
+ "insert into test_country (country, country_nm) values ('ES','SPAIN')",
+ "insert into test_country (country, country_nm) values ('FR','FRANCE')",
+ "insert into test_country (country, country_nm) values ('GB','UNITED KINGDOM')",
+ "insert into test_country (country, country_nm) values ('IT','ITALY')",
+ "insert into test_country (country, country_nm) values ('JP','JAPAN')",
+ "insert into test_country (country, country_nm) values ('MX','MEXICO')",
+ "insert into test_country (country, country_nm) values ('RU','RUSSIAN FEDERATION')",
+ "insert into test_country (country, country_nm) values ('US','UNITED STATES')",
+ "insert into test_country (country, country_nm) values ('ZA','SOUTH AFRICA')",
+];
+
+$table_schema{test_city} = <<EOF;
+create table test_city (
+ city_cd char(3) not null,
+ state char(2) null,
+ country char(2) null,
+ city_nm varchar(99) not null,
+ arp_nm varchar(99) null,
+ primary key (city_cd)
+)SUFFIX
+EOF
+$table_index{test_city} = [
+ "create index test_city_ie1 on test_city (city_nm)",
+ "create index test_city_ie2 on test_city (state)",
+ "create index test_city_ie3 on test_city (country)",
+];
+$table_data{test_city} = [
+ "insert into test_city values ('ATL', 'GA', 'US', 'Atlanta', 'William B. Hartsfield International Airport')",
+ "insert into test_city values ('CDG', '', 'FR', 'Paris', 'Roissy Ch. de Gaulle')",
+ "insert into test_city values ('DFW', 'TX', 'US', 'Dallas/Fort Worth', 'Dallas/Fort Worth Int''l')",
+ "insert into test_city values ('EWR', 'NJ', 'US', 'Newark', 'Newark International Airport')",
+ "insert into test_city values ('FCO', '', 'IT', 'Roma', 'Leonardo da Vinci/Fiumicino')",
+ "insert into test_city values ('HKG', '', 'CN', 'Hong Kong', 'Chek Lap Kok International Airport')",
+ "insert into test_city values ('HND', '', 'JP', 'Tokyo', 'Haneda')",
+ "insert into test_city values ('IAD', 'DC', 'US', 'Washington', 'Washington Dulles Int''l Airport')",
+ "insert into test_city values ('JFK', 'NY', 'US', 'New York', 'John F. Kennedy International Airport')",
+ "insert into test_city values ('JNB', '', 'ZA', 'Johannesburg', 'Johannesburg International Airport')",
+ "insert into test_city values ('LAX', 'CA', 'US', 'Los Angeles', 'Los Angeles International Airport')",
+ "insert into test_city values ('LGA', 'NY', 'US', 'New York', 'La Guardia Airport')",
+ "insert into test_city values ('LGW', '', 'GB', 'London', 'Gatwick Airport')",
+ "insert into test_city values ('MAD', '', 'ES', 'Madrid', 'Barajas')",
+ "insert into test_city values ('MDW', 'IL', 'US', 'Chicago', 'Chicago Midway Airport')",
+ "insert into test_city values ('MEX', '', 'MX', 'Mexico City', 'Benito Juarez International')",
+ "insert into test_city values ('NRT', '', 'JP', 'Tokyo', 'Narita')",
+ "insert into test_city values ('ORD', 'IL', 'US', 'Chicago', 'Chicago-O''Hare International Airport')",
+ "insert into test_city values ('SDU', 'RJ', 'BR', 'Rio de Janeiro', 'Aeroporto Santos Dumont')",
+ "insert into test_city values ('SVO', '', 'RU', 'Moscow', 'Sheremetyevo')",
+ "insert into test_city values ('SXF', '', 'DE', 'Berlin', 'Schoenefeld')",
+ "insert into test_city values ('SYD', '', 'AU', 'Sydney', 'Kingsford Smith')",
+ "insert into test_city values ('THF', '', 'DE', 'Berlin', 'Tempelhof')",
+ "insert into test_city values ('TXL', '', 'DE', 'Berlin', 'Tegel')",
+];
+
+$table_schema{test_hotel_prop} = <<EOF;
+create table test_hotel_prop (
+ prop_id integer not null AUTOINCREMENT,
+ prop_cd char(12) not null,
+ prop_nm varchar(99) not null,
+ address varchar(255) null,
+ chain_cd char(2) null,
+ state char(2) null,
+ country char(2) null,
+ primary key (prop_id)
+)SUFFIX
+EOF
+$table_index{test_hotel_prop} = [
+ "create unique index test_hotel_prop_ak1 on test_hotel_prop (prop_cd)",
+];
+$table_autoid_column{test_hotel_prop} = "prop_id";
+$table_data{test_hotel_prop} = [
+ "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('9128' ,'Hilton New York' ,'1335 Avenue Of The Americas, New York, NY 10019','HH','NY','US')",
+ "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('13111','Marriott Marquis NYC' ,'1535 BROADWAY, New York, NY 10036' ,'MC','NY','US')",
+ "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('668' ,'Sheraton New York Hotel&Towers','811 7th Ave, New York, NY 10019' ,'SI','NY','US')",
+ "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('3391' ,'Crowne Plaza Time Sq Manhattan','1605 Broadway, Manhattan, NY 10019' ,'CP','NY','US')",
+ "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('962' ,'Grand Hyatt New York' ,'Park Ave At Grand Central, New York, NY 10017' ,'HY','NY','US')",
+];
+
+$table_schema{test_hotel_bkg} = <<EOF;
+create table test_hotel_bkg (
+ bkg_id integer not null AUTOINCREMENT,
+ conf_num varchar(24) not null,
+ bkg_dt date not null,
+ person_id integer not null,
+ prop_id integer not null,
+ arv_dt date not null,
+ dpt_dt date not null,
+ cancel_dt date null,
+ rooms integer not null,
+ rev_amt_usd float not null,
+ primary key (bkg_id)
+)SUFFIX
+EOF
+$table_index{test_hotel_bkg} = [
+ "create unique index test_hotel_bkg_ak1 on test_hotel_bkg (conf_num, bkg_dt)",
+];
+$table_autoid_column{test_hotel_bkg} = "bkg_id";
+$table_data{test_hotel_bkg} = [
+ "insert into test_hotel_bkg values (null,'AAA001','2008-11-01',1,1,'2008-12-01','2008-12-04',null,1,300)",
+];
+
+sub create_table {
+ &App::sub_entry if ($App::trace);
+ my ($rep, $table) = @_;
+ my $dbh = $rep->{dbh};
+
+ my $ddl = $table_schema{$table} || die "Schema not defined for table [$table]\n";
+ my $dbtype = $App::options{dbtype} || "mysql";
+
+ my $autoincrement = "";
+ my $suffix = "";
+ if ($dbtype eq "mysql") {
+ $autoincrement = " auto_increment";
+ $suffix = " ENGINE=InnoDB DEFAULT CHARSET=latin1";
+ }
+
+ my $double_type = ($dbtype eq "mysql") ? 'double' : 'float';
+ my $datetime_type = ($dbtype eq "mysql") ? 'datetime' : 'date';
+
+ $ddl =~ s/AUTOINCREMENT/$autoincrement/g;
+ $ddl =~ s/SUFFIX/$suffix/g;
+ $ddl =~ s/DOUBLE/$double_type/g;
+ $ddl =~ s/DATETIME/$datetime_type/g;
+ if ($dbtype eq "oracle") {
+ $ddl =~ s/varchar\(/varchar2\(/ig;
+ }
+ $dbh->do($ddl);
+
+ my $ddls = $table_index{$table};
+ if ($ddls) {
+ foreach $ddl (@$ddls) {
+ $dbh->do($ddl);
+ }
+ }
+
+ my ($autoid_column);
+ if ($dbtype eq "oracle") {
+ $autoid_column = $table_autoid_column{$table};
+ if ($autoid_column) {
+ $ddl = <<EOF;
+create sequence ${table}_seq start with 1 increment by 1 nomaxvalue nocycle cache 200
+EOF
+ $dbh->do($ddl);
+ $ddl = <<EOF;
+CREATE OR REPLACE TRIGGER tib_$table
+BEFORE INSERT ON $table
+FOR EACH ROW
+WHEN (new.$autoid_column IS NULL or new.$autoid_column = 0)
+BEGIN
+ SELECT ${table}_seq.NEXTVAL
+ INTO :new.$autoid_column
+ FROM DUAL;
+END tib_${table};
+EOF
+ $dbh->do($ddl);
+ }
+ }
+
+ &App::sub_exit() if ($App::trace);
+}
+
+sub drop_table {
+ &App::sub_entry if ($App::trace);
+ my ($rep, $table) = @_;
+ my $dbh = $rep->{dbh};
+
+ my $dbtype = $App::options{dbtype} || "mysql";
+
+ eval { $dbh->do("drop table $table"); };
+ if ($dbtype eq "oracle") {
+ eval { $dbh->do("drop sequence ${table}_seq"); };
+ }
+
+ &App::sub_exit() if ($App::trace);
+}
+
+sub populate_table {
+ &App::sub_entry if ($App::trace);
+ my ($rep, $table) = @_;
+ my $dbh = $rep->{dbh};
+ my $data = $table_data{$table};
+ if ($data) {
+ foreach my $sql (@$data) {
+ $dbh->do($sql);
+ }
+ }
+ &App::sub_exit() if ($App::trace);
+}
+
+1;
Added: p5ee/trunk/App-Repository/t/SO-SQLTranslator.t
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/t/SO-SQLTranslator.t Mon Nov 10 15:23:53 2008
@@ -0,0 +1,100 @@
+#!/usr/local/bin/perl -w
+
+use App::Options (
+ options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
+ option => {
+ dbclass => { default => "App::Repository::MySQL", },
+ dbdriver => { default => "mysql", },
+ dbhost => { default => "localhost", },
+ dbname => { default => "test", },
+ dbuser => { default => "", },
+ dbpass => { default => "", },
+ },
+);
+
+use Test::More qw(no_plan);
+use lib "../App-Context/lib";
+use lib "../../App-Context/lib";
+use lib "lib";
+use lib "../lib";
+
+use App;
+use App::Repository;
+use strict;
+
+if (!$App::options{dbuser}) {
+ ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
+ exit(0);
+}
+
+my $context = App->context(
+ conf_file => "",
+ conf => {
+ SessionObject => {
+ base_translator => {
+ class => "App::SessionObject::SQLTranslator",
+ },
+ mysql2oracle => {
+ class => "App::SessionObject::SQLTranslator::MySQLToOracle",
+ },
+ },
+ },
+ debug_sql => $App::options{debug_sql},
+ trace => $App::options{trace},
+);
+
+{
+ my ($sql, $translated_sql, $expected_sql);
+ my $base_translator = $context->session_object("base_translator");
+ my $mysql2oracle = $context->session_object("mysql2oracle");
+
+ is(ref($base_translator), "App::SessionObject::SQLTranslator", "base_translator is a App::SessionObject::SQLTranslator");
+ is(ref($mysql2oracle), "App::SessionObject::SQLTranslator::MySQLToOracle", "mysql2oracle is a App::SessionObject::SQLTranslator::MySQLToOracle");
+
+ #######################################################################################
+ $sql = "select {datediff(arv_dt, eff_dt)} from res";
+
+ $translated_sql = $base_translator->translate($sql);
+ $expected_sql = "select datediff(arv_dt,eff_dt) from res";
+ is($translated_sql, $expected_sql, "translate datediff() : base");
+
+ $translated_sql = $mysql2oracle->translate($sql);
+ $expected_sql = "select arv_dt - eff_dt from res";
+ is($translated_sql, $expected_sql, "translate datediff() : oracle");
+
+ #######################################################################################
+ $sql = "select {25% 7}, {from_days( 45683)}, {concat('A' , foo)} from res";
+
+ $translated_sql = $base_translator->translate($sql);
+ $expected_sql = "select 25%7, from_days(45683), concat('A',foo) from res";
+ is($translated_sql, $expected_sql, "translate mod, from_days, concat : base");
+
+ $translated_sql = $mysql2oracle->translate($sql);
+ $expected_sql = "select mod(25,7), 45683, 'A'||foo from res";
+ is($translated_sql, $expected_sql, "translate mod, from_days, concat : oracle");
+
+ #######################################################################################
+ $sql = "select {if(foo=1, 0, conf_num)} from res where foo=1";
+
+ $translated_sql = $base_translator->translate($sql);
+ $expected_sql = "select if(foo = 1,0,conf_num) from res where foo=1";
+ is($translated_sql, $expected_sql, "translate if() : base");
+
+ $translated_sql = $mysql2oracle->translate($sql);
+ $expected_sql = "select case when foo = 1 then 0 else conf_num end from res where foo=1";
+ is($translated_sql, $expected_sql, "translate if() : oracle");
+
+ exit(0);
+ #######################################################################################
+ $sql = "select {quarter(res.eff_dt)}, {year(eff_dt)}, {week('2008-10-19')}, {dayofweek(res.eff_dt + 1)}, {quarter(dateadd(res.eff_dt, 3))} from res where foo=1";
+
+ $translated_sql = $base_translator->translate($sql);
+ $expected_sql = "select datediff(arv_dt,eff_dt) from res";
+ is($translated_sql, $expected_sql, "translate datediff() : base");
+
+ $translated_sql = $mysql2oracle->translate($sql);
+ $expected_sql = "select arv_dt - eff_dt from res";
+ is($translated_sql, $expected_sql, "translate datediff() : oracle");
+
+}
+