Improve parsing stability and functionality

[solotandem]

Remove debug output from sdp(). Try it out. Read CHANGELOG.txt.

diff --git CHANGELOG.txt CHANGELOG.txt
new file mode 100644
index 0000000..1fcd6c9
--- /dev/null
+++ CHANGELOG.txt
@@ -0,0 +1,18 @@
+// $Id: CHANGELOG.txt,v 1.0 2010/12/13 07:00:00 solotandem Exp $
+
+sql_parser 7.x-1.x, 2010-xx-xx (development version)
+------------------------------
+- TODO (2010-xx-xx):

• * sql_converter
• * - Create tests with known output for known input
• * - Allow for more than two conditional expressions on a JOIN
• * - Add _toString() method for SqlGroupByClause
• * - Handle presence or absence of each type of clause in adding '$query' to output
• * - Pass extenders to parser

+- Changes (2010-12-13):

• * sql_converter
• * - Add $query variable to converted expressions since not all items can be chained together
• * - Handle selecting all fields from multiple tables: SELECT a., b., c.field1 FROM ..
• * - Eliminate reuse of conditional placeholders from prior JOIN when no placeholders on current JOIN
• • Maintain conditional placeholders separately for different JOIN expressions (SAME AS ABOVE)
    diff --git includes/sql_converter.inc includes/sql_converter.inc
    index 272f2c7..2c08a07 100644
    --- includes/sql_converter.inc
    +++ includes/sql_converter.inc
    @@ -68,6 +68,9 @@ class SqlConverter {
• The SqlConditional object.
  */
  protected function convertConditionalPlaceholders(SqlConditional &$conditional) {
  +// sdp(FUNCTION);
  +// sdp($conditional, '$conditional');
• // @todo This is limited to two conditions.
  if ($conditional->arg1 instanceof SqlConditional) {
  $this->convertConditionalPlaceholders($conditional->arg1);
  }
  @@ -98,6 +101,8 @@ class SqlConverter {
• The new placeholder name.
  */
  protected function convertPlaceholder(SqlField $field) {
  +// sdp(FUNCTION);
  +// sdp($field, '$field');
  if ($field->alias) {
  $name = $field->alias;
  }
  @@ -193,7 +198,7 @@ class SqlDBTNGConverter extends SqlConverter {
  */
  protected function convertSelect() {
  // Add first table to db_select() function.
• $this->output[] = "db_select('" . $this->sql_object->tables[0]->name . "', '" . $this->getTableAlias(0) . "')";
• $this->output[] = "$query = db_select('" . $this->sql_object->tables[0]->name . "', '" . $this->getTableAlias(0) . "')";

$this->convertSelectFields();

@@ -210,7 +215,7 @@ class SqlDBTNGConverter extends SqlConverter {
$this->output[] = " ->groupBy($group_by_column)";
}

•  if ($this->sql_object->having_clause) {
  

•  if (isset($this->sql_object->having_clause)) { // @todo Why do we need to check isset() on this but not other objects? Where are they defaultly set?
   $this->convertHaving($this->sql_object->having_clause);
  

  }
  }
  @@ -284,18 +289,30 @@ class SqlDBTNGConverter extends SqlConverter {

  • Convert Select query fields.
    */
    protected function convertSelectFields() {
    +// sdp(FUNCTION);
    +// sdp($this->sql_object->tables, '$this->sql_object->tables');
    +// sdp($this->sql_object->fields, '$this->sql_object->fields');

• // Save first table items.

• $table0 = $this->sql_object->tables[0];
  // Select all fields.

• if ($this->sql_object->fields[0]->name == '*') {

• // @todo An asterisk may occur in other parts of the field list with a table qualifier.

• // Individual fields from a table may be added in addition to an asterisk on that table.

• if (FALSE && $this->sql_object->fields[0]->name == '*') {
  $this->output[] = " ->fields()";
  }
  // Select individual fields.
  else {
  // Group fields by table. If no table is set for a field, then use the first table.
  // If field has an alias, then the field needs to be grouped individually.

•  $table_fields = array();
  

•  $aliased_fields = array();
  

•  $all_fields = $table_fields = $aliased_fields = array();
  

  foreach ($this->sql_object->fields as $id => $field) {

•    if ($field->alias) {
  

•    if ($field->name == '*') {
  

•      // If no table is set for a field, then use the first table.
  

•      $table_alias = $field->table ? $field->table : ($table0->alias ? $table0->alias : $table0->name);
  

•      // Always add to output in case individual fields from the table are also included.
  

•      $this->output[] = "  ->fields('$table_alias')";
  

•    }
  

•    elseif ($field->alias) {
     $aliased_fields[] = $field;
   }
   else {
  

  @@ -317,11 +334,16 @@ class SqlDBTNGConverter extends SqlConverter {

  // Add aliased fields.
  if ($aliased_fields) {

•    // End the current expression as addField() returns a field object not a
  

•    // select query object.
  

•    $this->output[count($this->output) - 1] .= ';';
   foreach ($aliased_fields as $field) {
  

•      if (!($table_alias = $field->table)) {
  

•        $table_alias = $this->sql_object->tables[0]->alias;
  

•      }
  

•      $this->output[] = "  ->addField('$table_alias', '" . $field->name . "', '" . $field->alias . "')";
  

  +// if (!($table_alias = $field->table)) {
  +// $table_alias = $this->sql_object->tables[0]->alias;
  +// }

•      // If no table is set for a field, then use the first table.
  

•      $table_alias = $field->table ? $field->table : ($table0->alias ? $table0->alias : $table0->name);
  

•      $this->output[] = "\$query->addField('$table_alias', '" . $field->name . "', '" . $field->alias . "');";
   }
  

  }
  }
  @@ -346,23 +368,29 @@ class SqlDBTNGConverter extends SqlConverter {

  /**

  • Convert table joins.

• * As join() returns the unique alias that was assigned for this table, it

• * must form a separate statement.
  */
  function convertJoins() {
  foreach ($this->sql_object->tables as $key => $table) {

•  // Reset the placeholders.
  

•  // @todo Does this have side effects on the arguments passed to the function later?
  

•  $this->placeholders = array();
  

  // Only convert tables that have a join.
  if ($table->join) {
  switch ($table->join) {
  case 'join':
  case 'inner join':

•        $join_statement = "  ->join(";
  

•        $join_statement = '$query->join(';
       break;
  
     case 'left join':
  

•        $join_statement = "  ->leftJoin(";
  

•        $join_statement = '$query->leftJoin(';
       break;
  
     case 'right join':
  

•        $join_statement = "  ->rightJoin(";
  

•        $join_statement = '$query->rightJoin(';
       break;
   }
   $right_table_alias = $this->getTableAlias($key);
  

  @@ -375,12 +403,13 @@ class SqlDBTNGConverter extends SqlConverter {
  if ($table->join_conditional->type == 'using') {
  $join_conditions = array();
  foreach ($table->join_conditional->columns as $column) {

•        // @todo Are the left and right column names always the same (in this code anyhow)?
       $join_conditions[] = $left_table_alias . '.' . $column . ' = ' . $right_table_alias . '.' . $column;
     }
     $join_statement .= "'" . join(' AND ', $join_conditions) . "'";
   }
   else {
  

•      // Convert any placeholders and literlas in the join conditional 
  

•      // Convert any placeholders and literals in the join conditional 
     // statement to Drupal 7 placeholders.  This will set the 
     // $this->placeholders array with the new placeholders, which can then 
     // be used to generate the arguments array.
  

  @@ -393,7 +422,7 @@ class SqlDBTNGConverter extends SqlConverter {
  }
  }

•    $join_statement .= ")";
  

•    $join_statement .= ");";
  
   $this->output[] = $join_statement;
  

  }
  @@ -411,6 +440,9 @@ class SqlDBTNGConverter extends SqlConverter {
  protected function convertConditionals(SqlConditional $conditional, $level = 1) {
  $closing_parenthesis = FALSE;

• // Start the statement (@todo only if previous expressions required separate statements).

• $this->output[] = '$query';

// Convert operator.
switch ($conditional->operator) {
case 'and':
@@ -426,7 +458,7 @@ class SqlDBTNGConverter extends SqlConverter {
break;

 case 'xor':

•    $this->output[] = str_repeat("  ", $level - 1) . "->condition(db_xort()";
  

•    $this->output[] = str_repeat("  ", $level - 1) . "->condition(db_xor()";
   $closing_parenthesis = TRUE;
   break;
  

  }
  diff --git includes/sql_lexer.inc includes/sql_lexer.inc
  index 9b6a15d..0665d42 100644
  --- includes/sql_lexer.inc
  +++ includes/sql_lexer.inc
  @@ -226,6 +226,7 @@ class SqlLexer {
  // End of string is reached.
  if (is_null($c)) {
  $state = 1000;

•        // sdp('end of string');
       break;
     }
  

@@ -234,7 +235,9 @@ class SqlLexer {
if ($c == "\n" || $c == "\r") {
// Handles MAC/Unix/Windows line endings for multiline sql strings.
if ($c == "\r") {

•            // sdp('carriage return is ascii ' . ord($c));
           $c = $this->skip();
  

•            // sdp('next char is ascii ' . ord($c));
  
           // If not DOS newline
           if ($c != "\n") {
  

  @@ -245,6 +248,7 @@ class SqlLexer {
  $this->lineBegin = $this->tokenPointer;
  }
  $c = $this->skip();

•        // sdp('next char is ascii ' . ord($c));
       $this->tokenLength = 1;
     }
  

@@ -291,6 +295,7 @@ class SqlLexer {

       if ($c == '.') {
         $t = $this->get();

•        // sdp($t, '$t get'); // TODO Not hit.
       // Ellipsis.
       if ($t == '.') {
         if ($this->get() == '.') {
  

  @@ -300,6 +305,7 @@ class SqlLexer {
  }
  else {
  // Unknown token. Revert to single character.

•            // sdp($t, '$t $state = 999');
           $state = 999;
           break;
         }
  

  @@ -311,6 +317,7 @@ class SqlLexer {
  break;
  }
  else { // period

•          // sdp('unget $t');
         $this->unget();
       }
     }
  

  @@ -350,13 +357,15 @@ class SqlLexer {
  }

     // Unknown token.  Revert to single char.
  

•      // sdp($c, '$state = 999');
     $state = 999;
     break;
  
   // State 1 : Incomplete keyword or identifier.
   case 1:
     $c = $this->get();
  

•      if (ctype_alnum(ord($c)) || in_array($c, array('_', '.', '{' ,'}', '`'))) {
  

•      // @todo Include the '*' in the expression.
  

•      if (ctype_alnum(ord($c)) || in_array($c, array('_', '.', '{', '}', '`', '*'))) {
       $state = 1;
       break;
     }
  

  @@ -367,6 +376,7 @@ class SqlLexer {
  case 2:
  $this->unget();
  $this->setToken();

•      // sdp($this->tokenText, '$this->tokenText state 2');
  
     // Check if this token is a keyword in the symbols list.
     $testToken = strtolower($this->tokenText);
  

  @@ -595,17 +605,22 @@ class SqlLexer {
  break;

   // State 999 : Unknown token.  Revert to single char.
  

•    // Unknown is an odd categorization. This catches '*', ',', whitespace and return.
   case 999:
     if (is_null($c)) {
  

•        // sdp('$c is null');
       return NULL;
     }
     $this->revert();
  

•      $this->get();
  

•      $c = $this->get();
  

•      // sdp($c, '$c');
     $this->setToken();
  

•      // sdp($this->tokenText, '$this->tokenText in case 999');
     return $this->tokenText;
  
   // State 1000 : End Of Input.
   case 1000:
  

•      // sdp('end of input');
     $this->tokenText = '_end of input_';
     $this->skipText = substr($this->sql_string, $this->tokenAbsStart, $this->tokenStart - $this->tokenAbsStart);
     $this->tokenStart = $this->tokenPointer;
  

  diff --git includes/sql_object.inc includes/sql_object.inc
  index e1eb548..f4cd17f 100644
  --- includes/sql_object.inc
  +++ includes/sql_object.inc
  @@ -689,8 +689,8 @@ class SqlTable {
  $output .= ' ' . $this->alias;
  }
  if ($this->join_conditional) {

•  sdp($output, '$output');
  

•  sdp($this->join_conditional->__toString(), '$this->join_conditional');
  

•  // sdp($output, '$output');
  

•  // sdp($this->join_conditional->__toString(), '$this->join_conditional');
  

  $output .= $this->join_conditional;
  }
  return $output;
  diff --git includes/sql_parser.inc includes/sql_parser.inc
  index 783b228..202dfa6 100644
  --- includes/sql_parser.inc
  +++ includes/sql_parser.inc
  @@ -140,6 +140,7 @@ class SqlParser {
  }
  if ($sql_object) {
  $sql_object->placeholders = $this->placeholders;

•  // sdp(print_r($sql_object, 1), '$sql_object');
  

  return $sql_object;
  }
  }
  @@ -200,7 +201,12 @@ class SqlParser {
  break;

   case 'group':
  

•      $group_by = &$sql_object->addGroupBy();
  

•      // @todo Certain clauses should not restrict tokens to non-reserved words (e.g. module, count)
  

•      // This includes table and column alias expressions, and GROUP BY and ORDER BY clauses which
  

•      // can refer to the column aliases.
  

•      // Table names may not be used in an ON clause of a JOIN, or GROUP BY and ORDER BY clauses.
  

•      // Column aliases may not be used in an ON clause of a JOIN or a WHERE clause.
  

•      $group_by = /*&*/$sql_object->addGroupBy(); // @todo Remove '&' from before function calls
     $this->getToken();
     if ($this->token != 'by') {
       throw new SqlParserException('Expected "by"', $this->token, $this->lexer);
  

  @@ -226,6 +232,8 @@ class SqlParser {
  continue;

   default:
  

•      // sdp("'" . ord($this->token) . "'", 'ord($this->token)');
  

•      // is_null($this->token) ? sdp('token is null') : sdp('token is NOT null');
     throw new SqlParserException('Unexpected clause', $this->token, $this->lexer);
  

  }
  }
  @@ -691,8 +699,12 @@ class SqlParser {
  $sql_conditional->setNot(FALSE);
  }
  elseif (!is_null($this->token)) {

•  // sdp('$this->lexer->unget()');
  

  $this->lexer->unget();
  }

• else {

•  // sdp('is null in conditional');
  

• }
  return $sql_conditional;
  }

@@ -978,10 +990,14 @@ class SqlParser {

/**
* Parses field into a new field object.

• *
• * @note Handles field expressions in ON and WHERE clauses.

• @return SqlField
  */
  protected function parseField() {
  +// sdp(FUNCTION);
  +// sdp($this->lexer->tokenText, '$this->lexer->tokenText');
  if (strpos($this->lexer->tokenText, '.')) {
  list($columnTable, $columnName) = explode(".", $this->lexer->tokenText . '.');
  }
  @@ -1078,7 +1094,9 @@ class SqlParser {
  $function->setAlias($this->lexer->tokenText);
  }
  else {

•    throw new SqlParserException('Expected column alias', $this->token, $this->lexer);
  

•      // sdp($this->token, 'token type for alias');
  

•      // sdp($this->lexer->tokenText, 'tokenText for alias');
  

•    throw new SqlParserException('Expected column alias 1', $this->token, $this->lexer);
  

  }
  }
  else {
  @@ -1086,7 +1104,9 @@ class SqlParser {
  $function->setAlias($this->lexer->tokenText);
  }
  else {

•    throw new SqlParserException('Expected column alias, from or comma', $this->token, $this->lexer);
  

•      // sdp($this->token, 'token type for alias');
  

•      // sdp($this->lexer->tokenText, 'tokenText for alias');
  

•    throw new SqlParserException('Expected column alias 2, from or comma', $this->token, $this->lexer);
  

  }
  }
  return $function;
  @@ -1103,15 +1123,20 @@ class SqlParser {
  $sql_object->setSetIdentifier($this->token);
  $this->getToken();
  }
  +// sdp(FUNCTION);
  +// sdp($this->lexer->tokenText, '$this->lexer->tokenText');
  if ($this->token == '*') {
  $sql_object->addField($this->token);
  $this->getToken();
  }
  elseif ($this->token == 'identifier' || $this->dialect->isFunc($this->token)) {
  while ($this->token != 'from') {
  +// sdp(FUNCTION);
  +// sdp($this->lexer->tokenText, '$this->lexer->tokenText');
  if ($this->token == 'identifier') {
  list($columnTable, $columnName) = explode(".", $this->lexer->tokenText . '.');
  if (!$columnName) {

•        // @todo This is setting a.*; storing a as columnName
       $columnName = $columnTable;
       $columnTable = '';
     }
  

  @@ -1121,12 +1146,15 @@ class SqlParser {
  $this->getToken();
  if ($this->token == 'as') {
  $this->getToken();

•        if ($this->token == 'identifier' ) {
  

•          $columnAlias = $this->lexer->tokenText;
  

•        }
  

•        else {
  

•          throw new SqlParserException('Expected column alias', $this->token, $this->lexer);
  

•        }
  

  +// if ($this->token == 'identifier' ) {
  +// $columnAlias = $this->lexer->tokenText;
  +// }
  +// else {
  +// sdp($this->token, 'token type for alias');
  +// sdp($this->lexer->tokenText, 'tokenText for alias');
  +// throw new SqlParserException('Expected column alias 3', $this->token, $this->lexer);
  +// }

•        $columnAlias = $this->lexer->tokenText;
     }
     elseif ($this->token == 'identifier') {
       $columnAlias = $this->lexer->tokenText;
  

  @@ -1164,7 +1192,7 @@ class SqlParser {
  }
  }
  else {

•  throw new SqlParserException('Expected columns or a set function', $this->token, $this->lexer);
  

•  throw new SqlParserException('Expected columns or a [set?] function', $this->token, $this->lexer);
  

  }
  }

@@ -1265,6 +1293,8 @@ class SqlParser {
$table->setAlias($this->lexer->tokenText);
}
else {

•      sdp($this->token, 'token type for alias');
  

•      sdp($this->lexer->tokenText, 'tokenText for alias');
     throw new SqlParserException('Expected table alias', $this->token, $this->lexer);
   }
   $this->getToken();
  

  diff --git sql_parser.module sql_parser.module
  index d85a46a..58def40 100644
  --- sql_parser.module
  +++ sql_parser.module
  @@ -11,7 +11,7 @@
  */
  function sql_parser_menu() {
  $items['admin/config/development/sql-parser'] = array(
• 'title' => 'SQL parser',
• 'title' => 'SQL Parser',
  'description' => 'Parse SQL strings and convert to Drupal DBTNG objects.',
  'page callback' => 'drupal_get_form',
  'page arguments' => array('sql_parser_parser_form'),
  @@ -28,36 +28,39 @@ function sql_parser_parser_form($form, &$form_state) {
  if (isset($form_state['input']['sql'])) {
  $parsed_sql = sql_parser_parse($form_state['input']['sql']);
  if ($parsed_sql) {

•  $raw_query_string = (string) $parsed_sql;
  

  +// $raw_query_string = (string) $parsed_sql; // @todo Not all objects have _toString() defined.
  $dbtng_query_string = sql_parser_convert_dbtng($parsed_sql);
  }
• drupal_set_message('Peak memory usage = ' . number_format(memory_get_peak_usage(TRUE), 0, '.', ',') . " bytes");
  }
• $form['sql'] = array(
• '#title' => 'Original SQL string',
• '#type' => 'textarea',
• '#default_value' => isset($form_state['input']['sql']) ? $form_state['input']['sql'] : '',
• );
• $form['parsed_sql'] = array(
• '#title' => 'Parsed SQL string',
• '#type' => 'textarea',
• '#attributes' => array(

•  'readonly' => 'readonly',
  

• ),
• '#value' => isset($raw_query_string) ? $raw_query_string : '',
• // Move button here while testing.
• $form['submit'] = array(
• '#type' => 'button',
• '#value' => t('Convert'),
  );
  $form['output'] = array(
  '#title' => 'Database object syntax',
  '#type' => 'textarea',
• '#rows' => 10,
• '#rows' => min(35, max(15, isset($dbtng_query_string) ? count(explode("\n", $dbtng_query_string)) : 0)), // @todo For testing.
  '#attributes' => array(
  'readonly' => 'readonly',
  ),
  '#value' => isset($dbtng_query_string) ? $dbtng_query_string : '',
  );
• $form['submit'] = array(
• '#type' => 'button',
• '#value' => t('Convert'),
• $form['sql'] = array(
• '#title' => 'Original SQL string',
• '#type' => 'textarea',
• '#rows' => 15, // @todo For testing.
• '#default_value' => isset($form_state['input']['sql']) ? $form_state['input']['sql'] : '',
  );
  +// $form['parsed_sql'] = array(
  +// '#title' => 'Parsed SQL string',
  +// '#type' => 'textarea',
  +// '#attributes' => array(
  +// 'readonly' => 'readonly',
  +// ),
  +// '#value' => isset($raw_query_string) ? $raw_query_string : '',
  +// );
  return $form;
  }

@@ -113,6 +116,8 @@ function sql_parser_parse($sql) {

• Converted DBTNG string.
  */
  function sql_parser_convert_dbtng(SqlObject $parsed_sql) {
  +// sdp(FUNCTION);
  +// sdp($parsed_sql, '$parsed_sql');
  $converter = new SqlDBTNGConverter($parsed_sql);
  return $converter->convert();
  }