Edits a materialized view to the chado database to help speed data access.This function supports the older style where postgres column specifications are provided using the $mv_table, $mv_specs and $indexed variables. It also supports the newer preferred method where the materialized view is described using the Drupal Schema API array.

Parameters

$mview_id: The mview_id of the materialized view to edit

$name: The name of the materialized view.

$modulename: The name of the module submitting the materialized view (e.g. 'tripal_library')

$mv_table: The name of the table to add to chado. This is the table that can be queried.

$mv_specs: The table definition

$indexed: The columns that are to be indexed

$query: The SQL query that loads the materialized view with data

$special_index: currently not used

$comment: A string containing a description of the materialized view

$mv_schema: If using the newer Schema API array to define the materialized view then this variable should contain the array.

Related topics

File

tripal_core/api/tripal_core_mviews.api.inc, line 157

Contains functions for the Materialized Views API

Code

function tripal_edit_mview($mview_id, $name, $modulename, $mv_table, $mv_specs, 
$indexed, $query, $special_index, $comment = NULL, $mv_schema = NULL) {

  // get the table name from the schema array
  $schema_arr = array();
  if ($mv_schema) {
    // get the schema from the mv_specs and use it to add the custom table
    eval("\$schema_arr = $mv_schema;");
    $mv_table = $schema_arr['table'];
  }

  $record = new stdClass();
  $record->mview_id = $mview_id;
  $record->name = $name;
  $record->modulename = $modulename;
  $record->query = $query;
  $record->last_update = 0;
  $record->status = '';
  $record->comment = $comment;

  // get the view before we update and check to see if the table structure has
  // changed. IF so, then we want to drop and recreate the table. If not, then
  // just save the updated SQL.
  $create_table = 1;
  $sql = "SELECT * FROM {tripal_mviews} WHERE mview_id = %d";
  $mview = db_fetch_object(db_query($sql, $mview_id));
  if ($mview->mv_schema == $mv_schema and $mview->mv_table == $mv_table and 
    $mview->mv_specs == $mv_specs and $mview->indexed == $indexed and 
    $mview->special_index == $special_index) {
    // nothing has changed so simpy update the SQL and other fields 
    $create_table = 0;
  }
  else {
    // add in the table structure fields
    $record->mv_schema = $mv_schema;
    $record->mv_table = $mv_table;
    $record->mv_specs = $mv_specs;
    $record->indexed = $indexed;
    $record->query = $query;
    $record->special_index = $special_index;
  }

  // if we are going to create the table then we must first drop it if it exists
  if ($create_table) {
    $previous_db = tripal_db_set_active('chado'); // use chado database
    if (db_table_exists($mview->mv_table)) {
      $sql = "DROP TABLE %s";
      db_query($sql, $mview->mv_table);
      drupal_set_message(t("View '%name' dropped", array('%name' => $name)));
    }
    tripal_db_set_active($previous_db); // now use drupal database
  }

  // update the record to the tripal_mviews table and if successful
  // create the new materialized view in the chado schema
  if (drupal_write_record('tripal_mviews', $record, 'mview_id')) {
    // construct the indexes SQL if needed
    $index = '';
    if ($indexed) {
      // add to the array of values
      $vals = preg_split("/[\n,]+/", $indexed);
      $index = '';
      foreach ($vals as $field) {
        $field = trim($field);
        $index .= "CREATE INDEX idx_${mv_table}_${field} ON $mv_table ($field);";
      }
    }

    // re-create the table differently depending on if it the traditional method
    // or the Drupal Schema API method
    if ($create_table and $mv_schema) {
      if (!tripal_core_create_custom_table($ret, $mv_table, $schema_arr, 0)) {
        drupal_set_message(t("Could not create the materialized view. Check Drupal error report logs."));
      }
      else {
        drupal_set_message(t("View '%name' created", array('%name' => $name)));
      }
    }
    if ($create_table and !$mv_schema) {
      $sql = "CREATE TABLE {$mv_table} ($mv_specs); $index";
      $results = chado_query($sql);
      if ($results) {
        drupal_set_message(t("View '%name' created.  All records cleared. Please re-populate the view.", 
        array('%name' => $name)));
      }
      else {
        drupal_set_message(t("Failed to create the materialized view table: '%mv_table'", 
        array('%mv_table' => $mv_table)), 'error');
      }
    }
    if (!$create_table) {
      $message = "View '%name' updated.  All records remain. ";
      if ($query != $mview->query) {
        $message .= "Please repopulate the view to use updated query.";
      }
      drupal_set_message(t($message, array('%name' => $name)));
    }
  }
  else {
    drupal_set_message(t("Failed to update the materialized view: '%mv_table'", 
    array('%mv_table' => $mv_table)), 'error');
  }
}