content: /*****************************************************************
   *   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
   *
   *    https://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.cayenne.access;
  
  import org.apache.cayenne.Cayenne;
  import org.apache.cayenne.DataRow;
  import org.apache.cayenne.ResultIterator;
  import org.apache.cayenne.dba.frontbase.FrontBaseAdapter;
  import org.apache.cayenne.di.Inject;
  import org.apache.cayenne.map.DataMap;
  import org.apache.cayenne.map.EntityResult;
  import org.apache.cayenne.map.SQLResult;
  import org.apache.cayenne.query.CapsStrategy;
  import org.apache.cayenne.query.SQLTemplate;
  import org.apache.cayenne.runtime.CayenneRuntime;
  import org.apache.cayenne.test.jdbc.DBHelper;
  import org.apache.cayenne.test.jdbc.TableHelper;
  import org.apache.cayenne.testdo.testmap.Artist;
  import org.apache.cayenne.testdo.testmap.Painting;
  import org.apache.cayenne.unit.di.runtime.CayenneProjects;
  import org.apache.cayenne.unit.di.runtime.RuntimeCase;
  import org.apache.cayenne.unit.di.runtime.UseCayenneRuntime;
  import org.apache.cayenne.unit.util.SQLTemplateCustomizer;
  import org.junit.Before;
  import org.junit.Test;
  
  import java.sql.Types;
  import java.util.Collections;
  import java.util.List;
  
  import static org.junit.Assert.assertEquals;
  import static org.junit.Assert.assertFalse;
  import static org.junit.Assert.assertTrue;
  
  @UseCayenneRuntime(CayenneProjects.TESTMAP_PROJECT)
  public class DataContextSQLTemplateIT extends RuntimeCase {
  
  	@Inject
  	private CayenneRuntime runtime;
  
  	@Inject
  	protected DataContext context;
  
  	@Inject
  	protected DBHelper dbHelper;
  
  	@Inject
  	protected SQLTemplateCustomizer sqlTemplateCustomizer;
  
  	protected TableHelper tPainting;
  	protected TableHelper tArtist;
  
  	@Before
  	public void setUp() throws Exception {
  		tArtist = new TableHelper(dbHelper, "ARTIST");
  		tArtist.setColumns("ARTIST_ID", "ARTIST_NAME");
  
  		tPainting = new TableHelper(dbHelper, "PAINTING");
  		tPainting.setColumns("PAINTING_ID", "PAINTING_TITLE", "ARTIST_ID", "ESTIMATED_PRICE").setColumnTypes(
  				Types.INTEGER, Types.VARCHAR, Types.BIGINT, Types.DECIMAL);
  	}
  
  	protected void createFourArtists() throws Exception {
  		tArtist.insert(11, "artist2");
  		tArtist.insert(101, "artist3");
  		tArtist.insert(201, "artist4");
  		tArtist.insert(3001, "artist5");
  	}
  
  	protected void createFourArtistsAndThreePaintingsDataSet() throws Exception {
  		createFourArtists();
  
  		tPainting.insert(6, "p_artist3", 11, 1000);
  		tPainting.insert(7, "p_artist2", 101, 2000);
  		tPainting.insert(8, "p_artist4", null, 3000);
  	}
  
  	@Test
  	public void testSQLResultSetMappingMixed() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		String sql = "SELECT #result('t0.ARTIST_ID' 'long' 'X'), #result('t0.ARTIST_NAME' 'String' 'Y'), #result('t0.DATE_OF_BIRTH' 'Date' 'Z'), #result('count(t1.PAINTING_ID)' 'int' 'C') "
  				+ "FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) "
  				+ "GROUP BY t0.ARTIST_ID, t0.ARTIST_NAME, t0.DATE_OF_BIRTH " + "ORDER BY t0.ARTIST_ID";
  
  		DataMap map = context.getEntityResolver().getDataMap("testmap");
  		SQLTemplate query = new SQLTemplate(map, sql, false);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  
  		EntityResult artistResult = new EntityResult(Artist.class);
  		artistResult.addDbField(Artist.ARTIST_ID_PK_COLUMN, "X");
  		artistResult.addObjectField(Artist.ARTIST_NAME.getName(), "Y");
  		artistResult.addObjectField(Artist.DATE_OF_BIRTH.getName(), "Z");
  
  		SQLResult rsMap = new SQLResult();
  		rsMap.addEntityResult(artistResult);
  		rsMap.addColumnResult("C");
  		query.setResult(rsMap);
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(4, objects.size());
  
  		Object o1 = objects.get(0);
  		assertTrue("Expected Object[]: " + o1, o1 instanceof Object[]);
  		Object[] array1 = (Object[]) o1;
  		assertEquals(2, array1.length);
  		Object[] array2 = (Object[]) objects.get(1);
  		assertEquals(2, array2.length);
  		Object[] array3 = (Object[]) objects.get(2);
  		assertEquals(2, array3.length);
  		Object[] array4 = (Object[]) objects.get(3);
  		assertEquals(2, array3.length);
  
  		assertEquals(1, array1[1]);
  		assertEquals(1, array2[1]);
  		assertEquals(0, array3[1]);
  		assertEquals(0, array4[1]);
  		assertTrue("Unexpected Persistent: " + array1[0], array1[0] instanceof Artist);
  	}
  
  	@Test
  	public void testRootless_DataNodeName() throws Exception {
  		createFourArtists();
  
  		SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST", true);
  		query.setDataNodeName("testmap");
  		assertEquals(4, context.performQuery(query).size());
  	}
  
  	@Test
  	public void testRootless_DefaultDataNode() throws Exception {
  		createFourArtists();
  		SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST", true);
  		assertEquals(4, context.performQuery(query).size());
  	}
  
  	@Test
  	public void testSQLResultSetMappingScalar() throws Exception {
  		createFourArtists();
  
  		String sql = "SELECT count(1) AS X FROM ARTIST";
  
  		DataMap map = context.getEntityResolver().getDataMap("testmap");
  		SQLTemplate query = new SQLTemplate(map, sql, false);
  		query.setTemplate(FrontBaseAdapter.class.getName(), "SELECT COUNT(ARTIST_ID) X FROM ARTIST");
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  
  		SQLResult rsMap = new SQLResult();
  		rsMap.addColumnResult("X");
  		query.setResult(rsMap);
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(1, objects.size());
  
  		Object o = objects.get(0);
  		assertTrue("Expected Number: " + o, o instanceof Number);
  		assertEquals(4, ((Number) o).intValue());
  	}
  
  	@Test
  	public void testSQLResultSetMappingScalarArray() throws Exception {
  		createFourArtists();
  
  		String sql = "SELECT count(1) AS X, 77 AS Y FROM ARTIST";
  
  		DataMap map = context.getEntityResolver().getDataMap("testmap");
  		SQLTemplate query = new SQLTemplate(map, sql, false);
  		query.setTemplate(FrontBaseAdapter.class.getName(), "SELECT COUNT(ARTIST_ID) X, 77 Y FROM ARTIST GROUP BY Y");
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  
  		SQLResult rsMap = new SQLResult();
  		rsMap.addColumnResult("X");
  		rsMap.addColumnResult("Y");
  		query.setResult(rsMap);
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(1, objects.size());
  
  		Object o = objects.get(0);
  		assertTrue(o instanceof Object[]);
  
  		Object[] row = (Object[]) o;
  		assertEquals(2, row.length);
  
  		assertEquals(4, ((Number) row[0]).intValue());
  		assertEquals(77, ((Number) row[1]).intValue());
  	}
  
  	@Test
  	public void testColumnNamesCapitalization() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = new SQLTemplate(Artist.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.LOWER);
  		query.setFetchingDataRows(true);
  
  		List<DataRow> rows = context.performQuery(query);
  
  		DataRow row1 = rows.get(0);
  		assertFalse(row1.containsKey("ARTIST_ID"));
  		assertTrue(row1.containsKey("artist_id"));
  
  		DataRow row2 = rows.get(1);
  		assertFalse(row2.containsKey("ARTIST_ID"));
  		assertTrue(row2.containsKey("artist_id"));
  
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  
  		List<DataRow> rowsUpper = context.performQuery(query);
  
  		DataRow row3 = rowsUpper.get(0);
  		assertFalse(row3.containsKey("artist_id"));
  		assertTrue(row3.containsKey("ARTIST_ID"));
  
  		DataRow row4 = rowsUpper.get(1);
  		assertFalse(row4.containsKey("artist_id"));
  		assertTrue(row4.containsKey("ARTIST_ID"));
  	}
  
  	@Test
  	public void testFetchDataRows() throws Exception {
  		createFourArtists();
  
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = new SQLTemplate(Artist.class, template);
  
  		sqlTemplateCustomizer.updateSQLTemplate(query);
  
  		query.setFetchingDataRows(true);
  
  		List<DataRow> rows = context.performQuery(query);
  		assertEquals(4, rows.size());
  
  		DataRow row2 = rows.get(1);
  		assertEquals(3, row2.size());
  		Object id = row2.get("ARTIST_ID");
  		assertEquals(Integer.valueOf(101), Integer.valueOf(id.toString()));
  	}
  
  	@Test
  	public void testFetchObjects() throws Exception {
  		createFourArtists();
  
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
  
  		query.setFetchingDataRows(false);
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(4, objects.size());
  		assertTrue(objects.get(1) instanceof Artist);
  
  		Artist artist2 = (Artist) objects.get(1);
  		assertEquals("artist3", artist2.getArtistName());
  	}
  
  	@Test
  	public void testBindObjectEqualShort() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		Artist a = Cayenne.objectForPK(context, Artist.class, 101);
  
  		String template = "SELECT * FROM PAINTING " + "WHERE #bindObjectEqual($a) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("a", a));
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(1, objects.size());
  
  		Painting p = (Painting) objects.get(0);
  		assertEquals(7, Cayenne.intPKForObject(p));
  	}
  
  	@Test
  	public void testBindObjectNotEqualShort() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		Artist a = Cayenne.objectForPK(context, Artist.class, 101);
  
  		String template = "SELECT * FROM PAINTING " + "WHERE #bindObjectNotEqual($a) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("a", a));
  
  		List<?> objects = context.performQuery(query);
  
  		// null comparison is unpredictable across DB's ... some would return
  		// true on null
  		// <> value, some - false
  		assertTrue(objects.size() == 1 || objects.size() == 2);
  
  		Painting p = (Painting) objects.get(0);
  		assertEquals(6, Cayenne.intPKForObject(p));
  	}
  
  	@Test
  	public void testBindObjectEqualFull() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		Artist a = Cayenne.objectForPK(context, Artist.class, 101);
  
  		String template = "SELECT * FROM PAINTING t0"
  				+ " WHERE #bindObjectEqual($a [ 't0.ARTIST_ID' ] [ 'ARTIST_ID' ] ) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("a", a));
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(1, objects.size());
  
  		Painting p = (Painting) objects.get(0);
  		assertEquals(7, Cayenne.intPKForObject(p));
  	}
  
  	@Test
  	public void testBindObjectEqualFullNonArray() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		Artist a = Cayenne.objectForPK(context, Artist.class, 101);
  
  		String template = "SELECT * FROM PAINTING t0"
  				+ " WHERE #bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("a", a));
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(1, objects.size());
  
  		Painting p = (Painting) objects.get(0);
  		assertEquals(7, Cayenne.intPKForObject(p));
  	}
  
  	@Test
  	public void testBindObjectEqualNull() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		String template = "SELECT * FROM PAINTING t0"
  				+ " WHERE #bindObjectEqual($a [ 't0.ARTIST_ID' ] [ 'ARTIST_ID' ] ) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("a", null));
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(1, objects.size());
  
  		Painting p = (Painting) objects.get(0);
  		assertEquals(8, Cayenne.intPKForObject(p));
  	}
  
  	@Test
  	public void testBindObjectNotEqualFull() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		Artist a = Cayenne.objectForPK(context, Artist.class, 101);
  
  		String template = "SELECT * FROM PAINTING t0"
  				+ " WHERE #bindObjectNotEqual($a [ 't0.ARTIST_ID' ] [ 'ARTIST_ID' ] ) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("a", a));
  
  		List<?> objects = context.performQuery(query);
  		// null comparison is unpredictable across DB's ... some would return
  		// true on null
  		// <> value, some - false
  		assertTrue(objects.size() == 1 || objects.size() == 2);
  
  		Painting p = (Painting) objects.get(0);
  		assertEquals(6, Cayenne.intPKForObject(p));
  	}
  
  	@Test
  	public void testBindObjectNotEqualNull() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		String template = "SELECT * FROM PAINTING t0"
  				+ " WHERE #bindObjectNotEqual($a [ 't0.ARTIST_ID' ] [ 'ARTIST_ID' ] ) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("a", null));
  
  		List<Painting> objects = context.performQuery(query);
  		assertEquals(2, objects.size());
  
  		Painting p1 = objects.get(0);
  		assertEquals(6, Cayenne.intPKForObject(p1));
  
  		Painting p2 = objects.get(1);
  		assertEquals(7, Cayenne.intPKForObject(p2));
  	}
  
  	@Test
  	public void testBindEqualNull() throws Exception {
  		createFourArtistsAndThreePaintingsDataSet();
  
  		String template = "SELECT * FROM PAINTING t0" + " WHERE t0.ARTIST_ID #bindEqual($id) ORDER BY PAINTING_ID";
  		SQLTemplate query = new SQLTemplate(Painting.class, template);
  		query.setColumnNamesCapitalization(CapsStrategy.UPPER);
  		query.setParams(Collections.singletonMap("id", null));
  
  		List<Painting> objects = context.performQuery(query);
  		assertEquals(1, objects.size());
  
  		Painting p = objects.get(0);
  		assertEquals(8, Cayenne.intPKForObject(p));
  	}
  
  	@Test
  	public void testFetchLimit() throws Exception {
  		createFourArtists();
  
  		int fetchLimit = 2;
  
  		// sanity check
  		assertTrue(fetchLimit < 4);
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
  		query.setFetchLimit(fetchLimit);
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(fetchLimit, objects.size());
  		assertTrue(objects.get(0) instanceof Artist);
  	}
  
  	@Test
  	public void testFetchOffset() throws Exception {
  		createFourArtists();
  
  		int fetchOffset = 2;
  
  		// sanity check
  		assertTrue(fetchOffset < 4);
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
  		query.setFetchOffset(fetchOffset);
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(4 - fetchOffset, objects.size());
  		assertTrue(objects.get(0) instanceof Artist);
  	}
  
  	@Test
  	public void testFetchOffsetFetchLimit() throws Exception {
  		createFourArtists();
  
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
  		query.setFetchOffset(1);
  		query.setFetchLimit(2);
  
  		List<?> objects = context.performQuery(query);
  		assertEquals(2, objects.size());
  		assertTrue(objects.get(0) instanceof Artist);
  	}
  
  	@Test
  	public void testPageSize() throws Exception {
  		createFourArtists();
  
  		int pageSize = 3;
  
  		// sanity check
  		assertTrue(pageSize < 4);
  
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
  
  		query.setPageSize(pageSize);
  
  		List<?> objects = context.performQuery(query);
  
  		assertEquals(4, objects.size());
  		assertTrue(objects.get(0) instanceof Artist);
  
  		assertTrue(objects instanceof IncrementalFaultList<?>);
  		IncrementalFaultList<?> pagedList = (IncrementalFaultList<?>) objects;
  		assertEquals(4 - pageSize, pagedList.getUnfetchedObjects());
  
  		// check if we can resolve subsequent pages
  		Artist artist = (Artist) objects.get(pageSize);
  
  		int expectUnresolved = 4 - pageSize - pageSize;
  		if (expectUnresolved < 0) {
  			expectUnresolved = 0;
  		}
  		assertEquals(expectUnresolved, pagedList.getUnfetchedObjects());
  		assertEquals("artist" + (pageSize + 2), artist.getArtistName());
  	}
  
  	@Test
  	public void testIteratedQuery() throws Exception {
  		createFourArtists();
  
  		String template = "SELECT * FROM ARTIST ORDER BY ARTIST_ID";
  		SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
  
  		try (ResultIterator<?> it = context.performIteratedQuery(query);) {
  			long i = 0;
  
  			while (it.hasNextRow()) {
  				i++;
  
  				DataRow row = (DataRow) it.nextRow();
  				assertEquals(3, row.size());
  				assertEquals("artist" + (1 + i), row.get("ARTIST_NAME"));
  			}
  
  			assertEquals(4, i);
  		}
  
  	}
  
  	@Test
  	public void testQueryWithLineBreakAfterMacro() throws Exception {
  		createFourArtists();
  
  		// see CAY-726 for details
  		String template = "SELECT #result('count(*)' 'int' 'X')" + System.getProperty("line.separator") + "FROM ARTIST";
  		SQLTemplate query = sqlTemplateCustomizer.createSQLTemplate(Artist.class, template);
  		query.setFetchingDataRows(true);
  
  		List<?> result = context.performQuery(query);
  
  		assertEquals(4, ((DataRow) result.get(0)).get("X"));
  	}
  }
  