1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package org.jdbc4olap.jdbc;
23
24 import java.io.StringReader;
25 import java.sql.Connection;
26 import java.sql.DatabaseMetaData;
27 import java.sql.ResultSet;
28 import java.sql.ResultSetMetaData;
29 import java.sql.SQLException;
30 import java.sql.SQLWarning;
31 import java.sql.Statement;
32 import java.sql.Types;
33 import java.util.ArrayList;
34 import java.util.HashMap;
35 import java.util.Iterator;
36 import java.util.List;
37
38 import javax.xml.soap.Name;
39 import javax.xml.soap.Node;
40 import javax.xml.soap.SOAPBody;
41 import javax.xml.soap.SOAPElement;
42 import javax.xml.soap.SOAPEnvelope;
43 import javax.xml.soap.SOAPMessage;
44 import javax.xml.soap.SOAPPart;
45
46 import org.jdbc4olap.parsing.ParseException;
47 import org.jdbc4olap.parsing.SimpleNode;
48 import org.jdbc4olap.parsing.SqlGrammar;
49 import org.jdbc4olap.xmla.QueryColumn;
50 import org.jdbc4olap.xmla.QueryFilter;
51 import org.jdbc4olap.xmla.QueryFilterOperand;
52 import org.jdbc4olap.xmla.QueryTable;
53 import org.jdbc4olap.xmla.XmlaHelper;
54 import org.jdbc4olap.xmla.XmlaTuple;
55 import org.jdbc4olap.xmla.XmlaConn;
56 import org.w3c.dom.NodeList;
57
58
59
60
61
62 class OlapStatement implements Statement {
63
64 private final DatabaseMetaData olapMetadata;
65 private final XmlaConn xmlaConn;
66 private String catalog;
67 private String schema;
68 OlapResultSet resultSet;
69 private List<QueryTable> fromList;
70 private HashMap<String, List<String>> tableCache;
71 private HashMap<String, List<QueryColumn>> fieldMap;
72 private QueryParsingHelper parsingHelper;
73
74 private static final String MDD_URI = "urn:schemas-microsoft-com:xml-analysis:mddataset";
75 static final String ROWS_URI = "urn:schemas-microsoft-com:xml-analysis:rowset";
76 private static final String XMLA_URI = "urn:schemas-microsoft-com:xml-analysis";
77 static final String XSI_URI = "http://www.w3.org/2001/XMLSchema-instance";
78
79 OlapStatement(final OlapConnection c) throws SQLException {
80 olapMetadata = c.getMetaData();
81 xmlaConn = c.getXmlaConn();
82 resultSet = OlapResultSet.createEmptyResultSet();
83 parsingHelper = new QueryParsingHelper();
84 }
85
86 public void addBatch(final String sql) throws SQLException {
87 throw new SQLException("jdbc4olap driver does not support batch");
88 }
89
90 public void cancel() throws SQLException {
91 }
92
93 public void clearBatch() throws SQLException {
94 throw new SQLException("jdbc4olap driver does not support batch");
95 }
96
97 public void clearWarnings() throws SQLException {
98 }
99
100 public void close() throws SQLException {
101
102 if (resultSet != null) {
103 resultSet.close();
104 }
105 resultSet = null;
106 }
107
108 public boolean execute(final String sql) throws SQLException {
109 resultSet = (OlapResultSet) executeQuery(sql);
110 return true;
111 }
112
113 public boolean execute(final String sql, final int autoGeneratedKeys) throws SQLException {
114 return execute(sql);
115 }
116
117 public boolean execute(final String sql, final int[] columnIndexes) throws SQLException {
118 return execute(sql);
119 }
120
121 public boolean execute(final String sql, final String[] columnNames) throws SQLException {
122 return execute(sql);
123 }
124
125 public int[] executeBatch() throws SQLException {
126 throw new SQLException("jdbc4olap driver does not support batch");
127 }
128
129 public ResultSet executeQuery(final String sql) throws SQLException {
130
131
132 if (sql == null || "".equals(sql)) {
133 resultSet = OlapResultSet.createEmptyResultSet();
134 return resultSet;
135 }
136
137 final SimpleNode root = parseQuery(sql);
138 prepareQueryMetaData(root);
139 resultSet = (OlapResultSet) processQuery(root);
140 resultSet.setMetaData(getResultSetMetaData(resultSet));
141 resultSet.setStatement(this);
142 return resultSet;
143 }
144
145 public int executeUpdate(final String sql) throws SQLException {
146 execute(sql);
147 return 0;
148 }
149
150 public int executeUpdate(final String sql, final int autoGeneratedKeys) throws SQLException {
151 execute(sql);
152 return 0;
153 }
154
155 public int executeUpdate(final String sql, final int[] columnIndexes) throws SQLException {
156 execute(sql);
157 return 0;
158 }
159
160 public int executeUpdate(final String sql, final String[] columnNames) throws SQLException {
161 execute(sql);
162 return 0;
163 }
164
165 public Connection getConnection() throws SQLException {
166 return olapMetadata.getConnection();
167 }
168
169 public int getFetchDirection() throws SQLException {
170 return resultSet.getFetchDirection();
171 }
172
173 public int getFetchSize() throws SQLException {
174 return resultSet.getFetchSize();
175 }
176
177 public ResultSet getGeneratedKeys() throws SQLException {
178 return OlapResultSet.createEmptyResultSet();
179 }
180
181 public int getMaxFieldSize() throws SQLException {
182 return 0;
183 }
184
185 public int getMaxRows() throws SQLException {
186 return 0;
187 }
188
189 public boolean getMoreResults() throws SQLException {
190 return false;
191 }
192
193 public boolean getMoreResults(final int current) throws SQLException {
194 return false;
195 }
196
197 public int getQueryTimeout() throws SQLException {
198 return 0;
199 }
200
201 public ResultSet getResultSet() throws SQLException {
202 return resultSet;
203 }
204
205 public int getResultSetConcurrency() throws SQLException {
206 return ResultSet.CONCUR_READ_ONLY;
207 }
208
209 public int getResultSetHoldability() throws SQLException {
210 return ResultSet.HOLD_CURSORS_OVER_COMMIT;
211 }
212
213 public int getResultSetType() throws SQLException {
214 return resultSet.getType();
215 }
216
217 public int getUpdateCount() throws SQLException {
218 return -1;
219 }
220
221 public SQLWarning getWarnings() throws SQLException {
222 return null;
223 }
224
225 public void setCursorName(final String name) throws SQLException {
226 }
227
228 public void setEscapeProcessing(final boolean enable) throws SQLException {
229
230 }
231
232 public void setFetchDirection(final int direction) throws SQLException {
233 resultSet.setFetchDirection(direction);
234 }
235
236 public void setFetchSize(final int rows) throws SQLException {
237 resultSet.setFetchSize(rows);
238 }
239
240 public void setMaxFieldSize(final int max) throws SQLException {
241
242 }
243
244 public void setMaxRows(final int max) throws SQLException {
245
246 }
247
248 public void setQueryTimeout(final int seconds) throws SQLException {
249
250 }
251
252 SimpleNode parseQuery(final String sql) throws SQLException {
253 SqlGrammar p = new SqlGrammar(new StringReader(sql));
254 try {
255 return p.QueryStatement();
256 } catch (ParseException pe) {
257 throw new SQLException(pe.getLocalizedMessage());
258 }
259 }
260
261
262 void prepareQueryMetaData(final SimpleNode root) throws SQLException {
263
264 final SimpleNode selectClause = (SimpleNode) root.jjtGetChild(0);
265 final List<QueryColumn> selectList = new ArrayList<QueryColumn>(parsingHelper.extractQueryFields(selectClause));
266
267
268 final SimpleNode fromClause = (SimpleNode) root.jjtGetChild(1);
269 fromList = new ArrayList<QueryTable>(parsingHelper.extractQueryTables(fromClause));
270
271
272 catalog = "";
273 schema = "";
274 tableCache = new HashMap<String, List<String>>();
275 final List<String> tableAliases = new ArrayList<String>();
276 for (QueryTable table : fromList) {
277 final String lCat = table.getCatalog();
278 final String lSch = table.getSchema();
279
280 if (lCat == null || lCat.equals("")) {
281
282 throw new SQLException("Catalog must be specified");
283 }
284 if (!catalog.equals("") && !catalog.equals(lCat)) {
285
286 throw new SQLException("More than one catalog requested");
287 }
288 if (catalog.equals("")) {
289 final ResultSet catRS = olapMetadata.getCatalogs();
290 boolean catFound = false;
291 catRS.beforeFirst();
292 while (catRS.next()) {
293 if (catRS.getString(1).equals(lCat)) {
294 catFound = true;
295 }
296 }
297 if (!catFound) {
298
299 throw new SQLException("Unknown catalog : " + catalog);
300 }
301 catalog = lCat;
302 }
303
304 if (lSch == null || lSch.equals("")) {
305
306 throw new SQLException("Schema must be specified");
307 }
308 if (!schema.equals("") && !schema.equals(lSch)) {
309
310 throw new SQLException("More than one schema requested");
311 }
312 if (schema.equals("")) {
313 ResultSet schRS = olapMetadata.getSchemas();
314 boolean schFound = false;
315 schRS.beforeFirst();
316 while (schRS.next()) {
317 if (schRS.getString(1).equals(lSch)) {
318 schFound = true;
319 }
320 }
321 if (!schFound) {
322
323 throw new SQLException("Unknown schema : " + schema);
324 }
325 schema = lSch;
326 }
327
328 if (table.getTableAlias() != null) {
329 if (tableAliases.contains(table.getTableAlias())) {
330
331 throw new SQLException("Table alias '" + table.getTableAlias() + "' defined more than once.");
332 } else {
333 tableAliases.add(table.getTableAlias());
334 }
335 }
336
337 final ResultSet tableRS = olapMetadata.getTables(catalog, schema, table.getTable(), null);
338 if (tableRS.first()) {
339 final ResultSet colRS = olapMetadata.getColumns(catalog, schema, table.getTable(), null);
340 final List<String> colList = new ArrayList<String>();
341 colRS.beforeFirst();
342 while (colRS.next()) {
343 colList.add(colRS.getString(4));
344 }
345 tableCache.put(table.getTable(), colList);
346 } else {
347
348 throw new SQLException("Unknown table : " + table.getTable());
349 }
350 }
351 for (String alias : tableAliases) {
352 if (tableCache.containsKey(alias)) {
353
354 throw new SQLException("Can't use a table name as a table alias");
355 }
356 }
357
358
359 fieldMap = new HashMap<String, List<QueryColumn>>();
360 List<QueryColumn> fieldList;
361 int rank = 0;
362 final List<String> fieldAliases = new ArrayList<String>();
363 for (QueryColumn field : selectList) {
364
365 if (field.getFieldAlias() != null) {
366 if (fieldAliases.contains(field.getFieldAlias())) {
367
368 throw new SQLException("Column alias '" + field.getFieldAlias() + "' defined more than once.");
369 } else {
370 fieldAliases.add(field.getFieldAlias());
371 }
372 }
373 final List<QueryColumn> subList = new ArrayList<QueryColumn>();
374 if ("*".equals(field.getField())) {
375 final List<String> tableList = new ArrayList<String>();
376 if (field.getTableAlias() == null || "".equals(field.getTableAlias())) {
377 tableList.addAll(tableCache.keySet());
378 } else {
379 boolean found = false;
380 String alias = field.getTableAlias();
381 for (QueryTable table : fromList) {
382 if (alias.equals(table.getTable()) || alias.equals(table.getTableAlias())) {
383 found = true;
384 tableList.add(table.getTable());
385 }
386 }
387 if (!found) {
388 throw new SQLException("Unknown field '" + field.getField());
389 }
390 }
391 for (String table : tableList) {
392 for (String fieldName : tableCache.get(table)) {
393 QueryColumn qField = new QueryColumn();
394 qField.setField(fieldName);
395 qField.setTable(table);
396 subList.add(qField);
397 }
398 }
399 } else {
400 subList.add(checkField(field, fromList, tableCache));
401 }
402 for (QueryColumn qField : subList) {
403
404 fieldList = fieldMap.get(qField.getTable());
405 if (fieldList == null) {
406 fieldList = new ArrayList<QueryColumn>();
407 }
408 qField.setRank(rank);
409 rank += 1;
410 fieldList.add(qField);
411 fieldMap.put(qField.getTable(), fieldList);
412 }
413 }
414 }
415
416 private QueryColumn checkField(final QueryColumn field, final List<QueryTable> tableList,
417 final HashMap<String, List<String>> tablesFieldsCache) throws SQLException {
418 boolean fieldFound = false;
419
420 if (field.getTableAlias() != null) {
421
422 for (QueryTable table : tableList) {
423 if (field.getTableAlias().equals(table.getTableAlias())
424 || field.getTableAlias().equals(table.getTable())) {
425 List colList = tablesFieldsCache.get(table.getTable());
426 if (colList.contains(field.getField())) {
427 field.setTable(table.getTable());
428 fieldFound = true;
429 } else {
430
431 throw new SQLException("Unknown column '" + field.getField() + "' for table '" + table.getTable() + "'.");
432 }
433 }
434 }
435 } else {
436
437 for (QueryTable table : tableList) {
438 List colList = tablesFieldsCache.get(table.getTable());
439 if (colList.contains(field.getField())) {
440
441 if (fieldFound) {
442
443 throw new SQLException("Ambiguous column definition : " + field.getField());
444 } else {
445 field.setTable(table.getTable());
446 fieldFound = true;
447 }
448 }
449 }
450 }
451 if (!fieldFound) {
452
453 throw new SQLException("Unknown field '" + field.getField());
454 }
455 return field;
456 }
457
458 private String buildMdxQueryAxis(final QueryColumn field, final String fieldPrefix, final String fieldSuffix,
459 final List<String> current, final List<List<String>> filterColl) {
460 StringBuilder sb = new StringBuilder();
461 if (filterColl != null) {
462 if (filterColl.size() == current.size()) {
463 for (int i = 0; i < current.size(); i++) {
464 String col = current.get(i);
465 if (i + 1 < current.size()) {
466 sb.append("INTERSECT(");
467 }
468 sb.append(fieldPrefix).append("Union( {Ancestor(").append(col)
469 .append(",").append(field.getField()).append(")}, {Descendants(")
470 .append(col).append(",").append(field.getField()).append(")} )")
471 .append(fieldSuffix);
472 if (i > 0) {
473 sb.append("),");
474 } else {
475 sb.append(",");
476 }
477 }
478 } else {
479 List<String> firstCol = filterColl.get(current.size());
480 for (String elt : firstCol) {
481 List<String> tmp = new ArrayList<String>(current);
482 tmp.add(elt);
483 sb.append(buildMdxQueryAxis(field, fieldPrefix, fieldSuffix, tmp, filterColl));
484 }
485 }
486 }
487 return sb.toString();
488 }
489
490 private SOAPElement selectSingleNode(final SOAPElement contextNode, final Name childName) {
491 Iterator it = contextNode.getChildElements(childName);
492 if (it.hasNext()) {
493 return (SOAPElement) it.next();
494 } else {
495 return null;
496 }
497 }
498
499 ResultSet processQuery(final SimpleNode root) throws SQLException {
500
501 List<QueryFilter> whereList = new ArrayList<QueryFilter>();
502 if (root.jjtGetNumChildren() > 2) {
503 final SimpleNode whereClause = (SimpleNode) root.jjtGetChild(2);
504 whereList = parsingHelper.extractQueryFilters(whereClause);
505 }
506
507 final String measureName = xmlaConn.getMeasureName(catalog, schema);
508
509
510 final List<QueryFilter> mdxFilterList = new ArrayList<QueryFilter>();
511 final List<String> mdxWhereList = new ArrayList<String>();
512 final HashMap<String, HashMap<String, List<String>>> filterMap = new HashMap<String, HashMap<String, List<String>>>();
513 for (QueryFilter filter : whereList) {
514
515 final QueryFilterOperand leftOp = filter.getLeftOp();
516 final QueryFilterOperand rightOp = filter.getRightOp();
517 if (leftOp == null || filter.getOperator() == null || rightOp == null) {
518 throw new SQLException("Where clause syntax error.");
519 }
520 if (leftOp.getCol() != null) {
521 leftOp.setCol(checkField(leftOp.getCol(), fromList, tableCache));
522 }
523 if (rightOp.getCol() != null) {
524 rightOp.setCol(checkField(rightOp.getCol(), fromList, tableCache));
525 }
526
527
528 if (leftOp.getValList() != null && rightOp.getValList() != null) {
529 if (!leftOp.getValList().equals(rightOp.getValList())) {
530
531 return new OlapResultSet();
532 }
533 } else if (leftOp.getCol() != null && rightOp.getCol() != null) {
534
535 } else {
536
537 final QueryColumn field = leftOp.getCol() == null ? rightOp.getCol() : leftOp.getCol();
538 final List<String> expr = leftOp.getValList() == null ? rightOp.getValList() : leftOp.getValList();
539 if (!field.getField().endsWith("_ID")) {
540 if (measureName.equals(field.getTable())) {
541 mdxFilterList.add(filter);
542 } else {
543
544 if (!filter.getOperator().equals("=")) {
545 throw new SQLException("Only equality filter supported");
546 }
547
548 final NodeList members = xmlaConn.getMembersNodeList(catalog, schema, field.getTable(), field.getField(), null);
549 Node item;
550 XmlaHelper helper = new XmlaHelper();
551 final List<String> foundValues = new ArrayList<String>();
552 for (int i = 0; i < members.getLength(); i++) {
553 item = (Node) members.item(i);
554 String member = "";
555 String desc = "";
556 final NodeList nl = item.getChildNodes();
557 for (int j = 0; j < nl.getLength(); j++) {
558 org.w3c.dom.Node node = nl.item(j);
559 final String nodeName = node.getNodeName();
560 final String nodeTextContent = helper.getTextContent(node);
561
562 if (nodeName.equals("MEMBER_UNIQUE_NAME")) {
563 member = nodeTextContent;
564 } else if (nodeName.equals("MEMBER_CAPTION")) {
565 desc = nodeTextContent;
566 }
567 }
568 for (String pattern : expr) {
569 if (member.equalsIgnoreCase(pattern) || desc.equalsIgnoreCase(pattern)) {
570 foundValues.add(member);
571 }
572 }
573 }
574 if (foundValues.size() == 0) {
575
576 return new OlapResultSet();
577 }
578 if (fieldMap.get(field.getTable()) != null) {
579 HashMap<String, List<String>> tableMap = filterMap.get(field.getTable());
580 if (tableMap == null) {
581 tableMap = new HashMap<String, List<String>>();
582 }
583 List<String> filterList = tableMap.get(field.getField());
584 if (filterList == null) {
585 filterList = new ArrayList<String>();
586 filterList.addAll(foundValues);
587 } else {
588 filterList.retainAll(foundValues);
589 }
590 tableMap.put(field.getField(), filterList);
591 filterMap.put(field.getTable(), tableMap);
592 } else {
593 mdxWhereList.addAll(foundValues);
594 }
595 }
596 }
597 }
598 }
599
600 final StringBuffer fieldPrefix = new StringBuffer();
601 final StringBuffer fieldSuffix = new StringBuffer();
602 if (!mdxFilterList.isEmpty()) {
603 fieldPrefix.append("Filter(");
604 for (QueryFilter filter : mdxFilterList) {
605 if (fieldSuffix.length() > 0) {
606 fieldSuffix.append(" and ");
607 } else {
608 fieldSuffix.append(", ");
609 }
610 if (filter.getLeftOp().getCol() != null) {
611 fieldSuffix.append(filter.getLeftOp().getCol().getField()).append(filter.getOperator()).append(filter.getRightOp().getValList());
612 } else {
613 fieldSuffix.append(filter.getLeftOp().getValList()).append(filter.getOperator()).append(filter.getRightOp().getCol().getField());
614 }
615 }
616 fieldSuffix.append(")");
617 }
618
619
620 final StringBuffer mdx = new StringBuffer();
621 mdx.append("SELECT ");
622 int axisCount = 0;
623 int measureAxis = -1;
624
625 for (String table : fieldMap.keySet()) {
626
627 final List<QueryColumn> list = fieldMap.get(table);
628 final StringBuffer sb = new StringBuffer(" { ");
629 boolean fieldAdded = false;
630 String suffix = "";
631 if (!table.equals(measureName)) {
632 suffix = ".Members";
633 }
634 final HashMap<String, List<String>> tableFilterMap = filterMap.get(table);
635 for (QueryColumn field : list) {
636 if (!field.getField().endsWith("_ID")) {
637 if (tableFilterMap != null && tableFilterMap.size() > 0) {
638 sb.append(buildMdxQueryAxis(field, fieldPrefix.toString(), fieldSuffix.toString(), new ArrayList<String>(), new ArrayList<List<String>>(tableFilterMap.values())));
639 } else {
640 sb.append(fieldPrefix).append(field.getField())
641 .append(suffix).append(fieldSuffix).append(",");
642 }
643 fieldAdded = true;
644 }
645 }
646 if (fieldAdded) {
647 if (sb.charAt(sb.length() - 1) == ',') {
648 sb.deleteCharAt(sb.length() - 1);
649 }
650 mdx.append(sb).append(" } On Axis(").append(axisCount).append("),");
651 if (table.equals(measureName)) {
652 measureAxis = axisCount;
653 }
654 axisCount += 1;
655 }
656 }
657 if (axisCount == 1) {
658
659
660
661
662
663
664
665
666
667
668 }
669 if (mdx.charAt(mdx.length() - 1) == ',') {
670 mdx.deleteCharAt(mdx.length() - 1);
671 }
672 if (schema.trim().startsWith("[")) {
673 mdx.append(" FROM ").append(schema);
674 } else {
675 mdx.append(" FROM [").append(schema).append("]");
676 }
677
678 if (!mdxWhereList.isEmpty()) {
679 StringBuffer querySuffix = new StringBuffer("WHERE (");
680 for (String filter : mdxWhereList) {
681 querySuffix.append(filter).append(",");
682 }
683 querySuffix.deleteCharAt(querySuffix.length() - 1);
684 querySuffix.append(")");
685 mdx.append(querySuffix);
686 }
687
688
689
690
691 SOAPMessage reply = xmlaConn.execute(catalog, mdx.toString());
692
693
694
695
696 try {
697 final SOAPPart sp = reply.getSOAPPart();
698 final SOAPEnvelope envelope = sp.getEnvelope();
699 final SOAPBody body = envelope.getBody();
700 Name name;
701 if (xmlaConn.getServerType() == XmlaConn.SAP_BW_SERVER) {
702 name = envelope.createName("ExecuteResponse", "", XMLA_URI);
703 } else {
704 name = envelope.createName("ExecuteResponse", "m", XMLA_URI);
705 }
706 final SOAPElement eResponse = selectSingleNode(body, name);
707 if (xmlaConn.getServerType() == XmlaConn.SAP_BW_SERVER) {
708 name = envelope.createName("return", "", XMLA_URI);
709 } else {
710 name = envelope.createName("return", "m", XMLA_URI);
711 }
712 final SOAPElement eReturn = selectSingleNode(eResponse, name);
713 name = envelope.createName("root", "", MDD_URI);
714 final SOAPElement rootNode = selectSingleNode(eReturn, name);
715 name = envelope.createName("Axes", "", MDD_URI);
716 final SOAPElement axesNode = (SOAPElement) rootNode.getChildElements(name).next();
717
718
719
720 name = envelope.createName("Axis", "", MDD_URI);
721 Iterator axis = axesNode.getChildElements(name);
722 final int axisSize = axesNode.getElementsByTagName("Axis").getLength();
723 int nbCells = 1;
724 int nbRows = 1;
725 int currentAxis = 0;
726 while (axis.hasNext()) {
727 SOAPElement lAxis = (SOAPElement) axis.next();
728
729 if (currentAxis != axisSize - 1) {
730 name = envelope.createName("Tuples", "", MDD_URI);
731 SOAPElement tuplesNode = (SOAPElement) lAxis.getChildElements(name).next();
732 NodeList tuple = tuplesNode.getElementsByTagName("Tuple");
733 nbCells *= tuple.getLength();
734 if (currentAxis != measureAxis){
735 nbRows *= tuple.getLength();
736 }
737 }
738 currentAxis += 1;
739 }
740
741
742
743 final HashMap<String, Object[]> resultData = new HashMap<String, Object[]>();
744
745 final HashMap<String, Integer> resultTypeMap = new HashMap<String, Integer>();
746 for (List<QueryColumn> list : fieldMap.values()) {
747 for (QueryColumn field : list) {
748 resultData.put(field.getField(), new Object[nbRows]);
749 }
750 }
751
752
753
754 name = envelope.createName("Axis", "", MDD_URI);
755 axis = axesNode.getChildElements(name);
756 int dimCount = 1;
757 currentAxis = 0;
758 String[] measures;
759 final Name tuplesName = envelope.createName("Tuples", "", MDD_URI);
760 final Name tupleName = envelope.createName("Tuple", "", MDD_URI);
761 final Name memberName = envelope.createName("Member", "", MDD_URI);
762
763 while (axis.hasNext()) {
764 final SOAPElement lAxis = (SOAPElement) axis.next();
765 final SOAPElement tuplesNode = (SOAPElement) lAxis.getChildElements(tuplesName).next();
766 final Iterator tuple = tuplesNode.getChildElements(tupleName);
767 final int tuplesSize = tuplesNode.getElementsByTagName("Tuple").getLength();
768
769 if (currentAxis != axisSize - 1 && currentAxis < axisCount) {
770 if (currentAxis == measureAxis) {
771
772 measures = new String[tuplesSize];
773 int index = 0;
774 while (tuple.hasNext()) {
775 final SOAPElement lTuple = (SOAPElement) tuple.next();
776 final SOAPElement lMember = (SOAPElement) lTuple.getChildElements(memberName).next();
777 String uName = "";
778 final Iterator memberIt = lMember.getChildElements();
779 while (memberIt.hasNext()) {
780 final Node n = (Node) memberIt.next();
781 if (n instanceof SOAPElement) {
782 SOAPElement el = (SOAPElement) n;
783 if (el.getNodeName().equals("UName")) {
784 uName = el.getValue();
785 }
786 }
787 }
788 measures[index] = uName;
789 index += 1;
790 }
791 name = envelope.createName("CellData", "", MDD_URI);
792 final SOAPElement cellData = (SOAPElement) rootNode.getChildElements(name).next();
793 name = envelope.createName("Cell", "", MDD_URI);
794 final Iterator cells = cellData.getChildElements(name);
795 final Name cellOrdinal = envelope.createName("CellOrdinal", "", "");
796 final Name valueName = envelope.createName("Value", "", MDD_URI);
797 final Name xsiType = envelope.createName("xsi:type", "", "");
798 Integer ordinal;
799 int fact, mod;
800 String measure;
801 int fieldType = -1;
802
803
804
805 while (cells.hasNext()) {
806 final SOAPElement cell = (SOAPElement) cells.next();
807 ordinal = new Integer(cell.getAttributeValue(cellOrdinal));
808 if (ordinal < nbCells) {
809 mod = ordinal % tuplesSize;
810 fact = ordinal / tuplesSize;
811 measure = measures[mod];
812 final Object[] data = resultData.get(measure);
813 try {
814 SOAPElement valueCell = (SOAPElement) cell.getChildElements(valueName).next();
815 if (fieldType == -1) {
816 String type = valueCell.getAttributeValue(xsiType);
817 if (type!=null){
818 if (type.equalsIgnoreCase("xsd:boolean")) {
819 fieldType = Types.BOOLEAN;
820 } else if (type.equalsIgnoreCase("xsd:double")) {
821 fieldType = Types.DOUBLE;
822 } else if (type.equalsIgnoreCase("xsd:float")) {
823 fieldType = Types.FLOAT;
824 } else if (type.equalsIgnoreCase("xsd:int")) {
825 fieldType = Types.INTEGER;
826 } else {
827 fieldType = Types.NUMERIC;
828 }
829 } else {
830 fieldType = Types.NUMERIC;
831 }
832 }
833 switch (fieldType) {
834 case Types.BOOLEAN:
835 Boolean b = Boolean.valueOf(valueCell.getValue());
836 data[fact] = b.equals(Boolean.TRUE) ? 1 : 0;
837 break;
838 case Types.DOUBLE:
839 data[fact] = new Double(valueCell.getValue());
840 break;
841 case Types.FLOAT:
842 data[fact] = new Float(valueCell.getValue());
843 break;
844 case Types.INTEGER:
845 data[fact] = new Integer(valueCell.getValue());
846 break;
847 default:
848 String val = valueCell.getValue();
849 val = val.replace(" ", "");
850 if (val.matches("[\\d]+\\,[\\d]+")) {
851 val = val.replace(",", ".");
852 }
853 try {
854 data[fact] = Double.valueOf(val);
855 } catch (Exception e) {
856 data[fact] = null;
857 }
858 }
859 } catch (Exception e) {
860 data[fact] = null;
861 }
862 resultTypeMap.put(measure, fieldType);
863 resultData.put(measure, data);
864 }
865 }
866 } else {
867 final HashMap<String, XmlaTuple[]> dimData = new HashMap<String, XmlaTuple[]>();
868 int index = 0;
869 while (tuple.hasNext()) {
870 final SOAPElement lTuple = (SOAPElement) tuple.next();
871 final SOAPElement lMember = (SOAPElement) lTuple.getChildElements(memberName).next();
872 String lName = "";
873 String uName = "";
874 String caption = "";
875 final Iterator memberIt = lMember.getChildElements();
876 while (memberIt.hasNext()) {
877 final Node n = (Node) memberIt.next();
878 if (n instanceof SOAPElement) {
879 SOAPElement lChild = (SOAPElement) n;
880 if (lChild.getNodeName().equals("UName")) {
881 uName = lChild.getValue();
882 } else if (lChild.getNodeName().equals("LName")) {
883 lName = lChild.getValue();
884 } else if (lChild.getNodeName().equals("Caption")) {
885 caption = lChild.getValue();
886 }
887 }
888 }
889 XmlaTuple[] tab = dimData.get(lName);
890 if (tab == null) {
891 tab = new XmlaTuple[tuplesSize];
892 }
893 final XmlaTuple tup = new XmlaTuple();
894 tup.setField(lName);
895 tup.setHierarchy(uName);
896 tup.setValue(caption);
897 tab[index] = tup;
898 dimData.put(lName, tab);
899 index++;
900 }
901
902
903 if (dimData.keySet().size() > 1) {
904
905 final HashMap<String, List<String>> parentMap = new HashMap<String, List<String>>();
906 String bottomField = "";
907
908 for (String fieldA : dimData.keySet()) {
909 final XmlaTuple[] tupA = dimData.get(fieldA);
910
911 final List<Integer> listA = new ArrayList<Integer>();
912 final List<Integer> listB = new ArrayList<Integer>();
913 for (int i = 0; i < tupA.length; i++) {
914 if (tupA[i] != null) {
915 listA.add(i);
916 } else {
917 listB.add(i);
918 }
919 }
920 List<String> parentListA = parentMap.get(fieldA);
921 if (parentListA == null) {
922 parentListA = new ArrayList<String>();
923 }
924 boolean isBottom = true;
925 for (Integer indexA : listA) {
926 final String hierarchyA = tupA[indexA].getHierarchy();
927 for (String fieldB : dimData.keySet()) {
928 final XmlaTuple[] tupB = dimData.get(fieldB);
929 boolean found = false;
930 if (!fieldA.equals(fieldB) && !parentListA.contains(fieldB)) {
931 for (Integer indexB : listB) {
932 if (tupB[indexB] != null && tupB[indexB].getHierarchy().startsWith(hierarchyA)) {
933 tupA[indexB] = tupA[indexA];
934 found = true;
935 }
936 }
937 }
938 if (found) {
939 List<String> parentListB = parentMap.get(fieldB);
940 if (parentListB == null) {
941 parentListB = new ArrayList<String>();
942 }
943 parentListB.add(fieldA);
944 parentMap.put(fieldB, parentListB);
945 isBottom = false;
946 }
947 }
948 }
949 if (isBottom) {
950 bottomField = fieldA;
951 }
952 }
953 if (!bottomField.equals("")) {
954 final List<Integer> emptyCells = new ArrayList<Integer>();
955 final XmlaTuple[] tupBottom = dimData.get(bottomField);
956 for (int i = 0; i < tupBottom.length; i++) {
957 if (tupBottom[i] == null) {
958 emptyCells.add(i);
959 }
960 }
961 for (String field : dimData.keySet()) {
962 final XmlaTuple[] tup = dimData.get(field);
963 for (Integer i : emptyCells) {
964 tup[i] = null;
965 }
966 }
967 }
968 }
969
970
971 for (String table : dimData.keySet()) {
972 final Object[] data = resultData.get(table);
973 final XmlaTuple[] tupTab = dimData.get(table);
974 String value;
975 final int max = data.length;
976
977
978 for (int i = 0; i < max; i++) {
979 final int k = (i / dimCount) % tupTab.length;
980 if (tupTab[k] != null) {
981 value = tupTab[k].getValue();
982 data[i] = value;
983 }
984 }
985 resultData.put(table, data);
986 }
987 dimCount *= tuplesSize;
988 }
989 }
990 currentAxis += 1;
991 }
992 int fieldCount = 0;
993 for (List<QueryColumn> l : fieldMap.values()) {
994 fieldCount += l.size();
995 }
996 final String[] orderedFieldTable = new String[fieldCount];
997 for (List<QueryColumn> list : fieldMap.values()) {
998 for (QueryColumn field : list) {
999 orderedFieldTable[field.getRank()] = field.getField();
1000 }
1001 }
1002
1003 final List<List<Integer>> fieldIndexByTable = new ArrayList<List<Integer>>();
1004 for (String table : fieldMap.keySet()) {
1005 if (!table.equals(measureName)) {
1006 final List<Integer> tableList = new ArrayList<Integer>();
1007 for (QueryColumn field : fieldMap.get(table)) {
1008 tableList.add(field.getRank());
1009 }
1010 fieldIndexByTable.add(tableList);
1011 }
1012 }
1013
1014 final OlapResultSet rs = new OlapResultSet();
1015 Object[] dataList;
1016 for (int i = 0; i < nbRows; i++) {
1017 dataList = new Object[fieldCount];
1018 for (int j = 0; j < fieldCount; j++) {
1019 String field = orderedFieldTable[j];
1020 final Object[] data = resultData.get(field);
1021 dataList[j] = data[i];
1022 }
1023 boolean copy = true;
1024 for (List<Integer> tableIndex : fieldIndexByTable) {
1025 boolean hasData = false;
1026 for (Integer index : tableIndex) {
1027 if (dataList[index] != null) {
1028 hasData = true;
1029 }
1030 }
1031 if (!hasData) {
1032 copy = false;
1033 }
1034 }
1035 if (copy) {
1036 rs.add(dataList);
1037 }
1038 }
1039 return rs;
1040 } catch (Exception e) {
1041 return null;
1042 }
1043 }
1044
1045 ResultSetMetaData getResultSetMetaData(final OlapResultSet owner) throws SQLException {
1046 final OlapResultSetMetaData md = new OlapResultSetMetaData(owner);
1047
1048
1049 int fieldCount = 0;
1050 for (List<QueryColumn> l : fieldMap.values()) {
1051 fieldCount += l.size();
1052 }
1053
1054 final QueryColumn[] orderedQueryFields = new QueryColumn[fieldCount];
1055 for (List<QueryColumn> list : fieldMap.values()) {
1056 for (QueryColumn field : list) {
1057 orderedQueryFields[field.getRank()] = field;
1058 }
1059 }
1060
1061
1062 for (QueryColumn field : orderedQueryFields) {
1063 final OlapColumnMetaData column = new OlapColumnMetaData();
1064 column.setName(field.getField());
1065 column.setCatalogName(catalog);
1066 column.setSchemaName(schema);
1067 column.setTableName(field.getTable());
1068
1069
1070
1071 if (xmlaConn.getMeasureName(catalog, schema).equals(field.getTable())) {
1072 column.setType(Types.NUMERIC);
1073 } else {
1074 column.setType(Types.VARCHAR);
1075 }
1076
1077 md.add(column);
1078 }
1079 return md;
1080 }
1081 }