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

    Go to download

    This is the full Active Objects library, if you don't know which one to use, you probably want this one.

    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 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 com.google.common.collect.ImmutableSet;

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.IndexNameConverter;
import net.java.ao.schema.NameConverters;
import net.java.ao.schema.SequenceNameConverter;
import net.java.ao.schema.TriggerNameConverter;
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.types.TypeInfo;
import net.java.ao.types.TypeManager;
import net.java.ao.util.StringUtils;

public class PostgreSQLDatabaseProvider extends DatabaseProvider
{
    private static final int MAX_SEQUENCE_LENGTH = 64;
    private static final String SQL_STATE_UNDEFINED_FUNCTION = "42883";

    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 List renderAlterTableChangeColumn(NameConverters nameConverters, DDLTable table, DDLField oldField, DDLField field) {
        final TriggerNameConverter triggerNameConverter = nameConverters.getTriggerNameConverter();
        final SequenceNameConverter sequenceNameConverter = nameConverters.getSequenceNameConverter();
        final UniqueNameConverter uniqueNameConverter = nameConverters.getUniqueNameConverter();

        final List back = new ArrayList();

        String trigger = getTriggerNameForField(triggerNameConverter, table, oldField);
		if (trigger != null) {
			StringBuilder str = new StringBuilder();
			str.append("DROP TRIGGER ").append(processID(trigger));
			back.add(str.toString());
		}

		String function = getFunctionNameForField(triggerNameConverter, table, oldField);
		if (function != null) {
			StringBuilder str = new StringBuilder();
			str.append("DROP FUNCTION IF EXISTS ").append(withSchema(function));
			back.add(str.toString());
		}

        if (!field.isUnique() && oldField.isUnique())
        {
            back.add(new StringBuilder().append("ALTER TABLE ").append(withSchema(table.getName())).append(" DROP CONSTRAINT ").append(uniqueNameConverter.getName(table.getName(), field.getName())).toString());
        }

        if (field.isUnique() && !oldField.isUnique())
        {
            back.add(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(")").toString());
        }

		boolean foundChange = false;
		if (!field.getName().equalsIgnoreCase(oldField.getName())) {
			foundChange = true;

			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(str.toString());
		}

		if (!field.getType().equals(oldField.getType())) {
			foundChange = true;

			StringBuilder str = new StringBuilder();
			str.append("ALTER TABLE ").append(withSchema(table.getName())).append(" ALTER COLUMN ");
			str.append(processID(field.getName())).append(" TYPE ");
			str.append(renderFieldType(field));
			back.add(str.toString());
		}

		if (field.getDefaultValue() == null && oldField.getDefaultValue() == null) {
			// dummy case
		} else if (field.getDefaultValue() == null && oldField.getDefaultValue() != null) {
			foundChange = true;

			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(str.toString());
		} else if (!field.getDefaultValue().equals(oldField.getDefaultValue())) {
			foundChange = true;

			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(str.toString());
		}

		if (field.isNotNull() != oldField.isNotNull()) {
			foundChange = true;

			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(str.toString());
			} 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(str.toString());
			}
		}

		if (!foundChange) {
			System.err.println("WARNING: PostgreSQL doesn't fully support CHANGE TABLE statements");
			System.err.println("WARNING: Data contained in column '" + table.getName() + "." + oldField.getName() + "' will be lost");

			back.addAll(Arrays.asList(renderAlterTableDropColumn(triggerNameConverter, table, oldField)));
			back.addAll(renderAlterTableAddColumn(nameConverters, table, field));
		}

		String toRender = renderFunctionForField(triggerNameConverter, table, field);
		if (toRender != null) {
			back.add(toRender);
		}

		toRender = renderTriggerForField(triggerNameConverter, sequenceNameConverter, table, field);
		if (toRender != null) {
			back.add(toRender);
		}

        return back;
    }

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

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

		return back.toString();
	}

    @Override
    protected String renderCreateIndex(IndexNameConverter indexNameConverter, DDLIndex index)
    {
        return new StringBuilder().append("CREATE INDEX ")
                .append(processID(indexNameConverter.getName(shorten(index.getTable()), shorten(index.getField()))))
                .append(" ON ").append(withSchema(index.getTable()))
                .append('(').append(processID(index.getField())).append(')')
                .toString();
    }

    @Override
    protected String renderDropIndex(IndexNameConverter indexNameConverter, DDLIndex index)
    {
        if (hasIndex(indexNameConverter, index))
        {
            return new StringBuilder("DROP INDEX ")
                    .append(processID(indexNameConverter.getName(shorten(index.getTable()), shorten(index.getField()))))
                    .toString();
        }
        else
        {
            return "";
        }
    }

    protected String[] renderDropFunctions(TriggerNameConverter triggerNameConverter, DDLTable table)
    {
        final List dropFunctions = new ArrayList();
        for (DDLField field : table.getFields())
        {
            String function = renderDropFunction(getFunctionNameForField(triggerNameConverter, table, field));
            if(!StringUtils.isBlank(function))
            {
                dropFunctions.add(function);
            }
        }
        return dropFunctions.toArray(new String[dropFunctions.size()]);
    }

    private String renderDropFunction(String functionName)
    {
        if(!StringUtils.isBlank(functionName))
        {
            return "DROP FUNCTION IF EXISTS " + (isSchemaNotEmpty() ? getSchema() + "." + functionName : functionName)+ " CASCADE";
        }
        return null;
    }

    @Override
    public synchronized , 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('" + processID(sequenceName(pkField, table)) + "')";

			final PreparedStatement stmt = preparedStatement(conn, sql);

			ResultSet res = stmt.executeQuery();
			if (res.next()) {
				 back = typeManager.getType(pkType).getLogicalType().pullFromDatabase(null, res, pkType, 1);
			}
			res.close();
			stmt.close();

			List newParams = new ArrayList();
			newParams.addAll(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 !"*".equals(id);
    }

    @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", "ACTION", "ADD", "ADMIN", "AFTER", "AGGREGATE",
            "ALIAS", "ALL", "ALLOCATE", "ALTER", "ANALYSE", "ANALYZE", "AND", "ANY", "ARE",
            "ARRAY", "AS", "ASC", "ASENSITIVE", "ASSERTION", "ASYMMETRIC", "AT", "ATOMIC",
            "AUTHORIZATION", "AVG", "BEFORE", "BEGIN", "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", "CLOB", "CLOSE", "COALESCE", "COLLATE", "COLLATION", "COLLECT",
            "COLUMN", "COMMIT", "COMPLETION", "CONDITION", "CONNECT", "CONNECTION",
            "CONSTRAINT", "CONSTRAINTS", "CONSTRUCTOR", "CONTINUE", "CONVERT", "CORR",
            "CORRESPONDING", "COUNT", "COVAR_POP", "COVAR_SAMP", "CREATE", "CROSS", "CUBE",
            "CUME_DIST", "CURRENT", "CURRENT_DATE", "CURRENT_DEFAULT_TRANSFORM_GROUP",
            "CURRENT_PATH", "CURRENT_ROLE", "CURRENT_TIME", "CURRENT_TIMESTAMP",
            "CURRENT_TRANSFORM_GROUP_FOR_TYPE", "CURRENT_USER", "CURSOR", "CYCLE", "DATA",
            "DATE", "DAY", "DEALLOCATE", "DEC", "DECIMAL", "DECLARE", "DEFAULT",
            "DEFERRABLE", "DEFERRED", "DELETE", "DENSE_RANK", "DEPTH", "DEREF", "DESC",
            "DESCRIBE", "DESCRIPTOR", "DESTROY", "DESTRUCTOR", "DETERMINISTIC",
            "DIAGNOSTICS", "DICTIONARY", "DISCONNECT", "DISTINCT", "DO", "DOMAIN", "DOUBLE",
            "DROP", "DYNAMIC", "EACH", "ELEMENT", "ELSE", "END", "END-EXEC", "EQUALS",
            "ESCAPE", "EVERY", "EXCEPT", "EXCEPTION", "EXEC", "EXECUTE", "EXISTS", "EXP",
            "EXTERNAL", "EXTRACT", "FALSE", "FETCH", "FILTER", "FIRST", "FLOAT", "FLOOR",
            "FOR", "FOREIGN", "FOUND", "FREE", "FREEZE", "FROM", "FULL", "FUNCTION",
            "FUSION", "GENERAL", "GET", "GLOBAL", "GO", "GOTO", "GRANT", "GREATEST", "GROUP",
            "GROUPING", "HAVING", "HOLD", "HOST", "HOUR", "IDENTITY", "IGNORE", "ILIKE",
            "IMMEDIATE", "IN", "INDICATOR", "INITIALIZE", "INITIALLY", "INNER", "INOUT",
            "INPUT", "INSENSITIVE", "INSERT", "INT", "INTEGER", "INTERSECT", "INTERSECTION",
            "INTERVAL", "INTO", "IS", "ISNULL", "ISOLATION", "ITERATE", "JOIN", "KEY",
            "LANGUAGE", "LARGE", "LAST", "LATERAL", "LEADING", "LEAST", "LEFT", "LESS",
            "LEVEL", "LIKE", "LIMIT", "LN", "LOCAL", "LOCALTIME", "LOCALTIMESTAMP", "LOCATOR",
            "LOWER", "MAP", "MATCH", "MAX", "MEMBER", "MERGE", "METHOD", "MIN", "MINUTE",
            "MOD", "MODIFIES", "MODIFY", "MODULE", "MONTH", "MULTISET", "NAMES", "NATIONAL",
            "NATURAL", "NCHAR", "NCLOB", "NEW", "NEXT", "NO", "NONE", "NORMALIZE", "NOT",
            "NOTNULL", "NULL", "NULLIF", "NUMERIC", "OBJECT", "OCTET_LENGTH", "OF", "OFF",
            "OFFSET", "OLD", "ON", "ONLY", "OPEN", "OPERATION", "OPTION", "OR", "ORDER",
            "ORDINALITY", "OUT", "OUTER", "OUTPUT", "OVER", "OVERLAPS", "OVERLAY", "PAD",
            "PARAMETER", "PARAMETERS", "PARTIAL", "PARTITION", "PATH", "PERCENTILE_CONT",
            "PERCENTILE_DISC", "PERCENT_RANK", "PLACING", "POSITION", "POSTFIX", "POWER",
            "PRECISION", "PREFIX", "PREORDER", "PREPARE", "PRESERVE", "PRIMARY", "PRIOR",
            "PRIVILEGES", "PROCEDURE", "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", "SAVEPOINT", "SCHEMA", "SCOPE", "SCROLL", "SEARCH", "SECOND",
            "SECTION", "SELECT", "SENSITIVE", "SEQUENCE", "SESSION", "SESSION_USER", "SET",
            "SETOF", "SETS", "SIMILAR", "SIZE", "SMALLINT", "SOME", "SPACE", "SPECIFIC",
            "SPECIFICTYPE", "SQL", "SQLCODE", "SQLERROR", "SQLEXCEPTION", "SQLSTATE",
            "SQLWARNING", "SQRT", "START", "STATE", "STATEMENT", "STATIC", "STDDEV_POP",
            "STDDEV_SAMP", "STRUCTURE", "SUBMULTISET", "SUBSTRING", "SUM", "SYMMETRIC",
            "SYSTEM", "SYSTEM_USER", "TABLE", "TABLESAMPLE", "TEMPORARY", "TERMINATE", "THAN",
            "THEN", "TIME", "TIMESTAMP", "TIMEZONE_HOUR", "TIMEZONE_MINUTE", "TO", "TRAILING",
            "TRANSACTION", "TRANSLATE", "TRANSLATION", "TREAT", "TRIGGER", "TRIM", "TRUE",
            "UESCAPE", "UNDER", "UNION", "UNIQUE", "UNKNOWN", "UNNEST", "UPDATE", "UPPER",
            "USAGE", "USER", "USING", "VALUE", "VALUES", "VARCHAR", "VARIABLE", "VARYING",
            "VAR_POP", "VAR_SAMP", "VERBOSE", "VIEW", "WHEN", "WHENEVER", "WHERE",
            "WIDTH_BUCKET", "WINDOW", "WITH", "WITHIN", "WITHOUT", "WORK", "WRITE", "YEAR", "ZONE");
}    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy