tripal_phylotree.install

<?php
/**
 * @file
 * Installation of the phylotree module
 */

/**
 * Implementation of hook_requirements().
 *
 * @ingroup tripal_phylotree
 */
function tripal_phylotree_requirements($phase) {
  $requirements = array();
  if ($phase == 'install') {
    // make sure chado is installed
    if (!$GLOBALS["chado_is_installed"]) {
      $requirements ['tripal_phylotree'] = array(
        'title' => "tripal_phylotree",
        'value' => "ERROR: Chado must be installed before this module can be enabled",
        'severity' => REQUIREMENT_ERROR,
      );
    }
  }
  return $requirements;
}


/* hook_enable()*/

function tripal_phylotree_enable(){
$permissions = array('access chado_phylotree content');
tripal_phylotree_grant_permissions(DRUPAL_AUTHENTICATED_RID, $permissions);
$permissions_anonym = array('access chado_phylotree content');
tripal_phylotree_grant_permissions(DRUPAL_ANONYMOUS_RID, $permissions_anonym);
$permissions_admin = array('administer tripal phylotree');
tripal_phylotree_grant_permissions (DRUPAL_AUTHENTICATED_RID, $permissions_admin);


}


/**
 * Implements hook_install()
 * Allows installation of the tripal_gene module
 */

function tripal_phylotree_install() {
  $sql_count ="select count(f.feature_id) as count
        from {feature} f left join {cvterm} cvt
        on f.type_id=cvt.cvterm_id
      where cvt.name = :name";

  $args = array(':name' => 'polypeptide_domain');
  $result = chado_query($sql_count, $args)->fetchObject();
  if ($result->count > 0) {
      
    // add the materializedviews
    tripal_phylotree_add_mview();  

    // we want to integrate the materialized views so that they
    // are available for Drupal Views, upon which our search forms are built
    tripal_phylotree_integrate_view();

    $mview_id =  tripal_get_mview_id('tree2domain');
    print "\nPopulating tree2domain mview.....\n";
    tripal_populate_mview($mview_id);
  } else {
      // add the materialized view with no domain data
      tripal_phylotree_add_domainless_mview();

      // we want to integrate the materialized views so that they
      // are available for Drupal Views, upon which our search forms are built
      tripal_phylotree_integrate_view();
  }      

    
    $mview_id =  tripal_get_mview_id('phylotree_count');   
    print "\nPopulating phylotree_count mview.....\n";  
    tripal_populate_mview($mview_id);

}


/**
 * Implementation of hook_grant_permissions(). peu added
 */

function tripal_phylotree_grant_permissions($rid, array $permissions = array()) {

   // this line doesn't work on install / enable hooks
     $modules = user_permission_get_modules();
    // Grant new permissions for the role.
    foreach ($permissions as $name) {
        db_merge('role_permission')
        ->key(array(
            'rid' => $rid,
            'permission' => $name,
        ))
        ->fields(array(
      //hard-coded module name here as $modules was not working -peu
           'module' => 'tripal_phylotree',
       ))
        ->execute();
    }


    // Clear the user access cache.
    drupal_static_reset('user_access');
    drupal_static_reset('user_role_permissions');
}


/**
 * Implementation of hook_schema().
 * Standard tripal linker table between a node and a phylotree record.
 * @ingroup tripal_phylotree
 */
function tripal_phylotree_schema() {
  $schema['chado_phylotree'] = array(
    'fields' => array(
      'vid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0
      ),
      'nid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0
      ),
      'phylotree_id' => array(
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0
      )
    ),
    'indexes' => array(
      'chado_phylotree_idx1' => array('phylotree_id')
    ),
    'unique keys' => array(
      'chado_phylotree_uq1' => array('nid', 'vid'),
      'chado_phylotree_uq2' => array('vid')
    ),
    'primary key' => array('nid'),
  );
  return $schema;
}

/**
 * Implementation of hook_uninstall().
 */
