<?php
// $Id: tw.module,v 1.1.2.53 2010-03-20 22:03:21 mikeryan Exp $

/**
 * @file
 * Table Wizard - implementations of API functions and hooks.
 */

//////////////////////////////////////////////////////////////////////////////
// Public API
//

/**
 * Add tables to be managed by Table Wizard
 *
 * @param $tablelist
 *  The name of a table or an array of table names. If a period is present, the form
 *  connection.tablename is assumed (where connection is an alternate database in $db_url).
 *  The tablename must be the logical, not physical, name of the table. That is, if you have
 *  a table prefix of pf_ defined, pass in 'node' instead of 'pf_node'.
 * @param $skip_full_analysis
 *  Indicates that data statistics (mins and maxes) should not be collected, saving
 *  substantial time for large tables
 */
function tw_add_tables($tablelist, $skip_full_analysis = FALSE, $provide_view = TRUE, $view_name = '') {
  if (!is_array($tablelist)) {
    $tablelist = array($tablelist);
  }
  foreach ($tablelist as $tablename) {
    // Split out the connection
    $split = explode('.', $tablename);
    if (isset($split[1])) {
      $dbconnection = $split[0];
      $tablename = $split[1];
    }
    else {
      $dbconnection = 'default';
    }

    // Don't try to make sense of a view name with multiples
    if (count($tablelist) > 1) {
      $view_name = '';
    }
    // With one table, default view_name to tablename if not explicit
    else if ($provide_view && !$view_name) {
      $view_name = $tablename;
    }

    // See if we're already managing this table
    $sql = "SELECT twtid FROM {tw_tables}
            WHERE dbconnection='%s' AND tablename='%s'";
    $twtid = db_result(db_query($sql, $dbconnection, $tablename));
    if (!$twtid) {
      if ($view_name) {
        // See if there's an existing view with this name, defined by someone else - we
        // don't want to overwrite it
        $views_data = views_fetch_data($view_name);
        if ($views_data) {
          $provide_view = FALSE;
          drupal_set_message(t('Table Wizard cannot define a default view %view_name - a view
            with that name is already defined.',
            array('%view_name' => $view_name)));
        }
      }
      else {
        $view_name = $tablename;
      }
      db_query("INSERT INTO {tw_tables}
                (dbconnection, tablename, provide_view, view_name)
                VALUES('%s', '%s', %d, '%s')",
                $dbconnection, $tablename, $provide_view, $view_name);
    }

    // Analysis populates the tw_columns table
    tw_perform_analysis($tablename, $dbconnection, $skip_full_analysis);
  }

  // The views cache needs to be flushed to make new tables and default views available.
  views_invalidate_cache();

  // And also rebuild menus, so links to the default view work
  menu_rebuild();
}

/**
 * Add tables to be managed by Table Wizard, using Batch API to break each table into
 * a separate request.
 *
 * @param $tablelist
 *  The name of a table or an array of table names. If a period is present, the form
 *  connection.tablename is assumed (where connection is an alternate database in $db_url).
 *  The tablename must be the logical, not physical, name of the table. That is, if you
 *  have a table prefix of pf_ defined, pass in 'node' instead of 'pf_node'.
 * @param $skip_full_analysis
 *  Indicates that data statistics (mins and maxes) should not be collected, saving
 *  substantial time for large tables
 * @param $provide_view
 *  TRUE if a default view with the same name as the table itself should be provided
 */
function tw_add_tables_batch($tablelist, $skip_full_analysis = FALSE, $provide_view = TRUE) {
  if (!is_array($tablelist)) {
    $tablelist = array($tablelist);
  }

  $batch = array('operations' => array(),
    'title' => t('Processing tables'),
    'init_message' => t('Starting analysis of tables.'),
    'progress_message' => t('Analyzed @current out of @total tables.'),
    'error_message' => t('An error occurred. Some or all of the analysis has failed.'),
  );
  foreach ($tablelist as $tablename) {
    $batch['operations'][] = array('tw_add_tables', array($tablename, $skip_full_analysis, $provide_view, ''));
  }

  batch_set($batch);
}

