Views will only allow a single entry for a chado table in hook_views_data. Thus currently, although there is the User Interface to create a custom views definition for a chado table it is overridden by the default views integration and as such non-functional.

In order to solve this it has been proposed that we add priorities to views integration definitions and choose the definition with the lightest (drupal-style) priority. Priorities will be as follows:

How Definition is provided Priority Range Default Priority
base tripal module (feature, cv, etc) 10 10
custom modules (ND Genotypes, etc.) 9 to 0 9
through UI -1 to -10 -1

This way definitions created through the web interface will always override those created by modules and custom modules will always override base tripal modules.

The API will consist of a two functions called in the .install file of the module.

  • tripal_core_views_integration_add_entry($defn_array) which takes a single paramater defining the table and it's fields (see below for proposed array structure.
  • tripal_core_views_integration_remove_entry($tablename, $priority) where tablename and priority uniquely identify the views definition to remove.

Proposed $defn_array:

array(
    'table' => <tablename>,
    'type' => mview | chado,
    'description' => <description for views>,
    'priority' => -10 to 10,
    'fields' => array(
      <field name> => array(
        'name' => <field name in database>,
        'title' => <human-readable name -in views>,
        'handlers' => array(
          'field' => array(
            'name' => <handler name>
          ),
          'filter' => array( ... ),
          ...
        ),
        'join' => array(
          'table' => <table to join to>,
          'field' => <field to join to>
        ),
      )
    ),
);

Comments

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

For Base Tripal module the $defn_array will be at least partially generated from the hook_chado_
_schema() table definition. From the schema definition we know the tablename, type, list of fields with field name -we can choose handlers for field/filter/sort with a switch based on the field type from schema. Then we just need to hardcode the human readable names and description in the above hook. This allows us to keep views integration even when we switch chado versions.

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

API functions in place: tripal_core_views_integration_add_entry($defn_array) and tripal_core_views_integration_remove_entry($tablename, $priority). Neither yet use priority.

Commit 6.x-1482618-API_for_default_views_integration 38a1070

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Need to make some changes to the tripal_views* tables:

  1. Add priority field to tripal_views: Allows us to select the views setup with the lightest (drupal-style) priority
  2. Add handler field to tripal_views_join: Need to specify the handler to use per join, some will need the default views_join but others will need views_handler_join_chado_aggregator.
  3. Create tripal_views_fields table with column_name, title, description, type: Needed to keep track of fields for chado table integration. I assume mviews integration just looks this up in the mview schema definition but that won't work for chado tables since it's not in the db.
laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Table modifications discussed in comment #3 were done in 6.x-1482618-API_for_default_views_integration 224a032.

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Started work on an API for tripal views.

List of Functions so far:

tripal_views_get_lightest_priority_setup ($table_name)

Retrieve the views integration setup with the lightest priority for a given table

NOTE: Uses lightest priority (drupal-style) where the range is from -10 to 10
and -10 is of highest priority.

@param $table_name
The name of the table to retrieve the setup ID for. This can be either a materialized
view or a chado table

@return
On success, the setup_id to use for integration of this table; otherwise FALSE

tripal_views_is_integrated($table_name, $priority = NULL)

Check to see if this table already has an integration record with the given priority

@param $table_name
The name of the table to check for integration
@param $priority (optional)
The priority of record to check for

@return
If the table is already integrated, the setup_id of the existing integration
record is returned (If priority is not specified this will be the lightest record);
Otherwise the table is not already integrated and FALSE is returned.

tripal_views_is_lightest_priority_setup ($setup_id, $table_name)

Checks if you are dealing with the lightest priority setup for a given table

@param $setup_id
The ID of the setup to check (is this setup the lightest one?)
@param $table_name
The name of the table associated with this setup

@return TRUE is this is the lightest priority; FALSE otherwise

tripal_views_integration_add_entry($defn_array)

Add views integration records into the tripal_views* tables

@param $defn_array
An array describing the structure and fields of the table

@return
True/False if completed successfully/not

Example usage (in hook_install()):

  $defn_array = array(
    'table' => 'feature', //tablename or materialized view name
    'name' => 'Sequence Features', // Human readable name
    'type' => 'chado', //either chado or mview depending on tablename
    'description' => 'Create a listing of features.', //description seen when creating a view of this type
    'priority' => 10, //For Base tripal modules: 10; custom modules: 9 to 0;
    'fields' => array(
      'feature_id' => array(
        'name' => 'feature_id', //field name in database
        'title' => 'Feature ID', //human-readable name -seen in Views UI
        'description' => 'This is the unique identifier for features', //help/description seen in Views UI
        'type' => 'int', // the type of field
        'handlers' => array(  //possible keys are field, filter, sort, argument, relationship
          'field' => array(
            'name' => 'chado_views_handler_numeric' //name of handler
          ),
          'filter' => array( ... ),
          ...
        ),
        'join' => array( //describe a table that joins to this one via this field
          'table' => 'featureprop', //table to join to
          'field' => 'feature_id', //field in above table (featureprop)
          'handler' => 'views_handler_join_chado_aggregator', //handler to use
        ),
      )
    ),
  );
  tripal_views_integration_add_entry($defn_array);

tripal_views_integration_remove_entry_by_table_name ($table_name, $priority)

Removes a View Integration Entry

@param $table_name
The name of the table to remove a views integration entry for
@param $priority
The priority of the of views integration entry

@return
TRUE on Success; FALSE otherwise

tripal_views_integration_remove_entry_by_setup_id ($setup_id)

Removes a View Integration Entry

@param $setup_id
The setup ID of the views integration entry to remove

tripal_views_get_integration_array_for_chado_table ($table_name)

Returns the array needed to integrate a given chado table with views

@param $tablename
The table to generate the tripal views integration array for
@return
The tripal views integration array which is the parameter for
tripal_views_integration_add_entry($defn_array)

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

This API allows a tripal module to integrate all chado tables associated with it via the following code (example is for the organism module):

  // Only add views integration setups if the tripal_views module is both present and enabled
  if (module_exists('tripal_views')) {
    // List of tables to integrate
    $tables = array(
      'organism',
      'organismprop',
      'organism_dbxref'
    );
    foreach ($tables as $tablename) {
      // only integrate if there isn't already an integration setup for this table with the default chado table priority
      // thus if someone edits the default integration and changes the priority, a clean default integration will be added
      // (a clean integration will only be added if the priority is changed and a users edits will never be overridden)
      if (!tripal_views_is_integrated($tablename, 10)) {
        // this is a 2 step process so the module can tweak the automatically generated definition array returned by
        // tripal_views_get_integration_array_for_chado_table() before it gets saved to the database.
        $table_integration_array = tripal_views_get_integration_array_for_chado_table($tablename);
        tripal_views_integration_add_entry($table_integration_array);
      }
    }
  }
laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Modules can also integrate their own custom tables using tripal_views_integration_add_entry($table_integration_array) by manually coding their own definition array. The following example is from the new ND Genotypes module which has not yet been released:
Table to be integrated:

CREATE TABLE nd_genotype_experiment (
                        stock_id integer,
                        stock_name varchar(255),
                        stock_experiment_relationship_type_id integer,
                        stock_experiment_relationship_type_name varchar(255),
                        genotype_id integer,
                        genotype_uniquename text,
                        genotype_description text,
                        feature_genotype_relationship_type_id integer,
                        feature_genotype_relationship_type_name varchar(255),
                        feature_id integer[],
                        feature_name varchar(255),
                        nd_experiment_id integer,
                        nd_geolocation_id integer,
                        nd_geolocation_description varchar(255),
                        project_id integer,
                        project_name varchar(255),
                        nd_genotype_experiment_id serial
);

Tripal Views Integration code:

if (module_exists('tripal_views')) {
  $defn_array = array(
    'table' => 'nd_genotype_experiment',
    'type' => 'mview',
    'name' => 'Natural Diversity Genotype Experiments',
    'description' => 'List genotype experiments.',
    'priority' => 9,
    'fields' => array(
      'stock_id' => array(
        'name' => 'stock_id',
        'title' => 'Stock ID',
        'type' => 'integer',
        'description' => 'the unique idenfier for stocks',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(
          'stock' => array(
            'table' => 'stock',
            'field' => 'stock_id',
          ),
        ),
      ),
      'stock_name' => array( 
        'name' => 'stock_name',
        'title' => 'Stock Name',
        'type' => 'varchar(255)',
        'description' => 'Human-readable stock name',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'stock_experiment_relationship_type_id' => array( 
        'name' => 'stock_experiment_relationship_type_id',
        'title' => 'Stock <=> Experiment Relationship Type ID',
        'description' => 'The type of relationship joining this stock to this genotype experiment.',
        'type' => 'integer',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'stock_experiment_relationship_type_name' => array( 
        'name' => 'stock_experiment_relationship_type_name',
        'title' => 'Stock <=> Experiment Relationship Type',
        'description' => 'The type of relationship joining this stock to this genotype experiment.',
        'type' => 'varchar(255)',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'genotype_id' => array( 
        'name' => 'genotype_id',
        'title' => 'Genotype ID',
        'description' => 'Unique IDs of the genotypes associated with this experiment',
        'type' => 'integer',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(
          'genotype' => array(
            'table' => 'genotype',
            'field' => 'genotype_id',
            'handler' => 'views_handler_join_chado_aggregtor',
          ),
        ),
      ),
      'genotype_uniquename' => array( 
        'name' => 'genotype_uniquename',
        'title' => 'Genotype Unique Name',
        'description' => 'Unique name of the genotypes associated with this experiment',
        'type' => 'text',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'genotype_description' => array( 
        'name' => 'genotype_description',
        'title' => 'Genotype Allele',
        'description' => 'The genotypes/alleles associated with this experiment',
        'type' => 'text',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'feature_genotype_relationship_type_id' => array( 
        'name' => 'feature_genotype_relationship_type_id',
        'title' => 'Feature <=> Genotype Relationship Type ID',
        'description' => 'The type of relationship this genotype has to this feature',
        'type' => 'integer',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'feature_genotype_relationship_type_name' => array( 
        'name' => 'feature_genotype_relationship_type_name',
        'title' => 'Feature <=> Genotype Relationship Type',
        'description' => 'The type of relationship this genotype has to this feature',
        'type' => 'varchar(255)',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'feature_id' => array( 
        'name' => 'feature_id',
        'title' => 'Feature ID',
        'description' => 'Unique ID for features associated with this genotype experiment.',
        'type' => 'integer',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(
          'feature' => array(
            'table' => 'feature',
            'field' => 'feature_id',
            'handler' => 'views_handler_join_chado_aggregtor',
          ),
        ),
      ),
      'feature_name' => array( 
        'name' => 'feature_name',
        'title' => 'Feature Name',
        'description' => 'Human-readable name of the features associated with this genotype experiment.',
        'type' => 'varchar(255)',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'nd_experiment_id' => array( 
        'name' => 'nd_experiment_id',
        'title' => 'ND Experiment ID',
        'description' => 'Unique ID for this genotype experiment.',
        'type' => 'integer',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(
          'nd_experiment' => array(
            'table' => 'nd_experiment',
            'field' => 'nd_experiment_id'
          ),
        ),
      ),
      'nd_geolocation_id' => array( 
        'name' => 'nd_geolocation_id',
        'title' => 'ND Geolocation ID',
        'description' => 'Unique ID for the location in which this experiment was done.',
        'type' => 'integer',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(
          'nd_geolocation' => array(
            'table' => 'nd_geolocation',
            'field' => 'nd_geolocation_id',
          ),
        ),
      ),
      'nd_geolocation_description' => array( 
        'name' => 'nd_geolocation_description',
        'title' => 'ND Geolocation Description',
        'description' => 'A short description of the location where this experiment was done',
        'type' => 'varchar(255)',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'project_id' => array( 
        'name' => 'project_id',
        'title' => 'Project ID',
        'description' => 'Unique ID of the project this experiment was part of',
        'type' => 'integer',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(
          'project' => array(
            'table' => 'project',
            'field' => 'project_id',
          ),
        ),
      ),
      'project_name' => array( 
        'name' => 'project_name',
        'title' => 'Project Name',
        'description' => 'Human-readable name of the project this experiment was part of',
        'type' => 'varchar(255)',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field'),
          'filter' => array('name' => 'chado_views_handler_filter_string'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
      'nd_genotype_experiment_id' => array( 
        'name' => 'nd_genotype_experiment_id',
        'title' => 'ND Genotype Experiment ID',
        'description' => 'Unique ID of the Genotype Experiment',
        'type' => 'serial',
        'handlers' => array(
          'field' => array('name' => 'chado_views_handler_field_numeric'),
          'filter' => array('name' => 'chado_views_handler_filter_numeric'),
          'sort' => array('name' => 'chado_views_handler_sort'),
        ),
        'joins' => array(),
      ),
    ),
  );
  tripal_views_integration_add_entry($defn_array);
}
laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Integration code can either be done when a module is enabled or is the hook_views_data() hook.
Things to keep in mind:
- When integrating when the module is enabled, the table will not be integrated if the tripal_views module is enabled after the module in question
- When integrating in the hook_views_data() make sure to include a check so you don't try to integrate the same table multiple times. This hook is executed often so the check may be made a lot and could have performance issues.

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented
Issue tags: +required for release

Added a new tag to differentiate which teasks must be completed for the next release of Tripal.

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Things yet to be done for this issue to be closed:
1) Upgrade of specialty handlers (ie: select list filter) to use chado_wrapper functionality -so they are safe to use with aggregated tables
2) Add specialty handler defaults to chado tables
3) Tripal Views Integration Import/Export
4) Full Testing of tripal views integration

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Checked views_handler_filter_chado_select_cvterm_name and set as the default handler for cvterm.name and *.type_id fields.

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented

Import/Export of Views Integration is done and in the 6.x-0.4-dev branch.

laceysanderson’s picture

laceysanderson CreditAttribution: laceysanderson commented
Status: Active » Closed (fixed)