tripal/extensions/tripal_search_unigene/tripal_search_unigene.install

<?php

/*******************************************************************************
*  Implementation of hook_install();
*/
function tripal_search_unigene_install(){

   // create the module's data directory
   tripal_create_moddir('tripal_search_unigene');
	
	// Create a sequence as the primiry key for the unigene_for_search
	$sql = "DROP SEQUENCE IF EXISTS unigene_for_search_id; CREATE SEQUENCE unigene_for_search_id";
	chado_query($sql);	

	$view_name = 'unigene_for_search';
   // 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);
   }

   $schema = " unigene_for_search_id integer, 
   							  feature_id integer,
   							  feature_type character varying(1024),
   							  feature_name character varying(1024),
   							  feature_uniquename text,
   							  feature_is_obsolete boolean,
   							  organism_common_name character varying(255),
   							  feature_seqlen integer,
   							  go_term character varying(1024),
   							  unigene character varying(255),
   							  blast_value text,
   							  kegg_value text,
   							  interpro_value text
   							  ";
   
   $index = "feature_id, go_term, unigene, blast_value, kegg_value, interpro_value";
   $singlet_type_id = tripal_get_cvterm_id('singlet_in_analysis');
   if ($singlet_type_id) {
   	$singlet_condition = " AND F.feature_id NOT IN (SELECT feature_id from feature
   						   WHERE type_id = (select cvterm_id from cvterm where name = 'EST')
   						   AND feature_id NOT IN (SELECT F.feature_id FROM feature F
   						   INNER JOIN featureprop FP ON F.feature_id = FP.feature_id
   						   AND FP.type_id = (select cvterm_id from cvterm where name = 'singlet_in_analysis')))";
   }
   $EST_type = db_result(chado_query("SELECT cvterm_id FROM {cvterm} WHERE name = 'EST' AND cv_id = (SELECT cv_id FROM {cv} WHERE name = 'sequence')")); //P.S. 'EST' is not a tripal term
   $sql = "SELECT nextval ('unigene_for_search_id') AS unigene_for_search_id, T.* FROM (
						SELECT F.feature_id AS feature_id, 
										(SELECT name FROM cvterm WHERE F.type_id = cvterm_id) AS feature_type,
										F.name AS feature_name, 
										F.uniquename AS feature_uniquename,
										F.is_obsolete AS feature_is_obsolete,
										O.common_name AS organism_common_name, 
										F.seqlen AS feature_seqlen, 
										C.name AS go_term,
										UNIGENE.name AS unigene_name,
										BLAST.value AS blast_value, 
										KEGG.value AS KEGG_value, 
										INTERPRO.value AS interpro_value 
						FROM feature F
						LEFT JOIN organism O ON F.organism_id = O.organism_id 
						LEFT JOIN feature_cvterm FC ON F.feature_id = FC.feature_id 
						LEFT JOIN cvterm C ON FC.cvterm_id = C.cvterm_id 
						LEFT JOIN (SELECT AF.feature_id, AFP.value FROM analysisfeatureprop AFP
													INNER JOIN analysisfeature AF ON AF.analysisfeature_id = AFP.analysisfeature_id
													WHERE type_id = (SELECT cvterm_id FROM cvterm 
																								WHERE name = 'analysis_blast_besthit_description' 
																								AND cv_id = (SELECT cv_id FROM cv WHERE name = 'tripal')
																								) GROUP BY feature_id, value
													) BLAST ON BLAST.feature_id = F.feature_id
						LEFT JOIN (SELECT AF.feature_id, AFP.value FROM analysisfeatureprop AFP
													INNER JOIN analysisfeature AF ON AF.analysisfeature_id = AFP.analysisfeature_id
													WHERE type_id = (SELECT cvterm_id FROM cvterm 
																								WHERE name = 'analysis_kegg_output_keywords' 
																								AND cv_id = (SELECT cv_id FROM cv WHERE name = 'tripal')
																								) GROUP BY feature_id, value			
													) KEGG ON KEGG.feature_id = F.feature_id
						LEFT JOIN (SELECT AF.feature_id, AFP.value FROM analysisfeatureprop AFP
													INNER JOIN analysisfeature AF ON AF.analysisfeature_id = AFP.analysisfeature_id
													WHERE type_id = (SELECT cvterm_id FROM cvterm
																								WHERE name = 'analysis_interpro_output_keywords' 
																								AND cv_id = (SELECT cv_id FROM cv WHERE name = 'tripal')
																								) GROUP BY feature_id, value
													) INTERPRO ON INTERPRO.feature_id = F.feature_id 
						LEFT JOIN (SELECT * FROM analysisfeature AF 
													INNER JOIN analysis A ON A.analysis_id = AF.analysis_id 
													INNER JOIN analysisprop AP ON AF.analysis_id = AP.analysis_id
													WHERE AP.type_id = (SELECT cvterm_id FROM cvterm
																								WHERE name = 'analysis_type' 
																								AND cv_id = (SELECT cv_id FROM cv WHERE name = 'tripal')
																								)
													AND value = 'tripal_analysis_unigene') UNIGENE ON F.feature_id = UNIGENE.feature_id
						WHERE F.type_id != (SELECT cvterm_id FROM cvterm WHERE name = 'EST_match') $singlet_condition
						) T";
   
      // Create the MView
      tripal_add_mview(
      // view name
      $view_name,
      // tripal module name
      ' tripal_search_unigene',
      // table name
      $view_name,
      // table schema definition
		$schema,
      // columns for indexing
      $index,
      // SQL statement to populate the view
      $sql,
      // 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);
   }

}

/*******************************************************************************
 * Implementation of hook_uninstall()
 */
function tripal_search_unigene_uninstall(){
	$view_name = 'unigene_for_search';
	// 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);
	}
	 
	// Drop the sequence
	$sql = "DROP SEQUENCE IF EXISTS unigene_for_search_id";
	chado_query($sql);
}

/*******************************************************************************
 * Implementation of hook_requirements(). Make sure 'Tripal Core' is enabled
 * before installation
 */
function tripal_search_unigene_requirements($phase) {
   $requirements = array();
   if ($phase == 'install') {
      if (!function_exists('tripal_create_moddir')) {
         $requirements ['tripal_search_unigene'] = array(
            'title' => "tripal_search_unigene",
            'value' => "error. Some required modules are just being installed. Please try again.",
            'severity' => REQUIREMENT_ERROR,
         );
      }
   }
   return $requirements;
}