Views Database Connector is a powerful module that gives Views full access to external database tables found in the settings for your Drupal installation. With this module, you can setup a view around any table in any database configuration. This can be extremely useful to pull external data from a database to show to your users in a view.
This module depends on access to the information_schema table when using MySQL or PostgreSQL. If using SQLite, access to the sqlite_master table is required. These tables are used to gather information about your tables and their respective column names and data types. If you cannot accommodate this requirement, this module will not work. Also, any table names that conflict with Drupal table names cannot be used, and any conflicting table names among external databases will also need to be resolved. These restrictions are in place because of the way that Views has structured the return value of its hook_views_data() API.
Upload this module's folder to your server and place it in the sites/all/modules folder. In order to make use of this module, add extra databases following the instructions found in the installation's sites/default/settings.php file. After you've added a database or two, enable the module. If you later decide to change your database configuration, it is advisable to clear cache and cycle the module's state between disabled and enabled.
When you add a new view, you should now be able to pick a new entry in the "Show" select menu to use as the view type. Each item created by this module will be prefixed by [VDC]. After you select one of these options and create your view, the first column in the table will be added as the first field. You should also be able to add the other columns as fields using the "Add" button.
https://drupal.org/sandbox/clayfreeman/2225261
git clone --branch 7.x-1.x http://git.drupal.org/sandbox/clayfreeman/2225261.git views_database_connector
Comments
Comment #1
clayfreemanComment #2
PA robot commentedWe are currently quite busy with all the project applications and we prefer projects with a review bonus. Please help reviewing and put yourself on the high priority list, then we will take a look at your project right away :-)
Also, you should get your friends, colleagues or other community members involved to review this application. Let them go through the review checklist and post a comment that sets this issue to "needs work" (they found some problems with the project) or "reviewed & tested by the community" (they found no major flaws).
I'm a robot and this is an automated message from Project Applications Scraper.
Comment #3
clayfreemanAdded review bonus.
Comment #4
madhusudanmca commentedHi clayfreeman,
Thanks for your contribution!!
After initial review I have following observations:
1) I simply installed this module and landed up with following errors on add view page:
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: mb_strtolower() expects parameter 1 to be string, array given in drupal_strtolower() (line 525 of D:\xampp\htdocs\drupal\includes\unicode.inc).
Warning: uasort(): Array was modified by the user comparison function in views_fetch_fields() (line 5230 of D:\xampp\htdocs\drupal\sites\all\modules\views\includes\admin.inc).
2) I simply aaded one more drupal DB in settings.php like:
$databases = array (
'default' =>
array (
'default' =>
array (
'database' => 'drupal',
'username' => 'root',
'password' => '',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
'custom' =>
array (
'default' =>
array (
'database' => 'kb',
'username' => 'root',
'password' => '',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
);
However I found some tables like "node", "users" are missing from drop down list.
3) After looking at code I have one small observation as you are using "information_schema" DB, you should add something on project page or in readme.txt that "This module requires DB user who has access to infromation_schema" DB.
4) At line #51 and #60, you are using DB name to select data from table, so you can't use curly bracket ({}) however it'll be good to at least use tick(`) like:
"SELECT table_name FROM `information_schema`.`tables` WHERE table_name"
Thanks
Comment #5
clayfreemanI've since updated the project to address some further bugs that I found when trying to use a different database driver, so now the project supports MySQL (and equivalent databases), SQLite, and PostgreSQL with equal capabilities.
1) In response to your error messages, this is something that I cannot replicate on any of my platforms, so it may be an XAMPP or Windows specific issue. If you could attach something like
echo var_export($varname, true)."<br />\n";before the line that the error message is referring to, replacing$varnamewith the variable in question, it may help to resolve the issue, or at least determine whether or not it's my module's fault.2) Referring to the missing node or users tables, unfortunately, you are unable to have any table names that have the same table name as any existing view types. If you do, it will override the previously defined view type and throw wild errors. For example, we had an external database with a table named "users," and it broke the default User view type. I mitigated this issue by simply renaming the external user table to "users1." Since then, I've added safeguards to prevent any table names that Drupal uses from being used, and also prevent already used table names.
3) In regards to an information_schema user notice, I've since addressed that by adding discrete functionality for each database driver.
4) Since tick marks aren't required in this case, I'd prefer not to have them. Tick marks are only necessary if the table or database name has any special characters other than alphanumeric and underscore. If you think that it is absolutely necessary though, tell me and I'll add them.
Please review the updated source code and reply back with any problems that exist. I appreciate your time and effort greatly. If you need access to a linux based hosting platform, or need a demo site, get with me in PM and I can provide you with credentials to my test suite.
Comment #6
madhusudanmca commentedHi clayfreeman,
1) This doesn't seems to be XAMPP or windows related issue, I can see all these messages on Linux as well.
Please see below drupal error logs for reference:
php 03/27/2014 - 10:35 Warning: uasort(): Array was modified by the user... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
php 03/27/2014 - 10:35 Warning: mb_strtolower() expects parameter 1 to be... madhu
2) I would suggest you to add this info on project pages that "the extrernal DB table names should not conflict with Drupal table names", to make it clear.
3) It's always good to tell users about this as we are using the DB which may or ma not be accessible by all DB users.
Thanks!!
Comment #7
clayfreeman1) You may or may not have a problematic Drupal instance. On a clean install of Drupal, with a direct copy of the current git tree for the module, and only the Views module, I am able to connect to all three database platform types. No server warnings or errors were reported in my Apache logs, or on-screen, during any part of the view setup. Here is a screenshot of the add view page http://dpr.clayfreeman.com/X4fg
2) I'll definitely do that!
3) I've since updated the description to include this information.
As previously mentioned, feel free to contact me through private channels, and I'll be happy to give you access to my server and databases. If you use IRC, my nickname on Freenode is "clayfreeman," and I can be found in a few of the Drupal channels such as #drupal-contribute and #drupal-support. Otherwise, feel free to shoot me an email using the contact form on my user page.
Also, if you could, please provide me with the structure of your database, and which platform you're using (MySQL, PostgreSQL, or SQLite). If you can provide sample data too, that'd be great. I'd love to test with nearly your exact setup if at all possible.
Comment #8
clayfreemanUpdating description as per madhusudanmca's suggestion.
Comment #9
clayfreemanLeft out the table name conflict warning on accident.
Comment #10
clayfreemanComment #11
lquessenberry commentedI am very much looking forward to a full release of this module. I have installed this on Ubuntu 12.10 and a Bitnami stack on my iMac at work and it has worked flawlessly on both systems. I even went as far as to install views_field_view and was able to create a view that incorporated data from three different database driver types. I tried MySQL, SQLite, and PostgresQL, and it was amazing. I used a contextual filter that was relative to the ID column in the three different databases and was able to display data related to the contextual filter from all three databases in one view.
This is the next level for views in my opinion. I look forward to a thoroughly tested and released version of this module.
Comment #12
lquessenberry commentedWoops. I meant to change this to a Reviewed and Tested status. Thanks again.
Comment #13
clayfreemanUpdated the issue summary to match the sandbox project page.
Comment #14
clayfreemanUpdating priority due to no attention for two weeks.
Comment #15
clayfreemanUpdating priority due to no attention for two additional weeks.
Comment #16
heddnRemove .gitignore file
Possible (although remote) possibility of XSS since
'title' => '[VDC] ' . $dname . ': ' . $table[0],isn't passed through any sanitization. Remote possibility because I'm fairly certain that no person in their right mind would ever do that. But still safer to pass throughcheck_plainComment #17
clayfreemanI've taken care of the XSS issue, and added .DS_Store to a global .gitignore so I could remove it from the repository.
Comment #18
clayfreemanForgot to switch status
Comment #19
heddnThanks for your prompt update. Probably should also pass the table name through check_plain as well.
Comment #20
clayfreemanFixed. Thanks for your prompt review as well :)
Comment #21
heddnMarking RTBC
Comment #22
mpdonadioClay, I'm not going to remove the RTBC status as I haven't done a formal review, but as module duplication and fragmentation are a problem on drupal.org. What differentiates this module from Data?
Comment #23
clayfreemanmpdonadio,
Thanks for your question. I hope I can address it appropriately.
This module provides a more lightweight approach to database integration with Views. There is no UI or configuration specifically for this module; only the databases must be configured in the settings file of Drupal. While the Data module is nice, it covers a wider range of features than this module, some which are unnecessary in most cases for people who just want simple functionality of being able to pull data from a database. From the few minutes I've had to look at the Data module, it appears that this module isn't geared towards fetching external data as much as it is geared towards creating and using data inside of Drupal, just in a separate table. I actually had some trouble trying to figure out how to implement similar functionality that is found in my module, and that's how I figured out that the Data module uses Drupal's database to create/manage/edit separate tables. While that's great when you want a data source powered by Drupal, VDC is geared towards entirely read-only access to existing external data sources. It's well documented, and extremely straight-forward to use, and I believe different enough to merit a separate module.
I know this might not be the best place to debate things like this, but while it may be true there are some overlapping features, I don't think it's necessarily bad to have multiple projects doing the same thing. It's also never bad to have alternatives to projects. I know that I've found a couple useful alternatives in my time using Drupal that the main or original project had problems doing. I understand that coalesced efforts prove to make a better project, but sometimes things shouldn't be merged. It also discourages people from trying to become a maintainer for an existing project since they have to jump even more hoops, and most likely need to have full git access for the current maintainers of a project to even consider them. That's just my two cents ;)
If you have any further questions, hit me up and I'll try to answer them accordingly.
Comment #24
klausiRemoving review bonus tag, you have not done any manual review, you just posted the output of an automated review tool. Make sure to read through the source code of the other projects, as requested on the review bonus page.
Comment #25
clayfreemanShouldn't it be necessary that a module pass through automated reviews before they should be given the time needed for a manual review? It just seems like it should be a required first step to me.
Comment #26
mpdonadioAutomated review is one facet of the application process. Errors and warnings aren't automatic reasons to go back to Needs Review. Sometimes, the results are false, and we aren't going to force people to go back and fix a space here and there. Yes, people should make sure that they do the automated review themselves as a first step, but not everyone reads the application checklist carefully.
Manual review is also a facet of the process. It is still valuable to give feedback so people can go through things at once, instead of having several rounds between Review and Work. In the time I have been reviewing, I don't think I had run into a project where poor automated review has prevented manual review.
Comment #27
kscheirerNo issues found, and the module looks sufficiently different from Data, thanks for answering that question. You mat want to include that answer on your project page.
Thanks for your contribution, clayfreeman!
I updated your account so you can promote this to a full project and also create new projects as either a sandbox or a "full" project.
Here are some recommended readings to help with excellent maintainership:
You can find lots more contributors chatting on IRC in #drupal-contribute. So, come hang out and stay involved!
Thanks, also, for your patience with the review process. Anyone is welcome to participate in the review process. Please consider reviewing other projects that are pending review. I encourage you to learn more about that process and join the group of reviewers.
Thanks to the dedicated reviewer(s) as well.
Comment #28
lquessenberry commentedThanks for getting this passed through review. This module is pretty amazing and a good alternative to Views JSON Query and a few others if you wanna go straight into querying an external dataset. Bravo!