1 10 package org.mmbase.storage.search.implementation.database; 11 12 import java.util.*; 13 import java.sql.*; 14 import java.lang.reflect.Method ; 15 16 import org.mmbase.storage.search.*; 17 import org.mmbase.util.logging.Logger; 18 import org.mmbase.util.logging.Logging; 19 20 import org.mmbase.module.database.MultiConnection; 21 22 44 public class InformixSqlHandler extends BasicSqlHandler implements SqlHandler { 45 46 49 private static Logger log 50 = Logging.getLoggerInstance(InformixSqlHandler.class.getName()); 51 52 55 public InformixSqlHandler() { 56 super(); 57 } 58 59 65 private boolean isUnionQuery(SearchQuery query) { 66 Iterator iSteps = query.getSteps().iterator(); 67 while (iSteps.hasNext()) { 68 Step step = (Step) iSteps.next(); 69 if (step instanceof RelationStep) { 70 RelationStep relationStep = (RelationStep) step; 71 if (relationStep.getDirectionality() == RelationStep.DIRECTIONS_BOTH) { 74 return true; 75 } 76 } 77 } 78 return false; 79 } 80 81 public int getSupportLevel(int feature, SearchQuery query) throws SearchQueryException { 83 int result; 84 switch (feature) { 85 case SearchQueryHandler.FEATURE_MAX_NUMBER: 86 87 if (isUnionQuery(query)) { 88 result = SearchQueryHandler.SUPPORT_NONE; 89 } else { 90 result = SearchQueryHandler.SUPPORT_OPTIMAL; 91 } 92 break; 93 94 default: 95 result = super.getSupportLevel(feature, query); 96 } 97 return result; 98 } 99 100 public String toSql(SearchQuery query, SqlHandler firstInChain) throws SearchQueryException { 102 104 if (query.getSteps().isEmpty()) { 106 throw new IllegalStateException ("Searchquery has no step (at least 1 step is required)."); 107 } 108 if (query.getFields().isEmpty()) { 109 throw new IllegalStateException ("Searchquery has no field (at least 1 field is required)."); 110 } 111 112 if (query.getOffset() != SearchQuery.DEFAULT_OFFSET) { 114 throw new UnsupportedOperationException ("Value of offset other than " 115 + SearchQuery.DEFAULT_OFFSET + " not supported."); 116 } 117 118 StringBuffer sbQuery = new StringBuffer ("SELECT "); 120 121 if (log.isDebugEnabled()) { 122 log.trace("query:" + query.toString()); 123 } 124 125 if (!isUnionQuery(query)) { 126 129 if (query.getSteps().size() > 3) { 130 sbQuery.append("{+ORDERED} "); 131 } 132 133 136 if (query.getMaxNumber() != -1) { 137 sbQuery.append("FIRST "). 138 append(query.getMaxNumber()). 139 append(" "); 140 } 141 } 142 143 if (query.isDistinct() && !query.isAggregating()) { 145 sbQuery.append("DISTINCT "); 146 } 147 148 firstInChain.appendQueryBodyToSql(sbQuery, query, firstInChain); 149 150 return sbQuery.toString(); 151 } 152 153 public void appendQueryBodyToSql(StringBuffer sb, SearchQuery query, SqlHandler firstInChain) 155 throws SearchQueryException { 156 157 StringBuffer sbNodes = new StringBuffer (); 160 161 StringBuffer sbRelations = new StringBuffer (); 164 165 StringBuffer sbGroups = new StringBuffer (); 168 169 boolean multipleSteps = query.getSteps().size() > 1; 170 171 List lFields = new ArrayList(); 173 lFields.addAll(query.getFields()); 174 175 if (query.isDistinct()) { 182 Iterator iSortOrder = query.getSortOrders().iterator(); 183 while (iSortOrder.hasNext()) { 184 SortOrder sortOrder = (SortOrder) iSortOrder.next(); 185 StepField field = sortOrder.getField(); 186 if (lFields.indexOf(field) == -1) { 187 lFields.add(field); 188 } 189 } 190 } 191 192 boolean storesAsFile = org.mmbase.module.core.MMBase.getMMBase().getStorageManagerFactory().hasOption(org.mmbase.storage.implementation.database.Attributes.STORES_BINARY_AS_FILE); 193 Iterator iFields = lFields.iterator(); 194 boolean appended = false; 195 while (iFields.hasNext()) { 196 StepField field = (StepField) iFields.next(); 197 if (field.getType() == org.mmbase.bridge.Field.TYPE_BINARY) continue; 198 if (appended) { 199 sb.append(','); 200 } 201 appended = true; 202 203 Step step = field.getStep(); 205 String fieldName = field.getFieldName(); 206 String fieldAlias = field.getAlias(); 207 208 if (field instanceof AggregatedField) { 209 int aggregationType = ((AggregatedField) field).getAggregationType(); 210 if (aggregationType == AggregatedField.AGGREGATION_TYPE_GROUP_BY) { 211 212 appendField(sb, step, fieldName, multipleSteps); 214 215 if (sbGroups.length() > 0) { 217 sbGroups.append(","); 218 } 219 if (fieldAlias != null) { 220 sbGroups.append(getAllowedValue(fieldAlias)); 221 } else { 222 appendField(sbGroups, step, 223 fieldName, multipleSteps); 224 } 225 } else { 226 227 switch (aggregationType) { 229 case AggregatedField.AGGREGATION_TYPE_COUNT: 230 sb.append("COUNT("); 231 break; 232 233 case AggregatedField.AGGREGATION_TYPE_COUNT_DISTINCT: 234 sb.append("COUNT(DISTINCT "); 235 break; 236 237 case AggregatedField.AGGREGATION_TYPE_MIN: 238 sb.append("MIN("); 239 break; 240 241 case AggregatedField.AGGREGATION_TYPE_MAX: 242 sb.append("MAX("); 243 break; 244 245 default: 246 throw new IllegalStateException ("Invalid aggregationType value: " + aggregationType); 247 } 248 appendField(sb, step, fieldName, multipleSteps); 249 sb.append(")"); 250 } 251 252 } else { 253 254 appendField(sb, step, fieldName, multipleSteps); 256 } 257 258 if (fieldAlias != null) { 260 sb.append(" AS ") 261 .append(getAllowedValue(fieldAlias)); 262 } 263 264 } 265 266 if (log.isDebugEnabled()) { 267 log.trace("Base field part of query : " + sb); 268 } 269 270 List orElements = new ArrayList(); 272 273 StringBuffer andElements = new StringBuffer (); 275 276 sb.append(" FROM "); 278 Iterator iSteps = query.getSteps().iterator(); 279 while (iSteps.hasNext()) { 280 Step step = (Step) iSteps.next(); 281 String tableName = step.getTableName(); 282 String tableAlias = step.getAlias(); 283 284 sb.append(org.mmbase.module.core.MMBase.getMMBase().getBaseName()). 286 append("_"). 287 append(tableName); 293 294 if (tableAlias != null) { 296 sb.append(" "). 297 append(getAllowedValue(tableAlias)); 298 } else { 299 sb.append(" "). 300 append(getAllowedValue(tableName)); 301 } 302 303 if (iSteps.hasNext()) { 304 sb.append(","); 305 } 306 307 SortedSet nodes = step.getNodes(); 309 if (nodes.size() > 0) { 310 if (sbNodes.length() > 0) { 311 sbNodes.append(" AND "); 312 } 313 appendField(sbNodes, step, "number", multipleSteps); 314 sbNodes.append(" IN ("); 315 Iterator iNodes = nodes.iterator(); 316 while (iNodes.hasNext()) { 317 Integer node = (Integer ) iNodes.next(); 318 sbNodes.append(node); 319 if (iNodes.hasNext()) { 320 sbNodes.append(","); 321 } 322 } 323 sbNodes.append(")"); 324 } 325 if (log.isDebugEnabled()) { 326 log.trace("Node constraint string : " + sbNodes); 327 } 328 329 if (step instanceof RelationStep) { 331 RelationStep relationStep = (RelationStep) step; 332 Step previousStep = relationStep.getPrevious(); 333 Step nextStep = relationStep.getNext(); 334 if (sbRelations.length() > 0) { 335 sbRelations.append(" AND "); 336 } 337 switch (relationStep.getDirectionality()) { 338 case RelationStep.DIRECTIONS_SOURCE: 339 sbRelations.append("("); 340 appendField(sbRelations, previousStep, "number", multipleSteps); 341 sbRelations.append("="); 342 appendField(sbRelations, relationStep, "dnumber", multipleSteps); 343 sbRelations.append(" AND "); 344 appendField(sbRelations, nextStep, "number", multipleSteps); 345 sbRelations.append("="); 346 appendField(sbRelations, relationStep, "snumber", multipleSteps); 347 if (relationStep.getCheckedDirectionality()) { 348 sbRelations.append(" AND "); 349 appendField(sbRelations, relationStep, "dir", multipleSteps); 350 sbRelations.append("<>1"); 351 } 352 353 if (andElements.length() > 0) { 355 andElements.append(" AND "); 356 } 357 appendField(andElements, previousStep, "number", multipleSteps); 358 andElements.append("="); 359 appendField(andElements, relationStep, "dnumber", multipleSteps); 360 andElements.append(" AND "); 361 appendField(andElements, nextStep, "number", multipleSteps); 362 andElements.append("="); 363 appendField(andElements, relationStep, "snumber", multipleSteps); 364 if (relationStep.getCheckedDirectionality()) { 365 andElements.append(" AND "); 366 appendField(andElements, relationStep, "dir", multipleSteps); 367 andElements.append("<>1"); 368 } 369 370 break; 371 372 case RelationStep.DIRECTIONS_DESTINATION: 373 sbRelations.append("("); 374 appendField(sbRelations, previousStep, "number", multipleSteps); 375 sbRelations.append("="); 376 appendField(sbRelations, relationStep, "snumber", multipleSteps); 377 sbRelations.append(" AND "); 378 appendField(sbRelations, nextStep, "number", multipleSteps); 379 sbRelations.append("="); 380 appendField(sbRelations, relationStep, "dnumber", multipleSteps); 381 382 if (andElements.length() > 0) { 384 andElements.append(" AND "); 385 } 386 387 appendField(andElements, previousStep, "number", multipleSteps); 388 andElements.append("="); 389 appendField(andElements, relationStep, "snumber", multipleSteps); 390 andElements.append(" AND "); 391 appendField(andElements, nextStep, "number", multipleSteps); 392 andElements.append("="); 393 appendField(andElements, relationStep, "dnumber", multipleSteps); 394 395 break; 396 397 case RelationStep.DIRECTIONS_BOTH: 398 399 if (relationStep.getRole() != null) { 400 sbRelations.append("((("); 401 } else { 402 sbRelations.append("(("); 403 } 404 405 appendField(sbRelations, previousStep, "number", multipleSteps); 406 sbRelations.append("="); 407 appendField(sbRelations, relationStep, "dnumber", multipleSteps); 408 sbRelations.append(" AND "); 409 appendField(sbRelations, nextStep, "number", multipleSteps); 410 sbRelations.append("="); 411 appendField(sbRelations, relationStep, "snumber", multipleSteps); 412 if (relationStep.getCheckedDirectionality()) { 413 sbRelations.append(" AND "); 414 appendField(sbRelations, relationStep, "dir", multipleSteps); 415 sbRelations.append("<>1"); 416 } 417 sbRelations.append(") OR ("); 418 419 appendField(sbRelations, previousStep, "number", multipleSteps); 420 sbRelations.append("="); 421 appendField(sbRelations, relationStep, "snumber", multipleSteps); 422 sbRelations.append(" AND "); 423 appendField(sbRelations, nextStep, "number", multipleSteps); 424 sbRelations.append("="); 425 appendField(sbRelations, relationStep, "dnumber", multipleSteps); 426 if (relationStep.getRole() != null) { 427 sbRelations.append("))"); 428 } else { 429 sbRelations.append(")"); 430 } 431 432 StringBuffer orElement = new StringBuffer (); 435 orElement.append("("); 436 437 appendField(orElement, previousStep, "number", multipleSteps); 438 orElement.append("="); 439 appendField(orElement, relationStep, "dnumber", multipleSteps); 440 orElement.append(" AND "); 441 appendField(orElement, nextStep, "number", multipleSteps); 442 orElement.append("="); 443 appendField(orElement, relationStep, "snumber", multipleSteps); 444 if (relationStep.getCheckedDirectionality()) { 445 orElement.append(" AND "); 446 appendField(orElement, relationStep, "dir", multipleSteps); 447 orElement.append("<>1"); 448 } 449 450 if (relationStep.getRole() != null) { 451 orElement.append(" AND "); 453 appendField(orElement, relationStep, "rnumber", multipleSteps); 454 orElement.append("="). 455 append(relationStep.getRole()); 456 } 457 458 orElement.append(")"); 459 orElements.add(orElement); 460 461 orElement = new StringBuffer (); 463 orElement.append("("); 464 465 appendField(orElement, previousStep, "number", multipleSteps); 466 orElement.append("="); 467 appendField(orElement, relationStep, "snumber", multipleSteps); 468 orElement.append(" AND "); 469 appendField(orElement, nextStep, "number", multipleSteps); 470 orElement.append("="); 471 appendField(orElement, relationStep, "dnumber", multipleSteps); 472 473 if (relationStep.getRole() != null) { 474 orElement.append(" AND "); 476 appendField(orElement, relationStep, "rnumber", multipleSteps); 477 orElement.append("="). 478 append(relationStep.getRole()); 479 } 480 481 orElement.append(")"); 482 orElements.add(orElement); 483 484 break; 486 487 case RelationStep.DIRECTIONS_ALL: 488 throw new UnsupportedOperationException ("Directionality 'ALL' is not (yet) supported"); 489 490 case RelationStep.DIRECTIONS_EITHER: 491 throw new UnsupportedOperationException ("Directionality 'EITHER' is not (yet) supported"); 492 493 default: throw new IllegalStateException ("Invalid directionality value: " + relationStep.getDirectionality()); 495 } 496 if (relationStep.getRole() != null) { 497 sbRelations.append(" AND "); 498 appendField(sbRelations, relationStep, "rnumber", multipleSteps); 499 sbRelations.append("="). 500 append(relationStep.getRole()); 501 } 502 sbRelations.append(")"); 503 } 504 } 505 506 if (log.isDebugEnabled()) { 507 log.trace("Relation string : " + sbRelations); 508 } 509 510 StringBuffer sbConstraints = new StringBuffer (); 512 sbConstraints.append(sbNodes); if (sbConstraints.length() > 0 && sbRelations.length() > 0) { 514 sbConstraints.append(" AND "); 515 } 516 517 526 StringBuffer unionRelationConstraints = new StringBuffer (); 527 if (isUnionQuery(query)) { 528 StringBuffer unionConstraints = new StringBuffer (); 532 if (query.getConstraint() != null) { 533 Constraint constraint = query.getConstraint(); 534 if (sbConstraints.length() > 0) { 535 log.info("sbConstraints:" + sbConstraints); 538 log.info("sbConstraints.length:" + sbConstraints.length()); 539 540 if (sbConstraints.length() >= 4) { 542 if (!sbConstraints.substring(sbConstraints.length() - 4, sbConstraints.length()).equals("AND ")) { 543 unionConstraints.append(" AND "); 544 } 545 } 546 547 if (constraint instanceof CompositeConstraint) { 548 appendCompositeConstraintToSql(unionConstraints, (CompositeConstraint) constraint, 549 query, false, true, firstInChain); 550 } else { 551 firstInChain.appendConstraintToSql(unionConstraints, constraint, query, 552 false, true); 553 } 554 } else { 555 if (constraint instanceof CompositeConstraint) { 557 appendCompositeConstraintToSql(unionConstraints, (CompositeConstraint) constraint, 558 query, false, false, firstInChain); 559 } else { 560 firstInChain.appendConstraintToSql(unionConstraints, constraint, query, 561 false, false); 562 } 563 } 564 } 565 566 if (log.isDebugEnabled()) { 567 log.trace("Union constraint : " + unionConstraints); 568 } 569 570 574 575 if (unionConstraints.length() > 0) { 576 unionConstraints.insert(0, " AND "); 577 } 578 579 List combinedElements = new ArrayList(); 580 boolean skipCombination = false; 581 for (int counter = 0; counter < orElements.size(); counter++) { 582 for (int counter2 = counter; counter2 < orElements.size(); counter2++) { 583 if (counter%2==0 && counter2-counter==1) { 585 skipCombination=true; 587 } else { 588 skipCombination=false; 589 } 590 if (counter != counter2 && orElements.size() > 2) { 593 if (!skipCombination) { 595 combinedElements.add(orElements.get(counter) + " AND " + orElements.get(counter2) + unionConstraints); 596 } 597 } else { 598 if (counter == counter2 && orElements.size() <= 2) { 601 combinedElements.add(orElements.get(counter) + "" + unionConstraints); 602 } 603 } 604 } 605 } 606 607 611 Iterator e = combinedElements.iterator(); 612 String combinedElement = ""; 613 int teller = 1; 614 615 StringBuffer baseQuery = new StringBuffer (); 617 baseQuery.append(sb); 619 if (sbConstraints.length() + combinedElements.size() > 0) { 621 baseQuery.append(" WHERE "). 622 append(sbConstraints.toString()); 623 } 624 625 if (log.isDebugEnabled()) { 626 log.trace("Base query including fields and tables : " + sb); 627 } 628 629 while (e.hasNext()) { 631 combinedElement = (String ) e.next(); 632 if (teller != 1) { 633 if (sb.indexOf("COUNT") > -1) { 634 unionRelationConstraints.append(" UNION ALL ").append(baseQuery); 635 } else { 636 unionRelationConstraints.append(" UNION ").append(baseQuery); 637 } 638 } 639 640 if (unionRelationConstraints.length() >= 4) { 642 if (!unionRelationConstraints.substring(unionRelationConstraints.length() - 4, unionRelationConstraints.length()).equals("AND ") && !unionRelationConstraints.substring(unionRelationConstraints.length() - 6, unionRelationConstraints.length()).equals("WHERE ")) { 643 unionRelationConstraints.append(" AND "); 644 } 645 } 646 647 if (andElements.length() > 0) { 648 unionRelationConstraints.append(andElements).append(" AND "); 649 } 650 651 unionRelationConstraints.append(" " + combinedElement + " "); 652 653 if (log.isDebugEnabled()) { 654 log.trace("Union relation constraint " + teller + " : " + unionRelationConstraints); 655 } 656 teller++; 657 } 658 659 if (sbConstraints.length() > 0) { 661 sb.append(" WHERE ").append(sbConstraints.toString()).append(unionRelationConstraints); 662 } else { 663 sb.append(" WHERE ").append(unionRelationConstraints); 664 } 665 666 669 676 677 681 List sortOrders = query.getSortOrders(); 682 if (sortOrders.size() > 0) { 683 sb.append(" ORDER BY "); 684 Iterator iSortOrders = sortOrders.iterator(); 685 while (iSortOrders.hasNext()) { 686 SortOrder sortOrder = (SortOrder) iSortOrders.next(); 687 688 String fieldAlias = sortOrder.getField().getAlias(); 690 Step step = sortOrder.getField().getStep(); 691 StringBuffer orderByField = new StringBuffer (); 692 if (fieldAlias != null) { 693 orderByField.append(getAllowedValue(fieldAlias)); 694 } else { 695 appendField(orderByField, step, sortOrder.getField().getFieldName(), multipleSteps); 696 } 697 698 boolean found = false; 700 for (int i = 0; i < query.getFields().size(); i++) { 701 StepField sf = (StepField) query.getFields().get(i); 702 String field = sf.getStep().getAlias() + "." + sf.getFieldName(); 703 704 if (field.equals(orderByField.toString())) { 706 sb.append((i + 1) + " "); 708 found = true; 710 break; 711 } 712 } 713 if (! found) { 714 throw new RuntimeException ("Could not find the field " + orderByField + " in " + query.getFields() + " !"); 715 } 716 717 718 switch (sortOrder.getDirection()) { 720 case SortOrder.ORDER_ASCENDING: 721 sb.append(" ASC"); 722 break; 723 724 case SortOrder.ORDER_DESCENDING: 725 sb.append(" DESC"); 726 break; 727 728 default: throw new IllegalStateException ("Invalid direction value: " + sortOrder.getDirection()); 730 } 731 732 if (iSortOrders.hasNext()) { 733 sb.append(","); 734 } 735 } 736 } 737 log.debug("Completed generation of UNION query:" + sb.toString()); 738 } else { 739 sbConstraints.append(sbRelations); if (query.getConstraint() != null) { 741 Constraint constraint = query.getConstraint(); 742 if (sbConstraints.length() > 0) { 743 sbConstraints.append(" AND "); 745 if (constraint instanceof CompositeConstraint) { 746 appendCompositeConstraintToSql(sbConstraints, (CompositeConstraint) constraint, 747 query, false, true, firstInChain); 748 } else { 749 firstInChain.appendConstraintToSql(sbConstraints, constraint, query, 750 false, true); 751 } 752 } else { 753 if (constraint instanceof CompositeConstraint) { 755 appendCompositeConstraintToSql(sbConstraints, (CompositeConstraint) constraint, 756 query, false, false, firstInChain); 757 } else { 758 firstInChain.appendConstraintToSql(sbConstraints, constraint, query, 759 false, false); 760 } 761 } 762 } 763 if (sbConstraints.length() > 0) { 764 sb.append(" WHERE "). 765 append(sbConstraints.toString()); 766 } 767 768 if (sbGroups.length() > 0) { 770 sb.append(" GROUP BY "). 771 append(sbGroups.toString()); 772 } 773 774 List sortOrders = query.getSortOrders(); 776 if (sortOrders.size() > 0) { 777 sb.append(" ORDER BY "); 778 Iterator iSortOrders = sortOrders.iterator(); 779 while (iSortOrders.hasNext()) { 780 SortOrder sortOrder = (SortOrder) iSortOrders.next(); 781 782 String fieldAlias = sortOrder.getField().getAlias(); 784 Step step = sortOrder.getField().getStep(); 785 if (fieldAlias != null) { 786 sb.append(getAllowedValue(fieldAlias)); 787 } else { 788 appendField(sb, step, sortOrder.getField().getFieldName(), multipleSteps); 789 } 790 791 switch (sortOrder.getDirection()) { 793 case SortOrder.ORDER_ASCENDING: 794 sb.append(" ASC"); 795 break; 796 797 case SortOrder.ORDER_DESCENDING: 798 sb.append(" DESC"); 799 break; 800 801 default: throw new IllegalStateException ("Invalid direction value: " + sortOrder.getDirection()); 803 } 804 805 if (iSortOrders.hasNext()) { 806 sb.append(","); 807 } 808 } 809 } 810 log.debug("Completed generation of query:" + sb.toString()); 811 } 812 } 813 814 819 protected void closeConnection(Connection con, Statement stmt) { 820 try { 821 if (stmt != null) { 822 stmt.close(); 823 } 824 } catch (Exception g) {} 825 try { 826 if (con != null) { 827 if (con instanceof MultiConnection) { 828 closeInformix((MultiConnection)con); 829 } 830 con.close(); 831 } 832 } catch (Exception g) {} 833 } 834 835 private void closeInformix(MultiConnection activeConnection) { 836 Connection con = activeConnection.getRealConnection(); 837 try { 838 Method scrub = Class.forName("com.informix.jdbc.IfxConnection").getMethod("scrubConnection", null); 839 scrub.invoke(con, null); 840 } catch (Exception e) { 841 log.error("Exception while calling releaseBlob(): " + e.getMessage()); 842 } 843 } 844 } 845 | Popular Tags |