Author: spadkins
Date: Mon Aug 20 08:36:37 2007
New Revision: 9863
Modified:
p5ee/trunk/App-Repository/CHANGES
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm
p5ee/trunk/App-Repository/t/DBI-insert.t
Log:
better logging of SQL errors to include the SQL statement
Modified: p5ee/trunk/App-Repository/CHANGES
==============================================================================
--- p5ee/trunk/App-Repository/CHANGES (original)
+++ p5ee/trunk/App-Repository/CHANGES Mon Aug 20 08:36:37 2007
@@ -2,6 +2,25 @@
# CHANGE LOG
#########################################
+0.966 (not yet released)
+ x App::Repository::evaluate_expression(): can now supply defaults for null
columns
+ x App::Repository::evaluate_expression(): added column defaults
+ x App::Repository::evaluate_expression(): fixed to be more efficient and to
handle defaults on columns which are expressions themselves
+ x App::Repository::evaluate_expression(): fixed a bug when evaluating
expressions of columns that end in digits
+ x App::Repository::evaluate_expression(): fixed a bug where using a hashref
instead of arrayref
+ x App::Repository::evaluate_constant_expression(): fix for div 0 and sci
notation
+ x App::Repository::summarize_rows(): can now summarize columns which have
alternate aggregate levels
+ x App::Repository::summarize_rows(): produce an undef if all values that are
summed are undef
+ x App::Repository::last_inserted_id($table): new method (without underscore)
now takes $table param, works on redirects
+ x App::Repository - changed begin_work(), commit(), rollback() to consult an
{in_transaction} attribute
+ This does something sensible if begin_work() is called within a transaction
or a commit()/rollback() is outside a transaction
+ x App::Repository - get_table_def(): enable the 'table_def' option in
get_rows() to override or supplement table_def entries already in the config
+ x App::Repository::* - change debug_sql and all other debug-style output to
obey the debug_file option
+ x App::Repository::DBI - get_phys_column_names(): new method
+ x App::Repository::ValueDomain - use App::Repository::DBI::_do instead of DBI
methods
+ x App::Repository::_insert_row(): handle update on duplicate
+ x App::Repository::* - improved logging of database errors when they occur
(includes the SQL statement that failed)
+
0.965
x can now configure multiple databases (App::Repository::DBI) from app.conf
(xyz.dbhost, xyz.dbname, ... for rep "xyz")
x full redirection to other tables or other tables in other repositories is
done
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 Mon Aug 20 08:36:37 2007
@@ -186,11 +186,11 @@
if ($@) {
delete $self->{dbh};
if ($@ =~ /Lost connection/ || $@ =~ /server has gone away/) {
- $self->{context}->log("DBI Exception (retrying) in
_connect(): $@");
+ $self->{context}->log({level=>1},"DBI Exception (retrying)
in _connect(): $@");
sleep(1);
}
else {
- $self->{context}->log("DBI Exception (fail) in _connect():
$@");
+ $self->{context}->log({level=>1},"DBI Exception (fail) in
_connect(): $@");
die $@;
}
}
@@ -416,14 +416,14 @@
if ($@) {
$row = undef;
if ($@ =~ /Lost connection/ || $@ =~ /server has gone away/) {
- $self->{context}->log("DBI Exception (retrying) in _get_row():
$@");
+ $self->{context}->log({level=>1},"DBI Exception (retrying) in
_get_row(): $@");
$self->_disconnect();
sleep(1);
$self->_connect();
$dbh = $self->{dbh};
}
else {
- $self->{context}->log("DBI Exception (fail) in _get_row():
$@");
+ $self->{context}->log({level=>1},"DBI Exception (fail) in
_get_rows(): [EMAIL PROTECTED]");
die $@;
}
}
@@ -485,13 +485,13 @@
if ($@) {
$rows = [];
if ($@ =~ /Lost connection/ || $@ =~ /server has gone away/) {
- $self->{context}->log("DBI Exception (retrying) in
_get_rows(): $@");
+ $self->{context}->log({level=>1},"DBI Exception (retrying) in
_get_rows(): $@");
$self->_disconnect();
sleep(1);
$self->_connect();
}
else {
- $self->{context}->log("DBI Exception (fail) in _get_rows():
$@");
+ $self->{context}->log({level=>1},"DBI Exception (fail) in
_get_rows(): [EMAIL PROTECTED]");
die $@;
}
}
@@ -2177,6 +2177,7 @@
my $context_options = $self->{context}{options};
my $debug_sql = $context_options->{debug_sql};
my ($timer, $elapsed_time);
+ my $loglevel = 1;
if ($debug_sql) {
$timer = $self->_get_timer();
print $App::DEBUG_FILE "DEBUG_SQL: insert()\n";
@@ -2186,7 +2187,18 @@
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
}
- $retval = $dbh->do($sql, undef, @$row) if (defined $dbh);
+ if (defined $dbh) {
+ eval {
+ $retval = $dbh->do($sql, undef, @$row);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ };
+ if ($@) { # Log the error message with the SQL and rethrow the
exception
+ my $bind_values = join("|", map { defined $_ ? $_ : "undef" }
@$row);
+ $loglevel = 3 if ($@ =~ /duplicate/i);
+ $self->{context}->log({level=>$loglevel}, "DBI Exception (fail) in
_insert_row(): [EMAIL PROTECTED] VALUES: [$bind_values]\nSQL: $sql");
+ die $@;
+ }
+ }
if ($debug_sql && $debug_sql >= 2) {
$elapsed_time = $self->_read_timer($timer);
print $App::DEBUG_FILE "DEBUG_SQL: retval [$retval] ($elapsed_time
sec) $DBI::errstr\n";
@@ -2213,6 +2225,7 @@
my $debug_sql = $context_options->{debug_sql};
my $explain_sql = $context_options->{explain_sql};
my ($timer, $elapsed_time);
+ my $loglevel = 1;
if ($debug_sql) {
$timer = $self->_get_timer();
}
@@ -2227,7 +2240,18 @@
if ($explain_sql) {
$self->explain_sql($sql);
}
- $retval = $dbh->do($sql, undef, @$row);
+ if (defined $dbh) {
+ eval {
+ $retval = $dbh->do($sql, undef, @$row);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain old
"0"
+ };
+ if ($@) { # Log the error message with the SQL and rethrow
the exception
+ $loglevel = ($@ =~ /duplicate/i) ? 3 : 1;
+ my $bind_values = join("|", map { defined $_ ? $_ :
"undef" } @$row);
+ $self->{context}->log({level=>$loglevel}, "DBI Exception
(fail) in _insert_rows() [ARRAY]: [EMAIL PROTECTED] VALUES:
[$bind_values]\nSQL: $sql");
+ die $@;
+ }
+ }
if ($debug_sql) {
print $App::DEBUG_FILE "DEBUG_SQL: retval [$retval]
$DBI::errstr\n";
print $App::DEBUG_FILE "\n";
@@ -2261,7 +2285,18 @@
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
}
- $retval = $dbh->do($sql, undef, @$row);
+ if (defined $dbh) {
+ eval {
+ $retval = $dbh->do($sql, undef, @$row);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain
old "0"
+ };
+ if ($@) { # Log the error message with the SQL and
rethrow the exception
+ $loglevel = ($@ =~ /duplicate/i) ? 3 : 1;
+ my $bind_values = join("|", map { defined $_ ? $_ :
"undef" } @$row);
+ $self->{context}->log({level=>$loglevel}, "DBI
Exception (fail) in _insert_rows() [FILE]: [EMAIL PROTECTED] VALUES:
[$bind_values]\nSQL: $sql");
+ die $@;
+ }
+ }
if ($debug_sql) {
print $App::DEBUG_FILE "DEBUG_SQL: retval [$retval]
$DBI::errstr\n";
print $App::DEBUG_FILE "\n";
@@ -2305,8 +2340,18 @@
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
}
- my $retval = $self->{dbh}->do($sql);
- $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ my $retval = 0;
+ my $dbh = $self->{dbh};
+ if (defined $dbh) {
+ eval {
+ $retval = $dbh->do($sql);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ };
+ if ($@) { # Log the error message with the SQL and rethrow the
exception
+ $self->{context}->log({level=>1},"DBI Exception (fail) in
_delete(): [EMAIL PROTECTED]: $sql");
+ die $@;
+ }
+ }
if ($debug_sql) {
$elapsed_time = $self->_read_timer($timer);
print $App::DEBUG_FILE "DEBUG_SQL: retval [$retval] ($elapsed_time
sec) $DBI::errstr\n";
@@ -2340,8 +2385,18 @@
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
}
- my $retval = $self->{dbh}->do($sql);
- $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ my $retval = 0;
+ my $dbh = $self->{dbh};
+ if (defined $dbh) {
+ eval {
+ $retval = $dbh->do($sql);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ };
+ if ($@) { # Log the error message with the SQL and rethrow the
exception
+ $self->{context}->log({level=>1},"DBI Exception (fail) in
_update(): [EMAIL PROTECTED]: $sql");
+ die $@;
+ }
+ }
if ($debug_sql) {
$elapsed_time = $self->_read_timer($timer);
print $App::DEBUG_FILE "DEBUG_SQL: retval [$retval] ($elapsed_time
sec) $DBI::errstr\n";
@@ -2359,8 +2414,6 @@
$self->{error} = "";
my $sql = $self->_mk_delete_row_sql(@_);
$self->{sql} = $sql;
- my $dbh = $self->{dbh};
- my $retval = 0;
my $context_options = $self->{context}{options};
my $debug_sql = $context_options->{debug_sql};
@@ -2373,8 +2426,18 @@
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
}
- $retval = $dbh->do($sql) if (defined $dbh);
- $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ my $retval = 0;
+ my $dbh = $self->{dbh};
+ if (defined $dbh) {
+ eval {
+ $retval = $dbh->do($sql);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ };
+ if ($@) { # Log the error message with the SQL and rethrow the
exception
+ $self->{context}->log({level=>1},"DBI Exception (fail) in
_delete_row(): [EMAIL PROTECTED]: $sql");
+ die $@;
+ }
+ }
if ($debug_sql) {
$elapsed_time = $self->_read_timer($timer);
print $App::DEBUG_FILE "DEBUG_SQL: retval [$retval] ($elapsed_time
sec) $DBI::errstr\n";
@@ -2392,8 +2455,6 @@
$self->{error} = "";
my $sql = $self->_mk_delete_rows_sql(@_);
$self->{sql} = $sql;
- my $dbh = $self->{dbh};
- my $retval = 0;
my $context_options = $self->{context}{options};
my $debug_sql = $context_options->{debug_sql};
@@ -2406,7 +2467,18 @@
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
}
- $retval = $dbh->do($sql) if (defined $dbh);
+ my $retval = 0;
+ my $dbh = $self->{dbh};
+ if (defined $dbh) {
+ eval {
+ $retval = $dbh->do($sql);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ };
+ if ($@) { # Log the error message with the SQL and rethrow the
exception
+ $self->{context}->log({level=>1},"DBI Exception (fail) in
_delete_rows(): [EMAIL PROTECTED]: $sql");
+ die $@;
+ }
+ }
$retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
if ($debug_sql) {
$elapsed_time = $self->_read_timer($timer);
@@ -2439,14 +2511,20 @@
$self->explain_sql($sql);
}
if (defined $dbh) {
- if ($sql =~ /^select/i) {
- $retval = $dbh->selectall_arrayref($sql);
- }
- else {
- $retval = $dbh->do($sql);
- $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
- }
$self->{sql} = $sql;
+ eval {
+ if ($sql =~ /^select/i) {
+ $retval = $dbh->selectall_arrayref($sql);
+ }
+ else {
+ $retval = $dbh->do($sql);
+ $retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ }
+ };
+ if ($@) { # Log the error message with the SQL and rethrow the
exception
+ $self->{context}->log({level=>1},"DBI Exception (fail) in _do():
[EMAIL PROTECTED]");
+ die $@;
+ }
}
if ($debug_sql) {
my $nrows = 0;
Modified: p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm Mon Aug 20
08:36:37 2007
@@ -283,7 +283,7 @@
&App::sub_entry if ($App::trace);
my ($self, $table, $cols, $rows, $options) = @_;
$self->{error} = "";
- my ($sql, $retval);
+ my ($sql, $retval, $nrows_this_insert);
my $dbh = $self->{dbh};
return 0 if (!defined $dbh);
@@ -306,6 +306,7 @@
$rownum2 = $rownum + $maxrows - 1;
$rownum2 = $#$rows if ($rownum2 > $#$rows);
@current_rows = @{$rows}[($rownum .. $rownum2)];
+ $nrows_this_insert = $#current_rows + 1;
$sql = $self->_mk_insert_rows_sql($table, $cols, [EMAIL
PROTECTED], $options);
if ($debug_sql) {
@@ -318,6 +319,16 @@
print $App::DEBUG_FILE "\n";
}
+ # The MySQL "insert ... on duplicate key update ..." statement
returns 2 rows affected
+ # when the insert gets a collision and causes an update. So we
have to make this
+ # adjustment. I don't know if it affects the "replace ..."
statement in a similar way,
+ # but I figure this can't hurt.
+ if ($options->{update} || $options->{replace}) {
+ if ($retval > $nrows_this_insert) {
+ $retval = $nrows_this_insert;
+ }
+ }
+
$nrows += $retval;
$rownum += $maxrows;
}
Modified: p5ee/trunk/App-Repository/t/DBI-insert.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-insert.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-insert.t Mon Aug 20 08:36:37 2007
@@ -243,13 +243,13 @@
$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, 14, "insert_rows(): 7 rows (14 affected), bulk replace");
+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, 14, "insert_rows(): 7 rows (14 affected), bulk insert/update");
+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, 14, "insert_rows(): 7 rows (14 affected), bulk replace (4 at a
time)");
+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, 14, "insert_rows(): 7 rows (14 affected), bulk insert/update (4 at
a time)");
+is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update (4 at a time)");
}
