Related topics

File

tripal_feature/tripal_feature.install, line 57

@todo Add file header description

Code

function tripal_feature_add_organism_count_mview() {
  $view_name = 'organism_feature_count';

  // Drop the MView table if it exists
  $mview_id = tripal_mviews_get_mview_id($view_name);
  if ($mview_id) {
    tripal_mviews_action("delete", $mview_id);
  }

  // Create the MView
  tripal_add_mview(
  // view name
  $view_name, 
  // tripal module name
  'tripal_feature', 
  // table name
  $view_name, 
  // table schema definition
  'organism_id integer, genus character varying(255), ' .
    '  species character varying(255), ' .
    '  common_name character varying(255), ' .
    '  num_features integer, cvterm_id integer, ' .
    '  feature_type character varying(255)', 
  // columns for indexing
  'organism_id,cvterm_id,feature_type', 
  // SQL statement to populate the view
  'SELECT O.organism_id, O.genus, O.species, O.common_name,
        count(F.feature_id) as num_features,
        CVT.cvterm_id, CVT.name as feature_type
     FROM Organism O
        INNER JOIN Feature F           ON O.Organism_id = F.organism_id
        INNER JOIN Cvterm CVT          ON F.type_id = CVT.cvterm_id
     GROUP BY O.Organism_id, O.genus, O.species, O.common_name,
        CVT.cvterm_id, CVT.name', 
  // special index
  ''
  );

  // add a job to the job queue so this view gets updated automatically next
  // time the job facility is run
  $mview_id = tripal_mviews_get_mview_id($view_name);
  if ($mview_id) {
    tripal_mviews_action('update', $mview_id);
  }
}