001: /*
002: * $Header: /export/home/cvsroot/MyPersonalizerRepository/MyPersonalizer/Subsystems/Kernel/Sources/es/udc/mypersonalizer/kernel/model/query/executor/sql/SelectIdsMetaPropertyVisitorTest.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.Arrays;
046: import java.util.Collections;
047: import java.util.Iterator;
048:
049: import es.udc.mypersonalizer.kernel.model.query.ast.Query;
050: import es.udc.mypersonalizer.kernel.model.query.ast.Step;
051: import es.udc.mypersonalizer.kernel.model.query.ast.expr.ContainsFunctionExpression;
052: import es.udc.mypersonalizer.kernel.model.query.ast.expr.EqualsExpression;
053: import es.udc.mypersonalizer.kernel.model.query.ast.expr.Expression;
054: import es.udc.mypersonalizer.kernel.model.query.ast.expr.LiteralStringExpression;
055: import es.udc.mypersonalizer.kernel.model.query.ast.expr.StartsWithFunctionExpression;
056: import es.udc.mypersonalizer.kernel.model.query.ast.expr.StepExpression;
057: import es.udc.mypersonalizer.kernel.model.query.executor.MetaPropertyResolver;
058: import es.udc.mypersonalizer.kernel.model.query.parser.QueryParser;
059: import es.udc.mypersonalizer.kernel.model.repository.sql.plain.ConnectionPoolAdapterSingleton;
060: import es.udc.mypersonalizer.kernel.model.repository.sql.util.SQLOperations;
061: import junit.framework.TestCase;
062:
063: /**
064: * Casos de prueba para SelectIdsMetaPropertyVisitor.
065: *
066: * @author Abel Muinho
067: * @since 1.0
068: */
069: public class SelectIdsMetaPropertyVisitorTest extends TestCase {
070: /**
071: * Constructor for SelectIdsMetaPropertyVisitorTest.
072: *
073: * @param arg0
074: */
075: public SelectIdsMetaPropertyVisitorTest(String arg0) {
076: super (arg0);
077: }
078:
079: public void testGenerateSQL() throws Exception {
080: MetaPropertyResolver resolver = new MetaPropertyResolver();
081: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
082: Step steps[] = new Step[] { new Step(), new Step(), new Step() };
083: steps[0].setMetaProperty(resolver.resolve("StockNews"));
084: steps[1].setMetaProperty(resolver.resolve("symbols"));
085: MetaPropertyResolver forkedResolver = new MetaPropertyResolver(
086: resolver);
087: steps[2].setMetaProperty(resolver.resolve("name"));
088:
089: Step symbol = new Step();
090: symbol.setMetaProperty(forkedResolver.resolve("symbol"));
091: StepExpression symbolExpr = new StepExpression();
092: symbolExpr.setSteps(Collections.singletonList(symbol));
093: LiteralStringExpression oracle = new LiteralStringExpression();
094: oracle.setValue("ORCL");
095: EqualsExpression eq = new EqualsExpression();
096: eq.setLHS(symbolExpr);
097: eq.setRHS(oracle);
098:
099: steps[1].setExpression(eq);
100:
101: Step orderBySymbols = new Step();
102: orderBySymbols.setMetaProperty(steps[1].getMetaProperty());
103: Query q = new Query();
104: q.setReturnClause(Arrays.asList(steps));
105: q.setOrderByClause(Arrays.asList(new Step[] { steps[0],
106: orderBySymbols, symbol }));
107: v.buildQuery(q);
108: System.out.println(v.getSqlForPropertyIds());
109: }
110:
111: public void testGenerateSQLWithVirtualMetaProperties1()
112: throws Exception {
113: MetaPropertyResolver resolver = new MetaPropertyResolver();
114: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
115: Step steps[] = new Step[] { new Step(), new Step(), new Step() };
116: steps[0].setMetaProperty(resolver.resolve("URI"));
117: steps[1].setMetaProperty(resolver.resolve("desktop"));
118: steps[2].setMetaProperty(resolver.resolve("dwlPropId"));
119:
120: Query q = new Query();
121: q.setReturnClause(Arrays.asList(steps));
122:
123: v.buildQuery(q);
124: System.out.println(v.getSqlForPropertyIds());
125: }
126:
127: public void testGenerateSQLWithVirtualMetaProperties2()
128: throws Exception {
129: MetaPropertyResolver resolver = new MetaPropertyResolver();
130: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
131: Step steps[] = new Step[] { new Step(), new Step(), new Step() };
132: steps[0].setMetaProperty(resolver.resolve("URI"));
133: steps[1].setMetaProperty(resolver.resolve("desktop"));
134: steps[2].setMetaProperty(resolver.resolve("wlPropIds"));
135:
136: Query q = new Query();
137: q.setReturnClause(Arrays.asList(steps));
138:
139: v.buildQuery(q);
140: System.out.println(v.getSqlForPropertyIds());
141: }
142:
143: public void testGenerateSQLWithVirtualMetaProperties3()
144: throws Exception {
145: MetaPropertyResolver resolver = new MetaPropertyResolver();
146: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
147: Step steps[] = new Step[] { new Step(), new Step(), new Step() };
148: steps[0].setMetaProperty(resolver.resolve("URI"));
149: steps[1].setMetaProperty(resolver.resolve("desktop"));
150: steps[2].setMetaProperty(resolver.resolve("wlPropIds"));
151:
152: Query q = new Query();
153: q.setReturnClause(Arrays.asList(new Step[] { steps[0] }));
154: q.setOrderByClause(Arrays.asList(steps));
155:
156: v.buildQuery(q);
157: System.out.println(v.getSqlForPropertyIds());
158: }
159:
160: public void testGenerateSQLWithStartsWithFunction1()
161: throws Exception {
162: MetaPropertyResolver resolver = new MetaPropertyResolver();
163: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
164: Step steps[] = new Step[] { new Step() };
165: steps[0].setMetaProperty(resolver.resolve("URI"));
166:
167: Step name = new Step();
168: name.setMetaProperty(resolver.resolve("name"));
169: StepExpression stepExpression = new StepExpression();
170: stepExpression.setSteps(Collections.singletonList(name));
171: LiteralStringExpression literal = new LiteralStringExpression();
172: literal.setValue("my");
173:
174: StartsWithFunctionExpression startsWith = new StartsWithFunctionExpression();
175: startsWith.setArguments(new Expression[] { stepExpression,
176: literal });
177:
178: steps[0].setExpression(startsWith);
179: Query q = new Query();
180: q.setReturnClause(Arrays.asList(steps));
181:
182: v.buildQuery(q);
183: System.out.println(v.getSqlForPropertyIds());
184: System.out.println(v.getParameters());
185: assertTrue(v.getParameters().get(0).equals("my%"));
186: }
187:
188: public void testGenerateSQLWithStartsWithFunction2()
189: throws Exception {
190: MetaPropertyResolver resolver = new MetaPropertyResolver();
191: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
192: Step steps[] = new Step[] { new Step() };
193: steps[0].setMetaProperty(resolver.resolve("URI"));
194:
195: Step name = new Step();
196: name.setMetaProperty(resolver.resolve("name"));
197: StepExpression stepExpression = new StepExpression();
198: stepExpression.setSteps(Collections.singletonList(name));
199: LiteralStringExpression literal = new LiteralStringExpression();
200: /* This expression needs to be scaped inside a LIKE. */
201: literal.setValue("%%_%#_");
202:
203: StartsWithFunctionExpression startsWith = new StartsWithFunctionExpression();
204: startsWith.setArguments(new Expression[] { stepExpression,
205: literal });
206:
207: steps[0].setExpression(startsWith);
208: Query q = new Query();
209: q.setReturnClause(Arrays.asList(steps));
210:
211: v.buildQuery(q);
212: System.out.println(v.getSqlForPropertyIds());
213: System.out.println(v.getParameters());
214: assertTrue(v.getParameters().get(0).equals("#%#%#_#%###_%"));
215: }
216:
217: public void testGenerateSQLWithContainsFunction() throws Exception {
218: MetaPropertyResolver resolver = new MetaPropertyResolver();
219: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
220: Step result = new Step();
221: result.setMetaProperty(resolver.resolve("URI"));
222: Step expressionSteps[] = new Step[] { new Step(), new Step() };
223: expressionSteps[0].setMetaProperty(resolver.resolve("desktop"));
224: expressionSteps[1].setMetaProperty(resolver
225: .resolve("wlPropIds"));
226:
227: StepExpression stepExpression = new StepExpression();
228: stepExpression.setSteps(Arrays.asList(expressionSteps));
229:
230: LiteralStringExpression literal = new LiteralStringExpression();
231: literal.setValue("1");
232:
233: ContainsFunctionExpression startsWith = new ContainsFunctionExpression();
234: startsWith.setArguments(new Expression[] { stepExpression,
235: literal });
236:
237: result.setExpression(startsWith);
238: Query q = new Query();
239: q.setReturnClause(Collections.singletonList(result));
240:
241: v.buildQuery(q);
242: System.out.println(v.getSqlForPropertyIds());
243: System.out.println(v.getParameters());
244: assertTrue(v.getParameters().get(0).equals("%1%"));
245: }
246:
247: public void testBug0001() throws Exception {
248: /*
249: * The following query triggers an error, becouse the URI table is not
250: * being included in the WHERE clause. Found on 10-Jan-2004 NOTE:This
251: * also triggers a 2nd bug, where the join condition for DL.propId =
252: * DL1wlPropIds is missing.
253: */
254:
255: System.out.println("\n--- BUG 0001 ---");
256:
257: String queryString = "return /URI/desktop/defaultPage";
258: System.out.println(queryString);
259:
260: QueryParser parser = new QueryParser();
261: Query query = parser.parse(queryString);
262: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
263: v.buildQuery(query);
264:
265: System.out.println(v.getSqlForPropertyIds());
266: System.out.println(v.getParameters() + "\n");
267:
268: Connection c = null;
269: Statement st = null;
270: try {
271: c = ConnectionPoolAdapterSingleton.getInstance()
272: .getConnection();
273: st = c.createStatement();
274: st.executeQuery(v.getSqlForPropertyIds());
275: } finally {
276: SQLOperations.closeStatement(st);
277: SQLOperations.closeConnection(c);
278: }
279: }
280:
281: public void testServicesByName() throws Exception {
282: System.out.println("\n--- Services by name ---");
283:
284: String queryString = "return /URI/desktop/defaultPage/services/stockNews";
285: System.out.println(queryString);
286:
287: QueryParser parser = new QueryParser();
288: Query query = parser.parse(queryString);
289: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
290: v.buildQuery(query);
291:
292: System.out.println(v.getSqlForPropertyIds());
293: System.out.println(v.getParameters() + "\n");
294:
295: /* TODO: Check something. */
296: Connection c = null;
297: PreparedStatement st = null;
298: try {
299: c = ConnectionPoolAdapterSingleton.getInstance()
300: .getConnection();
301: st = c.prepareStatement(v.getSqlForPropertyIds());
302: Iterator it = v.getParameters().iterator();
303: int i = 1;
304: while (it.hasNext()) {
305: st.setObject(i++, it.next());
306: }
307: st.executeQuery();
308: } finally {
309: SQLOperations.closeStatement(st);
310: SQLOperations.closeConnection(c);
311: }
312: }
313:
314: /**
315: * Checks queries accessing URI multivalued simple properties.
316: *
317: * @throws Exception on unexpected errors.
318: */
319: public void testURIMultivalued() throws Exception {
320: System.out.println("\n--- URI Multivalued ---");
321:
322: String queryString = "return /URI[interests = 'Basket']";
323: System.out.println(queryString);
324:
325: QueryParser parser = new QueryParser();
326: Query query = parser.parse(queryString);
327: SelectIdsMetaPropertyVisitor v = new SelectIdsMetaPropertyVisitor();
328: v.buildQuery(query);
329:
330: System.out.println(v.getSqlForPropertyIds());
331: System.out.println(v.getParameters() + "\n");
332:
333: /* TODO: Check something. */
334: Connection c = null;
335: PreparedStatement st = null;
336: try {
337: c = ConnectionPoolAdapterSingleton.getInstance()
338: .getConnection();
339: st = c.prepareStatement(v.getSqlForPropertyIds());
340: Iterator it = v.getParameters().iterator();
341: int i = 1;
342: while (it.hasNext()) {
343: st.setObject(i++, it.next());
344: }
345: st.executeQuery();
346: } finally {
347: SQLOperations.closeStatement(st);
348: SQLOperations.closeConnection(c);
349: }
350: }
351:
352: }
|