Update a Materialized View

Parameters

$mview_id: The unique identifier for the materialized view to be updated

Return value

True if successful, FALSE otherwise

Related topics

File

tripal_core/api/tripal_core.mviews.api.inc, line 429

Provides an application programming interface (API) to manage materialized views in Chado.

Code

function tripal_populate_mview($mview_id) {
  $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = :mview_id ";
  $results = db_query($sql, array(':mview_id' => $mview_id));
  $mview = $results->fetchObject();
  if ($mview) {
    // execute the query inside a transaction so that it doesn't destroy existing data
    // that may leave parts of the site unfunctional
    $transaction = db_transaction();
    try {
      $previous_db = chado_set_active('chado'); // use chado database
      $success = db_query("DELETE FROM {" . $mview->mv_table . "}");
      $success = db_query("INSERT INTO {" . $mview->mv_table . "} ($mview->query)");
      chado_set_active($previous_db); // now use drupal database
      // if success get the number of results and update the table record
      if ($success) {
        $sql = "SELECT count(*) as cnt FROM {" . $mview->mv_table . "}";
        $results = chado_query($sql);
        $count = $results->fetchObject();
        $record = new stdClass();
        $record->mview_id = $mview_id;
        $record->last_update = REQUEST_TIME;
        $record->status = "Populated with " . number_format($count->cnt) . " rows";
        drupal_write_record('tripal_mviews', $record, 'mview_id');
      }
      // if not success then throw an error
      else {
        throw new Exception("ERROR populating the materialized view " . $mview->mv_table . ". See Drupal's recent log entries for details.");
      }
    }
    catch (Exception $e) {
      $transaction->rollback();
      // print and save the error message
      $record = new stdClass();
      $record->mview_id = $mview_id;
      $record->status = "ERROR populating $mview->mv_table. See Drupal's recent log entries for details.\n";
      drupal_write_record('tripal_mviews', $record, 'mview_id');
      watchdog_exception('tripal_mviews', $e);
      return FALSE;
    }
    print "Done.\n";
    return TRUE;
  }
}