PostgreSqlDdlBuilder.java

package org.jumpmind.db.platform.postgresql;

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

import java.sql.Types;
import java.util.Iterator;
import java.util.List;

import org.jumpmind.db.alter.AddColumnChange;
import org.jumpmind.db.alter.ColumnAutoIncrementChange;
import org.jumpmind.db.alter.ColumnDataTypeChange;
import org.jumpmind.db.alter.ColumnDefaultValueChange;
import org.jumpmind.db.alter.ColumnRequiredChange;
import org.jumpmind.db.alter.ColumnSizeChange;
import org.jumpmind.db.alter.CopyColumnValueChange;
import org.jumpmind.db.alter.PrimaryKeyChange;
import org.jumpmind.db.alter.RemoveColumnChange;
import org.jumpmind.db.alter.TableChange;
import org.jumpmind.db.model.Column;
import org.jumpmind.db.model.ColumnTypes;
import org.jumpmind.db.model.Database;
import org.jumpmind.db.model.IIndex;
import org.jumpmind.db.model.Table;
import org.jumpmind.db.platform.AbstractDdlBuilder;
import org.jumpmind.db.platform.DatabaseNamesConstants;

/*
 * The SQL Builder for PostgresSql.
 */
public class PostgreSqlDdlBuilder extends AbstractDdlBuilder {

