Can't add external and internal tables' columns to the same view

4. A restriction in Views?

My memory's a little rusty, but it seems I investigated this scenario and although modules can expose tables in external tables to Views, and the migrate module is able to dynamically modify a view to go across databases, I don't think the views_ui module supports creating a view combining tables across databases.

Needs a little more research... And maybe a solid patch for the Views queue...

I think that cross DB joins are a natively supported in mysql but require the dblink add-on to postgres. might be a reason why views doesn't support it. see http://stackoverflow.com/questions/46324/possible-to-perform-cross-datab...

IMO, we should make this mysql only and strongly suggest that admins specify use a single db connection. That way, TW can stop specifying the database key and instead use database.tablename approach. Thats my 10 second evaluation of the issue. Mike would know better.

Note that it is still possible to specify in mysql that the drupal mysql credentials only has read access to the legacy DB. Thats a good idea for safety puposes.

Ahh, my memory has been jogged - yes, it's the Views db_set_active() that stopped me in my tracks. Migrate uses the database.tablename hack for external tables itself - see migrate_content_process_import() lines 598-608.

I don't like the idea of creating tables in the external database on general principle. That DB could be a production DB for the "old" site, it could be readonly as Moshe suggests. Also, while doing it would ease this use case, it would break approaching from the other side (views joining destination data with the map and message tables). And, at this stage in development I don't want to break things for users of migrate (especially myself) - building views with the map table, or using it in prepare hooks to access releated data, is common.

I don't think we need to be MySQL only - it is reasonable to state that external databases are not supported with Postgres, though.

interested parties are invited to check the http://drupal.org/project/xdb cross database views project.

So when I did my migrate last time, (Mar/ Apr) external DB functionality wasn't there yet. I had to dump the legacy database (drupal 4.7), load it into a temporary database, change the prefix on the tables, dump it again, then import the tables into the new database. This made my backups huge, with like 20K users and 40K pieces of content, and the process to update the legacy tables with fresh data time consuming. So this time i want to try and use the external database feature. Since I join the map tables to legacy tables for importing comments, files, etc. , it doesn't look like i will be able to do that currently if the legacy tables are in their own DB? What I would prefer to do is just to share credentials with a copy of the legacy database on my local machine for setting up the content sets, as is described above, but can i currently do that either, or are we investigating that now??

OK reading comments again more thoroughly web chicks comment:

So since my previous approach to pulling in external data is apparently held together by rubber bands and old shoelaces, I'm wondering if the best thing for me to do is a hook_views_data_alter() for my own purposes that removes the 'database' key and prefixes the external tables accordingly, rather than this becoming "core" Table Wizard functionality, esp. if Table Wizard is trying to get this to work with PostgreSQL.

So the way forward then is to remove table wizards setting of "database" key and instead set the table keys to [database.table] ? And then just give a warning about externals not working on postgres?

OK, i'll see what I can come up with. Seems like the first hurdle is getting the actual database name from the db key ('default'). MySQL at least has the ability to "SELECT DATABASE;" so I'm working on a function to get the external name efficiently, and hopefully cache it.

OK, this little function looks like its working well. It will cache the results so only one hit per database.

function helper_get_dbname($dbconnection) {
  static $real_db_name = array();
  if (!isset($real_db_name[$dbconnection])) {
    db_set_active($dbconnection);
    dpm('db hit');
    $real_db_name[$dbconnection] = db_result(db_query('SELECT DATABASE()'));
    db_set_active('default');
  }
  dpm($real_db_name[$dbconnection]); 
  return $real_db_name[$dbconnection];
}  

OK, I've gotten this mostly working now (replacing external tables with extdb.table). But I'm getting no rows outputting.

Here is the sql from the view preview.

