Add 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

$name: The name of the materialized view.

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

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

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

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

$redirect: Optional (default: TRUE). By default this function redirects back to admin pages. However, when called by Drush we don't want to redirect. This parameter allows this to be used as a true API function.

Related topics

File

tripal_chado/api/tripal_chado.mviews.api.inc, line 45

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

Code

function chado_add_mview($name, $modulename, $mv_schema, $query, $comment = NULL, $redirect = TRUE) {

  if (!array_key_exists('table', $mv_schema)) {
    tripal_report_error('tripal_chado', TRIPAL_ERROR, 
    'Must have a table name when creating an mview.', array());
    return NULL;
  }

  $mv_table = $mv_schema['table'];

  // See if the mv_table name already exsists.
  $mview_id = db_query(
  'SELECT mview_id FROM {tripal_mviews} WHERE name = :name', 
  array(':name' => $name))->fetchField();

  if (!$mview_id) {
    $transaction = db_transaction();
    try {
      // Create a new record.
      $record = new stdClass();
      $record->name = $name;
      $record->modulename = $modulename;
      $record->mv_table = $mv_table;
      $record->query = $query;
      $record->comment = $comment;

      // Convert the schema into a string format.
      $str_schema = var_export($mv_schema, TRUE);
      $str_schema = preg_replace('/=>\s+\n\s+array/', '=> array', $str_schema);
      $record->mv_schema = $str_schema;

      // Add 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)) {

        // Drop the table from chado if it exists.
        if (chado_table_exists($mv_table)) {
          $sql = 'DROP TABLE {' . $mv_table . '}';
          chado_query($sql);
        }
        // Create the table.
        chado_create_custom_table($mv_table, $mv_schema, 0, $record->mview_id, $redirect);
      }
    }
    catch (Exception $e) {
      $transaction->rollback();
      watchdog_exception('tripal_chado', $e);
      $error = _drupal_decode_exception($e);
      drupal_set_message(t("Could not create the materialized view %table_name: %message.", 
      array('%table_name' => $name, '%message' => $error['!message'])), 'error');
      return FALSE;
    }
    drupal_set_message(t("Materialized view '%name' created", array('%name' => $name)));
    return TRUE;
  }
  else {
    drupal_set_message(t("Materialized view, $name, already exists. Skipping creation.", array('%name' => $name)));
    return FALSE;
  }
}