
#include "stdafx.h"

#ifndef _WIN32
#include <pcre.h>
#include <stdio.h>
#endif

#include "grt/common.h"

#include "module_db_mysql.h"
#include "diffchange.h"
#include "grtdiff.h"
#include "grtdb/diff_dbobjectmatch.h"
#include "db_mysql_diffsqlgen.h"
#include "db_mysql_params.h"

#include "db_mysql_diffsqlgen_grant.h"
#include "db_mysql_catalog_report.h"


using namespace grt;

static std::string get_table_old_name(db_mysql_TableRef table)
{
  return std::string("`").append(table->owner()->name().c_str()).append("`.`").append(table->oldName().c_str()).append("` ");
}

inline std::string get_name(const GrtNamedObjectRef object, const bool use_short_names){
  return use_short_names?
    std::string("`").append(object->name().c_str()).append("`"):
    get_qualified_schema_object_name(object);
  
};


DiffSQLGeneratorBEActionInterface::~DiffSQLGeneratorBEActionInterface()
{}

namespace {

class TextPadding
{
  int padding;
  int increment;

  std::string padding_text;

  void rebuild_padding_text() 
  { 
    padding_text= std::string(padding, ' '); 
  }

public:
  explicit TextPadding(int inc) 
    : padding(0), increment(inc)
  {}

  TextPadding& operator ++() 
  { 
    padding += increment;
    rebuild_padding_text();
    return *this;
  }
  
  TextPadding& operator --()  
  { 
    padding -= increment; 
    rebuild_padding_text();
    return *this;
  }
  
  std::string& pad(std::string& text)
  {
    return text.append(padding_text);
  }
};

static std::string generate_single_partition(db_mysql_PartitionDefinitionRef part, bool is_range)
{
  class Partition_options
  {
  public:
    static void generate(db_mysql_PartitionDefinitionRef part, std::string& sql)
    {
      if(strlen(part->comment().c_str()))
        sql.append(" COMMENT = '").append(bec::escape_sql_string(part->comment().c_str())).append("'");

      if(strlen(part->dataDirectory().c_str()))
        sql.append(" DATA DIRECTORY = '").append(bec::escape_sql_string(part->dataDirectory().c_str())).append("'");

      if(strlen(part->indexDirectory().c_str()))
        sql.append(" INDEX DIRECTORY = '").append(bec::escape_sql_string(part->indexDirectory().c_str())).append("'");

      if(strlen(part->maxRows().c_str()))
        sql.append(" MAX_ROWS = ").append(bec::escape_sql_string(part->maxRows().c_str()));

      if(strlen(part->minRows().c_str()))
        sql.append(" MIN_ROWS = ").append(bec::escape_sql_string(part->minRows().c_str()));

      // TODO: process TABLESPACE and NODEGROUP cluster-specific options
    }
  };

  std::string sql;

  sql.append(" PARTITION ");

  sql.append(part->name().c_str()).append(" VALUES ");

  if(is_range)
    sql.append("LESS THAN (").append(part->value().c_str()).append(")");
  else
    sql.append("IN (").append(part->value().c_str()).append(")");

  Partition_options::generate(part, sql);

  if(part->subpartitionDefinitions().count() > 0)
  {
    sql.append(" (");
    for(size_t subsz= part->subpartitionDefinitions().count(), j= 0; j < subsz; j++)
    {
      if(j > 0)
        sql.append(",");

      db_mysql_PartitionDefinitionRef subpart= part->subpartitionDefinitions().get(j);
      sql.append(" SUBPARTITION ").append(subpart->name().c_str());

      Partition_options::generate(subpart, sql);
    }
    sql.append(")");
  }

  return sql;
}

static std::string generate_drop_partitions(const std::list<std::string>& part_names)
{
  std::string sql(" DROP PARTITION ");
  bool first= true;

  for(std::list<std::string>::const_iterator e= part_names.end(), 
    it= part_names.begin(); it != e; it++)
  {
    if(first)
      first= false;
    else
      sql.append(", ");

    sql.append(*it);
  }

  return sql;
}

static std::string generate_create(db_mysql_ColumnRef column)
{
  std::string sql;

  sql.append("`").append(column->name().c_str()).append("` ");

  sql.append(column->formattedType());

  sql.append(" ");

  if (!(*column->characterSetName()).empty())
    sql
      .append("CHARACTER SET '")
      .append(column->characterSetName())
      .append("' ");
  if (!(*column->collationName()).empty())
    sql
      .append("COLLATE '")
      .append(column->collationName())
      .append("' ");

  grt::StringListRef flags= column->flags();
  size_t flags_count= flags.count();
  for(size_t j= 0; j < flags_count; j++)
  {
    sql.append(flags.get(j).c_str()).append(" ");
  }

  if(column->isNotNull())
    sql.append("NOT NULL ");
  else
    sql.append("NULL ");

  if(column->defaultValueIsNull())
    sql.append("DEFAULT NULL ");
  else if(column->defaultValue().is_valid() && column->defaultValue().c_str() && (strlen(column->defaultValue().c_str()) > 0))
    sql.append("DEFAULT ").append(column->defaultValue().c_str()).append(" ");

  if (column->autoIncrement())
  {
    db_SimpleDatatypeRef columnType;

    // Determine actually used column type first.
    if (column->userType().is_valid() && column->userType()->actualType().is_valid())
      columnType= column->userType()->actualType();
    else
      if (column->simpleType().is_valid() && column->simpleType()->group().is_valid())
        columnType= column->simpleType();
    
    if (columnType.is_valid() && columnType->group().is_valid()
      && !strcmp(columnType->group()->name().c_str(), "numeric"))
      sql.append("AUTO_INCREMENT ");
  }
  
  if(strlen(column->comment().c_str()) > 0)
    sql.append("COMMENT '").append(bec::escape_sql_string(column->comment())).append("' ");

  // TODO:
  // (?) [reference_definition]
  // [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
  // [STORAGE {DISK|MEMORY}]

  return sql;
}

static std::string generate_create(db_mysql_IndexRef index, std::string table_q_name, bool separate_index)
{
  std::string index_sql;
  bool pk= (index->isPrimary() != 0);
  char itoa_buf[32];

  separate_index= (!pk && separate_index);  // pk cannot be added via CREATE INDEX

  if(pk)
  {
    index_sql.append("PRIMARY KEY ");
  }
  else if(index->unique() != 0)
  {
    index_sql.append("UNIQUE INDEX ");
  }
  else if(strlen(index->indexType().c_str()) > 0)
  {
    if(stricmp(index->indexType().c_str(), "PRIMARY") == 0)
      index_sql.append("PRIMARY KEY ");
    if (stricmp(index->indexType().c_str(), "FOREIGN") == 0)
      index_sql.append("INDEX ");
    else
    {
      index_sql.append(index->indexType().c_str()).append(" ");

      if(stricmp(index->indexType().c_str(), "INDEX") != 0)
        index_sql.append("INDEX ");
    }
  }
  else
  {
    index_sql.append("INDEX ");
  }
  
  if(!pk)
  {
    const char *name= index->name().c_str();
    if (name && ('\0' != name[0]))
      index_sql.append(fmt("`%s` ", name));
  }

  if(strlen(index->indexKind().c_str()))
    index_sql.append("USING ").append(index->indexKind().c_str()).append(" ");

  if(separate_index)
    index_sql.append("ON ").append(table_q_name).append(" ");

  index_sql.append("(");

  grt::ListRef<db_mysql_IndexColumn> ind_columns= index->columns();
  for(size_t index_column_count= ind_columns.count(), j= 0; j < index_column_count; j++)
  {
    db_IndexColumnRef ind_column= ind_columns.get(j);
    
    if(j > 0)
      index_sql.append(", ");

    db_ColumnRef col= ind_column->referencedColumn();
    if(col.is_valid())
      index_sql.append("`").append(col->name().c_str()).append("`");
    
    if(ind_column->columnLength() > 0)
      index_sql.append("(").append(itoa(ind_column->columnLength(), itoa_buf, 10)).append(")");
    
    if(!pk)
      index_sql.append((ind_column->descend() == 0 ? " ASC" : " DESC"));
  }
  
  if (index->keyBlockSize())
    index_sql.append(" KEY_BLOCK_SIZE=").append(itoa(index->keyBlockSize(), itoa_buf, 10));

  if (index->withParser().is_valid() && *index->withParser().c_str())
    index_sql.append(" WITH PARSER ").append(index->withParser());

  index_sql.append(") ");

  return index_sql;
}

static std::string generate_create(db_mysql_ForeignKeyRef fk, TextPadding& padding, bool use_short_names)
{
  std::string sql;

  sql.append("CONSTRAINT `").append(fk->name().c_str()).append("`\n");

  ++padding;
  padding.pad(sql).append("FOREIGN KEY (");

  grt::ListRef<db_Column> fk_columns= fk->columns();
  for(size_t fk_column_count= fk_columns.count(), j= 0; j < fk_column_count; j++)
  {
    if(j != 0)
      sql.append(", ");

    db_ColumnRef fk_column= fk_columns.get(j);

    sql.append("`").append(fk_column->name().c_str()).append("` ");
  }

  
  sql.append(")\n");
  padding.pad(sql).append("REFERENCES `");
  if(fk->referencedTable().is_valid())
  {
    // Omit schema name if we use short names and the referenced table is in the same schema as the
    // referencing table.
    if (use_short_names && (fk->owner()->owner() == fk->referencedTable()->owner()))
      sql.append(fk->referencedTable()->name().c_str());
    else
      sql.append(fk->referencedTable()->owner()->name().c_str()).append("`.`").append(fk->referencedTable()->name().c_str());
  }
  sql.append("` (");

  grt::ListRef<db_Column> ref_fk_columns= fk->referencedColumns();
  for(size_t ref_fk_column_count= ref_fk_columns.count(), j= 0; j < ref_fk_column_count; j++)
  {
    if(j > 0)
      sql.append(", ");

    db_ColumnRef ref_fk_column= ref_fk_columns.get(j);
    if(!ref_fk_column.is_valid())
      continue;
    sql.append("`").append(ref_fk_column->name().c_str()).append("` ");
  }

  sql.append(")");

  if(strlen(fk->deleteRule().c_str()))
  {
    sql.append("\n");
    padding.pad(sql).append("ON DELETE ").append(fk->deleteRule().c_str());
  }

  if(strlen(fk->updateRule().c_str()))
  {
    sql.append("\n");
    padding.pad(sql).append("ON UPDATE ").append(fk->updateRule().c_str());
  }

  --padding;
  return sql;
}

static std::string generate_add_index(db_mysql_IndexRef index)
{
/*
  | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
*/
  return std::string("ADD ").append(generate_create(index, "", false));
}

static std::string generate_drop_index(db_mysql_IndexRef index)
{
/*
  | DROP {INDEX|KEY} index_name
*/
  if(index->isPrimary())
    return std::string("DROP PRIMARY KEY ");

  std::string index_name;
  {
    const char *name= index->name().c_str();
    if (name && ('\0' != name[0]))
      index_name= fmt("`%s` ", name);
    else
      index_name= " ";
  }    
  return fmt("DROP INDEX %s", index_name.c_str());
}

class ActionGenerateSQL : public DiffSQLGeneratorBEActionInterface
{
  TextPadding padding;

