foundation/eclipselink.core.test/src/it/java/org/eclipse/persistence/testing/tests/queries/options/ClearQueryOptionsOnStatementTest.java

/*
 * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     Oracle - initial API and implementation from Oracle TopLink
package org.eclipse.persistence.testing.tests.queries.options;

import org.eclipse.persistence.sessions.*;
import org.eclipse.persistence.exceptions.*;
import org.eclipse.persistence.queries.*;
import org.eclipse.persistence.internal.security.PrivilegedAccessHelper;
import org.eclipse.persistence.testing.framework.*;
import org.eclipse.persistence.internal.databaseaccess.*;

import java.math.*;
import java.util.*;
import java.sql.*;
import java.lang.reflect.*;

/**
 * Test to verify that max rows, query timeout and result set fetch size are cleared
 * on PreparedStatement objects utilized by TopLink. After a query has been executed,
 * these settings must be cleared so that other queries do not use these options
 * that are set local to each query.
 * For Bug 5709179 - MAX-ROWS/TIMEOUT NOT RESET ON CACHED STATEMENTS
 * @author dminsky
 */
public class ClearQueryOptionsOnStatementTest extends AutoVerifyTestCase {

    private List employeesCreated;
    protected boolean TYPE_SCROLL_INSENSITIVE_isSupported;
    protected boolean CONCUR_UPDATABLE_isSupported;

    public ClearQueryOptionsOnStatementTest() {
        super();
        setDescription("This test verifies max rows, query timeout & result set fetch size are cleared on prepared statements");
    }

