<commit_before>/*
 * Copyright (c) 2004-2022, University of Oslo
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 * Neither the name of the HISP project nor the names of its contributors may
 * be used to endorse or promote products derived from this software without
 * specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
 * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.hisp.dhis.datastore.hibernate;

import static java.lang.Boolean.parseBoolean;
import static java.lang.Double.parseDouble;
import static java.lang.String.format;
import static java.util.Arrays.asList;
import static java.util.stream.Collectors.joining;
import static java.util.stream.Collectors.toList;

import java.util.Arrays;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.BiConsumer;
import java.util.stream.Collectors;

import lombok.AllArgsConstructor;

import org.hisp.dhis.datastore.DatastoreQuery;
import org.hisp.dhis.datastore.DatastoreQuery.Comparison;
import org.hisp.dhis.datastore.DatastoreQuery.Filter;
import org.hisp.dhis.datastore.DatastoreQuery.Order;
import org.hisp.dhis.system.util.SqlUtils;

/**
 * Creates the HQL from a {@link DatastoreQuery} using {@link #createFetchHQL()}
 * and can set parameters for the filter value placeholders in that query using
 * {@link #applyParameterValues(BiConsumer)}.
 *
 * @author <NAME>
 */
@AllArgsConstructor
public class DatastoreQueryBuilder
{
    private final DatastoreQuery query;

    String createFetchHQL()
    {
        String fields = createFieldsHQL();
        String nonNullFilters = createHasNonNullFieldsFilters();
        String orders = createOrderHQL();
        String filters = createFilterHQL();

        return format(
            "select key %s from DatastoreEntry where namespace = :namespace and (%s) and (%s) order by %s",
            fields, nonNullFilters, filters, orders );
    }

    void applyParameterValues( BiConsumer<String, Object> setParameter )
    {
        int i = 0;
        for ( Filter f : query.getFilters() )
        {
            if ( !f.getOperator().isUnary() && !f.isNullValue() )
            {
                setParameter.accept( "f_" + i, toTypedFilterArgument( f ) );
            }
            i++;
        }
    }

    private String createFieldsHQL()
    {
        return query.getFields().isEmpty()
            ? ""
            : "," + String.join( ",", createExtractFieldValueExpressions() );
    }

    private List<String> createExtractFieldValueExpressions()
    {
        return query.getFields().stream()
            .map( f -> toValueAtPathHQL( f.getPath() ) )
            .collect( toList() );
    }

    private String createOrderHQL()
    {
        Order order = query.getOrder();
        String dir = order.getDirection().toString().replace( "n", "" );
        if ( order.isKeyPath() )
        {
            return "key " + dir;
        }
        if ( order.isValuePath() )
        {
            return "cast(jbPlainValue as text) " + dir;
        }
        String path = toValueAtPathHQL( order.getPath() );
        return order.getDirection().isNumeric()
            ? "cast(" + path + " as double) " + dir
            : path + " " + dir;
    }

    private String createHasNonNullFieldsFilters()
    {
        return query.isIncludeAll() || query.getFields().isEmpty()
            ? "1=1"
            : createExtractFieldValueExpressions().stream()
                .map( f -> f + " is not null" )
                .collect( joining( " or " ) );
    }

    private String createFilterHQL()
    {
        List<Filter> filters = query.getFilters();
        if ( filters.isEmpty() )
        {
            return "1=1";
        }
        AtomicInteger index = new AtomicInteger();
        return filters.stream()
            .map( f -> createFilterHQL( f, index.getAndIncrement() ) )
            .collect( joining( query.isAnyFilter() ? " or " : " and " ) );
    }

    private static String createFilterHQL( Filter filter, int id )
    {
        switch ( filter.getOperator() )<commit_msg>fix: datastore numeric sorting [DHIS2-12748] (#10163)<commit_after>/*
 * Copyright (c) 2004-2022, University of Oslo
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 * Neither the name of the HISP project nor the names of its contributors may
 * be used to endorse or promote products derived from this software without
 * specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
 * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.hisp.dhis.datastore.hibernate;

import static java.lang.Boolean.parseBoolean;
import static java.lang.Double.parseDouble;
import static java.lang.String.format;
import static java.util.Arrays.asList;
import static java.util.stream.Collectors.joining;
import static java.util.stream.Collectors.toList;

import java.util.Arrays;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.BiConsumer;
import java.util.stream.Collectors;

import lombok.AllArgsConstructor;

import org.hisp.dhis.datastore.DatastoreQuery;
import org.hisp.dhis.datastore.DatastoreQuery.Comparison;
import org.hisp.dhis.datastore.DatastoreQuery.Filter;
import org.hisp.dhis.datastore.DatastoreQuery.Order;
import org.hisp.dhis.system.util.SqlUtils;

/**
 * Creates the HQL from a {@link DatastoreQuery} using {@link #createFetchHQL()}
 * and can set parameters for the filter value placeholders in that query using
 * {@link #applyParameterValues(BiConsumer)}.
 *
 * @author <NAME>
 */
@AllArgsConstructor
public class DatastoreQueryBuilder
{
    private final DatastoreQuery query;

    String createFetchHQL()
    {
        String fields = createFieldsHQL();
        String nonNullFilters = createHasNonNullFieldsFilters();
        String orders = createOrderHQL();
        String filters = createFilterHQL();

        return format(
            "select key %s from DatastoreEntry where namespace = :namespace and (%s) and (%s) order by %s",
            fields, nonNullFilters, filters, orders );
    }

    void applyParameterValues( BiConsumer<String, Object> setParameter )
    {
        int i = 0;
        for ( Filter f : query.getFilters() )
        {
            if ( !f.getOperator().isUnary() && !f.isNullValue() )
            {
                setParameter.accept( "f_" + i, toTypedFilterArgument( f ) );
            }
            i++;
        }
    }

    private String createFieldsHQL()
    {
        return query.getFields().isEmpty()
            ? ""
            : "," + String.join( ",", createExtractFieldValueExpressions() );
    }

    private List<String> createExtractFieldValueExpressions()
    {
        return query.getFields().stream()
            .map( f -> toValueAtPathHQL( f.getPath() ) )
            .collect( toList() );
    }

    private String createOrderHQL()
    {
        Order order = query.getOrder();
        String dir = order.getDirection().toString().replace( "n", "" );
        if ( "desc".equals( dir ) )
        {
            dir += " nulls last";
        }
        if ( order.isKeyPath() )
        {
            return "key " + dir;
        }
        if ( order.isValuePath() )
        {
            return order.getDirection().isNumeric()
                ? "cast(cast(jbPlainValue as text) as double) " + dir
                : "cast(jbPlainValue as text) " + dir;
        }
        String path = toValueAtPathHQL( order.getPath() );
        return order.getDirection().isNumeric()
            ? "cast(cast(" + path + " as text) as double) " + dir
            : path + " " + dir;
    }

    private String createHasNonNullFieldsFilters()
    {
        return query.isIncludeAll() || query.getFields().isEmpty()
            ? "1=1"
            : createExtractFieldValueExpressions().stream()
                .map( f -> f + " is not null" )
                .collect( joining( " or " ) );
    }

    private String createFilterHQL()
    {
        List<Filter> filters = query.getFilters();
        if ( filters.isEmpty() )
        {
            return "1=1";
        }
        AtomicInteger index = new AtomicInteger();
        return filters.stream()
            .map( f -> createFilterHQL( f, index.getAndIncrement() ) )
            .collect( joining( query.isAnyFilter() ? " or " : " and " ) );
    }

    private static String createFilterHQL( Filter filter, int id )
    {
        switch ( filter.getOperator() )
{'fraction_non_alphanumeric': 0.09828146771946121, 'fraction_numerical': 0.003437064561077566, 'mean_word_length': 2.7935165609584214, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '5401939', 'n_tokens_mistral': 3027, 'n_tokens_neox': 2892, 'n_words': 944}