Describe and use external database tables using Data.

The biggest hurdle here is that Drupal's schema API does not support external databases. Most painfully this becomes clear when trying to use drupal_write_record() on an external database.

Other than that, I think this is a good feature request.

I have attached a patch which helps to do this.

Schema can let you point to other databases which are defined in the settings.php file. Views can query these.

The missing part was that the base database was not being set in the data_views_data hook, with this patch it is now possible to create views from external databases. They can be on a different server to the main database.

Still not 100% with creating git patches so apologies if it doesn't work.

Patch needs minor tweaks to conform to Drupal coding standards.

Added and removed some spacing.

For the record, I applied this patch in my D7 installation just to see what happened. I don't think anyone intended that to work, but just to make it official - Nothing happened.

how do we know if the patch works? I applied the patch too and nothing happened.

I'm not too sure how it's meant to work either. As far as I can see, that patch is just meant to tell Views about it, and requires a (possibly obsolete) patch to Views.

Bumping the version. Fixing the component.

Yeah, I'm with joachim, I don't think this patch does much to tell data about external db's.

I started work on this. See the attached patch.

What doesn't work (for non-default databases):

- content type/entity stuff
- views stuff

Confirming that patch in #10 works to adopt tables -- although, I wasn't able to do the whole "view records" bit.

I'd really like Data to have access to my external tables in Views, so I might try my hand at that part of it.

thanks differunt $db_prefix['external_table'] = 'external_database.';

Maybe I'm missing something from my install, but trying the .patch from #10 gives me an error if I try to view the tables because function dd() is not defined. -- edit: also if I comment out dd it seems to be trying to reference the table from the 2nd database as if it was in the main database. I'm going to see if I can figure out where the display issues are coming from and if I can do some integration into Views on my test machine.

Ok, so continuing to work with this, I've editted the DataTable.inc ->adopt() so that it can adopt tables from either database, so the message in the adopt tables tab becomes redundant.

Although continuing with this will mean adding similar code for checking the table's origin database every time the data is called.

I'm considering whether it's better to move towards adding a field for it.

At any rate since I really want this as a feature I'm going to continue to work towards getting the views integration working. I've got Git going on this with my first commit being the base data module install, and then a commit right after applying the patch. If I get it to what I would consider a good working point for testing I'll put a .patch up here for everyone.

Any rate, if there is interest in this, post up and it may help keep me motivated to finish this.

Yes, do please post your changes as a patch!

So I've gotten all the DataTable.inc changes made. I need to do some rigorous testing on this to make sure I haven't made any code errors. Basically to be able to have 2nd database support you can't use the db_ functions. I tried wrapping them in a db_set_active, but it would appear that Drupal doesn't always do things in an obvious order so I was having troubles with it making database calls to the cache with the wrong database being set active and having the whole thing throw exceptions.

I need to finish cleaning up my code (lots of comment out this line here, comment out that there), but I think I'll be able to put up a patch in the next day or two.

It's probably a good point to do this anyways, as I've never really dealt with a hook system like this before, so I got to spend a few days getting my head wrapped around the Views API so I can see where I need to go with that.

I'm also interested in your patch, @veskit!

@veskit -- I'll give it a go as well! I have a project where this type of functionality might be helpful.

Ok I'll try cleaning up the code tonight and seeing if I can figure out how to make a .patch, never worked with Git before.

Just an update since I said I'd try to get it up in the next day or two, I've found a couple of bugs, one in particular with primary keys. I'm having a bit of trouble figuring out where exactly it's coming from even with line debugging, so I'm going to try a few things with the stock data module to see if I can track down where my differences are making this impact.

I am still working on this though.

Ok, so I'm not fully convinced the index and primary key functions when editting tables worked properly before in the admin_ui interface. It would seem there was no provisions taken into account if people wanted to remove indexes or primary keys (yeah I know who would want to? but you never know, maybe done by accident or something)....

I've fixed this by adding some code to allow for this, and I think it is very close to working properly (although one change I had to make to allow for this to work doesn't make much sense to me).

I am going to spend an hour on it tonight, hopefully allowing me to put up a patch either tonight or tomorrow morning.

My slow progress is the only time I have to dedicate to this is on my bus commute to and from work, which is about 2 hrs a day of very broken time (a few transfers, not always able to get a seat to open my laptop, etc).

