Часто в проектах требуется обновление в БД множественных полей каких-либо документов. Наверное существуют готовые решения, но вбив в гугл "изменение множественных свойств документов", "обработка множественных полей", "обработка табличных полей" и т.д., я не нашел никакого решения, поэтому решил написать свое и заодно описать его в этой статье.
Все примеры будут на PHP, а используемая база данных mysql. Хотя, приведенный код не привязан к какой-либо базе данных, он всего лишь определяет, какие строки должны быть удалены, какие добавлены, а какие модифицированы. Далее эти "указания" можно легко реализовать для любой БД.
Например, мы имеем простой документ с одиночными свойствами типа "идентификатор", "название", "дата создания", а так же к этому документу имеется множественное поле - таблица управления доступом в виде: код пользователя, время и дата начала разрешения доступа, и время и дата с которой доступ запрещается.
Структуру данных конкретного документа в PHP можно изобразить примерно так:
$document["id"] = "1"; // идентификатор документа
$document["name"] = "Название документа"; // название документа
$document["create_date"] = "25-10-2012"; // дата создания документа
$document["permissions_table"] = array(
array(
"user_id" => 1, // Код пользователя
"grant_from" => "2012-10-25 00:00:00", // Время и дата с которого доступ разрешается
"grant_to" => "2012-10-27 23:59:59" // Время и дата с которого доступ запрещается
)
);
Примем, что в БД такой документ будет храниться в двух таблицах:
/*
- document_header (тут храним одиночные свойства, одна строка - один документ)
id INT NOT NULL AUTOINCREMENT
name TEXT NOT NULL
create_date DATETIME NOT NULL
- document_permissions (тут храним множественные свойства, один документ - много строк)
id INT NOT NULL AUTOINCREMENT
document_id INT NOT NULL
user_id INT NOT NULL
grant_from DATETIME
grant_to DATETIME
*/
Далее представим, что возникает задача изменения таблицы доступа нашего документа. При этом после сабмита пользователем, мы получаем в обработку два массива нашего документа: массив со старыми данными и массив с новыми данными.
Теперь рассмотрим, как нам эти массивы преобразовать в последовательность SQL запросов для изменения документа в БД.
С плоскими данными все достаточно просто, их можно обработать следующим кодом
$changes = array();
foreach($old_document as $k => $v) {
if($k == "permissions_table")
continue;
if($old_document[$k] != $new_document[$k])
$changes[$k] = $new_document[$k];
}
$changes["id"] = $old_document["id"]
$changes["document_id"] = $old_document["document_id"]
В итоге мы получаем массив $changes с измененными полями и их новыми значениями, который легко преобразовать в запрос базы данных UPDATE. Не хочу перегружать статью этой операцией, поэтому опустим ее.
Далее нам необходимо обработать изменения в таблице доступа к документу и произвести необходимые изменения в БД.
Тут могут случиться разные ситуации, например:
- старая строка была изменена
- была добавлена новая строка
- старая строка была удалена
- изменился порядок строк
И любые комбинации вышеуказанных операций.
При этом нам необходимо сгенерировать соответствующие запросы в БД и выполнить их обязательно в следующей последовательности:
1. Удаление лишних строк
2. Изменение существующих строк
3. Добавление новых строк
Удаление производится в первую очередь для того, чтобы не было конфликта уникальных ключей в БД при операциях добавления или модификации.
В итоге нам необходима функция, которая получает на вход массивы старых и новых значений, а на выходе дает нам три массива с линиями таблицы, которые необходимо удалить, изменить и добавить в нашу БД.
Для этой задачи я написал универсальную функцию, которая работает с любой конфигурацией табличных данных. Лишь с некоторыми ограничениями, которые указаны в конце статьи.
Итак, волшебная функция имеет следующий интерфейс:
/*
@$data - массив с новыми табличными данными
@$old_data - массив со старыми табличными данными
@$keys - ключевые поля в строках табличных данных (необязательный, см. в конце статьи описание по настройке)
@$hidden_keys - ключевые поля в таблице БД, отвечающей за наши данные (см. в конце статьи описание по настройке)
@$options - опции выполнения
*/
На выходе мы получаем заветные массивы delete, update, insert, которые легко преобразовать в запросы БД для последующего их выполнения.
Исходный код функции:
static function generateOperationsFromMultiData($data, $old_data, $keys, $hidden_keys, $options) {
$out = array("insert" => array(), "update" => array(), "delete" => array());
$unique_elements = array();
$unique_keys = array();
// Обходим все старые данные и считаем для каждой строки хеш
$old_elements_hashes = array();
$old_elements_keys = array();
foreach($old_data as $k => $fields) {
$res = self::__getKeyAndHashFromLine($fields, $keys, $hidden_keys);
$old_data[$k]["___key"] = $res["key"];
$old_data[$k]["___hash"] = $res["hash"];
if($res["key"]) {
$old_elements_hashes[$res["key"]] = $res["hash"];
$old_elements_keys[$res["key"]] = $k;
}
else {
$old_elements_hashes[$k] = $res["hash"];
}
}
// Обходим все новые данные
$data = array_merge($data);
foreach($data as $k => $fields) {
$res = self::__getKeyAndHashFromLine($fields, $keys);
$data[$k]["___key"] = $res["key"];
$data[$k]["___hash"] = $res["hash"];
foreach($hidden_keys as $k2)
unset($fields[$k2]);
// Если включен флаг уникальности выкидываем неуникальные элементы
if($options["unique"]) {
if(in_array($res["hash"], $unique_elements))
continue;
else
$unique_elements[] = $res["hash"];
}
if($res["key"]) {
// Проверяем чтобы данные были уникальные в пределах ключа
if(in_array($res["key"], $unique_keys))
continue;
else
$unique_keys[] = $res["key"];
// Добавляем строку если в старых данных нет такого ключа
if(!isset($old_elements_hashes[$res["key"]]))
$out["insert"][$k] = $fields;
else {
// Такой ключ существует в старых данных, сравниваем хеши данных
if($res["hash"] != $old_elements_hashes[$res["key"]]) {
// Добавляем к строке скрытые ключи из старых данных
foreach($hidden_keys as $v) {
$fields[$v] = $old_data[$old_elements_keys[$res["key"]]][$v];
}
// Добавляем новые данные в массив обновления данных
$out["update"][$k] = $fields;
}
$old_data[$old_elements_keys[$res["key"]]]["___new_key"] = $k;
unset($old_elements_hashes[$res["key"]]);
unset($old_elements_keys[$res["key"]]);
}
} else {
// Если ключ не задан просто проверяем наличие хеша в старых данных
if($key = array_keys($old_elements_hashes, $res["hash"])) {
$key = current($key);
unset($old_elements_hashes[$key]);
$old_data[$key]["___new_key"] = $k;
} else {
// Если хеш не найден, добавляем новые данные
$out["insert"][$k] = $fields;
}
}
}
// Остатки из old_data помещаем в массив для удаления
if($keys)
foreach($old_elements_keys as $k => $v) {
unset($old_data[$v]["___key"]);
unset($old_data[$v]["___hash"]);
unset($old_data[$v]["___new_key"]);
$out["delete"][] = $old_data[$v];
unset($old_data[$v]);
}
else
foreach($old_elements_hashes as $k => $v) {
unset($old_data[$k]["___key"]);
unset($old_data[$k]["___hash"]);
unset($old_data[$k]["___new_key"]);
$out["delete"][] = $old_data[$k];
unset($old_data[$k]);
}
// Выполняем проверку порядка следования данных
// Выравниваем индексы данных
$old_data = array_merge($old_data);
$data = array_merge($data);
if($options["save_order"]) {
$delete = false;
// Обходим старые данные (остались только те, которые должны остаться и некоторые или все из них в update массиве)
if($old_data[0]["___new_key"] != "0")
$delete = true;
foreach($old_data as $k => $v) {
// Если не совпали ключи, значит с этого момента необходимо перезаписать данные
if($v["___new_key"] != $k)
$delete = true;
if($delete) {
unset($old_data[$k]["___key"]);
unset($old_data[$k]["___hash"]);
unset($old_data[$k]["___new_key"]);
unset($data[$v["___new_key"]]["___key"]);
unset($data[$v["___new_key"]]["___hash"]);
$out["delete"][] = $old_data[$k];
foreach($hidden_keys as $hk) {
$data[$v["___new_key"]][$hk] = $old_data[$k][$hk];
}
$out["insert"][$v["___new_key"]] = $data[$v["___new_key"]];
if($keys)
unset($out["update"][$v["___new_key"]]);
}
}
}
$out["update"] = array_merge($out["update"]);
ksort($out["insert"]);
$out["insert"] = array_merge($out["insert"]);
return $out;
}
// Получение ключа из строки
function __getKeyAndHashFromLine($line, $keys, $hide_keys = array()) {
$hash = $line;
// Удаляем ключи
foreach($keys as $v)
unset($hash[$v]);
foreach($hide_keys as $v)
unset($hash[$v]);
// Считаем хеш строки
$hash = serialize($hash);
// ключ строки
$key = "";
foreach($keys as $v)
$key .= "__" . $line[$v];
return array("hash" => $hash, "key" => $key);
}
В нашем случае вызов функции будет выглядеть следующим образом:
$result = generateOperationsFromMultiData($new_document["permissions_table"], $old_document["permissions_table"], false, array("id"), array("unique" => false));
В итоге $result будет содержать три массива: delete, update, insert.
Приведу пару примеров для наглядности.
В первом примере мы имеем две строки для двух пользователей. Эмулируя изменения, вводим третьего пользователя и удаляем второго. При этом специально дублируем строку с первым пользователем и делаем изменения в датах обоих строк для него.
$old_document["permissions_table"] = array(
array(
"id" => 1,
"document_id" => 1,
"user_id" => 1,
"grant_from" => "2012-10-25 00:00:00",
"grant_to" => "2012-10-27 00:00:00"
),
array(
"id" => 2,
"document_id" => 1,
"user_id" => 2,
"grant_from" => "2012-10-25 00:00:00",
"grant_to" => "2012-10-27 00:00:00"
)
);
$new_document["permissions_table"] = array(
array(
"document_id" => 1,
"user_id" => 3,
"grant_from" => "2012-10-25 00:00:00",
"grant_to" => "2012-10-27 00:00:00"
),
array(
"document_id" => 1,
"user_id" => 1,
"grant_from" => "2012-10-25 00:00:00",
"grant_to" => "2012-10-03 00:00:00"
),
array(
"document_id" => 1,
"user_id" => 1,
"grant_from" => "2012-10-25 00:00:00",
"grant_to" => "2012-10-31 00:00:00"
)
);
generateOperationsFromMultiData(
$new_document["permissions_table"],
$old_document["permissions_table"],
array("user_id"),
array("id"),
array(
"unique" => false,
"save_order" => false
)
)
На выходе мы получим:
Array
(
[insert] => Array
(
[0] => Array
(
[document_id] => 1
[user_id] => 3
[grant_from] => 2012-10-25 00:00:00
[grant_to] => 2012-10-27 00:00:00
)
)
[update] => Array
(
[0] => Array
(
[document_id] => 1
[user_id] => 1
[grant_from] => 2012-10-25 00:00:00
[grant_to] => 2012-10-03 00:00:00
[id] => 1
)
)
[delete] => Array
(
[0] => Array
(
[id] => 2
[document_id] => 1
[user_id] => 2
[grant_from] => 2012-10-25 00:00:00
[grant_to] => 2012-10-27 00:00:00
)
)
)
В итоге, получается, что нам надо вставить строку с третьим пользователем, удалить строку со вторым пользователем и сделать изменения в первом пользователе. При этом обратите внимание, что изменения для первого пользователя берутся из первой строки для данного пользователя в массиве$new_document[permissions_table] . Этим я хотел показать корректную обработку "задублированных данных".
Теперь рассмотрим параметры, которые управляют поведением функции.
$keys - массив ключей табличных данных. Если указаны, то функция автоматически начнет выкидывать строки с повторяющимися ключами, в вышеуказанном примере указан ключ "user_id", таким образом, мы запретили повторение строк для одного и того же пользователя. Ключи могут быть составные, для этого достаточно просто их указать в массиве данного параметра. Если ключи не задать, то функция будет по прежнему работоспособна, однако придется ввести в БД какой-нибудь id, по которому надо будет отличать одну строку от другой. Его надо будет прописать в $hidden_keys функции.
$hidden_keys - скрытые ключи, это ключи, которые могут присутствовать в массиве старых данных и отсутствовать в массиве измененных данных. При этом функция автоматически переносит эти ключи в выходные массивы удаления и изменения строк. В частности это нужно для управления множественными данными, когда строка состоит из одного или нескольких не ключевых полей. Тогда для идентификации таких строк вводится уникальный id в БД, но его не обязательно "тягать" в форму и следить за его "сохранностью" при редактировании. Функция сама определит какие строки с определенным id удалить, какие изменить, и какие строки добавить новыми.
$options[unique] - если установить этот флаг в true, то помимо уникальности ключей функция начнет проверять уникальность остальных данных строки, то есть в нашем случае при установленном флаге$options[unique] мы не смогли бы указать одни и те же периоды для разных пользователей, при чем функция оставила бы первый попавшийся период и удалила последующие повторяющиеся.
$options[save_order] - флаг, который указывает на необходимость сохранения порядка следования данных в БД, такого же как в массиве $data (в нашем примере $new_document[permissions_table]). Чтобы понять, как действует этот флаг рассмотрим результат нашего примера, но только с установленным флагом $options[save_order]:
Array
(
[insert] => Array
(
[0] => Array
(
[document_id] => 1
[user_id] => 3
[grant_from] => 2012-10-25 00:00:00
[grant_to] => 2012-10-03 00:00:00
)
[1] => Array
(
[document_id] => 1
[user_id] => 1
[grant_from] => 2012-10-25 00:00:00
[grant_to] => 2012-10-03 00:00:00
[id] => 1
)
)
[update] => Array
(
)
[delete] => Array
(
[0] => Array
(
[id] => 2
[document_id] => 1
[user_id] => 2
[grant_from] => 2012-10-25 00:00:00
[grant_to] => 2012-10-27 00:00:00
)
[1] => Array
(
[id] => 1
[document_id] => 1
[user_id] => 1
[grant_from] => 2012-10-25 00:00:00
[grant_to] => 2012-10-27 00:00:00
)
)
)
Мы видим, что сначала функция предлагает удалить все существующие записи, чтобы вставить новую запись с третьим пользователем и после нее запись с первым пользователем. Таким образом, в БД мы получим такую же последовательность при выборке без сортировки, как в нашем новом массиве. С помощью такой функциональности можно сэкономить на дополнительном поле, которое понадобилось бы нам для сортировки строк. Однако если строка вставляется в середину или в начало таблицы, придется сначала удалить все данные следующие за этой строкой, а затем их заново добавить (эти действия мы и увидели в последнем примере)
Примечание: функция всегда предлагает минимальное количество запросов к БД для достижения желаемого результата.
Ограничения: данные строк должны быть только плоскими на момент выполнения фукнции. Поля не должны называться: "___key", "___hash", "___new_key".