tripal/tripal_chado/tripal_chado.install

<?php

function tripal_chado_install() {
  // The foreign key specification doesn't really add one to the
  // Drupal schema, it is just used internally, but we want one.
  db_query('
      ALTER TABLE {tripal_custom_tables}
      ADD CONSTRAINT tripal_custom_tables_fk1
      FOREIGN KEY (mview_id) REFERENCES {tripal_mviews} (mview_id)
      ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
  ');

  // Add the cv_root_mview.
  tripal_cv_add_cv_root_mview();

  // Add defaults to the tables that correlate OBO files/references with
  // a chado CV.
  tripal_cv_add_obo_defaults();

  // Add the Chado ontology CV.
  $obo_path = '{tripal_chado}/files/cv_property.obo';
  $obo_id = tripal_insert_obo('Chado CV Properties', $obo_path);
  tripal_submit_obo_job(array('obo_id' => $obo_id));

  // Create the temp table we will use for loading OBO files.
  tripal_cv_create_tripal_obo_temp();

  // Unfortunately, some Chado base tables do not have a type_id, so we must
  // take special action for those tables.  These include: organism and
  // analysis. Until we can find an appropriate controlled vocabulary
  // that is well supported by the community with types for these tables we
  // will have to use in-house terms.

  // Add a term to be used for an inherent 'type_id' for the organism table.
  tripal_insert_cvterm(array(
    'id' => 'local:organism',
    'name' => 'organism',
    'definition' => 'An individual form of life, such as a bacterium, protist, ' .
    'fungus, plant, or animal, composed of a single cell or a complex of cells  ' .
    'in which organelles or organs work together to carry out the various  ' .
    'processes of life. (American Heritage® Dictionary of the English ' .
    'Language, Fifth Edition. Copyright © 2011 by Houghton Mifflin ' .
    'Harcourt Publishing Company).',
    'cv_name' => 'local',
  ));

  // Add a term to be used for an inherent 'type_id' for the organism table.
  tripal_insert_cvterm(array(
    'id' => 'local:analysis',
    'name' => 'analysis',
    'definition' => 'A process as a method of studying the nature of something ' .
    'or of determining its essential features and their relations. ' .
    '(Random House Kernerman Webster\'s College Dictionary, © 2010 K ' .
    'Dictionaries Ltd).',
    'cv_name' => 'local',
  ));

  tripal_insert_cvterm(array(
    'id' => 'local:project',
    'name' => 'project',
    'definition' => 'A plan or proposal for accomplishing something. ' .
    '(American Heritage® Dictionary of the English Language, Fifth Edition. ' .
    'Copyright © 2011 by Houghton Mifflin Harcourt Publishing Company).',
    'cv_name' => 'local',
  ));

  // For the TripalBundle entities we will want to associate the cvterm_id,
  // and the chado table and field that it maps to.  We will use a few
  // variables to do this:
  tripal_insert_variable('chado_cvterm_id', 'The cvterm_id that a TripalBundle maps to.');
  tripal_insert_variable('chado_table', 'The name of the table to which a TripalBundle maps.');
  tripal_insert_variable('chado_column', 'The name of the column within the table that a TripalBundle maps to.');

  // We want to provide a set of commonly used entity types by default. This
  // way when a user first installs Tripal there are some commonly used
  // formats.
  module_load_include('inc', 'tripal', 'api/tripal.api');
  module_load_include('inc', 'tripal', 'includes/tripal.admin');

  // Create the 'Organism' entity type. This uses the local:organism term.
  $error = '';
  $term = array('name' => 'organism', 'cv_id' => array('name' => 'local'));
  $cvterm = chado_generate_var('cvterm', $term);
  if (!tripal_create_bundle('local', 'organism', 'organism', $error)) {
    throw new Exception($error);
  }

  // Create the 'Analysis' entity type. This uses the local:analysis term.
  $error = '';
  $term = array('name' => 'analysis', 'cv_id' => array('name' => 'local'));
  $cvterm = chado_generate_var('cvterm', $term);
  if (!tripal_create_bundle('local', 'analysis', 'analysis', $error)) {
    throw new Exception($error);
  }

  // Create the 'Project' entity type. This uses the local:project term.
  $error = '';
  $term = array('name' => 'project', 'cv_id' => array('name' => 'local'));
  $cvterm = chado_generate_var('cvterm', $term);
  if (!tripal_create_bundle('local', 'project', 'project', $error)) {
    throw new Exception($error);
  }
}

/**
 * Implementation of hook_uninstall().
 *
 * @ingroup tripal
 */
function tripal_chado_uninstall() {

  // Drop the foreign key between tripal_custom_tables and tripal_mviews
  // so that Drupal can then drop the tables
  db_query('
    ALTER TABLE {tripal_custom_tables}
    DROP CONSTRAINT tripal_custom_tables_fk1 CASCADE
  ');
}

/**
 * Table definition for the tripal_cv_obo table
 * @param $schema
 */
function tripal_chado_tripal_cv_obo_schema(&$schema) {
  return array(
    'fields' => array(
      'obo_id' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE
      ),
      'name' => array(
        'type' => 'varchar',
        'length' => 255
      ),
      'path'  => array(
        'type' => 'varchar',
        'length' => 1024
      ),
    ),
    'indexes' => array(
      'tripal_cv_obo_idx1' => array('obo_id'),
    ),
    'primary key' => array('obo_id'),
  );
}

/**
 * * Table definition for the tripal_cv_defaults table
 * @param unknown $schema
 */
function tripal_chado_tripal_cv_defaults_schema(&$schema) {
  return array(
    'fields' => array(
      'cv_default_id' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE
      ),
      'table_name' => array(
        'type' => 'varchar',
        'length' => 128,
        'not null' => TRUE,
      ),
      'field_name' => array(
        'type' => 'varchar',
        'length' => 128,
        'not null' => TRUE,
      ),
      'cv_id' => array(
        'type' => 'int',
        'not null' => TRUE,
      )
    ),
    'indexes' => array(
      'tripal_cv_defaults_idx1' => array('table_name', 'field_name'),
    ),
    'unique keys' => array(
      'tripal_cv_defaults_unq1' => array('table_name', 'field_name', 'cv_id'),
    ),
    'primary key' => array('cv_default_id')
  );
}

