net.java.ao.db.H2DatabaseProvider Maven / Gradle / Ivy

Go to download
package net.java.ao.db;

import com.atlassian.annotations.VisibleForTesting;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
import net.java.ao.DatabaseProvider;
import net.java.ao.DisposableDataSource;
import net.java.ao.Query;
import net.java.ao.schema.IndexNameConverter;
import net.java.ao.schema.NameConverters;
import net.java.ao.schema.UniqueNameConverter;
import net.java.ao.schema.ddl.DDLField;
import net.java.ao.schema.ddl.DDLForeignKey;
import net.java.ao.schema.ddl.DDLIndex;
import net.java.ao.schema.ddl.DDLTable;
import net.java.ao.schema.ddl.SQLAction;
import net.java.ao.types.TypeManager;
import net.java.ao.util.H2VersionUtil;

import java.sql.Types;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.google.common.collect.Iterables.concat;

public class H2DatabaseProvider extends DatabaseProvider {
    private final H2VersionUtil h2VersionUtil;

    public H2DatabaseProvider(final DisposableDataSource dataSource) {
        this(dataSource, "PUBLIC");
    }

    public H2DatabaseProvider(final DisposableDataSource dataSource, final String schema) {
        super(dataSource, schema, TypeManager.h2());
        h2VersionUtil = new H2VersionUtil(dataSource);
    }

    @Override
    protected String renderQueryLimit(final Query query) {
        final StringBuilder sql = new StringBuilder();

        // H2 requires a LIMIT when OFFSET is specified; -1 indicates unlimited
        //Invalid value "-1" (not recognizing "-1 "not a Max value), it is getting error , so changing to Integer.MAX_VALUE.
        if (query.getLimit() < 0 && query.getOffset() > 0) {
            if (!h2VersionUtil.isH2Latest2_1_X()){
                sql.append(" LIMIT -1");
            }
        }

        sql.append(super.renderQueryLimit(query));

        return sql.toString();
    }

    @Override
    protected Iterable renderAlterTableAddColumn(final NameConverters nameConverters, final DDLTable table, final DDLField field) {
        final Iterable back = super.renderAlterTableAddColumn(nameConverters, table, field);

        if (field.isUnique()) {
            return concat(back, ImmutableList.of(renderAddUniqueConstraint(nameConverters.getUniqueNameConverter(), table, field)));
        }

        return back;
    }

    @Override
    protected Iterable renderAlterTableChangeColumn(final NameConverters nameConverters, final DDLTable table, final DDLField oldField, final DDLField field) {
        final ImmutableList.Builder back = ImmutableList.builder();

        back.addAll(super.renderAlterTableChangeColumn(nameConverters, table, oldField, field));

        if (!field.isPrimaryKey()) {
            if (oldField.isUnique() && !field.isUnique()) {
                back.add(renderDropUniqueConstraint(nameConverters.getUniqueNameConverter(), table, field));
            } else if (!oldField.isUnique() && field.isUnique()) {
                back.add(renderAddUniqueConstraint(nameConverters.getUniqueNameConverter(), table, field));
            }
        }

        return back.build();
    }

    @Override
    protected SQLAction renderAlterTableChangeColumnStatement(final NameConverters nameConverters, final DDLTable table, final DDLField oldField, final DDLField field, final RenderFieldOptions options) {
        final StringBuilder sql = new StringBuilder();
        sql.append("ALTER TABLE ");
        sql.append(withSchema(table.getName()));
        sql.append(" ALTER COLUMN ");
        //AutoIncrement is not considering existing inserted data when alter the datatype of primary key.
        //To check oldfield and new field datatype is different
        if (h2VersionUtil.isH2Latest2_1_X() && oldField.isNotNull() && field.isNotNull()
                && field.isPrimaryKey()
                && !field.getType().equals(oldField.getType())) {
            sql.append(renderFieldForLatestH2(table, field));
        } else {
            sql.append(renderField(nameConverters, table, field, options));
            if (oldField.isNotNull() && !field.isNotNull()) {
                sql.append(" NULL ");
            }
        }
        return SQLAction.of(sql);
    }

