I would like to explore the possibility of adopting a view instead of an orphaned table. Basically, I am thinking that this would be analogous to adopting a table but making it read-only, which might be a very useful feature as well.

1. Does anyone else have any interest in this?
2. Can anyone point me in the right direction on this, where to look, what criteria might have to be satisfied to make it adoptable?

Thanks,
r.b.

Comments

Patricia_W’s picture

I was able to adopt a MySQL view but it does not appear in the list of Contents when I attempt to create a Drupal view.
However, the problem may be related to the lack of a primary key. When I attempted to add one to the adopted view it threw an error and did not save it. Probably this is because views do not have keys.

Patricia_W’s picture

In the Drupal 6 version someone had created a patch that allowed a primary key to be added to the Drupal data table but not to the MySQL view. I wish there was a Drupal 7 version.

robertwb’s picture

@Patricia_W - I think that you are correct that without a primary key Drupal may not allow you to use the data. As an alternative to using the data module, I have recently found that it is fairly trivial to tell Drupal about any table or view and allow Views to use it like any other piece of content.

If you are willing to do a very small amount of coding, I have recently found the documentation at http://views-help.doc.logrus.com/help/views/api-example, and I will include a small example here:

For my specific use case, I had a content type "MM Model Element" which specified a geographic area, that I wished to link to a table of data pertaining to some rainfall models for that geographic area. The "MM Model Element" was simulated in an environment outside Drupal which specified a unique primary key called "elementid" for each element. I wanted to enable myself to construct views around an element with pieces of data from simulations that were located in another table, joined on this "elementid". All I needed to do to enable this linkage was to tell Drupal about the relationship and the columns in the table by adding a small amount of data into an array in the "views_data" hook (my module is named "wsp_devel"):

function wsp_devel_views_data() {

   // ************************************************************
   // ***                     Fields to Add                    ***
   // ************************************************************
   // * defaults for numeric fields:
   $defaults_numeric = array(
      'title' => t('Numeric Field'),
      'help' => t('Numeric Field.'), // The help that appears on the UI,
      'argument' => array(
         'handler' => 'views_handler_argument_numeric',
      ),
      'field' => array(
         'handler' => 'views_handler_field_numeric',
         'click sortable' => TRUE,
      ),
      'filter' => array(
         'handler' => 'views_handler_filter_numeric',
      ),
      'sort' => array(
         'handler' => 'views_handler_sort',
      )
   );
   $defaults_string = array(
      'title' => t('String Field'),
      'help' => t('String Field.'), // The help that appears on the UI,
      'argument' => array(
         'handler' => 'views_handler_argument_string',
      ),
      'field' => array(
         'handler' => 'views_handler_field',
         'click sortable' => TRUE,
      ),
      'filter' => array(
         'handler' => 'views_handler_filter_string',
      ),
      'sort' => array(
         'handler' => 'views_handler_sort',
      )
   );

   
   $data['om_model_results']['table']['group'] = t('OM Data');
   $data['om_model_results']['table']['join']['node'] = array(
     'left_table' => 'field_data_field_mm_element_id',
     'left_field' => 'field_mm_element_id_value',
     'field' => 'elementid'
   );
   // * om_model_results - table of results to be joined to nodes representing model outlet points
   $data['om_model_results']['dataval'] = $defaults_numeric;
   $data['om_model_results']['dataval']['title'] = t('Value');
   $data['om_model_results']['dataval']['help'] = t('Simulation Data Value.');
   $data['om_model_results']['dataname'] = $defaults_string;
   $data['om_model_results']['dataname']['title'] = t('Data Name');
   $data['om_model_results']['dataname']['help'] = t('Simulation Data Name.');
   $data['om_model_results']['runid'] = $defaults_numeric;
   $data['om_model_results']['runid']['title'] = t('Run ID');
   $data['om_model_results']['runid']['help'] = t('Unique Simulation Run Identifier.');
   
  
   return $data;
}
Patricia_W’s picture

@robertwb
Before I opted to try the data view I had investigated using the views api but did not have the time to test it. Since I do not require that the data be accessed in real time, I decided to use the Feeds module which does what I need.
Thanks

agerson’s picture

Issue summary: View changes

+1 for this feature in D7

joel_guesclin’s picture

I would vote for allowing Data to adopt SQL Views despited the lack of a primary key. I have some very tricky reports to develop (with multiple subqueries) and it occurred to me that the Data module would be perfect: I can create my complex queries as SQL Views, then adopt them with Data and use them as ordinary tables in Views. Perfect!
Except that although you can adopt an SQL View, it won't show up in Drupal Views (apparently because no primary key) and of course you can't add a primary key to a SQL View.
So this would in my mind be a very useful feature.

jelo’s picture

Just adding another +1 for this feature. I had created another issue (https://www.drupal.org/node/2522006) to suggest the ability to adopt tables in other databases, e.g. if multiple databases are configured in Drupal's settings file. Right now data only reads from the default database.

My workaround was to create a view in database 1 on tables in database 2 and adopt the view. However, I then realized the limitation of missing a primary key which prohibited this from working.