001: /***************************************************************
002: * This file is part of the [fleXive](R) project.
003: *
004: * Copyright (c) 1999-2008
005: * UCS - unique computing solutions gmbh (http://www.ucs.at)
006: * All rights reserved
007: *
008: * The [fleXive](R) project is free software; you can redistribute
009: * it and/or modify it under the terms of the GNU General Public
010: * License as published by the Free Software Foundation;
011: * either version 2 of the License, or (at your option) any
012: * later version.
013: *
014: * The GNU General Public License can be found at
015: * http://www.gnu.org/copyleft/gpl.html.
016: * A copy is found in the textfile GPL.txt and important notices to the
017: * license from the author are found in LICENSE.txt distributed with
018: * these libraries.
019: *
020: * This library is distributed in the hope that it will be useful,
021: * but WITHOUT ANY WARRANTY; without even the implied warranty of
022: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
023: * GNU General Public License for more details.
024: *
025: * For further information about UCS - unique computing solutions gmbh,
026: * please see the company website: http://www.ucs.at
027: *
028: * For further information about [fleXive](R), please see the
029: * project website: http://www.flexive.org
030: *
031: *
032: * This copyright notice MUST APPEAR in all copies of the file!
033: ***************************************************************/package com.flexive.tests.shared;
034:
035: import com.flexive.shared.exceptions.FxRuntimeException;
036: import com.flexive.shared.search.SortDirection;
037: import com.flexive.shared.search.Table;
038: import com.flexive.shared.search.query.AssignmentValueNode;
039: import com.flexive.shared.search.query.PropertyValueComparator;
040: import com.flexive.shared.search.query.QueryOperatorNode.Operator;
041: import com.flexive.shared.search.query.SqlQueryBuilder;
042: import com.flexive.shared.structure.FxDataType;
043: import com.flexive.shared.value.FxDate;
044: import com.flexive.shared.value.FxString;
045: import com.flexive.tests.shared.QueryNodeTreeTests.AssignmentNodeGenerator;
046: import org.testng.Assert;
047: import org.testng.annotations.Test;
048:
049: import java.util.Date;
050: import java.util.Formatter;
051:
052: /**
053: * Tests for the SqlQueryBuilder class.
054: *
055: * @author Daniel Lichtenberger (daniel.lichtenberger@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
056: * @see com.flexive.shared.search.query.SqlQueryBuilder
057: */
058: @Test(groups="shared")
059: public class SqlQueryBuilderTest {
060: /**
061: * A very basic query test with one condition.
062: */
063: @Test
064: public void simpleQuery() {
065: AssignmentNodeGenerator generator = new AssignmentNodeGenerator(
066: FxDataType.String1024, new FxString("test"));
067: AssignmentValueNode node = generator.createNode(0);
068: assert node.getValue().getDefaultTranslation().equals("test");
069: node.setComparator(PropertyValueComparator.EQ);
070:
071: SqlQueryBuilder builder = new SqlQueryBuilder().andSub();
072: buildAssignmentNode(builder, node);
073: final String expected = ("(" + Table.CONTENT.getAlias() + ".#"
074: + generator.getAssignment().getId() + " = 'test')");
075: assertConditions(builder, expected);
076: }
077:
078: /**
079: * A query with several expressions, but without nested conditions.
080: */
081: @Test
082: public void oneLevelQuery() {
083: AssignmentNodeGenerator generator = new AssignmentNodeGenerator(
084: FxDataType.String1024, new FxString("test"));
085: AssignmentValueNode node = generator.createNode(0);
086: AssignmentValueNode node2 = generator.createNode(1);
087: node2.getValue().setDefaultTranslation("value2");
088: AssignmentValueNode node3 = generator.createNode(2);
089: node3.getValue().setDefaultTranslation("value3");
090:
091: SqlQueryBuilder builder = new SqlQueryBuilder();
092: builder.enterSub(Operator.OR);
093: buildAssignmentNode(builder, node);
094: buildAssignmentNode(builder, node2);
095: buildAssignmentNode(builder, node3);
096: builder.closeSub();
097:
098: final String alias = Table.CONTENT.getAlias();
099: final String expected = new Formatter()
100: .format(
101: "(%1$s = 'test' OR %1$s = 'value2' OR %1$s = 'value3')",
102: alias + ".#"
103: + generator.getAssignment().getId())
104: .toString();
105: assertConditions(builder, expected);
106: }
107:
108: /*
109: * A query with a nested condition.
110: */
111: @Test
112: public void twoLevelQuery() {
113: AssignmentNodeGenerator generator = new AssignmentNodeGenerator(
114: FxDataType.String1024, new FxString("test"));
115: AssignmentValueNode node = generator.createNode(0);
116: AssignmentValueNode node2 = generator.createNode(1);
117: node2.getValue().setDefaultTranslation("value2");
118: AssignmentValueNode node3 = generator.createNode(2);
119: node3.getValue().setDefaultTranslation("value3");
120:
121: SqlQueryBuilder builder = new SqlQueryBuilder();
122: builder.enterSub(Operator.AND);
123: buildAssignmentNode(builder, node);
124: builder.enterSub(Operator.OR);
125: buildAssignmentNode(builder, node2);
126: buildAssignmentNode(builder, node3);
127: builder.closeSub();
128: buildAssignmentNode(builder, node3);
129: builder.closeSub();
130:
131: final String alias = Table.CONTENT.getAlias();
132: final String expected = new Formatter().format(
133: "(%1$s = 'test' AND (%1$s = 'value2' OR "
134: + "%1$s = 'value3') AND %1$s = 'value3')",
135: alias + ".#" + generator.getAssignment().getId())
136: .toString();
137: assertConditions(builder, expected);
138: }
139:
140: @Test
141: public void twoLevelQuery2() {
142: SqlQueryBuilder builder = new SqlQueryBuilder();
143: builder.enterSub(Operator.AND).enterSub(Operator.OR).condition(
144: "title", PropertyValueComparator.LIKE, "abc")
145: .condition("caption", PropertyValueComparator.LIKE,
146: "def").closeSub().condition("title",
147: PropertyValueComparator.NOT_EMPTY, null);
148: final String expected = "((co.title LIKE 'abc' OR co.caption LIKE 'def') AND co.title IS NOT NULL)";
149: assertConditions(builder, expected);
150: }
151:
152: /**
153: * Test the text-only conditions builder
154: */
155: @Test
156: public void textBuilderQuery() {
157: SqlQueryBuilder builder = new SqlQueryBuilder();
158: builder.enterSub(Operator.AND).condition("caption",
159: PropertyValueComparator.LIKE, new FxString("test"))
160: .condition("date", PropertyValueComparator.EQ,
161: new FxDate(new Date(0))).closeSub();
162: final String alias = Table.CONTENT.getAlias();
163: final String expected = "(" + alias
164: + ".caption LIKE 'test' AND " + alias
165: + ".date = '1970-01-01')";
166: assertConditions(builder, expected);
167: }
168:
169: @Test
170: public void getColumnNames() {
171: SqlQueryBuilder builder = new SqlQueryBuilder();
172: assert !builder.getColumnNames().isEmpty() : "Default column must not be empty";
173: assert builder.getColumnNames().contains("@pk") : "Primary key must be selected by default.";
174: }
175:
176: private void assertConditions(SqlQueryBuilder builder,
177: final String expected) {
178: String conditions = builder.getConditions();
179: final String commentsPattern = "/\\*.+?\\*/\\s*";
180: conditions = conditions.replaceAll(commentsPattern, ""); // strip comments
181: assert expected.equals(conditions) : "Invalid conditions: "
182: + conditions + ", expected: " + expected;
183: assert builder.getQuery().replaceAll(commentsPattern, "")
184: .indexOf(expected) > 0 : "Query must contain conditions: "
185: + expected
186: + ", generated getResult:\n"
187: + builder.getQuery();
188: }
189:
190: private void buildAssignmentNode(SqlQueryBuilder builder,
191: AssignmentValueNode node) {
192: builder.condition(node.getAssignment(), node.getComparator(),
193: node.getValue());
194: }
195:
196: @Test
197: public void propertyNameQuery() {
198: new SqlQueryBuilder().enterSub(Operator.AND).condition("co.*",
199: PropertyValueComparator.EQ, "'test'").closeSub()
200: .getQuery();
201: }
202:
203: @Test
204: public void childQuery() {
205: assertConditions(new SqlQueryBuilder().isChild(25),
206: "(IS CHILD OF 25)");
207: assertConditions(new SqlQueryBuilder().isChild(25)
208: .isDirectChild(26),
209: "(IS CHILD OF 25 AND IS DIRECT CHILD OF 26)");
210: }
211:
212: @Test
213: public void emptyQuery() {
214: new SqlQueryBuilder().getQuery(); // ok
215: new SqlQueryBuilder().enterSub(Operator.AND).closeSub()
216: .getQuery(); // ok
217: }
218:
219: /**
220: * Checks if calls to {@link com.flexive.shared.search.query.SqlQueryBuilder#getQuery()}
221: * and {@link com.flexive.shared.search.query.SqlQueryBuilder#getConditions()}
222: * can be executed multiple times without changing the result.
223: */
224: @Test
225: public void repeatableQueryTest() {
226: final SqlQueryBuilder builder = new SqlQueryBuilder()
227: .condition("caption", PropertyValueComparator.LIKE,
228: "test%");
229: final String query = builder.getQuery();
230: final String query2 = builder.getQuery();
231: assert query.equals(query2) : "Second call to getQuery() returned a different "
232: + "result than the first one:\n"
233: + "[1]: "
234: + query
235: + "\n[2]: " + query2;
236: }
237:
238: @Test
239: public void copyQueryBuilderConditionTest() {
240: final SqlQueryBuilder builder = new SqlQueryBuilder()
241: .condition("title", PropertyValueComparator.LIKE,
242: "test%");
243: final SqlQueryBuilder builder2 = new SqlQueryBuilder(builder);
244: builder2.condition("title", PropertyValueComparator.LIKE,
245: "123%");
246: assert !builder.getQuery().equals(builder2.getQuery()) : "Queries should not be equal - copy constructor not implemented correctly";
247: }
248:
249: @Test
250: public void copyQueryBuilderSelectTest() {
251: final SqlQueryBuilder builder = new SqlQueryBuilder().select(
252: "@pk").condition("title", PropertyValueComparator.LIKE,
253: "test%");
254: final SqlQueryBuilder builder2 = new SqlQueryBuilder(builder)
255: .select("@pk", "version");
256: assert !builder.getQuery().equals(builder2.getQuery()) : "Queries should not be equal";
257: assert !builder.getQuery().contains("co.@pk, co.version");
258: assert builder2.getQuery().contains("co.@pk, co.version");
259: }
260:
261: @Test
262: public void filterTypeTest() {
263: final SqlQueryBuilder builder = new SqlQueryBuilder().select(
264: "@pk").filterType("MYTYPE");
265: Assert.assertEquals(builder.getFilters(), "co.TYPE=MYTYPE");
266: assert builder.getQuery().contains("FILTER co.TYPE=MYTYPE") : "Filter not contained in getResult: "
267: + builder.getQuery();
268:
269: final SqlQueryBuilder builder2 = new SqlQueryBuilder().select(
270: "@pk").filterType(21);
271: Assert.assertEquals(builder2.getFilters(), "co.TYPE=21");
272: assert builder2.getQuery().contains("FILTER co.TYPE=21") : "Filter not contained in getResult: "
273: + builder2.getQuery();
274: // remove filter
275: builder2.filterType(-1);
276: Assert.assertEquals(builder2.getFilters(), "");
277: assert !builder2.getQuery().contains("FILTER co.TYPE=21") : "Filter still present in getResult: "
278: + builder2.getQuery();
279: }
280:
281: @Test
282: public void illegalImplicitScopeTest() {
283: try {
284: final SqlQueryBuilder builder = new SqlQueryBuilder()
285: .orSub().isChild(1).closeSub().andSub().isChild(5);
286: assert false : "Query builder assembled illegal getResult: "
287: + builder.getQuery();
288: } catch (FxRuntimeException e) {
289: // pass
290: }
291: try {
292: final SqlQueryBuilder builder2 = new SqlQueryBuilder()
293: .orSub().isChild(1).closeSub().isChild(5);
294: assert false : "Query builder assembled illegal getResult: "
295: + builder2.getQuery();
296: } catch (FxRuntimeException e) {
297: // pass
298: }
299: }
300:
301: @Test
302: public void orderByTest() {
303: final SqlQueryBuilder builder = new SqlQueryBuilder().select(
304: "property", "anotherProperty").orderBy("property",
305: SortDirection.ASCENDING);
306: assert builder.getQuery().contains("ORDER BY co.property") : "Order by not contained in getResult: "
307: + builder.getQuery();
308: builder.orderBy("anotherProperty", SortDirection.DESCENDING);
309: assert !builder.getQuery().contains("ORDER BY co.property") : "Old order by should be removed: "
310: + builder.getQuery();
311: assert builder.getQuery().contains(
312: "ORDER BY co.anotherProperty DESC") : "Order by not contained in getResult: "
313: + builder.getQuery();
314: builder.orderBy(1, SortDirection.ASCENDING);
315: builder.orderBy(2, SortDirection.DESCENDING);
316: try {
317: builder.orderBy(3, SortDirection.ASCENDING);
318: assert false : "Column 2 doesn't exist";
319: } catch (Exception e) {
320: // pass
321: }
322: }
323:
324: @Test
325: public void selectAssignmentTest() {
326: final SqlQueryBuilder builder = new SqlQueryBuilder()
327: .select("mycontent/mycaption");
328: assert builder.getQuery().contains(
329: "SELECT co.mycontent/mycaption") : "Expected assignment query: "
330: + builder.getQuery();
331: }
332: }
|