MatchgridBuilder.php

<?php
/**
 * COmanage Match Matchgrid Builder
 *
 * Portions licensed to the University Corporation for Advanced Internet
 * Development, Inc. ("UCAID") under one or more contributor license agreements.
 * See the NOTICE file distributed with this work for additional information
 * regarding copyright ownership.
 *
 * UCAID licenses this file to you under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with the
 * License. You may obtain a copy of the License at:
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * @link          http://www.internet2.edu/comanage COmanage Project
 * @package       match
 * @since         COmanage Match v1.0.0
 * @license       Apache License, Version 2.0 (http://www.apache.org/licenses/LICENSE-2.0)
 */

declare(strict_types = 1);

namespace App\Lib\Match;

use Cake\Datasource\EntityInterface;

use Doctrine\DBAL\Connection as DBALConnection;
use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\Exception;
use Doctrine\DBAL\Schema\Comparator;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Schema\SchemaException;
use App\Lib\Enum\ReferenceIdEnum;

class MatchgridBuilder {
  use \App\Lib\Traits\DatabaseTrait;

  /**
   * Build the requested Matchgrid.
   *
   * @since  COmanage Match v1.0.0
   * @param EntityInterface $Matchgrid  Matchgrid Object, contain()d to include MatchgridSettings
   * @param array           $attributes Array of Attributes
   * @param bool            $indexes    Whether to build indexes (disable for bulk loading only)
   * @throws SchemaException|DBALException|Exception
   */
  
  public function build(EntityInterface $Matchgrid, array $attributes, bool $indexes=true): void
  {
    // Connect to the database
    $dbc = $this->connectDatabase();

    // Build and execute the schema
    $this->configToSchema($dbc, $Matchgrid, $attributes, $indexes);

    $this->disconnectDatabase();
  }

  /**
   * Convert a Matchgrid Attribute configuration into a DBAL schema.
   *
   * @since  COmanage Match v1.0.0
   * @param DBALConnection $dbc         DBAL Connection Object
   * @param EntityInterface $Matchgrid  Matchgrid Object
   * @param array $attributes           Array of Attributes
   * @param bool $indexes               Whether to build indexes (disable for bulk loading only)
   * @throws SchemaException
   */

