1 package org.apache.torque.task; 2 3 18 19 import java.io.BufferedOutputStream ; 20 import java.io.BufferedReader ; 21 import java.io.IOException ; 22 import java.io.InputStreamReader ; 23 import java.io.File ; 24 import java.io.FileInputStream ; 25 import java.io.FileOutputStream ; 26 import java.io.FileReader ; 27 import java.io.PrintStream ; 28 import java.io.StringReader ; 29 import java.io.Reader ; 30 import java.util.List ; 31 import java.util.ArrayList ; 32 import java.util.Iterator ; 33 import java.util.HashMap ; 34 import java.util.Map ; 35 import java.util.Properties ; 36 import java.sql.Connection ; 37 import java.sql.DatabaseMetaData ; 38 import java.sql.Driver ; 39 import java.sql.ResultSet ; 40 import java.sql.ResultSetMetaData ; 41 import java.sql.SQLException ; 42 import java.sql.SQLWarning ; 43 import java.sql.Statement ; 44 import org.apache.commons.lang.StringUtils; 45 import org.apache.tools.ant.AntClassLoader; 46 import org.apache.tools.ant.BuildException; 47 import org.apache.tools.ant.Project; 48 import org.apache.tools.ant.ProjectHelper; 49 import org.apache.tools.ant.Task; 50 import org.apache.tools.ant.types.EnumeratedAttribute; 51 import org.apache.tools.ant.types.Path; 52 import org.apache.tools.ant.types.Reference; 53 54 65 public class TorqueSQLExec extends Task 66 { 67 private int goodSql = 0; 68 private int totalSql = 0; 69 private Path classpath; 70 private AntClassLoader loader; 71 72 75 public static class DelimiterType extends EnumeratedAttribute 76 { 77 public static final String NORMAL = "normal"; 78 public static final String ROW = "row"; 79 80 public String [] getValues() 81 { 82 return new String [] {NORMAL, ROW}; 83 } 84 } 85 86 87 private Connection conn = null; 88 89 90 private boolean autocommit = false; 91 92 93 private Statement statement = null; 94 95 96 private String driver = null; 97 98 99 private String url = null; 100 101 102 private String userId = null; 103 104 105 private String password = null; 106 107 108 private String sqlCommand = ""; 109 110 111 private String delimiter = ";"; 112 113 117 private String delimiterType = DelimiterType.NORMAL; 118 119 120 private boolean print = false; 121 122 123 private boolean showheaders = true; 124 125 126 private File output = null; 127 128 129 private String rdbms = null; 130 131 132 private String version = null; 133 134 135 private String onError = "abort"; 136 137 138 private String encoding = null; 139 140 141 private String srcDir; 142 143 144 private File sqldbmap; 145 146 151 public void setSqlDbMap(String sqldbmap) 152 { 153 this.sqldbmap = project.resolveFile(sqldbmap); 154 } 155 156 161 public File getSqlDbMap() 162 { 163 return sqldbmap; 164 } 165 166 171 public void setSrcDir(String srcDir) 172 { 173 this.srcDir = project.resolveFile(srcDir).toString(); 174 } 175 176 181 public String getSrcDir() 182 { 183 return srcDir; 184 } 185 186 191 public void setClasspath(Path classpath) 192 { 193 if (this.classpath == null) 194 { 195 this.classpath = classpath; 196 } 197 else 198 { 199 this.classpath.append(classpath); 200 } 201 } 202 203 208 public Path createClasspath() 209 { 210 if (this.classpath == null) 211 { 212 this.classpath = new Path(project); 213 } 214 return this.classpath.createPath(); 215 } 216 217 222 public void setClasspathRef(Reference r) 223 { 224 createClasspath().setRefid(r); 225 } 226 227 232 public void addText(String sql) 233 { 234 this.sqlCommand += sql; 235 } 236 237 242 public void setDriver(String driver) 243 { 244 this.driver = driver; 245 } 246 247 252 public void setUrl(String url) 253 { 254 this.url = url; 255 } 256 257 262 public void setUserid(String userId) 263 { 264 this.userId = userId; 265 } 266 267 272 public void setEncoding(String encoding) 273 { 274 this.encoding = encoding; 275 } 276 277 282 public void setPassword(String password) 283 { 284 this.password = password; 285 } 286 287 292 public void setAutocommit(boolean autocommit) 293 { 294 this.autocommit = autocommit; 295 } 296 297 305 public void setDelimiter(String delimiter) 306 { 307 this.delimiter = delimiter; 308 } 309 310 318 public void setDelimiterType(DelimiterType delimiterType) 319 { 320 this.delimiterType = delimiterType.getValue(); 321 } 322 323 328 public void setPrint(boolean print) 329 { 330 this.print = print; 331 } 332 333 338 public void setShowheaders(boolean showheaders) 339 { 340 this.showheaders = showheaders; 341 } 342 343 348 public void setOutput(File output) 349 { 350 this.output = output; 351 } 352 353 358 public void setRdbms(String vendor) 359 { 360 this.rdbms = vendor.toLowerCase(); 361 } 362 363 368 public void setVersion(String version) 369 { 370 this.version = version.toLowerCase(); 371 } 372 373 378 public void setOnerror(OnError action) 379 { 380 this.onError = action.getValue(); 381 } 382 383 388 public void execute() throws BuildException 389 { 390 sqlCommand = sqlCommand.trim(); 391 392 if (sqldbmap == null || getSqlDbMap().exists() == false) 393 { 394 throw new BuildException("You haven't provided an sqldbmap, or " 395 + "the one you specified doesn't exist: " + sqldbmap); 396 } 397 398 if (driver == null) 399 { 400 throw new BuildException("Driver attribute must be set!", location); 401 } 402 if (userId == null) 403 { 404 throw new BuildException("User Id attribute must be set!", 405 location); 406 } 407 if (password == null) 408 { 409 throw new BuildException("Password attribute must be set!", 410 location); 411 } 412 if (url == null) 413 { 414 throw new BuildException("Url attribute must be set!", location); 415 } 416 417 Properties map = new Properties (); 418 419 try 420 { 421 FileInputStream fis = new FileInputStream (getSqlDbMap()); 422 map.load(fis); 423 fis.close(); 424 } 425 catch (IOException ioe) 426 { 427 throw new BuildException("Cannot open and process the sqldbmap!"); 428 } 429 430 Map databases = new HashMap (); 431 432 Iterator eachFileName = map.keySet().iterator(); 433 while (eachFileName.hasNext()) 434 { 435 String sqlfile = (String ) eachFileName.next(); 436 String database = map.getProperty(sqlfile); 437 438 List files = (List ) databases.get(database); 439 440 if (files == null) 441 { 442 files = new ArrayList (); 443 databases.put(database, files); 444 } 445 446 if (sqlfile.indexOf("schema.sql") != -1) 449 { 450 files.add(0, sqlfile); 451 } 452 else 453 { 454 files.add(sqlfile); 455 } 456 } 457 458 Iterator eachDatabase = databases.keySet().iterator(); 459 while (eachDatabase.hasNext()) 460 { 461 String db = (String ) eachDatabase.next(); 462 List transactions = new ArrayList (); 463 eachFileName = ((List ) databases.get(db)).iterator(); 464 while (eachFileName.hasNext()) 465 { 466 String fileName = (String ) eachFileName.next(); 467 File file = new File (srcDir, fileName); 468 469 if (file.exists()) 470 { 471 Transaction transaction = new Transaction(); 472 transaction.setSrc(file); 473 transactions.add(transaction); 474 } 475 else 476 { 477 super.log("File '" + fileName 478 + "' in sqldbmap does not exist, so skipping it."); 479 } 480 } 481 482 insertDatabaseSqlFiles(url, db, transactions); 483 } 484 } 485 486 494 private void insertDatabaseSqlFiles(String url, String database, 495 List transactions) 496 { 497 url = StringUtils.replace(url, "@DB@", database); 498 System.out.println("Our new url -> " + url); 499 500 Driver driverInstance = null; 501 try 502 { 503 Class dc; 504 if (classpath != null) 505 { 506 log("Loading " + driver 507 + " using AntClassLoader with classpath " + classpath, 508 Project.MSG_VERBOSE); 509 510 loader = new AntClassLoader(project, classpath); 511 dc = loader.loadClass(driver); 512 } 513 else 514 { 515 log("Loading " + driver + " using system loader.", 516 Project.MSG_VERBOSE); 517 dc = Class.forName(driver); 518 } 519 driverInstance = (Driver ) dc.newInstance(); 520 } 521 catch (ClassNotFoundException e) 522 { 523 throw new BuildException("Class Not Found: JDBC driver " + driver 524 + " could not be loaded", location); 525 } 526 catch (IllegalAccessException e) 527 { 528 throw new BuildException("Illegal Access: JDBC driver " + driver 529 + " could not be loaded", location); 530 } 531 catch (InstantiationException e) 532 { 533 throw new BuildException("Instantiation Exception: JDBC driver " 534 + driver + " could not be loaded", location); 535 } 536 537 try 538 { 539 log("connecting to " + url, Project.MSG_VERBOSE); 540 Properties info = new Properties (); 541 info.put("user", userId); 542 info.put("password", password); 543 conn = driverInstance.connect(url, info); 544 545 if (conn == null) 546 { 547 throw new SQLException ("No suitable Driver for " + url); 549 } 550 551 if (!isValidRdbms(conn)) 552 { 553 return; 554 } 555 556 conn.setAutoCommit(autocommit); 557 statement = conn.createStatement(); 558 PrintStream out = System.out; 559 try 560 { 561 if (output != null) 562 { 563 log("Opening PrintStream to output file " + output, 564 Project.MSG_VERBOSE); 565 out = new PrintStream (new BufferedOutputStream ( 566 new FileOutputStream (output))); 567 } 568 569 for (Iterator it = transactions.iterator(); it.hasNext();) 571 { 572 ((Transaction) it.next()).runTransaction(out); 573 if (!autocommit) 574 { 575 log("Commiting transaction", Project.MSG_VERBOSE); 576 conn.commit(); 577 } 578 } 579 } 580 finally 581 { 582 if (out != null && out != System.out) 583 { 584 out.close(); 585 } 586 } 587 } 588 catch (IOException e) 589 { 590 if (!autocommit && conn != null && onError.equals("abort")) 591 { 592 try 593 { 594 conn.rollback(); 595 } 596 catch (SQLException ex) 597 { 598 } 600 } 601 throw new BuildException(e, location); 602 } 603 catch (SQLException e) 604 { 605 if (!autocommit && conn != null && onError.equals("abort")) 606 { 607 try 608 { 609 conn.rollback(); 610 } 611 catch (SQLException ex) 612 { 613 } 615 } 616 throw new BuildException(e, location); 617 } 618 finally 619 { 620 try 621 { 622 if (statement != null) 623 { 624 statement.close(); 625 } 626 if (conn != null) 627 { 628 conn.close(); 629 } 630 } 631 catch (SQLException e) 632 { 633 } 634 } 635 636 log(goodSql + " of " + totalSql 637 + " SQL statements executed successfully"); 638 } 639 640 649 protected void runStatements(Reader reader, PrintStream out) 650 throws SQLException , IOException 651 { 652 String sql = ""; 653 String line = ""; 654 655 BufferedReader in = new BufferedReader (reader); 656 657 try 658 { 659 while ((line = in.readLine()) != null) 660 { 661 line = line.trim(); 662 line = ProjectHelper.replaceProperties(project, line, 663 project.getProperties()); 664 if (line.startsWith("//") || line.startsWith("--")) 665 { 666 continue; 667 } 668 if (line.length() > 4 669 && line.substring(0, 4).equalsIgnoreCase("REM ")) 670 { 671 continue; 672 } 673 674 sql += " " + line; 675 sql = sql.trim(); 676 677 if (line.indexOf("--") >= 0) 681 { 682 sql += "\n"; 683 } 684 685 if (delimiterType.equals(DelimiterType.NORMAL) 686 && sql.endsWith(delimiter) 687 || delimiterType.equals(DelimiterType.ROW) 688 && line.equals(delimiter)) 689 { 690 log("SQL: " + sql, Project.MSG_VERBOSE); 691 execSQL(sql.substring(0, sql.length() - delimiter.length()), 692 out); 693 sql = ""; 694 } 695 } 696 697 if (!sql.equals("")) 699 { 700 execSQL(sql, out); 701 } 702 } 703 catch (SQLException e) 704 { 705 throw e; 706 } 707 } 708 709 714 protected boolean isValidRdbms(Connection conn) 715 { 716 if (rdbms == null && version == null) 717 { 718 return true; 719 } 720 721 try 722 { 723 DatabaseMetaData dmd = conn.getMetaData(); 724 725 if (rdbms != null) 726 { 727 String theVendor = dmd.getDatabaseProductName().toLowerCase(); 728 729 log("RDBMS = " + theVendor, Project.MSG_VERBOSE); 730 if (theVendor == null || theVendor.indexOf(rdbms) < 0) 731 { 732 log("Not the required RDBMS: " 733 + rdbms, Project.MSG_VERBOSE); 734 return false; 735 } 736 } 737 738 if (version != null) 739 { 740 String theVersion = dmd.getDatabaseProductVersion() 741 .toLowerCase(); 742 743 log("Version = " + theVersion, Project.MSG_VERBOSE); 744 if (theVersion == null || !(theVersion.startsWith(version) 745 || theVersion.indexOf(" " + version) >= 0)) 746 { 747 log("Not the required version: \"" + version + "\"", 748 Project.MSG_VERBOSE); 749 return false; 750 } 751 } 752 } 753 catch (SQLException e) 754 { 755 log("Failed to obtain required RDBMS information", Project.MSG_ERR); 757 return false; 758 } 759 760 return true; 761 } 762 763 770 protected void execSQL(String sql, PrintStream out) throws SQLException 771 { 772 if ("".equals(sql.trim())) 774 { 775 return; 776 } 777 778 try 779 { 780 totalSql++; 781 if (!statement.execute(sql)) 782 { 783 log(statement.getUpdateCount() + " rows affected", 784 Project.MSG_VERBOSE); 785 } 786 else 787 { 788 if (print) 789 { 790 printResults(out); 791 } 792 } 793 794 SQLWarning warning = conn.getWarnings(); 795 while (warning != null) 796 { 797 log(warning + " sql warning", Project.MSG_VERBOSE); 798 warning = warning.getNextWarning(); 799 } 800 conn.clearWarnings(); 801 goodSql++; 802 } 803 catch (SQLException e) 804 { 805 log("Failed to execute: " + sql, Project.MSG_ERR); 806 if (!onError.equals("continue")) 807 { 808 throw e; 809 } 810 log(e.toString(), Project.MSG_ERR); 811 } 812 } 813 814 820 protected void printResults(PrintStream out) throws java.sql.SQLException 821 { 822 ResultSet rs = null; 823 do 824 { 825 rs = statement.getResultSet(); 826 if (rs != null) 827 { 828 log("Processing new result set.", Project.MSG_VERBOSE); 829 ResultSetMetaData md = rs.getMetaData(); 830 int columnCount = md.getColumnCount(); 831 StringBuffer line = new StringBuffer (); 832 if (showheaders) 833 { 834 for (int col = 1; col < columnCount; col++) 835 { 836 line.append(md.getColumnName(col)); 837 line.append(","); 838 } 839 line.append(md.getColumnName(columnCount)); 840 out.println(line); 841 line.setLength(0); 842 } 843 while (rs.next()) 844 { 845 boolean first = true; 846 for (int col = 1; col <= columnCount; col++) 847 { 848 String columnValue = rs.getString(col); 849 if (columnValue != null) 850 { 851 columnValue = columnValue.trim(); 852 } 853 854 if (first) 855 { 856 first = false; 857 } 858 else 859 { 860 line.append(","); 861 } 862 line.append(columnValue); 863 } 864 out.println(line); 865 line.setLength(0); 866 } 867 } 868 } 869 while (statement.getMoreResults()); 870 out.println(); 871 } 872 873 877 public static class OnError extends EnumeratedAttribute 878 { 879 public String [] getValues() 880 { 881 return new String [] {"continue", "stop", "abort"}; 882 } 883 } 884 885 891 public class Transaction 892 { 893 private File tSrcFile = null; 894 private String tSqlCommand = ""; 895 896 public void setSrc(File src) 897 { 898 this.tSrcFile = src; 899 } 900 901 public void addText(String sql) 902 { 903 this.tSqlCommand += sql; 904 } 905 906 private void runTransaction(PrintStream out) 907 throws IOException , SQLException 908 { 909 if (tSqlCommand.length() != 0) 910 { 911 log("Executing commands", Project.MSG_INFO); 912 runStatements(new StringReader (tSqlCommand), out); 913 } 914 915 if (tSrcFile != null) 916 { 917 log("Executing file: " + tSrcFile.getAbsolutePath(), 918 Project.MSG_INFO); 919 Reader reader = (encoding == null) ? new FileReader (tSrcFile) 920 : new InputStreamReader (new FileInputStream (tSrcFile), 921 encoding); 922 runStatements(reader, out); 923 reader.close(); 924 } 925 } 926 } 927 } 928 | Popular Tags |