All Downloads are FREE. Search and download functionalities are using the official Maven repository.

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

Go to download

This is the core library for Active Objects. It is generic and can be embedded in any environment. As such it is generic and won't contain all connection pooling, etc.

There is a newer version: 6.1.1
Show newest version
/*
 * Copyright 2007 Daniel Spiewak
 * 
 * Licensed 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.
 */
package net.java.ao.db;

import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Iterables;
import com.google.common.collect.Lists;
import net.java.ao.Common;
import net.java.ao.DBParam;
import net.java.ao.DatabaseProvider;
import net.java.ao.DisposableDataSource;
import net.java.ao.EntityManager;
import net.java.ao.RawEntity;
import net.java.ao.schema.Case;
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.DDLIndexField;
import net.java.ao.schema.ddl.DDLTable;
import net.java.ao.schema.ddl.SQLAction;
import net.java.ao.types.TypeInfo;
import net.java.ao.types.TypeManager;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class PostgreSQLDatabaseProvider extends DatabaseProvider {
    private static final int MAX_SEQUENCE_LENGTH = 64;
    private static final String SQL_STATE_UNDEFINED_FUNCTION = "42883";
    private static final Pattern PATTERN_QUOTE_ID = Pattern.compile("(\\*|\\d*?)");

    public PostgreSQLDatabaseProvider(DisposableDataSource dataSource) {
        this(dataSource, "public");
    }

    public PostgreSQLDatabaseProvider(DisposableDataSource dataSource, String schema) {
        super(dataSource, schema, TypeManager.postgres());
    }

    @Override
    public Object parseValue(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;

            case Types.BIT:
                try {
                    return Byte.parseByte(value);
                } catch (Throwable t) {
                    try {
                        return Boolean.parseBoolean(value);
                    } catch (Throwable t1) {
                        return null;
                    }
                }
        }

        return super.parseValue(type, value);
    }

    @Override
    public ResultSet getTables(Connection conn) throws SQLException {
        return conn.getMetaData().getTables(null, getSchema(), null, new String[]{"TABLE"});
    }

    @Override
    protected String renderAutoIncrement() {
        return "";
    }

    @Override
    protected String renderFieldType(DDLField field) {
        if (field.getJdbcType() == Types.NUMERIC) // numeric is used by Oracle
        {
            field.setType(typeManager.getType(Integer.class));
        }
        if (field.isAutoIncrement()) {
            if (field.getJdbcType() == Types.BIGINT) {
                return "BIGSERIAL";
            }
            return "SERIAL";
        }
        return super.renderFieldType(field);
    }

    @Override
    protected String renderValue(Object value) {
        if (value instanceof Boolean) {
            if (value.equals(true)) {
                return "TRUE";
            }
            return "FALSE";
        }

        return super.renderValue(value);
    }

    @Override
    protected String renderUnique(UniqueNameConverter uniqueNameConverter, DDLTable table, DDLField field) {
        return "CONSTRAINT " + uniqueNameConverter.getName(table.getName(), field.getName()) + " UNIQUE";
    }

    @Override
    public Object handleBlob(ResultSet res, Class type, String field) throws SQLException {
        if (type.equals(InputStream.class)) {
            return res.getBinaryStream(field);
        } else if (type.equals(byte[].class)) {
            return res.getBytes(field);
        } else {
            return null;
        }
    }

    @Override
    protected Iterable renderAlterTableChangeColumn(NameConverters nameConverters, DDLTable table, DDLField oldField, DDLField field) {
        final UniqueNameConverter uniqueNameConverter = nameConverters.getUniqueNameConverter();

        final List back = Lists.newArrayList();

        if (!field.isUnique() && oldField.isUnique()) {
            // use oldField here (in case of a renamed column we need the old name)
            back.add(SQLAction.of(new StringBuilder().append("ALTER TABLE ").append(withSchema(table.getName())).append(" DROP CONSTRAINT ").append(uniqueNameConverter.getName(table.getName(), oldField.getName()))));
        }

        if (field.isUnique() && !oldField.isUnique()) {
            back.add(SQLAction.of(new StringBuilder().append("ALTER TABLE ").append(withSchema(table.getName())).append(" ADD CONSTRAINT ").append(uniqueNameConverter.getName(table.getName(), field.getName())).append(" UNIQUE (").append(processID(field.getName())).append(")")));
        }

        if (!field.getName().equalsIgnoreCase(oldField.getName())) {
            StringBuilder str = new StringBuilder();
            str.append("ALTER TABLE ").append(withSchema(table.getName())).append(" RENAME COLUMN ");
            str.append(processID(oldField.getName())).append(" TO ").append(processID(field.getName()));
            back.add(SQLAction.of(str));
        }

        if (!field.getType().equals(oldField.getType())) {
            StringBuilder str = new StringBuilder();
            str.append("ALTER TABLE ").append(withSchema(table.getName())).append(" ALTER COLUMN ");
            str.append(processID(field.getName())).append(" TYPE ");

            final boolean autoIncrement = field.isAutoIncrement();
            field.setAutoIncrement(false); // we don't want the auto increment property to be changed or even affect the change

            str.append(renderFieldType(field));
            back.add(SQLAction.of(str));

            field.setAutoIncrement(autoIncrement); // setting back to normal
        }

        if (field.getDefaultValue() == null && oldField.getDefaultValue() == null) {
            // dummy case
        } else if (field.getDefaultValue() == null && oldField.getDefaultValue() != null) {
            StringBuilder str = new StringBuilder();
            str.append("ALTER TABLE ").append(withSchema(table.getName())).append(" ALTER COLUMN ");
            str.append(processID(field.getName())).append(" DROP DEFAULT");
            back.add(SQLAction.of(str));
        } else if (!field.getDefaultValue().equals(oldField.getDefaultValue())) {
            StringBuilder str = new StringBuilder();
            str.append("ALTER TABLE ").append(withSchema(table.getName())).append(" ALTER COLUMN ");
            str.append(processID(field.getName())).append(" SET DEFAULT ").append(renderValue(field.getDefaultValue()));
            back.add(SQLAction.of(str));
        }

        if (field.isNotNull() != oldField.isNotNull()) {
            if (field.isNotNull()) {
                StringBuilder str = new StringBuilder();
                str.append("ALTER TABLE ").append(withSchema(table.getName())).append(" ALTER COLUMN ");
                str.append(processID(field.getName())).append(" SET NOT NULL");
                back.add(SQLAction.of(str));
            } else {
                StringBuilder str = new StringBuilder();
                str.append("ALTER TABLE ").append(withSchema(table.getName())).append(" ALTER COLUMN ");
                str.append(processID(field.getName())).append(" DROP NOT NULL");
                back.add(SQLAction.of(str));
            }
        }

        // if we don't have any ALTER TABLE DDL by this point then fall back to dropping and re-creating the column
        if (back.isEmpty()) {
            System.err.println("WARNING: Unable to modify column '" + table.getName() + "' in place. Going to drop and re-create column.");
            System.err.println("WARNING: Data contained in column '" + table.getName() + "." + oldField.getName() + "' will be lost");

            Iterables.addAll(back, renderAlterTableDropColumn(nameConverters, table, oldField));
            Iterables.addAll(back, renderAlterTableAddColumn(nameConverters, table, field));
        }

        return ImmutableList.builder()
                .addAll(renderDropAccessoriesForField(nameConverters, table, oldField))
                .addAll(back)
                .addAll(renderAccessoriesForField(nameConverters, table, field))
                .build();
    }

    @Override
    protected SQLAction renderAlterTableDropKey(DDLForeignKey key) {
        StringBuilder back = new StringBuilder("ALTER TABLE ");

        back.append(withSchema(key.getDomesticTable())).append(" DROP CONSTRAINT ").append(processID(key.getFKName()));

        return SQLAction.of(back);
    }

    @Override
    protected SQLAction renderCreateIndex(IndexNameConverter indexNameConverter, DDLIndex index) {
        String statement = "CREATE INDEX " + processID(index.getIndexName())
                + " ON " + withSchema(index.getTable()) +
                Stream.of(index.getFields())
                        .map(DDLIndexField::getFieldName)
                        .map(this::processID)
                        .collect(Collectors.joining(",", "(", ")"));

        return SQLAction.of(statement);
    }

    @Override
    protected SQLAction renderDropIndex(IndexNameConverter indexNameConverter, DDLIndex index) {
        final String indexName = index.getIndexName();
        final String tableName = index.getTable();
        if (hasIndex(tableName, indexName)) {
            return SQLAction.of(new StringBuilder("DROP INDEX ")
                    .append(withSchema(indexName)));
        } else {
            return null;
        }
    }

    @Override
    public , K> K insertReturningKey(EntityManager manager, Connection conn,
                                                            Class entityType, Class pkType,
                                                            String pkField, boolean pkIdentity, String table, DBParam... params) throws SQLException {
        K back = null;
        for (DBParam param : params) {
            if (param.getField().trim().equalsIgnoreCase(pkField)) {
                back = (K) param.getValue();
                break;
            }
        }

        if (back == null) {
            final String sql = "SELECT NEXTVAL('" + withSchema(sequenceName(pkField, table)) + "')";

            try (final PreparedStatement stmt = preparedStatement(conn, sql); final ResultSet res = stmt.executeQuery()) {
                if (res.next()) {
                    back = typeManager.getType(pkType).getLogicalType().pullFromDatabase(null, res, pkType, 1);
                }
            }

            List newParams = new ArrayList<>(Arrays.asList(params));

            newParams.add(new DBParam(pkField, back));
            params = newParams.toArray(new DBParam[newParams.size()]);
        }

        super.insertReturningKey(manager, conn, entityType, pkType, pkField, pkIdentity, table, params);

        return back;
    }

    private String sequenceName(String pkField, String table) {
        final String suffix = "_" + pkField + "_seq";
        final int tableLength = table.length();
        final int theoreticalLength = tableLength + suffix.length();
        if (theoreticalLength > MAX_SEQUENCE_LENGTH) {
            final int extraCharacters = theoreticalLength - MAX_SEQUENCE_LENGTH;
            return table.substring(0, tableLength - extraCharacters - 1) + suffix;
        } else {
            return table + suffix;
        }
    }

    @Override
    protected , K> K executeInsertReturningKey(EntityManager manager, Connection conn,
                                                                      Class entityType, Class pkType,
                                                                      String pkField, String sql, DBParam... params) throws SQLException {
        final PreparedStatement stmt = preparedStatement(conn, sql);

        for (int i = 0; i < params.length; i++) {
            Object value = params[i].getValue();

            if (value instanceof RawEntity) {
                value = Common.getPrimaryKeyValue((RawEntity) value);
            }

            if (value == null) {
                putNull(stmt, i + 1);
            } else {
                TypeInfo type = (TypeInfo) typeManager.getType(value.getClass());
                type.getLogicalType().putToDatabase(manager, stmt, i + 1, value, type.getJdbcWriteType());
            }
        }

        stmt.executeUpdate();
        stmt.close();

        return null;
    }

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

    @Override
    protected boolean shouldQuoteID(String id) {
        return !PATTERN_QUOTE_ID.matcher(id).matches();
    }

    @Override
    protected boolean shouldQuoteTableName(String tableName) {
        return getReservedWords().contains(Case.UPPER.apply(tableName));
    }

    @Override
    public void handleUpdateError(String sql, SQLException e) throws SQLException {
        if (e.getSQLState().equals(SQL_STATE_UNDEFINED_FUNCTION) && e.getMessage().contains("does not exist")) {
            logger.debug("Ignoring SQL exception for <" + sql + ">", e);
            return;
        }
        super.handleUpdateError(sql, e);
    }

    private static final Set RESERVED_WORDS = ImmutableSet.of(
            "ABS", "ABSOLUTE", "ACOS", "ACTION", "ADD", "ADMIN", "AFTER", "AGGREGATE",
            "ALIAS", "ALL", "ALLOCATE", "ALTER", "ANALYSE", "ANALYZE", "AND", "ANY", "ARE",
            "ARRAY", "ARRAY_AGG", "ARRAY_MAX_CARDINALITY", "AS", "ASC", "ASENSITIVE",
            "ASIN", "ASSERTION", "ASYMMETRIC", "AT", "ATAN", "ATOMIC", "AUTHORIZATION", "AVG",
            "BEFORE", "BEGIN", "BEGIN_FRAME", "BEGIN_PARTITION", "BETWEEN", "BIGINT", "BINARY",
            "BIT", "BIT_LENGTH", "BLOB", "BOOLEAN", "BOTH", "BREADTH", "BY", "CALL", "CALLED",
            "CARDINALITY", "CASCADE", "CASCADED", "CASE", "CAST", "CATALOG", "CEIL", "CEILING",
            "CHAR", "CHARACTER", "CHARACTER_LENGTH", "CHAR_LENGTH", "CHECK", "CLASS", "CLASSIFIER",
            "CLOB", "CLOSE", "COALESCE", "COLLATE", "COLLATION", "COLLECT", "COLUMN", "COMMIT",
            "COMPLETION", "CONDITION", "CONNECT", "CONNECTION", "CONSTRAINT", "CONSTRAINTS",
            "CONSTRUCTOR", "CONTAINS", "CONTINUE", "CONVERT", "COPY", "CORR", "CORRESPONDING",
            "COS", "COSH", "COUNT", "COVAR_POP", "COVAR_SAMP", "CREATE", "CROSS", "CUBE", "CUME_DIST",
            "CURRENT", "CURRENT_CATALOG", "CURRENT_DATE", "CURRENT_DEFAULT_TRANSFORM_GROUP",
            "CURRENT_PATH", "CURRENT_ROLE", "CURRENT_ROW", "CURRENT_SCHEMA", "CURRENT_TIME",
            "CURRENT_TIMESTAMP", "CURRENT_TRANSFORM_GROUP_FOR_TYPE", "CURRENT_USER",
            "CURRENT_DEFAULT_TRANSFORM_GROUP", "CURRENT_TRANSFORM_GROUP_FOR_TYPE",
            "CURSOR", "CYCLE", "DATA", "DATALINK", "DATE", "DAY", "DEALLOCATE", "DEC", "DECFLOAT", "DECIMAL",
            "DECLARE", "DEFAULT", "DEFERRABLE", "DEFERRED", "DEFINE", "DELETE", "DENSE_RANK", "DEPTH",
            "DEREF", "DESC", "DESCRIBE", "DESCRIPTOR", "DESTROY", "DESTRUCTOR", "DETERMINISTIC",
            "DIAGNOSTICS", "DICTIONARY", "DISCONNECT", "DISTINCT", "DLNEWCOPY", "DLPREVIOUSCOPY",
            "DLURLCOMPLETE", "DLURLCOMPLETEONLY", "DLURLCOMPLETEWRITE", "DLURLPATH", "DLURLPATHONLY",
            "DLURLPATHWRITE", "DLURLSCHEME", "DLURLSERVER", "DLVALUE", "DO", "DOMAIN", "DOUBLE", "DROP",
            "DYNAMIC", "EACH", "ELEMENT", "ELSE", "EMPTY", "END", "END-EXEC", "END_FRAME", "END_PARTITION",
            "EQUALS", "ESCAPE", "EVERY", "EXCEPT", "EXCEPTION", "EXEC", "EXECUTE", "EXISTS", "EXP",
            "EXTERNAL", "EXTRACT", "FALSE", "FETCH", "FILTER", "FIRST", "FIRST_VALUE", "FLOAT", "FLOOR",
            "FOR", "FOREIGN", "FOUND", "FRAME_ROW", "FREE", "FREEZE", "FROM", "FULL", "FUNCTION",
            "FUSION", "GENERAL", "GET", "GLOBAL", "GO", "GOTO", "GRANT", "GREATEST", "GROUP", "GROUPING",
            "GROUPS", "HAVING", "HOLD", "HOST", "HOUR", "IDENTITY", "IGNORE", "ILIKE", "IMMEDIATE",
            "IMPORT", "IN", "INDICATOR", "INITIAL", "INITIALIZE", "INITIALLY", "INNER", "INOUT",
            "INPUT", "INSENSITIVE", "INSERT", "INT", "INTEGER", "INTERSECT", "INTERSECTION", "INTERVAL",
            "INTO", "IS", "ISNULL", "ISOLATION", "ITERATE", "JOIN", "JSON_ARRAY", "JSON_ARRAYAGG",
            "JSON_EXISTS", "JSON_OBJECT", "JSON_OBJECTAGG", "JSON_QUERY", "JSON_TABLE",
            "JSON_TABLE_PRIMITIVE", "JSON_VALUE", "KEY", "LAG", "LANGUAGE", "LARGE", "LAST",
            "LAST_VALUE", "LATERAL", "LEAD", "LEADING", "LEAST", "LEFT", "LESS", "LEVEL", "LIKE",
            "LIKE_REGEX", "LIMIT", "LISTAGG", "LN", "LOCAL", "LOCALTIME", "LOCALTIMESTAMP", "LOCATOR",
            "LOG", "LOG10", "LOWER", "MAP", "MATCH", "MATCHES", "MATCH_NUMBER", "MATCH_RECOGNIZE",
            "MAX", "MEASURES", "MEMBER", "MERGE", "METHOD", "MIN", "MINUTE", "MOD", "MODIFIES", "MODIFY",
            "MODULE", "MONTH", "MULTISET", "NAMES", "NATIONAL", "NATURAL", "NCHAR", "NCLOB", "NEW",
            "NEXT", "NO", "NONE", "NORMALIZE", "NOT", "NOTNULL", "NTH_VALUE", "NTILE", "NULL", "NULLIF",
            "NUMERIC", "OBJECT", "OCCURRENCES_REGEX", "OCTET_LENGTH", "OF", "OFF", "OFFSET", "OLD",
            "OMIT", "ON", "ONE", "ONLY", "OPEN", "OPERATION", "OPTION", "OR", "ORDER", "ORDINALITY",
            "OUT", "OUTER", "OUTPUT", "OVER", "OVERLAPS", "OVERLAY", "PAD", "PARAMETER", "PARAMETERS",
            "PARTIAL", "PARTITION", "PATH", "PATTERN", "PER", "PERCENT", "PERCENTILE_CONT",
            "PERCENTILE_DISC", "PERCENT_RANK", "PERIOD", "PERMUTE", "PLACING", "PORTION", "POSITION",
            "POSITION_REGEX", "POSTFIX", "POWER", "PRECEDES", "PRECISION", "PREFIX", "PREORDER",
            "PREPARE", "PRESERVE", "PRIMARY", "PRIOR", "PRIVILEGES", "PROCEDURE", "PTF", "PUBLIC",
            "RANGE", "RANK", "READ", "READS", "REAL", "RECURSIVE", "REF", "REFERENCES", "REFERENCING",
            "REGR_AVGX", "REGR_AVGY", "REGR_COUNT", "REGR_INTERCEPT", "REGR_R2", "REGR_SLOPE",
            "REGR_SXX", "REGR_SXY", "REGR_SYY", "RELATIVE", "RELEASE", "RESTRICT", "RESULT", "RETURN",
            "RETURNING", "RETURNS", "REVOKE", "RIGHT", "ROLE", "ROLLBACK", "ROLLUP", "ROUTINE", "ROW",
            "ROWS", "ROW_NUMBER", "RUNNING", "SAVEPOINT", "SCHEMA", "SCOPE", "SCROLL", "SEARCH",
            "SECOND", "SECTION", "SEEK", "SELECT", "SENSITIVE", "SEQUENCE", "SESSION", "SESSION_USER",
            "SET", "SETOF", "SETS", "SHOW", "SIMILAR", "SIN", "SINH", "SIZE", "SKIP", "SMALLINT",
            "SOME", "SPACE", "SPECIFIC", "SPECIFICTYPE", "SQL", "SQLCODE", "SQLERROR", "SQLEXCEPTION",
            "SQLSTATE", "SQLWARNING", "SQRT", "START", "STATE", "STATEMENT", "STATIC", "STDDEV_POP",
            "STDDEV_SAMP", "STRUCTURE", "SUBMULTISET", "SUBSET", "SUBSTRING", "SUBSTRING_REGEX",
            "SUCCEEDS", "SUM", "SYMMETRIC", "SYSTEM", "SYSTEM_TIME", "SYSTEM_USER", "TABLE",
            "TABLESAMPLE", "TAN", "TANH", "TEMPORARY", "TERMINATE", "THAN", "THEN", "TIME",
            "TIMESTAMP", "TIMEZONE_HOUR", "TIMEZONE_MINUTE", "TO", "TRAILING", "TRANSACTION",
            "TRANSLATE", "TRANSLATE_REGEX", "TRANSLATION", "TREAT", "TRIGGER", "TRIM", "TRIM_ARRAY",
            "TRUE", "TRUNCATE", "UESCAPE", "UNDER", "UNION", "UNIQUE", "UNKNOWN", "UNMATCHED", "UNNEST",
            "UPDATE", "UPPER", "USAGE", "USER", "USING", "VALUE", "VALUES", "VALUE_OF", "VARBINARY",
            "VARCHAR", "VARIABLE", "VARIADIC", "VARYING", "VAR_POP", "VAR_SAMP", "VERBOSE",
            "VERSIONING", "VIEW", "WHEN", "WHENEVER", "WHERE", "WIDTH_BUCKET", "WINDOW", "WITH",
            "WITHIN", "WITHOUT", "WORK", "WRITE", "XML", "XMLAGG", "XMLATTRIBUTES", "XMLBINARY",
            "XMLCAST", "XMLCOMMENT", "XMLCONCAT", "XMLDOCUMENT", "XMLELEMENT", "XMLEXISTS", "XMLFOREST",
            "XMLITERATE", "XMLNAMESPACES", "XMLPARSE", "XMLPI", "XMLQUERY", "XMLSERIALIZE", "XMLTABLE",
            "XMLTEXT", "XMLVALIDATE", "YEAR", "ZONE");
}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy