[interchange-cvs] interchange - heins modified 2 files
interchange-core@icdevgroup.org
interchange-core@icdevgroup.org
Thu Jul 18 15:28:00 2002
User: heins
Date: 2002-07-18 19:27:57 GMT
Modified: lib/Vend/Table Common.pm DBI.pm
Log:
* Make AUTO_SEQUENCE transparent for MySQL, Postgres, and Oracle.
* To do a minimal sequenced table, all you need to do is:
Database sequenced sequenced.txt __DBIDSN__
Database sequenced AUTO_SEQUENCE sequenced
The parameter passed to AUTO_SEQUENCE (in the above, "sequenced") will
be used as the sequence name for Postgres and Oracle (and presumably othe=
rs
that emulate them).
For MySQL, the same technique that Stefan introduced is used, with an
AUTO_INCREMENT field. The value in AUTO_SEQUENCE is just a non-false
value. The behavior depends on the definition of
$capability->{LAST_SEQUENCE_FUNCTION}.
If MySQL is the DB in use,
$key =3D $s->autonumber();
returns nothing and the key will be later found with
$key =3D $s->last_sequence_value($key);
and returned in $db->set_slice, etc. ($db->set_row also uses this, but
the key value is never returned. You can get it with
$db->last_sequence_value if you need it.)
If Postgres/Oracle is used, the key is returned with
$key =3D $s->autonumber();
and is just parroted back with
$key =3D $s->last_sequence_value($key);
If the table is being created, the sequence will be created as well. If
it exists, it will not be dropped. If the "code" or key field is not
set with a COLUMN_DEF, the field type to be used will be found in
$capability->{SEQUENCE_KEY}.
Capablility entries used:
SEQUENCE_CREATE Query to create a sequence on table creation.
SEQUENCE_QUERY Query to get next value in sequence.
SEQUENCE_KEY Type definition for key field when AUTO_SEQUENCE
table is created.
SEQUENCE_VALUE_FUNCTION Query to get current value of sequence.
SEQUENCE_LAST_FUNCTION Query to get key when MySQL behavior is wanted.
Revision Changes Path
2.18 +7 -4 interchange/lib/Vend/Table/Common.pm
rev 2.18, prev_rev 2.17
Index: Common.pm
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: /anon_cvs/repository/interchange/lib/Vend/Table/Common.pm,v
retrieving revision 2.17
retrieving revision 2.18
diff -u -r2.17 -r2.18
--- Common.pm 9 Jul 2002 17:42:12 -0000 2.17
+++ Common.pm 18 Jul 2002 19:27:57 -0000 2.18
@@ -1,6 +1,6 @@
# Vend::Table::Common - Common access methods for Interchange databases
#
-# $Id: Common.pm,v 2.17 2002/07/09 17:42:12 mheins Exp $
+# $Id: Common.pm,v 2.18 2002/07/18 19:27:57 mheins Exp $
#
# Copyright (C) 1996-2002 Red Hat, Inc. <interchange@redhat.com>
#
@@ -22,7 +22,7 @@
# Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
# MA 02111-1307 USA.
=20
-$VERSION =3D substr(q$Revision: 2.17 $, 10);
+$VERSION =3D substr(q$Revision: 2.18 $, 10);
use strict;
=20
package Vend::Table::Common;
@@ -135,8 +135,11 @@
sub autonumber {
my $s =3D shift;
my $start;
- return $s->[$CONFIG]{SEQUENCE_VAL} if $s->[$CONFIG]{AUTO_SEQUENCE};
- return '' if not $start =3D $s->[$CONFIG]->{AUTO_NUMBER};
+ my $cfg =3D $s->[$CONFIG];
+
+ return $s->autosequence() if $cfg->{AUTO_SEQUENCE};
+
+ return '' if not $start =3D $cfg->{AUTO_NUMBER};
local($/) =3D "\n";
my $c =3D $s->[$CONFIG];
if(! defined $c->{AutoNumberCounter}) {
2.24 +100 -50 interchange/lib/Vend/Table/DBI.pm
rev 2.24, prev_rev 2.23
Index: DBI.pm
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
RCS file: /anon_cvs/repository/interchange/lib/Vend/Table/DBI.pm,v
retrieving revision 2.23
retrieving revision 2.24
diff -u -r2.23 -r2.24
--- DBI.pm 15 Jul 2002 20:18:46 -0000 2.23
+++ DBI.pm 18 Jul 2002 19:27:57 -0000 2.24
@@ -1,6 +1,6 @@
# Vend::Table::DBI - Access a table stored in an DBI/DBD database
#
-# $Id: DBI.pm,v 2.23 2002/07/15 20:18:46 jon Exp $
+# $Id: DBI.pm,v 2.24 2002/07/18 19:27:57 mheins Exp $
#
# Copyright (C) 1996-2002 Red Hat, Inc. <interchange@redhat.com>
#
@@ -20,7 +20,7 @@
# MA 02111-1307 USA.
=20
package Vend::Table::DBI;
-$VERSION =3D substr(q$Revision: 2.23 $, 10);
+$VERSION =3D substr(q$Revision: 2.24 $, 10);
=20
use strict;
=20
@@ -176,23 +176,31 @@
Pg =3D> 'CREATE _UNIQUE_ INDEX $TABLE$_$COLUMN$ ON _TABLE_ (_COLUMN_)',
default =3D> 'CREATE _UNIQUE_ INDEX $TABLE$_$COLUMN$ ON _TABLE_ (_COLUMN=
_)',
},
- SEQUENCE_NO_EXPLICIT =3D> {=20
- Pg =3D> 1,
+ SEQUENCE_CREATE =3D> {=20
+ Oracle =3D> "CREATE SEQUENCE _SEQUENCE_NAME_",
+ Pg =3D> "CREATE SEQUENCE _SEQUENCE_NAME_",
},
- SEQUENCE_GET =3D> {=20
- Oracle =3D> 1,
+ SEQUENCE_QUERY =3D> {=20
+ Oracle =3D> "SELECT _SEQUENCE_NAME_.nextval FROM dual",
+ Pg =3D> "SELECT nextval('_SEQUENCE_NAME_')",
},
SEQUENCE_VAL =3D> {=20
mysql =3D> undef,
- Pg =3D> undef,
},
SEQUENCE_KEY =3D> {=20
mysql =3D> 'INT PRIMARY KEY AUTO_INCREMENT',
- Pg =3D> 'SERIAL PRIMARY KEY',
+ Pg =3D> 'INT NOT NULL PRIMARY KEY',
+ Oracle =3D> 'INT NOT NULL PRIMARY KEY',
+ },
+ SEQUENCE_VALUE_FUNCTION =3D> {=20
+ Pg =3D> "SELECT currval('_SEQUENCE_NAME_')",
+ Oracle =3D> "SELECT _SEQUENCE_NAME_.currval FROM dual",
},
SEQUENCE_LAST_FUNCTION =3D> {=20
mysql =3D> 'select last_insert_id()',
- Pg =3D> 'select last_value from _TABLE___COLUMN__seq',
+ ## These use explicit
+ Pg =3D> undef,
+ Oracle =3D> undef,
},
UPPER_COMPARE =3D> {=20
Oracle =3D> 1,
@@ -258,8 +266,14 @@
$config->{KEY_INDEX} ||=3D $keycol;
$config->{KEY} ||=3D $key;
=20
- $cols[$keycol] =3D~ s/\s+.*/ char(16) NOT NULL/
- unless defined $config->{COLUMN_DEF}->{$key};
+ if ( not defined $config->{COLUMN_DEF}->{$key} ) {
+ if($config->{AUTO_SEQUENCE} and $config->{SEQUENCE_KEY}) {
+ $cols[$keycol] =3D~ s/\s+.*/ $config->{SEQUENCE_KEY}/;
+ }
+ else {
+ $cols[$keycol] =3D~ s/\s+.*/ char(16) NOT NULL/;
+ }
+ }
=20
my $query =3D "create table $tablename ( \n";
$query .=3D join ",\n", @cols;
@@ -365,6 +379,21 @@
::logError("table %s created: %s" , $tablename, $query );
}
=20
+
+ }
+
+#::logDebug("seq: $config->{AUTO_SEQUENCE} create: $config->{SEQUENCE_CREA=
TE}");
+ if($config->{AUTO_SEQUENCE} and my $q =3D $config->{SEQUENCE_CREATE}) {
+ $q =3D~ s/_SEQUENCE_NAME_/$config->{AUTO_SEQUENCE}/g;
+ $q =3D~ s/_SEQUENCE_START_/$config->{AUTO_SEQUENCE_START} || 1/eg;
+ $q =3D~ s/_SEQUENCE_CACHE_/$config->{AUTO_SEQUENCE_CACHE} || 1/eg;
+ $q =3D~ s/_SEQUENCE_MINVAL_/$config->{AUTO_SEQUENCE_MINVAL} || 1/eg;
+ $q =3D~ s/_SEQUENCE_MAXVAL_/$config->{AUTO_SEQUENCE_MAXVAL} || 214748364=
7/eg;
+#::logDebug("create query: $q");
+ eval {
+ $db->do($q)
+ or warn("create sequence failed: $q");
+ };
}
=20
my @index;
@@ -784,7 +813,7 @@
}
=20
sub bind_entire_row {
- my($s, $sth, $key, @fields) =3D @_;
+ my($s, $sth, @fields) =3D @_;
#::logDebug("bind_entire_row=3D" . ::uneval(\@_));
#::logDebug("bind_entire_row=3D" . ::uneval(\@fields));
my $i;
@@ -794,12 +823,6 @@
my $j =3D 1;
=20
my $ki;
- if($key and ! $fields[$ki =3D $s->[$CONFIG]{KEY_INDEX}] ) {
- $name =3D [@$name];
- splice @fields, $ki, 1;
- splice @$name, $ki, 1;
- undef $key;
- }
=20
for($i =3D 0; $i < scalar @$name; $i++, $j++) {
#::logDebug("bind $j=3D$fields[$i]");
@@ -809,16 +832,28 @@
$numeric->[$i],
);
}
- $sth->bind_param(
- $j,
- $key,
- $numeric->[ $s->[$CONFIG]{KEY_INDEX} ],
- )
- if $key;
#::logDebug("last bind $j=3D$fields[$i]");
return;
}
=20
+sub autosequence {
+ my $s =3D shift;
+
+ my $cfg =3D $s->[$CONFIG];
+ # Like MySQL, get sequence number *after* insert
+ return $cfg->{SEQUENCE_VAL} if $cfg->{SEQUENCE_LAST_FUNCTION};
+
+ # Like Oracle or Pg, get it now then return passed value later
+ my $q =3D $cfg->{SEQUENCE_QUERY} || "select nextval('_SEQUENCE_NAME_')";
+ $q =3D~ s/_SEQUENCE_NAME_/$cfg->{AUTO_SEQUENCE}/g;
+ my $sth =3D $s->[$DBI]->prepare($q)
+ or die ::errmsg('prepare %s: %s', $q, $DBI::errstr);
+ $sth->execute()
+ or die ::errmsg('execute %s: %s', $q, $DBI::errstr);
+ my $k =3D $sth->fetchrow_arrayref->[0];
+ return $k;
+}
+
sub add_column {
my ($s, $column, $def) =3D @_;
return $s->alter_column($column, $def, 'ALTER_ADD');
@@ -1019,16 +1054,24 @@
$tkey =3D $s->quote($key, $s->[$KEY]) if defined $key;
#::logDebug("tkey now $tkey");
=20
+
if ( defined $tkey and $s->record_exists($key) ) {
my $fstring =3D join ",", map { "$_=3D?" } @$fary;
$sql =3D "update $s->[$TABLE] SET $fstring WHERE $s->[$KEY] =3D $tkey";
}
else {
- my ($found_key) =3D grep $_ eq $s->[$KEY], @$fary;
- unless ($found_key) {
+ my $found;
+ if(! length($key)) {
+ $key =3D $s->autonumber();
+ }
+ for(my $i =3D 0; $i < @$fary; $i++) {
+ next unless $fary->[$i] eq $s->[$KEY];
+ splice @$fary, $i;
+ splice @$vary, $i;
+ last;
+ }
unshift @$fary, $s->[$KEY];
unshift @$vary, $key;
- }
my $fstring =3D join ",", @$fary;
my $vstring =3D join ",", map {"?"} @$vary;
$sql =3D "insert into $s->[$TABLE] ($fstring) VALUES ($vstring)";
@@ -1045,7 +1088,7 @@
or die ::errmsg("execute %s: %s", $sql, $DBI::errstr);
=20
$val =3D $s->[$CONFIG]->{AUTO_SEQUENCE}
- ? $s->last_sequence_value()
+ ? $s->last_sequence_value($key)
: $key;
};
=20
@@ -1071,8 +1114,6 @@
my $cfg =3D $s->[$CONFIG];
my $ki =3D $cfg->{KEY_INDEX};
=20
- my $popkey;
-
$s->filter(\@fields, $s->[$CONFIG]{COLUMN_INDEX}, $s->[$CONFIG]{FILTER_TO=
})
if $cfg->{FILTER_TO};
my ($val);
@@ -1120,7 +1161,6 @@
=20
if(! length($fields[$ki]) ) {
$fields[$ki] =3D $s->autonumber();
- $popkey =3D 1 if $cfg->{SEQUENCE_NO_EXPLICIT} and $cfg->{AUTO_SEQUENCE};
}
elsif ( ! $s->[$CONFIG]{Clean_start}
and defined $fields[$ki]
@@ -1142,28 +1182,22 @@
$i++;
}
my $fstring =3D '';
- if ($popkey) {
- pop @ins_mark;
- $fstring =3D ' (';
- $fstring .=3D join ",", grep $_ ne $s->[$KEY], @{$s->[$NAME]};
- $fstring .=3D ')';
- }
+
my $ins_string =3D join ", ", @ins_mark;
my $query =3D "INSERT INTO $s->[$TABLE]$fstring VALUES ($ins_string)";
-#::logDebug("set_row popkey=3D$popkey query=3D$query");
+#::logDebug("set_row query=3D$query");
$cfg->{_Insert_h} =3D $s->[$DBI]->prepare($query);
die "$DBI::errstr\n" if ! defined $cfg->{_Insert_h};
}
=20
- splice (@fields, $cfg->{KEY_INDEX}, 1) if $popkey;
#::logDebug("set_row fields=3D'" . join(',', @fields) . "'" );
- $s->bind_entire_row($cfg->{_Insert_h}, $popkey, @fields);
+ $s->bind_entire_row($cfg->{_Insert_h}, @fields);
=20
my $rc =3D $cfg->{_Insert_h}->execute()
or die "$DBI::errstr\n";
=20
$val =3D $cfg->{AUTO_SEQUENCE}
- ? $s->last_sequence_value()
+ ? $s->last_sequence_value($val)
: $fields[$ki];
=20
#::logDebug("set_row rc=3D$rc key=3D$val");
@@ -1172,9 +1206,16 @@
=20
sub last_sequence_value {
my $s =3D shift;
+ my $passed =3D shift;
my $cfg =3D $s->[$CONFIG];
- my $q =3D $cfg->{SEQUENCE_LAST_FUNCTION}
- or return undef;=20
+ my $q =3D $cfg->{SEQUENCE_LAST_FUNCTION};
+
+ if (! $q) {
+ return $passed if $passed;
+ $q =3D $cfg->{SEQUENCE_VALUE_FUNCTION};
+ }
+
+ $q =3D~ s/_SEQUENCE_NAME_/$s->[$CONFIG]{AUTO_SEQUENCE}/g;
$q =3D~ s/_TABLE_/$s->[$TABLE]/g;
$q =3D~ s/_COLUMN_/$s->[$KEY]/g;
my $sth =3D $s->[$DBI]->prepare($q)
@@ -1182,14 +1223,14 @@
my $rc =3D $sth->execute()
or die ::errmsg("execute %s: %s", $q, $DBI::errstr);
my $aref =3D $sth->fetchrow_arrayref();
- if ($aref) {
- if ($aref->[0] !~ /^\d+$/) {
- die ::errmsg("bogus return value from %s: %s", $q, $aref->[0]);
- }
- $aref->[0];
- } else {
+
+ if (! $aref) {
die ::errmsg("missing return value from %s: %s", $q, $sth->err());
}
+ elsif ($aref->[0] !~ /^\d+$/) {
+ die ::errmsg("bogus return value from %s: %s", $q, $aref->[0]);
+ }
+ return $aref->[0];
}
=20
sub row {
@@ -1304,14 +1345,23 @@
);
return undef;
}
+
+ $key =3D $s->autonumber() if ! length($key);
+
my $rawkey =3D $key;
my $rawval =3D $value;
+
$key =3D $s->quote($key, $s->[$KEY]);
$value =3D $s->quote($value, $column);
my $query;
if(! $s->record_exists($rawkey)) {
if( $s->[$CONFIG]{AUTO_SEQUENCE} ) {
- $query =3D qq{INSERT INTO $s->[$TABLE] ($column) VALUES ($value)};
+ $key =3D 0 if ! $key;
+ $query =3D qq{
+ INSERT INTO $s->[$TABLE]
+ ($s->[$KEY], $column)
+ VALUES ($key, $value)
+ };
}
else {
#::logDebug("creating key '$rawkey' in table $s->[$TABLE]");