1 19 package org.netbeans.modules.db.sql.visualeditor.querybuilder; 20 21 import java.util.ArrayList ; 22 import java.util.List ; 23 import java.util.Collection ; 24 25 import org.openide.ErrorManager; 26 27 import org.netbeans.modules.db.sql.visualeditor.Log; 28 29 import org.netbeans.modules.db.sql.visualeditor.querymodel.QueryItem; 30 import org.netbeans.modules.db.sql.visualeditor.querymodel.Query; 31 import org.netbeans.modules.db.sql.visualeditor.querymodel.Select; 32 import org.netbeans.modules.db.sql.visualeditor.querymodel.From; 33 import org.netbeans.modules.db.sql.visualeditor.querymodel.Where; 34 import org.netbeans.modules.db.sql.visualeditor.querymodel.GroupBy; 35 import org.netbeans.modules.db.sql.visualeditor.querymodel.OrderBy; 36 import org.netbeans.modules.db.sql.visualeditor.querymodel.Having; 37 import org.netbeans.modules.db.sql.visualeditor.querymodel.JoinTable; 38 import org.netbeans.modules.db.sql.visualeditor.querymodel.Table; 39 import org.netbeans.modules.db.sql.visualeditor.querymodel.Column; 40 import org.netbeans.modules.db.sql.visualeditor.querymodel.Expression; 41 import org.netbeans.modules.db.sql.visualeditor.querymodel.ColumnProvider; 42 import org.netbeans.modules.db.sql.visualeditor.querymodel.Predicate; 43 import org.netbeans.modules.db.sql.visualeditor.querymodel.SQLQueryFactory; 44 import org.netbeans.modules.db.sql.visualeditor.querymodel.Value; 45 import org.netbeans.modules.db.sql.visualeditor.querymodel.And; 46 import org.netbeans.modules.db.sql.visualeditor.querymodel.Or; 47 import org.netbeans.modules.db.sql.visualeditor.querymodel.ExpressionList; 48 49 class QueryModel { 50 51 53 private Query _query = null; 55 static boolean DEBUG = false; 56 57 58 60 QueryModel() { 61 } 62 63 65 void parse (String cmd) 67 throws org.netbeans.modules.db.sql.visualeditor.parser.ParseException 68 { 69 _query = SQLQueryFactory.parse(cmd); 70 } 71 72 73 75 String genText() { 76 77 Log.err.log(ErrorManager.INFORMATIONAL, "Entering QueryModel.genText"); if (QueryModel.DEBUG) 79 (new Throwable ()).printStackTrace(); 80 if ( _query == null ) 81 return null; 82 else 83 return (_query.genText()); 84 } 85 86 87 89 91 Select getSelect() { 92 return _query.getSelect(); 93 } 94 95 From getFrom() { 96 return _query.getFrom(); 97 } 98 99 Where getWhere() { 100 return (_query == null) ? null : _query.getWhere(); 103 } 104 105 GroupBy getGroupBy() { 106 return _query.getGroupBy(); 107 } 108 109 OrderBy getOrderBy() { 110 return _query.getOrderBy(); 111 } 112 113 Having getHaving() { 114 return _query.getHaving(); 115 } 116 117 void setSelect(Select select) { 118 _query.setSelect(select); 119 } 120 121 void setFrom(From from) { 122 _query.setFrom(from); 123 } 124 125 void setWhere(Where where) { 126 _query.setWhere(where); 127 } 128 129 void setGroupBy(GroupBy groupBy) { 130 _query.setGroupBy(groupBy); 131 } 132 133 void setOrderBy(OrderBy orderBy) { 134 _query.setOrderBy(orderBy); 135 } 136 137 void setHaving(Having having) { 138 _query.setHaving(having); 139 } 140 141 143 void removeGroupBy() 145 { 146 this.setGroupBy(null); 147 } 148 149 boolean hasGroupBy() 151 { 152 return (_query.getGroupBy() != null); 153 } 154 155 void insertTable(JoinTable joinTable) { 157 _query.getFrom().addTable(joinTable); 158 } 159 160 161 163 166 void removeTable (String tableSpec) { 167 Log.err.log(ErrorManager.INFORMATIONAL, "Entering QueryModel.removeTable, tableSpec: " + tableSpec); _query.removeTable(tableSpec); 169 } 170 171 172 175 void replaceStar(ColumnProvider tableReader) { 176 177 Log.err.log(ErrorManager.INFORMATIONAL, "Entering QueryModel.replaceStar"); _query.replaceStar(tableReader); 179 } 180 181 void addColumn(String tableSpec, String columnName) { 184 _query.addColumn(tableSpec, columnName); 185 } 186 187 void removeColumn(String tableSpec, String columnName) { 188 _query.removeColumn(tableSpec, columnName); 189 } 190 191 Expression findCond(String table1, String column1, String table2, String column2) { 193 return _query.getWhere().findExpression(table1, column1, table2, column2); 194 } 195 196 197 Table findTable(String tableSpec) { 199 if ((_query != null) && (_query.getFrom() != null) ) 200 return _query.getFrom().findTable(tableSpec); 201 else 202 return null; 203 } 204 205 206 Column findSelectColumn(String tableSpec, String columnName) { 207 ArrayList columns = new ArrayList (); 208 _query.getSelect().getReferencedColumns(columns); 209 for (int i=0; i<columns.size(); i++) { 210 Column col = (Column)columns.get(i); 211 if (col.matches(tableSpec, columnName)) 212 return col; 213 } 214 return null; 215 } 216 217 String getFullTableName(String corrName) { 221 if ((_query != null) && (_query.getFrom() != null) && (corrName != null)) 222 return _query.getFrom().getFullTableName(corrName); 223 else 224 return null; 225 } 226 227 228 230 String genUniqueName(String fullTableName) { 231 232 if (findTable(fullTableName)==null) 233 return null; 235 else 236 { 237 String [] table = fullTableName.split("\\."); 239 String tableName=(table.length>1) ? table[1] : table[0]; 240 String candidate; 241 for (int i=1;;i++) { 242 243 candidate = tableName+"_"+i; 252 253 if (findTable(candidate)==null) 254 return candidate; 256 } 257 } 258 } 259 260 void addSortSpecification(String tableSpec, String columnName, String direction, int order) { 262 OrderBy orderBy = this.getOrderBy(); 263 if (orderBy == null) { 264 orderBy = SQLQueryFactory.createOrderBy(); 265 this.setOrderBy(orderBy); 266 } 267 268 orderBy.removeSortSpecification(tableSpec, columnName); 270 271 orderBy.addSortSpecification(tableSpec, columnName, direction, order); 273 } 274 275 void removeSortSpecification(String tableSpec, String columnName) { 277 OrderBy ob = this.getOrderBy(); 278 if (ob != null) 279 ob.removeSortSpecification(tableSpec, columnName); 280 } 281 282 283 286 int getSortCount() { 287 OrderBy orderBy = this.getOrderBy(); 288 if (orderBy==null) 289 return 0; 290 else 291 return orderBy.getSortSpecificationCount(); 292 } 293 294 295 void setDerivedColName(String tableSpec, String columnName, String derivedColName) { 297 Column col = findSelectColumn(tableSpec, columnName); 298 if (col!=null) 299 col.setDerivedColName(derivedColName); 300 } 301 302 void removeDerivedColName(String tableSpec, String columnName) { 304 setDerivedColName(tableSpec, columnName, null); 305 } 306 307 310 boolean isParameterized() { 311 Where where = this.getWhere(); 315 if (where==null) 316 return false; 317 else 318 return where.isParameterized(); 319 320 } 321 322 327 void getParameterizedPredicates(Collection predicates) { 328 Where where = this.getWhere(); 330 if (where==null) 331 return; 332 else { 333 Expression expr = where.getExpression(); 334 getParameterized (expr, predicates); 335 } 336 return; 337 } 338 339 private void getParameterized (Expression expr, Collection predicates) { 340 if ( expr instanceof ExpressionList ) { 341 for (int i=0; i<((ExpressionList)expr).size();i++) { 342 Expression e = ((ExpressionList)expr).getExpression(i); 343 getParameterized (e, predicates); 344 } 345 } 346 else if ( expr.isParameterized() && (expr instanceof Predicate) ) { 347 Predicate pred = (Predicate) expr; 348 Value val1 = pred.getVal1(); 349 Value val2 = pred.getVal2(); 350 if ( val1.isParameterized() ) { 354 String val1String = val1.genText(); 355 for (int i=0; i<val1String.length(); i++) { 356 if (val1String.charAt(i) == '?' ) 357 predicates.add(val2.genText()); 358 } 359 } 360 else if ( val2.isParameterized() ) { 361 String val2String = val2.genText(); 362 for (int i=0; i<val2String.length(); i++) { 363 if (val2String.charAt(i) == '?' ) 364 predicates.add(val1.genText()); 365 } 366 } 367 } 368 369 } 370 371 376 void renameTableSpec(String oldTableSpec, String corrName) { 377 Log.err.log(ErrorManager.INFORMATIONAL, 380 "Entering QueryModel.renameTableSpec, oldTableSpec: " + oldTableSpec + " corrName: " + corrName); 383 _query.renameTableSpec(oldTableSpec, corrName); 384 } 385 386 387 JoinTable findJoinTable(String table1, String column1, String table2, String column2) { 391 return _query.getFrom().findJoinTable(table1, column1, table2, column2); 392 } 393 394 public void getColumnNames (String tableSpec, Collection columnNames) { 395 ArrayList columns = new ArrayList (); 396 this.getSelect().getReferencedColumns(columns); 397 for (int i=0; i<columns.size(); i++) { 398 Object element = columns.get(i); 399 if (element instanceof Column) { 400 Column col = (Column) element; 401 if (col != null) { 402 String colName = col.getColumnName(); 403 String tabSpec = col.getTableSpec(); 404 if ( (colName != null) && (tabSpec != null) ) 405 if (colName.equals("*") || tabSpec.equals(tableSpec)) columnNames.add(colName); 407 } 408 } 409 } 410 } 411 412 void addGroupBy() 415 { 416 ArrayList columns = new ArrayList (); 417 this.getSelect().getReferencedColumns(columns); 418 this.setGroupBy(SQLQueryFactory.createGroupBy(columns)); 419 } 420 421 void addRelationships(JoinTable joinTable, List relationships) { 425 426 if (relationships.isEmpty()) 427 return; 428 else { 429 String [] rel = (String []) relationships.get(0); 432 433 435 String corrName1 = _query.getFrom().getTableSpec(rel[0]); 436 String corrName2 = _query.getFrom().getTableSpec(rel[2]); 437 438 String lastTableFullName = _query.getFrom().getPreviousTableFullName(); 439 if ((rel[0].equals(lastTableFullName)) || rel[2].equals(lastTableFullName)) { 440 joinTable.addJoinCondition(rel); 441 448 } 449 else 450 addOrCreateAndExpression(SQLQueryFactory.createPredicate(rel)); 452 453 for (int i=1; i<relationships.size(); i++) { 454 455 rel = (String []) relationships.get(i); 457 addOrCreateAndExpression(SQLQueryFactory.createPredicate(rel)); 458 } 459 } 460 } 461 462 void removeCondition (Predicate removePred) { 467 468 Where where = this.getWhere(); 469 470 if ( where == null || where.getExpression() == null ) 471 return; 472 else { 473 Expression expr = this.getWhere().getExpression(); 474 expr = this.removeConditionFromExpression(removePred, expr); 475 if ( expr == null ) { 476 where.resetExpression(); 477 } 478 else { 479 where.replaceExpression(expr); 480 } 481 } 482 } 483 484 private Expression removeConditionFromExpression (Predicate removePred, Expression expr) { 488 Value removeVal1 = removePred.getVal1(); 489 Value removeVal2 = removePred.getVal2(); 490 491 if (expr instanceof Predicate) { 492 Predicate pred = (Predicate) expr; 493 Value val1 = pred.getVal1(); 494 Value val2 = pred.getVal2(); 495 496 if ( (val1 instanceof Column) && 497 (val2 instanceof Column) && 498 (removeVal1 instanceof Column) && 499 (removeVal2 instanceof Column) ) { 500 Column col1 = (Column)val1; 501 Column col2 = (Column)val2; 502 Column removeCol1 = (Column)removeVal1; 503 Column removeCol2 = (Column)removeVal2; 504 if ( col1.equals(removeCol1) && col2.equals(removeCol2) ) { 505 return null; 506 } 507 } 508 } 509 else if ( expr instanceof ExpressionList ) { 510 511 ExpressionList exprList = (ExpressionList) expr; 512 for (int i=exprList.size()-1; i>=0 ; i--) { 513 Expression element = this.removeConditionFromExpression(removePred, exprList.getExpression(i)); 514 if ( element == null ) { 515 exprList.removeExpression(i); 516 } 517 else { 518 exprList.replaceExpression(i, element); 519 } 520 } 521 522 int size = exprList.size(); 524 if ( size == 0 ) { 525 return null; 526 } 527 else if ( size == 1 ) { 528 return exprList.getExpression(0); 529 } 530 } 531 return expr; 532 } 533 534 537 int getCriteriaCount() { 538 Where where = this.getWhere(); 539 if (where != null) { 540 Expression expr = where.getExpression(); 541 if (expr != null) { 542 if (expr instanceof And) 543 return ((ExpressionList)expr).size(); 544 else 545 return 1; 546 } 547 } 548 return 0; 549 } 550 551 void removeJoinNode ( String table1, String column1, String table2, String column2) { 553 if ( this.getFrom() != null ) { 554 JoinTable jt = this.getFrom().findJoinTable ( table1, column1, table2, column2 ); 555 if ( jt != null ) { 556 jt.setJoinType (null); 558 jt.setExpression (null); 560 } 561 } 562 563 } 564 565 void removeCriteria (String tableSpec, String columnName, int recurseLevel) { 567 Where where = this.getWhere(); 568 if (where != null) { 569 570 Expression expr = where.getExpression(); 571 if ( expr == null ) 572 return; 573 574 expr = removeCriteriaFromExpression(tableSpec, columnName, expr, recurseLevel); 575 if ( expr == null ) 576 where.resetExpression(); 577 else 578 where.replaceExpression(expr); 579 } 580 } 581 582 private Expression removeCriteriaFromExpression(String tableSpec, String columnName, Expression expr, int recurseLevel) { 586 if (expr instanceof Predicate) { 587 588 Predicate pred = (Predicate) expr; 589 Value val1 = pred.getVal1(); 590 Value val2 = pred.getVal2(); 591 592 if (pred.isCriterion()) { 594 if (((val1 instanceof Column) && ((Column)val1).matches(tableSpec, columnName)) 595 || ((val2 instanceof Column) && ((Column)val2).matches(tableSpec, columnName))) 596 { 597 return null; 599 } 600 } 601 } 602 else if ( recurseLevel > 0 && expr instanceof ExpressionList ) { 603 604 recurseLevel--; 605 ExpressionList exprList = (ExpressionList) expr; 606 for (int i=exprList.size()-1; i>=0; i--) { 607 Expression item = removeCriteriaFromExpression(tableSpec, columnName, exprList.getExpression(i), recurseLevel); 608 if (item == null) { 609 exprList.removeExpression(i); 610 } 611 else { 612 exprList.replaceExpression(i, item); 613 } 614 } 615 616 int size = exprList.size(); 617 if ( size == 0 ) { 618 return null; 620 } 621 else if ( size == 1 ) { 622 return exprList.getExpression(0); 623 } 624 } 625 626 return expr; 627 } 628 629 631 void addCriteria(String tableSpec, String columnName, Predicate pred) { 632 633 if (QueryModel.DEBUG) 634 System.out.println("Entering QueryModel.addCriteriaOrder () tableSpec = " + tableSpec + " ColumnName = " + columnName + " Predicate = " + pred + "\n"); 638 Where where = this.getWhere(); 639 if (where==null) { 640 this.setWhere(SQLQueryFactory.createWhere(pred)); 641 } 642 else 643 { 644 Expression expr = where.getExpression(); 645 if (expr == null) { 646 where.replaceExpression(pred); 648 } 649 else { 650 addOrCreateAndExpression(pred); 651 } 652 } 653 } 654 655 void replaceCriteria(String tableSpec, String columnName, Predicate pred, int order) { 656 657 order--; if (QueryModel.DEBUG) 659 System.out.println("Entering QueryModel.replaceCriteria () tableSpec = " + tableSpec + " ColumnName = " + columnName + " Predicate = " + pred + " Order = " + order + "\n"); 663 Where where = this.getWhere(); 664 Expression expr = where.getExpression(); 665 if (expr instanceof ExpressionList) 666 ((ExpressionList)expr).replaceExpression(order, pred); 667 else { 668 where.replaceExpression(pred); 670 } 671 } 672 673 void addCriteria(String tableSpec, String columnName, Predicate pred, int order) { 674 675 order--; if (QueryModel.DEBUG) 677 System.out.println("Entering QueryModel.addCriteriaOrder () tableSpec = " + tableSpec + " ColumnName = " + columnName + " Predicate = " + pred + " Order = " + order + "\n"); 681 Where where = this.getWhere(); 682 Expression expr = where.getExpression(); 683 if (expr instanceof And) { 684 ((ExpressionList)expr).addExpression(order, pred); 685 } 686 else { 687 if (expr == null) 688 where.replaceExpression(pred); 689 else { 690 if (order == 0) 691 expr = SQLQueryFactory.createAnd(pred, expr); 692 else 693 expr = SQLQueryFactory.createAnd(expr, pred); 694 this.getWhere().replaceExpression(expr); 695 } 696 } 697 } 698 699 void addOrCreateAndExpression (Predicate pred) { 702 if (this.getWhere()!=null) { 703 Expression expr = this.getWhere().getExpression(); 704 if (expr != null) { 705 if (expr instanceof And) { 706 And andExpr = (And)expr; 707 andExpr.addExpression(pred); 708 } 709 else { 710 expr = SQLQueryFactory.createAnd(expr, pred); 711 this.getWhere().replaceExpression(expr); 712 } 713 return; 714 } 715 } 716 this.setWhere(SQLQueryFactory.createWhere(pred)); 717 } 718 719 void addOrCreateOrExpression (Predicate pred) { 722 if (this.getWhere()!=null) { 723 Expression expr = this.getWhere().getExpression(); 724 if (expr != null) { 725 if (expr instanceof Or) { 726 Or orExpr = (Or)expr; 727 orExpr.addExpression(pred); 728 return; 729 } 730 else { 731 expr = SQLQueryFactory.createOr(expr, pred); 732 this.getWhere().replaceExpression(expr); 733 return; 734 } 735 } 736 } 737 this.setWhere(SQLQueryFactory.createWhere(pred)); 738 } 739 740 } 741 742 743 744 | Popular Tags |