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