001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.unit;
007:
008: import java.io.ByteArrayOutputStream;
009: import java.io.File;
010: import java.io.PrintStream;
011: import java.math.BigDecimal;
012: import java.sql.Connection;
013: import java.sql.DriverManager;
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import java.sql.SQLException;
017: import java.sql.Statement;
018: import java.sql.Types;
019: import java.util.ArrayList;
020: import java.util.Random;
021:
022: import org.h2.engine.Constants;
023: import org.h2.store.FileLister;
024: import org.h2.test.TestBase;
025: import org.h2.test.trace.Player;
026: import org.h2.tools.Backup;
027: import org.h2.tools.ChangePassword;
028: import org.h2.tools.ConvertTraceFile;
029: import org.h2.tools.DeleteDbFiles;
030: import org.h2.tools.Recover;
031: import org.h2.tools.Restore;
032: import org.h2.tools.RunScript;
033: import org.h2.tools.Script;
034: import org.h2.tools.Server;
035: import org.h2.util.FileUtils;
036: import org.h2.util.Resources;
037:
038: /**
039: * Tests the database tools.
040: */
041: public class TestTools extends TestBase {
042:
043: private Server server;
044:
045: public void test() throws Exception {
046: if (config.networked) {
047: return;
048: }
049: deleteDb("utils");
050: testScriptRunscriptLob();
051: deleteDb("utils");
052: testServerMain();
053: testRemove();
054: testConvertTraceFile();
055: testManagementDb();
056: testResourceGenerator();
057: testChangePassword();
058: testServer();
059: testScriptRunscript();
060: testBackupRestore();
061: testRecover();
062: }
063:
064: private void testServerMain() throws Exception {
065: String result;
066: Connection conn;
067: org.h2.Driver.load();
068:
069: result = runServer(new String[] { "-?" }, 1);
070: check(result.indexOf("[options]") >= 0);
071: check(result.indexOf("Unknown option") < 0);
072:
073: result = runServer(new String[] { "-xy" }, 1);
074: check(result.indexOf("[options]") >= 0);
075: check(result.indexOf("Unknown option") >= 0);
076: result = runServer(new String[] { "-tcp", "-tcpAllowOthers",
077: "false", "-tcpPort", "9001", "-tcpPassword", "abc" }, 0);
078: check(result.indexOf("tcp://") >= 0);
079: check(result.indexOf(":9001") >= 0);
080: check(result.indexOf("only local") >= 0);
081: check(result.indexOf("[options]") < 0);
082: conn = DriverManager.getConnection(
083: "jdbc:h2:tcp://localhost:9001/mem:", "sa", "sa");
084: conn.close();
085: result = runServer(new String[] { "-tcpShutdown",
086: "tcp://localhost:9001", "-tcpPassword", "abc",
087: "-tcpShutdownForce", "true" }, 0);
088: check(result.indexOf("Shutting down") >= 0);
089:
090: result = runServer(new String[] { "-tcp", "-tcpAllowOthers",
091: "true", "-tcpPort", "9001", "-tcpPassword", "abcdef",
092: "-tcpSSL", "true" }, 0);
093: check(result.indexOf("ssl://") >= 0);
094: check(result.indexOf(":9001") >= 0);
095: check(result.indexOf("others can") >= 0);
096: check(result.indexOf("[options]") < 0);
097: conn = DriverManager.getConnection(
098: "jdbc:h2:ssl://localhost:9001/mem:", "sa", "sa");
099: conn.close();
100:
101: result = runServer(new String[] { "-tcpShutdown",
102: "ssl://localhost:9001", "-tcpPassword", "abcdef",
103: "-tcpShutdownForce", "false" }, 0);
104: check(result.indexOf("Shutting down") >= 0);
105: try {
106: conn = DriverManager.getConnection(
107: "jdbc:h2:ssl://localhost:9001/mem:", "sa", "sa");
108: error();
109: } catch (SQLException e) {
110: checkNotGeneralException(e);
111: }
112:
113: result = runServer(new String[] { "-web", "-webPort", "9002",
114: "-webAllowOthers", "true", "-webSSL", "true", "-pg",
115: "-pgAllowOthers", "true", "-pgPort", "9003", "-ftp",
116: "-ftpPort", "9004", "-ftpDir", ".", "-ftpRead",
117: "guest", "-ftpWrite", "sa", "-ftpWritePassword", "sa",
118: "-ftpTask", "true", "-tcp", "-tcpAllowOthers", "true",
119: "-tcpPort", "9005", "-tcpPassword", "abc" }, 0);
120: Server stop = server;
121: check(result.indexOf("https://") >= 0);
122: check(result.indexOf(":9002") >= 0);
123: check(result.indexOf("pg://") >= 0);
124: check(result.indexOf(":9003") >= 0);
125: check(result.indexOf("others can") >= 0);
126: check(result.indexOf("only local") < 0);
127: check(result.indexOf("ftp://") >= 0);
128: check(result.indexOf(":9004") >= 0);
129: check(result.indexOf("tcp://") >= 0);
130: check(result.indexOf(":9005") >= 0);
131:
132: result = runServer(new String[] { "-tcpShutdown",
133: "tcp://localhost:9005", "-tcpPassword", "abc",
134: "-tcpShutdownForce", "true" }, 0);
135: check(result.indexOf("Shutting down") >= 0);
136: stop.shutdown();
137: try {
138: conn = DriverManager.getConnection(
139: "jdbc:h2:tcp://localhost:9005/mem:", "sa", "sa");
140: error();
141: } catch (SQLException e) {
142: checkNotGeneralException(e);
143: }
144: }
145:
146: private String runServer(String[] args, int exitCode)
147: throws Exception {
148: ByteArrayOutputStream buff = new ByteArrayOutputStream();
149: PrintStream ps = new PrintStream(buff);
150: server = new Server();
151: int gotCode = server.run(args, ps);
152: check(exitCode, gotCode);
153: ps.flush();
154: String s = new String(buff.toByteArray());
155: return s;
156: }
157:
158: private void testConvertTraceFile() throws Exception {
159: deleteDb("toolsConvertTraceFile");
160: Class.forName("org.h2.Driver");
161: String url = "jdbc:h2:" + baseDir + "/toolsConvertTraceFile";
162: Connection conn = DriverManager.getConnection(url
163: + ";TRACE_LEVEL_FILE=3", "sa", "sa");
164: Statement stat = conn.createStatement();
165: stat
166: .execute("create table test(id int primary key, name varchar, amount decimal)");
167: PreparedStatement prep = conn
168: .prepareStatement("insert into test values(?, ?, ?)");
169: prep.setInt(1, 1);
170: prep.setString(2, "Hello");
171: prep.setBigDecimal(3, new BigDecimal("10.20"));
172: prep.executeUpdate();
173: stat
174: .execute("create table test2(id int primary key, a real, b double, c bigint, "
175: + "d smallint, e boolean, f binary, g date, h time, i timestamp)");
176: prep = conn
177: .prepareStatement("insert into test2 values(1, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
178: prep.setFloat(1, Float.MIN_VALUE);
179: prep.setDouble(2, Double.MIN_VALUE);
180: prep.setLong(3, Long.MIN_VALUE);
181: prep.setShort(4, Short.MIN_VALUE);
182: prep.setBoolean(5, false);
183: prep.setBytes(6, new byte[] { (byte) 10, (byte) 20 });
184: prep.setDate(7, java.sql.Date.valueOf("2007-12-31"));
185: prep.setTime(8, java.sql.Time.valueOf("23:59:59"));
186: prep.setTimestamp(9, java.sql.Timestamp
187: .valueOf("2007-12-31 23:59:59"));
188: prep.execute();
189: conn.close();
190:
191: ConvertTraceFile.main(new String[] { "-traceFile",
192: baseDir + "/toolsConvertTraceFile.trace.db",
193: "-javaClass", baseDir + "/Test", "-script",
194: baseDir + "/test.sql" });
195: new File(baseDir + "/Test.java").delete();
196:
197: File trace = new File(baseDir
198: + "/toolsConvertTraceFile.trace.db");
199: check(trace.exists());
200: File newTrace = new File(baseDir + "/test.trace.db");
201: newTrace.delete();
202: check(trace.renameTo(newTrace));
203: deleteDb("toolsConvertTraceFile");
204: Player.main(new String[] { baseDir + "/test.trace.db" });
205: testTraceFile(url);
206:
207: deleteDb("toolsConvertTraceFile");
208: RunScript.main(new String[] { "-url", url, "-user", "sa",
209: "-script", baseDir + "/test.sql" });
210: testTraceFile(url);
211: }
212:
213: private void testTraceFile(String url) throws Exception {
214: Connection conn;
215: Recover.main(new String[] { "-removePassword", "-log", "false",
216: "-dir", baseDir, "-db", "toolsConvertTraceFile" });
217: conn = DriverManager.getConnection(url, "sa", "");
218: Statement stat = conn.createStatement();
219: ResultSet rs;
220: rs = stat.executeQuery("select * from test");
221: rs.next();
222: check(1, rs.getInt(1));
223: check("Hello", rs.getString(2));
224: check("10.20", rs.getBigDecimal(3).toString());
225: checkFalse(rs.next());
226: rs = stat.executeQuery("select * from test2");
227: rs.next();
228: check(Float.MIN_VALUE, rs.getFloat("a"));
229: check(Double.MIN_VALUE, rs.getDouble("b"));
230: check(Long.MIN_VALUE, rs.getLong("c"));
231: check(Short.MIN_VALUE, rs.getShort("d"));
232: check(!rs.getBoolean("e"));
233: check(new byte[] { (byte) 10, (byte) 20 }, rs.getBytes("f"));
234: check("2007-12-31", rs.getString("g"));
235: check("23:59:59", rs.getString("h"));
236: check("2007-12-31 23:59:59.0", rs.getString("i"));
237: checkFalse(rs.next());
238: conn.close();
239: }
240:
241: private void testRemove() throws Exception {
242: deleteDb("toolsRemove");
243: Class.forName("org.h2.Driver");
244: String url = "jdbc:h2:" + baseDir + "/toolsRemove";
245: Connection conn = DriverManager.getConnection(url, "sa", "sa");
246: Statement stat = conn.createStatement();
247: stat
248: .execute("create table test(id int primary key, name varchar)");
249: stat.execute("insert into test values(1, 'Hello')");
250: conn.close();
251: ArrayList list = FileLister.getDatabaseFiles(baseDir,
252: "toolsRemove", true);
253: for (int i = 0; i < list.size(); i++) {
254: String fileName = (String) list.get(i);
255: if (fileName.endsWith(Constants.SUFFIX_LOG_FILE)) {
256: FileUtils.delete(fileName);
257: }
258: }
259: Recover.main(new String[] { "-dir", baseDir, "-db",
260: "toolsRemove", "-removePassword", "-log", "false" });
261: conn = DriverManager.getConnection(url, "sa", "");
262: stat = conn.createStatement();
263: ResultSet rs;
264: rs = stat.executeQuery("select * from test");
265: rs.next();
266: check(1, rs.getInt(1));
267: check("Hello", rs.getString(2));
268: conn.close();
269: }
270:
271: private void testRecover() throws Exception {
272: deleteDb("toolsRecover");
273: Class.forName("org.h2.Driver");
274: String url = "jdbc:h2:" + baseDir + "/toolsRecover";
275: Connection conn = DriverManager.getConnection(url, "sa", "sa");
276: Statement stat = conn.createStatement();
277: stat
278: .execute("create table test(id int primary key, name varchar, b blob, c clob)");
279: stat
280: .execute("insert into test values(1, 'Hello', SECURE_RAND(2000), space(2000))");
281: ResultSet rs;
282: rs = stat.executeQuery("select * from test");
283: rs.next();
284: byte[] b1 = rs.getBytes(3);
285: String s1 = rs.getString(4);
286:
287: conn.close();
288: Recover.main(new String[] { "-dir", baseDir, "-db",
289: "toolsRecover" });
290: deleteDb("toolsRecover");
291: conn = DriverManager.getConnection(url, "another", "another");
292: stat = conn.createStatement();
293: stat.execute("runscript from '" + baseDir
294: + "/toolsRecover.data.sql'");
295: rs = stat.executeQuery("select * from test");
296: rs.next();
297: check(1, rs.getInt(1));
298: check("Hello", rs.getString(2));
299: byte[] b2 = rs.getBytes(3);
300: String s2 = rs.getString(4);
301: check(2000, b2.length);
302: check(2000, s2.length());
303: check(b1, b2);
304: check(s1, s2);
305: checkFalse(rs.next());
306: conn.close();
307: }
308:
309: private void testManagementDb() throws Exception {
310: int count = getSize(2, 10);
311: for (int i = 0; i < count; i++) {
312: Server server = Server.createTcpServer(
313: new String[] { "-tcpPort", "9192" }).start();
314: server.stop();
315: server = Server.createTcpServer(
316: new String[] { "-tcpPassword", "abc", "-tcpPort",
317: "9192" }).start();
318: server.stop();
319: }
320: }
321:
322: private void testScriptRunscriptLob() throws Exception {
323: Class.forName("org.h2.Driver");
324: String url = "jdbc:h2:" + baseDir + "/utils";
325: String user = "sa", password = "abc";
326: String fileName = baseDir + "/b2.sql";
327: Connection conn = DriverManager.getConnection(url, user,
328: password);
329: conn
330: .createStatement()
331: .execute(
332: "CREATE TABLE TEST(ID INT PRIMARY KEY, BDATA BLOB, CDATA CLOB)");
333: PreparedStatement prep = conn
334: .prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
335:
336: prep.setInt(1, 1);
337: prep.setNull(2, Types.BLOB);
338: prep.setNull(3, Types.CLOB);
339: prep.execute();
340:
341: prep.setInt(1, 2);
342: prep.setString(2, "face");
343: prep.setString(3, "face");
344: prep.execute();
345:
346: Random random = new Random(1);
347: prep.setInt(1, 3);
348: byte[] large = new byte[getSize(10 * 1024, 100 * 1024)];
349: random.nextBytes(large);
350: prep.setBytes(2, large);
351: String largeText = new String(large, "ISO-8859-1");
352: prep.setString(3, largeText);
353: prep.execute();
354:
355: for (int i = 0; i < 2; i++) {
356: ResultSet rs = conn.createStatement().executeQuery(
357: "SELECT * FROM TEST ORDER BY ID");
358: rs.next();
359: check(1, rs.getInt(1));
360: check(rs.getString(2) == null);
361: check(rs.getString(3) == null);
362: rs.next();
363: check(2, rs.getInt(1));
364: check("face", rs.getString(2));
365: check("face", rs.getString(3));
366: rs.next();
367: check(3, rs.getInt(1));
368: check(large, rs.getBytes(2));
369: check(largeText, rs.getString(3));
370: checkFalse(rs.next());
371:
372: conn.close();
373: Script.main(new String[] { "-url", url, "-user", user,
374: "-password", password, "-script", fileName });
375: DeleteDbFiles.main(new String[] { "-dir", baseDir, "-db",
376: "utils", "-quiet" });
377: RunScript.main(new String[] { "-url", url, "-user", user,
378: "-password", password, "-script", fileName });
379: conn = DriverManager.getConnection("jdbc:h2:" + baseDir
380: + "/utils", "sa", "abc");
381: }
382: conn.close();
383:
384: }
385:
386: private void testScriptRunscript() throws Exception {
387: Class.forName("org.h2.Driver");
388: String url = "jdbc:h2:" + baseDir + "/utils";
389: String user = "sa", password = "abc";
390: String fileName = baseDir + "/b2.sql";
391: Connection conn = DriverManager.getConnection(url, user,
392: password);
393: conn.createStatement().execute(
394: "CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
395: conn.createStatement().execute(
396: "INSERT INTO TEST VALUES(1, 'Hello')");
397: conn.close();
398: Script.main(new String[] { "-url", url, "-user", user,
399: "-password", password, "-script", fileName, "-options",
400: "nodata", "compression", "lzf", "cipher", "xtea",
401: "password", "'123'" });
402: DeleteDbFiles.main(new String[] { "-dir", baseDir, "-db",
403: "utils", "-quiet" });
404: RunScript.main(new String[] { "-url", url, "-user", user,
405: "-password", password, "-script", fileName, "-options",
406: "compression", "lzf", "cipher", "xtea", "password",
407: "'123'" });
408: conn = DriverManager.getConnection("jdbc:h2:" + baseDir
409: + "/utils", "sa", "abc");
410: ResultSet rs = conn.createStatement().executeQuery(
411: "SELECT * FROM TEST");
412: checkFalse(rs.next());
413: conn.close();
414: }
415:
416: private void testBackupRestore() throws Exception {
417: Class.forName("org.h2.Driver");
418: String url = "jdbc:h2:" + baseDir + "/utils";
419: String user = "sa", password = "abc";
420: String fileName = baseDir + "/b2.zip";
421: DeleteDbFiles.main(new String[] { "-dir", baseDir, "-db",
422: "utils", "-quiet" });
423: Connection conn = DriverManager.getConnection(url, user,
424: password);
425: conn.createStatement().execute(
426: "CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
427: conn.createStatement().execute(
428: "INSERT INTO TEST VALUES(1, 'Hello')");
429: conn.close();
430: Backup.main(new String[] { "-file", fileName, "-dir", baseDir,
431: "-db", "utils", "-quiet" });
432: DeleteDbFiles.main(new String[] { "-dir", baseDir, "-db",
433: "utils", "-quiet" });
434: Restore.main(new String[] { "-file", fileName, "-dir", baseDir,
435: "-db", "utils", "-quiet" });
436: conn = DriverManager.getConnection("jdbc:h2:" + baseDir
437: + "/utils", "sa", "abc");
438: ResultSet rs = conn.createStatement().executeQuery(
439: "SELECT * FROM TEST");
440: check(rs.next());
441: checkFalse(rs.next());
442: conn.close();
443: DeleteDbFiles.main(new String[] { "-dir", baseDir, "-db",
444: "utils", "-quiet" });
445: }
446:
447: private void testResourceGenerator() throws Exception {
448: Resources.main(new String[] { "." });
449: }
450:
451: private void testChangePassword() throws Exception {
452: Class.forName("org.h2.Driver");
453: Connection conn = DriverManager.getConnection("jdbc:h2:"
454: + baseDir + "/utils;CIPHER=XTEA;STORAGE=TEXT", "sa",
455: "abc 123");
456: Statement stat = conn.createStatement();
457: stat
458: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
459: conn.close();
460: String[] args = new String[] { "-dir", baseDir, "-db", "utils",
461: "-cipher", "XTEA", "-decrypt", "abc", "-quiet" };
462: ChangePassword.main(args);
463: args = new String[] { "-dir", baseDir, "-db", "utils",
464: "-cipher", "AES", "-encrypt", "def", "-quiet" };
465: ChangePassword.main(args);
466: conn = DriverManager.getConnection("jdbc:h2:" + baseDir
467: + "/utils;CIPHER=AES", "sa", "def 123");
468: stat = conn.createStatement();
469: stat.execute("SELECT * FROM TEST");
470: conn.close();
471: args = new String[] { "-dir", baseDir, "-db", "utils", "-quiet" };
472: DeleteDbFiles.main(args);
473: }
474:
475: private void testServer() throws Exception {
476: Connection conn;
477: deleteDb("test");
478: Server server = Server.createTcpServer(
479: new String[] { "-ifExists", "false", "-baseDir",
480: baseDir, "-tcpPort", "9192" }).start();
481: conn = DriverManager.getConnection(
482: "jdbc:h2:tcp://localhost:9192/test", "sa", "");
483: conn.close();
484: server.stop();
485: server = Server
486: .createTcpServer(
487: new String[] { "-ifExists", "true",
488: "-tcpPassword", "abc", "-baseDir",
489: baseDir, "-tcpPort", "9192" }).start();
490: try {
491: conn = DriverManager.getConnection(
492: "jdbc:h2:tcp://localhost:9192/test2", "sa", "");
493: error("should not be able to create new db");
494: } catch (SQLException e) {
495: checkNotGeneralException(e);
496: }
497: conn = DriverManager.getConnection(
498: "jdbc:h2:tcp://localhost:9192/test", "sa", "");
499: conn.close();
500: try {
501: Server.shutdownTcpServer("tcp://localhost:9192", "", true);
502: error("shouldn't work and should throw an exception");
503: } catch (SQLException e) {
504: checkNotGeneralException(e);
505: }
506: conn = DriverManager.getConnection(
507: "jdbc:h2:tcp://localhost:9192/test", "sa", "");
508: // conn.close();
509: Server.shutdownTcpServer("tcp://localhost:9192", "abc", true);
510: // check that the database is closed
511: deleteDb("test");
512: try {
513: conn = DriverManager.getConnection(
514: "jdbc:h2:tcp://localhost:9192/test", "sa", "");
515: error("server must have been closed");
516: } catch (SQLException e) {
517: checkNotGeneralException(e);
518: }
519: }
520:
521: }
|