1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 package complex.connectivity; 23 24 import com.sun.star.beans.XPropertySet; 25 import com.sun.star.lang.XMultiServiceFactory; 26 import com.sun.star.sdb.CommandType; 27 import com.sun.star.sdbc.SQLException; 28 import com.sun.star.uno.UnoRuntime; 29 import com.sun.star.util.Date; 30 import connectivity.tools.CsvDatabase; 31 import connectivity.tools.RowSet; 32 import java.io.File; 33 import java.io.FileOutputStream; 34 import java.io.PrintWriter; 35 import java.util.ArrayList; 36 import java.util.List; 37 38 import org.junit.After; 39 import org.junit.AfterClass; 40 import org.junit.Before; 41 import org.junit.BeforeClass; 42 import org.junit.Test; 43 import static org.junit.Assert.*; 44 import org.openoffice.test.Argument; 45 import org.openoffice.test.OfficeConnection; 46 47 public class FlatFileAccess 48 { FlatFileAccess()49 public FlatFileAccess() 50 { 51 } 52 53 @BeforeClass beforeClass()54 public static void beforeClass() throws Exception 55 { 56 connection.setUp(); 57 } 58 59 @AfterClass afterClass()60 public static void afterClass() throws Exception 61 { 62 connection.tearDown(); 63 } 64 65 @Before before()66 public void before() throws Exception 67 { 68 XMultiServiceFactory xMSF = UnoRuntime.queryInterface(XMultiServiceFactory.class, connection.getComponentContext().getServiceManager()); 69 m_database = new CsvDatabase( xMSF ); 70 71 // proper settings 72 final XPropertySet dataSourceSettings = m_database.getDataSource().geSettings(); 73 dataSourceSettings.setPropertyValue( "Extension", "csv" ); 74 dataSourceSettings.setPropertyValue( "HeaderLine", Boolean.TRUE ); 75 dataSourceSettings.setPropertyValue( "FieldDelimiter", " " ); 76 m_database.store(); 77 78 // write the table(s) for our test 79 final String tableLocation = m_database.getTableFileLocation().getAbsolutePath(); 80 final PrintWriter tableWriter = new PrintWriter( new FileOutputStream( tableLocation + File.separatorChar + "dates.csv", false ) ); 81 tableWriter.println( "ID date" ); 82 tableWriter.println( "1 2013-01-01" ); 83 tableWriter.println( "2 2012-02-02" ); 84 tableWriter.println( "3 2011-03-03" ); 85 tableWriter.close(); 86 } 87 88 private class EqualityDate extends Date 89 { EqualityDate( short i_day, short i_month, short i_year )90 EqualityDate( short i_day, short i_month, short i_year ) 91 { 92 super( i_day, i_month, i_year ); 93 } 94 EqualityDate( Date i_date )95 EqualityDate( Date i_date ) 96 { 97 super( i_date.Day, i_date.Month, i_date.Year ); 98 } 99 100 @Override equals( Object i_compare )101 public boolean equals( Object i_compare ) 102 { 103 if ( !( i_compare instanceof Date ) ) 104 return false; 105 return Day == ((Date)i_compare).Day 106 && Month == ((Date)i_compare).Month 107 && Year == ((Date)i_compare).Year; 108 } 109 } 110 111 /** 112 * ensures simple SELECTs from our table(s) work, and deliver the expected results 113 */ 114 @Test testBasicAccess()115 public void testBasicAccess() 116 { 117 testRowSetResults( 118 "SELECT * FROM \"dates\"", 119 new RowSetIntGetter(1), 120 new Integer[] { 1, 2, 3 }, 121 "simple select", "wrong IDs" 122 ); 123 124 testRowSetResults( 125 "SELECT * FROM \"dates\"", 126 new RowSetDateGetter( 2 ), 127 new EqualityDate[] { new EqualityDate( (short)1, (short)1, (short)2013 ), 128 new EqualityDate( (short)2, (short)2, (short)2012 ), 129 new EqualityDate( (short)3, (short)3, (short)2011 ) 130 }, 131 "simple select", "wrong dates" 132 ); 133 testRowSetResults( 134 "SELECT \"date\", \"ID\" FROM \"dates\" ORDER BY \"ID\" DESC", 135 new RowSetIntGetter( 2 ), 136 new Integer[] { 3, 2, 1 }, 137 "explicit column selection, sorted by IDs", "wrong IDs" 138 ); 139 testRowSetResults( 140 "SELECT * FROM \"dates\" ORDER BY \"date\"", 141 new RowSetIntGetter( 1 ), 142 new Integer[] { 3, 2, 1 }, 143 "sorted by date", "wrong IDs" 144 ); 145 } 146 147 /** 148 * ensures the basic functionality for selecting calendar functions from a CSV table - this is a prerequisite for 149 * later tests. 150 */ 151 @Test testCalendarFunctions()152 public void testCalendarFunctions() 153 { 154 // simple check for proper results of the calendar functions (DATE/MONTH) 155 // The * at the first position is crucial here - there was code which wrongly calculated 156 // column positions of function columns when * was present in the statement 157 testRowSetResults( 158 "SELECT \"dates\".*, YEAR( \"date\" ) FROM \"dates\"", 159 new RowSetIntGetter( 3 ), 160 new Integer[] { 2013, 2012, 2011 }, 161 "YEAR function", "wrong calculated years" 162 ); 163 testRowSetResults( 164 "SELECT \"dates\".*, MONTH( \"date\" ) FROM \"dates\"", 165 new RowSetIntGetter( 3 ), 166 new Integer[] { 1, 2, 3 }, 167 "MONTH function", "wrong calculated months" 168 ); 169 } 170 171 /** 172 * ensures that sorting by a function column works 173 */ 174 @Test testSortingByFunction()175 public void testSortingByFunction() 176 { 177 // most simple case: select a function, and sort by it 178 testRowSetResults( 179 "SELECT YEAR( \"date\" ) AS \"year\" FROM \"dates\" ORDER BY \"year\"", 180 new RowSetIntGetter(1), 181 new Integer[] { 2011, 2012, 2013 }, 182 "single YEAR selection, sorted by years", "wrong calculated years" 183 ); 184 // somewhat more "difficult" (this used to crash): Select all columns, plus a function, so the calculated 185 // column has a position greater than column count 186 testRowSetResults( 187 "SELECT \"dates\".*, YEAR( \"date\" ) AS \"year\" FROM \"dates\" ORDER BY \"year\" DESC", 188 new RowSetIntGetter(3), 189 new Integer[] { 2013, 2012, 2011 }, 190 "extended YEAR selection, sorted by years", "wrong calculated years" 191 ); 192 } 193 194 private interface RowSetValueGetter 195 { getValue( final RowSet i_rowSet )196 public Object getValue( final RowSet i_rowSet ) throws SQLException; 197 } 198 199 private abstract class RowSetColumnValueGetter implements RowSetValueGetter 200 { RowSetColumnValueGetter( final int i_columnIndex )201 RowSetColumnValueGetter( final int i_columnIndex ) 202 { 203 m_columnIndex = i_columnIndex; 204 } 205 206 protected final int m_columnIndex; 207 } 208 209 private class RowSetIntGetter extends RowSetColumnValueGetter 210 { RowSetIntGetter( final int i_columnIndex )211 RowSetIntGetter( final int i_columnIndex ) 212 { 213 super( i_columnIndex ); 214 } 215 getValue( final RowSet i_rowSet )216 public Object getValue( final RowSet i_rowSet ) throws SQLException 217 { 218 return i_rowSet.getInt( m_columnIndex ); 219 } 220 } 221 222 private class RowSetDateGetter extends RowSetColumnValueGetter 223 { RowSetDateGetter( final int i_columnIndex )224 RowSetDateGetter( final int i_columnIndex ) 225 { 226 super( i_columnIndex ); 227 } 228 getValue( final RowSet i_rowSet )229 public Object getValue( final RowSet i_rowSet ) throws SQLException 230 { 231 return i_rowSet.getDate( m_columnIndex ); 232 } 233 } 234 testRowSetResults( String i_command, RowSetValueGetter i_getter, T[] i_expectedValues, String i_context, String i_failureDesc )235 private <T> void testRowSetResults( String i_command, RowSetValueGetter i_getter, 236 T[] i_expectedValues, String i_context, String i_failureDesc ) 237 { 238 RowSet rowSet = null; 239 try 240 { 241 rowSet = m_database.createRowSet( CommandType.COMMAND, i_command ); 242 rowSet.execute(); 243 244 List< T > values = new ArrayList< T >(); 245 while ( rowSet.next() ) 246 { 247 values.add( (T)i_getter.getValue( rowSet ) ); 248 } 249 assertEquals( i_context + ": " + i_failureDesc, i_expectedValues, values.toArray() ); 250 } 251 catch( final SQLException e ) 252 { 253 fail( i_context + ": caught an exception: " + e.toString()); 254 } 255 finally 256 { 257 if ( rowSet != null ) 258 rowSet.dispose(); 259 } 260 } 261 262 private static final OfficeConnection connection = new OfficeConnection(); 263 private CsvDatabase m_database = null; 264 } 265