|
From: <wi...@us...> - 2011-03-26 12:52:15
|
Revision: 6207
http://squirrel-sql.svn.sourceforge.net/squirrel-sql/?rev=6207&view=rev
Author: wis775
Date: 2011-03-26 12:52:09 +0000 (Sat, 26 Mar 2011)
Log Message:
-----------
3238037: It's impossible to create a foreign key in oracle 10g, if some tables contains a / in their name eg. recycle bins
For some names of BIN$ objects, Oracle's jdbc driver could not fetch the columns.
Calling DatabaseMetaData#getColumns for a table like BIN$nPl/2NHWRNXgQKjAQgFYEQ==$0 will end in a
ORA-01424: missing or illegal character following the escape character.
We have to escape the / by //
This is tested with driver version 10.2.0.5.0 an 11.2.0.2.0
Modified Paths:
--------------
trunk/sql12/doc/src/main/resources/changes.txt
trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaData.java
Added Paths:
-----------
trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaDataOracleSpecificTest.java
Modified: trunk/sql12/doc/src/main/resources/changes.txt
===================================================================
--- trunk/sql12/doc/src/main/resources/changes.txt 2011-03-24 21:51:09 UTC (rev 6206)
+++ trunk/sql12/doc/src/main/resources/changes.txt 2011-03-26 12:52:09 UTC (rev 6207)
@@ -29,6 +29,8 @@
Bug-fixes:
+3238037: It's impossible to create a foreign key in oracle 10g, if some tables contains a / in their name eg. recycle bins
+
Now, "Press to open logs" shows the current log file by default instead of a random one.
3222350: Cannot enter negative values for numeric fields when editing the result table.
Modified: trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaData.java
===================================================================
--- trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaData.java 2011-03-24 21:51:09 UTC (rev 6206)
+++ trunk/sql12/fw/src/main/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaData.java 2011-03-26 12:52:09 UTC (rev 6207)
@@ -33,6 +33,7 @@
import java.util.TreeMap;
import java.util.TreeSet;
+
import net.sourceforge.squirrel_sql.fw.datasetviewer.DataSetException;
import net.sourceforge.squirrel_sql.fw.datasetviewer.DatabaseTypesDataSet;
import net.sourceforge.squirrel_sql.fw.datasetviewer.IDataSet;
@@ -1689,7 +1690,7 @@
private ResultSet getColumns(ITableInfo ti) throws SQLException
{
- return privateGetJDBCMetaData().getColumns(ti.getCatalogName(), ti.getSchemaName(), ti.getSimpleName(),
+ return privateGetJDBCMetaData().getColumns(ti.getCatalogName(), ti.getSchemaName(), escapeTableNames(ti.getSimpleName()),
"%");
}
@@ -1730,6 +1731,9 @@
throws SQLException
{
ResultSet rs = null;
+
+ table = escapeTableNames(table);
+
try
{
final Map<Integer, TableColumnInfo> columns = new TreeMap<Integer, TableColumnInfo>();
@@ -1798,6 +1802,27 @@
}
/**
+ * For some databases, we need to escape table names.
+ * e.g. for Oracle, we need to escape the slash in BIN$ objects, because the JDBC-driver could not handle them until now (version 11.2.0.2.0)
+ * @param table name of the table, which might contains some chars to escape
+ * @return the escaped table name, or the original, if escaping is not necessary.
+ */
+ private String escapeTableNames(String table) {
+ if(DialectFactory.isOracle(this) && table != null){
+ /*
+ * For some names of BIN$ objects, the jdbc driver could not catch the columns.
+ * Calling DatabaseMetaData#getColumns for a table like BIN$nPl/2NHWRNXgQKjAQgFYEQ==$0 will end in a
+ * ORA-01424: missing or illegal character following the escape character.
+ *
+ */
+ if(table.startsWith("BIN$")){
+ table = table.replaceAll("/", "//");
+ }
+ }
+ return table;
+ }
+
+ /**
* @see net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData#getColumnInfo(net.sourceforge.squirrel_sql.fw.sql.ITableInfo)
*/
public synchronized TableColumnInfo[] getColumnInfo(ITableInfo ti) throws SQLException
Added: trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaDataOracleSpecificTest.java
===================================================================
--- trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaDataOracleSpecificTest.java (rev 0)
+++ trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaDataOracleSpecificTest.java 2011-03-26 12:52:09 UTC (rev 6207)
@@ -0,0 +1,116 @@
+package net.sourceforge.squirrel_sql.fw.sql;
+
+/*
+ * Copyright (C) 2011 Stefan Willinger
+ * wi...@us...
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 2.1 of the License, or (at your option) any later version.
+ *
+ * This library is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public
+ * License along with this library; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+ */
+import static org.easymock.EasyMock.expect;
+import static org.junit.Assert.assertEquals;
+
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+
+import net.sourceforge.squirrel_sql.BaseSQuirreLJUnit4TestCase;
+
+import org.junit.Before;
+import org.junit.Test;
+
+import utils.EasyMockHelper;
+
+/**
+ * Some Oracle specific tests for {@link SQLDatabaseMetaData}.
+ * @author Stefan Willinger
+ *
+ */
+public class SQLDatabaseMetaDataOracleSpecificTest extends BaseSQuirreLJUnit4TestCase {
+
+ private SQLDatabaseMetaData classUnderTest = null;
+ private EasyMockHelper mockHelper = new EasyMockHelper();
+
+ /* Mock Objects */
+ private Connection mockConnection = mockHelper.createMock(Connection.class);
+ private ISQLConnection mockSqlConnection = mockHelper.createMock(ISQLConnection.class);
+ private DatabaseMetaData mockDatabaseMetaData = mockHelper.createMock(DatabaseMetaData.class);
+
+ @Before
+ public void setUp() throws Exception {
+
+ expect(mockDatabaseMetaData.getDatabaseProductName()).andStubReturn("Oracle");
+ expect(mockDatabaseMetaData.getDatabaseProductVersion()).andStubReturn("10.2.0.2.0");
+ expect(mockDatabaseMetaData.supportsSchemasInIndexDefinitions()).andStubReturn(true);
+ expect(mockDatabaseMetaData.supportsSchemasInDataManipulation()).andStubReturn(true);
+ expect(mockDatabaseMetaData.supportsCatalogsInDataManipulation()).andStubReturn(true);
+ expect(mockDatabaseMetaData.supportsSchemasInTableDefinitions()).andStubReturn(true);
+ expect(mockDatabaseMetaData.getCatalogSeparator()).andStubReturn(".");
+ expect(mockDatabaseMetaData.getIdentifierQuoteString()).andStubReturn("\"");
+
+ expect(mockConnection.getMetaData()).andStubReturn(mockDatabaseMetaData);
+ expect(mockSqlConnection.getConnection()).andStubReturn(mockConnection);
+
+ }
+
+ /**
+ * Ensures, that table names of recycle bins are escaped before calling {@link DatabaseMetaData#getColumns(String, String, String, String)}
+ * For some names of BIN$ objects, the jdbc driver could not catch the columns.
+ * Calling getColumns for a table like BIN$nPl/2NHWRNXgQKjAQgFYEQ==$0 will end in a exception without escaping
+ * ORA-01424: missing or illegal character following the escape character.
+ *
+ * This test ensures, that the table name is escaped for a Oracle Database
+ *
+ */
+ @Test
+ public void testGetColumnsForOracleRecycleBin() throws SQLException {
+
+ final String tableNameExpected = "BIN$nPl//2NHWRNXgQKjAQgFYEQ==$0";
+ final String tableName = "BIN$nPl/2NHWRNXgQKjAQgFYEQ==$0";
+
+ // Build an empty ResultSet
+ ResultSet columnResultSet = buildTableMetaDataResultSet();
+
+ // Ensure, that getColumns() was called with the escaped table name.
+ expect(mockDatabaseMetaData.getColumns(null, "mySchema", tableNameExpected, "%")).andReturn(columnResultSet);
+ mockHelper.replayAll();
+ classUnderTest = new SQLDatabaseMetaData(mockSqlConnection);
+
+ // Check to be sure we get only one schema
+ TableColumnInfo[] columnInfo = classUnderTest.getColumnInfo(null, "mySchema", tableName);
+ // we expect an empty table info because this test ensures escaping the table name.
+ assertEquals(0, columnInfo.length);
+
+ mockHelper.verifyAll();
+ }
+
+
+ /**
+ * The ResultSet is empty.
+ */
+ private ResultSet buildTableMetaDataResultSet() throws SQLException {
+ ResultSetMetaData rsmd = mockHelper.createMock(ResultSetMetaData.class);
+
+ ResultSet rs = mockHelper.createMock(ResultSet.class);
+ expect(rs.getMetaData()).andStubReturn(rsmd);
+
+ expect(rs.next()).andReturn(false);
+ rs.close();
+ return rs;
+
+ }
+
+}
Property changes on: trunk/sql12/fw/src/test/java/net/sourceforge/squirrel_sql/fw/sql/SQLDatabaseMetaDataOracleSpecificTest.java
___________________________________________________________________
Added: svn:mime-type
+ text/plain
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