    protected String renderFieldForLatestH2(final DDLTable table, final DDLField field) {
        StringBuilder back = new StringBuilder();
        //AutoIncrement is not considering existing inserted data when alter the datatype of primary key.
        //Autoincrement should consider existing data(max(id)+1).
        back.append(field.getName());
        back.append(" ");
        back.append(field.getType().getSchemaProperties().getSqlTypeName());
        back.append(" GENERATED BY DEFAULT AS IDENTITY(RESTART WITH (SELECT MAX(");
        back.append(field.getName());
        back.append(") FROM ");
        back.append(withSchema(table.getName())).append(")+1)");

        return back.toString();
    }

    protected String renderConstraints(NameConverters nameConverters, List primaryKeys, DDLTable table){
        StringBuilder back = new StringBuilder();

        if (primaryKeys.size() > 0) {
            back.append("    PRIMARY KEY(").append(processID(primaryKeys.get(0))).append(")");
        }

        back.append(renderConstraintsForTable(
                nameConverters.getUniqueNameConverter(), table));

        return back.toString();
    }

    @Override
    protected String renderFieldDefault(final DDLTable table, final DDLField field) {
        final StringBuilder sql = new StringBuilder();

        if (field.getDefaultValue() != null) {
            sql.append(" DEFAULT ").append(renderValue(field.getDefaultValue()));
        }

        return sql.toString();
    }

    @Override
    protected SQLAction renderAlterTableDropKey(final DDLForeignKey key) {
        final StringBuilder sql = new StringBuilder();

        sql.append("ALTER TABLE ");
        sql.append(withSchema(key.getDomesticTable()));
        sql.append(" DROP CONSTRAINT ");
        sql.append(processID(key.getFKName()));

        return SQLAction.of(sql);
    }

    @Override
    protected SQLAction renderDropIndex(final IndexNameConverter indexNameConverter, final DDLIndex index) {
        return SQLAction.of(new StringBuilder()
                .append("DROP INDEX IF EXISTS ")
                .append(withSchema(index.getIndexName()))
        );
    }

    @Override
    protected String renderConstraintsForTable(final UniqueNameConverter uniqueNameConverter, final DDLTable table) {
        StringBuilder sql = new StringBuilder();
        int count = 0;
        boolean uniqueCheck = false;
        int uniqueCount = 0;
        int checkUnique = 0;

        for (final DDLField field : table.getFields()) {
            if (field.isUnique()) {
                uniqueCheck = true;
                uniqueCount ++;
            }
        }

        if(table.getForeignKeys().length > 0) {
            sql.append(",\n    ");
            for (DDLForeignKey key : table.getForeignKeys()) {
                count++;
                sql.append(renderForeignKey(key));
                if(table.getForeignKeys().length > count) {
                    sql.append(",\n");
                }
            }
        }

        if(uniqueCheck) {
            sql.append(",\n   ");
            for (final DDLField field : table.getFields()) {
                if (field.isUnique()) {
                    checkUnique++;
                    sql.append(renderUniqueConstraint(uniqueNameConverter, table, field));
                    if (uniqueCount > checkUnique) {
                        sql.append(",\n   ");
                    }
                }
            }
        }

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

    }

    @Override
    protected String renderUnique(final UniqueNameConverter uniqueNameConverter, final DDLTable table, final DDLField field) {
        return "";
    }

    @Override
    public Object parseValue(final int type, String value) {
        if (value == null || value.equals("") || value.equals("NULL")) {
            return null;
        }

        switch (type) {
            case Types.TIMESTAMP:
            case Types.DATE:
            case Types.TIME:
            case Types.VARCHAR:
                Matcher matcher = Pattern.compile("'(.*)'.*").matcher(value);
                if (matcher.find()) {
                    value = matcher.group(1);
                }
                break;
        }

        return super.parseValue(type, value);
    }

    @Override
    protected Set getReservedWords() {
        return RESERVED_WORDS;
    }

