| /* |
| * 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>"; |
| } |