openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/query/TestSQLQueries.java

/*
 * TestSQLQueries.java
 *
 * Created on October 5, 2006, 4:59 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.openjpa.persistence.jdbc.query;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
import org.apache.openjpa.jdbc.meta.ClassMapping;
import org.apache.openjpa.jdbc.meta.MappingRepository;
import org.apache.openjpa.jdbc.sql.DBDictionary;
import org.apache.openjpa.kernel.OpenJPAStateManager;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import org.apache.openjpa.persistence.OpenJPAEntityManagerFactorySPI;
import org.apache.openjpa.persistence.OpenJPAQuery;
import org.apache.openjpa.persistence.jdbc.common.apps.AttachA;
import org.apache.openjpa.persistence.jdbc.common.apps.AttachD;
import org.apache.openjpa.persistence.jdbc.common.apps.RuntimeTest1;


public class TestSQLQueries
        extends org.apache.openjpa.persistence.jdbc.kernel.BaseJDBCTest {


    /** Creates a new instance of TestSQLQueries */
    public TestSQLQueries(String name)
    {
    	super(name);
    }

    public TestSQLQueries()
    {}

    private String _tableName = null;
    private String _fullTableName = null;
    private String _pkColName = null;
    private String _intColName = null;
    private String _stringColName = null;
    private String _relColName = null;

    @Override
    public void setUp() {
       deleteAll(RuntimeTest1.class);

        RuntimeTest1 pc1 = new RuntimeTest1("1", 1);
        RuntimeTest1 pc2 = new RuntimeTest1("2", 2);
        pc1.setSelfOneOne(pc2);

        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        startTx(pm);
        pm.persist(pc1);
        endTx(pm);

        JDBCConfiguration conf =
            (JDBCConfiguration) ((OpenJPAEntityManagerFactorySPI) pm)
            .getConfiguration();
        DBDictionary dict = conf.getDBDictionaryInstance();
        MappingRepository repos = conf.getMappingRepositoryInstance();
        ClassMapping mapping = repos.getMapping(RuntimeTest1.class,
                pm.getClassLoader(), true);

        _tableName = mapping.getTable().getName();
        _fullTableName = dict.getFullName(mapping.getTable(), false);
        _pkColName = mapping.getTable().getPrimaryKey().
                getColumns()[0].getName();
        _intColName = mapping.getFieldMapping("intField").
                getColumns()[0].getName();
        _stringColName = mapping.getFieldMapping("stringField").
                getColumns()[0].getName();
        _relColName = mapping.getFieldMapping("selfOneOne").
                getColumns()[0].getName();

        pm.close();
    }

    public void testStarQuery() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select * from " + _fullTableName + " order by " + _intColName);
        q.setResultClass(RuntimeTest1.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        assertEquals("1", ((RuntimeTest1) itr.next()).getStringField());
        assertTrue(itr.hasNext());
        assertEquals("2", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testCompiledQuery()
    throws Exception {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select * from " + _fullTableName + " order by " + _intColName);
        q.setResultClass(RuntimeTest1.class);

        //FIXME jthomas
        //q = pm.createQuery(roundtrips(q, false));
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        assertEquals("1", ((RuntimeTest1) itr.next()).getStringField());
        assertTrue(itr.hasNext());
        assertEquals("2", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testCompiledLanguageQuery()
    throws Exception {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select * from " + _fullTableName + " order by " + _intColName);
        q.setResultClass(RuntimeTest1.class);

        //FIXME jthomas
        //q = pm.createQuery("javax.jdo.query.SQL", roundtrips(q, false));
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        assertEquals("1", ((RuntimeTest1) itr.next()).getStringField());
        assertTrue(itr.hasNext());
        assertEquals("2", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testTableStarQuery() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select " + _tableName + ".* from " + _fullTableName
                + " order by " + _intColName);
        q.setResultClass(RuntimeTest1.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        assertEquals("1", ((RuntimeTest1) itr.next()).getStringField());
        assertTrue(itr.hasNext());
        assertEquals("2", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testColumnQuery() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select " + _pkColName + ", " + _intColName + ", "
                + _stringColName + " from " + _fullTableName + " order by "
                + _intColName);
        q.setResultClass(RuntimeTest1.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        assertEquals("1", ((RuntimeTest1) itr.next()).getStringField());
        assertTrue(itr.hasNext());
        assertEquals("2", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testJoinQuery() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select t0.* from " + _fullTableName + " t0, "
                + _fullTableName + " t1 where t0." + _relColName + " = t1."
                + _pkColName + " and t1." + _intColName + " = 2");
        q.setResultClass(RuntimeTest1.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        assertEquals("1", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testParameters() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select * from " + _fullTableName + " where 'foo' = ? and "
                + _intColName + " = ?");
        q.setResultClass(RuntimeTest1.class);
        //FIXME jthomas
        /*Iterator itr = ((Collection) q.execute("foo", new Integer(2))).
                iterator();
        assertTrue(itr.hasNext());
        assertEquals("2", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
         */
        q.closeAll();

        Map params = new HashMap();
        params.put(1, "foo");
        params.put(2, 2);
        //FIXME jthomas
        /*itr = ((Collection) q.executeWithMap(params)).iterator();
        assertTrue(itr.hasNext());
        assertEquals("2", ((RuntimeTest1) itr.next()).getStringField());
        assertFalse(itr.hasNext());
         */
        q.closeAll();
        pm.close();
    }

    public void testOnlySelectedFieldsLoaded() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select " + _pkColName + ", " + _intColName + ", "
                + _stringColName + " from " + _fullTableName + " order by "
                + _intColName);
        q.setResultClass(RuntimeTest1.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        RuntimeTest1 pc = (RuntimeTest1) itr.next();
        OpenJPAStateManager sm = getStateManager(pc, pm);
        assertTrue(sm.getLoaded().get(sm.getMetaData().
                getField("intField").getIndex()));
        assertTrue(sm.getLoaded().get(sm.getMetaData().
                getField("stringField").getIndex()));
        assertFalse(sm.getLoaded().get(sm.getMetaData().
                getField("longField").getIndex()));
        assertEquals("1", pc.getStringField());
        assertFalse(sm.getLoaded().get(sm.getMetaData().
                getField("longField").getIndex()));
        q.closeAll();
        pm.close();
    }

    public void testSingleColumnClasslessQuery() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select " + _stringColName + " from " + _fullTableName
                + " order by " + _stringColName);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        assertEquals("1", itr.next());
        assertTrue(itr.hasNext());
        assertEquals("2", itr.next());
        assertFalse(itr.hasNext());
        q.closeAll();

        q.setResultClass(Object[].class);
        itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        Object[] vals = (Object[]) itr.next();
        assertEquals(1, vals.length);
        assertEquals("1", vals[0]);
        assertTrue(itr.hasNext());
        vals = (Object[]) itr.next();
        assertEquals(1, vals.length);
        assertEquals("2", vals[0]);
        assertFalse(itr.hasNext());
        q.closeAll();

        pm.close();
    }

    public void testMultiColumnClasslessQuery() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select " + _intColName + ", " + _stringColName + " from "
                + _fullTableName + " order by " + _stringColName);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        Object[] vals = (Object[]) itr.next();
        assertEquals(2, vals.length);
        assertEquals(1, ((Number) vals[0]).intValue());
        assertEquals("1", vals[1]);
        assertTrue(itr.hasNext());
        vals = (Object[]) itr.next();
        assertEquals(2, vals.length);
        assertEquals(2, ((Number) vals[0]).intValue());
        assertEquals("2", vals[1]);
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testResultClass() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select * from " + _fullTableName + " order by " + _intColName);
        q.setResultClass(RuntimeTest1.class);
        q.setResultClass(Holder.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        Holder holder = (Holder) itr.next();
        assertEquals(0, holder.I);
        assertNull(holder.S);
        assertNotNull(holder.pc);
        assertEquals("1", holder.pc.getStringField());
        assertTrue(itr.hasNext());
        holder = (Holder) itr.next();
        assertEquals(0, holder.I);
        assertNull(holder.S);
        assertNotNull(holder.pc);
        assertEquals("2", holder.pc.getStringField());
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    public void testClasslessProjection() {
        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select " + _intColName + " as I, " + _stringColName
                + " as S from " + _fullTableName + " order by " + _intColName);
        q.setResultClass(Holder.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());
        Holder holder = (Holder) itr.next();
        assertNull(holder.pc);
        assertEquals(1, holder.I);
        assertEquals("1", holder.S);
        assertTrue(itr.hasNext());
        holder = (Holder) itr.next();
        assertNull(holder.pc);
        assertEquals(2, holder.I);
        assertEquals("2", holder.S);
        assertFalse(itr.hasNext());
        q.closeAll();
        pm.close();
    }

    /**
     * Manual test to see if a relation will be eagerly loaded when SQL
     * containing enough information is run. This is not run as part of
     * the unit tests since we don't know if this behavior should be
     * really expected to work or not.
     */
    public void relationLoadedTest() {
       deleteAll(AttachD.class);
       deleteAll(AttachA.class);

        AttachD d = new AttachD();
        AttachA a = new AttachA();
        d.setA(a);

        OpenJPAEntityManager pm =(OpenJPAEntityManager)currentEntityManager();
        pm.getTransaction().begin();
        pm.persist(d);
        pm.getTransaction().commit();
        pm.close();

        JDBCConfiguration conf =
            (JDBCConfiguration) ((OpenJPAEntityManagerFactorySPI) pm)
            .getConfiguration();
        DBDictionary dict = conf.getDBDictionaryInstance();
        MappingRepository repos = conf.getMappingRepositoryInstance();

        ClassMapping mappingA = repos.getMapping(AttachD.class,
                pm.getClassLoader(), true);
        String tableNameA = mappingA.getTable().getName();
        String fullTableNameA = dict.getFullName(mappingA.getTable(), false);
        String relColNameA = mappingA.getFieldMapping("a").
                getColumns()[0].getName();

        ClassMapping mappingD = repos.getMapping(AttachA.class,
                pm.getClassLoader(), true);
        String tableNameD = mappingD.getTable().getName();
        String fullTableNameD = dict.getFullName(mappingD.getTable(), false);
        String pkColNameD = mappingD.getTable().getPrimaryKey().
                getColumns()[0].getName();

        pm = (OpenJPAEntityManager)currentEntityManager();
        OpenJPAQuery q = pm.createQuery("javax.jdo.query.SQL",
                "select t0.*, t1.* from "
                + fullTableNameA + " t0, "
                + fullTableNameD + " t1 "
                + "where t0." + relColNameA + " = t1." + pkColNameD);

        // even the exact same SQL that Kodo generates will not
        // eagerly load the relation
        /*
          q = pm.newQuery ("javax.jdo.query.SQL",
              "SELECT t1.ID, t1.TYP, t1.VERSN, t1.ADBL, t1.AINT, "
              + "t1.ASTR, t1.BDBL, t1.BINT, t1.BSTR, t1.CDBL, t1.CINT, "
              + "t1.CSTR, t0.DDBL, t0.DINT, t0.DSTR "
              + "FROM ATTACHD t0 LEFT OUTER JOIN ATTACHA t1 ON t0.A = t1.ID");
         */

        q.setResultClass(AttachD.class);
        Iterator itr = ((Collection) q.getCandidateCollection()).iterator();
        assertTrue(itr.hasNext());

        d = (AttachD) itr.next();
        // d.getDstr ();

        OpenJPAStateManager sm = getStateManager(d, pm);
        assertTrue(sm.getLoaded().
                get(sm.getMetaData().getField("a").getIndex()));
        assertNotNull(d.getA());
        assertFalse(itr.hasNext());

        q.closeAll();
        pm.close();
    }

    public static class Holder {

        public RuntimeTest1 pc;
        public int I;
        public String S;

        public void setRuntimeTest1(RuntimeTest1 pc) {
            this.pc = pc;
        }
    }

    public static void main(String[] args)
    throws Exception {
        // main ();

        new TestSQLQueries().relationLoadedTest();
    }

    private static Object roundtrips(Object orig, boolean validateEquality)
    throws IOException, ClassNotFoundException {
        assertNotNull(orig);

        ByteArrayOutputStream bout = new ByteArrayOutputStream();
        ObjectOutputStream out = new ObjectOutputStream(bout);
        out.writeObject(orig);
        ByteArrayInputStream bin = new ByteArrayInputStream(
                bout.toByteArray());
        ObjectInputStream in = new ObjectInputStream(bin);
        Object result = in.readObject();

        if (validateEquality) {
            assertEquals(orig.hashCode(), result.hashCode());
            assertEquals(orig, result);
        }

        return result;
    }

}