DBI::tables()

[Simon Oliver]

I noticed a curious thing when using the tables() method.  It has been 
coded in such a way to quote the schema (owner?) identifier when the 
schema name is not all uppercase? The table name identifier is never 
quoted (in my experience this is more likely to require quotation than 
the schema identifier).  There is no mention of the catalog 
(database?file?) identifier.

I suggest modification to the tables() method to remove any DBMS 
specific code (such as descriminatory quoting).  The new tables() method 
should only include additional (to the table name) table identifiers as 
defined by a set of DBI attributes set by the DBD driver.  And the 
quoting of identifiers should be controlled by a DBI attribute set by 
the DBD driver.

I propose the addition of four new DBI attributes:

QuotedIdentifiers (boolean, inherited), disabled by default
  indicates if quoted identifiers are in use

QuotedIdentifierStart (string, inherited), " (double quote) by default
  sets the start string for quoted identifiers

QuotedIdentifierEnd (string, inherited), " (double quote) by default
  sets the terminating string for quoted identifiers

TableIdentifiers (arrayref, inherited), [1,2] by default
  list of identifiers that uniquely describe a table,
  an array of zero based column indexes as returned from $dbh->table_info()

I propose the addition of one new database handle method:

quote_identifier(string, inherited)
   returns the identifier (string) appropriatly quoted:
   if QuotedIdentifiers is set returns the string quoted using the
    values of QuotedIdentifierStart and QuotedIdentifierEnd,
   if QuotedIdentifiers is not set returns the string unchanged.

I guess it would look something like:

     sub quote_identifier {
        my ($dbh, $str) = @_;
        if (defined $dbh->{QuotedIdentifiers}) {
            $str = $dbh->{QuotedIdentifierStart} .
                   $str .
                   $dbh->{QuotedIdentifierEnd};
        }
        return $str;
     }

I propose the modification of the DBI::tables() method:

     sub tables {
        my ($dbh, @args) = @_;
        my $sth = $dbh->table_info(@args);
        return unless $sth;
        my ($row, @tables);
        while($row = $sth->fetch) {
            my @ids = ();
            foreach (@{$dbh->{TableIdentifiers}}) {
                if (defined $row->[$_]) {
                    push @ids, $dbh->quote_identifier($row->[$_]);
                }
            }
            push @tables, join('.',@ids);
        }
        return @tables;
}

As a final point, some DBMS's permit the use of quoted identifiers on a 
per connection or even per statement basis by setting database options. 
  Presumably, this database option could be set by the DBD when the 
QuotedIdentifiers is STOREd?

-- 
   Simon Oliver