Modification in the database tabular or multiple fields of documents

Often, projects require updating the multiple fields of any documents in the database. Probably there are ready-made solutions, but by typing in Google "changing the multiple properties of documents", "processing multiple fields", "processing table fields", etc., I did not find any solution, so I decided to write my own and at the same time describe it in this article .


All examples will be in PHP, and the mysql database used. Although, the above code is not tied to any database, it only determines which lines should be deleted, which lines should be added, and which lines should be modified. Further, these "instructions" can be easily implemented for any database.

For example, we have a simple document with single properties such as "identifier", "name", "creation date", and there is also a multiple field for this document - an access control table in the form: user code, time and date the access permission began, and time and date from which access is prohibited.
')
The data structure of a particular document in PHP can be represented like this:

$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" //        ) ); 

We assume that in the database such a document will be stored in two tables:

 /* - 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 */ 

Further we will present that there is a task of change of the access table of our document. At the same time, after the user submits, we receive two arrays of our document for processing: an array with old data and an array with new data.

Now we will consider how to convert these arrays into a sequence of SQL queries to modify the document in the database.

With flat data, everything is quite simple, they can be processed with the following code

 $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"] 

As a result, we get an array of $ changes with modified fields and their new values, which is easy to convert to an UPDATE database query. I do not want to overload the article with this operation, so we will omit it.

Next, we need to process the changes in the table of access to the document and make the necessary changes in the database.
Different situations can happen here, for example:
- old line has been changed
- a new line has been added
- old line has been deleted
- the row order has changed

And any combination of the above operations.

At the same time, we need to generate the corresponding queries in the database and execute them in the following sequence:
1. Delete extra lines
2. Modify existing rows
3. Adding new lines
Deletion is performed primarily to ensure that there is no conflict of unique keys in the database during add or modify operations.

As a result, we need a function that receives arrays of old and new values ​​as input, and at the output gives us three arrays with table lines that need to be deleted, modified and added to our database.

For this task, I wrote a universal function that works with any configuration of tabular data. Only with some restrictions that are listed at the end of the article.

So, the magic function has the following interface:

 /* @$data -      @$old_data -      @$keys -       (, .      ) @$hidden_keys -     ,     (.      ) @$options -   */ 

At the output, we get the treasured arrays of delete, update, insert, which are easy to convert to database queries for later execution.

Function source code:

 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); } 

In our case, the function call will look like this:

 $result = generateOperationsFromMultiData($new_document["permissions_table"], $old_document["permissions_table"], false, array("id"), array("unique" => false)); 

As a result, $ result will contain three arrays: delete , update , insert .

I will give a couple of examples for clarity.
In the first example, we have two lines for two users. Emulating changes, we enter the third user and we delete the second. In this case, we specifically duplicate the line with the first user and make changes in the dates of both lines for it.

 $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 ) ) 

At the output we get:

 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 ) ) ) 

As a result, it turns out that we need to insert a line with the third user, delete the line with the second user and make changes in the first user. At the same time note that the changes for the first user are taken from the first row for this user in the $ new_document [permissions_table] array. By this, I wanted to show the correct processing of the “dubbed data”.

Now consider the parameters that control the behavior of the function.

$ keys - an array of table data keys. If specified, the function will automatically start throwing out lines with duplicate keys; in the above example, the key “user_id” is indicated, so we have forbidden repeating lines for the same user. Keys can be composite, for this purpose it is enough just to specify them in the array of this parameter. If the keys are not set, the function will still work, but you will have to enter some id into the database, by which it will be necessary to distinguish one line from another. You will need to register it in the $ hidden_keys function.

$ hidden_keys - hidden keys, these are keys that may be present in the array of old data and absent in the array of changed data. In this case, the function automatically transfers these keys to the output arrays for deleting and modifying strings. In particular, this is necessary for managing multiple data when a string consists of one or several non-key fields. Then, in order to identify such lines, a unique id is entered in the database, but it is not necessary to “force” it into the form and monitor its “integrity” when editing. The function itself will determine which lines with a specific id to delete, which ones to change, and which lines to add with new ones.

$ options [unique] - if you set this flag to true , then besides the uniqueness of the keys, the function will begin to check the uniqueness of the remaining data of the string, that is, in our case, if the $ options [unique] flag is set, we could not specify the same periods for different users, and the function would leave the first available period and delete the subsequent repeated ones.

$ options [save_order] is a flag that indicates the need to preserve the order of data in the database, the same as in the $ data array (in our example, $ new_document [permissions_table] ). To understand how this flag works, consider the result of our example, but only with the $ options [save_order] flag set:

 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 ) ) ) 

We see that the function first suggests deleting all existing records in order to insert a new record with a third user and after it a record with the first user. Thus, in the database we get the same sequence when sampling without sorting, as in our new array. With this functionality, you can save on the extra field that we would need to sort the rows. However, if a row is inserted in the middle or at the beginning of the table, you will first have to delete all the data following this row, and then add it again (we saw these actions in the last example)

Note: the function always offers the minimum number of queries to the database to achieve the desired result.

Restrictions: the data of lines should be only flat at the time of execution of the function. Fields should not be called: "___key", "___hash", "___new_key".