%line	%branch
org.apache.torque.task.TorqueSQLExec$Transaction	0%	0%

1		package org.apache.torque.task;
2
3		/*
4		* Licensed to the Apache Software Foundation (ASF) under one
5		* or more contributor license agreements. See the NOTICE file
6		* distributed with this work for additional information
7		* regarding copyright ownership. The ASF licenses this file
8		* to you under the Apache License, Version 2.0 (the
9		* "License"); you may not use this file except in compliance
10		* with the License. You may obtain a copy of the License at
11		*
12		* http://www.apache.org/licenses/LICENSE-2.0
13		*
14		* Unless required by applicable law or agreed to in writing,
15		* software distributed under the License is distributed on an
16		* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
17		* KIND, either express or implied. See the License for the
18		* specific language governing permissions and limitations
19		* under the License.
20		*/
21
22		import java.io.BufferedOutputStream;
23		import java.io.BufferedReader;
24		import java.io.File;
25		import java.io.FileInputStream;
26		import java.io.FileOutputStream;
27		import java.io.FileReader;
28		import java.io.IOException;
29		import java.io.InputStreamReader;
30		import java.io.PrintStream;
31		import java.io.Reader;
32		import java.io.StringReader;
33		import java.sql.Connection;
34		import java.sql.DatabaseMetaData;
35		import java.sql.Driver;
36		import java.sql.ResultSet;
37		import java.sql.ResultSetMetaData;
38		import java.sql.SQLException;
39		import java.sql.SQLWarning;
40		import java.sql.Statement;
41		import java.util.ArrayList;
42		import java.util.HashMap;
43		import java.util.Iterator;
44		import java.util.List;
45		import java.util.Map;
46		import java.util.Properties;
47
48		import org.apache.commons.lang.StringUtils;
49		import org.apache.tools.ant.AntClassLoader;
50		import org.apache.tools.ant.BuildException;
51		import org.apache.tools.ant.Project;
52		import org.apache.tools.ant.PropertyHelper;
53		import org.apache.tools.ant.Task;
54		import org.apache.tools.ant.types.EnumeratedAttribute;
55		import org.apache.tools.ant.types.Path;
56		import org.apache.tools.ant.types.Reference;
57
58		/**
59		* This task uses an SQL -> Database map in the form of a properties
60		* file to insert each SQL file listed into its designated database.
61		*
62		* @author <a href="mailto:jeff@custommonkey.org">Jeff Martin</a>
63		* @author <a href="mailto:gholam@xtra.co.nz">Michael McCallum</A>
64		* @author <a href="mailto:tim.stephenson@sybase.com">Tim Stephenson</A>
65		* @author <a href="mailto:jvanzyl@apache.org">Jason van Zyl</A>
66		* @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
67		* @version $Id: TorqueSQLExec.java 473814 2006-11-11 22:30:30Z tv $
68		*/
69		public class TorqueSQLExec extends Task
70		{
71		private int goodSql = 0;
72		private int totalSql = 0;
73		private Path classpath;
74		private AntClassLoader loader;
75
76		/**
77		*
78		*/
79		public static class DelimiterType extends EnumeratedAttribute
80		{
81		public static final String NORMAL = "normal";
82		public static final String ROW = "row";
83
84		public String[] getValues()
85		{
86		return new String[] {NORMAL, ROW};
87		}
88		}
89
90		/** Database connection */
91		private Connection conn = null;
92
93		/** Autocommit flag. Default value is false */
94		private boolean autocommit = false;
95
96		/** SQL statement */
97		private Statement statement = null;
98
99		/** DB driver. */
100		private String driver = null;
101
102		/** DB url. */
103		private String url = null;
104
105		/** User name. */
106		private String userId = null;
107
108		/** Password */
109		private String password = null;
110
111		/** SQL Statement delimiter */
112		private String delimiter = ";";
113
114		/**
115		* The delimiter type indicating whether the delimiter will
116		* only be recognized on a line by itself
117		*/
118		private String delimiterType = DelimiterType.NORMAL;
119
120		/** Print SQL results. */
121		private boolean print = false;
122
123		/** Print header columns. */
124		private boolean showheaders = true;
125
126		/** Results Output file. */
127		private File output = null;
128
129		/** RDBMS Product needed for this SQL. */
130		private String rdbms = null;
131
132		/** RDBMS Version needed for this SQL. */
133		private String version = null;
134
135		/** Action to perform if an error is found */
136		private String onError = "abort";
137
138		/** Encoding to use when reading SQL statements from a file */
139		private String encoding = null;
140
141		/** Src directory for the files listed in the sqldbmap. */
142		private String srcDir;
143
144		/** Properties file that maps an individual SQL file to a database. */
145		private File sqldbmap;
146
147		/**
148		* Set the sqldbmap properties file.
149		*
150		* @param sqldbmap filename for the sqldbmap
151		*/
152		public void setSqlDbMap(String sqldbmap)
153		{
154		this.sqldbmap = getProject().resolveFile(sqldbmap);
155		}
156
157		/**
158		* Get the sqldbmap properties file.
159		*
160		* @return filename for the sqldbmap
161		*/
162		public File getSqlDbMap()
163		{
164		return sqldbmap;
165		}
166
167		/**
168		* Set the src directory for the sql files listed in the sqldbmap file.
169		*
170		* @param srcDir sql source directory
171		*/
172		public void setSrcDir(String srcDir)
173		{
174		this.srcDir = getProject().resolveFile(srcDir).toString();
175		}
176
177		/**
178		* Get the src directory for the sql files listed in the sqldbmap file.
179		*
180		* @return sql source directory
181		*/
182		public String getSrcDir()
183		{
184		return srcDir;
185		}
186
187		/**
188		* Set the classpath for loading the driver.
189		*
190		* @param classpath the classpath
191		*/
192		public void setClasspath(Path classpath)
193		{
194		if (this.classpath == null)
195		{
196		this.classpath = classpath;
197		}
198		else
199		{
200		this.classpath.append(classpath);
201		}
202		}
203
204		/**
205		* Create the classpath for loading the driver.
206		*
207		* @return the classpath
208		*/
209		public Path createClasspath()
210		{
211		if (this.classpath == null)
212		{
213		this.classpath = new Path(getProject());
214		}
215		return this.classpath.createPath();
216		}
217
218		/**
219		* Set the classpath for loading the driver using the classpath reference.
220		*
221		* @param r reference to the classpath
222		*/
223		public void setClasspathRef(Reference r)
224		{
225		createClasspath().setRefid(r);
226		}
227
228		/**
229		* Set the sql command to execute
230		*
231		* @param sql sql command to execute
232		* @deprecated This method has no effect and
233		* will be removed in a future version.
234		*/
235		public void addText(String sql)
236		{
237		}
238
239		/**
240		* Set the JDBC driver to be used.
241		*
242		* @param driver driver class name
243		*/
244		public void setDriver(String driver)
245		{
246		this.driver = driver;
247		}
248
249		/**
250		* Set the DB connection url.
251		*
252		* @param url connection url
253		*/
254		public void setUrl(String url)
255		{
256		this.url = url;
257		}
258
259		/**
260		* Set the user name for the DB connection.
261		*
262		* @param userId database user
263		*/
264		public void setUserid(String userId)
265		{
266		this.userId = userId;
267		}
268
269		/**
270		* Set the file encoding to use on the sql files read in
271		*
272		* @param encoding the encoding to use on the files
273		*/
274		public void setEncoding(String encoding)
275		{
276		this.encoding = encoding;
277		}
278
279		/**
280		* Set the password for the DB connection.
281		*
282		* @param password database password
283		*/
284		public void setPassword(String password)
285		{
286		this.password = password;
287		}
288
289		/**
290		* Set the autocommit flag for the DB connection.
291		*
292		* @param autocommit the autocommit flag
293		*/
294		public void setAutocommit(boolean autocommit)
295		{
296		this.autocommit = autocommit;
297		}
298
299		/**
300		* Set the statement delimiter.
301		*
302		* <p>For example, set this to "go" and delimitertype to "ROW" for
303		* Sybase ASE or MS SQL Server.</p>
304		*
305		* @param delimiter
306		*/
307		public void setDelimiter(String delimiter)
308		{
309		this.delimiter = delimiter;
310		}
311
312		/**
313		* Set the Delimiter type for this sql task. The delimiter type takes two
314		* values - normal and row. Normal means that any occurence of the delimiter
315		* terminate the SQL command whereas with row, only a line containing just
316		* the delimiter is recognized as the end of the command.
317		*
318		* @param delimiterType
319		*/
320		public void setDelimiterType(DelimiterType delimiterType)
321		{
322		this.delimiterType = delimiterType.getValue();
323		}
324
325		/**
326		* Set the print flag.
327		*
328		* @param print
329		*/
330		public void setPrint(boolean print)
331		{
332		this.print = print;
333		}
334
335		/**
336		* Set the showheaders flag.
337		*
338		* @param showheaders
339		*/
340		public void setShowheaders(boolean showheaders)
341		{
342		this.showheaders = showheaders;
343		}
344
345		/**
346		* Set the output file.
347		*
348		* @param output
349		*/
350		public void setOutput(File output)
351		{
352		this.output = output;
353		}
354
355		/**
356		* Set the rdbms required
357		*
358		* @param vendor
359		*/
360		public void setRdbms(String vendor)
361		{
362		this.rdbms = vendor.toLowerCase();
363		}
364
365		/**
366		* Set the version required
367		*
368		* @param version
369		*/
370		public void setVersion(String version)
371		{
372		this.version = version.toLowerCase();
373		}
374
375		/**
376		* Set the action to perform onerror
377		*
378		* @param action
379		*/
380		public void setOnerror(OnError action)
381		{
382		this.onError = action.getValue();
383		}
384
385		/**
386		* Load the sql file and then execute it
387		*
388		* @throws BuildException
389		*/
390		public void execute() throws BuildException
391		{
392		if (sqldbmap == null || getSqlDbMap().exists() == false)
393		{
394		throw new BuildException("You haven't provided an sqldbmap, or "
395		+ "the one you specified doesn't exist: " + sqldbmap);
396		}
397
398		if (driver == null)
399		{
400		throw new BuildException("Driver attribute must be set!", getLocation());
401		}
402		if (userId == null)
403		{
404		throw new BuildException("User Id attribute must be set!",
405		getLocation());
406		}
407		if (password == null)
408		{
409		throw new BuildException("Password attribute must be set!",
410		getLocation());
411		}
412		if (url == null)
413		{
414		throw new BuildException("Url attribute must be set!", getLocation());
415		}
416
417		Properties map = new Properties();
418
419		try
420		{
421		FileInputStream fis = new FileInputStream(getSqlDbMap());
422		map.load(fis);
423		fis.close();
424		}
425		catch (IOException ioe)
426		{
427		throw new BuildException("Cannot open and process the sqldbmap!");
428		}
429
430		Map databases = new HashMap();
431
432		Iterator eachFileName = map.keySet().iterator();
433		while (eachFileName.hasNext())
434		{
435		String sqlfile = (String) eachFileName.next();
436		String database = map.getProperty(sqlfile);
437
438		List files = (List) databases.get(database);
439
440		if (files == null)
441		{
442		files = new ArrayList();
443		databases.put(database, files);
444		}
445
446		// We want to make sure that the base schemas
447		// are inserted first.
448		if (sqlfile.indexOf("schema.sql") != -1)
449		{
450		files.add(0, sqlfile);
451		}
452		else
453		{
454		files.add(sqlfile);
455		}
456		}
457
458		Iterator eachDatabase = databases.keySet().iterator();
459		while (eachDatabase.hasNext())
460		{
461		String db = (String) eachDatabase.next();
462		List transactions = new ArrayList();
463		eachFileName = ((List) databases.get(db)).iterator();
464		while (eachFileName.hasNext())
465		{
466		String fileName = (String) eachFileName.next();
467		File file = new File(srcDir, fileName);
468
469		if (file.exists())
470		{
471		Transaction transaction = new Transaction();
472		transaction.setSrc(file);
473		transactions.add(transaction);
474		}
475		else
476		{
477		System.out.println("File '" + file.getAbsolutePath()
478		+ "' in sqldbmap does not exist, so skipping it.");
479		}
480		}
481
482		insertDatabaseSqlFiles(url, db, transactions);
483		}
484		}
485
486		/**
487		* Take the base url, the target database and insert a set of SQL
488		* files into the target database.
489		*
490		* @param url
491		* @param database
492		* @param transactions
493		*/
494		private void insertDatabaseSqlFiles(String url, String database,
495		List transactions)
496		{
497		url = StringUtils.replace(url, "@DB@", database);
498		System.out.println("Our new url -> " + url);
499
500		Driver driverInstance = null;
501		try
502		{
503		Class dc;
504		if (classpath != null)
505		{
506		log("Loading " + driver
507		+ " using AntClassLoader with classpath " + classpath,
508		Project.MSG_VERBOSE);
509
510		loader = new AntClassLoader(getProject(), classpath);
511		dc = loader.loadClass(driver);
512		}
513		else
514		{
515		log("Loading " + driver + " using system loader.",
516		Project.MSG_VERBOSE);
517		dc = Class.forName(driver);
518		}
519		driverInstance = (Driver) dc.newInstance();
520		}
521		catch (ClassNotFoundException e)
522		{
523		throw new BuildException("Class Not Found: JDBC driver " + driver
524		+ " could not be loaded", getLocation());
525		}
526		catch (IllegalAccessException e)
527		{
528		throw new BuildException("Illegal Access: JDBC driver " + driver
529		+ " could not be loaded", getLocation());
530		}
531		catch (InstantiationException e)
532		{
533		throw new BuildException("Instantiation Exception: JDBC driver "
534		+ driver + " could not be loaded", getLocation());
535		}
536
537		try
538		{
539		log("connecting to " + url, Project.MSG_VERBOSE);
540		Properties info = new Properties();
541		info.put("user", userId);
542		info.put("password", password);
543		conn = driverInstance.connect(url, info);
544
545		if (conn == null)
546		{
547		// Driver doesn't understand the URL
548		throw new SQLException("No suitable Driver for " + url);
549		}
550
551		if (!isValidRdbms(conn))
552		{
553		return;
554		}
555
556		conn.setAutoCommit(autocommit);
557		statement = conn.createStatement();
558		PrintStream out = System.out;
559		try
560		{
561		if (output != null)
562		{
563		log("Opening PrintStream to output file " + output,
564		Project.MSG_VERBOSE);
565		out = new PrintStream(class="keyword">new BufferedOutputStream(
566		new FileOutputStream(output)));
567		}
568
569		// Process all transactions
570		for (Iterator it = transactions.iterator(); it.hasNext();)
571		{
572		Transaction transaction = (Transaction) it.next();
573		transaction.runTransaction(out);
574		if (!autocommit)
575		{
576		log("Commiting transaction", Project.MSG_VERBOSE);
577		conn.commit();
578		}
579		}
580		}
581		finally
582		{
583		if (out != null && out != System.out)
584		{
585		out.close();
586		}
587		}
588		}
589		catch (IOException e)
590		{
591		if (!autocommit && conn != null && onError.equals("abort"))
592		{
593		try
594		{
595		conn.rollback();
596		}
597		catch (SQLException ex)
598		{
599		// do nothing.
600		}
601		}
602		throw new BuildException(e, getLocation());
603		}
604		catch (SQLException e)
605		{
606		if (!autocommit && conn != null && onError.equals("abort"))
607		{
608		try
609		{
610		conn.rollback();
611		}
612		catch (SQLException ex)
613		{
614		// do nothing.
615		}
616		}
617		throw new BuildException(e, getLocation());
618		}
619		finally
620		{
621		try
622		{
623		if (statement != null)
624		{
625		statement.close();
626		}
627		if (conn != null)
628		{
629		conn.close();
630		}
631		}
632		catch (SQLException e)
633		{
634		}
635		}
636
637		System.out.println(goodSql + " of " + totalSql
638		+ " SQL statements executed successfully");
639		}
640
641		/**
642		* Read the statements from the .sql file and execute them.
643		* Lines starting with '//', '--' or 'REM ' are ignored.
644		*
645		* @param reader
646		* @param out
647		* @throws SQLException
648		* @throws IOException
649		*/
650		protected void runStatements(Reader reader, PrintStream out)
651		throws SQLException, IOException
652		{
653		String sql = "";
654		String line = "";
655
656		BufferedReader in = new BufferedReader(reader);
657		PropertyHelper ph = PropertyHelper.getPropertyHelper(getProject());
658
659		try
660		{
661		while ((line = in.readLine()) != null)
662		{
663		line = line.trim();
664		line = ph.replaceProperties("", line, getProject().getProperties());
665		if (line.startsWith("//") || line.startsWith("--"))
666		{
667		continue;
668		}
669		if (line.length() > 4
670		&& line.substring(0, 4).equalsIgnoreCase("REM "))
671		{
672		continue;
673		}
674
675		sql += " " + line;
676		sql = sql.trim();
677
678		// SQL defines "--" as a comment to EOL
679		// and in Oracle it may contain a hint
680		// so we cannot just remove it, instead we must end it
681		if (line.indexOf("--") >= 0)
682		{
683		sql += "\n";
684		}
685
686		if (delimiterType.equals(DelimiterType.NORMAL)
687		&& sql.endsWith(delimiter)
688		|| delimiterType.equals(DelimiterType.ROW)
689		&& line.equals(delimiter))
690		{
691		log("SQL: " + sql, Project.MSG_VERBOSE);
692		execSQL(sql.substring(0, sql.length() - delimiter.length()),
693		out);
694		sql = "";
695		}
696		}
697
698		// Catch any statements not followed by ;
699		if (!sql.equals(""))
700		{
701		execSQL(sql, out);
702		}
703		}
704		catch (SQLException e)
705		{
706		throw e;
707		}
708		}
709
710		/**
711		* Verify if connected to the correct RDBMS
712		*
713		* @param conn
714		*/
715		protected boolean isValidRdbms(Connection conn)
716		{
717		if (rdbms == null && version == class="keyword">null)
718		{
719		return true;
720		}
721
722		try
723		{
724		DatabaseMetaData dmd = conn.getMetaData();
725
726		if (rdbms != null)
727		{
728		String theVendor = dmd.getDatabaseProductName().toLowerCase();
729
730		log("RDBMS = " + theVendor, Project.MSG_VERBOSE);
731		if (theVendor == null || theVendor.indexOf(rdbms) < 0)
732		{
733		log("Not the required RDBMS: "
734		+ rdbms, Project.MSG_VERBOSE);
735		return false;
736		}
737		}
738
739		if (version != null)
740		{
741		String theVersion = dmd.getDatabaseProductVersion()
742		.toLowerCase();
743
744		log("Version = " + theVersion, Project.MSG_VERBOSE);
745		if (theVersion == null || !(theVersion.startsWith(version)
746		|| theVersion.indexOf(" " + version) >= 0))
747		{
748		log("Not the required version: \"" + version + "\"",
749		Project.MSG_VERBOSE);
750		return false;
751		}
752		}
753		}
754		catch (SQLException e)
755		{
756		// Could not get the required information
757		log("Failed to obtain required RDBMS information", Project.MSG_ERR);
758		return false;
759		}
760
761		return true;
762		}
763
764		/**
765		* Exec the sql statement.
766		*
767		* @param sql
768		* @param out
769		* @throws SQLException
770		*/
771		protected void execSQL(String sql, PrintStream out) throws SQLException
772		{
773		// Check and ignore empty statements
774		if ("".equals(sql.trim()))
775		{
776		return;
777		}
778
779		try
780		{
781		totalSql++;
782		if (!statement.execute(sql))
783		{
784		log(statement.getUpdateCount() + " rows affected",
785		Project.MSG_VERBOSE);
786		}
787		else
788		{
789		if (print)
790		{
791		printResults(out);
792		}
793		}
794
795		SQLWarning warning = conn.getWarnings();
796		while (warning != null)
797		{
798		log(warning + " sql warning", Project.MSG_VERBOSE);
799		warning = warning.getNextWarning();
800		}
801		conn.clearWarnings();
802		goodSql++;
803		}
804		catch (SQLException e)
805		{
806		System.out.println("Failed to execute: " + sql);
807		if (!onError.equals("continue"))
808		{
809		throw e;
810		}
811		log(e.toString(), Project.MSG_ERR);
812		}
813		}
814
815		/**
816		* print any results in the statement.
817		*
818		* @param out
819		* @throws SQLException
820		*/
821		protected void printResults(PrintStream out) throws java.sql.SQLException
822		{
823		ResultSet rs = null;
824		do
825		{
826		rs = statement.getResultSet();
827		if (rs != null)
828		{
829		log("Processing new result set.", Project.MSG_VERBOSE);
830		ResultSetMetaData md = rs.getMetaData();
831		int columnCount = md.getColumnCount();
832		StringBuffer line = new StringBuffer();
833		if (showheaders)
834		{
835		for (int col = 1; col < columnCount; col++)
836		{
837		line.append(md.getColumnName(col));
838		line.append(",");
839		}
840		line.append(md.getColumnName(columnCount));
841		out.println(line);
842		line.setLength(0);
843		}
844		while (rs.next())
845		{
846		boolean first = true;
847		for (int col = 1; col <= columnCount; col++)
848		{
849		String columnValue = rs.getString(col);
850		if (columnValue != null)
851		{
852		columnValue = columnValue.trim();
853		}
854
855		if (first)
856		{
857		first = false;
858		}
859		else
860		{
861		line.append(",");
862		}
863		line.append(columnValue);
864		}
865		out.println(line);
866		line.setLength(0);
867		}
868		}
869		}
870		while (statement.getMoreResults());
871		out.println();
872		}
873
874		/**
875		* Enumerated attribute with the values "continue", "stop" and "abort"
876		* for the onerror attribute.
877		*/
878		public static class OnError extends EnumeratedAttribute
879		{
880		public static final String CONTINUE = "continue";
881
882		public static final String STOP = "stop";
883
884		public static final String ABORT = "abort";
885
886		public String[] getValues()
887		{
888		return new String[] {CONTINUE, STOP, ABORT};
889		}
890		}
891
892		/**
893		* Contains the definition of a new transaction element.
894		* Transactions allow several files or blocks of statements
895		* to be executed using the same JDBC connection and commit
896		* operation in between.
897		*/
898	0	public class Transaction
899		{
900	0	private File tSrcFile = null;
901	0	private String tSqlCommand = "";
902
903		public void setSrc(File src)
904		{
905	0	this.tSrcFile = src;
906	0	}
907
908		public void addText(String sql)
909		{
910	0	this.tSqlCommand += sql;
911	0	}
912
913		private void runTransaction(PrintStream out)
914		throws IOException, SQLException
915		{
916	0	if (tSqlCommand.length() != 0)
917		{
918	0	log("Executing commands", Project.MSG_INFO);
919	0	runStatements(new StringReader(tSqlCommand), out);
920		}
921
922	0	if (tSrcFile != null)
923		{
924	0	System.out.println("Executing file: " + tSrcFile.getAbsolutePath());
925	0	Reader reader = (encoding == null) ? new FileReader(tSrcFile)
926		: new InputStreamReader(class="keyword">new FileInputStream(tSrcFile),
927		encoding);
928	0	runStatements(reader, out);
929	0	reader.close();
930		}
931	0	}
932		}
933		}

This report is generated by jcoverage, Maven and Maven JCoverage Plugin.