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.sql.Connection;
032 import java.sql.Driver;
033 import java.sql.ResultSet;
034 import java.sql.ResultSetMetaData;
035 import java.sql.SQLException;
036 import java.sql.SQLWarning;
037 import java.sql.Statement;
038 import java.util.ArrayList;
039 import java.util.Collections;
040 import java.util.Enumeration;
041 import java.util.List;
042 import java.util.Properties;
043 import java.util.StringTokenizer;
044 import java.util.Vector;
045
046 import org.apache.commons.io.IOUtils;
047 import org.apache.commons.lang.StringEscapeUtils;
048 import org.apache.maven.execution.MavenSession;
049 import org.apache.maven.plugin.AbstractMojo;
050 import org.apache.maven.plugin.MojoExecutionException;
051 import org.apache.maven.project.MavenProject;
052 import org.apache.maven.settings.Server;
053 import org.apache.maven.settings.Settings;
054 import org.apache.maven.shared.filtering.MavenFileFilter;
055 import org.apache.maven.shared.filtering.MavenFileFilterRequest;
056 import org.apache.maven.shared.filtering.MavenFilteringException;
057 import org.codehaus.plexus.util.FileUtils;
058 import org.codehaus.plexus.util.IOUtil;
059 import org.codehaus.plexus.util.StringUtils;
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 Resource[] 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 List<String> locs = readLines(resource);
709 if (locations == null) {
710 locs = trim(locs);
711 return getResources(locs.toArray(new String[locs.size()]));
712 }
713 for (String location : locations) {
714 locs.add(location);
715 }
716 locs = trim(locs);
717 return getResources(locs.toArray(new String[locs.size()]));
718 }
719
720 protected List<String> trim(List<String> lines) {
721 List<String> trimmed = new ArrayList<String>();
722 for (String line : lines) {
723 trimmed.add(StringUtils.trim(line));
724 }
725 return trimmed;
726 }
727
728 protected List<String> readLines(Resource resource) {
729 InputStream in = null;
730 try {
731 in = resource.getInputStream();
732 if (StringUtils.isBlank(encoding)) {
733 return IOUtils.readLines(in);
734 } else {
735 return IOUtils.readLines(in, encoding);
736 }
737 } catch (IOException e) {
738 throw new IllegalStateException("Unexpected IO error", e);
739 } finally {
740 IOUtils.closeQuietly(in);
741 }
742 }
743
744 protected Resource[] getResources(String[] locations) throws MojoExecutionException {
745 ResourceLoader loader = new DefaultResourceLoader();
746 if (locations == null || locations.length == 0) {
747 return new Resource[] {};
748 }
749 List<Resource> resources = new ArrayList<Resource>();
750 for (int i = 0; i < locations.length; i++) {
751 String location = locations[i];
752 // Skip it if the location is empty
753 if (StringUtils.isEmpty(location)) {
754 continue;
755 }
756 Resource resource = loader.getResource(location);
757 if (!resource.exists()) {
758 // The location was not empty, but we couldn't find it
759 throw new MojoExecutionException("Resource " + location + " was not found");
760 }
761 resources.add(resource);
762 }
763 return resources.toArray(new Resource[resources.size()]);
764 }
765
766 protected void copy(Resource resource, File file) throws IOException {
767 InputStream in = resource.getInputStream();
768 OutputStream out = new FileOutputStream(file);
769 IOUtils.copyLarge(in, out);
770 }
771
772 /**
773 * Add user input of srcFiles to transaction list.
774 *
775 * @throws MojoExecutionException
776 */
777 protected void addResourcesToTransactions() throws MojoExecutionException {
778 String[] locations = getResourceLocations();
779 Resource[] resources = getResources(locations, resourceListingLocation);
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; i < resources.length; i++) {
787 Resource resource = resources[i];
788
789 if (!enableFiltering) {
790 createTransaction().setResource(resource);
791 continue;
792 }
793
794 String filename = resource.getFilename();
795 String basename = FileUtils.basename(filename);
796 String extension = FileUtils.extension(filename);
797 if (!extension.startsWith(".")) {
798 extension = "." + extension;
799 }
800 File sourceFile = FileUtils.createTempFile(basename, extension, null);
801 if (!getLog().isDebugEnabled()) {
802 sourceFile.deleteOnExit();
803 }
804
805 try {
806 copy(resource, sourceFile);
807 } catch (IOException e) {
808 throw new MojoExecutionException("Error copying resource " + resource + " to a local temporary file", e);
809 }
810
811 if (!enableFiltering) {
812 createTransaction().setSrc(sourceFile);
813 continue;
814 }
815
816 File targetFile = FileUtils.createTempFile(basename, extension, null);
817 if (!getLog().isDebugEnabled()) {
818 sourceFile.deleteOnExit();
819 }
820
821 request.setFrom(sourceFile);
822 request.setTo(targetFile);
823
824 try {
825 fileFilter.copyFile(request);
826 } catch (MavenFilteringException e) {
827 throw new MojoExecutionException(e.getMessage());
828 }
829
830 createTransaction().setSrc(targetFile);
831 }
832 }
833
834 /**
835 * Add user input of srcFiles to transaction list.
836 *
837 * @throws MojoExecutionException
838 */
839 private void addFilesToTransactions() throws MojoExecutionException {
840 File[] files = getSrcFiles();
841
842 MavenFileFilterRequest request = new MavenFileFilterRequest();
843 request.setEncoding(encoding);
844 request.setMavenSession(mavenSession);
845 request.setMavenProject(project);
846 request.setFiltering(enableFiltering);
847 for (int i = 0; files != null && i < files.length; ++i) {
848 if (files[i] != null && !files[i].exists()) {
849 throw new MojoExecutionException(files[i].getPath() + " not found.");
850 }
851
852 if (!enableFiltering) {
853 createTransaction().setSrc(files[i]);
854 continue;
855 }
856
857 File sourceFile = files[i];
858 String basename = FileUtils.basename(sourceFile.getName());
859 String extension = FileUtils.extension(sourceFile.getName());
860 if (!extension.startsWith(".")) {
861 extension = "." + extension;
862 }
863 File targetFile = FileUtils.createTempFile(basename, extension, null);
864 if (!getLog().isDebugEnabled()) {
865 targetFile.deleteOnExit();
866 }
867
868 request.setFrom(sourceFile);
869 request.setTo(targetFile);
870
871 try {
872 fileFilter.copyFile(request);
873 } catch (MavenFilteringException e) {
874 throw new MojoExecutionException(e.getMessage());
875 }
876
877 createTransaction().setSrc(targetFile);
878 }
879 }
880
881 /**
882 * Sort the transaction list.
883 */
884 protected void sortTransactions() {
885 switch (orderFile) {
886 case ASCENDING:
887 Collections.sort(transactions);
888 break;
889 case DESCENDING:
890 Collections.sort(transactions, Collections.reverseOrder());
891 break;
892 case NONE:
893 break;
894 default:
895 throw new RuntimeException("Unknown value for orderFile: " + orderFile);
896 }
897 }
898
899 /**
900 * Load username password from settings if user has not set them in JVM properties
901 *
902 * @throws MojoExecutionException
903 */
904 private void loadUserInfoFromSettings() throws MojoExecutionException {
905 if (this.settingsKey == null) {
906 this.settingsKey = getUrl();
907 }
908
909 if ((getUsername() == null || getPassword() == null) && (settings != null)) {
910 Server server = this.settings.getServer(this.settingsKey);
911
912 if (server != null) {
913 if (getUsername() == null) {
914 setUsername(server.getUsername());
915 }
916
917 if (getPassword() == null) {
918 setPassword(server.getPassword());
919 }
920 }
921 }
922
923 if (getUsername() == null) {
924 // allow emtpy username
925 setUsername("");
926 }
927
928 if (getPassword() == null) {
929 // allow emtpy password
930 setPassword("");
931 }
932 }
933
934 /**
935 * Creates a new Connection as using the driver, url, userid and password specified.
936 *
937 * The calling method is responsible for closing the connection.
938 *
939 * @return Connection the newly created connection.
940 * @throws MojoExecutionException
941 * if the UserId/Password/Url is not set or there is no suitable driver or the driver fails to load.
942 * @throws SQLException
943 * if there is problem getting connection with valid url
944 *
945 */
946 private Connection getConnection() throws MojoExecutionException, SQLException {
947 getLog().debug("connecting to " + getUrl());
948
949 Properties properties = getProperties();
950 Connection conn = getDriverInstance().connect(getUrl(), properties);
951
952 if (conn == null) {
953 // Driver doesn't understand the URL
954 throw new SQLException("No suitable Driver for " + getUrl());
955 }
956
957 conn.setAutoCommit(autocommit);
958 return conn;
959 }
960
961 protected boolean isBlankOrNone(String s) {
962 return StringUtils.isBlank(s) || "NONE".equals(s.trim());
963 }
964
965 protected boolean isSkipPassword() {
966 return enableAnonymousPassword && isBlankOrNone(getPassword());
967 }
968
969 protected Properties getProperties() throws MojoExecutionException {
970 Properties properties = new Properties();
971 properties.put("user", getUsername());
972
973 if (!isSkipPassword()) {
974 properties.put("password", getPassword());
975 }
976 properties.putAll(this.getDriverProperties());
977 return properties;
978 }
979
980 protected Driver getDriverInstance() throws MojoExecutionException {
981 try {
982 Class<?> dc = Class.forName(getDriver());
983 return (Driver) dc.newInstance();
984 } catch (ClassNotFoundException e) {
985 throw new MojoExecutionException("Driver class not found: " + getDriver(), e);
986 } catch (Exception e) {
987 throw new MojoExecutionException("Failure loading driver: " + getDriver(), e);
988 }
989 }
990
991 /**
992 * parse driverProperties into Properties set
993 *
994 * @return the driver properties
995 * @throws MojoExecutionException
996 */
997 protected Properties getDriverProperties() throws MojoExecutionException {
998 Properties properties = new Properties();
999
1000 if (!StringUtils.isEmpty(this.driverProperties)) {
1001 String[] tokens = StringUtils.split(this.driverProperties, ",");
1002 for (int i = 0; i < tokens.length; ++i) {
1003 String[] keyValueTokens = StringUtils.split(tokens[i].trim(), "=");
1004 if (keyValueTokens.length != 2) {
1005 throw new MojoExecutionException("Invalid JDBC Driver properties: " + this.driverProperties);
1006 }
1007
1008 properties.setProperty(keyValueTokens[0], keyValueTokens[1]);
1009
1010 }
1011 }
1012
1013 return properties;
1014 }
1015
1016 /**
1017 * read in lines and execute them
1018 *
1019 * @param reader
1020 * the reader
1021 * @param out
1022 * the outputstream
1023 * @throws SQLException
1024 * @throws IOException
1025 */
1026 private void runStatements(Reader reader, PrintStream out) throws SQLException, IOException {
1027 String line;
1028
1029 if (enableBlockMode) {
1030 // no need to parse the content, ship it directly to jdbc in one sql
1031 // statement
1032 line = IOUtil.toString(reader);
1033 execSQL(line, out);
1034 return;
1035 }
1036
1037 StringBuffer sql = new StringBuffer();
1038
1039 BufferedReader in = new BufferedReader(reader);
1040
1041 while ((line = in.readLine()) != null) {
1042 getLog().debug("line='" + line + "'");
1043 if (!keepFormat) {
1044 line = line.trim();
1045 }
1046
1047 if (!keepFormat) {
1048 if (line.startsWith("//")) {
1049 continue;
1050 }
1051 if (line.startsWith("--")) {
1052 continue;
1053 }
1054 StringTokenizer st = new StringTokenizer(line);
1055 if (st.hasMoreTokens()) {
1056 String token = st.nextToken();
1057 if ("REM".equalsIgnoreCase(token)) {
1058 continue;
1059 }
1060 }
1061 }
1062
1063 if (!keepFormat) {
1064 sql.append(" ").append(line);
1065 } else {
1066 sql.append("\n").append(line);
1067 }
1068
1069 // SQL defines "--" as a comment to EOL
1070 // but in Oracle it may contain a hint
1071 // so we cannot just remove it, instead we must end it
1072 if (!keepFormat) {
1073 if (SqlSplitter.containsSqlEnd(line, delimiter) == SqlSplitter.NO_END) {
1074 sql.append("\n");
1075 }
1076 }
1077
1078 if (isEnd(line)) {
1079 execSQL(sql.substring(0, sql.length() - delimiter.length()), out);
1080 sql.setLength(0); // clean buffer
1081 }
1082 }
1083
1084 // Catch any statements not followed by ;
1085 if (executeTrailingSQL && !sql.toString().equals("")) {
1086 execSQL(sql.toString(), out);
1087 }
1088 }
1089
1090 protected boolean isEnd(String line) {
1091 if (delimiterType.equals(DelimiterType.ROW)) {
1092 return line.trim().equals(delimiter);
1093 }
1094 int pos = SqlSplitter.containsSqlEnd(line, delimiter);
1095 if (delimiterType.equals(DelimiterType.NORMAL) && pos > 0) {
1096 return true;
1097 }
1098 return false;
1099 }
1100
1101 /**
1102 * Exec the sql statement.
1103 *
1104 * @param sql
1105 * query to execute
1106 * @param out
1107 * the outputstream
1108 */
1109 private void execSQL(String sql, PrintStream out) throws SQLException {
1110 // Check and ignore empty statements
1111 if ("".equals(sql.trim())) {
1112 return;
1113 }
1114
1115 ResultSet resultSet = null;
1116 try {
1117 totalStatements++;
1118 if (showSql) {
1119 getLog().info("SQL-" + totalStatements + ": " + sql);
1120 } else {
1121 getLog().debug("SQL-" + totalStatements + ": " + sql);
1122 }
1123
1124 boolean ret;
1125 int updateCountTotal = 0;
1126
1127 ret = statement.execute(sql);
1128 do {
1129 if (!ret) {
1130 int updateCount = statement.getUpdateCount();
1131 if (updateCount != -1) {
1132 updateCountTotal += updateCount;
1133 }
1134 } else {
1135 resultSet = statement.getResultSet();
1136 if (printResultSet) {
1137 printResultSet(resultSet, out);
1138 }
1139 }
1140 ret = statement.getMoreResults();
1141 } while (ret);
1142
1143 getLog().debug(updateCountTotal + " rows affected");
1144
1145 if (printResultSet) {
1146 StringBuffer line = new StringBuffer();
1147 line.append(updateCountTotal).append(" rows affected");
1148 out.println(line);
1149 }
1150
1151 SQLWarning warning = conn.getWarnings();
1152 while (warning != null) {
1153 getLog().debug(warning + " sql warning");
1154 warning = warning.getNextWarning();
1155 }
1156 conn.clearWarnings();
1157 successfulStatements++;
1158 } catch (SQLException e) {
1159 getLog().error("Failed to execute: " + sql);
1160 if (ON_ERROR_ABORT.equalsIgnoreCase(getOnError())) {
1161 throw e;
1162 }
1163 getLog().error(e.toString());
1164 } finally {
1165 if (resultSet != null) {
1166 resultSet.close();
1167 }
1168 }
1169 }
1170
1171 /**
1172 * print any results in the result set.
1173 *
1174 * @param rs
1175 * the resultset to print information about
1176 * @param out
1177 * the place to print results
1178 * @throws SQLException
1179 * on SQL problems.
1180 */
1181 private void printResultSet(ResultSet rs, PrintStream out) throws SQLException {
1182 if (rs != null) {
1183 getLog().debug("Processing new result set.");
1184 ResultSetMetaData md = rs.getMetaData();
1185 int columnCount = md.getColumnCount();
1186 StringBuffer line = new StringBuffer();
1187 if (showheaders) {
1188 boolean first = true;
1189 for (int col = 1; col <= columnCount; col++) {
1190 String columnValue = md.getColumnName(col);
1191
1192 if (columnValue != null) {
1193 columnValue = columnValue.trim();
1194
1195 if (",".equals(outputDelimiter)) {
1196 columnValue = StringEscapeUtils.escapeCsv(columnValue);
1197 }
1198 }
1199
1200 if (first) {
1201 first = false;
1202 } else {
1203 line.append(outputDelimiter);
1204 }
1205 line.append(columnValue);
1206 }
1207 out.println(line);
1208 line = new StringBuffer();
1209 }
1210 while (rs.next()) {
1211 boolean first = true;
1212 for (int col = 1; col <= columnCount; col++) {
1213 String columnValue = rs.getString(col);
1214 if (columnValue != null) {
1215 columnValue = columnValue.trim();
1216
1217 if (",".equals(outputDelimiter)) {
1218 columnValue = StringEscapeUtils.escapeCsv(columnValue);
1219 }
1220 }
1221
1222 if (first) {
1223 first = false;
1224 } else {
1225 line.append(outputDelimiter);
1226 }
1227 line.append(columnValue);
1228 }
1229 out.println(line);
1230 line = new StringBuffer();
1231 }
1232 }
1233 out.println();
1234 }
1235
1236 /**
1237 * Contains the definition of a new transaction element. Transactions allow several files or blocks of statements to be executed using
1238 * the same JDBC connection and commit operation in between.
1239 */
1240 protected class Transaction implements Comparable<Transaction> {
1241
1242 protected Resource resource = null;
1243
1244 protected File tSrcFile = null;
1245
1246 protected String tSqlCommand = "";
1247
1248 /**
1249 *
1250 */
1251 public void setResource(Resource resource) {
1252 this.resource = resource;
1253 }
1254
1255 /**
1256 *
1257 */
1258 public void setSrc(File src) {
1259 this.tSrcFile = src;
1260 }
1261
1262 /**
1263 *
1264 */
1265 public void addText(String sql) {
1266 this.tSqlCommand += sql;
1267 }
1268
1269 /**
1270 *
1271 */
1272 private void runTransaction(PrintStream out) throws IOException, SQLException {
1273 if (tSqlCommand.length() != 0) {
1274 getLog().info("Executing commands");
1275
1276 runStatements(new StringReader(tSqlCommand), out);
1277 }
1278
1279 if (tSrcFile != null) {
1280 getLog().info("Executing file: " + tSrcFile.getAbsolutePath());
1281
1282 Reader reader = null;
1283
1284 if (StringUtils.isEmpty(encoding)) {
1285 reader = new FileReader(tSrcFile);
1286 } else {
1287 reader = new InputStreamReader(new FileInputStream(tSrcFile), encoding);
1288 }
1289
1290 try {
1291 runStatements(reader, out);
1292 } finally {
1293 IOUtils.closeQuietly(reader);
1294 }
1295 }
1296
1297 if (resource != null) {
1298 getLog().info("Executing: " + resource.getURL());
1299
1300 Reader reader = null;
1301
1302 if (StringUtils.isEmpty(encoding)) {
1303 reader = new InputStreamReader(resource.getInputStream());
1304 } else {
1305 reader = new InputStreamReader(resource.getInputStream(), encoding);
1306 }
1307
1308 try {
1309 runStatements(reader, out);
1310 } finally {
1311 IOUtils.closeQuietly(reader);
1312 }
1313 }
1314 }
1315
1316 @Override
1317 public int compareTo(Transaction transaction) {
1318
1319 if (transaction.tSrcFile == null) {
1320 if (this.tSrcFile == null) {
1321 return 0;
1322 } else {
1323 return Integer.MAX_VALUE;
1324 }
1325 } else {
1326 if (this.tSrcFile == null) {
1327 return Integer.MIN_VALUE;
1328 } else {
1329 return this.tSrcFile.compareTo(transaction.tSrcFile);
1330 }
1331 }
1332 }
1333 }
1334
1335 //
1336 // helper accessors for unit test purposes
1337 //
1338
1339 public String getUsername() {
1340 return this.username;
1341 }
1342
1343 public void setUsername(String username) {
1344 this.username = username;
1345 }
1346
1347 public String getPassword() {
1348 return this.password;
1349 }
1350
1351 public void setPassword(String password) {
1352 this.password = password;
1353 }
1354
1355 public String getUrl() {
1356 return this.url;
1357 }
1358
1359 public void setUrl(String url) {
1360 this.url = url;
1361 }
1362
1363 public String getDriver() {
1364 return this.driver;
1365 }
1366
1367 public void setDriver(String driver) {
1368 this.driver = driver;
1369 }
1370
1371 void setAutocommit(boolean autocommit) {
1372 this.autocommit = autocommit;
1373 }
1374
1375 void setFileset(Fileset fileset) {
1376 this.fileset = fileset;
1377 }
1378
1379 public File[] getSrcFiles() {
1380 return this.srcFiles;
1381 }
1382
1383 public void setSrcFiles(File[] files) {
1384 this.srcFiles = files;
1385 }
1386
1387 /**
1388 * @deprecated use {@link #getSuccessfulStatements()}
1389 */
1390 @Deprecated
1391 int getGoodSqls() {
1392 return this.getSuccessfulStatements();
1393 }
1394
1395 /**
1396 * Number of SQL statements executed so far that caused errors.
1397 *
1398 * @return the number
1399 */
1400 public int getSuccessfulStatements() {
1401 return successfulStatements;
1402 }
1403
1404 /**
1405 * Number of SQL statements executed so far, including the ones that caused errors.
1406 *
1407 * @return the number
1408 */
1409 public int getTotalStatements() {
1410 return totalStatements;
1411 }
1412
1413 public String getOnError() {
1414 return this.onError;
1415 }
1416
1417 public void setOnError(String action) {
1418 if (ON_ERROR_ABORT.equalsIgnoreCase(action)) {
1419 this.onError = ON_ERROR_ABORT;
1420 } else if (ON_ERROR_CONTINUE.equalsIgnoreCase(action)) {
1421 this.onError = ON_ERROR_CONTINUE;
1422 } else if (ON_ERROR_ABORT_AFTER.equalsIgnoreCase(action)) {
1423 this.onError = ON_ERROR_ABORT_AFTER;
1424 } else {
1425 throw new IllegalArgumentException(action + " is not a valid value for onError, only '" + ON_ERROR_ABORT + "', '" + ON_ERROR_ABORT_AFTER + "', or '"
1426 + ON_ERROR_CONTINUE + "'.");
1427 }
1428 }
1429
1430 void setSettings(Settings settings) {
1431 this.settings = settings;
1432 }
1433
1434 void setSettingsKey(String key) {
1435 this.settingsKey = key;
1436 }
1437
1438 void setSkip(boolean skip) {
1439 this.skip = skip;
1440 }
1441
1442 public void setDriverProperties(String driverProperties) {
1443 this.driverProperties = driverProperties;
1444 }
1445
1446 public boolean isEnableBlockMode() {
1447 return enableBlockMode;
1448 }
1449
1450 public void setEnableBlockMode(boolean enableBlockMode) {
1451 this.enableBlockMode = enableBlockMode;
1452 }
1453
1454 public String getSqlCommand() {
1455 return sqlCommand;
1456 }
1457
1458 public void setSqlCommand(String sqlCommand) {
1459 this.sqlCommand = sqlCommand;
1460 }
1461
1462 public Vector<Transaction> getTransactions() {
1463 return transactions;
1464 }
1465
1466 public void setTransactions(Vector<Transaction> transactions) {
1467 this.transactions = transactions;
1468 }
1469
1470 public void setFileFilter(MavenFileFilter filter) {
1471 this.fileFilter = filter;
1472 }
1473
1474 public String[] getResourceLocations() {
1475 return resourceLocations;
1476 }
1477
1478 public void setResourceLocations(String[] resourceLocations) {
1479 this.resourceLocations = resourceLocations;
1480 }
1481
1482 public boolean isExecuteTrailingSQL() {
1483 return executeTrailingSQL;
1484 }
1485
1486 public void setExecuteTrailingSQL(boolean executeTrailingSQL) {
1487 this.executeTrailingSQL = executeTrailingSQL;
1488 }
1489
1490 public Order getOrderFile() {
1491 return orderFile;
1492 }
1493
1494 public void setOrderFile(String orderFile) {
1495 this.orderFile = Order.valueOf(orderFile.toUpperCase());
1496 }
1497
1498 public String getResourceListingLocation() {
1499 return resourceListingLocation;
1500 }
1501
1502 public void setResourceListingLocation(String resourceListingLocation) {
1503 this.resourceListingLocation = resourceListingLocation;
1504 }
1505 }