  std::string sql;
  std::string comma;
  std::string table_q_name;
  char itoa_buf[32];
  size_t empty_length;
  bool first_column, first_index,
  first_change, first_fk_create, first_fk_drop;

  std::string fk_add_sql;
  std::string fk_drop_sql;

  std::list<std::string> partitions_to_drop;
  std::list<std::string> partitions_to_change;
  std::list<std::string> partitions_to_add;

  grt::DictRef target_map;
  grt::StringListRef target_list;
  grt::ListRef<GrtNamedObject> target_object_list;

  void remember_alter(const GrtNamedObjectRef &obj, const std::string &sql);
  void remember(const GrtNamedObjectRef &obj, const std::string &sql,const bool front = false);

  void alter_table_property(std::string& to, const std::string& name, const std::string& value);

public:
  ActionGenerateSQL(grt::ValueRef target, grt::ListRef<GrtNamedObject> obj_list);
  virtual ~ActionGenerateSQL();

  // create table
  void create_table_props_begin(db_mysql_TableRef);
  void create_table_props_end(db_mysql_TableRef);

  void create_table_columns_begin(db_mysql_TableRef);
  void create_table_column(db_mysql_ColumnRef);
  void create_table_columns_end(db_mysql_TableRef);

  void create_table_indexes_begin(db_mysql_TableRef);
  void create_table_index(db_mysql_IndexRef, bool gen_create_index);
  void create_table_indexes_end(db_mysql_TableRef);

  void create_table_fks_begin(db_mysql_TableRef);
  void create_table_fk(db_mysql_ForeignKeyRef);
  void create_table_fks_end(db_mysql_TableRef);

  void create_table_engine(grt::StringRef);
  void create_table_next_auto_inc(grt::StringRef);
  void create_table_password(grt::StringRef);
  void create_table_delay_key_write(grt::IntegerRef);
  void create_table_charset(grt::StringRef);
  void create_table_collate(grt::StringRef);
  void create_table_merge_union(grt::StringRef);
  void create_table_merge_insert(grt::StringRef);
  void create_table_pack_keys(grt::StringRef);
  void create_table_checksum(grt::IntegerRef);
  void create_table_row_format(grt::StringRef);
  void create_table_avg_row_length(grt::StringRef);
  void create_table_min_rows(grt::StringRef);
  void create_table_max_rows(grt::StringRef);
  void create_table_comment(grt::StringRef);
  void create_table_data_dir(grt::StringRef);
  void create_table_index_dir(grt::StringRef);

  // drop table
  void drop_table(db_mysql_TableRef);

  // schema drop/create
  void create_schema(db_mysql_SchemaRef);
  void drop_schema(db_mysql_SchemaRef);

  // alter schema
  void alter_schema_props_begin(db_mysql_SchemaRef);
  void alter_schema_default_charset(db_mysql_SchemaRef, grt::StringRef value);
  void alter_schema_default_collate(db_mysql_SchemaRef, grt::StringRef value);
  void alter_schema_name(db_mysql_SchemaRef, grt::StringRef value);
  void alter_schema_props_end(db_mysql_SchemaRef);

  void alter_table_props_begin(db_mysql_TableRef);
  void alter_table_name(db_mysql_TableRef, grt::StringRef);
  void alter_table_engine(db_mysql_TableRef, grt::StringRef);
  void alter_table_next_auto_inc(db_mysql_TableRef, grt::StringRef);
  void alter_table_password(db_mysql_TableRef, grt::StringRef);
  void alter_table_delay_key_write(db_mysql_TableRef, grt::IntegerRef);
  void alter_table_charset(db_mysql_TableRef, grt::StringRef);
  void alter_table_collate(db_mysql_TableRef, grt::StringRef);
  void alter_table_comment(db_mysql_TableRef, grt::StringRef);
  void alter_table_merge_union(db_mysql_TableRef, grt::StringRef);
  void alter_table_merge_insert(db_mysql_TableRef, grt::StringRef);
  void alter_table_pack_keys(db_mysql_TableRef, grt::StringRef);
  void alter_table_checksum(db_mysql_TableRef, grt::IntegerRef);
  void alter_table_row_format(db_mysql_TableRef, grt::StringRef);
  void alter_table_avg_row_length(db_mysql_TableRef, grt::StringRef);
  void alter_table_min_rows(db_mysql_TableRef, grt::StringRef);
  void alter_table_max_rows(db_mysql_TableRef, grt::StringRef);
  void alter_table_connection_string(db_mysql_TableRef, grt::StringRef);
  void alter_table_generate_partitioning(db_mysql_TableRef table, 
                                         const std::string& part_type,
                                         const std::string& part_expr, 
                                         int part_count,
                                         const std::string& subpart_type, 
                                         const std::string& subpart_expr,
                                         grt::ListRef<db_mysql_PartitionDefinition> part_defs);
  void alter_table_drop_partitioning(db_mysql_TableRef table);
  void alter_table_add_partition(db_mysql_PartitionDefinitionRef part, bool is_range);
  void alter_table_reorganize_partition(db_mysql_PartitionDefinitionRef old_part,
                                        db_mysql_PartitionDefinitionRef new_part,
                                        bool is_range);
  void alter_table_drop_partition(const std::string& part_name);
  void alter_table_partition_count(db_mysql_TableRef, grt::IntegerRef);
  void alter_table_partition_definitions(db_mysql_TableRef, grt::StringRef);
  void alter_table_props_end(db_mysql_TableRef);

