1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.tools; |
7 | |
8 | import java.io.BufferedInputStream; |
9 | import java.io.IOException; |
10 | import java.io.InputStream; |
11 | import java.io.InputStreamReader; |
12 | import java.io.Reader; |
13 | import java.nio.charset.Charset; |
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 | |
20 | import org.h2.engine.Constants; |
21 | import org.h2.engine.SysProperties; |
22 | import org.h2.message.DbException; |
23 | import org.h2.store.fs.FileUtils; |
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 "<url>"]</td> |
45 | * <td>The database URL (jdbc:...)</td></tr> |
46 | * <tr><td>[-user <user>]</td> |
47 | * <td>The user name (default: sa)</td></tr> |
48 | * <tr><td>[-password <pwd>]</td> |
49 | * <td>The password</td></tr> |
50 | * <tr><td>[-script <file>]</td> |
51 | * <td>The script file to run (default: backup.sql)</td></tr> |
52 | * <tr><td>[-driver <class>]</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 | * @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 | @Override |
90 | public void runTool(String... args) throws SQLException { |
91 | String url = null; |
92 | String user = ""; |
93 | String password = ""; |
94 | String script = "backup.sql"; |
95 | String options = null; |
96 | boolean continueOnError = false; |
97 | boolean showTime = false; |
98 | for (int i = 0; args != null && i < args.length; i++) { |
99 | String arg = args[i]; |
100 | if (arg.equals("-url")) { |
101 | url = args[++i]; |
102 | } else if (arg.equals("-user")) { |
103 | user = args[++i]; |
104 | } else if (arg.equals("-password")) { |
105 | password = args[++i]; |
106 | } else if (arg.equals("-continueOnError")) { |
107 | continueOnError = true; |
108 | } else if (arg.equals("-checkResults")) { |
109 | checkResults = true; |
110 | } else if (arg.equals("-showResults")) { |
111 | showResults = true; |
112 | } else if (arg.equals("-script")) { |
113 | script = args[++i]; |
114 | } else if (arg.equals("-time")) { |
115 | showTime = true; |
116 | } else if (arg.equals("-driver")) { |
117 | String driver = args[++i]; |
118 | JdbcUtils.loadUserClass(driver); |
119 | } else if (arg.equals("-options")) { |
120 | StringBuilder buff = new StringBuilder(); |
121 | i++; |
122 | for (; i < args.length; i++) { |
123 | buff.append(' ').append(args[i]); |
124 | } |
125 | options = buff.toString(); |
126 | } else if (arg.equals("-help") || arg.equals("-?")) { |
127 | showUsage(); |
128 | return; |
129 | } else { |
130 | showUsageAndThrowUnsupportedOption(arg); |
131 | } |
132 | } |
133 | if (url == null) { |
134 | showUsage(); |
135 | throw new SQLException("URL not set"); |
136 | } |
137 | long time = System.currentTimeMillis(); |
138 | if (options != null) { |
139 | processRunscript(url, user, password, script, options); |
140 | } else { |
141 | process(url, user, password, script, null, continueOnError); |
142 | } |
143 | if (showTime) { |
144 | time = System.currentTimeMillis() - time; |
145 | out.println("Done in " + time + " ms"); |
146 | } |
147 | } |
148 | |
149 | /** |
150 | * Executes the SQL commands read from the reader against a database. |
151 | * |
152 | * @param conn the connection to a database |
153 | * @param reader the reader |
154 | * @return the last result set |
155 | */ |
156 | public static ResultSet execute(Connection conn, Reader reader) |
157 | throws SQLException { |
158 | // can not close the statement because we return a result set from it |
159 | Statement stat = conn.createStatement(); |
160 | ResultSet rs = null; |
161 | ScriptReader r = new ScriptReader(reader); |
162 | while (true) { |
163 | String sql = r.readStatement(); |
164 | if (sql == null) { |
165 | break; |
166 | } |
167 | if (sql.trim().length() == 0) { |
168 | continue; |
169 | } |
170 | boolean resultSet = stat.execute(sql); |
171 | if (resultSet) { |
172 | if (rs != null) { |
173 | rs.close(); |
174 | rs = null; |
175 | } |
176 | rs = stat.getResultSet(); |
177 | } |
178 | } |
179 | return rs; |
180 | } |
181 | |
182 | private void process(Connection conn, String fileName, |
183 | boolean continueOnError, Charset charset) throws SQLException, |
184 | IOException { |
185 | InputStream in = FileUtils.newInputStream(fileName); |
186 | String path = FileUtils.getParent(fileName); |
187 | try { |
188 | in = new BufferedInputStream(in, Constants.IO_BUFFER_SIZE); |
189 | Reader reader = new InputStreamReader(in, charset); |
190 | process(conn, continueOnError, path, reader, charset); |
191 | } finally { |
192 | IOUtils.closeSilently(in); |
193 | } |
194 | } |
195 | |
196 | private void process(Connection conn, boolean continueOnError, String path, |
197 | Reader reader, Charset charset) throws SQLException, IOException { |
198 | Statement stat = conn.createStatement(); |
199 | ScriptReader r = new ScriptReader(reader); |
200 | while (true) { |
201 | String sql = r.readStatement(); |
202 | if (sql == null) { |
203 | break; |
204 | } |
205 | String trim = sql.trim(); |
206 | if (trim.length() == 0) { |
207 | continue; |
208 | } |
209 | if (trim.startsWith("@") && StringUtils.toUpperEnglish(trim). |
210 | startsWith("@INCLUDE")) { |
211 | sql = trim; |
212 | sql = sql.substring("@INCLUDE".length()).trim(); |
213 | if (!FileUtils.isAbsolute(sql)) { |
214 | sql = path + SysProperties.FILE_SEPARATOR + sql; |
215 | } |
216 | process(conn, sql, continueOnError, charset); |
217 | } else { |
218 | try { |
219 | if (showResults && !trim.startsWith("-->")) { |
220 | out.print(sql + ";"); |
221 | } |
222 | if (showResults || checkResults) { |
223 | boolean query = stat.execute(sql); |
224 | if (query) { |
225 | ResultSet rs = stat.getResultSet(); |
226 | int columns = rs.getMetaData().getColumnCount(); |
227 | StringBuilder buff = new StringBuilder(); |
228 | while (rs.next()) { |
229 | buff.append("\n-->"); |
230 | for (int i = 0; i < columns; i++) { |
231 | String s = rs.getString(i + 1); |
232 | if (s != null) { |
233 | s = StringUtils.replaceAll(s, "\r\n", "\n"); |
234 | s = StringUtils.replaceAll(s, "\n", "\n--> "); |
235 | s = StringUtils.replaceAll(s, "\r", "\r--> "); |
236 | } |
237 | buff.append(' ').append(s); |
238 | } |
239 | } |
240 | buff.append("\n;"); |
241 | String result = buff.toString(); |
242 | if (showResults) { |
243 | out.print(result); |
244 | } |
245 | if (checkResults) { |
246 | String expected = r.readStatement() + ";"; |
247 | expected = StringUtils.replaceAll(expected, "\r\n", "\n"); |
248 | expected = StringUtils.replaceAll(expected, "\r", "\n"); |
249 | if (!expected.equals(result)) { |
250 | expected = StringUtils.replaceAll(expected, " ", "+"); |
251 | result = StringUtils.replaceAll(result, " ", "+"); |
252 | throw new SQLException( |
253 | "Unexpected output for:\n" + sql.trim() + |
254 | "\nGot:\n" + result + "\nExpected:\n" + expected); |
255 | } |
256 | } |
257 | |
258 | } |
259 | } else { |
260 | stat.execute(sql); |
261 | } |
262 | } catch (Exception e) { |
263 | if (continueOnError) { |
264 | e.printStackTrace(out); |
265 | } else { |
266 | throw DbException.toSQLException(e); |
267 | } |
268 | } |
269 | } |
270 | } |
271 | } |
272 | |
273 | private static void processRunscript(String url, String user, String password, |
274 | String fileName, String options) throws SQLException { |
275 | Connection conn = null; |
276 | Statement stat = null; |
277 | try { |
278 | org.h2.Driver.load(); |
279 | conn = DriverManager.getConnection(url, user, password); |
280 | stat = conn.createStatement(); |
281 | String sql = "RUNSCRIPT FROM '" + fileName + "' " + options; |
282 | stat.execute(sql); |
283 | } finally { |
284 | JdbcUtils.closeSilently(stat); |
285 | JdbcUtils.closeSilently(conn); |
286 | } |
287 | } |
288 | |
289 | /** |
290 | * Executes the SQL commands in a script file against a database. |
291 | * |
292 | * @param url the database URL |
293 | * @param user the user name |
294 | * @param password the password |
295 | * @param fileName the script file |
296 | * @param charset the character set or null for UTF-8 |
297 | * @param continueOnError if execution should be continued if an error |
298 | * occurs |
299 | */ |
300 | public static void execute(String url, String user, String password, |
301 | String fileName, Charset charset, boolean continueOnError) |
302 | throws SQLException { |
303 | new RunScript().process(url, user, password, fileName, charset, |
304 | continueOnError); |
305 | } |
306 | |
307 | /** |
308 | * Executes the SQL commands in a script file against a database. |
309 | * |
310 | * @param url the database URL |
311 | * @param user the user name |
312 | * @param password the password |
313 | * @param fileName the script file |
314 | * @param charset the character set or null for UTF-8 |
315 | * @param continueOnError if execution should be continued if an error |
316 | * occurs |
317 | */ |
318 | void process(String url, String user, String password, |
319 | String fileName, Charset charset, |
320 | boolean continueOnError) throws SQLException { |
321 | try { |
322 | org.h2.Driver.load(); |
323 | Connection conn = DriverManager.getConnection(url, user, password); |
324 | if (charset == null) { |
325 | charset = Constants.UTF8; |
326 | } |
327 | try { |
328 | process(conn, fileName, continueOnError, charset); |
329 | } finally { |
330 | conn.close(); |
331 | } |
332 | } catch (IOException e) { |
333 | throw DbException.convertIOException(e, fileName); |
334 | } |
335 | } |
336 | |
337 | } |