Author: spadkins
Date: Tue Nov 11 14:46:13 2008
New Revision: 12065
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-repobjects.t
p5ee/trunk/App-Repository/t/DBI-select.t
p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm
p5ee/trunk/App-Repository/t/app.conf.sample
Log:
latest for Oracle. last_insert_id now works.
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 Tue Nov 11 14:46:13 2008
@@ -1492,14 +1492,14 @@
$sql .= ")\n";
$values .= ")\n";
$sql .= $values;
- $sql .= $self->_mk_insert_row_suffix($table, $options);
&App::sub_exit($sql) if ($App::trace);
$sql;
}
-sub _mk_insert_row_suffix {
- my ($self, $table, $options) = @_;
- return("");
+sub _mk_last_inserted_id_suffix {
+ my ($self, $table, $last_inserted_id_column, $options) = @_;
+ my $suffix = "";
+ return($suffix);
}
sub _mk_insert_rows_sql {
@@ -1956,12 +1956,31 @@
sub _insert_row {
&App::sub_entry if ($App::trace);
my ($self, $table, $cols, $row, $options) = @_;
+
$self->{error} = "";
- my $sql = $self->_mk_insert_row_sql($table, $cols, undef, $options);
- $self->{sql} = $sql;
my $dbh = $self->{dbh};
my $retval = 0;
+ 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);
+ if ($options->{last_inserted_id}) {
+ my $primary_key = $self->{table}{$table}{primary_key};
+ if ($primary_key && $#$primary_key == 0) {
+ $last_inserted_id_column = $primary_key->[0];
+ $is_last_inserted_id_returned_from_insert =
$self->{is_last_inserted_id_returned_from_insert};
+ if ($is_last_inserted_id_returned_from_insert) {
+ $sql .= $self->_mk_last_inserted_id_suffix($table,
$last_inserted_id_column, $options);
+ #$row = [ @$row, \$last_inserted_id ];
+ }
+ delete $self->{last_inserted_id};
+ }
+ }
+
+ $self->{sql} = $sql;
+#print STDERR $sql;
+#print STDERR "1. lii=[$last_inserted_id]
liirfi=[$is_last_inserted_id_returned_from_insert]
liic=[$last_inserted_id_column]\n";
+
my $context = $self->{context};
my $context_options = $context->{options};
my $debug_sql = $context_options->{debug_sql};
@@ -1989,7 +2008,15 @@
$row->[$i] = $serializer->serialize($row->[$i]);
}
}
- $insert_sth->bind_param($i+1, undef, $sqltype ? {TYPE =>
$sqltype} : () );
+ $insert_sth->bind_param($i+1, $row->[$i], $sqltype ? {TYPE =>
$sqltype} : () );
+#print STDERR "insert_sth->bind_param(", $i+1, ", $row->[$i], $sqltype)
[$column]\n";
+ }
+#print STDERR "2. lii=[$last_inserted_id]
liirfi=[$is_last_inserted_id_returned_from_insert]
liic=[$last_inserted_id_column]\n";
+ if ($is_last_inserted_id_returned_from_insert) {
+ $sqltype = $column_defs->{$last_inserted_id_column}{sqltype};
+#print STDERR "insert_sth->bind_param_inout(", $#$cols+2, ",
\$last_inserted_id, $sqltype) [$last_inserted_id_column] BEFORE\n";
+ $insert_sth->bind_param_inout($#$cols+2, \$last_inserted_id,
$sqltype ? {TYPE => $sqltype} : () );
+#print STDERR "insert_sth->bind_param_inout(", $#$cols+2, ",
\$last_inserted_id, $sqltype) [$last_inserted_id_column] AFTER\n";
}
if ($debug_sql) {
$timer = $self->_get_timer();
@@ -1997,8 +2024,13 @@
print $App::DEBUG_FILE "DEBUG_SQL: bind vars [", join("|",map
{ defined $_ ? $_ : "undef" } @$row), "]\n";
print $App::DEBUG_FILE $sql;
}
- $retval = $insert_sth->execute(@$row);
+#print STDERR "BEFORE execute: last_inserted_id=[$last_inserted_id]\n";
+ $retval = $insert_sth->execute;
+#print STDERR "AFTER execute: last_inserted_id=[$last_inserted_id]\n";
$retval = 0 if ($retval == 0); # turn "0E0" into plain old "0"
+ if ($is_last_inserted_id_returned_from_insert && $retval) {
+ $self->{last_inserted_id} = $last_inserted_id;
+ }
};
if (my $e = $@) { # Log the error message with the SQL and rethrow
the exception
#print STDERR "###### EXCEPTION: $e\n";
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 Tue Nov 11
14:46:13 2008
@@ -40,6 +40,7 @@
$context->serializer("Repository_Oracle_BLOB", class =>
"App::Serializer::Scalar", pack_format => "H*");
$self->SUPER::_init2();
$self->{datetime_regexp} = '_dttm$' if (!defined $self->{datetime_regexp});
+ $self->{is_last_inserted_id_returned_from_insert} = 1;
&App::sub_exit() if ($App::trace);
}
@@ -122,17 +123,11 @@
}
}
-#sub _mk_insert_row_suffix {
-# my ($self, $table, $options) = @_;
-# my $suffix = "";
-# if ($options->{last_inserted_id}) {
-# my $primary_key = $self->{table}{$table}{primary_key};
-# if ($primary_key && $#$primary_key == 0) {
-# $suffix = " returning $primary_key->[0] into ?";
-# }
-# }
-# return($suffix);
-#}
+sub _mk_last_inserted_id_suffix {
+ my ($self, $table, $last_inserted_id_column, $options) = @_;
+ my $suffix = " returning $last_inserted_id_column into ?\n";
+ return($suffix);
+}
sub _load_table_metadata_from_source2 {
&App::sub_entry if ($App::trace);
Modified: p5ee/trunk/App-Repository/t/DBI-repobjects.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-repobjects.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-repobjects.t Tue Nov 11 14:46:13 2008
@@ -176,13 +176,11 @@
is($obj3->{age},$obj->{age}, "new.age seems ok");
is($obj3->{_key},$obj->{_key}, "new._key seems ok");
- if ($dbtype eq "mysql") {
- my $obj4 = $rep->new_object("test_person",{first_name => "christine",
gender => "F"});
- is($obj4->{first_name},"christine", "new.first_name (2) seems ok");
- is($obj4->{_key},8, "new._key is ok");
- is($obj4->{person_id},8, "new.person_id is ok");
- isa_ok($obj4, "App::RepositoryObject::Woman", "by new_object(),
christine");
- }
+ my $obj4 = $rep->new_object("test_person",{first_name => "christine",
gender => "F"});
+ is($obj4->{first_name},"christine", "new.first_name (2) seems ok");
+ is($obj4->{_key},8, "new._key is ok");
+ is($obj4->{person_id},8, "new.person_id is ok");
+ isa_ok($obj4, "App::RepositoryObject::Woman", "by new_object(),
christine");
}
{
Modified: p5ee/trunk/App-Repository/t/DBI-select.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select.t Tue Nov 11 14:46:13 2008
@@ -496,7 +496,7 @@
#&check_select($sql,0);
if ($dbtype eq 'mysql') {
-$expect_sql = <<EOF;
+ $expect_sql = <<EOF;
select
first_name,
last_name,
@@ -505,13 +505,9 @@
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;
+ $expect_sql = <<EOF;
select
first_name,
last_name,
@@ -521,11 +517,50 @@
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);
}
+$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);
+
+#{
+# my $expect_sql_template = <<EOF;
+#select
+# person_id
+#from test_personWHERE
+#order by person_idLIMIT
+#EOF
+# my @options = (
+# { startrow => 1, endrow => 1, eff_startrow => 1, eff_endrow => 1, },
+# );
+# my ($options);
+# for (my $i = 0; $i <= $#options; $i++) {
+# $options = {};
+# $options->{startrow} = $options[$i]{startrow} if (exists
$options[$i]{startrow});
+# $options->{endrow} = $options[$i]{endrow} if (exists
$options[$i]{endrow});
+# $expect_sql = $expect_sql_template;
+# if ($dbtype eq 'mysql') {
+# $expect_sql =~ s/WHERE//;
+# $expect_sql =~ s/LIMIT/\nlimit /;
+# }
+# }
+# 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,
Modified: p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm
==============================================================================
--- p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm (original)
+++ p5ee/trunk/App-Repository/t/RepositoryTestUtils.pm Tue Nov 11 14:46:13 2008
@@ -118,13 +118,13 @@
&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')");
+ $dbh->do("insert into test_person (age,first_name,gender,state) values
(39,'stephen', 'M','GA')");
+ $dbh->do("insert into test_person (age,first_name,gender,state) values
(37,'susan', 'F','GA')");
+ $dbh->do("insert into test_person (age,first_name,gender,state) values (
6,'maryalice','F','GA')");
+ $dbh->do("insert into test_person (age,first_name,gender,state) values (
3,'paul', 'M','GA')");
+ $dbh->do("insert into test_person (age,first_name,gender,state) values (
1,'christine','F','GA')");
+ $dbh->do("insert into test_person (age,first_name,gender,state) values
(45,'tim', 'M','GA')");
+ $dbh->do("insert into test_person (age,first_name,gender,state) values
(39,'keith', 'M','GA')");
&App::sub_exit() if ($App::trace);
}
Modified: p5ee/trunk/App-Repository/t/app.conf.sample
==============================================================================
--- p5ee/trunk/App-Repository/t/app.conf.sample (original)
+++ p5ee/trunk/App-Repository/t/app.conf.sample Tue Nov 11 14:46:13 2008
@@ -4,7 +4,6 @@
[dbtype=oracle]
dbclass = App::Repository::Oracle
-dbdsn = dbi:Oracle:sampledb
dbhost = sampleoraclehost
dbname = sampledb
dbuser = scott
