NetezzaEnvironment.java

package dbfit.environment;

import dbfit.annotations.DatabaseEnvironment;
import dbfit.api.AbstractDbEnvironment;
import dbfit.util.DbParameterAccessor;
import dbfit.util.Direction;
import dbfit.util.NameNormaliser;

import javax.sql.RowSet;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Pattern;

@DatabaseEnvironment(name="Netezza", driver="org.netezza.Driver")
public class NetezzaEnvironment extends AbstractDbEnvironment {
    public NetezzaEnvironment(String driverClassName) {
        super(driverClassName);
    }

    protected String getConnectionString(String dataSource) {
        return "jdbc:netezza://" + dataSource;
    }

    protected String getConnectionString(String dataSource, String database) {
        return "jdbc:netezza://" + dataSource + "/" + database;
    }

    private static String paramNamePattern = "_:([A-Za-z0-9_]+)";
    private static Pattern paramsNames = Pattern.compile(paramNamePattern);

    public Pattern getParameterPattern() {
        return paramsNames;
    }

    // override the buildInsertPreparedStatement to leave out RETURN_GENERATED_KEYS
    public PreparedStatement buildInsertPreparedStatement(String tableName,
            DbParameterAccessor[] accessors) throws SQLException {
        return getConnection().prepareStatement(
                buildInsertCommand(tableName, accessors));
    }

    // netezza jdbc driver does not support named parameters - so just map them
    // to standard jdbc question marks
    protected String parseCommandText(String commandText) {
        commandText = commandText.replaceAll(paramNamePattern, "?");
        return super.parseCommandText(commandText);
    }

    public Map<String, DbParameterAccessor> getAllColumns(String tableOrViewName)
            throws SQLException {
        String[] qualifiers = NameNormaliser.normaliseName(tableOrViewName)
                .split("\\.");
        String qry = " select ATTNAME, FORMAT_TYPE, ATTLEN from _v_relation_column where ";

        if (qualifiers.length == 2) {
            qry += " lower(owner)=? and lower(name)=? ";
        } else {
            qry += " (lower(name)=?)";
        }
        qry += " order by attnum";
        return readIntoParams(qualifiers, qry);
    }

    private Map<String, DbParameterAccessor> readIntoParams(
            String[] queryParameters, String query) throws SQLException {
        try (PreparedStatement dc = currentConnection.prepareStatement(query)) {
            for (int i = 0; i < queryParameters.length; i++) {
                dc.setString(i + 1,
                        NameNormaliser.normaliseName(queryParameters[i]));
            }
            ResultSet rs = dc.executeQuery();
            Map<String, DbParameterAccessor> allParams = new HashMap<String, DbParameterAccessor>();
            int position = 0;
            while (rs.next()) {
                String paramName = rs.getString(1);
                if (paramName == null) {
                    paramName = "";
                }
                String dataType = rs.getString(2);
                DbParameterAccessor dbp = createDbParameterAccessor(
                        paramName,
                        Direction.INPUT, getSqlType(dataType),
                        getJavaClass(dataType), position++);
                allParams.put(NameNormaliser.normaliseName(paramName), dbp);
            }
            rs.close();
            return allParams;
        }
    }

    // List interface has sequential search, so using list instead of array to
    // map types
    private static List<String> stringTypes = Arrays.asList(new String[] {
            "VARCHAR", "CHAR", "CHARACTER", "CHARACTER VARYING", "VARYING", "TEXT",
            "NAME", "XML", "BPCHAR", "UNKNOWN", "NVCHAR" ,"NCHAR", "NATIONAL CHARACTER VARYING", "NATIONAL CHARACTER"});
    private static List<String> intTypes = Arrays.asList(new String[] {
            "INT", "INT4", "INTEGER", "SERIAL" });
    private static List<String> tinyintTypes = Arrays.asList(new String[] {
            "BYTEINT","INT1"});
    private static List<String> smallintTypes = Arrays.asList(new String[] {
            "SMALLINT", "INT2"});
    private static List<String> longTypes = Arrays.asList(new String[] {
            "BIGINT", "BIGSERIAL", "INT8" });
    private static List<String> floatTypes = Arrays.asList(new String[] {
            "REAL", "FLOAT4" });
    private static List<String> doubleTypes = Arrays.asList(new String[] {
            "DOUBLE PRECISION", "FLOAT8", "FLOAT","DOUBLE" });
    private static List<String> decimalTypes = Arrays.asList(new String[] {
            "DECIMAL", "NUMERIC" });
    private static List<String> dateTypes = Arrays
            .asList(new String[] { "DATE" });
    private static List<String> timestampTypes = Arrays.asList(new String[] {
            "TIMESTAMP", "TIMESTAMP WITHOUT TIME ZONE",
            "TIMESTAMP WITH TIME ZONE", "TIMESTAMPTZ" });
    private static List<String> refCursorTypes = Arrays
        .asList(new String[] { "REFTABLE" });
    private static List<String> booleanTypes = Arrays.asList(new String[] {
            "BOOL", "BOOLEAN" });

    private static String normaliseTypeName(String dataType) {
        if (dataType.indexOf("(") <= 0) {
           dataType = dataType.toUpperCase().trim();
        } else {
           dataType = dataType.toUpperCase().trim().substring(0,dataType.indexOf("("));
        }
        return dataType;
    }

    private static int getSqlType(String dataType) {
        // todo:strip everything from first blank
        dataType = normaliseTypeName(dataType);

        if (stringTypes.contains(dataType))
            return java.sql.Types.VARCHAR;
        if (decimalTypes.contains(dataType))
            return java.sql.Types.NUMERIC;
        if (intTypes.contains(dataType))
            return java.sql.Types.INTEGER;
        if (tinyintTypes.contains(dataType))
            return java.sql.Types.TINYINT;
        if (smallintTypes.contains(dataType))
            return java.sql.Types.SMALLINT;
        if (floatTypes.contains(dataType))
            return java.sql.Types.FLOAT;
        if (doubleTypes.contains(dataType))
            return java.sql.Types.DOUBLE;
        if (longTypes.contains(dataType))
            return java.sql.Types.BIGINT;
        if (timestampTypes.contains(dataType))
            return java.sql.Types.TIMESTAMP;
        if (dateTypes.contains(dataType))
            return java.sql.Types.DATE;
        if (refCursorTypes.contains(dataType))
            return java.sql.Types.REF;
        if (booleanTypes.contains(dataType))
            return java.sql.Types.BOOLEAN;
        throw new UnsupportedOperationException("Type " + dataType
                + " is not supported");
    }

    public Class getJavaClass(String dataType) {
        dataType = normaliseTypeName(dataType);
        if (stringTypes.contains(dataType))
            return String.class;
        if (decimalTypes.contains(dataType))
            return BigDecimal.class;
        if (intTypes.contains(dataType))
            return Integer.class;
        if (tinyintTypes.contains(dataType))
            return Byte.class;
        if (smallintTypes.contains(dataType))
            return Short.class;
        if (floatTypes.contains(dataType))
            return Float.class;
        if (dateTypes.contains(dataType))
            return java.sql.Date.class;
        if (refCursorTypes.contains(dataType))
            return RowSet.class;
        if (doubleTypes.contains(dataType))
            return Double.class;
        if (longTypes.contains(dataType))
            return Long.class;
        if (timestampTypes.contains(dataType))
            return java.sql.Timestamp.class;
        if (booleanTypes.contains(dataType))
            return Boolean.class;
        throw new UnsupportedOperationException("Type " + dataType
                + " is not supported");
    }

    public Map<String, DbParameterAccessor> getAllProcedureParameters(
            String procName) throws SQLException {

        String[] qualifiers = NameNormaliser.normaliseName(procName).split(
                "\\.");
        String qry = "select btrim(btrim(arguments,'('),')') as param_list from _v_procedure where 1=1";
        if (qualifiers.length == 3) {
            qry += " and lower(database)=? and lower(schema)=? and lower(procedure)=? ";
        } else if (qualifiers.length == 2) {
            qry += " and lower(schema)=? and lower(procedure)=? ";
        } else {
            qry += " and lower(procedure)=? ";
        }

        String paramList;

        try (PreparedStatement dc = currentConnection.prepareStatement(qry)) {
            for (int i = 0; i < qualifiers.length; i++) {
                dc.setString(i + 1, NameNormaliser.normaliseName(qualifiers[i]));
            }
            ResultSet rs = dc.executeQuery();
            if (!rs.next()) {
                throw new SQLException("Unknown procedure " + procName);
            }
            paramList = rs.getString(1);
            rs.close();
        }

        int position = 0;
        Direction direction = Direction.INPUT;
        String paramName;
        String dataType;
        String token;
        Map<String, DbParameterAccessor> allParams = new HashMap<String, DbParameterAccessor>();

        if (paramList.length() != 0) {
            for (String param : paramList.split(",")) {

                StringTokenizer s = new StringTokenizer(param.trim().toLowerCase(),
                        " ()");

                token = s.nextToken();
                paramName = "$" + (position + 1);

                dataType = normaliseTypeName(param);

                DbParameterAccessor dbp = createDbParameterAccessor(
                        paramName,
                        direction, getSqlType(dataType), getJavaClass(dataType),
                        position++);
                allParams.put(NameNormaliser.normaliseName(paramName), dbp);
            }
        }
        return allParams;
    }
}