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