我們有 ac（中央）服務器和幾個 d（區域服務器），例如 d1、d2、d3、d4、d5。

有一些要復制的表。為簡單起見，假設我們有一個tblFoo表，它也存在于 d1、d2、d3、d4、d5 和 c 上，并且它具有相同的結構。規則很簡單：

• 如果記錄存在于廣告服務器上，則它存在于 c 服務器上，并且每個欄位的值完全相同
• 如果記錄存在于廣告服務器上（例如在 d1 上），則它不存在于任何其他 d 服務器上（d2、d3、d4 或 d5）

目標是確保如果對廣告服務器的tblFoo( insert, update, delete) 進行了更改，那么它也應該在 c 服務器上及時完成。這很好用insert（因為 idpkFooID具有auto_increment定義的屬性）。這也適用于updateand delete，但我們對此有些擔心。這是（的簡化版本）代碼：

namespace App\ORM;

use Cake\ORM\Query as ORMQuery;
// Some other use statements

class Query extends ORMQuery
{
    //Lots of stuff...

    /**
     * Overrides a method with the same name to handle synchonizations with c
     */
    public function execute()
    {
        //Some tables need replication. If this is such a table, then we need to perform some extra steps. Otherwise we would just call the parent
        //Method
        if (($this->_repository->getIgnoreType() || (!in_array($this->type(), ['select']))) && $this->isReplicate() && ($this->getConnection()->configName() !== 'c')) {
            //Getting the table
            $table = $this->_repository->getTable();
            //Replicating the query
            $replica = clone $this;
            //Setting the connection of the replica to c, because we need to apply the district changes on central
            $replica->setParentConnectionType('d')->setConnection(ConnectionManager::get('c'));
            $replica->setIgnoreType($this->_repository->getIgnoreType());
            //We execute the replica first, because we will need to refer to c IDs and not the other way around
            $replica->execute();
            //If this is an insert, then we need to handle the ids as well
            if (!empty($this->clause('insert'))) {
                //We load the primary key's name to use it later to find the maximum value
                $primaryKey = $this->_repository->getPrimaryKey();
                //We get the highest ID value, which will always be a positive number, because we have already executed the query at the replica
                $firstID = $replica->getConnection()
                                   ->execute("SELECT LAST_INSERT_ID() AS {$primaryKey}")
                                   ->fetchAll('assoc')[0][$primaryKey];

                //We get the columns
                $columns = $this->clause('values')->getColumns();
                //In order to add the primary key
                $columns[] = $primaryKey;
                //And then override the insert clause with this adjusted array
                $this->insert($columns);
                //We get the values
                $values = $this->clause('values')->getValues();
                //And their count
                $count = count($values);
                //There could be multiple rows inserted already into the replica as part of this query, we need to replicate all their IDs, without
                //assuming that there is a single inserted record
                for ($index = 0; $index < $count; $index  ) {
                    //We add the proper ID value into all of the records to be inserted
                    $values[$index][$primaryKey] = $firstID   $index;
                }
                //We override the values clause with this adjusted array, which contains PK values as well
                $this->clause('values')->values($values);
            }
        }
        if ($this->isQueryDelete) {
            $this->setIgnoreType(false);
        }
        //We nevertheless execute the query in any case, independently of whether it was a replicate table
        //If it was a replicate table, then we have already made adjustments to the query in the if block
        return parent::execute();
    }

}

擔心如下：如果我們在 d1 上執行updateordelete陳述句，其條件將被另一個地區服務器（d2，d3，d4，d5）的記錄滿足，那么我們最終會在 d1 上正確執行updateand陳述句，但是delete一旦在 d1 執行了相同的陳述句，我們可能會不小心從 c 服務器更新/洗掉其他地區的記錄。

To remedy this issue, the proposed solution is to validate the statements and throw an exception if one of the following conditions is not met:

• the condition is either = or IN
• the field is [pk|fk]*ID, that is, a foreign key or a primary key

Tables not having the replication behavior would perform execute normally, the above restrictions would only be valid for tables having the replication behavior, such as tblFoo from our example.

The Question

How can I validate update/delete queries in my execute override so that only primary keys or foreign keys can be searched for and only with the = or IN operator?

uj5u.com熱心網友回復：

這就是我解決問題的方法。

具有復制行為的模型執行如下驗證

<?php

namespace App\ORM;

use Cake\ORM\Table as ORMTable;

class Table extends ORMTable
{
    protected static $replicateTables = [
        'inteacherkeyjoin',
    ];

    public function isValidReplicateCondition(array $conditions)
    {
        return count(array_filter($conditions, function ($v, $k) {
            return (bool) preg_match('/^[\s]*[pf]k(' . implode('|', self::$replicateTables) . ')id[\s]*((in|=).*)?$/i', strtolower(($k === intval($k)) ? $v : $k));
        }, ARRAY_FILTER_USE_BOTH)) > 0;
    }

    public function validateUpdateDeleteCondition($action, $conditions)
    {
        if ($this->behaviors()->has('Replicate')) {
            if (!is_array($conditions)) {
                throw new \Exception("When calling {$action} for replicate tables, you need to pass an array");
            } elseif (!$this->isValidReplicateCondition($conditions)) {
                throw new \Exception("Unsafe condition was passed to the {$action} action, you need to specify primary keys or foreign keys with = or IN operators");
            }
        }
    }

    public function query()
    {
        return new Query($this->getConnection(), $this);
    }
}

對于Query該類，我們有一個isReplicate觸發我們需要的驗證的方法，并且該where方法已被覆寫以確保正確驗證條件：

    /**
     * True if and only if:
     * - _repository is properly initialized
     * - _repository has the Replicate behavior
     * - The current connection is not c
     */
    protected function isReplicate()
    {
        if (($this->type() !== 'select') && ($this->getConnection()->configName() === 'c') && ($this->getParentConnectionType() !== 'd')) {
            throw new \Exception('Replica tables must always be changed from a district connection');
        }
        if (in_array($this->type(), ['update', 'delete'])) {
            $this->_repository->validateUpdateDeleteCondition($this->type(), $this->conditions);
        }

        return ($this->_repository && $this->_repository->behaviors()->has('Replicate'));
    }

    public function where($conditions = null, $types = [], $overwrite = false)
    {
        $preparedConditions = is_array($conditions) ? $conditions : [$conditions];
        $this->conditions = array_merge($this->conditions, $preparedConditions);

        return parent::where($conditions, $types, $overwrite);
    }

標籤：mysql validation orm replication cakephp-3.4

上一篇：Laravel驗證-全名欄位是必需的

下一篇：MIDV500檔案本地化：擬合問題