As part of my Open Source Factory Monitoring setup I have a MySQL database that is populated by a node.js script. The display of the data will be in Drupal using Highcharts. (note that Highcharts is not open source and will require a license for commercial applications.) After evaluating several graphing packages I decided to use Highcharts because it is beautiful and works well for large data sets.
To access data in an external MySQL database Drupal needs two bits of information. The first is the location and login for the database and the second is the table structure. The database location and login is stored in the settings.php file in the same array as the Drupal database. The table structure is spelled out in a custom module.
settings.php
In the settings.php file find the section that defines the Drupal database and add the external database.
$databases = array (
'default' =>
array (
'default' =>
array (
'database' => 'default',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
'arduino' =>
array (
'default' =>
array (
'database' => 'arduino',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
);Custom Module
The custom module has three files: module, info and inc. Place the three files into a folder named mydatabase_views within the sites/all/modules folder in Drupal. When the module is enabled a new type of view will be available named "arduino". To create the view navigate to structure -> views -> add new view. Under the 'Show' field select "arduino" and create a new page.
mydatabase_views.info
name = "MyDatabase Views"
description = "Exposes ModBase Tables to Views."
dependencies[] = views
package = "MyDatabase Views"
core = 7.x
version = "7.x-1.0-dev"
project = "mydatabase_views"
files[] = mydatabase_views.module
files[] = mydatabase_views.views.incmydatabase_views.module
<?php
function mydatabase_views_views_api() {
return array(
'api' => 3,
);
}mydatabase_views.views.inc
<?php
/**
* Implementation of hook_views_data
*/
function mydatabase_views_views_data() {
error_log("Loading views");
$data['arTable']['table']['group'] = t('Arduino Data');
$data['arTable']['table']['base'] = array(
'field' => 'id',
'title' => t('arduino'),
'help' => t('Autoincremented ID field, to enable views integration'),
'database' => 'arduino',
'weight' => -10,
);
$data['arTable']['value'] = array(
'title' => t('Analog Input Value'),
'help' => t('Analog Input Value'),
'field' => array(
'handler' => 'views_handler_field',
' click sortable' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort',
),
'filter' => array(
'handler' => 'views_handler_filter_string',
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
);
$data['arTable']['serialNum'] = array(
'title' => t('Serial Number'),
'help' => t('Serial Number'),
'field' => array(
'handler' => 'views_handler_field',
' click sortable' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort',
),
'filter' => array(
'handler' => 'views_handler_filter_string',
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
);
$data['arTable']['date'] = array(
'title' => t('Date'),
'help' => t('Date'),
'field' => array(
'handler' => 'views_handler_field',
' click sortable' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort',
),
'filter' => array(
'handler' => 'date_views_filter_handler',
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
);
$data['arTable']['IP'] = array(
'title' => t('Senders IP Address'),
'help' => t('Senders IP Address'),
'field' => array(
'handler' => 'views_handler_field',
' click sortable' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort',
),
'filter' => array(
'handler' => 'views_handler_filter_string',
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
);
$data['arTable']['datetime'] = array(
'title' => t('Datetime'),
'help' => t('Datetime'),
'field' => array(
'handler' => 'views_handler_field',
' click sortable' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort',
),
'filter' => array(
'handler' => 'date_views_filter_handler',
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
);
return $data;
}