001: /*
002: * $Header: /export/home/cvsroot/MyPersonalizerRepository/MyPersonalizer/Subsystems/Kernel/Sources/es/udc/mypersonalizer/kernel/model/query/executor/sql/SQLQueryExecutorDelegateTest.java,v 1.1.1.1 2004/03/25 12:08:37 fbellas Exp $
003: * $Revision: 1.1.1.1 $
004: * $Date: 2004/03/25 12:08:37 $
005: *
006: * =============================================================================
007: *
008: * Copyright (c) 2003, The MyPersonalizer Development Group
009: * (http://www.tic.udc.es/~fbellas/mypersonalizer/index.html) at
010: * University Of A Coruņa
011: * All rights reserved.
012: *
013: * Redistribution and use in source and binary forms, with or without
014: * modification, are permitted provided that the following conditions are met:
015: *
016: * - Redistributions of source code must retain the above copyright notice,
017: * this list of conditions and the following disclaimer.
018: *
019: * - Redistributions in binary form must reproduce the above copyright notice,
020: * this list of conditions and the following disclaimer in the documentation
021: * and/or other materials provided with the distribution.
022: *
023: * - Neither the name of the University Of A Coruņa nor the names of its
024: * contributors may be used to endorse or promote products derived from
025: * this software without specific prior written permission.
026: *
027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
028: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
029: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
030: * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
031: * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
032: * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
033: * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
034: * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
035: * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
036: * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
037: * POSSIBILITY OF SUCH DAMAGE.
038: *
039: */
040: package es.udc.mypersonalizer.kernel.model.query.executor.sql;
041:
042: import java.sql.Connection;
043: import java.sql.PreparedStatement;
044: import java.sql.Statement;
045: import java.util.ArrayList;
046: import java.util.Arrays;
047: import java.util.Collections;
048: import java.util.HashMap;
049: import java.util.Iterator;
050: import java.util.List;
051: import java.util.Map;
052:
053: import es.udc.mypersonalizer.kernel.model.properties.CompoundProperty;
054: import es.udc.mypersonalizer.kernel.model.properties.Property;
055: import es.udc.mypersonalizer.kernel.model.properties.PropertyNotFoundException;
056: import es.udc.mypersonalizer.kernel.model.properties.PropertyStructure;
057: import es.udc.mypersonalizer.kernel.model.properties.SimpleProperty;
058: import es.udc.mypersonalizer.kernel.model.query.ast.Query;
059: import es.udc.mypersonalizer.kernel.model.query.ast.Step;
060: import es.udc.mypersonalizer.kernel.model.query.ast.expr.EqualsExpression;
061: import es.udc.mypersonalizer.kernel.model.query.ast.expr.LiteralStringExpression;
062: import es.udc.mypersonalizer.kernel.model.query.ast.expr.PropertyListExpression;
063: import es.udc.mypersonalizer.kernel.model.query.ast.expr.StepExpression;
064: import es.udc.mypersonalizer.kernel.model.query.executor.MetaPropertyResolver;
065: import es.udc.mypersonalizer.kernel.model.query.parser.QueryParser;
066: import es.udc.mypersonalizer.kernel.model.repository.sql.plain.ConnectionPoolAdapterSingleton;
067: import es.udc.mypersonalizer.kernel.util.exceptions.InternalErrorException;
068: import junit.framework.TestCase;
069:
070: /* TODO: Needs better checking of results, and completing the checks for
071: * the last test cases.
072: * It might be possible to retrieve the properties using an accessor
073: * and compare the results...
074: */
075: /**
076: * Test cases for the SQLQueryExecutorDelegate
077: *
078: * @author Abel Muinho
079: * @since 1.0
080: */
081: public class SQLQueryExecutorDelegateTest extends TestCase {
082:
083: /**
084: * Constructor for SQLQueryExecutorDelegateTest.
085: * @param arg0
086: */
087: public SQLQueryExecutorDelegateTest(String arg0) {
088: super (arg0);
089: }
090:
091: protected void setUp() throws Exception {
092: dropData();
093: insertData();
094: }
095:
096: protected void tearDown() throws Exception {
097: dropData();
098: }
099:
100: public void testEvaluate1() throws Exception {
101: /* Get all simbol names where the symbol is "WHT" */
102: MetaPropertyResolver resolver = new MetaPropertyResolver();
103: Step steps[] = new Step[] { new Step(), new Step(), new Step() };
104: steps[0].setMetaProperty(resolver.resolve("StockNews"));
105: steps[1].setMetaProperty(resolver.resolve("symbols"));
106: MetaPropertyResolver forkedResolver = new MetaPropertyResolver(
107: resolver);
108: steps[2].setMetaProperty(resolver.resolve("name"));
109:
110: Step symbol = new Step();
111: symbol.setMetaProperty(forkedResolver.resolve("symbol"));
112: StepExpression symbolExpr = new StepExpression();
113: symbolExpr.setSteps(Collections.singletonList(symbol));
114: LiteralStringExpression white = new LiteralStringExpression();
115: white.setValue("WHT");
116: EqualsExpression eq = new EqualsExpression();
117: eq.setLHS(symbolExpr);
118: eq.setRHS(white);
119:
120: steps[1].setExpression(eq);
121:
122: Query query = new Query();
123: query.setReturnClause(Arrays.asList(steps));
124:
125: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
126: PropertyListExpression results = delegate.evaluate(query);
127: List data = results.getProperties();
128: assertTrue(data.size() == 2);
129: System.out.println("-- test 1");
130: for (Iterator it = data.iterator(); it.hasNext();) {
131: String[] values = ((Property) it.next())
132: .getValuesAsString();
133: System.out.println(Arrays.asList(values));
134: assertTrue(values.length == 1);
135: assertTrue(values[0].equals("White"));
136: }
137: }
138:
139: public void testEvaluate2() throws Exception {
140: /* Get all symbol names. */
141: MetaPropertyResolver resolver = new MetaPropertyResolver();
142: Step steps[] = new Step[] { new Step(), new Step(), new Step() };
143: steps[0].setMetaProperty(resolver.resolve("StockNews"));
144: steps[1].setMetaProperty(resolver.resolve("symbols"));
145: steps[2].setMetaProperty(resolver.resolve("name"));
146:
147: Query query = new Query();
148: query.setReturnClause(Arrays.asList(steps));
149:
150: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
151: PropertyListExpression results = delegate.evaluate(query);
152: List data = results.getProperties();
153: assertTrue(data.size() == 6);
154: List valuesExpected = new ArrayList(Arrays.asList(new String[] {
155: "White", "Blue", "Red", "White", "Green", "Yellow" }));
156: System.out.println("-- test 2");
157: for (Iterator it = data.iterator(); it.hasNext();) {
158: String[] values = ((Property) it.next())
159: .getValuesAsString();
160: System.out.println(Arrays.asList(values));
161: assertTrue(values.length == 1);
162: valuesExpected.remove(values[0]);
163: }
164: assertTrue(valuesExpected.size() == 0);
165: }
166:
167: public void testEvaluate3() throws Exception {
168: /* Get all values from the multivalued property "simple" */
169: MetaPropertyResolver resolver = new MetaPropertyResolver();
170: Step steps[] = new Step[] { new Step(), new Step() };
171: steps[0].setMetaProperty(resolver.resolve("SimpleMultivalued"));
172: steps[1].setMetaProperty(resolver.resolve("simple"));
173:
174: Query query = new Query();
175: query.setReturnClause(Arrays.asList(steps));
176:
177: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
178: PropertyListExpression results = delegate.evaluate(query);
179: List data = results.getProperties();
180: assertTrue(data.size() == 2);
181: System.out.println("-- test 3");
182: int previousLength = 0;
183: for (Iterator it = data.iterator(); it.hasNext();) {
184: String[] values = ((Property) it.next())
185: .getValuesAsString();
186: System.out.println(Arrays.asList(values));
187: if (values.length == 2 && previousLength != 2) {
188: assertTrue(values[0].equals("Magenta"));
189: assertTrue(values[1].equals("Cyan"));
190: previousLength = 2;
191: } else if (values.length == 3 && previousLength != 3) {
192: assertTrue(values[0].equals("Pink"));
193: assertTrue(values[1].equals("Gray"));
194: assertTrue(values[2].equals("Black"));
195: previousLength = 3;
196: } else {
197: fail("simple must have one property with 2 values and "
198: + "another one with 3 values");
199: }
200: }
201: }
202:
203: public void testEvaluate4() throws Exception {
204: /* Get all compound properties "symbols" */
205: MetaPropertyResolver resolver = new MetaPropertyResolver();
206: Step steps[] = new Step[] { new Step(), new Step() };
207: steps[0].setMetaProperty(resolver.resolve("StockNews"));
208: steps[1].setMetaProperty(resolver.resolve("symbols"));
209:
210: Query query = new Query();
211: query.setReturnClause(Arrays.asList(steps));
212:
213: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
214: PropertyListExpression results = delegate.evaluate(query);
215: List data = results.getProperties();
216: assertTrue(data.size() == 6);
217: Map expectedPairs = new HashMap();
218: expectedPairs.put("White", "WHT");
219: expectedPairs.put("White", "WHT");
220: expectedPairs.put("Red", "RD");
221: expectedPairs.put("Green", "GRN");
222: expectedPairs.put("Blue", "BL");
223: expectedPairs.put("Yellow", "YLLW");
224:
225: System.out.println("-- test 4");
226: for (Iterator it = data.iterator(); it.hasNext();) {
227: CompoundProperty prop = (CompoundProperty) it.next();
228: System.out.println(propertyToString(prop));
229: SimpleProperty name = (SimpleProperty) prop
230: .findProperty("0.name");
231: SimpleProperty symbol = (SimpleProperty) prop
232: .findProperty("0.symbol");
233: String[] nameValues = name.getValuesAsString();
234: String[] symbolValues = symbol.getValuesAsString();
235: assertTrue(nameValues.length == 1);
236: assertTrue(symbolValues.length == 1);
237: boolean onlyOneValue = false;
238: try {
239: prop.findProperty("1.name");
240: } catch (PropertyNotFoundException e) {
241: onlyOneValue = true;
242: }
243: assertTrue(onlyOneValue);
244: assertTrue(expectedPairs.containsKey(nameValues[0]));
245: assertTrue(expectedPairs.get(nameValues[0]).equals(
246: symbolValues[0]));
247: }
248: }
249:
250: public void testEvaluate5() throws Exception {
251: MetaPropertyResolver resolver = new MetaPropertyResolver();
252: Step steps[] = new Step[] { new Step() };
253: steps[0].setMetaProperty(resolver.resolve("StockNews"));
254:
255: Query query = new Query();
256: query.setReturnClause(Arrays.asList(steps));
257:
258: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
259: PropertyListExpression results = delegate.evaluate(query);
260: List data = results.getProperties();
261: System.out.println("-- test 5");
262: for (Iterator it = data.iterator(); it.hasNext();) {
263: CompoundProperty prop = (CompoundProperty) it.next();
264: System.out.println(propertyToString(prop));
265: SimpleProperty maxHeadlines = (SimpleProperty) prop
266: .findProperty("0.maxHeadlines");
267: assertTrue(maxHeadlines.getValuesAsObject()[0] instanceof Number);
268: assertTrue(maxHeadlines.getValuesAsObject()[0] instanceof Integer);
269: }
270: }
271:
272: public void testEvaluate6() throws Exception {
273: MetaPropertyResolver resolver = new MetaPropertyResolver();
274: Step steps[] = new Step[] { new Step(), new Step() };
275: steps[0].setMetaProperty(resolver.resolve("StockNews"));
276: steps[1].setMetaProperty(resolver.resolve("maxHeadlines"));
277:
278: Query query = new Query();
279: query.setReturnClause(Arrays.asList(steps));
280:
281: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
282: PropertyListExpression results = delegate.evaluate(query);
283: List data = results.getProperties();
284: System.out.println("-- test 6");
285: for (Iterator it = data.iterator(); it.hasNext();) {
286: SimpleProperty prop = (SimpleProperty) it.next();
287: System.out.println(propertyToString(prop));
288: Object value = prop.getValuesAsObject()[0];
289: System.out.println("Value class: "
290: + value.getClass().getName());
291: assertTrue(value instanceof Number);
292: assertTrue(value instanceof Integer);
293: }
294: }
295:
296: public void testEvaluate7() throws Exception {
297: MetaPropertyResolver resolver = new MetaPropertyResolver();
298: Step steps[] = new Step[] { new Step(), new Step(), new Step() };
299: steps[0].setMetaProperty(resolver.resolve("StockNews"));
300: steps[1].setMetaProperty(resolver.resolve("symbols"));
301: MetaPropertyResolver forkedResolver = new MetaPropertyResolver(
302: resolver);
303: steps[2].setMetaProperty(resolver.resolve("name"));
304:
305: Step symbol = new Step();
306: symbol.setMetaProperty(forkedResolver.resolve("symbol"));
307:
308: Query query = new Query();
309: query.setReturnClause(Arrays.asList(steps));
310: query.setOrderByClause(Arrays.asList(new Step[] { steps[0],
311: steps[1], symbol }));
312:
313: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
314: PropertyListExpression results = delegate.evaluate(query);
315: List data = results.getProperties();
316: System.out.println("-- test 6");
317: for (Iterator it = data.iterator(); it.hasNext();) {
318: System.out.println(Arrays.asList(((Property) it.next())
319: .getValuesAsString()));
320: }
321: }
322:
323: public void testEvaluate8() throws Exception {
324: MetaPropertyResolver resolver = new MetaPropertyResolver();
325: Step steps[] = new Step[] { new Step(), new Step() };
326: steps[0].setMetaProperty(resolver.resolve("SimpleMultivalued"));
327: steps[1].setMetaProperty(resolver.resolve("simple"));
328:
329: Query query = new Query();
330: query.setReturnClause(Arrays.asList(steps));
331: query.setOrderByClause(Arrays.asList(steps));
332:
333: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
334: PropertyListExpression results = delegate.evaluate(query);
335: List data = results.getProperties();
336: System.out.println("-- test 7");
337: for (Iterator it = data.iterator(); it.hasNext();) {
338: System.out.println(Arrays.asList(((Property) it.next())
339: .getValuesAsString()));
340: }
341: }
342:
343: public void testBug0002() throws Exception {
344: /* Found by Fernando Bellas, 2004-02-17
345: * Checked and Fixed by Abel Muinho.
346: *
347: * This query fails with ArrayIndexOutOfBoundsException.
348: * return /URI[desktop/page/services/stockQuote/symbol="SUNW"
349: * or desktop/page/services/stockQuote/symbol="SUNW"]/email
350: * The relationship services-StockQuote is used twice, generating
351: * 2 parameters for it. The generated SQL only uses the
352: * relationship once, so we have one extra parameter.
353: *
354: * Since StockQuote is not available in MyPersonalizer test data,
355: * we use the following query, triggering the same error.
356: * return /WL/services[weather/style='infobox'
357: * or weather/style='bigtemp']
358: */
359: QueryParser p = new QueryParser();
360: Query q = p.parse("return "
361: + "/WL/services[weather/style='infobox'"
362: + " or weather/style='bigtemp']");
363: SQLQueryExecutorDelegate delegate = new SQLQueryExecutorDelegate();
364: /* Perform the query. */
365: try {
366: delegate.evaluate(q, 0, 1);
367: } catch (InternalErrorException e) {
368: if (e.getCause() instanceof ArrayIndexOutOfBoundsException) {
369: fail("ArrayIndexOutOfBoundsException");
370: } else {
371: throw e;
372: }
373: }
374: }
375:
376: private String propertyToString(SimpleProperty p) {
377: return Arrays.asList(p.getValuesAsString()).toString();
378: }
379:
380: private String propertyToString(CompoundProperty p) {
381: PropertyStructure[] values = (PropertyStructure[]) p
382: .getValuesAsObject();
383: StringBuffer result = new StringBuffer();
384: result.append("[");
385: for (int i = 0; i < values.length; i++) {
386: Map properties = values[i].getAsMap();
387: Iterator it = properties.entrySet().iterator();
388: result.append("[");
389: while (it.hasNext()) {
390: Map.Entry entry = (Map.Entry) it.next();
391: Property property = (Property) entry.getValue();
392:
393: if (property instanceof SimpleProperty) {
394: result
395: .append(propertyToString((SimpleProperty) property));
396: } else {
397: result
398: .append(propertyToString((CompoundProperty) property));
399: }
400: if (it.hasNext()) {
401: result.append(", ");
402: }
403: }
404: result.append("]");
405: if (i < values.length - 1) {
406: result.append(", ");
407: }
408: }
409: result.append("]");
410: return result.toString();
411: }
412:
413: private void insertData() throws Exception {
414: Connection c = ConnectionPoolAdapterSingleton.getInstance()
415: .getConnection();
416: PreparedStatement st = c
417: .prepareStatement("INSERT into StockNews"
418: + " (propId, maxHeadlines)" + " VALUES (?, ?)");
419: st.setLong(1, 1); // propId = 1
420: st.setInt(2, 5); // maxHeadlines = 5
421: st.addBatch();
422: st.setLong(1, 2); // propId = 2
423: st.setInt(2, 7); // maxHeadlines = 7
424: st.addBatch();
425: st.executeBatch();
426: st.close();
427: st = c.prepareStatement("INSERT INTO StockNews1symbols"
428: + " (propId, genId, name, symbol)"
429: + " VALUES (?, ?, ?, ?)");
430: // First property
431: // First value
432: st.setLong(1, 1);
433: st.setLong(2, 1);
434: st.setString(3, "White");
435: st.setString(4, "WHT");
436: st.addBatch();
437: // Second value
438: st.setLong(1, 1);
439: st.setLong(2, 2);
440: st.setString(3, "Blue");
441: st.setString(4, "BL");
442: st.addBatch();
443: // Third value
444: st.setLong(1, 1);
445: st.setLong(2, 3);
446: st.setString(3, "Red");
447: st.setString(4, "RD");
448: st.addBatch();
449: // Second property
450: // First value
451: st.setLong(1, 2);
452: st.setLong(2, 1);
453: st.setString(3, "White");
454: st.setString(4, "WHT");
455: st.addBatch();
456: // Second value
457: st.setLong(1, 2);
458: st.setLong(2, 2);
459: st.setString(3, "Green");
460: st.setString(4, "GRN");
461: st.addBatch();
462: // Third value
463: st.setLong(1, 2);
464: st.setLong(2, 3);
465: st.setString(3, "Yellow");
466: st.setString(4, "YLLW");
467: st.addBatch();
468:
469: st.executeBatch();
470: st.close();
471:
472: //--- SIMPLE MULTIVALUED
473: st = c
474: .prepareStatement("INSERT INTO SimpleMultivalued VALUES (?)");
475: st.setLong(1, 1);
476: st.addBatch();
477: st.setLong(1, 2);
478: st.addBatch();
479: st.executeBatch();
480: st.close();
481: st = c
482: .prepareStatement("INSERT INTO SimpleMultivaluedDataTable"
483: + " VALUES (?, ?, ?)");
484: // FIRST
485: st.setLong(1, 1);
486: st.setLong(2, 1);
487: st.setString(3, "Magenta");
488: st.addBatch();
489: st.setLong(1, 1);
490: st.setLong(2, 2);
491: st.setString(3, "Cyan");
492: st.addBatch();
493: // SECOND
494: st.setLong(1, 2);
495: st.setLong(2, 1);
496: st.setString(3, "Pink");
497: st.addBatch();
498: st.setLong(1, 2);
499: st.setLong(2, 2);
500: st.setString(3, "Gray");
501: st.addBatch();
502: st.setLong(1, 2);
503: st.setLong(2, 3);
504: st.setString(3, "Black");
505: st.addBatch();
506:
507: st.executeBatch();
508: c.close();
509: }
510:
511: private void dropData() throws Exception {
512: Connection c = ConnectionPoolAdapterSingleton.getInstance()
513: .getConnection();
514: Statement st = c.createStatement();
515: try {
516: st
517: .executeUpdate("DELETE FROM StockNews1symbols WHERE 1 = 1");
518: } catch (Exception e) {
519: System.err
520: .println("Couldn't delete from StockNews1symbols.");
521: e.printStackTrace();
522: }
523: try {
524: st.executeUpdate("DELETE FROM StockNews WHERE 1 = 1");
525: } catch (Exception e) {
526: System.err.println("Couldn't delete from StockNews.");
527: e.printStackTrace();
528: }
529:
530: try {
531: st
532: .executeUpdate("DELETE FROM SimpleMultivaluedDataTable WHERE 1 = 1");
533: } catch (Exception e) {
534: System.err
535: .println("Couldn't delete from SimpleMultivaluedDataTable.");
536: e.printStackTrace();
537: }
538: try {
539: st
540: .executeUpdate("DELETE FROM SimpleMultivalued WHERE 1 = 1");
541: } catch (Exception e) {
542: System.err
543: .println("Couldn't delete from SimpleMultivalued.");
544: e.printStackTrace();
545: }
546:
547: st.close();
548: c.close();
549: }
550:
551: }
|