File D:\maven-qcr-plugin-testsunitaires\qcr4java\h2-1.3.170-sources\target\site\qc\lecode\tools\RunScript.java.html

1 /*
2 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
3 * Version 1.0, and under the Eclipse Public License, Version 1.0
4 * (http://h2database.com/html/license.html).
5 * Initial Developer: H2 Group
6 */
7 package org.h2.tools;
8
9 import java.io.BufferedInputStream;
10 import java.io.IOException;
11 import java.io.InputStream;
12 import java.io.InputStreamReader;
13 import java.io.Reader;
14 import java.sql.Connection;
15 import java.sql.DriverManager;
16 import java.sql.ResultSet;
17 import java.sql.SQLException;
18 import java.sql.Statement;
19 import org.h2.constant.SysProperties;
20 import org.h2.engine.Constants;
21 import org.h2.message.DbException;
22 import org.h2.store.fs.FileUtils;
23 import org.h2.util.Utils;
24 import org.h2.util.IOUtils;
25 import org.h2.util.JdbcUtils;
26 import org.h2.util.ScriptReader;
27 import org.h2.util.StringUtils;
28 import org.h2.util.Tool;
29
30 /**
31 * Runs a SQL script against a database.
32 * @h2.resource
33 */
34 public class RunScript extends Tool {
35
36 private boolean showResults;
37 private boolean checkResults;
38
39 /**
40 * Options are case sensitive. Supported options are:
41 * <table>
42 * <tr><td>[-help] or [-?]</td>
43 * <td>Print the list of options</td></tr>
44 * <tr><td>[-url "&lt;url&gt;"]</td>
45 * <td>The database URL (jdbc:...)</td></tr>
46 * <tr><td>[-user &lt;user&gt;]</td>
47 * <td>The user name (default: sa)</td></tr>
48 * <tr><td>[-password &lt;pwd&gt;]</td>
49 * <td>The password</td></tr>
50 * <tr><td>[-script &lt;file&gt;]</td>
51 * <td>The script file to run (default: backup.sql)</td></tr>
52 * <tr><td>[-driver &lt;class&gt;]</td>
53 * <td>The JDBC driver class to use (not required in most cases)</td></tr>
54 * <tr><td>[-showResults]</td>
55 * <td>Show the statements and the results of queries</td></tr>
56 * <tr><td>[-checkResults]</td>
57 * <td>Check if the query results match the expected results</td></tr>
58 * <tr><td>[-continueOnError]</td>
59 * <td>Continue even if the script contains errors</td></tr>
60 * <tr><td>[-options ...]</td>
61 * <td>RUNSCRIPT options (embedded H2; -*Results not supported)</td></tr>
62 * </table>
63 * @h2.resource
64 *
65 * @param args the command line arguments
66 */
67 public static void main(String... args) throws SQLException {
68 new RunScript().runTool(args);
69 }
70
71 /**
72 * Executes the contents of a SQL script file against a database.
73 * This tool is usually used to create a database from script.
74 * It can also be used to analyze performance problems by running
75 * the tool using Java profiler settings such as:
76 * <pre>
77 * java -Xrunhprof:cpu=samples,depth=16 ...
78 * </pre>
79 * To include local files when using remote databases, use the special
80 * syntax:
81 * <pre>
82 * &#064;INCLUDE fileName
83 * </pre>
84 * This syntax is only supported by this tool. Embedded RUNSCRIPT SQL
85 * statements will be executed by the database.
86 *
87 * @param args the command line arguments
88 */
89 public void runTool(String... args) throws SQLException {
90 String url = null;
91 String user = "sa";
92 String password = "";
93 String script = "backup.sql";
94 String options = null;
95 boolean continueOnError = false;
96 boolean showTime = false;
97 for (int i = 0; args != null && i < args.length; i++) {
98 String arg = args[i];
99 if (arg.equals("-url")) {
100 url = args[++i];
101 } else if (arg.equals("-user")) {
102 user = args[++i];
103 } else if (arg.equals("-password")) {
104 password = args[++i];
105 } else if (arg.equals("-continueOnError")) {
106 continueOnError = true;
107 } else if (arg.equals("-checkResults")) {
108 checkResults = true;
109 } else if (arg.equals("-showResults")) {
110 showResults = true;
111 } else if (arg.equals("-script")) {
112 script = args[++i];
113 } else if (arg.equals("-time")) {
114 showTime = true;
115 } else if (arg.equals("-driver")) {
116 String driver = args[++i];
117 Utils.loadUserClass(driver);
118 } else if (arg.equals("-options")) {
119 StringBuilder buff = new StringBuilder();
120 i++;
121 for (; i < args.length; i++) {
122 buff.append(' ').append(args[i]);
123 }
124 options = buff.toString();
125 } else if (arg.equals("-help") || arg.equals("-?")) {
126 showUsage();
127 return;
128 } else {
129 showUsageAndThrowUnsupportedOption(arg);
130 }
131 }
132 if (url == null) {
133 showUsage();
134 throw new SQLException("URL not set");
135 }
136 long time = System.currentTimeMillis();
137 if (options != null) {
138 processRunscript(url, user, password, script, options);
139 } else {
140 process(url, user, password, script, null, continueOnError);
141 }
142 if (showTime) {
143 time = System.currentTimeMillis() - time;
144 out.println("Done in " + time + " ms");
145 }
146 }
147
148 /**
149 * Executes the SQL commands read from the reader against a database.
150 *
151 * @param conn the connection to a database
152 * @param reader the reader
153 * @return the last result set
154 */
155 public static ResultSet execute(Connection conn, Reader reader) throws SQLException {
156 Statement stat = conn.createStatement();
157 ResultSet rs = null;
158 ScriptReader r = new ScriptReader(reader);
159 while (true) {
160 String sql = r.readStatement();
161 if (sql == null) {
162 break;
163 }
164 if (sql.trim().length() == 0) {
165 continue;
166 }
167 boolean resultSet = stat.execute(sql);
168 if (resultSet) {
169 if (rs != null) {
170 rs.close();
171 rs = null;
172 }
173 rs = stat.getResultSet();
174 }
175 }
176 return rs;
177 }
178
179 private void process(Connection conn, String fileName,
180 boolean continueOnError, String charsetName) throws SQLException, IOException {
181 InputStream in = FileUtils.newInputStream(fileName);
182 String path = FileUtils.getParent(fileName);
183 try {
184 in = new BufferedInputStream(in, Constants.IO_BUFFER_SIZE);
185 Reader reader = new InputStreamReader(in, charsetName);
186 process(conn, continueOnError, path, reader, charsetName);
187 } finally {
188 IOUtils.closeSilently(in);
189 }
190 }
191
192 private void process(Connection conn, boolean continueOnError,
193 String path, Reader reader, String charsetName) throws SQLException, IOException {
194 Statement stat = conn.createStatement();
195 ScriptReader r = new ScriptReader(reader);
196 while (true) {
197 String sql = r.readStatement();
198 if (sql == null) {
199 break;
200 }
201 String trim = sql.trim();
202 if (trim.length() == 0) {
203 continue;
204 }
205 if (trim.startsWith("@") && StringUtils.toUpperEnglish(trim).startsWith("@INCLUDE")) {
206 sql = trim;
207 sql = sql.substring("@INCLUDE".length()).trim();
208 if (!FileUtils.isAbsolute(sql)) {
209 sql = path + SysProperties.FILE_SEPARATOR + sql;
210 }
211 process(conn, sql, continueOnError, charsetName);
212 } else {
213 try {
214 if (showResults && !trim.startsWith("-->")) {
215 out.print(sql + ";");
216 }
217 if (showResults || checkResults) {
218 boolean query = stat.execute(sql);
219 if (query) {
220 ResultSet rs = stat.getResultSet();
221 int columns = rs.getMetaData().getColumnCount();
222 StringBuilder buff = new StringBuilder();
223 while (rs.next()) {
224 buff.append("\n-->");
225 for (int i = 0; i < columns; i++) {
226 String s = rs.getString(i + 1);
227 if (s != null) {
228 s = StringUtils.replaceAll(s, "\r\n", "\n");
229 s = StringUtils.replaceAll(s, "\n", "\n--> ");
230 s = StringUtils.replaceAll(s, "\r", "\r--> ");
231 }
232 buff.append(' ').append(s);
233 }
234 }
235 buff.append("\n;");
236 String result = buff.toString();
237 if (showResults) {
238 out.print(result);
239 }
240 if (checkResults) {
241 String expected = r.readStatement() + ";";
242 expected = StringUtils.replaceAll(expected, "\r\n", "\n");
243 expected = StringUtils.replaceAll(expected, "\r", "\n");
244 if (!expected.equals(result)) {
245 expected = StringUtils.replaceAll(expected, " ", "+");
246 result = StringUtils.replaceAll(result, " ", "+");
247 throw new SQLException("Unexpected output for:\n" + sql.trim() + "\nGot:\n" + result + "\nExpected:\n" + expected);
248 }
249 }
250
251 }
252 } else {
253 stat.execute(sql);
254 }
255 } catch (Exception e) {
256 if (continueOnError) {
257 e.printStackTrace(out);
258 } else {
259 throw DbException.toSQLException(e);
260 }
261 }
262 }
263 }
264 }
265
266 private static void processRunscript(String url, String user, String password,
267 String fileName, String options) throws SQLException {
268 Connection conn = null;
269 Statement stat = null;
270 try {
271 org.h2.Driver.load();
272 conn = DriverManager.getConnection(url, user, password);
273 stat = conn.createStatement();
274 String sql = "RUNSCRIPT FROM '" + fileName + "' " + options;
275 stat.execute(sql);
276 } finally {
277 JdbcUtils.closeSilently(stat);
278 JdbcUtils.closeSilently(conn);
279 }
280 }
281
282 /**
283 * Executes the SQL commands in a script file against a database.
284 *
285 * @param url the database URL
286 * @param user the user name
287 * @param password the password
288 * @param fileName the script file
289 * @param charsetName the character set name or null for UTF-8
290 * @param continueOnError if execution should be continued if an error occurs
291 */
292 public static void execute(String url, String user, String password,
293 String fileName, String charsetName, boolean continueOnError) throws SQLException {
294 new RunScript().process(url, user, password, fileName, charsetName, continueOnError);
295 }
296
297 /**
298 * Executes the SQL commands in a script file against a database.
299 *
300 * @param url the database URL
301 * @param user the user name
302 * @param password the password
303 * @param fileName the script file
304 * @param charsetName the character set name or null for UTF-8
305 * @param continueOnError if execution should be continued if an error occurs
306 */
307 void process(String url, String user, String password,
308 String fileName, String charsetName,
309 boolean continueOnError) throws SQLException {
310 try {
311 org.h2.Driver.load();
312 Connection conn = DriverManager.getConnection(url, user, password);
313 if (charsetName == null) {
314 charsetName = Constants.UTF8;
315 }
316 try {
317 process(conn, fileName, continueOnError, charsetName);
318 } finally {
319 conn.close();
320 }
321 } catch (IOException e) {
322 throw DbException.convertIOException(e, fileName);
323 }
324 }
325
326 }
327