sqlquery.java

来自「数据仓库展示程序」· Java 代码 · 共 874 行 · 第 1/2 页

/*
// $Id: //open/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#39 $
// This software is subject to the terms of the Common Public License
// Agreement, available at the following URL:
// http://www.opensource.org/licenses/cpl.html.
// (C) Copyright 2002-2005 Kana Software, Inc. and others.
// All Rights Reserved.
// You must accept the terms of that agreement to use this software.
//
// jhyde, Mar 21, 2002
*/

package mondrian.rolap.sql;

import mondrian.olap.*;
import mondrian.resource.MondrianResource;

import org.eigenbase.util.property.Property;
import org.eigenbase.util.property.Trigger;

import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.*;

/**
 * <code>SqlQuery</code> allows us to build a <code>select</code>
 * statement and generate it in database-specific sql syntax.
 *
 * <p> Notable differences in database syntax are:<dl>
 *
 * <dt> Identifier quoting </dt>
 * <dd> Oracle (and all JDBC-compliant drivers) uses double-quotes,
 * for example <code>select * from "emp"</code>. Access prefers brackets,
 * for example <code>select * from [emp]</code>. mySQL allows single- and
 * double-quotes for string literals, and therefore does not allow
 * identifiers to be quoted, for example <code>select 'foo', "bar" from
 * emp</code>. </dd>
 *
 * <dt> AS in from clause </dt>
 * <dd> Oracle doesn't like AS in the from * clause, for example
 * <code>select from emp as e</code> vs. <code>select * from emp
 * e</code>. </dd>
 *
 * <dt> Column aliases </dt>
 * <dd> Some databases require that every column in the select list
 * has a valid alias. If the expression is an expression containing
 * non-alphanumeric characters, an explicit alias is needed. For example,
 * Oracle will barfs at <code>select empno + 1 from emp</code>. </dd>
 *
 * <dt> Parentheses around table names </dt>
 * <dd> Oracle doesn't like <code>select * from (emp)</code> </dd>
 *
 * <dt> Queries in FROM clause </dt>
 * <dd> PostgreSQL and hsqldb don't allow, for example, <code>select * from
 * (select * from emp) as e</code>.</dd>
 *
 * <dt> Uniqueness of index names </dt>
 * <dd> In PostgreSQL and Oracle, index names must be unique within the
 * database; in Access and hsqldb, they must merely be unique within their
 * table </dd>
 *
 * <dt> Datatypes </dt>
 * <dd> In Oracle, BIT is CHAR(1), TIMESTAMP is DATE.
 *      In PostgreSQL, DOUBLE is DOUBLE PRECISION, BIT is BOOL. </dd>
 * </ul>
 *
 * <p>
 * NOTE: Instances of this class are NOT thread safe so the user must make
 * sure this is accessed by only one thread at a time.
 */
public class SqlQuery
{
    /** This static variable controls the formatting of the sql string. */
    private static boolean generateFormattedSql =
             MondrianProperties.instance().GenerateFormattedSql.get();

    static {
        // Trigger is used to lookup and change the value of the
        // variable that controls formatting.
        // Using a trigger means we don't have to look up the property eveytime.
        MondrianProperties.instance().GenerateFormattedSql.addTrigger(
                new Trigger() {
                    public boolean isPersistent() {
                        return true;
                    }
                    public int phase() {
                        return Trigger.PRIMARY_PHASE;
                    }
                    public void execute(Property property, String value) {
                        generateFormattedSql = property.booleanValue();
                    }
                }
        );
    }

    private boolean distinct;

    private final ClauseList select;
    private final ClauseList from;
    private final ClauseList where;
    private final ClauseList groupBy;
    private final ClauseList having;
    private final ClauseList orderBy;

    /**
     * This list is used to keep track of what aliases have been  used in the
     * FROM clause. One might think that a java.util.Set would be a more
     * appropriate Collection type, but if you only have a couple of "from
     * aliases", then iterating over a list is faster than doing a hash lookup
     * (as is used in java.util.HashSet).
     */
    private final List fromAliases;

    /** The SQL dialect this query is to be generated in. */
    private final Dialect dialect;

    /** Scratch buffer. Clear it before use. */
    private final StringBuffer buf;

