Upgrade custom tables that may match the tables now in Chado v1.3.

There were many new tables that were added to Chado v1.3 that were suggested by the Chado user community. Some of those were Tripal users. Therefore, to help these Tripal users upgrade more seemlessly this function checks if those custom tables already exists, and if so updates them as best it can to match. At a minimum it will create the table if it doesn't exist and if it does it will change the primary keys and foreign keys to be big ints.

File

tripal_core/includes/tripal_core.chado_install.inc, line 376

Functions to install chado schema through Drupal

Code

function tripal_core_upgrade_chado_1_2_to_1_3_pre_alter() {

  // Include the Chado v1.3 schema definitions.
  module_load_include('inc', 'tripal_core', '/api/tripal_core.schema_v1.3.api');

  // The list of new tables in Chado v1.3
  $new_tables = array('analysis_cvterm', 'analysis_dbxref', 'analysis_pub', 'analysis_relationship',
    'contactprop', 'dbprop', 'feature_contact', 'featuremap_contact', 'featuremap_dbxref',
    'featuremap_organism', 'featuremapprop', 'featureposprop', 'library_contact',
    'library_expression', 'library_expressionprop', 'library_featureprop',
    'library_relationship', 'library_relationship_pub', 'nd_experiment_analysis',
    'organism_cvterm', 'organism_cvtermprop', 'organism_pub', 'organism_relationship',
    'organismprop_pub', 'phenotypeprop', 'phylotreeprop', 'project_analysis',
    'project_dbxref', 'project_feature', 'project_stock', 'pubauthor_contact',
    'stock_feature', 'stock_featuremap', 'stock_library', 'stockcollection_db',
  );

  // Get the name of the chado schema.
  $chado_schema = tripal_get_schema_name('chado');

  // Iterate through the new Chado tables and create them or if they already
  // exist then update them.
  foreach ($new_tables as $table) {

    // Get the schema for this table.
    $function = 'tripal_core_chado_schema_v1_3_' . $table;
    $schema = $function();

    // If the table exists then fix the pkeys and fkeys.
    if (chado_table_exists($table)) {

      // Update the primary key fields to be bigints.
      $fields = $schema['fields'];
      foreach ($fields as $field_name => $field) {
        if ($field['type'] == 'serial') {
          if (chado_column_exists($table, $field_name)) {
            $sql = 'ALTER TABLE {' . $table . '} ALTER COLUMN ' . $field_name . ' TYPE bigint';
            chado_query($sql);
          }
          else {
            throw new Exception('Could not alter primary key to bigint: ' . $table . '.' . $field_name);
          }
        }
      }

      // Update the foreign key fields to be bigints.
      $fkeys = $schema['foreign keys'];
      foreach ($fkeys as $fktable => $details) {
        foreach ($details['columns'] as $leftkey => $rightkey) {
          if (chado_column_exists($table, $leftkey)) {
            $sql = 'ALTER TABLE {' . $table . '} ALTER COLUMN ' . $leftkey . ' TYPE bigint';
            chado_query($sql);
          }
          else {
            throw new Exception('Could not alter foreign key to bigint: ' . $table . '.' . $leftkey);
          }
        }
      }

    }
  }
  // Now create the sequences if they don't already exist.
  $sequences = array(
    'analysis_cvterm_analysis_cvterm_id_seq',
    'analysis_dbxref_analysis_dbxref_id_seq',
    'analysis_pub_analysis_pub_id_seq',
    'analysis_relationship_analysis_relationship_id_seq',
    'contactprop_contactprop_id_seq',
    'dbprop_dbprop_id_seq',
    'feature_contact_feature_contact_id_seq',
    'featuremap_contact_featuremap_contact_id_seq',
    'featuremap_dbxref_featuremap_dbxref_id_seq',
    'featuremap_organism_featuremap_organism_id_seq',
    'featuremapprop_featuremapprop_id_seq',
    'featureposprop_featureposprop_id_seq',
    'library_contact_library_contact_id_seq',
    'library_expression_library_expression_id_seq',
    'library_expressionprop_library_expressionprop_id_seq',
    'library_featureprop_library_featureprop_id_seq',
    'library_relationship_library_relationship_id_seq',
    'library_relationship_pub_library_relationship_pub_id_seq',
    'nd_experiment_analysis_nd_experiment_analysis_id_seq',
    'organism_cvterm_organism_cvterm_id_seq',
    'organism_cvtermprop_organism_cvtermprop_id_seq',
    'organism_pub_organism_pub_id_seq',
    'organism_relationship_organism_relationship_id_seq',
    'organismprop_pub_organismprop_pub_id_seq',
    'phenotypeprop_phenotypeprop_id_seq',
    'phylotreeprop_phylotreeprop_id_seq',
    'project_analysis_project_analysis_id_seq',
    'project_dbxref_project_dbxref_id_seq',
    'project_feature_project_feature_id_seq',
    'project_stock_project_stock_id_seq',
    'pubauthor_contact_pubauthor_contact_id_seq',
    'stock_feature_stock_feature_id_seq',
    'stock_featuremap_stock_featuremap_id_seq',
    'stock_library_stock_library_id_seq',
    'stockcollection_db_stockcollection_db_id_seq'
  );
  foreach ($sequences as $sequence) {

    // Now add in the sequences if they don't already exist. There is no
    // PostgreSQL 'CREATE SEQUENCE IF NOT EXIST' so we're forced to do it here
    // and these create statements were removed from the diff upgrade file.
    if (!chado_sequence_exists($sequence)) {
      $sql = "CREATE SEQUENCE {" . $sequence . "} START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1";
      chado_query($sql);
    }
  }
}