function tripal_phylotree_uninstall() {

    // Drop the MView table if it exists
    $mview_id =  tripal_get_mview_id('phylotree_count');
    if ($mview_id) {
        tripal_delete_mview($mview_id);
    }
    //Remove views integration
    // Note: tripal_remove_views_intergration accepts table_name and priority in a key value form. 

    $delete_view=array(
        'table_name' => 'phylotree_count',
        'priority' => '-1',
    );
    tripal_remove_views_integration($delete_view); 

    // Drop the MView table if it exists
    $mview_id =  tripal_get_mview_id('tree2domain');
    if ($mview_id) {
        tripal_delete_mview($mview_id);

        //Remove views integration
        // Note: tripal_remove_views_intergration accepts table_name and priority in a key value form.

        $delete_view=array(
            'table_name' => 'tree2domain',
            'priority' => '-1',
        );
        tripal_remove_views_integration($delete_view);
    }
}


function tripal_phylotree_add_domainless_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),
count_medtr_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'medtr.%' group by t.phylotree_id),
count_pissa_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'pissa.%' group by t.phylotree_id),
count_tripr_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'tripr.%' group by t.phylotree_id),
count_lotja_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'lotja.%' group by t.phylotree_id),
count_cicar_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'cicar.%' group by t.phylotree_id),
count_cajca_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'cajca.%' group by t.phylotree_id),
count_vigun_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'vigun.%' group by t.phylotree_id),
count_vigra_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'vigra.%' group by t.phylotree_id),
count_vigan_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'vigan.%' group by t.phylotree_id),
count_phavu_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'phavu.%' group by t.phylotree_id),
count_phalu_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'phalu.%' group by t.phylotree_id),
count_aradu_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'aradu.%' group by t.phylotree_id),
count_araip_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'araip.%' group by t.phylotree_id),
count_arahy_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'arahy.%' group by t.phylotree_id),
-- count_arahy_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'arahy.%' group by t.phylotree_id),
count_lupan_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'lupan.%' group by t.phylotree_id),
count_lupal_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'lupal.%' group by t.phylotree_id),
count_glyma_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'glyma.%' 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,
count_medtr_genes.count as medtr_count,
coalesce(count_pissa_genes.count,0) as pissa_count,
coalesce(count_tripr_genes.count,0) as tripr_count,
count_lotja_genes.count as lotja_count,
count_cicar_genes.count as cicar_count,
count_glyma_genes.count as glyma_count,
count_phavu_genes.count as phavu_count,
coalesce(count_phalu_genes.count,0) as phalu_count,
count_cajca_genes.count as cajca_count,
coalesce(count_vigun_genes.count,0) as vigun_count,
count_vigra_genes.count as vigra_count,
coalesce(count_vigan_genes.count,0) as vigan_count,
count_aradu_genes.count as aradu_count,
count_araip_genes.count as araip_count,
coalesce(count_arahy_genes.count,0) as arahy_count,
coalesce(count_lupan_genes.count,0) as lupan_count,
coalesce(count_lupal_genes.count,0) as lupal_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
LEFT JOIN count_medtr_genes ON phylotree.phylotree_id = count_medtr_genes.phylotree_id
LEFT JOIN count_pissa_genes ON phylotree.phylotree_id = count_pissa_genes.phylotree_id
LEFT JOIN count_tripr_genes ON phylotree.phylotree_id = count_tripr_genes.phylotree_id
LEFT JOIN count_lotja_genes ON phylotree.phylotree_id = count_lotja_genes.phylotree_id
LEFT JOIN count_cicar_genes ON phylotree.phylotree_id = count_cicar_genes.phylotree_id
LEFT JOIN count_glyma_genes ON phylotree.phylotree_id = count_glyma_genes.phylotree_id
LEFT JOIN count_phavu_genes ON phylotree.phylotree_id = count_phavu_genes.phylotree_id
LEFT JOIN count_phalu_genes ON phylotree.phylotree_id = count_phalu_genes.phylotree_id
LEFT JOIN count_cajca_genes ON phylotree.phylotree_id = count_cajca_genes.phylotree_id
LEFT JOIN count_vigun_genes ON phylotree.phylotree_id = count_vigun_genes.phylotree_id
LEFT JOIN count_vigra_genes ON phylotree.phylotree_id = count_vigra_genes.phylotree_id
LEFT JOIN count_vigan_genes ON phylotree.phylotree_id = count_vigan_genes.phylotree_id
LEFT JOIN count_aradu_genes ON phylotree.phylotree_id = count_aradu_genes.phylotree_id
LEFT JOIN count_araip_genes ON phylotree.phylotree_id = count_araip_genes.phylotree_id
LEFT JOIN count_arahy_genes ON phylotree.phylotree_id = count_arahy_genes.phylotree_id
LEFT JOIN count_lupan_genes ON phylotree.phylotree_id = count_lupan_genes.phylotree_id
LEFT JOIN count_lupal_genes ON phylotree.phylotree_id = count_lupal_genes.phylotree_id
JOIN analysis a ON phylotree.analysis_id=a.analysis_id JOIN analysisprop ap ON ap.analysis_id=a.analysis_id JOIN cvterm ct ON ap.type_id=ct.cvterm_id
WHERE ct.name='is_current'
";

        //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,
                ),
                'medtr_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'pissa_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'tripr_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'lotja_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'cicar_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'glyma_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'phavu_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'phalu_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'cajca_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'vigun_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'vigra_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'vigan_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'aradu_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'araip_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'arahy_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'lupan_count' => array(
                    'type' => 'int',
                    'not null' => TRUE,
                ),
                'lupal_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- and per species- counts.');
        tripal_add_mview(
            'phylotree_count', // name of materialized view
            'tripal_phylotree', // name of module submitting view
            $schema,      // schema api array representation
            $sql_query,         // sql query that loads the mview
            $comment );

}


