1   /*
2    *
3    * The DbUnit Database Testing Framework
4    * Copyright (C)2002-2004, DbUnit.org
5    *
6    * This library is free software; you can redistribute it and/or
7    * modify it under the terms of the GNU Lesser General Public
8    * License as published by the Free Software Foundation; either
9    * version 2.1 of the License, or (at your option) any later version.
10   *
11   * This library is distributed in the hope that it will be useful,
12   * but WITHOUT ANY WARRANTY; without even the implied warranty of
13   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
14   * Lesser General Public License for more details.
15   *
16   * You should have received a copy of the GNU Lesser General Public
17   * License along with this library; if not, write to the Free Software
18   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
19   *
20   */
21  
22  package org.dbunit.database;
23  
24  import java.io.File;
25  import java.sql.Connection;
26  import java.sql.DatabaseMetaData;
27  import java.util.ArrayList;
28  import java.util.List;
29  import java.util.Locale;
30  
31  import org.dbunit.AbstractDatabaseIT;
32  import org.dbunit.DatabaseEnvironment;
33  import org.dbunit.HypersonicEnvironment;
34  import org.dbunit.TestFeature;
35  import org.dbunit.dataset.Column;
36  import org.dbunit.dataset.Columns;
37  import org.dbunit.dataset.IDataSet;
38  import org.dbunit.dataset.ITable;
39  import org.dbunit.dataset.ITableMetaData;
40  import org.dbunit.dataset.NoSuchTableException;
41  import org.dbunit.dataset.datatype.DataType;
42  import org.dbunit.dataset.datatype.DataTypeException;
43  import org.dbunit.dataset.datatype.DefaultDataTypeFactory;
44  import org.dbunit.dataset.datatype.IDataTypeFactory;
45  import org.dbunit.testutil.TestUtils;
46  
47  /**
48   * @author Manuel Laflamme
49   * @version $Revision: 1162 $
50   * @since Mar 14, 2002
51   */
52  public class DatabaseTableMetaDataIT extends AbstractDatabaseIT
53  {
54      
55      public static final String TEST_TABLE = "TEST_TABLE";
56      
57      public DatabaseTableMetaDataIT(String s)
58      {
59          super(s);
60      }
61  
62      protected IDataSet createDataSet() throws Exception
63      {
64          return _connection.createDataSet();
65      }
66  
67      protected String convertString(String str) throws Exception
68      {
69          return DatabaseEnvironment.getInstance().convertString(str);
70      }
71  
72      public void testGetPrimaryKeys() throws Exception
73      {
74          String tableName = "PK_TABLE";
75  //        String[] expected = {"PK0"};
76          String[] expected = {"PK0", "PK1", "PK2"};
77  
78          ITableMetaData metaData = createDataSet().getTableMetaData(tableName);
79          Column[] columns = metaData.getPrimaryKeys();
80  
81          assertEquals("pk count", expected.length, columns.length);
82          for (int i = 0; i < columns.length; i++)
83          {
84              Column column = columns[i];
85              assertEquals("name", convertString(expected[i]), column.getColumnName());
86          }
87      }
88  
89      public void testGetNoPrimaryKeys() throws Exception
90      {
91          String tableName = TEST_TABLE;
92  
93          ITableMetaData metaData = createDataSet().getTableMetaData(tableName);
94          Column[] columns = metaData.getPrimaryKeys();
95  
96          assertEquals("pk count", 0, columns.length);
97      }
98  
99      
100     
101     
102     public void testCreation_UnknownTable() throws Exception
103     {
104         String tableName = "UNKNOWN_TABLE";
105         IDatabaseConnection connection = getConnection();
106         String schema = connection.getSchema();
107         try
108         {
109         	new DatabaseTableMetaData(tableName, getConnection());
110         	fail("Should not be able to create a DatabaseTableMetaData for an unknown table");
111         }
112         catch (NoSuchTableException expected)
113         {
114         	String msg = "Did not find table '" + convertString("UNKNOWN_TABLE") + "' in schema '" + schema + "'";
115         	assertEquals(msg, expected.getMessage());
116         }
117     }
118 
119     public void testGetNoColumns() throws Exception
120     {
121     	// Since the "unknown_table" does not exist it also does not have any columns
122         String tableName = "UNKNOWN_TABLE";
123         boolean validate = false;
124 
125         ITableMetaData metaData = new DatabaseTableMetaData(tableName,
126                 getConnection(), validate);
127         
128         Column[] columns = metaData.getColumns();
129         assertEquals(0, columns.length);
130     }
131 
132     public void testColumnIsNullable() throws Exception
133     {
134         String tableName = "PK_TABLE";
135         String[] notNullable = {"PK0", "PK1", "PK2"};
136         String[] nullable = {"NORMAL0", "NORMAL1"};
137 
138         ITableMetaData metaData = createDataSet().getTableMetaData(tableName);
139         Column[] columns = metaData.getColumns();
140 
141         assertEquals("column count", nullable.length + notNullable.length,
142                 columns.length);
143 
144         // not nullable
145         for (int i = 0; i < notNullable.length; i++)
146         {
147             Column column = Columns.getColumn(notNullable[i], columns);
148             assertEquals(notNullable[i], Column.NO_NULLS, column.getNullable());
149         }
150 
151         // nullable
152         for (int i = 0; i < nullable.length; i++)
153         {
154             Column column = Columns.getColumn(nullable[i], columns);
155             assertEquals(nullable[i], Column.NULLABLE, column.getNullable());
156         }
157     }
158 
159     public void testUnsupportedColumnDataType() throws Exception
160     {
161         IDataTypeFactory dataTypeFactory = new DefaultDataTypeFactory() {
162 			public DataType createDataType(int sqlType, String sqlTypeName,
163 					String tableName, String columnName)
164 					throws DataTypeException {
165 				return DataType.UNKNOWN;
166 			}
167         };
168         this._connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, dataTypeFactory);
169     	
170         String tableName = "EMPTY_MULTITYPE_TABLE";
171         ITableMetaData metaData = createDataSet().getTableMetaData(tableName);
172         Column[] columns = metaData.getColumns();
173         // No columns recognized -> should not provide any columns here
174         assertEquals("Should be an empty column array", 0, columns.length);
175     }
176     
177     public void testColumnDataType() throws Exception
178     {
179     	String tableName = "EMPTY_MULTITYPE_TABLE";
180 
181         List expectedNames = new ArrayList();
182         expectedNames.add("VARCHAR_COL");
183         expectedNames.add("NUMERIC_COL");
184         expectedNames.add("TIMESTAMP_COL");
185 
186         List expectedTypes = new ArrayList();
187         expectedTypes.add(DataType.VARCHAR);
188         expectedTypes.add(DataType.NUMERIC);
189         expectedTypes.add(DataType.TIMESTAMP);
190 
191     	DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
192         if (environment.support(TestFeature.VARBINARY)) {
193             expectedNames.add("VARBINARY_COL");
194             expectedTypes.add(DataType.VARBINARY);
195         }
196 
197         // Check correct setup
198         assertEquals("expected columns", expectedNames.size(), expectedTypes.size());
199 
200         ITableMetaData metaData = createDataSet().getTableMetaData(tableName);
201         Column[] columns = metaData.getColumns();
202 
203         assertEquals("column count", 4, columns.length);
204 
205         for (int i = 0; i < expectedNames.size(); i++)
206         {
207             Column column = columns[i];
208             assertEquals("name", convertString((String)expectedNames.get(i)), column.getColumnName());
209             if (expectedTypes.get(i).equals(DataType.NUMERIC))
210             {
211                 // 2009-10-10 TODO John Hurst: hack for Oracle, returns java.sql.Types.DECIMAL for this column
212                 assertTrue("Expected numeric datatype, got [" + column.getDataType() + "]",
213                         column.getDataType().equals(DataType.NUMERIC) ||
214                         column.getDataType().equals(DataType.DECIMAL)
215                 );
216             }
217             else if (expectedTypes.get(i).equals(DataType.TIMESTAMP) && column.getDataType().equals(DataType.DATE))
218             {
219                 // 2009-10-22 TODO John Hurst: hack for Postgresql, returns DATE for TIMESTAMP.
220                 // Need to move DataType comparison to DatabaseEnvironment.
221                 assertTrue(true);
222             }
223             else if (expectedTypes.get(i).equals(DataType.VARBINARY) && column.getDataType().equals(DataType.VARCHAR))
224             {
225                 // 2009-10-22 TODO John Hurst: hack for Postgresql, returns VARCHAR for VARBINARY.
226                 // Need to move DataType comparison to DatabaseEnvironment.
227                 assertTrue(true);
228             }
229             else
230             {
231                 assertEquals("datatype", (DataType)expectedTypes.get(i), column.getDataType());
232             }
233         }
234     }
235  
236     /**
237      * Tests whether dbunit works correctly when the local machine has a specific locale set while having
238      * case sensitivity=false (so that the "toUpperCase()" is internally invoked on table names)
239      * @throws Exception
240      */
241     public void testCaseInsensitiveAndI18n() throws Exception
242     {
243         // To test bug report #1537894 where the user has a turkish locale set on his box
244         
245         // Change the locale for this test
246         Locale oldLocale = Locale.getDefault();
247         // Set the locale to turkish where "i".toUpperCase() produces an "\u0131" ("I" with dot above) which is not equal to "I". 
248         Locale.setDefault(new Locale("tr", "TR"));
249         
250         try {
251             // Use the "EMPTY_MULTITYPE_TABLE" because it has an "I" in the name.
252             // Use as input a completely lower-case string so that the internal "toUpperCase()" has effect
253             // 2009-11-06 TODO John Hurst: not working in original form with MySQL.
254             // Is it because "internal toUpperCase() mentioned above is actually not being called?
255             // Investigate further.
256 //            String tableName = "empty_multitype_table";
257             String tableName = "EMPTY_MULTITYPE_TABLE";
258 
259             IDataSet dataSet = this._connection.createDataSet();
260             ITable table = dataSet.getTable(tableName);
261             // Should now find the table, regardless that we gave the tableName in lowerCase
262             assertNotNull("Table '" + tableName + "' was not found", table);
263         }
264         finally {
265             //Reset locale
266             Locale.setDefault(oldLocale);
267         }
268     }
269 
270 
271     /**
272      * Tests the pattern-like column retrieval from the database. DbUnit
273      * should not interpret any table names as regex patterns. 
274      * @throws Exception
275      */
276     public void testGetColumnsForTablesMatchingSamePattern() throws Exception
277     {
278         Connection jdbcConnection = HypersonicEnvironment.createJdbcConnection("tempdb");
279         HypersonicEnvironment.executeDdlFile(TestUtils.getFile("sql/hypersonic_dataset_pattern_test.sql"),
280                 jdbcConnection);
281         IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
282 
283         try {
284             String tableName = "PATTERN_LIKE_TABLE_X_";
285             String[] columnNames = {"VARCHAR_COL_XUNDERSCORE"};
286     
287             ITableMetaData metaData = connection.createDataSet().getTableMetaData(tableName);
288             Column[] columns = metaData.getColumns();
289     
290             assertEquals("column count", columnNames.length, columns.length);
291     
292             for (int i = 0; i < columnNames.length; i++)
293             {
294                 Column column = Columns.getColumn(columnNames[i], columns);
295                 assertEquals(columnNames[i], columnNames[i], column.getColumnName());
296             }
297         }
298         finally {
299             HypersonicEnvironment.shutdown(jdbcConnection);
300             jdbcConnection.close();
301             HypersonicEnvironment.deleteFiles("tempdb");
302         }
303     }
304 
305     public void testCaseSensitive() throws Exception
306     {
307         Connection jdbcConnection = HypersonicEnvironment.createJdbcConnection("tempdb");
308         HypersonicEnvironment.executeDdlFile(TestUtils.getFile("sql/hypersonic_case_sensitive_test.sql"),
309                 jdbcConnection);
310         IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
311 
312         try {
313             String tableName = "MixedCaseTable";
314             String tableNameWrongCase = "MIXEDCASETABLE";
315             boolean validate = true;
316             boolean caseSensitive = true;
317 
318             ITableMetaData metaData = new DatabaseTableMetaData(tableName,
319                     connection, validate, caseSensitive);
320             Column[] columns = metaData.getColumns();
321             assertEquals(1, columns.length);
322             assertEquals("COL1", columns[0].getColumnName());
323             
324             // Now test with same table name but wrong case
325             try {
326                 ITableMetaData metaDataWrongCase = new DatabaseTableMetaData(tableNameWrongCase,
327                         connection, validate, caseSensitive);
328                 fail("Should not be able to create DatabaseTableMetaData with non-existing table name " + tableNameWrongCase + 
329                         ". Created "+ metaDataWrongCase);
330             }
331             catch(NoSuchTableException expected){
332                 assertTrue(expected.getMessage().indexOf(tableNameWrongCase) != -1);
333             }
334         }
335         finally {
336             HypersonicEnvironment.shutdown(jdbcConnection);
337             jdbcConnection.close();
338             HypersonicEnvironment.deleteFiles("tempdb");
339         }
340     }
341     
342     /**
343      * Ensure that the same table name is returned by {@link DatabaseTableMetaData#getTableName()}
344      * as the specified by the input parameter.
345      * @throws Exception
346      */
347     public void testFullyQualifiedTableName() throws Exception
348     {
349         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
350         String schema = environment.getProfile().getSchema();
351         
352         assertNotNull("Precondition: db environment 'schema' must not be null", schema);
353 //        Connection jdbcConn = _connection.getConnection();
354 //        String schema = SQLHelper.getSchema(jdbcConn);
355         DatabaseTableMetaData metaData = new DatabaseTableMetaData(schema + "." + TEST_TABLE, _connection);
356         assertEquals(schema + "." + convertString(TEST_TABLE), metaData.getTableName());
357     }
358     
359     public void testDbStoresUpperCaseTableNames() throws Exception
360     {
361         IDatabaseConnection connection = getConnection();
362         DatabaseMetaData metaData = connection.getConnection().getMetaData();
363         if(metaData.storesUpperCaseIdentifiers())
364         {
365             DatabaseTableMetaData dbTableMetaData = new DatabaseTableMetaData(TEST_TABLE.toLowerCase(Locale.ENGLISH), _connection);
366             // Table name should have been "toUpperCase'd"
367             assertEquals(TEST_TABLE.toUpperCase(Locale.ENGLISH), dbTableMetaData.getTableName());
368         }
369         else
370         {
371             // skip the test
372             assertTrue(true);
373         }
374     }
375 
376     public void testDbStoresLowerCaseTableNames() throws Exception
377     {
378         IDatabaseConnection connection = getConnection();
379         DatabaseMetaData metaData = connection.getConnection().getMetaData();
380         if(metaData.storesLowerCaseIdentifiers())
381         {
382             DatabaseTableMetaData dbTableMetaData = new DatabaseTableMetaData(TEST_TABLE.toUpperCase(Locale.ENGLISH), _connection);
383             // Table name should have been "toUpperCase'd"
384             assertEquals(TEST_TABLE.toLowerCase(Locale.ENGLISH), dbTableMetaData.getTableName());
385         }
386         else
387         {
388             // skip the test
389             assertTrue(true);
390         }
391     }
392 }
393 
394 
395 
396 
397 
398 
399