14972998308

Committed 12 May 2025 01:04PM UTC coverage: 67.475% (+0.3%) from 67.194%

Build # 14972998308

Build Type

Pull #27

github

Committed by

web-flow

Commit Message

Merge d0df0a4c0 into 1f1b3df71

Pull Request Pull Request #27: feat: add support for SQL Server dialect

Coverage Stats

219 of 418 branches covered (52.39%)

Branch coverage included in aggregate %.

51 of 64 new or added lines in 13 files covered. (79.69%)

1144 of 1602 relevant lines covered (71.41%)

3.81 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

77.93

/src/main/java/io/github/torand/fastersql/statement/SelectStatement.java

/*
 * Copyright (c) 2024-2025 Tore Eide Andersen
 *
 * 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 io.github.torand.fastersql.statement;

import io.github.torand.fastersql.Column;
import io.github.torand.fastersql.Context;
import io.github.torand.fastersql.Join;
import io.github.torand.fastersql.Table;
import io.github.torand.fastersql.alias.Alias;
import io.github.torand.fastersql.alias.ColumnAlias;
import io.github.torand.fastersql.expression.Expression;
import io.github.torand.fastersql.function.aggregate.AggregateFunction;
import io.github.torand.fastersql.order.Order;
import io.github.torand.fastersql.predicate.OptionalPredicate;
import io.github.torand.fastersql.predicate.Predicate;
import io.github.torand.fastersql.projection.Projection;
import io.github.torand.fastersql.relation.Relation;
import io.github.torand.fastersql.subquery.Subquery;

import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Optional;
import java.util.Set;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.stream.Stream;

import static io.github.torand.fastersql.Command.SELECT;
import static io.github.torand.fastersql.dialect.Capability.LIMIT_OFFSET;
import static io.github.torand.fastersql.dialect.Capability.SELECT_FOR_UPDATE;
import static io.github.torand.fastersql.statement.Helpers.unwrapSuppliers;
import static io.github.torand.fastersql.util.collection.CollectionHelper.asList;
import static io.github.torand.fastersql.util.collection.CollectionHelper.concat;
import static io.github.torand.fastersql.util.collection.CollectionHelper.isEmpty;
import static io.github.torand.fastersql.util.collection.CollectionHelper.nonEmpty;
import static io.github.torand.fastersql.util.collection.CollectionHelper.streamSafely;
import static io.github.torand.fastersql.util.contract.Requires.require;
import static io.github.torand.fastersql.util.contract.Requires.requireNonEmpty;
import static io.github.torand.fastersql.util.functional.Functions.castTo;
import static io.github.torand.fastersql.util.functional.Optionals.mapIfNonNull;
import static io.github.torand.fastersql.util.functional.Predicates.instanceOf;
import static java.util.Objects.nonNull;
import static java.util.Objects.requireNonNull;
import static java.util.function.Predicate.not;
import static java.util.stream.Collectors.joining;
import static java.util.stream.Collectors.toSet;

public class SelectStatement extends PreparableStatement {
    private final List<Projection> projections;
    private final List<Relation> relations;
    private final List<Join> joins;
    private final List<Predicate> wherePredicates;
    private final List<Column> groups;
    private final List<Predicate> havingPredicates;
    private final List<Order> orders;
    private final boolean distinct;
    private final Long limit;
    private final Long offset;
    private final boolean forUpdate;

    SelectStatement(List<Projection> projections, List<Relation> relations, List<Join> joins, List<Predicate> wherePredicates, List<Column> groups, List<Predicate> havingPredicates, List<Order> orders, boolean distinct, Long limit, Long offset, boolean forUpdate) {
        this.projections = asList(projections);
        this.relations = asList(relations);
        this.joins = asList(joins);
        this.wherePredicates = asList(wherePredicates);
        this.groups = asList(groups);
        this.havingPredicates = asList(havingPredicates);
        this.orders = asList(orders);
        this.distinct = distinct;
        this.limit = limit;
        this.offset = offset;
        this.forUpdate = forUpdate;
    }

    public SelectStatement join(Join... joins) {
        requireNonEmpty(joins, "No joins specified");
        require(() -> streamSafely(relations).noneMatch(instanceOf(Subquery.class)), "Can't combine a subquery FROM clause with joins");

        List<Join> concatenated = concat(this.joins, joins);
        return new SelectStatement(projections, relations, concatenated, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);
    }

    public SelectStatement leftOuterJoin(Join join) {
        requireNonNull(join, "No join specified");
        return join(join.leftOuter());
    }

    public SelectStatement rightOuterJoin(Join join) {
        requireNonNull(join, "No join specified");
        return join(join.rightOuter());
    }

    @SafeVarargs
    public final SelectStatement joinIf(boolean condition, Supplier<Join>... joinSuppliers) {
        requireNonEmpty(joinSuppliers, "No join suppliers specified");
        require(() -> streamSafely(relations).noneMatch(instanceOf(Subquery.class)), "Can't combine a subquery FROM clause with joins");
        if (condition) {
            List<Join> concatenated = concat(this.joins, unwrapSuppliers(joinSuppliers));
            return new SelectStatement(projections, relations, concatenated, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);
        } else {
            return this;
        }
    }

    /**
     * Adds one or more predicates to the WHERE clause.
     * @param predicates the WHERE predicates to add
     * @return updated statement, for method chaining
     */
    public SelectStatement where(Predicate... predicates) {
        requireNonEmpty(predicates, "No WHERE predicates specified");

        List<Predicate> concatenated = concat(this.wherePredicates, predicates);
        return new SelectStatement(projections, relations, joins, concatenated, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);
    }

    /**
     * Same as other method of same name, but only adds to the WHERE clause predicates that are present.
     * @param maybePredicates the WHERE predicate that may be present or not
     * @return updated statement, for method chaining
     */
    @SafeVarargs
    public final SelectStatement where(OptionalPredicate... maybePredicates) {
        requireNonEmpty(maybePredicates, "No optional WHERE predicates specified");

        List<Predicate> concatenated = concat(this.wherePredicates, OptionalPredicate.unwrap(maybePredicates));
        return new SelectStatement(projections, relations, joins, concatenated, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);
    }

    /**
     * Adds one or more predicates to the WHERE clause, if a predicate is true.
     * @param condition the condition that must be true for predicates to be added
     * @param predicateSuppliers the suppliers providing WHERE predicates to add
     * @return updatet statement, for method chaining
     */
    @SafeVarargs
    public final SelectStatement whereIf(boolean condition, Supplier<Predicate>... predicateSuppliers) {
        requireNonEmpty(predicateSuppliers, "No WHERE predicate suppliers specified");
        if (condition) {
            List<Predicate> concatenated = concat(this.wherePredicates, unwrapSuppliers(predicateSuppliers));
            return new SelectStatement(projections, relations, joins, concatenated, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);
        } else {
            return this;
        }
    }

    public SelectStatement groupBy(Column... groups) {
        requireNonEmpty(groups, "No groups specified");

        List<Column> concatenated = concat(this.groups, groups);
        return new SelectStatement(projections, relations, joins, wherePredicates, concatenated, havingPredicates, orders, distinct, limit, offset, forUpdate);
    }

    /**
     * Adds one or more predicates to the HAVING clause.
     * @param predicates the HAVING predicates to add
     * @return updated statement, for method chaining
     */
    public SelectStatement having(Predicate... predicates) {
        requireNonEmpty(predicates, "No HAVING predicates specified");

        List<Predicate> concatenated = concat(this.havingPredicates, predicates);
        return new SelectStatement(projections, relations, joins, wherePredicates, groups, concatenated, orders, distinct, limit, offset, forUpdate);
    }

    /**
     * Same as other method of same name, but only adds to the HAVING clause predicates that are present.
     * @param maybePredicates the HAVING predicate that may be present or not
     * @return updated statement, for method chaining
     */
    @SafeVarargs
    public final SelectStatement having(OptionalPredicate... maybePredicates) {
        requireNonEmpty(maybePredicates, "No optional HAVING predicates specified");

        List<Predicate> concatenated = concat(this.havingPredicates, OptionalPredicate.unwrap(maybePredicates));
        return new SelectStatement(projections, relations, joins, wherePredicates, groups, concatenated, orders, distinct, limit, offset, forUpdate);
    }

    /**
     * Adds one or more predicates to the HAVING clause, if a predicate is true.
     * @param condition the condition that must be true for predicates to be added
     * @param predicateSuppliers the suppliers providing HAVING predicates to add
     * @return updatet statement, for method chaining
     */
    @SafeVarargs
    public final SelectStatement havingIf(boolean condition, Supplier<Predicate>... predicateSuppliers) {
        requireNonEmpty(predicateSuppliers, "No HAVING predicate suppliers specified");
        if (condition) {
            List<Predicate> concatenated = concat(this.havingPredicates, unwrapSuppliers(predicateSuppliers));
            return new SelectStatement(projections, relations, joins, wherePredicates, groups, concatenated, orders, distinct, limit, offset, forUpdate);
        } else {
            return this;
        }
    }

    public SelectStatement orderBy(Order... orders) {
        requireNonEmpty(orders, "No orders specified");

        List<Order> concatenated = concat(this.orders, orders);
        return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, concatenated, distinct, limit, offset, forUpdate);
    }

    public SelectStatement limit(long limit) {
        return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);
    }

    public SelectStatement offset(long offset) {
        return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);
    }

    public SelectStatement forUpdate() {
        return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, true);
    }

    @Override
    public String sql(Context context) {
        final Context localContext = context
            .withCommand(SELECT)
            .withOuterStatement(this);

        validate(context);

        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        if (distinct) {
            sb.append("distinct ");
        }

        sb.append(streamSafely(projections)
            .map(p -> p.sql(localContext) + (p.alias().isEmpty() ? "" : " " + p.alias().map(a -> a.sql(localContext)).get()))
            .collect(joining(", ")));

        sb.append(" from ");

        // Tables that are joined with should not be specified in the FROM clause
        Set<Table<?>> joinedTables = streamSafely(joins).map(Join::joined).collect(toSet());

        sb.append(streamSafely(relations)
            .filter(not(joinedTables::contains))
            .map(t -> t.sql(localContext))
            .collect(joining(", ")));

        if (nonEmpty(joins)) {
            sb.append(" ");
            sb.append(streamSafely(joins)
                .map(j -> j.sql(localContext))
                .collect(joining(" ")));
        }

        if (nonEmpty(wherePredicates)) {
            sb.append(" where ");
            sb.append(streamSafely(wherePredicates)
                .map(p -> p.sql(localContext))
                .collect(joining(" and ")));
        }

        if (nonEmpty(groups)) {
            sb.append(" group by ");
            sb.append(streamSafely(groups)
                .map(g -> g.sql(localContext))
                .collect(joining(", ")));
        }

        if (nonEmpty(havingPredicates)) {
            sb.append(" having ");
            sb.append(streamSafely(havingPredicates)
                .map(p -> p.sql(localContext))
                .collect(joining(" and ")));
        }

        if (nonEmpty(orders)) {
            sb.append(" order by ");
            sb.append(streamSafely(orders)
                .map(o -> o.sql(localContext))
                .collect(joining(", ")));
        }

        if (nonNull(offset) || nonNull(limit)) {
            if (context.getDialect().supports(LIMIT_OFFSET)) {
                String offsetClause = nonNull(offset) ? " " + localContext.getDialect().formatRowOffsetClause().orElseThrow(() -> new RuntimeException("Dialect " + localContext.getDialect().getProductName() + " has no row offset clause")) : "";
                String limitClause = nonNull(limit) ? " " + localContext.getDialect().formatRowLimitClause().orElseThrow(() -> new RuntimeException("Dialect " + localContext.getDialect().getProductName() + " has no row limit clause")) : "";

                if (localContext.getDialect().offsetBeforeLimit()) {
                    sb.append(offsetClause).append(limitClause);
                } else {
                    sb.append(limitClause).append(offsetClause);
                }
            } else {
                sb = addLimitOffsetFallback(localContext, sb, rowFrom(), rowTo());
            }
        }

        if (forUpdate) {
            sb.append(" for update");
        }

        return sb.toString();
    }

    private Long rowFrom() {
        return mapIfNonNull(offset, o -> o + 1);
    }

    private Long rowTo() {
        return mapIfNonNull(limit, l -> (nonNull(offset) ? offset : 0) + l);
    }

    private StringBuilder addLimitOffsetFallback(Context context, StringBuilder innerSql, Long rowFrom, Long rowTo) {
        String rowNum = context.getDialect().formatRowNumLiteral()
            .orElseThrow(() -> new RuntimeException("Dialect " + context.getDialect().getProductName() + " has no row number literal"));

        if (nonNull(rowFrom) && nonNull(rowTo)) {
            String limitSql = "select ORIGINAL.*, {ROWNUM} ROW_NO from ( " + innerSql.toString() + " ) ORIGINAL where {ROWNUM} <= ?";
            String offsetSql = "select * from ( " + limitSql + " ) where ROW_NO >= ?";
            return new StringBuilder(offsetSql.replace("{ROWNUM}", rowNum));
        } else if (nonNull(rowFrom)) {
            String offsetSql = "select * from ( " + innerSql.toString() + " ) where {ROWNUM} >= ?";
            return new StringBuilder(offsetSql.replace("{ROWNUM}", rowNum));
        } else if (nonNull(rowTo)) {
            String limitSql = "select * from ( " + innerSql.toString() + " ) where {ROWNUM} <= ?";
            return new StringBuilder(limitSql.replace("{ROWNUM}", rowNum));
        }

        return innerSql;
    }

    @Override
    public List<Object> params(Context context) {
        List<Object> params = new LinkedList<>();

        streamSafely(projections).flatMap(p -> p.params(context)).forEach(params::add);

        streamSafely(relations).flatMap(r -> r.params(context)).forEach(params::add);

        streamSafely(wherePredicates).flatMap(p -> p.params(context)).forEach(params::add);

        streamSafely(havingPredicates).flatMap(p -> p.params(context)).forEach(params::add);

        if (context.getDialect().supports(LIMIT_OFFSET)) {
            if (context.getDialect().offsetBeforeLimit()) {
                if (nonNull(offset)) {
                    params.add(offset);
                }
                if (nonNull(limit)) {
                    params.add(limit);
                }
            } else {
                if (nonNull(limit)) {
                    params.add(limit);
                }
                if (nonNull(offset)) {
                    params.add(offset);
                }
            }
        } else {
            if (nonNull(limit)) {
                params.add(rowTo());
            }
            if (nonNull(offset)) {
                params.add(rowFrom());
            }
        }

        return params;
    }

    private void validate(Context context) {
        if (isEmpty(relations)) {
            throw new IllegalStateException("No FROM clause specified");
        }

        Stream<Column> projectedColumns = streamSafely(projections)
            .filter(instanceOf(Expression.class))
            .map(castTo(Expression.class))
            .flatMap(Expression::columnRefs);
        validateColumnTableRelations(context, projectedColumns);

        if (nonNull(joins)) {
            validateColumnTableRelations(context, streamSafely(joins).flatMap(Join::columnRefs));
        }

        if (nonNull(orders)) {
            Set<String> orderableAliases = streamSafely(projections)
                .map(Projection::alias)
                .flatMap(Optional::stream)
                .map(Alias::name)
                .collect(toSet());

            streamSafely(orders)
                .flatMap(o -> o.aliasRefs())
                .map(ColumnAlias::name)
                .filter(a -> !orderableAliases.contains(a))
                .findFirst()
                .ifPresent(a -> {
                    throw new IllegalStateException("ORDER BY column alias " + a + " is not specified in the SELECT clause");
                });
        }

        validateColumnTableRelations(context, streamSafely(wherePredicates).flatMap(Predicate::columnRefs));
        validateColumnTableRelations(context, streamSafely(groups));
        validateColumnTableRelations(context, streamSafely(havingPredicates).flatMap(Predicate::columnRefs));
        validateColumnTableRelations(context, streamSafely(orders).flatMap(Order::columnRefs));

        if (forUpdate) {
            if (!context.getDialect().supports(SELECT_FOR_UPDATE)) {
                throw new UnsupportedOperationException("%s does not support the SELECT ... FOR UPDATE clause".formatted(context.getDialect().getProductName()));
            }

            if (distinct || nonEmpty(groups) || streamSafely(projections).anyMatch(instanceOf(AggregateFunction.class))) {
                throw new IllegalStateException("SELECT ... FOR UPDATE can't be used with DISTINCT, GROUP BY or aggregates");
            }

            List<String> projectedTables = streamSafely(projections)
                .filter(instanceOf(Expression.class))
                .map(castTo(Expression.class))
                .flatMap(Expression::columnRefs)
                .map(cr -> cr.table().name())
                .distinct()
                .toList();

            if (projectedTables.size() != 1) {
                throw new IllegalStateException("SELECT ... FOR UPDATE can be used for a single projected table only. Projected tables in this statement are: %s".formatted(projectedTables));
            }
        }
    }

    private void validateColumnTableRelations(Context context, Stream<Column> columns) {
        Function<Relation, Stream<Table>> filterTables = r -> r instanceof Table ? Stream.of((Table)r) : Stream.empty();

        Set<String> outerTableNames = new HashSet<>();
        if (nonEmpty(context.getOuterStatements())) {
            context.getOuterStatements().forEach(os -> streamSafely(os.relations).flatMap(filterTables).map(Table::name).forEach(outerTableNames::add));
            context.getOuterStatements().forEach(os -> streamSafely(os.joins).map(Join::joined).map(Table::name).forEach(outerTableNames::add));
        }

        Set<String> tableNames = Stream.concat(streamSafely(relations).flatMap(filterTables), streamSafely(joins).map(Join::joined))
            .map(Table::name)
            .collect(toSet());

        columns
            .filter(c -> !outerTableNames.contains(c.table().name()) && !tableNames.contains(c.table().name()))
            .findFirst()
            .ifPresent(c -> {
                throw new IllegalStateException("Column " + c.name() + " belongs to table " + c.table().name() + ", but is not specified in a FROM or JOIN clause");
            });
    }
}

