Enable views to handle external tables properly, allowing for joins across tables in different mysql databases

Views does not currently support UNION statements, which is what you seem to be suggesting (two different selects, combined).

The solution to accessing tables from different databases relies on the database prefix which can include the database name (i.e. in form dbnam.prefix). See http://drupal.org/node/291373 for more details.

HTH
Terry
Recent Drupal sites:-
http://www.sportrelief.com http://www.intelflexplus.com http://www.aprilroseturner.com http://www.businessclubagent.ihg.com http://www.businessclub.ihg.com
http://www.university-bookseller.co.uk http://www.univarpcp.co.uk

I'm working on this in relation to the tw module (see #610128: Can't add external and internal tables' columns to the same view for more info), and I've gotten this to work. The premise as I understand it is that MySQL can handle accessing external tables from other databases, as long as you have the same credentials across both databases (same username and password). You access them by prepending the database name and a period before the external table. An example might be "externaldb.user" where externaldb is the actual name of the database (not the key for it in your settings.php file) and the user is the name of the table. You can experiment with it like I did in navicat or phpmyadmin to see that it does indeed work. I think as far as views is concerned, it's just one more table in the same database, even though mysql is reaching into the other database to do any joins, etc.

The problem that we ran into was that when views created the query with these tables, the aliases it was trying to make were invalid aliases in mysql (I guess you can't have a period in an alias), and caused the underlying query to fail. What i did was to make a function to clean the alias by replacing any periods with underscores, and it looks like it is finally working.

webchick was able to make this work as well by joining an external table to a internal base table, but to actually be able to use the external table as a base table this fix is required.

+function views_clean_alias($alias){
+  return  preg_replace('/\./', '_' , $alias); 
+}

Just a regular ol' str_replace() should be able to do the job here, and will be faster since it's not invoking the full regex engine.

NICE job tracking this down!!

yes, definitely. Thanks for opening up this possibility! It could be very powerful.

When I make a view from a foreign table from scratch and try to join other foreign fields, I get an error about a missing handler. Looking at the $view handler , I'm seeing the added field has the wrong table and field.

It should be
$table = external_db.external_table
$field = external_field

But it's showing up as:
$table = external_db
$field = external_table.external_field

I'm not certain this is views, but I'm pretty sure since the tw_views_data() output seems right.

Having a hard time tracking down where these values get set in the views code. Can anybody enlighten me?

RE: #12 I think i may have figured it out. I think it has to do with the way that manually added relationships are handled by the form. When I look at the "add field" form I see this code, where ext is the external db:

<div id="edit-name-ext.users.access-wrapper" class="form-item">
  <label class="option" for="edit-name-ext.users.access">
    <input id="edit-name-ext.users.access" class="form-checkbox" type="checkbox"   value="ext.users.access" name="name[ext.users.access]"/> d47_users: access
  </label>
  <div class="description">Timestamp for previous time user accessed the site.</div>
</div>

Non-external db fields look like this where d47_users_user_map is a local table:

<div id="edit-name-d47-users-user-map.mcsid-wrapper" class="form-item">
  <label class="option" for="edit-name-d47-users-user-map.mcsid">
    <input id="edit-name-d47-users-user-map.mcsid" class="form-checkbox" type="checkbox" value="d47_users_user_map.mcsid" name="name[d47_users_user_map.mcsid]"/>
d47_users_user_map: mcsid
  </label>
  <div class="description">d47_users_user_map.mcsid</div>
</div>

notice the extra period in the ext.table.field vs table.field. So I think the reason that this is getting messed up is that the string is slit at the first period, not the last. I'm still working to track that code down now, but any heads up would be a big help.

OK views_fetch_fields() in /includes/admin.inc makes this the big array that then gets turned into the big list of checkboxes for fields. I considered using my new function, views_clean_alias(), to clean it, but it looks like it may need to be the actual tablename not an alias, so now I'm going to see if we can just fix it on the other side where the "ext.users.access" gets submitted and parsed incorrectly.

yeah that was it. I had to figure out a way to only explode on the last period, not the first so i reversed the array, exploded it, then reversed the pieces. If someone has a better method, please enlighten.

function views_ui_add_item_form_submit($form, &$form_state) {
...
  // Reverse the string so we can split on the last period, not the first
   $rev_field = strrev($field);
      list($field, $table) = explode('.', $rev_field, 2);
      // Put the strings back in correct order.
      $table = strrev($table);
      $field = strrev($field);
      $id = $form_state['view']->add_item($form_state['display_id'], $type, $table, $field);
...
}

So I was able to add the external field to the view and have it work. I also see that the sql is formed properly with the aliases, so it looks like we're good for adding external tables manually to internal tables, but i still have to check the other permutations, and I'll provide the patch.

Automatic relationships required adding another views_clean_alias(), but it looks like all the external permutations are working for me now:

Joining External Tables to a Local Base Table
Joining Local Tables to a External Base Table
Joining External Tables to an External Base Table
Joining Local Tables to a Local Base Table (default views)

Also now working with "manual" and "automatic" tw relationships.

Note: This was patched against version 2.6.

My goodness, man. You are an absolute superstar! :D Thank you so much for your tireless efforts on this issue!

I have a small request. Would it be possible for you to post exports of the views you're using and/or any other requirements to reproduce the problem here while the problem is still fresh in your head? While Views doesn't have automated tests yet, capturing this kind of information is invaluable for adding some down the road so we make sure we don't ever break this again once it's fixed. This information would also make it much easier for patch reviewers (hopefully one of whom is Earl eventually :)) to get their heads around how to tell if the solution is optimal or not.