/**
 * Remove tables from the Table Wizard
 *
 * @param $tablelist The name of a table or an array of table names. If a period is present,
 *                   the form connection.tablename is assumed (where connection is an
 *                   alternate database in $db_url). The tablename must be the logical,
 *                   not physical, name of the table. That is, if you have a table prefix
 *                   of pf_ defined, pass in 'node' instead of 'pf_node'.
 */
function tw_remove_tables($tablelist) {
  if (!is_array($tablelist)) {
    $tablelist = array($tablelist);
  }
  foreach ($tablelist as $tablename) {
    $split = explode('.', $tablename);
    if (isset($split[1])) {
      $dbconnection = $split[0];
      $tablename = $split[1];
    }
    else {
      $dbconnection = 'default';
    }
    $sql = "SELECT twtid FROM {tw_tables}
            WHERE dbconnection='%s' AND tablename='%s'";
    $twtid = db_result(db_query($sql, $dbconnection, $tablename));
    if ($twtid) {
      // Remove all relationship references
      $sql = "SELECT tr.twrid
              FROM {tw_relationships} tr
              INNER JOIN {tw_columns} tc ON tr.leftcol=tc.twcid OR tr.rightcol=tc.twcid
              WHERE tc.twtid=%d";
      $result = db_query($sql, $twtid);
      while ($row = db_fetch_object($result)) {
        db_query("DELETE FROM {tw_relationships} WHERE twrid=%d", $row->twrid);
      }
      // Remove columns
      db_query("DELETE FROM {tw_columns} WHERE twtid=%d", $twtid);
      // Remove the table
      db_query("DELETE FROM {tw_tables} WHERE twtid=%d", $twtid);
    }
  }

  // The views cache needs to be flushed to make sure default views are removed
  views_invalidate_cache();
}

/**
 * Remove tables to be managed by Table Wizard, using Batch API to break each table into
 * a separate request.
 *
 * @param $tablelist The name of a table or an array of table names. If a period is present,
 *                   the form connection.tablename is assumed (where connection is an
 *                   alternate database in $db_url). The tablename must be the logical,
 *                   not physical, name of the table. That is, if you have a table prefix
 *                   of pf_ defined, pass in 'node' instead of 'pf_node'.
 */
function tw_remove_tables_batch($tablelist) {
  if (!is_array($tablelist)) {
    $tablelist = array($tablelist);
  }

  $batch = array('operations' => array(),
    'title' => t('Removing tables'),
    'init_message' => t('Starting removal of tables.'),
    'progress_message' => t('Removed @current out of @total tables.'),
    'error_message' => t('An error occurred. Some or all of the tables were not removed.'),
  );
  foreach ($tablelist as $tablename) {
    $batch['operations'][] = array('tw_remove_tables', array($tablename));
  }

  batch_set($batch);
}

/**
 * Mark a column as available for joins in relationships
 *
 * @param $tablename
 * @param $colname
 */
function tw_add_fk($tablename, $colname) {
  $split = explode('.', $tablename);
  if (isset($split[1])) {
    $dbconnection = $split[0];
    $tablename = $split[1];
  }
  else {
    $dbconnection = 'default';
  }
  $sql = "SELECT twtid FROM {tw_tables}
          WHERE dbconnection='%s' AND tablename='%s'";
  $twtid = db_result(db_query($sql, $dbconnection, $tablename));
  if ($twtid) {
    $sql = "UPDATE {tw_columns}
            SET availablefk=1
            WHERE twtid=%d AND colname='%s'";
    db_query($sql, $twtid, $colname);
  }
}

// @TODO: Create an index for any field referenced in the criteria (or suggest it)

