MySQL Cookbook

9.9 Applying Table Structure Information

9.9.1 Problem

It's all well and good to be able to obtain table structure information, but what can you use it for?

9.9.2 Solution

Lots of things: displaying lists of table columns, creating web form elements, producing ALTER TABLE statements for modifying ENUM or SET columns, and more.

9.9.3 Discussion

This section describes some applications for the table structure information that MySQL provides.

9.9.4 Displaying Column Lists

Probably the simplest use of table information is to present a list of the table's columns. This is common in web-based or GUI applications that allow users to construct queries interactively by selecting a table column from a list and entering a value against which to compare column values. The various versions of the get_column_names_with_show( ) or get_column_names_with_meta( ) functions shown earlier in the chapter can serve as the basis for such list displays.

9.9.5 Interactive Record Editing

Knowledge of a table's structure can be very useful for interactive record-editing applications. Suppose you have an application that retrieves a record from the database, displays a form containing the record's content so a user can edit it, and then updates the record in the database after the user modifies the form and submits it. You can use table structure information for validating column values. For example, if a column is an ENUM, you can find out the valid enumeration values and check the value submitted by the user against them to determine whether or not it's legal. If the column is an integer type, you check the submitted value to make sure that it consists entirely of digits, possibly preceded by a sign character. If the column contains dates, look for a legal date format.

But what if the user leaves a field empty? If the field corresponds to, say, a CHAR column in the table, do you set the column value to NULL or to the empty string? This too is a question that can be answered by checking the table's structure. Determine whether or not the column can contain NULL values. If it can, set the column to NULL; otherwise, set it to the empty string.

9.9.6 Mapping Column Types onto Web Page Elements

Some column types such as ENUM and SET correspond naturally to elements of web forms:

• An ENUM has a fixed set of values from which you choose a single value. This is analogous to a group of radio buttons, a pop-up menu, or a single-pick scrolling list.

• A SET column is similar, except that you can select multiple values; this corresponds to a group of checkboxes or a multiple-pick scrolling list.

If you access the information for these types of columns using SHOW COLUMNS, you can easily determine the legal values for a column and map them onto the appropriate form element automatically. This allows you to present users with a list of applicable values from which selections can be made easily without any typing. Earlier in this chapter we saw how to get ENUM and SET column metadata. The methods developed there are used in Chapter 18, which discusses form generation in more detail.

9.9.7 Adding Elements to ENUM or SET Column Definitions

It's really a pain to add a new element to an ENUM or SET column definition when you use ALTER TABLE, because you have to list not only the new element, but all the existing elements as well. One approach for doing this using mysqldump and an editor is described in Recipe 8.3. Another way to accomplish this task is to write your own program that does most of the work for you by using column metadata. Let's develop a Python script add_element.py that generates the appropriate ALTER TABLE statement automatically when given a table name, an ENUM or SET column name, and the new element value. Suppose you want to add "hot pink" to the colors column of the item table. The current structure of the column looks like this:

mysql> SHOW COLUMNS FROM item LIKE 'colors'\G
*************************** 1. row ***************************
  Field: colors
   Type: set('chartreuse','mauve','lime green','puce')
   Null: YES
    Key:
Default: puce
  Extra:

add_element.py will use that information to figure out the correct ALTER TABLE statement and write it out:

% ./add_element.py item colors "hot pink"
ALTER TABLE item
    MODIFY colors
    set('chartreuse','mauve','lime green','puce','hot pink')
    NULL DEFAULT 'puce';

By having add_element.py produce the statement as its output, you have the choice of shoving it into mysql for immediate execution or saving the output into a file:

% ./add_element.py item colors "hot pink" | mysql cookbook
% ./add_element.py item colors "hot pink" > stmt.sql

You might choose the latter course if you want the new element somewhere other than at the end of the list of values, which is where add_element.py will put it. In this case, edit stmt.sql to place the element where you want it, then execute the statement:

% vi stmt.sql
% mysql cookbook < stmt.sql

The first part of the add_element.py script imports the requisite modules and checks the command-line arguments. This is fairly straightforward:

#! /usr/bin/python
# add_element.py - show ALTER TABLE statement for ENUM or SET column
# (assumes cookbook database)

import sys
sys.path.insert (0, "/usr/local/apache/lib/python")
import re
import MySQLdb
import Cookbook

if len (sys.argv) != 4:
    print "Usage: add_element.py tbl_name col_name new_element"
    sys.exit (1)
