Author: spadkins
Date: Fri Nov 21 08:40:53 2008
New Revision: 12093
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-insert-ora.t
p5ee/trunk/App-Repository/t/DBI-insert.t
Log:
allow insert() to use hashes. fix the Oracle DSN
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 Fri Nov 21 08:40:53 2008
@@ -1994,6 +1994,21 @@
my $dbh = $self->{dbh};
my $retval = 0;
+ my ($hashcols, $hashrow, $ref);
+ $ref = ref($cols);
+ if ($ref && $ref ne "ARRAY") {
+ $hashcols = $cols;
+ $cols = [ keys %$hashcols ];
+ }
+ $ref = ref($row);
+ if (! defined $row) {
+ $row = [ @[EMAIL PROTECTED] ] if ($hashcols);
+ }
+ elsif ($ref && $ref ne "ARRAY") {
+ $hashrow = $row;
+ $row = [ @[EMAIL PROTECTED] ];
+ }
+
my $sql = $self->_mk_insert_row_sql($table, $cols, undef, $options);
my ($last_inserted_id, $is_last_inserted_id_returned_from_insert,
$last_inserted_id_column);
@@ -2017,7 +2032,7 @@
my $context = $self->{context};
my $context_options = $context->{options};
my $debug_sql = $context_options->{debug_sql};
- my ($timer, $elapsed_time);
+ my ($timer, $elapsed_time, $bind_values);
my $loglevel = 1;
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
@@ -2082,14 +2097,14 @@
$retval = $self->_update($table, $key_idx, $cols, $row);
if (!$retval) {
$loglevel = 3;
- my $bind_values = join("|", map { defined $_ ? $_ :
"undef" } @$row);
+ $bind_values = join("|", map { defined $_ ? $_ :
"undef" } @$row);
$self->{context}->log({level=>$loglevel},
"App-Repository Exception in _insert_row(): update failed to find row after
failed insert\nBIND VALUES: [$bind_values]\nSQL: $sql");
die $e;
}
}
else {
$loglevel = 3;
- my $bind_values = join("|", map { defined $_ ? $_ :
"undef" } @$row);
+ $bind_values = join("|", map { defined $_ ? $_ : "undef" }
@$row);
$self->{context}->log({level=>$loglevel}, "DBI Exception
(fail) in _insert_row(): ${e}BIND VALUES: [$bind_values]\nSQL: $sql");
die $e;
}
@@ -2122,14 +2137,14 @@
my $context_options = $context->{options};
my $debug_sql = $context_options->{debug_sql};
my $explain_sql = $context_options->{explain_sql};
- my ($timer, $elapsed_time);
+ my ($timer, $elapsed_time, $bind_values, $row);
my $loglevel = 1;
if ($debug_sql) {
$timer = $self->_get_timer();
}
if (ref($rows) eq "ARRAY") {
$sql = $self->_mk_insert_row_sql($table, $cols);
- foreach my $row (@$rows) {
+ foreach $row (@$rows) {
if ($explain_sql) {
$self->explain_sql($sql);
}
@@ -2166,7 +2181,7 @@
};
if (my $e = $@) { # Log the error message with the SQL and
rethrow the exception
$loglevel = $self->is_duplicate_key_error($e) ? 3 : 1;
- my $bind_values = join("|", map { defined $_ ? $_ :
"undef" } @$row);
+ $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 $e;
}
@@ -2195,7 +2210,7 @@
while (1) {
$rows = $self->_read_rows_from_file($fh, $cols, \%options);
last if ($#$rows == -1);
- foreach my $row (@$rows) {
+ foreach $row (@$rows) {
if ($debug_sql) {
print $App::DEBUG_FILE "DEBUG_SQL: _insert_rows()\n";
print $App::DEBUG_FILE "DEBUG_SQL: bind vars [",
join("|",map { defined $_ ? $_ : "undef" } @$row), "]\n";
@@ -2212,7 +2227,7 @@
};
if (my $e = $@) { # Log the error message with the SQL
and rethrow the exception
$loglevel = $self->is_duplicate_key_error($e) ? 3 : 1;
- my $bind_values = join("|", map { defined $_ ? $_ :
"undef" } @$row);
+ $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 $e;
}
@@ -3086,7 +3101,9 @@
sub _last_inserted_id {
my ($self, $table) = @_;
- return $self->{last_inserted_id};
+ my $id = $self->{last_inserted_id} || die "No recently inserted id is
available";
+ delete $self->{last_inserted_id};
+ return($id);
}
# $pk_idx = $rep->_key_idx($table, [EMAIL PROTECTED], [EMAIL PROTECTED]);
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 Fri Nov 21
08:40:53 2008
@@ -1,6 +1,6 @@
######################################################################
-## File: $Id: MySQL.pm 10474 2008-01-04 19:09:48Z spadkins $
+## File: $Id: Oracle.pm 10474 2008-01-04 19:09:48Z spadkins $
######################################################################
use App::Repository::DBI;
@@ -15,11 +15,11 @@
=head1 NAME
-App::Repository::MySQL - a MySQL database, accessed through the Repository
interface
+App::Repository::Oracle - a Oracle database, accessed through the Repository
interface
=head1 SYNOPSIS
- use App::Repository::MySQL;
+ use App::Repository::Oracle;
(see man pages for App::Repository and App::Repository::DBI for additional
methods)
@@ -29,7 +29,7 @@
=head1 DESCRIPTION
-The App::Repository::MySQL class encapsulates all access to a MySQL database.
+The App::Repository::Oracle class encapsulates all access to an Oracle
database.
=cut
@@ -62,17 +62,7 @@
my $dbschema = $self->{dbschema};
my $dbioptions = $self->{dbioptions};
- die "ERROR: missing DBI driver and/or db name [$dbdriver,$dbname] in
configuration.\n"
- if (!$dbdriver || !$dbname);
-
- # NOTE: mysql_client_found_rows=true is important for the following
condition.
- # If an update is executed against a row that exists, but its values
do not change,
- # MySQL does not ordinarily report this as a row that has been
affected by the
- # statement. However, we occasionally need to know if the update
found the row.
- # We really don't care if the values were changed or not. To get this
behavior,
- # we need to set this option.
-
- $dsn = "dbi:${dbdriver}:sid=${dbname}";
+ $dsn = "dbi:${dbdriver}:${dbname}"; # NOTE: a blank ${dbname} is
allowed for an Oracle dsn
$dsn .= ";host=$dbhost" if ($dbhost);
$dsn .= ";port=$dbport" if ($dbport);
$dsn .= ";$dbioptions" if ($dbioptions);
@@ -399,7 +389,7 @@
sub explain_sql {
my ($self, $sql) = @_;
my $dbh = $self->{dbh};
- # NOTE: MySQL "explain" only works for "select".
+ # TODO: Make this work for Oracle
# We convert "update" and "delete" to "select" to explain them.
if (defined $dbh) {
if ($sql =~ s/^delete/select */is) {
Modified: p5ee/trunk/App-Repository/t/DBI-insert-ora.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-insert-ora.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-insert-ora.t Fri Nov 21 08:40:53 2008
@@ -65,6 +65,8 @@
ok(1, "These tests are only for Oracle");
}
else {
+ my ($id, $nrows);
+ my ($expect_sql, $sql);
ok($db->_insert_row("test_person",
["age","first_name","gender","state"],
[39,"stephen", "M","GA"]),
"insert row (primary key included)");
@@ -77,15 +79,37 @@
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");
+
+ $expect_sql = <<EOF;
+insert into test_person
+ (age,
+ first_name,
+ gender,
+ state)
+values
+ (?,
+ ?,
+ ?,
+ ?)
+EOF
+ ok($db->_insert_row("test_person",
["age","first_name","gender","state"],
+ { age => 1, first_name => "christine", gender => "F", state =>
"GA" }),
+ "insert again (cols, values as hash)");
+ is($db->{sql}, $expect_sql, "insert row. sql ok");
+ $expect_sql .= " returning person_id into ?\n";
+
+ $nrows = $db->_insert_row("test_person",
["age","first_name","gender","state"],
+ { age => 45, first_name => "tim", gender => "M", state => "GA" },
{last_inserted_id => 1});
+ ok($nrows, "insert again (cols, values as hash, returning
last_inserted_id)");
+ $id = $db->_last_inserted_id("test_person");
+ is($id, 6, "insert again (cols, values as hash, returning
last_inserted_id) got id=6");
+ is($db->{sql}, $expect_sql, "insert again (cols, values as hash,
returning last_inserted_id) sql ok");
+
+ $nrows = $db->_insert_row("test_person",
+ { age => 39, first_name => "keith", gender => "M", state => "GA"
}, undef, {last_inserted_id => 1}),
+ ok($nrows, "insert again (cols/values as single hash, returning
last_inserted_id)");
+ $id = $db->_last_inserted_id("test_person");
+ is($id, 7, "insert again (cols/values as single hash, returning
last_inserted_id) got id=7");
ok($db->insert("test_person", {
# person_id => 8,
@@ -169,7 +193,6 @@
[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)
@@ -224,7 +247,6 @@
# 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 });
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 Fri Nov 21 08:40:53 2008
@@ -65,6 +65,8 @@
ok(1, "These tests are not for Oracle");
}
else {
+ my ($nrows, $id);
+ my ($expect_sql, $sql);
ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
[1,39,"stephen", "M","GA"]),
"insert row (primary key included)");
@@ -77,15 +79,46 @@
ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
[0, 3,"paul", "M","GA"]),
"insert row (primary key included, 0)");
- ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
- [5, 1,"christine","F","GA"]),
- "insert again");
- ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
- [6,45,"tim", "M","GA"]),
- "insert again");
- ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
- [7,39,"keith", "M","GA"]),
- "insert again");
+# ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
+# [5, 1,"christine","F","GA"]),
+# "insert again");
+# ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
+# [6,45,"tim", "M","GA"]),
+# "insert again");
+# ok($db->_insert_row("test_person",
["person_id","age","first_name","gender","state"],
+# [7,39,"keith", "M","GA"]),
+# "insert again");
+
+ $expect_sql = <<EOF;
+insert into test_person
+ (age,
+ first_name,
+ gender,
+ state)
+values
+ (?,
+ ?,
+ ?,
+ ?)
+EOF
+ ok($db->_insert_row("test_person",
["age","first_name","gender","state"],
+ { age => 1, first_name => "christine", gender => "F", state =>
"GA" }),
+ "insert again (cols, values as hash)");
+ is($db->{sql}, $expect_sql, "insert row. sql ok");
+
+ $nrows = $db->_insert_row("test_person",
["age","first_name","gender","state"],
+ { age => 45, first_name => "tim", gender => "M", state => "GA" },
{last_inserted_id => 1});
+ ok($nrows, "insert again (cols, values as hash, returning
last_inserted_id)");
+ $id = $db->_last_inserted_id("test_person");
+ is($id, 6, "insert again (cols, values as hash, returning
last_inserted_id) got id=6");
+ is($db->{sql}, $expect_sql, "insert again (cols, values as hash,
returning last_inserted_id) sql ok");
+
+ $nrows = $db->_insert_row("test_person",
+ { age => 39, first_name => "keith", gender => "M", state => "GA"
}, undef, {last_inserted_id => 1}),
+ ok($nrows, "insert again (cols/values as single hash, returning
last_inserted_id)");
+ $id = $db->_last_inserted_id("test_person");
+ is($id, 7, "insert again (cols/values as single hash, returning
last_inserted_id) got id=7");
+
ok($db->insert("test_person", {
person_id => 8,
age => 35,
@@ -169,7 +202,6 @@
[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)
@@ -222,7 +254,6 @@
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 });
