/** Write the primary key constraint inline with the create table statement. */
  protected void writePrimaryKeyConstraint(DdlBuffer buffer, String pkName, String[] pkColumns)
      throws IOException {

    buffer.append(",").newLine();
    buffer.append("  constraint ").append(pkName).append(" primary key");
    appendColumns(pkColumns, buffer);
  }
  /** Use Postgres range type rather than start and end timestamps. */
  @Override
  protected void addSysPeriodColumns(
      DdlBuffer apply, String baseTableName, String whenCreatedColumn) throws IOException {
    apply
        .append("alter table ")
        .append(baseTableName)
        .append(" add column ")
        .append(sysPeriod)
        .append(" tstzrange not null default tstzrange(")
        .append(currentTimestamp)
        .append(", null)")
        .endOfStatement();

    if (whenCreatedColumn != null) {
      apply
          .append("update table ")
          .append(baseTableName)
          .append(" set ")
          .append(sysPeriod)
          .append(" = tstzrange(")
          .append(whenCreatedColumn)
          .append(", null)")
          .endOfStatement();
    }
  }
  protected void writeForeignKey(
      DdlWrite write,
      String fkName,
      String tableName,
      String[] columns,
      String refTable,
      String[] refColumns,
      String indexName)
      throws IOException {

    tableName = lowerName(tableName);
    DdlBuffer fkeyBuffer = write.applyForeignKeys();
    alterTableAddForeignKey(fkeyBuffer, fkName, tableName, columns, refTable, refColumns);

    if (indexName != null) {
      // no matching unique constraint so add the index
      fkeyBuffer.append(platformDdl.createIndex(indexName, tableName, columns)).endOfStatement();
    }

    fkeyBuffer.end();

    write
        .rollbackForeignKeys()
        .append(platformDdl.alterTableDropForeignKey(tableName, fkName))
        .endOfStatement();

    if (indexName != null) {
      write
          .rollbackForeignKeys()
          .append(platformDdl.dropIndex(indexName, tableName))
          .endOfStatement();
    }

    write.rollbackForeignKeys().end();
  }
  /** Write a check constraint. */
  protected void writeCheckConstraint(DdlBuffer buffer, Column column, String checkConstraint)
      throws IOException {

    String ckName = column.getCheckConstraintName();

    buffer.append(",").newLine();
    buffer.append("  constraint ").append(ckName);
    buffer.append(" ").append(checkConstraint);
  }
 private void appendColumns(String[] columns, DdlBuffer buffer) throws IOException {
   buffer.append(" (");
   for (int i = 0; i < columns.length; i++) {
     if (i > 0) {
       buffer.append(",");
     }
     buffer.append(lowerName(columns[i].trim()));
   }
   buffer.append(")");
 }
  /**
   * Generate the appropriate 'create table' and matching 'drop table' statements and add them to
   * the appropriate 'apply' and 'rollback' buffers.
   */
  @Override
  public void generate(DdlWrite writer, CreateTable createTable) throws IOException {

    reset();

    String tableName = lowerName(createTable.getName());
    List<Column> columns = createTable.getColumn();
    List<Column> pk = determinePrimaryKeyColumns(columns);

    boolean singleColumnPrimaryKey = (pk.size() == 1);
    boolean useIdentity = false;
    boolean useSequence = false;

    if (singleColumnPrimaryKey) {
      IdType useDbIdentityType = platformDdl.useIdentityType(createTable.getIdentityType());
      useIdentity = (IdType.IDENTITY == useDbIdentityType);
      useSequence = (IdType.SEQUENCE == useDbIdentityType);
    }

    DdlBuffer apply = writer.apply();
    apply.append("create table ").append(tableName).append(" (");
    writeTableColumns(apply, columns, useIdentity);
    writeCheckConstraints(apply, createTable);
    writeUniqueConstraints(apply, createTable);
    writeCompoundUniqueConstraints(apply, createTable);
    if (!pk.isEmpty()) {
      // defined on the columns
      writePrimaryKeyConstraint(apply, createTable.getPkName(), toColumnNames(pk));
    }

    apply.newLine().append(")").endOfStatement();

    writeUniqueOneToOneConstraints(writer, createTable);

    if (isTrue(createTable.isWithHistory())) {
      // create history with rollback before the
      // associated drop table is written to rollback
      createWithHistory(writer, createTable.getName());
    }

    // add drop table to the rollback buffer - do this before
    // we drop the related sequence (if sequences are used)
    dropTable(writer.rollback(), tableName);

    if (useSequence) {
      String pkCol = pk.get(0).getName();
      writeSequence(writer, createTable, pkCol);
    }

    // add blank line for a bit of whitespace between tables
    apply.end();
    writer.rollback().end();

    writeAddForeignKeys(writer, createTable);
  }
  /** Write the unique constraint inline with the create table statement. */
  protected void inlineUniqueConstraintSingle(DdlBuffer buffer, String tableName, Column column)
      throws IOException {

    String uqName = determineUniqueConstraintName(tableName, column.getName());

    buffer.append(",").newLine();
    buffer.append("  constraint ").append(uqName).append(" unique ");
    buffer.append("(");
    buffer.append(lowerName(column.getName()));
    buffer.append(")");
  }
  /** Write alter table add primary key statement. */
  public void alterTableAddPrimaryKey(DdlBuffer buffer, String tableName, List<Column> pk)
      throws IOException {

    String[] pkColumns = toColumnNames(pk);
    String pkName = determinePrimaryKeyName(tableName);

    buffer.append("alter table ").append(tableName);
    buffer.append(" add primary key ").append(pkName);
    appendColumns(pkColumns, buffer);
    buffer.append(")").endOfStatement();
  }
  /** Write the unique constraint inline with the create table statement. */
  protected void inlineUniqueConstraintSingle(DdlBuffer buffer, Column column) throws IOException {

    String uqName = column.getUnique();
    if (uqName == null) {
      uqName = column.getUniqueOneToOne();
    }

    buffer.append(",").newLine();
    buffer.append("  constraint ").append(uqName).append(" unique ");
    buffer.append("(");
    buffer.append(lowerName(column.getName()));
    buffer.append(")");
  }
  /** Write the column definition to the create table statement. */
  protected void writeColumnDefinition(DdlBuffer buffer, Column column, boolean useIdentity)
      throws IOException {

    boolean identityColumn = useIdentity && isTrue(column.isPrimaryKey());
    String platformType = convertToPlatformType(column.getType(), identityColumn);

    buffer.append("  ");
    buffer.append(lowerName(column.getName()), 30);
    buffer.append(platformType);
    if (isTrue(column.isNotnull()) || isTrue(column.isPrimaryKey())) {
      buffer.append(" not null");
    }

    // add check constraints later as we really want to give them a nice name
    // so that the database can potentially provide a nice SQL error
  }
 @Override
 protected void dropTriggers(DdlBuffer buffer, String baseTable) throws IOException {
   // rollback trigger then function
   buffer
       .append("drop trigger if exists ")
       .append(triggerName(baseTable))
       .append(" on ")
       .append(baseTable)
       .append(" cascade")
       .endOfStatement();
   buffer
       .append("drop function if exists ")
       .append(procedureName(baseTable))
       .append("()")
       .endOfStatement();
   buffer.end();
 }
  protected void addFunction(
      DdlBuffer apply, String procedureName, String historyTable, List<String> includedColumns)
      throws IOException {
    apply
        .append("create or replace function ")
        .append(procedureName)
        .append("() returns trigger as $$")
        .newLine()
        .append("begin")
        .newLine();
    apply.append("  if (TG_OP = 'UPDATE') then").newLine();
    appendInsertIntoHistory(apply, historyTable, includedColumns);
    apply
        .append("    NEW.")
        .append(sysPeriod)
        .append(" = tstzrange(CURRENT_TIMESTAMP,null);")
        .newLine()
        .append("    return new;")
        .newLine()
        .newLine();
    apply.append("  elsif (TG_OP = 'DELETE') then").newLine();
    appendInsertIntoHistory(apply, historyTable, includedColumns);
    apply.append("    return old;").newLine().newLine();
    apply
        .append("  end if;")
        .newLine()
        .append("end;")
        .newLine()
        .append("$$ LANGUAGE plpgsql;")
        .newLine();

    apply.end();
  }
 @Override
 protected void dropSysPeriodColumns(DdlBuffer buffer, String baseTableName) throws IOException {
   buffer
       .append("alter table ")
       .append(baseTableName)
       .append(" drop column ")
       .append(sysPeriod)
       .endOfStatement();
 }
  @Override
  protected void appendInsertIntoHistory(
      DdlBuffer buffer, String historyTable, List<String> columns) throws IOException {

    buffer
        .append("    insert into ")
        .append(historyTable)
        .append(" (")
        .append(sysPeriod)
        .append(",");
    appendColumnNames(buffer, columns, "");
    buffer
        .append(") values (tstzrange(lower(OLD.")
        .append(sysPeriod)
        .append("), current_timestamp), ");
    appendColumnNames(buffer, columns, "OLD.");
    buffer.append(");").newLine();
  }
  protected void alterTableDropColumn(DdlBuffer buffer, String tableName, String columnName)
      throws IOException {

    buffer
        .append("alter table ")
        .append(tableName)
        .append(" drop column ")
        .append(columnName)
        .endOfStatement();
  }
  @Override
  protected void createTriggers(DdlWrite writer, MTable table) throws IOException {

    String baseTableName = table.getName();
    String procedureName = procedureName(baseTableName);
    String triggerName = triggerName(baseTableName);

    DdlBuffer apply = writer.applyHistory();
    apply
        .append("create trigger ")
        .append(triggerName)
        .newLine()
        .append("  before update or delete on ")
        .append(baseTableName)
        .newLine()
        .append("  for each row execute procedure ")
        .append(procedureName)
        .append("();")
        .newLine()
        .newLine();
  }
  @Override
  protected void addStoredFunction(DdlWrite writer, MTable table, HistoryTableUpdate update)
      throws IOException {

    String procedureName = procedureName(table.getName());
    String historyTable = historyTableName(table.getName());

    List<String> includedColumns = includedColumnNames(table);

    DdlBuffer apply = writer.applyHistory();

    if (update != null) {
      apply.append("-- Regenerated ").append(procedureName).newLine();
      apply.append("-- changes: ").append(update.description()).newLine();
    }

    addFunction(apply, procedureName, historyTable, includedColumns);

    if (update != null) {
      // put a reverted version into the rollback buffer
      update.toRevertedColumns(includedColumns);

      DdlBuffer rollback = writer.rollback();
      rollback.append("-- Revert regenerated ").append(procedureName).newLine();
      rollback.append("-- revert changes: ").append(update.description()).newLine();

      addFunction(rollback, procedureName, historyTable, includedColumns);
    }
  }
  /** Use Postgres create table like to create the history table. */
  @Override
  protected void createHistoryTable(DdlBuffer apply, MTable table) throws IOException {

    String baseTable = table.getName();
    apply
        .append("create table ")
        .append(baseTable)
        .append(historySuffix)
        .append("(like ")
        .append(baseTable)
        .append(")")
        .endOfStatement();
  }
  protected void alterTableAddColumn(
      DdlBuffer buffer, String tableName, Column column, boolean onHistoryTable)
      throws IOException {

    buffer
        .append("alter table ")
        .append(tableName)
        .append(" add column ")
        .append(column.getName())
        .append(" ")
        .append(column.getType());

    if (!onHistoryTable) {
      if (isTrue(column.isNotnull())) {
        buffer.append(" not null");
      }
      if (hasValue(column.getCheckConstraint())) {
        buffer.append(" ").append(column.getCheckConstraint());
      }
    }
    buffer.endOfStatement();
  }
  protected void alterTableAddForeignKey(
      DdlBuffer buffer,
      String fkName,
      String tableName,
      String[] columns,
      String refTable,
      String[] refColumns)
      throws IOException {

    buffer
        .append(
            platformDdl.alterTableAddForeignKey(tableName, fkName, columns, refTable, refColumns))
        .endOfStatement();
  }
  protected void writeCompoundUniqueConstraints(DdlBuffer apply, CreateTable createTable)
      throws IOException {

    String tableName = createTable.getName();

    List<UniqueConstraint> uniqueConstraints = createTable.getUniqueConstraint();
    for (UniqueConstraint uniqueConstraint : uniqueConstraints) {
      String[] columns = toColumnNamesSplit(uniqueConstraint.getColumnNames());
      apply
          .append(
              platformDdl.alterTableAddUniqueConstraint(
                  tableName, uniqueConstraint.getName(), columns))
          .endOfStatement();
    }
  }
  protected void writeForeignKey(
      DdlWrite write,
      String fkName,
      String tableName,
      String[] columns,
      String refTable,
      String[] refColumns)
      throws IOException {

    tableName = lowerName(tableName);
    DdlBuffer fkeyBuffer = write.applyForeignKeys();
    fkeyBuffer
        .append("alter table ")
        .append(tableName)
        .append(" add constraint ")
        .append(fkName)
        .append(" foreign key");
    appendColumns(columns, fkeyBuffer);
    fkeyBuffer.append(" references ").append(lowerName(refTable));
    appendColumns(refColumns, fkeyBuffer);
    fkeyBuffer.appendWithSpace(platformDdl.getForeignKeyRestrict()).endOfStatement();

    String indexName = determineForeignKeyIndexName(tableName, columns);

    boolean addIndex = indexSet.add(columns);
    if (addIndex) {
      // no matching unique constraint so add the index
      fkeyBuffer.append("create index ").append(indexName).append(" on ").append(tableName);
      appendColumns(columns, fkeyBuffer);
      fkeyBuffer.endOfStatement();
    }

    fkeyBuffer.end();

    if (addIndex) {
      write.rollbackForeignKeys().append("drop index ").append(indexName).endOfStatement();
    }

    write
        .rollbackForeignKeys()
        .append("alter table ")
        .append(tableName)
        .append(" drop constraint ")
        .append(fkName)
        .endOfStatement();

    write.rollbackForeignKeys().end();
  }
  /** Add 'drop table' statement to the buffer. */
  protected void dropTable(DdlBuffer buffer, String tableName) throws IOException {

    buffer.append(platformDdl.dropTable(tableName)).endOfStatement();
  }