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.db;
007:
008: import java.io.File;
009: import java.io.FileReader;
010: import java.io.RandomAccessFile;
011: import java.sql.Connection;
012: import java.sql.PreparedStatement;
013: import java.sql.ResultSet;
014: import java.sql.ResultSetMetaData;
015: import java.sql.Statement;
016: import java.util.ArrayList;
017: import java.util.Random;
018:
019: import org.h2.test.TestBase;
020: import org.h2.tools.Csv;
021: import org.h2.util.FileUtils;
022: import org.h2.util.IOUtils;
023: import org.h2.util.StringUtils;
024:
025: /**
026: * CSVREAD and CSVWRITE tests.
027: *
028: * @author Thomas Mueller
029: * @author Sylvain Cuaz (testNull)
030: *
031: */
032: public class TestCsv extends TestBase {
033:
034: public void test() throws Exception {
035: testNull();
036: testRandomData();
037: testEmptyFieldDelimiter();
038: testFieldDelimiter();
039: testAsTable();
040: testWriteRead();
041: testRead();
042: testPipe();
043: }
044:
045: /**
046: * Test custom NULL string.
047: */
048: public void testNull() throws Exception {
049: deleteDb("csv");
050:
051: File f = new File(baseDir + "/testNull.csv");
052: FileUtils.delete(f.getAbsolutePath());
053:
054: RandomAccessFile file = new RandomAccessFile(f, "rw");
055: String csvContent = "\"A\",\"B\",\"C\",\"D\"\n\\N,\"\",\"\\N\",";
056: file.write(csvContent.getBytes("UTF-8"));
057: file.close();
058: Csv csv = Csv.getInstance();
059: csv.setNullString("\\N");
060: ResultSet rs = csv.read(f.getPath(), null, "UTF8");
061: ResultSetMetaData meta = rs.getMetaData();
062: check(meta.getColumnCount(), 4);
063: check(meta.getColumnLabel(1), "A");
064: check(meta.getColumnLabel(2), "B");
065: check(meta.getColumnLabel(3), "C");
066: check(meta.getColumnLabel(4), "D");
067: check(rs.next());
068: check(rs.getString(1), null);
069: check(rs.getString(2), "");
070: // null is never quoted
071: check(rs.getString(3), "\\N");
072: // an empty string is always parsed as null
073: check(rs.getString(4), null);
074: checkFalse(rs.next());
075:
076: Connection conn = getConnection("csv");
077: Statement stat = conn.createStatement();
078: stat
079: .execute("call csvwrite('"
080: + f.getPath()
081: + "', 'select NULL as a, '''' as b, ''\\N'' as c, NULL as d', 'UTF8', ',', '\"', NULL, '\\N', '\n')");
082: FileReader reader = new FileReader(f);
083: // on read, an empty string is treated like null,
084: // but on write a null is always written with the nullString
085: String data = IOUtils.readStringAndClose(reader, -1);
086: check(csvContent + "\\N", data.trim());
087: conn.close();
088:
089: FileUtils.delete(f.getAbsolutePath());
090: }
091:
092: private void testRandomData() throws Exception {
093: deleteDb("csv");
094: Connection conn = getConnection("csv");
095: Statement stat = conn.createStatement();
096: stat.execute("drop table if exists test");
097: stat.execute("create table test(a varchar, b varchar)");
098: int len = getSize(1000, 10000);
099: PreparedStatement prep = conn
100: .prepareStatement("insert into test values(?, ?)");
101: ArrayList list = new ArrayList();
102: Random random = new Random(1);
103: for (int i = 0; i < len; i++) {
104: String a = randomData(random), b = randomData(random);
105: prep.setString(1, a);
106: prep.setString(2, b);
107: list.add(new String[] { a, b });
108: prep.execute();
109: }
110: stat
111: .execute("CALL CSVWRITE('test.csv', 'SELECT * FROM test', 'UTF-8', '|', '#')");
112: Csv csv = Csv.getInstance();
113: csv.setFieldSeparatorRead('|');
114: csv.setFieldDelimiter('#');
115: ResultSet rs = csv.read("test.csv", null, "UTF-8");
116: for (int i = 0; i < len; i++) {
117: check(rs.next());
118: String[] pair = (String[]) list.get(i);
119: check(pair[0], rs.getString(1));
120: check(pair[1], rs.getString(2));
121: }
122: checkFalse(rs.next());
123: conn.close();
124: }
125:
126: private String randomData(Random random) {
127: int len = random.nextInt(5);
128: StringBuffer buff = new StringBuffer();
129: String chars = "\\\'\",\r\n\t ;.-123456|#";
130: for (int i = 0; i < len; i++) {
131: buff.append(chars.charAt(random.nextInt(chars.length())));
132: }
133: return buff.toString();
134: }
135:
136: private void testEmptyFieldDelimiter() throws Exception {
137: File f = new File(baseDir + "/test.csv");
138: f.delete();
139: Connection conn = getConnection("csv");
140: Statement stat = conn.createStatement();
141: stat
142: .execute("call csvwrite('"
143: + baseDir
144: + "/test.csv', 'select 1 id, ''Hello'' name', null, '|', '', null, null, chr(10))");
145: FileReader reader = new FileReader(baseDir + "/test.csv");
146: String text = IOUtils.readStringAndClose(reader, -1).trim();
147: text = StringUtils.replaceAll(text, "\n", " ");
148: check("ID|NAME 1|Hello", text);
149: ResultSet rs = stat.executeQuery("select * from csvread('"
150: + baseDir + "/test.csv', null, null, '|', '')");
151: ResultSetMetaData meta = rs.getMetaData();
152: check(meta.getColumnCount(), 2);
153: check(meta.getColumnLabel(1), "ID");
154: check(meta.getColumnLabel(2), "NAME");
155: check(rs.next());
156: check(rs.getString(1), "1");
157: check(rs.getString(2), "Hello");
158: checkFalse(rs.next());
159: conn.close();
160: }
161:
162: private void testFieldDelimiter() throws Exception {
163: File f = new File(baseDir + "/test.csv");
164: f.delete();
165: RandomAccessFile file = new RandomAccessFile(f, "rw");
166: file.write("'A'; 'B'\n\'It\\'s nice\'; '\nHello\\*\n'"
167: .getBytes());
168: file.close();
169: Connection conn = getConnection("csv");
170: Statement stat = conn.createStatement();
171: ResultSet rs = stat.executeQuery("select * from csvread('"
172: + baseDir + "/test.csv', null, null, ';', '''', '\\')");
173: ResultSetMetaData meta = rs.getMetaData();
174: check(meta.getColumnCount(), 2);
175: check(meta.getColumnLabel(1), "A");
176: check(meta.getColumnLabel(2), "B");
177: check(rs.next());
178: check(rs.getString(1), "It's nice");
179: check(rs.getString(2), "\nHello*\n");
180: checkFalse(rs.next());
181: stat
182: .execute("call csvwrite('"
183: + baseDir
184: + "/test2.csv', 'select * from csvread(''"
185: + baseDir
186: + "/test.csv'', null, null, '';'', '''''''', ''\\'')', null, '+', '*', '#')");
187: rs = stat.executeQuery("select * from csvread('" + baseDir
188: + "/test2.csv', null, null, '+', '*', '#')");
189: meta = rs.getMetaData();
190: check(meta.getColumnCount(), 2);
191: check(meta.getColumnLabel(1), "A");
192: check(meta.getColumnLabel(2), "B");
193: check(rs.next());
194: check(rs.getString(1), "It's nice");
195: check(rs.getString(2), "\nHello*\n");
196: checkFalse(rs.next());
197: conn.close();
198: }
199:
200: private void testPipe() throws Exception {
201: deleteDb("csv");
202: Connection conn = getConnection("csv");
203: Statement stat = conn.createStatement();
204: stat
205: .execute("call csvwrite('"
206: + baseDir
207: + "/test.csv', 'select 1 id, ''Hello'' name', 'utf-8', '|')");
208: ResultSet rs = stat.executeQuery("select * from csvread('"
209: + baseDir + "/test.csv', null, 'utf-8', '|')");
210: check(rs.next());
211: check(rs.getInt(1), 1);
212: check(rs.getString(2), "Hello");
213: checkFalse(rs.next());
214: new File(baseDir + "/test.csv").delete();
215:
216: // PreparedStatement prep = conn.prepareStatement("select * from
217: // csvread(?, null, ?, ?)");
218: // prep.setString(1, BASE_DIR+"/test.csv");
219: // prep.setString(2, "utf-8");
220: // prep.setString(3, "|");
221: // rs = prep.executeQuery();
222:
223: conn.close();
224: }
225:
226: private void testAsTable() throws Exception {
227: deleteDb("csv");
228: Connection conn = getConnection("csv");
229: Statement stat = conn.createStatement();
230: stat.execute("call csvwrite('" + baseDir
231: + "/test.csv', 'select 1 id, ''Hello'' name')");
232: ResultSet rs = stat.executeQuery("select name from csvread('"
233: + baseDir + "/test.csv')");
234: check(rs.next());
235: check(rs.getString(1), "Hello");
236: checkFalse(rs.next());
237: rs = stat.executeQuery("call csvread('" + baseDir
238: + "/test.csv')");
239: check(rs.next());
240: check(rs.getInt(1), 1);
241: check(rs.getString(2), "Hello");
242: checkFalse(rs.next());
243: new File(baseDir + "/test.csv").delete();
244: conn.close();
245: }
246:
247: public void testRead() throws Exception {
248: File f = new File(baseDir + "/test.csv");
249: f.delete();
250: RandomAccessFile file = new RandomAccessFile(f, "rw");
251: file
252: .write("a,b,c,d\n201,-2,0,18\n, \"abc\"\"\" ,,\"\"\n 1 ,2 , 3, 4 \n5, 6, 7, 8"
253: .getBytes());
254: file.close();
255: ResultSet rs = Csv.getInstance().read(baseDir + "/test.csv",
256: null, "UTF8");
257: ResultSetMetaData meta = rs.getMetaData();
258: check(meta.getColumnCount(), 4);
259: check(meta.getColumnLabel(1), "a");
260: check(meta.getColumnLabel(2), "b");
261: check(meta.getColumnLabel(3), "c");
262: check(meta.getColumnLabel(4), "d");
263: check(rs.next());
264: check(rs.getString(1), "201");
265: check(rs.getString(2), "-2");
266: check(rs.getString(3), "0");
267: check(rs.getString(4), "18");
268: check(rs.next());
269: check(rs.getString(1), null);
270: check(rs.getString(2), "abc\"");
271: check(rs.getString(3), null);
272: check(rs.getString(4), "");
273: check(rs.next());
274: check(rs.getString(1), "1");
275: check(rs.getString(2), "2");
276: check(rs.getString(3), "3");
277: check(rs.getString(4), "4");
278: check(rs.next());
279: check(rs.getString(1), "5");
280: check(rs.getString(2), "6");
281: check(rs.getString(3), "7");
282: check(rs.getString(4), "8");
283: checkFalse(rs.next());
284:
285: // a,b,c,d
286: // 201,-2,0,18
287: // 201,2,0,18
288: // 201,2,0,18
289: // 201,2,0,18
290: // 201,2,0,18
291: // 201,2,0,18
292: }
293:
294: public void testWriteRead() throws Exception {
295:
296: deleteDb("csv");
297:
298: Connection conn = getConnection("csv");
299: Statement stat = conn.createStatement();
300: stat.execute("CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR)");
301: int len = 100;
302: for (int i = 0; i < len; i++) {
303: stat.execute("INSERT INTO TEST(NAME) VALUES('Ruebezahl')");
304: }
305: Csv.getInstance().write(conn, baseDir + "/testRW.csv",
306: "SELECT * FROM TEST", "UTF8");
307: ResultSet rs = Csv.getInstance().read(baseDir + "/testRW.csv",
308: null, "UTF8");
309: // stat.execute("CREATE ALIAS CSVREAD FOR \"org.h2.tools.Csv.read\"");
310: ResultSetMetaData meta = rs.getMetaData();
311: check(2, meta.getColumnCount());
312: for (int i = 0; i < len; i++) {
313: rs.next();
314: check(rs.getString("ID"), "" + (i + 1));
315: check(rs.getString("NAME"), "Ruebezahl");
316: }
317: checkFalse(rs.next());
318: rs.close();
319: conn.close();
320:
321: }
322:
323: }
|