Database Reference
In-Depth Information
array membership tests versus hash lookups, try the
lookup_time.pl
script in the
trans
fer
directory of the
recipes
distribution.)
Begin by getting the metadata for the column and convert the list of legal enumeration
members to a hash:
my
$ref
=
get_enumorset_info
(
$dbh
,
$db_name
,
$tbl_name
,
$col_name
);
my
%members
;
# convert hash key to consistent lettercase
map
{
$members
{
lc
(
$_
)}
=
1
; }
@
{
$ref
->
{
values
}};
The
map
expression makes each enumeration member exist as the key of a hash element.
The hash key is what's important here; the value associated with it is irrelevant. (The
example shown sets the value to
1
, but you could use
undef
,
0
, or any other value.) Note
that the code converts the hash keys to lowercase before storing them. This is done
because hash key lookups in Perl are case sensitive. That's fine if the values that you
check also are case sensitive, but
ENUM
columns by default are not. By converting the
enumeration values to a given lettercase before storing them in the hash, and then
converting the values you want to check similarly, you perform, in effect, a case-
insensitive key existence test:
$valid
=
exists
(
$members
{
lc
(
$val
)});
The preceding example converts enumeration values and input values to lowercase. You
could just as well use uppercase, as long as you do so for all values consistently.
Note that the existence test may fail if the input value is the empty string. You must
decide how to handle that case on a column-by-column basis. For example, if the column
permits
NULL
values, you might interpret the empty string as equivalent to
NULL
and
thus as being a legal value.
The validation procedure for
SET
values is similar to that for
ENUM
values, except that an
input value might consist of any number of
SET
members, separated by commas. For
the value to be legal, each element in it must be legal. In addition, because “any number
of members” includes “none,” the empty string is a legal value for any
SET
column.
For one-shot testing of individual input values, use a utility routine
check_set_val
ue()
that is similar to
check_enum_value()
:
sub
check_set_value
{
my
(
$dbh
,
$db_name
,
$tbl_name
,
$col_name
,
$val
)
=
@_
;
my
$valid
=
0
;
my
$info
=
get_enumorset_info
(
$dbh
,
$db_name
,
$tbl_name
,
$col_name
);
if
(
$info
&&
uc
(
$info
->
{
type
})
eq
"SET"
)
{
return
1
if
$val
eq
""
;
# empty string is legal element
# use case-insensitive comparison because default collation
# (latin1_swedish_ci) is case-insensitive (adjust if you use
