foundation/eclipselink.core.test/src/it/java/org/eclipse/persistence/testing/tests/schemaframework/SPGExecuteStoredProcedureTest.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.schemaframework;

import org.eclipse.persistence.queries.*;
import org.eclipse.persistence.testing.framework.*;

/**
 * Author: Edwin Tang
 * The test executes and verifies the stored procedures in the database
 * generated by SPGGenerateAmendmentClassTest.
 */
public class SPGExecuteStoredProcedureTest extends TestCase {
    boolean shouldBindAllParameters;
    int insertSuccess = 1;
    int updateSuccess = 1;
    int deleteSuccess = 1;
    String parameterNamePrefix;
    boolean shouldUseNamedArguments;
    static final Integer menuID = 99;
    static final Integer restaurantID = 100;
    static final Integer dinerID = 101;
    static final Integer personID = 102;
    static final Integer locationID = 103;
    static final Integer waiterID = 104;
    static final Integer menuItemID = 105;
    static final String menuType = "Lunch";
    static final String menuTypeUpdate = "Dinner";
    static final String dinerFirstName = "Steve";
    static final String dinerFirstNameUpdate = "Stephen";
    static final String dinerLastName = "McDonald";
    static final String dinerClass = "B";
    static final String personFirstName = "Dan";
    static final String personFirstNameUpdate = "Danial";
    static final String personLastName = "Smith";
    static final String personClass = "A";
    static final String locationArea = "Downtown";
    static final String locationAreaUpdate = "Southeastern";
    static final String locationCity = "Ottawa";
    static final String waiterFirstName = "Joel";
    static final String waiterLastName = "Clark";
    static final String waiterSpeciality = "Speaking Spanish";
    static final String waiterSpecialityUpdate = "Speaking Spanish and Italian";
    static final String waiterClass = "A";
    static final String menuItemName = "Roasted beef and potato";
    static final Float menuItemPrice = 20.99f;
    static final Float menuItemPriceUpdate = 22.99f;
    static final String restaurantName = "May Flower";
    static final String restaurantNameUpdate = "Great Wall Restaurant";

    public SPGExecuteStoredProcedureTest() {
    }

    @Override
    public void setup() {
        shouldUseNamedArguments = !getSession().getDatasourcePlatform().isMySQL();
        if(shouldUseNamedArguments) {
            if(getSession().getDatasourcePlatform().isOracle()) {
                parameterNamePrefix = "P_";
            } else {
                parameterNamePrefix = "";
            }
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("INS_Menu");
        addArgumentValue(call, "ID",menuID);
        addArgumentValue(call, "REST_ID",restaurantID);
        addArgumentValue(call, "TYPE",menuType);
        insertSuccess = insertSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("INS_Diner");
        addArgumentValue(call, "ID",dinerID);
        addArgumentValue(call, "F_NAME",dinerFirstName);
        addArgumentValue(call, "CLASS",dinerClass);
        addArgumentValue(call, "L_NAME",dinerLastName);
        insertSuccess = insertSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("INS_Person");
        addArgumentValue(call, "ID",personID);
        addArgumentValue(call, "F_NAME",personFirstName);
        addArgumentValue(call, "CLASS",personClass);
        addArgumentValue(call, "L_NAME",personLastName);
        insertSuccess = insertSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("INS_Location");
        addArgumentValue(call, "AREA",locationArea);
        addArgumentValue(call, "ID",locationID);
        addArgumentValue(call, "CITY",locationCity);
        insertSuccess = insertSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("INS_Waiter");
        addArgumentValue(call, "ID",waiterID);
        addArgumentValue(call, "F_NAME",waiterFirstName);
        addArgumentValue(call, "SPECIALT",waiterSpeciality);
        addArgumentValue(call, "CLASS",waiterClass);
        addArgumentValue(call, "W_RST_ID",restaurantID);
        addArgumentValue(call, "L_NAME",waiterLastName);
        insertSuccess = insertSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("INS_MenuItem");
        addArgumentValue(call, "ID",menuItemID);
        addArgumentValue(call, "MENU_ID",menuID);
        addArgumentValue(call, "PRICE",menuItemPrice);
        addArgumentValue(call, "NAME",menuItemName);
        insertSuccess = insertSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("INS_Restaurant");
        addArgumentValue(call, "ID",restaurantID);
        addArgumentValue(call, "NAME",restaurantName);
        insertSuccess = insertSuccess * getSession().executeNonSelectingCall(call);
        }
    }

    @Override
    public void test() {
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("UPD_Menu");
        addArgumentValue(call, "ID",menuID);
        addArgumentValue(call, "REST_ID",restaurantID);
        addArgumentValue(call, "TYPE",menuTypeUpdate);
        updateSuccess = updateSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("UPD_Diner");
        addArgumentValue(call, "ID",dinerID);
        addArgumentValue(call, "F_NAME",dinerFirstNameUpdate);
        addArgumentValue(call, "CLASS",dinerClass);
        addArgumentValue(call, "L_NAME",dinerLastName);
        updateSuccess = updateSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("UPD_Person");
        addArgumentValue(call, "ID",personID);
        addArgumentValue(call, "F_NAME",personFirstNameUpdate);
        addArgumentValue(call, "CLASS",personClass);
        addArgumentValue(call, "L_NAME",personLastName);
        updateSuccess = updateSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("UPD_Location");
        addArgumentValue(call, "AREA",locationAreaUpdate);
        addArgumentValue(call, "ID",locationID);
        addArgumentValue(call, "CITY",locationCity);
        updateSuccess = updateSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("UPD_Waiter");
        addArgumentValue(call, "ID",waiterID);
        addArgumentValue(call, "F_NAME",waiterFirstName);
        addArgumentValue(call, "SPECIALT",waiterSpecialityUpdate);
        addArgumentValue(call, "CLASS",waiterClass);
        addArgumentValue(call, "W_RST_ID",restaurantID);
        addArgumentValue(call, "L_NAME",waiterLastName);
        updateSuccess = updateSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("UPD_MenuItem");
        addArgumentValue(call, "ID",menuItemID);
        addArgumentValue(call, "MENU_ID",menuID);
        addArgumentValue(call, "PRICE",menuItemPriceUpdate);
        addArgumentValue(call, "NAME",menuItemName);
        updateSuccess = updateSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("UPD_Restaurant");
        addArgumentValue(call, "ID",restaurantID);
        addArgumentValue(call, "NAME",restaurantNameUpdate);
        updateSuccess = updateSuccess * getSession().executeNonSelectingCall(call);
        }
    }

    @Override
    public void reset() {
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("DEL_Menu");
        addArgumentValue(call, "ID",menuID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("D_1M_Menu_items");
        addArgumentValue(call, "ID",menuID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("DEL_Diner");
        addArgumentValue(call, "ID",dinerID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("DEL_Person");
        addArgumentValue(call, "ID",personID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("DEL_Location");
        addArgumentValue(call, "ID",locationID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("DEL_Waiter");
        addArgumentValue(call, "ID",waiterID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("DEL_MenuItem");
        addArgumentValue(call, "ID",menuItemID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("DEL_Restaurant");
        addArgumentValue(call, "ID",restaurantID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("D_1M_Restaurant_waiters");
        addArgumentValue(call, "ID",restaurantID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
        {
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("D_1M_Restaurant_menus");
        addArgumentValue(call, "ID",restaurantID);
        deleteSuccess = deleteSuccess * getSession().executeNonSelectingCall(call);
        }
    }

    @Override
    public void verify() {
        if(insertSuccess * updateSuccess * deleteSuccess == 0 ) {
          throw new TestErrorException("Failed to call stored procedures.") ;
        }
    }

    protected void addArgumentValue(StoredProcedureCall call, String name, Object value) {
        if(shouldUseNamedArguments) {
            call.addNamedArgumentValue(parameterNamePrefix + name, value);
        } else {
            call.addUnamedArgumentValue(value);
        }
    }
}