How to make an external database available to Views?

Question

There's a lot of discussion here and in other places about using external databases in Drupal. What I've figured out from that is:

• The best way to work with data in a database table is to use the Views module. I've installed that and started using it and found it's great for working with a table that I've added to the site's database.
• The easy way to get Views to work with a table that was not created by Drupal is with the Data module. This module treats any table in the site database that is not in use by Drupal as an "orphan table" and allows it to be "adopted" at the click of a button.
• The module "Table Wizard" apparently provides a way to do that with tables that are not in the site's database. But that module is not available for Drupal 7.
• The "settings.php" file, in the section on "Database settings," has detailed documentation on how to define additional databases that the site can work with. But when a database is defined in that way, its tables do not show up in the list of orphan tables in the Data module.

The change that I've made to the database definition in the “settings.php” file of one of my sites is:

$databases = array ( 'default' => array ('default' => array ( 'database' => 'db_local', 'username' => 'db_local', 'password' => '_________', 'host' => 'localhost', 'port' => '', 'driver' => 'mysql', 'prefix' => '', ), ), 'extra' => array ('default' => array ( 'database' => 'db__extra', 'username' => 'db_admin', 'password' => '_________, 'host' => 'localhost', 'port' => '', 'driver' => 'mysql', 'prefix' => '', ), ), );

I have a table that I want to use in more than one site in my multi-site installation. This means I need to have the table in a database that is accessible to each site. According to the reading I've done, my options for doing that are:

1. Best, if it's possible: Find out that there's a way to get the Data module to recognize tables in the additional database defined in "settings.php", so it can adopt them.
2. Use table prefixes to allow all the sites to use one database.
3. The module “Forena Reports” seemed to do exactly what I need, but when I installed it, I couldn’t get it to work. I submitted issue 2475645 and can’t do much more there unless there’s a fix for that problem.
4. Write my own module to give Views access to the additional database defined in "settings.php".
5. Install Drupal 6 with Table Wizard and use that to get Views to see my external table, and then somehow take the result of that and put it into Drupal 7. (This has been suggested in some posts.)
6. Follow the procedure in https://drupal.stackexchange.com/a/3321/45991 , which starts with installing a patch and seems to also require creating my own module.
7. I’ve also looked at the modules Feeds, Feeds SQL, Views XML Backend, and Migrate, but none of them say they can connect an external db to Views. Am I missing something? Is one of them the right solution?

Option 2 is not practical because it would result in a huge database with hundreds of tables that would be very difficult to manage. I’ve been making great progress in getting started on Drupal, but I don’t feel up to the task of 4, 5, or 6. Is there any possibility of something like option 1 working? Or is there any other way to get Views to work with a table outside the site database without being able to write my own module?

Two additional details: The first table I need to work with is pretty large, over 6,000 rows and about 20 columns. There is no intention to give visitors to the site access to the database except for information drawn from it for page content.

Thanks for your help.

Answer 1

I think the Forena module does indeed exactly what you need. Your setup issue is only a support request I think, not something that requires a "fix". After you get that resolved, you will even have a choice to yes or no use its views integration also.

The Forena issue you created is very well documented (great work!). But nowhere it says anything about permissions you granted (which makes me believe you might be missing some of the required permissions). So here is an attempt to address that:

• Can you briefly explain which of the (required) Forena related permissions you granted?

• The required Forena permissions are explained in the Setup Guide that comes with Forena? FYI: here is a quote of (part of) it:

  Forena allows for a lot of granularity as it comes to reporting related authorizations, by using the standard Drupal administration facilities to grant permissions to roles. The list of Forena related Drupal permissions that can be granted may be a bit overwhelming at first. Because there are 3 entries for each defined data source, and vanilla Forena already comes with 3 data sources.

  To retrieve this guide in your own site, just navigate to /reports/help.setup

Those permissions to access the "data" are originally withheld. That's by design, because Forena is also pretty sensitive to anything that relates to granting access to data via Forena. What you wouldn't want to happen is that by simply installing and configuring Forena (without permissions admin), you can use it as a workaround to access secured data.

Also note that apart from permissions at the level of an entire data source, you have an option to further refine required permissions for specific data blocks (= custom SQL queries). To do so, just specify such required (Drupal) permissions within the SQL statement (syntax details are included in the Forena guides). A typical usecase for this, is to secure access to columns with sensitive data in it.

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

Answer 2

I've done this successfully a couple of different ways:

Use a SQL View to create a virtual table in db_local that refers to the corresponding table in db__extra. The query to create that would look something like this:

USE db_local; CREATE VIEW some_table AS SELECT * FROM db__extra.some_table;

You can then refer to this SQL View as if it were a native table in your Drupal database, and you don't even need to add the external database at all in settings.php. Note that this might not be compatible with Data module (see https://www.drupal.org/node/1973806), but you should be able to describe the SQL View to the Drupal Views module by implementing hook_views_data(), and stop using Data module.

Here's another possibility: use selective table prefixes to trick Drupal into rewriting {some_table} to db__extra.some_table. This is not the same as prefixing your Drupal tables.

Your $databases setting would look like this:

$databases = array ( 'default' => array ('default' => array ( 'database' => 'db_local', 'username' => 'db_local', 'password' => '_________', 'host' => 'localhost', 'port' => '', 'driver' => 'mysql', 'prefix' => array( 'some_table' => 'db__extra.', ), )) );

Again, I'm not sure if this is compatible with Data module, since I don't use it. But it should work with Views if you implement hook_views_data() yourself.