MySQL / 10. Импорт и экспорт данных

Проверка корректности при помощи метаданных таблицы

Задача
Вам необходимо проверить, являются ли вводимые значения разрешенными для столбца ENUM или SET.

Решение
Получите определение столбца, извлеките список его членов и сверьте вводимое значение со списком.

Обсуждение
Некоторые виды проверки корректности данных требуют использования информации, хранящейся в базе данных. Это относится и к значениям, предполагаемым для хранения в столбце ENUM или SET, которые можно сравнить с допустимыми членами из определений таких столбцов. Проверки с использованием информации из базы данных также проводятся для значений, которые должны совпадать с перечисленными в справочных таблицах.

Например, можно потребовать, чтобы записи, содержащие идентификаторы клиентов, соответствовали записям таблицы customers, а аббревиатуры названий штатов в адресах можно сравнивать с таблицей штатов. В этом разделе описаны проверки для столбцов ENUM и SET.

Одним из способов проверки вводимых значений на соответствие разрешенным значениям для столбцов ENUM или SET является получение списка допустимых значений в массиве с помощью информации, возвращаемой запросом SHOW COLUMNS, а затем – выполнение проверки на принадлежность массиву.

Например, столбец любимого цвета color таблицы profile относится к типу ENUM и определен так:

mysql> SHOW COLUMNS FROM profile LIKE 'color'\G
*************************** 1. row ***************************
Field: color
Type: enum('blue','red','green','brown','black','white')
Null: YES
Key:
Default: NULL
Extra:

Если извлечь список перечислимых значений из Type и сохранить в массиве @members, то проверку на членство можно будет выполнить так:

$valid = grep (/^$val$/i, @members);

Конструктор образца начинается и заканчивается символами ^ и $, которые требуют полного совпадения $val с членом перечисления (а не просто подстрокой). За конструктором следует модификатор i, задающий сравнение, нечувствительное к регистру, так как столбцы ENUM не чувствительны к регистру.

В рецепте 9.6 была написана функция get_enumorset_info(), возвращающая метаданные столбцов ENUM и SET. Метаданные включали и список членов, так что используем эту функцию для создания другой полезной программы, check_enum_value(), которая извлекает разрешенные значения перечислимого типа и выполняет тест на принадлежность. Программа принимает четыре аргумента: дескриптор базы данных, имя таблицы, имя столбца ENUM и значение для проверки. В зависимости от допустимости значения возвращается «истина» или «ложь»:

sub check_enum_value
{
my ($dbh, $tbl_name, $col_name, $val) = @_;
my $valid = 0;
my $info = get_enumorset_info ($dbh, $tbl_name, $col_name);
if ($info && $info->{type} eq "enum")
{
# сравнение будет нечувствительным к регистру,
# т. к. столбцы ENUM нечувствительны к регистру.
$valid = grep (/^$val$/i, @{$info->{values}});
}
return ($valid);

}Такой тест хорошо подходит для проверки отдельных значений, например, для значения, передаваемого через веб-форму. Но если вы собираетесь тестировать ряд значений (например, целый столбец файла данных), лучше один раз считать значения перечислимого типа в память, а затем многократно использовать их для проверки каждого значения данных. Более того, гораздо более эффективными являются справочные хеши, а не массивы (по крайней мере, в Perl). Разрешенные значения перечислимого типа извлекаются и хранятся как ключи хеша. Затем для каждого вводимого значения проверяется, существует ли такой ключ хеша. Создать хеш чуть сложнее, поэтому check_enum_value() этим не занимается. Но для пакетной проверки корректности выигрыш от ускорения просмотра больше, чем расходы на формирование хеша.

Начнем с получения метаданных для столбца, затем преобразуем список разрешенных значений перечислимого типа в хеш:

my $ref = get_enumorset_info ($dbh, $tbl_name, $col_name);
my %members;
foreach my $member (@{$ref->{values}})
{
# преобразуем ключи хеша к одному регистру; ENUM нечувствителен к регистру
$members{lc ($member)} = 1;
}

