<commit_before>/*
// $Id$
// 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 <NAME>, 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 mondrian.olap.MondrianDef;

import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;

/**
 * <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(),
		orderBy = new StringBuffer();
    private HashSet groupBySet = new HashSet();
	int selectCount = 0,
		fromCount = 0,
		whereCount = 0,
		groupByCount = 0,
		havingCount = 0,
		orderByCount = 0;
	public ArrayList fromAliases = new ArrayList();

    /**
	 * 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("while quoting identifier", e);
		}
		if (q == null || q.trim().equals("")) {
			if (isMySQL()) {
				// mm.mysql.2.0.4 driver lies. We know better.
				q = "`";
			} else {
				return val; // quoting is not supported
			}
		}
		// if the value is already quoted, do nothing
		//  if not, then check for a dot qualified expression
		//  like "owner.table".
		//  In that case, prefix the single parts separately.
		if ( val.startsWith(q) && val.endsWith(q) ) {
			// already quoted - nothing to do
			return val;
	  }
	  int k = val.indexOf('.');
	  if ( k > 0 ) {
			// qualified
			String val1 = Util.replace(val.substring(0,k), q, q + q);
			String val2 = Util.replace(val.substring(k+1), q, q + q);
			return q + val1 + q + "." +  q + val2 + q ;
	  } else {
			// not Qualified
			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.assertTrue(
				!qual.equals(""),
				"qual should probably be null, not empty");

			return quoteIdentifier(qual) +
				"." +
				quoteIdentifier(name);
		}
	}

	// -- detect various databases --

	private String getProduct() {
		try {
			String productName = databaseMetaData.getDatabaseProductName();
			return productName;
		} catch (SQLException e) {
			throw Util.getRes().newInternal(
					"while detecting database product", e);
		}
	}


	private String getProductVersion() {
		try {
			String version = databaseMetaData.getDatabaseProductVersion();
			return version;
		} catch (SQLException e) {
			throw Util.getRes().newInternal(
			"while detecting database product version", e);
		}
	}


	public boolean isDB2() {
		// DB2 on NT returns "DB2/NT"
		return getProduct().startsWith("DB2");
	}
	public boolean isAS400() {
		// DB2/AS400 Product String = "DB2 UDB for AS/400"
		return getProduct().startsWith("DB2 UDB for AS/400");
	}
	public boolean isOldAS400() {
		if ( isAS400() ) {
			// TB "04.03.0000 V4R3m0"
			//  this version cannot handle subqueries and is considered "old"
			// DEUKA "05.01.0000 V5R1m0" is ok
			String version = getProductVersion();
			String[] version_release = version.split("\\.", 3);
		/*
		if ( version_release.length > 2 &&
			"04".compareTo(version_release[0]) > 0 ||
			("04".compareTo(version_release[0]) == 0
			&& "03".compareTo(version_release[1]) >= 0) )
			return true;
		*/
		// assume, that version <= 04 is "old"
		if ( "04".compareTo(version_release[0]) >= 0 )
			return true;
		}
    	return false;
	}


	public boolean isOracle() {
		return getProduct().equals("Oracle");
	}
	public boolean isAccess() {
		return getProduct().equals("ACCESS");
	}
	public boolean isPostgres() {
		return getProduct().toUpperCase().indexOf("POSTGRE") >= 0;
	}
	public boolean isMySQL() {
		return getProduct().toUpperCase().equals("MYSQL");
	}
	public boolean isInformix() {
		return getProduct().startsWith("Informix");
	}


	// -- behaviors --
	protected boolean requiresAliasForFromItems() {
		return isPostgres();
	}
	protected boolean allowsAs() {
		return !isOracle();
	}
	/** Whether "select * from (select * from t)" is OK. **/
	public boolean allowsFromQuery() {
		// older versions of AS400 do not allow FROM subqueries
		return !(isMySQL() || isOldAS400());
	}
	/** Whether "select count(distinct x, y) from t" is OK. **/
	public boolean allowsCompoundCountDistinct() {
		return isMySQL();
	}
    /** Whether "select count(distinct x) from t" is OK. **/
    public boolean allowsCountDistinct() {
        return !isAccess();
    }

	/**
	 * Chooses the variant within an array of {@link
	 * mondrian.olap.MondrianDef.SQL} which best matches the current SQL
	 * dialect.
	 */
	public String chooseQuery(MondrianDef.SQL[] sqls) {
		String best;
		if (isOracle()) {
			best = "oracle";
		} else if (isMySQL()) {
			best = "mysql";
		} else if (isAccess()) {
			best = "access";<commit_msg>MONDRIAN: support Informix<commit_after>/*
// $Id$
// 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 mondrian.olap.MondrianDef;

import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;

/**
 * <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(),
		orderBy = new StringBuffer();
    private HashSet groupBySet = new HashSet();
	int selectCount = 0,
		fromCount = 0,
		whereCount = 0,
		groupByCount = 0,
		havingCount = 0,
		orderByCount = 0;
	public ArrayList fromAliases = new ArrayList();

    /**
	 * 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("while quoting identifier", e);
		}
		if (q == null || q.trim().equals("")) {
			if (isMySQL()) {
				// mm.mysql.2.0.4 driver lies. We know better.
				q = "`";
			} else {
				return val; // quoting is not supported
			}
		}
		// if the value is already quoted, do nothing
		//  if not, then check for a dot qualified expression
		//  like "owner.table".
		//  In that case, prefix the single parts separately.
		if ( val.startsWith(q) && val.endsWith(q) ) {
			// already quoted - nothing to do
			return val;
	  }
	  int k = val.indexOf('.');
	  if ( k > 0 ) {
			// qualified
			String val1 = Util.replace(val.substring(0,k), q, q + q);
			String val2 = Util.replace(val.substring(k+1), q, q + q);
			return q + val1 + q + "." +  q + val2 + q ;
	  } else {
			// not Qualified
			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.assertTrue(
				!qual.equals(""),
				"qual should probably be null, not empty");

			return quoteIdentifier(qual) +
				"." +
				quoteIdentifier(name);
		}
	}

	// -- detect various databases --

	private String getProduct() {
		try {
			String productName = databaseMetaData.getDatabaseProductName();
			return productName;
		} catch (SQLException e) {
			throw Util.getRes().newInternal(
					"while detecting database product", e);
		}
	}


	private String getProductVersion() {
		try {
			String version = databaseMetaData.getDatabaseProductVersion();
			return version;
		} catch (SQLException e) {
			throw Util.getRes().newInternal(
			"while detecting database product version", e);
		}
	}


	public boolean isDB2() {
		// DB2 on NT returns "DB2/NT"
		return getProduct().startsWith("DB2");
	}
	public boolean isAS400() {
		// DB2/AS400 Product String = "DB2 UDB for AS/400"
		return getProduct().startsWith("DB2 UDB for AS/400");
	}
	public boolean isOldAS400() {
		if ( isAS400() ) {
			// TB "04.03.0000 V4R3m0"
			//  this version cannot handle subqueries and is considered "old"
			// DEUKA "05.01.0000 V5R1m0" is ok
			String version = getProductVersion();
			String[] version_release = version.split("\\.", 3);
		/*
		if ( version_release.length > 2 &&
			"04".compareTo(version_release[0]) > 0 ||
			("04".compareTo(version_release[0]) == 0
			&& "03".compareTo(version_release[1]) >= 0) )
			return true;
		*/
		// assume, that version <= 04 is "old"
		if ( "04".compareTo(version_release[0]) >= 0 )
			return true;
		}
    	return false;
	}


	public boolean isOracle() {
		return getProduct().equals("Oracle");
	}
	public boolean isAccess() {
		return getProduct().equals("ACCESS");
	}
	public boolean isPostgres() {
		return getProduct().toUpperCase().indexOf("POSTGRE") >= 0;
	}
	public boolean isMySQL() {
		return getProduct().toUpperCase().equals("MYSQL");
	}
	public boolean isInformix() {
		return getProduct().startsWith("Informix");
	}


	// -- behaviors --
	protected boolean requiresAliasForFromItems() {
		return isPostgres();
	}
	protected boolean allowsAs() {
		return !isOracle();
	}
	/** Whether "select * from (select * from t)" is OK. **/
	public boolean allowsFromQuery() {
		// older versions of AS400 do not allow FROM subqueries
		return !(isMySQL() || isOldAS400() || isInformix());
	}
	/** Whether "select count(distinct x, y) from t" is OK. **/
	public boolean allowsCompoundCountDistinct() {
		return isMySQL();
	}
    /** Whether "select count(distinct x) from t" is OK. **/
    public boolean allowsCountDistinct() {
        return !isAccess();
    }

	/**
	 * Chooses the variant within an array of {@link
	 * mondrian.olap.MondrianDef.SQL} which best matches the current SQL
	 * dialect.
	 */
	public String chooseQuery(MondrianDef.SQL[] sqls) {
		String best;
		if (isOracle()) {
			best = "oracle";
		} else if (isMySQL()) {
			best = "mysql";
		} else if (isAccess()) {
			best = "access";
{'fraction_non_alphanumeric': 0.13661698776202594, 'fraction_numerical': 0.012601478250333212, 'mean_word_length': 3.366931216931217, 'pattern_counts': {'":': 0, '<': 144, '<?xml version=': 0, '>': 154, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6876348', 'n_tokens_mistral': 5746, 'n_tokens_neox': 5191, 'n_words': 1721}