/*******************************************************************************
* Copyright (c) 1998, 2015 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 v1.0 and Eclipse Distribution License v. 1.0
* which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* Contributors:
* Mike Norman - May 2008, created DBWS test package
******************************************************************************/
package dbws.testing.simplesp;
//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;
import org.eclipse.persistence.platform.database.MySQLPlatform;
//testing imports
import dbws.testing.DBWSTestSuite;
public class SimpleSPTestSuite extends DBWSTestSuite {
static final String CREATE_SIMPLESP_TABLE =
"CREATE TABLE IF NOT EXISTS simplesp (" +
"\nEMPNO decimal(4,0)," +
"\nENAME varchar(10)," +
"\nJOB varchar(9)," +
"\nMGR decimal(4,0)," +
"\nHIREDATE date," +
"\nSAL decimal(7,2)," +
"\nCOMM decimal(7,2)," +
"\nDEPTNO decimal(2)," +
"\nPRIMARY KEY (EMPNO)" +
"\n)";
static String[] POPULATE_SIMPLESP_TABLE = new String[] {
"INSERT INTO simplesp VALUES (7369,'SMITH','CLERK',7902,'1980-12--17',800,NULL,20)",
"INSERT INTO simplesp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30)",
"INSERT INTO simplesp VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30)",
"INSERT INTO simplesp VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20)",
"INSERT INTO simplesp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30)",
"INSERT INTO simplesp VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30)",
"INSERT INTO simplesp VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10)",
"INSERT INTO simplesp VALUES (7788,'SCOTT','ANALYST',7566,'1981-06-09',3000,NULL,20)",
"INSERT INTO simplesp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10)",
"INSERT INTO simplesp VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30)",
"INSERT INTO simplesp VALUES (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20)",
"INSERT INTO simplesp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30)",
"INSERT INTO simplesp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20)",
"INSERT INTO simplesp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)"
};
static final String CREATE_NOARGSP_PROC =
"CREATE PROCEDURE NoArgSP()" +
"\nBEGIN" +
"\nEND";
static final String CREATE_VARCHARSP_PROC =
"CREATE PROCEDURE VarcharSP(IN X VARCHAR(20))" +
"\nBEGIN" +
"\nEND";
static final String CREATE_GETALL_PROC =
"CREATE PROCEDURE GetAll()" +
"\nBEGIN" +
"\nSELECT * FROM simplesp;" +
"\nEND";
static final String CREATE_FINDBYJOB_PROC =
"CREATE PROCEDURE FindByJob(IN J VARCHAR(29))" +
"\nBEGIN" +
"\nSELECT * FROM simplesp WHERE JOB LIKE J;" +
"\nEND";
static final String CREATE_INOUTARGSP_PROC =
"CREATE PROCEDURE InOutArgsSP(IN T VARCHAR(20), OUT U VARCHAR(20), OUT V NUMERIC)" +
"\nBEGIN" +
"\nset U = CONCAT('barf-' , T);" +
"\nset V = 55;" +
"\nEND";
static final String CREATE_GETSALARYBYID_PROC =
"CREATE PROCEDURE GetSalaryById(INOUT S NUMERIC)" +
"\nBEGIN" +
"\nDECLARE VAL NUMERIC;" +
"\nSELECT SAL INTO VAL FROM simplesp WHERE EMPNO LIKE S;" +
"\nSET S = VAL;" +
"\nEND";
static final String DROP_SIMPLESP_TABLE =
"DROP TABLE simplesp";
static final String DROP_NOARGSP_PROC =
"DROP PROCEDURE NoArgSP";
static final String DROP_VARCHARSP_PROC =
"DROP PROCEDURE VarcharSP";
static final String DROP_GETALL_PROC =
"DROP PROCEDURE GetAll";
static final String DROP_FINDBYJOB_PROC =
"DROP PROCEDURE FindByJob";
static final String DROP_INOUTARGSP_PROC =
"DROP PROCEDURE InOutArgsSP";
static final String DROP_GETSALARYBYID_PROC =
"DROP PROCEDURE GetSalaryById";
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_SIMPLESP_TABLE, ddlDebug);
runDdl(conn, CREATE_NOARGSP_PROC, ddlDebug);
runDdl(conn, CREATE_VARCHARSP_PROC, ddlDebug);
runDdl(conn, CREATE_GETALL_PROC, ddlDebug);
runDdl(conn, CREATE_FINDBYJOB_PROC, ddlDebug);
runDdl(conn, CREATE_INOUTARGSP_PROC, ddlDebug);
runDdl(conn, CREATE_GETSALARYBYID_PROC, ddlDebug);
try {
Statement stmt = conn.createStatement();
for (int i = 0; i < POPULATE_SIMPLESP_TABLE.length; i++) {
stmt.addBatch(POPULATE_SIMPLESP_TABLE[i]);
}
stmt.executeBatch();
}
catch (SQLException e) {
if (ddlDebug) {
e.printStackTrace();
}
}
}
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\">simpleSP</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>" +
"<procedure " +
"name=\"VarcharTest\" " +
"procedurePattern=\"VarcharSP\" " +
"returnType=\"xsd:int\" " +
"/>" +
"<procedure " +
"name=\"NoArgsTest\" " +
"procedurePattern=\"NoArgSP\" " +
"returnType=\"xsd:int\" " +
"/>" +
"<procedure " +
"name=\"GetAllTest\" " +
"procedurePattern=\"GetAll\" " +
"isCollection=\"true\" " +
"isSimpleXMLFormat=\"true\" " +
"simpleXMLFormatTag=\"simplesp-rows\" " +
"xmlTag=\"simplesp-row\" " +
"/>" +
"<procedure " +
"name=\"FindByJobTest\" " +
"procedurePattern=\"FindByJob\" " +
"isCollection=\"true\" " +
"isSimpleXMLFormat=\"true\" " +
"simpleXMLFormatTag=\"simplesp-rows\" " +
"xmlTag=\"simplesp-row\" " +
"/>" +
"<procedure " +
"name=\"InOutArgsTest\" " +
"procedurePattern=\"InOutArgsSP\" " +
"isSimpleXMLFormat=\"true\" " +
"/>" +
"<procedure " +
"name=\"GetSalaryByIdTest\" " +
"procedurePattern=\"GetSalaryById\" " +
"/>" +
"</dbws-builder>";
builder = null;
DBWSTestSuite.setUp(".");
}
@AfterClass
public static void tearDown() {
String ddlDrop = System.getProperty(DATABASE_DDL_DROP_KEY, DEFAULT_DATABASE_DDL_DROP);
if ("true".equalsIgnoreCase(ddlDrop)) {
runDdl(conn, DROP_SIMPLESP_TABLE, ddlDebug);
runDdl(conn, DROP_NOARGSP_PROC, ddlDebug);
runDdl(conn, DROP_VARCHARSP_PROC, ddlDebug);
runDdl(conn, DROP_GETALL_PROC, ddlDebug);
runDdl(conn, DROP_FINDBYJOB_PROC, ddlDebug);
runDdl(conn, DROP_INOUTARGSP_PROC, ddlDebug);
runDdl(conn, DROP_GETSALARYBYID_PROC, ddlDebug);
}
}
@Test
public void varcharTest() {
Invocation invocation = new Invocation("VarcharTest");
invocation.setParameter("X", "this is a test");
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(xrService.getORSession()
.getProject().getDatasourceLogin().getPlatform() instanceof MySQLPlatform ? VALUE_0_XML
: VALUE_1_XML));
assertTrue("control document not same as instance document", comparer.isNodeEqual(
controlDoc, doc));
}
public static final String VALUE_0_XML =
"<?xml version = '1.0' encoding = 'UTF-8'?>" +
"<value>0</value>";
public static final String VALUE_1_XML =
"<?xml version = '1.0' encoding = 'UTF-8'?>" +
"<value>1</value>";
@Test
public void noargsTest() {
Invocation invocation = new Invocation("NoArgsTest");
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(xrService.getORSession()
.getProject().getDatasourceLogin().getPlatform() instanceof MySQLPlatform ? VALUE_0_XML
: VALUE_1_XML));
assertTrue("control document not same as instance document", comparer.isNodeEqual(
controlDoc, doc));
}
@Test
public void getAllTest() {
Invocation invocation = new Invocation("GetAllTest");
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(ALL_SIMPLESP_ROWS_XML));
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
}
public static final String ALL_SIMPLESP_ROWS_XML =
"<?xml version = '1.0' encoding = 'UTF-8'?>" +
"<simplesp-rows xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"simple-xml-format\">" +
"<simplesp-row>" +
"<EMPNO>7369</EMPNO>" +
"<ENAME>SMITH</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7902</MGR>" +
"<HIREDATE>1980-12-17</HIREDATE>" +
"<SAL>800.00</SAL>" +
"<DEPTNO>20</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7499</EMPNO>" +
"<ENAME>ALLEN</ENAME>" +
"<JOB>SALESMAN</JOB>" +
"<MGR>7698</MGR>" +
"<HIREDATE>1981-02-20</HIREDATE>" +
"<SAL>1600.00</SAL>" +
"<COMM>300.00</COMM>" +
"<DEPTNO>30</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7521</EMPNO>" +
"<ENAME>WARD</ENAME>" +
"<JOB>SALESMAN</JOB>" +
"<MGR>7698</MGR>" +
"<HIREDATE>1981-02-22</HIREDATE>" +
"<SAL>1250.00</SAL>" +
"<COMM>500.00</COMM>" +
"<DEPTNO>30</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7566</EMPNO>" +
"<ENAME>JONES</ENAME>" +
"<JOB>MANAGER</JOB>" +
"<MGR>7839</MGR>" +
"<HIREDATE>1981-04-02</HIREDATE>" +
"<SAL>2975.00</SAL>" +
"<DEPTNO>20</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7654</EMPNO>" +
"<ENAME>MARTIN</ENAME>" +
"<JOB>SALESMAN</JOB>" +
"<MGR>7698</MGR>" +
"<HIREDATE>1981-09-28</HIREDATE>" +
"<SAL>1250.00</SAL>" +
"<COMM>1400.00</COMM>" +
"<DEPTNO>30</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7698</EMPNO>" +
"<ENAME>BLAKE</ENAME>" +
"<JOB>MANAGER</JOB>" +
"<MGR>7839</MGR>" +
"<HIREDATE>1981-05-01</HIREDATE>" +
"<SAL>2850.00</SAL>" +
"<DEPTNO>30</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7782</EMPNO>" +
"<ENAME>CLARK</ENAME>" +
"<JOB>MANAGER</JOB>" +
"<MGR>7839</MGR>" +
"<HIREDATE>1981-06-09</HIREDATE>" +
"<SAL>2450.00</SAL>" +
"<DEPTNO>10</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7788</EMPNO>" +
"<ENAME>SCOTT</ENAME>" +
"<JOB>ANALYST</JOB>" +
"<MGR>7566</MGR>" +
"<HIREDATE>1981-06-09</HIREDATE>" +
"<SAL>3000.00</SAL>" +
"<DEPTNO>20</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7839</EMPNO>" +
"<ENAME>KING</ENAME>" +
"<JOB>PRESIDENT</JOB>" +
"<HIREDATE>1981-11-17</HIREDATE>" +
"<SAL>5000.00</SAL>" +
"<DEPTNO>10</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7844</EMPNO>" +
"<ENAME>TURNER</ENAME>" +
"<JOB>SALESMAN</JOB>" +
"<MGR>7698</MGR>" +
"<HIREDATE>1981-09-08</HIREDATE>" +
"<SAL>1500.00</SAL>" +
"<COMM>0.00</COMM>" +
"<DEPTNO>30</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7876</EMPNO>" +
"<ENAME>ADAMS</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7788</MGR>" +
"<HIREDATE>1987-05-23</HIREDATE>" +
"<SAL>1100.00</SAL>" +
"<DEPTNO>20</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7900</EMPNO>" +
"<ENAME>JAMES</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7698</MGR>" +
"<HIREDATE>1981-12-03</HIREDATE>" +
"<SAL>950.00</SAL>" +
"<DEPTNO>30</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7902</EMPNO>" +
"<ENAME>FORD</ENAME>" +
"<JOB>ANALYST</JOB>" +
"<MGR>7566</MGR>" +
"<HIREDATE>1981-12-03</HIREDATE>" +
"<SAL>3000.00</SAL>" +
"<DEPTNO>20</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7934</EMPNO>" +
"<ENAME>MILLER</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7782</MGR>" +
"<HIREDATE>1982-01-23</HIREDATE>" +
"<SAL>1300.00</SAL>" +
"<DEPTNO>10</DEPTNO>" +
"</simplesp-row>" +
"</simplesp-rows>";
@Test
public void findByJobTest() {
Invocation invocation = new Invocation("FindByJobTest");
invocation.setParameter("J", "CL%");
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(ALL_SIMPLESP_CLERK_ROWS_XML));
assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
}
public static final String ALL_SIMPLESP_CLERK_ROWS_XML =
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<simplesp-rows xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"simple-xml-format\">" +
"<simplesp-row>" +
"<EMPNO>7369</EMPNO>" +
"<ENAME>SMITH</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7902</MGR>" +
"<HIREDATE>1980-12-17</HIREDATE>" +
"<SAL>800.00</SAL>" +
"<DEPTNO>20</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7876</EMPNO>" +
"<ENAME>ADAMS</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7788</MGR>" +
"<HIREDATE>1987-05-23</HIREDATE>" +
"<SAL>1100.00</SAL>" +
"<DEPTNO>20</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7900</EMPNO>" +
"<ENAME>JAMES</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7698</MGR>" +
"<HIREDATE>1981-12-03</HIREDATE>" +
"<SAL>950.00</SAL>" +
"<DEPTNO>30</DEPTNO>" +
"</simplesp-row>" +
"<simplesp-row>" +
"<EMPNO>7934</EMPNO>" +
"<ENAME>MILLER</ENAME>" +
"<JOB>CLERK</JOB>" +
"<MGR>7782</MGR>" +
"<HIREDATE>1982-01-23</HIREDATE>" +
"<SAL>1300.00</SAL>" +
"<DEPTNO>10</DEPTNO>" +
"</simplesp-row>" +
"</simplesp-rows>";
@Test
public void inOutArgsTest() {
Invocation invocation = new Invocation("InOutArgsTest");
invocation.setParameter("T", "yuck");
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(IN_OUT_ARGS_XML));
assertTrue("control document not same as instance document", comparer.isNodeEqual(
controlDoc, doc));
}
public static final String IN_OUT_ARGS_XML =
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<simple-xml-format>" +
"<simple-xml>" +
"<U>barf-yuck</U>" +
"<V>55</V>" +
"</simple-xml>" +
"</simple-xml-format>";
@Test
public void getSalaryByIdTest() {
Invocation invocation = new Invocation("GetSalaryByIdTest");
invocation.setParameter("S", 7902);
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(SALARY_XML));
assertTrue("Control document not same as instance document. Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
}
public static final String SALARY_XML =
"<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>" +
"<value>3000</value>";
}