tripal_seq.install

<?php
/*
 *  create database tables and columns
 */
function tripal_seq_install()
{
    
    /*
     *  Populate the diamond_settings table with a default row.
     */
    $tseq_settings = array(
        'settings_id'               => '0',
        'num_threads'               => '1',
        'file_expire_time'          => '30',
        'blast_exe_location'        => '',
        'diamond_exe_location'      => '',
        'preferred_remote_resource' => '',
        'defaults_e_value'          => '0.003',
        'defaults_target_coverage'  => '50',
        'defaults_query_coverage'   => '50',
        'defaults_max_alignments_list'  => '5, 10 25, 50, 100, 250, 500, 750, 1000',
        'defaults_max_alignments_selected'  => '10',
    );
    
    drupal_write_record('tseq_settings', $tseq_settings);
    
    /*
     *  Populate the diamond_categories table with default categories.
     *      Protein, Gene, and Genome (all enabled by default)
     */
    
    $tseq_category_standard = array(
        'category_title'            =>'Standard',
        'enabled'                   => '1'
    );
    drupal_write_record('tseq_categories', $tseq_category_standard);
}

    /*
     * Be nice, prevent clutter by uninstalling the content type
     * 
     * Is it possible to remove the content type but keep nodes in place
     * in the event of a reinstall/accidental uninstall?
     */
function tripal_seq_uninstall()
{
    $query = 'SELECT nid FROM {node} WHERE {node}.type = :type ';
    
    $result = db_query($query, array(':type'   => 'diamond_database'));
    
    $nids = array();
    
    foreach( $result as $row )
    {
        $nids[] = $row->nid;
    }
    
    node_delete_multiple( $nids );
    
    foreach (field_info_instances('node', 'diamond_database') as $field_name => $instance )
    {
        field_delete_instance($instance);
    }
    
    node_type_delete('diamond_database');
    
    field_purge_batch(1000);
}

