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