1	/*
2	* Copyright (c) 2024-2025 Tore Eide Andersen
3	*
4	* Licensed under the Apache License, Version 2.0 (the "License");
5	* you may not use this file except in compliance with the License.
6	* You may obtain a copy of the License at
7	*
8	* http://www.apache.org/licenses/LICENSE-2.0
9	*
10	* Unless required by applicable law or agreed to in writing, software
11	* distributed under the License is distributed on an "AS IS" BASIS,
12	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13	* See the License for the specific language governing permissions and
14	* limitations under the License.
15	*/
16	package io.github.torand.fastersql.statement;
17
18	import io.github.torand.fastersql.Column;
19	import io.github.torand.fastersql.Context;
20	import io.github.torand.fastersql.Join;
21	import io.github.torand.fastersql.Table;
22	import io.github.torand.fastersql.alias.Alias;
23	import io.github.torand.fastersql.alias.ColumnAlias;
24	import io.github.torand.fastersql.expression.Expression;
25	import io.github.torand.fastersql.function.aggregate.AggregateFunction;
26	import io.github.torand.fastersql.order.Order;
27	import io.github.torand.fastersql.predicate.OptionalPredicate;
28	import io.github.torand.fastersql.predicate.Predicate;
29	import io.github.torand.fastersql.projection.Projection;
30	import io.github.torand.fastersql.relation.Relation;
31	import io.github.torand.fastersql.subquery.Subquery;
32
33	import java.util.HashSet;
34	import java.util.LinkedList;
35	import java.util.List;
36	import java.util.Optional;
37	import java.util.Set;
38	import java.util.function.Function;
39	import java.util.function.Supplier;
40	import java.util.stream.Stream;
41
42	import static io.github.torand.fastersql.Command.SELECT;
43	import static io.github.torand.fastersql.dialect.Capability.LIMIT_OFFSET;
44	import static io.github.torand.fastersql.dialect.Capability.SELECT_FOR_UPDATE;
45	import static io.github.torand.fastersql.statement.Helpers.unwrapSuppliers;
46	import static io.github.torand.fastersql.util.collection.CollectionHelper.asList;
47	import static io.github.torand.fastersql.util.collection.CollectionHelper.concat;
48	import static io.github.torand.fastersql.util.collection.CollectionHelper.isEmpty;
49	import static io.github.torand.fastersql.util.collection.CollectionHelper.nonEmpty;
50	import static io.github.torand.fastersql.util.collection.CollectionHelper.streamSafely;
51	import static io.github.torand.fastersql.util.contract.Requires.require;
52	import static io.github.torand.fastersql.util.contract.Requires.requireNonEmpty;
53	import static io.github.torand.fastersql.util.functional.Functions.castTo;
54	import static io.github.torand.fastersql.util.functional.Optionals.mapIfNonNull;
55	import static io.github.torand.fastersql.util.functional.Predicates.instanceOf;
56	import static java.util.Objects.nonNull;
57	import static java.util.Objects.requireNonNull;
58	import static java.util.function.Predicate.not;
59	import static java.util.stream.Collectors.joining;
60	import static java.util.stream.Collectors.toSet;
61
62	public class SelectStatement extends PreparableStatement {
63	private final List<Projection> projections;
64	private final List<Relation> relations;
65	private final List<Join> joins;
66	private final List<Predicate> wherePredicates;
67	private final List<Column> groups;
68	private final List<Predicate> havingPredicates;
69	private final List<Order> orders;
70	private final boolean distinct;
71	private final Long limit;
72	private final Long offset;
73	private final boolean forUpdate;
74
75	SelectStatement(List<Projection> projections, List<Relation> relations, List<Join> joins, List<Predicate> wherePredicates, List<Column> groups, List<Predicate> havingPredicates, List<Order> orders, boolean distinct, Long limit, Long offset, boolean forUpdate) {	2✔
76	this.projections = asList(projections);	4✔
77	this.relations = asList(relations);	4✔
78	this.joins = asList(joins);	4✔
79	this.wherePredicates = asList(wherePredicates);	4✔
80	this.groups = asList(groups);	4✔
81	this.havingPredicates = asList(havingPredicates);	4✔
82	this.orders = asList(orders);	4✔
83	this.distinct = distinct;	3✔
84	this.limit = limit;	3✔
85	this.offset = offset;	3✔
86	this.forUpdate = forUpdate;	3✔
87	}	1✔
88
89	public SelectStatement join(Join... joins) {
90	requireNonEmpty(joins, "No joins specified");	6✔
91	require(() -> streamSafely(relations).noneMatch(instanceOf(Subquery.class)), "Can't combine a subquery FROM clause with joins");	13✔
92
93	List<Join> concatenated = concat(this.joins, joins);	5✔
94	return new SelectStatement(projections, relations, concatenated, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);	25✔
95	}
96
97	public SelectStatement leftOuterJoin(Join join) {
98	requireNonNull(join, "No join specified");	×
99	return join(join.leftOuter());	×
100	}
101
102	public SelectStatement rightOuterJoin(Join join) {
103	requireNonNull(join, "No join specified");	×
104	return join(join.rightOuter());	×
105	}
106
107	@SafeVarargs
108	public final SelectStatement joinIf(boolean condition, Supplier<Join>... joinSuppliers) {
109	requireNonEmpty(joinSuppliers, "No join suppliers specified");	×
110	require(() -> streamSafely(relations).noneMatch(instanceOf(Subquery.class)), "Can't combine a subquery FROM clause with joins");	×
111	if (condition) {	×
112	List<Join> concatenated = concat(this.joins, unwrapSuppliers(joinSuppliers));	×
113	return new SelectStatement(projections, relations, concatenated, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);	×
114	} else {
115	return this;	×
116	}
117	}
118
119	/**
120	* Adds one or more predicates to the WHERE clause.
121	* @param predicates the WHERE predicates to add
122	* @return updated statement, for method chaining
123	*/
124	public SelectStatement where(Predicate... predicates) {
125	requireNonEmpty(predicates, "No WHERE predicates specified");	6✔
126
127	List<Predicate> concatenated = concat(this.wherePredicates, predicates);	5✔
128	return new SelectStatement(projections, relations, joins, concatenated, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);	25✔
129	}
130
131	/**
132	* Same as other method of same name, but only adds to the WHERE clause predicates that are present.
133	* @param maybePredicates the WHERE predicate that may be present or not
134	* @return updated statement, for method chaining
135	*/
136	@SafeVarargs
137	public final SelectStatement where(OptionalPredicate... maybePredicates) {
138	requireNonEmpty(maybePredicates, "No optional WHERE predicates specified");	6✔
139
140	List<Predicate> concatenated = concat(this.wherePredicates, OptionalPredicate.unwrap(maybePredicates));	6✔
141	return new SelectStatement(projections, relations, joins, concatenated, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);	25✔
142	}
143
144	/**
145	* Adds one or more predicates to the WHERE clause, if a predicate is true.
146	* @param condition the condition that must be true for predicates to be added
147	* @param predicateSuppliers the suppliers providing WHERE predicates to add
148	* @return updatet statement, for method chaining
149	*/
150	@SafeVarargs
151	public final SelectStatement whereIf(boolean condition, Supplier<Predicate>... predicateSuppliers) {
152	requireNonEmpty(predicateSuppliers, "No WHERE predicate suppliers specified");	×
153	if (condition) {	×
154	List<Predicate> concatenated = concat(this.wherePredicates, unwrapSuppliers(predicateSuppliers));	×
155	return new SelectStatement(projections, relations, joins, concatenated, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);	×
156	} else {
157	return this;	×
158	}
159	}
160
161	public SelectStatement groupBy(Column... groups) {
162	requireNonEmpty(groups, "No groups specified");	6✔
163
164	List<Column> concatenated = concat(this.groups, groups);	5✔
165	return new SelectStatement(projections, relations, joins, wherePredicates, concatenated, havingPredicates, orders, distinct, limit, offset, forUpdate);	25✔
166	}
167
168	/**
169	* Adds one or more predicates to the HAVING clause.
170	* @param predicates the HAVING predicates to add
171	* @return updated statement, for method chaining
172	*/
173	public SelectStatement having(Predicate... predicates) {
174	requireNonEmpty(predicates, "No HAVING predicates specified");	6✔
175
176	List<Predicate> concatenated = concat(this.havingPredicates, predicates);	5✔
177	return new SelectStatement(projections, relations, joins, wherePredicates, groups, concatenated, orders, distinct, limit, offset, forUpdate);	25✔
178	}
179
180	/**
181	* Same as other method of same name, but only adds to the HAVING clause predicates that are present.
182	* @param maybePredicates the HAVING predicate that may be present or not
183	* @return updated statement, for method chaining
184	*/
185	@SafeVarargs
186	public final SelectStatement having(OptionalPredicate... maybePredicates) {
187	requireNonEmpty(maybePredicates, "No optional HAVING predicates specified");	×
188
189	List<Predicate> concatenated = concat(this.havingPredicates, OptionalPredicate.unwrap(maybePredicates));	×
190	return new SelectStatement(projections, relations, joins, wherePredicates, groups, concatenated, orders, distinct, limit, offset, forUpdate);	×
191	}
192
193	/**
194	* Adds one or more predicates to the HAVING clause, if a predicate is true.
195	* @param condition the condition that must be true for predicates to be added
196	* @param predicateSuppliers the suppliers providing HAVING predicates to add
197	* @return updatet statement, for method chaining
198	*/
199	@SafeVarargs
200	public final SelectStatement havingIf(boolean condition, Supplier<Predicate>... predicateSuppliers) {
201	requireNonEmpty(predicateSuppliers, "No HAVING predicate suppliers specified");	×
202	if (condition) {	×
203	List<Predicate> concatenated = concat(this.havingPredicates, unwrapSuppliers(predicateSuppliers));	×
204	return new SelectStatement(projections, relations, joins, wherePredicates, groups, concatenated, orders, distinct, limit, offset, forUpdate);	×
205	} else {
206	return this;	×
207	}
208	}
209
210	public SelectStatement orderBy(Order... orders) {
211	requireNonEmpty(orders, "No orders specified");	6✔
212
213	List<Order> concatenated = concat(this.orders, orders);	5✔
214	return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, concatenated, distinct, limit, offset, forUpdate);	25✔
215	}
216
217	public SelectStatement limit(long limit) {
218	return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);	26✔
219	}
220
221	public SelectStatement offset(long offset) {
222	return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, forUpdate);	26✔
223	}
224
225	public SelectStatement forUpdate() {
226	return new SelectStatement(projections, relations, joins, wherePredicates, groups, havingPredicates, orders, distinct, limit, offset, true);	25✔
227	}
228
229	@Override
230	public String sql(Context context) {
231	final Context localContext = context	2✔
232	.withCommand(SELECT)	2✔
233	.withOuterStatement(this);	2✔
234
235	validate(context);	3✔
236
237	StringBuilder sb = new StringBuilder();	4✔
238	sb.append("select ");	4✔
239	if (distinct) {	3✔
240	sb.append("distinct ");	4✔
241	}
242
243	sb.append(streamSafely(projections)	8✔
244	.map(p -> p.sql(localContext) + (p.alias().isEmpty() ? "" : " " + p.alias().map(a -> a.sql(localContext)).get()))	25✔
245	.collect(joining(", ")));	3✔
246
247	sb.append(" from ");	4✔
248
249	// Tables that are joined with should not be specified in the FROM clause
250	Set<Table<?>> joinedTables = streamSafely(joins).map(Join::joined).collect(toSet());	9✔
251
252	sb.append(streamSafely(relations)	8✔
253	.filter(not(joinedTables::contains))	7✔
254	.map(t -> t.sql(localContext))	6✔
255	.collect(joining(", ")));	3✔
256
257	if (nonEmpty(joins)) {	4✔
258	sb.append(" ");	4✔
259	sb.append(streamSafely(joins)	8✔
260	.map(j -> j.sql(localContext))	6✔
261	.collect(joining(" ")));	3✔
262	}
263
264	if (nonEmpty(wherePredicates)) {	4✔
265	sb.append(" where ");	4✔
266	sb.append(streamSafely(wherePredicates)	8✔
267	.map(p -> p.sql(localContext))	6✔
268	.collect(joining(" and ")));	3✔
269	}
270
271	if (nonEmpty(groups)) {	4✔
272	sb.append(" group by ");	4✔
273	sb.append(streamSafely(groups)	8✔
274	.map(g -> g.sql(localContext))	6✔
275	.collect(joining(", ")));	3✔
276	}
277
278	if (nonEmpty(havingPredicates)) {	4✔
279	sb.append(" having ");	4✔
280	sb.append(streamSafely(havingPredicates)	8✔
281	.map(p -> p.sql(localContext))	6✔
282	.collect(joining(" and ")));	3✔
283	}
284
285	if (nonEmpty(orders)) {	4✔
286	sb.append(" order by ");	4✔
287	sb.append(streamSafely(orders)	8✔
288	.map(o -> o.sql(localContext))	6✔
289	.collect(joining(", ")));	3✔
290	}
291
292	if (nonNull(offset) \|\| nonNull(limit)) {	8!
293	if (context.getDialect().supports(LIMIT_OFFSET)) {	5✔
294	String offsetClause = nonNull(offset) ? " " + localContext.getDialect().formatRowOffsetClause().orElseThrow(() -> new RuntimeException("Dialect " + localContext.getDialect().getProductName() + " has no row offset clause")) : "";	14!
295	String limitClause = nonNull(limit) ? " " + localContext.getDialect().formatRowLimitClause().orElseThrow(() -> new RuntimeException("Dialect " + localContext.getDialect().getProductName() + " has no row limit clause")) : "";	14!
296
297	if (localContext.getDialect().offsetBeforeLimit()) {	4✔
298	sb.append(offsetClause).append(limitClause);	7✔
299	} else {
300	sb.append(limitClause).append(offsetClause);	6✔
301	}
302	} else {	1✔
303	sb = addLimitOffsetFallback(localContext, sb, rowFrom(), rowTo());	9✔
304	}
305	}
306
307	if (forUpdate) {	3✔
308	sb.append(" for update");	4✔
309	}
310
311	return sb.toString();	3✔
312	}
313
314	private Long rowFrom() {
315	return mapIfNonNull(offset, o -> o + 1);	12✔
316	}
317
318	private Long rowTo() {
319	return mapIfNonNull(limit, l -> (nonNull(offset) ? offset : 0) + l);	20!
320	}
321
322	private StringBuilder addLimitOffsetFallback(Context context, StringBuilder innerSql, Long rowFrom, Long rowTo) {
323	String rowNum = context.getDialect().formatRowNumLiteral()	5✔
324	.orElseThrow(() -> new RuntimeException("Dialect " + context.getDialect().getProductName() + " has no row number literal"));	3✔
325
326	if (nonNull(rowFrom) && nonNull(rowTo)) {	6!
327	String limitSql = "select ORIGINAL.*, {ROWNUM} ROW_NO from ( " + innerSql.toString() + " ) ORIGINAL where {ROWNUM} <= ?";	4✔
328	String offsetSql = "select * from ( " + limitSql + " ) where ROW_NO >= ?";	3✔
329	return new StringBuilder(offsetSql.replace("{ROWNUM}", rowNum));	8✔
330	} else if (nonNull(rowFrom)) {	×
331	String offsetSql = "select * from ( " + innerSql.toString() + " ) where {ROWNUM} >= ?";	×
332	return new StringBuilder(offsetSql.replace("{ROWNUM}", rowNum));	×
333	} else if (nonNull(rowTo)) {	×
334	String limitSql = "select * from ( " + innerSql.toString() + " ) where {ROWNUM} <= ?";	×
335	return new StringBuilder(limitSql.replace("{ROWNUM}", rowNum));	×
336	}
337
338	return innerSql;	×
339	}
340
341	@Override
342	public List<Object> params(Context context) {
343	List<Object> params = new LinkedList<>();	4✔
344
345	streamSafely(projections).flatMap(p -> p.params(context)).forEach(params::add);	16✔
346
347	streamSafely(relations).flatMap(r -> r.params(context)).forEach(params::add);	16✔
348
349	streamSafely(wherePredicates).flatMap(p -> p.params(context)).forEach(params::add);	16✔
350
351	streamSafely(havingPredicates).flatMap(p -> p.params(context)).forEach(params::add);	16✔
352
353	if (context.getDialect().supports(LIMIT_OFFSET)) {	5✔
354	if (context.getDialect().offsetBeforeLimit()) {	4✔
355	if (nonNull(offset)) {	4✔
356	params.add(offset);	5✔
357	}
358	if (nonNull(limit)) {	4✔
359	params.add(limit);	6✔
360	}
361	} else {
362	if (nonNull(limit)) {	4✔
363	params.add(limit);	5✔
364	}
365	if (nonNull(offset)) {	4✔
366	params.add(offset);	6✔
367	}
368	}
369	} else {
370	if (nonNull(limit)) {	4!
371	params.add(rowTo());	5✔
372	}
373	if (nonNull(offset)) {	4!
374	params.add(rowFrom());	5✔
375	}
376	}
377
378	return params;	2✔
379	}
380
381	private void validate(Context context) {
382	if (isEmpty(relations)) {	4!
383	throw new IllegalStateException("No FROM clause specified");	×
384	}
385
386	Stream<Column> projectedColumns = streamSafely(projections)	4✔
387	.filter(instanceOf(Expression.class))	3✔
388	.map(castTo(Expression.class))	3✔
389	.flatMap(Expression::columnRefs);	2✔
390	validateColumnTableRelations(context, projectedColumns);	4✔
391
392	if (nonNull(joins)) {	4!
393	validateColumnTableRelations(context, streamSafely(joins).flatMap(Join::columnRefs));	8✔
394	}
395
396	if (nonNull(orders)) {	4!
397	Set<String> orderableAliases = streamSafely(projections)	4✔
398	.map(Projection::alias)	2✔
399	.flatMap(Optional::stream)	2✔
400	.map(Alias::name)	1✔
401	.collect(toSet());	4✔
402
403	streamSafely(orders)	4✔
404	.flatMap(o -> o.aliasRefs())	5✔
405	.map(ColumnAlias::name)	3✔
406	.filter(a -> !orderableAliases.contains(a))	7!
407	.findFirst()	2✔
408	.ifPresent(a -> {	1✔
409	throw new IllegalStateException("ORDER BY column alias " + a + " is not specified in the SELECT clause");	×
410	});
411	}
412
413	validateColumnTableRelations(context, streamSafely(wherePredicates).flatMap(Predicate::columnRefs));	8✔
414	validateColumnTableRelations(context, streamSafely(groups));	6✔
415	validateColumnTableRelations(context, streamSafely(havingPredicates).flatMap(Predicate::columnRefs));	8✔
416	validateColumnTableRelations(context, streamSafely(orders).flatMap(Order::columnRefs));	8✔
417
418	if (forUpdate) {	3✔
419	if (!context.getDialect().supports(SELECT_FOR_UPDATE)) {	5!
NEW 420	throw new UnsupportedOperationException("%s does not support the SELECT ... FOR UPDATE clause".formatted(context.getDialect().getProductName()));	×
421	}
422
423	if (distinct \|\| nonEmpty(groups) \|\| streamSafely(projections).anyMatch(instanceOf(AggregateFunction.class))) {	14!
424	throw new IllegalStateException("SELECT ... FOR UPDATE can't be used with DISTINCT, GROUP BY or aggregates");	×
425	}
426
427	List<String> projectedTables = streamSafely(projections)	4✔
428	.filter(instanceOf(Expression.class))	3✔
429	.map(castTo(Expression.class))	3✔
430	.flatMap(Expression::columnRefs)	2✔
431	.map(cr -> cr.table().name())	5✔
432	.distinct()	1✔
433	.toList();	2✔
434
435	if (projectedTables.size() != 1) {	4!
NEW 436	throw new IllegalStateException("SELECT ... FOR UPDATE can be used for a single projected table only. Projected tables in this statement are: %s".formatted(projectedTables));	×
437	}
438	}
439	}	1✔
440
441	private void validateColumnTableRelations(Context context, Stream<Column> columns) {
442	Function<Relation, Stream<Table>> filterTables = r -> r instanceof Table ? Stream.of((Table)r) : Stream.empty();	11✔
443
444	Set<String> outerTableNames = new HashSet<>();	4✔
445	if (nonEmpty(context.getOuterStatements())) {	4✔
446	context.getOuterStatements().forEach(os -> streamSafely(os.relations).flatMap(filterTables).map(Table::name).forEach(outerTableNames::add));	20✔
447	context.getOuterStatements().forEach(os -> streamSafely(os.joins).map(Join::joined).map(Table::name).forEach(outerTableNames::add));	19✔
448	}
449
450	Set<String> tableNames = Stream.concat(streamSafely(relations).flatMap(filterTables), streamSafely(joins).map(Join::joined))	12✔
451	.map(Table::name)	1✔
452	.collect(toSet());	4✔
453
454	columns	4✔
455	.filter(c -> !outerTableNames.contains(c.table().name()) && !tableNames.contains(c.table().name()))	15!
456	.findFirst()	2✔
457	.ifPresent(c -> {	1✔
458	throw new IllegalStateException("Column " + c.name() + " belongs to table " + c.table().name() + ", but is not specified in a FROM or JOIN clause");	×
459	});
460	}	1✔
461	}

torand / FasterSQL / 14972998308

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous