/** 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();
}