Use Views to list data from another database namespace

Changing status to send patch to testbot.

65 exceptions are not so bad, given that function definitions changed.
263 fails means there is no painless way to introduce it. If your patch is meant to be evolution (minor version bump) not revolution (7.x-4.x bump), it shouldn't render any existing code unusavle.

Most common error is "Base table or view not found" - it means you broken views for pretty much anyone NOT using your new parameters. Please make them optional, and make views work as they were if not provided.

views-sql-namespace.patch queued for re-testing.

Requesting re-test to make sure it's not a temporary MySQL glitch in testbot.

Consider generating one against 3.x too. I bet it'll make it easier for maintainers to integrate it into new version.
And I really prefer PostgreSQL and db2 over MySQL, so, well, I'll be glad to help you if I can.

Remember to always set status to "needs review" when submitting new patch file. Only then testbots will care about automatic review of your patch.

And when submitting 7.x-3.x patch, be sure to set issue branch to 7.x-3.x too (yea I know, obvious, but it's less frustrating to tell things everybody knows than to be surprised).

You have patch in patch you know. It makes system go crazy. Done that myself too ;)

Here is how I make my patches after trial and error:
1) checkout fresh project version from GIT
2) manually apply my changes (or semi manually - by hand cutting out meaningless changes from temporary patch file)
3) commit
4) make a patch between commits

At least this works for me and allows me to create patches without white character changes and alike.
And it helps to include 3 ~ 5 lines of context. You are not doing it, it seems, so things like @@ -1614,3 +1643,7 may get inserted anywhere between blank line and } if patcher thinks it's reasonably close to line numbers you provided...

Fixing status I accidentaly messed up.
Patch needs to be fixed, so it is "needs work"

It is not obvious what your use case is to begin with. I assume you want to fetch data from another database namespace, most likely out of some legacy system? Could you give more details about what this data is and how you want to use it?

Views doesn't support namespaces at all, as you already noted. But (from memory) it does support multiple database connections, so you might be able to use that instead.

Simplest use-case is to use namespaces in place of db_prefix. Drupal already recognizes there is a need for multiple data sets in one database and provides crippled support of it. Why not to use full support the true-SQL provides?

My use case is - I need one data set for purely CMS data, and other to index machine parts in a way that'll make it accessible both by CMS and by software in repair shop, sales department and so on. And I see no reason to make separate databases out of them, but I will want to join CMS tables with machine parts ones. Classic example of separation by namespaces. There is a tool for that. Drupal was written for MySQL but since Drupal 7 it is going in the direction of other, more advanced RDBMS. And Views in D8 core proves that Views may pretty much cripple that trend.

I agree that other way around it would be to make each namespace different db connection in core, but it would mess the ability to make cross-namespace joins. Default namespace in settings.php and namespace support in drupal's core would be great, but even then significant changes would be needed in Views to use it. Changes quite similar to OP's idea presented here, so why not implement them and then go improving core? It'll give us useable something fast at least. Code seems pretty much ready, except for patch generating skills far from perfect.

Move bug to a separate issue

you can't access a table in an external database with a name that is the same as one that already provided

It's a bug, not a feature. I strongly suggest creating new issue for it for further discussion.
Use case - well, contrib modules does not know about each other and they might collide easily.

@Mołot: You are mistaken, MySQL has namespaces, or more precisely what it calls Database is actually a Schema. What MySQL doesn't have is the concept of Database (in the sense of PostgreSQL and Oracle - and what SQL Server calls Instance).

And Drupal itself does support namespaces properly. The database prefix can contain a dot (".") to indicate a namespace separator.

@Damien Tournoud - MySQL have 2 step naming, PostgreSQL, Oracle and so on 3 step. That's what matters here. Sorry if my lack of formality was misleading or seemed lazy. What given segment actually is, conceptually, according to ISO/IEC 9075:2011 simply seemed unimportant to me in this very case.

And I never got Drupal to properly join between namespaces, so either I did something wrong or Drupal is quite far from "proper support". Or, at least, from "full support" of namespaces.

@machinehum - be so kind and post link when (if) you'll move bug part to separate issue. I know managing 2 patches in the same code area may be hard, but I really think that table name collisions are bug that requires fixing.

I tried this patch on my own Drupal installation and it worked perfectly!

My System:
Drupal 7.24
Views 7.x-3.x (pulled from git on 7Dec2013)
PostgreSQL 9.1.5

Setup: I have Drupal in the public schema of my PostgreSQL database and other tables in a "chado" schema. I added a very simple views integration definition to my module_views_data function for a single table and it's primary key. Then I made a simple view with the unformatted style for that table with the only field being the primary key.

Result of patch: Where before I experienced an error and inability to load the table, once I applied the patch, I was able to select from the table through views error free!

IMPORTANT NOTE (for others attempting to use this patch): To use this functionality you need to add the following to your table definition:
$data['example_table']['table']['base']['search_path'] = 'my_schema_name';

The previous patch only worked for integration of views base tables. I've added an extra check that allows it to work for non-base tables as well. I didn't change any of the underlying logic -just added an extra check.

Now, you can set the search path for base tables (previous patch included in this one):
$data['example_table']['table']['base']['search_path'] = 'my_schema_name';
And non-base tables such as linking tables, etc. (new to this patch):
$data['example_table']['table']['search_path'] = 'my_schema_name';

I would like to assist in getting @laceysanderson's patch incorporated into Views. Could a Drupal core contributor comment on what would need to be done to make this happen?

For those using this patch, it is incomplete because molot requested it be split into two issues in #16. This patch alone will only work if your tables in separate namespaces are all named differently; you also need 1972880 to be able to work with tables in different namespaces having the same name.

For convenience, I'm including a patch that merges the latest passing versions of the individual patches from both issues.

Just an update on this, the patch in #24 breaks the functionality I had working in #22. I am looking into why this is the case and working on a patch from my end.
Thanks @mbaynton for bringing up the other issue!

Furthermore, the above patch does not apply cleanly to the newest 7.x-3.x-dev (96a54e041b171eb47c6a00805b9c4731c81d9283) and testing seems to be postponed due to another test failure on the views branch Testbot Report for Views 7.x-3.x

The patch in #1972880-2: You can't access a table in an external database with a name that is the same as one that already provided also does not apply cleanly to the current Views 7.x-3.x-dev. Since the table collision is only a problem if you have two tables (one in each integrated database) both with Drupal Views integration, I don't think we should combine the patches at this time. Furthermore, there does not seem to be a consensus on that issue for the best way to solve such collisions. They were separated out into different issues for a reason and applying two patches instead of one seems like less work then maintaining a merged patch as well as individual patches.

I'm going to retest the patch in #22 to confirm that all is well on the newest views 7.x-3.x dev version.

The patch in #22 turns $right_table into a string, which breaks relationships like views_handler_relationship_node_term_data which use a subquery as a right table.

The attached patch avoids interfering with other modules that aren't using search_path by only prepending the search_path if search_path is defined.

I tested the patch in #30 and it works for my purposes :-)

Patch #30 not working with views 7.x-3.20, it cannot be applied.

BTW, the patch #30 works with views 7.x-3.18

Hi @jungle, we had an updated version of this patch contributed by our Tripal Community by https://github.com/abretaud. I've attached it here for you :-)

Wow, Lacey, Thank you and Tripal!

Especially, Thank you, @laceysanderson, very much for your contributions to the Tripal project.

Patch #34 Works for me.