function tripal_edit_mview

2.x tripal_edit_mview($mview_id, $name, $modulename, $mv_table, $mv_specs, $indexed, $query, $special_index, $comment = NULL, $mv_schema = NULL)
1.x tripal_edit_mview($mview_id, $name, $modulename, $mv_table, $mv_specs, $indexed, $query, $special_index, $comment = NULL, $mv_schema = NULL)

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.


$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

1 call to tripal_edit_mview()
tripal_mviews_form_submit in tripal_core/includes/
Submit the Create/Edit Materialized View Form Implements hook_form_submit().


tripal_core/api/, line 211
Provides an application programming interface (API) to manage materialized views in Chado.


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

  $transaction = db_transaction();
  try {
    // 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;
    $record->mv_schema = $mv_schema;
    $record->mv_table = $mv_table;

    // update the record to the tripal_mviews table
    drupal_write_record('tripal_mviews', $record, 'mview_id');

    // 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 = :mview_id";
    $results = db_query($sql, array(':mview_id' => $mview_id));
    $mview = $results->fetchObject();
    if ($mview->mv_schema == $mv_schema and $mview->mv_table == $mv_table) {
      chado_create_custom_table($mv_table, $schema_arr, 0, $record->mview_id);
      drupal_set_message(t("Materialized view '%name' created", array('%name' => $name)));
    else {
      $message = "View '%name' updated.  All records remain. ";
      if ($query != $mview->query) {
        $message .= "Please repopulate the view to use the updated query.";
      drupal_set_message(t($message, array('%name' => $name)));

    // 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);";

  catch (Exception $e) {
    watchdog_exception('tripal_core', $e);
    $error = _drupal_decode_exception($e);
    drupal_set_message(t("Could not update materialized view '%table_name': %message.", 
    array('%table_name' => $mv_table, '%message' => $error['!message'])), 'error');
    return FALSE;