001: /*
002: * BatchRunnerTest.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.sql;
013:
014: import java.io.BufferedReader;
015: import java.io.IOException;
016: import java.io.PrintStream;
017: import java.io.PrintWriter;
018: import java.io.File;
019: import java.io.FileReader;
020: import java.io.FileWriter;
021: import java.sql.ResultSet;
022: import java.sql.Statement;
023: import junit.framework.TestCase;
024: import workbench.AppArguments;
025: import workbench.TestUtil;
026: import workbench.db.ConnectionMgr;
027: import workbench.db.ConnectionMgr;
028: import workbench.db.WbConnection;
029: import workbench.gui.profiles.ProfileKey;
030: import workbench.util.ArgumentParser;
031: import workbench.util.FileUtil;
032: import workbench.util.SqlUtil;
033: import workbench.util.WbFile;
034:
035: /**
036: * @author support@sql-workbench.net
037: */
038: public class BatchRunnerTest extends TestCase {
039: private TestUtil util;
040:
041: public BatchRunnerTest(String testName) {
042: super (testName);
043: try {
044: util = new TestUtil(testName);
045: util.prepareEnvironment();
046: } catch (IOException ex) {
047: fail(ex.getMessage());
048: }
049: }
050:
051: public void testTransactionControlError() {
052: WbConnection con = null;
053: try {
054: util.emptyBaseDirectory();
055: WbFile errorFile = new WbFile(util.getBaseDir(),
056: "error.sql");
057: TestUtil.writeFile(errorFile, "rollback;\n");
058: WbFile successFile = new WbFile(util.getBaseDir(),
059: "success.sql");
060: TestUtil.writeFile(successFile, "commit;\n");
061:
062: WbFile importFile = new WbFile(util.getBaseDir(),
063: "data.txt");
064: String data = "nr\tfirstname\tlastname\n"
065: + "1\tArthur\tDent\n";
066: TestUtil.writeFile(importFile, data);
067: WbFile scriptFile = new WbFile(util.getBaseDir(),
068: "myscript.sql");
069: WbFile logfile = new WbFile(util.getBaseDir(),
070: "junit_transaction_test.txt");
071:
072: PrintWriter writer = new PrintWriter(new FileWriter(
073: scriptFile));
074: writer.println("-- test script");
075: writer
076: .println("CREATE TABLE person (nr integer primary key, firstname varchar(100), lastname varchar(100));");
077: writer
078: .println("insert into person (nr, firstname, lastname) values (1,'Arthur', 'Dent');");
079: writer.println("commit;");
080: writer
081: .println("insert into person (nr, firstname, lastname) values (2,'Ford', 'Prefect');");
082: writer
083: .println("insert into person (nr, firstname, lastname) values (3,'Zaphod', 'Beeblebrox');");
084: writer.println("-- import data. should fail!");
085: writer
086: .println("WbImport -file='"
087: + importFile.getName()
088: + "' -type=text -header=true -table=person -continueOnError=false -transactionControl=false");
089: writer.close();
090:
091: ArgumentParser parser = new AppArguments();
092: WbFile dbFile = new WbFile(util.getBaseDir(), "errtest");
093: parser.parse("-url='jdbc:h2:'" + dbFile.getFullPath()
094: + " -user=sa -driver=org.h2.Driver "
095: + " -logfile='" + logfile.getFullPath() + "' "
096: + " -script='" + scriptFile.getFullPath() + "' "
097: + " -abortOnError=true -cleanupError='"
098: + errorFile.getFullPath() + "' "
099: + " -autocommit=false " + " -cleanupSuccess='"
100: + successFile.getFullPath() + "' ");
101:
102: BatchRunner runner = BatchRunner.createBatchRunner(parser);
103: assertNotNull(runner);
104:
105: runner.connect();
106: runner.execute();
107:
108: con = util.getConnection(dbFile);
109: Statement stmt = con.createStatement();
110: ResultSet rs = stmt
111: .executeQuery("select count(*) from person");
112: if (rs.next()) {
113: int nr = rs.getInt(1);
114: assertEquals("Not enough rows!", 1, nr);
115: } else {
116: fail("No data");
117: }
118: SqlUtil.closeAll(rs, stmt);
119: } catch (Exception e) {
120: e.printStackTrace();
121: fail(e.getMessage());
122: } finally {
123: con.close();
124: }
125: }
126:
127: public void testTransactionControlSuccess() {
128: WbConnection con = null;
129: try {
130: util.emptyBaseDirectory();
131: WbFile errorFile = new WbFile(util.getBaseDir(),
132: "error.sql");
133: TestUtil.writeFile(errorFile, "rollback;\n");
134: WbFile successFile = new WbFile(util.getBaseDir(),
135: "success.sql");
136: TestUtil.writeFile(successFile, "commit;\n");
137:
138: WbFile importFile = new WbFile(util.getBaseDir(),
139: "data_success.txt");
140: String data = "nr\tfirstname\tlastname\n"
141: + "5\tMary\tMoviestar\n" + "6\tMajor\tBug\n"
142: + "7\tGeneral\tFailure\n";
143: TestUtil.writeFile(importFile, data);
144: WbFile scriptFile = new WbFile(util.getBaseDir(),
145: "myscript.sql");
146: PrintWriter writer = new PrintWriter(new FileWriter(
147: scriptFile));
148: writer.println("-- test script");
149: writer
150: .println("CREATE TABLE person (nr integer primary key, firstname varchar(100), lastname varchar(100));");
151: writer.println("commit;");
152: writer
153: .println("insert into person (nr, firstname, lastname) values (1,'Arthur', 'Dent');");
154: writer
155: .println("insert into person (nr, firstname, lastname) values (2,'Ford', 'Prefect');");
156: writer
157: .println("insert into person (nr, firstname, lastname) values (3,'Zaphod', 'Beeblebrox');");
158: writer.println("-- import data. should fail!");
159: writer
160: .println("WbImport -file='"
161: + importFile.getName()
162: + "' -type=text -header=true -table=person -continueOnError=false -transactionControl=false");
163: writer
164: .println("insert into person (nr, firstname, lastname) values (8,'Tricia', 'McMillian');");
165: writer.close();
166:
167: ArgumentParser parser = new AppArguments();
168: WbFile dbFile = new WbFile(util.getBaseDir(), "successtest");
169: parser.parse("-url='jdbc:h2:'" + dbFile.getFullPath()
170: + " -user=sa -driver=org.h2.Driver " + " -script='"
171: + scriptFile.getFullPath() + "' "
172: + " -abortOnError=true -cleanupError='"
173: + errorFile.getFullPath() + "' "
174: + " -cleanupSuccess='" + successFile.getFullPath()
175: + "' " + " -autocommit=false "
176: + " -rollbackOnDisconnect=true ");
177:
178: BatchRunner runner = BatchRunner.createBatchRunner(parser);
179: assertNotNull(runner);
180:
181: runner.connect();
182: assertTrue(runner.isConnected());
183: runner.execute();
184: assertTrue(runner.isSuccess());
185:
186: con = util.getConnection(dbFile);
187: Statement stmt = con.createStatement();
188: ResultSet rs = stmt
189: .executeQuery("select count(*) from person");
190: if (rs.next()) {
191: int nr = rs.getInt(1);
192: assertEquals("Not enough rows!", 6, nr);
193: } else {
194: fail("No data");
195: }
196: SqlUtil.closeAll(rs, stmt);
197: } catch (Exception e) {
198: e.printStackTrace();
199: fail(e.getMessage());
200: } finally {
201: con.close();
202: }
203: }
204:
205: public void testEmptyStatement() {
206: try {
207: String sql = "-- comment only";
208: util.emptyBaseDirectory();
209:
210: File scriptFile = new File(util.getBaseDir(),
211: "testbatch.sql");
212: FileWriter writer = new FileWriter(scriptFile);
213: writer.write(sql);
214: writer.close();
215:
216: ArgumentParser parser = new AppArguments();
217: String script = "-script='" + scriptFile.getAbsolutePath()
218: + "'";
219: parser
220: .parse("-url='jdbc:h2:mem:testEmptyStmt' -user=sa -driver=org.h2.Driver "
221: + script
222: + " -displayresult=true -ignoredroperrors=true -showprogress=true -showtiming=false");
223: BatchRunner runner = BatchRunner.createBatchRunner(parser);
224:
225: assertNotNull(runner);
226:
227: runner.connect();
228: WbConnection con = runner.getConnection();
229: assertNotNull(con);
230: assertNotNull(con.getProfile());
231:
232: runner.execute();
233: assertEquals(true, runner.isSuccess());
234: } catch (Exception e) {
235: e.printStackTrace();
236: fail(e.getMessage());
237: }
238: }
239:
240: public void testBatchRunner() {
241: try {
242: util.emptyBaseDirectory();
243: util.prepareEnvironment(true);
244:
245: WbFile scriptFile = new WbFile(util.getBaseDir(),
246: "preparedata.sql");
247: PrintWriter writer = new PrintWriter(new FileWriter(
248: scriptFile));
249: writer.println("-- test script");
250: writer
251: .println("CREATE TABLE person (nr integer primary key, firstname varchar(100), lastname varchar(100));");
252: writer.println("-- first row");
253: writer
254: .println("insert into person (nr, firstname, lastname) values (1,'Arthur', 'Dent');");
255: writer.println("-- first row");
256: writer
257: .println("insert into person (nr, firstname, lastname) values (2,'Ford', 'Prefect');");
258: writer.println("-- first row");
259: writer
260: .println("insert into person (nr, firstname, lastname) values (3,'Zaphod', 'Beeblebrox');");
261: writer
262: .println("/* make everything permanent\nmore comments */");
263: writer.println("commit;");
264: writer.close();
265:
266: ArgumentParser parser = new AppArguments();
267: String script = "-script='" + scriptFile.getFullPath()
268: + "'";
269: parser.parse("-url='jdbc:h2:mem:testBatchRunner' "
270: + "-user=sa " + "-driver=org.h2.Driver " + script
271: + " -rollbackOnDisconnect=true");
272: BatchRunner runner = BatchRunner.createBatchRunner(parser);
273:
274: assertNotNull(runner);
275:
276: runner.connect();
277: WbConnection con = runner.getConnection();
278: assertNotNull(con);
279: assertNotNull(con.getProfile());
280:
281: boolean rollback = con.getProfile()
282: .getRollbackBeforeDisconnect();
283: assertEquals("Rollback property not read from commandline",
284: true, rollback);
285:
286: runner.execute();
287:
288: Statement stmt = con.createStatement();
289: ResultSet rs = stmt
290: .executeQuery("select count(*) from person");
291:
292: if (rs.next()) {
293: int count = rs.getInt(1);
294: assertEquals("Not enough records inserted", 3, count);
295: }
296: SqlUtil.closeAll(rs, stmt);
297: } catch (Exception e) {
298: e.printStackTrace();
299: fail(e.getMessage());
300: } finally {
301: ConnectionMgr.getInstance().disconnectAll();
302: }
303: }
304:
305: public void testNoConnection() {
306: Statement stmt = null;
307: ResultSet rs = null;
308: try {
309: ConnectionMgr.getInstance().disconnectAll();
310: ConnectionMgr.getInstance().clearProfiles();
311: WbFile targetDb = new WbFile(util.getBaseDir(),
312: "brTargetdb");
313: WbFile sourceDb = new WbFile(util.getBaseDir(),
314: "brSourcedb");
315: util.createProfiles(sourceDb, targetDb);
316: util.prepareSource(sourceDb);
317: util.prepareTarget(targetDb);
318: WbFile script = new WbFile(util.getBaseDir(),
319: "copydata.sql");
320: String command = "WbCopy -sourceProfile='SourceConnection' -targetProfile='TargetConnection' -sourceTable=person -targetTable=person;";
321: TestUtil.writeFile(script, command);
322: ArgumentParser parser = new AppArguments();
323:
324: parser.parse("-script='" + script.getFullPath() + "'");
325: BatchRunner runner = BatchRunner.createBatchRunner(parser);
326: assertNotNull(runner);
327: runner.connect();
328: runner.execute();
329: assertTrue(runner.isSuccess());
330: WbConnection target = ConnectionMgr.getInstance()
331: .getConnection(new ProfileKey("TargetConnection"),
332: "CopyCheck");
333: stmt = target.createStatement();
334: rs = stmt.executeQuery("select count(*) from person");
335: if (rs.next()) {
336: int count = rs.getInt(1);
337: assertEquals(4, count);
338: } else {
339: fail("No rows copied");
340: }
341: } catch (Exception e) {
342: e.printStackTrace();
343: fail(e.getMessage());
344: } finally {
345: ConnectionMgr.getInstance().disconnectAll();
346: }
347: }
348:
349: public void testAltDelimiter() {
350: try {
351: util.emptyBaseDirectory();
352:
353: ArgumentParser parser = new AppArguments();
354: File scriptFile = new File(util.getBaseDir(),
355: "preparedata.sql");
356: PrintWriter writer = new PrintWriter(new FileWriter(
357: scriptFile));
358: writer.println("-- test script");
359: writer
360: .println("CREATE TABLE person (nr integer primary key, firstname varchar(100), lastname varchar(100))");
361: writer.println("/");
362: writer
363: .println("insert into person (nr, firstname, lastname) values (1,'Arthur', 'Dent')");
364: writer.println("/");
365: writer
366: .println("insert into person (nr, firstname, lastname) values (2,'Ford', 'Prefect')");
367: writer.println("/");
368: writer
369: .println("insert into person (nr, firstname, lastname) values (3,'Zaphod', 'Beeblebrox')");
370: writer.println("/");
371: writer.println("commit");
372: writer.println("/");
373: writer.close();
374:
375: File scriptFile2 = new File(util.getBaseDir(), "insert.sql");
376: PrintWriter writer2 = new PrintWriter(new FileWriter(
377: scriptFile2));
378: writer2.println("-- test script");
379: writer2
380: .println("insert into person (nr, firstname, lastname) values (4,'Tricia', 'McMillian');");
381: writer2.println("commit;");
382: writer2.close();
383:
384: parser
385: .parse("-url='jdbc:h2:mem:testAltDelimiter' -altdelimiter='/;nl' -user=sa -driver=org.h2.Driver -script='"
386: + scriptFile.getAbsolutePath()
387: + "','"
388: + scriptFile2.getAbsolutePath() + "'");
389: BatchRunner runner = BatchRunner.createBatchRunner(parser);
390:
391: assertNotNull(runner);
392:
393: runner.connect();
394: WbConnection con = runner.getConnection();
395: assertNotNull(con);
396: assertNotNull(con.getProfile());
397:
398: DelimiterDefinition def = con.getProfile()
399: .getAlternateDelimiter();
400: assertNotNull("No alternate delimiter defined", def);
401: assertEquals("Wrong alternate delimiter parsed", "/", def
402: .getDelimiter());
403: assertEquals("Wrong singleLine Property parsed", true, def
404: .isSingleLine());
405:
406: runner.execute();
407: assertEquals("Runner not successful!", true, runner
408: .isSuccess());
409:
410: Statement stmt = con.createStatement();
411: ResultSet rs = stmt
412: .executeQuery("select count(*) from person");
413:
414: if (rs.next()) {
415: int count = rs.getInt(1);
416: assertEquals("Not enough records inserted", 4, count);
417: }
418: SqlUtil.closeAll(rs, stmt);
419: } catch (Exception e) {
420: e.printStackTrace();
421: fail(e.getMessage());
422: } finally {
423: ConnectionMgr.getInstance().disconnectAll();
424: }
425: }
426:
427: public void testConsoleOutput() {
428: WbConnection con = null;
429: Statement stmt = null;
430: ResultSet rs = null;
431: PrintStream console = null;
432: try {
433: util.prepareEnvironment();
434: con = util.getConnection();
435: stmt = con.createStatement();
436: stmt
437: .executeUpdate("CREATE TABLE person (nr integer, firstname varchar(100), lastname varchar(100))");
438: stmt
439: .executeUpdate("INSERT INTO person (nr, firstname, lastname) values (1, 'Arthur', 'Dent')");
440: stmt
441: .executeUpdate("INSERT INTO person (nr, firstname, lastname) values (2, 'Ford', 'Prefect')");
442: con.commit();
443:
444: File scriptFile = new File(util.getBaseDir(),
445: "runselect.sql");
446: PrintWriter writer = new PrintWriter(new FileWriter(
447: scriptFile));
448: writer.println("select * from person;");
449: writer.close();
450:
451: ArgumentParser parser = new AppArguments();
452: parser
453: .parse("-displayresult=true -altdelimiter='/;nl' -script="
454: + scriptFile.getAbsolutePath());
455: BatchRunner runner = BatchRunner.createBatchRunner(parser);
456: runner.setConnection(con);
457:
458: File out = new File(util.getBaseDir(), "console.txt");
459: console = new PrintStream(out);
460: runner.setConsole(console);
461: runner.execute();
462: console.close();
463:
464: BufferedReader in = new BufferedReader(new FileReader(out));
465: String content = FileUtil.readCharacters(in);
466:
467: int pos = content.indexOf("NR\tFIRSTNAME\tLASTNAME");
468: assertEquals("Header not found", (pos > -1), true);
469:
470: pos = content.indexOf("1\tArthur\tDent");
471: assertEquals("Record not found", (pos > -1), true);
472:
473: pos = content.indexOf("2\tFord\tPrefect");
474: assertEquals("Record not found", (pos > -1), true);
475:
476: } catch (Exception e) {
477: e.printStackTrace();
478: fail(e.getMessage());
479: } finally {
480: SqlUtil.closeAll(rs, stmt);
481: ConnectionMgr.getInstance().disconnectAll();
482: }
483: }
484: }
|