SetOperationQuery.java
/*
Copyright (c) 2008 Health Market Science, Inc.
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 com.healthmarketscience.sqlbuilder;
import java.io.IOException;
import com.healthmarketscience.common.util.AppendableExt;
import com.healthmarketscience.sqlbuilder.dbspec.Column;
/**
* Base query for queries which generate a series of SELECT queries joined by
* one or more "set operations", such as UNION [ALL], EXCEPT [ALL], and
* INTERSECT [ALL].
*
* @author James Ahlborn
*/
public class SetOperationQuery<ThisType extends SetOperationQuery<ThisType>>
extends Query<ThisType>
{
/** Enumeration representing the type of union to use */
public enum Type
{
UNION(" UNION "),
UNION_ALL(" UNION ALL "),
EXCEPT(" EXCEPT "),
EXCEPT_ALL(" EXCEPT ALL "),
INTERSECT(" INTERSECT "),
INTERSECT_ALL(" INTERSECT ALL ");
private final String _typeStr;
private Type(String typeStr) {
_typeStr = typeStr;
}
@Override
public String toString() { return _typeStr; }
}
private Type _defaultType;
private SqlObjectList<RelateTo> _queries = SqlObjectList.create("");
private SqlObjectList<SqlObject> _ordering = SqlObjectList.create();
public SetOperationQuery(Type type) {
this(type, (Object[])null);
}
public SetOperationQuery(Type type, Object... queries) {
_defaultType = type;
addQueriesImpl(_defaultType, queries);
}
/** Actual implementation which adds the given queries to the list of
queries with the given type. */
private void addQueriesImpl(final Type type, Object[] queries)
{
_queries.addObjects(new Converter<Object,RelateTo>() {
@Override
public RelateTo convert(Object src) {
return (_queries.isEmpty() ?
new RelateTo(null, src) :
new RelateTo(type, src));
}
}, queries);
}
/** Adds the given queries to the list of queries with the default set
operation type (the one configured in the constructor). */
public ThisType addQueries(SelectQuery... queries) {
return addQueries((Object[])queries);
}
/** Adds the given queries to the list of queries with the given set
operation type. */
public ThisType addQueries(Type type, SelectQuery... queries) {
return addQueries(type, (Object[])queries);
}
/** Adds the given queries to the list of queries with the default set
operation type (the one configured in the constructor). */
public ThisType addQueries(Object... queries) {
return addQueries(_defaultType, queries);
}
/** Adds the given queries to the list of queries with the given set
operation type. */
public ThisType addQueries(Type type, Object... queries) {
addQueriesImpl(type, queries);
return getThisType();
}
/**
* Adds the given column with the given direction to the "ORDER BY"
* clause
* <p>
* {@code Object} -> {@code SqlObject} conversions handled by
* {@link Converter#toCustomColumnSqlObject(Object)}.
*/
public ThisType addCustomOrdering(Object columnStr,
OrderObject.Dir dir) {
return addCustomOrderings(
new OrderObject(dir, Converter.toCustomColumnSqlObject(columnStr)));
}
/**
* Adds the given columns to the "ORDER BY" clause
* <p>
* Note, NULL ordering can be specified by passing an appropriately
* configured instance of {@link OrderObject} to this method.
* <p>
* {@code Object} -> {@code SqlObject} conversions handled by
* {@link Converter#CUSTOM_COLUMN_TO_OBJ}.
*/
public ThisType addCustomOrderings(Object... columnStrs) {
_ordering.addObjects(Converter.CUSTOM_COLUMN_TO_OBJ, columnStrs);
return getThisType();
}
/** Adds the given column with the given direction to the "ORDER BY"
clause */
public ThisType addOrdering(Column column, OrderObject.Dir dir) {
return addCustomOrdering(column, dir);
}
/** Adds the given columns to the "ORDER BY" clause */
public ThisType addOrderings(Column... columns) {
return addCustomOrderings((Object[])columns);
}
/** Adds the given column index with the given direction to the "ORDER BY"
clause */
public ThisType addIndexedOrdering(Integer columnIdx,
OrderObject.Dir dir) {
return addCustomOrdering(columnIdx, dir);
}
/** Adds the given column index to the "ORDER BY" clause */
public ThisType addIndexedOrderings(Integer... columnIdxs) {
return addCustomOrderings((Object[])columnIdxs);
}
@Override
public void validate(ValidationContext vContext)
throws ValidationException
{
// check super
super.validate(vContext);
// now, validate each of our sub-query's select queries, and check numbers
// of args if possible
int currentCount = -1;
boolean ignoreColumnCount = false;
for(RelateTo relateTo : _queries) {
Object queryObj = relateTo.getQuery();
if(!(queryObj instanceof SelectQuery)) {
continue;
}
SelectQuery selectQuery = (SelectQuery)queryObj;
// check the column count against the other queries
if(!ignoreColumnCount) {
if(selectQuery.hasAllColumns()) {
// can't validate if using the "*" syntax
ignoreColumnCount = true;
} else {
if(currentCount < 0) {
// get expected column count
currentCount = selectQuery.getColumns().size();
} else {
// validate current query against expected count
if(currentCount != selectQuery.getColumns().size()) {
throw new ValidationException(
"mismatched number of columns in union statement, found " + currentCount +
" while query has " + selectQuery.getColumns().size());
}
}
}
}
// sub-selects may not have ordering clauses
if(!selectQuery.getOrdering().isEmpty()) {
throw new ValidationException(
"Union selects may not have ordering clause");
}
}
SelectQuery.validateOrdering(currentCount, _ordering, ignoreColumnCount);
}
@Override
protected void collectSchemaObjects(ValidationContext vContext) {
super.collectSchemaObjects(vContext);
_queries.collectSchemaObjects(vContext);
}
@Override
protected void appendTo(AppendableExt app, SqlContext newContext)
throws IOException
{
// this will only really apply to the ordering not to the sub-queries, as
// they may use a different value internally
newContext.setUseTableAliases(false);
app.append(_queries);
if(!_ordering.isEmpty()) {
// append ordering clause
app.append(" ORDER BY ").append(_ordering);
}
}
SqlObject getFirstQuery() {
return (!_queries.isEmpty() ? _queries.get(0).getQuery() : null);
}
/**
* Convenience method to create a UNION query.
*/
public static UnionQuery union() {
return new UnionQuery(Type.UNION);
}
/**
* Convenience method to create a UNION query.
*/
public static UnionQuery union(SelectQuery... queries) {
return new UnionQuery(Type.UNION, queries);
}
/**
* Convenience method to create a UNION ALL query.
*/
public static UnionQuery unionAll() {
return new UnionQuery(Type.UNION_ALL);
}
/**
* Convenience method to create a UNION ALL query.
*/
public static UnionQuery unionAll(SelectQuery... queries) {
return new UnionQuery(Type.UNION_ALL, queries);
}
/**
* Convenience method to create a EXCEPT query.
*/
public static ExceptQuery except() {
return new ExceptQuery(Type.EXCEPT);
}
/**
* Convenience method to create a EXCEPT query.
*/
public static ExceptQuery except(SelectQuery... queries) {
return new ExceptQuery(Type.EXCEPT, queries);
}
/**
* Convenience method to create a EXCEPT ALL query.
*/
public static ExceptQuery exceptAll() {
return new ExceptQuery(Type.EXCEPT_ALL);
}
/**
* Convenience method to create a EXCEPT ALL query.
*/
public static ExceptQuery exceptAll(SelectQuery... queries) {
return new ExceptQuery(Type.EXCEPT_ALL, queries);
}
/**
* Convenience method to create a INTERSECT query.
*/
public static IntersectQuery intersect() {
return new IntersectQuery(Type.INTERSECT);
}
/**
* Convenience method to create a INTERSECT query.
*/
public static IntersectQuery intersect(SelectQuery... queries) {
return new IntersectQuery(Type.INTERSECT, queries);
}
/**
* Convenience method to create a INTERSECT ALL query.
*/
public static IntersectQuery intersectAll() {
return new IntersectQuery(Type.INTERSECT_ALL);
}
/**
* Convenience method to create a INTERSECT ALL query.
*/
public static IntersectQuery intersectAll(SelectQuery... queries) {
return new IntersectQuery(Type.INTERSECT_ALL, queries);
}
/**
* Outputs the set operator type (if non-{@code null}) and the query
* <code>"<type> <query>"</code>.
*/
private static class RelateTo extends Subquery
{
private Type _type;
private RelateTo(Type type, Object query)
{
super(query);
_type = type;
}
private SqlObject getQuery() {
return _query;
}
@Override
public void appendTo(AppendableExt app) throws IOException {
// type is null for the first query
if(_type != null) {
app.append(_type);
}
app.append(getQuery());
}
}
}