Custom external read-only databases with Drupal, how to use views to communicate with the external database?

Question

I have a pretty detailed relational database that I would like Drupal to communicate with (Read Only). Now, I know that I can create custom modules to output the data but I am not sure I will be leveraging Drupal's benefits using in that fashion (it seems like a hack). I do not plan on moving the data to Drupal either (too large in size and also more than 80 tables).

Is there a way that I could use views to get the data out from my external database without interfering with the Drupal core? I am sure that Drupal developers run into this issue many times, I would like to have a pointer as far as where to look. Is that possible? has anyone ever done this?

I have already used Google in an attempt to find a solution. I even tried the entity module, entity api, data module a few other API and modules without success.

Just need a workflow/pointer or simply a successful website that is using Drupal in that fashion.

I am using Drupal 7 and I will be willing to go to Drupal 8.

Answer 1

Yes there is a way to use views to get data out of an external database. You will need the hooks_view_data()

There is however the limitation the view can not cross databases I believe.

To solve this, you can write a custom module

<?php

function yourmoduleame_views_views_api() {
   return array(
      'api' => 3,
   );
}


function  yourmodulename_views_views_data() {
   $data['arTable']['table']['group'] = t('yourtable Data');
   $data['arTable']['table']['base'] = array(
      'field' => 'id',
      'title' => t('your title'),
      'help' => t('help text'),
      'database' => 'databasename',
      'weight' => -10,
   );

Where databasename is one of your db_names defined in settings.php.

For more information, look at this post

Answer 2

Maybe the Forena module is what you're looking for? It is built of the idea of using SQL to get data out of a database (which can be the Drupal database or an external one such as My SQL, Oracle, MS SQL, ...) and use XHTML and CSS to format it into web reports. There are quite some reasons for considering Forena. Visit the Forena HowTos page for some links (near the bottom) to some live demo / showcase links. Refer to its community documentation for a lot more details.

To answer this specific answer, I'd use the views integration of it. Here are some details about that integration, included in the Forena documentation that gets installed with the module also (and shown in the demo site):

Using the Forena integration with the Views module, it is possible to use Forena reports as a views row style, so that in a view you can use the Forena templating engine to do the formatting. Although this can be done through the views templating engine Forena users may find they have more control with this engine.

Disclosure: I'm a co-maintainer of this module,
I hope this does not violate the site's policy on self-promotion.