001: /*
002: * Copyright 2003 (C) TJDO.
003: * All rights reserved.
004: *
005: * This software is distributed under the terms of the TJDO License version 1.0.
006: * See the terms of the TJDO License in the documentation provided with this software.
007: *
008: * $Id: DirectSQLQueryTest.java,v 1.3 2003/03/17 07:02:52 jackknifebarber Exp $
009: */
010:
011: package com.triactive.jdo.test;
012:
013: import com.triactive.jdo.store.NoExtentException;
014: import java.util.Collection;
015: import java.util.Iterator;
016: import javax.jdo.Extent;
017: import javax.jdo.PersistenceManager;
018: import javax.jdo.Query;
019: import javax.jdo.Transaction;
020: import org.apache.log4j.Category;
021:
022: /**
023: * Tests the functionality of TJDOSQL queries.
024: *
025: * @author <a href="mailto:mmartin5@austin.rr.com">Mike Martin</a>
026: * @version $Revision: 1.3 $
027: */
028:
029: public class DirectSQLQueryTest extends StorageTestCase {
030: private static final Category LOG = Category
031: .getInstance(DirectSQLQueryTest.class);
032:
033: private boolean schemaInitialized = false;
034:
035: /**
036: * Used by the JUnit framework to construct tests. Normally, programmers
037: * would never explicitly use this constructor.
038: *
039: * @param name Name of the <tt>TestCase</tt>.
040: */
041:
042: public DirectSQLQueryTest(String name) {
043: super (name);
044: }
045:
046: protected void setUp() throws Exception {
047: super .setUp();
048:
049: if (!schemaInitialized) {
050: addClassesToSchema(new Class[] { Widget.class, });
051:
052: schemaInitialized = true;
053: }
054: }
055:
056: public void testTJDOSQLQueryOfWidgets() throws Exception {
057: /*
058: * Can't run this test on SQL Server because it doesn't allow you to
059: * GROUP BY a bit column.
060: */
061: if ("sqlserver".equals(vendorID))
062: return;
063:
064: LOG.info("Testing TJDOSQL query against " + TEST_OBJECT_COUNT
065: + " " + Widget.class.getName() + " objects");
066: insertObjects(Widget.class);
067:
068: PersistenceManager pm = pmf.getPersistenceManager();
069: Transaction tx = pm.currentTransaction();
070:
071: try {
072: tx.begin();
073:
074: String sqlText;
075:
076: if ("db2".equals(vendorID)) {
077: /*
078: * In DB2 the AVG() function has to work on FLOAT values to
079: * avoid an arithmetic overflow.
080: */
081: sqlText = "SELECT W.{Widget.booleanField} AS {this.booleanValue},"
082: + " INTEGER(AVG(FLOAT(W.{Widget.intField}))) AS {this.avgIntValue},"
083: + " SMALLINT(AVG(FLOAT(W.{Widget.shortField}))) AS {this.avgShortValue}"
084: + " FROM {Widget} W"
085: + " WHERE W.{Widget.shortField} BETWEEN ?loVal? AND ?hiVal?"
086: + " GROUP BY W.{Widget.booleanField}";
087: } else if ("mysql".equals(vendorID)) {
088: /*
089: * In MySQL the GROUP BY clause has to reference columns by
090: * their alias.
091: */
092: sqlText = "SELECT W.{Widget.booleanField} AS {this.booleanValue},"
093: + " AVG(W.{Widget.intField}) AS {this.avgIntValue},"
094: + " AVG(W.{Widget.shortField}) AS {this.avgShortValue}"
095: + " FROM {Widget} W"
096: + " WHERE W.{Widget.shortField} BETWEEN ?loVal? AND ?hiVal?"
097: + " GROUP BY {this.booleanValue}";
098: } else if ("postgresql".equals(vendorID)) {
099: /* This is to get around a broken JDBC driver. */
100: sqlText = "SELECT W.{Widget.booleanField} AS {this.booleanValue},"
101: + " int8(AVG(W.{Widget.intField})) AS {this.avgIntValue},"
102: + " int4(AVG(W.{Widget.shortField})) AS {this.avgShortValue}"
103: + " FROM {Widget} W"
104: + " WHERE W.{Widget.shortField} BETWEEN ?loVal? AND ?hiVal?"
105: + " GROUP BY W.{Widget.booleanField}";
106: } else {
107: sqlText = "SELECT W.{Widget.booleanField} AS {this.booleanValue},"
108: + " AVG(W.{Widget.intField}) AS {this.avgIntValue},"
109: + " AVG(W.{Widget.shortField}) AS {this.avgShortValue}"
110: + " FROM {Widget} W"
111: + " WHERE W.{Widget.shortField} BETWEEN ?loVal? AND ?hiVal?"
112: + " GROUP BY W.{Widget.booleanField}";
113: }
114:
115: short loVal = 0;
116: short hiVal = Short.MAX_VALUE / 2;
117:
118: Query query = pm.newQuery("javax.jdo.query.TJDOSQL",
119: sqlText);
120: query.setClass(WidgetAverages.class);
121: query
122: .declareImports("import com.triactive.jdo.test.Widget");
123: query.declareParameters("short loVal, short hiVal");
124: Collection results = (Collection) query.execute(new Short(
125: loVal), new Short(hiVal));
126:
127: Iterator i = results.iterator();
128: int count = 0;
129:
130: while (i.hasNext()) {
131: WidgetAverages wa = (WidgetAverages) i.next();
132:
133: assertTrue("", wa.getAvgShortValue() >= loVal
134: && wa.getAvgShortValue() <= hiVal);
135: ++count;
136: }
137:
138: assertEquals("TJDOSQL query returned wrong number of rows",
139: 2, count);
140:
141: tx.commit();
142:
143: /*
144: * Negative test #1. Ensure that an attempt to write a field
145: * throws the proper exception.
146: */
147:
148: try {
149: tx.begin();
150:
151: WidgetAverages wa = (WidgetAverages) results.iterator()
152: .next();
153: wa.fillRandom();
154:
155: tx.commit();
156:
157: fail("Writing to a TJDOSQL query result object succeeded");
158: } catch (NoExtentException e) {
159: if (tx.isActive())
160: tx.rollback();
161: }
162:
163: /*
164: * Negative test #2. Ensure that an attempt to make a query result
165: * object persistent throws the proper exception.
166: */
167:
168: try {
169: tx.begin();
170:
171: WidgetAverages wa = new WidgetAverages();
172: pm.makePersistent(wa);
173:
174: tx.commit();
175:
176: fail("Making a query result object persistent succeeded");
177: } catch (NoExtentException e) {
178: if (tx.isActive())
179: tx.rollback();
180: }
181:
182: /*
183: * Negative test #3. Ensure that an attempt to delete a view object
184: * throws the proper exception.
185: */
186:
187: try {
188: tx.begin();
189:
190: WidgetAverages wa = (WidgetAverages) results.iterator()
191: .next();
192: pm.deletePersistent(wa);
193:
194: tx.commit();
195:
196: fail("Deleting a TJDOSQL query result object succeeded");
197: } catch (NoExtentException e) {
198: if (tx.isActive())
199: tx.rollback();
200: }
201: } finally {
202: if (tx.isActive())
203: tx.rollback();
204:
205: pm.close();
206: }
207:
208: removeObjects();
209: }
210: }
|