Цикл делает каждый член перечислимого типа ключом элемента хеша.

В данном случае важен сам ключ, сопоставленное ему значение к делу не относится. (В приведенном примере значение устанавливается в 1, но вы можете использовать undef, 0 или любое другое значение.) Обратите внимание на то, что код перед сохранением преобразует ключи хеша к нижнему регистру. Дело в том, что ключи хеша в Perl чувствительны к регистру. Это удобно, если проверяемые значение тоже чувствительны к регистру, но столбцы ENUM не обладают таким свойством. Преобразуя перед сохранением в хеш значения перечислимого типа к определенному регистру, а затем аналогичным образом преобразуя проверяемые значения, вы тем самым делаете нечувствительным к регистру тест на существование ключа:

$valid = exists ($members{lc ($val)});

В нашем примере значения приводились к нижнему регистру, но можно выбрать и верхний, важно лишь единообразно применить преобразование ко всем значениям.

Заметьте, что проверка на существование может оказаться неудачной, если вводимое значение является пустой строкой. Вам придется придумать, как обрабатывать такую ситуацию в зависимости от столбца. Например, если столбец допускает использование значений NULL, можно интерпретировать пустую строку как эквивалент NULL, то есть как разрешенное значение.Для столбцов SET процедура проверки корректности аналогична процедуре для значений ENUM, только вводимое значение может состоять из любого количества членов SET, разделенных запятыми. Для того чтобы значение было разрешенным, каждый его элемент должен быть разрешен. Кроме того, поскольку «любое количество» включает в себя и «ноль», пустая строка является разрешенным значением для любого столбца SET.

Для разовой проверки отдельных значений ввода можно использовать служебную процедуру check_set_value(), напоминающую check_enum_value():

sub check_set_value
{
my ($dbh, $tbl_name, $col_name, $val) = @_;
my $valid = 0;
my $info = get_enumorset_info ($dbh, $tbl_name, $col_name);
if ($info && $info->{type} eq "set")
{
return 1 if $val eq ""; # пустая строка – допустимый элемент
# использовать нечувствительное к регистру сравнение
# столбцы SET не чувствительны к регистру
$valid = 1; # считать разрешенным, пока не выявлено обратное
foreach my $v (split (/,/, $val))
{
if (!grep (/^$v$/i, @{$info->{values}}))
{
$valid = 0; # значение содержит неразрешенный элемент
last;
}
}
}
return ($valid);
}

Для массового тестирования создайте хеш из разрешенных членов SET. Процедура повторяет формирование хеша из элементов ENUM:

my $ref = get_enumorset_info ($dbh, $tbl_name, $col_name);
my %members;
foreach my $member (@{$ref->{values}})
{
# преобразовать ключи хеша к одному регистру; SET не чувствителен к регистру
$members{lc ($member)} = 1;
}

Чтобы сравнить вводимое значение с хешем членов SET, преобразуйте его к тому же регистру, что и ключи хеша, разделите на части по запятым, чтобы получить список отдельных элементов значения, и проверьте каждое из них. Если один из элементов не разрешен, не разрешено и все значение:

$valid = 1; # считать разрешенным, пока не выявлено обратное
foreach my $elt (split (/,/, lc ($val)))
{
if (!exists ($members{$elt})){
$valid = 0; # значение содержит неразрешенный элемент
last;
}
}

После завершения цикла $valid – «истина», если значение допустимо для столбца SET, и «ложь» в противном случае. Пустые строки всегда разрешены для столбцов SET, но этот код не выполняет никаких специальных проверок на пустую строку. В этом нет необходимости, так как если операция split() возвращает пустой список, то цикл ни разу не выполняется, и значение $valid остается «истиной».

Статьи по MySQL на эту тему:

Диагностическая утилита для LOAD DATA
Импорт XML в MySQL
Использование временных таблиц для преобразования дат
Использование дат с недостающими частями
Обмен данными между MySQL и FileMaker Pro

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных