title: How can I validate fields and criteria at execute override?
body: <p>We have a c (central) server and several d (district servers), such as d1, d2, d3, d4, d5.</p>
  <p>There are some tables that are to be replicated. For the sake of simplicity, let's assume that we have a <code>tblFoo</code> table that exists on d1, d2, d3, d4, d5 and c as well and it has the same structure in all. The rules are simple:</p>
  <ul>
  <li>if a record exists at a d server, then it exists on the c server and has the exact same values for each fields</li>
  <li>if a record exists on a d server (for example on d1), then it does not exist on any other d server (d2, d3, d4 nor d5)</li>
  </ul>
  <p>The goal is to make sure that if a change is made on <code>tblFoo</code> (<code>insert</code>, <code>update</code>, <code>delete</code>) for a d server, then it should be done promptly on the c server as well. This works nicely for <code>insert</code> (because the id, <code>pkFooID</code> is having the <code>auto_increment</code> property by definition). This also works for <code>update</code> and <code>delete</code>, but we have some worries about those. This is (the simplified version of the) code:</p>
  <pre><code>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-&gt;_repository-&gt;getIgnoreType() || (!in_array($this-&gt;type(), ['select']))) &amp;&amp; $this-&gt;isReplicate() &amp;&amp; ($this-&gt;getConnection()-&gt;configName() !== 'c')) {
              //Getting the table
              $table = $this-&gt;_repository-&gt;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-&gt;setParentConnectionType('d')-&gt;setConnection(ConnectionManager::get('c'));
              $replica-&gt;setIgnoreType($this-&gt;_repository-&gt;getIgnoreType());
              //We execute the replica first, because we will need to refer to c IDs and not the other way around
              $replica-&gt;execute();
              //If this is an insert, then we need to handle the ids as well
              if (!empty($this-&gt;clause('insert'))) {
                  //We load the primary key's name to use it later to find the maximum value
                  $primaryKey = $this-&gt;_repository-&gt;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-&gt;getConnection()
                                     -&gt;execute(&quot;SELECT LAST_INSERT_ID() AS {$primaryKey}&quot;)
                                     -&gt;fetchAll('assoc')[0][$primaryKey];
  
                  //We get the columns
                  $columns = $this-&gt;clause('values')-&gt;getColumns();
                  //In order to add the primary key
                  $columns[] = $primaryKey;
                  //And then override the insert clause with this adjusted array
                  $this-&gt;insert($columns);
                  //We get the values
                  $values = $this-&gt;clause('values')-&gt;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 &lt; $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-&gt;clause('values')-&gt;values($values);
              }
          }
          if ($this-&gt;isQueryDelete) {
              $this-&gt;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();
      }
  
  }
  </code></pre>
  <p><strong>The worry is the following</strong>: If we execute <code>update</code> or <code>delete</code> statements on d1 whose condition would be met by records at another district server (d2, d3, d4, d5), then we would end up with the <code>update</code> and <code>delete</code> statement being correctly executed on d1, but once the same statements are executed at d1, we may accidentally update/delete records of other districts from the c server.</p>
  <p>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:</p>
  <ul>
  <li>the condition is either <code>=</code> or <code>IN</code></li>
  <li>the field is <code>[pk|fk]*ID</code>, that is, a foreign key or a primary key</li>
  </ul>
  <p>Tables not having the replication behavior would perform <code>execute</code> normally, the above restrictions would only be valid for tables having the replication behavior, such as <code>tblFoo</code> from our example.</p>
  <h1>The Question</h1>
  <p><strong>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?</strong></p>