View Javadoc
1   /*
2   Copyright (c) 2007 Health Market Science, Inc.
3   
4   Licensed under the Apache License, Version 2.0 (the "License");
5   you may not use this file except in compliance with the License.
6   You may obtain a copy of the License at
7   
8       http://www.apache.org/licenses/LICENSE-2.0
9   
10  Unless required by applicable law or agreed to in writing, software
11  distributed under the License is distributed on an "AS IS" BASIS,
12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  See the License for the specific language governing permissions and
14  limitations under the License.
15  */
16  
17  package com.healthmarketscience.jackcess.util;
18  
19  import java.io.File;
20  import java.lang.reflect.InvocationHandler;
21  import java.lang.reflect.Method;
22  import java.lang.reflect.Proxy;
23  import java.sql.ResultSet;
24  import java.sql.ResultSetMetaData;
25  import java.sql.Types;
26  import java.util.ArrayList;
27  import java.util.Arrays;
28  import java.util.List;
29  import java.util.Map;
30  
31  import com.healthmarketscience.jackcess.Column;
32  import com.healthmarketscience.jackcess.ColumnBuilder;
33  import com.healthmarketscience.jackcess.DataType;
34  import com.healthmarketscience.jackcess.Database;
35  import static com.healthmarketscience.jackcess.Database.*;
36  import com.healthmarketscience.jackcess.Table;
37  import com.healthmarketscience.jackcess.TableBuilder;
38  import com.healthmarketscience.jackcess.impl.JetFormatTest;
39  import junit.framework.TestCase;
40  import static com.healthmarketscience.jackcess.TestUtil.*;
41  
42  /** 
43   *  @author Rob Di Marco
44   */ 
45  public class ImportTest extends TestCase
46  {
47  
48    public ImportTest(String name) {
49      super(name);
50    }
51  
52    public void testImportFromFile() throws Exception
53    {
54      for (final FileFormat fileFormat : JetFormatTest.SUPPORTED_FILEFORMATS) {
55        Database db = create(fileFormat);
56        String tableName = new ImportUtil.Builder(db, "test")
57          .setDelimiter("\\t")
58          .importFile(new File("src/test/data/sample-input.tab"));
59        Table t = db.getTable(tableName);
60  
61        List<String> colNames = new ArrayList<String>();
62        for(Column c : t.getColumns()) {
63          colNames.add(c.getName());
64        }
65        assertEquals(Arrays.asList("Test1", "Test2", "Test3"), colNames);
66  
67        List<? extends Map<String, Object>> expectedRows =
68          createExpectedTable(
69              createExpectedRow(
70                  "Test1", "Foo",
71                  "Test2", "Bar",
72                  "Test3", "Ralph"),
73              createExpectedRow(
74                  "Test1", "S",
75                  "Test2", "Mouse",
76                  "Test3", "Rocks"),
77              createExpectedRow(
78                  "Test1", "",
79                  "Test2", "Partial line",
80                  "Test3", null),
81              createExpectedRow(
82                  "Test1", " Quoted Value",
83                  "Test2", " bazz ",
84                  "Test3", " Really \"Crazy" + ImportUtil.LINE_SEPARATOR
85                  + "value\""),
86              createExpectedRow(
87                  "Test1", "buzz",
88                  "Test2", "embedded\tseparator",
89                  "Test3", "long")
90              );
91        assertTable(expectedRows, t);
92  
93        t = new TableBuilder("test2")
94          .addColumn(new ColumnBuilder("T1", DataType.TEXT))
95          .addColumn(new ColumnBuilder("T2", DataType.TEXT))
96          .addColumn(new ColumnBuilder("T3", DataType.TEXT))
97          .toTable(db);
98  
99        new ImportUtil.Builder(db, "test2")
100         .setDelimiter("\\t")
101         .setUseExistingTable(true)
102         .setHeader(false)
103         .importFile(new File("src/test/data/sample-input.tab"));
104 
105       expectedRows =
106         createExpectedTable(
107             createExpectedRow(
108                 "T1", "Test1",
109                 "T2", "Test2",
110                 "T3", "Test3"),
111             createExpectedRow(
112                 "T1", "Foo",
113                 "T2", "Bar",
114                 "T3", "Ralph"),
115             createExpectedRow(
116                 "T1", "S",
117                 "T2", "Mouse",
118                 "T3", "Rocks"),
119             createExpectedRow(
120                 "T1", "",
121                 "T2", "Partial line",
122                 "T3", null),
123             createExpectedRow(
124                 "T1", " Quoted Value",
125                 "T2", " bazz ",
126                 "T3", " Really \"Crazy" + ImportUtil.LINE_SEPARATOR
127                 + "value\""),
128             createExpectedRow(
129                 "T1", "buzz",
130                 "T2", "embedded\tseparator",
131                 "T3", "long")
132             );
133       assertTable(expectedRows, t);
134 
135 
136       ImportFilter oddFilter = new SimpleImportFilter() {
137         private int _num;
138         @Override
139         public Object[] filterRow(Object[] row) {
140           if((_num++ % 2) == 1) {
141             return null;
142           }
143           return row;
144         }
145       };
146 
147       tableName = new ImportUtil.Builder(db, "test3")
148         .setDelimiter("\\t")
149         .setFilter(oddFilter)
150         .importFile(new File("src/test/data/sample-input.tab"));
151       t = db.getTable(tableName);
152 
153       colNames = new ArrayList<String>();
154       for(Column c : t.getColumns()) {
155         colNames.add(c.getName());
156       }
157       assertEquals(Arrays.asList("Test1", "Test2", "Test3"), colNames);
158 
159       expectedRows =
160         createExpectedTable(
161             createExpectedRow(
162                 "Test1", "Foo",
163                 "Test2", "Bar",
164                 "Test3", "Ralph"),
165             createExpectedRow(
166                 "Test1", "",
167                 "Test2", "Partial line",
168                 "Test3", null),
169             createExpectedRow(
170                 "Test1", "buzz",
171                 "Test2", "embedded\tseparator",
172                 "Test3", "long")
173             );
174       assertTable(expectedRows, t);
175 
176       db.close();
177     }
178   }
179 
180   public void testImportFromFileWithOnlyHeaders() throws Exception
181   {
182     for (final FileFormat fileFormat : JetFormatTest.SUPPORTED_FILEFORMATS) {
183       Database db = create(fileFormat);
184       String tableName = new ImportUtil.Builder(db, "test")
185         .setDelimiter("\\t")
186         .importFile(new File("src/test/data/sample-input-only-headers.tab"));
187 
188       Table t = db.getTable(tableName);
189 
190       List<String> colNames = new ArrayList<String>();
191       for(Column c : t.getColumns()) {
192         colNames.add(c.getName());
193       }
194       assertEquals(Arrays.asList(
195                        "RESULT_PHYS_ID", "FIRST", "MIDDLE", "LAST", "OUTLIER",
196                        "RANK", "CLAIM_COUNT", "PROCEDURE_COUNT",
197                        "WEIGHTED_CLAIM_COUNT", "WEIGHTED_PROCEDURE_COUNT"), 
198                    colNames);
199 
200       db.close();
201     }
202   }
203 
204   public void testCopySqlHeaders() throws Exception
205   {
206     for (final FileFormat fileFormat : JetFormatTest.SUPPORTED_FILEFORMATS) {
207 
208       TestResultSet rs = new TestResultSet();
209 
210       rs.addColumn(Types.INTEGER, "col1");
211       rs.addColumn(Types.VARCHAR, "col2", 60, 0, 0);
212       rs.addColumn(Types.VARCHAR, "col3", 500, 0, 0);
213       rs.addColumn(Types.BINARY, "col4", 128, 0, 0);
214       rs.addColumn(Types.BINARY, "col5", 512, 0, 0);
215       rs.addColumn(Types.NUMERIC, "col6", 0, 7, 15);
216       rs.addColumn(Types.VARCHAR, "col7", Integer.MAX_VALUE, 0, 0);
217 
218       Database db = create(fileFormat);
219       ImportUtil.importResultSet((ResultSet)Proxy.newProxyInstance(
220                        Thread.currentThread().getContextClassLoader(),
221                        new Class[]{ResultSet.class},
222                        rs), db, "Test1");
223 
224       Table t = db.getTable("Test1");
225       List<? extends Column> columns = t.getColumns();
226       assertEquals(7, columns.size());
227 
228       Column c = columns.get(0);
229       assertEquals("col1", c.getName());
230       assertEquals(DataType.LONG, c.getType());
231 
232       c = columns.get(1);
233       assertEquals("col2", c.getName());
234       assertEquals(DataType.TEXT, c.getType());
235       assertEquals(120, c.getLength());
236 
237       c = columns.get(2);
238       assertEquals("col3", c.getName());
239       assertEquals(DataType.MEMO, c.getType());
240       assertEquals(0, c.getLength());
241 
242       c = columns.get(3);
243       assertEquals("col4", c.getName());
244       assertEquals(DataType.BINARY, c.getType());
245       assertEquals(128, c.getLength());
246 
247       c = columns.get(4);
248       assertEquals("col5", c.getName());
249       assertEquals(DataType.OLE, c.getType());
250       assertEquals(0, c.getLength());
251 
252       c = columns.get(5);
253       assertEquals("col6", c.getName());
254       assertEquals(DataType.NUMERIC, c.getType());
255       assertEquals(17, c.getLength());
256       assertEquals(7, c.getScale());
257       assertEquals(15, c.getPrecision());
258 
259       c = columns.get(6);
260       assertEquals("col7", c.getName());
261       assertEquals(DataType.MEMO, c.getType());
262       assertEquals(0, c.getLength());
263     }
264   }
265 
266 
267   private static class TestResultSet implements InvocationHandler
268   {
269     private List<Integer> _types = new ArrayList<Integer>();
270     private List<String> _names = new ArrayList<String>();
271     private List<Integer> _displaySizes = new ArrayList<Integer>();
272     private List<Integer> _scales = new ArrayList<Integer>();
273     private List<Integer> _precisions = new ArrayList<Integer>();
274     
275     public Object invoke(Object proxy, Method method, Object[] args)
276     {
277       String methodName = method.getName();
278       if(methodName.equals("getMetaData")) {
279         return Proxy.newProxyInstance(
280             Thread.currentThread().getContextClassLoader(),
281             new Class[]{ResultSetMetaData.class},
282             this);
283       } else if(methodName.equals("next")) {
284         return Boolean.FALSE;
285       } else if(methodName.equals("getColumnCount")) {
286         return _types.size();
287       } else if(methodName.equals("getColumnName")) {
288         return getValue(_names, args[0]);
289       } else if(methodName.equals("getColumnDisplaySize")) {
290         return getValue(_displaySizes, args[0]);
291       } else if(methodName.equals("getColumnType")) {
292         return getValue(_types, args[0]);
293       } else if(methodName.equals("getScale")) {
294         return getValue(_scales, args[0]);
295       } else if(methodName.equals("getPrecision")) {
296         return getValue(_precisions, args[0]);
297       } else {
298         throw new UnsupportedOperationException(methodName);
299       }
300     }
301 
302     public void addColumn(int type, String name)
303     {
304       addColumn(type, name, 0, 0, 0);
305     }
306 
307     public void addColumn(int type, String name, int displaySize,
308                           int scale, int precision)
309     {
310       _types.add(type);
311       _names.add(name);
312       _displaySizes.add(displaySize);
313       _scales.add(scale);
314       _precisions.add(precision);
315     }
316 
317     private static <T> T getValue(List<T> values, Object index) {
318       return values.get((Integer)index - 1);
319     }
320   }
321   
322 }