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(): $@$sql");
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(): $@$sql");
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(): $@BIND 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]: $@BIND 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]: $@BIND 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(): $@SQL: $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(): $@SQL: $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(): $@SQL: $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(): $@SQL: $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(): $@$sql");
+ 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, \@current_rows, $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)");
}
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.