UniverseUtil { public UniverseQuery convertQuery(String text, UniverseClient client, String token) throws UniverseException { StringBuilder select = new StringBuilder(); StringBuilder universe = new StringBuilder(); StringBuilder buf = new StringBuilder(); StringBuilder resultObj = new StringBuilder(); StringBuilder whereBuf = new StringBuilder(); UniverseInfo universeInfo = null; String where = null; boolean singleQuoteClosed = true; boolean pathClosed = true; boolean universePart = false; boolean selectPart = false; boolean wherePart = false; boolean listOperator = false; boolean operatorPosition = false; boolean duplicatedRows = true; Map<String, UniverseNodeInfo> nodeInfos = null; OptionalInt limit = OptionalInt.empty(); final int limitIndex = text.lastIndexOf("limit"); if (limitIndex != -1) { final String[] arguments = delimiter.delimit(text, 0).getArguments(); final int length = arguments.length; if (arguments[length - 3].equals("limit")) { limit = parseInt(arguments[length - 2]); } else if (arguments[length - 2].equals("limit")) { final String toParse = arguments[length - 1]; limit = parseInt(toParse.endsWith(";") ? toParse.substring(0, toParse.length() - 1) : toParse); } text = text.substring(0, limitIndex); } if (!text.endsWith(";")) { text += ";"; } char[] array = text.toCharArray(); for (int i = 0; i < array.length; i++) { char c = array[i]; buf.append(c); if (c == '\'') { if (i == 0 || array[i - 1] != '\\') { singleQuoteClosed = !singleQuoteClosed; } } if (c == '[' && pathClosed && singleQuoteClosed) { pathClosed = false; if (wherePart) { operatorPosition = false; } } if (c == ']' && !pathClosed && singleQuoteClosed) { pathClosed = true; if (wherePart) { operatorPosition = true; if (i + 1 == array.length || (array[i + 1] != '.' && isFilter(String.format("%s]", whereBuf.toString()), text.substring(i + 1)))) { whereBuf.append(c); whereBuf.append(MARKER_FILTER); if (i + 1 == array.length) { wherePart = false; where = parseWhere(whereBuf.toString(), nodeInfos); } continue; } } } if (c == '(' && wherePart && pathClosed && singleQuoteClosed) { if (listOperator) { whereBuf.append(MARKER_LEFT_BRACE); continue; } else { whereBuf.append(c); continue; } } if (c == ')' && wherePart && pathClosed && singleQuoteClosed) { if (listOperator) { whereBuf.append(MARKER_RIGHT_BRACE); listOperator = false; continue; } else { whereBuf.append(c); continue; } } if (!universePart && singleQuoteClosed && buf.toString().toLowerCase().endsWith("universe")) { universePart = true; continue; } if (universePart) { if (c == ';' && singleQuoteClosed) { universePart = false; if (universe.toString().trim().length() > 2) { String universeName = universe.toString().trim().substring(1, universe.toString().trim().length() - 1); universeInfo = client.getUniverseInfo(universeName); nodeInfos = client.getUniverseNodesInfo(token, universeName); } } else { universe.append(c); } continue; } if (!selectPart && pathClosed && singleQuoteClosed && buf.toString().toLowerCase().endsWith("select")) { if (StringUtils.isBlank(universe.toString())) { throw new UniverseException("Not found universe name"); } selectPart = true; select.append(RESULT_START_TEMPLATE); continue; } if (!wherePart && pathClosed && singleQuoteClosed) { if (buf.toString().toLowerCase().endsWith("where")) { wherePart = true; } if (buf.toString().toLowerCase().endsWith("where") || i == array.length - 1) { selectPart = false; select.append(parseResultObj(resultObj.toString() .replaceAll("(?i)wher$", "").replaceAll("(?i)distinc", ""), nodeInfos)); select.append(RESULT_END_TEMPLATE); continue; } } if (selectPart) { if (pathClosed && singleQuoteClosed && buf.toString().toLowerCase().endsWith("distinct")) { duplicatedRows = false; continue; } if (pathClosed && singleQuoteClosed && c == ',') { select.append(parseResultObj(resultObj.toString().replaceAll("(?i)distinc", ""), nodeInfos)); resultObj = new StringBuilder(); } else { resultObj.append(c); } continue; } if (wherePart) { if (c == ';' && pathClosed && singleQuoteClosed) { wherePart = false; where = parseWhere(whereBuf.toString(), nodeInfos); } else { if (!singleQuoteClosed || !pathClosed) { switch (c) { case ' ': case '\n': whereBuf.append(MARKER_BACKSPACE); break; case '(': whereBuf.append(MARKER_LEFT_BRACE); break; case ')': whereBuf.append(MARKER_RIGHT_BRACE); break; default: whereBuf.append(c); } } else if (pathClosed) { if ((c == 'a' || c == 'A') && i < array.length - 2 && text.substring(i, i + 3).equalsIgnoreCase("and")) { i += 2; whereBuf.append(MARKER_AND); operatorPosition = false; continue; } if ((c == 'o' || c == 'O') && i < array.length - 1 && text.substring(i, i + 2).equalsIgnoreCase("or")) { i += 1; whereBuf.append(MARKER_OR); operatorPosition = false; continue; } if (operatorPosition) { switch (c) { case '=': whereBuf.append(MARKER_EQUAL); operatorPosition = false; break; case '<': if (i + 1 < array.length) { if (array[i + 1] == '=') { whereBuf.append(MARKER_LESS_EQUAL); operatorPosition = false; i++; break; } else if (array[i + 1] == '>') { whereBuf.append(MARKER_NOT_EQUAL); operatorPosition = false; i++; break; } } operatorPosition = false; whereBuf.append(MARKER_LESS); break; case '>': if (i + 1 < array.length) { if (array[i + 1] == '=') { whereBuf.append(MARKER_GREATER_EQUALS); operatorPosition = false; i++; break; } } operatorPosition = false; whereBuf.append(MARKER_GREATER); break; case 'i': case 'I': boolean whileI = true; StringBuilder operI = new StringBuilder(); operI.append(c); while (whileI) { i++; if (i >= array.length) { whileI = false; } if (array[i] != ' ' && array[i] != '\n') { operI.append(array[i]); } else { continue; } String tmp = operI.toString().toLowerCase(); if (tmp.equals("in")) { whereBuf.append(MARKER_IN); listOperator = true; whileI = false; operatorPosition = false; } else if (tmp.equals("isnull")) { whereBuf.append(MARKER_NULL); whileI = false; operatorPosition = false; } else if (tmp.equals("isnotnull")) { whereBuf.append(MARKER_NOT_NULL); whileI = false; operatorPosition = false; } if (tmp.length() > 8) { whileI = false; } } break; case 'n': case 'N': boolean whileN = true; StringBuilder operN = new StringBuilder(); operN.append(c); while (whileN) { i++; if (i >= array.length) { whileN = false; } if (array[i] != ' ' && array[i] != '\n') { operN.append(array[i]); } else { continue; } String tmp = operN.toString().toLowerCase(); if (tmp.equals("notin")) { whereBuf.append(MARKER_NOT_IN); listOperator = true; whileN = false; operatorPosition = false; } if (tmp.length() > 4) { whileN = false; } } break; default: whereBuf.append(c); } } else { whereBuf.append(c); } } else { whereBuf.append(c); } } } } if (wherePart && StringUtils.isBlank(where)) { throw new UniverseException("Incorrect block where"); } UniverseQuery universeQuery = new UniverseQuery(select.toString().trim(), where, universeInfo, duplicatedRows, limit); if (!universeQuery.isCorrect()) { throw new UniverseException("Incorrect query"); } return universeQuery; }  static OptionalInt parseInt(String toParse); UniverseQuery convertQuery(String text, UniverseClient client, String token); static final Map<String, Integer> OPERATIONS; }
@Test public void testForConvert() throws UniverseException { String request = "universe [testUniverse];\n" + "select [Measure].[name5]\n" + "where [Filter].[name3] and [Dimension].[Test].[name2] > 1;"; UniverseQuery universeQuery = universeUtil.convertQuery(request, universeClient, null); assertNotNull(universeQuery); assertNotNull(universeQuery.getUniverseInfo()); assertEquals("<resultObjects>\n" + "<resultObject path=\"Measure|folder\\name5|measure\" id=\"name5id\"/>\n" + "</resultObjects>", universeQuery.getSelect()); assertEquals("<and>\n" + "<predefinedFilter path=\"Filter|folder\\name3|filter\" id=\"name3id\"/>\n" + "\n<comparisonFilter path=\"Dimension|folder\\Test|folder\\name2|dimension\"" + " operator=\"GreaterThan\" id=\"name2id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">1</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n", universeQuery.getWhere()); assertEquals("testUniverse", universeQuery.getUniverseInfo().getName()); }
@Test public void testConvertConditions() throws UniverseException { String request = "universe [testUniverse];\n" + "select [Measure].[name5]\n" + "where [Filter].[name3] " + "and [Dimension].[Test].[name2] >= 1 " + "and [Dimension].[Test].[name2] < 20 " + "and [Dimension].[Test].[name1] <> 'test' " + "and [Dimension].[Test].[name1] is not null " + "and [Measure].[name5] is null" + "and [Dimension].[Test].[name1] in ('var1', 'v a r 2') " + "and [Dimension].[Test].[name1] in ('var1','withoutspaces')" + "and [Dimension].[Test].[name1] in ('one value')" + "and [Dimension].[Test].[name2] in (1,3,4);"; UniverseQuery universeQuery = universeUtil.convertQuery(request, universeClient, null); assertNotNull(universeQuery); assertEquals("<and>\n" + "<and>\n" + "<and>\n" + "<and>\n" + "<and>\n" + "<and>\n" + "<and>\n" + "<and>\n" + "<and>\n" + "<predefinedFilter path=\"Filter|folder\\name3|filter\" id=\"name3id\"/>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name2|dimension\"" + " operator=\"GreaterThanOrEqualTo\" id=\"name2id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">1</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name2|dimension\"" + " operator=\"LessThan\" id=\"name2id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">20</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name1|dimension\"" + " operator=\"NotEqualTo\" id=\"name1id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"String\">test</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n\n" + "<comparisonFilter id=\"name1id\" path=\"Dimension|folder\\Test|folder\\name1|dimension\"" + " operator=\"IsNotNull\"/>\n\n" + "</and>\n\n" + "<comparisonFilter id=\"name5id\" path=\"Measure|folder\\name5|measure\" operator=\"IsNull\"/>\n\n" + "</and>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name1|dimension\"" + " operator=\"InList\" id=\"name1id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"String\">var1</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"String\">v a r 2</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name1|dimension\"" + " operator=\"InList\" id=\"name1id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"String\">var1</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"String\">withoutspaces</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name1|dimension\"" + " operator=\"InList\" id=\"name1id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"String\">one value</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name2|dimension\"" + " operator=\"InList\" id=\"name2id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">1</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"Numeric\">3</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"Numeric\">4</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</and>\n", universeQuery.getWhere()); }
@Test(expected = UniverseException.class) public void testFailConvertWithoutUniverse() throws UniverseException { String request = "universe ;\n" + "select [Measure].[name5]\n" + "where [Filter].[name3] and [Dimension].[Test].[name2] > 1;"; universeUtil.convertQuery(request, universeClient, null); }
@Test(expected = UniverseException.class) public void testFailConvertWithIncorrectSelect() throws UniverseException { String request = "universe [testUniverse];\n" + "select [not].[exist];"; universeUtil.convertQuery(request, universeClient, null); }
@Test(expected = UniverseException.class) public void testFailConvertWithIncorrectCondition() throws UniverseException { String request = "universe [testUniverse];\n" + "select [Measure].[name5]\n" + "where [Filter].[name;"; universeUtil.convertQuery(request, universeClient, null); }
@Test public void testFiltersConditions() throws UniverseException { String request1 = "universe [testUniverse];\n" + "select [Measure].[name5]\n" + "where [Filter].[name3];"; String request2 = "universe [testUniverse];\n" + "select [Measure].[name5]\n" + "where [Measure].[name5] > 2 and [Filter].[name3];"; String request3 = "universe [testUniverse];\n" + "select [Measure].[name5]\n" + "where [Filter].[name3] or [Measure].[name5];"; String request4 = "universe [testUniverse];\n" + "select [Measure].[name5]\n" + "where [Filter].[name3] and [Measure].[name5] is null;"; UniverseQuery universeQuery = universeUtil.convertQuery(request1, universeClient, null); assertEquals("<predefinedFilter path=\"Filter|folder\\name3|filter\" id=\"name3id\"/>\n", universeQuery.getWhere()); universeQuery = universeUtil.convertQuery(request2, universeClient, null); assertEquals("<and>\n" + "<comparisonFilter path=\"Measure|folder\\name5|measure\" operator=\"GreaterThan\" id=\"name5id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">2</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "<predefinedFilter path=\"Filter|folder\\name3|filter\" id=\"name3id\"/>\n\n" + "</and>\n", universeQuery.getWhere()); universeQuery = universeUtil.convertQuery(request3, universeClient, null); assertEquals("<or>\n" + "<predefinedFilter path=\"Filter|folder\\name3|filter\" id=\"name3id\"/>\n\n" + "<predefinedFilter path=\"Measure|folder\\name5|measure\" id=\"name5id\"/>\n\n" + "</or>\n", universeQuery.getWhere()); universeQuery = universeUtil.convertQuery(request4, universeClient, null); assertEquals("<and>\n" + "<predefinedFilter path=\"Filter|folder\\name3|filter\" id=\"name3id\"/>\n\n" + "<comparisonFilter id=\"name5id\" path=\"Measure|folder\\name5|measure\" operator=\"IsNull\"/>\n\n" + "</and>\n", universeQuery.getWhere()); }
@Test public void testNestedConditions() throws UniverseException { String request = "universe [testUniverse];\n" + "select [Dimension].[Test].[name2]\n" + "where ([Measure].[name5] = 'text' or ([Dimension].[Test].[name1] in ('1','2', '3') and\n" + "[Dimension].[Test].[name2] is not null)) and ([Filter].[name4] or [Measure].[name5] >=12)\n" + "or [Dimension].[Test].[name2] not in (31, 65, 77);"; UniverseQuery universeQuery = universeUtil.convertQuery(request, universeClient, null); assertEquals("<or>\n" + "<and>\n" + "<or>\n" + "<comparisonFilter path=\"Measure|folder\\name5|measure\" operator=\"EqualTo\" id=\"name5id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"String\">text</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "<and>\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name1|dimension\" operator=\"InList\" id=\"name1id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"String\">1</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"String\">2</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"String\">3</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "<comparisonFilter id=\"name2id\" path=\"Dimension|folder\\Test|folder\\name2|dimension\" operator=\"IsNotNull\"/>\n\n" + "</and>\n\n" + "</or>\n\n" + "<or>\n" + "<predefinedFilter path=\"Filter|folder\\name4|filter\" id=\"name4id\"/>\n\n" + "<comparisonFilter path=\"Measure|folder\\name5|measure\" operator=\"GreaterThanOrEqualTo\" id=\"name5id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">12</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</or>\n\n" + "</and>\n\n" + "<comparisonFilter path=\"Dimension|folder\\Test|folder\\name2|dimension\" operator=\"NotInList\" id=\"name2id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">31</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"Numeric\">65</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"Numeric\">77</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "</or>\n", universeQuery.getWhere()); }
@Test public void testWithoutConditions() throws UniverseException { String request = "universe [testUniverse];\n" + "select [Dimension].[Test].[name2], [Measure].[name5],\n" + "[Dimension].[Test].[name1] ;"; UniverseQuery universeQuery = universeUtil.convertQuery(request, universeClient, null); assertNull(universeQuery.getWhere()); assertEquals("<resultObjects>\n" + "<resultObject path=\"Dimension|folder\\Test|folder\\name2|dimension\" id=\"name2id\"/>\n" + "<resultObject path=\"Measure|folder\\name5|measure\" id=\"name5id\"/>\n" + "<resultObject path=\"Dimension|folder\\Test|folder\\name1|dimension\" id=\"name1id\"/>\n" + "</resultObjects>", universeQuery.getSelect()); }
@Test public void testCaseSensitive() throws UniverseException { String request = "uniVersE [testUniverse];\n" + "seLEct [Dimension].[Test].[name2], [Measure].[name5]\n" + "whERE [Dimension].[Test].[name2] Is NULl Or [Measure].[name5] IN (1,2) aNd [Measure].[name5] is NOT nUll;"; UniverseQuery universeQuery = universeUtil.convertQuery(request, universeClient, null); assertEquals("<resultObjects>\n" + "<resultObject path=\"Dimension|folder\\Test|folder\\name2|dimension\" id=\"name2id\"/>\n" + "<resultObject path=\"Measure|folder\\name5|measure\" id=\"name5id\"/>\n" + "</resultObjects>", universeQuery.getSelect()); assertEquals("<or>\n" + "<comparisonFilter id=\"name2id\" path=\"Dimension|folder\\Test|folder\\name2|dimension\" operator=\"IsNull\"/>\n\n" + "<and>\n" + "<comparisonFilter path=\"Measure|folder\\name5|measure\" operator=\"InList\" id=\"name5id\">\n" + "<constantOperand>\n" + "<value>\n" + "<caption type=\"Numeric\">1</caption>\n" + "</value>\n" + "<value>\n" + "<caption type=\"Numeric\">2</caption>\n" + "</value>\n" + "</constantOperand>\n" + "</comparisonFilter>\n\n" + "<comparisonFilter id=\"name5id\" path=\"Measure|folder\\name5|measure\" operator=\"IsNotNull\"/>\n\n" + "</and>\n\n" + "</or>\n", universeQuery.getWhere()); }