We used Drupal with Oracle Database and it all started off with the requirement to create a few pages of charts & graphs based on a few tables available in an oracle database.

Why we used Drupal with Oracle DB?

We did have the option of rendering the infographics after manually querying the database, but then, who can resist the charm of Drupal’s Views module? And off we went, on a quest to marry Views’ queries with database tables in Oracle. As it turned out, it is possible!

Maximizing Value Via Continuous Discovery (PDF)

How we used Drupal with Oracle DB

The Views api allows you to direct all queries fired at a particular table to use a different database connection.

$data[<table name>]['table']['base']['database'] = '<alternate db connection>';

It was now only a matter of getting our custom tables registered with views and could go about implementing hook_views_data(), and list out each and every column of every table, but then again, Drupalers are (in)famous for the “theres a module for that” attitude. I am no exception, so in came data and schema and we were merrily on our way to doing the fun stuff.

The first task was to have a common set of filters across graphs. This would not be possible using contextual filters, and we had to rely on the regular filters to do the work by specifying common identifiers in all the views. An undesired side-effect of this was that we would not have ajax refreshing graphs, resulting in page reloads, and thus a degraded user experience.
Our exposed filters were dropdowns. Drupal’s views module allows only static values in the list of entries, but we wanted the list to be populated based on the unique entries of that db  column, So we wrote a small views plugin that would allow us to do the same.

<?php

/**
 * @file
 * data_handler_filter_column_distinct_values.inc
 */

class data_handler_filter_column_distinct_values extends views_handler_filter_string {

  /**
   * Option form customizations.
   *
   * @param array $form
   *   The form for setting option values.
   * @param array $form_state
   *   The current state of the submitted form.
   */
  public function options_form(&$form, &$form_state) {
    parent::options_form($form, $form_state);

    if ($this->is_a_group()) {
      unset($form['group_info']['default_group_multiple']);
      unset($form['group_info']['default_group']);
      unset($form['group_info']['group_items']);
      unset($form['group_info']['add_group']);
      unset($form_state['js settings']['tableDrag']);
    }
  }

  /**
   * Group form customizations.
   *
   * @param array $form
   *   The form for setting option values.
   * @param array $form_state
   *   The current state of the submitted form.
   */
  public function group_form(&$form, &$form_state) {
    parent::group_form($form, $form_state);

    $form[$this->options['group_info']['identifier']]['#options'] += $this->get_distinct_column_values();
  }

  /**
   * This has to be compulsorily true.
   *
   * @param object $input
   *   Unused parameter - required due to inheritance.
   * @param null $selected_group_id
   *   Unused parameter - required due to inheritance.
   *
   * @return bool
   *   True
   */
  public function convert_exposed_input(&$input, $selected_group_id = NULL) {
    return TRUE;
  }

  /**
   * Returns the list of distinct values from the table column.
   *
   * @return array
   *   The list of values.
   */
  private function get_distinct_column_values() {

    $query = 'SELECT DISTINCT ' . $this->options['field'] .
      ' FROM {' . $this->options['table'] . '} t' .
      ' ORDER BY ' . $this->options['field'] . ' ASC';
    $results = db_query($query);
    $options = array();
    foreach ($results as $row) {
      $value = current($row);
      $options[$value] = $value;
    }
    return $options;
  }
}

The Views Date Filter handler also had to be overridden with some custom code. As it turns out, the default date filter handler assumes that all dates would be integers, but we were dealing with date columns in the db and hence the methods generating the sql query bit had to be overridden.

<?php

/**
 * @file
 * data_handler_filter_datetime_values.inc
 */
class data_handler_filter_datetime_values extends views_handler_filter_date {

  /**
   * Override method for sql date comparisons.
   *
   * @param string $field
   *   The field being compared.
   */
  public function op_between($field) {
    $where_clause = "$field BETWEEN DATE '{$this->value['min']}' AND DATE '{$this->value['max']}'";
    $this->query->add_where_expression($this->options['group'], $where_clause);
  }