So a review of the underlying issue is that to make external views work, we have to name a table as "ext_db.table", not just "table". This played havoc with views because when it was creating aliases for the tables and fields, it was trying to create an alias with a period in it because the table had a period. I solved this by creating a new views function called views_clean_alias(), which i wrapped around any aliases created in /include/query.inc and turned any period into an underscore. So if there were to be an overall test, it would be to ensure that any alias that is in the sql query doesn't have any periods in it.

The second issue was the way the ui "add item" form handled tables with periods in them. (the form where you add a new fields or relationships in views and select checkboxes for fields you want to add) Here the submit function was using explode() to split the string into two parts at the period, but because the table name had a period in it, it split them incorrectly into "ext_db" and "table.field". I fixed this by reversing the string, exploding it, and then un-reversing the two parts. (Not sure if there just wasn't a way to do it with explode, but I don't think so)

The patch is pretty small considering the changes so it should be clear what i'm doing between this post and that, but if this isn't what you were looking for, just clarify for me what kind of data you mean. To reproduce it, you'd have to have your own external db, which could just be a copy of a drupal database (which is what i'm doing). You can then try to join the external tables, like users to nodes. You could write the views data in a custom module, but i think it's easier to just use the tw module. The latest tw dev includes a working patch to allow it to setup the views data and relationships on external tables.

Also, you should be able to mix and match external and internal tables like they were all living in your default database as long as you are following the mysql rules (see #610128: Can't add external and internal tables' columns to the same view) I'm not sure a bunch of wrappers over alias creations is optimal, but it's really just overcoming one bug, not any fundamental change of the underlying api. Views_data hooks are just like they always were, they just use ext_db.tablename instead of just tablename when talking about an external table.

Cheers!

+1 - works nicely with the very latest Table Wizard!

Ok, I'm convinced. The patch only applies to 2.x; I need a 3.x version as well (it should be similar but query.inc has been removed) then I'll commit.

That sounds like a "needs work".

I applied the patch to Views 2.8 without an error. Thanks to the patch I was able to join data from an external database. Great work and thanks for this patch!

Subscribing and will test the patch.... need this patch to be able to use Table Wizard with external database.

--Ben

Test results are great, but what we really need is someone to port this to Views 3.x so it can be committed in both places. :)

Working on this now. Hopefully have something today. @merlinofchaos any big changes in 3 that might effect this? you said query.inc doesn't exist anymore?

OK, got most of this done in an hour, but that last bug was a PITA.. more on that in a moment. First, let me get out this new tester module I made up to help others test this for views 3. TW module tries to include query.inc, so it doesn't work for views 3 yet (I already made a ticket).

So I exported some views_data and views_default definitions so that it's easier for someone to test this out quickly. All you have to do is create a copy of your drupal database (or at least the node, user, and node_revisions tables) to a new database called "ext" (physical name of db, not key in $db_url). It needs to either have the same user as the default database, or the default user needs select access to it. Then turn on the module. If you turn it on beforehand, it will check to make sure that an "ext" database exists, and if it doesn't it will basically give you these same instructions. If it finds it, it will give a message with a link to the default view. The user join is "automatic", and the node_revisions join is manual, so as to test both, but they are already setup in the view.

More to follow.

OK here is the PITA bug #686830: Manual relationships broken. It's its own issue, so I'll try to make sure that it doesn't make it into this patch, which is on it's way after I clean out all the dpm statements.

OK, here is the patch. Again, you need the other patch linked to in #29 for this to work. If you use the module in #28, it's easier to test this out. You could also use that module for views2 testing as well. And finally, remember that tw module doesn't work with views 3 yet.

cheers!

and of course remember to add a patch before you click save :P

+++ plugins/views_plugin_query_default.inc	16 Jan 2010 19:52:52 -0000
@@ -1214,3 +1214,17 @@ class views_plugin_query_default extends
+  dpm($alias);
+  dpm(debug_backtrace(false));

Some debug code left. I wonder why not to move this function into includes/view.inc, because i guess this would be needed by several query plugins.

Powered by Dreditor.

ok, removed dpms and moved function views_clean_alias() to views.inc

Cleaning up the title of this issue. Let me know if there is anything else I need to do here to get this committed. Thanks.

     $def = $this->definition;
     $def['table'] = $this->definition['base'];
     $def['field'] = $base_field;
