<?php
// $Id: tw.module,v 1.1.2.19 2009-03-31 00:11:25 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 dbname.tablename is assumed (where dbname is an alternate database
 *                   in $db_url)
 */
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 database
    $split = explode('.', $tablename);
    if ($split[1]) {
      $tabledb = $split[0];
      $tablename = $split[1];
    } else {
      $tabledb = 'default';
    }

    // See if we're already managing this table
    $sql = "SELECT twtid FROM {tw_tables} 
            WHERE tabledb='%s' AND tablename='%s'";
    $twtid = db_result(db_query($sql, $tabledb, $tablename));
    if (!$twtid) {
      db_query("INSERT INTO {tw_tables}
                (tabledb, tablename)
                VALUES('%s', '%s')",
               $tabledb, $tablename);
    } 
    
    // Analysis populates the tw_columns table
    tw_perform_analysis($tablename, $tabledb);
  }
  // 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();
}

/**
 * Remove tables from the Table Wizard
 *
 * @param $tablelist - The name of a table being managed by Table Wizard, or an array
 *                     of table names.
 */
function tw_remove_tables($tablelist) {
  if (!is_array($tablelist)) {
    $tablelist = array($tablelist);
  }
  foreach ($tablelist as $tablename) {
    $sql = "SELECT twtid FROM {tw_tables} WHERE tablename='%s'";
    $twtid = db_result(db_query($sql, $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) {
  $sql = "SELECT twtid FROM {tw_tables} WHERE tablename='%s'";
  $twtid = db_result(db_query($sql, $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 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 table.column format.
 */
function tw_add_relationship($leftcol, $rightcol) {
  if (is_numeric($leftcol)) {
    $leftcolid = $leftcol;
  } else {
    $split = explode('.', $leftcol);
    $leftcolid = tw_get_column_id($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 $tablename
 * @param unknown_type $colname
 * @return unsigned int
 */
function tw_get_column_id($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.tablename='%s'";
  $twcid = db_result(db_query($sql, $colname, $tablename));
  return $twcid;
}

/**
 * Analyze a table - determine value ranges, empty fields, etc., and 
 * populate {tw_columns}
 *
 * @param $tablename
 * @param $tabledb
 */
function tw_perform_analysis($tablename, $tabledb = 'default') {
  $twtid = db_result(db_query("SELECT twtid FROM {tw_tables}
                               WHERE tablename='%s'",
                              $tablename));

  // Let the Schema module figure out the table definition
  db_set_active($tabledb);
  if ($tabledb == 'default') {
    // Remove the prefix if any - views knows tables by their logical name, not the DB name
    $rawtablename = schema_unprefix_table($tablename);
  } else {
    $rawtablename = $tablename;
  }
  $inspect = schema_invoke('inspect', $tablename);
  $schema = $inspect[$rawtablename];
  
  if (!$schema) {
    drupal_set_message(t('Could not analyze !tablename (db !db)',
      array('!tablename' => $tablename, '!db' => $tabledb)));
  } 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 (db !db)',
        array('!tablename' => $tablename, '!db' => $tabledb)));
    } else {
      foreach ($schema['fields'] as $colname => $coldef) {
        db_set_active($tabledb);
        $coltype = $coldef['type'];
        // Analyze the values in the table
        $sql = 'SELECT MIN('.tw_quote_column($colname).") FROM $tablename";
        $minvalue = db_result(db_query($sql));
        $sql = 'SELECT MAX('.tw_quote_column($colname).") FROM $tablename";
        $maxvalue = db_result(db_query($sql));
        if (tw_column_type($coltype) == 'text') {
          $sql = 'SELECT MIN(CHAR_LENGTH('.tw_quote_column($colname).")) FROM $tablename";
          $minlength = db_result(db_query($sql));
          $sql = 'SELECT MAX(CHAR_LENGTH('.tw_quote_column($colname).")) FROM $tablename";
          $maxlength = db_result(db_query($sql));
          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)
        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;
          $colrecord->minstring = $minvalue;
          $colrecord->maxstring = $maxvalue;
          unset($colrecord->minvalue);
          unset($colrecord->maxvalue);
        } else {
          $colrecord->minvalue = $minvalue;
          $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 ($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("Removed $row->colname");
        }
      }
    }
  }
  db_set_active('default');
}

/**
 * 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':
      return 'text';

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

    case 'datetime':
      return 'datetime';

    default:
      return 'unknown';
  }
}

/**
 * Quote column names, in case they contain spaces/are reserved words/etc.
 *
 * @param $colname
 * @return Quoted column name
 */
function tw_quote_column($colname) {
  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.$colname.$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');
}