/*******************************************************************************
 * Copyright (c) 1998-2013 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:
 *     David McCann - September 08, 2011 - 2.4 - Initial implementation
 ******************************************************************************/
package dbws.testing.rowtype;

//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 %ROWTYPE support. If, in a PL/SQL package, you want a record 
 * with fields (names & types, but not constraints) that mirror a
 * JDBC table, without defining the record in the package, %ROWTYPE
 * can be used, i.e. "PARAM1 OUT MYTABLE%ROWTYPE".  
 *
 */
public class RowTypeTestSuite extends DBWSTestSuite {

    static final String WSDL_REF = "ref=\"ns1:rtype_table_rowtypeType\"";
    static final String XSD = 
        "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
        "<xsd:schema xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" targetNamespace=\"urn:rowtype\" xmlns=\"urn:rowtype\" elementFormDefault=\"qualified\">" +
           "<xsd:complexType name=\"rtype_table_rowtypeType\">" +
              "<xsd:sequence>" + 
                 "<xsd:element name=\"id\" type=\"xsd:decimal\" minOccurs=\"0\" nillable=\"true\"/>" +
                 "<xsd:element name=\"name\" type=\"xsd:string\" minOccurs=\"0\" nillable=\"true\"/>" +
                 "<xsd:element name=\"since\" type=\"xsd:date\" minOccurs=\"0\" nillable=\"true\"/>" +
              "</xsd:sequence>" +
           "</xsd:complexType>" +
           "<xsd:element name=\"rtype_table_rowtypeType\" type=\"rtype_table_rowtypeType\"/>" +
        "</xsd:schema>";
    static final String SERVICE = 
        "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
        "<dbws xmlns:ns1=\"urn:rowtype\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
           "<name>rowtype</name>" +
           "<sessions-file>eclipselink-dbws-sessions.xml</sessions-file>" +
           "<query>" +
              "<name>rowtypeTest</name>" +
              "<parameter>" +
                 "<name>PARAM1</name>" +
                 "<type>xsd:int</type>" +
              "</parameter>" +
              "<result>" +
                 "<type>ns1:rtype_table_rowtypeType</type>" +
              "</result>" +
              "<named-query>" +
                 "<name>rowtypeTest</name>" +
              "</named-query>" +
           "</query>" +
           "<query>" +
              "<name>rowtypeTest2</name>" +
              "<parameter>" +
                 "<name>PARAM1</name>" +
                 "<type>xsd:int</type>" +
              "</parameter>" +
              "<result>" +
                 "<type>ns1:rtype_table_rowtypeType</type>" +
              "</result>" +
              "<named-query>" +
                 "<name>rowtypeTest2</name>" +
              "</named-query>" +
            "</query>" +
        "</dbws>";
    
    
    static final String ROWTYPE_TEST_TABLE = "RTYPE_TABLE";
    static final String CREATE_ROWTYPE_TEST_TABLE = "CREATE TABLE " + ROWTYPE_TEST_TABLE + " (" +
        "\nID NUMBER NOT NULL," +
        "\nNAME VARCHAR(25)," +
        "\nSINCE DATE," +
        "\nPRIMARY KEY (ID)" +
    "\n)";
    static final String[] POPULATE_ROWTYPE_TEST_TABLE = new String[] {
        "INSERT INTO " + ROWTYPE_TEST_TABLE + " (ID, NAME, SINCE) VALUES (1, 'Conky', " +
            "TO_DATE('2001-12-25 00:00:00','YYYY-MM-DD HH24:MI:SS'))",
        "INSERT INTO " + ROWTYPE_TEST_TABLE + " (ID, NAME, SINCE) VALUES (2, 'Ricky', " +
            "TO_DATE('2002-02-12 00:00:00','YYYY-MM-DD HH24:MI:SS'))",
        "INSERT INTO " + ROWTYPE_TEST_TABLE + " (ID, NAME, SINCE) VALUES (3, 'Bubbles'," +
            "TO_DATE('2001-10-30 00:00:00','YYYY-MM-DD HH24:MI:SS'))",
        "INSERT INTO " + ROWTYPE_TEST_TABLE + " (ID, NAME, SINCE) VALUES (4, 'Julian', " +
            "TO_DATE('2010-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))"
    };
    static final String ROWTYPE_TEST_PACKAGE = "RTYPE_PKG";
    static final String CREATE_ROWTYPE_TEST_PACKAGE =
        "CREATE OR REPLACE PACKAGE " + ROWTYPE_TEST_PACKAGE + " AS" +
            "\nPROCEDURE testProc(PARAM1 IN INTEGER, PARAM2 OUT " + ROWTYPE_TEST_TABLE + "%ROWTYPE);" +
            "\nFUNCTION test(PARAM1 IN INTEGER) RETURN " + ROWTYPE_TEST_TABLE + "%ROWTYPE;" +
        "\nEND " + ROWTYPE_TEST_PACKAGE + ";";
    
    static final String CREATE_ROWTYPE_TEST_PACKAGE_BODY =
        "CREATE OR REPLACE PACKAGE BODY " + ROWTYPE_TEST_PACKAGE + " AS" +
            "\nPROCEDURE testProc(PARAM1 IN INTEGER, PARAM2 OUT " + ROWTYPE_TEST_TABLE + "%ROWTYPE) AS" +
            "\nBEGIN" +
              "\nPARAM2 := test(PARAM1);" +
            "\nEND testProc;" +
            "\nFUNCTION test(PARAM1 IN INTEGER) RETURN " + ROWTYPE_TEST_TABLE + "%ROWTYPE AS" +        
                "\nL_DATA1 " + ROWTYPE_TEST_TABLE + "%ROWTYPE;" +
                "\nCURSOR C_EMP(PARAMTEMP IN INTEGER) IS SELECT * FROM " + ROWTYPE_TEST_TABLE + 
                    " WHERE " + ROWTYPE_TEST_TABLE + ".ID=PARAMTEMP;" +
            "\nBEGIN" +
                "\nOPEN C_EMP(PARAM1);" +
                "\nLOOP" +
                "\nFETCH C_EMP INTO L_DATA1;" +
                "\nEXIT WHEN C_EMP%NOTFOUND;" +
                "\nEND LOOP;" +
                "\nRETURN L_DATA1;" +
            "\nEND test;" +
        "\nEND " + ROWTYPE_TEST_PACKAGE + ";";
    