/**
 * Add a materialized view of root terms for all chado cvs. This is needed for viewing cv trees
 *
 * @ingroup tripal_cv
 */
function tripal_cv_add_cv_root_mview() {
  $mv_name = 'cv_root_mview';
  $comment = 'A list of the root terms for all controlled vocabularies. This is needed for viewing CV trees';
  $schema = array(
    'table' => $mv_name,
    'description' => $comment,
    'fields' => array(
      'name' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
      ),
      'cvterm_id' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'cv_id' => array(
        'type' => 'int',
        'not null' => TRUE,
      ),
      'cv_name' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
      ),
    ),
    'indexes' => array(
      'cv_root_mview_indx1' => array('cvterm_id'),
      'cv_root_mview_indx2' => array('cv_id'),
    ),
  );

  $sql = "
    SELECT DISTINCT CVT.name,CVT.cvterm_id, CV.cv_id, CV.name
    FROM cvterm_relationship CVTR
      INNER JOIN cvterm CVT on CVTR.object_id = CVT.cvterm_id
      INNER JOIN cv CV on CV.cv_id = CVT.cv_id
    WHERE CVTR.object_id not in
      (SELECT subject_id FROM cvterm_relationship)
  ";

  // Create the MView
  tripal_add_mview($mv_name, 'tripal_cv', $schema, $sql, $comment);
}

/**
 * Add's defaults to the tripal_cv_obo table
 *
 * @ingroup tripal_cv
 */
function tripal_cv_add_obo_defaults() {

  // Insert commonly used ontologies into the tables.
  $ontologies = array(
    array('Relationship Ontology', 'http://purl.obolibrary.org/obo/ro.obo'),
    //    array('Relationship Ontology (older deprecated version)', 'http://www.obofoundry.org/ro/ro.obo'),
    array('Sequence Ontology', 'https://github.com/The-Sequence-Ontology/SO-Ontologies/blob/master/so-xp-simple.obo'),
    array('Gene Ontology', 'http://www.geneontology.org/ontology/gene_ontology.obo'),
    //    array('Cell Ontology', 'https://raw.githubusercontent.com/obophenotype/cell-ontology/master/cl.obo'),
  //    array('Plant Structure Ontology', 'http://palea.cgrb.oregonstate.edu/viewsvn/Poc/trunk/ontology/OBO_format/po_anatomy.obo?view=co'),
  //    array('Plant Growth and Development Stages Ontology', 'http://palea.cgrb.oregonstate.edu/viewsvn/Poc/trunk/ontology/OBO_format/po_temporal.obo?view=co')
  );
  foreach ($ontologies as $o) {
    db_query("INSERT INTO {tripal_cv_obo} (name,path) VALUES (:name, :path)", array(':name' => $o[0], ':path' => $o[1]));
  }
}

/**
 * Implements hook_schema().
 */
function tripal_chado_schema() {

  // Links TripalEntity entities to the chado record.
  $schema['chado_entity'] = tripal_chado_chado_entity_schema();
  $schema['tripal_mviews'] = tripal_chado_tripal_mviews_schema();
  $schema['tripal_custom_tables'] = tripal_chado_tripal_custom_tables_schema();

  $schema['tripal_cv_obo'] = tripal_chado_tripal_cv_obo_schema($schema);
  $schema['tripal_cv_defaults'] = tripal_chado_tripal_cv_defaults_schema($schema);

  // if this module is already installed and enabled, then we want to provide
  // the schemas for all of the custom tables.  This will allow Views to
  // see the schemas.  We check if the module is installed because during
  // installation we don't want to make these custom tables available as we don't
  // want them created in the Drupal database.  The custom tables go in the
  // Chado database.
  if (db_table_exists('tripal_custom_tables')) {
    $sql = 'SELECT * FROM {tripal_custom_tables}';
    $results = db_query($sql);
    foreach ($results as $custom) {
      $schema[$custom->table_name] = unserialize($custom->schema);
    }
  }

  return $schema;
}