function tripal_phylotree_add_mview(){
    //Materialized view addition

    $sql_t2d = "select string_agg(DISTINCT(f1.name),E' ') AS domains, t.phylotree_id, string_agg(DISTINCT(d.iprterm),E' ') AS ipr
 FROM feature f1
 JOIN cvterm cvt1
 ON cvt1.cvterm_id = f1.type_id
 JOIN featureloc
 ON featureloc.feature_id = f1.feature_id
 JOIN feature f2
 ON featureloc.srcfeature_id = f2.feature_id
 JOIN cvterm cvt2
 ON cvt2.cvterm_id = f2.type_id
 JOIN phylonode n
 ON n.feature_id=f2.feature_id
 JOIN phylotree t
 ON n.phylotree_id=t.phylotree_id
 JOIN domain d
 ON d.feature_name=f1.name
 WHERE cvt1.name in ('protein_hmm_match', 'protein_match') AND 
 cvt2.name = 'polypeptide'
 GROUP BY t.phylotree_id";

    $schema_t2d = array (
        'table' => 'tree2domain',
        'fields' => array (
            'domains' => array (
                'type' => 'text',
                'not null' => false,
            ),
            'phylotree_id' => array (
                'type' => 'int',
                'not null' => 'FASLE',
            ),
            'ipr' => array (
            'type' => 'text',
            'not null' => false,
            ),

         ),
         'indexes' => array (
            't2d_indx0' => array (
                0 => 'phylotree_id',
            ),
        ),
    );
    
    $comment_t2d = t('This view is used to provide a helper table for mapping phylotrees to domains.
                          The view is queried by the phylotree_count mview\'s sql so it needs to be created and populated before the population of phylotree_count mview.');

    tripal_add_mview(
        'tree2domain', // name of materialized view
        'tripal_phylotree', // name of module submitting view
        $schema_t2d,      // schema api array representation
        $sql_t2d,         // sql query that loads the mview
        $comment_t2d );
   


   $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),