    public PostgreSqlDdlBuilder() {
        super(DatabaseNamesConstants.POSTGRESQL);
        
        // this is the default length though it might be changed when building
        // PostgreSQL
        // in file src/include/postgres_ext.h
        databaseInfo.setMaxIdentifierLength(63);

        databaseInfo.setRequiresSavePointsInTransaction(true);
        databaseInfo.setRequiresAutoCommitForDdl(true);

        databaseInfo.addNativeTypeMapping(Types.ARRAY, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.BINARY, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.BIT, "BOOLEAN");
        databaseInfo.addNativeTypeMapping(Types.BLOB, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.CLOB, "TEXT", Types.LONGVARCHAR);
        databaseInfo.addNativeTypeMapping(Types.DECIMAL, "NUMERIC", Types.NUMERIC);
        databaseInfo.addNativeTypeMapping(Types.DISTINCT, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.DOUBLE, "DOUBLE PRECISION");
        databaseInfo.addNativeTypeMapping(Types.FLOAT, "DOUBLE PRECISION", Types.DOUBLE);
        databaseInfo.addNativeTypeMapping(Types.JAVA_OBJECT, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.LONGVARBINARY, "BYTEA");
        databaseInfo.addNativeTypeMapping(Types.LONGVARCHAR, "TEXT", Types.LONGVARCHAR);
        databaseInfo.addNativeTypeMapping(Types.NULL, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.OTHER, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.REF, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.STRUCT, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping(Types.TINYINT, "SMALLINT", Types.SMALLINT);
        databaseInfo.addNativeTypeMapping(Types.VARBINARY, "BYTEA", Types.LONGVARBINARY);
        databaseInfo.addNativeTypeMapping("BOOLEAN", "BOOLEAN", "BIT");
        databaseInfo.addNativeTypeMapping("DATALINK", "BYTEA", "LONGVARBINARY");
        databaseInfo.addNativeTypeMapping(ColumnTypes.NVARCHAR, "VARCHAR", Types.VARCHAR);
        databaseInfo.addNativeTypeMapping(ColumnTypes.LONGNVARCHAR, "VARCHAR", Types.VARCHAR);
        databaseInfo.addNativeTypeMapping(ColumnTypes.NCHAR, "CHAR", Types.CHAR);
        
        databaseInfo.setDefaultSize(Types.CHAR, 254);
        databaseInfo.setDefaultSize(Types.VARCHAR, 254);

        // no support for specifying the size for these types (because they are
        // mapped to BYTEA which back-maps to BLOB)
        databaseInfo.setHasSize(Types.BINARY, false);
        databaseInfo.setHasSize(Types.VARBINARY, false);

        databaseInfo.setNonBlankCharColumnSpacePadded(true);
        databaseInfo.setBlankCharColumnSpacePadded(true);
        databaseInfo.setCharColumnSpaceTrimmed(false);
        databaseInfo.setEmptyStringNulled(false);
        // we need to handle the backslash first otherwise the other
        // already escaped sequences would be affected
        addEscapedCharSequence("\\", "\\\\");
        addEscapedCharSequence("'", "\\'");
        addEscapedCharSequence("\b", "\\b");
        addEscapedCharSequence("\f", "\\f");
        addEscapedCharSequence("\n", "\\n");
        addEscapedCharSequence("\r", "\\r");
        addEscapedCharSequence("\t", "\\t");
    }

    public static boolean isUsePseudoSequence() {
        return "true".equalsIgnoreCase(System.getProperty(
                "org.jumpmind.symmetric.ddl.use.table.seq", "false"));
    }

    @Override
    protected void dropTable(Table table, StringBuilder ddl, boolean temporary, boolean recreate) {
        ddl.append("DROP TABLE ");
        ddl.append(getFullyQualifiedTableNameShorten(table));
        ddl.append(" CASCADE");
        printEndOfStatement(ddl);
        if (!temporary && !recreate) {
            Column[] columns = table.getAutoIncrementColumns();

            for (int idx = 0; idx < columns.length; idx++) {
                dropAutoIncrementSequence(table, columns[idx], ddl);
            }
        }
    }

    @Override
    protected boolean writeAlterColumnDataTypeToBigInt(ColumnDataTypeChange change, StringBuilder ddl) {
        writeTableAlterStmt(change.getChangedTable(), ddl);
        ddl.append(" ALTER COLUMN ");
        Column column = change.getChangedColumn();
        column.setTypeCode(change.getNewTypeCode());
        printIdentifier(getColumnName(column), ddl);
        ddl.append(" TYPE ");
        ddl.append(getSqlType(column));
        printEndOfStatement(ddl);
        return true;
    }

    @Override
    public void writeExternalIndexDropStmt(Table table, IIndex index, StringBuilder ddl) {
        ddl.append("DROP INDEX ");
        printIdentifier(getIndexName(index), ddl);
        printEndOfStatement(ddl);
    }

    @Override
    protected void createTable(Table table, StringBuilder ddl, boolean temporary, boolean recreate) {
        if (!temporary && !recreate) {
            for (int idx = 0; idx < table.getColumnCount(); idx++) {
                Column column = table.getColumn(idx);

                if (column.isAutoIncrement()) {
                    createAutoIncrementSequence(table, column, ddl);
                }
            }
        }
        super.createTable(table, ddl, temporary, recreate);
    }

    /*
     * Creates the auto-increment sequence that is then used in the column.
     * 
     * @param table The table
     * 
     * @param column The column
     */
    private void createAutoIncrementSequence(Table table, Column column, StringBuilder ddl) {
        if (isUsePseudoSequence()) {
            ddl.append("CREATE TABLE ");
            ddl.append(getConstraintName(null, table, column.getName(), "tbl"));
            ddl.append("(SEQ_ID int8)");
            printEndOfStatement(ddl);

            ddl.append("CREATE FUNCTION ");
            ddl.append(getConstraintName(null, table, column.getName(), "seq"));
            ddl.append("() ");
            ddl.append("RETURNS INT8 AS $$ ");
            ddl.append("DECLARE curVal int8; ");
            ddl.append("BEGIN ");
            ddl.append("  select seq_id into curVal from ");
            ddl.append(getConstraintName(null, table, column.getName(), "tbl"));
            ddl.append(" for update;");
            ddl.append("  if curVal is null then ");
            ddl.append("      insert into ");
            ddl.append(getConstraintName(null, table, column.getName(), "tbl"));
            ddl.append(" values(1); ");
            ddl.append("      curVal = 0; ");
            ddl.append("  else ");
            ddl.append("      update ");
            ddl.append(getConstraintName(null, table, column.getName(), "tbl"));
            ddl.append(" set seq_id=curVal+1; ");
            ddl.append("  end if; ");
            ddl.append("  return curVal+1; ");
            ddl.append("END; ");
            println("$$ LANGUAGE plpgsql; ", ddl);
        } else {
            ddl.append("CREATE SEQUENCE ");
            printIdentifier(getConstraintName(null, table, column.getName(), "seq"), ddl);
            printEndOfStatement(ddl);
        }
    }

    /*
     * Creates the auto-increment sequence that is then used in the column.
     * 
     * @param table The table
     * 
     * @param column The column
     */
    private void dropAutoIncrementSequence(Table table, Column column, StringBuilder ddl) {
        if (isUsePseudoSequence()) {
            ddl.append("DROP TABLE ");
            ddl.append(getConstraintName(null, table, column.getName(), "tbl"));
            printEndOfStatement(ddl);

            ddl.append("DROP FUNCTION ");
            ddl.append(getConstraintName(null, table, column.getName(), "seq"));
            ddl.append("()");
            printEndOfStatement(ddl);
        } else {
            ddl.append("DROP SEQUENCE ");
            printIdentifier(getConstraintName(null, table, column.getName(), "seq"), ddl);
            printEndOfStatement(ddl);
        }
    }

    @Override
    protected void writeColumnAutoIncrementStmt(Table table, Column column, StringBuilder ddl) {
        if (isUsePseudoSequence()) {
            ddl.append(" DEFAULT ");
            ddl.append(getConstraintName(null, table, column.getName(), "seq"));
            ddl.append("()");
        } else {
            ddl.append(" DEFAULT nextval('");
            printIdentifier(getConstraintName(null, table, column.getName(), "seq"), ddl);
            ddl.append("')");
        }
    }

    @Override
    public String getSelectLastIdentityValues(Table table) {
        Column[] columns = table.getAutoIncrementColumns();

        if (columns.length == 0) {
            return null;
        } else {
            StringBuffer result = new StringBuffer();

            result.append("SELECT ");
            for (int idx = 0; idx < columns.length; idx++) {
                if (idx > 0) {
                    result.append(", ");
                }
                result.append("currval('");
                result.append(getDelimitedIdentifier(getConstraintName(null, table,
                        columns[idx].getName(), "seq")));
                result.append("') AS ");
                result.append(getDelimitedIdentifier(columns[idx].getName()));
            }
            return result.toString();
        }
    }

    @Override
    protected void processTableStructureChanges(Database currentModel, Database desiredModel,
            Table sourceTable, Table targetTable, List<TableChange> changes, StringBuilder ddl) {
        for (Iterator<TableChange> changeIt = changes.iterator(); changeIt.hasNext();) {
            TableChange change = changeIt.next();

            if (change instanceof AddColumnChange) {
                AddColumnChange addColumnChange = (AddColumnChange) change;
                processChange(currentModel, desiredModel, addColumnChange, ddl);
                changeIt.remove();
            } else if (change instanceof RemoveColumnChange) {
                processChange(currentModel, desiredModel, (RemoveColumnChange) change, ddl);
                changeIt.remove();
            } else if (change instanceof CopyColumnValueChange) {
                CopyColumnValueChange copyColumnChange = (CopyColumnValueChange)change;
                processChange(currentModel, desiredModel, copyColumnChange, ddl);
                changeIt.remove();                           
            } else if (change instanceof ColumnDefaultValueChange) {
                processChange(currentModel, desiredModel, (ColumnDefaultValueChange) change, ddl);
                changeIt.remove();
            } else if (change instanceof ColumnRequiredChange) {
                processChange(currentModel, desiredModel, (ColumnRequiredChange) change, ddl);
                changeIt.remove();
            } else if (change instanceof ColumnSizeChange) {
                processChange(currentModel, desiredModel, (ColumnSizeChange) change, ddl);
                changeIt.remove();
            } else if (change instanceof PrimaryKeyChange) {
                processChange(currentModel, desiredModel, (PrimaryKeyChange) change, ddl);
                changeIt.remove();
            } else if (change instanceof ColumnAutoIncrementChange) {
                if (processChange(currentModel, desiredModel, (ColumnAutoIncrementChange) change,
                        ddl)) {
                    changeIt.remove();
                }
            }
        }
        super.processTableStructureChanges(currentModel, desiredModel, sourceTable, targetTable,
                changes, ddl);
    }

    protected void processChange(Database currentModel, Database desiredModel,
            PrimaryKeyChange change, StringBuilder ddl) {
        writeTableAlterStmt(change.getChangedTable(), ddl);
        printIndent(ddl);
        ddl.append(" DROP CONSTRAINT ");
        printIdentifier(change.getChangedTable().getPrimaryKeyConstraintName(), ddl);
        printEndOfStatement(ddl);

        writeTableAlterStmt(change.getChangedTable(), ddl);
        printIndent(ddl);
        ddl.append(" ADD ");
        writePrimaryKeyStmt(change.getChangedTable(), change.getNewPrimaryKeyColumns(), ddl);
        printEndOfStatement(ddl);

    }

    /*
     * Processes the addition of a column to a table.
     */
    protected void processChange(Database currentModel, Database desiredModel,
            AddColumnChange change, StringBuilder ddl) {
        writeTableAlterStmt(change.getChangedTable(), ddl);
        printIndent(ddl);
        ddl.append(" ADD COLUMN ");
        writeColumn(change.getChangedTable(), change.getNewColumn(), ddl);
        printEndOfStatement(ddl);
        change.apply(currentModel, delimitedIdentifierModeOn);
    }

    /*
     * Processes the removal of a column from a table.
     */
    protected void processChange(Database currentModel, Database desiredModel,
            RemoveColumnChange change, StringBuilder ddl) {
        writeTableAlterStmt(change.getChangedTable(), ddl);
        printIndent(ddl);
        ddl.append("DROP COLUMN ");
        printIdentifier(getColumnName(change.getColumn()), ddl);
        printEndOfStatement(ddl);
        if (change.getColumn().isAutoIncrement()) {
            dropAutoIncrementSequence(change.getChangedTable(), change.getColumn(), ddl);
        }
        change.apply(currentModel, delimitedIdentifierModeOn);
    }

    protected void processChange(Database currentModel, Database desiredModel,
            ColumnDefaultValueChange change, StringBuilder ddl) {
        if (change.getNewDefaultValue() == null
                && change.getChangedColumn().getDefaultValue() != null) {
            writeTableAlterStmt(change.getChangedTable(), ddl);
            ddl.append(" ALTER COLUMN ");
            Column column = change.getChangedColumn();
            printIdentifier(getColumnName(column), ddl);
            ddl.append(" DROP DEFAULT ");
            printEndOfStatement(ddl);
        } else {
            writeTableAlterStmt(change.getChangedTable(), ddl);
            ddl.append(" ALTER COLUMN ");
            Column column = change.getChangedColumn();
            printIdentifier(getColumnName(column), ddl);
            ddl.append(" SET DEFAULT ");
            printDefaultValue(change.getNewDefaultValue(), column.getMappedTypeCode(), ddl);
            printEndOfStatement(ddl);
        }
    }

    protected void processChange(Database currentModel, Database desiredModel,
            ColumnRequiredChange change, StringBuilder ddl) {
        boolean required = !change.getChangedColumn().isRequired();
        writeTableAlterStmt(change.getChangedTable(), ddl);
        ddl.append(" ALTER COLUMN ");
        Column column = change.getChangedColumn();
        printIdentifier(getColumnName(column), ddl);
        if (required) {
            ddl.append(" SET NOT NULL ");
        } else {
            ddl.append(" DROP NOT NULL ");
        }
        printEndOfStatement(ddl);
    }

    protected void processChange(Database currentModel, Database desiredModel,
            ColumnSizeChange change, StringBuilder ddl) {
        writeTableAlterStmt(change.getChangedTable(), ddl);
        ddl.append(" ALTER COLUMN ");
        Column column = change.getChangedColumn();
        column.setSizeAndScale(change.getNewSize(), change.getNewScale());
        printIdentifier(getColumnName(column), ddl);
        ddl.append(" TYPE ");
        ddl.append(getSqlType(column));
        printEndOfStatement(ddl);
    }

    protected boolean processChange(Database currentModel, Database desiredModel,
            ColumnAutoIncrementChange change, StringBuilder ddl) {
        boolean autoIncrement = !change.getColumn().isAutoIncrement();
        if (!autoIncrement) {
            writeTableAlterStmt(change.getChangedTable(), ddl);
            ddl.append(" ALTER COLUMN ");
            Column column = change.getColumn();
            printIdentifier(getColumnName(column), ddl);
            ddl.append(" DROP DEFAULT ");
            printEndOfStatement(ddl);
            return true;
        } else {
            return false;
        }
    }
    
    @Override
    protected void printDefaultValue(String defaultValue, int typeCode, StringBuilder ddl) {
        if (defaultValue != null && 
                ((defaultValue.endsWith("::uuid") && Types.OTHER == typeCode) ||
                 (defaultValue.contains("::") && Types.ARRAY == typeCode))) {
            ddl.append(defaultValue);
        } else {
            super.printDefaultValue(defaultValue, typeCode, ddl);
        }
    }

}