SELECT matadortravel_live2_copy.node.nid AS nid,
   matadortravel_live2_copy.node.vid AS matadortravel_live2_copy.node_vid,
   matadortravel_live2_copy.node.type AS matadortravel_live2_copy.node_type,
   matadortravel_live2_copy.node.title AS matadortravel_live2_copy.node_title,
   matadortravel_live2_copy.node.uid AS matadortravel_live2_copy.node_uid,
   matadortravel_live2_copy.node.status AS matadortravel_live2_copy.node_status,
   matadortravel_live2_copy.node.created AS matadortravel_live2_copy.node_created,
   matadortravel_live2_copy.node.changed AS matadortravel_live2_copy.node_changed,
   matadortravel_live2_copy.node.comment AS matadortravel_live2_copy.node_comment,
   matadortravel_live2_copy.node.promote AS matadortravel_live2_copy.node_promote,
   matadortravel_live2_copy.node.moderate AS matadortravel_live2_copy.node_moderate,
   matadortravel_live2_copy.node.sticky AS matadortravel_live2_copy.node_sticky
 FROM matadortravel_live2_copy.node matadortravel_live2_copy.node 

Firing the sql manually in navicat gives me errors related to matadortravel_live2_copy.node_* aliases. When I run this version without them, it works.

SELECT matadortravel_live2_copy.node.nid AS nid,
   matadortravel_live2_copy.node.vid AS node_vid,
   matadortravel_live2_copy.node.type AS node_type,
   matadortravel_live2_copy.node.title AS node_title,
   matadortravel_live2_copy.node.uid AS node_uid,
   matadortravel_live2_copy.node.status AS node_status,
   matadortravel_live2_copy.node.created AS node_created,
   matadortravel_live2_copy.node.changed AS node_changed,
   matadortravel_live2_copy.node.comment AS node_comment,
   matadortravel_live2_copy.node.promote AS node_promote,
   matadortravel_live2_copy.node.moderate AS node_moderate,
   matadortravel_live2_copy.node.sticky AS node_sticky
 FROM matadortravel_live2_copy.node

@webchick, yeah i saw pretty much that exact thing in the code just now in tw.views_default.inc Probably safer and cheaper to do it that way.

 global $db_url;
 $url = parse_url($db_url[$dbconnection ]);
 $truedbname = substr($url['path'], 1);

might also get db type while we're at it as well, since this won't fly in postgres.

Thinking i may have to set table name and to get aliases to work better?

This was in webchick's code but i don't see us using it in tw_tablebuild.inc

$data['ext']['table']['name'] = 'external_users.users';

From http://views-help.doc.logrus.com/help/views/api-tables

The key should be the actual database name of the table (not including prefix), but it can be an alias as long as the join information (explained later) contains the real name of the table.

I can't find any reference to a $data['mytable']['name'] = real name in the views2 documentation.

this doesn't seem to be working for me. Here is the new sql output. my external db is has the key 'd47_' and the actual db name is 'matadortravel_live2_copy'

SELECT d47__node.nid AS nid,
   d47__node.vid AS d47__node_vid,
   d47__node.type AS d47__node_type,
   d47__node.title AS d47__node_title,
   d47__node.uid AS d47__node_uid,
   d47__node.status AS d47__node_status,
   d47__node.created AS d47__node_created,
   d47__node.changed AS d47__node_changed,
   d47__node.comment AS d47__node_comment,
   d47__node.promote AS d47__node_promote,
   d47__node.moderate AS d47__node_moderate,
   d47__node.sticky AS d47__node_sticky
 FROM d47__node d47__node 

Maybe we can just patch views to create better aliases when it runs into a table with a period ... if it just replaces the period with an underscore in the alias, we should be ok. There is no other reason for a period in a table name anyway right?

i'm stumped for now. A few things differ from the example hook_views_data in #6 and what i think i'm trying to do now (treat all external tables like they were internal) . One thing is that the example code doesn't seem to have that ext table set as a base table, so what i *think* is happening in that the view is that it is querying the core user table as the base table and then getting access to the fields from the other database through the relationships that are created.

