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.util.Date;
29 import complexlib.ComplexTestCase;
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 public class FlatFileAccess extends ComplexTestCase
39 {
40     public FlatFileAccess()
41     {
42         super();
43     }
44 
45     @Override
46     public String[] getTestMethodNames()
47     {
48         return new String[] {
49             "testBasicAccess",
50             "testCalendarFunctions",
51             "testSortingByFunction"
52         };
53     }
54 
55     @Override
56     public String getTestObjectName()
57     {
58         return "FlatFileAccess";
59     }
60 
61     public void before() throws Exception
62     {
63         m_database = new CsvDatabase( (XMultiServiceFactory)param.getMSF() );
64 
65         // proper settings
66         final XPropertySet dataSourceSettings = m_database.getDataSource().geSettings();
67         dataSourceSettings.setPropertyValue( "Extension", "csv" );
68         dataSourceSettings.setPropertyValue( "HeaderLine", Boolean.TRUE );
69         dataSourceSettings.setPropertyValue( "FieldDelimiter", " " );
70         m_database.store();
71 
72         // write the table(s) for our test
73         final String tableLocation = m_database.getTableFileLocation().getAbsolutePath();
74         final PrintWriter tableWriter = new PrintWriter( new FileOutputStream( tableLocation + File.separatorChar + "dates.csv", false ) );
75         tableWriter.println( "ID date" );
76         tableWriter.println( "1 2013-01-01" );
77         tableWriter.println( "2 2012-02-02" );
78         tableWriter.println( "3 2011-03-03" );
79         tableWriter.close();
80     }
81 
82     public void after()
83     {
84     }
85 
86     private class EqualityDate extends Date
87     {
88         EqualityDate( short i_day, short i_month, short i_year )
89         {
90             super( i_day, i_month, i_year );
91         }
92 
93         EqualityDate( Date i_date )
94         {
95             super( i_date.Day, i_date.Month, i_date.Year );
96         }
97 
98         @Override
99         public boolean equals( Object i_compare )
100         {
101             if ( !( i_compare instanceof Date ) )
102                 return false;
103             return  Day   == ((Date)i_compare).Day
104                 &&  Month == ((Date)i_compare).Month
105                 &&  Year  == ((Date)i_compare).Year;
106         }
107     }
108 
109     /**
110      * ensures simple SELECTs from our table(s) work, and deliver the expected results
111      */
112     public void testBasicAccess()
113     {
114         testRowSetResults(
115             "SELECT * FROM \"dates\"",
116             new RowSetIntGetter(1),
117             new Integer[] { 1, 2, 3 },
118             "simple select", "wrong IDs"
119         );
120 
121         testRowSetResults(
122             "SELECT * FROM \"dates\"",
123             new RowSetDateGetter( 2 ),
124             new EqualityDate[] { new EqualityDate( (short)1, (short)1, (short)2013 ),
125                                  new EqualityDate( (short)2, (short)2, (short)2012 ),
126                                  new EqualityDate( (short)3, (short)3, (short)2011 )
127             },
128             "simple select", "wrong dates"
129         );
130         testRowSetResults(
131             "SELECT \"date\", \"ID\" FROM \"dates\" ORDER BY \"ID\" DESC",
132             new RowSetIntGetter( 2 ),
133             new Integer[] { 3, 2, 1 },
134             "explicit column selection, sorted by IDs", "wrong IDs"
135         );
136         testRowSetResults(
137             "SELECT * FROM \"dates\" ORDER BY \"date\"",
138             new RowSetIntGetter( 1 ),
139             new Integer[] { 3, 2, 1 },
140             "sorted by date", "wrong IDs"
141         );
142     }
143 
144     /**
145      * ensures the basic functionality for selecting calendar functions from a CSV table - this is a prerequisite for
146      * later tests.
147      */
148     public void testCalendarFunctions()
149     {
150         // simple check for proper results of the calendar functions (DATE/MONTH)
151         // The * at the first position is crucial here - there was code which wrongly calculated
152         // column positions of function columns when * was present in the statement
153         testRowSetResults(
154             "SELECT \"dates\".*, YEAR( \"date\" ) FROM \"dates\"",
155             new RowSetIntGetter( 3 ),
156             new Integer[] { 2013, 2012, 2011 },
157             "YEAR function", "wrong calculated years"
158         );
159         testRowSetResults(
160             "SELECT \"dates\".*, MONTH( \"date\" ) FROM \"dates\"",
161             new RowSetIntGetter( 3 ),
162             new Integer[] { 1, 2, 3 },
163             "MONTH function", "wrong calculated months"
164         );
165     }
166 
167     /**
168      * ensures that sorting by a function column works
169      */
170     public void testSortingByFunction()
171     {
172         // most simple case: select a function, and sort by it
173         testRowSetResults(
174             "SELECT YEAR( \"date\" ) AS \"year\" FROM \"dates\" ORDER BY \"year\"",
175             new RowSetIntGetter(1),
176             new Integer[] { 2011, 2012, 2013 },
177             "single YEAR selection, sorted by years", "wrong calculated years"
178         );
179         // somewhat more "difficult" (this used to crash): Select all columns, plus a function, so the calculated
180         // column has a position greater than column count
181         testRowSetResults(
182             "SELECT \"dates\".*, YEAR( \"date\" ) AS \"year\" FROM \"dates\" ORDER BY \"year\" DESC",
183             new RowSetIntGetter(3),
184             new Integer[] { 2013, 2012, 2011 },
185             "extended YEAR selection, sorted by years", "wrong calculated years"
186         );
187     }
188 
189     private interface RowSetValueGetter
190     {
191         public Object getValue( final RowSet i_rowSet ) throws SQLException;
192     }
193 
194     private abstract class RowSetColumnValueGetter implements RowSetValueGetter
195     {
196         RowSetColumnValueGetter( final int i_columnIndex )
197         {
198             m_columnIndex = i_columnIndex;
199         }
200 
201         protected final int m_columnIndex;
202     }
203 
204     private class RowSetIntGetter extends RowSetColumnValueGetter
205     {
206         RowSetIntGetter( final int i_columnIndex )
207         {
208             super( i_columnIndex );
209         }
210 
211         public Object getValue( final RowSet i_rowSet ) throws SQLException
212         {
213             return i_rowSet.getInt( m_columnIndex );
214         }
215     }
216 
217     private class RowSetDateGetter extends RowSetColumnValueGetter
218     {
219         RowSetDateGetter( final int i_columnIndex )
220         {
221             super( i_columnIndex );
222         }
223 
224         public Object getValue( final RowSet i_rowSet ) throws SQLException
225         {
226             return i_rowSet.getDate( m_columnIndex );
227         }
228     }
229 
230     private <T> void testRowSetResults( String i_command, RowSetValueGetter i_getter,
231         T[] i_expectedValues, String i_context, String i_failureDesc )
232     {
233         RowSet rowSet = null;
234         try
235         {
236             rowSet = m_database.createRowSet( CommandType.COMMAND, i_command );
237             rowSet.execute();
238 
239             List< T > values = new ArrayList< T >();
240             while ( rowSet.next() )
241             {
242                 values.add( (T)i_getter.getValue( rowSet ) );
243             }
244             assureEquals( i_context + ": " + i_failureDesc, i_expectedValues, values.toArray(), true );
245         }
246         catch( final SQLException e )
247         {
248             failed( i_context + ": caught an exception: " + e.toString(), false );
249         }
250         finally
251         {
252             if ( rowSet != null )
253                 rowSet.dispose();
254         }
255     }
256 
257     private CsvDatabase m_database = null;
258 }
259