/**
 * @section
 * Schema Definitions.
 */
/**
 * Describes the Tripal Custom Tables (tripal_custom_tables) table
 * This keeps track of tables created by Tripal and stored in chado that may or may not
 * also be materialized views.
 *
 * @ingroup tripal
 */
function tripal_chado_tripal_custom_tables_schema() {
  return array(
    'fields' => array(
      'table_id' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not NULL' => TRUE
      ),
      'table_name' => array(
        'type' => 'varchar',
        'length' => 255,
        'not NULL' => TRUE
      ),
      'schema' => array(
        'type' => 'text',
        'not NULL' => TRUE
      ),
      'mview_id' => array(
        'type' => 'int',
        'not NULL' => FALSE
      )
    ),
    'indexes' => array(
      'table_id' => array('table_id'),
    ),
    'primary key' => array('table_id'),
    'foreign keys' => array(
      'tripal_mviews' => array(
        'table' => 'tripal_mviews',
        'columns' => array(
          'mview_id' => 'mview_id'
        ),
      ),
    ),
  );
}
/**
 * Describes the Tripal Materialized View (tripal_mviews) table
 * This table keeps track of all materialized views created by Tripal and stored in chado
 *
 * @ingroup tripal
 */
function tripal_chado_tripal_mviews_schema() {
  return array(
    'fields' => array(
      'mview_id' => array(
        'type' => 'serial',
        'unsigned' => TRUE,
        'not NULL' => TRUE
      ),
      'name' => array(
        'type' => 'varchar',
        'length' => 255,
        'not NULL' => TRUE
      ),
      'modulename' => array(
        'type' => 'varchar',
        'length' => 50,
        'not NULL' => TRUE,
        'description' => 'The module name that provides the callback for this job'
      ),
      'mv_table' => array(
        'type' => 'varchar',
        'length' => 128,
        'not NULL' => FALSE
      ),
      'mv_specs' => array(
        'type' => 'text',
        'size' => 'normal',
        'not NULL' => FALSE
      ),
      'mv_schema' => array(
        'type' => 'text',
        'size' => 'normal',
        'not NULL' => FALSE
      ),
      'indexed' => array(
        'type' => 'text',
        'size' => 'normal',
        'not NULL' => FALSE
      ),
      'query' => array(
        'type' => 'text',
        'size' => 'normal',
        'not NULL' => TRUE
      ),
      'special_index' => array(
        'type' => 'text',
        'size' => 'normal',
        'not NULL' => FALSE
      ),
      'last_update' => array(
        'type' => 'int',
        'not NULL' => FALSE,
        'description' => 'UNIX integer time'
      ),
      'status'        => array(
        'type' => 'text',
        'size' => 'normal',
        'not NULL' => FALSE
      ),
      'comment' => array(
        'type' => 'text',
        'size' => 'normal',
        'not NULL' => FALSE
      ),
    ),
    'indexes' => array(
      'mview_id' => array('mview_id')
    ),
    'unique keys' => array(
      'mv_table' => array('mv_table'),
      'mv_name' => array('name'),
    ),
    'primary key' => array('mview_id'),
  );
}

/**
 * Links Biological Data Entities to the chado "base" table the data is stored in.
 * This is where we would specify that a particular gene maps to the record in the
 * chado.feature table with a feature_id=2432;
 */
function tripal_chado_chado_entity_schema() {

  $schema = array(
    'description' => 'The linker table that associates an enitity from the public.tripal_entity table with a "base" record in Chado',
    'fields' => array(
      'chado_entity_id' => array(
        'description' => 'The primary identifier for this table.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'entity_id' => array(
        'description' => 'The unique entity id.',
        'type' => 'int',
        'not null' => TRUE,
      ),
      'record_id' => array(
        'description' => 'The unique numerical identifier for the record that this entity is associated with (e.g. feature_id, stock_id, library_id, etc.).',
        'type' => 'int',
        'not null' => TRUE,
      ),
      'data_table' => array(
        'description' => 'Indicates the table in Chado that this term services (e.g. feature, stock, library, etc.)',
        'type' => 'varchar',
        'length' => 128,
        'not null' => TRUE,
        'default' => '',
      ),
      'type_table' => array(
        'description' => 'Sometimes the record in the data table doesn’t have a field that specifies  the record type.  For example, an analysis type is stored in the analysisprop table.  If the data_table does have a type field then this value will be the same as the data_table.',
        'type' => 'varchar',
        'length' => 128,
        'not null' => TRUE,
        'default' => '',
      ),
      'field' => array(
        'description' => 'The name of the field in the typetable that contains the cvterm record.',
        'type' => 'varchar',
        'length' => 128,
        'not null' => FALSE,
        'default' => ''
      ),
    ),
    'indexes' => array(
      'record_id' => array('record_id'),
      'entity_id' => array('entity_id'),
      'data_table' => array('data_table'),
    ),
    'unique keys' => array(
      'record' => array('data_table', 'record_id'),
      'entity_id' => array('entity_id'),
    ),
    'primary key' => array('chado_entity_id'),
  );
  return $schema;
}