count_medtr_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'medtr.%' group by t.phylotree_id),
count_lupal_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'lupal.%' group by t.phylotree_id),
count_pissa_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'pissa.%' group by t.phylotree_id),
count_tripr_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'tripr.%' group by t.phylotree_id),
count_lotja_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'lotja.%' group by t.phylotree_id),
count_cicar_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'cicar.%' group by t.phylotree_id),
count_cajca_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'cajca.%' group by t.phylotree_id),
count_vigun_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'vigun.%' group by t.phylotree_id),
count_vigra_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'vigra.%' group by t.phylotree_id),
count_vigan_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'vigan.%' group by t.phylotree_id),
count_phavu_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'phavu.%' group by t.phylotree_id),
count_phalu_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'phalu.%' group by t.phylotree_id),
count_aradu_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'aradu.%' group by t.phylotree_id),
count_araip_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'araip.%' group by t.phylotree_id),
count_arahy_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'arahy.%' group by t.phylotree_id),
-- count_arahy_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'arahy.%' group by t.phylotree_id),
count_lupan_genes as (select count(fp.value) count, t.phylotree_id from phylotree t left outer join featureprop fp on fp.value=t.name inner join feature f on fp.feature_id=f.feature_id and f.name like 'lupan.%' group by t.phylotree_id),
count_glyma_genes as (select count(n.phylonode_id) count, t.phylotree_id from phylotree t left outer join phylonode n on n.phylotree_id=t.phylotree_id and n.label like 'glyma.%' group by t.phylotree_id),
consensus2domain as (select string_agg(DISTINCT(f.name),E' ') domains_con ,string_agg(DISTINCT(d.iprterm),E' ') AS ipr2, phylotree_id from feature f JOIN cvterm cvt1 ON cvt1.cvterm_id=f.type_id JOIN featureloc floc ON f.feature_id=floc.feature_id JOIN feature f2 ON floc.srcfeature_id=f2.feature_id JOIN cvterm cvt2 ON cvt2.cvterm_id=f2.type_id JOIN phylotree ON f2.name = phylotree.name || '-consensus' JOIN domain d ON d.feature_name=f.name WHERE cvt2.name='consensus_region' and (cvt1.name='protein_match' OR cvt1.name='protein_hmm_match') group by phylotree.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, 
count_medtr_genes.count as medtr_count, 
coalesce(count_pissa_genes.count,0) as pissa_count,
coalesce(count_tripr_genes.count,0) as tripr_count,
count_lotja_genes.count as lotja_count, 
count_cicar_genes.count as cicar_count, 
count_glyma_genes.count as glyma_count, 
count_phavu_genes.count as phavu_count, 
coalesce(count_phalu_genes.count,0) as phalu_count,
count_cajca_genes.count as cajca_count, 
coalesce(count_vigun_genes.count,0) as vigun_count,
count_vigra_genes.count as vigra_count, 
coalesce(count_vigan_genes.count,0) as vigan_count,
count_aradu_genes.count as aradu_count, 
count_araip_genes.count as araip_count,
coalesce(count_arahy_genes.count,0) as arahy_count,
coalesce(count_lupan_genes.count,0) as lupan_count,
coalesce(count_lupal_genes.count,0) as lupal_count,
c2d.domains_con || ' ' || coalesce(c2d.ipr2, '') as domains_con
FROM chado.phylotree phylotree
LEFT JOIN consensus2domain c2d ON phylotree.phylotree_id = c2d.phylotree_id
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
LEFT JOIN count_medtr_genes ON phylotree.phylotree_id = count_medtr_genes.phylotree_id
LEFT JOIN count_pissa_genes ON phylotree.phylotree_id = count_pissa_genes.phylotree_id
LEFT JOIN count_tripr_genes ON phylotree.phylotree_id = count_tripr_genes.phylotree_id
LEFT JOIN count_lotja_genes ON phylotree.phylotree_id = count_lotja_genes.phylotree_id
LEFT JOIN count_cicar_genes ON phylotree.phylotree_id = count_cicar_genes.phylotree_id
LEFT JOIN count_glyma_genes ON phylotree.phylotree_id = count_glyma_genes.phylotree_id
LEFT JOIN count_phavu_genes ON phylotree.phylotree_id = count_phavu_genes.phylotree_id
LEFT JOIN count_phalu_genes ON phylotree.phylotree_id = count_phalu_genes.phylotree_id
LEFT JOIN count_cajca_genes ON phylotree.phylotree_id = count_cajca_genes.phylotree_id
LEFT JOIN count_vigun_genes ON phylotree.phylotree_id = count_vigun_genes.phylotree_id
LEFT JOIN count_vigra_genes ON phylotree.phylotree_id = count_vigra_genes.phylotree_id
LEFT JOIN count_vigan_genes ON phylotree.phylotree_id = count_vigan_genes.phylotree_id
LEFT JOIN count_aradu_genes ON phylotree.phylotree_id = count_aradu_genes.phylotree_id
LEFT JOIN count_araip_genes ON phylotree.phylotree_id = count_araip_genes.phylotree_id
LEFT JOIN count_arahy_genes ON phylotree.phylotree_id = count_arahy_genes.phylotree_id
LEFT JOIN count_lupan_genes ON phylotree.phylotree_id = count_lupan_genes.phylotree_id
LEFT JOIN count_lupal_genes ON phylotree.phylotree_id = count_lupal_genes.phylotree_id
JOIN analysis a ON phylotree.analysis_id=a.analysis_id JOIN analysisprop ap ON ap.analysis_id=a.analysis_id JOIN cvterm ct ON ap.type_id=ct.cvterm_id
WHERE ct.name='is_current'";

    //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, 
      ),
      'medtr_count' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'pissa_count' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'tripr_count' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'lotja_count' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'cicar_count' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'glyma_count' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'phavu_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'phalu_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'cajca_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'vigun_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'vigra_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'vigan_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'aradu_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'araip_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'arahy_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'lupan_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),
      'lupal_count' => array(
          'type' => 'int',
          'not null' => TRUE,
      ),

     'domains_con' => array (
          'type' => 'text',
          'not null' => false,
      ),





   ),
   'primary key' => array('phylotree_phylotree_id'),
    'indexes' => array (
        'phylotree_indx0' => array (
            0 => 'phylotree_name',
        ),
        'phylotree_indx2' => array (
            0 => 'domains_con',
        ),
    ),
  );

   // 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- and per species- counts.');
  tripal_add_mview(
    'phylotree_count', // name of materialized view
    'tripal_phylotree', // name of module submitting view
    $schema,      // schema api array representation
    $sql_query,         // sql query that loads the mview
    $comment );
  
}