I'm not sure why the aliases wouldn't still be messed up in this case, but maybe it is using the core table to make a field alias something like "user_fname" and not "external_users.users_fname", which seems to be a no-no.

Any help in the right direction appreciated.

So there are 2 alias issues immediately effecting the sql query, and I'm looking in views/includes/query.inc for answers.

1) field aliases - for some reason, the base_field in the views_query object doesn't get an alias (a non-issue), but all the others do.
Here it is in the code:

 // We check for this specifically because it gets a special alias.
    615     if ($table == $this->base_table && $field == $this->base_field && empty($alias)) {
    616       $alias = $this->base_field;
    617     }

It seems possible to set the field aliases, or maybe just run table through a function that cleans any periods in $table

 function add_field($table, $field, $alias = '', $params = NULL) {
    614     // We check for this specifically because it gets a special alias.
    615     if ($table == $this->base_table && $field == $this->base_field && empty($alias)) {
    616       $alias = $this->base_field;
    617     }
    618 
    619     if ($table && empty($this->table_queue[$table])) {
    620       $this->ensure_table($table);
    621     }
    622 
    623     if (!$alias && $table) {
    624       $alias = $table . '_' . $field;
    625     }

2) table aliases - the FROM [table] [table_alias] seems to come from this code:

on line 971: $query = "SELECT $fields\n FROM {" . $this->base_table . "} $this->base_table \n$joins $where $groupby $having $orderby";

as you can see, $this->base_table is used not only for the base table, but for the alias as well, which seems odd to me, and seems impossible to overcome without patching views.

OK, now i'm really stopping. I hope all this was helpful.

I was using hook_views_query_alter() to debug this, and force some changes in the query object to the point where i could get the query to run, but the only field that showed up was the base_field, and all the others in the view were blank. This might be because the default view definitions are messed up?

function helper_views_query_alter(&$view, &$query) {
  
  dpm($query);
  
  //clean the table_queue
   foreach($query->table_queue as $name => $queue) {
    $query->table_queue[$name]['alias'] = helper_clean_alias($queue['alias']);
  }
  
  // clean the tables 
  foreach($query->tables as $tablename => $table) {
    $query->tables[$tablename][$tablename]['alias'] = helper_clean_alias($table[$tablename]['alias']);
  }
   //clean the relationships
   foreach($query->relationships as $name => $relationship) {
    $query->relationships[$name]['alias'] = helper_clean_alias($relationship['alias']);
  }
  //clean the fields
   foreach($query->fields as $fieldname => $field) {
    $query->fields[$fieldname]['alias'] = helper_clean_alias($field['alias']);
  }
  //this changes both the FROM table and the alias! 
  //$query->base_table = helper_clean_alias($query->base_table);
  dpm($query);

}  


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

"obsessions make my life worse and my work better" - sagmiester

I figured I'd give this one more shot by just hacking views to see if it would work, ....and it does!

I attached the views patch here #576694: Enable views to handle external tables properly, allowing for joins across tables in different mysql databases

here is the patch for tw ... it probably needs work, but I'm able to see those external tables fine. I haven't tried any joins/relationships yet.

Thanks for working this out! I don't have time to thoroughly test it tonight (and this is something that will need thorough testing), but will soon. In the meantime, I'd like to point out that this will break Postgres. How about only prepending the dbname when it's not the default connection?

I may be rushing, but I've committed a variation of Frank's patch, tested in the absence of the related Views patch. This version should work just as today for Postgres, or for MySQL installations which do not have the Views patch. Next step is to test this with the Views patch...

Not quite there - with the Views patch present, I get AJAX errors trying to add a field (if the tw relationship is automatic) or relationship (if the tw relationship is manual). This gets into an area that's difficult to debug...

I'm having issues around joining (can't find table?). I've added code for the compatibility logic, but i'm not getting any ajax errors. I should be able to get these relationships debugged today.

