On Tue, Nov 29, 2005 at 04:05:26PM -0800, Tyler MacDonald wrote:
> Tim Bunce <[EMAIL PROTECTED]> wrote:
> > PostgreSQL is non-standard (and inconvenient) in this respect.
>
> I chatted with Mischa (my work's resident DB guru) about this, and
> according to him, the error behaviour when you attempt to SELECT from a
> table that does not exist is "undetermined" in the SQL standard, so it
> really is the individual DBMS' choice. I think that's actually worse; all of
> these DBMs are behaving completely differently but still "correctly" on such
> a basic SQL operation due to a lack of standard!
I was thinking more generally: the failure of a statement within a
transaction (such as an insert getting a duplicate key error) usually
rolls-back just that statement and does not abort the whole transaction.
If that's not true for PostgreSQL then that's certainly inconvenient.
> > There isn't, as far as I know, except to accept the 'lowest common
> > denominator'. In this case that means forcing a rollback if any
> > statement fails.
>
> > execute() is sufficient if the driver doesn't also supply it's own do()
> > because DBI's default do() calls execute(). But some drivers do supply
> > their own do() method (for good reasons).
>
> Fair enough. So what I've done, is modified DBIx::Transaction to
> mark a transaction error if any query in the transaction returns false;
>
> --snip--
> sub execute {
> my $self = shift;
> my $rv = eval { DBI::st::execute($self, @_); };
I'd probably say:
my $rv = eval { $self->SUPER::execute(@_) };
> if($@) {
> $self->{Database}->inc_transaction_error;
> die "[EMAIL PROTECTED]";
> }
> if(!$rv) {
> $self->{Database}->inc_transaction_error;
> }
> return $rv;
> }
> --snip--
>
> (and similar logic for db::do()).
>
> The package I'm working on that uses DBIx::Transaction now also
> checks for the existance of a table before attempting to manipulate it.
See note above. I think that's just one example of a more general issue.
> I was considering using the "table_info" method for this, but
> there's a problem there; I don't know how to ask DBI what database/catalog
> name I am currently working in, and "undef" is documented as returning
> tables in *every* database, not just the current one.
This is something I've been meaning to address for a while. I was
thinking of something like:
$schema_name = $dbh->current_schema
> So what I've done instead is defaulted to this query to check for a
> table:
>
> SELECT 1 FROM information_schema.tables WHERE table_name = ?
>
> ... then for MySQL,
>
> SHOW TABLES LIKE ?
>
> ... and SQLite2,
>
> SELECT 1 FROM sqlite_master WHERE type ='table' AND name = ?
>
> The default query does work for postgres, and I'm told I can expect
> it to work with M$SQL, Sybase, and Oracle as well. Can you think of any DBI
> drivers I should expect it to *not* work with?
Very few databases support information_schema.
The generic portable fallback is "select 1 from $table where 1=0" - if that
statement can be executed without error then the table exists.
> DB::Introspector claims to help you do this, so I might just use it.
> Although it does a lot more stuff that I don't need, and I only see MySQL,
> Oracle, and Postgres subclasses for it. Is there a better module or method
> out there?
I don't know off-hand.
Why not help save the world and help me add current_schema() to the DBI
and send implementations to the authors of drivers you're using?
Tim.