    static final String DROP_ROWTYPE_TEST_TABLE = "DROP TABLE " + ROWTYPE_TEST_TABLE;
    static final String DROP_ROWTYPE_SHADOWTYPE = "DROP TYPE " + ROWTYPE_TEST_TABLE + "_ROWTYPE";
    static final String DROP_ROWTYPE_TEST_PACKAGE = "DROP PACKAGE " + ROWTYPE_TEST_PACKAGE;

    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_ROWTYPE_TEST_TABLE, ddlDebug);
            try {
                Statement stmt = conn.createStatement();
                for (int i = 0; i < POPULATE_ROWTYPE_TEST_TABLE.length; i++) {
                    stmt.addBatch(POPULATE_ROWTYPE_TEST_TABLE[i]);
                }
                stmt.executeBatch();
            }
            catch (SQLException e) {
                //e.printStackTrace();
            }
            runDdl(conn, CREATE_ROWTYPE_TEST_PACKAGE, ddlDebug);
            runDdl(conn, CREATE_ROWTYPE_TEST_PACKAGE_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\">rowtype</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=\"rowtypeTest2\" " +
	              "catalogPattern=\"" + ROWTYPE_TEST_PACKAGE + "\" " +
	              "procedurePattern=\"testProc\" " +
	           "/>" +
              "<plsql-procedure " +
                  "name=\"rowtypeTest\" " +
                  "catalogPattern=\"" + ROWTYPE_TEST_PACKAGE + "\" " +
                  "procedurePattern=\"test\" " +
               "/>" +
            "</dbws-builder>";
          builder = null;
          DBWSTestSuite.setUp(".");
          
          // execute shadow type ddl to generate JDBC equivalents of PL/SQL types 
          for (String ddl : builder.getTypeDDL()) {
        	  runDdl(conn, ddl, ddlDebug);
          }
    }

    @AfterClass
    public static void tearDown() {
        String ddlDrop = System.getProperty(DATABASE_DDL_DROP_KEY, DEFAULT_DATABASE_DDL_DROP);
        if ("true".equalsIgnoreCase(ddlDrop)) {
            runDdl(conn, DROP_ROWTYPE_TEST_PACKAGE, ddlDebug);
            runDdl(conn, DROP_ROWTYPE_TEST_TABLE, ddlDebug);
        }
        // drop shadow type ddl 
        for (String ddl : builder.getTypeDropDDL()) {
      	  	runDdl(conn, ddl, ddlDebug);
        }
    }

    @Test
    public void rowTypeTest() {
        Invocation invocation = new Invocation("rowtypeTest");
        Operation op = xrService.getOperation(invocation.getName());
        invocation.setParameter("PARAM1", Integer.valueOf(1));
        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(RECORD_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    @Test
    public void rowTypeTest2() {
        Invocation invocation = new Invocation("rowtypeTest2");
        Operation op = xrService.getOperation(invocation.getName());
        invocation.setParameter("PARAM1", Integer.valueOf(1));
        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(RECORD_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    public static final String RECORD_XML =
        STANDALONE_XML_HEADER +
        "<rtype_table_rowtypeType xmlns=\"urn:rowtype\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
            "<id>1</id>" +
            "<name>Conky</name>" +
            "<since>2001-12-25</since>" +
        "</rtype_table_rowtypeType>";

    /**
     * Test element/complex type names do not contain '%'
     */
    @Test
    public void compareXSD() {
        assertNotNull("Schema stream is null", DBWS_SCHEMA_STREAM);
        Document controlDoc = xmlParser.parse(new StringReader(XSD));
        removeEmptyTextNodes(controlDoc);
        Document testDoc = xmlParser.parse(new StringReader(DBWS_SCHEMA_STREAM.toString()));
        removeEmptyTextNodes(testDoc);
        assertTrue("Schema comparison failed.  Expected:\n" + documentToString(controlDoc) + "\nActual\n" + documentToString(testDoc), comparer.isNodeEqual(controlDoc, testDoc));
    }
    
    /**
     * Test type names do not contain '%'
     */
    @Test
    public void compareSessions() {
        assertNotNull("Service stream is null", DBWS_SERVICE_STREAM);
        Document controlDoc = xmlParser.parse(new StringReader(SERVICE));
        removeEmptyTextNodes(controlDoc);
        Document testDoc = xmlParser.parse(new StringReader(DBWS_SERVICE_STREAM.toString()));
        removeEmptyTextNodes(testDoc);
        assertTrue("Service file comparison failed.  Expected:\n" + documentToString(controlDoc) + "\nActual\n" + documentToString(testDoc), comparer.isNodeEqual(controlDoc, testDoc));
    }
    
    /**
     * Test element ref name does not contain '%'
     */
    @Test
    public void compareWSDLElementRef() {
        assertNotNull("WSDL stream is null", DBWS_WSDL_STREAM);
        assertTrue("Expected to find:\n" + WSDL_REF + "\nbut did not.  WSDL:\n" + DBWS_WSDL_STREAM.toString(), DBWS_WSDL_STREAM.toString().contains(WSDL_REF));
    }
}