Author: spadkins
Date: Mon Dec 4 11:33:33 2006
New Revision: 8356
Modified:
p5ee/trunk/App-Repository/lib/App/Repository.pm
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm
Log:
insert_rows(), and mods to import_rows()
Modified: p5ee/trunk/App-Repository/lib/App/Repository.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository.pm Mon Dec 4 11:33:33 2006
@@ -102,8 +102,8 @@
$rep->commit();
$rep->rollback();
- $rep->import_rows($table, $file, $options);
- $rep->export_rows($table, $file, $options);
+ $rep->import_rows($table, $columns, $file, $options);
+ $rep->export_rows($table, $columns, $file, $options);
=cut
@@ -1772,7 +1772,6 @@
# need to do a select after insert. However, there might be defaults
# set in the database that I don't know about, and I want them to be
# reflected in the returned object.
-# NOTE 2: Tables which have
# $object = $rep->new_object($table, [EMAIL PROTECTED], [EMAIL PROTECTED]);
# $object = $rep->new_object($table, \%obj_values);
# $object = $rep->new_object($table, $col, $value);
@@ -2373,6 +2372,28 @@
=cut
#############################################################################
+# begin_work()
+#############################################################################
+
+=head2 begin_work()
+
+ * Signature: $rep->begin_work();
+ * Param: void
+ * Return: void
+ * Throws: App::Exception::Repository
+ * Since: 0.01
+
+ Sample Usage:
+
+ $rep->begin_work();
+
+=cut
+
+sub begin_work {
+ my $self = shift;
+}
+
+#############################################################################
# commit()
#############################################################################
@@ -2471,12 +2492,12 @@
=head2 import_rows()
- * Signature: $rep->import_rows($table, $file);
- * Signature: $rep->import_rows($table, $file, $options);
+ * Signature: $rep->import_rows($table, $columns, $file);
+ * Signature: $rep->import_rows($table, $columns, $file, $options);
* Param: $table string
+ * Param: $columns ARRAY names of columns of the fields in the
file
* Param: $file string
* Param: $options named
- * Param: columns ARRAY names of columns of the fields in the
file
* Param: replace boolean rows should replace existing rows
based on unique indexes
* Param: field_sep char character which separates the fields
in the file (can by "\t")
* Param: field_quote char character which optionally encloses
the fields in the file (i.e. '"')
@@ -2490,17 +2511,17 @@
$rep->import_rows("usr","usr.dat");
# root:x:0:0:root:/root:/bin/bash
- $rep->import_rows("usr", "/etc/passwd" ,{
- field_sep => ":",
- columns => [ "username", "password", "uid", "gid", "comment",
"home_directory", "shell" ],
- });
+ $rep->import_rows("usr",
+ [ "username", "password", "uid", "gid", "comment", "home_directory",
"shell" ],
+ "/etc/passwd" ,
+ { field_sep => ":", });
=cut
sub import_rows {
&App::sub_entry if ($App::trace);
- my ($self, $table, $file, $options) = @_;
- my $columns = $options->{columns} || $self->{table}{$table}{columns};
+ my ($self, $table, $columns, $file, $options) = @_;
+ $columns = $self->_get_default_columns($table) if (!$columns);
my $field_sep = $options->{field_sep} || ",";
my $field_quote = $options->{field_quote};
my $field_escape = $options->{field_escape};
@@ -2511,6 +2532,7 @@
chomp;
if ($field_quote) {
@row = ();
+ # TODO: use the _read_rows_from_file() method
# TODO: incorporate escaping
$field_regexp =
"$field_sep?$field_quote([^$field_quote]*)$field_quote";
$quoted_field_regexp = "$field_sep?([^$field_sep]*)";
@@ -2543,8 +2565,8 @@
=head2 export_rows()
- * Signature: $rep->export_rows($table, $file);
- * Signature: $rep->export_rows($table, $file, $options);
+ * Signature: $rep->export_rows($table, $columns, $file);
+ * Signature: $rep->export_rows($table, $columns, $file, $options);
* Param: $table string
* Param: $file string
* Param: $options named
@@ -2571,9 +2593,8 @@
sub export_rows {
&App::sub_entry if ($App::trace);
- my ($self, $table, $file, $options) = @_;
-
- my $columns = $options->{columns} || $self->{table}{$table}{columns};
+ my ($self, $table, $columns, $file, $options) = @_;
+ $columns = $self->_get_default_columns($table) if (!$columns);
my $rows = $self->get_rows($table, {}, $columns);
my $field_sep = $options->{field_sep} || ",";
my $field_quote = $options->{field_quote};
@@ -2607,6 +2628,60 @@
&App::sub_exit() if ($App::trace);
}
+sub _read_rows_from_file {
+ &App::sub_entry if ($App::trace);
+ my ($self, $fh, $cols, $options) = @_;
+ my $maxrows = $options->{maxrows};
+ my $null_value = $options->{null_value};
+ $null_value = '\N' if (!defined $null_value);
+ my $field_sep = $options->{field_sep} || ",";
+ my $field_quote = $options->{field_quote} || "";
+ my $field_escape = $options->{field_escape} || "";
+ die "TODO: field_escape not yet implemented" if ($field_escape);
+ my $fieldsep_regexp = ($field_sep eq "|") ? '\|' : $field_sep;
+ my $quoted_field_regexp =
"$field_sep?$field_quote([^$field_quote]*)$field_quote";
+ my $field_regexp = "$field_sep?([^$field_sep]*)";
+ my $num_cols = $#$cols + 1;
+ my $rows_read = 0;
+ my $rows = [];
+ my ($num_values_read, $line, $line_remainder, $row);
+ while (<$fh>) {
+ chomp;
+ $line = $_;
+ if ($line) {
+ if (!$field_quote && !$field_escape) {
+ $row = [ map { $_ eq $null_value ? undef : $_ }
split(/$fieldsep_regexp/, $line) ];
+ $num_values_read = $#$row + 1;
+ }
+ else {
+ $num_values_read = 0;
+ $line_remainder = $line;
+ $row = [];
+ while ($line_remainder) {
+ if ($line_remainder =~ s/^$quoted_field_regexp//) {
+ push(@$row, $1 eq $null_value ? undef : $1);
+ }
+ elsif ($line_remainder =~ s/^$field_regexp//) {
+ push(@$row, $1 eq $null_value ? undef : $1);
+ }
+ else {
+ die "Imported data [$line] doesn't match quoted or
unquoted field at [$line_remainder]";
+ }
+ }
+ }
+ die "In imported data [$line], num values on line
[$num_values_read] != num columns expected [$num_cols]"
+ if ($num_values_read != $num_cols);
+ push(@$rows, $row);
+ $rows_read ++;
+ if ($maxrows && $rows_read >= $maxrows) {
+ last;
+ }
+ }
+ }
+ &App::sub_exit($rows) if ($App::trace);
+ return($rows);
+}
+
#############################################################################
# METHODS
#############################################################################
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 Dec 4 11:33:33 2006
@@ -2181,7 +2181,7 @@
# $nrows = $rep->_insert_rows ($table, [EMAIL PROTECTED], [EMAIL PROTECTED]);
sub _insert_rows {
&App::sub_entry if ($App::trace);
- my ($self, $table, $cols, $rows) = @_;
+ my ($self, $table, $cols, $rows, $options) = @_;
$self->{error} = "";
my ($sql, $retval);
@@ -2190,35 +2190,72 @@
my $nrows = 0;
my $ok = 1;
- $sql = $self->_mk_insert_row_sql($table, $cols);
my $context_options = $self->{context}{options};
my $debug_sql = $context_options->{debug_sql};
+ my $explain_sql = $context_options->{explain_sql};
my ($timer, $elapsed_time);
if ($debug_sql) {
$timer = $self->_get_timer();
}
- foreach my $row (@$rows) {
- if ($debug_sql) {
- print "DEBUG_SQL: _insert_rows()\n";
- print "DEBUG_SQL: bind vars [", join("|",map { defined $_ ? $_ :
"undef" } @$row), "]\n";
- print $sql;
- }
- if ($context_options->{explain_sql}) {
- $self->explain_sql($sql);
- }
- $retval = $dbh->do($sql, undef, @$row);
- if ($debug_sql) {
- print "DEBUG_SQL: retval [$retval] $DBI::errstr\n";
- print "\n";
- }
-
- if ($retval) {
- $nrows ++;
+ if (ref($rows) eq "ARRAY") {
+ $sql = $self->_mk_insert_row_sql($table, $cols);
+ foreach my $row (@$rows) {
+ if ($debug_sql) {
+ print "DEBUG_SQL: _insert_rows()\n";
+ print "DEBUG_SQL: bind vars [", join("|",map { defined $_ ? $_
: "undef" } @$row), "]\n";
+ print $sql;
+ }
+ if ($explain_sql) {
+ $self->explain_sql($sql);
+ }
+ $retval = $dbh->do($sql, undef, @$row);
+ if ($debug_sql) {
+ print "DEBUG_SQL: retval [$retval] $DBI::errstr\n";
+ print "\n";
+ }
+
+ if ($retval) {
+ $nrows ++;
+ }
+ else {
+ $self->{numrows} = $nrows;
+ $ok = 0;
+ last;
+ }
}
- else {
- $self->{numrows} = $nrows;
- $ok = 0;
- last;
+ }
+ else {
+ my $fh = $rows; # assume it is a file handle
+ $rows = []; # we will be refilling this buffer
+ my %options = ( %$options ); # make a copy so it can be modified
+ $options->{maxrows} = 100;
+ $sql = $self->_mk_insert_row_sql($table, $cols);
+ while (1) {
+ $rows = $self->_read_rows_from_file($fh, $cols, \%options);
+ last if ($#$rows == -1);
+ foreach my $row (@$rows) {
+ if ($debug_sql) {
+ print "DEBUG_SQL: _insert_rows()\n";
+ print "DEBUG_SQL: bind vars [", join("|",map { defined $_
? $_ : "undef" } @$row), "]\n";
+ print $sql;
+ }
+ if ($context_options->{explain_sql}) {
+ $self->explain_sql($sql);
+ }
+ $retval = $dbh->do($sql, undef, @$row);
+ if ($debug_sql) {
+ print "DEBUG_SQL: retval [$retval] $DBI::errstr\n";
+ print "\n";
+ }
+
+ if ($retval) {
+ $nrows ++;
+ }
+ else {
+ $self->{numrows} = $nrows;
+ $ok = 0;
+ }
+ }
}
}
if ($debug_sql) {
@@ -2377,6 +2414,7 @@
$timer = $self->_get_timer();
print "DEBUG_SQL: _do()\n";
print $sql;
+ print "\n" if ($sql !~ /\n$/);
}
if ($context_options->{explain_sql}) {
$self->explain_sql($sql);
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 Dec 4
11:33:33 2006
@@ -107,6 +107,11 @@
return($dsn);
}
+sub _last_inserted_id {
+ my ($self) = @_;
+ return($self->{dbh}{mysql_insertid});
+}
+
sub _mk_select_sql_suffix {
&App::sub_entry if ($App::trace);
my ($self, $table, $options) = @_;
@@ -119,9 +124,140 @@
return($suffix);
}
-sub _last_inserted_id {
- my ($self) = @_;
- return($self->{dbh}{mysql_insertid});
+# $insert_sql = $rep->_mk_insert_rows_sql ($table, [EMAIL PROTECTED], [EMAIL
PROTECTED], \%options);
+# i.e. $options->{replace}
+sub _mk_insert_rows_sql {
+ &App::sub_entry if ($App::trace);
+ my ($self, $table, $cols, $rows, $options) = @_;
+
+ $self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
+ my $dbh = $self->{dbh};
+
+ if ($#$cols == -1) {
+ $self->{error} = "Database->_mk_insert_row_sql(): no columns
specified";
+ return();
+ }
+ my $tabcols = $self->{table}{$table}{column};
+
+ my $insert = $options->{replace} ? "replace" : "insert";
+ my $sql = "$insert into $table\n (" . join(", ", @$cols) . ")\nvalues\n";
+ my ($value, $colnum, $quoted, $row, $col);
+ for (my $rownum = 0; $rownum <= $#$rows; $rownum++) {
+ $row = $rows->[$rownum];
+ for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
+ $col = $cols->[$colnum];
+ if (!defined $row || $#$row == -1) {
+ $value = "?";
+ }
+ else {
+ $value = $row->[$colnum];
+ if (!defined $value) {
+ $value = "NULL";
+ }
+ else {
+ $quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
+ if ($quoted) {
+ $value = $dbh->quote($value);
+ }
+ }
+ }
+ if ($tabcols->{$col}{dbexpr_update}) {
+ $value = sprintf($tabcols->{$col}{dbexpr_update}, $value);
+ }
+ $sql .= ($colnum == 0) ? " ($value" : ", $value";
+ }
+ $sql .= ($rownum < $#$rows) ? "),\n" : ")\n";
+ }
+ &App::sub_exit($sql) if ($App::trace);
+ $sql;
+}
+
+# $nrows = $rep->_insert_rows ($table, [EMAIL PROTECTED], [EMAIL PROTECTED]);
+sub _insert_rows {
+ &App::sub_entry if ($App::trace);
+ my ($self, $table, $cols, $rows, $options) = @_;
+ $self->{error} = "";
+ my ($sql, $retval);
+
+ my $dbh = $self->{dbh};
+ return 0 if (!defined $dbh);
+
+ my $nrows = 0;
+ my $ok = 1;
+ my $context_options = $self->{context}{options};
+ my $debug_sql = $context_options->{debug_sql};
+ my $explain_sql = $context_options->{explain_sql};
+ my ($timer, $elapsed_time);
+ if ($debug_sql) {
+ $timer = $self->_get_timer();
+ }
+ my $rows_ref = ref($rows);
+ if ($rows_ref eq "ARRAY") {
+ $sql = $self->_mk_insert_rows_sql($table, $cols, $rows);
+ if ($debug_sql) {
+ print "DEBUG_SQL: _insert_rows()\n";
+ print $sql;
+ }
+ $retval = $dbh->do($sql);
+ if ($debug_sql) {
+ print "DEBUG_SQL: retval [$retval] $DBI::errstr\n";
+ print "\n";
+ }
+
+ $nrows = $retval;
+ $self->{numrows} = $nrows;
+ if ($retval != $#$rows + 1) {
+ $ok = 0;
+ }
+ }
+ else {
+ my ($fh);
+ if (!$rows_ref) {
+ my $file = $rows; # assume it is a file name
+ open(App::Repository::MySQL::FILE, $file) || die "Unable to open
$file for reading: $!";
+ $fh = \*App::Repository::MySQL::FILE;
+ }
+ else {
+ $fh = $rows; # assume it is a file handle
+ }
+ $rows = []; # we will be refilling this buffer
+ my %options = ( %$options ); # make a copy so it can be modified
+ $options{maxrows} = 100;
+ $sql = $self->_mk_insert_row_sql($table, $cols);
+ $nrows = 0;
+ while (1) {
+ $rows = $self->_read_rows_from_file($fh, $cols, \%options);
+ last if ($#$rows == -1);
+ $sql = $self->_mk_insert_rows_sql($table, $cols, $rows);
+ if ($debug_sql) {
+ print "DEBUG_SQL: _insert_rows()\n";
+ print $sql;
+ }
+ $retval = $dbh->do($sql);
+ if ($debug_sql) {
+ print "DEBUG_SQL: retval [$retval] $DBI::errstr\n";
+ print "\n";
+ }
+
+ $nrows += $retval;
+ if ($retval != $#$rows + 1) {
+ $ok = 0;
+ last;
+ }
+ }
+ $self->{numrows} = $nrows;
+ if (!$rows_ref) {
+ close(App::Repository::MySQL::FILE);
+ }
+ }
+ if ($debug_sql) {
+ $elapsed_time = $self->_read_timer($timer);
+ print "DEBUG_SQL: nrows [$nrows] ($elapsed_time sec)\n";
+ }
+ $self->{sql} = $sql;
+ $self->{numrows} = $nrows;
+ &App::sub_exit($nrows) if ($App::trace);
+ return($nrows);
}
sub _load_table_key_metadata {
@@ -224,7 +360,9 @@
* Param: $file string
* Param: $options named
* Param: columns ARRAY names of columns of the fields in the
file
- * Param: method string [basic=invokes generic superclass to
do work]
+ * Param: import_method string [basic=invokes generic superclass to
do work,
+ insert=loads with multiple-row
inserts,
+ <otherwise>=use "load data infile"]
* Param: local boolean file is on client machine rather than
database server
* Param: replace boolean rows should replace existing rows
based on unique indexes
* Param: field_sep char character which separates the fields
in the file (can by "\t")
@@ -265,10 +403,19 @@
sub import_rows {
&App::sub_entry if ($App::trace);
- my ($self, $table, $file, $options) = @_;
+ my ($self, $table, $columns, $file, $options) = @_;
+ $columns = $self->_get_default_columns($table) if (!$columns);
- if ($options->{method} && $options->{method} eq "basic") {
- $self->SUPER::import_rows($table, $file, $options);
+ my $nrows = 0;
+ my $import_method = $options->{import_method} || $self->{import_method} ||
"";
+ if ($import_method eq "basic") {
+ $nrows = $self->SUPER::import_rows($table, $columns, $file, $options);
+ }
+ elsif ($import_method eq "insert") {
+ $nrows = $self->insert_rows($table, $columns, $file, $options);
+ #open(App::Repository::MySQL::FILE, $file) || die "Unable to open
$file for reading: $!";
+ #$nrows = $self->insert_rows($table, $columns,
\*App::Repository::MySQL::FILE, $options);
+ #close(App::Repository::MySQL::FILE);
}
else {
my $local = $options->{local};
@@ -281,9 +428,7 @@
$sql .= "\n optionally enclosed by '$options->{field_quote}'" if
($options->{field_quote});
$sql .= "\n escaped by '$options->{field_escape}'" if
($options->{field_escape});
}
- if ($options->{columns}) {
- $sql .= "\n(" . join(",", @{$options->{columns}}) . ")";
- }
+ $sql .= "\n(" . join(",", @$columns) . ")";
my $context_options = $self->{context}{options};
my $debug_sql = $context_options->{debug_sql};
my ($timer, $elapsed_time);
@@ -292,18 +437,18 @@
print "DEBUG_SQL: import_rows()\n";
print $sql;
}
- my ($retval);
eval {
- $retval = $self->{dbh}->do($sql);
+ $nrows = $self->{dbh}->do($sql);
};
if ($debug_sql) {
$elapsed_time = $self->_read_timer($timer);
- print "DEBUG_SQL: import_rows=[$retval] ($elapsed_time sec)
$DBI::errstr : [EMAIL PROTECTED]";
+ print "DEBUG_SQL: import_rows=[$nrows] ($elapsed_time sec)
$DBI::errstr : [EMAIL PROTECTED]";
}
die $@ if ($@);
}
- &App::sub_exit() if ($App::trace);
+ &App::sub_exit($nrows) if ($App::trace);
+ return($nrows);
}
#############################################################################
@@ -318,7 +463,7 @@
* Param: $file string
* Param: $options named
* Param: columns ARRAY names of columns of the fields in the
file
- * Param: method string [basic=invokes generic superclass to
do work]
+ * Param: export_method string [basic=invokes generic superclass to
do work]
* Param: field_sep char character which separates the fields
in the file (can by "\t")
* Param: field_quote char character which optionally encloses
the fields in the file (i.e. '"')
* Param: field_escape char character which escapes the quote
chars within quotes (i.e. "\")
@@ -367,7 +512,7 @@
&App::sub_entry if ($App::trace);
my ($self, $table, $params, $file, $options) = @_;
- if ($options->{method} && $options->{method} eq "basic") {
+ if ($options->{export_method} && $options->{export_method} eq "basic") {
$self->SUPER::export_rows($table, $file, $options);
}
else {
