utils/eclipselink.dbws.builder.test.oracle/src/it/java/dbws/testing/prependedschema/PrependedSchemaTestSuite.java

/*
 * Copyright (c) 2011, 2020 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:
//     David McCann - June 05, 2012 - 2.4.1 - Initial implementation
package dbws.testing.prependedschema;

//javase imports
import java.io.StringReader;
import java.sql.SQLException;
import java.sql.Statement;

import org.w3c.dom.Document;

//java eXtension imports
import javax.wsdl.WSDLException;

//JUnit4 imports
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

//EclipseLink imports
import org.eclipse.persistence.internal.xr.Invocation;
import org.eclipse.persistence.internal.xr.Operation;
import org.eclipse.persistence.oxm.XMLMarshaller;

//test imports
import dbws.testing.DBWSTestSuite;

/**
 * Tests parser handling of types, etc. that have the schema name (aka user name)
 * prepended to the name, i.e. 'SCOTT.MY_TYPE'.
 *
 */
public class PrependedSchemaTestSuite extends DBWSTestSuite {
    static String username;
    static {
        username = System.getProperty(DATABASE_USERNAME_KEY, DEFAULT_DATABASE_USERNAME);
    }

    static final String CREATE_TESMAN_TABLE2 =
        "CREATE TABLE XTESMAN_TABLE2(SRNO NUMBER, DETAIL "+username+".XTESMAN_TYPE2)" +
          "\nLOGGING" +
          "\nTABLESPACE USERS" +
          "\nPCTFREE 10" +
          "\nINITRANS 1" +
          "\nSTORAGE" +
          "\n(" +
            "\nINITIAL 65536" +
            "\nMINEXTENTS 1" +
            "\nMAXEXTENTS UNLIMITED" +
            "\nBUFFER_POOL DEFAULT" +
          "\n)";
    static final String CREATE_TESMAN_TYPE1 =
        "CREATE TYPE XTESMAN_TYPE1 AS OBJECT" +
          "\n(" +
            "\nACCT NUMBER, " +
            "\nSTATE VARCHAR2(30)," +
            "\nDIVISION VARCHAR2(30)," +
            "\nCOUNTRY VARCHAR2(30)" +
          "\n);";
    static final String CREATE_TESMAN_TYPE2 =
        "CREATE TYPE XTESMAN_TYPE2 AS OBJECT" +
          "\n(" +
            "\nPNR NUMBER, " +
            "\nCOMPANY VARCHAR2(30)," +
            "\nSE VARCHAR2(30)," +
            "\nSCRIP VARCHAR2(30)," +
            "\nTT "+username+".XTESMAN_TYPE1" +
          "\n);";

    static final String[] POPULATE_TESMAN_TABLE2 = new String[] {
        "INSERT INTO XTESMAN_TABLE2 (SRNO, DETAIL) VALUES (1, " +
            "XTESMAN_TYPE2(11, 'SOMECOMPANY', 'SEVAL', 'SCRIPVAL'," +
            "XTESMAN_TYPE1(45, 'SOMESTATE', 'SOMEDIV', 'CANADA')))",
        "INSERT INTO XTESMAN_TABLE2 (SRNO, DETAIL) VALUES (666, " +
            "XTESMAN_TYPE2(12, 'SOMECO', 'SECKS', 'SCRIPPER'," +
            "XTESMAN_TYPE1(46, 'COMASTATE', 'MYDIV', 'CANADA')))"};

    static final String CREATE_TESMAN_PACK =
        "create or replace PACKAGE XTESMANPACK AS" +
          "\nFUNCTION TESMANFUNC17( param1 in INTEGER) return "+username+".XTESMAN_TABLE2%ROWTYPE;" +
        "\nEND XTESMANPACK;";
    static final String CREATE_TESMAN_BODY =
        "create or replace PACKAGE BODY XTESMANPACK AS" +
          "\nFUNCTION TESMANFUNC17(param1 in INTEGER) return "+username+".XTESMAN_TABLE2%ROWTYPE AS" +
            "\nl_data1 "+username+".XTESMAN_TABLE2%ROWTYPE;" +
            "\nCURSOR c_emp(paramtemp in INTEGER) IS" +
              "\nselect * from XTESMAN_TABLE2 te WHERE te.srno=paramtemp;" +
          "\nbegin" +
            "\nopen c_emp(param1);" +
            "\nLOOP" +
              "\nFETCH c_emp into l_data1;" +
              "\nexit when c_emp%NOTFOUND;" +
            "\nEND LOOP;" +
            "\nRETURN l_data1;" +
          "\nEND TESMANFUNC17;" +
        "\nEND XTESMANPACK;";

    static final String DROP_TESMAN_BODY =
        "DROP PACKAGE BODY XTESMANPACK";
    static final String DROP_TESMAN_PACK =
        "DROP PACKAGE XTESMANPACK";
    static final String DROP_TESMAN_TABLE2 =
        "DROP TABLE XTESMAN_TABLE2";
    static final String DROP_TESMAN_TYPE2 =
        "DROP TYPE XTESMAN_TYPE2";
    static final String DROP_TESMAN_TYPE1 =
        "DROP TYPE XTESMAN_TYPE1";

    static boolean ddlCreate = false;
    static boolean ddlDrop = false;
    static boolean ddlDebug = false;

