001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.tools.importExport
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.tools;
023:
024: import java.sql.Connection;
025: import java.sql.DriverManager;
026: import java.sql.Statement;
027: import java.sql.ResultSet;
028: import java.sql.SQLException;
029: import java.sql.PreparedStatement;
030: import java.sql.ResultSetMetaData;
031: import java.io.File;
032: import java.io.FileOutputStream;
033: import java.io.InputStream;
034: import java.io.BufferedInputStream;
035: import java.io.FileInputStream;
036: import java.util.Properties;
037: import org.apache.derby.tools.ij;
038: import org.apache.derby.tools.JDBCDisplayUtil;
039:
040: /**
041: This tests import and export utilties. It first creates
042: a temp table T1 and inserts data into it. Then it calls
043: export to export data out from it into a temp file. Then
044: it calls import to read data from the temp file just
045: created. The program goes through the resultset of import
046: and inserts one row at a time into another temp table T2
047: which has same number of columns as T1. Then it compares
048: number of rows in T1 and T2. If the number of rows are same
049: as in T1 then part of the test succedded.
050:
051: The second part imports data out from T2 into second temp
052: file and then we compare both the temp files to see if the
053: 2 files exactly match
054:
055: @author Mamta, Suresht
056: */
057:
058: public class importExport {
059:
060: private static Connection conn;
061: private static String currentVersion;
062: private static boolean passed = false;
063:
064: public static void main(String[] args) {
065: System.out.println("Test importExport starting");
066:
067: try {
068: // use the ij utility to read the property file and
069: // make the initial connection.
070: ij.getPropertyArg(args);
071: conn = ij.startJBMS();
072:
073: conn.setAutoCommit(true);
074: setup(true);
075:
076: //try to import from a file which doesn't exist
077: try {
078: System.out.println("testing non-existing data file");
079: doImport("Z", "T1", null, null, null, 0);
080: } catch (Exception ex) {
081: printExceptionMessage(ex);
082: }
083:
084: //try to import from a null file
085: try {
086: System.out.println("testing null data file");
087: doImport(null, "T1", null, null, null, 0);
088: } catch (Exception ex) {
089: printExceptionMessage(ex);
090: }
091:
092: System.out.println("testing empty table");
093: doImportAndExport("T1", null, null, null);
094:
095: System.out
096: .println("testing empty table with Delimited format");
097: doImportAndExport("T1", null, null, "8859_1");
098:
099: System.out
100: .println("testing empty table import Field/Char Delimiters");
101: doImportAndExport("T1", "\t", "|", "8859_1");
102:
103: cleanupBeforeNextRun();
104: addDummyRows();
105: System.out
106: .println("testing import/export with default options");
107: doImportAndExport("T1", null, null, null);
108:
109: cleanupBeforeNextRun();
110: System.out.println("testing IE with code set 8859_1");
111: doImportAndExport("T1", null, null, "8859_1");
112:
113: cleanupBeforeNextRun();
114: System.out.println("testing IE with delimiter and codeset");
115: doImportAndExport("T1", "\t", "|", "8859_1");
116:
117: cleanupBeforeNextRun();
118: System.out
119: .println("testing IE with delimiters(%, &) and Cp1252");
120: doImportAndExport("T1", "%", "&", "Cp1252");
121:
122: cleanupBeforeNextRun();
123: System.out
124: .println("testing IE with delimiters(%, &) and UTF-16");
125: doImportAndExport("T1", "%", "&", "UTF-16");
126:
127: cleanupBeforeNextRun();
128:
129: System.out
130: .println("testing IE with delimiters(^, #) and WRONG ENCODEINGH");
131: try {
132: doImportAndExport("T1", "^", "#", "INAVALID ENCODING");
133: } catch (Exception ex) {
134: printExceptionMessage(ex);
135: }
136:
137: System.out
138: .println("testing datatypes that does not have Export Supprt");
139: try {
140: doExport("T3", null, null, null);
141: } catch (Exception ex) {
142: printExceptionMessage(ex);
143: }
144:
145: try {
146: doImport("T1", "T3", null, null, null, 0);
147: } catch (Exception ex) {
148: printExceptionMessage(ex);
149: }
150:
151: //test less data case on the seconds line of input that
152: //should throw end of file exception.
153: try {
154: doImportFromFile("extin/EndOfFile.txt", "T4", null,
155: null, null, 0);
156: } catch (Exception ex) {
157: printExceptionMessage(ex);
158: }
159:
160: System.out
161: .println("PASS: finished testing import and export");
162: teardown();
163: System.out
164: .println("PASS: finished cleaning up the temporary objects from database");
165:
166: conn.close();
167:
168: passed = true;
169:
170: } catch (Throwable e) {
171: System.out.println("FAIL: exception thrown:");
172: passed = false;
173: JDBCDisplayUtil.ShowException(System.out, e);
174: }
175:
176: if (passed)
177: System.out.println("PASS");
178: System.out.println("Test importExport finished");
179: }
180:
181: static void cleanupBeforeNextRun() throws Exception {
182: Statement stmt = conn.createStatement();
183: stmt.execute("delete from t2");
184: stmt.close();
185: }
186:
187: static void doImportAndExport(String fromTable, String colDel,
188: String charDel, String codeset) throws Exception {
189:
190: doExport(fromTable, colDel, charDel, codeset);
191: doImportAndVerify(fromTable, colDel, charDel, codeset, 0);
192: //test with replace
193: doImportAndVerify(fromTable, colDel, charDel, codeset, 1);
194:
195: }
196:
197: private static void doExport(String fromTable, String colDel,
198: String charDel, String codeset) throws Exception {
199:
200: String expsql = "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)";
201: PreparedStatement ps = conn.prepareStatement(expsql);
202: ps.setString(1, "APP");
203: ps.setString(2, fromTable);
204: ps.setString(3, (fromTable == null ? fromTable : "extinout/"
205: + fromTable + ".dat"));
206: ps.setString(4, colDel);
207: ps.setString(5, charDel);
208: ps.setString(6, codeset);
209:
210: //perform export
211: ps.execute();
212: ps.close();
213:
214: }
215:
216: private static void doImport(String fromTable, String toTable,
217: String colDel, String charDel, String codeset, int replace)
218: throws Exception {
219:
220: String impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)";
221: PreparedStatement ps = conn.prepareStatement(impsql);
222: ps.setString(1, "APP");
223: ps.setString(2, toTable);
224: ps.setString(3, (fromTable == null ? fromTable : "extinout/"
225: + fromTable + ".dat"));
226: ps.setString(4, colDel);
227: ps.setString(5, charDel);
228: ps.setString(6, codeset);
229: ps.setInt(7, replace);
230:
231: //perform export
232: ps.execute();
233: ps.close();
234:
235: }
236:
237: private static void doImportFromFile(String fileName,
238: String toTable, String colDel, String charDel,
239: String codeset, int replace) throws Exception {
240:
241: String impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)";
242: PreparedStatement ps = conn.prepareStatement(impsql);
243: ps.setString(1, "APP");
244: ps.setString(2, toTable);
245: ps.setString(3, fileName);
246: ps.setString(4, colDel);
247: ps.setString(5, charDel);
248: ps.setString(6, codeset);
249: ps.setInt(7, replace);
250:
251: //perform export
252: ps.execute();
253: ps.close();
254:
255: }
256:
257: static void doImportAndVerify(String fromTable, String colDel,
258: String charDel, String codeset, int replace)
259: throws Exception {
260:
261: doImport(fromTable, "T2", colDel, charDel, codeset, replace);
262:
263: Statement stmt = conn.createStatement();
264: ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM "
265: + fromTable);
266: rs.next();
267: int numberOfRowsInT1 = rs.getInt(1);
268: rs.close();
269: rs = stmt.executeQuery("SELECT COUNT(*) FROM t2");
270: rs.next();
271: int numberOfRowsInT2 = rs.getInt(1);
272: rs.close();
273: stmt.close();
274: if (numberOfRowsInT1 != numberOfRowsInT2) {
275: System.out
276: .println("FAIL: Expected " + numberOfRowsInT1
277: + " got " + numberOfRowsInT2
278: + " rows after import");
279: throw new SQLException("Wrong number of rows returned");
280: }
281:
282: doExport("T2", colDel, charDel, codeset);
283:
284: //check whether the exported files from T1 and T2 are same now.
285: if (diffTwoFiles("extinout/" + fromTable + ".dat", "extinout/"
286: + "T2.dat")) {
287: throw new SQLException("Export from " + fromTable
288: + " and T2 don't match.");
289:
290: }
291:
292: }
293:
294: static boolean diffTwoFiles(String file1, String file2)
295: throws Exception {
296:
297: InputStream f1 = new BufferedInputStream(new FileInputStream(
298: file1));
299: InputStream f2 = new BufferedInputStream(new FileInputStream(
300: file2));
301:
302: int lineNo = 1;
303: int o = 1;
304: String lineSep = "\n";
305:
306: boolean diffed = false;
307: boolean notDone = true;
308:
309: int b1, b2;
310:
311: while (notDone) {
312: b1 = f1.read();
313: b2 = f2.read();
314: if ((b1 != b2) && (b1 != -1) && (b2 != -1)) {
315: diffed = true;
316: System.out.println(file1 + " " + file2
317: + " differ: byte " + o + ", line " + lineNo);
318: notDone = false;
319: } else {
320: if (b1 == b2) {
321: if (b1 == -1) {
322: notDone = false;
323: } else if (b1 == (int) lineSep.charAt(0)) {
324: lineNo++;
325: }
326: } else if (b1 == -1) {
327: diffed = true;
328: System.out.println(file1 + " " + file2
329: + " differ: EOF on " + file1);
330: notDone = false;
331: } else if (b2 == -1) {
332: diffed = true;
333: System.out.println(file1 + " " + file2
334: + " differ: EOF on " + file2);
335: notDone = false;
336: }
337:
338: }
339: o++;
340: }
341: return diffed;
342: }
343:
344: static void setup(boolean first) throws Exception {
345: Statement stmt = conn.createStatement();
346:
347: if (first) {
348: verifyCount(
349: stmt
350: .executeUpdate("CREATE TABLE T1 (COLUMN1 VARCHAR(5) , COLUMN2 VARCHAR(8) , "
351: + "COLUMN3 SMALLINT , COLUMN4 CHAR(11) , COLUMN5 DATE , COLUMN6 DECIMAL(5,1) , "
352: + "COLUMN7 DOUBLE PRECISION , COLUMN8 INT , COLUMN9 BIGINT , COLUMN10 NUMERIC , "
353: + "COLUMN11 REAL , COLUMN12 SMALLINT , COLUMN13 TIME , COLUMN14 TIMESTAMP , "
354: + "COLUMN15 SMALLINT , COLUMN16 VARCHAR(1))"),
355: 0);
356: verifyCount(
357: stmt
358: .executeUpdate("CREATE TABLE T2 (COLUMN1 VARCHAR(5) , COLUMN2 VARCHAR(8) , "
359: + "COLUMN3 SMALLINT, COLUMN4 CHAR(11) , COLUMN5 DATE , COLUMN6 DECIMAL(5,1) , "
360: + "COLUMN7 DOUBLE PRECISION , COLUMN8 INT , COLUMN9 BIGINT , COLUMN10 NUMERIC , "
361: + "COLUMN11 REAL , COLUMN12 SMALLINT , COLUMN13 TIME , COLUMN14 TIMESTAMP , "
362: + "COLUMN15 SMALLINT , COLUMN16 VARCHAR(1))"),
363: 0);
364: verifyCount(
365: stmt.executeUpdate("CREATE TABLE T3 (C1 BLOB)"), 0);
366: verifyCount(
367: stmt
368: .executeUpdate("create table T4 ( Account int, Fname char(30),"
369: + "Lname char(30), Company varchar(35), Address varchar(40), City varchar(20),"
370: + "State char(5), Zip char(10), Payment decimal(8,2), Balance decimal(8,2))"),
371: 0);
372:
373: } else {
374: verifyBoolean(stmt.execute("DELETE FROM t1"), false);
375: }
376: stmt.close();
377: }
378:
379: static void addDummyRows() throws Exception {
380: Statement stmt = conn.createStatement();
381:
382: verifyCount(
383: stmt
384: .executeUpdate("INSERT INTO T1 VALUES (null,'aa',1,'a',DATE('1998-06-30'),"
385: + "1,1,1,1,1,1,1,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),1,'a')"),
386: 1);
387:
388: verifyCount(
389: stmt
390: .executeUpdate("INSERT INTO T1 VALUES (null,'bb',1,'b',DATE('1998-06-30'),"
391: + "2,2,2,2,2,2,2,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),2,'b')"),
392: 1);
393:
394: verifyCount(
395: stmt
396: .executeUpdate("INSERT INTO T1 VALUES (null,'cc',1,'c',DATE('1998-06-30'),"
397: + "3,3,3,3,3,3,3,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),3,'c')"),
398: 1);
399:
400: verifyCount(
401: stmt
402: .executeUpdate("INSERT INTO T1 VALUES (null,'dd',1,'d',DATE('1998-06-30'),"
403: + "4,4,4,4,4,4,4,TIME('12:00:00'),TIMESTAMP('1998-06-30 12:00:00.0'),4,'d')"),
404: 1);
405:
406: System.out.println("PASS: setup complete");
407: }
408:
409: static void teardown() throws SQLException {
410: Statement stmt = conn.createStatement();
411:
412: verifyCount(stmt.executeUpdate("DROP TABLE t1"), 0);
413:
414: verifyCount(stmt.executeUpdate("DROP TABLE t2"), 0);
415:
416: stmt.close();
417:
418: System.out.println("PASS: teardown complete");
419: }
420:
421: static void verifyCount(int count, int expect) throws SQLException {
422: if (count != expect) {
423: System.out.println("FAIL: Expected " + expect + " got "
424: + count + " rows");
425: throw new SQLException("Wrong number of rows returned");
426: }
427: }
428:
429: static void verifyBoolean(boolean got, boolean expect)
430: throws SQLException {
431: if (got != expect) {
432: System.out.println("FAIL: Expected " + expect + " got "
433: + got);
434: throw new SQLException("Wrong boolean returned");
435: }
436: }
437:
438: static void printExceptionMessage(Exception ex) throws Exception {
439: if (ex instanceof SQLException) {
440: SQLException ie_ex = ((SQLException) ex);
441:
442: while (ie_ex.getNextException() != null) {
443: ie_ex = ie_ex.getNextException();
444: }
445: System.out.println(ie_ex.getMessage());
446: } else
447: throw ex;
448: }
449: }
|