/*
 * 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.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.ByteArrayOutputStream;
import java.util.*;
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.OpenJPAQuery;

import org.apache.openjpa.persistence.jdbc.common.apps.*;


import java.lang.annotation.Annotation;
import junit.framework.*;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import org.apache.openjpa.persistence.OpenJPAEntityManagerFactory;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import org.apache.openjpa.persistence.OpenJPAEntityManagerFactorySPI;
import org.apache.openjpa.persistence.OpenJPAPersistence;


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;
    
    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(new Integer(1), "foo");
        params.put(new Integer(2), new Integer(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;
    }
    
}