-    $def['left_table'] = $this->table_alias;
+    $def['left_table'] = $this->table;
     $def['left_field'] = $this->field;
     if (!empty($this->options['required'])) {

I think this will break chaining relationships. For example, chain two nodereferences together -- I think the 2nd one will end up bound to the first node, not the second. If that makes sense.

(Example: track -> album -> artist)

subscribing.

subscribe

Subscribing

Has this effort stalled, or died?

Sub. So good to see the effort come this far. Thanks frankcarey.

Works for me, and in combination with the Data module you can easily mix tables from external non-Drupal databases in your views.

Subscribing

I use the patch wit Drupal 6, Views 2, and Table Wizard 2.1 and it works nice.

But it don't work when using the Summary options, in an Views Argument taken from a TW table.

This is the error shown

user warning: Unknown column 'personal.personal.dni' in 'field list' query: SELECT SUBSTR(personal_personal.firma_apellidos, 1, 1) AS firma_apellidos_truncated, COUNT(personal.personal.dni) AS num_records FROM personal.personal personal_personal GROUP BY firma_apellidos_truncated ORDER BY firma_apellidos_truncated ASC LIMIT 0, 50 in C:\xampp\htdocs\stage1\sites\all\modules\views\includes\view.inc on line 765.

It fails at the COUNT sentence because it has renamed personal.personal as personal_personal. The SUBSTR sentence is nicely build instead. I haven't found the bug origin yet. I will write here if I manage to repair it.

Thank you.

Please!!! don't hijack issues. With this behaviour you just get ignored

I'm sorry, I didn't want to be rude. I thought the description was about my comment.

I just apologize.

Any clue where is that SELECT SUBSTR () ... COUNT() query built?

I haven't found any SUBSTR query all through views, and the COUNT calls in Views build correctly the db table name with its alias.

I have no idea where to find for more. As I told before the warning arise when i do a summarized arguments Views in order to do an alphabetic index of my main view?

I guess it lacks a call to views_clean_alias, added in the patch, but i can't find where to do it.

Thank you.

So the patch in #33 still hasn't been committed? Is anyone working on cleaning up the issue Merlinofchaos indicated in #35? I do have at least one instance of a double reference as described, so I'm leery of applying the patch, but I'm getting these annoying errors all over my site, and it's been suggested that this patch might help:

warning: class_exists() expects parameter 1 to be string, array given in /usr/local/web/lamp/users/wsbe/site/sites/all/modules/views/includes/handlers.inc on line 17.

No the patch wasn't commited yet. The reason is the "needs work" state and you review :)

+1

Subscribe

Many complains about this:
http://drupal.org/node/617142
http://drupal.org/node/884228
Would be great to commit
thanks

Patched marked 'needs work' need someone to work on them before they can be committed. Are you volunteering?

+1

hi

i think this patch is just amazing. patch for view 2 is pretty old... does any one know to make this work with views 2.12.

thank you

hi

where can i find the patch for views 2.12

Okay you didn't tryed out the patch. This is nice! So http://drupal.org/node/576694#comment-2523946

Holding up the patch for 2.x because the patch for 3.x isn't ready yet seems counterproductive. Now the patch for 2.x doesn't apply cleanly:

patch -p0<views_handle_external_table_aliases_w_str_replace_2.patch
patching file includes/admin.inc
Hunk #1 FAILED at 2217.
1 out of 1 hunk FAILED -- saving rejects to file includes/admin.inc.rej
patching file includes/query.inc
Hunk #5 succeeded at 307 (offset 17 lines).
Hunk #7 succeeded at 640 (offset 17 lines).
Hunk #8 succeeded at 974 (offset 4 lines).
Hunk #9 succeeded at 1010 (offset 17 lines).

Could we get the patch for 2.x applied and split the 3.x patch into a new issue?

No, and at this point, 2.x is not likely to actually get this as I'm moving toward only fixing critical issues in 2.x so we can deprecate it in favor of 3.x.

Full development effort has been on 3.x for over a year. There is no excuse for developing only for Views 2.x at this time.

So in short, the patch for 2.x should have been committed a year ago. And now it's too late. Excellent!

I asked for a 3.x patch on Jan 4, 2010. It's not my fault that development work on this stalled. Complaining won't gain you anything.

Oh and then some random person also changed the version of the issue, making it even worse. Awesome.

Subscribe

subscribe

Got plenty of Views 2 situations that could benefit from the #34 patch (kudos, frankcarey!) -- if only anybody clever enough figured out a fix for the problem brought up in #35. Subscribing...

subscribe

sub

+1

subscribe

I have renewed my search for the ability to use external tables from a secondary connection in D7 using Views 3, the data module and Schema. This looks like the place, is there a D7 summary of this issue thus far, or a better thread to follow?

Has anyone tried out the above? I need the same functionality, to query the external db using views. How do I get the tables to show up using the data module?

Stop subscribing, start following. The big green "Follow" button in the upper right corner. :)

What is the status here?
Can someone update the issue summary, please?

I'm wondering if Views Database Connector solved the problem that progressed this issue over the years.

@itpromanager: Views database connector basically use the db_set_active, which will not
help if we want to join across multi database,