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