    /**
     * Base constructor used by all other constructors to create an empty
     * instance.
     */
    public SqlQuery(Dialect dialect) {

        // both select and from allow duplications
        this.select = new ClauseList(true);
        this.from = new ClauseList(true);

        this.where = new ClauseList(false);
        this.groupBy = new ClauseList(false);
        this.having = new ClauseList(false);
        this.orderBy = new ClauseList(false);
        this.fromAliases = new ArrayList();
        this.buf = new StringBuffer(128);

        this.dialect = dialect;
    }

    /**
     * Creates a <code>SqlQuery</code>
     *
     * @param databaseMetaData used to determine which dialect of
     *     SQL to generate. Must not be held beyond the constructor.
     */
    public SqlQuery(final DatabaseMetaData databaseMetaData) {
        this(Dialect.create(databaseMetaData));
    }

    /**
     * Creates an empty <code>SqlQuery</code> with the same environment as this
     * one. (As per the Gang of Four 'prototype' pattern.)
     */
    public SqlQuery cloneEmpty()
    {
        return new SqlQuery(dialect);
    }

    public void setDistinct(final boolean distinct) {
        this.distinct = distinct;
    }

    /**
     * The size required to add quotes around a string - this ought to be
     * large enough to prevent a reallocation.
     */
    private static final int SINGLE_QUOTE_SIZE = 10;
    /**
     * Two strings are quoted and the character '.' is placed between them.
     */
    private static final int DOUBLE_QUOTE_SIZE = 2 * SINGLE_QUOTE_SIZE + 1;

    /**
     *
     *
     * @param query
     * @param alias (if not null, must not be zero length).
     * @param failIfExists if true, throws exception if alias already exists
     * @return true if query *was* added
     *
     * @pre alias != null
     */
    public boolean addFromQuery(final String query,
                                final String alias,
                                final boolean failIfExists) {
        Util.assertPrecondition(alias != null);

        if (fromAliases.contains(alias)) {
            if (failIfExists) {
                throw Util.newInternal(
                        "query already contains alias '" + alias + "'");
            } else {
                return false;
            }
        }

        buf.setLength(0);

        buf.append('(');
        buf.append(query);
        buf.append(')');
        if (alias != null) {
            Util.assertTrue(alias.length() > 0);

            if (dialect.allowsAs()) {
                buf.append(" as ");
            } else {
                buf.append(' ');
            }
            dialect.quoteIdentifier(alias, buf);
            fromAliases.add(alias);
        }

        from.add(buf.toString());
        return true;
    }

    /**
     * Adds <code>[schema.]table AS alias</code> to the FROM clause.
     *
     * @param schema schema name; may be null
     * @param table table name
     * @param alias table alias, may not be null
     *              (if not null, must not be zero length).
     * @param failIfExists
     *
     * @pre alias != null
     * @return true if table *was* added
     */
    private boolean addFromTable(final String schema,
                                 final String table,
                                 final String alias,
                                 final String filter,
                                 final boolean failIfExists) {
        if (fromAliases.contains(alias)) {
            if (failIfExists) {
                throw Util.newInternal(
                        "query already contains alias '" + alias + "'");
            } else {
                return false;
            }
        }

        buf.setLength(0);
        dialect.quoteIdentifier(schema, table, buf);
        if (alias != null) {
            Util.assertTrue(alias.length() > 0);

            if (dialect.allowsAs()) {
                buf.append(" as ");
            } else {
                buf.append(' ');
            }
            dialect.quoteIdentifier(alias, buf);
            fromAliases.add(alias);
        }

        from.add(buf.toString());

        if (filter != null) {
            // append filter condition to where clause
            addWhere("(", filter, ")");
        }
        return true;
    }

    public void addFrom(final SqlQuery sqlQuery,
                        final String alias,
                        final boolean failIfExists)
    {
        addFromQuery(sqlQuery.toString(), alias, failIfExists);
    }