    @Override
    public void setup() {
        TYPE_SCROLL_INSENSITIVE_isSupported = true;
        CONCUR_UPDATABLE_isSupported = true;
        if(getSession().getPlatform().isSQLServer()) {
            // In case either TYPE_SCROLL_INSENSITIVE or CONCUR_UPDATABLE used
            // MS SQL Server  Version: 9.00.2050;  MS SQL Server 2005 JDBC Driver  Version: 1.2.2828.100 throws exception:
            // com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported.
            TYPE_SCROLL_INSENSITIVE_isSupported = false;
            CONCUR_UPDATABLE_isSupported = false;
        }
        // must enable statement caching
        getDatabaseSession().getLogin().cacheAllStatements();
        getDatabaseSession().getIdentityMapAccessor().initializeAllIdentityMaps();
        UnitOfWork uow = getDatabaseSession().acquireUnitOfWork();
        employeesCreated = new ArrayList(10);
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(190), "Jak"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(191), "Daxter"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(192), "Ratchet"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(193), "Clank"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(194), "Crash"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(195), "Sonic"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(196), "Mario"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(197), "Luigi"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(198), "Peach"));
        employeesCreated.add(new QueryOptionEmployee(new BigDecimal(199), "Bowser"));
        uow.registerAllObjects(employeesCreated);
        uow.commit();
    }

    @Override
    public void test() {
        DatabaseSession session = getDatabaseSession();
        testQueryTimeoutReset(session);
        testMaxRowsReset(session);
        testResultSetFetchSizeReset(session);
    }

    public void testMaxRowsReset(Session session) {
        // MAX ROWS
        // 1. Execute query to read employees with a max-rows set to 4
        ReadAllQuery query = new ReadAllQuery(QueryOptionEmployee.class);
        query.setMaxRows(4);
        List employees = (List) session.executeQuery(query);

        // 2. Check employees read = 4 per MaxRows setting - just with an assert
        if (employees.size() != 4) {
            throw new TestErrorException("Max Rows reset - Rows returned: " + employees.size() + " (expecting 4)");
        }

        // 3. Execute another (new) query 100 times with same SQL & no max-rows setting
        for (int iteration = 0; iteration < 100; iteration++) {
            query = new ReadAllQuery(QueryOptionEmployee.class);
            employees = (List) session.executeQuery(query);
            if (employees.size() <= 4) {
                throw new TestErrorException("Max Rows reset - Rows returned: " + employees.size() + " (expecting >= 10)");
            }
        }
    }

    public void testResultSetFetchSizeReset(Session session) {
        // H2 sets the query fetch size on the connection, and does not clear it, so this will fail.
        if (getSession().getLogin().getDatasourcePlatform().isH2()) {
            return;
        }
        // Resultset fetch size
        ReadAllQuery query = new ReadAllQuery(QueryOptionEmployee.class);
        // HANA supports only TYPE_FORWARD_ONLY and CONCUR_READ_ONLY
        if (getSession().getPlatform().isHANA()) {
            ScrollableCursorPolicy policy = new ScrollableCursorPolicy();
            policy.setResultSetType(ScrollableCursorPolicy.TYPE_FORWARD_ONLY);
            policy.setResultSetConcurrency(ScrollableCursorPolicy.CONCUR_READ_ONLY);
            policy.setPageSize(10);
            query.useScrollableCursor(policy);
        } else if(TYPE_SCROLL_INSENSITIVE_isSupported && CONCUR_UPDATABLE_isSupported) {
            query.useScrollableCursor(2);
        } else {
            ScrollableCursorPolicy policy = new ScrollableCursorPolicy();
            if(!TYPE_SCROLL_INSENSITIVE_isSupported) {
                policy.setResultSetType(ScrollableCursorPolicy.TYPE_SCROLL_SENSITIVE);
            }
            if(!CONCUR_UPDATABLE_isSupported) {
                policy.setResultSetConcurrency(ScrollableCursorPolicy.CONCUR_READ_ONLY);
            }
            policy.setPageSize(10);
            query.useScrollableCursor(policy);
        }

        String sql = "SELECT ID, NAME, HISTORY_ID FROM QUERY_OPTION_EMPLOYEE";
        int fetchSize = 100;
        query.setSQLString(sql);
        query.setFetchSize(fetchSize);

        // The statement cache is protected - need to obtain the internal hashtable from the accessor
        org.eclipse.persistence.internal.sessions.DatabaseSessionImpl impl =
            (org.eclipse.persistence.internal.sessions.DatabaseSessionImpl) session;
        DatabaseAccessor accessor = (DatabaseAccessor) impl.getAccessor();
        Map statementCache = null;
        try {
            Method method = PrivilegedAccessHelper.getDeclaredMethod(DatabaseAccessor.class,
                "getStatementCache", new Class<?>[]{});
            method.setAccessible(true);
            statementCache = (Map) method.invoke(accessor, new Object[] {});
        } catch (Exception nsme) {
            throwError("Could not invoke DatabaseAccessor>>getStatementCache()", nsme);
        }

        // now cache the statement's previous fetch size
        int previousFetchSize = 0;
        Statement statement = (Statement) statementCache.get(sql);
        if (statement != null) {
            try {
                previousFetchSize = statement.getFetchSize();
            } catch (SQLException sqle) {
                throwError("Error whilst invoking intial Statement>>getFetchSize()", sqle);
            }
        }

        // execute query
        ScrollableCursor cursor = (ScrollableCursor) session.executeQuery(query);
        List employees = new ArrayList();
        while (cursor.hasNext()) {
            employees.add(cursor.next());
        }
        cursor.close();

        // now check the statement
        int postQueryFetchSize = 0;
        statement = (Statement) statementCache.get(sql);
        if (statement != null) {
            try {
                postQueryFetchSize = statement.getFetchSize();
            } catch (SQLException sqle) {
                throwError("Error whilst invoking secondary Statement>>getFetchSize()", sqle);
            }
        }

        if (postQueryFetchSize == fetchSize) {
            throwError("Statement fetch size was not reset");
        }

    }

    public void testQueryTimeoutReset(Session session) {
        if (getSession().getPlatform().isSymfoware()) {
            throwWarning("Test testQueryTimeoutReset skipped for this platform, "
                    + "the driver does not support query timeout. (bug 304905)");
        }
        boolean query1TimedOut = false;
        boolean query2TimedOut = false;
        // H2 sets the query timeout on the connection, and does not clear it, so this will fail.
        if (getSession().getLogin().getDatasourcePlatform().isH2()) {
            return;
        }
        String sql;
        if (getSession().getLogin().getDatasourcePlatform().isDB2() || getSession().getLogin().getDatasourcePlatform().isMySQL()) {
          sql = "SELECT SUM(e.EMP_ID) from EMPLOYEE e , EMPLOYEE b, EMPLOYEE c,EMPLOYEE d";
        } else {
          sql = "SELECT SUM(e.EMP_ID) from EMPLOYEE a , EMPLOYEE b, EMPLOYEE c, EMPLOYEE d, EMPLOYEE e, EMPLOYEE f, EMPLOYEE g";
        }
        // set the lowest timeout value on a query which is virtually guaranteed to produce a timeout
        try {
            DataReadQuery query = new DataReadQuery();
            query.setSQLString(sql);
            query.setQueryTimeout(1);
            session.executeQuery(query);
        } catch (Exception e) {
            if (e instanceof DatabaseException) {
                // cache value for debug purposes
                query1TimedOut = true;
            }
        }

        // do not set a timeout on the query, and test for a timeout
        try {
            DataReadQuery query = new DataReadQuery();
            query.setSQLString(sql);
            session.executeQuery(query);
        } catch (Exception e) {
            if (e instanceof DatabaseException) {
                query2TimedOut = true;
            }
        }

        // we're interested in if query 2 timed out
        // if no timeout value was set, query 2 should not produce a timeout
        if (query2TimedOut == true) {
            throw new TestErrorException("Query timeout occurred - PreparedStatement query timeout setting not cleared");
        }
    }

    @Override
    public void reset() {
        getDatabaseSession().getLogin().dontCacheAllStatements();
        UnitOfWork uow = getDatabaseSession().acquireUnitOfWork();
        uow.deleteAllObjects(employeesCreated);
        uow.commit();
        getDatabaseSession().getIdentityMapAccessor().initializeAllIdentityMaps();
    }

}