Re: detecting the existance of a table [was: undefined behaviour for sub-transactions?]

Tim Bunce <Tim.Bunce@xxxxxxxxx> 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!

> 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, @_); };
if($@) {
$self->{Database}->inc_transaction_error;
die "$@\n";
}
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.

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.

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?

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?

- Tyler

.