001: /*
002: * WbCopyTest.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.wbcommands;
013:
014: import java.sql.ResultSet;
015: import java.sql.Statement;
016: import java.util.List;
017: import junit.framework.TestCase;
018: import workbench.TestUtil;
019: import workbench.db.ColumnIdentifier;
020: import workbench.db.ConnectionMgr;
021: import workbench.db.TableIdentifier;
022: import workbench.db.WbConnection;
023: import workbench.sql.DefaultStatementRunner;
024: import workbench.sql.StatementRunnerResult;
025: import workbench.util.SqlUtil;
026:
027: /**
028: *
029: * @author support@sql-workbench.net
030: */
031: public class WbCopyTest extends TestCase {
032:
033: public WbCopyTest(String testName) {
034: super (testName);
035: }
036:
037: public void testCopy() throws Exception {
038: try {
039: TestUtil util = new TestUtil("WbCopyTest_testExecute");
040: util.prepareEnvironment();
041:
042: DefaultStatementRunner runner = util
043: .createConnectedStatementRunner();
044: WbConnection con = runner.getConnection();
045:
046: Statement stmt = con.createStatement();
047:
048: stmt
049: .executeUpdate("create table source_data (nr integer not null primary key, lastname varchar(50), firstname varchar(50), binary_data blob)");
050:
051: stmt
052: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (1,'Dent', 'Arthur', '01')");
053: stmt
054: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (2,'Beeblebrox', 'Zaphod','0202')");
055: stmt
056: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (3,'Moviestar', 'Mary', '030303')");
057: stmt
058: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (4,'Perfect', 'Ford', '04040404')");
059:
060: con.commit();
061:
062: String sql = "--copy source_data and create target\n"
063: + "wbcopy -sourceTable=source_data "
064: + "-targettable=target_data "
065: + "-createTarget=true";
066:
067: runner.runStatement(sql, -1, -1);
068: StatementRunnerResult result = runner.getResult();
069: assertEquals(result.getMessageBuffer().toString(), true,
070: result.isSuccess());
071:
072: ResultSet rs = stmt
073: .executeQuery("select count(*) from target_data");
074: if (rs.next()) {
075: int count = rs.getInt(1);
076: assertEquals("Incorrect number of rows copied", 4,
077: count);
078: }
079: rs.close();
080: rs = stmt
081: .executeQuery("select lastname from target_data where nr = 3");
082: if (rs.next()) {
083: String name = rs.getString(1);
084: assertEquals("Incorrect value copied", "Moviestar",
085: name);
086: } else {
087: fail("Record with nr = 3 not copied");
088: }
089: rs.close();
090: rs = stmt
091: .executeQuery("select nr, binary_data from target_data");
092: while (rs.next()) {
093: int id = rs.getInt(1);
094: Object blob = rs.getObject(2);
095: assertNotNull("No blob data imported", blob);
096: if (blob instanceof byte[]) {
097: byte[] retrievedData = (byte[]) blob;
098: assertEquals("Wrong blob size imported", id,
099: retrievedData.length);
100: assertEquals("Wrong content of blob data", id,
101: retrievedData[0]);
102: }
103: }
104:
105: stmt
106: .executeUpdate("update source_data set lastname = 'Prefect' where nr = 4");
107: con.commit();
108:
109: sql = "--update target table\nwbcopy -sourceTable=source_data -targettable=target_data -mode=update";
110: runner.runStatement(sql, -1, -1);
111: result = runner.getResult();
112: assertEquals("Copy not successful", true, result
113: .isSuccess());
114:
115: rs = stmt
116: .executeQuery("select lastname from target_data where nr = 4");
117: if (rs.next()) {
118: String name = rs.getString(1);
119: assertEquals("Incorrect value copied", "Prefect", name);
120: } else {
121: fail("Record with nr = 4 not copied");
122: }
123: ConnectionMgr.getInstance().removeProfile(con.getProfile());
124: } catch (Exception e) {
125: e.printStackTrace();
126: fail(e.getMessage());
127: } finally {
128: ConnectionMgr.getInstance().disconnectAll();
129: }
130: }
131:
132: public void testCreateWithMap() throws Exception {
133: try {
134: TestUtil util = new TestUtil("CreateOrderedTest");
135: util.prepareEnvironment();
136:
137: DefaultStatementRunner runner = util
138: .createConnectedStatementRunner();
139: WbConnection con = runner.getConnection();
140:
141: Statement stmt = con.createStatement();
142:
143: stmt
144: .executeUpdate("create table source_data (nr integer not null primary key, lastname varchar(50), firstname varchar(50))");
145:
146: stmt
147: .executeUpdate("insert into source_data (nr, lastname, firstname) values (1,'Dent', 'Arthur')");
148: stmt
149: .executeUpdate("insert into source_data (nr, lastname, firstname) values (2,'Beeblebrox', 'Zaphod')");
150: stmt
151: .executeUpdate("insert into source_data (nr, lastname, firstname) values (3,'Moviestar', 'Mary')");
152: stmt
153: .executeUpdate("insert into source_data (nr, lastname, firstname) values (4,'Perfect', 'Ford')");
154:
155: con.commit();
156:
157: String sql = "wbcopy -sourceTable=source_data "
158: + "-targetTable=target_data "
159: + "-columns=lastname/nachname, firstname/vorname, nr/id "
160: + "-createTarget=true";
161:
162: runner.runStatement(sql, -1, -1);
163: StatementRunnerResult result = runner.getResult();
164: assertEquals(result.getMessageBuffer().toString(), true,
165: result.isSuccess());
166:
167: ResultSet rs = stmt
168: .executeQuery("select count(*) from target_data");
169: if (rs.next()) {
170: int count = rs.getInt(1);
171: assertEquals("Incorrect number of rows copied", 4,
172: count);
173: }
174: rs.close();
175:
176: // Make sure the order in the column mapping is preserved when creating the table
177: List<ColumnIdentifier> columns = con
178: .getMetadata()
179: .getTableColumns(new TableIdentifier("TARGET_DATA"));
180: for (ColumnIdentifier col : columns) {
181: if (col.getColumnName().equalsIgnoreCase("NACHNAME")) {
182: assertEquals(1, col.getPosition());
183: } else if (col.getColumnName().equalsIgnoreCase(
184: "VORNAME")) {
185: assertEquals(2, col.getPosition());
186: } else if (col.getColumnName().equalsIgnoreCase("ID")) {
187: assertEquals(3, col.getPosition());
188: } else {
189: fail("Wrong column " + col.getColumnName()
190: + " created");
191: }
192: }
193: ConnectionMgr.getInstance().removeProfile(con.getProfile());
194: } catch (Exception e) {
195: e.printStackTrace();
196: fail(e.getMessage());
197: } finally {
198: ConnectionMgr.getInstance().disconnectAll();
199: }
200: }
201:
202: public void testWithColumnMap() {
203: try {
204: TestUtil util = new TestUtil("CopyWithMapTest");
205: util.prepareEnvironment();
206:
207: WbConnection con = util.getConnection("mappedCopyTest");
208:
209: WbCopy copyCmd = new WbCopy();
210: copyCmd.setConnection(con);
211:
212: Statement stmt = con.createStatement();
213:
214: stmt
215: .executeUpdate("create table source_data (nr integer not null primary key, lastname varchar(50), firstname varchar(50), binary_data blob)");
216:
217: stmt
218: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (1,'Dent', 'Arthur', '01')");
219: stmt
220: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (2,'Beeblebrox', 'Zaphod','0202')");
221: stmt
222: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (3,'Moviestar', 'Mary', '030303')");
223: stmt
224: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (4,'Perfect', 'Ford', '04040404')");
225:
226: stmt
227: .executeUpdate("create table target_data (tnr integer not null primary key, tlastname varchar(50), tfirstname varchar(50), tbinary_data blob)");
228: stmt
229: .executeUpdate("insert into target_data (tnr, tlastname, tfirstname) values (42,'Gaga', 'Radio')");
230:
231: con.commit();
232:
233: String sql = "wbcopy -sourceTable=source_data "
234: + "-targetTable=target_data "
235: + "-deleteTarget=true "
236: + "-columns=lastname/tlastname, firstname/tfirstname, nr/tnr";
237:
238: StatementRunnerResult result = copyCmd.execute(sql);
239: assertEquals("Copy not successful", true, result
240: .isSuccess());
241:
242: ResultSet rs = stmt
243: .executeQuery("select count(*) from target_data where tbinary_data is null");
244: if (rs.next()) {
245: int count = rs.getInt(1);
246: assertEquals("Incorrect number of rows copied", 4,
247: count);
248: }
249: SqlUtil.closeResult(rs);
250:
251: rs = stmt
252: .executeQuery("select tfirstname, tlastname from target_data where tnr = 3");
253: if (rs.next()) {
254: String s = rs.getString(1);
255: assertEquals("Incorrect firstname", "Mary", s);
256: s = rs.getString(2);
257: assertEquals("Incorrect firstname", "Moviestar", s);
258: } else {
259: fail("Nothing copied");
260: }
261: SqlUtil.closeResult(rs);
262: ConnectionMgr.getInstance().removeProfile(con.getProfile());
263: } catch (Exception e) {
264: e.printStackTrace();
265: fail(e.getMessage());
266: } finally {
267: ConnectionMgr.getInstance().disconnectAll();
268: }
269: }
270:
271: public void testQueryCopy() {
272: try {
273: TestUtil util = new TestUtil("WbCopyTest_testExecute");
274: util.prepareEnvironment();
275:
276: WbConnection con = util.getConnection("queryCopyTest");
277:
278: WbCopy copyCmd = new WbCopy();
279: copyCmd.setConnection(con);
280:
281: Statement stmt = con.createStatement();
282:
283: stmt
284: .executeUpdate("create table source_data (nr integer not null primary key, lastname varchar(50), firstname varchar(50), binary_data blob)");
285:
286: stmt
287: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (1,'Dent', 'Arthur', '01')");
288: stmt
289: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (2,'Beeblebrox', 'Zaphod','0202')");
290: stmt
291: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (3,'Moviestar', 'Mary', '030303')");
292: stmt
293: .executeUpdate("insert into source_data (nr, lastname, firstname, binary_data) values (4,'Perfect', 'Ford', '04040404')");
294:
295: stmt
296: .executeUpdate("create table target_data (tnr integer not null primary key, tlastname varchar(50), tfirstname varchar(50), tbinary_data blob)");
297:
298: con.commit();
299:
300: String sql = "wbcopy -sourceQuery='select firstname, nr, lastname from source_data where nr < 3' "
301: + "-targetTable=target_data "
302: + "-columns=tfirstname, tnr, tlastname";
303:
304: StatementRunnerResult result = copyCmd.execute(sql);
305: assertEquals("Copy not successful", true, result
306: .isSuccess());
307:
308: ResultSet rs = stmt
309: .executeQuery("select count(*) from target_data where tbinary_data is null");
310: if (rs.next()) {
311: int count = rs.getInt(1);
312: assertEquals("Incorrect number of rows copied", 2,
313: count);
314: }
315: SqlUtil.closeResult(rs);
316:
317: rs = stmt
318: .executeQuery("select tfirstname, tlastname from target_data where tnr = 1");
319: if (rs.next()) {
320: String s = rs.getString(1);
321: assertEquals("Incorrect firstname", "Arthur", s);
322: s = rs.getString(2);
323: assertEquals("Incorrect firstname", "Dent", s);
324: } else {
325: fail("Nothing copied");
326: }
327: SqlUtil.closeResult(rs);
328: ConnectionMgr.getInstance().removeProfile(con.getProfile());
329: } catch (Exception e) {
330: e.printStackTrace();
331: fail(e.getMessage());
332: } finally {
333: ConnectionMgr.getInstance().disconnectAll();
334: }
335: }
336:
337: public void testCopySchema() {
338: try {
339: TestUtil util = new TestUtil("WbCopyTest_testExecute");
340: util.prepareEnvironment();
341:
342: WbConnection con = util.getConnection("schemaCopySource");
343: WbConnection target = util
344: .getConnection("schemaCopyTarget");
345:
346: WbCopy copyCmd = new WbCopy();
347: copyCmd.setConnection(con);
348:
349: Statement stmt = con.createStatement();
350:
351: stmt
352: .executeUpdate("create table person (nr integer not null primary key, lastname varchar(50), firstname varchar(50), binary_data blob)");
353: stmt
354: .executeUpdate("create table address (person_id integer, address_details varchar(100))");
355: stmt
356: .executeUpdate("create table some_data (id integer, some_details varchar(100))");
357: stmt
358: .executeUpdate("alter table address add foreign key (person_id) references person(nr)");
359:
360: stmt
361: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (1,'Dent', 'Arthur', '01')");
362: stmt
363: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (2,'Beeblebrox', 'Zaphod','0202')");
364: stmt
365: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (3,'Moviestar', 'Mary', '030303')");
366: stmt
367: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (4,'Perfect', 'Ford', '04040404')");
368:
369: stmt
370: .executeUpdate("insert into address (person_id, address_details) values (1, 'Arlington')");
371: stmt
372: .executeUpdate("insert into address (person_id, address_details) values (2, 'Heart of Gold')");
373: stmt
374: .executeUpdate("insert into address (person_id, address_details) values (3, 'Sleepy by Lane')");
375: stmt
376: .executeUpdate("insert into address (person_id, address_details) values (4, 'Betelgeuse')");
377:
378: con.commit();
379:
380: Statement tstmt = target.createStatement();
381: tstmt
382: .executeUpdate("create table person (nr integer not null primary key, lastname varchar(50), firstname varchar(50), binary_data blob)");
383: tstmt
384: .executeUpdate("create table address (person_id integer, address_details varchar(100))");
385: tstmt
386: .executeUpdate("alter table address add foreign key (person_id) references person(nr)");
387: target.commit();
388:
389: String sql = "wbcopy -sourceTable=some_data,address,person -checkDependencies=true -sourceProfile='schemaCopySource' -targetProfile='schemaCopyTarget'";
390:
391: StatementRunnerResult result = copyCmd.execute(sql);
392: assertEquals(result.getMessageBuffer().toString(), true,
393: result.isSuccess());
394:
395: ResultSet rs = tstmt
396: .executeQuery("select nr, lastname, firstname from person");
397: while (rs.next()) {
398: int nr = rs.getInt(1);
399: String ln = rs.getString(2);
400: String fn = rs.getString(3);
401: if (nr == 1) {
402: assertEquals("Incorrect data copied", "Dent", ln);
403: assertEquals("Incorrect data copied", "Arthur", fn);
404: } else if (nr == 2) {
405: assertEquals("Incorrect data copied", "Beeblebrox",
406: ln);
407: assertEquals("Incorrect data copied", "Zaphod", fn);
408: }
409: }
410: SqlUtil.closeResult(rs);
411:
412: rs = tstmt.executeQuery("select count(*) from address");
413: if (rs.next()) {
414: assertEquals(
415: "Wrong number of rows copied to address table",
416: 4, rs.getInt(1));
417: }
418: SqlUtil.closeResult(rs);
419:
420: ConnectionMgr.getInstance().removeProfile(con.getProfile());
421: ConnectionMgr.getInstance().removeProfile(
422: target.getProfile());
423: } catch (Exception e) {
424: e.printStackTrace();
425: fail(e.getMessage());
426: } finally {
427: ConnectionMgr.getInstance().disconnectAll();
428: }
429: }
430:
431: public void testCreateTarget() {
432: try {
433: TestUtil util = new TestUtil("WbCopyCreateTest");
434: util.prepareEnvironment();
435:
436: WbConnection source = util
437: .getConnection("copyCreateTestSource");
438: WbConnection target = util
439: .getHSQLConnection("copyCreateTestTarget");
440:
441: Statement stmt = source.createStatement();
442:
443: stmt
444: .executeUpdate("create table person (nr integer not null primary key, lastname varchar(50), firstname varchar(50))");
445: stmt
446: .executeUpdate("insert into person (nr, lastname, firstname) values (1,'Dent', 'Arthur')");
447: stmt
448: .executeUpdate("insert into person (nr, lastname, firstname) values (2,'Beeblebrox', 'Zaphod')");
449: stmt
450: .executeUpdate("insert into person (nr, lastname, firstname) values (3,'Moviestar', 'Mary')");
451: stmt
452: .executeUpdate("insert into person (nr, lastname, firstname) values (4,'Perfect', 'Ford')");
453: source.commit();
454:
455: // First test a copy with a fully specified column mapping
456: String sql = "wbcopy -createTarget=true "
457: + "-sourceTable=person "
458: + "-targetTable=participants "
459: + "-columns=nr/person_id, firstname/firstname, lastname/lastname "
460: + "-sourceProfile='copyCreateTestSource' "
461: + "-targetProfile='copyCreateTestTarget' ";
462:
463: WbCopy copyCmd = new WbCopy();
464: StatementRunnerResult result = copyCmd.execute(sql);
465: assertEquals(result.getMessageBuffer().toString(), true,
466: result.isSuccess());
467:
468: Statement tstmt = target.createStatement();
469: ResultSet rs = tstmt
470: .executeQuery("select person_id, lastname, firstname from participants");
471: while (rs.next()) {
472: int nr = rs.getInt(1);
473: String ln = rs.getString(2);
474: String fn = rs.getString(3);
475: if (nr == 1) {
476: assertEquals("Incorrect data copied", "Dent", ln);
477: assertEquals("Incorrect data copied", "Arthur", fn);
478: } else if (nr == 2) {
479: assertEquals("Incorrect data copied", "Beeblebrox",
480: ln);
481: assertEquals("Incorrect data copied", "Zaphod", fn);
482: }
483: }
484: SqlUtil.closeResult(rs);
485:
486: // Now test the table creation without columns
487: sql = "wbcopy -createTarget=true " + "-dropTarget=true "
488: + "-sourceTable=person "
489: + "-targetTable=participants "
490: + "-sourceProfile='copyCreateTestSource' "
491: + "-targetProfile='copyCreateTestTarget' ";
492:
493: result = copyCmd.execute(sql);
494: assertEquals(result.getMessageBuffer().toString(), true,
495: result.isSuccess());
496:
497: rs = tstmt
498: .executeQuery("select nr, lastname, firstname from participants");
499: while (rs.next()) {
500: int nr = rs.getInt(1);
501: String ln = rs.getString(2);
502: String fn = rs.getString(3);
503: if (nr == 1) {
504: assertEquals("Incorrect data copied", "Dent", ln);
505: assertEquals("Incorrect data copied", "Arthur", fn);
506: } else if (nr == 2) {
507: assertEquals("Incorrect data copied", "Beeblebrox",
508: ln);
509: assertEquals("Incorrect data copied", "Zaphod", fn);
510: }
511: }
512: SqlUtil.closeResult(rs);
513:
514: ConnectionMgr.getInstance().removeProfile(
515: source.getProfile());
516: ConnectionMgr.getInstance().removeProfile(
517: target.getProfile());
518:
519: } catch (Exception e) {
520: e.printStackTrace();
521: fail(e.getMessage());
522: } finally {
523: ConnectionMgr.getInstance().disconnectAll();
524: }
525: }
526:
527: public void testCopySchemaCreateTable() {
528: try {
529: TestUtil util = new TestUtil("WbCopyTest");
530: util.prepareEnvironment();
531:
532: WbConnection con = util
533: .getConnection("schemaCopyCreateSource");
534: WbConnection target = util
535: .getHSQLConnection("schemaCopyCreateTarget");
536:
537: WbCopy copyCmd = new WbCopy();
538: copyCmd.setConnection(con);
539:
540: Statement stmt = con.createStatement();
541:
542: stmt
543: .executeUpdate("create table person (nr integer not null primary key, lastname varchar(50), firstname varchar(50), binary_data blob)");
544: stmt
545: .executeUpdate("create table address (person_id integer, address_details varchar(100))");
546:
547: stmt
548: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (1,'Dent', 'Arthur', '01')");
549: stmt
550: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (2,'Beeblebrox', 'Zaphod','0202')");
551: stmt
552: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (3,'Moviestar', 'Mary', '030303')");
553: stmt
554: .executeUpdate("insert into person (nr, lastname, firstname, binary_data) values (4,'Perfect', 'Ford', '04040404')");
555:
556: stmt
557: .executeUpdate("insert into address (person_id, address_details) values (1, 'Arlington')");
558: stmt
559: .executeUpdate("insert into address (person_id, address_details) values (2, 'Heart of Gold')");
560: stmt
561: .executeUpdate("insert into address (person_id, address_details) values (3, 'Sleepy by Lane')");
562: stmt
563: .executeUpdate("insert into address (person_id, address_details) values (4, 'Betelgeuse')");
564:
565: con.commit();
566:
567: String sql = "wbcopy -createTarget=true -sourceTable=person,address -sourceProfile='schemaCopyCreateSource' -targetProfile='schemaCopyCreateTarget'";
568:
569: StatementRunnerResult result = copyCmd.execute(sql);
570: assertEquals(result.getMessageBuffer().toString(), true,
571: result.isSuccess());
572:
573: Statement tstmt = target.createStatement();
574: ResultSet rs = tstmt
575: .executeQuery("select nr, lastname, firstname from person");
576: while (rs.next()) {
577: int nr = rs.getInt(1);
578: String ln = rs.getString(2);
579: String fn = rs.getString(3);
580: if (nr == 1) {
581: assertEquals("Incorrect data copied", "Dent", ln);
582: assertEquals("Incorrect data copied", "Arthur", fn);
583: } else if (nr == 2) {
584: assertEquals("Incorrect data copied", "Beeblebrox",
585: ln);
586: assertEquals("Incorrect data copied", "Zaphod", fn);
587: }
588: }
589: SqlUtil.closeResult(rs);
590: ConnectionMgr.getInstance().removeProfile(con.getProfile());
591: ConnectionMgr.getInstance().removeProfile(
592: target.getProfile());
593: } catch (Exception e) {
594: e.printStackTrace();
595: fail(e.getMessage());
596: } finally {
597: ConnectionMgr.getInstance().disconnectAll();
598: }
599: }
600:
601: }
|