function tripal_seq_schema()
{
    $schema['tseq_job_information'] = array(
        'description' => t('TSeq Job Information'),
        'fields' => array(
            'tseq_job_id' => array(
                'description' => t('Primary key'),
                'type' => 'serial',
                'unsigned' => true,
                'not null' => true,
            ),
            'tripal_job_id' => array(
                'description' => t('Tied to Tripal Job ID'),
                'type' => 'int',
                'unsigned' => false,
                'not null' => true,
            ),
            'user_id' => array(
                'description' => t('User ID of the submitting user'),
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
            ),
            'submit_time' => array(
                'description' => 'Date and time submitted (UNIX time)',
                'type' => 'int',
                'not NULL' => true,
            ),
            'sequence_query_type' => array(
                'description' => 'Type of Query (Protein, Nucleotide)',
                'type' => 'varchar',
                'length' => 31,
                'not NULL' => true,
            ),
            'database_search_type' => array(
                'description' => 'Type of Target for search (Protein, Nucleotide)',
                'type' => 'varchar',
                'length' => 31,
                'not NULL' => true,
            ),
            'sequence_file_type' => array(
                'description' => 'How the sequence was provided (paste, upload)',
                'type' => 'varchar',
                'length' => 31,
                'not NULL' => true, 
            ),
            'database_file_type' => array(
                'description' => 'How the database was provided (database, paste, upload)',
                'type' => 'varchar',
                'length' => 31,
                'not NULL' => true, 
            ),
            'sequence_file' => array(
                'description' => 'filename of the provided sequence (without path)',
                'type' => 'varchar',
                'length' => 255,
                'not NULL' => true,
            ),
            'database_file' => array(
                'description' => 'filename of the provided database (without path)',
                'type' => 'varchar',
                'length' => 255,
                'not NULL' => true,
            ),
            'command' => array(
                'description' => 'The assembled command to run on the remote machine',
                'type' => 'varchar',
                'length' => 5000,
                'not NULL' => true,
            ),
                'tseq_unique_id' => array(
                'descripton'    => 'TSeq Unique ID: TUI + job_id + #ofseqs + search type + date + time',
                'type'          => 'varchar',
                'length'        => 255,
                'not null'      => FALSE,
            ),
        ),
    );
    
    $schema['tseq_db_existing_locations'] = array(
        'description' => t('TSeq Database Locations for Pre-existing databases'),
        'fields' => array(
            'db_id' => array(
                'description' => t('ID of the database (autoincrement)'),
                'type' => 'serial',
                'not null' => true,
            ),
            'type' => array(
                'description' => t('Type of Database (Protein or Nucleotide)'),
                'type' => 'varchar',
                'length' => 31,
                'not null' => true,                
            ),
            'name' => array(
                'description' => t('Name of the database'),
                'type' => 'varchar',
                'length' => 255,
                'not null' => true,
            ),
            'version' => array(
                'description' => t('Version of the stored database'),
                'type' => 'varchar',
                'length' => 31,
            ),
            'location' => array(
                'description' => t('Location on disk where the database file exists'),
                'type' => 'varchar',
                'length' => 512,
                'not null' => true,
            ),
            //Perhaps present this (category) as a list of options, defined by the admin 
            'category' => array(
                'description' => t('Define a category for this database; e.g. TSA, UniGene'),
                'type' => 'varchar',
                'length' => 512,
                'not null' => false,
            ),
            'web_location' => array(
                'description'   => 'Web URL of original sequence if available (optional)',
                'type'          => 'varchar',
                'length'        => 512,
                'not null'      => FALSE,
            ),
            'count' => array(
                'descripton'    => 'Count of genes, proteins, or scaffolds in the original sequence',
                'type'          => 'int',
                'length'        => 15,
                'not null'      => FALSE,
            ),
            'status' => array(
                'description'   => 'Status of the readability of the file on disk',
                'type'          => 'int',
                'length'        => 7,
                'not null'      => FALSE,
                'default'       => -1,
            ),
        ),
    );
    
    $schema['tseq_settings'] = array(
        'description' => t('Administrator settings for Tripal Sequence Similarity Search module'),
        'fields' => array(
            'settings_id' => array(
                'description'   => t('Primary key for drupal_write_record purposes'),
                'type'          => 'int',
                'length'        => 7,
            ),
            'num_threads' => array(
                'description' => t('The number of threads a job is allowed to use on the remote machine.'),
                'type'          => 'int',
                'length'        => 7,
            ),
            'file_expire_time' => array(
                'description'   => t('The number of days to keep files related to a job'),
                'type'          => 'int',
                'length'        => 7,
            ),
            'blast_exe_location' => array(
                'description'   => t('Location on remote machine where the BLAST executables can be found'),
                'type'          => 'varchar',
                'length'        => 255,
            ),
            'diamond_exe_location' => array(
                'description'   => t('Location on remote machine where the Diamond executables can be found'),
                'type'          => 'varchar',
                'length'        => 255,
            ),
            'preferred_remote_resource' => array(
                'description'   => t('ID of the remote resource to use, as specified in the Tripal Remote Resource module'),
                'type'          => 'int',
                'length'        => 7,
            ),
            'defaults_e_value'  => array(
                'description'   => t('Default Submission Settings: e-value'),
                'type'          => 'varchar',
                'length'        => 15,
            ),
            'defaults_target_coverage'  => array(
                'description'   => t('Default Submission Settings: target coverage'),
                'type'          => 'varchar',
                'length'        => 7,
            ),
            'defaults_query_coverage'   => array(
                'description'   => t('Default Submission Settings: query coverage'),
                'type'          => 'varchar',
                'length'        => 7,
            ),
            'defaults_max_alignments_list'  => array(
                'description'   => t('Default Submission Settings: Available Max Alignments'),
                'type'          => 'varchar',
                'length'        => 255,
            ),
            'defaults_max_alignments_selected'  => array(
                'description'   => t('Default Submission Settings: Max Alignment Selected/Default'),
                'type'          => 'int',
                'length'        => 15,
            ),
            'diamond_exe_version' => array(
                'description'   => t('Diamond executable version'),
                'type'          => 'varchar',
                'length'        => 15,
                'not null'      => FALSE,
            ),
            'blast_exe_version' => array(
                'description'   => t('BLAST executable version'),
                'type'          => 'varchar',
                'length'        => 15,
                'not null'      => FALSE,
            ),
            'produce_blast_pairwise' => array(
                'description'   => t('Produce pairwise alignment view for BLAST jobs'),
                'type'          => 'int',
                'size'          => 'tiny',
                'default'       => 1,
                'not null'      => FALSE,
            ),
            'produce_diamond_pairwise'  => array(
                'description'   => t('Produce pairwise alignment view for Diamond jobs'),
                'type'          => 'int',
                'size'          => 'tiny',
                'default'       => 0,
                'not null'      => FALSE,
            ),
            'db_last_refresh' => array(
                'description'   => t('When the list of database files was last refreshed'),
                'type'          => 'int',
                'size'          => 'big',
                'default'       => 0,
                'not null'      => FALSE,
            ),
        ),
    );
    
    $schema['tseq_categories'] = array(
        'description' => t('Maintain a list of available categories for Database files'),
        'fields' => array(
            'category_id' => array(
                'description'   => t('Category ID'),
                'type'          => 'serial',
                'length'        => 7
            ),
            'category_title' => array(
                'description'   => t('Title of the category'),
                'type'          => 'varchar',
                'length'        => 127,
            ),
            'enabled'       => array(
                'description'   => t('Is this category enabled on the Submit Job page?'),
                'type'          => 'int',
                'length'        => 1,
            ),
        ),
    );
    
    $schema['tseq_results'] = array(
        'description' => t('Status and data from TSeq jobs'),
        'fields' => array(
            'tripal_job_id' => array(
                'description' => t('Tied to Tripal Job ID'),
                'type' => 'int',
                'unsigned' => false,
                'not null' => true,
            ),
            'user_id'       => array(
                'description' => t('User ID of the submitting user'),
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
            ),
            'tseq_db_id'    => array(
                'description' => t('The database associated with this job (if exist)'),
                'type'        => 'int',
                'unsigned'    => true,
                'not null'    => false,
            ),
            'data'          => array(
                'description'   => t('Miscellaneous data about the job (not actual results data'),
                'type'          => 'varchar',
                'not null'      => 'false',
            ),
            'summary'        => array(
                'description'   => t('Status of the job (success, failure, empty'),
                'type'          => 'varchar',
                'length'        => 31,
                'not null'      => true,
            ),
            'result_type'   => array(
                'description'   => t('What format are the results in (ex BLAST XML, TSEQ_INFO)'),
                'type'          => 'varchar',
                'length'        => 31,
                'not null'      => true,
            ),
            'sequence_count'    =>  array(
                'description'   => t('How many sequences were provided to search for'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => true,
            ),
            'matches'       => array(
                'description'   => t('The number of matches that were found and can be expected in the table'),
                'type'          => 'int',
                'length'        => 31,
                'not null'      => false,
            ),
            'database_used' => array(
                'description'   => t('The database that was used (if provided by site)'),
                'type'          => 'varchar',
                'length'        => 255,
                'not null'      => false,
            ),
        ),
    );
    
    $schema['tseq_results_data'] = array(
        'description'   => t('Data from the TSeq job, in BLAST XML format (12 columns)'),
        'fields'        => array(
            'tripal_job_id' => array(
                'description'   => t('Tied to Tripal Job ID'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => true,
            ),
            'match_id'  => array(
                'description'   => t('Which row of results is this'),
                'type'          =>  'int',
                'unsigned'      =>  false,
                'not null'      =>  true,
            ),
            'query_label'   => array(
                'description'   => t('Query Label'),
                'type'          => 'varchar',
                'length'        => 255,
                'not null'      => false,
            ),
            'target'    => array(
                'description'   => t('Target'),
                'type'          => 'varchar',
                'length'        => 255,
                'not null'      => false,
            ),
            'percent_identity'  => array(
                'description'   => t('Percent Identity'),
                'type'          => 'varchar',
                'length'        => 7,
                'not null'      => false,
            ),
            'alignment_length'  => array(
                'description'   => t('Alignment Length'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => false,
            ),
            'mismatches'    => array(
                'description'   => t('Mismatches'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => false,
            ),
            'gap_opens' => array(
                'description'   => t('Gap Opens'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => false,
            ),
            'start_position_query'  => array(
                'description'   => t('Start Position (Query)'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => false,
            ),
            'end_position_query'    => array(
                'description'   => t('End Postion (Query)'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => false,
            ),
            'start_position_database'   => array(
                'description'   => t('Start Position (Database)'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => false,
            ),
            'end_position_database' => array(
                'description'   => t('End Position (Database)'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => false,
            ),
            'e_value'   => array(
                'description'   => t('e-value'),
                'type'          => 'varchar',
                'length'        => 255,
                'not null'      => false,
            ),
            'bit_score' => array(
                'description'   => t('Target'),
                'type'          => 'varchar',
                'length'        => 255,
                'not null'      => false,
            ),
        )
    );
    
   return $schema;         
}
/**
 * This should be update 7001: add web_location field to tseq_db_existing_locations table
 */
function tripal_seq_update_5000()
{
    db_add_field(
        'tseq_db_existing_locations',
        'web_location',
        array(
            'description'   => 'Web URL of original sequence if available (optional)',
            'type'          => 'varchar',
            'length'        => 512,
            'not null'      => FALSE,
        )
    );
}

/**
 * Add tseq_results table, keep track of results within the database
 */
function tripal_seq_update_7002()
{
    db_create_table(
        'tseq_results',
        array(
            'description' => t('Status and data from TSeq jobs'),
            'fields' => array(
                'tripal_job_id' => array(
                    'description' => t('Tied to Tripal Job ID'),
                    'type' => 'int',
                    'unsigned' => false,
                    'not null' => true,
                ),
                'user_id'       => array(
                    'description' => t('User ID of the submitting user'),
                    'type' => 'int',
                    'unsigned' => true,
                    'not null' => true,
                ),
                'tseq_db_id'    => array(
                    'description' => t('The database associated with this job (if exist)'),
                    'type'        => 'int',
                    'unsigned'    => true,
                    'not null'    => false,
                ),
                'data'          => array(
                    'description'   => t('Miscellaneous data about the job (not actual results data'),
                    'type'          => 'varchar',
                    'not null'      => 'false',
                ),
                'summary'        => array(
                    'description'   => t('Status of the job (success, failure, empty'),
                    'type'          => 'varchar',
                    'length'        => 31,
                    'not null'      => true,
                ),
                'result_type'   => array(
                    'description'   => t('What format are the results in (ex BLAST XML, TSEQ_INFO)'),
                    'type'          => 'varchar',
                    'length'        => 31,
                    'not null'      => true,
                ),
                'sequence_count'    =>  array(
                    'description'   => t('How many sequences were provided to search for'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => true,
                ),
                'matches'       => array(
                    'description'   => t('The number of matches that were found and can be expected in the table'),
                    'type'          => 'int',
                    'length'        => 31,
                    'not null'      => false,
                ),
                'database_used' => array(
                    'description'   => t('The database that was used (if provided by site)'),
                    'type'          => 'varchar',
                    'length'        => 255,
                    'not null'      => false,
                ),
            ),
        )
    );
}

/**
 * Add tseq_results_data table to handle data from successful jobs
 */
function tripal_seq_update_7003()
{
    db_create_table(
        'tseq_results_data',
        array(
            'description'   => t('Data from the TSeq job, in BLAST XML format (12 columns)'),
            'fields'        => array(
                'tripal_job_id' => array(
                'description'   => t('Tied to Tripal Job ID'),
                'type'          => 'int',
                'unsigned'      => false,
                'not null'      => true,
                ),
                'match_id'  => array(
                    'description'   => t('Which row of results is this'),
                    'type'          =>  'int',
                    'unsigned'      =>  false,
                    'not null'      =>  true,
                ),
                'query_label'   => array(
                    'description'   => t('Query Label'),
                    'type'          => 'varchar',
                    'length'        => 255,
                    'not null'      => false,
                ),
                'target'    => array(
                    'description'   => t('Target'),
                    'type'          => 'varchar',
                    'length'        => 255,
                    'not null'      => false,
                ),
                'percent_identity'  => array(
                    'description'   => t('Percent Identity'),
                    'type'          => 'varchar',
                    'length'        => 7,
                    'not null'      => false,
                ),
                'alignment_length'  => array(
                    'description'   => t('Alignment Length'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => false,
                ),
                'mismatches'    => array(
                    'description'   => t('Mismatches'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => false,
                ),
                'gap_opens' => array(
                    'description'   => t('Gap Opens'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => false,
                ),
                'start_position_query'  => array(
                    'description'   => t('Start Position (Query)'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => false,
                ),
                'end_position_query'    => array(
                    'description'   => t('End Postion (Query)'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => false,
                ),
                'start_position_database'   => array(
                    'description'   => t('Start Position (Database)'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => false,
                ),
                'end_position_database' => array(
                    'description'   => t('End Position (Database)'),
                    'type'          => 'int',
                    'unsigned'      => false,
                    'not null'      => false,
                ),
                'e_value'   => array(
                    'description'   => t('e-value'),
                    'type'          => 'varchar',
                    'length'        => 255,
                    'not null'      => false,
                ),
                'bit_score' => array(
                    'description'   => t('Target'),
                    'type'          => 'varchar',
                    'length'        => 255,
                    'not null'      => false,
                ),
            ),
        )
    );
}
/**
 * Add additional fields to the settings table to handle "default advanced configuration parameters"
 */
function tripal_seq_update_7004()
{
    db_add_field(
        'tseq_settings',
        'defaults_e_value',
            array(
                'description'   => t('Default Submission Settings: e-value'),
                'type'          => 'varchar',
                'length'        => 15,
            )
        );
    db_add_field('tseq_settings',
        'defaults_target_coverage',
            array(
                'description'   => t('Default Submission Settings: target coverage'),
                'type'          => 'varchar',
                'length'        => 7,
            )
        );
    db_add_field('tseq_settings',
        'defaults_query_coverage',
            array(
                'description'   => t('Default Submission Settings: query coverage'),
                'type'          => 'varchar',
                'length'        => 7,
            )
        );
    db_add_field('tseq_settings',
        'defaults_max_alignments_list',
            array(
                'description'   => t('Default Submission Settings: Available Max Alignments'),
                'type'          => 'varchar',
                'length'        => 255,
            )
        );
    db_add_field('tseq_settings',
        'defaults_max_alignments_selected',
            array(
                'description'   => t('Default Submission Settings: Max Alignment Selected/Default'),
                'type'          => 'int',
                'length'        => 15,
            )
        );
}

/**
 * Add "count" to tseq_db_existing_locations to keep track of gene/protein/scaffold count in original sequence
 */
function tripal_seq_update_7005()
{
    db_add_field(
        'tseq_db_existing_locations',
        'count',
        array(
            'descripton'    => 'Count of genes, proteins, or scaffolds in the original sequence',
            'type'          => 'int',
            'length'        => 15,
            'not null'      => FALSE,
        )
    );
}

/**
 * Add "tseq_unique_id" to tseq_job_information "TSeq Unique ID, TUI". Used to keep track of jobs with built-in
 * meta-data.
 */
function tripal_seq_update_7006()
{
    db_add_field(
        'tseq_job_information',
        'tseq_unique_id',
        array(
            'descripton'    => 'TSeq Unique ID: TUI + job_id + #ofseqs + search type + date + time',
            'type'          => 'varchar',
            'length'        => 255,
            'not null'      => FALSE,
        )
    );
}

/**
 * Populates the tseq_unique_id field from tseq_job_information table with data
 */

function tripal_seq_update_7007()
{
    $v1Jobs = db_select('tseq_job_information', 't')
            ->fields('t', array('tseq_job_id', 'tripal_job_id','tseq_unique_id','submit_time'))
            ->condition('tseq_unique_id',NULL)
            ->orderBy('tseq_job_id','ASC')
            ->execute();
    //$i = 0;
    foreach ($v1Jobs as $v1Job)
    {
        $newTUI = "TUI_".$v1Job->tripal_job_id."_legacyV1_".format_date($v1Job->submit_time, 'custom', 'Ymd_His');
        
        db_update('tseq_job_information')
            ->fields(array('tseq_unique_id' => $newTUI))
            ->condition('tseq_job_id', $v1Job->tseq_job_id)
            ->execute();
        
    }
}

/**
 * Create a column for Diamond version and for BLAST version (both can be NULL)
 */
function tripal_seq_update_7008()
{
    // Diamond exe version
    db_add_field(
        'tseq_settings',
        'diamond_exe_version',
        array(
            'descripton'    => 'Diamond executable version',
            'type'          => 'varchar',
            'length'        => 15,
            'not null'      => FALSE,
        )
    );
    
    // BLAST exe version
    db_add_field(
        'tseq_settings',
        'blast_exe_version',
        array(
            'descripton'    => 'BLAST executable version',
            'type'          => 'varchar',
            'length'        => 15,
            'not null'      => FALSE,
        )
    );
}

/**
 * Increase the character limit for "command" in tseq_job_information to 5000
 */
function tripal_seq_update_7009()
{
    db_change_field('tseq_job_information','command','command',
        array(
            'description' => 'The assembled command to run on the remote machine',
            'type' => 'varchar',
            'length' => 5000,
            'not NULL' => true,
        )
    );
}

/**
 * Add "produce pairwise alignments" for Diamond and BLAST jobs setting
 */
function tripal_seq_update_7010()
{
    # Produce BLAST Pairwise alignment view
    db_add_field(
        'tseq_settings',
        'produce_blast_pairwise',
        array(
            'description'   => t('Produce pairwise alignment view for BLAST jobs'),
            'type'          => 'int',
            'size'          => 'tiny',
            'default'       => 1,
            'not null'      => FALSE,
        )
    );
    # Produce DIAMOND Pairwise alignment view
    db_add_field(
        'tseq_settings',
        'produce_diamond_pairwise',
        array(
            'description'   => t('Produce pairwise alignment view for Diamond jobs'),
            'type'          => 'int',
            'size'          => 'tiny',
            'default'       => 0,
            'not null'      => FALSE,
        )
    );
}

/**
 * Drop the tseq_jobs table
 */
function tripal_seq_update_7011()
{
    if(db_table_exists('tseq_jobs')) 
    {
        // Check if the table has rows
        $count = db_query("SELECT COUNT(*) FROM tseq_jobs")->fetchField();
        if ($count == 0) 
        {
            // No rows, so we can drop the table
            echo "Dropping tseq_jobs table...\n";
            db_drop_table('tseq_jobs');
        }
        else
        {
            echo "there is data in the tseq_jobs table. Not dropping the table.\n";
            // There are rows, so we can't drop the table
            return;
        }

    }
}

/**
 * Add status field to tseq_db_existing_locations table
 */
function tripal_seq_update_7012()
{
    db_add_field(
        'tseq_db_existing_locations',
        'status',
        array(
            'description'   => t('Status of the readability of the file on disk'),
            'type'          => 'int',
            'size'          => 'tiny',
            'default'       => -1,
            'not null'      => FALSE,
        )
    );
}

/**
 * Continue to overload this silly table
 */
function tripal_seq_update_7013()
{
    # Add last_refreshed to settings table
    db_add_field(
        'tseq_settings',
        'db_last_refresh',
        array(
            'description'   => t('When the list of database files was last refreshed'),
            'type'          => 'int',
            'size'          => 'big',
            'default'       => 0,
            'not null'      => FALSE,
        )
    );
}