tripal_elasticsearch.install

<?php

/**
 * @file
 * Input, update, and delete data for build search blocks.
 */

/**
 * Implements hook_schema().
 */
function tripal_elasticsearch_schema() {
  $schema['tripal_elasticsearch'] = [
    'description' => 'The table for store data for building search blocks',
    'fields' => [
      'index_name' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'table_name' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'index_field' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'form_field_type' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'form_field_title' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'form_field_description' => [
        'type' => 'text',
      ],
      'form_field_options' => [
        'type' => 'text',
      ],
      'form_field_weight' => [
        'type' => 'varchar',
        'length' => '255',
      ],
    ],
  ];

  $schema['tripal_elasticsearch_links'] = [
    'description' => t(
      'A table for storing data for adding page links to search results'
    ),
    'fields' => [
      'index_name' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'table_name' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'index_field' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'field_url' => [
        'type' => 'varchar',
        'length' => '255',
      ],
    ],
  ];

  $schema['tripal_elasticsearch_servers'] = [
    'description' => t(
      'Stores URLs and configuration for remote elasticsearch servers.'
    ),
    'fields' => [
      'id' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE],
      'url' => ['type' => 'varchar', 'length' => '255'],
      'label' => ['type' => 'varchar', 'length' => '255'],
      'description' => ['type' => 'text'],
      'logo' => ['type' => 'text'],
    ],
    'primary key' => ['id'],
  ];

  $schema['tripal_elasticsearch_queues'] = [
    'description' => 'Store elasticsearch queue information',
    'fields' => [
      'id' => [
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ],
      'index_name' => [
        'type' => 'varchar',
        'length' => '255',
        'not null' => TRUE,
      ],
      'total' => [
        'type' => 'int',
        'default' => 0,
        'not null' => TRUE,
      ],
      'completed' => [
        'type' => 'int',
        'default' => 0,
        'not null' => TRUE,
      ],
      'last_run_at' => [
        'type' => 'int',
        'not null' => TRUE,
      ],
    ],
    'primary key' => ['id'],
    'indexes' => [
      'index_name_index' => ['index_name'],
    ],
  ];

  $schema['tripal_elasticsearch_priority'] = [
    'description' => t('Stores TripalEntity field priority settings.'),
    'fields' => [
      'id' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE],
      'field_id' => ['type' => 'int', 'not null' => TRUE],
      'priority' => ['type' => 'int', 'not null' => TRUE],
    ],
    'primary key' => ['id'],
  ];

  $schema['tripal_elasticsearch_indices'] = [
    'description' => 'Store elasticsearch indices',
    'fields' => [
      'id' => [
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ],
      'index_name' => [
        'type' => 'varchar',
        'length' => '255',
      ],
      'table_name' => [
        'type' => 'varchar',
        'length' => '255',
      ],
    ],
    'primary key' => ['id'],
    'unique keys' => [
      'index_name_unique_constraint' => ['index_name'],
    ],
  ];

  return $schema;
}

/**
 * Uninstall
 */
function tripal_elasticsearch_uninstall() {
  // Get the schema
  $schema = tripal_elasticsearch_schema();

  // Auto drop anything in the schema
  foreach ($schema as $table => $fields) {
    if (db_table_exists($table)) {
      db_drop_table($table);
    }
  }

  // Drop tables that are created in the update functions
  if (db_table_exists('tripal_elasticsearch_indices')) {
    db_drop_table('tripal_elasticsearch_indices');
  }
}

/**
 *  Run updates on install
 */
function tripal_elasticsearch_install() {
  tripal_elasticsearch_update_7200();
  tripal_elasticsearch_update_7201();
  tripal_elasticsearch_update_7202();
  tripal_elasticsearch_update_7203();
  tripal_elasticsearch_update_7204();
  tripal_elasticsearch_update_7205();
  tripal_elasticsearch_update_7206();
  tripal_elasticsearch_update_7207();
  tripal_elasticsearch_update_7208();
}

/**
 * create tripal_elasticsearch_indices table to store indices information.
 */
function tripal_elasticsearch_update_7200() {
  if (!db_table_exists('tripal_elasticsearch_indices')) {
    // Create new table
    $schema = tripal_elasticsearch_schema();
    $tripal_elasticsearch_indices = $schema['tripal_elasticsearch_indices'];
    db_create_table(
      'tripal_elasticsearch_indices',
      $tripal_elasticsearch_indices
    );
  }

  // Add a new column to the old table
  if (!db_field_exists('tripal_elasticsearch', 'index_id')) {
    $spec = [
      'type' => 'int',
      'unsigned' => TRUE,
      'description' => "Foreign key references id on tripal_elasticsearch_indices",
      'not null' => TRUE,
      'default' => 1,
    ];
    db_add_field('tripal_elasticsearch', 'index_id', $spec);
  }

  // Assign the right values to the new id field in tripal_elasticsearch_indices
  $fields = db_query('SELECT * FROM {tripal_elasticsearch_indices}')->fetchAll(
  );
  foreach ($fields as $field) {
    db_query(
      'UPDATE {tripal_elasticsearch} SET index_id=:id WHERE index_name=:index_name',
      [
        ':index_name' => $field->index_name,
        ':id' => $field->id,
      ]
    );
  }
}

