/* // $Id: //guest/julian_hyde/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#1 $ // 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 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.Util; import java.sql.DatabaseMetaData; import java.sql.SQLException; /** * <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> **/ public class SqlQuery { DatabaseMetaData databaseMetaData; // todo: replace {select, selectCount} with a StringList; etc. boolean distinct; StringBuffer select = new StringBuffer(), from = new StringBuffer(), where = new StringBuffer(), groupBy = new StringBuffer(), having = new StringBuffer(); int selectCount = 0, fromCount = 0, whereCount = 0, groupByCount = 0, havingCount = 0; /** * Creates a <code>SqlQuery</code> * * @param databaseMetaData used to determine which dialect of * SQL to generate */ public SqlQuery(DatabaseMetaData databaseMetaData) { this.databaseMetaData = 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(databaseMetaData); } public void setDistinct(boolean distinct) { this.distinct = distinct; } /** * Encloses an identifier in quotation marks appropriate for the * current SQL dialect. For example, * <code>quoteIdentifier("emp")</code> yields a string containing * <code>"emp"</code> in Oracle, and a string containing * <code>[emp]</code> in Access. **/ public String quoteIdentifier(String val) { String q; try { q = databaseMetaData.getIdentifierQuoteString(); } catch (SQLException e) { throw Util.getRes().newInternal(e, "while quoting identifier"); } if (q.equals(" ")) { return val; // quoting is not supported } String val2 = Util.replace(val, q, q + q); return q + val2 + q; } /** * Encloses an identifier in quotation marks appropriate for the * current SQL dialect. For example, in Oracle, where the identifiers * are quoted using double-quotes, * <code>quoteIdentifier("schema","table")</code> yields a string * containing <code>"schema"."table"</code>. * * @param qual Qualifier. If it is not null, * <code>"<em>qual</em>".</code> is prepended. * @param name Name to be quoted. **/ public String quoteIdentifier(String qual, String name) { if (qual == null) { return quoteIdentifier(name); } else { Util.assert( !qual.equals(""), "qual should probably be null, not empty"); return quoteIdentifier(qual) + "." + quoteIdentifier(name); } } public boolean isOracle() { return getProduct().equals("Oracle"); } public boolean isAccess() { String product = getProduct(); return product.equals("ACCESS"); } private String getProduct() { try { return databaseMetaData.getDatabaseProductName(); } catch (SQLException e) { throw Util.getRes().newInternal(e, "while detecting database product"); } } public void addFromQuery(String query, String alias) { if (fromCount++ == 0) { from.append(" from "); } else { from.append(", "); } from.append("("); from.append(query); from.append(")"); if (alias != null) { Util.assert(!alias.equals("")); if (isOracle()) { from.append(" "); } else { from.append(" as "); } from.append(quoteIdentifier(alias)); } } public void addFromTable(String schema, String table, String alias) { if (fromCount++ == 0) { from.append(" from "); } else { from.append(", "); } from.append(quoteIdentifier(schema, table)); if (alias != null) { Util.assert(!alias.equals("")); if (isOracle()) { from.append(" "); } else { from.append(" as "); } from.append(quoteIdentifier(alias)); } } public void addFrom(SqlQuery sqlQuery, String alias) { addFromQuery(sqlQuery.toString(), alias); } public void addJoin( String type, String query, String alias, String condition) { Util.assert(fromCount > 0); from.append( " " + type + " join " + query + " as " + quoteIdentifier(alias) + " on " + condition); } /** Adds an expression to the select clause, automatically creating a * column alias. **/ public void addSelect(String expression) { addSelect(expression, "c" + selectCount); } /** Adds an expression to the select clause, with a specified column * alias. **/ public void addSelect(String expression, String alias) { if (alias != null) { expression += " as " + quoteIdentifier(alias); } select.append( (selectCount++ == 0 ? ("select " + (distinct ? "distinct " : "")) : ", ") + expression); } public void addWhere(String expression) { where.append( (whereCount++ == 0 ? " where " : " and ") + expression); } public void addGroupBy(String expression) { groupBy.append( (groupByCount++ == 0 ? " group by " : ", ") + expression); } public void addHaving(String expression) { having.append( (havingCount++ == 0 ? " having " : " and ") + expression); } public String toString() { return select.toString() + from.toString() + where.toString() + groupBy.toString() + having.toString(); } } // End SqlQuery.java
# | Change | User | Description | Committed | |
---|---|---|---|---|---|
#3 | 1603 | Julian Hyde |
mondrian: Add Andreas' taglib; Rename 'mondrian.rolap.Util.assert' to 'assertTrue', because 'assert' is a keyword in jdk 1.4; Fix 'NON EMPTY'; JUnit framework for tests; Add Oracle dataset. |
||
#2 | 1583 | Julian Hyde |
mondrian: add transformer scripts for postgres, hsql; fix bugs in SqlQuery; add roadmap. |
||
#1 | 1576 | Julian Hyde |
mondrian: fix dataset (add column customer.ordinal); create dataset for oracle; get queries working on oracle; get format strings working; refactor out new packages mondrian.rolap.agg and mondrian.rolap.sql. |