001/*
002// This software is subject to the terms of the Eclipse Public License v1.0
003// Agreement, available at the following URL:
004// http://www.eclipse.org/legal/epl-v10.html.
005// You must accept the terms of that agreement to use this software.
006//
007// Copyright (C) 2008-2013 Pentaho
008// All Rights Reserved.
009*/
010package mondrian.spi.impl;
011
012import mondrian.rolap.SqlStatement;
013
014import java.sql.*;
015import java.util.regex.Pattern;
016import java.util.regex.PatternSyntaxException;
017
018/**
019 * Implementation of {@link mondrian.spi.Dialect} for the PostgreSQL database.
020 *
021 * @author jhyde
022 * @since Nov 23, 2008
023 */
024public class PostgreSqlDialect extends JdbcDialectImpl {
025    public static final JdbcDialectFactory FACTORY =
026        new JdbcDialectFactory(
027            PostgreSqlDialect.class,
028            DatabaseProduct.POSTGRESQL)
029        {
030            protected boolean acceptsConnection(Connection connection) {
031                // Greenplum looks a lot like Postgres. If this is a
032                // Greenplum connection, yield to the Greenplum dialect.
033                return super.acceptsConnection(connection)
034                    && !isDatabase(DatabaseProduct.GREENPLUM, connection)
035                    && !isDatabase(DatabaseProduct.NETEZZA, connection)
036                    && !isDatabase(DatabaseProduct.REDSHIFT, connection);
037            }
038        };
039
040    /**
041     * Creates a PostgreSqlDialect.
042     *
043     * @param connection Connection
044     */
045    public PostgreSqlDialect(Connection connection) throws SQLException {
046        super(connection);
047    }
048
049    public boolean requiresAliasForFromQuery() {
050        return true;
051    }
052
053    @Override
054    protected String generateOrderByNulls(
055        String expr,
056        boolean ascending,
057        boolean collateNullsLast)
058    {
059        // Support for "ORDER BY ... NULLS LAST" was introduced in Postgres 8.3.
060        if (productVersion.compareTo("8.3") >= 0) {
061            return
062                generateOrderByNullsAnsi(
063                    expr,
064                    ascending,
065                    collateNullsLast);
066        } else {
067            return
068                super.generateOrderByNulls(
069                    expr,
070                    ascending,
071                    collateNullsLast);
072        }
073    }
074
075    public DatabaseProduct getDatabaseProduct() {
076        return DatabaseProduct.POSTGRESQL;
077    }
078
079    @Override
080    public boolean allowsRegularExpressionInWhereClause() {
081        return true;
082    }
083
084    public String generateRegularExpression(String source, String javaRegex) {
085        try {
086            Pattern.compile(javaRegex);
087        } catch (PatternSyntaxException e) {
088            // Not a valid Java regex. Too risky to continue.
089            return null;
090        }
091        javaRegex = javaRegex.replace("\\Q", "");
092        javaRegex = javaRegex.replace("\\E", "");
093        final StringBuilder sb = new StringBuilder();
094        sb.append("cast(");
095        sb.append(source);
096        sb.append(" as text) ~ ");
097        quoteStringLiteral(sb, javaRegex);
098        return sb.toString();
099    }
100
101    @Override
102    public SqlStatement.Type getType(
103        ResultSetMetaData metaData, int columnIndex)
104        throws SQLException
105    {
106        final int precision = metaData.getPrecision(columnIndex + 1);
107        final int scale = metaData.getScale(columnIndex + 1);
108        final int columnType = metaData.getColumnType(columnIndex + 1);
109        final String columnName = metaData.getColumnName(columnIndex + 1);
110
111        //  TODO - Do we need the check for "m"??
112        if (columnType == Types.NUMERIC
113            && scale == 0 && precision == 0
114            && columnName.startsWith("m"))
115        {
116            // In Greenplum  NUMBER/NUMERIC w/ no precision or
117            // scale means floating point.
118            logTypeInfo(metaData, columnIndex, SqlStatement.Type.OBJECT);
119            return SqlStatement.Type.OBJECT; // TODO - can this be DOUBLE?
120        }
121        return super.getType(metaData, columnIndex);
122    }
123
124}
125
126// End PostgreSqlDialect.java