  protected function configToSchema(
    DBALConnection $dbc,
    EntityInterface $Matchgrid,
    array $attributes,
    bool $indexes=true): void
  {
    // Unlike ADOdb, there is no native DBAL format. We could create a JSON
    // document similar to what DatabaseCommand uses, but the use case is just
    // different enough that it's not really worth the effort at the moment.
    
    // Just let any errors bubble up the stack.
    
    $schema = new Schema();
    
    // Create the table
    $table = $schema->createTable($Matchgrid->prefixed_table_name);

    // For type definitions see https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#types

    // There are various mandatory columns that we hardcode here.
    $table->addColumn("id", "integer", ['autoincrement' => true, 'notnull' => true]);
    // Maybe SOR Label and ID should be UI configured so length can be set?
    $table->addColumn("sor", "string", ['length' => 64, 'notnull' => true]);
    // See also ResultManager::getResultsForJson special handling
    $table->addColumn("sorid", "string", ['length' => 64, 'notnull' => true]);
    $table->addColumn("referenceid", "string", ['length' => 64, 'notnull' => false]);
    $table->addColumn("request_time", "datetime", ['notnull' => true]);
    $table->addColumn("resolution_time", "datetime", ['notnull' => false]);
    
    // Add in the configured fields
    foreach($attributes as $attr) {
      // XXX everything is a varchar because we don't have a configuration option for field type
      $table->addColumn($attr->name, "string", ['length' => 80, 'notnull' => false]);
    }
    
    $table->setPrimaryKey(["id"]);
    
    // Track which attributes need case insensitive indexes
    $ciAttrs = [];
    
    if($indexes) {
      // Since index names need to be unique across the schema, we'll use the
      // matchgrid ID to make the names unique.
      
      // $flags and $options as passed to Index(), but otherwise undocumented
      $flags = [];
      $options = [];
      $indexPrefix = "matchgrid_" . $Matchgrid->id;

      $i = 1;
      
      // Start with the standard indexes
      $indexLabel = $indexPrefix . "_i";
      $table->addIndex(['sor'], $indexLabel.$i++, $flags, $options);
      $table->addIndex(['sorid'], $indexLabel.$i++, $flags, $options);
      $table->addUniqueIndex(['sor', 'sorid'], $indexLabel.$i++, $options);
      $table->addIndex(['referenceid'], $indexLabel.$i++, $flags, $options);
      $table->addIndex(['resolution_time'], $indexLabel.$i++, $flags, $options);
      
      // Add in indexes for configured fields
      foreach($attributes as $attr) {
        // We use the Entity ID to provide some level of reproducibility
        $indexName = $indexPrefix . '_attr_id' . $attr->id;

        $table->addIndex([$attr->name], $indexName, $flags, $options);

        if(!$attr->case_sensitive) {
          $ciAttrs[$indexName] = $attr->name;
        }
      }
    }

    // If the Matchgrid uses Sequence for the referenceid_method, create the sequence
    if($Matchgrid->matchgrid_setting->referenceid_method == ReferenceIdEnum::Sequence) {
      $schema->createSequence($Matchgrid->matchgrid_setting->ref_id_sequence_name,
                              1,
                              $Matchgrid->matchgrid_setting->referenceid_start);
    }
    // else we could dropSequence, but for now we'll leave abandoned sequences in place
    
    // We're done with the table assembly, so move on to running the schema.
    
    // This is the SQL that represents the desired state of the database
    $toSql = $schema->toSql($dbc->getDatabasePlatform());
    
    // SchemaManager provides info about the database
    $sm = $dbc->createSchemaManager();
    
    // This is the current database representation
    $curSchema = $sm->createSchema();

    $fromSql = $curSchema->toSql($dbc->getDatabasePlatform());
    
    $comparator = new Comparator();
    $schemaDiff = $comparator->compare($curSchema, $schema);
    
    $diffSql = $schemaDiff->toSaveSql($dbc->getDatabasePlatform());
    
    // We don't start a transaction since in general we always want to move to
    // the desired state, and if we fail in flight it's probably a bug that
    // needs to be fixed.
    
    foreach($diffSql as $sql) {
      $matches = false;
      if(preg_match("/^CREATE INDEX .* \(resolution_time\)$/", $sql)) {
        // We need the resolution_time index to sort NULLS FIRST, and this is
        // currently the least painful way to do it
        $sql = rtrim($sql, ")") . " NULLS FIRST)";
      } elseif(preg_match("/^CREATE INDEX (\w+) ON .*/", $sql, $matches)) {
        // If we're creating an index on a case insensitive attribute, swap out
        // the SQL for a lower cased index
        if(!empty($ciAttrs[ $matches[1] ])) {
          $attr = $ciAttrs[ $matches[1] ];
          // Not ideal to hardcode SQL, but this should be pretty portable.
          // Note the validation rule for table_name and attr is pretty restrictive,
          // so we don't need to SQL escape them. Similarly, we create the index
          // name above.
          // We use IF NOT EXISTS to avoid errors on rebuilding the matchgrid,
          // however a better long term solution would be to add native support to
          // DBAL for expression in index creation (CO-2217).
          $sql = "CREATE INDEX IF NOT EXISTS " . $matches[1] . " ON " . $Matchgrid->prefixed_table_name . " (LOWER(" . $attr . "))";
        }
      }

      // $stmt just returns the query string so we don't bother examining it
      $stmt = $dbc->executeQuery($sql);
    }
  }

  /**
   * Remove the Matchgrid table.
   * 
   * @since  COmanage Match v1.1.0
   * @param  EntityInterface $Matchgrid   Matchgrid Object
   * @return bool                         True on success
   */

  public function removeTable(EntityInterface $Matchgrid) {
    // Connect to the database
    $dbc = $this->connectDatabase();

    // We just construct the drop statement here since it's simple and the
    // table_name is constrained from having any special characters
    $sql = "DROP TABLE " . $Matchgrid->prefixed_table_name;

    // $stmt just returns the query string so we don't bother examining it
    $stmt = $dbc->executeQuery($sql);

    $this->disconnectDatabase();

    return true;
  }
  
  /**
   * Determine if the Matchgrid table exists.
   * 
   * @since  COmanage Match v1.1.0
   * @param  EntityInterface $Matchgrid   Matchgrid Object
   * @return bool                         True if the Matchgrid table exists, false otherwise
   */
  
  public function tableExists(EntityInterface $Matchgrid) {
    // Connect to the database
    $dbc = $this->connectDatabase();

    $sm = $dbc->getSchemaManager();
    
    $ret = $sm->tablesExist($Matchgrid->prefixed_table_name);

    $this->disconnectDatabase();

    return $ret;
  }
}