Still trying to figure out where joins are going wrong, but here are the two new functions I'm using to check compatibility and get database name.

/**
 * Check if the external database and the default database are compatible
 * enough to share tables.
 *
 * @param $dbkey
 *   The non-default database key set in the settings.php file.
 * @return
 *   TRUE if they are compatible, FALSE if they are not.
 */
function tw_check_dbcompat($dbkey) {
  static $checked_compats = array();
  
  //if we have already checked this, just return it.
  if (isset($checked_compats[$dbkey])) {
    return $checked_compats[$dbkey];
  }
  
  //this is a new check
  $externaldb = tw_get_dbinfo($dbkey);
  $internaldb = tw_get_dbinfo('default');
  
  //they must have the same scheme, username, password, host?
  $checks = array('scheme', 'username', 'password', 'host');
  //only works with mysql, so let's check that right away.
  $compatible = ($externaldb['scheme'] == 'mysql' || $externaldb['scheme'] == 'mysqli') ;
  $i = 0;
  
  while($compatible === TRUE && $i < 4) {
    $compatible = ($externaldb[$checks[$i]] == $internaldb[$checks[$i]]);
    $i++;
  }
  $checked_compats[$dbkey] = $compatible;
  return $compatible;
}

/**
 * Get the database properties from it's dbconnection key in $db_url, 
 * and cache it.
 *
 * @param $dbconnection
 *   The database key set in the settings.php file ('default').
 * @param $property
 *   Optional - The individual property you want returned.
 * @return
 *   If property was given, then the property value. Otherwise the whole
 *   array of properties plus 'name'.
 */
function tw_get_dbinfo($dbkey, $property = NULL) {
  //cache the parsed db properties.
  static $db_url_parsed = array();
  if (!isset($db_url_parsed[$dbkey])) {
    global $db_url;
    $db_url_parsed[$dbkey] = parse_url($db_url[$dbkey]);
    $db_url_parsed[$dbkey]['name'] = substr($db_url_parsed[$dbkey]['path'], 1);
  }
  // if a preporty was requested, then return just that property.
  if(isset($property)) {
    return $db_url_parsed[$dbkey][$property];
  }
  //else just return the whole array.
  return $db_url_parsed[$dbkey];  
}

It turned out my ajax problem was this: #625262: Ajax Error. Creating a cross-db view still doesn't work, though, so I'll leave it to Frank for the moment...

The generated views seem to be right according to the api. In trying to narrow down the variables,I created a new view of an external table (comments) which i can see fine and tried to join another external table to it. I set a singe relationship (ext_comment.cid = ext_node.cid) to automatic and then try to add a field (body) from the joined node table and i get an error about a broken/missing handler. I didn't have any time for this today, but i may have an hour or so in the morning. Does anyone know a good way to output the table and default views definitions so I can paste them here for review? I've been using dpm() statements, but I doubt a cut and paste would be very pretty. maybe a ctools fn?

I'll also post an updated patch tomorrow of where i'm at so maybe more eyes on this can get us moving forward again.

here is my current output from _tw_generate_views_relationship_data() in tw_tablebuild.inc (using ctools_var_export() to get this output)

http://drupalbin.com/12316

Simple automatic relationship between (ext)comment.nid and (ext)node.nid, where both tables are external.

if I add the relationship manually (turn off automatic) and try to add the relationship in the view, i can check the d47_node relationship, and add it, but I get the "Error: handler for matadortravel_live2_copy > comments.nid doesn't exist!" error immediately, and can't go any further to say, add a field from that relationship.

http://drupalbin.com/12317

I tracked down function views_get_handler($table, $field, $key) {} as the source of the error message

a dpm() on the variables sheds some light on the issue:

$table = matadortravel_live2_copy

$field = comments.nid

It SHOULD look like

$table = matadortravel_live2_copy.comments

$field = nid

so, i'm tracking down further now.

having a really tough time debugging these classes. I can't seem to find where/how $table, $field, $key are being generated. maybe moshe or mike can enlighten how this might happen?

note: if I add a new view based on the external.comments table (don't use the tw default view) right now when i even add a field, i get the same error. I'm going to have to revert things back to a clean state i think and start fresh with the debugging (still keeping in mind what i found out above), but not tonight.

OK got this working on the views side. Needed more aliases for relationships, and had to change the way the ui handled adding relationships and fields. Check the new patch at #576694: Enable views to handle external tables properly, allowing for joins across tables in different mysql databases

I don't *think* I changed anything in the tw patch, but here is a current patch against an older dev (Nov 8th?). I'll upgrade to newest and then try applying it.

oops, here is the patch. (again, this might be no different than the previous patch, and I think mike said he already applied to to dev?

looking at latest dev now and it's diff to what I have in the patch. They differ a bit, but it looks like the external to internal part is still working with the new views patch. A few things about our two approaches.

1) I like the use of tw_qualified_tablename instead of tw_check_dbcompat since it pulls the logic into it's own function and I think it's cleaner. but I think it needs more of the guts that were in tw_check_dbcompat () (see #3)

2) I like tw_get_dbinfo() over my previous function, tw_get_dbname(). I think my second attempt is more useful, allowing you to get and cache all of the db information which comes in handy in #3.

3) It looks like tw_qualified_tablename() is only checking that the two databases are mysql(i). The way I understand it (and i may be wrong because I couldn't find any documentation on this over at mysql.com) is that the default user has to have the same credentials as well, and has to be on the same host. (username, pw, and host all have to be equal between databases). tw_get_dbinfo() will give you back an array with all this information to use for comparing. My comparison code is in tw_check_dbcompat().

function tw_qualified_tablename($dbconnection, $tablename) {
  global $db_type;
  // MySQL is the one support db known to handle this
  if ($db_type == 'mysqli' || $db_type == 'mysql') {
    // And we also need the Views patch at **************
    // to pull this off
    views_include('query');
    if (function_exists('views_clean_alias')) {
      return tw_get_dbname($dbconnection) . '.' . $tablename;
    }
    else {
      return $tablename;
    }
  }
  else {
    return $tablename;
  }
}

So, I suggest that we move the tw_check_dbcompat() logic into tw_qualified_tablename() and replace tw_get_dbname() with tw_get_dbinfo(). Thoughts?

Off the top of my head, that sounds good. Unfortunately, I'm heading off to vacation and my #1 priority before going is #601656: Update migrated content in-place - I won't have a chance to give this a good shaking out before December. Make the best patch you can against the current dev patch, and I'll review it then.

Thanks for all the work you've put into this!

This patch adds in tw_get_dbinfo(), slightly modified, but leaves in tw_get_dbname() for now. I'll post another patch to remove it and change it's calls to tw_get_dbinfo() once this one gets into dev.

I'm, also adding a function called tw_inspect_schema() that has the dual purpose of reusing the $inspect = schema_invoke('inspect'); logic of switching databases before running the inspection and then returning to default, but also checking first if the table has a period in it, and if it does, to replace the db and table name so that schema can properly get info about the table (not going to find an external table in the default db schema.)

This is used by the migrate module patch that adds external db functionality, which i'm just abut to submit, but i thought it should probably find a home here for now , and maybe work its way toward the schema module eventually( see TODO comment added to function). Still more to do on #47, but this is a good step forward, and critical to being able to use external databases with migrate module.

The migrate patch that requires this one to work is here #661934: Allow migrate to handle external tables.

OK, I've committed what I think are the last missing pieces of this (integrating Frank's last two patches in this issue with the current CVS, plus a couple other tweaks). It could use more testing, so by all means please bang away on it!

Question: Has the above patch (and the related patch for the Migrate module) been applied to the Dec 19 and 20 development releases?

