CsvToolTest.java Source File

00001 package net.threebit.utils.sosc.test;
00002 
00003 /*
00004         Copyright 2003 Shawn Deleurme
00005         Copyright 2003 Kevin O'Donnell
00006 
00007         This program is free software; you can redistribute it and/or modify
00008         it under the terms of the GNU General Public License as published by
00009         the Free Software Foundation; either version 2 of the License, or
00010         (at your option) any later version.
00011 
00012         This program is distributed in the hope that it will be useful,
00013         but WITHOUT ANY WARRANTY; without even the implied warranty of
00014         MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
00015         GNU General Public License for more details.
00016 
00017         You should have received a copy of the GNU General Public License
00018         along with this program; if not, write to the Free Software
00019         Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
00020 */
00021 
00022 import java.io.*;
00023 import java.sql.*;
00024 import java.util.*;
00025 import java.util.logging.*;
00026 import junit.framework.*;
00027 import net.threebit.utils.sosc.*;
00028 
00033 public class CsvToolTest extends TestCase {
00034 
00035         private DbTool getDbTool() throws Exception {
00036                 String url = System.getProperty("net.threebit.utils.sosc.DbTool.jdbcURL");
00037                 String className = System.getProperty("net.threebit.utils.sosc.DbTool.jdbcURLClassName");
00038                 String userName = System.getProperty("net.threebit.utils.sosc.DbTool.jdbcURLUserName");
00039                 String password = System.getProperty("net.threebit.utils.sosc.DbTool.jdbcURLPassword");
00040                 return new DbTool(className, url, userName, password);
00041         }
00042 
00043         public void test20() throws Exception {
00044                 String line = "1,2,3";
00045                 String[] l = CsvTool.csvLine(line);
00046                 assertTrue(l[0].equals("1"));
00047                 assertTrue(l[1].equals("2"));
00048                 assertTrue(l[2].equals("3"));
00049 
00050                 line = "1,2,3,";
00051                 l = CsvTool.csvLine(line);
00052                 assertTrue(l[0].equals("1"));
00053                 assertTrue(l[1].equals("2"));
00054                 assertTrue(l[2].equals("3"));
00055                 assertTrue(l[3].equals(""));
00056 
00057                 line = "1,\"2,\",3,";
00058                 l = CsvTool.csvLine(line);
00059                 for (int x = 0; x < l.length; x++) { System.out.println(x+": '"+l[x]+"'"); }
00060                 System.out.println(""+l);
00061                 assertTrue(l[0].equals("1"));
00062                 assertTrue(l[1].equals("2,"));
00063                 assertTrue(l[2].equals("3"));
00064                 assertTrue(l[3].equals(""));    }
00065 
00066         public void test1() throws Exception {
00067                 // Trivial case.
00068                 ResultSet r = getDbTool().query(" select '1' as one, '2' as two, '3' as three ");
00069                 r.first();
00070                 StringBuffer b = CsvTool.resultSetToCsv(r);
00071                 // System.out.println("b: '" + b + "'");
00072                 assertTrue(b.toString().equals("one,two,three\n1,2,3\n"));
00073         }
00074 
00075         public void test2() throws Exception {
00076 
00077                 // Limit and re-order the column list.
00078                 ResultSet r = getDbTool().query(" select '1' as one, '2' as two, '3' as three ");
00079                 r.first();
00080                 StringBuffer b = CsvTool.resultSetToCsv(r, new StringBuffer(), new String[] { "one","three" } );
00081                 assertTrue(b.toString().equals("one,three\n1,3\n"));
00082 
00083         }
00084 
00085         public void test3() throws Exception {
00086                 // Limit and re-order the column list.
00087                 ResultSet r = getDbTool().query(" select '1' as one, '2' as two, '3' as three ");
00088                 r.first();
00089                 try {
00090                         StringBuffer b = CsvTool.resultSetToCsv(r, new StringBuffer(), new String[] { "one","four" } );
00091                         assertTrue(false); // If we get there then the invalid column "four" was not detected.
00092                 }
00093                 catch (Exception e) {
00094                         // OK!
00095                 }
00096         }
00097 
00098         public void test4() throws Exception {
00099                 StringBuffer sb = new StringBuffer();
00100                 sb.append("\"Phone\",\"Name\",\"Address\",\"City\",\"State\",\"Zip\",\"Keycode\"\n");
00101                 sb.append("\"(413) 536-2796\",\"Jay Baran\",\"634 Mckinstry Ave\",\"Chicopee\",\"MA\",\"01020-1121\",\"A\"\n");
00102                 List list = CsvTool.csvToList(sb, new String[] { "name","address","city","state","zip","phone" });
00103                 assertTrue(list.size() == 1);
00104                 String[] line = (String[]) list.get(0);
00105                 assertTrue(line != null);
00106                 assertTrue(line.length == 6);
00107                 assertTrue("Jay Baran".equals(line[0]));
00108                 assertTrue("634 Mckinstry Ave".equals(line[1]));
00109                 assertTrue("Chicopee".equals(line[2]));
00110                 assertTrue("MA".equals(line[3]));
00111                 assertTrue("01020-1121".equals(line[4]));
00112                 assertTrue("(413) 536-2796".equals(line[5]));
00113         }
00114 
00115         public void test5() throws Exception {
00116                 StringBuffer sb = new StringBuffer();
00117                 sb.append("c1,c2,c3\n");
00118                 sb.append("v1,\"value,2\",v3\n");
00119                 List list = CsvTool.csvToList(sb, new String[] { "c1","c3","c2" });
00120                 assertTrue(list.size() == 1);
00121                 String[] line = (String[]) list.get(0);
00122                 assertTrue(line != null);
00123                 // for (int x = 0; x < line.length; x++) { System.out.println("--->["+x+"] '" + line[x] + "'"); }
00124                 assertTrue(line.length == 3);
00125                 assertTrue("v1".equals(line[0]));
00126                 assertTrue("v3".equals(line[1]));
00127                 assertTrue("value,2".equals(line[2]));
00128         }
00129 
00130         public void test6() throws Exception {
00131                 StringBuffer sb = new StringBuffer();
00132                 sb.append("phone,first,last,address,city,state,zip\n");
00133                 sb.append("5059897545,FRED,HARPMAN,#18 GOLDEN RIDGE RD,SANTA FE,NM,87505\n");
00134                 sb.append("5626967050,JOHN,MERLAN,0437 SCOTT AVE,WHITTIER,CA,90603\n");
00135                 sb.append("8176455247,ORIS,WILLIAMS,1 1 5 GATRI X AVE,CLEBURNE,TX,76031\n");
00136                 sb.append("5708255523,FLORENCE,SNIPAS,1 1/2 KETCHUM ST,WILKES BARRE,PA,18702\n");
00137                 String[][] formats = new String[][] {
00138                         new String[] { "address","city","state","zip","phone","name" } ,
00139                         new String[] { "address","city","st","zip","phone","name" } ,
00140                         new String[] { "address","city","state","zip","phone","firstname","lastname" } ,
00141                         new String[] { "address","city","st","zip","phone","first","last" } ,
00142                         new String[] { "address","city","state","zip","phone","first","last" } ,
00143                         new String[] { "add","ct","st","zip","newphone","fn","ln" } ,
00144                 };
00145                 List entries = null;
00146                 for (int x = 0; x < formats.length; x++) {
00147                         try {
00148                                 // If the format contains multiple options, then present each one.
00149                                 // System.out.println("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! " + x + " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
00150                                 entries = CsvTool.csvToList( sb, formats[x] );
00151                         }
00152                         catch (Exception e) {
00153                                 // some exceptions are OK
00154                                 if (! e.getMessage().startsWith("CSVFORMATERROR")) { throw(e); }
00155                         }
00156                 }
00157                 assertTrue(entries != null);
00158                 assertTrue(entries.size() == 4);
00159         }
00160 
00161         public void test7() throws Exception {
00162                 String test = "val1,val2\nval3,val4\n";
00163                 ArrayList list = new ArrayList();
00164                 ArrayList row = new ArrayList();
00165                 row.add("val1");
00166                 row.add("val2");
00167                 list.add(row);
00168                 row = new ArrayList();
00169                 row.add("val3");
00170                 row.add("val4");
00171                 list.add(row);
00172                 StringBuffer sb = CsvTool.listOfListsToCsv(list);
00173                 assertTrue(test.equals(sb.toString()));
00174         }
00175 
00176         public void test8() throws Exception {
00177                 // Note the row 3, column 3 and the trailing comma *within* the cell.
00178                 StringBuffer csv = new StringBuffer(
00179                         "\"phone\",\"name\",\"address\",\"city\",\"state\",\"zip\"\n" +
00180                         "\"4042064133\",\"ANAND RAGHU\",\"328454 GEORGIA TECH STATION\",\"ATLANTA\",\"GA\",\"30332\"\n" +
00181                         "\"4042068090\",\"YONATAN COHEN\",\"330055 GEORGIA TECH STATION,\",\"ATLANTA\",\"GA\",\"30332\"\n" +
00182                         "\"4042069231\",\"DEVENDRA SATHE\",\"329882 GEORGIA TECH STATION\",\"ATLANTA\",\"GA\",\"30332\"\n"
00183                 );
00184                 String[] columns = new String[] { "phone","name","address","city","state","zip" };
00185                 try {
00186                         List list = CsvTool.csvToList(csv,columns);
00187                         // must throw exception
00188                         assertTrue(false);
00189                 }
00190                 catch (Exception e) {
00191                         assertTrue(e.getMessage().startsWith("Unhandled embedded comma condition"));
00192                 }
00193         }
00194 
00195         public void test9() throws Exception {
00196                 // Note the row 3, column 3 and the trailing comma *within* the cell.
00197                 StringBuffer csv = new StringBuffer(
00198                         "\"phone\",\"name\",\"address\",\"city\",\"state\",\"zip\"\n" +
00199                         "\"\",\"ANAND RAGHU\",\"328454 GEORGIA TECH STATION\",\"ATLANTA\",\"GA\",\"30332\"\n" 
00200                 );
00201                 String[] columns = new String[] { "phone","name","address","city","state","zip" };
00202                 List list = CsvTool.csvToList(csv,columns);
00203         }
00204 
00205         public void test10() throws Exception {
00206                 StringBuffer csv = new StringBuffer(
00207                         "\"first\",\"last\",\"address\",\"junk\",\"city\",\"state\",\"zip\",\"phone\",\"junk2\"\n" +
00208                         "\"SARI\",\"HAENISCH\",\"5049 WORNALL RD 2B\",\"2B\",\"KANSAS CITY\",\"MISSOUR\",\"MO\",\"64112\",\"8167530116\",\"CC70694C\"\n"
00209                 );
00210                 String[] columns = new String[] { "first","last","address","city","state","zip","phone" };
00211                 List list = CsvTool.csvToList(csv,columns);
00212         }
00213 
00214 }