  void alter_table_columns_begin(db_mysql_TableRef);
  void alter_table_add_column(db_mysql_TableRef, std::map<std::string, std::string>, 
                              db_mysql_ColumnRef, db_mysql_ColumnRef after);
  void alter_table_drop_column(db_mysql_TableRef, db_mysql_ColumnRef);
  void alter_table_change_column(db_mysql_TableRef table, db_mysql_ColumnRef org_col, 
                                 db_mysql_ColumnRef mod_col, db_mysql_ColumnRef after,
                                 bool modified, 
                                 std::map<std::string, std::string> column_rename_map);
  void alter_table_columns_end(db_mysql_TableRef);

  void alter_table_indexes_begin(db_mysql_TableRef);
  void alter_table_add_index(db_mysql_IndexRef);
  void alter_table_drop_index(db_mysql_IndexRef);
  void alter_table_indexes_end(db_mysql_TableRef);

  void alter_table_fks_begin(db_mysql_TableRef);
  void alter_table_add_fk(db_mysql_ForeignKeyRef);
  void alter_table_drop_fk(db_mysql_ForeignKeyRef);
  void alter_table_fks_end(db_mysql_TableRef);

  // triggers
  void create_trigger(db_mysql_TriggerRef, bool for_alter);
  void drop_trigger(db_mysql_TriggerRef, bool for_alter);

  // views
  void create_view(db_mysql_ViewRef);
  void drop_view(db_mysql_ViewRef);

  // routines
  void create_routine(db_mysql_RoutineRef, bool for_alter);
  void drop_routine(db_mysql_RoutineRef, bool for_alter);

  // users
  void create_user(db_UserRef);
  void drop_user(db_UserRef);

