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