Модификация в БД табличных или множественных полей документов

Часто в проектах требуется обновление в БД множественных полей каких-либо документов. Наверное существуют готовые решения, но вбив в гугл «изменение множественных свойств документов», «обработка множественных полей», «обработка табличных полей» и т.д., я не нашел никакого решения, поэтому решил написать свое и заодно описать его в этой статье.


Все примеры будут на 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".
Share post
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 4

    0
    А не проще создать временную таблицу с результатами в MySQL а потом запустить два запроса — один на delete а второй на upsert. Тогда по сети данные нужно будет гонять только один раз и вся логика будет в одном месте в этих двух SQLях.
      0
      Да, действительно можно и так, и в скорости мы получим выйгрыш однозначно, правда тогда я не очень представляю, как в таком случае можно реализовать логику сохранения порядка следования строк как в новом массиве (без заведения соответствующего ключа). То есть например, когда мы просто меням местами две строчки, при этом значения всех полей остаются как есть.

      И второй момент, в форме вывода надо будет отслеживать ключи БД, которые могут быть нарушены пользователем. Например, когда он случайно удалит строку, и потом не сохраняя документ вновь ее добавит с теми же значениями, в результате мы потеряем ключ БД этой строки, и он будет изменен после сохранения, что не есть хорошо в некоторых случаях. Ну и дополнительная логика в обработке формы появляется.
        0
        По первому пункту, я считаю, что добавления номера строки только на пользу пойдет, так как порядок строк будет сохранен в явном виде.
        По второму — тоже — ну и что строка будет удалена и потом снова создана?
        А если это частый use-case, то надо просто undelete в gui добавить и показывать ту же строку, как она была сначала считана из базы.

        Иногда возможно лучше логику в коде держать, но где-то я слышал такую максиму, что все, что можно сделать в SQL, надо делать в SQL. По крайней мере чтобы не изобретать велосипед, не терять время на сетевые операции и облегчать масштабирование.
          0
          В моем частном случае этот код работает в составе CMS, в которой все делается максимально модульно и гибко. И в дальнейшем при замене в каких то определенных случаях самой БД, например, на Nosql или любую другую, проблем по адаптации будет намного меньше, по этому лично для этого проекта он подходит больше. Ваш вариант тоже очень интересный, так как тут помимо скорости можно выйграть в более простой реализации. Если кто-то захочет воспользоваться данными методами у него всегда будет выбор.

          Абсолютно согласен и с тем, что максимум надо делать в SQL, особенно когда application работает на скриптовых языках, которые по определению в десятки раз медленнее, плюс экономия на коммуникациях с базой.

    Only users with full accounts can post comments. Log in, please.