001: package com.flexive.tests.shared;
002:
003: import org.testng.annotations.Test;
004: import org.apache.commons.lang.StringUtils;
005: import com.flexive.sqlParser.*;
006: import com.flexive.shared.search.query.PropertyValueComparator;
007: import com.flexive.shared.search.query.VersionFilter;
008: import com.flexive.shared.FxFormatUtils;
009: import com.flexive.shared.content.FxPK;
010: import com.flexive.shared.structure.FxSelectListItem;
011: import com.flexive.shared.structure.FxSelectListEdit;
012: import com.flexive.shared.structure.FxSelectList;
013: import com.flexive.shared.value.*;
014:
015: import java.util.Date;
016: import java.util.Arrays;
017:
018: /**
019: * Tests for the FxSQL parser. Checks various syntax features, but does not
020: * execute real queries.
021: *
022: * @author Daniel Lichtenberger, UCS
023: * @version $Rev$
024: */
025: public class SqlParserTest {
026:
027: @Test(groups={"shared","search"})
028: public void emptyWhereClause() throws SqlParserException {
029: parse("SELECT co.id FROM content co", new String[] { "co.id" });
030: try {
031: parse("SELECT co.id FROM content co WHERE",
032: new String[] { "co.id" });
033: assert false : "WHERE specified, but no conditions - expected failure";
034: } catch (SqlParserException e) {
035: // pass
036: }
037: }
038:
039: @Test(groups={"shared","search"})
040: public void contentTypeFilter() throws SqlParserException {
041: final FxStatement stmt1 = parse(
042: "SELECT co.id FROM content co FILTER co.TYPE=21",
043: new String[] { "co.id" });
044: assert stmt1.getContentTypeFilter().equals("21") : "Content type filter was "
045: + stmt1.getContentTypeFilter() + ", expected: 21";
046:
047: final FxStatement stmt2 = parse(
048: "SELECT co.id FROM content co FILTER co.TYPE=mytype",
049: new String[] { "co.id" });
050: assert stmt2.getContentTypeFilter().equalsIgnoreCase("mytype") : "Content type filter was "
051: + stmt2.getContentTypeFilter() + ", expected: mytype";
052: }
053:
054: @Test(groups={"shared","search"})
055: public void versionFilter() throws SqlParserException {
056: assert parse(
057: "SELECT co.id FROM content co FILTER co.VERSION=max")
058: .getVersionFilter().equals(VersionFilter.MAX);
059: assert parse(
060: "SELECT co.id FROM content co FILTER co.VERSION=LIVE")
061: .getVersionFilter().equals(VersionFilter.LIVE);
062: assert parse(
063: "SELECT co.id FROM content co FILTER co.VERSION=ALL")
064: .getVersionFilter().equals(VersionFilter.ALL);
065: // auto gets the version through some user session magic, but it should definitely not return auto
066: assert !parse(
067: "SELECT co.id FROM content co FILTER co.VERSION=AUTO")
068: .getVersionFilter().equals(VersionFilter.AUTO);
069: try {
070: parse("SELECT co.id FROM content co FILTER co.VERSION=15");
071: assert false : "Specific versions cannot be selected.";
072: } catch (SqlParserException e) {
073: // pass
074: }
075: }
076:
077: @Test(groups={"shared","search"})
078: public void ignoreCaseFilter() throws SqlParserException {
079: assert parse(
080: "SELECT co.id FROM content co FILTER IGNORE_CASE=T")
081: .getIgnoreCase();
082: assert parse(
083: "SELECT co.id FROM content co FILTER IGNORE_CASE=t")
084: .getIgnoreCase();
085: assert parse(
086: "SELECT co.id FROM content co FILTER IGNORE_CASE=true")
087: .getIgnoreCase();
088: assert !parse(
089: "SELECT co.id FROM content co FILTER IGNORE_CASE=F")
090: .getIgnoreCase();
091: assert !parse(
092: "SELECT co.id FROM content co FILTER IGNORE_CASE=f")
093: .getIgnoreCase();
094: assert !parse(
095: "SELECT co.id FROM content co FILTER IGNORE_CASE=false")
096: .getIgnoreCase();
097: }
098:
099: @Test(groups={"shared","search"})
100: public void maxResultRowsFilter() throws SqlParserException {
101: assert parse(
102: "SELECT co.id FROM content co FILTER MAX_RESULTROWS=21")
103: .getMaxResultRows() == 21;
104: assert parse(
105: "SELECT co.id FROM content co FILTER MAX_RESULTROWS=0")
106: .getMaxResultRows() == 0;
107: try {
108: parse("SELECT co.id FROM content co FILTER MAX_RESULTROWS=-1");
109: assert false : "Negative values should not be allowed for filter value MAX_RESULTROWS.";
110: } catch (SqlParserException e) {
111: // pass
112: }
113: }
114:
115: @Test(groups={"shared","search"})
116: public void searchLanguagesFilter() throws SqlParserException {
117: assert parse("SELECT co.id FROM content co").getTableByAlias(
118: "co").getSearchLanguages().length == 0;
119: assert parse(
120: "SELECT co.id FROM content co FILTER co.SEARCH_LANGUAGES=de")
121: .getTableByAlias("co").getSearchLanguages()[0]
122: .equals("de");
123: assert parse(
124: "SELECT co.id FROM content co FILTER co.SEARCH_LANGUAGES=de|en")
125: .getTableByAlias("co").getSearchLanguages()[0]
126: .equals("de");
127: assert parse(
128: "SELECT co.id FROM content co FILTER co.SEARCH_LANGUAGES=de|en")
129: .getTableByAlias("co").getSearchLanguages()[1]
130: .equals("en");
131: try {
132: parse("SELECT co.id FROM content co FILTER SEARCH_LANGUAGES=de");
133: assert false : "Filter SEARCH_LANGUAGES illegally specified without a table alias.";
134: } catch (SqlParserException e) {
135: // pass
136: }
137: }
138:
139: @Test(groups={"shared","search"})
140: public void briefcaseFilter() throws SqlParserException {
141: assert parse("SELECT co.id FROM content co")
142: .getBriefcaseFilter().length == 0;
143: assert parse("SELECT co.id FROM content co FILTER briefcase=1")
144: .getBriefcaseFilter()[0] == 1;
145: assert parse(
146: "SELECT co.id FROM content co FILTER briefcase=1|21")
147: .getBriefcaseFilter()[0] == 1;
148: assert parse(
149: "SELECT co.id FROM content co FILTER briefcase=1|21")
150: .getBriefcaseFilter()[1] == 21;
151: }
152:
153: @Test(groups={"shared","search"})
154: public void combinedFilters() throws SqlParserException {
155: final FxStatement stmt = parse("SELECT co.id FROM content co \n"
156: + "FILTER IGNORE_CASE=false, max_resultrows=21, co.SEARCH_LANGUAGES=fr|it, briefcase=2|3,\n"
157: + " co.version=max, co.type=mine\n");
158: assert !stmt.getIgnoreCase();
159: assert stmt.getMaxResultRows() == 21;
160: assert stmt.getTableByAlias("co").getSearchLanguages()[0]
161: .equals("fr");
162: assert stmt.getTableByAlias("co").getSearchLanguages()[1]
163: .equals("it");
164: assert stmt.getBriefcaseFilter()[0] == 2;
165: assert stmt.getBriefcaseFilter()[1] == 3;
166: }
167:
168: @Test(groups={"shared","search"})
169: public void basicConditionComparators() throws SqlParserException {
170: for (PropertyValueComparator comp : PropertyValueComparator
171: .values()) {
172: final String query = "SELECT co.id FROM content co WHERE "
173: + comp.getSql("co.property", "myvalue");
174: try {
175: parse(query);
176: } catch (SqlParserException e) {
177: assert false : "Failed to submit query with comparator "
178: + comp
179: + ":\n"
180: + query
181: + "\n\nError message: "
182: + e.getMessage();
183: }
184: }
185: }
186:
187: @Test(groups={"shared","search"})
188: public void nestedConditionComparators() {
189: for (PropertyValueComparator comp : PropertyValueComparator
190: .values()) {
191: final Date date = new Date();
192: final String query = "SELECT co.id FROM content co WHERE "
193: + comp.getSql("co.p1", 1) + " AND ("
194: + comp.getSql("co.p2", "stringval") + " OR ("
195: + comp.getSql("co.p3", 2) + " AND "
196: + comp.getSql("co.p4", date) + "))";
197: try {
198: final FxStatement stmt = parse(query);
199:
200: // check root expression
201: final Brace root = stmt.getRootBrace();
202: assert root.isAnd() : "Root expression should be AND";
203: assert root.getElements().length == 2 : "Root should have two children, has: "
204: + Arrays.asList(root.getElements());
205: checkStatementCondition(root.getElementAt(0), comp,
206: "co.p1", "1");
207:
208: // check first nested level
209: final Brace level1 = (Brace) root.getElementAt(1);
210: assert level1.isOr() : "Level 1 expression should be 'or'";
211: assert level1.getElements().length == 2 : "Level1 should have two children, has: "
212: + Arrays.asList(level1.getElements());
213: checkStatementCondition(level1.getElementAt(0), comp,
214: "co.p2", FxFormatUtils
215: .escapeForSql("stringval"));
216:
217: // check innermost level
218: final Brace level2 = (Brace) level1.getElementAt(1);
219: assert level2.isAnd() : "Level 2 expression should be 'and'";
220: assert level2.getElements().length == 2 : "Level2 should have two children, has: "
221: + Arrays.asList(level2.getElements());
222: checkStatementCondition(level2.getElementAt(0), comp,
223: "co.p3", "2");
224: checkStatementCondition(level2.getElementAt(1), comp,
225: "co.p4", FxFormatUtils.escapeForSql(date));
226: } catch (Exception e) {
227: assert false : "Failed to submit query with comparator "
228: + comp
229: + ":\n"
230: + query
231: + "\n\nError message: "
232: + e.getMessage();
233: }
234: }
235: }
236:
237: @Test(groups={"shared","search"})
238: public void dataTypeSupport() throws SqlParserException {
239: final FxSelectListEdit selectList = new FxSelectList("test")
240: .asEditable();
241: final FxSelectListItem item1 = new FxSelectListItem(25,
242: selectList, -1, new FxString("label1"));
243: final FxSelectListItem item2 = new FxSelectListItem(28,
244: selectList, -1, new FxString("label2"));
245: final FxValue[] testData = {
246: new FxString("som'e test string"),
247: new FxHTML("<h1>\"HTML c'aption\"</h1>"),
248: new FxFloat(1.21f),
249: new FxDouble(1.21),
250: new FxDate(new Date()),
251: new FxSelectOne(item1),
252: new FxSelectMany(new SelectMany(selectList).select(
253: item1.getId()).select(item2.getId())),
254: new FxBoolean(false), new FxBoolean(true),
255: new FxLargeNumber(2741824312312L), new FxNumber(21),
256: new FxReference(new ReferencedContent(21, FxPK.MAX)),
257: new FxReference(new ReferencedContent(21, FxPK.LIVE)),
258: new FxReference(new ReferencedContent(21, 4)) };
259: for (FxValue value : testData) {
260: final FxStatement stmt = parse("SELECT co.id FROM content co WHERE co.value = "
261: + FxFormatUtils.escapeForSql(value));
262: final Object conditionValue = ((Condition) stmt
263: .getRootBrace().getElementAt(0)).getRValueInfo()
264: .getValue();
265: assert StringUtils.isNotBlank(value.getSqlValue());
266: assert conditionValue.equals(value.getSqlValue()) : "SQL condition value should be "
267: + value.getSqlValue() + ", is: " + conditionValue;
268: }
269: }
270:
271: @Test(groups={"shared","search"})
272: public void selectFunctions() throws SqlParserException {
273: assert parse("SELECT min(co.id) FROM content co")
274: .getSelectedValues().get(0).getValue().getFunctions()[0]
275: .equals("min");
276: final Value val1 = parse(
277: "SELECT co.id, min(max(avg(co.id))) FROM content co")
278: .getSelectedValues().get(1).getValue();
279: assert Arrays.equals(val1.getFunctions(), new String[] { "min",
280: "max", "avg" }) : "Expected functions min, max, avg; got: "
281: + Arrays.asList(val1.getFunctions());
282: }
283:
284: @Test(groups={"shared","search"})
285: public void orderBy() throws SqlParserException {
286: for (String valid : new String[] {
287: "SELECT co.id FROM content co ORDER BY co.id, 1",
288: "SELECT co.id FROM content co ORDER BY co.id ASC",
289: "SELECT co.id FROM content co ORDER BY co.id",
290: "SELECT co.id FROM content co ORDER BY 1",
291: "SELECT co.id FROM content co ORDER BY 1 ASC, 1 DESC",
292: "SELECT co.id FROM content co ORDER BY 1 DESC, 1 DESC" }) {
293: final FxStatement stmt = parse(valid);
294: assert stmt.getOrderByValues().get(0).getColumnIndex() == 0 : "Order by column index should be 0, was: "
295: + stmt.getOrderByValues().get(0).getColumnIndex();
296: assert stmt.getOrderByValues().get(0).getSelectedValue() != null;
297: assert stmt.getOrderByValues().get(0).getSelectedValue()
298: .equals(stmt.getSelectedValues().get(0));
299: }
300: // some invalid queries
301: for (String invalid : new String[] {
302: "SELECT co.id FROM content co ORDER BY co.id2", // order by value not selected
303: "SELECT co.id, co.ver FROM content co ORDER BY 3",
304: "SELECT co.id, co.ver FROM content co ORDER BY co.1",
305: "SELECT co.id, co.ver FROM content co ORDER BY co.id DESCC", }) {
306: try {
307: parse(invalid);
308: assert false : "Query " + invalid + " is invalid.";
309: } catch (SqlParserException e) {
310: // pass
311: }
312: }
313: }
314:
315: @Test(groups={"shared","search"})
316: public void groupBy() throws SqlParserException {
317: for (String valid : new String[] {
318: "SELECT co.id FROM content co GROUP BY co.id",
319: "SELECT co.id FROM content co GROUP BY co.id, co.id",
320: "SELECT co.id FROM content co GROUP BY 1", }) {
321: parse(valid);
322: // TODO: add checks, since group by are recognized by the parser, but not evaluated
323: }
324:
325: /*for (String invalid : new String[] {
326: "SELECT co.id FROM content co GROUP BY co.id2",
327: "SELECT co.id FROM content co GROUP BY 2",
328: }) {
329: try {
330: parse(invalid);
331: assert false : "Query " + invalid + " is invalid.";
332: } catch (SqlParserException e) {
333: // pass
334: }
335: }*/
336: }
337:
338: @Test(groups={"shared","search"})
339: public void queryComments() throws SqlParserException {
340: parse("SELECT co.ID /* some comment */ FROM content -- line-comment\nco");
341: parse("SELECT co.ID /* some \n multiline -- nested \n comment */ FROM content co");
342: parse("SELECT co.ID FROM content co WHERE co.property /* some comment */ = /* some comment */ 21");
343: parse("SELECT /* some \ncomment */ co.ID FROM /* some \n\ncomment */ content co -- another comment");
344: }
345:
346: private void checkStatementCondition(BraceElement element,
347: PropertyValueComparator comp, String lvalue, String rvalue) {
348: assert element instanceof Condition : "First root child should be a condition, is: "
349: + element;
350: final Condition condition = (Condition) element;
351: assert condition.getLValueInfo().getValue().equals(lvalue);
352: assert !comp.isNeedsInput()
353: || !condition.getRValueInfo().isNull();
354: assert !comp.isNeedsInput()
355: || condition.getRValueInfo().getValue().equals(rvalue) : "RValue should be "
356: + rvalue
357: + ", is: "
358: + condition.getRValueInfo().getValue();
359: assert !comp.isNeedsInput()
360: || condition.getConstant().getValue().equals(rvalue);
361: }
362:
363: /**
364: * Parses the given query, performs basic validity checks based on the additional parameters
365: * like selected columns, and returns the parsed statement.
366: *
367: * @param query the FxSQL query
368: * @param selectedColumns the selected columns, the returned statement will be checked to contain these columns
369: * @return the parsed FxSQL statement
370: * @throws com.flexive.sqlParser.SqlParserException
371: * on parser errors
372: */
373: private FxStatement parse(String query, String[] selectedColumns)
374: throws SqlParserException {
375: final FxStatement stmt2 = FxStatement.parseSql(query);
376: checkStatement(stmt2, selectedColumns);
377: return stmt2;
378: }
379:
380: /**
381: * Parses the given query, performs basic validity checks and returns the parsed statement.
382: *
383: * @param query the FxSQL query
384: * @return the parsed FxSQL statement
385: * @throws SqlParserException on parser errors
386: */
387: private FxStatement parse(String query) throws SqlParserException {
388: return parse(query, null);
389: }
390:
391: /**
392: * Perform basic validity checks of common queries.
393: *
394: * @param statement the statement to be checked
395: * @param selectedColumns the selected column(s). If null, the corresponding tests will be skipped.
396: * @return the statement
397: */
398: private FxStatement checkStatement(FxStatement statement,
399: String[] selectedColumns) {
400: assert statement.getTables().length == 1 : "One table should be selected, got: "
401: + statement.getTables().length;
402: assert statement.getTableByType(Table.TYPE.CONTENT) != null : "No content table selected";
403: assert statement.getParserExecutionTime() >= 0 : "Parser execution time not set.";
404: assert statement.getTableByAlias("co") != null;
405: assert statement.getTableByAlias("co").getType().equals(
406: Table.TYPE.CONTENT);
407: if (selectedColumns != null) {
408: for (int i = 0; i < statement.getSelectedValues().size(); i++) {
409: final SelectedValue value = statement
410: .getSelectedValues().get(i);
411: assert value.getAlias().equals(selectedColumns[i]) : "Unexpected column selected: "
412: + value;
413: }
414: }
415: return statement;
416: }
417: }
|