Recipe 9.7. Getting ENUM and SET Column Information

Problem

You want to know what members an ENUM or SET column has.

Solution

This problem is a subset of getting table structure metadata. Obtain the column definition from the table metadata, and then extract the member list from the definition.

Discussion

It's often useful to know the list of legal values for an ENUM or SET column. Suppose that you want to present a web form containing a pop-up menu that has options corresponding to each legal value of an ENUM column, such as the sizes in which a garment can be ordered, or the available shipping methods for delivering a package. You could hardwire the choices into the script that generates the form, but if you alter the column later (for example, to add a new enumeration value), you introduce a discrepancy between the column and the script that uses it. If instead you look up the legal values using the table metadata, the script always produces a pop-up that contains the proper set of values. A similar approach can be used with SET columns.

To find out what values an ENUM or SET column can have, get the column definition using one of the techniques described in Section 9.6 and look at the data type in the definition. 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:

set('chartreuse','mauve','lime green','puce') 

ENUM columns are similar, except that they say enum rather than set. For either data type, the allowable values can be extracted by stripping off 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?   info = {}   info["name"] = row[0]   return nil unless row[1] =~ /^(ENUM|SET)\((.*)\)$/i   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 possible lettercase changes in metadata results.

The following example shows one way to access and display each element of the hash returned by get_enumorset_info⁠(⁠ ⁠ ⁠):

info = get_enumorset_info(dbh, db_name, tbl_name, col_name) puts "Information for " + db_name + "." + tbl_name + "." + col_name + ":" if info.nil?   puts "No information available (not an ENUM or SET column?)" else   puts "Name: " + info["name"]   puts "Type: " + info["type"]   puts "Legal values: " + info["values"].join(",")   puts "Nullable: " + (info["nullable"] ? "yes" : "no")   puts "Default value: " + (info["default"].nil? ? "NULL" : info["default"]) end 

That code produces the following output for the item table colors column:

Information for cookbook.item.colors: Name: colors Type: set Legal values: chartreuse,mauve,lime green,puce Nullable: yes Default value: puce 

Equivalent routines for other APIs are similar. Such routines are especially handy for generating list elements in web forms. (See Recipes Section 19.2 and Section 19.3.)