/**
 * Add field to tripal_elasticsearch_indices table to indicate whether an index
 * is exposed via elasticsearch or not
 */
function tripal_elasticsearch_update_7201() {
  if (!db_field_exists('tripal_elasticsearch_indices', 'exposed')) {
    $spec = [
      'type' => 'int',
      'size' => 'small',
      'description' => "Exposes index to other sites for cross-site search",
      'not NULL' => TRUE,
      'default' => 0,
    ];
    db_add_field('tripal_elasticsearch_indices', 'exposed', $spec);
  }
}

/**
 * Create the tripal_elasticsearch_queues table to track queue progress.
 */
function tripal_elasticsearch_update_7202() {
  if (!db_table_exists('tripal_elasticsearch_queues')) {
    $tripal_elasticsearch_queues = [
      'description' => 'Store elasticsearch queue information',
      'fields' => [
        'id' => [
          'type' => 'serial',
          'unsigned' => TRUE,
          'not null' => TRUE,
        ],
        'index_name' => [
          'type' => 'varchar',
          'length' => '255',
          'not null' => TRUE,
        ],
        'total' => [
          'type' => 'int',
          'default' => 0,
          'not null' => TRUE,
        ],
        'completed' => [
          'type' => 'int',
          'default' => 0,
          'not null' => TRUE,
        ],
        'last_run_at' => [
          'type' => 'int',
          'not null' => TRUE,
        ],
      ],
      'primary key' => ['id'],
      'indexes' => [
        'index_name_index' => ['index_name'],
      ],
    ];

    db_create_table(
      'tripal_elasticsearch_queues',
      $tripal_elasticsearch_queues
    );
  }
}

/**
 * Add type field to tripal_elasticsearch_queues table.
 */
function tripal_elasticsearch_update_7203() {
  if (!db_field_exists('tripal_elasticsearch_queues', 'type')) {
    $spec = [
      'type' => 'varchar',
      'length' => '255',
      'not null' => FALSE,
    ];
    db_add_field('tripal_elasticsearch_queues', 'type', $spec);
  }
}

/**
 * Add started_at field to tripal_elasticsearch_queues table.
 */
function tripal_elasticsearch_update_7204() {
  if (!db_field_exists('tripal_elasticsearch_queues', 'started_at')) {
    $spec = [
      'type' => 'int',
      'not null' => FALSE,
    ];
    db_add_field('tripal_elasticsearch_queues', 'started_at', $spec);
  }
}

/**
 * Add url field to tripal_elasticsearch_indices table.
 */
function tripal_elasticsearch_update_7205() {
  if (!db_field_exists('tripal_elasticsearch_indices', 'url')) {
    $spec = [
      'type' => 'varchar',
      'length' => '255',
      'not null' => FALSE,
    ];
    db_add_field('tripal_elasticsearch_indices', 'url', $spec);
  }
}

/**
 * Add tripal elasticsearch servers table.
 */
function tripal_elasticsearch_update_7206() {
  if (!db_table_exists('tripal_elasticsearch_servers')) {
    $spec = [
      'description' => t(
        'Stores URLs and configuration for remote elasticsearch servers.'
      ),
      'fields' => [
        'id' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE],
        'url' => ['type' => 'varchar', 'length' => '255'],
        'label' => ['type' => 'varchar', 'length' => '255'],
        'description' => ['type' => 'text'],
      ],
      'primary key' => ['id'],
    ];
    db_create_table('tripal_elasticsearch_servers', $spec);
  }
}

/**
 * Create tripal elasticsearch priority table.
 */
function tripal_elasticsearch_update_7207() {
  if (!db_table_exists('tripal_elasticsearch_priority')) {
    $spec = [
      'description' => t('Stores TripalEntity field priority settings.'),
      'fields' => [
        'id' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE],
        'field_id' => ['type' => 'int'],
        'priority' => ['type' => 'int'],
      ],
      'primary key' => ['id'],
    ];
    db_create_table('tripal_elasticsearch_priority', $spec);
  }
}

/**
 * Add priority field to tripal_elasticsearch_queues table.
 */
function tripal_elasticsearch_update_7208() {
  if (!db_field_exists('tripal_elasticsearch_queues', 'priority')) {
    $spec = [
      'type' => 'int',
      'not null' => TRUE,
      'default' => 1,
    ];
    db_add_field('tripal_elasticsearch_queues', 'priority', $spec);
  }
}