/**
 * Integrate the qtl_search materialized view for use by Drupal Views and
 * our search form
 */

function tripal_phylotree_integrate_view(){

    $integration_t2d = array (
        'table' => 'tree2domain',
        'name' => 'tree2domain',
        'type' => 'chado',
        'description' => '',
        'priority' => '-1',
        'base_table' => '1',
        'fields' => array (
            'phylotree_id' => array (
                'name' => 'phylotree_id',
                'title' => 'phylotree_id',
                'description' => 'Phylotree ID',
                'type' => 'int',
                'handlers' => array (
                    'filter' => array (
                        'name' => 'views_handler_filter_numeric',
                    ),
                    'field' => array (
                        'name' => 'views_handler_field_numeric',
                    ),
                    'sort' => array (
                        'name' => 'views_handler_sort',
                    ),
                    'argument' => array (
                        'name' => 'views_handler_argument_numeric',
                    ),
                    'relationship' => array (
                        'name' => 'views_handler_relationship',
                    ),
                ),
                'joins' => array (
                ),
            ),
            'domains' => array (
                'name' => 'domains',
                'title' => 'Domains',
                'description' => 'Domains',
                'type' => 'text',
                'handlers' => array (
                    'filter' => array (
                        'name' => 'tripal_views_handler_filter_select_string',
                    ),
                    'field' => array (
                        'name' => 'views_handler_field',
                    ),
                    'sort' => array (
                        'name' => 'views_handler_sort',
                    ),
                    'argument' => array (
                        'name' => 'views_handler_argument_string',
                    ),
                    'relationship' => array (
                        'name' => 'views_handler_relationship',
                    ),
                ),
                'joins' => array (
                ),
            ),
        ),
    );
    // add the array above that will integrate our qtl_search materialized view
    // for use with Drupal Views
    tripal_add_views_integration($integration_t2d);
   

 
    $integration = array (
    'table' => 'phylotree_count',
    'name' => 'phylotree_count',
    'type' => 'chado',
    'description' => '',
    'priority' => '-1',
    'base_table' => '1',
    'fields' => array (
      'phylotree_phylotree_id' => array (
          'name' => 'phylotree_phylotree_id',
          'title' => 'Phylotree ID',
          'description' => 'Phylotree ID',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field_numeric',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'phylotree_name' => array (
          'name' => 'phylotree_name',
          'title' => 'Family ID',
          'description' => 'Family ID',
          'type' => 'text',
          'handlers' => array (
              'filter' => array (
                  'name' => 'tripal_views_handler_filter_select_string',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_string',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'phylotree_comment' => array (
          'name' => 'phylotree_comment',
          'title' => 'Description',
          'description' => 'Description',
          'type' => 'text',
          'handlers' => array (
              'filter' => array (
                  'name' => 'tripal_views_handler_filter_select_string',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_string',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),
 
      'total_count' => array (
          'name' => 'total_count',
          'title' => 'Gene count',
          'description' => 'Gene count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'medtr_count' => array (
          'name' => 'medtr_count',
          'title' => 'Medtr count',
          'description' => 'Medtr count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'pissa_count' => array (
          'name' => 'pissa_count',
          'title' => 'Pissa count',
          'description' => 'Pissa count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'tripr_count' => array (
          'name' => 'tripr_count',
          'title' => 'Tripr count',
          'description' => 'Tripr count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'lotja_count' => array (
          'name' => 'lotja_count',
          'title' => 'Lotja count',
          'description' => 'Lotja count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'cicar_count' => array (
          'name' => 'cicar_count',
          'title' => 'Cicar (CDCFrontier) count',
          'description' => 'Cicar (CDCFrontier) count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'glyma_count' => array (
          'name' => 'glyma_count',
          'title' => 'Glyma count',
          'description' => 'Glyma count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'phavu_count' => array (
          'name' => 'phavu_count',
          'title' => 'Phavu count',
          'description' => 'Phavu count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'phalu_count' => array (
          'name' => 'phalu_count',
          'title' => 'Phalu count',
          'description' => 'Phalu count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'cajca_count' => array (
          'name' => 'cajca_count',
          'title' => 'Cajca count',
          'description' => 'Cajca count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'vigun_count' => array (
          'name' => 'vigun_count',
          'title' => 'Vigun count',
          'description' => 'Vigun count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'vigra_count' => array (
          'name' => 'vigra_count',
          'title' => 'Vigra count',
          'description' => 'Vigra count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'vigan_count' => array (
          'name' => 'vigan_count',
          'title' => 'Vigan count',
          'description' => 'Vigan count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'aradu_count' => array (
          'name' => 'aradu_count',
          'title' => 'Aradu count',
          'description' => 'Aradu count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'araip_count' => array (
          'name' => 'araip_count',
          'title' => 'Araip count',
          'description' => 'Araip count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),
      
      'arahy_count' => array (
          'name' => 'arahy_count',
          'title' => 'Arahy count',
          'description' => 'Arahy count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),
      

      'lupan_count' => array (
          'name' => 'lupan_count',
          'title' => 'Lupan count',
          'description' => 'Lupan count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),

      'lupal_count' => array (
          'name' => 'lupal_count',
          'title' => 'Lupal count',
          'description' => 'Lupal count',
          'type' => 'int',
          'handlers' => array (
              'filter' => array (
                  'name' => 'views_handler_filter_numeric',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_numeric',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),
      


     'domains_con' => array (
          'name' => 'domains_con',
          'title' => 'Domains_con',
          'description' => 'Domains_con',
          'type' => 'text',
          'handlers' => array (
              'filter' => array (
                  'name' => 'tripal_views_handler_filter_select_string',
              ),
              'field' => array (
                  'name' => 'views_handler_field',
              ),
              'sort' => array (
                  'name' => 'views_handler_sort',
              ),
              'argument' => array (
                  'name' => 'views_handler_argument_string',
              ),
              'relationship' => array (
                  'name' => 'views_handler_relationship',
              ),
          ),
          'joins' => array (
          ),
      ),
   ), 
    );
// add the array above that will integrate our qtl_search materialized view
    // for use with Drupal Views
    tripal_add_views_integration($integration);

}