<?php
// $Id: tw.module,v 1.1.2.31 2009-05-03 18:58: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'.
 */
function tw_add_tables($tablelist) {
  if (!is_array($tablelist)) {
    $tablelist = array($tablelist);
  }
  foreach ($tablelist as $tablename) {
    // TODO: Use Batch API (multiple large tables can take a while)
    // Split out the connection
    $split = explode('.', $tablename);
    if (isset($split[1])) {
      $dbconnection = $split[0];
      $tablename = $split[1];
    }
    else {
      $dbconnection = 'default';
    }

    // 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) {
      db_query("INSERT INTO {tw_tables}
                (dbconnection, tablename)
                VALUES('%s', '%s')",
                $dbconnection, $tablename);
    }

    // Analysis populates the tw_columns table
    tw_perform_analysis($tablename, $dbconnection);
  }
  // The views cache needs to be flushed to make new tables and default views available. For
  // some reason, flushing only the views cache doesn't always seem to do the job, so we flush
  // everything.
  drupal_flush_all_caches();
}

/**
 * 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'.
 */
function tw_add_tables_batch($tablelist) {
  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));
  }

  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);
    }
  }
  drupal_flush_all_caches();
}

/**
 * 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.
 */
function tw_add_relationship($leftcol, $rightcol) {
  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);
    $rightcolid = tw_get_column_id($split[0], $split[1]);
  }
  if ($leftcolid && $rightcolid) {
    db_query("INSERT INTO {tw_relationships}
              (leftcol, rightcol)
              VALUES(%d, %d)",
              $leftcolid, $rightcolid);
    // Make sure Views hears about the newly available relationship
    drupal_flush_all_caches();
  }
}

/**
 * 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);
  }
  drupal_flush_all_caches();
}

/**
 * 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;
}

/**
 * Analyze a table - determine value ranges, empty fields, etc., and
 * populate {tw_columns}
 *
 * @param $tablename
 * @param $dbconnection
 */
function tw_perform_analysis($tablename, $dbconnection = 'default') {
  $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) {
        db_set_active($dbconnection);
        $coltype = $coldef['type'];
        // 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;
          }
        }
        // See if we've already got this column recorded (i.e., whether we'll need
        // to insert or update)
        if ($dbconnection != 'default') {
          db_set_active('default');
        }
        $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 = $isempty;
        if (tw_column_type($coltype) == 'text') {
          $colrecord->minlength = $minlength;
          $colrecord->maxlength = $maxlength;
          if (!$minvalue) {
            $minvalue = '';
          }
          $colrecord->minstring = $minvalue;
          if (!$maxvalue) {
            $maxvalue = '';
          }
          $colrecord->maxstring = $maxvalue;
          unset($colrecord->minvalue);
          unset($colrecord->maxvalue);
        }
        else {
          if (!$minvalue) {
            $minvalue = 0;
          }
          $colrecord->minvalue = $minvalue;
          if (!$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 (!$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');
  }
  drupal_flush_all_caches();
}

/**
 * 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;
}

//////////////////////////////////////////////////////////
// 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/relationships'] = array(
    'title' => 'Relationships',
    'description' => 'Manage relationships between managed tables',
    'page callback' => 'tw_relationships',
    'access arguments' => array(TW_ACCESS),
    'file' => 'tw_pages.inc',
  );
  $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',
  );
  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');
}