  /**
   * Customizing the exposed form to add datepickers.
   *
   * @param array $form
   *   The form for setting option values.
   * @param array $form_state
   *   The current state of the submitted form.
   */
  public function value_form(&$form, &$form_state) {
    parent::value_form($form, $form_state);

    if (isset($form['value']['min']) and isset($form['value']['max'])) {

      $form['value']['min']['#attributes']['class'][] = 'oversight-datepicker';
      $form['value']['max']['#attributes']['class'][] = 'oversight-datepicker';

      drupal_add_library('system', 'ui.datepicker');
      drupal_add_js("jQuery('.oversight-datepicker').datepicker({ dateFormat: 'yy-mm-dd' });", array(
        'type' => 'inline',
        'scope' => 'footer',
      ));
    }
  }
}

Another Views plugin that was required was in order to display integers (total values) in the short scale format. Figures like 5M are much simpler to grasp than 5,000,000 (or worse, 5000000).

<?php
/**
 * @file
 * data_handler_field_numeric_formatted.inc
 */

class data_handler_field_numeric_formatted extends views_handler_field_numeric {

  /**
   * Adding custom option for short scale formatting.
   *
   * @return array
   *   The option definition.
   */
  public function option_definition() {
    $options = parent::option_definition();

    $options['format_short_scale'] = array('default' => FALSE, 'bool' => TRUE);
    return $options;
  }

  /**
   * The form for setting short scale formatting.
   *
   * @param array $form
   *   The form for setting option values.
   * @param array $form_state
   *   The current state of the submitted form.
   */
  public function options_form(&$form, &$form_state) {
    parent::options_form($form, $form_state);

    $form['format_short_scale'] = array(
      '#type' => 'checkbox',
      '#title' => t('Display in Short Scale'),
      '#description' => t('If checked, the number will be displayed in short scale.'),
      '#default_value' => $this->options['format_short_scale'],
    );
  }

  /**
   * If set, the logic to render the short scale formatting.
   *
   * @param object $values
   *   The values to be rendered.
   *
   * @return string
   *   The rendered html.
   */
  public function render($values) {
    module_load_include('module', 'short_scale_formatter', 'short_scale_formatter');
    $value = parent::render($values);
    if (empty($this->options['format_short_scale'])) {
      return $value;
    }
    $value = str_replace($this->sanitize_value($this->options['prefix'], 'xss'), NULL, $value);
    $value = str_replace($this->sanitize_value($this->options['suffix'], 'xss'), NULL, $value);

    $suffixes = array('K', 'M', 'B', 'T');
    $value = short_scale_formatter_format_number($value, $suffixes, 0);

    return $this->sanitize_value($this->options['prefix'], 'xss')
    . $this->sanitize_value($value)
    . $this->sanitize_value($this->options['suffix'], 'xss');
  }
}

The nitty-gritty of using Drupal with Oracle

In addition to the reports generated by Views, there were some more where the queries were complex and hence had to be manually queried and the results rendered.

We used the oci8 module to query Oracle. Our installation was still in MySQL and as such we didn’t need the Oracle driver. The API method exposed by the module worked beautifully, save a small patch that we contributed back.

We now needed to ensure that all the Views queries would get executed in Oracle and we just had the db connection in the views API mentioned above. We did not require a full Oracle driver; just to reroute the queries through the oci8 module. This was accomplished by writing a dummy driver that would in turn, invoke the API methods exposed by the oci8 module. We also had a couple of string_replace’s to ensure that the queries generated by views were Oracle compliant, and we were good to go.

<?php

/**
 * Created by PhpStorm.
 */
class DatabaseConnection_oci extends OCI8DatabaseConnection {

  public function query($query, array $args = array(), $options = array()) {
    $query = str_replace('\\', '\', $query);
    return oci8_db_query($query, $args, $options);
  }
}

Here’s how a couple of graphs turned out.

Sample Graph Using Drupal With Oracle Database

Sample Graph Using Drupal With Oracle Database

Sample Graph Using Drupal With Oracle Database

Sample Graph Using Drupal With Oracle Database

Relevant links:

Join Axelerant