Author: spadkins
Date: Thu Jan 8 21:42:01 2009
New Revision: 12314
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm
p5ee/trunk/App-Repository/t/DBI-select-join.t
p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm
Log:
fixed bug which caused joins to be out of order based on the dependencies
Modified: p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm Thu Jan 8 21:42:01 2009
@@ -3089,6 +3089,49 @@
}
}
+ { # ORDER THE TABLE ALIASES BASED ON DEPENDENCIES
+ my $tablealiases = $table_def->{tablealiases};
+ my $tablealias = $table_def->{tablealias};
+ # print STDERR "ALIASES [$table] BEFORE : [...@$tablealiases]\n";
+ my @orig_tablealiases = @$tablealiases;
+ my (@ordered_tablealiases, $alias, %alias_seen, $dep, $dependencies,
%alias_tries);
+ my $max_tries = $#$tablealiases + 1;
+ while ($#orig_tablealiases > -1) {
+ $alias = shift(@orig_tablealiases);
+ if ($alias_seen{$alias}) {
+ # do nothing (throw this duplicate copy away)
+ }
+ else {
+ $dependencies = $tablealias->{$alias}{dependencies};
+ if (!$dependencies || ref($dependencies) ne "ARRAY") {
+ push(@ordered_tablealiases, $alias);
+ $alias_seen{$alias} = 1;
+ }
+ else {
+ my $dependencies_satisfied = 1;
+ $alias_tries{$alias} = 0 if (! defined
$alias_tries{$alias});
+ if ($alias_tries{$alias} <= $max_tries) {
+ foreach my $dep (@$dependencies) {
+ if (!$alias_seen{$dep}) { # $alias depends on
another alias which has never been seen
+ push(@orig_tablealiases, $alias); # so put it
back on the end
+ $dependencies_satisfied = 0;
+ last;
+ }
+ }
+ }
+ $alias_tries{$alias}++;
+ if ($dependencies_satisfied) {
+ push(@ordered_tablealiases, $alias);
+ $alias_seen{$alias} = 1;
+ }
+ }
+ }
+ }
+ $tablealiases = \...@ordered_tablealiases;
+ $table_def->{tablealiases} = $tablealiases;
+ # print STDERR "ALIASES [$table] AFTER : [...@$tablealiases]\n";
+ }
+
#if ($App::DEBUG >= 2 && $self->{context}->dbg(2)) {
# print $App::DEBUG_FILE "Table Metadata: $table\n";
# my $d = Data::Dumper->new([ $table_def ], [ "table_def" ]);
Modified: p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm Thu Jan 8
21:42:01 2009
@@ -605,52 +605,52 @@
&App::sub_exit() if ($App::trace);
}
-#+----+-------------+-------+-------+-------------------------------------+-------------------+---------+-------------+------+-------+
-#| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
-#+----+-------------+-------+-------+-------------------------------------+-------------------+---------+-------------+------+-------+
-#| 1 | SIMPLE | t1 | const | hotel_prop_ds_ak1,hotel_prop_ds_ie1 |
hotel_prop_ds_ak1 | 9 | const,const | 1 | |
-#+----+-------------+-------+-------+-------------------------------------+-------------------+---------+-------------+------+-------+
-sub TBDexplain_sql {
+#SQL> explain plan for select * from hotel_brand_grp where brand_grp_cd like
'H%';
+#
+#Explained.
+#
+#SQL> SELECT * from table (dbms_xplan.display);
+#
+#PLAN_TABLE_OUTPUT
+#--------------------------------------------------------------------------------------------------------------------------------------------
+#Plan hash value: 980249155
+#
+#---------------------------------------------------------------------------------------------------
+#| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
+#---------------------------------------------------------------------------------------------------
+#| 0 | SELECT STATEMENT | | 2 | 84 |
1 (0)| 00:00:01 |
+#| 1 | TABLE ACCESS BY INDEX ROWID| HOTEL_BRAND_GRP | 2 | 84 |
1 (0)| 00:00:01 |
+#|* 2 | INDEX RANGE SCAN | HOTEL_BRAND_GRP_AK1 | 2 | |
1 (0)| 00:00:01 |
+#---------------------------------------------------------------------------------------------------
+#
+#Predicate Information (identified by operation id):
+#
+#PLAN_TABLE_OUTPUT
+#--------------------------------------------------------------------------------------------------------------------------------------------
+#---------------------------------------------------
+#
+# 2 - access("BRAND_GRP_CD" LIKE 'H%')
+# filter("BRAND_GRP_CD" LIKE 'H%')
+#
+#15 rows selected.
+
+sub explain_sql {
my ($self, $sql) = @_;
my $dbh = $self->{dbh};
- # NOTE: Oracle "explain" only works for "select".
- # We convert "update" and "delete" to "select" to explain them.
+ # NOTE: Oracle "explain" only works for "select", "update", and "delete".
if (defined $dbh) {
- if ($sql =~ s/^delete/select */is) {
- # do nothing
- }
- elsif ($sql =~ s/^update\s+(.*)\sset\s+.*\swhere/select * from
$1\nwhere/is) {
- # do nothing
- }
- if ($sql =~ /^select/i) {
+ if ($sql =~ /^\s*(select|update|delete)/i) {
my ($rows, $posskeys, $key, $keylen);
eval {
- $rows = $dbh->selectall_arrayref("explain $sql");
+ $dbh->do("explain plan for $sql");
+ $rows = $dbh->selectall_arrayref("select * from table
(dbms_xplan.display)");
};
print $App::DEBUG_FILE "EXPLAIN_SQL: $DBI::errstr\n";
- if ($rows) {
- print $App::DEBUG_FILE
"+----+-------------+----------------------+-------+----------------------+---------+----------+\n";
- print $App::DEBUG_FILE "| id | select_type | table
| type | key | key_len | rows |\n";
- print $App::DEBUG_FILE
"+----+-------------+----------------------+-------+----------------------+---------+----------+\n";
- foreach my $row (@$rows) {
- $key = $row->[5];
- $keylen = length($key);
- if ($keylen > 21) {
- $key = substr($key,0,12) . ".." .
substr($key,$keylen-7,7);
- }
- printf($App::DEBUG_FILE "|%3s | %-12s| %-21s| %-6s|
%-21s|%8d |%9d | %s\n", @{$row}[0,1,2,3], $key, @{$row}[6,8]);
- }
- print $App::DEBUG_FILE
"+----+----------------------------------------------------------------------------------------+\n";
- print $App::DEBUG_FILE "| id | possible_keys/ref/extra\n";
- print $App::DEBUG_FILE
"+----+----------------------------------------------------------------------------------------+\n";
- foreach my $row (@$rows) {
- $key = $row->[5];
- $posskeys = $row->[4];
- $posskeys =~ s/\b($key)\b/[$key]/;
- printf($App::DEBUG_FILE "|%3s | posskeys: %s\n",
$row->[0], $posskeys);
- printf($App::DEBUG_FILE "|%3s | ref: %s; extra:
%s\n", @{$row}[0,7,9]);
- }
- print $App::DEBUG_FILE
"+---------------------------------------------------------------------------------------------+\n";
+ if ($rows && $#$rows == 0) {
+ print $App::DEBUG_FILE $rows->[0][0];
+ }
+ else {
+ print $App::DEBUG_FILE "EXPLAIN_SQL: Got unexpected result.\n";
}
}
else {
Modified: p5ee/trunk/App-Repository/t/DBI-select-join.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select-join.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select-join.t Thu Jan 8 21:42:01 2009
@@ -64,7 +64,8 @@
},
test_person => {
alias => "p",
- tablealiases => [ "p", "c", "ctry" ],
+ #tablealiases => [ "p", "c", "ctry", "ctyctry" ],
+ tablealiases => [ "ctyctry", "ctry", "c", "p", ],
tablealias => {
c => {
table => "test_city",
@@ -76,9 +77,15 @@
dependencies => [ "p" ],
joincriteria => "ctry.country = p.country",
},
+ ctyctry => {
+ table => "test_country",
+ dependencies => [ "c", "p", ],
+ joincriteria => "ctyctry.country = c.country",
+ },
},
column => {
country_nm => { dbexpr => "ctry.country_nm", },
+ city_country_nm => { dbexpr => "ctyctry.country_nm", },
gender => { alias => "gnd", },
},
},
@@ -914,6 +921,28 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): city->country. where
country_nm=JAPAN");
&check_select($sql,2);
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.city,
+ p.state,
+ p.age
+from test_person p
+ inner join test_city c on c.city_cd = p.city_cd
+ inner join test_country ctyctry on ctyctry.country = c.country
+where ctyctry.country_nm = 'United States'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): ensure that joins are
in order",
+ "test_person",
+ { city_country_nm => 'United States' },
+ ["first_name","last_name","city","state","age"]);
+$sql = $rep->_mk_select_joined_sql("test_person",
+ { city_country_nm => 'United States' },
+ ["first_name","last_name","city","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): ensure that joins are in
order");
+&check_select($sql,0);
+
exit(0); # XXX REMOVE EXIT HERE XXX
###########################################################################
Modified: p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm
==============================================================================
--- p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm (original)
+++ p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm Thu Jan 8 21:42:01 2009
@@ -57,6 +57,7 @@
state varchar(99) null,
zip varchar(10) null,
country char(2) null,
+ city_cd varchar(5) null,
home_phone varchar(99) null,
work_phone varchar(99) null,
email_address varchar(99) null,
@@ -202,6 +203,7 @@
state varchar(99) null,
zip varchar(10) null,
country char(2) null,
+ city_cd varchar(5) null,
home_phone varchar(99) null,
work_phone varchar(99) null,
email_address varchar(99) null,
