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