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_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 or a string representation of the array.

Related topics

File

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

Contains functions for the Materialized Views API

Code

function tripal_add_mview($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) {
    // if the schema is provided as a string then convert it to an array
    if (!is_array($mv_schema)) {
      eval("\$schema_arr = $mv_schema;");
    }
    // if the schema is provided as an array then create a string
    // copy of it for storage in the mview 
    else {
      $schema_arr = $mv_schema;
      $mv_schema = var_export($schema_arr, 1);
    }
    $mv_table = $schema_arr['table'];
  }

  // Create a new record
  $record = new stdClass();
  $record->name = $name;
  $record->modulename = $modulename;
  $record->mv_table = $mv_table;
  $record->mv_specs = $mv_specs;
  $record->indexed = $indexed;
  $record->query = $query;
  $record->special_index = $special_index;
  $record->comment = $comment;
  $record->mv_schema = $mv_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
    $previous_db = tripal_db_set_active('chado'); // use chado database
    if (db_table_exists($mv_table)) {
      $sql = "DROP TABLE $mv_table";
      db_query($sql);
    }
    tripal_db_set_active($previous_db); // now use drupal database

    // now construct the indexes
    $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);";
      }
    }

    // create the table differently depending on if it the traditional method
    // or the Drupal Schema API method
    if ($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."), 'error');
      }
      else {
        drupal_set_message(t("View '%name' created", array('%name' => $name)));
      }
    }
    else {
      // add the table to the database
      $sql = "CREATE TABLE {$mv_table} ($mv_specs); $index";
      $previous_db = tripal_db_set_active('chado'); // use chado database
      $results = db_query($sql);
      tripal_db_set_active($previous_db); // now use drupal database
      if ($results) {
        drupal_set_message(t("View '%name' created", array('%name' => $name)));
      }
      else {
        drupal_set_message(t("Failed to create the materialized view table: '%mv_table'", array('%mv_table' => $mv_table)), 'error');
      }
    }
  }
}