File

legacy/tripal_phylogeny/tripal_phylogeny.install, line 155

Installation of the phylotree module

Code

function tripal_phylogeny_add_mview() {
  // Materialized view addition.
  $sql_query = "
    WITH count_genes as
      (SELECT count(*) count, t.phylotree_id
       FROM phylotree t, phylonode n
       WHERE n.phylotree_id = t.phylotree_id AND n.label is NOT NULL
       GROUP BY t.phylotree_id)
    SELECT
      phylotree.phylotree_id AS phylotree_phylotree_id,
      phylotree.name         AS phylotree_name,
      phylotree.comment      AS phylotree_comment,
      count_genes.count      AS total_count
    FROM chado.phylotree phylotree
      LEFT JOIN chado_phylotree chado_phylotree ON phylotree.phylotree_id = chado_phylotree.phylotree_id
      LEFT JOIN count_genes count_genes         ON phylotree.phylotree_id = count_genes.phylotree_id
  ";

  // Table Phylotree User Search description.
  $schema = array(
    'table' => 'phylotree_count',
    'fields' => array(
      'phylotree_phylotree_id' => array(
        'type' => 'int',
        'not null' => FALSE,
      ),
      'phylotree_name' => array(
        'type' => 'text',
        'not null' => FALSE,
      ),
      'phylotree_comment' => array(
        'type' => 'text',
        'not null' => FALSE,
      ),
      'total_count' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('phylotree_phylotree_id'),
  );

  // Add a comment to make sure this view makes sense to the site administator.
  $comment = t('This view is used to provide a table for Phylotree User Search with total count.');
  tripal_add_mview('phylotree_count', 'tripal_phylogeny', $schema, $sql_query, $comment);
}