    private static final Set RESERVED_WORDS = ImmutableSet.of(
            "ALL", // since 1.4.198
            //"AND", // since 1.4.199. See also additionalKeywords flag
            "ARRAY", // since 1.4.198
            //"BETWEEN", // since 1.4.199. See also additionalKeywords flag
            //"BOTH", // since 1.4.199. See also additionalKeywords flag
            "CASE", // since 1.4.198
            "CHECK", // since 1.4.196
            "CONSTRAINT", // since 1.4.198
            "CROSS",
            "CURRENT_DATE",
            "CURRENT_TIME",
            "CURRENT_TIMESTAMP",
            "CURRENT_USER", // since 1.4.198
            "DISTINCT",
            "EXCEPT",
            "EXISTS",
            "FALSE",
            "FETCH", // since 1.3.176 at least. See also supportsOffsetFetch flag (<1.4.198)
            //"FILTER", // since 1.4.199. See also additionalKeywords flag
            "FOR",
            "FOREIGN", // since 1.4.198
            "FROM",
            "FULL",
            "GROUP",
            //"GROUPS", // since 1.4.199. See also additionalKeywords flag
            "HAVING",
            "IF", // since 1.4.198
            //"ILIKE", // since 1.4.199. See also additionalKeywords flag
            //"IN", // since 1.4.199. See also additionalKeywords flag
            "INNER",
            "INTERSECT",
            "INTERSECTS", // since 1.4.198
            "INTERVAL", // since 1.4.198
            "IS",
            "JOIN",
            //"LEADING", // since 1.4.199. See also additionalKeywords flag
            //"LEFT", // since 1.4.199. See also additionalKeywords flag
            "LIKE",
            "LIMIT",
            "LOCALTIME", // since 1.4.198
            "LOCALTIMESTAMP", // since 1.4.198
            "MINUS",
            "NATURAL",
            "NOT",
            "NULL",
            "OFFSET", // since 1.3.176 at least. See also supportsOffsetFetch flag (<1.4.198)
            "ON",
            //"OR", // since 1.4.199. See also additionalKeywords flag
            "ORDER",
            //"OVER", // since 1.4.199. See also additionalKeywords flag
            //"PARTITION", // since 1.4.199. See also additionalKeywords flag
            "PRIMARY",
            "QUALIFY", // since 1.4.198
            //"RANGE", // since 1.4.199. See also additionalKeywords flag
            //"REGEXP", // since 1.4.199. See also additionalKeywords flag
            //"RIGHT", // since 1.4.199. See also additionalKeywords flag
            "ROW", // since 1.4.198
            //"ROWS", // since 1.4.199. See also additionalKeywords flag
            "ROWNUM",
            "SELECT",
            "SYSDATE", // See also additionalKeywords flag (14.198+)
            "SYSTIME", // See also additionalKeywords flag (14.198+)
            "SYSTIMESTAMP", // See also additionalKeywords flag (14.198+)
            "TABLE", // since 1.4.198
            "TODAY", // See also additionalKeywords flag (14.198+)
            //"TOP", // since 1.4.198. See also additionalKeywords flag
            //"TRAILING", // since 1.4.199. See also additionalKeywords flag
            "TRUE",
            "UNION",
            "UNIQUE",
            "VALUES", // since 1.4.198
            "WHERE",
            "WINDOW", // since 1.4.198
            "WITH", // since 1.3.176 at least
            "AND",  // since 2.1.210
            "SECOND", // since 2.1.210
            "KEY",    // since 2.1.210
            "VALUE",   // since 2.1.210
            "SYSTEM_USER", // since 2.1.210
            "USER",    // since 2.1.210
            "DAY", // since 2.1.210
            "DEFAULT", // since 2.1.210
            "END", // since 2.1.210
            "TO" // since 2.1.210
    );

    private SQLAction renderAddUniqueConstraint(UniqueNameConverter uniqueNameConverter, DDLTable table, DDLField field) {
        final StringBuilder sql = new StringBuilder();

        sql.append("ALTER TABLE ");
        sql.append(withSchema(table.getName()));
        sql.append(" ADD ");
        sql.append(renderUniqueConstraint(uniqueNameConverter, table, field));

        return SQLAction.of(sql);
    }

    private SQLAction renderDropUniqueConstraint(UniqueNameConverter uniqueNameConverter, DDLTable table, DDLField field) {
        final StringBuilder sql = new StringBuilder();

        sql.append("ALTER TABLE ");
        sql.append(withSchema(table.getName()));
        sql.append(" DROP CONSTRAINT ");
        sql.append(uniqueNameConverter.getName(table.getName(), field.getName()));

        return SQLAction.of(sql);
    }

    private String renderUniqueConstraint(UniqueNameConverter uniqueNameConverter, DDLTable table, DDLField field) {
        final StringBuilder sql = new StringBuilder();

        sql.append(" CONSTRAINT ");
        sql.append(uniqueNameConverter.getName(table.getName(), field.getName()));
        sql.append(" UNIQUE(");
        sql.append(processID(field.getName()));
        sql.append(")");

        return sql.toString();
    }

    @VisibleForTesting
    public H2VersionUtil getH2VersionUtil() {
        return h2VersionUtil;
    }
}