cvsuser 02/07/12 14:08:31
Modified: P5EEx/Blue/P5EEx/Blue/Repository DBI.pm
Log:
lots of work on store_rows() 1. auto_id 2. table_ih 3. locking 4. timestamping
Revision Changes Path
1.13 +345 -13 p5ee/P5EEx/Blue/P5EEx/Blue/Repository/DBI.pm
Index: DBI.pm
===================================================================
RCS file: /cvs/public/p5ee/P5EEx/Blue/P5EEx/Blue/Repository/DBI.pm,v
retrieving revision 1.12
retrieving revision 1.13
diff -u -w -r1.12 -r1.13
--- DBI.pm 7 Jun 2002 17:55:07 -0000 1.12
+++ DBI.pm 12 Jul 2002 21:08:31 -0000 1.13
@@ -1,17 +1,18 @@
######################################################################
-## File: $Id: DBI.pm,v 1.12 2002/06/07 17:55:07 spadkins Exp $
+## File: $Id: DBI.pm,v 1.13 2002/07/12 21:08:31 spadkins Exp $
######################################################################
use P5EEx::Blue::P5EE;
use P5EEx::Blue::Repository;
package P5EEx::Blue::Repository::DBI;
-$VERSION = do { my @r=(q$Revision: 1.12 $=~/\d+/g); sprintf "%d."."%02d"x$#r,@r};
+$VERSION = do { my @r=(q$Revision: 1.13 $=~/\d+/g); sprintf "%d."."%02d"x$#r,@r};
@ISA = ( "P5EEx::Blue::Repository" );
use Data::Dumper;
+use Date::Format;
use strict;
@@ -309,8 +310,11 @@
sub mk_where_clause {
my ($self, $table, $params, $paramvalues) = @_;
my ($where, $column, $colstr, $value, $colnum, $repop, $sqlop, $column_def,
$quoted);
- my $tabcols = $self->{table}{$table}{column};
- my %sqlop = (
+ my ($tabledef, $tabcols, %sqlop);
+
+ $tabledef = $self->{table}{$table};
+ $tabcols = $tabledef->{column};
+ %sqlop = (
'contains' => 'like',
'matches' => 'like',
'eq' => '=',
@@ -364,6 +368,29 @@
$value =~ s/'/\\'/g;
$value = "'$value'";
}
+ elsif ($sqlop eq "in" || $sqlop eq "=") {
+ if ($quoted) {
+ $value =~ s/'/\\'/g;
+ if ($value =~ /,/ && !
$tabledef->{param}{$colstr}{no_auto_in_param}) {
+ $value =~ s/,/','/g;
+ $value = "('$value')";
+ $sqlop = "in";
+ }
+ else {
+ $value = "'$value'";
+ $sqlop = "=";
+ }
+ }
+ else {
+ if ($value =~ /,/ && !
$tabledef->{param}{$colstr}{no_auto_in_param}) {
+ $value = "($value)";
+ $sqlop = "in";
+ }
+ else {
+ $sqlop = "=";
+ }
+ }
+ }
elsif ($quoted) {
$value =~ s/'/\\'/g;
$value = "'$value'";
@@ -950,7 +977,7 @@
$values = "values\n";
for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
$col = $cols->[$colnum];
- if ($#$row == -1) {
+ if (!defined $row || $#$row == -1) {
$value = "?";
}
else {
@@ -995,7 +1022,7 @@
for ($i = 0; $i <= $#$keycolidx; $i++) {
$colnum = $keycolidx->[$i];
$col = $cols->[$colnum];
- if ($#$row == -1) {
+ if (!defined $row || $#$row == -1) {
$value = "?";
}
else {
@@ -1021,7 +1048,7 @@
for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
if (!$colused[$colnum]) {
$col = $cols->[$colnum];
- if ($#$row == -1) {
+ if (!defined $row || $#$row == -1) {
$value = "?";
}
else {
@@ -1058,7 +1085,7 @@
for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
$col = $cols->[$colnum];
- if ($#$row == -1) {
+ if (!defined $row || $#$row == -1) {
$value = "?";
}
else {
@@ -1100,7 +1127,7 @@
for ($i = 0; $i <= $#$keycolidx; $i++) {
$colnum = $keycolidx->[$i];
$col = $cols->[$colnum];
- if ($#$row == -1) {
+ if (!defined $row || $#$row == -1) {
$value = "?";
}
else {
@@ -1181,11 +1208,11 @@
# $ok = $rep->insert_row ($table, \@cols, \@row);
sub insert_row {
my ($self, $table, $cols, $row) = @_;
- my $sql = $self->mk_insert_row_sql($table, $cols, $row);
+ my $sql = $self->mk_insert_row_sql($table, $cols);
$self->{sql} = $sql;
my $dbh = $self->{dbh};
return 0 if (!defined $dbh);
- return $dbh->do($sql);
+ return $dbh->do($sql, undef, @$row);
}
# $ok = $rep->insert_rows ($table, \@cols, \@rows);
@@ -1197,10 +1224,10 @@
return 0 if (!defined $dbh);
$ok = 1;
+ $sql = $self->mk_insert_row_sql($table, $cols);
foreach $row (@$rows) {
- $sql = $self->mk_insert_row_sql($table, $cols, $row);
$nrows += $self->{numrows};
- if (!$dbh->do($sql)) {
+ if (!$dbh->do($sql, undef, @$row)) {
$self->{numrows} = $nrows;
$ok = 0;
last;
@@ -1249,6 +1276,311 @@
my $dbh = $self->{dbh};
return 0 if (!defined $dbh);
return $dbh->do($sql);
+}
+
+#############################################################################
+# store_rows()
+#############################################################################
+
+=head2 store_rows()
+
+ * Signature: $nrows = $rep->store_rows ($table, $cols, $rows, $keycolidx,
$update_first);
+ * Param: $table string
+ * Param: $cols []
+ * Param: $rows [][]
+ * Param: $keycolidx []
+ * Param: $update_first boolean
+ * Return: $nrows integer
+ * Throws: P5EEx::Blue::Exception::Repository
+ * Since: 0.01
+
+ Sample Usage:
+
+ $rep->store_rows ($table, \@cols, \@rows, \@keycolidx, $update_first);
+
+The store_rows() method ensures that all rows of data are stored by
+trying both an insert, then an update (or vice-versa, depending on the
+$update_first flag).
+
+If any row fails to be stored, the method will exit immediately,
+returning the number of rows that were successfully stored.
+
+The default implementation here is built on using the store_row() method.
+It is fully functional, but it may be overridden in
+the subclass if there is a more efficient way to do it.
+
+=cut
+
+# $nrows = $rep->store_rows ($table, \@cols, \@rows, \@keycolidx, $update_first);
+sub store_rows {
+ my ($self, $table, $cols, $rows, $keycolidx, $update_first, $row_crit) = @_;
+ my ($row, $ok, $nrows, $rownum, $colnum, $column, $tabledef);
+ my ($pk_column, @extra_cols, @insert_cols, @insert_cols_ih);
+ my ($id, %paramvalues, $oldrow, $oldrows, %oldrows, $different, $key);
+ my ($table_ih, $auto_id, $current_datetime, $context, $error, $sql);
+ my ($column_obsolete_dttm, $column_change_dttm, %colidx, %nodiff_column);
+
+ $context = $self->{context};
+ if ($P5EEx::Blue::DEBUG && $context->dbg(1)) {
+ $context->dbgprint("store_rows($table, col=$cols, rows=$rows (", ($#$rows +
1), "), kidx=$keycolidx, up=$update_first, crit=$row_crit)");
+ $context->dbgprint(" cols=[", join(",",@$cols), "]") if ($cols);
+ $context->dbgprint(" keycolidx=[", join(",",@$keycolidx), "]") if
($keycolidx);
+ $context->dbgprint(" rowcrit=[", join(",",%$row_crit),"]") if
($row_crit);
+ }
+
+ $self->store_begin($table);
+ $tabledef = $self->{table}{$table};
+ $table_ih = $tabledef->{table_ih} || "";
+ $column_change_dttm = $tabledef->{column_change_dttm} || "";
+ $column_obsolete_dttm = $tabledef->{column_obsolete_dttm} || "";
+
+ @insert_cols = @$cols;
+ @insert_cols_ih = @$cols;
+ %colidx = ();
+ $pk_column = "";
+ $auto_id = 0;
+
+ # todo: make it work with PK bigger than one column
+ if ($tabledef->{prikey} && ref($tabledef->{prikey}) eq "ARRAY" &&
$#{$tabledef->{prikey}} == 0) {
+ $pk_column = $tabledef->{prikey}[0];
+ $auto_id = $tabledef->{column}{$pk_column}{auto_id};
+ }
+
+ if ($P5EEx::Blue::DEBUG && $context->dbg(1)) {
+ $context->dbgprint(" auto_id=$auto_id pk=$pk_column
chg=$column_change_dttm obs=$column_obsolete_dttm");
+ }
+
+ if (($auto_id && $pk_column) ||
+ ($column_change_dttm && $column_obsolete_dttm)) {
+
+ for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
+ $column = $cols->[$colnum];
+ $colidx{$column} = $colnum;
+ }
+
+ if ($pk_column && ! defined $colidx{$pk_column}) {
+ push(@insert_cols, $pk_column);
+ push(@insert_cols_ih, $pk_column);
+ $colidx{$pk_column} = $#insert_cols;
+ }
+
+ if ($column_change_dttm && ! defined $colidx{$column_change_dttm}) {
+ push(@insert_cols, $column_change_dttm);
+ push(@insert_cols_ih, $column_change_dttm);
+ $colidx{$column_change_dttm} = $#insert_cols;
+ }
+
+ if ($column_obsolete_dttm && ! defined $colidx{$column_obsolete_dttm}) {
+ push(@insert_cols_ih, $column_obsolete_dttm);
+ $colidx{$column_obsolete_dttm} = $#insert_cols_ih;
+ }
+ }
+
+ %nodiff_column = ();
+ if ($tabledef->{nodiff_column}) {
+ foreach $column (split(/,/,$tabledef->{nodiff_column})) {
+ $nodiff_column{$column} = 1;
+ }
+ }
+
+ %oldrows = ();
+ if (defined $row_crit) {
+ $oldrows = [];
+
+ my ($tmprows);
+ $tmprows = $self->select_rows($table,\@insert_cols_ih,undef,$row_crit);
+
+ for ($rownum = 0; $rownum <= $#$tmprows; $rownum++) {
+ $oldrow = $tmprows->[$rownum];
+ $key = join(",", @{$oldrow}[@$keycolidx]);
+ $oldrows{$key} = $oldrow;
+ }
+
+ for ($rownum = 0; $rownum <= $#$rows; $rownum++) {
+ $row = $rows->[$rownum];
+ $key = join(",", @{$row}[@$keycolidx]);
+ $oldrows->[$rownum] = $oldrows{$key};
+ delete $oldrows{$key};
+ }
+
+ $current_datetime = $self->current_datetime() if (%oldrows &&
!$current_datetime);
+
+ if ($P5EEx::Blue::DEBUG && $context->dbg(1)) {
+ $error = $self->error();
+ $context->dbgprint("store_rows($table): replacing ", $#$tmprows + 1, "
$error");
+ }
+ }
+ else {
+ for ($rownum = 0; $rownum <= $#$rows; $rownum++) {
+ $row = $rows->[$rownum];
+
+ # (todo) allow for $keycolidx to be undef (use "prikey")
+ %paramvalues = ();
+ foreach $colnum (@$keycolidx) {
+ $column = $cols->[$colnum];
+ $paramvalues{$column} = $row->[$colnum];
+ }
+ $oldrow =
$self->select_row($table,\@insert_cols_ih,undef,\%paramvalues);
+ $oldrows->[$rownum] = $oldrow;
+ }
+ }
+
+ $nrows = 0;
+ for ($rownum = 0; $rownum <= $#$rows; $rownum++) {
+ $row = $rows->[$rownum];
+ $oldrow = $oldrows->[$rownum];
+
+ #print "store_rows(): old=[", (defined $oldrow) ? (join(",", @$oldrow)) :
"", "]\n";
+ #print "store_rows(): new=[", join(",", @$row), "]\n";
+ #print "sql=", $self->{sql}, "\n";
+
+ $ok = 1;
+ if ($oldrow && $#$oldrow > -1) { # old row existed
+ # next if (does not have permission to modify); (todo)
+ # reduce the non-key column list to the set this user is permitted to
modify (todo)
+
+ # check if data is the same
+ $different = 0;
+ for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
+ $column = $cols->[$colnum];
+ next if ($nodiff_column{$column});
+
+ if ((!defined $row->[$colnum] && defined $oldrow->[$colnum]) ||
+ ( defined $row->[$colnum] && !defined $oldrow->[$colnum])) {
+ $different = 1;
+ }
+
+ if (defined $row->[$colnum] && defined $oldrow->[$colnum]) {
+ if (($row->[$colnum] =~ /^-?[0-9]+\.?[0-9]*$/ ||
$row->[$colnum] =~ /^-?\.[0-9]+$/) &&
+ ($oldrow->[$colnum] =~ /^-?[0-9]+\.?[0-9]*$/ ||
$oldrow->[$colnum] =~ /^-?\.[0-9]+$/)) {
+ if ($row->[$colnum] != $oldrow->[$colnum]) {
+ $different = 1;
+ }
+ }
+ else {
+ if ($row->[$colnum] ne $oldrow->[$colnum]) {
+ $different = 1;
+ }
+ }
+ }
+ last if ($different);
+ }
+
+ # only if it is considered different do we try to physically store the
row
+ if ($different) {
+ # substitute any defaults (todo)
+ if ($table_ih) {
+ if ($column_obsolete_dttm && $column_change_dttm) {
+ $oldrow->[$colidx{$column_obsolete_dttm}] =
$row->[$colidx{$column_change_dttm}];
+ }
+ $ok = $self->insert_row($table_ih,\@insert_cols_ih,$oldrow);
+ }
+ $ok = $self->update_row($table,$cols,$row,$keycolidx) if ($ok);
+ }
+
+ if ($P5EEx::Blue::DEBUG && $context->dbg(2)) {
+ $context->dbgprint("store_rows() old=[", join(",",@$oldrow), "]");
+ $context->dbgprint(" new=[", join(",",@$row), "]");
+ $error = $self->error();
+ $sql = $error ? $self->{sql} : "";
+ $context->dbgprint(" diff=$different ok=$ok err=$error
$sql");
+ }
+ }
+ else {
+ # next if (does not have permission to insert); (todo)
+
+ # if we need to automatically allocate an ID ...
+ if ($auto_id) {
+ $id = $self->next_id($table, $id);
+ $row->[$colidx{$pk_column}] = $id;
+ }
+ $ok = $self->insert_row($table,\@insert_cols,$row);
+
+ if ($P5EEx::Blue::DEBUG && $context->dbg(2)) {
+ $context->dbgprint("store_rows() new=[", join(",",@$row), "]");
+ $error = $self->error();
+ $sql = $error ? $self->{sql} : "";
+ $context->dbgprint(" ok=$ok err=$error $sql");
+ }
+ }
+
+ last if (!$ok);
+ $nrows++;
+ }
+
+ if (defined $row_crit && %oldrows) {
+ foreach $key (keys %oldrows) {
+ $oldrow = $oldrows{$key};
+ $ok = 1;
+ if ($table_ih) {
+ if ($column_obsolete_dttm) {
+ $current_datetime = $self->current_datetime() if
(!$current_datetime);
+ $oldrow->[$colidx{$column_obsolete_dttm}] = $current_datetime;
+ }
+ $ok = $self->insert_row($table_ih,\@insert_cols_ih,$oldrow);
+ }
+ if ($ok || $self->error() =~ /duplicate/i) {
+ $ok = $self->delete_row($table,$cols,$row,$keycolidx);
+ }
+
+ if ($P5EEx::Blue::DEBUG && $context->dbg(2)) {
+ $context->dbgprint("store_rows() del=[", join(",",@$row), "]");
+ $error = $self->error();
+ $sql = $error ? $self->{sql} : "";
+ $context->dbgprint(" ok=$ok err=$error $sql");
+ }
+
+ last if (!$ok);
+ }
+ }
+
+ $self->store_end($table);
+ $self->{numrows} = $nrows;
+ $nrows;
+}
+
+sub current_datetime {
+ my ($self) = @_;
+ return time2str("%Y-%m-%d %H:%M:%S",time());
+}
+
+sub store_begin {
+ my ($self, $table) = @_;
+ if (! $self->{locked}) {
+ my ($lock_stmt, $alias, $table_ih);
+ $lock_stmt = "lock tables $table write";
+ $alias = $self->{table}{$table}{alias};
+ $lock_stmt .= ", $table $alias write" if ($alias);
+ $table_ih = $self->{table}{$table}{table_ih};
+ $lock_stmt .= ", $table_ih write" if ($table_ih);
+ $self->{dbh}->do($lock_stmt);
+ $self->{locked} = 1;
+ }
+}
+
+sub next_id {
+ my ($self, $table, $last_id) = @_;
+ return ($last_id + 1) if ($last_id);
+
+ my ($tabledef, $upper_bound, $where_upper_bound, $id, $pk_column);
+ $tabledef = $self->{table}{$table};
+ $pk_column = "";
+ $pk_column = $tabledef->{prikey}[0] if ($tabledef->{prikey});
+ $upper_bound = $tabledef->{column}{$pk_column}{auto_id_upper_bound};
+ $where_upper_bound = $upper_bound ? " where $pk_column < $upper_bound" : "";
+ $id = $self->{dbh}->selectrow_array("select max($pk_column) from
$table$where_upper_bound");
+ $id = 0 if (!$id);
+ $id ++;
+
+ return $id;
+}
+
+sub store_end {
+ my ($self, $table) = @_;
+ if ($self->{locked}) {
+ delete $self->{locked};
+ $self->{dbh}->do("unlock tables");
+ }
}
######################################################################