/**
 * Add a relationship between two table columns, making it possible to join them in Views
 *
 * @param $leftcol
 *  The left side of a potential join, expressed either as a column ID from
 *  {tw_columns} or as a string in [connection.]table.column format.
 * @param $rightcol
 *  The right side of a potential join, expressed either as a column ID from
 *  {tw_columns} or as a string in [connection.]table.column format.
 * @param $automatic
 *  Boolean indicating whether to create views joins (i.e., relate the table
 *  automatically) or relationships.
 */
function tw_add_relationship($leftcol, $rightcol, $automatic = FALSE) {
  if (is_numeric($leftcol)) {
    $leftcolid = $leftcol;
  }
  else {
    $split = explode('.', $leftcol);
    if (count($split) == 3) {
      $leftcolid = tw_get_column_id($split[0], $split[1], $split[2]);
    }
    else {
      $leftcolid = tw_get_column_id('default', $split[0], $split[1]);
    }
  }
  if (is_numeric($rightcol)) {
    $rightcolid = $rightcol;
  }
  else {
    $split = explode('.', $rightcol);
    if (count($split) == 3) {
      $rightcolid = tw_get_column_id($split[0], $split[1], $split[2]);
    }
    else {
      $rightcolid = tw_get_column_id('default', $split[0], $split[1]);
    }
  }
  if ($leftcolid && $rightcolid) {
    db_query("INSERT INTO {tw_relationships}
              (leftcol, rightcol, automatic)
              VALUES(%d, %d, %d)",
              $leftcolid, $rightcolid, $automatic);
    // Make sure Views hears about the newly available relationship
    views_invalidate_cache();
  }
}

/**
 * Delete a relationship record
 *
 * @param $twrid ID from {tw_relationships}
 */
function tw_delete_relationship($twrid) {
  if ($twrid) {
    $sql = "DELETE FROM {tw_relationships}
            WHERE twrid=%d";
    db_query($sql, $twrid);
  }
  views_invalidate_cache();
}

/**
 * Retrieve the column ID ({tw_columns}) for a given column
 *
 * @param $dbconnection
 * @param $tablename
 * @param $colname
 * @return unsigned int
 */
function tw_get_column_id($dbconnection, $tablename, $colname) {
  $sql = "SELECT tc.twcid
          FROM {tw_tables} tt
          INNER JOIN {tw_columns} tc ON tt.twtid=tc.twtid AND tc.colname='%s'
          WHERE tt.dbconnection='%s' AND tt.tablename='%s'";
  $twcid = db_result(db_query($sql, $colname, $dbconnection, $tablename));
  return $twcid;
}

/**
 * Get the default view corresponding to a given table
 *
 * @param $tablename
 */
function tw_get_view_name($twtid) {
  $view_name = db_result(db_query("SELECT view_name FROM {tw_tables} WHERE twtid=%d", $twtid));
  return $view_name;
}

/**
 * Analyze a table - determine value ranges, empty fields, etc., and
 * populate {tw_columns}
 *
 * @param $tablename
 * @param $dbconnection
 * @param $skip_full_analysis Indicates that data statistics (mins and maxes) should not be
 *                   collected, saving substantial time for large tables
 */