    @BeforeClass
    public static void setUp() throws WSDLException {
        if (conn == null) {
            try {
                conn = buildConnection();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        String ddlCreateProp = System.getProperty(DATABASE_DDL_CREATE_KEY, DEFAULT_DATABASE_DDL_CREATE);
        if ("true".equalsIgnoreCase(ddlCreateProp)) {
            ddlCreate = true;
        }
        String ddlDropProp = System.getProperty(DATABASE_DDL_DROP_KEY, DEFAULT_DATABASE_DDL_DROP);
        if ("true".equalsIgnoreCase(ddlDropProp)) {
            ddlDrop = true;
        }
        String ddlDebugProp = System.getProperty(DATABASE_DDL_DEBUG_KEY, DEFAULT_DATABASE_DDL_DEBUG);
        if ("true".equalsIgnoreCase(ddlDebugProp)) {
            ddlDebug = true;
        }
        if (ddlCreate) {
            runDdl(conn, CREATE_TESMAN_TYPE1, ddlDebug);
            runDdl(conn, CREATE_TESMAN_TYPE2, ddlDebug);
            runDdl(conn, CREATE_TESMAN_TABLE2, ddlDebug);
            try {
                Statement stmt = conn.createStatement();
                for (int i = 0; i < POPULATE_TESMAN_TABLE2.length; i++) {
                    stmt.addBatch(POPULATE_TESMAN_TABLE2[i]);
                }
                stmt.executeBatch();
            }
            catch (SQLException e) {e.printStackTrace();}
            runDdl(conn, CREATE_TESMAN_PACK, ddlDebug);
            runDdl(conn, CREATE_TESMAN_BODY, ddlDebug);
        }
        DBWS_BUILDER_XML_USERNAME =
            "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
            "<dbws-builder xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
              "<properties>" +
                  "<property name=\"projectName\">PrependedSchema</property>" +
                  "<property name=\"logLevel\">off</property>" +
                  "<property name=\"username\">";
          DBWS_BUILDER_XML_PASSWORD =
                  "</property><property name=\"password\">";
          DBWS_BUILDER_XML_URL =
                  "</property><property name=\"url\">";
          DBWS_BUILDER_XML_DRIVER =
                  "</property><property name=\"driver\">";
          DBWS_BUILDER_XML_PLATFORM =
                  "</property><property name=\"platformClassname\">";
          DBWS_BUILDER_XML_MAIN =
                  "</property>" +
              "</properties>" +
              "<plsql-procedure " +
                  "name=\"TestFunc17\" " +
                  "catalogPattern=\"XTESMANPACK\" " +
                  "procedurePattern=\"TESMANFUNC17\" " +
              "/>" +
            "</dbws-builder>";
          builder = null;
          DBWSTestSuite.setUp(".");

          // execute shadow type ddl to generate JDBC equivalents of PL/SQL types
          for (String ddl : builder.getTypeDDL()) {
              //System.out.println("create: " + ddl);
              runDdl(conn, ddl, ddlDebug);
          }
    }

    @AfterClass
    public static void tearDown() {
        // drop shadow type ddl
        for (String ddl : builder.getTypeDropDDL()) {
            // may need to strip off trailing ';'
            try {
                int lastIdx = ddl.lastIndexOf(";");
                if (lastIdx == (ddl.length() - 1)) {
                    ddl = ddl.substring(0, ddl.length() - 1);
                }
            } catch (Exception xxx) {}
            //System.out.println("drop: " + ddl);
            runDdl(conn, ddl, ddlDebug);
        }
        if (ddlDrop) {
            runDdl(conn, DROP_TESMAN_BODY, ddlDebug);
            runDdl(conn, DROP_TESMAN_PACK, ddlDebug);
            runDdl(conn, DROP_TESMAN_TABLE2, ddlDebug);
            runDdl(conn, DROP_TESMAN_TYPE2, ddlDebug);
            runDdl(conn, DROP_TESMAN_TYPE1, ddlDebug);
        }
    }

    @Test
    public void test() {
        Invocation invocation = new Invocation("TestFunc17");
        invocation.setParameter("param1", 666);
        Operation op = xrService.getOperation(invocation.getName());
        Object result = op.invoke(xrService, invocation);
        assertNotNull("result is null", result);
        Document doc = xmlPlatform.createDocument();
        XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller();
        marshaller.marshal(result, doc);
        Document controlDoc = xmlParser.parse(new StringReader(TESMAN_TABLE2_ROWTYPE));
        assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }

    // Cater for case of Oracle 12c and usage of container database - name prefixed with C##
    public static final String XML_USERNAME = username.toLowerCase().replaceAll("#", "_x0023_");
    public static final String TESMAN_TABLE2_ROWTYPE =
        STANDALONE_XML_HEADER +
        "<"+XML_USERNAME+".xtesman_table2_rowtypeType xmlns=\"urn:PrependedSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
           "<srno>666</srno>" +
           "<detail>" +
              "<pnr>12</pnr>" +
              "<company>SOMECO</company>" +
              "<se>SECKS</se>" +
              "<scrip>SCRIPPER</scrip>" +
              "<tt>" +
                 "<acct>46</acct>" +
                 "<state>COMASTATE</state>" +
                 "<division>MYDIV</division>" +
                 "<country>CANADA</country>" +
              "</tt>" +
           "</detail>" +
        "</"+XML_USERNAME+".xtesman_table2_rowtypeType>";
}