I may end up participating in the Drupal weekend just to get some dedicated time on this. Especially since the code up to this point only gets the DataTable fully up to handle two databases. I really want it to have full views integration which will be the next major hurdle. (but I'll post the patch for 2nd database support in DataTable.inc and data_ui.admin.inc).

hi,

have you finished your work on this?

thanks

Can we get a status update on this? I could use it too. Trying to pull data out of an external database into tables.
Thanks!

There are some related issues which may need to be resolved first, particularly in light of #31.

- #1538588: Add Entity Label field (Was: allow entity ref fields to point to data items)
- #1671380: Data Entity allows entities to be created with entity ids that are not integers
- #1952884: Fix integration with Field UI

@veskit, do any of the patches in the above issues help?

Ah well, I thought I could get away with the patch because I am simply reading some tables from an external db, then graphing from there. I tried the patch and it didn't seem to help. I can see the tables with the Data module but Views has the wrong prefix.

Try out this one: https://drupal.org/project/feeds_sql. The only scary part about using this module is the fact that it has not been updated since 2011-12. But there is also this other module that is maintained and does what you need: https://drupal.org/project/views_xml_backend

Patch attached which allows views to make use of any tables adopted from an external database.

It adds an extra metadata item which is the database the tables were adopted from.

To use it, configure schema module to point to the database you need access to, then adopt the tables as usual. When the default views for the tables are created, they will automatically point to the correct database.

Trying this last patch. It works. I can see the view which was missing, but having a notice:

Notice: Undefined property: stdClass::$ID in data_entity_views_handler_field_edit_link->render() (line 28 of /home/robert/30mbru/sites/all/modules/data/data_entity/views/data_entity_views_handler_field_edit_link.inc).

Can also confirm the patch in comment #27 works. Have not seen the warning that Robert is getting, but I also reserved the view config settings in Data after applying the patch.

Maintainers, can this be rolled into the next release?

I tested patch in #27 as well, with the most recent -dev branch: works as advertised.

I'm a bit confused by the large differences between the patches here. One of them appears to have some sort of changes to the UI, but the latest one doesn't. Should there be a UI here?

Also, how is this feature meant to be used? Could some brief notes be added to the README?

@joachim the earlier patches appear to be work-in-progress patches that were never completed.

The patch in #27 takes a much simpler approach, as actually Views and Data both already support the use-case defined here, but the database connection wasn't being passed between the two (which made the Views integration fail). The patch simply passes on the additional details from Data module to Views.

To be honest, I would consider this issue a bug-fix rather than a feature request. Data module can be configured to use external databases and *should* be exposing those to Views, but it doesn't work correctly at present.

In terms of adding to README, no changes to the current configuration or set up of the Data module are needed, this just ensures that the existing set up works as intended.

> this just ensures that the existing set up works as intended.

So you mean that when you have multiple databases defined in your settings.php file, this patch allows them to be used with Data?

How does the UI work when you adopt tables, for instance?

@joachim apologies, I'm a bit fuzzy on the use case for this (I wrote the patch two years ago!)

I believe you just adopt the tables 'as normal', i.e. when using an external database you select it in Schema module config, and then use the Data Admin UI to adopt the required tables.

What the patch fixes is if you change the Schema configuration back to the primary database or to a different external database, Data module and Views will lose the tables because they don't remember which connection was used (they rely on Schema's current setting).

I'll try and rediscover the project I was using this for and update with any further details.

Would love to see some progress on this thread as it would likely solve my problem. I will close my ticket as duplicate of this one.
https://www.drupal.org/node/2522006

Not sure why the database connection would have to use the same credentials though. If I define multiple database connections in my settings.php file, it would be great if data would expose all those databases and allow adoption of tables from any database, not just the primary one.

Is this still an option.
How do I go about , to get Data to adobt tables from an external database on a separate server?

I can confirm the patch in #27 works for me.

To use it linda, you need to have the dev version of this module installed and patched with the patch above.

You then need to add the other database settings to your settings.php and the Schema module will then let you choose the other database connection in it's settings (/admin/structure/schema/settings). This then exposes all the tables available on that source to the Data module.

You can then go to /admin/structure/data/adopt and select the tables you need from the source.

Data then automatically creates a View of the newly adopted table.

One word of warning though, I needed to clear the cache between each and every stage of this process, so add source, clear cache, change Schema settings, clear cache, adopt table, clear cache, edit adopted table to add foreign keys, clear cache, go to Views list and see new sources in views.

#27 works for me too

#27 works great, worth committing!