function tw_perform_analysis($tablename, $dbconnection = 'default', $skip_full_analysis = FALSE) {
  $twtid = db_result(db_query("SELECT twtid FROM {tw_tables}
                               WHERE dbconnection='%s' AND tablename='%s'",
                              $dbconnection, $tablename));

  // Let the Schema module figure out the table definition
  if ($dbconnection != 'default') {
    db_set_active($dbconnection);
  }
  $inspect = schema_invoke('inspect');
  $schema = $inspect[$tablename];

  if (!$schema) {
    drupal_set_message(t('Could not analyze !tablename (connection !conn)',
      array('!tablename' => $tablename, '!conn' => $dbconnection)), 'error');
  }
  else {
    if (isset($schema['primary key'])) {
      $pks = $schema['primary key'];
    }
    else {
      $pks = array();
    }
    $weight = 0;
    // Go through the existing fields in the table, adding and updating as necessary
    if (!is_array($schema)) {
      drupal_set_message(t('No fields found for !tablename (connection !conn)',
        array('!tablename' => $tablename, '!conn' => $dbconnection)));
    }
    else {
      foreach ($schema['fields'] as $colname => $coldef) {
        $coltype = $coldef['type'];
        if (!$skip_full_analysis) {
          db_set_active($dbconnection);
          // Analyze the values in the table
          $sql = 'SELECT MIN(' . tw_quote_identifier($colname) . ')
                  FROM ' . tw_quote_identifier('{' . $tablename . '}');
          $minvalue = db_result(db_query($sql));
          $sql = 'SELECT MAX(' . tw_quote_identifier($colname) . ')
                  FROM ' . tw_quote_identifier('{' . $tablename . '}');
          $maxvalue = db_result(db_query($sql));
          if (tw_column_type($coltype) == 'text') {
            $sql = 'SELECT MIN(CHAR_LENGTH(' . tw_quote_identifier($colname) . '))
                    FROM ' . tw_quote_identifier('{' . $tablename . '}');
            $minlength = db_result(db_query($sql));
            if (!isset($minlength)) {
              $minlength = 0;
            }
            $sql = 'SELECT MAX(CHAR_LENGTH(' . tw_quote_identifier($colname) . '))
                    FROM ' . tw_quote_identifier('{' . $tablename . '}');
            $maxlength = db_result(db_query($sql));
            if (!isset($maxlength)) {
              $maxlength = 0;
            }
            if ($maxlength == 0) {
              $isempty = TRUE;
            }
            else {
              $isempty = FALSE;
            }
          }
          else {
            if ($minvalue || $maxvalue) {
              $isempty = FALSE;
            }
            else {
              $isempty = TRUE;
            }
          }
        }

        if ($dbconnection != 'default') {
          db_set_active('default');
        }

        // See if we've already got this column recorded (i.e., whether we'll need
        // to insert or update)
        $sql = "SELECT * FROM {tw_columns}
                WHERE twtid=%d AND colname='%s'";
        $colrecord = db_fetch_object(db_query($sql, $twtid, $colname));
        if (!$colrecord) {
          $colrecord = new stdClass;
          $colrecord->twtid = $twtid;
          $colrecord->colname = $colname;
        }
        $colrecord->weight = $weight++;
        $colrecord->coltype = $coltype;
        $colrecord->isempty = isset($isempty) ? $isempty : FALSE;
        if (tw_column_type($coltype) == 'text') {
          if (isset($minlength)) {
            $colrecord->minlength = $minlength;
          }
          if (isset($maxlength)) {
            $colrecord->maxlength = $maxlength;
          }
          if (!$minvalue) {
            $minvalue = '';
          }
          $colrecord->minstring = substr($minvalue, 0, 255);
          if (!$maxvalue) {
            $maxvalue = '';
          }
          $colrecord->maxstring = substr($maxvalue, 0, 255);
          unset($colrecord->minvalue);
          unset($colrecord->maxvalue);
        }
        else {
          if (!isset($minvalue) || !$minvalue) {
            $minvalue = 0;
          }
          $colrecord->minvalue = $minvalue;
          if (!isset($maxvalue) || !$maxvalue) {
            $maxvalue = 0;
          }
          $colrecord->maxvalue = $maxvalue;
          unset($colrecord->minlength);
          unset($colrecord->maxlength);
          unset($colrecord->minstring);
          unset($colrecord->maxstring);
        }
        if (in_array($colname, $pks)) {
          $colrecord->primarykey = (int)TRUE;
          // Assume PKs are always available as FKs
          $colrecord->availablefk = (int)TRUE;
        }
        else {
          $colrecord->primarykey = (int)FALSE;
          // Leave previous availablefk setting alone
        }

        if (isset($colrecord->twcid)) {
          drupal_write_record('tw_columns', $colrecord, 'twcid');
        }
        else {
          drupal_write_record('tw_columns', $colrecord);
        }
      }
      // Look for any columns no longer in the real table, and remove our record of them
      $sql = "SELECT colname FROM {tw_columns}
              WHERE twtid=%d";
      $result = db_query($sql, $twtid);
      while ($row = db_fetch_object($result)) {
        if (!isset($schema['fields'][$row->colname])) {
          $sql = "DELETE FROM {tw_columns}
                  WHERE twtid=%d AND colname='%s'";
          db_query($sql, $twtid, $row->colname);
          drupal_set_message(t('Removed %colname', array('%colname' => $row->colname)));
        }
      }
    }
  }
  if ($dbconnection != 'default') {
    db_set_active('default');
  }
  views_invalidate_cache();
}