tbl_name = sys.argv[1]
col_name = sys.argv[2]
elt_val = sys.argv[3]

After connecting to the MySQL server (code not shown), we need to run a SHOW COLUMNS query to retrieve information about the designated column. The following code does this, checking to make sure that the column really exists in the table:

cursor = conn.cursor ( )
# escape SQL pattern characters in column name to match it literally
esc_col_name = re.sub (r'([%_])', r'\\\1', col_name)
# this is *not* a use of placeholders
cursor.execute ("SHOW COLUMNS FROM %s LIKE '%s'" % (tbl_name, esc_col_name))
info = cursor.fetchone ( )
cursor.close
if info == None:
    print "Could not retrieve information for table %s, column %s" \
                                            % (tbl_name, col_name)
    sys.exit (1)

At this point, if the SHOW COLUMNS statement succeeded, the information produced by it is available as a tuple stored in the info variable. We'll need to use several elements from this tuple. The most important is the column type value, which provides the enum(...) or set(...) string containing the column's current definition. We can use this to verify that the column really is an ENUM or SET, then add the new element to the string just before the closing parenthesis. For the colors column, we want to change this:

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

To this:

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

It's also necessary to check whether column values can be NULL and what the default value is so that the program can add the appropriate information to the ALTER TABLE statement. The code that does all this is as follows:

# get column type string; make sure it begins with ENUM or SET
type = info[1]
if not re.match ('(enum|set)', type):
    print "table %s, column %s is not an ENUM or SET" % (tbl_name, col_name)
    sys.exit(1)
# add quotes, insert comma and new element just before closing paren
elt_val = conn.literal (elt_val)
type = re.sub ('\)$', ',' + elt_val + ')', type)

# determine whether column can contain NULL values
if info[2] == "YES":
    nullable = "NULL"
else:
    nullable = "NOT NULL";

# construct DEFAULT clause (add surrounding quotes unless
# value is NULL)
default = "DEFAULT " + conn.literal (info[4])

print "ALTER TABLE %s\n\tMODIFY %s\n\t%s\n\t%s %s;" \
            % (tbl_name, col_name, type, nullable, default)

That's it. You now have a working ENUM- or SET-altering program. Still, add_element.py is fairly basic and could be improved in various ways:

• Make sure that the element value you're adding to the column isn't already there.

• Allow add_element.py to take more than one argument after the column name and add all of them to the column definition at the same time.

• Add an option to indicate that the named element should be deleted rather than added.

• Add an option that tells the script to execute the ALTER TABLE statement immediately rather than displaying it.

• If you have a version of MySQL older than 3.22.16, it won't understand the MODIFY col_name syntax used by add_element.py. You may want to edit the script to use CHANGE col_name syntax instead. The following two statements are equivalent:

  ALTER TABLE tbl_name MODIFY col_name col_definition;
  ALTER TABLE tbl_name CHANGE col_name col_name col_definition;

  add_element.py uses MODIFY because it's less confusing than CHANGE.

9.9.8 Retrieving Dates in Non-ISO Format

MySQL stores dates in ISO 8601 format (CCYY-MM-DD), but it's often desirable or necessary to rewrite date values, such as when you need to transfer table data into another program that expects dates in another format. You can write a script that retrieves and prints table rows, using column metadata to detect DATE, DATETIME, and TIMESTAMP columns, and reformat them with DATE_FORMAT( ) into whatever date format you want. (For an example, see Recipe 10.34, which describes a short script named iso_to_us.pl that uses this technique to rewrite ISO dates into U.S. format.)

9.9.9 Converting Character Columns Between Fixed-Length and Variable-Length Types

CHAR columns have a fixed length, whereas VARCHAR columns are variable length. In general, tables that use CHAR columns can be processed more quickly but take up more space than tables that use VARCHAR columns. To make it easier to convert tables to use CHAR or VARCHAR columns, you can use the information provided by SHOW COLUMNS to generate an ALTER TABLE statement that performs the requisite column conversions. Here is a Python function alter_to_char( ) that creates a statement for changing all the VARCHAR columns to CHAR:

def alter_to_char (conn, tbl_name):
    cursor = conn.cursor ( )
    cursor.execute ("SHOW COLUMNS FROM " + tbl_name)
    rows = cursor.fetchall ( )
    cursor.close ( )
    str = ""
    for info in rows:
        col_name = info[0]
        type = info[1]
        if re.match ('varchar', type):      # it's a VARCHAR column
            type = re.sub ("var", "", type) # convert to CHAR
            # determine whether column can contain NULL values
            if info[2] == "YES":
                nullable = "NULL"
            else:
                nullable = "NOT NULL";
            # construct DEFAULT clause (add surrounding quotes unless
            # value is NULL)
            default = "DEFAULT " + conn.literal (info[4])
            # add MODIFY clause to string
            if str != "":
                str = str + ",\n\t"
            str = str + \
                    "MODIFY %s %s %s %s" % (col_name, type, nullable, default)
    cursor.close ( )
    if str == "":
        return None
    return "ALTER TABLE " + tbl_name + "\n\t" + str

Suppose you have a table that looks like this:

CREATE TABLE chartbl
(
    c1  VARCHAR(10),
    c2  VARCHAR(10) BINARY,
    c3  VARCHAR(10) NOT NULL DEFAULT 'abc\'def'
);

If you pass the name of that table to the alter_to_varchar( ) function, the statement that it returns looks like this:

ALTER TABLE chartbl
    MODIFY c1 char(10) NULL DEFAULT NULL,
    MODIFY c2 char(10) binary NULL DEFAULT NULL,
    MODIFY c3 char(10) NOT NULL DEFAULT 'abc\'def'

A function to convert columns in the other direction (from CHAR to VARCHAR) would be similar. Here is an example, this time in Perl:

sub alter_to_varchar
{
my ($dbh, $tbl_name) = @_;
my ($sth, $str);

    $sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
    $sth->execute ( );
    while (my @row = $sth->fetchrow_array ( ))
    {
        if ($row[1] =~ /^char/)     # it's a CHAR column
        {
            $row[1] = "var" . $row[1];
            $str .= ",\n\t" if $str;
            $str .= "MODIFY $row[0] $row[1]";
            $str .= ($row[2] eq "YES" ? "" : " NOT") . " NULL";
            $str .= " DEFAULT " . $dbh->quote ($row[4]);
        }
    }
    $str = "ALTER TABLE $tbl_name\n\t$str" if $str;
    return ($str);
}

For completeness, the function generates an ALTER TABLE statement that explicitly converts all CHAR columns to VARCHAR. In practice, it's necessary to convert only one such column. MySQL notices the change of a column from fixed-length to variable-length format, and automatically converts any other fixed-length columns that have a variable-length equivalent.

9.9.10 Selecting All Except Certain Columns

Sometimes you want to retrieve "almost all" the columns from a table. Suppose you have an image table that contains a BLOB column named data used for storing images that might be very large, and other columns that characterize the BLOB column, such as its ID, a description, and so forth. It's easy to write a SELECT * query that retrieves all the columns, but if all you need is the descriptive information about the images and not the images themselves, it's inefficient to drag the BLOB values over the connection along with the other columns. Instead, you want to select everything in the record except the data column.

Unfortunately, there is no way to say directly in SQL, "select all columns except this one." You must explicitly name all the columns except data. On the other hand, it's easy to construct that kind of query by using table structure information. Extract the list of column names, delete the one to be excluded, then construct a SELECT query from those columns that remain. The following example shows how to do this in PHP, using the get_column_names_with_show( ) function developed earlier in the chapter to obtain the column names from a table:

$names = get_column_names_with_show ($conn_id, $tbl_name);
$query = "";
# construct list of columns to select: all but "data"
reset ($names);
while (list ($index, $name) = each ($names))
{
    if ($name == "data")
        continue;
    if ($query != "")   # put commas between column names
        $query .= ",";
    $query .= $name;
}
$query = "SELECT $query FROM $tbl_name";

The equivalent Perl code for constructing the query is a bit shorter (and correspondingly more cryptic):

my @names = get_column_names_with_show ($dbh, $tbl_name);
my $query = "SELECT "
            . join (",", grep (!/^data$/, @names))
            . " FROM $tbl_name";

Whichever language you use, the result is a query that you can use to select all columns but data. It will be more efficient than SELECT * because it won't pull the BLOB values over the network. Of course, this process does involve an extra round trip to the server to execute the statement that retrieves the column names, so you should consider the context in which you plan to use the SELECT query. If you're just going to retrieve a single record, it might be more efficient simply to select the entire row than to incur the overhead of the extra round trip. But if you're selecting many rows, the reduction in network traffic achieved by skipping the BLOB columns will be worth the overhead of the additional query for getting table structure.