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, \@cols, \@row);
# $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, \@cols, \@rows);
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, \@cols, \@rows, \%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, \@cols, \@rows);
+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 : $@\n";
+ print "DEBUG_SQL: import_rows=[$nrows] ($elapsed_time sec) $DBI::errstr : $@\n";
}
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 {
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.