/**
 * Break down schema field types into text/numeric/datetime
 *
 * @param $schematype
 *   The schema type to identify
 * @return
 *   Generic column type (text, numeric, or datetime) - unknown if unrecognized
 */
function tw_column_type($schematype) {
  switch ($schematype) {
    case 'varchar':
    case 'char':
    case 'text':
    case 'blob':
    case 'enum':
      return 'text';

    case 'numeric':
    case 'serial':
    case 'int':
    case 'float':
      return 'numeric';

    case 'datetime':
      return 'datetime';

    default:
      return 'unknown';
  }
}

/**
 * Quote SQL identifiers, in case they contain spaces/are reserved words/etc.
 *
 * @param $identname
 * @return Quoted identifier name
 */
function tw_quote_identifier($identifier) {
  global $db_type;
  if ($db_type == 'mysql' || $db_type == 'mysqli') {
    $quote = '`';
  }
  elseif ($db_type == 'pgsql') {
    $quote = '"';
  }
  else {
    drupal_set_message(t('Unrecognized database type %dbtype', array('%dbtype' => $db_type)));
  }
  return $quote . $identifier . $quote;
}

/**
 * Get the database properties from its 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 = NULL, $property = NULL) {
  // Cache the parsed db properties.
  static $db_url_parsed = array();
  global $db_url;

  //if no dbkey is given, then return the full array of all the databases.
  if (!isset($dbkey)) {
    foreach($db_url as $dbkey => $url) {
      $db_url_parsed[$dbkey] = tw_get_dbinfo($dbkey);
    }
    return $db_url_parsed;
  }

  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);
      $db_url_parsed[$dbkey]['path'] = $db_url[$dbkey];
  }
  // 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];
}

/**
 * Construct a dbengine-specific qualified tablename
 *
 * @param $dbconnection
 *   The database key set in the settings.php file ('default').
 * @param $tablename
 *   The name of the table
 * @return
 *   The qualified tablename if qualification is supported, just the
 *   tablename otherwise.
 */
function tw_qualified_tablename($dbconnection, $tablename) {
  global $db_type;
  // No need to worry about the default connection
  if ($dbconnection == 'default') {
    return $tablename;
  }

  // MySQL is the one supported db known to handle this
  if ($db_type == 'mysqli' || $db_type == 'mysql') {
    // And we also need the Views patch at http://drupal.org/node/576694#comment-2277558
    // to pull this off
    views_include('query');
    if (function_exists('views_clean_alias')) {
      // Check if cross-db joins are possible (match)
      static $checked_compats = array();
      if (!isset($checked_compats[$dbconnection])) {
        $externaldb = tw_get_dbinfo($dbconnection);
        $internaldb = tw_get_dbinfo('default');
        if ($externaldb['scheme'] != 'mysql' && $externaldb['scheme'] != 'mysqli') {
          $checked_compats[$dbconnection] = FALSE;
        }
        else {
          if ($externaldb['user'] == $internaldb['user'] &&
              $externaldb['pass'] == $internaldb['pass'] &&
              $externaldb['host'] == $internaldb['host']) {
            $checked_compats[$dbconnection] = TRUE;
          }
          else {
            $checked_compats[$dbconnection] = FALSE;
          }
        }
      }
      if ($checked_compats[$dbconnection]) {
        return tw_get_dbinfo($dbconnection, 'name') . '.' . $tablename;
      }
      else {
        return $tablename;
      }
    }
    else {
      return $tablename;
    }
  }
  else {
    return $tablename;
  }
}