/**
 * Create new gene search elasticsearch fields.
 */
function tripal_elasticsearch_update_7209() {
  try {
    $es = new ESInstance();
    $es->putMapping('gene_search_index', 'entity_id', 'integer');
    $es->putMapping('gene_search_index', 'node_id', 'integer');
  } catch (Exception $exception) {
    print "ERROR: failed to add new elasticsearch fields due to the following error\n";
    print $exception->getMessage();

    throw $exception;
  }
}

/**
 * Remove blast database content type from website index.
 */
function tripal_elasticsearch_update_7210() {
  try {
    $es = new ESInstance();
    if (in_array('website', $es->getIndices())) {
      return;
    }
    $count = $es->setWebsiteSearchParams('*', 'Blast Database', 'website')
      ->count();
    $client = $es->client;

    if ($count === 0) {
      print "No blast database content was found\n";
      return;
    }

    for ($i = 0; $i < $count; $i += 100) {
      $results = $es->setWebsiteSearchParams(
        '*',
        'Blast Database',
        'website',
        'website',
        [
          $i,
          100,
        ]
      )->search(FALSE);
      foreach ($results as $result) {
        $client->deleteByQuery(
          [
            'index' => 'website',
            'type' => 'website',
            'body' => [
              'query' => [
                'match' => [
                  'nid' => $result['nid'],
                ],
              ],
            ],
          ]
        );
      }
    }

    print "Successfully removed all blast database nodes from website index.\n";
  } catch (Exception $exception) {
    print "ERROR: failed to remove blast database nodes from website index due to the following exception\n";
    print $exception->getMessage();

    throw $exception;
  }
}

/**
 * Remove blastdb content type from website index.
 */
function tripal_elasticsearch_update_7211() {
  try {
    $es = new ESInstance();

    if (!in_array('website', $es->getIndices())) {
      return;
    }

    $count = $es->setWebsiteSearchParams('*', 'blastdb', 'website')->count();
    $client = $es->client;

    if ($count === 0) {
      print "No blastdb content was found\n";
      return;
    }

    for ($i = 0; $i < $count; $i += 100) {
      $results = $es->setWebsiteSearchParams(
        '*',
        'blastdb',
        'website',
        'website',
        [
          $i,
          100,
        ]
      )->search(FALSE);
      foreach ($results as $result) {
        $client->deleteByQuery(
          [
            'index' => 'website',
            'type' => 'website',
            'body' => [
              'query' => [
                'match' => [
                  'nid' => $result['nid'],
                ],
              ],
            ],
          ]
        );
      }
    }

    print "Successfully removed all blastdb nodes from website index.\n";
  } catch (Exception $exception) {
    print "ERROR: failed to remove blastdb nodes from website index due to the following exception\n";
    print $exception->getMessage();

    throw $exception;
  }
}

/**
 * Add the related_features field to the gene search index.
 */
function tripal_elasticsearch_update_7212() {
  try {
    $es = new ESInstance();
    if (in_array('gene_search_index', $es->getIndices())) {
      $es->putMapping(
        'gene_search_index',
        'related_features',
        'text',
        'chado.feature'
      );
    }
  } catch (Exception $exception) {
    // Ignore failure since it's not important. The field gets auto created on
    // index update. We don't want to remove this function to maintain the
    // increment of the update functions.
  }
}

/**
 * Add logo column to tripal_elasticsearch_servers table
 */
function tripal_elasticsearch_update_7213() {
  $table = 'tripal_elasticsearch_servers';
  $field = 'logo';

  if (!db_field_exists($table, $field)) {
    db_add_field($table, $field, ['type' => 'text']);
  }
}

/**
 * Remove duplicate indices.
 */
function tripal_elasticsearch_delete_duplicate_indices() {
  $visited = [];
  $query = db_select('tripal_elasticsearch_indices', 't');
  $query->fields('t');
  $query->orderBy('id', 'DESC');
  $indices = $query->execute()->fetchAll();
  foreach ($indices as $index) {
    if (isset($visited[$index->index_name])) {
      continue;
    }
    $visited[$index->index_name] = $index;

    $query = db_update('tripal_elasticsearch');
    $query->condition('index_name', $index->name);
    $query->fields([
      'index_id' => $index->id
    ]);
    $query->execute();

    $query = db_delete('tripal_elasticsearch_indices');
    $query->condition('index_name', $index->index_name);
    $query->condition('id', $index->id, '!=');
    $query->execute();
  }
}

/**
 * Delete duplicate indices and add unique name constraint.
 */
function tripal_elasticsearch_update_7214() {
  tripal_elasticsearch_delete_duplicate_indices();

  try {
    db_add_unique_key(
      'tripal_elasticsearch_indices',
      'index_name_unique_constraint',
      [
        'index_name',
      ]
    );
  } catch (Exception $exception) {
    print $exception->getMessage();
  }
}