[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]");