//////////////////////////////////////////////////////////
// Drupal core hooks
//
define('TW_ACCESS', 'table wizard administration');

/**
 * Implementation of hook_menu().
 */
function tw_menu() {
  $items = array();

  $items['admin/content/tw'] = array(
    'title' => 'Table Wizard',
    'description' => 'Manage raw data from arbitrary tables: import, analyze, and view it',
    'page callback' => 'tw_sources',
    'access arguments' => array(TW_ACCESS),
    'file' => 'tw_pages.inc',
  );
  $items['admin/content/tw/tables'] = array(
    'title' => 'Tables',
    'description' => 'Manage raw data from arbitrary tables: import, analyze, and view it',
    'type' => MENU_DEFAULT_LOCAL_TASK,
    'weight' => 1,
  );
  $items['admin/content/tw/relationships'] = array(
    'title' => 'Relationships',
    'description' => 'Manage relationships between managed tables',
    'page callback' => 'tw_relationships',
    'access arguments' => array(TW_ACCESS),
    'file' => 'tw_pages.inc',
    'type' => MENU_LOCAL_TASK,
    'weight' => 2,
  );
  $items['admin/content/tw/analyze/%'] = array(
    'title' => 'Table analysis',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('tw_analysis', 4),
    'access arguments' => array(TW_ACCESS),
    'type' => MENU_CALLBACK,
    'file' => 'tw_pages.inc',
  );
  $items['admin/content/tw/analyze/%/3'] = array(
    'title' => 'Non-ignored fields',
    'access arguments' => array(TW_ACCESS),
    'type' => MENU_DEFAULT_LOCAL_TASK,
    'file' => 'tw_pages.inc',
    'weight' => 1,
  );
  $items['admin/content/tw/analyze/%/2'] = array(
    'title' => 'Ignored fields',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('tw_analysis', 4, 5),
    'access arguments' => array(TW_ACCESS),
    'type' => MENU_LOCAL_TASK,
    'file' => 'tw_pages.inc',
    'weight' => 2,
  );
  $items['admin/content/tw/analyze/%/1'] = array(
    'title' => 'All fields',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('tw_analysis', 4, 5),
    'access arguments' => array(TW_ACCESS),
    'type' => MENU_LOCAL_TASK,
    'file' => 'tw_pages.inc',
    'weight' => 3,
  );
  $items['admin/content/tw/export'] = array(
    'title' => 'Views definition for hook_views_data()',
    'page callback' => 'tw_export',
    'access arguments' => array(TW_ACCESS),
    'type' => MENU_CALLBACK,
    'file' => 'tw_pages.inc',
  );
  return $items;
}

/**
 * Implementation of hook_perm().
 */
function tw_perm() {
  return array(TW_ACCESS);
}

/**
 * Implementation of hook_help().
 */
function tw_help($page, $arg) {
  switch ($page) {
    case 'admin/help#tw':
      $output = '<p>' . t('') . '</p>';
      $output .= '<p>' . t('') . '</p>';

      return $output;
  }
}

/**
 * Implementation of hook_theme().
 */
function tw_theme() {
  return array(
    'tw_analysis' => array(
      'arguments' => array('form' => NULL),
      'function' => 'theme_tw_analysis',
    ),
    'tw_relationships' => array(
      'arguments' => array('form' => NULL),
      'function' => 'theme_tw_relationships',
    ),
    'tw_sources' => array(
      'arguments' => array('form' => NULL),
      'function' => 'theme_tw_sources',
    ),
  );
}

///////////////////////////////////////////////////////////////
// Contrib module hooks
//

/**
 * Implementation of hook_views_api().
 */
function tw_views_api() {
  return array('api' => '2.0');
}