<commit_before><?php
/**
 * Diffs tables.
 *
 * @package DBSteward
 * @subpackage mysql5
 * @license http://www.opensource.org/licenses/bsd-license.php Simplified BSD License
 * @author <NAME> <<EMAIL>>
 */

class mysql5_diff_tables extends sql99_diff_tables {
  /**
   * Outputs DDL for addition, removal and modifications of table columns
   *
   * @param $ofs1       stage1 output pointer
   * @param $ofs3       stage3 output pointer
   * @param $old_schema original schema
   * @param $new_schema new schema
   */
  // @TODO: pull up
  public static function diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table_target = null, $new_table_target = null) {
    static::create_tables($ofs1, $old_schema, $new_schema, $old_table_target, $new_table_target);
    
    // were specific tables passed?
    if ( $old_table_target !== null || $new_table_target !== null ) {
      $old_table = $old_table_target;
      $new_table = $new_table_target;

      if ( $old_table && $new_table) {
        static::update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table);
      }
    }
    else {
      foreach(dbx::get_tables($new_schema) as $new_table) {
        if ( !$old_schema ) {
          // old_schema not defined
          continue;
        }
  
        $old_table = dbx::get_table($old_schema, $new_table['name']);
        
        dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table);
        
        if ( !$old_table ) {
          // old_table not defined
          continue;
        }
  
        static::update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table);
      }
    }
  }

  /**
   * Outputs commands for creation of new tables.
   *
   * @param $ofs         output file pointer
   * @param $old_schema  original schema
   * @param $new_schema  new schema
   */
  // @TODO: pull up
  private static function create_tables($ofs, $old_schema, $new_schema, $old_table = null, $new_table = null) {
    foreach(dbx::get_tables($new_schema) as $table) {
      if ( $new_table != null ) {
        if ( strcasecmp($table['name'], $new_table['name']) != 0 ) {
          continue;
        }
      }
      if (($old_schema == null) || !mysql5_schema::contains_table($old_schema, $table['name'])) {
        if ( !dbsteward::$ignore_oldname && mysql5_diff_tables::is_renamed_table($old_schema, $new_schema, $table) ) {
          // oldName renamed table ? rename table instead of create new one
          $old_table_name = mysql5::get_fully_qualified_table_name($new_schema['name'], $table['oldName']);
          // ALTER TABLE ... RENAME TO does not accept schema qualifiers when renaming a table
          // ALTER TABLE message.message_report RENAME TO report ;
          $new_table_name = mysql5::get_quoted_table_name($table['name']);
          $ofs->write("-- table rename from oldName specification" . "\n"
            . "ALTER TABLE $old_table_name RENAME TO $new_table_name ;" . "\n");
        }
        else {
          $ofs->write(mysql5_table::get_creation_sql($new_schema, $table, dbsteward::$quote_column_names) . "\n");
        }
      }
    }
  }

  /**
   * Outputs commands for addition, removal and modifications of
   * table columns.
   *
   * @param $ofs1       stage1 output file segmenter
   * @param $ofs3       stage3 output file segmenter
   * @param $old_table  original table
   * @param $new_table  new table
   */
  private static function update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table) {
    $commands = array();
    $drop_defaults_columns = array();
    static::add_drop_table_columns($commands, $old_table, $new_table);
    static::add_create_table_columns($commands, $old_table, $new_schema, $new_table, $drop_defaults_columns);
    static::add_modify_table_columns($commands, $old_schema, $old_table, $new_schema, $new_table, $drop_defaults_columns);

    if (count($commands) > 0) {
      // do 'pre' 'entire' statements before aggregate table alterations
      for($i=0; $i < count($commands); $i++) {
        if ( $commands[$i]['stage'] == 'BEFORE1' ) {
          $ofs1->write($commands[$i]['command'] . "\n");
        }
        else if ( $commands[$i]['stage'] == 'BEFORE3' ) {
          $ofs3->write($commands[$i]['command'] . "\n");
        }
      }

      $quotedTableName = mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']);

      $stage1_sql = '';
      $stage3_sql = '';

      for($i=0; $i < count($commands); $i++) {
        if ( !isset($commands[$i]['stage']) || !isset($commands[$i]['command']) ) {
          var_dump($commands[$i]);
          throw new exception("bad command format");
        }

        if ( $commands[$i]['stage'] == '1' ) {
          // we have a stage 1 alteration to make
          // do the alter table prefix if we haven't yet
          if ( strlen($stage1_sql) == 0 ) {
            $stage1_sql = "ALTER TABLE " . $quotedTableName . "\n";
          }
          $stage1_sql .= $commands[$i]['command'] . " ,\n";
        }
        else if ( $commands[$i]['stage'] == '3' ) {
          // we have a stage 3 alteration to make
          // do the alter table prefix if we haven't yet
          if ( strlen($stage3_sql) == 0 ) {
            $stage3_sql = "ALTER TABLE " . $quotedTableName . "\n";
          }
          $stage3_sql .= $commands[$i]['command'] . " ,\n";
        }
      }

      if ( strlen($stage1_sql) > 0 ) {
        $stage1_sql = substr($stage1_sql, 0, -3) . ";\n";
        $ofs1->write($stage1_sql);
      }
      if ( strlen($stage3_sql) > 0 ) {
        $stage3_sql = substr($stage3_sql, 0, -3) . ";\n";
        $ofs3->write($stage3_sql);
      }

      if (count($drop_defaults_columns) > 0) {
        $ofs1->write("\n");
        $ofs1->write("ALTER TABLE " . $quotedTableName . "\n");

        $alters = array_map(function($c){
          return "\tALTER COLUMN " . mysql5::get_quoted_column_name($c['name']) . " DROP DEFAULT";
        }, $drop_defaults_columns);
        $ofs->write(implode(",\n", $alters).";\n");
      }

      // do 'post' 'entire' statements immediately following aggregate table alterations
      for($i=0; $i < count($commands); $i++) {
        if ( $commands[$i]['stage'] == 'BEFORE1' ) {
          // already taken care of in earlier entire command output loop
        }
        else if ( $commands[$i]['stage'] == 'BEFORE3' ) {
          // already taken care of in earlier entire command output loop
        }
        else if ( $commands[$i]['stage'] == '1' ) {
          // already taken care of in earlier command aggregate loop
        }
        else if ( $commands[$i]['stage'] == '3' ) {
          // already taken care of in earlier command aggregate loop
        }
        else if ( $commands[$i]['stage'] == 'AFTER1' ) {
          $ofs1->write($commands[$i]['command'] . "\n");
        }
        else if ( $commands[$i]['stage'] == 'AFTER3' ) {
          $ofs3->write($commands[$i]['command'] . "\n");
        }
        else {
          throw new exception("Unknown stage " . $commands[$i]['stage'] . " during table " . $quotedTableName . " updates");
        }
      }
    }
  }

  /**
   * Adds commands for removal of columns to the list of commands.
   *
   * @param commands list of commands
   * @param old_table original table
   * @param new_table new table
   */
  // @TODO: pull up
  private static function add_drop_table_columns(&$commands, $old_table, $new_table) {
    foreach(dbx::get_table_columns($old_table) as $old_column) {
      if (!mysql5_table::contains_column($new_table, $old_column['name'])) {
        if ( !dbsteward::$ignore_oldname && ($renamed_column_name = mysql5_table::column_name_by_old_name($new_table, $old_column['name'])) !== false ) {
          // table indicating oldName = table['name'] present in new schema? don't do DROP statement
          $old_table_name = mysql5::get_quoted_table_name($old_table['name']);
          $old_column_name = mysql5::get_quoted_column_name($old_column['name']);
          $commands[] = array(
            'stage' => 'AFTER3',
            'command' => "-- $old_table_name DROP COLUMN $old_column_name omitted: new column $renamed_column_name indicates it is the replacement for " . $old_column_name
          );
        }
        else {
          //echo "NOTICE: add_drop_table_columns()  " . $new_table['name'] . " does not contain " . $old_column['name'] . "\n";
          $commands[] = array(
            'stage' => '3',
            'command' => "\tDROP COLUMN " . mysql5::get_quoted_column_name($old_column['name'])
          );
        }
      }
    }
  }

  /**
   * Adds commands for creation of new columns to the list of
   * commands.
   *
   * @param commands list of commands
   * @param old_table original table
   * @param new_table new table
   * @param drop_defaults_columns list for storing columns for which default value should be dropped
   */
  // @TODO: pull up
  private static function add_create_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns) {
    foreach(dbx::get_table_columns($new_table) as $new_column) {
      if (!mysql5_table::contains_column($old_table, $new_column['name'])) {
        if ( !dbsteward::$ignore_oldname && mysql5_diff_tables::is_renamed_column($old_table, $new_table, $new_column) ) {
          // oldName renamed column ? rename column instead of create new one
          $old_column_name = mysql5::get_quoted_column_name($new_column['oldName']);
          // $new_column_name = mysql5::get_quoted_column_name($new_column['name']);
          $to = mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults);
          $commands[] = array(
            'stage' => 'AFTER1',
            'command' => "-- column rename from oldName specification\n"
              . "ALTER TABLE " . mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name'])
              . " CHANGE COLUMN $old_column_name $to;"
          );
          continue;
        }
        
        // notice $include_null_definition is false
        // this is because ADD COLUMNs with NOT NULL will fail when there are existing rows

        $commands[] = array(
          'stage' => '1',
          'command' => "\tADD COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults, false)
        );
        // instead we put the NOT NULL defintion in stage3 schema changes once data has been updated in stage2 data
        if ( ! pgsql8_column::null_allowed($new_table, $new_column) ) {
          $commands[] = array(
            'stage' => '3',
            'command' => "\tMODIFY COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults)
          );
          // also, if it's defined, default the column in stage 1 so the SET NULL will actually pass in stage 3
          if ( strlen($new_column['default']) > 0 ) {
            $commands[] = array(
              'stage' => 'AFTER1',
              'command' => "UPDATE " . mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name'])
                . " SET " . mysql5::get_quoted_column_name($new_column['name']) . " = DEFAULT"
                . " WHERE " . mysql5::get_quoted_column_name($new_column['name']) . " IS NULL;"
            );
          }

        }

        if (mysql5_diff::$add_defaults && !mysql5_column::null_allowed($new_table, $new_column)) {
          $drop_defaults_columns[] = $new_column;
        }

        // some columns need filled with values before any new constraints can be applied
        // this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute
        $db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']);
        if ( $db_doc_new_schema ) {
          $db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']);
          if ( $db_doc_new_table ) {
            $db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']);
            if ( $db_doc_new_column ) {
              if ( isset($db_doc_new_column['beforeAddStage1']) ) {
                $commands[] = array(
                  'stage' => 'BEFORE1',
                  'command' => trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition"
                );
              }
              if ( isset($db_doc_new_column['afterAddStage1']) ) {
                $commands[] = array(
                  'stage' => 'AFTER1',
                  'command' => trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition"
                );
              }
              if ( isset($db_doc_new_column['beforeAddStage3']) ) {
                $commands[] = array(
                  'stage' => 'BEFORE3',
                  'command' => trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition"
                );
              }
              if ( isset($db_doc_new_column['afterAddStage3']) ) {
                $commands[] = array(
                  'stage' => 'AFTER3',
                  'command' => trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition"
                );
              }
            }
            else {
              throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found");
            }
          }
          else {
            throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found");
          }
        }
        else {
          throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found");
        }
      }
    }
  }

  /**
   * Adds commands for modification of columns to the list of
   * commands.
   *
   * @param commands list of commands
   * @param old_table original table
   * @param new_table new table
   * @param drop_defaults_columns list for storing columns for which default value should be dropped
   */
  // @TODO: pull up
  private static function add_modify_table_columns(&$commands, $old_schema, $old_table, $new_schema, $new_table, &$drop_defaults_columns) {
    foreach(dbx::get_table_columns($new_table) as $new_column) {
      if (!mysql5_table::contains_column($old_table, $new_column['name'])) {
        continue;
      }
      if ( !dbsteward::$ignore_oldname && mysql5_diff_tables::is_renamed_column($old_table, $new_table, $new_column) ) {
        // oldName renamed column ? skip definition diffing on it, it is being renamed
        continue;
      }

      $old_column = dbx::get_table_column($old_table, $new_column['name']);
      $new_column_name = mysql5::get_quoted_column_name($new_column['name']);

      $old_column_type = null;
      if ( $old_column ) {
        $old_column_type = mysql5_column::column_type(dbsteward::$old_database, $old_schema, $old_table, $old_column);
      }
      $new_column_type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column);

      if ( strcmp($old_column_type, $new_column_type) != 0 ) {
        // @TODO: Implement type conversion works-alike to pgsql's ALTER COLUMN ___ TYPE ___ USING ___

        $commands[] = array(
          'stage' => '1',
          'command' => "  MODIFY COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults)
            . " /* TYPE change - table: {$new_table['name']} original: {$old_column['type']} new: $new_column_type */"
        );
      }

      $old_default = isset($old_column['default']) ? $old_column['default'] : '';
      $new_default = isset($new_column['default']) ? $new_column['default'] : '';

      if (strcmp($old_default, $new_default) != 0) {
        if (strlen($new_default) == 0) {
          // if the default value was gotten rid of
          $commands[] = array(
            'stage' => '1',
            'command' => "\tALTER COLUMN $new_column_name DROP DEFAULT"
          );
        }
        else {
          // if the default value changed
          $commands[] = array(
            'stage' => '1',
            'command' => "\tALTER COLUMN $new_column_name SET DEFAULT $new_default"
          );
        }
      }

      if ( strcasecmp($old_column['null'], $new_column['null']) != 0 ) {
        if (mysql5_column::null_allowed($new_table, $new_column)) {
          // if the column is now allowed to be null, redefine it (because mysql won't let you just change NULL/NOT NULL)
          $commands[] = array(
            'stage' => '1',
            'command' => "\tMODIFY COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, FALSE)<commit_msg>Treat absent null attributes as true null attributes<commit_after><?php
/**
 * Diffs tables.
 *
 * @package DBSteward
 * @subpackage mysql5
 * @license http://www.opensource.org/licenses/bsd-license.php Simplified BSD License
 * @author <NAME> <<EMAIL>>
 */

class mysql5_diff_tables extends sql99_diff_tables {
  /**
   * Outputs DDL for addition, removal and modifications of table columns
   *
   * @param $ofs1       stage1 output pointer
   * @param $ofs3       stage3 output pointer
   * @param $old_schema original schema
   * @param $new_schema new schema
   */
  // @TODO: pull up
  public static function diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table_target = null, $new_table_target = null) {
    static::create_tables($ofs1, $old_schema, $new_schema, $old_table_target, $new_table_target);
    
    // were specific tables passed?
    if ( $old_table_target !== null || $new_table_target !== null ) {
      $old_table = $old_table_target;
      $new_table = $new_table_target;

      if ( $old_table && $new_table) {
        static::update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table);
      }
    }
    else {
      foreach(dbx::get_tables($new_schema) as $new_table) {
        if ( !$old_schema ) {
          // old_schema not defined
          continue;
        }
  
        $old_table = dbx::get_table($old_schema, $new_table['name']);
        
        dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table);
        
        if ( !$old_table ) {
          // old_table not defined
          continue;
        }
  
        static::update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table);
      }
    }
  }

  /**
   * Outputs commands for creation of new tables.
   *
   * @param $ofs         output file pointer
   * @param $old_schema  original schema
   * @param $new_schema  new schema
   */
  // @TODO: pull up
  private static function create_tables($ofs, $old_schema, $new_schema, $old_table = null, $new_table = null) {
    foreach(dbx::get_tables($new_schema) as $table) {
      if ( $new_table != null ) {
        if ( strcasecmp($table['name'], $new_table['name']) != 0 ) {
          continue;
        }
      }
      if (($old_schema == null) || !mysql5_schema::contains_table($old_schema, $table['name'])) {
        if ( !dbsteward::$ignore_oldname && mysql5_diff_tables::is_renamed_table($old_schema, $new_schema, $table) ) {
          // oldName renamed table ? rename table instead of create new one
          $old_table_name = mysql5::get_fully_qualified_table_name($new_schema['name'], $table['oldName']);
          // ALTER TABLE ... RENAME TO does not accept schema qualifiers when renaming a table
          // ALTER TABLE message.message_report RENAME TO report ;
          $new_table_name = mysql5::get_quoted_table_name($table['name']);
          $ofs->write("-- table rename from oldName specification" . "\n"
            . "ALTER TABLE $old_table_name RENAME TO $new_table_name ;" . "\n");
        }
        else {
          $ofs->write(mysql5_table::get_creation_sql($new_schema, $table, dbsteward::$quote_column_names) . "\n");
        }
      }
    }
  }

  /**
   * Outputs commands for addition, removal and modifications of
   * table columns.
   *
   * @param $ofs1       stage1 output file segmenter
   * @param $ofs3       stage3 output file segmenter
   * @param $old_table  original table
   * @param $new_table  new table
   */
  private static function update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table) {
    $commands = array();
    $drop_defaults_columns = array();
    static::add_drop_table_columns($commands, $old_table, $new_table);
    static::add_create_table_columns($commands, $old_table, $new_schema, $new_table, $drop_defaults_columns);
    static::add_modify_table_columns($commands, $old_schema, $old_table, $new_schema, $new_table, $drop_defaults_columns);

    if (count($commands) > 0) {
      // do 'pre' 'entire' statements before aggregate table alterations
      for($i=0; $i < count($commands); $i++) {
        if ( $commands[$i]['stage'] == 'BEFORE1' ) {
          $ofs1->write($commands[$i]['command'] . "\n");
        }
        else if ( $commands[$i]['stage'] == 'BEFORE3' ) {
          $ofs3->write($commands[$i]['command'] . "\n");
        }
      }

      $quotedTableName = mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']);

      $stage1_sql = '';
      $stage3_sql = '';

      for($i=0; $i < count($commands); $i++) {
        if ( !isset($commands[$i]['stage']) || !isset($commands[$i]['command']) ) {
          var_dump($commands[$i]);
          throw new exception("bad command format");
        }

        if ( $commands[$i]['stage'] == '1' ) {
          // we have a stage 1 alteration to make
          // do the alter table prefix if we haven't yet
          if ( strlen($stage1_sql) == 0 ) {
            $stage1_sql = "ALTER TABLE " . $quotedTableName . "\n";
          }
          $stage1_sql .= $commands[$i]['command'] . " ,\n";
        }
        else if ( $commands[$i]['stage'] == '3' ) {
          // we have a stage 3 alteration to make
          // do the alter table prefix if we haven't yet
          if ( strlen($stage3_sql) == 0 ) {
            $stage3_sql = "ALTER TABLE " . $quotedTableName . "\n";
          }
          $stage3_sql .= $commands[$i]['command'] . " ,\n";
        }
      }

      if ( strlen($stage1_sql) > 0 ) {
        $stage1_sql = substr($stage1_sql, 0, -3) . ";\n";
        $ofs1->write($stage1_sql);
      }
      if ( strlen($stage3_sql) > 0 ) {
        $stage3_sql = substr($stage3_sql, 0, -3) . ";\n";
        $ofs3->write($stage3_sql);
      }

      if (count($drop_defaults_columns) > 0) {
        $ofs1->write("\n");
        $ofs1->write("ALTER TABLE " . $quotedTableName . "\n");

        $alters = array_map(function($c){
          return "\tALTER COLUMN " . mysql5::get_quoted_column_name($c['name']) . " DROP DEFAULT";
        }, $drop_defaults_columns);
        $ofs->write(implode(",\n", $alters).";\n");
      }

      // do 'post' 'entire' statements immediately following aggregate table alterations
      for($i=0; $i < count($commands); $i++) {
        if ( $commands[$i]['stage'] == 'BEFORE1' ) {
          // already taken care of in earlier entire command output loop
        }
        else if ( $commands[$i]['stage'] == 'BEFORE3' ) {
          // already taken care of in earlier entire command output loop
        }
        else if ( $commands[$i]['stage'] == '1' ) {
          // already taken care of in earlier command aggregate loop
        }
        else if ( $commands[$i]['stage'] == '3' ) {
          // already taken care of in earlier command aggregate loop
        }
        else if ( $commands[$i]['stage'] == 'AFTER1' ) {
          $ofs1->write($commands[$i]['command'] . "\n");
        }
        else if ( $commands[$i]['stage'] == 'AFTER3' ) {
          $ofs3->write($commands[$i]['command'] . "\n");
        }
        else {
          throw new exception("Unknown stage " . $commands[$i]['stage'] . " during table " . $quotedTableName . " updates");
        }
      }
    }
  }

  /**
   * Adds commands for removal of columns to the list of commands.
   *
   * @param commands list of commands
   * @param old_table original table
   * @param new_table new table
   */
  // @TODO: pull up
  private static function add_drop_table_columns(&$commands, $old_table, $new_table) {
    foreach(dbx::get_table_columns($old_table) as $old_column) {
      if (!mysql5_table::contains_column($new_table, $old_column['name'])) {
        if ( !dbsteward::$ignore_oldname && ($renamed_column_name = mysql5_table::column_name_by_old_name($new_table, $old_column['name'])) !== false ) {
          // table indicating oldName = table['name'] present in new schema? don't do DROP statement
          $old_table_name = mysql5::get_quoted_table_name($old_table['name']);
          $old_column_name = mysql5::get_quoted_column_name($old_column['name']);
          $commands[] = array(
            'stage' => 'AFTER3',
            'command' => "-- $old_table_name DROP COLUMN $old_column_name omitted: new column $renamed_column_name indicates it is the replacement for " . $old_column_name
          );
        }
        else {
          //echo "NOTICE: add_drop_table_columns()  " . $new_table['name'] . " does not contain " . $old_column['name'] . "\n";
          $commands[] = array(
            'stage' => '3',
            'command' => "\tDROP COLUMN " . mysql5::get_quoted_column_name($old_column['name'])
          );
        }
      }
    }
  }

  /**
   * Adds commands for creation of new columns to the list of
   * commands.
   *
   * @param commands list of commands
   * @param old_table original table
   * @param new_table new table
   * @param drop_defaults_columns list for storing columns for which default value should be dropped
   */
  // @TODO: pull up
  private static function add_create_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns) {
    foreach(dbx::get_table_columns($new_table) as $new_column) {
      if (!mysql5_table::contains_column($old_table, $new_column['name'])) {
        if ( !dbsteward::$ignore_oldname && mysql5_diff_tables::is_renamed_column($old_table, $new_table, $new_column) ) {
          // oldName renamed column ? rename column instead of create new one
          $old_column_name = mysql5::get_quoted_column_name($new_column['oldName']);
          // $new_column_name = mysql5::get_quoted_column_name($new_column['name']);
          $to = mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults);
          $commands[] = array(
            'stage' => 'AFTER1',
            'command' => "-- column rename from oldName specification\n"
              . "ALTER TABLE " . mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name'])
              . " CHANGE COLUMN $old_column_name $to;"
          );
          continue;
        }
        
        // notice $include_null_definition is false
        // this is because ADD COLUMNs with NOT NULL will fail when there are existing rows

        $commands[] = array(
          'stage' => '1',
          'command' => "\tADD COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults, false)
        );
        // instead we put the NOT NULL defintion in stage3 schema changes once data has been updated in stage2 data
        if ( ! pgsql8_column::null_allowed($new_table, $new_column) ) {
          $commands[] = array(
            'stage' => '3',
            'command' => "\tMODIFY COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults)
          );
          // also, if it's defined, default the column in stage 1 so the SET NULL will actually pass in stage 3
          if ( strlen($new_column['default']) > 0 ) {
            $commands[] = array(
              'stage' => 'AFTER1',
              'command' => "UPDATE " . mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name'])
                . " SET " . mysql5::get_quoted_column_name($new_column['name']) . " = DEFAULT"
                . " WHERE " . mysql5::get_quoted_column_name($new_column['name']) . " IS NULL;"
            );
          }

        }

        if (mysql5_diff::$add_defaults && !mysql5_column::null_allowed($new_table, $new_column)) {
          $drop_defaults_columns[] = $new_column;
        }

        // some columns need filled with values before any new constraints can be applied
        // this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute
        $db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']);
        if ( $db_doc_new_schema ) {
          $db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']);
          if ( $db_doc_new_table ) {
            $db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']);
            if ( $db_doc_new_column ) {
              if ( isset($db_doc_new_column['beforeAddStage1']) ) {
                $commands[] = array(
                  'stage' => 'BEFORE1',
                  'command' => trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition"
                );
              }
              if ( isset($db_doc_new_column['afterAddStage1']) ) {
                $commands[] = array(
                  'stage' => 'AFTER1',
                  'command' => trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition"
                );
              }
              if ( isset($db_doc_new_column['beforeAddStage3']) ) {
                $commands[] = array(
                  'stage' => 'BEFORE3',
                  'command' => trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition"
                );
              }
              if ( isset($db_doc_new_column['afterAddStage3']) ) {
                $commands[] = array(
                  'stage' => 'AFTER3',
                  'command' => trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition"
                );
              }
            }
            else {
              throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found");
            }
          }
          else {
            throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found");
          }
        }
        else {
          throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found");
        }
      }
    }
  }

  /**
   * Adds commands for modification of columns to the list of
   * commands.
   *
   * @param commands list of commands
   * @param old_table original table
   * @param new_table new table
   * @param drop_defaults_columns list for storing columns for which default value should be dropped
   */
  // @TODO: pull up
  private static function add_modify_table_columns(&$commands, $old_schema, $old_table, $new_schema, $new_table, &$drop_defaults_columns) {
    foreach(dbx::get_table_columns($new_table) as $new_column) {
      if (!mysql5_table::contains_column($old_table, $new_column['name'])) {
        continue;
      }
      if ( !dbsteward::$ignore_oldname && mysql5_diff_tables::is_renamed_column($old_table, $new_table, $new_column) ) {
        // oldName renamed column ? skip definition diffing on it, it is being renamed
        continue;
      }

      $old_column = dbx::get_table_column($old_table, $new_column['name']);
      $new_column_name = mysql5::get_quoted_column_name($new_column['name']);

      $old_column_type = null;
      if ( $old_column ) {
        $old_column_type = mysql5_column::column_type(dbsteward::$old_database, $old_schema, $old_table, $old_column);
      }
      $new_column_type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column);

      if ( strcmp($old_column_type, $new_column_type) != 0 ) {
        // @TODO: Implement type conversion works-alike to pgsql's ALTER COLUMN ___ TYPE ___ USING ___

        $commands[] = array(
          'stage' => '1',
          'command' => "  MODIFY COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mysql5_diff::$add_defaults)
            . " /* TYPE change - table: {$new_table['name']} original: {$old_column['type']} new: $new_column_type */"
        );
      }

      $old_default = isset($old_column['default']) ? $old_column['default'] : '';
      $new_default = isset($new_column['default']) ? $new_column['default'] : '';

      if (strcmp($old_default, $new_default) != 0) {
        if (strlen($new_default) == 0) {
          // if the default value was gotten rid of
          $commands[] = array(
            'stage' => '1',
            'command' => "\tALTER COLUMN $new_column_name DROP DEFAULT"
          );
        }
        else {
          // if the default value changed
          $commands[] = array(
            'stage' => '1',
            'command' => "\tALTER COLUMN $new_column_name SET DEFAULT $new_default"
          );
        }
      }

      // note that no null attribute is a true null attribute
      if ( strcasecmp($old_column['null'] ?: 'true', $new_column['null'] ?: 'true') != 0 ) {
        if (mysql5_column::null_allowed($new_table, $new_column)) {
          // if the column is now allowed to be null, redefine it (because mysql won't let you just change NULL/NOT NULL)
          $commands[] = array(
            'stage' => '1',
            'command' => "\tMODIFY COLUMN " . mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, FALSE)
{'fraction_non_alphanumeric': 0.1460989217471743, 'fraction_numerical': 0.00832538374815714, 'mean_word_length': 2.564554353426069, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 97, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6536798', 'n_tokens_mistral': 10808, 'n_tokens_neox': 10731, 'n_words': 2332}