Database Reference
In-Depth Information
Solution
Check them, possibly rewriting them into a more suitable format.
Discussion
Recipes in
Chapter 11
show how to work with the structural characteristics of files, by
reading lines and breaking them into separate columns. But sometimes you must focus
on the data content of a file, not only its structure:
• It's often a good idea to validate data values to make sure they're legal for the data
types into which you store them. For example, you can make sure that values in‐
tended for
INT
,
DATE
, and
ENUM
columns are integers, dates in
CCYY-MM-DD
format,
and legal enumeration values, respectively.
• Data values may need reformatting. You might store credit card values as a string
of digits but permit users of a web application to separate blocks of digits by spaces
or dashes. These values must be rewritten before storing them. Rewriting dates from
one format to another is especially common; for example, if a program writes dates
in
MM-DD-YY
format to ISO format for import into MySQL. If a program understands
only date and time formats and not a combined date-and-time format (such as
MySQL uses for the
DATETIME
and
TIMESTAMP
data types), you must split date-and-
time values into separate date and time values.
This is the first of a set of recipes that describe validation and reformatting techniques
that help in these kinds of situations. Techniques covered here for checking values in‐
clude pattern matching and validation against information in a database. It's not unusual
for certain validation operations to occur repeatedly, in which case you'll probably find
it useful to construct a library of functions. By packaging validation operations as library
routines, it is easier to write utilities based on them, and the utilities make it easier to
perform command-line operations on entire files so that you can avoid editing them
yourself.
To avoid writing your own library routines, look around to see if someone else has
already written suitable routines that you can use. For example, if you check the Perl
CPAN (
cpan.perl.org
), you'll find a Data::Validate module hierarchy. The modules there
provide library routines that standardize a number of common validation tasks. Da‐
ta::Validate::MySQL deals specifically with MySQL data types.
