9

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.

2

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.

  • Thank you for this. I didn't notice when I first went through Setup that there were five permissions that were not granted to anyone. I now granted them to Administrator (i.e., me). The Setup doc doesn't say why those permissions are orginally withheld and whether there is a reason not to grant them to Admin. However, I now have those data sources, so this has solved that problem. Is there a reason you responded here instead of in the issue I submitted? – NewSites Apr 26 '15 at 18:27
  • Great to read the progress! It somehow compensates the "downvote" of my answer ... FYI: I also responded via the issue queue now. So now that "this has solved the problem", does that mean that you consider Forena indeed as "the" answer to your question (which it was not yet as in bullet nr 3 of the OP)? FYI: I'll try to update my answer here to briefly explain the "why those permissions are originally withheld", which is a good extra question! – Pierre.Vriens Apr 26 '15 at 18:41
  • I wasn't the one who down-voted your answer. I have now accepted yours as <u>the</u> answer and up-voted it (which unfortunately only returns it to zero). – NewSites Apr 26 '15 at 19:21
  • Hm, I wasn't assuming @NewSites to have downvoted, actually I've learned to live with downvotes. I personally do not like downvoting in general, and I ignore any of them where there is no comment to somehow explain them. But of course "upvoting", and "accepting" is greatly appreciated. And of course knowing there is yet another Drupal user who seems to have discovered Forena now, way to go! Make sure to file (support?) issues where appropriate (to also see the support at work ...). – Pierre.Vriens Apr 26 '15 at 19:46
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.

  • You say in both methods that I need to implement <code>hook_views_data()</code>. Doesn't that require writing a module? In any case, I tried the database definition in your second method. It didn't cause the table in "db_extra" to appear in the Data module's list of orphaned tables. In your first method, where do I put the query you suggested (if not in a custom module)? – NewSites Apr 26 '15 at 17:00
  • If you're creating a SQL View, you need to run the CREATE VIEW query once in MySQL directly. It doesn't need to be re-run. – Les Lim Apr 26 '15 at 21:33
  • I'm sorry, I still don't understand. Where do you want me to run this query? In phpMyAdmin? Will that give me something I can use in Views? I searched for "SQL view" on Drupal.org and got drupal.org/project/sql_views , which has no production download and whose doc at drupal.org/node/2458947 says I need to implement a hook (which I presume means writing a module) to tell the Views module about my SQL view. So it sounds like what you're suggesting requires writing a module (which I just don't feel ready for), and if not, I just don't get what you're telling me to do. – NewSites Apr 26 '15 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.