    /**
     * Adds a relation to a query, adding appropriate join conditions, unless
     * it is already present.
     *
     * <p>Returns whether the relation was added to the query.
     *
     * @param relation Relation to add
     * @param alias Alias of relation. If null, uses relation's alias.
     * @param failIfExists Whether to fail if relation is already present
     * @return true, if relation *was* added to query
     */
    public boolean addFrom(final MondrianDef.Relation relation,
                           final String alias,
                           final boolean failIfExists)
    {
        if (relation instanceof MondrianDef.View) {
            final MondrianDef.View view = (MondrianDef.View) relation;
            final String viewAlias = (alias == null)
                    ? view.getAlias()
                    : alias;
            final String sqlString = dialect.chooseQuery(view.selects);

            return addFromQuery(sqlString, viewAlias, false);

        } else if (relation instanceof MondrianDef.Table) {
            final MondrianDef.Table table = (MondrianDef.Table) relation;
            final String tableAlias = (alias == null)
                    ? table.getAlias()
                    : alias;

            return addFromTable(table.schema, table.name, tableAlias,
                table.getFilter(), failIfExists);

        } else if (relation instanceof MondrianDef.Join) {
            final MondrianDef.Join join = (MondrianDef.Join) relation;
            final String leftAlias = join.getLeftAlias();
            final String rightAlias = join.getRightAlias();

            boolean addLeft = addFrom(join.left, leftAlias, failIfExists);
            boolean addRight = addFrom(join.right, rightAlias, failIfExists);

            boolean added = addLeft || addRight;
            if (added) {
                buf.setLength(0);

                dialect.quoteIdentifier(leftAlias, join.leftKey, buf);
                buf.append(" = ");
                dialect.quoteIdentifier(rightAlias, join.rightKey, buf);

                addWhere(buf.toString());
            }
            return added;

        } else {
            throw Util.newInternal("bad relation type " + relation);
        }
    }

    /**
     * Adds an expression to the select clause, automatically creating a
     * column alias.
     */
    public void addSelect(final String expression) {
        // some DB2 versions (AS/400) throw an error, if a column alias is
        //  *not* used in a subsequent order by (Group by)
        if (dialect.isAS400()) {
            addSelect(expression, null);
        } else {
            addSelect(expression, "c" + select.size());
        }
    }
    /** Adds an expression to the select clause, with a specified column
     * alias. **/
    public void addSelect(final String expression, final String alias) {
        buf.setLength(0);

        buf.append(expression);
        if (alias != null) {
            buf.append(" as ");
            dialect.quoteIdentifier(alias, buf);
        }

        select.add(buf.toString());
    }

    public void addWhere(final String exprLeft,
                         final String exprMid,
                         final String exprRight)
    {
        int len = exprLeft.length() + exprMid.length() + exprRight.length();
        StringBuffer buf = new StringBuffer(len);

        buf.append(exprLeft);
        buf.append(exprMid);
        buf.append(exprRight);

        addWhere(buf.toString());
    }
    public void addWhere(final String expression)
    {
        where.add(expression);
    }

    public void addGroupBy(final String expression)
    {
        groupBy.add(expression);
    }

    public void addHaving(final String expression)
    {
        having.add(expression);
    }

    public void addOrderBy(final String expression)
    {
    	if (dialect.isDerby() || dialect.isCloudscape()) {
    		orderBy.add(dialect.quoteIdentifier("c" + (select.size() - 1)));
    	} else {
    		orderBy.add(expression);
    	}
    }

    public String toString()
    {
        if (generateFormattedSql) {
            StringWriter sw = new StringWriter(256);
            PrintWriter pw = new PrintWriter(sw);
            print(pw, "");
            pw.flush();
            return sw.toString();

        } else {
            buf.setLength(0);

            select.toBuffer(buf,
                distinct ? "select distinct " : "select ", ", ");
            from.toBuffer(buf, " from ", ", ");
            where.toBuffer(buf, " where ", " and ");
            groupBy.toBuffer(buf, " group by ", ", ");
            having.toBuffer(buf, " having ", " and ");
            orderBy.toBuffer(buf, " order by ", ", ");

            return buf.toString();
        }
    }
    public void print(PrintWriter pw, String prefix) {
        // This <CR> is added to the front because the part of the code
        // that prints out the sql (if the trace level is non-zero),
        // RolapUtil, does not print the sql at the start of a new line.
        pw.println();

        select.print(pw, prefix,
            distinct ? "select distinct " : "select ", ", ");
        from.print(pw, prefix, "from ", ", ");
        where.print(pw, prefix, "where ", " and ");
        groupBy.print(pw, prefix, "group by ", ", ");
        having.print(pw, prefix, "having ", " and ");
        orderBy.print(pw, prefix, "order by ", ", ");
    }

    public Dialect getDialect() {
        return dialect;