On Thu, Feb 20, 2003 at 06:57:37PM +0000, Tim Bunce wrote:
> Does anyone have a reasonably working implementation of column_info() for mysql?
I guess not, so I've written my own basic one and appended it below for feedback.
Tim.
sub DBD::mysql::db::column_info {
my ($dbh, $catalog, $schema, $table, $column) = @_;
return $dbh->set_err(1, "column_info doesn't support catalog or schema")
if defined $catalog or defined $schema;
return $dbh->set_err(1, "column_info doesn't support table wildcard")
if $table !~ /^\w+$/;
return $dbh->set_err(1, "column_info doesn't support column selection")
if $column ne "%";
my @names = qw(
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF
SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH
ORDINAL_POSITION IS_NULLABLE CHAR_SET_CAT
CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM COLLATION_NAME
UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME
SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY
DTD_IDENTIFIER IS_SELF_REF
);
my %col_info;
my $desc = $dbh->selectall_arrayref("DESCRIBE $table", { Columns=>{} });
my $ordinal_pos = 0;
foreach my $row (@$desc) {
my $info = $col_info{ $row->{field} } = {
TABLE_NAME => $table,
COLUMN_NAME => $row->{field},
ORDINAL_POSITION => ++$ordinal_pos,
NULLABLE => ($row->{null} eq 'YES') ? 1 : 0,
IS_NULLABLE => ($row->{null} eq 'YES') ? "YES" : "NO",
TYPE_NAME => $row->{type},
COLUMN_DEF => $row->{default},
mysql_is_pri_key => ($row->{key} eq 'PRI'),
};
my $type = $row->{type};
$type =~ m/^(\w+)(?:\((.*?)\))?\s*(.*)/;
my $basetype = $1;
my @type_params = map { s/^'(.*)'$/$1/; $_ } split /,/, $2||'';
my @type_attr = split / /, $3||'';
#warn "$type: $basetype [@type_params] [@type_attr]\n";
$info->{DATA_TYPE} = SQL_VARCHAR();
if ($basetype =~ /char|text|long/) {
$info->{DATA_TYPE} = SQL_CHAR() if $basetype eq 'char';
$info->{COLUMN_SIZE} = $type_params[0];
}
elsif ($basetype =~ /enum|set/) {
$info->{COLUMN_SIZE} = ($basetype eq 'set') ? length(join
",",@type_params) : 255; # XXX fix
$info->{"mysql_${basetype}_values"} = \@type_params;
}
elsif ($basetype =~ /int/) {
$info->{DATA_TYPE} = SQL_INTEGER();
$info->{NUM_PREC_RADIX} = 10;
$info->{COLUMN_SIZE} = $type_params[0];
}
elsif ($basetype =~ /decimal/) {
$info->{DATA_TYPE} = SQL_DECIMAL();
$info->{NUM_PREC_RADIX} = 10;
$info->{COLUMN_SIZE} = $type_params[0];
$info->{DECIMAL_DIGITS} = $type_params[1];
}
elsif ($basetype =~ /float|double/) {
$info->{DATA_TYPE} = ($basetype eq 'float') ? SQL_FLOAT() : SQL_DOUBLE();
$info->{NUM_PREC_RADIX} = 2;
$info->{COLUMN_SIZE} = ($basetype eq 'float') ? 32 : 64;
}
elsif ($basetype =~ /date|time/) { # date/datetime/time/timestamp
if ($basetype eq 'time' or $basetype eq 'date') {
$info->{DATA_TYPE} = ($basetype eq 'time') ? SQL_TYPE_TIME() :
SQL_TYPE_DATE();
$info->{COLUMN_SIZE} = ($basetype eq 'time') ? 8 : 10;
}
else { # datetime/timestamp
$info->{DATA_TYPE} = SQL_TYPE_TIMESTAMP();
$info->{SQL_DATA_TYPE} = SQL_DATETIME();
$info->{SQL_DATETIME_SUB} = $info->{DATA_TYPE} -
($info->{SQL_DATA_TYPE} * 10);
$info->{COLUMN_SIZE} = ($basetype eq 'datetime') ? 19 :
$type_params[0] || 14;
}
$info->{DECIMAL_DIGITS} = 0; # no fractional seconds
}
else {
warn "unsupported column '$row->{field}' type '$basetype' treated as
varchar";
}
$info->{SQL_DATA_TYPE} ||= $info->{DATA_TYPE};
#warn Dumper($info);
}
my $sponge = DBI->connect("DBI:Sponge:", '','')
or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr");
my $sth = $sponge->prepare("column_info $table", {
rows => [ map { [ @{$_}{@names} ] } values %col_info ],
NUM_OF_FIELDS => scalar @names,
NAME => \@names,
}) or return $dbh->DBI::set_err($sponge->err(), $sponge->errstr());
}
1;
__END__
