function tripal_add_mview

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

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.


$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

Related topics

8 calls to tripal_add_mview()
tripal_analysis_add_mview_analysis_organism in tripal_analysis/tripal_analysis.install
Creates a view showing the link between an organism & it's analysis through associated features.
tripal_core_extensions_form_submit in tripal_core/includes/
Process the import buttons.
tripal_cv_add_cv_root_mview in tripal_cv/tripal_cv.install
Add a materialized view of root terms for all chado cvs. This is needed for viewing cv trees
tripal_feature_add_organism_count_mview in tripal_feature/tripal_feature.install
Creates a materialized view that stores the type & number of features per organism
tripal_library_add_mview_library_feature_count in tripal_library/tripal_library.install
Adds a materialized view keeping track of the type of features associated with each library

... See full list


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


function tripal_add_mview($name, $modulename, $mv_schema, $query, $comment = NULL) {

  if (!array_key_exists('table', $mv_schema)) {
    tripal_report_error('tripal_core', 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 . '}';
        // create the table
        chado_create_custom_table($mv_table, $mv_schema, 0, $record->mview_id);
    catch (Exception $e) {
      watchdog_exception('tripal_core', $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 {
    tripal_report_error('tripal_cv', TRIPAL_WARNING, 
    "Materialized view, %vname, already exists. Cannot create.", 
    array('%vname' => $name));
    drupal_set_message(t("Materialized view, $name, already exists. Cannot create.", array('%name' => $name)));
    return FALSE;