Working with Metadata - MySQL - page 362

later (for example, to add a new enumeration value), you introduce a discrepancy be‐
tween the column and the script that uses it. If instead you look up the legal values using
the table metadata, the script can always produce a pop-up that contains the proper set
of values. A similar approach applies to SET columns.
To determine the permitted values for an ENUM or SET column, get its definition using
one of the techniques described in Recipe 10.6 . For example, if you select from the
INFORMATION_SCHEMA COLUMNS table, the COLUMN_TYPE value for the colors column of
the item table looks like this:
enum('chartreuse','mauve','lime green','puce')
SET columns are similar, except that they say set rather than enum . For either data type,
extract the permitted values by stripping the initial word and the parentheses, splitting
at the commas, and removing the enclosing quotes from the individual values. Let's
write a get_enumorset_info() routine to break out these values from the data-type
definition. While we're at it, we can have the routine return the column's type, its default
value, and whether values can be NULL . Then the routine can be used by scripts that may
need more than just the list of values. Here is a version in Ruby. Its arguments are a
database handle, a database name, a table name, and a column name. It returns a hash
with entries corresponding to the various aspects of the column definition (or nil if the
column does not exist):
def get_enumorset_info ( dbh , db_name , tbl_name , col_name )
row = dbh . select_one (
"SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?" ,
db_name , tbl_name , col_name )
return nil if row . nil? # no such column
info = {}
info [ "name" ] = row [ 0 ]
return nil unless row [ 1 ] =~ /^(ENUM|SET)\((.*)\)$/i # not ENUM or SET
info [ "type" ] = $1
# split value list on commas, trim quotes from end of each word
info [ "values" ] = $2 . split ( "," ) . collect { | val | val . sub ( /^'(.*)'$/ , " \\ 1" ) }
# determine whether column can contain NULL values
info [ "nullable" ] = ( row [ 2 ]. upcase == "YES" )
# get default value (nil represents NULL)
info [ "default" ] = row [ 3 ]
return info
end
The routine uses case-insensitive matching when checking the data type and nullable
attributes. This guards against future lettercase changes in metadata results.
The following example shows how to access and display each element of the hash re‐
turned by get_enumorset_info() :