  inline std::string get_name(GrtNamedObjectRef object) const{return ::get_name(object,use_short_names);};
};

ActionGenerateSQL::ActionGenerateSQL(grt::ValueRef target, grt::ListRef<GrtNamedObject> obj_list)
  : padding(2)
{
  if(target.type() == DictType)
  {
    this->target_list= grt::StringListRef();
    this->target_map= grt::DictRef::cast_from(target);
  }
  else if(target.type() == ListType)
  {
    this->target_list= grt::StringListRef::cast_from(target);
    this->target_map= grt::DictRef();
  }
  this->target_object_list= obj_list;
}

ActionGenerateSQL::~ActionGenerateSQL()
{}

// create table methods

void ActionGenerateSQL::create_table_props_begin(db_mysql_TableRef table)
{
  sql.assign("CREATE ");

  table_q_name = get_name(table);

  if(table->isTemporary())
    sql.append("TEMPORARY ");

  sql.append(" TABLE IF NOT EXISTS ");
  sql.append(table_q_name).append(" (\n");
  ++padding;
}

void ActionGenerateSQL::create_table_props_end(db_mysql_TableRef table)
{
  remember(table, sql);
}

void ActionGenerateSQL::create_table_columns_begin(db_mysql_TableRef)
{
  first_column= true;
}

void ActionGenerateSQL::create_table_column(db_mysql_ColumnRef column)
{
  if(first_column)
    first_column= false;
  else
    sql.append(",\n");

  padding.pad(sql).append(generate_create(column));
}

void ActionGenerateSQL::create_table_columns_end(db_mysql_TableRef)
{
}

void ActionGenerateSQL::create_table_indexes_begin(db_mysql_TableRef)
{
}

void ActionGenerateSQL::create_table_index(db_mysql_IndexRef index, bool gen_create_index)
{
  std::string index_sql(generate_create(index, table_q_name, gen_create_index));

  if(gen_create_index)
  {
    index_sql= std::string("CREATE ").append(index_sql);
    remember(index, index_sql);
  }
  else
  {
    sql.append(",\n");
    padding.pad(sql).append(index_sql);
  }
}

void ActionGenerateSQL::create_table_indexes_end(db_mysql_TableRef)
{
}

void ActionGenerateSQL::create_table_fks_begin(db_mysql_TableRef)
{
}

void ActionGenerateSQL::create_table_fk(db_mysql_ForeignKeyRef fk)
{
  sql.append(",\n");
  padding.pad(sql).append(generate_create(fk, padding, use_short_names));

}

void ActionGenerateSQL::create_table_fks_end(db_mysql_TableRef)
{
  sql.append(")");
  --padding;
}

void ActionGenerateSQL::create_table_engine(grt::StringRef value)
{
  sql.append("\nENGINE = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_next_auto_inc(grt::StringRef value)
{
  sql.append("\nAUTO_INCREMENT = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_password(grt::StringRef value)
{
  sql.append("\nPASSWORD = '").append(value.c_str()).append("'");
}

void ActionGenerateSQL::create_table_delay_key_write(grt::IntegerRef value)
{
  sql.append("\nDELAY_KEY_WRITE = ").append(itoa(value, itoa_buf, 10));
}

void ActionGenerateSQL::create_table_charset(grt::StringRef value)
{
  sql.append("\nDEFAULT CHARACTER SET = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_collate(grt::StringRef value)
{
  sql.append("\nCOLLATE = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_merge_union(grt::StringRef value)
{
  sql.append("\nUNION = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_merge_insert(grt::StringRef value)
{
  sql.append("\nINSERT_METHOD = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_pack_keys(grt::StringRef value)
{
  sql.append("\nPACK_KEYS = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_checksum(grt::IntegerRef value)
{
  sql.append("\nCHECKSUM = ").append(itoa(value, itoa_buf, 10));
}

void ActionGenerateSQL::create_table_row_format(grt::StringRef value)
{
  sql.append("\nROW_FORMAT = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_avg_row_length(grt::StringRef value)
{
  sql.append("\nAVG_ROW_LENGTH = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_min_rows(grt::StringRef value)
{
  sql.append("\nMIN_ROWS = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_max_rows(grt::StringRef value)
{
  sql.append("\nMAX_ROWS = ").append(value.c_str());
}

void ActionGenerateSQL::create_table_comment(grt::StringRef value)
{
  sql.append("\nCOMMENT = '").append(escape_sql_string(value).c_str()).append("'");
}

void ActionGenerateSQL::create_table_data_dir(grt::StringRef value)
{
  sql.append("\nDATA DIRECTORY = '").append(value.c_str()).append("'");
}

void ActionGenerateSQL::create_table_index_dir(grt::StringRef value)
{
  sql.append("\nINDEX DIRECTORY = '").append(value.c_str()).append("'");
}

// drop table
void ActionGenerateSQL::drop_table(db_mysql_TableRef table)
{
  sql.clear();
  sql.append("DROP TABLE IF EXISTS ").append(get_name(table)).append(" ");
  remember(table, sql);
}

// schema

void ActionGenerateSQL::create_schema(db_mysql_SchemaRef schema)
{
  std::string schema_sql;
  schema_sql.append("CREATE SCHEMA IF NOT EXISTS `").append(schema->name().c_str()).append("` ");

  if(strlen(schema->defaultCharacterSetName().c_str()))
    schema_sql.append("DEFAULT CHARACTER SET ").append(schema->defaultCharacterSetName().c_str()).append(" ");

  if(strlen(schema->defaultCollationName().c_str()))
    schema_sql.append("COLLATE ").append(schema->defaultCollationName().c_str()).append(" ");

  remember(schema, schema_sql);
}

void ActionGenerateSQL::drop_schema(db_mysql_SchemaRef schema)
{
  std::string schema_sql;
  schema_sql.append("DROP SCHEMA IF EXISTS `").append(schema->name().c_str()).append("` ");
  remember(schema, schema_sql, true);
}

// alter schema methods

void ActionGenerateSQL::alter_schema_props_begin(db_mysql_SchemaRef schema)
{
  sql.clear();
}

void ActionGenerateSQL::alter_schema_name(db_mysql_SchemaRef schema, grt::StringRef value)
{
  std::string rename_sql("RENAME SCHEMA `");
  rename_sql += schema->name().c_str();
  rename_sql += "` TO `";
  rename_sql += value.c_str();
  rename_sql += "`";
  remember_alter(schema, rename_sql);
}

void ActionGenerateSQL::alter_schema_default_charset(db_mysql_SchemaRef schema, grt::StringRef value)
{
  sql.append(" DEFAULT CHARACTER SET ").append(value).append(" ");
}

void ActionGenerateSQL::alter_schema_default_collate(db_mysql_SchemaRef schema, grt::StringRef value)
{
  sql.append(" DEFAULT COLLATE ").append(value).append(" ");
}

void ActionGenerateSQL::alter_schema_props_end(db_mysql_SchemaRef schema)
{
  if(!sql.empty())
  {
    sql= std::string("ALTER SCHEMA `").append(schema->name().c_str()).append("` ").append(sql);
    remember_alter(schema, sql);
  }
}

// alter table

void ActionGenerateSQL::alter_table_props_begin(db_mysql_TableRef table)
{
  comma.clear();
  sql.assign("ALTER TABLE ");
  sql += get_table_old_name(table);
  empty_length= sql.length();

  partitions_to_drop.clear();
  partitions_to_change.clear();
  partitions_to_add.clear();
  first_change= true;
}

void ActionGenerateSQL::alter_table_property(std::string& to, const std::string& name, const std::string& value)
{
  if(first_change)
    first_change= false;
  else
    to.append(", ");

  to.append(name).append(value).append(" ");
}

void ActionGenerateSQL::alter_table_name(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(
    sql,
    "RENAME TO ", 
	use_short_names?
	std::string(" `").append(str.c_str()).append("`"):
	std::string(" `").append(table->owner()->name().c_str()).append("`.`").append(str.c_str()).append("`")
	);
}

void ActionGenerateSQL::alter_table_engine(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "ENGINE = ", str.c_str());
}

void ActionGenerateSQL::alter_table_next_auto_inc(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "AUTO_INCREMENT = ", str.c_str());
}

void ActionGenerateSQL::alter_table_password(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "PASSWORD  = '", std::string(str.c_str()).append("' "));
}

void ActionGenerateSQL::alter_table_delay_key_write(db_mysql_TableRef table, grt::IntegerRef n)
{
  alter_table_property(sql, "DELAY_KEY_WRITE  = ", itoa(n, itoa_buf, 10));
}

void ActionGenerateSQL::alter_table_charset(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "CHARACTER SET = ", str.c_str());
}

void ActionGenerateSQL::alter_table_collate(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "COLLATE = ", str.c_str());
}

void ActionGenerateSQL::alter_table_comment(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "COMMENT = '", std::string(str.c_str()).append("'"));
}

void ActionGenerateSQL::alter_table_merge_union(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "UNION = (", std::string(str.c_str()).append(") "));
}

void ActionGenerateSQL::alter_table_merge_insert(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "INSERT_METHOD = ", str.c_str());
}

void ActionGenerateSQL::alter_table_pack_keys(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "PACK_KEYS = ", str.c_str());
}

void ActionGenerateSQL::alter_table_checksum(db_mysql_TableRef table, grt::IntegerRef n)
{
  alter_table_property(sql, "CHECKSUM = ", itoa(n, itoa_buf, 10));
}

void ActionGenerateSQL::alter_table_row_format(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "ROW_FORMAT = ", str.c_str());
}

void ActionGenerateSQL::alter_table_avg_row_length(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "AVG_ROW_LENGTH = ", str.c_str());
}

void ActionGenerateSQL::alter_table_min_rows(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "MIN_ROWS = ", str.c_str());
}

void ActionGenerateSQL::alter_table_max_rows(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "MAX_ROWS = ", str.c_str());
}

void ActionGenerateSQL::alter_table_connection_string(db_mysql_TableRef table, grt::StringRef str)
{
  alter_table_property(sql, "CONNECTION = ", str.c_str());
}

// used by generate_create_partitioning and generate_add_partitioning
// generates full PARTITION BY clause
void ActionGenerateSQL::alter_table_generate_partitioning(
                                       db_mysql_TableRef table, 
                                       const std::string& part_type,
                                       const std::string& part_expr, 
                                       int part_count,
                                       const std::string& subpart_type, 
                                       const std::string& subpart_expr,
                                       grt::ListRef<db_mysql_PartitionDefinition> part_defs)
{
  bool is_range= (part_type.compare("RANGE") == 0);
  bool is_list= false;
  char itoa_buf[32];

  if(!is_range)
    is_list= (part_type.compare("LIST") == 0);

  std::string partition_sql(" PARTITION BY ");

  partition_sql
    .append(part_type)
    .append("(")
    .append(part_expr)
    .append(") PARTITIONS ")
    .append(itoa(part_count, itoa_buf, 10));

  if(is_range || is_list)
  {
    if(!subpart_type.empty())
    {
      partition_sql
        .append(" SUBPARTITION BY ")
        .append(subpart_type)
        .append("(")
        .append(subpart_expr)
        .append(") ");
    }

    partition_sql.append("(");

    for(size_t sz= part_defs.count(), i= 0; i < sz; i++)
    {
      if(i > 0)
        partition_sql.append(", ");

      db_mysql_PartitionDefinitionRef part= part_defs.get(i);
      partition_sql.append(generate_single_partition(part, is_range));
    }
    partition_sql.append(")");
  }

  sql.append(comma).append(partition_sql);  
}

void ActionGenerateSQL::alter_table_drop_partitioning(db_mysql_TableRef table)
{
  sql.append(" REMOVE PARTITIONING");
}

void ActionGenerateSQL::alter_table_reorganize_partition(
                                         db_mysql_PartitionDefinitionRef old_part,
                                         db_mysql_PartitionDefinitionRef new_part,
                                         bool is_range)
{
  std::string part_sql(" REORGANIZE PARTITION ");
  
  part_sql.append(old_part->name().c_str())
     .append(" INTO (")
     .append(generate_single_partition(new_part, is_range)) 
     .append(")");

  partitions_to_change.push_back(part_sql);
}

void ActionGenerateSQL::alter_table_drop_partition(const std::string& part_name)
{
  partitions_to_drop.push_back(part_name.c_str());
}

void ActionGenerateSQL::alter_table_add_partition(db_mysql_PartitionDefinitionRef part, bool is_range)
{
  partitions_to_add.push_back(
    std::string(" ADD PARTITION (")
    .append(generate_single_partition(part, is_range))
    .append(") "));
}

void ActionGenerateSQL::alter_table_partition_count(db_mysql_TableRef table, grt::IntegerRef newcount)
{
  // we get here only if partitionType was not changed, so we can rely on old type setting

  char buf[32];

  int oldcount= table->partitionCount();
  std::string part_type(table->partitionType().c_str());

  // for range/list partitions we use add/drop/reorganize partitions
  if((oldcount == newcount) || ((part_type.find("HASH") == std::string::npos)
      && (part_type.find("KEY") == std::string::npos)))
    return;

  std::string part_count_sql;

  if(oldcount > newcount) // merge
    part_count_sql.append(" COALESCE PARTITION ").append(itoa((oldcount - newcount), buf, 10));
  else  // split
    part_count_sql.append(" ADD PARTITION PARTITIONS ").append(itoa((newcount - oldcount), buf, 10));

  // partition count alone can be changed only for HASH/KEY partitions
  // generate_change_partition_count() will return empty string otherwise
  // for RANGE/LIST we ignore change of this attribute and rely solely on
  // partition definitions change
  if(!part_count_sql.empty())
  {
    sql.append(comma).append(part_count_sql);
    //partitions_processed= true;
  }
}

void ActionGenerateSQL::alter_table_partition_definitions(db_mysql_TableRef table, grt::StringRef str)
{}

void ActionGenerateSQL::alter_table_props_end(db_mysql_TableRef table)
{
  if(!partitions_to_drop.empty())
  {
    sql.append(generate_drop_partitions(partitions_to_drop));

    remember_alter(table, sql);
    sql.assign("ALTER TABLE ");
    sql.append(get_table_old_name(table));
  }

  if(!partitions_to_change.empty())
  {
    for(std::list<std::string>::const_iterator e= partitions_to_change.end(), 
      it= partitions_to_change.begin(); it != e; it++)
    {
      sql.append(*it);
      remember_alter(table, sql);
      sql.assign("ALTER TABLE ");
      sql.append(get_table_old_name(table));
    }
  }

  if(!partitions_to_add.empty())
  {
    for(std::list<std::string>::const_iterator e= partitions_to_add.end(), 
      it= partitions_to_add.begin(); it != e; it++)
    {
      sql.append(*it);
      remember_alter(table, sql);
      sql.assign("ALTER TABLE ");
      sql.append(get_table_old_name(table));
    }
  }

  if(sql.length() > empty_length)
    remember_alter(table, sql);
}

void ActionGenerateSQL::alter_table_columns_begin(db_mysql_TableRef)
{
  //first_column= true;
}

void ActionGenerateSQL::alter_table_add_column(db_mysql_TableRef table, 
                                               std::map<std::string, std::string> rename_map,
                                               db_mysql_ColumnRef column, 
                                               db_mysql_ColumnRef after)
{
  if(first_change)
    first_change= false;
  else
    sql.append(", ");

/*
  | ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
*/

  sql.append("ADD COLUMN ");
  sql.append(generate_create(column));
  sql.append(" ");

  if(after.is_valid())
  {
    //const char *after_sql= after->name().c_str();
    std::string after_name(after->name().c_str());
    std::map<std::string, std::string>::const_iterator it= 
      rename_map.find(after_name);
    if(it != rename_map.end())
      after_name= it->second;
    sql.append("AFTER `").append(after_name).append("` ");
  }
  else
  {
    sql.append("FIRST ");
  }

  //return sql;
}

void ActionGenerateSQL::alter_table_drop_column(db_mysql_TableRef, db_mysql_ColumnRef column)
{
  if(first_change)
    first_change= false;
  else
    sql.append(", ");

  sql += ("DROP COLUMN `");
  sql += column->name().c_str();
  sql += "` ";
}

void ActionGenerateSQL::alter_table_change_column(db_mysql_TableRef table, db_mysql_ColumnRef org_col, 
                                                  db_mysql_ColumnRef mod_col, db_mysql_ColumnRef after,
                                                  bool modified, 
                                                  std::map<std::string, std::string> column_rename_map)
{
  if(first_change)
    first_change= false;
  else
    sql.append(", ");

  /*
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  */

  sql.append("CHANGE COLUMN `");
  sql.append(org_col->oldName().c_str()).append("` ");
  if(modified)
    sql.append(generate_create(org_col));
  else
    sql.append(generate_create(mod_col));
  sql.append(" ");

  if(!modified)
  {
    if(after.is_valid())
    {
      std::string after_name(after->name().c_str());
      std::map<std::string, std::string>::const_iterator it= 
        column_rename_map.find(after_name);
      if(it != column_rename_map.end())
        after_name= it->second;
      sql.append("AFTER `").append(after_name).append("` ");
    }
    else
    {
      sql.append("FIRST ");
    }
  }
}

void ActionGenerateSQL::alter_table_columns_end(db_mysql_TableRef)
{}

void ActionGenerateSQL::alter_table_indexes_begin(db_mysql_TableRef)
{}

void ActionGenerateSQL::alter_table_add_index(db_mysql_IndexRef index)
{
  sql.append("\n");
  padding.pad(sql);

  if(first_change)
    first_change= false;
  else
    sql.append(", ");  

  sql.append(generate_add_index(index));
}

void ActionGenerateSQL::alter_table_drop_index(db_mysql_IndexRef index)
{
  sql.append("\n");
  padding.pad(sql);

  if(first_change)
    first_change= false;
  else
    sql.append(", ");  

  sql.append(generate_drop_index(index));
}

void ActionGenerateSQL::alter_table_indexes_end(db_mysql_TableRef)
{
}

void ActionGenerateSQL::alter_table_fks_begin(db_mysql_TableRef)
{
  first_fk_create= true;
  first_fk_drop= true;
  fk_add_sql.clear();
  fk_drop_sql.clear();
}

void ActionGenerateSQL::alter_table_add_fk(db_mysql_ForeignKeyRef fk)
{
  if(first_fk_create)
    first_fk_create= false;
  else
    fk_add_sql.append(", ");

/*
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
*/
  fk_add_sql += "\n  ADD ";
  fk_add_sql += generate_create(fk, padding, use_short_names);
}

void ActionGenerateSQL::alter_table_drop_fk(db_mysql_ForeignKeyRef fk)
{
  if(first_fk_drop)
    first_fk_drop= false;
  else
    fk_drop_sql.append(", ");

/*
  | DROP FOREIGN KEY fk_symbol
*/
  fk_drop_sql += "DROP FOREIGN KEY `";
  fk_drop_sql += fk->name().c_str();
  fk_drop_sql += "` ";
}

void ActionGenerateSQL::alter_table_fks_end(db_mysql_TableRef table)
{
  if(!fk_add_sql.empty() && !fk_drop_sql.empty())
  {
    if(!first_change)
      sql.append(", ");
    sql.append(fk_drop_sql);
    remember_alter(table, sql);  // let DROP go first
    sql.assign("ALTER TABLE ");
    sql.append(get_table_old_name(table));
    sql.append(fk_add_sql);
    comma.clear();
    first_change= false;
  }
  else if(!fk_add_sql.empty())
  {
    if(first_change)
      first_change= false;
    else
      sql.append(", ");
    sql.append(fk_add_sql);    
  }
  else if(!fk_drop_sql.empty())
  {
    if(first_change)
      first_change= false;
    else
      sql.append(", ");
    sql.append(fk_drop_sql);
  }
}

// triggers

void ActionGenerateSQL::create_trigger(db_mysql_TriggerRef trigger, bool for_alter)
{
  std::string trigger_sql= trigger->sqlDefinition().c_str();

  if(for_alter)
    remember_alter(trigger, trigger_sql);
  else
    remember(trigger, trigger_sql);
}

void ActionGenerateSQL::drop_trigger(db_mysql_TriggerRef trigger, bool for_alter)
{
  std::string trigger_sql;
  trigger_sql.append("DROP TRIGGER IF EXISTS ").append(get_name(trigger)).append(" ");
  if(for_alter)
    remember_alter(trigger, trigger_sql);
  else
    remember(trigger, trigger_sql);
}

// views

void ActionGenerateSQL::create_view(db_mysql_ViewRef view)
{
  std::string view_def(view->sqlDefinition().c_str());
  const char *errptr;
  int erroffs;
  bool or_replace_present= false;
  int patres[3];

  pcre *patre= pcre_compile("^\\s*CREATE\\s+OR\\s+REPLACE\\s+", PCRE_CASELESS | PCRE_MULTILINE, &errptr, &erroffs, NULL);
  if (patre && (pcre_exec(patre, NULL, view_def.c_str(), view_def.length(), 0, 0, patres, sizeof(patres)/sizeof(int)) > 0))
  {
    or_replace_present= true;
  }

  if (patre)
    pcre_free(patre);

  if (!or_replace_present)
  {
    patre= pcre_compile("^\\s*CREATE\\s+", PCRE_CASELESS | PCRE_MULTILINE , &errptr, &erroffs, NULL);
    if (patre && (pcre_exec(patre, NULL, view_def.c_str(), view_def.length(), 0, 0, patres, sizeof(patres)/sizeof(int)) > 0))
      view_def.insert(patres[1], " OR REPLACE ");

    if (patre)
      pcre_free(patre);
  }

  remember(view, view_def);
}

void ActionGenerateSQL::drop_view(db_mysql_ViewRef view)
{
  std::string view_sql;
  view_sql.append("DROP VIEW IF EXISTS ").append(get_name(view)).append(" ");
  remember(view, view_sql);
}

// routines
void ActionGenerateSQL::create_routine(db_mysql_RoutineRef routine, bool for_alter)
{

  std::string routine_sql;
  if (!use_short_names)
  {
    routine_sql = "USE ";
    routine_sql.append(routine->owner()->name()).append("//\n").append(routine->owner()->name()).append("//\n");
  };
  routine_sql.append(routine->sqlDefinition().c_str());

  //remove_delims(routine_sql);
  if(for_alter)
    remember_alter(routine, routine_sql);
  else
    remember(routine, routine_sql);
}

void ActionGenerateSQL::drop_routine(db_mysql_RoutineRef routine, bool for_alter)
{
  std::string routine_sql;
  if (!use_short_names)
  {
    routine_sql = "USE ";
    routine_sql.append(routine->owner()->name()).append("//\n").append(routine->owner()->name()).append("//\n");
  };

  routine_sql.append("DROP ").append(routine->routineType().c_str())
    .append(" IF EXISTS ").append(get_name(routine)).append(" ");

  if(for_alter)
    remember_alter(routine, routine_sql);
  else
    remember(routine, routine_sql);
}

// users
void ActionGenerateSQL::create_user(db_UserRef user)
{
  std::string sql;

  sql.append("CREATE USER ").append(user->name().c_str());
  if (user->password().is_valid() && *user->password().c_str())
    sql.append(" IDENTIFIED BY '").append(*user->password()).append("'");

  sql.append(";\n\n");

  std::list<std::string> grants;
  gen_grant_sql(user, grants);

  std::list<std::string>::iterator iter= grants.begin();
  for(; iter != grants.end(); ++iter)
    sql.append(*iter).append(";\n");

  remember(user, sql);
}

void ActionGenerateSQL::drop_user(db_UserRef user)
{
  std::string user_sql;
  sql.append("DROP USER IF EXISTS ").append(user->name().c_str());
  remember(user, user_sql);
}

void ActionGenerateSQL::remember(const GrtNamedObjectRef &obj, const std::string &sql, const bool front)
{
  if(target_list.is_valid())
  {
    target_list.insert(grt::StringRef(sql),front?0:StringListRef::npos);
    if(target_object_list.is_valid())
      target_object_list.insert(obj,front?0:StringListRef::npos);
  }
  else
  {
    target_map.set(get_full_object_name_for_key(obj), grt::StringRef(sql));
  }
}

// in case of ALTERs there could be > 1 statement to remember
// so we use grt::StringListRefs as needed
void ActionGenerateSQL::remember_alter(const GrtNamedObjectRef &obj, const std::string &sql)
{
  if(target_list.is_valid())
  {
    target_list.insert(grt::StringRef(sql));
    if(target_object_list.is_valid())
      target_object_list.insert(obj);
    return;
  }

  std::string key= get_full_object_name_for_key(obj);

  if(target_map.has_key(key))
  {
    grt::ValueRef value= target_map.get(key);
    if(grt::StringRef::can_wrap(value))
    {
      grt::StringListRef list_value(target_map.get_grt());
      list_value.insert(grt::StringRef::cast_from(value));
      list_value.insert(grt::StringRef(sql));
      target_map.set(key, list_value);
    }
    else if(grt::StringListRef::can_wrap(value))
    {
      grt::StringListRef::cast_from(value).insert(grt::StringRef(sql));
    }
    else
    {
      // a bug
    }
  }
  else
  {
    target_map.set(key, grt::StringRef(sql));
  }
}

} // namespace

int DbMySQLImpl::generateSQL(GrtNamedObjectRef org_object, 
                                        const grt::DictRef& options, 
                                        const std::string &address1)
{
  DiffChange *p= 0;

  // yes, this is a hack. yes, this should be fixed
  sscanf(address1.c_str(), "%p", &p);

  if (!p)
    return 0;

  grt::ValueRef result= options.get("OutputContainer");
  grt::ListRef<GrtNamedObject> obj_list;

  if(options.has_key("OutputObjectContainer"))
    obj_list= grt::ListRef<GrtNamedObject>::cast_from(options.get("OutputObjectContainer"));
  
  if(result.type() == DictType)
  {
    DiffSQLGeneratorBE(options, new ActionGenerateSQL(result, obj_list))
      .process_diff_change(org_object, p, grt::DictRef::cast_from(result));
  }
  else if(result.type() == ListType)
  {
    DiffSQLGeneratorBE(options, new ActionGenerateSQL(result, obj_list))
      .process_diff_change(org_object, p, grt::StringListRef::cast_from(result), obj_list);
  }

  return 0;
}

grt::StringRef DbMySQLImpl::generateReport(GrtNamedObjectRef org_object, 
                                                        const grt::DictRef& options, 
                                                        const std::string &address1)
{
  DiffChange *p= 0;

  // yes, this is a hack. yes, this should be fixed
  sscanf(address1.c_str(), "%p", &p);

  if (!p)
    return grt::StringRef("");

  grt::StringRef tpl_file= grt::StringRef::cast_from(options.get("TemplateFile"));
  
  ActionGenerateReport *r= new ActionGenerateReport(tpl_file);

  DiffSQLGeneratorBE(options, r)
    .process_diff_change(org_object, p, grt::StringListRef(), grt::ListRef<GrtNamedObject>());

  grt::StringRef retval(r->generate_output());
  delete r;

  return retval;
}

static bool exists_in_map(GrtNamedObjectRef object, DictRef dict)
{
  std::string qname(get_full_object_name_for_key(object));
  return dict.has_key(qname);
}

static std::string string_from_map(GrtNamedObjectRef object, DictRef dict)
{
  std::string qname(get_full_object_name_for_key(object));
  StringRef res= dict.get_string(qname);

  //if (!res.is_valid())
  //  return std::string();
  return *res;
}


static std::string generate_view_placeholder(db_mysql_ViewRef view, const bool show_warnings, grt::GRT *grt, const bool use_short_names)
{
  std::string sql;
  std::string view_q_name(get_name(view,use_short_names));

  SelectStatement::Ref select_statement(new SelectStatement());
  SqlFacade* parser = SqlFacade::instance_for_rdbms_name(grt, "Mysql");
  parser->sqlStatementDecomposer()->decompose_view(view,select_statement);

  sql.append("\n-- -----------------------------------------------------\n")
    .append("-- Placeholder table for view ").append(view_q_name)
    .append("\n-- -----------------------------------------------------\n");

  sql.append("CREATE TABLE IF NOT EXISTS ").append(view_q_name).append(" (");
  if(select_statement->select_items.empty())
    sql.append("`id` INT");
  else
  {
    SelectItems::const_iterator it = select_statement->select_items.begin();
    sql.append("`").append(it->effective_alias()).append("` INT" );
    for (++it; it != select_statement->select_items.end(); ++it)
      sql.append(", `").append(it->effective_alias()).append("` INT" );
  }
  sql.append(");\n");

  if(show_warnings)
    sql.append("SHOW WARNINGS;\n");
  return sql;
}

static std::string generate_view_ddl(db_mysql_ViewRef view, 
                                     std::string create_view, 
                                     std::string drop_view, 
                                     const bool show_warnings,
                                     const bool use_short_names)
{
  //if(create_view.empty())
  //  return std::string();

  std::string sql;
  std::string view_q_name(get_name(view,use_short_names));

  // create view
  sql.append("\n");
  sql.append("-- -----------------------------------------------------\n");
  sql.append("-- View ").append(view_q_name).append("\n");
  sql.append("-- -----------------------------------------------------\n");

  //std::string create_view_sql= string_from_map(view, create_map);
  //if(create_view_sql.empty())
  //  continue;

  //std::string drop_view(string_from_map(view, drop_map));
  if(!drop_view.empty())
  {
    sql.append(drop_view).append(";\n");
    if(show_warnings)
      sql.append("SHOW WARNINGS;\n");
  }

  // remove placehoder
  sql.append("DROP TABLE IF EXISTS ").append(view_q_name).append(";\n");
  if(show_warnings)
    sql.append("SHOW WARNINGS;\n");
  // view DDL
  if(!create_view.empty())
    sql.append(create_view).append(";\n");
  if(show_warnings)
    sql.append("SHOW WARNINGS;\n");

  return sql;
}

static std::string get_export_sql(db_mysql_CatalogRef cat, 
                                  grt::DictRef options,
                                  grt::DictRef create_map, 
                                  grt::DictRef drop_map,
                                  grt::GRT *grt)
{
  std::string out_sql;

  bool gen_create_index= false;
  bool use_short_names = false;
  bool show_warnings= false;
  bool gen_drops= false;
  bool gen_schema_drops = false;
  bool no_user_just_privileges= false;
  bool gen_inserts= false;

  std::string inserts_sql; // separate from main sql script & append to it as a last step,
                           // to separate creation of structures from data loading.

  struct IntOption {
    static int get(grt::DictRef dict, const char *option, int defval= 0)
    {
      if(dict.has_key(option))
      {
        grt::IntegerRef option_value= grt::IntegerRef::cast_from(dict.get(option));
        if(option_value.is_valid())
          return (option_value.operator long() != 0);
      }
      return defval;
    }
  
  };

  class GenerateTable {
    
    static std::string generate_deps(db_mysql_SchemaRef schema,
                                     db_mysql_TableRef table, 
                                     std::set<db_mysql_TableRef>& generated_tables,
                                     grt::DictRef options,
                                     grt::DictRef create_map, 
                                     grt::DictRef drop_map,
                                     grt::GRT *grt,
									 std::string& inserts_sql)
    {
      std::string sql;
      const grt::ListRef<db_mysql_ForeignKey> fks= table->foreignKeys();
      for(size_t c= fks.count(), i= 0; i < c; i++)
      {
        const db_mysql_ForeignKeyRef fk= fks.get(i);

        if (fk.is_valid() && fk->referencedTable().is_valid())
        {
          if (fk->modelOnly() || fk->referencedTable()->modelOnly())
            continue;

          db_mysql_TableRef table(fk->referencedTable());
          if(table.is_valid())
            sql += GenerateTable::perform(schema, table, generated_tables, options, create_map, drop_map, grt,inserts_sql);
        }
      }
      return sql;
    }

  public:

    static std::string perform(db_mysql_SchemaRef schema, 
                               db_mysql_TableRef table,
                               std::set<db_mysql_TableRef>& generated_tables,
                               grt::DictRef options,
                               grt::DictRef create_map, 
                               grt::DictRef drop_map,
                               grt::GRT *grt,
                               std::string& inserts_sql)
    {
      if(generated_tables.find(table) != generated_tables.end())
        return std::string();

      generated_tables.insert(table);

      std::string sql(
        generate_deps(schema, table, generated_tables, 
          options, create_map, drop_map, grt,inserts_sql));

      bool gen_create_index = IntOption::get(options, "GenerateCreateIndex") != 0;
      bool use_short_names= IntOption::get(options, "UseShortNames") != 0;
      bool show_warnings = IntOption::get(options, "GenerateWarnings") != 0;
      bool gen_drops = IntOption::get(options, "GenerateDrops") != 0;
      bool gen_schema_drops = IntOption::get(options, "GenerateSchemaDrops") != 0;
      //bool no_user_just_privileges= IntOption::get(options, "NoUsersJustPrivileges") != 0;
      bool gen_inserts= IntOption::get(options, "GenerateInserts") != 0;

      bool process_table= exists_in_map(table, create_map);

      if(process_table)
      {
        std::string create_table_sql= string_from_map(table, create_map);

        sql.append("\n");
        sql.append("-- -----------------------------------------------------\n");
        sql.append("-- Table ").append(get_name(table,use_short_names)).append("\n");
        sql.append("-- -----------------------------------------------------\n");

        if(gen_drops)
        {
          sql.append(string_from_map(table, drop_map)).append(";\n\n");
          if(show_warnings)
            sql.append("SHOW WARNINGS;\n");
        }
        sql.append(create_table_sql).append(";\n\n");
        if(show_warnings)
          sql.append("SHOW WARNINGS;\n");

        if (grt) 
          grt->send_output(std::string("Processing Table ").append(schema->name()).append(".").append(table->name()).append("\n"));

        // table indices
        if(gen_create_index && (table->indices().count() > 0))
        {
          grt::ListRef<db_mysql_Index> indices= table->indices();
          for(size_t c3= indices.count(), k= 0; k < c3; k++)
          {
            db_mysql_IndexRef index= indices.get(k);
            std::string index_sql= string_from_map(index, create_map);
            if(!index_sql.empty())
            {
              sql.append(index_sql).append(";\n\n");
              if(show_warnings)
                sql.append("SHOW WARNINGS;\n");
            }
          }
        }
      } // process table

      if(gen_inserts && process_table)
      {
        std::string use_code;
        if (!use_short_names)
          use_code.append("USE `").append(schema->name().c_str()).append("`;\n");

        std::string table_inserts_sql= table->inserts();
        if (!table_inserts_sql.empty())
        {
          inserts_sql
            .append("\n-- -----------------------------------------------------\n-- Data for table ")
            .append(get_name(table,use_short_names))
            .append("\n-- -----------------------------------------------------\nSET AUTOCOMMIT=0;\n")
            .append(use_code)
            .append(table_inserts_sql)
            .append("\nCOMMIT;\n");
        }
      }

      return sql;
    }
  };

  if (grt)
    grt->send_output("Generating Script...\n");

  gen_create_index= IntOption::get(options, "GenerateCreateIndex") != 0;
  use_short_names= IntOption::get(options, "UseShortNames") != 0;
  show_warnings= IntOption::get(options, "GenerateWarnings") != 0;
  gen_drops= IntOption::get(options, "GenerateDrops") != 0;
  gen_schema_drops = IntOption::get(options, "GenerateSchemaDrops") != 0;
  no_user_just_privileges= IntOption::get(options, "NoUsersJustPrivileges") != 0;
  gen_inserts= IntOption::get(options, "GenerateInserts") != 0;

  out_sql.append("SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;\n");
  out_sql.append("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n");
  out_sql.append("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';\n\n");

  std::set<db_mysql_TableRef> generated_tables;

  // schemata
  grt::ListRef<db_mysql_Schema> schemata= cat->schemata();
  for(size_t c1= schemata.count(), i= 0; i < c1; i++)
  {
    std::string schema_sql;
    std::string objects_sql;

    db_mysql_SchemaRef schema= schemata.get(i);
    if (schema->modelOnly())
      continue;

    if (!use_short_names)
    {
      if (gen_schema_drops)
        schema_sql.append("DROP SCHEMA IF EXISTS `").append(schema->name().c_str()).append("` ").append(";\n");
      schema_sql.append(string_from_map(schema, create_map)).append(";\n");
    }

    if(show_warnings)
      schema_sql.append("SHOW WARNINGS;\n");
    if (grt) 
      grt->send_output(std::string("Processing Schema ").append(schema->name()).append("\n"));
    if (!use_short_names)
      schema_sql.append("USE `").append(schema->name().c_str()).append("`;\n");

    // tables
    grt::ListRef<db_mysql_Table> tables= schema->tables();
    for(size_t c2= tables.count(), j= 0; j < c2; j++)
    {
      db_mysql_TableRef table= tables.get(j);
      if (table->modelOnly())
        continue;
      objects_sql += GenerateTable::perform(schema, table, generated_tables, options, create_map, drop_map, grt,inserts_sql);
    }

    // views placeholder tables
    grt::ListRef<db_mysql_View> views= schema->views();
    for(size_t c2= views.count(), j= 0; j < c2; j++)
    {
      db_mysql_ViewRef view= views.get(j);
      if (view->modelOnly())
        continue;
      if(exists_in_map(view, create_map))
        objects_sql.append(generate_view_placeholder(view, show_warnings, grt, use_short_names));
    }

	    // routines
    grt::ListRef<db_mysql_Routine> routines= schema->routines();
    if(schema->routines().count() > 0)
    {
      bool need_start_delim= true;
      bool need_end_delim= false;

      for(size_t c2= routines.count(), j= 0; j < c2; j++)
      {
        db_mysql_RoutineRef routine= routines.get(j);
        if (routine->modelOnly())
          continue;
        std::string create_routine_sql= string_from_map(routine, create_map);
        if(create_routine_sql.empty())
          continue;

        if(need_start_delim)
        {
          objects_sql.append("\nDELIMITER //\n");
          need_start_delim= false;
          need_end_delim= true;
        }

        //if(gen_drops)
        {
          objects_sql.append(string_from_map(routine, drop_map)).append("//\n");
          if(show_warnings)
            objects_sql.append("SHOW WARNINGS//\n");
        }
        objects_sql.append(string_from_map(routine, create_map)).append("//\n");
        if(show_warnings)
          objects_sql.append("SHOW WARNINGS//\n");

        if (grt) 
          grt->send_output(std::string("Processing Routine ").append(schema->name()).append(".").append(routine->name()).append("\n"));
      }

      if(need_end_delim)
        objects_sql.append("\nDELIMITER ;\n");

    }


    // views DDL
    for(size_t c2= views.count(), j= 0; j < c2; j++)
    {
      db_mysql_ViewRef view= views.get(j);
      if (view->modelOnly())
        continue;
      if(exists_in_map(view, create_map))
      {
        objects_sql.append(generate_view_ddl(view, 
            string_from_map(view, create_map), 
            string_from_map(view, drop_map), 
            show_warnings,
            use_short_names));

        if (grt) 
          grt->send_output(std::string("Processing View ").append(schema->name()).append(".").append(view->name()).append("\n"));
      }
    }

    if(!objects_sql.empty())
    {
      out_sql += schema_sql;
      out_sql += objects_sql;
    }
  }

  for(size_t c1= schemata.count(), i= 0; i < c1; i++)
  {
      std::string schema_sql;
      std::string objects_sql;
    db_mysql_SchemaRef schema= schemata.get(i);
    if (schema->modelOnly())
      continue;
    if (!use_short_names)
      schema_sql.append("USE `").append(schema->name().c_str()).append("`;\n");
    // tables
    grt::ListRef<db_mysql_Table> tables= schema->tables();

    // triggers
    for(size_t c2= tables.count(), j= 0; j < c2; j++)
    {
      db_mysql_TableRef table= tables.get(j);
      if (table->modelOnly())
        continue;
      grt::ListRef<db_mysql_Trigger> triggers= table->triggers();
      if(table->triggers().count() > 0)
      {
        objects_sql.append("\nDELIMITER //\n");

        for(size_t c3= triggers.count(), k= 0; k < c3; k++)
        {
          db_mysql_TriggerRef trigger= triggers.get(k);

          if (trigger->modelOnly())
            continue;

          if (grt) 
            grt->send_output(std::string("Processing Trigger ").append(schema->name()).append(".").append(trigger->name()).append("\n"));

          if(!exists_in_map(trigger, create_map))
            continue;
          std::string create_trigger_sql= string_from_map(trigger, create_map);

          //if(gen_drops)
          {
            std::string drop_trigger(string_from_map(trigger, drop_map));
            if(!drop_trigger.empty())
              objects_sql.append("\n").append(drop_trigger).append("//\n");
            if(show_warnings)
              objects_sql.append("SHOW WARNINGS//\n");
          }
          objects_sql.append(string_from_map(trigger, create_map)).append("//\n\n");
          if(show_warnings)
            objects_sql.append("SHOW WARNINGS//\n");

          if (grt) 
            grt->send_output(std::string("Processing Trigger ") 
            .append(schema->name()).append(".").append(table->name()).append(".").append(trigger->name()).append("\n"));
        }

        objects_sql.append("\nDELIMITER ;\n");
      }
    }
      if(!objects_sql.empty())
      {
        out_sql += schema_sql;
        out_sql += objects_sql;
      }
  }

    out_sql.append("\n");

  if (no_user_just_privileges)
  {
    std::list<std::string> grants;
    db_CatalogRef catt= cat;
    gen_grant_sql(catt, grants);

    std::list<std::string>::iterator iter= grants.begin();
    for(; iter != grants.end(); ++iter)
      out_sql.append(*iter).append(";\n");
  }
  else
  {
    grt::ListRef<db_User> users= cat->users();
    for(size_t c1= users.count(), i= 0; i < c1; i++)
    {
      db_UserRef user= users.get(i);

      if (user->modelOnly())
        continue;

      std::string create_user_sql= string_from_map(user, create_map);
      if(create_user_sql.empty())
        continue;
      
      //if(gen_drops)
      {
        out_sql.append(string_from_map(user, drop_map)).append(";\n");
        if(show_warnings)
          out_sql.append("SHOW WARNINGS;\n");
      }

      out_sql.append(string_from_map(user, create_map));
      if(show_warnings)
        out_sql.append("SHOW WARNINGS;\n");
    
      if (grt) 
        grt->send_output(std::string("Processing User ").append(user->name()).append("\n"));
    }
  }

  out_sql.append("\n");
  out_sql.append("SET SQL_MODE=@OLD_SQL_MODE;\n");
  out_sql.append("SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\n");
  out_sql.append("SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;\n");

  if(gen_inserts)
    out_sql.append(inserts_sql);

  return out_sql;
}

int DbMySQLImpl::makeSQLExportScript(GrtNamedObjectRef dbobject, 
                                          const grt::DictRef& options, 
                                          const grt::DictRef& createSQL, 
                                          const grt::DictRef& dropSQL)
{
  if (!db_mysql_CatalogRef::can_wrap(dbobject))
  {
    // now only catalog supported
    return 1;
  }

  db_mysql_CatalogRef catalog;
  catalog= db_mysql_CatalogRef::cast_from(dbobject);

  std::string sql= get_export_sql(catalog, options, createSQL, dropSQL, get_grt());

  // final script is output in options dict
  ((grt::DictRef)options).set("OutputScript", grt::StringRef(sql));
  
  return 0;
}

int DbMySQLImpl::makeSQLSyncScript(const grt::DictRef& options, 
                                              const grt::StringListRef& sql_list,
                                              const grt::ListRef<GrtNamedObject>& obj_list)
{
  std::string sql;

  sql.append("SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;\n");
  sql.append("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n");
  sql.append("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';\n\n");

  std::list<int> views_indices, routines_indices;

  for(size_t sz= sql_list.count(), i= 0; i < sz; i++)
  {
    GrtNamedObjectRef obj= obj_list.get(i);
    if(db_RoutineRef::can_wrap(obj) || db_TriggerRef::can_wrap(obj))
    {
      routines_indices.push_back(i);
    }
    else if(db_ViewRef::can_wrap(obj))
    {
      views_indices.push_back(i);
    }
    else
    {
      sql.append(sql_list.get(i)).append(";\n\n");
      if(db_SchemaRef::can_wrap(obj))
      {
        if(strncmp(sql_list.get(i).c_str(), "RENAME", 6) == 0)
        {
          const char *quoted_new_name= strstr(sql_list.get(i).c_str(), "` TO `") + 5;
          sql.append("USE ").append(quoted_new_name).append(";\n\n");
        }
        else if(strncmp(sql_list.get(i).c_str(), "DROP", 4) != 0)
        {
          db_SchemaRef schema= db_SchemaRef::cast_from(obj);
          sql.append("USE `").append(schema->name().c_str()).append("`;\n\n");
        }
      }
    }
  }

  // views placeholders
  for(std::list<int>::const_iterator e= views_indices.end(), it= views_indices.begin(); 
    it != e; it++)
  {
    db_mysql_ViewRef view= db_mysql_ViewRef::cast_from(obj_list.get(*it));
    std::string view_ddl(sql_list.get(*it));
    if(!view_ddl.empty())
      sql.append(generate_view_placeholder(view, false, get_grt(),false));
  }

  // views DDL
  for(std::list<int>::const_iterator e= views_indices.end(), it= views_indices.begin(); 
    it != e; it++)
  {
    db_mysql_ViewRef view= db_mysql_ViewRef::cast_from(obj_list.get(*it));
    std::string view_ddl(sql_list.get(*it));
    sql.append("\n\nUSE ").append(view->owner()->name()).append(";\n");
    sql.append(generate_view_ddl(view, view_ddl, std::string(), false, false));
  }

  if(!routines_indices.empty())
    sql.append("\nDELIMITER //\n\n");

  for(std::list<int>::const_iterator e= routines_indices.end(), it= routines_indices.begin(); 
    it != e; it++)
  {
    sql.append(sql_list.get(*it)).append("//\n\n");
  }

  if(!routines_indices.empty())
    sql.append("\nDELIMITER ;\n\n");

  sql.append("\n");
  sql.append("SET SQL_MODE=@OLD_SQL_MODE;\n");
  sql.append("SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\n");
  sql.append("SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;\n");

  ((grt::DictRef)options).set("OutputScript", grt::StringRef(sql));

  return 0;
}

// This function is used from scripts and HTML report generator.
std::string DbMySQLImpl::makeCreateScriptForObject(GrtNamedObjectRef object)
{
  grt::DbObjectMatchRecreateOmf omf;

  DictRef options(get_grt());
  DictRef result(get_grt());

  ValueRef parent;

  StringListRef list(get_grt());
  list.insert(get_object_name_for_key(object));

  if (object.is_instance(db_Schema::static_class_name()))
    parent= object->owner();
  else if (object.is_instance(db_Table::static_class_name()))
    parent= object->owner()->owner();
  else if (object.is_instance(db_Trigger::static_class_name()))
    parent= object->owner()->owner()->owner();
  else if (object.is_instance(db_View::static_class_name()))
    parent= object->owner()->owner();
  else if (object.is_instance(db_Routine::static_class_name()))
    parent= object->owner()->owner();
  else if (object.is_instance(db_RoutineGroup::static_class_name()))
    parent= object->owner()->owner();
  else
    return "";

  options.set("UseFilteredLists", IntegerRef(0));
  DiffChange* diff= diff_make(ValueRef(), parent, NULL, grt::NormalizedComparer<grt::GRT*>(get_grt()));

  std::string sql;

  if (diff)
  {
    DiffSQLGeneratorBE *gen= new DiffSQLGeneratorBE(
      options, new ActionGenerateSQL(result, grt::ListRef<GrtNamedObject>()));

    gen->process_diff_change(ValueRef(), diff, result);
    delete diff;
    sql= result.get_string(get_full_object_name_for_key(object), "");
  }

  return sql;
}


grt::ListRef<db_mysql_StorageEngine> DbMySQLImpl::getKnownEngines()
{
  return dbmysql::get_known_engines(this->get_grt());
}

GRT_MODULE_ENTRY_POINT(DbMySQLImpl);
