1 /*************************************************************************
2  *
3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4  *
5  * Copyright 2000, 2010 Oracle and/or its affiliates.
6  *
7  * OpenOffice.org - a multi-platform office productivity suite
8  *
9  * This file is part of OpenOffice.org.
10  *
11  * OpenOffice.org is free software: you can redistribute it and/or modify
12  * it under the terms of the GNU Lesser General Public License version 3
13  * only, as published by the Free Software Foundation.
14  *
15  * OpenOffice.org is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18  * GNU Lesser General Public License version 3 for more details
19  * (a copy is included in the LICENSE file that accompanied this code).
20  *
21  * You should have received a copy of the GNU Lesser General Public License
22  * version 3 along with OpenOffice.org.  If not, see
23  * <http://www.openoffice.org/license.html>
24  * for a copy of the LGPLv3 License.
25  *
26  ************************************************************************/
27 
28 package ifc.sdb;
29 
30 import com.sun.star.sdb.XSingleSelectQueryComposer;
31 import lib.MultiMethodTest;
32 import com.sun.star.sdb.XSingleSelectQueryAnalyzer;
33 import com.sun.star.uno.AnyConverter;
34 import com.sun.star.uno.UnoRuntime;
35 import lib.StatusException;
36 import lib.Status;
37 import com.sun.star.beans.PropertyValue;
38 import com.sun.star.beans.XPropertySet;
39 import com.sun.star.sdb.SQLFilterOperator;
40 
41 /**
42 * Testing <code>com.sun.star.sdb.XSingleSelectQueryComposer</code>
43 * interface methods :
44 * <ul>
45 *  <li><code>setFilter()</code></li>
46 *  <li><code>setStructuredFilter()</code></li>
47 *  <li><code>appendFilterByColumn()</code></li>
48 *  <li><code>appendGroupByColumn()</code></li>
49 *  <li><code>setGroup()</code></li>
50 *  <li><code>setHavingClause()</code></li>
51 *  <li><code>setStructuredHavingClause()</code></li>
52 *  <li><code>appendHavingClauseByColumn()</code></li>
53 *  <li><code>appendOrderByColumn()</code></li>
54 *  <li><code>setOrder()</code></li>
55 
56 * </ul> <p>
57 * @see com.sun.star.sdb.XSingleSelectQueryComposer
58 */
59 public class _XSingleSelectQueryComposer extends MultiMethodTest {
60 
61     // oObj filled by MultiMethodTest
62     public XSingleSelectQueryComposer oObj = null ;
63 
64     private String queryString = "SELECT * FROM \"biblio\"";
65 
66     private XSingleSelectQueryAnalyzer xQueryAna = null;
67 
68     private XPropertySet xProp = null;
69 
70     private String colName = null;
71 
72     /**
73      * Retcieves the object relations:
74     * <ul>
75     *  <li><code>XSingleSelectQueryAnalyzer xQueryAna</code></li>
76     *  <li><code>XPropertySet xProp</code></li>
77     *  <li><code>String colName</code></li>
78     * </ul> <p>
79      * @see om.sun.star.sdb.XSingleSelectQueryAnalyzer
80      * @see com.sun.star.beans.XPropertySet
81      */
82     protected void before() /* throws Exception*/ {
83 
84         xQueryAna = (XSingleSelectQueryAnalyzer)
85                       UnoRuntime.queryInterface(XSingleSelectQueryAnalyzer.class,
86                       tEnv.getObjRelation("xQueryAna"));
87 
88         if (xQueryAna == null) {
89             throw new StatusException(Status.failed(
90            "Couldn't get object relation 'xQueryAna'. Test must be modified"));
91 
92         }
93 
94         xProp = (XPropertySet)
95                       UnoRuntime.queryInterface(XPropertySet.class,
96                       tEnv.getObjRelation("xProp"));
97 
98         if (xProp == null) {
99             throw new StatusException(Status.failed(
100            "Couldn't get object relation 'xProp'. Test must be modified"));
101 
102         }
103 
104         try
105         {
106             colName = AnyConverter.toString(tEnv.getObjRelation("colName"));
107         }
108         catch (com.sun.star.lang.IllegalArgumentException e)
109         {
110             colName = null;
111         }
112 
113         if (colName == null) {
114             throw new StatusException(Status.failed(
115            "Couldn't get object relation 'colName'. Test must be modified"));
116 
117         }
118 
119     }
120 
121 
122     /**
123     * Object relation <code>xQueryAna</code> set a filter. This filter
124     * must returned while calling <code>getFilter</code>
125     */
126     public void _setFilter() {
127         try{
128             String filter = "\"Identifier\" = 'BOR02b'";
129             oObj.setFilter(filter);
130             tRes.tested("setFilter()", (xQueryAna.getFilter().equals(filter)));
131 
132         } catch (com.sun.star.sdbc.SQLException e){
133             log.println("unexpected Exception: " + e.toString());
134             tRes.tested("setFilter()", false);
135         }
136     }
137 
138     /**
139     * Object relation <code>xQueryAna</code> set a complex filter with method
140     . <code>setFilter</code>. Then <code>getStructuredFilter</code> returns a
141     * sequenze of <code>PropertyValue</code> which was set with method
142     * <code>setStructuredFilter</code> from <code>xQueryAna</code>.
143     * Then test has ok status if <code>getFilter</code> returns the complex filter.
144     */
145     public void _setStructuredFilter() {
146         requiredMethod("setFilter()");
147         try{
148             xQueryAna.setQuery("SELECT \"Identifier\", \"Type\", \"Address\" FROM \"biblio\" \"biblio\"");
149             String complexFilter = "( \"Identifier\" = '1' AND \"Type\" = '4' ) OR ( \"Identifier\" = '2' AND \"Type\" = '5' ) OR ( \"Identifier\" = '3' AND \"Type\" = '6' AND \"Address\" = '7' ) OR ( \"Address\" = '8' ) OR ( \"Type\" = '9' )";
150             oObj.setFilter(complexFilter);
151             PropertyValue[][] aStructuredFilter = xQueryAna.getStructuredFilter();
152             oObj.setFilter("");
153             oObj.setStructuredFilter(aStructuredFilter);
154             tRes.tested("setStructuredFilter()", (xQueryAna.getFilter().equals(complexFilter)));
155 
156         } catch (com.sun.star.sdbc.SQLException e){
157             log.println("unexpected Exception: " + e.toString());
158             tRes.tested("setStructuredFilter()", false);
159         } catch (com.sun.star.lang.IllegalArgumentException e){
160             log.println("unexpected Exception: " + e.toString());
161             tRes.tested("setStructuredFilter()", false);
162         }
163     }
164 
165     /**
166     * At first the object relation <code>xProp</code> was set as parameter.
167     * Relation <code>xQueryAna</code> was used to chek if realtion
168     * <code>colName</code> was found.
169     * Second an empty <code>XPropertySet</code> was used as parameter. A
170     * <code>com.sun.star.sdbc.SQLException</code> must be thrown.
171     */
172     public void _appendFilterByColumn() {
173         boolean ok = true;
174         try{
175 
176             oObj.appendFilterByColumn(xProp, true,SQLFilterOperator.EQUAL);
177             log.println("appendFilterByColumn: " + xQueryAna.getFilter());
178             ok = ok && (xQueryAna.getFilter().indexOf(colName) > 0);
179 
180         } catch (com.sun.star.sdbc.SQLException e){
181             log.println("unexpected Exception: " + e.toString());
182             tRes.tested("appendFilterByColumn()", false);
183         }
184 
185         try{
186 
187             oObj.appendFilterByColumn(xProp, false,SQLFilterOperator.EQUAL);
188             log.println("appendFilterByColumn: " + xQueryAna.getFilter());
189             ok = ok && (xQueryAna.getFilter().indexOf(colName) > 0);
190 
191         } catch (com.sun.star.sdbc.SQLException e){
192             log.println("unexpected Exception: " + e.toString());
193             tRes.tested("appendFilterByColumn()", false);
194         }
195 
196         try{
197             XPropertySet dummy = null;
198             oObj.appendFilterByColumn(dummy, true,SQLFilterOperator.EQUAL);
199             log.println("expected Exception was not thrown");
200             tRes.tested("appendFilterByColumn()", false);
201 
202         } catch (com.sun.star.sdbc.SQLException e){
203             log.println("expected Exception");
204             ok = ok && true;
205         }
206         tRes.tested("appendFilterByColumn()", ok);
207     }
208 
209     /**
210     * At first the object relation <code>xProp</code> was used as parameter.
211     * Relation <code>xQueryAna</code> was used to chek if realtion
212     * <code>colName</code> was found.
213     * Second an empty <code>XPropertySet</code> was used as parameter. An
214     * <code>com.sun.star.sdbc.SQLException</code> must be thrown.
215     */
216     public void _appendGroupByColumn() {
217         boolean ok = true;
218         try{
219 
220             oObj.appendGroupByColumn(xProp);
221             log.println("appendGroupByColumn: " + xQueryAna.getFilter());
222             ok = ok && (xQueryAna.getFilter().indexOf(colName) > 0);
223 
224         } catch (com.sun.star.sdbc.SQLException e){
225             log.println("unexpected Exception: " + e.toString());
226             tRes.tested("appendGroupByColumn()", false);
227         }
228         try{
229             XPropertySet dummy = null;
230             oObj.appendGroupByColumn(dummy);
231             log.println("expected Exception was not thrown");
232             tRes.tested("appendGroupByColumn()", false);
233 
234         } catch (com.sun.star.sdbc.SQLException e){
235             log.println("expected Exception");
236             ok = ok && true;
237         }
238         tRes.tested("appendGroupByColumn()", ok);
239     }
240 
241     /**
242     * The group which was setted by <code>setGroup</code> must be returned
243     * while calling from object relation <code>XQueryAna</code>
244     * method <code>getGroup</code>
245     */
246     public void _setGroup() {
247         try{
248             String group = "\"Identifier\"";
249             oObj.setGroup(group);
250             tRes.tested("setGroup()", (xQueryAna.getGroup().equals(group)));
251 
252         } catch (com.sun.star.sdbc.SQLException e){
253             log.println("unexpected Exception: " + e.toString());
254             tRes.tested("setGroup()", false);
255         }
256     }
257 
258 
259     /**
260     * The cluase which was setted by <code>setHavingClause</code> must be returned
261     * while calling from object relation <code>XQueryAna</code>
262     * method <code>getHavingClause</code>
263     */
264     public void _setHavingClause() {
265         try{
266             String clause = "\"Identifier\" = 'BOR02b'";
267             oObj.setHavingClause(clause);
268             tRes.tested("setHavingClause()", (
269                                    xQueryAna.getHavingClause().equals(clause)));
270 
271         } catch (com.sun.star.sdbc.SQLException e){
272             log.println("unexpected Exception: " + e.toString());
273             tRes.tested("setHavingClause()", false);
274         }
275     }
276 
277     /**
278     * At first <code>setHavingClause</code> sets a complex clause.
279     * Then method <code>getStructuredHavingClause</code> from object relation
280     * <code>xQueryAna</code> returns a valid <code>PropertyValue[][]</code>
281     * Method <code>setHavingClause</code> was called with an empty sting to
282     * reset filter. Now <code>setStructuredHavingClause</code> with the valid
283     * <code>PropertyValue[][]</code> as parameter was called.
284     * Test is ok if <code>getHavingClause</code> from <code>xQueryAna</code>
285     * returns the complex clause from beginning.
286     * <p>
287     * required methods:
288     *<ul>
289     * <li><code>setHavingClause</code></li>
290     * <li><code>setStructuredFilter</code></li>
291     *</ul>
292     */
293     public void _setStructuredHavingClause() {
294         requiredMethod("setHavingClause()");
295         executeMethod("setStructuredFilter()");
296         String complexFilter = "( \"Identifier\" = '1' AND \"Type\" = '4' ) OR ( \"Identifier\" = '2' AND \"Type\" = '5' ) OR ( \"Identifier\" = '3' AND \"Type\" = '6' AND \"Address\" = '7' ) OR ( \"Address\" = '8' ) OR ( \"Type\" = '9' )";
297 
298         try{
299            oObj.setHavingClause(complexFilter);
300            PropertyValue[][] aStructuredHaving =
301                                           xQueryAna.getStructuredHavingClause();
302            oObj.setHavingClause("");
303            oObj.setStructuredHavingClause(aStructuredHaving);
304            tRes.tested("setStructuredHavingClause()",
305                            (xQueryAna.getHavingClause().equals(complexFilter)));
306 
307         } catch (com.sun.star.sdbc.SQLException e){
308             log.println("unexpected Exception: " + e.toString());
309             tRes.tested("setStructuredHavingClause()", false);
310         }
311     }
312 
313     /**
314     * First object relation <code>xProp</code> was used as parameter. Relation
315     * <code>xQueryAna</code> was used to chek if realtion <code>colName</code>
316     * was found.
317     * Second an empty <code>XPropertySet</code> was given as parameter. An
318     * <code>com.sun.star.sdbc.SQLException</code> must be thrown.
319     */
320     public void _appendHavingClauseByColumn() {
321         boolean ok = true;
322         try{
323 
324             oObj.appendHavingClauseByColumn(xProp, true,SQLFilterOperator.EQUAL);
325             log.println("appendHavingClauseByColumn: " + xQueryAna.getFilter());
326             ok = ok && (xQueryAna.getFilter().indexOf(colName) > 0);
327 
328         } catch (com.sun.star.sdbc.SQLException e){
329             log.println("unexpected Exception: " + e.toString());
330             tRes.tested("appendHavingClauseByColumn()", false);
331         }
332         try{
333             XPropertySet dummy = null;
334             oObj.appendHavingClauseByColumn(dummy, true,SQLFilterOperator.EQUAL);
335             log.println("expected Exception was not thrown");
336             tRes.tested("appendHavingClauseByColumn()", false);
337 
338         } catch (com.sun.star.sdbc.SQLException e){
339             log.println("expected Exception");
340             ok = ok && true;
341         }
342         tRes.tested("appendHavingClauseByColumn()", ok);
343     }
344 
345     /**
346     * First object relation <code>xProp</code> was set as parameter. Relation
347     * <code>xQueryAna</code> was used to chek if realtion <code>colName</code>
348     * was found.
349     * Second an empty <code>XPropertySet</code> was given as parameter. An
350     * <code>com.sun.star.sdbc.SQLException</code> must be thrown.
351     */
352     public void _appendOrderByColumn() {
353         boolean ok = true;
354         try{
355 
356             oObj.appendOrderByColumn(xProp, true);
357             log.println("appendOrderByColumn: " + xQueryAna.getFilter());
358             ok = ok && (xQueryAna.getFilter().indexOf(colName) > 0);
359 
360         } catch (com.sun.star.sdbc.SQLException e){
361             log.println("unexpected Exception: " + e.toString());
362             tRes.tested("appendOrderByColumn()", false);
363         }
364         try{
365             XPropertySet dummy = null;
366             oObj.appendOrderByColumn(dummy, true);
367             log.println("expected Exception was not thrown");
368             tRes.tested("appendOrderByColumn()", false);
369 
370         } catch (com.sun.star.sdbc.SQLException e){
371             log.println("expected Exception");
372             ok = ok && true;
373         }
374         tRes.tested("appendOrderByColumn()", ok);
375     }
376 
377 
378     /**
379     * Method <code>getOrder</code> from object relation <code>xQueryAna</code>
380     * checks the order which was setted while calling <code>setOrder</code>
381     */
382     public void _setOrder() {
383         try{
384             String order = "\"Identifier\"";
385             oObj.setOrder(order);
386             tRes.tested("setOrder()", (xQueryAna.getOrder().equals(order)));
387 
388         } catch (com.sun.star.sdbc.SQLException e){
389             log.println("unexpected Exception: " + e.toString());
390             tRes.tested("setOrder()", false);
391         }
392     }
393 
394 
395 
396 }  // finish class _XSingleSelectQueryComposer
397