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

в 10:53, , рубрики: mysql, php, Алгоритмы, метки: , ,

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


Все примеры будут на 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"];
            
            // Если включен флаг уникальности выкидываем неуникальные элементы
            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($keys) {
                    // Если не совпали ключи, значит с этого момента необходимо перезаписать данные
                    if($data[$v["___new_key"]]["___key"] != $old_data[$k]["___key"])
                        $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"]];
                        unset($out["update"][$v["___new_key"]]);
                    }
                } else {
                    // Ключи не заданы ориентируемся по хешам
                    // Если не совпали хеши, значит с этого момента необходимо перезаписать данные
                    if($data[$v["___new_key"]]["___hash"] != $old_data[$k]["___hash"])
                        $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"]];
                    }
                }
            }
        }
        $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".

Автор: grishao


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js