001 package org.codehaus.mojo.sql;
002
003 /*
004 * Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE
005 * file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file
006 * to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the
007 * License. You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
012 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
013 * specific language governing permissions and limitations under the License.
014 */
015
016 import java.io.BufferedOutputStream;
017 import java.io.BufferedReader;
018 import java.io.File;
019 import java.io.FileInputStream;
020 import java.io.FileOutputStream;
021 import java.io.FileReader;
022 import java.io.IOException;
023 import java.io.InputStream;
024 import java.io.InputStreamReader;
025 import java.io.OutputStream;
026 import java.io.PrintStream;
027 import java.io.Reader;
028 import java.io.StringReader;
029 import java.sql.Connection;
030 import java.sql.Driver;
031 import java.sql.ResultSet;
032 import java.sql.ResultSetMetaData;
033 import java.sql.SQLException;
034 import java.sql.SQLWarning;
035 import java.sql.Statement;
036 import java.util.ArrayList;
037 import java.util.Collections;
038 import java.util.Enumeration;
039 import java.util.List;
040 import java.util.Properties;
041 import java.util.StringTokenizer;
042 import java.util.Vector;
043
044 import org.apache.commons.io.IOUtils;
045 import org.apache.commons.lang.StringEscapeUtils;
046 import org.apache.maven.execution.MavenSession;
047 import org.apache.maven.plugin.AbstractMojo;
048 import org.apache.maven.plugin.MojoExecutionException;
049 import org.apache.maven.project.MavenProject;
050 import org.apache.maven.settings.Server;
051 import org.apache.maven.settings.Settings;
052 import org.apache.maven.shared.filtering.MavenFileFilter;
053 import org.apache.maven.shared.filtering.MavenFileFilterRequest;
054 import org.apache.maven.shared.filtering.MavenFilteringException;
055 import org.codehaus.plexus.util.FileUtils;
056 import org.codehaus.plexus.util.IOUtil;
057 import org.codehaus.plexus.util.StringUtils;
058 import org.springframework.core.io.DefaultResourceLoader;
059 import org.springframework.core.io.Resource;
060 import org.springframework.core.io.ResourceLoader;
061
062 /**
063 * Executes SQL against a database.
064 *
065 * @goal execute
066 */
067 public class SqlExecMojo extends AbstractMojo {
068
069 /**
070 * Call {@link #setOnError(String)} with this value to abort SQL command execution if an error is found.
071 */
072 public static final String ON_ERROR_ABORT = "abort";
073
074 /**
075 * Call {@link #setOnError(String)} with this value to continue SQL command execution until all commands have been
076 * attempted, then abort the build if an SQL error occurred in any of the commands.
077 */
078 public static final String ON_ERROR_ABORT_AFTER = "abortAfter";
079
080 /**
081 * Call {@link #setOnError(String)} with this value to continue SQL command execution if an error is found.
082 */
083 public static final String ON_ERROR_CONTINUE = "continue";
084
085 // ////////////////////////// User Info ///////////////////////////////////
086
087 /**
088 * Database username. If not given, it will be looked up through <code>settings.xml</code>'s server with
089 * <code>${settingsKey}</code> as key.
090 *
091 * @since 1.0
092 * @parameter expression="${username}"
093 */
094 private String username;
095
096 /**
097 * Database password. If not given, it will be looked up through <code>settings.xml</code>'s server with
098 * <code>${settingsKey}</code> as key.
099 *
100 * @since 1.0
101 * @parameter expression="${password}"
102 */
103 private String password;
104
105 /**
106 * Ignore the password and use anonymous access. This may be useful for databases like MySQL which do not allow
107 * empty password parameters in the connection initialization.
108 *
109 * @since 1.4
110 * @parameter default-value="false"
111 */
112 private boolean enableAnonymousPassword;
113
114 /**
115 * Additional key=value pairs separated by comma to be passed into JDBC driver.
116 *
117 * @since 1.0
118 * @parameter expression="${driverProperties}" default-value = ""
119 */
120 private String driverProperties;
121
122 /**
123 * @parameter expression="${settings}"
124 * @required
125 * @since 1.0
126 * @readonly
127 */
128 private Settings settings;
129
130 /**
131 * Server's <code>id</code> in <code>settings.xml</code> to look up username and password. Defaults to
132 * <code>${url}</code> if not given.
133 *
134 * @since 1.0
135 * @parameter expression="${settingsKey}"
136 */
137 private String settingsKey;
138
139 /**
140 * Skip execution when there is an error obtaining a connection. This is a special case to support databases, such
141 * as embedded Derby, that can shutdown the database via the URL (i.e. <code>shutdown=true</code>).
142 *
143 * @since 1.1
144 * @parameter expression="${skipOnConnectionError}" default-value="false"
145 */
146 private boolean skipOnConnectionError;
147
148 /**
149 * Setting this parameter to <code>true</code> will force the execution of this mojo, even if it would get skipped
150 * usually.
151 *
152 * @parameter expression="${forceOpenJpaExecution}" default-value=false
153 * @required
154 */
155 private boolean forceMojoExecution;
156
157 /**
158 * The Maven Project Object
159 *
160 * @parameter default-value="${project}"
161 * @required
162 * @readonly
163 */
164 protected MavenProject project;
165
166 /**
167 * @parameter default-value="${session}"
168 * @required
169 * @readonly
170 */
171 private MavenSession mavenSession;
172
173 // ////////////////////////////// Source info /////////////////////////////
174
175 /**
176 * SQL input commands separated by <code>${delimiter}</code>.
177 *
178 * @since 1.0
179 * @parameter expression="${sqlCommand}" default-value=""
180 */
181 private String sqlCommand = "";
182
183 /**
184 * List of files containing SQL statements to load.
185 *
186 * @since 1.0
187 * @parameter
188 */
189 private File[] srcFiles;
190
191 /**
192 * List of resources containing SQL statements
193 *
194 * @since 1.5
195 * @parameter
196 */
197 private String[] resourceLocations;
198
199 /**
200 * File(s) containing SQL statements to load.
201 *
202 * @since 1.0
203 * @parameter
204 */
205 private Fileset fileset;
206
207 /**
208 * When <code>true</code>, skip the execution.
209 *
210 * @since 1.0
211 * @parameter default-value="false"
212 */
213 private boolean skip;
214
215 // //////////////////////////////// Database info /////////////////////////
216 /**
217 * Database URL.
218 *
219 * @parameter expression="${url}"
220 * @required
221 * @since 1.0-beta-1
222 */
223 private String url;
224
225 /**
226 * Database driver classname.
227 *
228 * @since 1.0
229 * @parameter expression="${driver}"
230 * @required
231 */
232 private String driver;
233
234 // //////////////////////////// Operation Configuration ////////////////////
235 /**
236 * Set to <code>true</code> to execute none-transactional SQL.
237 *
238 * @since 1.0
239 * @parameter expression="${autocommit}" default-value="false"
240 */
241 private boolean autocommit;
242
243 /**
244 * Action to perform if an error is found. Possible values are <code>abort</code> and <code>continue</code>.
245 *
246 * @since 1.0
247 * @parameter expression="${onError}" default-value="abort"
248 */
249 private String onError = ON_ERROR_ABORT;
250
251 // //////////////////////////// Parser Configuration ////////////////////
252
253 /**
254 * Set the delimiter that separates SQL statements.
255 *
256 * @since 1.0
257 * @parameter expression="${delimiter}" default-value=";"
258 */
259 private String delimiter = ";";
260
261 /**
262 * <p>
263 * The delimiter type takes two values - "normal" and "row". Normal means that any occurrence of the delimiter
264 * terminate the SQL command whereas with row, only a line containing just the delimiter is recognized as the end of
265 * the command.
266 * </p>
267 * <p>
268 * For example, set this to "go" and delimiterType to "row" for Sybase ASE or MS SQL Server.
269 * </p>
270 *
271 * @since 1.2
272 * @parameter expression="${delimiterType}" default-value="normal"
273 */
274 private String delimiterType = DelimiterType.NORMAL;
275
276 /**
277 * Set the order in which the SQL files will be executed. Possible values are <code>ASCENDING</code> and
278 * <code>DESCENDING</code> and <code>NONE</code>.
279 *
280 * @since 1.1
281 * @parameter expression="${orderFile}" default-value="NONE";
282 */
283 private Order orderFile;
284
285 /**
286 * When <code>true</code>, the whole SQL content in <code>sqlCommand</code>, <code>srcFiles</code> and
287 * <code>fileset</code> are sent directly to JDBC in one SQL statement. This option is for executing database stored
288 * procedures/functions.
289 *
290 * @deprecated used <i>delimiterType<i> instead.
291 * @since 1.1
292 * @parameter expression="${enableBlockMode}"
293 */
294
295 @Deprecated
296 private boolean enableBlockMode = false;
297
298 /**
299 * Keep the format of an SQL block.
300 *
301 * @since 1.1
302 * @parameter expression="${keepFormat}" default-value="false"
303 */
304 private boolean keepFormat = false;
305
306 // /////////////////////////////////////////////////////////////////////////////////////
307 /**
308 * Print SQL results.
309 *
310 * @parameter
311 * @since 1.3
312 */
313 private boolean printResultSet = false;
314
315 /**
316 * Print header columns.
317 */
318 private boolean showheaders = true;
319
320 /**
321 * Dump the SQL exection's output to a file. Default is stdout.
322 *
323 * @parameter
324 * @since 1.3
325 */
326 private File outputFile;
327
328 /**
329 * @parameter default-value=","
330 * @since 1.4
331 */
332 private String outputDelimiter;
333
334 /**
335 * Encoding to use when reading SQL statements from a file.
336 *
337 * @parameter expression="${encoding}" default-value= "${project.build.sourceEncoding}"
338 * @since 1.1
339 */
340 private String encoding = "";
341
342 /**
343 * Append to an existing file or overwrite it?
344 */
345 private boolean append = false;
346
347 /**
348 * Argument to Statement.setEscapeProcessing If you want the driver to use regular SQL syntax then set this to
349 * false.
350 *
351 * @since 1.4
352 * @parameter expression="${escapeProcessing}" default-value="true"
353 */
354 private boolean escapeProcessing = true;
355
356 // //////////////////////////////// Internal
357 // properties//////////////////////
358
359 /**
360 * number of successful executed statements
361 */
362 private int successfulStatements = 0;
363
364 /**
365 * number of total executed statements
366 */
367 private int totalStatements = 0;
368
369 /**
370 * Database connection
371 */
372 private Connection conn = null;
373
374 /**
375 * SQL statement
376 */
377 private Statement statement = null;
378
379 /**
380 * SQL transactions to perform
381 */
382 private Vector<Transaction> transactions = new Vector<Transaction>();
383
384 /**
385 * @component role="org.apache.maven.shared.filtering.MavenFileFilter"
386 * @since 1.4
387 */
388 private MavenFileFilter fileFilter;
389
390 /**
391 * Set to true if you want to filter the srcFiles using system-, user- and project properties
392 *
393 * @parameter
394 * @since 1.4
395 */
396 private boolean enableFiltering;
397
398 /**
399 * Set to false to disable executing SQL that appears without a delimiter at the end of a file (is enabled by
400 * default for backwards compatibility)
401 *
402 * @parameter expression="${sql.executeTrailingSQL}" default-value="true"
403 * @since 1.7
404 */
405 private boolean executeTrailingSQL;
406
407 /**
408 * Interpolator especially for braceless expressions
409 */
410 // private Interpolator interpolator = new
411 // RegexBasedInterpolator("\\$([^\\s;)]+?)", "(?=[\\s;)])");
412
413 /**
414 * Add a SQL transaction to execute
415 *
416 * @return a new SqlExecMojo.Transaction
417 */
418 public Transaction createTransaction() {
419 Transaction t = new Transaction();
420 transactions.addElement(t);
421 return t;
422 }
423
424 /**
425 * Set an inline SQL command to execute. NB: Properties are not expanded in this text.
426 *
427 * @param sql
428 * the sql statement to add
429 */
430 public void addText(String sql) {
431 this.sqlCommand += sql;
432 }
433
434 /**
435 * Set the file encoding to use on the SQL files read in
436 *
437 * @param encoding
438 * the encoding to use on the files
439 */
440 public void setEncoding(String encoding) {
441 this.encoding = encoding;
442 }
443
444 /**
445 * Set the delimiter that separates SQL statements. Defaults to ";";
446 *
447 * @param delimiter
448 * the new delimiter
449 */
450 public void setDelimiter(String delimiter) {
451 this.delimiter = delimiter;
452 }
453
454 /**
455 * Set the delimiter type: "normal" or "row" (default "normal").
456 *
457 * @param delimiterType
458 * the new delimiterType
459 */
460 public void setDelimiterType(String delimiterType) {
461 this.delimiterType = delimiterType;
462 }
463
464 /**
465 * Print result sets from the statements; optional, default false
466 *
467 * @param print
468 * <code>true</code> to print the resultset, otherwise <code>false</code>
469 * @deprecated typo, use setPrintResultSet()
470 */
471 @Deprecated
472 public void setPrintResutlSet(boolean print) {
473 setPrintResultSet(print);
474 }
475
476 /**
477 * Print result sets from the statements; optional, default false
478 *
479 * @param print
480 * <code>true</code> to print the resultset, otherwise <code>false</code>
481 */
482 public void setPrintResultSet(boolean print) {
483 this.printResultSet = print;
484 }
485
486 /**
487 * Print headers for result sets from the statements; optional, default true.
488 *
489 * @param showheaders
490 * <code>true</code> to show the headers, otherwise <code>false</code>
491 */
492 public void setShowheaders(boolean showheaders) {
493 this.showheaders = showheaders;
494 }
495
496 /**
497 * Set the output file;
498 *
499 * @param output
500 * the output file
501 */
502 public void setOutputFile(File output) {
503 this.outputFile = output;
504 }
505
506 /**
507 * whether output should be appended to or overwrite an existing file. Defaults to false.
508 *
509 * @param append
510 * <code>true</code> to append, otherwise <code>false</code> to overwrite
511 */
512 public void setAppend(boolean append) {
513 this.append = append;
514 }
515
516 /**
517 * whether or not format should be preserved. Defaults to false.
518 *
519 * @param keepformat
520 * The keepformat to set
521 */
522 public void setKeepFormat(boolean keepformat) {
523 this.keepFormat = keepformat;
524 }
525
526 /**
527 * Set escape processing for statements.
528 *
529 * @param enable
530 * <code>true</code> to escape, otherwiser <code>false</code>
531 */
532 public void setEscapeProcessing(boolean enable) {
533 escapeProcessing = enable;
534 }
535
536 /**
537 * <p>
538 * Determine if the mojo execution should get skipped.
539 * </p>
540 * This is the case if:
541 * <ul>
542 * <li>{@link #skip} is <code>true</code></li>
543 * <li>if the mojo gets executed on a project with packaging type 'pom' and {@link #forceMojoExecution} is
544 * <code>false</code></li>
545 * </ul>
546 *
547 * @return <code>true</code> if the mojo execution should be skipped.
548 */
549 protected boolean skipMojo() {
550 if (skip) {
551 getLog().info("Skip sql execution");
552 return true;
553 }
554
555 if (!forceMojoExecution && project != null && "pom".equals(project.getPackaging())) {
556 getLog().info("Skipping sql execution for project with packaging type 'pom'");
557 return true;
558 }
559
560 return false;
561 }
562
563 /**
564 * Load the sql file and then execute it
565 *
566 * @throws MojoExecutionException
567 */
568 @Override
569 public void execute() throws MojoExecutionException {
570
571 if (skipMojo()) {
572 return;
573 }
574
575 successfulStatements = 0;
576
577 totalStatements = 0;
578
579 loadUserInfoFromSettings();
580
581 addCommandToTransactions();
582
583 addFilesToTransactions();
584
585 addFileSetToTransactions();
586
587 addResourcesToTransactions();
588
589 sortTransactions();
590
591 try {
592 conn = getConnection();
593 } catch (SQLException e) {
594 if (!this.skipOnConnectionError) {
595 throw new MojoExecutionException(e.getMessage(), e);
596 } else {
597 // error on get connection and user asked to skip the rest
598 return;
599 }
600 }
601
602 try {
603 statement = conn.createStatement();
604 statement.setEscapeProcessing(escapeProcessing);
605
606 PrintStream out = System.out;
607 try {
608 if (outputFile != null) {
609 getLog().debug("Opening PrintStream to output file " + outputFile);
610 out = new PrintStream(new BufferedOutputStream(new FileOutputStream(outputFile.getAbsolutePath(),
611 append)));
612 }
613
614 // Process all transactions
615 for (Enumeration<Transaction> e = transactions.elements(); e.hasMoreElements();) {
616 Transaction t = e.nextElement();
617
618 t.runTransaction(out);
619
620 if (!autocommit) {
621 getLog().debug("Committing transaction");
622 conn.commit();
623 }
624 }
625 } finally {
626 if (out != null && out != System.out) {
627 out.close();
628 }
629 }
630 } catch (IOException e) {
631 throw new MojoExecutionException(e.getMessage(), e);
632 } catch (SQLException e) {
633 if (!autocommit && conn != null && ON_ERROR_ABORT.equalsIgnoreCase(getOnError())) {
634 try {
635 conn.rollback();
636 } catch (SQLException ex) {
637 // ignore
638 }
639 }
640 throw new MojoExecutionException(e.getMessage(), e);
641 } finally {
642 try {
643 if (statement != null) {
644 statement.close();
645 }
646 if (conn != null) {
647 conn.close();
648 }
649 } catch (SQLException ex) {
650 // ignore
651 }
652 }
653
654 getLog().info(
655 getSuccessfulStatements() + " of " + getTotalStatements() + " SQL statements executed successfully");
656
657 if (ON_ERROR_ABORT_AFTER.equalsIgnoreCase(getOnError()) && totalStatements != successfulStatements) {
658 throw new MojoExecutionException("Some SQL statements failed to execute");
659 }
660
661 }
662
663 /**
664 * Add sql command to transactions list.
665 *
666 */
667 private void addCommandToTransactions() {
668 createTransaction().addText(sqlCommand.trim());
669 }
670
671 /**
672 * Add user sql fileset to transation list
673 *
674 */
675 private void addFileSetToTransactions() {
676 String[] includedFiles;
677 if (fileset != null) {
678 fileset.scan();
679 includedFiles = fileset.getIncludedFiles();
680 } else {
681 includedFiles = new String[0];
682 }
683
684 for (int j = 0; j < includedFiles.length; j++) {
685 createTransaction().setSrc(new File(fileset.getBasedir(), includedFiles[j]));
686 }
687 }
688
689 protected Resource[] getResources(String[] locations) throws MojoExecutionException {
690 if (locations == null || locations.length == 0) {
691 return new Resource[] {};
692 }
693 ResourceLoader loader = new DefaultResourceLoader();
694 List<Resource> resources = new ArrayList<Resource>();
695 for (int i = 0; i < locations.length; i++) {
696 String location = locations[i];
697 // Skip it if the location is empty
698 if (StringUtils.isEmpty(location)) {
699 continue;
700 }
701 Resource resource = loader.getResource(location);
702 if (!resource.exists()) {
703 // The location was not empty, but we couldn't find it
704 throw new MojoExecutionException("Resource " + location + " was not found");
705 }
706 resources.add(resource);
707 }
708 return resources.toArray(new Resource[resources.size()]);
709 }
710
711 protected void copy(Resource resource, File file) throws IOException {
712 InputStream in = resource.getInputStream();
713 OutputStream out = new FileOutputStream(file);
714 IOUtils.copyLarge(in, out);
715 }
716
717 /**
718 * Add user input of srcFiles to transaction list.
719 *
720 * @throws MojoExecutionException
721 */
722 private void addResourcesToTransactions() throws MojoExecutionException {
723 String[] locations = getResourceLocations();
724 Resource[] resources = getResources(locations);
725
726 MavenFileFilterRequest request = new MavenFileFilterRequest();
727 request.setEncoding(encoding);
728 request.setMavenSession(mavenSession);
729 request.setMavenProject(project);
730 request.setFiltering(enableFiltering);
731 for (int i = 0; i < resources.length; i++) {
732 Resource resource = resources[i];
733 String filename = resource.getFilename();
734 String basename = FileUtils.basename(filename);
735 String extension = FileUtils.extension(filename);
736 if (!extension.startsWith(".")) {
737 extension = "." + extension;
738 }
739 File sourceFile = FileUtils.createTempFile(basename, extension, null);
740 if (!getLog().isDebugEnabled()) {
741 sourceFile.deleteOnExit();
742 }
743
744 try {
745 copy(resource, sourceFile);
746 } catch (IOException e) {
747 throw new MojoExecutionException("Error copying resource " + resource + " to a local temporary file", e);
748 }
749
750 if (!enableFiltering) {
751 createTransaction().setSrc(sourceFile);
752 continue;
753 }
754
755 File targetFile = FileUtils.createTempFile(basename, extension, null);
756 if (!getLog().isDebugEnabled()) {
757 sourceFile.deleteOnExit();
758 }
759
760 request.setFrom(sourceFile);
761 request.setTo(targetFile);
762
763 try {
764 fileFilter.copyFile(request);
765 } catch (MavenFilteringException e) {
766 throw new MojoExecutionException(e.getMessage());
767 }
768
769 createTransaction().setSrc(targetFile);
770 }
771 }
772
773 /**
774 * Add user input of srcFiles to transaction list.
775 *
776 * @throws MojoExecutionException
777 */
778 private void addFilesToTransactions() throws MojoExecutionException {
779 File[] files = getSrcFiles();
780
781 MavenFileFilterRequest request = new MavenFileFilterRequest();
782 request.setEncoding(encoding);
783 request.setMavenSession(mavenSession);
784 request.setMavenProject(project);
785 request.setFiltering(enableFiltering);
786 for (int i = 0; files != null && i < files.length; ++i) {
787 if (files[i] != null && !files[i].exists()) {
788 throw new MojoExecutionException(files[i].getPath() + " not found.");
789 }
790
791 if (!enableFiltering) {
792 createTransaction().setSrc(files[i]);
793 continue;
794 }
795
796 File sourceFile = files[i];
797 String basename = FileUtils.basename(sourceFile.getName());
798 String extension = FileUtils.extension(sourceFile.getName());
799 if (!extension.startsWith(".")) {
800 extension = "." + extension;
801 }
802 File targetFile = FileUtils.createTempFile(basename, extension, null);
803 if (!getLog().isDebugEnabled()) {
804 targetFile.deleteOnExit();
805 }
806
807 request.setFrom(sourceFile);
808 request.setTo(targetFile);
809
810 try {
811 fileFilter.copyFile(request);
812 } catch (MavenFilteringException e) {
813 throw new MojoExecutionException(e.getMessage());
814 }
815
816 createTransaction().setSrc(targetFile);
817 }
818 }
819
820 /**
821 * Sort the transaction list.
822 */
823 protected void sortTransactions() {
824 switch (orderFile) {
825 case ASCENDING:
826 Collections.sort(transactions);
827 break;
828 case DESCENDING:
829 Collections.sort(transactions, Collections.reverseOrder());
830 break;
831 case NONE:
832 break;
833 default:
834 throw new RuntimeException("Unknown value for orderFile: " + orderFile);
835 }
836 }
837
838 /**
839 * Load username password from settings if user has not set them in JVM properties
840 *
841 * @throws MojoExecutionException
842 */
843 private void loadUserInfoFromSettings() throws MojoExecutionException {
844 if (this.settingsKey == null) {
845 this.settingsKey = getUrl();
846 }
847
848 if ((getUsername() == null || getPassword() == null) && (settings != null)) {
849 Server server = this.settings.getServer(this.settingsKey);
850
851 if (server != null) {
852 if (getUsername() == null) {
853 setUsername(server.getUsername());
854 }
855
856 if (getPassword() == null) {
857 setPassword(server.getPassword());
858 }
859 }
860 }
861
862 if (getUsername() == null) {
863 // allow emtpy username
864 setUsername("");
865 }
866
867 if (getPassword() == null) {
868 // allow emtpy password
869 setPassword("");
870 }
871 }
872
873 /**
874 * Creates a new Connection as using the driver, url, userid and password specified.
875 *
876 * The calling method is responsible for closing the connection.
877 *
878 * @return Connection the newly created connection.
879 * @throws MojoExecutionException
880 * if the UserId/Password/Url is not set or there is no suitable driver or the driver fails to load.
881 * @throws SQLException
882 * if there is problem getting connection with valid url
883 *
884 */
885 private Connection getConnection() throws MojoExecutionException, SQLException {
886 getLog().debug("connecting to " + getUrl());
887 Properties info = new Properties();
888 info.put("user", getUsername());
889
890 if (!enableAnonymousPassword) {
891 info.put("password", getPassword());
892 }
893
894 info.putAll(this.getDriverProperties());
895
896 Driver driverInstance = null;
897
898 try {
899 Class<?> dc = Class.forName(getDriver());
900 driverInstance = (Driver) dc.newInstance();
901 } catch (ClassNotFoundException e) {
902 throw new MojoExecutionException("Driver class not found: " + getDriver(), e);
903 } catch (Exception e) {
904 throw new MojoExecutionException("Failure loading driver: " + getDriver(), e);
905 }
906
907 Connection conn = driverInstance.connect(getUrl(), info);
908
909 if (conn == null) {
910 // Driver doesn't understand the URL
911 throw new SQLException("No suitable Driver for " + getUrl());
912 }
913
914 conn.setAutoCommit(autocommit);
915 return conn;
916 }
917
918 /**
919 * parse driverProperties into Properties set
920 *
921 * @return the driver properties
922 * @throws MojoExecutionException
923 */
924 protected Properties getDriverProperties() throws MojoExecutionException {
925 Properties properties = new Properties();
926
927 if (!StringUtils.isEmpty(this.driverProperties)) {
928 String[] tokens = StringUtils.split(this.driverProperties, ",");
929 for (int i = 0; i < tokens.length; ++i) {
930 String[] keyValueTokens = StringUtils.split(tokens[i].trim(), "=");
931 if (keyValueTokens.length != 2) {
932 throw new MojoExecutionException("Invalid JDBC Driver properties: " + this.driverProperties);
933 }
934
935 properties.setProperty(keyValueTokens[0], keyValueTokens[1]);
936
937 }
938 }
939
940 return properties;
941 }
942
943 /**
944 * read in lines and execute them
945 *
946 * @param reader
947 * the reader
948 * @param out
949 * the outputstream
950 * @throws SQLException
951 * @throws IOException
952 */
953 private void runStatements(Reader reader, PrintStream out) throws SQLException, IOException {
954 String line;
955
956 if (enableBlockMode) {
957 // no need to parse the content, ship it directly to jdbc in one sql
958 // statement
959 line = IOUtil.toString(reader);
960 execSQL(line, out);
961 return;
962 }
963
964 StringBuffer sql = new StringBuffer();
965
966 BufferedReader in = new BufferedReader(reader);
967
968 while ((line = in.readLine()) != null) {
969 getLog().debug("line='" + line + "'");
970 if (!keepFormat) {
971 line = line.trim();
972 }
973
974 if (!keepFormat) {
975 if (line.startsWith("//")) {
976 continue;
977 }
978 if (line.startsWith("--")) {
979 continue;
980 }
981 StringTokenizer st = new StringTokenizer(line);
982 if (st.hasMoreTokens()) {
983 String token = st.nextToken();
984 if ("REM".equalsIgnoreCase(token)) {
985 continue;
986 }
987 }
988 }
989
990 if (!keepFormat) {
991 sql.append(" ").append(line);
992 } else {
993 sql.append("\n").append(line);
994 }
995
996 // SQL defines "--" as a comment to EOL
997 // but in Oracle it may contain a hint
998 // so we cannot just remove it, instead we must end it
999 if (!keepFormat) {
1000 if (SqlSplitter.containsSqlEnd(line, delimiter) == SqlSplitter.NO_END) {
1001 sql.append("\n");
1002 }
1003 }
1004
1005 if (isEnd(line)) {
1006 execSQL(sql.substring(0, sql.length() - delimiter.length()), out);
1007 sql.setLength(0); // clean buffer
1008 }
1009 }
1010
1011 // Catch any statements not followed by ;
1012 if (executeTrailingSQL && !sql.toString().equals("")) {
1013 execSQL(sql.toString(), out);
1014 }
1015 }
1016
1017 protected boolean isEnd(String line) {
1018 if (delimiterType.equals(DelimiterType.ROW)) {
1019 return line.trim().equals(delimiter);
1020 }
1021 int pos = SqlSplitter.containsSqlEnd(line, delimiter);
1022 if (delimiterType.equals(DelimiterType.NORMAL) && pos > 0) {
1023 return true;
1024 }
1025 return false;
1026 }
1027
1028 /**
1029 * Exec the sql statement.
1030 *
1031 * @param sql
1032 * query to execute
1033 * @param out
1034 * the outputstream
1035 */
1036 private void execSQL(String sql, PrintStream out) throws SQLException {
1037 // Check and ignore empty statements
1038 if ("".equals(sql.trim())) {
1039 return;
1040 }
1041
1042 ResultSet resultSet = null;
1043 try {
1044 totalStatements++;
1045 getLog().debug("SQL: " + sql);
1046
1047 boolean ret;
1048 int updateCountTotal = 0;
1049
1050 ret = statement.execute(sql);
1051 do {
1052 if (!ret) {
1053 int updateCount = statement.getUpdateCount();
1054 if (updateCount != -1) {
1055 updateCountTotal += updateCount;
1056 }
1057 } else {
1058 resultSet = statement.getResultSet();
1059 if (printResultSet) {
1060 printResultSet(resultSet, out);
1061 }
1062 }
1063 ret = statement.getMoreResults();
1064 } while (ret);
1065
1066 getLog().debug(updateCountTotal + " rows affected");
1067
1068 if (printResultSet) {
1069 StringBuffer line = new StringBuffer();
1070 line.append(updateCountTotal).append(" rows affected");
1071 out.println(line);
1072 }
1073
1074 SQLWarning warning = conn.getWarnings();
1075 while (warning != null) {
1076 getLog().debug(warning + " sql warning");
1077 warning = warning.getNextWarning();
1078 }
1079 conn.clearWarnings();
1080 successfulStatements++;
1081 } catch (SQLException e) {
1082 getLog().error("Failed to execute: " + sql);
1083 if (ON_ERROR_ABORT.equalsIgnoreCase(getOnError())) {
1084 throw e;
1085 }
1086 getLog().error(e.toString());
1087 } finally {
1088 if (resultSet != null) {
1089 resultSet.close();
1090 }
1091 }
1092 }
1093
1094 /**
1095 * print any results in the result set.
1096 *
1097 * @param rs
1098 * the resultset to print information about
1099 * @param out
1100 * the place to print results
1101 * @throws SQLException
1102 * on SQL problems.
1103 */
1104 private void printResultSet(ResultSet rs, PrintStream out) throws SQLException {
1105 if (rs != null) {
1106 getLog().debug("Processing new result set.");
1107 ResultSetMetaData md = rs.getMetaData();
1108 int columnCount = md.getColumnCount();
1109 StringBuffer line = new StringBuffer();
1110 if (showheaders) {
1111 boolean first = true;
1112 for (int col = 1; col <= columnCount; col++) {
1113 String columnValue = md.getColumnName(col);
1114
1115 if (columnValue != null) {
1116 columnValue = columnValue.trim();
1117
1118 if (",".equals(outputDelimiter)) {
1119 columnValue = StringEscapeUtils.escapeCsv(columnValue);
1120 }
1121 }
1122
1123 if (first) {
1124 first = false;
1125 } else {
1126 line.append(outputDelimiter);
1127 }
1128 line.append(columnValue);
1129 }
1130 out.println(line);
1131 line = new StringBuffer();
1132 }
1133 while (rs.next()) {
1134 boolean first = true;
1135 for (int col = 1; col <= columnCount; col++) {
1136 String columnValue = rs.getString(col);
1137 if (columnValue != null) {
1138 columnValue = columnValue.trim();
1139
1140 if (",".equals(outputDelimiter)) {
1141 columnValue = StringEscapeUtils.escapeCsv(columnValue);
1142 }
1143 }
1144
1145 if (first) {
1146 first = false;
1147 } else {
1148 line.append(outputDelimiter);
1149 }
1150 line.append(columnValue);
1151 }
1152 out.println(line);
1153 line = new StringBuffer();
1154 }
1155 }
1156 out.println();
1157 }
1158
1159 /**
1160 * Contains the definition of a new transaction element. Transactions allow several files or blocks of statements to
1161 * be executed using the same JDBC connection and commit operation in between.
1162 */
1163 private class Transaction implements Comparable<Transaction> {
1164 private File tSrcFile = null;
1165
1166 private String tSqlCommand = "";
1167
1168 /**
1169 *
1170 */
1171 public void setSrc(File src) {
1172 this.tSrcFile = src;
1173 }
1174
1175 /**
1176 *
1177 */
1178 public void addText(String sql) {
1179 this.tSqlCommand += sql;
1180 }
1181
1182 /**
1183 *
1184 */
1185 private void runTransaction(PrintStream out) throws IOException, SQLException {
1186 if (tSqlCommand.length() != 0) {
1187 getLog().info("Executing commands");
1188
1189 runStatements(new StringReader(tSqlCommand), out);
1190 }
1191
1192 if (tSrcFile != null) {
1193 getLog().info("Executing file: " + tSrcFile.getAbsolutePath());
1194
1195 Reader reader = null;
1196
1197 if (StringUtils.isEmpty(encoding)) {
1198 reader = new FileReader(tSrcFile);
1199 } else {
1200 reader = new InputStreamReader(new FileInputStream(tSrcFile), encoding);
1201 }
1202
1203 try {
1204 runStatements(reader, out);
1205 } finally {
1206 reader.close();
1207 }
1208 }
1209 }
1210
1211 @Override
1212 public int compareTo(Transaction transaction) {
1213
1214 if (transaction.tSrcFile == null) {
1215 if (this.tSrcFile == null) {
1216 return 0;
1217 } else {
1218 return Integer.MAX_VALUE;
1219 }
1220 } else {
1221 if (this.tSrcFile == null) {
1222 return Integer.MIN_VALUE;
1223 } else {
1224 return this.tSrcFile.compareTo(transaction.tSrcFile);
1225 }
1226 }
1227 }
1228 }
1229
1230 //
1231 // helper accessors for unit test purposes
1232 //
1233
1234 public String getUsername() {
1235 return this.username;
1236 }
1237
1238 public void setUsername(String username) {
1239 this.username = username;
1240 }
1241
1242 public String getPassword() {
1243 return this.password;
1244 }
1245
1246 public void setPassword(String password) {
1247 this.password = password;
1248 }
1249
1250 public String getUrl() {
1251 return this.url;
1252 }
1253
1254 public void setUrl(String url) {
1255 this.url = url;
1256 }
1257
1258 public String getDriver() {
1259 return this.driver;
1260 }
1261
1262 public void setDriver(String driver) {
1263 this.driver = driver;
1264 }
1265
1266 void setAutocommit(boolean autocommit) {
1267 this.autocommit = autocommit;
1268 }
1269
1270 void setFileset(Fileset fileset) {
1271 this.fileset = fileset;
1272 }
1273
1274 public File[] getSrcFiles() {
1275 return this.srcFiles;
1276 }
1277
1278 public void setSrcFiles(File[] files) {
1279 this.srcFiles = files;
1280 }
1281
1282 /**
1283 * @deprecated use {@link #getSuccessfulStatements()}
1284 */
1285 @Deprecated
1286 int getGoodSqls() {
1287 return this.getSuccessfulStatements();
1288 }
1289
1290 /**
1291 * Number of SQL statements executed so far that caused errors.
1292 *
1293 * @return the number
1294 */
1295 public int getSuccessfulStatements() {
1296 return successfulStatements;
1297 }
1298
1299 /**
1300 * Number of SQL statements executed so far, including the ones that caused errors.
1301 *
1302 * @return the number
1303 */
1304 public int getTotalStatements() {
1305 return totalStatements;
1306 }
1307
1308 public String getOnError() {
1309 return this.onError;
1310 }
1311
1312 public void setOnError(String action) {
1313 if (ON_ERROR_ABORT.equalsIgnoreCase(action)) {
1314 this.onError = ON_ERROR_ABORT;
1315 } else if (ON_ERROR_CONTINUE.equalsIgnoreCase(action)) {
1316 this.onError = ON_ERROR_CONTINUE;
1317 } else if (ON_ERROR_ABORT_AFTER.equalsIgnoreCase(action)) {
1318 this.onError = ON_ERROR_ABORT_AFTER;
1319 } else {
1320 throw new IllegalArgumentException(action + " is not a valid value for onError, only '" + ON_ERROR_ABORT
1321 + "', '" + ON_ERROR_ABORT_AFTER + "', or '" + ON_ERROR_CONTINUE + "'.");
1322 }
1323 }
1324
1325 void setSettings(Settings settings) {
1326 this.settings = settings;
1327 }
1328
1329 void setSettingsKey(String key) {
1330 this.settingsKey = key;
1331 }
1332
1333 void setSkip(boolean skip) {
1334 this.skip = skip;
1335 }
1336
1337 public void setDriverProperties(String driverProperties) {
1338 this.driverProperties = driverProperties;
1339 }
1340
1341 public boolean isEnableBlockMode() {
1342 return enableBlockMode;
1343 }
1344
1345 public void setEnableBlockMode(boolean enableBlockMode) {
1346 this.enableBlockMode = enableBlockMode;
1347 }
1348
1349 public String getSqlCommand() {
1350 return sqlCommand;
1351 }
1352
1353 public void setSqlCommand(String sqlCommand) {
1354 this.sqlCommand = sqlCommand;
1355 }
1356
1357 public Vector<Transaction> getTransactions() {
1358 return transactions;
1359 }
1360
1361 public void setTransactions(Vector<Transaction> transactions) {
1362 this.transactions = transactions;
1363 }
1364
1365 public void setFileFilter(MavenFileFilter filter) {
1366 this.fileFilter = filter;
1367 }
1368
1369 public String[] getResourceLocations() {
1370 return resourceLocations;
1371 }
1372
1373 public void setResourceLocations(String[] resourceLocations) {
1374 this.resourceLocations = resourceLocations;
1375 }
1376
1377 public boolean isExecuteTrailingSQL() {
1378 return executeTrailingSQL;
1379 }
1380
1381 public void setExecuteTrailingSQL(boolean executeTrailingSQL) {
1382 this.executeTrailingSQL = executeTrailingSQL;
1383 }
1384
1385 public Order getOrderFile() {
1386 return orderFile;
1387 }
1388
1389 public void setOrderFile(String orderFile) {
1390 this.orderFile = Order.valueOf(orderFile.toUpperCase());
1391 }
1392 }