Or, do I need to apply the patches to my modules?

I'm having issues importing external tables (although they are within the Drupal database)...
Running DRUPAL 6 on a Win 2008 R2 server under XAMPP.

I can see the data under TW, but I cant import it with MIGRATE due to a reported MYSQL error where it has issues reading the table column fields. (It reads them as stated earlier in the TW module)
ERROR:
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group AS job_data_sub-group, job_data.program_type AS job_data_program_type, ' at line 4 query: SELECT job_data.job_num AS job_num, job_data.cust_name AS job_data_cust_name, job_data.industry AS job_data_industry, job_data.sub-group AS job_data_sub-group, job_data.program_type AS job_data_program_type, job_data.prime_tech AS job_data_prime_tech, job_data.seconf_tech AS job_data_seconf_tech, job_data.job_deliverable AS job_data_job_deliverable, job_data.job_description AS job_data_job_description, job_data.quote_num AS job_data_quote_num, job_data.cust_program AS job_data_cust_program, job_data.project_leader AS job_data_project_leader, job_data.mechanical_leader AS job_data_mechanical_leader, job_data.controls_leader AS job_data_controls_leader, job_data.process_leader AS job_data_process_leader, job_data.est_cost AS job_data_est_cost, job_data.job_compl_cost AS job_data_job_compl_cost, job_data.sell_price AS job_data_sell_price, job_data.est_marg AS job_data_est_marg, job_data.final_marg AS job_data_final_marg, job_data.post_mortom_notes AS job_data_post_mortom_notes, job_data.sales_notes AS job_data_sales_notes, job_data.part_image AS job_data_part_image, job_data.cell_image AS job_data_cell_image, job_data.tooling_image AS job_data_tooling_image, job_data.id AS job_data_id, job_data.date_added AS job_data_date_added, job_data.date_modified AS job_data_date_modified FROM job_data job_data LIMIT 0, 1 in E:\xampplite\htdocs\drupal\sites\all\modules\views\includes\view.inc on line 765

Your thoughts on this are most welcome!

The migrate patch needs still to be applied to the migrate module, but i think this tw patch is in the newest devs (I haven't tested them yet). To clarify an "external" table is one that lives in a database different from the drupal one. I think you may have some issues around using a hyphen instead of an underscore for the "sub-group" field name.

Thanks for the tip.
I'll look into the hyphen / underscore issue. That is great trail to follow…

Now I just have to search for how to patch on a windows based xampp system.... I haven’t needed to patch anything up until this point. Just another new area to learn and add to my growing repertoire of experiences with Drupal.

The hyphen that frankcarey pointed out in my table heading was of course an issue. Correcting that typo allowed the migrate function continue on, but it didn’t import data. I can see all the data with the Table Wizard. But I can’t import it. Now I need to figure out how to apply the patch and see if that is whats holding me back. Are there any really good documents that you are aware of on patching Drupal on a windows based xamp system? I need to get moving on my data migration and table project...

you can just patch it manually. Make sure you have a fresh install of the latest migrate dev, then look at the migrate.module file and add or remove the lines from the patch file manually (+ means to add and - means to delete that line) The patch is small so you should be able to easily do it. If you have any more questions about migrate you should make an issue there, not in the tw module. here is the link to the patch. http://drupal.org/files/issues/migrate_external_table_schema_0.patch

I have not been able to connect to and external drupal database and pull in the users information. I cant figure out how to tell views that the table is "users".

$data = array();
  // Define the base group of this table. Fields that don't
  // have a group defined will go into this field by default.
  $data['staff_directory']['table']['group']  = t('Staff Directory');
  
  $data['staff_directory']['table']['base'] = array(
    'field' => 'uid',
    'title' => t('Staff Directory'),
    'help' => t('Users who have created accounts on Staff Web.'),
    'database' => 'staff_directory',
  );

any help is greatly appreciated