[CDBI] Class::DBI patch

Veselin Slavov <vess-TY/[email protected]> Thu, 17 Jan 2008 20:18:51 +0200

Newsgroups	gmane.comp.lang.perl.modules.class-dbi
Message-ID	<[email protected]>

Hello Tony,

I'm using '*Class::DBI*' in my projects with '*DBD::Pg*' database 
driver. When use some tables like this
*__PACKAGE__->table('schema_name.table_name');*
and inserts more than 1 record - inserting failed!
To simulate this problem please see attached file '*simulate.pl*'.
I found solution - *Class_DBI.diff* - and think it will be useful for 
other PostgreSQL users also.

I will be very happy if You apply patch in future version of package.

Thanks in advice!

Best regards,
vess

_______________________________________________
ClassDBI mailing list
ClassDBI-Ra3b/[email protected]
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi

Class_DBI.diff (text/x-patch, 1.1 KB)

--- Class/DBI.pm.orig	Thu Jan 17 14:44:35 2008
+++ Class/DBI.pm	Thu Jan 17 15:12:41 2008
@@ -599,12 +599,24 @@
 	my $self = shift;
 	my $dbh  = $self->db_Main;
 
+	# In PostgreSQL when using more than 1 schema and
+	# $self->table =~ 'schema_name.table_name'
+	# geting id failed. So must separete $self->table to
+	# to 2 components - schema and table name
+	my $table = $self->table;
+	my $schema;
+	if ( $table =~ /^(\w+)\.(\w+)$/ )
+   	{
+      		$schema = $1;
+      		$table = $2;
+   	}
+
 	# Try to do this in a standard method. Fall back to MySQL/SQLite
 	# specific versions. TODO remove these when last_insert_id is more
 	# widespread.
 	# Note: I don't believe the last_insert_id can be zero. We need to
 	# switch to defined() checks if it can.
-	my $id = $dbh->last_insert_id(undef, undef, $self->table, undef)    # std
+	my $id = $dbh->last_insert_id(undef, $schema, $table, undef)    # std
 		|| $dbh->{mysql_insertid}                                         # mysql
 		|| eval { $dbh->func('last_insert_rowid') }
 		or $self->_croak("Can't get last insert id");

simulate.pl (application/x-perl, 1.1 KB)

#!/usr/bin/perl -w
use strict;

package MYDBI;
use base 'Class::DBI';

 __PACKAGE__->connection('dbi:Pg:database=testdbi', 'pgsql','', { AutoCommit => 1 });

1;

package TestDBI;
use base 'MYDBI';

__PACKAGE__->table( 'test.table1' );
__PACKAGE__->columns( Primary => 'id');
__PACKAGE__->columns( All => qw( data date));

1;

package MAIN;

use DBI;

my $dbh = DBI->connect("dbi:Pg:database=postgres", 'pgsql');
$dbh->do("create database testdbi");
$dbh->disconnect;

$dbh = DBI->connect("dbi:Pg:database=testdbi", 'pgsql');
$dbh->do("create schema test");

my $sql=<<_SQL;
	create table test.table1 (
		id serial primary key,
		data integer,
		date timestamp default current_timestamp
	);
_SQL
$dbh->do( $sql );
$dbh->disconnect;

for( my $i=1; $i <= 10; $i++ ){
	TestDBI->insert({ data => $i });
}

my $records = TestDBI->retrieve_all;
while( my $r = $records->next ){
	printf "[%s][%s]\n",$r->data,$r->date;
}

#	When use more than 'public' schema in PostgreSQL DB 
#	and package used Class::DBI 
#	and have __PACKAGE__->table('schema_name.table_name'); 
#	- inserting in table failed if inserts are more than 1