[CDBI] Re: postgres and case sensitive table names

>>> Try a variation of the following:
>>> 
>>> __PACKAGE__->table('"YourMixedCaseTableName"');
>> 
>> That seems to do the trick. Thank you!
>
> Well, I was too quick. It actually it complains:
>
> Can't insert new Zone: DBD::Pg::db last_insert_id failed: Could not
> find the table ""Zone"" [for Statement "SELECT c.oid FROM
> pg_catalog.pg_class c WHERE relname = ?"] at
> /usr/local/share/perl/5.8.8/Class/DBI.pm line 609.
>
> although I see the records being inserted with incremental id's.

Looks like you'll need to override Class::DBI's _auto_increment_value()
method in your CDBI base class as well. I would try something like the
following:

sub _auto_increment_value {
	my $self = shift;
	my $dbh  = $self->db_Main;

	# First, remove the double quotes from the table name.
	my $table = $self->table;
	$table =~ s/\"//g;
	# The remaining code is *almost* identical to the Class::DBI
	# implementation....

	# 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, $table, undef)    # std
		|| $dbh->{mysql_insertid}                             # mysql
		|| eval { $dbh->func('last_insert_rowid') }           # sqlite?
		or $self->_croak("Can't get last insert id");
	return $id;
}

It's an ugly hack, but I think it'll work....

The clean, API-approved way would be to derive a subclass of
Class::DBI::SQL::Transformer which can handle mixed-case table names
correctly by overriding the _expand_table() method in that class, I think.
I'm not sure though. This is uncharted territory for the most part.

Hope this helps,
Ed