All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

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

    /*
 * 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",
            "NULL_ORDERING", "NUMERIC", "OBJECT", "OCCURRENCE", "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", "PIPE", "PLACING", "PORTION", "POSITION",
            "POSITION_REGEX", "POSTFIX", "POWER", "PRECEDES", "PRECISION", "PREFIX", "PREORDER",
            "PREPARE", "PRESERVE", "PREV", "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", "SEMANTICS", "SENSITIVE", "SEQUENCE", "SESSION", "SESSION_USER",
            "SET", "SETOF", "SETS", "SHOW", "SIMILAR", "SIN", "SINH", "SIZE", "SKIP", "SMALLINT",
            "SOME", "SORT_DIRECTION", "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 - 2025 Weber Informatics LLC | Privacy Policy