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 23 package integration.forms; 24 25 import com.sun.star.awt.XListBox; 26 import com.sun.star.beans.XPropertySet; 27 import com.sun.star.container.XChild; 28 import com.sun.star.container.XIndexAccess; 29 import com.sun.star.container.XNameAccess; 30 import com.sun.star.form.ListSourceType; 31 import com.sun.star.form.runtime.FormFeature; 32 import com.sun.star.form.runtime.XFormController; 33 import com.sun.star.form.runtime.XFormOperations; 34 import com.sun.star.lang.XMultiServiceFactory; 35 import com.sun.star.sdb.CommandType; 36 import com.sun.star.sdbc.SQLException; 37 import com.sun.star.sdbc.XParameters; 38 import com.sun.star.sdbc.XPreparedStatement; 39 import com.sun.star.uno.Exception; 40 import com.sun.star.uno.UnoRuntime; 41 import connectivity.tools.HsqlColumnDescriptor; 42 import connectivity.tools.HsqlDatabase; 43 import connectivity.tools.HsqlTableDescriptor; 44 import connectivity.tools.sdb.Connection; 45 import java.util.HashMap; 46 import java.util.Map; 47 import java.util.Vector; 48 49 import org.junit.After; 50 import org.junit.AfterClass; 51 import org.junit.Before; 52 import org.junit.BeforeClass; 53 import org.junit.Test; 54 import static org.junit.Assert.*; 55 import org.openoffice.test.OfficeConnection; 56 57 58 public class ListBox extends TestCase 59 { 60 private static final OfficeConnection officeConnection = new OfficeConnection(); 61 private XMultiServiceFactory m_orb = null; 62 HsqlDatabase m_database = null; 63 private final String m_foreignKeyTableName = "foreign_keys"; 64 65 @BeforeClass beforeClass()66 public static void beforeClass() throws java.lang.Exception 67 { 68 officeConnection.setUp(); 69 } 70 71 @AfterClass afterClass()72 public static void afterClass() throws java.lang.Exception 73 { 74 officeConnection.tearDown(); 75 } 76 ListBox()77 public ListBox() 78 { 79 super( DocumentType.WRITER ); 80 } 81 82 /* ------------------------------------------------------------------ */ 83 @Test checkForeignKeys()84 public void checkForeignKeys() throws com.sun.star.uno.Exception, java.lang.Exception 85 { 86 try 87 { 88 // create the form document 89 prepareDocument(); 90 91 final XIndexAccess formsCollection = UnoRuntime.queryInterface( XIndexAccess.class, 92 m_document.getFormComponentTreeRoot() ); 93 final XNameAccess form = UnoRuntime.queryInterface( XNameAccess.class, formsCollection.getByIndex(0) ); 94 95 final DocumentViewHelper view = m_document.getCurrentView(); 96 final XFormController formController = view.getFormController( form ); 97 final XFormOperations formOperations = formController.getFormOperations(); 98 99 // move through all records, and check that the display values in the list boxes are as expected 100 final String[][] fieldTypesDefinitions = impl_getFieldTypeDefinitions(); 101 final String[] fieldTypes = fieldTypesDefinitions[0]; 102 103 final String[] displayValues = impl_getDisplayValues(); 104 105 formOperations.execute( FormFeature.MoveToFirst ); 106 for ( int row=0; row<2; ++row ) 107 { 108 StringBuffer failedFieldTypes = new StringBuffer(); 109 for ( int i=0; i<fieldTypes.length; ++i ) 110 { 111 final String columnFKName = fieldTypes[i] + "_fk"; 112 Object listBoxModel = form.getByName( columnFKName ); 113 XListBox listBoxControl = UnoRuntime.queryInterface( XListBox.class, 114 view.getControl( listBoxModel ) ); 115 if ( !listBoxControl.getSelectedItem().equals( displayValues[row] ) ) 116 { 117 if ( failedFieldTypes.length() > 0 ) 118 failedFieldTypes.append( ", " ); 119 failedFieldTypes.append( fieldTypes[i] ); 120 } 121 } 122 /*assertTrue( "The following field types do not work when used as bound list box fields: " + failedFieldTypes.toString() + 123 " (row " + row + ")", failedFieldTypes.length() == 0 );*/ 124 125 formOperations.execute( FormFeature.MoveToNext ); 126 } 127 128 } 129 finally 130 { 131 closeDocument(); 132 } 133 } 134 135 /* ------------------------------------------------------------------ */ 136 @Before before()137 public void before() throws Exception, java.lang.Exception 138 { 139 m_orb = UnoRuntime.queryInterface(XMultiServiceFactory.class, officeConnection.getComponentContext().getServiceManager()); 140 impl_createDatabase(); 141 } 142 143 /* ------------------------------------------------------------------ */ prepareDocument()144 protected void prepareDocument() throws com.sun.star.uno.Exception, java.lang.Exception 145 { 146 super.prepareDocument(m_orb); 147 impl_createForm(); 148 } 149 150 /* ------------------------------------------------------------------ */ impl_getFieldTypeDefinitions()151 private String[][] impl_getFieldTypeDefinitions() 152 { 153 return new String[][] { 154 new String[] { 155 "bigint", "boolean", "date", "decimal", "double", "float", "numeric", "time", "timestamp", "tinyint", "varchar" 156 }, 157 new String[] { 158 null, null, null, "(10,2)", null, null, "(10,2)", null, null, null, "(50)" 159 } 160 }; 161 } 162 163 /* ------------------------------------------------------------------ */ impl_getTypedValue( final String _asType, final int _rowNum )164 private String[] impl_getTypedValue( final String _asType, final int _rowNum ) throws SQLException 165 { 166 Map< String, String[] > valueMap = new HashMap< String, String[] >(); 167 valueMap.put( "bigint", new String[] { "1111111111", "222222222" } ); 168 valueMap.put( "boolean", new String[] { "false", "true" } ); 169 valueMap.put( "date", new String[] { "2001-01-01", "2002-02-02" } ); 170 valueMap.put( "decimal", new String[] { "1.11", "2.22" } ); 171 valueMap.put( "double", new String[] { "1.11", "2.22" } ); 172 valueMap.put( "float", new String[] { "1.11", "2.22" } ); 173 valueMap.put( "numeric", new String[] { "1.11", "2.22" } ); 174 valueMap.put( "time", new String[] { "01:01:01", "02:02:02" } ); 175 valueMap.put( "timestamp", new String[] { "2001-01-01 01:01:01", "2002-02-02 02:02:02" } ); 176 valueMap.put( "tinyint", new String[] { "1", "2" } ); 177 valueMap.put( "varchar", new String[] { "first", "second" } ); 178 179 return valueMap.get( _asType ); 180 } 181 182 /* ------------------------------------------------------------------ */ impl_getDisplayValues()183 private String[] impl_getDisplayValues() 184 { 185 return new String[] { "one", "two" }; 186 } 187 188 /* ------------------------------------------------------------------ */ impl_createDatabase()189 private void impl_createDatabase() throws java.lang.Exception 190 { 191 try 192 { 193 m_database = new HsqlDatabase( m_orb ); 194 Connection connection = m_database.defaultConnection(); 195 System.out.println( m_database.getDocumentURL() ); 196 197 final String[][] fieldDefinitions = impl_getFieldTypeDefinitions(); 198 final String[] keyTypes = fieldDefinitions[0]; 199 final String[] keyCreationArgs = fieldDefinitions[1]; 200 201 Vector< HsqlColumnDescriptor > foreignKeyColumns = new Vector< HsqlColumnDescriptor >(); 202 foreignKeyColumns.add( new HsqlColumnDescriptor( "ID", "integer", HsqlColumnDescriptor.PRIMARY ) ); 203 204 Vector< String[] > foreignKeyValues = new Vector< String[] >(); 205 206 StringBuffer foreignKeyInsertSQL = new StringBuffer(); 207 foreignKeyInsertSQL.append( "INSERT INTO \"" + m_foreignKeyTableName + "\" VALUES (?" ); 208 209 final String[] displayValues = impl_getDisplayValues(); 210 211 for ( int i=0; i<keyTypes.length; ++i ) 212 { 213 final String tableName = keyTypes[i] + "_pk"; 214 final String columnPKName = keyTypes[i] + "_pk"; 215 final String columnFKName = keyTypes[i] + "_fk"; 216 final String columnType = keyTypes[i] + ( keyCreationArgs[i] != null ? keyCreationArgs[i] : "" ); 217 m_database.createTable( new HsqlTableDescriptor( tableName, 218 new HsqlColumnDescriptor[] { 219 new HsqlColumnDescriptor( columnPKName, columnType, HsqlColumnDescriptor.PRIMARY ), 220 new HsqlColumnDescriptor( "content", "varchar(50)" ) 221 } 222 ) ); 223 224 // insert a few rows 225 StringBuffer sql = new StringBuffer(); 226 sql.append( "INSERT INTO \"" ); 227 sql.append( tableName ); 228 sql.append( "\" VALUES (?, ?)"); 229 XPreparedStatement statement = connection.prepareStatement( sql.toString() ); 230 XParameters statementParameters = UnoRuntime.queryInterface( XParameters.class, statement ); 231 232 final String[] keyValues = impl_getTypedValue( keyTypes[i], 0 ); 233 234 for ( int row=0; row<displayValues.length; ++row ) 235 { 236 statementParameters.setString( 1, keyValues[row] ); 237 statementParameters.setString( 2, displayValues[row] ); 238 statement.execute(); 239 } 240 241 // remember a column descriptor for later creation of the table with the foreign keys 242 foreignKeyColumns.add( new HsqlColumnDescriptor( columnFKName, columnType, HsqlColumnDescriptor.REQUIRED, 243 tableName, columnPKName ) ); 244 245 // remember the data to fill into this table 246 foreignKeyValues.add( keyValues ); 247 foreignKeyInsertSQL.append( ", ?" ); 248 } 249 250 // create the table taking all those foreign keys 251 m_database.createTable( new HsqlTableDescriptor( m_foreignKeyTableName, foreignKeyColumns.toArray( new HsqlColumnDescriptor[0] ) ) ); 252 // fill in some data 253 foreignKeyInsertSQL.append( ")" ); 254 XPreparedStatement statement = connection.prepareStatement( foreignKeyInsertSQL.toString() ); 255 XParameters statementParameters = UnoRuntime.queryInterface( XParameters.class, statement ); 256 for ( int row=0; row<2; ++row ) 257 { 258 statementParameters.setInt( 1, row ); 259 for ( int i=0; i<keyTypes.length; ++i ) 260 { 261 statementParameters.setString( i+2, foreignKeyValues.get(i)[row] ); 262 } 263 statement.execute(); 264 } 265 266 m_database.defaultConnection().refreshTables(); 267 } 268 finally 269 { 270 if ( m_database != null ) 271 m_database.store(); 272 } 273 } 274 275 /* ------------------------------------------------------------------ */ impl_createForm()276 private void impl_createForm() throws java.lang.Exception 277 { 278 // a single control for the ID field 279 XPropertySet controlModel = m_formLayer.insertControlLine( "DatabaseNumericField", "ID", null, 10, 10, 6 ); 280 // bind the form to the foreign_keys table 281 XPropertySet form = dbfTools.queryPropertySet( dbfTools.getParent( controlModel, XChild.class ) ); 282 form.setPropertyValue( "Command", m_foreignKeyTableName ); 283 form.setPropertyValue( "CommandType", CommandType.TABLE ); 284 form.setPropertyValue( "DataSourceName", m_database.getDocumentURL() ); 285 286 // create list boxes for the different foreign keys 287 final String[][] fieldDefinitions = impl_getFieldTypeDefinitions(); 288 final String[] fieldTypes = fieldDefinitions[0]; 289 for ( int i=0; i<fieldTypes.length; ++i ) 290 { 291 final String tableName = fieldTypes[i] + "_pk"; 292 final String columnFKName = fieldTypes[i] + "_fk"; 293 final String columnPKName = fieldTypes[i] + "_pk"; 294 XPropertySet listBoxModel = m_formLayer.insertControlLine( "DatabaseListBox", columnFKName, null, 10, 20 + 10*i, 6 ); 295 listBoxModel.setPropertyValue( "Dropdown", new Boolean( true ) ); 296 listBoxModel.setPropertyValue( "ListSourceType", ListSourceType.SQL ); 297 listBoxModel.setPropertyValue( "ListSource", new String[] { "SELECT \"content\", \"" + columnPKName + 298 "\" FROM \"" + tableName + "\"" } ); 299 listBoxModel.setPropertyValue( "BoundColumn", new Short( (short)1 ) ); 300 } 301 302 m_document.getCurrentView().toggleFormDesignMode(); 303 } 304 } 305