diff options
author | kmoore | 2011-02-05 17:01:41 +0000 |
---|---|---|
committer | kmoore | 2011-02-05 17:01:41 +0000 |
commit | c7fdcd4adaef670a00a425aa1129b5d8ee93a666 (patch) | |
tree | c6dfe26adc7a69bd00c773e9fb130b3084a8341c /jpa/tests | |
parent | cefe08946d9fe60e80485220b39722931925e632 (diff) | |
download | webtools.dali-c7fdcd4adaef670a00a425aa1129b5d8ee93a666.tar.gz webtools.dali-c7fdcd4adaef670a00a425aa1129b5d8ee93a666.tar.xz webtools.dali-c7fdcd4adaef670a00a425aa1129b5d8ee93a666.zip |
forgot to rename the jpt.db package names along with the project names
Diffstat (limited to 'jpa/tests')
15 files changed, 3850 insertions, 3 deletions
diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/META-INF/MANIFEST.MF b/jpa/tests/org.eclipse.jpt.jpa.db.tests/META-INF/MANIFEST.MF index b68821260c..f5638002b3 100644 --- a/jpa/tests/org.eclipse.jpt.jpa.db.tests/META-INF/MANIFEST.MF +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/META-INF/MANIFEST.MF @@ -4,7 +4,7 @@ Bundle-Name: %pluginName Bundle-Vendor: %providerName Bundle-SymbolicName: org.eclipse.jpt.jpa.db.tests Bundle-Version: 1.0.200.qualifier -Bundle-Activator: org.eclipse.jpt.db.tests.internal.JptDbTestsPlugin +Bundle-Activator: org.eclipse.jpt.jpa.db.tests.internal.JptJpaDbTestsPlugin Bundle-ActivationPolicy: lazy Bundle-Localization: plugin Bundle-RequiredExecutionEnvironment: J2SE-1.5 @@ -15,5 +15,5 @@ Require-Bundle: org.eclipse.core.runtime;bundle-version="[3.4.0,4.0.0)", org.eclipse.jpt.common.utility;bundle-version="[1.2.0,2.0.0)", org.eclipse.jpt.jpa.db;bundle-version="[1.2.0,2.0.0)", org.junit;bundle-version="3.8.0" -Export-Package: org.eclipse.jpt.db.tests.internal;x-internal:=true, - org.eclipse.jpt.db.tests.internal.platforms;x-internal:=true +Export-Package: org.eclipse.jpt.jpa.db.tests.internal;x-internal:=true, + org.eclipse.jpt.jpa.db.tests.internal.platforms;x-internal:=true diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JDBCTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JDBCTests.java new file mode 100644 index 0000000000..9d2efa4996 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JDBCTests.java @@ -0,0 +1,135 @@ +/******************************************************************************* + * Copyright (c) 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal; + +import java.sql.Connection; +import java.sql.DriverManager; + +import junit.framework.TestCase; + +/** + * These aren't tests. They are just an easy way to dump JDBC metadata to the + * console. + */ +@SuppressWarnings("nls") +public class JDBCTests extends TestCase { + + public JDBCTests(String name) { + super(name); + } + + public void testDerby() throws Exception { + this.dumpMetaData(DERBY); + } + + public void testMySQL() throws Exception { + this.dumpMetaData(MYSQL); + } + + public void testOracle() throws Exception { + this.dumpMetaData(ORACLE); + } + + public void testPostgreSQL() throws Exception { + this.dumpMetaData(POSTGRESQL); + } + + public void testSybase() throws Exception { + this.dumpMetaData(SYBASE); + } + + protected void dumpMetaData(ConnectionConfig config) throws Exception { + System.out.println("***** PLATFORM: " + config.platformName + " *****"); + System.out.println(); + Class.forName(config.driverClassName); + Connection connection = DriverManager.getConnection(this.buildURL(config), config.user, config.password); + System.out.println("CATALOGS:"); + JDBCTools.dump(connection.getMetaData().getCatalogs()); + System.out.println(); + System.out.println("SCHEMATA:"); + JDBCTools.dump(connection.getMetaData().getSchemas()); + connection.close(); + System.out.println(); + } + + protected String buildURL(ConnectionConfig config) { + return "jdbc:" + config.databaseURL; + } + + protected static final ConnectionConfig DERBY = + new ConnectionConfig( + "Derby", + "org.apache.derby.jdbc.EmbeddedDriver", + "derby:C:/derby/data/test", + null, + null + ); + + protected static final ConnectionConfig MYSQL = + new ConnectionConfig( + "MySQL", + "com.mysql.jdbc.Driver", + "mysql://localhost:3306", + "root", + "oracle" + ); + + protected static final ConnectionConfig ORACLE = + new ConnectionConfig( + "Oracle", + "oracle.jdbc.OracleDriver", + "oracle:thin:@localhost:1521:orcl", + "scott", + "tiger" + ); + + protected static final ConnectionConfig POSTGRESQL = + new ConnectionConfig( + "PostgreSQL", + "org.postgresql.Driver", + "postgresql:postgres", + "postgres", + "oracle" + ); + + // the Sybase server must be configured explicitly to "localhost" + // in the config file [SYBASE]/ini/sql.ini + protected static final ConnectionConfig SYBASE = + new ConnectionConfig( + "Sybase", + "com.sybase.jdbc3.jdbc.SybDriver", + "sybase:Tds:localhost:5000", + "sa", + "oracle" + ); + + protected static class ConnectionConfig { + protected final String platformName; + protected final String driverClassName; + protected final String databaseURL; + protected final String user; + protected final String password; + protected ConnectionConfig( + String platformName, + String driverClassName, + String databaseURL, + String user, + String password + ) { + super(); + this.platformName = platformName; + this.driverClassName = driverClassName; + this.databaseURL = databaseURL; + this.user = user; + this.password = password; + } + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JDBCTools.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JDBCTools.java new file mode 100644 index 0000000000..4f8d879705 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JDBCTools.java @@ -0,0 +1,105 @@ +/******************************************************************************* + * Copyright (c) 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal; + +import java.io.OutputStreamWriter; +import java.io.PrintWriter; +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.Iterator; +import java.util.Map; + +import org.eclipse.jpt.common.utility.internal.iterators.ResultSetIterator; + +@SuppressWarnings("nls") +public class JDBCTools { + + public static void dump(Connection connection, String sql) throws SQLException { + dump(execute(connection, sql)); + } + + public static void dump(ResultSet resultSet) throws SQLException { + PrintWriter pw = new PrintWriter(new OutputStreamWriter(System.out)); + // synchronize the console so everything is contiguous + synchronized (System.out) { + dumpOn(resultSet, pw); + } + pw.flush(); + } + + public static void dumpOn(ResultSet resultSet, PrintWriter pw) throws SQLException { + ArrayList<HashMap<String, Object>> maps = convertToMaps(resultSet); + for (Iterator<HashMap<String, Object>> mapStream = maps.iterator(); mapStream.hasNext(); ) { + for (Iterator<Map.Entry<String, Object>> entryStream = mapStream.next().entrySet().iterator(); entryStream.hasNext(); ) { + Map.Entry<String, Object> entry = entryStream.next(); + pw.print(entry.getKey()); + pw.print(" = "); + pw.print(entry.getValue()); + pw.println(); + } + if (mapStream.hasNext()) { + pw.println(); + } + } + pw.println("total rows: " + maps.size()); + } + + public static ArrayList<HashMap<String, Object>> convertToMaps(Connection connection, String sql) throws SQLException { + return convertToMaps(execute(connection, sql)); + } + + public static ResultSet execute(Connection connection, String sql) throws SQLException { + Statement statement = connection.createStatement(); + statement.execute(sql); + ResultSet resultSet = statement.getResultSet(); + statement.close(); + return resultSet; + } + + public static ArrayList<HashMap<String, Object>> convertToMaps(ResultSet resultSet) throws SQLException { + ArrayList<HashMap<String, Object>> rows = new ArrayList<HashMap<String, Object>>(); + for (Iterator<HashMap<String, Object>> stream = buildMapIterator(resultSet); stream.hasNext(); ) { + rows.add(stream.next()); + } + return rows; + } + + public static Iterator<HashMap<String, Object>> buildMapIterator(ResultSet resultSet) throws SQLException { + return new ResultSetIterator<HashMap<String, Object>>(resultSet, new MapResultSetIteratorAdapter(buildColumnNames(resultSet))); + } + + public static String[] buildColumnNames(ResultSet resultSet) throws SQLException { + String[] names = new String[resultSet.getMetaData().getColumnCount()]; + for (int i = 0; i < names.length; i++) { + names[i] = resultSet.getMetaData().getColumnName(i + 1); // NB: ResultSet index/subscript is 1-based + } + return names; + } + + public static class MapResultSetIteratorAdapter implements ResultSetIterator.Adapter<HashMap<String, Object>> { + private final String[] columnNames; + public MapResultSetIteratorAdapter(String[] columnNames) { + super(); + this.columnNames = columnNames; + } + public HashMap<String, Object> buildNext(ResultSet rs) throws SQLException { + HashMap<String, Object> map = new HashMap<String, Object>(this.columnNames.length); + for (int i = 0; i < this.columnNames.length; i++) { + map.put(this.columnNames[i], rs.getObject(i + 1)); // NB: ResultSet index/subscript is 1-based + } + return map; + } + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JptJpaDbTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JptJpaDbTests.java new file mode 100644 index 0000000000..937b8c2b8c --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JptJpaDbTests.java @@ -0,0 +1,34 @@ +/******************************************************************************* + * Copyright (c) 2006, 2008 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal; + +import junit.framework.Test; +import junit.framework.TestSuite; +import org.eclipse.jpt.jpa.db.tests.internal.platforms.AllPlatformTests; + +/** + * JPT DB Tests + */ +public class JptJpaDbTests { + + public static Test suite() { + TestSuite suite = new TestSuite(JptJpaDbTests.class.getPackage().getName()); + + suite.addTest( AllPlatformTests.suite()); + + return suite; + } + + private JptJpaDbTests() { + super(); + throw new UnsupportedOperationException(); + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JptJpaDbTestsPlugin.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JptJpaDbTestsPlugin.java new file mode 100644 index 0000000000..f42832e95f --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/JptJpaDbTestsPlugin.java @@ -0,0 +1,56 @@ +/******************************************************************************* + * Copyright (c) 2006, 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal; + +import org.eclipse.core.runtime.Plugin; +import org.osgi.framework.BundleContext; + +/** + * JptJpaDbTestsPlugin + */ +public class JptJpaDbTestsPlugin extends Plugin { + + // The shared instance + private static JptJpaDbTestsPlugin INSTANCE; + + public static final String BUNDLE_ID = "org.eclipse.jpt.jpa.db.tests"; //$NON-NLS-1$ + + /** + * Returns the shared instance + */ + public static JptJpaDbTestsPlugin instance() { + return INSTANCE; + } + + /** + * The constructor + */ + public JptJpaDbTestsPlugin() { + super(); + INSTANCE = this; + } + + /** + * This method is called upon plug-in activation + */ + @Override + public void start(BundleContext context) throws Exception { + super.start(context); + } + + /** + * This method is called when the plug-in is stopped + */ + @Override + public void stop(BundleContext context) throws Exception { + INSTANCE = null; + super.stop(context); + } +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/AllPlatformTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/AllPlatformTests.java new file mode 100644 index 0000000000..042e954e6a --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/AllPlatformTests.java @@ -0,0 +1,37 @@ +/******************************************************************************* + * Copyright (c) 2006, 2008 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + +import junit.framework.Test; +import junit.framework.TestSuite; + +public class AllPlatformTests { + + public static Test suite() { + TestSuite suite = new TestSuite(AllPlatformTests.class.getPackage().getName()); + + suite.addTestSuite(DerbyTests.class); + suite.addTestSuite(MySQLTests.class); + suite.addTestSuite(Oracle10gTests.class); +// suite.addTestSuite(Oracle10gXETests.class); +// suite.addTestSuite(Oracle9iTests.class); + suite.addTestSuite(PostgreSQLTests.class); +// suite.addTestSuite(SQLServerTests.class); + suite.addTestSuite(SybaseTests.class); + + return suite; + } + + private AllPlatformTests() { + super(); + throw new UnsupportedOperationException(); + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/DTPPlatformTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/DTPPlatformTests.java new file mode 100644 index 0000000000..5586335b34 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/DTPPlatformTests.java @@ -0,0 +1,1027 @@ +/******************************************************************************* + * Copyright (c) 2006, 2010 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + +import java.io.File; +import java.io.IOException; +import java.io.OutputStreamWriter; +import java.net.URL; +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.Iterator; +import java.util.List; +import java.util.Properties; + +import junit.framework.TestCase; + +import org.eclipse.core.runtime.CoreException; +import org.eclipse.core.runtime.IPath; +import org.eclipse.core.runtime.IStatus; +import org.eclipse.core.runtime.Platform; +import org.eclipse.datatools.connectivity.ConnectionProfileException; +import org.eclipse.datatools.connectivity.IConnectionProfile; +import org.eclipse.datatools.connectivity.IManagedConnection; +import org.eclipse.datatools.connectivity.ProfileManager; +import org.eclipse.datatools.connectivity.drivers.IDriverMgmtConstants; +import org.eclipse.datatools.connectivity.drivers.IPropertySet; +import org.eclipse.datatools.connectivity.drivers.PropertySetImpl; +import org.eclipse.datatools.connectivity.drivers.XMLFileManager; +import org.eclipse.datatools.connectivity.drivers.jdbc.IJDBCDriverDefinitionConstants; +import org.eclipse.datatools.connectivity.internal.ConnectivityPlugin; +import org.eclipse.datatools.connectivity.sqm.core.connection.ConnectionInfo; +import org.eclipse.datatools.connectivity.sqm.core.rte.ICatalogObject; +import org.eclipse.jpt.common.utility.IndentingPrintWriter; +import org.eclipse.jpt.common.utility.internal.ReflectionTools; +import org.eclipse.jpt.common.utility.internal.StringTools; +import org.eclipse.jpt.common.utility.internal.iterators.ResultSetIterator; +import org.eclipse.jpt.jpa.db.Catalog; +import org.eclipse.jpt.jpa.db.Column; +import org.eclipse.jpt.jpa.db.ConnectionListener; +import org.eclipse.jpt.jpa.db.ConnectionProfile; +import org.eclipse.jpt.jpa.db.ConnectionProfileFactory; +import org.eclipse.jpt.jpa.db.ConnectionProfileListener; +import org.eclipse.jpt.jpa.db.Database; +import org.eclipse.jpt.jpa.db.DatabaseIdentifierAdapter; +import org.eclipse.jpt.jpa.db.ForeignKey; +import org.eclipse.jpt.jpa.db.JptJpaDbPlugin; +import org.eclipse.jpt.jpa.db.Schema; +import org.eclipse.jpt.jpa.db.SchemaContainer; +import org.eclipse.jpt.jpa.db.Sequence; +import org.eclipse.jpt.jpa.db.Table; +import org.eclipse.jpt.jpa.db.ForeignKey.ColumnPair; +import org.eclipse.jpt.jpa.db.tests.internal.JptJpaDbTestsPlugin; + +/** + * Base class for testing DTP wrappers on various databases. + */ +@SuppressWarnings("nls") +public abstract class DTPPlatformTests extends TestCase { + + /** + * The platform properties are loaded from a Java properties file in the + * 'org.eclipse.jpt.jpa.db.tests/config' directory. Each database platform has + * its own properties file (e.g. 'derby.properties'). + */ + private Properties platformProperties; + + /** + * This is the Dali connection profile wrapper. + */ + protected ConnectionProfile connectionProfile; + + + // ********** constants ********** + + private static final String PLATFORM_CONFIG_DIRECTORY = "config"; + + private static final String DB_USER_ID_PROPERTY = "userID"; + private static final String DB_USER_ID_DEFAULT = "user"; + + private static final String DB_PASSWORD_PROPERTY = "password"; + private static final String DB_PASSWORD_DEFAULT = ""; + + private static final String DB_DRIVER_JARS_PROPERTY = "jars"; + // required - no default + + private static final String DB_URL_PROPERTY = "url"; + // required - no default + + + + // ********** constructor ********** + + protected DTPPlatformTests(String name) { + super(name); + } + + + // ********** set-up/tear-down ********** + + @Override + protected void setUp() throws Exception { + super.setUp(); + + this.platformProperties = this.loadPlatformProperties(); + this.buildDTPDriverDefinitionFile(); + this.buildDTPConnectionProfile(); + this.connectionProfile = this.getConnectionProfileFactory().buildConnectionProfile(this.getProfileName(), DatabaseIdentifierAdapter.Default.instance()); + } + + @Override + protected void tearDown() throws Exception { + this.connectionProfile = null; + this.platformProperties = null; + + super.tearDown(); + } + + // ***** platform properties file + private Properties loadPlatformProperties() throws IOException { + Properties p = new Properties(); + p.load(this.buildPlatformPropertiesFileURL().openStream()); + return p; + } + + private URL buildPlatformPropertiesFileURL() { + return Platform.getBundle(this.getTestPluginBundleID()).getEntry(this.getPlatformPropertiesFilePath()); + } + + private String getTestPluginBundleID() { + return JptJpaDbTestsPlugin.BUNDLE_ID; + } + + private String getPlatformPropertiesFilePath() { + return this.getPlatformPropertiesDirectoryName() + '/' + this.getPlatformPropertiesFileName(); + } + + private String getPlatformPropertiesDirectoryName() { + return PLATFORM_CONFIG_DIRECTORY; + } + + /** + * Each database platform has a separate properties file in the 'config' + * directory that must be customized by whomever is executing the tests. + */ + protected abstract String getPlatformPropertiesFileName(); + + // ***** driver definition file + private void buildDTPDriverDefinitionFile() throws CoreException { + XMLFileManager.setStorageLocation(this.getDTPDriverDefinitionLocation()); + XMLFileManager.setFileName(this.getDTPDriverFileName()); + + IPropertySet[] sets = XMLFileManager.loadPropertySets(); + for (IPropertySet set : sets) { + if (set.getID().equals(this.getDriverDefinitionID())) { + return; // property sets live across tests + } + } + + XMLFileManager.saveNamedPropertySet(this.buildDTPDriverDefinitionPropertySets()); + + // verify the file was created: + File driverDefinitioneFile = this.getDTPDriverDefinitionLocation().append(this.getDTPDriverFileName()).toFile(); + assertTrue(driverDefinitioneFile.exists()); + } + + private IPath getDTPDriverDefinitionLocation() { + return ConnectivityPlugin.getDefault().getStateLocation(); + } + + private String getDTPDriverFileName() { + return IDriverMgmtConstants.DRIVER_FILE; + } + + private IPropertySet[] buildDTPDriverDefinitionPropertySets() { + IPropertySet[] propertySets = new IPropertySet[1]; + PropertySetImpl propertySet = new PropertySetImpl(this.getDriverName(), this.getDriverDefinitionID()); + propertySet.setProperties(this.getDriverDefinitionID(), this.buildDTPDriverDefinitionProperties()); + propertySets[0] = propertySet; + return propertySets; + } + + protected abstract String getDriverName(); + + protected abstract String getDriverDefinitionID(); + + private Properties buildDTPDriverDefinitionProperties() { + Properties p = new Properties(); + p.setProperty(ConnectionProfile.DRIVER_DEFINITION_TYPE_PROP_ID, this.getDriverDefinitionType()); + p.setProperty(ConnectionProfile.DRIVER_JAR_LIST_PROP_ID, this.getJDBCDriverJarList()); + p.setProperty(IJDBCDriverDefinitionConstants.DATABASE_VENDOR_PROP_ID, this.getDatabaseVendor()); + p.setProperty(IJDBCDriverDefinitionConstants.DATABASE_VERSION_PROP_ID, this.getDatabaseVersion()); + p.setProperty(IJDBCDriverDefinitionConstants.DRIVER_CLASS_PROP_ID, this.getDriverClass()); + p.setProperty(IJDBCDriverDefinitionConstants.URL_PROP_ID, this.getJDBCURL()); + p.setProperty(IJDBCDriverDefinitionConstants.USERNAME_PROP_ID, this.getUserID()); + p.setProperty(IJDBCDriverDefinitionConstants.PASSWORD_PROP_ID, this.getPassword()); + return p; + } + + protected abstract String getDriverDefinitionType(); + + /** + * The JAR list is workspace-specific and is set in the properties file. + */ + private String getJDBCDriverJarList() { + return this.getRequiredPlatformProperty(DB_DRIVER_JARS_PROPERTY); + } + + protected abstract String getDatabaseVendor(); + + protected abstract String getDatabaseVersion(); + + protected abstract String getDriverClass(); + + /** + * The database URL is workspace-specific and is set in the properties file + * for some databases. + */ + private String getJDBCURL() { + return this.platformProperties.getProperty(DB_URL_PROPERTY, this.getDefaultJDBCURL()); + } + + protected String getDefaultJDBCURL() { + return ""; + } + + /** + * The user ID is optional and can be set in the properties file. + */ + protected String getUserID() { + return this.platformProperties.getProperty(DB_USER_ID_PROPERTY, DB_USER_ID_DEFAULT); + } + + /** + * The password is optional and can be set in the properties file. + */ + private String getPassword() { + return this.platformProperties.getProperty(DB_PASSWORD_PROPERTY, DB_PASSWORD_DEFAULT); + } + + // ***** DTP connection profile + private void buildDTPConnectionProfile() throws ConnectionProfileException { + if (this.getDTPProfileManager().getProfileByName(this.getProfileName()) != null) { + return; // profiles live across tests + } + this.createProfile(this.getProfileName()); + assertNotNull(this.getDTPProfileManager().getProfileByName(this.getProfileName())); + } + + protected void createProfile(String profileName) throws ConnectionProfileException { + this.getDTPProfileManager().createProfile( + profileName, + this.getProfileDescription(), + this.getProviderID(), + this.buildDTPConnectionProfileProperties() + ); + } + + protected abstract String getProfileName(); + + protected abstract String getProfileDescription(); + + protected String getProviderID() { + return ConnectionProfile.CONNECTION_PROFILE_TYPE; + } + + protected Properties buildDTPConnectionProfileProperties() { + Properties p = new Properties(); + p.setProperty(IJDBCDriverDefinitionConstants.USERNAME_PROP_ID, this.getUserID()); + p.setProperty(IJDBCDriverDefinitionConstants.PASSWORD_PROP_ID, this.getPassword()); + p.setProperty(ConnectionProfile.DRIVER_DEFINITION_PROP_ID, this.getDriverDefinitionID()); + + p.setProperty(IJDBCDriverDefinitionConstants.DRIVER_CLASS_PROP_ID, this.getDriverClass()); + p.setProperty(IJDBCDriverDefinitionConstants.URL_PROP_ID, this.getJDBCURL()); + p.setProperty(IJDBCDriverDefinitionConstants.DATABASE_VENDOR_PROP_ID, this.getDatabaseVendor()); + p.setProperty(IJDBCDriverDefinitionConstants.DATABASE_VERSION_PROP_ID, this.getDatabaseVersion()); + + p.setProperty(ConnectionProfile.DATABASE_SAVE_PWD_PROP_ID, this.passwordIsSaved()); + return p; + } + + private String passwordIsSaved() { + return "true"; + } + + + // ********** tests ********** + + public void testConnectionProfileListener() throws ConnectionProfileException { + TestConnectionProfileListener listener = new TestConnectionProfileListener(); + this.getConnectionProfileFactory().addConnectionProfileListener(listener); + + String cpName1 = this.getProfileName() + "1"; + this.createProfile(cpName1); + IConnectionProfile dtpCP = this.getDTPProfileManager().getProfileByName(cpName1); + assertNotNull(dtpCP); + + assertEquals(cpName1, listener.addedName); + listener.clear(); + + String cpName2 = this.getProfileName() + "2"; + this.getDTPProfileManager().modifyProfile(dtpCP, cpName2, null); + assertEquals(cpName1, listener.renamedOldName); + assertEquals(cpName2, listener.renamedNewName); + listener.clear(); + + ConnectionProfile cp = this.getConnectionProfileFactory().buildConnectionProfile(cpName2); + assertNotNull(cp); + + this.getDTPProfileManager().deleteProfile(dtpCP); + assertEquals(cpName2, listener.removedName); + listener.clear(); + + cp = this.getConnectionProfileFactory().buildConnectionProfile(cpName2); + assertNull(cp); + + this.getConnectionProfileFactory().removeConnectionProfileListener(listener); + } + + public void testName() { + assertEquals(this.getProfileName(), this.connectionProfile.getName()); + } + + public void testConnection() throws Exception { + assertTrue(this.connectionProfile.isInactive()); + assertTrue(this.connectionProfile.isDisconnected()); + this.connectionProfile.connect(); + assertTrue(this.connectionProfile.isActive()); + assertTrue(this.connectionProfile.isConnected()); + + this.verifyDatabaseVersionNumber(); + this.verifyDatabaseVendor(); + this.verifyDatabaseContent(); + + this.connectionProfile.disconnect(); + assertTrue(this.connectionProfile.isInactive()); + assertTrue(this.connectionProfile.isDisconnected()); + } + + private void verifyDatabaseVersionNumber() { + Database database = this.connectionProfile.getDatabase(); + assertNotNull(database); + + String actual = database.getVersion(); + String expected = this.getDatabaseVersion(); + String errorMessage = "expected: " + expected + " - actual: " + actual; + // partial match is good enough + assertTrue(errorMessage, actual.indexOf(expected) != -1); + } + + private void verifyDatabaseVendor() { + Database database = this.connectionProfile.getDatabase(); + String actual = database.getVendorName(); + String expected = this.getDatabaseVendor(); + assertEquals(expected, actual); + } + + private void verifyDatabaseContent() { + Database database = this.connectionProfile.getDatabase(); + assertTrue(database.getSchemataSize() >= 0); + + Schema schema = database.getDefaultSchema(); + if (schema != null) { + if (schema.getTablesSize() > 0) { + Table table = schema.getTables().iterator().next(); + assertTrue(table.getColumnsSize() >= 0); + assertTrue(table.getPrimaryKeyColumnsSize() >= 0); + assertTrue(table.getForeignKeysSize() >= 0); + } + } + } + + protected abstract boolean executeOfflineTests(); + + public final void testOffline() { + if ( ! this.executeOfflineTests()) { + return; + } + if ( ! this.connectionProfile.supportsWorkOfflineMode()) { + return; + } + + this.prepareForOfflineWork(); + + IStatus status = this.connectionProfile.workOffline(); + assertTrue(status.isOK()); + assertTrue(this.connectionProfile.isActive()); + assertTrue(this.connectionProfile.isWorkingOffline()); + + this.connectionProfile.disconnect(); + assertTrue(this.connectionProfile.isInactive()); + assertTrue(this.connectionProfile.isDisconnected()); + } + + protected void prepareForOfflineWork() { + if ( ! this.connectionProfile.canWorkOffline()) { + this.connectionProfile.connect(); + IStatus status = this.connectionProfile.saveWorkOfflineData(); + assertTrue(status.isOK()); + this.connectionProfile.disconnect(); + assertTrue(this.connectionProfile.canWorkOffline()); + } + } + + public void testConnectionListenerConnect() { + assertTrue(this.connectionProfileHasNoListeners()); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + assertTrue(this.connectionProfileHasAnyListeners()); + + this.connectionProfile.connect(); + assertSame(this.connectionProfile, listener.openedProfile); + listener.clear(); + + this.connectionProfile.disconnect(); + assertSame(this.connectionProfile, listener.okToCloseProfile); + assertSame(this.connectionProfile, listener.aboutToCloseProfile); + assertSame(this.connectionProfile, listener.closedProfile); + + this.connectionProfile.removeConnectionListener(listener); + assertTrue(this.connectionProfileHasNoListeners()); + } + + public final void testConnectionListenerOffline() { + if ( ! this.executeOfflineTests()) { + return; + } + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.prepareForOfflineWork(); + listener.clear(); + + this.connectionProfile.workOffline(); + assertSame(this.connectionProfile, listener.openedProfile); + listener.clear(); + + this.connectionProfile.disconnect(); + assertSame(this.connectionProfile, listener.okToCloseProfile); + assertSame(this.connectionProfile, listener.aboutToCloseProfile); + assertSame(this.connectionProfile, listener.closedProfile); + listener.clear(); + + this.connectionProfile.removeConnectionListener(listener); + } + + public void testConnectionListenerDatabase() { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + assertSame(this.connectionProfile.getDatabase(), listener.changedDatabase); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testConnectionListenerCatalog() { + this.connectionProfile.connect(); + if ( ! this.connectionProfile.getDatabase().supportsCatalogs()) { + this.connectionProfile.disconnect(); + return; + } + + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + // take the first catalog + org.eclipse.datatools.modelbase.sql.schema.Catalog dtpCatalog = this.getFirstDTPCatalog(); + Catalog catalog = this.getCatalogNamed(dtpCatalog.getName()); + ((ICatalogObject) dtpCatalog).refresh(); + assertSame(catalog, listener.changedCatalog); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testConnectionListenerSchema() { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + List<org.eclipse.datatools.modelbase.sql.schema.Catalog> dtpCatalogs = this.getDTPCatalogs(); + org.eclipse.datatools.modelbase.sql.schema.Catalog dtpCatalog = null; + org.eclipse.datatools.modelbase.sql.schema.Schema dtpSchema = null; + Schema schema = null; + if (this.connectionProfile.getDatabase().supportsCatalogs()) { + dtpCatalog = dtpCatalogs.get(0); + dtpSchema = (org.eclipse.datatools.modelbase.sql.schema.Schema) dtpCatalog.getSchemas().get(0); + schema = this.getCatalogNamed(dtpCatalog.getName()).getSchemaNamed(dtpSchema.getName()); + } else { + if (dtpCatalogs.isEmpty()) { + dtpSchema = (org.eclipse.datatools.modelbase.sql.schema.Schema) this.getDTPDatabase().getSchemas().get(0); + schema = this.connectionProfile.getDatabase().getSchemaNamed(dtpSchema.getName()); + } else { + dtpCatalog = dtpCatalogs.get(0); // should be the "virtual" catalog + assertEquals("", dtpCatalog.getName()); + dtpSchema = (org.eclipse.datatools.modelbase.sql.schema.Schema) dtpCatalog.getSchemas().get(0); + // the schemata are held directly by the database in this situation + schema = this.getDatabase().getSchemaNamed(dtpSchema.getName()); + } + } + assertTrue(schema.getTablesSize() >= 0); // force tables to be loaded + ((ICatalogObject) dtpSchema).refresh(); + assertSame(schema, listener.changedSchema); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testSupportsCatalogs() { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + boolean supportsCatalogs = this.supportsCatalogs(); + assertEquals(supportsCatalogs, this.connectionProfile.getDatabase().supportsCatalogs()); + if (supportsCatalogs) { + assertTrue(this.connectionProfile.getDatabase().getCatalogsSize() > 0); + assertEquals(0, this.connectionProfile.getDatabase().getSchemataSize()); + } else { + assertEquals(0, this.connectionProfile.getDatabase().getCatalogsSize()); + assertTrue(this.connectionProfile.getDatabase().getSchemataSize() > 0); + } + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + protected abstract boolean supportsCatalogs(); + +// public void testDEBUG() throws Exception { +// this.connectionProfile.connect(); +// this.dumpJDBCCatalogs(); +// this.dumpJDBCSchemata(); +// this.dumpDatabaseContainers(); +// this.connectionProfile.disconnect(); +// } + + + // ********** convenience methods ********** + + protected ConnectionProfileFactory getConnectionProfileFactory() { + return JptJpaDbPlugin.getConnectionProfileFactory(); + } + + protected ConnectionProfile getConnectionProfile() { + return this.connectionProfile; + } + + protected Database getDatabase() { + return this.connectionProfile.getDatabase(); + } + + protected Catalog getDefaultCatalog() { + return this.getDatabase().getDefaultCatalog(); + } + + /** + * only valid on databases that do not support catalogs + */ + protected Schema getDefaultSchema() { + return this.getDatabase().getDefaultSchema(); + } + + protected Catalog getCatalogNamed(String catalogName) { + return this.connectionProfile.getDatabase().getCatalogNamed(catalogName); + } + + protected String getRequiredPlatformProperty(String propertyKey) { + String propertyValue = this.platformProperties.getProperty(propertyKey); + if (StringTools.stringIsEmpty(propertyValue)) { + throw new IllegalArgumentException("The database platform properties file '" + this.getPlatformPropertiesFilePath() + + "' is missing a value for the property '" + propertyKey + "'."); + } + return propertyValue; + } + + protected boolean connectionProfileHasAnyListeners() { + return connectionProfileHasAnyListeners(this.connectionProfile); + } + + protected static boolean connectionProfileHasAnyListeners(ConnectionProfile cp) { + return ((Boolean) ReflectionTools.executeMethod(cp, "hasAnyListeners")).booleanValue(); + } + + protected boolean connectionProfileHasNoListeners() { + return connectionProfileHasNoListeners(this.connectionProfile); + } + + protected static boolean connectionProfileHasNoListeners(ConnectionProfile cp) { + return ((Boolean) ReflectionTools.executeMethod(cp, "hasNoListeners")).booleanValue(); + } + + + // ********** DTP model ********** + + protected ProfileManager getDTPProfileManager() { + return ProfileManager.getInstance(); + } + + protected IConnectionProfile getDTPConnectionProfile() { + return getDTPConnectionProfile(this.connectionProfile); + } + + protected static IConnectionProfile getDTPConnectionProfile(ConnectionProfile cp) { + return (IConnectionProfile) ReflectionTools.getFieldValue(cp, "dtpConnectionProfile"); + } + + protected IManagedConnection getDTPManagedConnection() { + return (IManagedConnection) ReflectionTools.getFieldValue(this.connectionProfile, "dtpManagedConnection"); + } + + protected org.eclipse.datatools.modelbase.sql.schema.Database getDTPDatabase() { + return getDTPDatabase(this.connectionProfile.getDatabase()); + } + + protected static org.eclipse.datatools.modelbase.sql.schema.Database getDTPDatabase(Database database) { + return (org.eclipse.datatools.modelbase.sql.schema.Database) ReflectionTools.getFieldValue(database, "dtpDatabase"); + } + + @SuppressWarnings("unchecked") + protected List<org.eclipse.datatools.modelbase.sql.schema.Catalog> getDTPCatalogs() { + return this.getDTPDatabase().getCatalogs(); + } + + protected org.eclipse.datatools.modelbase.sql.schema.Catalog getFirstDTPCatalog() { + return this.getDTPCatalogs().get(0); + } + + protected org.eclipse.datatools.modelbase.sql.schema.Catalog getDTPCatalogNamed(String name) { + return getDTPCatalog(this.getDatabase().getCatalogNamed(name)); + } + + protected static org.eclipse.datatools.modelbase.sql.schema.Catalog getDTPCatalog(Catalog catalog) { + return (org.eclipse.datatools.modelbase.sql.schema.Catalog) ReflectionTools.getFieldValue(catalog, "dtpCatalog"); + } + + protected org.eclipse.datatools.modelbase.sql.schema.Schema getDTPSchemaNamed(String name) { + return getDTPSchema(this.getDatabase().getSchemaNamed(name)); + } + + protected static org.eclipse.datatools.modelbase.sql.schema.Schema getDTPSchema(Schema schema) { + return (org.eclipse.datatools.modelbase.sql.schema.Schema) ReflectionTools.getFieldValue(schema, "dtpSchema"); + } + + + // ********** execute SQL ********** + + /** + * ignore any errors (useful for dropping database objects that might + * not be there) + */ + protected void executeUpdateIgnoreErrors(String sql) { + try { + this.executeUpdate(sql); + } catch (Exception ex) { +// System.err.println("SQL: " + sql); +// ex.printStackTrace(); + } + } + + protected void executeUpdate(String sql) throws SQLException { + Statement jdbcStatement = this.createJDBCStatement(); + try { + jdbcStatement.executeUpdate(sql); + } finally { + jdbcStatement.close(); + } + } + + protected void dump(String sql) throws SQLException { + this.dump(sql, 20); + } + + protected void dump(String sql, int columnWidth) throws SQLException { + IndentingPrintWriter pw = new IndentingPrintWriter(new OutputStreamWriter(System.out)); + // synchronize the console so everything is contiguous + synchronized (System.out) { + this.dumpOn(sql, pw, columnWidth); + } + pw.flush(); + } + + protected void dumpOn(String sql, IndentingPrintWriter pw, int columnWidth) throws SQLException { + pw.println(sql); + for (ArrayList<Object> row : this.execute(sql)) { + for (Object columnValue : row) { + StringTools.padOrTruncateOn(String.valueOf(columnValue), columnWidth, pw); + pw.print(' '); + } + pw.println(); + } + } + + protected ArrayList<ArrayList<Object>> execute(String sql) throws SQLException { + Statement jdbcStatement = this.createJDBCStatement(); + jdbcStatement.execute(sql); + ArrayList<ArrayList<Object>> rows = this.buildRows(jdbcStatement.getResultSet()); + jdbcStatement.close(); + return rows; + } + + protected ArrayList<ArrayList<Object>> buildRows(ResultSet resultSet) throws SQLException { + ArrayList<ArrayList<Object>> rows = new ArrayList<ArrayList<Object>>(); + for (Iterator<ArrayList<Object>> stream = this.buildArrayIterator(resultSet); stream.hasNext(); ) { + rows.add(stream.next()); + } + return rows; + } + + protected Iterator<ArrayList<Object>> buildArrayIterator(ResultSet resultSet) throws SQLException { + return new ResultSetIterator<ArrayList<Object>>(resultSet, new ListResultSetIteratorAdapter(resultSet.getMetaData().getColumnCount())); + } + + public static class ListResultSetIteratorAdapter implements ResultSetIterator.Adapter<ArrayList<Object>> { + private final int columnCount; + public ListResultSetIteratorAdapter(int columnCount) { + super(); + this.columnCount = columnCount; + } + public ArrayList<Object> buildNext(ResultSet rs) throws SQLException { + ArrayList<Object> list = new ArrayList<Object>(this.columnCount); + for (int i = 1; i <= this.columnCount; i++) { // NB: ResultSet index/subscript is 1-based + list.add(rs.getObject(i)); + } + return list; + } + } + + protected Statement createJDBCStatement() throws SQLException { + return this.getJDBCConnection().createStatement(); + } + + protected Connection getJDBCConnection() { + return ((ConnectionInfo) this.getDTPManagedConnection().getConnection().getRawConnection()).getSharedConnection(); + } + + protected DatabaseMetaData getDatabaseMetaData() throws SQLException { + return this.getJDBCConnection().getMetaData(); + } + + + // ********** dump DTP metadata ********** + + /** + * dump all the database metadata to the console + */ + protected void dumpDatabase() { + this.dumpDatabase(true); + } + + /** + * dump the database catalogs and schemata to the console + */ + protected void dumpDatabaseContainers() { + this.dumpDatabase(false); + } + + protected void dumpDatabase(boolean deep) { + IndentingPrintWriter pw = new IndentingPrintWriter(new OutputStreamWriter(System.out)); + // synchronize the console so everything is contiguous + synchronized (System.out) { + this.dumpDatabaseOn(pw, deep); + } + pw.flush(); + } + + protected void dumpDatabaseOn(IndentingPrintWriter pw, boolean deep) { + Database database = this.connectionProfile.getDatabase(); + pw.print("database: "); + pw.println(database.getName()); + if (database.supportsCatalogs()) { + for (Catalog catalog : database.getCatalogs()) { + this.dumpCatalogOn(catalog, pw, deep); + } + } else { + this.dumpSchemaContainerOn(database, pw, deep); + } + } + + protected void dumpCatalogOn(Catalog catalog, IndentingPrintWriter pw, boolean deep) { + pw.print("catalog: "); + pw.println(catalog.getName()); + pw.indent(); + this.dumpSchemaContainerOn(catalog, pw, deep); + pw.undent(); + } + + protected void dumpSchemaContainerOn(SchemaContainer schemaContainer, IndentingPrintWriter pw, boolean deep) { + for (Schema schema : schemaContainer.getSchemata()) { + this.dumpSchemaOn(schema, pw, deep); + } + } + + protected void dumpSchema(Schema schema) { + this.dumpSchema(schema, true); + } + + protected void dumpSchema(Schema schema, boolean deep) { + IndentingPrintWriter pw = new IndentingPrintWriter(new OutputStreamWriter(System.out)); + // synchronize the console so everything is contiguous + synchronized (System.out) { + this.dumpSchemaOn(schema, pw, deep); + } + pw.flush(); + } + + protected void dumpSchemaOn(Schema schema, IndentingPrintWriter pw, boolean deep) { + pw.print("schema: "); + pw.println(schema.getName()); + if (deep) { + pw.indent(); + for (Table table : schema.getTables()) { + this.dumpTableOn(table, pw); + } + for (Sequence sequence : schema.getSequences()) { + this.dumpSequenceOn(sequence, pw); + } + pw.undent(); + } + } + + protected void dumpTableOn(Table table, IndentingPrintWriter pw) { + pw.print("table: "); + pw.println(table.getName()); + pw.indent(); + for (Column column : table.getColumns()) { + this.dumpColumnOn(column, pw); + } + for (ForeignKey foreignKey : table.getForeignKeys()) { + this.dumpForeignKeyOn(foreignKey, pw); + } + pw.undent(); + } + + protected void dumpColumnOn(Column column, IndentingPrintWriter pw) { + pw.print("column: "); + pw.print(column.getName()); + pw.print(" : "); + pw.print(column.getDataTypeName()); + if (column.isPartOfPrimaryKey()) { + pw.print(" [primary key]"); + } + pw.println(); + } + + protected void dumpForeignKeyOn(ForeignKey foreignKey, IndentingPrintWriter pw) { + pw.print("foreign key: "); + pw.print(foreignKey.getName()); + pw.print("=>"); + pw.print(foreignKey.getReferencedTable().getName()); + pw.print(" ("); + for (Iterator<ColumnPair> stream = foreignKey.getColumnPairs().iterator(); stream.hasNext(); ) { + ColumnPair cp = stream.next(); + pw.print(cp.getBaseColumn().getName()); + pw.print("=>"); + pw.print(cp.getReferencedColumn().getName()); + if (stream.hasNext()) { + pw.print(", "); + } + } + pw.print(')'); + pw.println(); + } + + protected void dumpSequenceOn(Sequence sequence, IndentingPrintWriter pw) { + pw.print("sequence: "); + pw.println(sequence.getName()); + } + + + // ********** dump JDBC metadata ********** + + protected void dumpJDBCCatalogs() throws SQLException { + IndentingPrintWriter pw = new IndentingPrintWriter(new OutputStreamWriter(System.out)); + // synchronize the console so everything is contiguous + synchronized (System.out) { + this.dumpJDBCCatalogsOn(pw); + } + pw.flush(); + } + + protected void dumpJDBCCatalogsOn(IndentingPrintWriter pw) throws SQLException { + pw.println("JDBC catalogs: "); + pw.indent(); + ArrayList<ArrayList<Object>> rows = this.buildRows(this.getDatabaseMetaData().getCatalogs()); + for (Iterator<ArrayList<Object>> stream = rows.iterator(); stream.hasNext(); ) { + pw.println(stream.next().get(0)); + } + pw.undent(); + } + + protected void dumpJDBCSchemata() throws SQLException { + IndentingPrintWriter pw = new IndentingPrintWriter(new OutputStreamWriter(System.out)); + // synchronize the console so everything is contiguous + synchronized (System.out) { + this.dumpJDBCSchemataOn(pw); + } + pw.flush(); + } + + protected void dumpJDBCSchemataOn(IndentingPrintWriter pw) throws SQLException { + pw.println("JDBC schemata: "); + pw.indent(); + ArrayList<ArrayList<Object>> rows = this.buildRows(this.getDatabaseMetaData().getSchemas()); + for (ArrayList<Object> row : rows) { + if (row.size() == 2) { // catalogs were added in jdk 1.4 + Object catalog = row.get(1); + pw.print(catalog); + pw.print('.'); + } + Object schema = row.get(0); + pw.println(schema); + } + pw.undent(); + } + + + // ********** connection profile listener ********** + + protected static class TestConnectionProfileListener implements ConnectionProfileListener { + public String addedName; + public String removedName; + public String renamedOldName; + public String renamedNewName; + + public void connectionProfileAdded(String name) { + this.addedName = name; + } + public void connectionProfileRemoved(String name) { + this.removedName = name; + } + public void connectionProfileRenamed(String oldName, String newName) { + this.renamedOldName = oldName; + this.renamedNewName = newName; + } + public void clear() { + this.addedName = null; + this.removedName = null; + this.renamedOldName = null; + this.renamedNewName = null; + } + } + + + // ********** connection listener ********** + + protected static class TestConnectionListener implements ConnectionListener { + public ConnectionProfile openedProfile; + public ConnectionProfile modifiedProfile; + public ConnectionProfile okToCloseProfile; + public ConnectionProfile aboutToCloseProfile; + public ConnectionProfile closedProfile; + public Database changedDatabase; + public Catalog changedCatalog; + public Schema changedSchema; + public Sequence changedSequence; + public Table changedTable; + public Column changedColumn; + public ForeignKey changedForeignKey; + + public void opened(ConnectionProfile profile) { + this.openedProfile = profile; + } + public void modified(ConnectionProfile profile) { + this.modifiedProfile = profile; + } + public boolean okToClose(ConnectionProfile profile) { + this.okToCloseProfile = profile; + return true; + } + public void aboutToClose(ConnectionProfile profile) { + this.aboutToCloseProfile = profile; + } + public void closed(ConnectionProfile profile) { + this.closedProfile = profile; + } + public void databaseChanged(ConnectionProfile profile, Database database) { + this.changedDatabase = database; + } + public void catalogChanged(ConnectionProfile profile, Catalog catalog) { + this.changedCatalog = catalog; + } + public void schemaChanged(ConnectionProfile profile, Schema schema) { + this.changedSchema = schema; + } + public void sequenceChanged(ConnectionProfile profile, Sequence sequence) { + this.changedSequence = sequence; + } + public void tableChanged(ConnectionProfile profile, Table table) { + this.changedTable = table; + } + public void columnChanged(ConnectionProfile profile, Column column) { + this.changedColumn = column; + } + public void foreignKeyChanged(ConnectionProfile profile, ForeignKey foreignKey) { + this.changedForeignKey = foreignKey; + } + public void clear() { + this.openedProfile = null; + this.modifiedProfile = null; + this.okToCloseProfile = null; + this.aboutToCloseProfile = null; + this.closedProfile = null; + this.changedDatabase = null; + this.changedCatalog = null; + this.changedSchema = null; + this.changedSequence = null; + this.changedTable = null; + this.changedColumn = null; + this.changedForeignKey = null; + } + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/DerbyTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/DerbyTests.java new file mode 100644 index 0000000000..4dd8792c63 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/DerbyTests.java @@ -0,0 +1,482 @@ +/******************************************************************************* + * Copyright (c) 2006, 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + +import org.eclipse.datatools.connectivity.sqm.core.rte.ICatalogObject; +import org.eclipse.jpt.jpa.db.Column; +import org.eclipse.jpt.jpa.db.ForeignKey; +import org.eclipse.jpt.jpa.db.Schema; +import org.eclipse.jpt.jpa.db.Table; + +@SuppressWarnings("nls") +public class DerbyTests extends DTPPlatformTests { + + public DerbyTests(String name) { + super(name); + } + + @Override + protected void setUp() throws Exception { + super.setUp(); + } + + @Override + protected void tearDown() throws Exception { + super.tearDown(); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "derby.properties"; + } + + @Override + protected String getDriverName() { + return "Derby Embedded JDBC Driver"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.Derby Embedded JDBC Driver"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.connectivity.db.derby101.genericDriverTemplate"; + } + + @Override + protected String getDatabaseVendor() { + return "Derby"; + } + + @Override + protected String getDatabaseVersion() { + return "10.1"; + } + + @Override + protected String getDriverClass() { + return "org.apache.derby.jdbc.EmbeddedDriver"; + } + + @Override + protected String getProfileName() { + return "Derby_10.1_Embedded"; + } + + @Override + protected String getProfileDescription() { + return "Derby 10.1 Embedded JDBC Profile [Test]"; + } + + @Override + protected String getProviderID() { + return "org.eclipse.datatools.connectivity.db.derby.embedded.connectionProfile"; + } + + @Override + protected boolean supportsCatalogs() { + return false; + } + + @Override + protected boolean executeOfflineTests() { + return true; + } + + public void testSchema() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropSchema("TEST1"); + this.dropSchema("TEST2"); + + this.executeUpdate("CREATE SCHEMA TEST1"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema1 = this.getDatabase().getSchemaNamed("TEST1"); + assertNotNull(schema1); + + this.executeUpdate("CREATE SCHEMA TEST2"); + Schema schema2 = this.getDatabase().getSchemaNamed("TEST2"); + assertNull(schema2); // should be null until refresh + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + assertSame(this.getDatabase(), listener.changedDatabase); + + schema2 = this.getDatabase().getSchemaNamed("TEST2"); + assertNotNull(schema2); + assertNotSame(schema1, this.getDatabase().getSchemaNamed("TEST1")); // we should have a new schema after the refresh + + this.dropSchema("TEST2"); + this.dropSchema("TEST1"); + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testSchemaLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropSchema("LOOKUP_TEST"); + this.dropSchema("\"lookup_TEST\""); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + this.executeUpdate("CREATE SCHEMA LOOKUP_TEST"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + assertNotNull(this.getDatabase().getSchemaNamed("LOOKUP_TEST")); + assertNotNull(this.getDatabase().getSchemaForIdentifier("LOOKUP_TEST")); + + assertNull(this.getDatabase().getSchemaNamed("lookup_test")); + assertNotNull(this.getDatabase().getSchemaForIdentifier("lookup_test")); + + assertNull(this.getDatabase().getSchemaNamed("lookup_TEST")); + assertNotNull(this.getDatabase().getSchemaForIdentifier("lookup_TEST")); + + assertNotNull(this.getDatabase().getSchemaForIdentifier("\"LOOKUP_TEST\"")); + assertNull(this.getDatabase().getSchemaForIdentifier("\"lookup_TEST\"")); + + this.dropSchema("LOOKUP_TEST"); + + this.executeUpdate("CREATE SCHEMA \"lookup_TEST\""); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + assertNull(this.getDatabase().getSchemaNamed("LOOKUP_TEST")); + assertNull(this.getDatabase().getSchemaForIdentifier("LOOKUP_TEST")); + + assertNull(this.getDatabase().getSchemaNamed("lookup_test")); + assertNull(this.getDatabase().getSchemaForIdentifier("lookup_test")); + + assertNotNull(this.getDatabase().getSchemaNamed("lookup_TEST")); + assertNull(this.getDatabase().getSchemaForIdentifier("lookup_TEST")); + + assertNull(this.getDatabase().getSchemaForIdentifier("\"LOOKUP_TEST\"")); + assertNotNull(this.getDatabase().getSchemaForIdentifier("\"lookup_TEST\"")); + + this.dropSchema("\"lookup_TEST\""); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testSchemaIdentifier() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropSchema("LOOKUP_TEST"); + this.dropSchema("\"lookup_TEST\""); + + this.executeUpdate("CREATE SCHEMA lookup_test"); // this gets folded to uppercase + this.executeUpdate("CREATE SCHEMA \"lookup_TEST\""); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDatabase().getSchemaForIdentifier("LOOKUP_TEST"); + assertEquals("LOOKUP_TEST", schema.getIdentifier()); + assertEquals("LOOKUP_TEST", schema.getIdentifier("LookupTest")); + assertNull(schema.getIdentifier("Lookup_Test")); + + schema = this.getDatabase().getSchemaForIdentifier("lookup_test"); + assertEquals("LOOKUP_TEST", schema.getIdentifier()); + + schema = this.getDatabase().getSchemaForIdentifier("\"lookup_TEST\""); + assertEquals("\"lookup_TEST\"", schema.getIdentifier()); + assertEquals("\"lookup_TEST\"", schema.getIdentifier("lookup_TEST")); + + this.dropSchema("\"lookup_TEST\""); + this.dropSchema("LOOKUP_TEST"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testTable() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("TABLE_TEST", "FOO_BAZ"); + this.dropTable("TABLE_TEST", "BAZ"); + this.dropTable("TABLE_TEST", "FOO"); + this.dropTable("TABLE_TEST", "BAR"); + this.dropSchema("TABLE_TEST"); + + this.executeUpdate("CREATE SCHEMA TABLE_TEST"); + this.executeUpdate("SET SCHEMA = TABLE_TEST"); + + this.executeUpdate(this.buildBarDDL()); + this.executeUpdate(this.buildFooDDL()); + this.executeUpdate(this.buildBazDDL()); + this.executeUpdate(this.buildFooBazDDL()); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDatabase().getSchemaNamed("TABLE_TEST"); + + // FOO + Table fooTable = schema.getTableNamed("FOO"); + assertEquals(4, fooTable.getColumnsSize()); + assertEquals(1, fooTable.getPrimaryKeyColumnsSize()); + assertEquals(1, fooTable.getForeignKeysSize()); + + Column pkColumn = fooTable.getPrimaryKeyColumn(); + assertEquals("ID", pkColumn.getName()); + Column idColumn = fooTable.getColumnNamed("ID"); + assertSame(pkColumn, idColumn); + assertEquals("INTEGER", idColumn.getDataTypeName()); + assertSame(fooTable, idColumn.getTable()); + assertTrue(idColumn.isPartOfPrimaryKey()); + assertFalse(idColumn.isPartOfForeignKey()); + assertEquals("int", idColumn.getJavaTypeDeclaration()); + + Column nameColumn = fooTable.getColumnNamed("NAME"); + assertEquals("VARCHAR", nameColumn.getDataTypeName()); + assertEquals("java.lang.String", nameColumn.getJavaTypeDeclaration()); + assertEquals(20, nameColumn.getLength()); + assertFalse(nameColumn.isPartOfPrimaryKey()); + assertFalse(nameColumn.isNumeric()); + assertTrue(nameColumn.isNullable()); + + Column barColumn = fooTable.getColumnNamed("BAR_ID"); + assertEquals("INTEGER", barColumn.getDataTypeName()); + assertTrue(barColumn.isPartOfForeignKey()); + assertFalse(barColumn.isPartOfPrimaryKey()); + + Column salaryColumn = fooTable.getColumnNamed("SALARY"); + assertEquals("DECIMAL", salaryColumn.getDataTypeName()); + assertTrue(salaryColumn.isNullable()); + assertTrue(salaryColumn.isNumeric()); + assertEquals(11, salaryColumn.getPrecision()); + assertEquals(2, salaryColumn.getScale()); + assertEquals(-1, salaryColumn.getLength()); + + ForeignKey barFK = fooTable.getForeignKeys().iterator().next(); // there should only be 1 foreign key + assertEquals(1, barFK.getColumnPairsSize()); + assertEquals("BAR", barFK.getAttributeName()); + assertNull(barFK.getJoinColumnAnnotationIdentifier("bar")); + assertEquals("BAR_ID", barFK.getJoinColumnAnnotationIdentifier("primaryBar")); + assertSame(fooTable, barFK.getBaseTable()); + + assertFalse(fooTable.isPossibleJoinTable()); + assertSame(schema, fooTable.getSchema()); + + // BAR + Table barTable = schema.getTableNamed("BAR"); + assertEquals(3, barTable.getColumnsSize()); + assertEquals(1, barTable.getPrimaryKeyColumnsSize()); + assertEquals(0, barTable.getForeignKeysSize()); + assertEquals("ID", barTable.getPrimaryKeyColumn().getName()); + assertFalse(barTable.isPossibleJoinTable()); + + Column id2Column = barTable.getColumnNamed("ID2"); + assertEquals("INTEGER", id2Column.getDataTypeName()); +// assertTrue(id2Column.isPartOfUniqueConstraint()); // doesn't work(?) + assertFalse(id2Column.isNullable()); + assertTrue(id2Column.isNumeric()); + assertEquals(0, id2Column.getPrecision()); // not sure what to expect here... + assertEquals(0, id2Column.getScale()); // not sure what to expect here either... + assertEquals("BLOB", barTable.getColumnNamed("CHUNK").getDataTypeName()); + assertEquals("byte[]", barTable.getColumnNamed("CHUNK").getJavaTypeDeclaration()); + assertTrue(barTable.getColumnNamed("CHUNK").isLOB()); + assertSame(barTable, barFK.getReferencedTable()); + + // BAZ + Table bazTable = schema.getTableNamed("BAZ"); + Column nicknameColumn = bazTable.getColumnNamed("NICKNAME"); + assertEquals(20, nicknameColumn.getLength()); +// assertTrue(nicknameColumn.isPartOfUniqueConstraint()); // doesn't work(?) + + // FOO_BAZ + Table foo_bazTable = schema.getTableNamed("FOO_BAZ"); + assertEquals(2, foo_bazTable.getColumnsSize()); + assertEquals(0, foo_bazTable.getPrimaryKeyColumnsSize()); + assertEquals(2, foo_bazTable.getForeignKeysSize()); + assertTrue(foo_bazTable.isPossibleJoinTable()); + assertTrue(foo_bazTable.joinTableNameIsDefault()); + assertTrue(foo_bazTable.getColumnNamed("FOO_ID").isPartOfForeignKey()); + + this.dropTable("TABLE_TEST", "FOO_BAZ"); + this.dropTable("TABLE_TEST", "BAZ"); + this.dropTable("TABLE_TEST", "FOO"); + this.dropTable("TABLE_TEST", "BAR"); + this.dropSchema("TABLE_TEST"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private static final String CR = System.getProperty("line.separator"); //$NON-NLS-1$ + + private String buildBarDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE BAR (").append(CR); + sb.append(" ID INT PRIMARY KEY,").append(CR); + sb.append(" ID2 INT UNIQUE NOT NULL,").append(CR); + sb.append(" CHUNK BLOB(100K)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE FOO (").append(CR); + sb.append(" ID INT PRIMARY KEY,").append(CR); + sb.append(" NAME VARCHAR(20),").append(CR); + sb.append(" SALARY DECIMAL(11, 2),").append(CR); + sb.append(" BAR_ID INT REFERENCES BAR(ID)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE BAZ (").append(CR); + sb.append(" ID INT PRIMARY KEY,").append(CR); + sb.append(" NICKNAME VARCHAR(20) NOT NULL UNIQUE").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE FOO_BAZ (").append(CR); + sb.append(" FOO_ID INT REFERENCES FOO(ID),").append(CR); + sb.append(" BAZ_ID INT REFERENCES BAZ(ID)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + public void testColumnLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("COLUMN_TEST", "test"); + this.dropSchema("COLUMN_TEST"); + + this.executeUpdate("CREATE SCHEMA COLUMN_TEST"); + this.executeUpdate("SET SCHEMA = COLUMN_TEST"); + + // lowercase + this.executeUpdate("CREATE TABLE test (id INTEGER, name VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Table table = this.getDatabase().getSchemaNamed("COLUMN_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("id")); + assertNotNull(table.getColumnForIdentifier("name")); + + this.dropTable("COLUMN_TEST", "test"); + + // uppercase + this.executeUpdate("CREATE TABLE test (ID INTEGER, NAME VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDatabase().getSchemaNamed("COLUMN_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("ID")); + assertNotNull(table.getColumnForIdentifier("NAME")); + + this.dropTable("COLUMN_TEST", "test"); + + // mixed case + this.executeUpdate("CREATE TABLE test (Id INTEGER, Name VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDatabase().getSchemaNamed("COLUMN_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("Id")); + assertNotNull(table.getColumnForIdentifier("Name")); + + this.dropTable("COLUMN_TEST", "test"); + + // delimited + this.executeUpdate("CREATE TABLE test (\"Id\" INTEGER, \"Name\" VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDatabase().getSchemaNamed("COLUMN_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("\"Id\"")); + assertNotNull(table.getColumnForIdentifier("\"Name\"")); + + this.dropTable("COLUMN_TEST", "test"); + this.dropSchema("COLUMN_TEST"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testCrossSchemaReference() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("XREF_TEST2", "EMP"); + this.dropSchema("XREF_TEST2"); + this.dropTable("XREF_TEST1", "ORG"); + this.dropSchema("XREF_TEST1"); + + this.executeUpdate("CREATE SCHEMA XREF_TEST1"); + this.executeUpdate("SET SCHEMA = XREF_TEST1"); + this.executeUpdate("CREATE TABLE ORG (ID INTEGER PRIMARY KEY, NAME VARCHAR(20))"); + + this.executeUpdate("CREATE SCHEMA XREF_TEST2"); + this.executeUpdate("SET SCHEMA = XREF_TEST2"); + this.executeUpdate("CREATE TABLE EMP (ID INTEGER PRIMARY KEY, NAME VARCHAR(20), " + + "ORG_ID INTEGER REFERENCES XREF_TEST1.ORG(ID))"); + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + Schema schema1 = this.getDatabase().getSchemaNamed("XREF_TEST1"); + assertNotNull(schema1); + Table orgTable = schema1.getTableNamed("ORG"); + assertNotNull(orgTable); + + Schema schema2 = this.getDatabase().getSchemaNamed("XREF_TEST2"); + assertNotNull(schema2); + Table empTable = schema2.getTableNamed("EMP"); + assertNotNull(empTable); + assertEquals(1, empTable.getForeignKeysSize()); + ForeignKey fk = empTable.getForeignKeys().iterator().next(); + Table refTable = fk.getReferencedTable(); + assertNotNull(refTable); + assertEquals("ORG", refTable.getName()); + assertEquals(1, fk.getColumnPairsSize()); + ForeignKey.ColumnPair cp = fk.getColumnPairs().iterator().next(); + Column baseColumn = cp.getBaseColumn(); + assertEquals("ORG_ID", baseColumn.getName()); + Column refColumn = cp.getReferencedColumn(); + assertEquals("ID", refColumn.getName()); + + this.dropTable("XREF_TEST2", "EMP"); + this.dropSchema("XREF_TEST2"); + this.dropTable("XREF_TEST1", "ORG"); + this.dropSchema("XREF_TEST1"); + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private void dropTable(String schemaName, String tableName) throws Exception { + Schema schema= this.getDatabase().getSchemaForIdentifier(schemaName); + if (schema != null) { + if (schema.getTableForIdentifier(tableName) != null) { + this.executeUpdate("DROP TABLE " + schemaName + '.' + tableName); + } + } + } + + /** + * NB: A Derby schema must be empty before it can be dropped. + */ + private void dropSchema(String name) throws Exception { + if (this.getDatabase().getSchemaForIdentifier(name) != null) { + this.executeUpdate("DROP SCHEMA " + name + " RESTRICT"); + } + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/MySQLTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/MySQLTests.java new file mode 100644 index 0000000000..18e945ff81 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/MySQLTests.java @@ -0,0 +1,404 @@ +/******************************************************************************* + * Copyright (c) 2007, 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + +import java.util.Properties; + +import org.eclipse.datatools.connectivity.drivers.jdbc.IJDBCDriverDefinitionConstants; +import org.eclipse.datatools.connectivity.sqm.core.rte.ICatalogObject; +import org.eclipse.jpt.jpa.db.Column; +import org.eclipse.jpt.jpa.db.ForeignKey; +import org.eclipse.jpt.jpa.db.Schema; +import org.eclipse.jpt.jpa.db.Table; + +/** + * MySQL + * + * Notes: + * - We can only get database objects from the database associated with our + * connection profile. + * - We can reference objects across multiple databases, so they are sorta like + * schemas.... + * - Foreign keys must be defined as table-level constraints; they cannot be + * defined as part of the column clause. + * - Case-sensitivity and -folding is whacked on MySQL.... + */ +@SuppressWarnings("nls") +public class MySQLTests extends DTPPlatformTests { + + public MySQLTests( String name) { + super(name); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "mysql.properties"; + } + + @Override + protected String getDriverName() { + return "MySQL JDBC Driver"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.MySQL JDBC Driver"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.enablement.mysql.4_1.driverTemplate"; + } + + @Override + protected String getDatabaseVendor() { + return "MySql"; + } + + @Override + protected String getDatabaseVersion() { + return "4.1"; + } + + @Override + protected String getDriverClass() { + return "com.mysql.jdbc.Driver"; + } + + @Override + protected String getDefaultJDBCURL() { + return "jdbc:mysql://localhost:3306"; + } + + @Override + protected String getProfileName() { + return "MySQL_4.1"; + } + + @Override + protected String getProfileDescription() { + return "MySQL 4.1 JDBC Profile [Test]"; + } + + @Override + protected boolean supportsCatalogs() { + return false; + } + + @Override + protected Properties buildDTPConnectionProfileProperties() { + Properties p = super.buildDTPConnectionProfileProperties(); + p.setProperty(IJDBCDriverDefinitionConstants.DATABASE_NAME_PROP_ID, this.getDatabaseName()); + return p; + } + + private String getDatabaseName() { + return "dalitest"; + } + + @Override + protected boolean executeOfflineTests() { + return true; // seems to work... + } + + public void testDatabase() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + // DTP: MySQL has a single schema with the same name as the database + Schema schema = this.getDatabase().getSchemaNamed(this.getDatabaseName()); + assertNotNull(schema); + assertSame(this.getDefaultSchema(), schema); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testTable() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropDatabase(this.getDatabaseName()); + this.executeUpdate("CREATE DATABASE " + this.getDatabaseName()); + this.getJDBCConnection().setCatalog(this.getDatabaseName()); + + this.dropTable(this.getDatabaseName(), "foo_baz"); + this.dropTable(this.getDatabaseName(), "baz"); + this.dropTable(this.getDatabaseName(), "foo"); + this.dropTable(this.getDatabaseName(), "bar"); + + this.executeUpdate(this.buildBarDDL()); + this.executeUpdate(this.buildFooDDL()); + this.executeUpdate(this.buildBazDDL()); + this.executeUpdate(this.buildFooBazDDL()); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDefaultSchema(); + + // foo + Table fooTable = schema.getTableNamed("foo"); + assertEquals(3, fooTable.getColumnsSize()); + assertEquals(1, fooTable.getPrimaryKeyColumnsSize()); + assertEquals(1, fooTable.getForeignKeysSize()); + + Column pkColumn = fooTable.getPrimaryKeyColumn(); + assertEquals("id", pkColumn.getName()); + Column idColumn = fooTable.getColumnNamed("id"); + assertSame(pkColumn, idColumn); + assertEquals("INT", idColumn.getDataTypeName()); + assertSame(fooTable, idColumn.getTable()); + assertTrue(idColumn.isPartOfPrimaryKey()); + assertFalse(idColumn.isPartOfForeignKey()); + assertEquals("int", idColumn.getJavaTypeDeclaration()); + + Column nameColumn = fooTable.getColumnNamed("name"); + assertEquals("VARCHAR", nameColumn.getDataTypeName()); + assertEquals("java.lang.String", nameColumn.getJavaTypeDeclaration()); + assertFalse(nameColumn.isPartOfPrimaryKey()); + + Column barColumn = fooTable.getColumnNamed("bar_id"); + assertEquals("INT", barColumn.getDataTypeName()); + assertTrue(barColumn.isPartOfForeignKey()); + assertFalse(barColumn.isPartOfPrimaryKey()); + + ForeignKey barFK = fooTable.getForeignKeys().iterator().next(); // there should only be 1 foreign key + assertEquals(1, barFK.getColumnPairsSize()); + assertEquals("bar", barFK.getAttributeName()); + assertNull(barFK.getJoinColumnAnnotationIdentifier("bar")); + assertEquals("bar_id", barFK.getJoinColumnAnnotationIdentifier("primaryBar")); + assertSame(fooTable, barFK.getBaseTable()); + + assertFalse(fooTable.isPossibleJoinTable()); + assertSame(schema, fooTable.getSchema()); + + // BAR + Table barTable = schema.getTableNamed("bar"); + assertEquals(2, barTable.getColumnsSize()); + assertEquals(1, barTable.getPrimaryKeyColumnsSize()); + assertEquals(0, barTable.getForeignKeysSize()); + assertEquals("id", barTable.getPrimaryKeyColumn().getName()); + assertFalse(barTable.isPossibleJoinTable()); + assertEquals("BLOB", barTable.getColumnNamed("chunk").getDataTypeName()); + assertEquals("byte[]", barTable.getColumnNamed("chunk").getJavaTypeDeclaration()); + assertTrue(barTable.getColumnNamed("chunk").isLOB()); + assertSame(barTable, barFK.getReferencedTable()); + + // FOO_BAZ + Table foo_bazTable = schema.getTableNamed("foo_baz"); + assertEquals(2, foo_bazTable.getColumnsSize()); + assertEquals(0, foo_bazTable.getPrimaryKeyColumnsSize()); + assertEquals(2, foo_bazTable.getForeignKeysSize()); + assertTrue(foo_bazTable.isPossibleJoinTable()); + assertTrue(foo_bazTable.joinTableNameIsDefault()); + assertTrue(foo_bazTable.getColumnNamed("foo_id").isPartOfForeignKey()); + + this.dropTable(this.getDatabaseName(), "foo_baz"); + this.dropTable(this.getDatabaseName(), "baz"); + this.dropTable(this.getDatabaseName(), "foo"); + this.dropTable(this.getDatabaseName(), "bar"); + + this.dropDatabase(this.getDatabaseName()); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private static final String CR = System.getProperty("line.separator"); //$NON-NLS-1$ + + private String buildBarDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE bar (").append(CR); + sb.append(" id INTEGER PRIMARY KEY,").append(CR); + sb.append(" chunk BLOB").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE foo (").append(CR); + sb.append(" id INTEGER PRIMARY KEY,").append(CR); + sb.append(" name VARCHAR(20),").append(CR); + sb.append(" bar_id INTEGER,").append(CR); + sb.append(" FOREIGN KEY (bar_id) REFERENCES bar(id)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE baz (").append(CR); + sb.append(" id INTEGER PRIMARY KEY,").append(CR); + sb.append(" name VARCHAR(20)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE foo_baz (").append(CR); + sb.append(" foo_id INT,").append(CR); + sb.append(" baz_id INT,").append(CR); + sb.append(" FOREIGN KEY (foo_id) REFERENCES foo(id),").append(CR); + sb.append(" FOREIGN KEY (baz_id) REFERENCES baz(id)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + /** + * On Windows, table names get folded to lowercase by default; + * even if the name is delimited (apparently). + */ + public void testTableLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropDatabase(this.getDatabaseName()); + this.executeUpdate("CREATE DATABASE " + this.getDatabaseName()); + this.getJDBCConnection().setCatalog(this.getDatabaseName()); + + this.dropTable(this.getDatabaseName(), "test1"); + this.dropTable(this.getDatabaseName(), "TEST2"); + this.dropTable(this.getDatabaseName(), "`TEST3`"); + + this.executeUpdate("CREATE TABLE test1 (id INTEGER, name VARCHAR(20))"); + this.executeUpdate("CREATE TABLE TEST2 (id INTEGER, name VARCHAR(20))"); + this.executeUpdate("CREATE TABLE `TEST3` (id INTEGER, name VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDefaultSchema(); + + Table test1Table = schema.getTableForIdentifier("test1"); + assertNotNull(test1Table); + + // this probably only works on Windows + Table test2Table = schema.getTableForIdentifier("test2"); + assertNotNull(test2Table); + + // this probably only works on Windows + Table test3Table = schema.getTableForIdentifier("`test3`"); + assertNotNull(test3Table); + + this.dropTable(this.getDatabaseName(), "test1"); + this.dropTable(this.getDatabaseName(), "TEST2"); + this.dropTable(this.getDatabaseName(), "`TEST3`"); + + this.dropDatabase(this.getDatabaseName()); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testColumnLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropDatabase(this.getDatabaseName()); + this.executeUpdate("CREATE DATABASE " + this.getDatabaseName()); + this.getJDBCConnection().setCatalog(this.getDatabaseName()); + + this.dropTable(this.getDatabaseName(), "test"); + + // lowercase + this.executeUpdate("CREATE TABLE test (id INTEGER, name VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Table table = this.getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnNamed("id")); + assertNotNull(table.getColumnNamed("name")); + + this.dropTable(this.getDatabaseName(), "test"); + + // uppercase + this.executeUpdate("CREATE TABLE test (ID INTEGER, NAME VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnNamed("ID")); + assertNotNull(table.getColumnNamed("NAME")); + + this.dropTable(this.getDatabaseName(), "test"); + + // mixed case + this.executeUpdate("CREATE TABLE test (Id INTEGER, Name VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnNamed("Id")); + assertNotNull(table.getColumnNamed("Name")); + + this.dropTable(this.getDatabaseName(), "test"); + + // delimited + this.executeUpdate("CREATE TABLE test (`Id` INTEGER, `Name` VARCHAR(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnForIdentifier("`Id`")); + assertNotNull(table.getColumnForIdentifier("`Name`")); + + this.dropTable(this.getDatabaseName(), "test"); + + this.dropDatabase(this.getDatabaseName()); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + /** + * We can only get a single "schema" per connection via DTP, + * so cross-schema references are not visible. + */ + public void testCrossSchemaReference() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropDatabase("xref_test2"); + this.dropDatabase("xref_test1"); + + this.executeUpdate("CREATE DATABASE xref_test1"); + this.getJDBCConnection().setCatalog("xref_test1"); + this.executeUpdate("CREATE TABLE org (id INTEGER PRIMARY KEY, name VARCHAR(20))"); + + this.executeUpdate("CREATE DATABASE xref_test2"); + this.getJDBCConnection().setCatalog("xref_test2"); + this.executeUpdate("CREATE TABLE emp (id INTEGER PRIMARY KEY, name VARCHAR(20), " + + "org_id INTEGER, FOREIGN KEY (org_id) REFERENCES xref_test1.org(id))"); + + this.getJDBCConnection().setCatalog("xref_test2"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + Schema schema2 = this.getDefaultSchema(); + assertNotNull(schema2); + Table empTable = schema2.getTableNamed("emp"); + assertNotNull(empTable); + // no foreign keys + assertEquals(0, empTable.getForeignKeysSize()); + + this.dropDatabase("xref_test2"); + this.dropDatabase("xref_test1"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private void dropTable(String dbName, String tableName) throws Exception { + this.executeUpdate("DROP TABLE IF EXISTS " + dbName + '.' + tableName); + } + + private void dropDatabase(String name) throws Exception { + this.executeUpdate("DROP DATABASE IF EXISTS " + name); + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle10gTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle10gTests.java new file mode 100644 index 0000000000..c4711e8830 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle10gTests.java @@ -0,0 +1,397 @@ +/******************************************************************************* + * Copyright (c) 2007, 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + +import java.sql.SQLException; + +import org.eclipse.datatools.connectivity.sqm.core.rte.ICatalogObject; +import org.eclipse.jpt.jpa.db.Column; +import org.eclipse.jpt.jpa.db.ForeignKey; +import org.eclipse.jpt.jpa.db.Schema; +import org.eclipse.jpt.jpa.db.Table; + +@SuppressWarnings("nls") +public class Oracle10gTests extends DTPPlatformTests { + + public Oracle10gTests( String name) { + super( name); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "oracle10g.properties"; + } + + @Override + protected String getDriverName() { + return "Oracle 10g Thin Driver"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.Oracle Thin Driver"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.enablement.oracle.10.driverTemplate"; + } + + @Override + protected String getDatabaseVendor() { + return "Oracle"; + } + + @Override + protected String getDatabaseVersion() { + return "10"; + } + + @Override + protected String getDriverClass() { + return "oracle.jdbc.OracleDriver"; + } + + @Override + protected String getProfileName() { + return "Oracle10g_10.1.0.4"; + } + + @Override + protected String getProfileDescription() { + return "Oracle10g (10.1.0.4) JDBC Profile [Test]"; + } + + @Override + protected boolean supportsCatalogs() { + return false; + } + + @Override + protected boolean executeOfflineTests() { + // working offline is pretty ugly + return false; + } + + public void testDatabase() throws Exception { + if (this.connectionProfile.getUserName().toUpperCase().equals("SYS")) { + System.out.println("skipped test: " + this.getClass() + '.' + this.getName()); + return; // SYS does not have a schema + } + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + // Oracle should have a schema with the same name as the user + Schema schema = this.getDatabase().getSchemaForIdentifier(this.getUserID()); + assertNotNull(schema); + assertSame(this.getDefaultSchema(), schema); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testTable() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("foo_baz"); + this.dropTable("baz"); + this.dropTable("foo"); + this.dropTable("bar"); + + this.executeUpdate(this.buildBarDDL()); + this.executeUpdate(this.buildFooDDL()); + this.executeUpdate(this.buildBazDDL()); + this.executeUpdate(this.buildFooBazDDL()); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDefaultSchema(); + + // foo + Table fooTable = schema.getTableForIdentifier("foo"); + assertEquals(3, fooTable.getColumnsSize()); + assertEquals(1, fooTable.getPrimaryKeyColumnsSize()); + assertEquals(1, fooTable.getForeignKeysSize()); + + Column pkColumn = fooTable.getPrimaryKeyColumn(); + assertEquals("ID", pkColumn.getName()); + Column idColumn = fooTable.getColumnForIdentifier("id"); + assertSame(pkColumn, idColumn); + assertEquals("NUMBER", idColumn.getDataTypeName()); + assertSame(fooTable, idColumn.getTable()); + assertTrue(idColumn.isPartOfPrimaryKey()); + assertFalse(idColumn.isPartOfForeignKey()); + assertEquals("java.math.BigDecimal", idColumn.getJavaTypeDeclaration()); + + Column nameColumn = fooTable.getColumnForIdentifier("name"); + assertEquals("VARCHAR2", nameColumn.getDataTypeName()); + assertEquals("java.lang.String", nameColumn.getJavaTypeDeclaration()); + assertFalse(nameColumn.isPartOfPrimaryKey()); + + Column barColumn = fooTable.getColumnForIdentifier("bar_id"); + assertEquals("NUMBER", barColumn.getDataTypeName()); + assertTrue(barColumn.isPartOfForeignKey()); + assertFalse(barColumn.isPartOfPrimaryKey()); + + ForeignKey barFK = fooTable.getForeignKeys().iterator().next(); // there should only be 1 foreign key + assertEquals(1, barFK.getColumnPairsSize()); + assertEquals("BAR", barFK.getAttributeName()); + assertNull(barFK.getJoinColumnAnnotationIdentifier("bar")); + assertEquals("BAR_ID", barFK.getJoinColumnAnnotationIdentifier("primaryBar")); + assertSame(fooTable, barFK.getBaseTable()); + + assertFalse(fooTable.isPossibleJoinTable()); + assertSame(schema, fooTable.getSchema()); + + // BAR + Table barTable = schema.getTableForIdentifier("bar"); + assertEquals(2, barTable.getColumnsSize()); + assertEquals(1, barTable.getPrimaryKeyColumnsSize()); + assertEquals(0, barTable.getForeignKeysSize()); + assertEquals("ID", barTable.getPrimaryKeyColumn().getName()); + assertFalse(barTable.isPossibleJoinTable()); + assertEquals("BLOB", barTable.getColumnForIdentifier("chunk").getDataTypeName()); + assertEquals("byte[]", barTable.getColumnForIdentifier("chunk").getJavaTypeDeclaration()); + assertTrue(barTable.getColumnForIdentifier("chunk").isLOB()); + assertSame(barTable, barFK.getReferencedTable()); + + // FOO_BAZ + Table foo_bazTable = schema.getTableForIdentifier("foo_baz"); + assertEquals(2, foo_bazTable.getColumnsSize()); + assertEquals(0, foo_bazTable.getPrimaryKeyColumnsSize()); + assertEquals(2, foo_bazTable.getForeignKeysSize()); + assertTrue(foo_bazTable.isPossibleJoinTable()); + assertTrue(foo_bazTable.joinTableNameIsDefault()); + assertTrue(foo_bazTable.getColumnForIdentifier("foo_id").isPartOfForeignKey()); + + this.dropTable("foo_baz"); + this.dropTable("baz"); + this.dropTable("foo"); + this.dropTable("bar"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private static final String CR = System.getProperty("line.separator"); //$NON-NLS-1$ + + private String buildBarDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE bar (").append(CR); + sb.append(" id NUMBER(10) PRIMARY KEY,").append(CR); + sb.append(" chunk BLOB").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE foo (").append(CR); + sb.append(" id NUMBER(10) PRIMARY KEY,").append(CR); + sb.append(" name VARCHAR2(20),").append(CR); + sb.append(" bar_id REFERENCES bar(id)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE baz (").append(CR); + sb.append(" id NUMBER(10) PRIMARY KEY,").append(CR); + sb.append(" name VARCHAR2(20)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE foo_baz (").append(CR); + sb.append(" foo_id NUMBER(10) REFERENCES foo(id),").append(CR); + sb.append(" baz_id NUMBER(10) REFERENCES baz(id)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + public void testTableLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("test1"); + this.dropTable("TEST2"); + this.dropTable("\"test3\""); + + this.executeUpdate("CREATE TABLE test1 (id NUMBER(10), name VARCHAR2(20))"); + this.executeUpdate("CREATE TABLE TEST2 (id NUMBER(10), name VARCHAR2(20))"); + this.executeUpdate("CREATE TABLE \"test3\" (id NUMBER(10), name VARCHAR2(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDefaultSchema(); + + Table test1Table = schema.getTableForIdentifier("test1"); + assertNotNull(test1Table); + test1Table = schema.getTableForIdentifier("TEST1"); + assertNotNull(test1Table); + + Table test2Table = schema.getTableForIdentifier("test2"); + assertNotNull(test2Table); + test2Table = schema.getTableForIdentifier("TEST2"); + assertNotNull(test2Table); + + Table test3Table = schema.getTableForIdentifier("\"test3\""); + assertNotNull(test3Table); + test3Table = schema.getTableForIdentifier("test3"); + assertNull(test3Table); + + this.dropTable("test1"); + this.dropTable("TEST2"); + this.dropTable("\"test3\""); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testColumnLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("test"); + + // lowercase + this.executeUpdate("CREATE TABLE test (id NUMBER(10), name VARCHAR2(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Table table = this.getDefaultSchema().getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("id")); + assertNotNull(table.getColumnForIdentifier("name")); + + this.dropTable("test"); + + // uppercase + this.executeUpdate("CREATE TABLE test (ID NUMBER(10), NAME VARCHAR2(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultSchema().getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("ID")); + assertNotNull(table.getColumnForIdentifier("NAME")); + + this.dropTable("test"); + + // mixed case + this.executeUpdate("CREATE TABLE test (Id NUMBER(10), Name VARCHAR2(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultSchema().getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("Id")); + assertNotNull(table.getColumnForIdentifier("Name")); + + this.dropTable("test"); + + // delimited + this.executeUpdate("CREATE TABLE test (\"Id\" NUMBER(10), \"Name\" VARCHAR2(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultSchema().getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("\"Id\"")); + assertNotNull(table.getColumnForIdentifier("\"Name\"")); + + this.dropTable("test"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testCrossSchemaReference() throws Exception { + if ( ! this.connectionProfile.getUserName().toUpperCase().equals("SYS")) { + System.out.println("skipped test: " + this.getClass() + '.' + this.getName()); + return; // SYS does not have a schema + } + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.executeUpdateIgnoreErrors("DROP USER XREF_TEST2 CASCADE"); + this.executeUpdateIgnoreErrors("DROP USER XREF_TEST1 CASCADE"); + + this.executeUpdate("CREATE USER XREF_TEST1 IDENTIFIED BY foo"); + this.executeUpdate("ALTER USER XREF_TEST1 QUOTA UNLIMITED ON USERS"); + this.executeUpdate("CREATE TABLE XREF_TEST1.ORG (ID NUMBER(10) PRIMARY KEY, NAME VARCHAR2(20))"); + + this.executeUpdate("CREATE USER XREF_TEST2 IDENTIFIED BY foo"); + this.executeUpdate("ALTER USER XREF_TEST2 QUOTA UNLIMITED ON USERS"); + this.executeUpdate("GRANT ALL ON XREF_TEST1.ORG TO XREF_TEST2"); + this.executeUpdate("CREATE TABLE XREF_TEST2.EMP (ID NUMBER(10) PRIMARY KEY, NAME VARCHAR2(20), " + + "ORG_ID NUMBER(10) REFERENCES XREF_TEST1.ORG(ID))"); + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + Schema schema1 = this.getDatabase().getSchemaNamed("XREF_TEST1"); + assertNotNull(schema1); + Table orgTable = schema1.getTableNamed("ORG"); + assertNotNull(orgTable); + + Schema schema2 = this.getDatabase().getSchemaNamed("XREF_TEST2"); + assertNotNull(schema2); + Table empTable = schema2.getTableNamed("EMP"); + assertNotNull(empTable); + assertEquals(1, empTable.getForeignKeysSize()); + ForeignKey fk = empTable.getForeignKeys().iterator().next(); + Table refTable = fk.getReferencedTable(); + assertNotNull(refTable); + assertEquals("ORG", refTable.getName()); + assertEquals(1, fk.getColumnPairsSize()); + ForeignKey.ColumnPair cp = fk.getColumnPairs().iterator().next(); + Column baseColumn = cp.getBaseColumn(); + assertEquals("ORG_ID", baseColumn.getName()); + Column refColumn = cp.getReferencedColumn(); + assertEquals("ID", refColumn.getName()); + + this.executeUpdate("DROP USER XREF_TEST2 CASCADE"); + this.executeUpdate("DROP USER XREF_TEST1 CASCADE"); + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private void dropTable(String tableName) throws Exception { + this.executeUpdateIgnoreErrors("DROP TABLE " + tableName + " CASCADE CONSTRAINTS"); + } + +// need Oracle enablement plug-in +// public void testSequence() throws Exception { +// this.connectionProfile.connect(); +// TestConnectionListener listener = new TestConnectionListener(); +// this.connectionProfile.addConnectionListener(listener); +// +// this.dropSequence("FOO_SEQ"); +// +// this.executeUpdate("CREATE SEQUENCE FOO_SEQ"); +// ((ICatalogObject) this.getDTPDatabase()).refresh(); +// +// Sequence sequence = this.getDatabase().getDefaultSchema().getSequenceForIdentifier("FOO"); +// assertNotNull(sequence); +// assertEquals("FOO_SEQ", sequence.getName()); +// +// this.dropSequence("FOO_SEQ"); +// +// this.connectionProfile.removeConnectionListener(listener); +// this.connectionProfile.disconnect(); +// } +// +// private void dropSequence(String sequenceName) throws Exception { +// this.executeUpdateIgnoreErrors("DROP SEQUENCE " + sequenceName); +// } +// + protected void dumpUserObjects() throws SQLException { + this.dump("select * from user_objects"); + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle10gXETests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle10gXETests.java new file mode 100644 index 0000000000..d966a9a14b --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle10gXETests.java @@ -0,0 +1,79 @@ +/******************************************************************************* + * Copyright (c) 2007, 2008 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + + +/** + * Oracle 10g Thin Driver Test + */ +@SuppressWarnings("nls") +public class Oracle10gXETests extends DTPPlatformTests { + + public Oracle10gXETests( String name) { + super( name); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "oracle10gXE.properties"; + } + + @Override + protected String getDriverName() { + return "Oracle 10g Thin Driver"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.Oracle Thin Driver"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.enablement.oracle.10.driverTemplate"; + } + + @Override + protected String getDatabaseVendor() { + return "Oracle"; + } + + @Override + protected String getDatabaseVersion() { + return "10"; + } + + @Override + protected String getDriverClass() { + return "oracle.jdbc.OracleDriver"; + } + + @Override + protected String getProfileName() { + return "Oracle10g_XE"; + } + + @Override + protected String getProfileDescription() { + return "Oracle10g XE Release 2 (10.2) JDBC Profile [Test]"; + } + + @Override + protected boolean supportsCatalogs() { + return false; + } + + @Override + protected boolean executeOfflineTests() { + // working offline is pretty ugly + return false; + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle9iTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle9iTests.java new file mode 100644 index 0000000000..102e91970d --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/Oracle9iTests.java @@ -0,0 +1,79 @@ +/******************************************************************************* + * Copyright (c) 2007, 2008 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + + +/** + * Oracle 9i Thin Driver Test + */ +@SuppressWarnings("nls") +public class Oracle9iTests extends DTPPlatformTests { + + public Oracle9iTests( String name) { + super( name); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "oracle9i.properties"; + } + + @Override + protected String getDriverName() { + return "Oracle 9i Thin Driver"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.Oracle Thin Driver"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.enablement.oracle.9.driverTemplate"; + } + + @Override + protected String getDatabaseVendor() { + return "Oracle"; + } + + @Override + protected String getDatabaseVersion() { + return "9"; + } + + @Override + protected String getDriverClass() { + return "oracle.jdbc.OracleDriver"; + } + + @Override + protected String getProfileName() { + return "Oracle9i"; + } + + @Override + protected String getProfileDescription() { + return "Oracle9i JDBC Profile [Test]"; + } + + @Override + protected boolean supportsCatalogs() { + return false; + } + + @Override + protected boolean executeOfflineTests() { + // working offline is pretty ugly + return false; + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/PostgreSQLTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/PostgreSQLTests.java new file mode 100644 index 0000000000..6e501a7942 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/PostgreSQLTests.java @@ -0,0 +1,486 @@ +/******************************************************************************* + * Copyright (c) 2007, 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + +import org.eclipse.datatools.connectivity.sqm.core.rte.ICatalogObject; +import org.eclipse.jpt.jpa.db.Column; +import org.eclipse.jpt.jpa.db.ForeignKey; +import org.eclipse.jpt.jpa.db.Schema; +import org.eclipse.jpt.jpa.db.Table; + +@SuppressWarnings("nls") +public class PostgreSQLTests extends DTPPlatformTests { + + public PostgreSQLTests( String name) { + super( name); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "postgresql.properties"; + } + + @Override + protected String getDriverName() { + return "PostgreSQL JDBC Driver"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.PostgreSQL JDBC Driver"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.enablement.postgresql.postgresqlDriverTemplate"; + } + + @Override + protected String getDatabaseVendor() { + return "postgres"; + } + + @Override + protected String getDatabaseVersion() { + return "8.x"; + } + + @Override + protected String getDriverClass() { + return "org.postgresql.Driver"; + } + + @Override + protected String getDefaultJDBCURL() { + // using this URL will result in the DTP database containing a single + // catalog named "" - which, unfortunately, resembles the pseudo-catalog + // generated by DTP for databases that do not return any catalogs via + // JDBC metadata calls... + return "jdbc:postgresql"; + } + + @Override + protected String getProfileName() { + return "PostgreSQL"; + } + + @Override + protected String getProfileDescription() { + return "PostgreSQL 8.2 JDBC Profile [Test]"; + } + + @Override + protected boolean supportsCatalogs() { + return true; + } + + @Override + protected boolean executeOfflineTests() { + // DTP does not support PostgreSQL off-line - see 226704/241558 + return false; + } + + public void testSchema() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropSchema("TEST1"); + this.dropSchema("TEST2"); + + this.executeUpdate("CREATE SCHEMA TEST1"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema1 = this.getDefaultCatalog().getSchemaForIdentifier("TEST1"); + assertNotNull(schema1); + + this.executeUpdate("CREATE SCHEMA TEST2"); + Schema schema2 = this.getDefaultCatalog().getSchemaForIdentifier("TEST2"); + assertNull(schema2); // should be null until refresh + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + assertSame(this.getDatabase(), listener.changedDatabase); + + schema2 = this.getDefaultCatalog().getSchemaForIdentifier("TEST2"); + assertNotNull(schema2); + assertNotSame(schema1, this.getDefaultCatalog().getSchemaForIdentifier("TEST1")); // we should have a new schema after the refresh + + this.dropSchema("TEST2"); + this.dropSchema("TEST1"); + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testSchemaLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropSchema("LOOKUP_TEST"); + this.dropSchema("\"lookup_TEST\""); + + this.executeUpdate("CREATE SCHEMA LOOKUP_TEST"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + assertNull(this.getDefaultCatalog().getSchemaNamed("LOOKUP_TEST")); + assertNotNull(this.getDefaultCatalog().getSchemaForIdentifier("LOOKUP_TEST")); + + assertNotNull(this.getDefaultCatalog().getSchemaNamed("lookup_test")); + assertNotNull(this.getDefaultCatalog().getSchemaForIdentifier("lookup_test")); + + assertNull(this.getDefaultCatalog().getSchemaNamed("lookup_TEST")); + assertNotNull(this.getDefaultCatalog().getSchemaForIdentifier("lookup_TEST")); + + assertNotNull(this.getDefaultCatalog().getSchemaForIdentifier("\"lookup_test\"")); + assertNull(this.getDefaultCatalog().getSchemaForIdentifier("\"lookup_TEST\"")); + assertNull(this.getDefaultCatalog().getSchemaForIdentifier("\"LOOKUP_TEST\"")); + + this.dropSchema("LOOKUP_TEST"); + + this.executeUpdate("CREATE SCHEMA \"lookup_TEST\""); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + assertNull(this.getDefaultCatalog().getSchemaNamed("LOOKUP_TEST")); + assertNull(this.getDefaultCatalog().getSchemaForIdentifier("LOOKUP_TEST")); + + assertNull(this.getDefaultCatalog().getSchemaNamed("lookup_test")); + assertNull(this.getDefaultCatalog().getSchemaForIdentifier("lookup_test")); + + assertNotNull(this.getDefaultCatalog().getSchemaNamed("lookup_TEST")); + assertNull(this.getDefaultCatalog().getSchemaForIdentifier("lookup_TEST")); + + assertNull(this.getDefaultCatalog().getSchemaForIdentifier("\"LOOKUP_TEST\"")); + assertNotNull(this.getDefaultCatalog().getSchemaForIdentifier("\"lookup_TEST\"")); + + this.dropSchema("\"lookup_TEST\""); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testSchemaIdentifier() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropSchema("LOOKUP_TEST"); + this.dropSchema("\"lookup_TEST\""); + + this.executeUpdate("CREATE SCHEMA lookup_test"); // this gets folded to lowercase + this.executeUpdate("CREATE SCHEMA \"lookup_TEST\""); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDefaultCatalog().getSchemaForIdentifier("LOOKUP_TEST"); + assertEquals("lookup_test", schema.getIdentifier()); + assertEquals("lookup_test", schema.getIdentifier("LookupTest")); + assertNull(schema.getIdentifier("Lookup_Test")); + + schema = this.getDefaultCatalog().getSchemaNamed("lookup_test"); + assertEquals("lookup_test", schema.getIdentifier()); + + schema = this.getDefaultCatalog().getSchemaForIdentifier("\"lookup_TEST\""); + assertEquals("\"lookup_TEST\"", schema.getIdentifier()); + assertEquals("\"lookup_TEST\"", schema.getIdentifier("lookup_TEST")); + + this.dropSchema("\"lookup_TEST\""); + this.dropSchema("LOOKUP_TEST"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testTable() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("TABLE_TEST", "FOO_BAZ"); + this.dropTable("TABLE_TEST", "BAZ"); + this.dropTable("TABLE_TEST", "FOO"); + this.dropTable("TABLE_TEST", "BAR"); + this.dropSchema("TABLE_TEST"); + + this.executeUpdate("CREATE SCHEMA TABLE_TEST"); + this.executeUpdate("SET search_path TO TABLE_TEST"); + + this.executeUpdate(this.buildBarDDL()); + this.executeUpdate(this.buildFooDDL()); + this.executeUpdate(this.buildBazDDL()); + this.executeUpdate(this.buildFooBazDDL()); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDefaultCatalog().getSchemaForIdentifier("TABLE_TEST"); + + // FOO + Table fooTable = schema.getTableForIdentifier("FOO"); + assertEquals(3, fooTable.getColumnsSize()); + assertEquals(1, fooTable.getPrimaryKeyColumnsSize()); + assertEquals(1, fooTable.getForeignKeysSize()); + + Column pkColumn = fooTable.getPrimaryKeyColumn(); + assertEquals("id", pkColumn.getName()); + Column idColumn = fooTable.getColumnForIdentifier("ID"); + assertSame(pkColumn, idColumn); + assertEquals("INT4", idColumn.getDataTypeName()); + assertSame(fooTable, idColumn.getTable()); + assertTrue(idColumn.isPartOfPrimaryKey()); + assertFalse(idColumn.isPartOfForeignKey()); + assertEquals("java.lang.Integer", idColumn.getJavaTypeDeclaration()); + + Column nameColumn = fooTable.getColumnForIdentifier("NAME"); + assertEquals("VARCHAR", nameColumn.getDataTypeName()); + assertEquals("java.lang.String", nameColumn.getJavaTypeDeclaration()); + assertFalse(nameColumn.isPartOfPrimaryKey()); + + Column barColumn = fooTable.getColumnForIdentifier("BAR_ID"); + assertEquals("INT4", barColumn.getDataTypeName()); + assertTrue(barColumn.isPartOfForeignKey()); + assertFalse(barColumn.isPartOfPrimaryKey()); + + ForeignKey barFK = fooTable.getForeignKeys().iterator().next(); // there should only be 1 foreign key + assertEquals(1, barFK.getColumnPairsSize()); + assertEquals("bar", barFK.getAttributeName()); + assertNull(barFK.getJoinColumnAnnotationIdentifier("bar")); + assertEquals("bar_id", barFK.getJoinColumnAnnotationIdentifier("primaryBar")); + assertSame(fooTable, barFK.getBaseTable()); + + assertFalse(fooTable.isPossibleJoinTable()); + assertSame(schema, fooTable.getSchema()); + + // BAR + Table barTable = schema.getTableForIdentifier("BAR"); + assertEquals(2, barTable.getColumnsSize()); + assertEquals(1, barTable.getPrimaryKeyColumnsSize()); + assertEquals(0, barTable.getForeignKeysSize()); + assertEquals("id", barTable.getPrimaryKeyColumn().getName()); + assertFalse(barTable.isPossibleJoinTable()); + assertEquals("BYTEA", barTable.getColumnForIdentifier("CHUNK").getDataTypeName()); + assertEquals("byte[]", barTable.getColumnForIdentifier("CHUNK").getJavaTypeDeclaration()); + // assertTrue(barTable.getColumnForIdentifier("CHUNK").dataTypeIsLOB()); + assertSame(barTable, barFK.getReferencedTable()); + + // FOO_BAZ + Table foo_bazTable = schema.getTableForIdentifier("FOO_BAZ"); + assertEquals(2, foo_bazTable.getColumnsSize()); + assertEquals(0, foo_bazTable.getPrimaryKeyColumnsSize()); + assertEquals(2, foo_bazTable.getForeignKeysSize()); + assertTrue(foo_bazTable.isPossibleJoinTable()); + assertTrue(foo_bazTable.joinTableNameIsDefault()); + assertTrue(foo_bazTable.getColumnForIdentifier("FOO_ID").isPartOfForeignKey()); + + this.dropTable("TABLE_TEST", "FOO_BAZ"); + this.dropTable("TABLE_TEST", "BAZ"); + this.dropTable("TABLE_TEST", "FOO"); + this.dropTable("TABLE_TEST", "BAR"); + this.dropSchema("TABLE_TEST"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private static final String CR = System.getProperty("line.separator"); //$NON-NLS-1$ + + private String buildBarDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE BAR (").append(CR); + sb.append(" ID integer PRIMARY KEY,").append(CR); + sb.append(" CHUNK bytea").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE FOO (").append(CR); + sb.append(" ID integer PRIMARY KEY,").append(CR); + sb.append(" NAME varchar(20),").append(CR); + sb.append(" BAR_ID integer REFERENCES BAR(ID)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE BAZ (").append(CR); + sb.append(" ID integer PRIMARY KEY,").append(CR); + sb.append(" NAME varchar(20)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("CREATE TABLE FOO_BAZ (").append(CR); + sb.append(" FOO_ID int REFERENCES FOO(ID),").append(CR); + sb.append(" BAZ_ID int REFERENCES BAZ(ID)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + public void testColumnLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("TABLE_TEST", "test"); + this.dropSchema("TABLE_TEST"); + + this.executeUpdate("CREATE SCHEMA TABLE_TEST"); + this.executeUpdate("SET search_path TO TABLE_TEST"); + + // lowercase + this.executeUpdate("CREATE TABLE test (id int, name varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Table table = this.getDefaultCatalog().getSchemaForIdentifier("TABLE_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("id")); + assertNotNull(table.getColumnForIdentifier("name")); + + this.dropTable("TABLE_TEST", "test"); + + // uppercase + this.executeUpdate("CREATE TABLE test (ID int, NAME varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultCatalog().getSchemaForIdentifier("TABLE_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("ID")); + assertNotNull(table.getColumnForIdentifier("NAME")); + + this.dropTable("TABLE_TEST", "test"); + + // mixed case + this.executeUpdate("CREATE TABLE test (Id int, Name varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultCatalog().getSchemaForIdentifier("TABLE_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("Id")); + assertNotNull(table.getColumnForIdentifier("Name")); + + this.dropTable("TABLE_TEST", "test"); + + // delimited + this.executeUpdate("CREATE TABLE test (\"Id\" int, \"Name\" varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDefaultCatalog().getSchemaForIdentifier("TABLE_TEST").getTableForIdentifier("test"); + assertNotNull(table.getColumnForIdentifier("\"Id\"")); + assertNotNull(table.getColumnForIdentifier("\"Name\"")); + + this.dropTable("TABLE_TEST", "test"); + this.dropSchema("TABLE_TEST"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testCrossSchemaReference() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.dropTable("XREF_TEST2", "EMP"); + this.dropSchema("XREF_TEST2"); + this.dropTable("XREF_TEST1", "ORG"); + this.dropSchema("XREF_TEST1"); + + this.executeUpdate("CREATE SCHEMA XREF_TEST1"); + this.executeUpdate("SET search_path TO XREF_TEST1"); + this.executeUpdate("CREATE TABLE ORG (ID integer PRIMARY KEY, NAME varchar(20))"); + + this.executeUpdate("CREATE SCHEMA XREF_TEST2"); + this.executeUpdate("SET search_path TO XREF_TEST2"); + this.executeUpdate("CREATE TABLE EMP (ID integer PRIMARY KEY, NAME varchar(20), " + + "ORG_ID integer REFERENCES XREF_TEST1.ORG(ID))"); + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + Schema schema1 = this.getDefaultCatalog().getSchemaNamed("xref_test1"); + assertNotNull(schema1); + Table orgTable = schema1.getTableNamed("org"); + assertNotNull(orgTable); + + Schema schema2 = this.getDefaultCatalog().getSchemaNamed("xref_test2"); + assertNotNull(schema2); + Table empTable = schema2.getTableNamed("emp"); + assertNotNull(empTable); + assertEquals(1, empTable.getForeignKeysSize()); + ForeignKey fk = empTable.getForeignKeys().iterator().next(); + Table refTable = fk.getReferencedTable(); + assertNotNull(refTable); + assertEquals("org", refTable.getName()); + assertEquals(1, fk.getColumnPairsSize()); + ForeignKey.ColumnPair cp = fk.getColumnPairs().iterator().next(); + Column baseColumn = cp.getBaseColumn(); + assertEquals("org_id", baseColumn.getName()); + Column refColumn = cp.getReferencedColumn(); + assertEquals("id", refColumn.getName()); + + this.dropTable("XREF_TEST2", "EMP"); + this.dropSchema("XREF_TEST2"); + this.dropTable("XREF_TEST1", "ORG"); + this.dropSchema("XREF_TEST1"); + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private void dropTable(String schemaName, String tableName) throws Exception { + Schema schema= this.getDefaultCatalog().getSchemaForIdentifier(schemaName); + if (schema != null) { + if (schema.getTableForIdentifier(tableName) != null) { + this.executeUpdate("DROP TABLE " + schemaName + '.' + tableName); + } + } + } + + private void dropSchema(String name) throws Exception { + if (this.getDefaultCatalog().getSchemaForIdentifier(name) != null) { + this.executeUpdate("DROP SCHEMA " + name + " CASCADE"); + } + } + +// see 241578/241557 +// public void testSequence() throws Exception { +// this.connectionProfile.connect(); +// TestConnectionListener listener = new TestConnectionListener(); +// this.connectionProfile.addConnectionListener(listener); +// +// this.dropSequence("SEQUENCE_TEST", "FOO"); +// this.dropSchema("SEQUENCE_TEST"); +// +// this.executeUpdate("CREATE SCHEMA SEQUENCE_TEST"); +// this.executeUpdate("SET search_path TO SEQUENCE_TEST"); +// +// this.executeUpdate(this.buildBarDDL()); +// this.executeUpdate("CREATE SEQUENCE FOO START 1"); +//// List<Object[]> list = this.execute("SELECT nextval('foo')"); +//// System.out.println(list); +// ((ICatalogObject) this.getDTPDatabase()).refresh(); +// +// Schema schema = this.getDefaultCatalog().getSchemaNamed("SEQUENCE_TEST"); +// Sequence sequence = schema.getSequenceNamed("FOO"); +// assertNotNull(sequence); +// assertEquals("foo_seq", sequence.getName()); +// +// this.dropSequence("SEQUENCE_TEST", "FOO"); +// this.dropSchema("SEQUENCE_TEST"); +// +// this.connectionProfile.removeConnectionListener(listener); +// this.connectionProfile.disconnect(); +// } +// +// private void dropSequence(String schemaName, String sequenceName) throws Exception { +// Schema schema= this.getDefaultCatalog().getSchemaNamed(schemaName); +// if (schema != null) { +// if (schema.getSequenceNamed(sequenceName) != null) { +// this.executeUpdate("DROP SEQUENCE " + schemaName + '.' + sequenceName); +// } +// } +// } +// +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/SQLServerTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/SQLServerTests.java new file mode 100644 index 0000000000..346583f282 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/SQLServerTests.java @@ -0,0 +1,83 @@ +/******************************************************************************* + * Copyright (c) 2007, 2008 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + + +/** + * SQL Server 2005 Driver Test + */ +@SuppressWarnings("nls") +public class SQLServerTests extends DTPPlatformTests { + + public SQLServerTests( String name) { + super( name); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "sqlserver.properties"; + } + + @Override + protected String getDriverName() { + return "Microsoft SQL Server 2005 JDBC Driver"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.Microsoft SQL Server 2005 JDBC Driver"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.enablement.msft.sqlserver.2005.driverTemplate"; + } + + @Override + protected String getDatabaseVendor() { + return "SQLServer"; + } + + @Override + protected String getDatabaseVersion() { + return "2005"; + } + + @Override + protected String getDriverClass() { + return "com.microsoft.sqlserver.jdbc.SQLServerDriver"; + } + + @Override + protected String getProfileName() { + return "SQLServer_2005"; + } + + @Override + protected String getProfileDescription() { + return "Microsoft SQL Server 2005 JDBC Profile [Test]"; + } + + @Override + protected String getProviderID() { + return "org.eclipse.datatools.connectivity.db.generic.connectionProfile"; + } + + @Override + protected boolean supportsCatalogs() { + return true; + } + + @Override + protected boolean executeOfflineTests() { + return true; // haven't actually tried this yet... + } + +} diff --git a/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/SybaseTests.java b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/SybaseTests.java new file mode 100644 index 0000000000..ef91d7ab57 --- /dev/null +++ b/jpa/tests/org.eclipse.jpt.jpa.db.tests/src/org/eclipse/jpt/jpa/db/tests/internal/platforms/SybaseTests.java @@ -0,0 +1,443 @@ +/******************************************************************************* + * Copyright (c) 2007, 2009 Oracle. All rights reserved. + * This program and the accompanying materials are made available under the + * terms of the Eclipse Public License v1.0, which accompanies this distribution + * and is available at http://www.eclipse.org/legal/epl-v10.html. + * + * Contributors: + * Oracle - initial API and implementation + ******************************************************************************/ +package org.eclipse.jpt.jpa.db.tests.internal.platforms; + +import org.eclipse.datatools.connectivity.sqm.core.rte.ICatalogObject; +import org.eclipse.jpt.jpa.db.Catalog; +import org.eclipse.jpt.jpa.db.Column; +import org.eclipse.jpt.jpa.db.ForeignKey; +import org.eclipse.jpt.jpa.db.Schema; +import org.eclipse.jpt.jpa.db.Table; + +@SuppressWarnings("nls") +public class SybaseTests extends DTPPlatformTests { + + public SybaseTests( String name) { + super( name); + } + + @Override + protected String getPlatformPropertiesFileName() { + return "sybase.properties"; + } + + @Override + protected String getDriverName() { + return "Sybase JDBC Driver for Sybase ASE 15.x"; + } + + @Override + protected String getDriverDefinitionID() { + return "DriverDefn.Sybase JDBC Driver for Sybase ASE 15.x"; + } + + @Override + protected String getDriverDefinitionType() { + return "org.eclipse.datatools.connectivity.db.sybase.ase.genericDriverTemplate_15"; + } + + @Override + protected String getDatabaseVendor() { + return "Sybase_ASE"; + } + + @Override + protected String getDatabaseVersion() { + return "15.x"; + } + + @Override + protected String getDriverClass() { + return "com.sybase.jdbc3.jdbc.SybDriver"; + } + + @Override + protected String getProfileName() { + return "Sybase_15"; + } + + @Override + protected String getProfileDescription() { + return "Sybase ASE 15 jConnect JDBC Profile [Test]"; + } + + @Override + protected boolean supportsCatalogs() { + return true; + } + + @Override + protected boolean executeOfflineTests() { + // working offline is pretty ugly + return false; + } + + /** + * Sybase "databases" become DTP "catalogs" + */ + public void testCatalog() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdateIgnoreErrors("drop database test1"); + this.executeUpdateIgnoreErrors("drop database test2"); + + this.executeUpdate("create database test1"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Catalog catalog1 = this.getDatabase().getCatalogNamed("test1"); + assertNotNull(catalog1); + Schema schema1 = catalog1.getSchemaNamed("dbo"); + assertNotNull(schema1); + assertSame(schema1, catalog1.getDefaultSchema()); + + this.executeUpdate("create database test2"); + Catalog catalog2 = this.getDatabase().getCatalogNamed("test2"); + assertNull(catalog2); // should be null until refresh + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + assertSame(this.getDatabase(), listener.changedDatabase); + + catalog2 = this.getDatabase().getCatalogNamed("test2"); + assertNotNull(catalog2); + Schema schema2 = catalog2.getDefaultSchema(); + assertNotNull(schema2); + + assertNotSame(catalog1, this.getDatabase().getCatalogNamed("test1")); // we should have a new catalog after the refresh + assertNotSame(schema1, this.getDatabase().getCatalogNamed("test1").getDefaultSchema()); // we should have a new schema after the refresh + + this.executeUpdate("drop database test2"); + this.executeUpdate("drop database test1"); + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testTable() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdateIgnoreErrors("drop database table_test"); + this.executeUpdate("create database table_test"); + this.getJDBCConnection().setCatalog("table_test"); + + this.executeUpdate(this.buildBarDDL()); + this.executeUpdate(this.buildFooDDL()); + this.executeUpdate(this.buildBazDDL()); + this.executeUpdate(this.buildFooBazDDL()); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDatabase().getCatalogNamed("table_test").getDefaultSchema(); + + // foo + Table fooTable = schema.getTableNamed("foo"); + assertEquals(3, fooTable.getColumnsSize()); + assertEquals(1, fooTable.getPrimaryKeyColumnsSize()); + assertEquals(1, fooTable.getForeignKeysSize()); + + Column pkColumn = fooTable.getPrimaryKeyColumn(); + assertEquals("id", pkColumn.getName()); + Column idColumn = fooTable.getColumnNamed("id"); + assertSame(fooTable, idColumn.getTable()); + assertSame(pkColumn, idColumn); + assertTrue(idColumn.isPartOfPrimaryKey()); + assertFalse(idColumn.isPartOfForeignKey()); + assertTrue(idColumn.isPartOfUniqueConstraint()); + assertFalse(idColumn.isNullable()); + + assertEquals("INT", idColumn.getDataTypeName()); + assertTrue(idColumn.isNumeric()); + assertEquals(0, idColumn.getPrecision()); + assertEquals(0, idColumn.getScale()); + assertEquals(-1, idColumn.getLength()); + assertFalse(idColumn.isLOB()); + assertEquals("int", idColumn.getJavaTypeDeclaration()); + + Column nameColumn = fooTable.getColumnNamed("name"); + assertFalse(nameColumn.isPartOfPrimaryKey()); + assertFalse(nameColumn.isPartOfForeignKey()); + assertTrue(nameColumn.isPartOfUniqueConstraint()); + assertFalse(nameColumn.isNullable()); // implied "NOT NULL" ? + + assertEquals("VARCHAR", nameColumn.getDataTypeName()); + assertFalse(nameColumn.isNumeric()); + assertEquals(-1, nameColumn.getPrecision()); + assertEquals(-1, nameColumn.getScale()); + assertEquals(20, nameColumn.getLength()); + assertFalse(nameColumn.isLOB()); + assertEquals("java.lang.String", nameColumn.getJavaTypeDeclaration()); + + Column barColumn = fooTable.getColumnNamed("bar_id"); + assertEquals("INT", barColumn.getDataTypeName()); + assertTrue(barColumn.isPartOfForeignKey()); + assertFalse(barColumn.isPartOfPrimaryKey()); + + ForeignKey barFK = fooTable.getForeignKeys().iterator().next(); // there should only be 1 foreign key + assertEquals(1, barFK.getColumnPairsSize()); + assertEquals("bar", barFK.getAttributeName()); + assertNull(barFK.getJoinColumnAnnotationIdentifier("bar")); + assertEquals("bar_id", barFK.getJoinColumnAnnotationIdentifier("primaryBar")); + assertSame(fooTable, barFK.getBaseTable()); + + assertFalse(fooTable.isPossibleJoinTable()); + assertSame(schema, fooTable.getSchema()); + + // BAR + Table barTable = schema.getTableNamed("bar"); + assertEquals(2, barTable.getColumnsSize()); + assertEquals(1, barTable.getPrimaryKeyColumnsSize()); + assertEquals(0, barTable.getForeignKeysSize()); + assertEquals("id", barTable.getPrimaryKeyColumn().getName()); + assertFalse(barTable.isPossibleJoinTable()); + Column chunkColumn = barTable.getColumnNamed("chunk"); + assertEquals("IMAGE", chunkColumn.getDataTypeName()); + assertFalse(chunkColumn.isNumeric()); + assertTrue(chunkColumn.isLOB()); + assertEquals("byte[]", chunkColumn.getJavaTypeDeclaration()); + assertSame(barTable, barFK.getReferencedTable()); + + // BAZ + Table bazTable = schema.getTableNamed("baz"); + assertEquals(4, bazTable.getColumnsSize()); + assertEquals(1, bazTable.getPrimaryKeyColumnsSize()); + assertEquals(0, bazTable.getForeignKeysSize()); + + Column nicknameColumn = bazTable.getColumnNamed("nickname"); + assertTrue(nicknameColumn.isNullable()); + + Column songColumn = bazTable.getColumnNamed("song"); + assertFalse(songColumn.isNullable()); + + Column salaryColumn = bazTable.getColumnNamed("salary"); + assertFalse(salaryColumn.isPartOfUniqueConstraint()); + assertEquals("DECIMAL", salaryColumn.getDataTypeName()); + assertTrue(salaryColumn.isNumeric()); + assertEquals(10, salaryColumn.getPrecision()); + assertEquals(2, salaryColumn.getScale()); + assertEquals(-1, salaryColumn.getLength()); + assertFalse(salaryColumn.isLOB()); + + // FOO_BAZ + Table foo_bazTable = schema.getTableNamed("foo_baz"); + assertEquals(2, foo_bazTable.getColumnsSize()); + assertEquals(0, foo_bazTable.getPrimaryKeyColumnsSize()); + assertEquals(2, foo_bazTable.getForeignKeysSize()); + assertTrue(foo_bazTable.isPossibleJoinTable()); + assertTrue(foo_bazTable.joinTableNameIsDefault()); + assertTrue(foo_bazTable.getColumnNamed("foo_id").isPartOfForeignKey()); + + this.executeUpdate("drop table foo_baz"); + this.executeUpdate("drop table baz"); + this.executeUpdate("drop table foo"); + this.executeUpdate("drop table bar"); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdate("drop database table_test"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + private static final String CR = System.getProperty("line.separator"); //$NON-NLS-1$ + + private String buildBarDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("create table bar (").append(CR); + sb.append(" id integer primary key,").append(CR); + sb.append(" chunk image").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("create table foo (").append(CR); + sb.append(" id integer primary key,").append(CR); + sb.append(" name varchar(20) unique,").append(CR); + sb.append(" bar_id integer references bar(id)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("create table baz (").append(CR); + sb.append(" id integer primary key,").append(CR); + sb.append(" nickname varchar(20) null,").append(CR); + sb.append(" song varchar(20) not null,").append(CR); + sb.append(" salary decimal(10, 2)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + private String buildFooBazDDL() { + StringBuilder sb = new StringBuilder(200); + sb.append("create table foo_baz (").append(CR); + sb.append(" foo_id integer references foo(id),").append(CR); + sb.append(" baz_id integer references baz(id)").append(CR); + sb.append(")").append(CR); + return sb.toString(); + } + + public void testTableLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdateIgnoreErrors("drop database table_lookup_test"); + this.executeUpdate("create database table_lookup_test"); + this.getJDBCConnection().setCatalog("table_lookup_test"); + + this.executeUpdate("create table test1 (id integer, name varchar(20))"); + this.executeUpdate("create table TEST2 (id integer, name varchar(20))"); + this.executeUpdate("create table [Test3] (id integer, name varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Schema schema = this.getDatabase().getCatalogNamed("table_lookup_test").getDefaultSchema(); + + assertNotNull(schema.getTableNamed("test1")); + assertNotNull(schema.getTableForIdentifier("test1")); + + assertNotNull(schema.getTableNamed("TEST2")); + assertNotNull(schema.getTableForIdentifier("TEST2")); + + assertNotNull(schema.getTableForIdentifier("[Test3]")); + + this.executeUpdate("drop table [Test3]"); + this.executeUpdate("drop table TEST2"); + this.executeUpdate("drop table test1"); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdate("drop database table_lookup_test"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testColumnLookup() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdateIgnoreErrors("drop database column_lookup_test"); + this.executeUpdate("create database column_lookup_test"); + this.getJDBCConnection().setCatalog("column_lookup_test"); + + // lowercase + this.executeUpdate("create table test (id integer, name varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + Table table = this.getDatabase().getCatalogNamed("column_lookup_test").getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnNamed("id")); + assertNotNull(table.getColumnNamed("name")); + + this.executeUpdate("drop table test"); + + // uppercase + this.executeUpdate("create table test (ID integer, NAME varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDatabase().getCatalogNamed("column_lookup_test").getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnForIdentifier("ID")); + assertNotNull(table.getColumnForIdentifier("NAME")); + + this.executeUpdate("drop table test"); + + // mixed case + this.executeUpdate("create table test (Id integer, Name varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDatabase().getCatalogNamed("column_lookup_test").getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnForIdentifier("Id")); + assertNotNull(table.getColumnForIdentifier("Name")); + + this.executeUpdate("drop table test"); + + // delimited + this.executeUpdate("create table test ([Id] integer, [Name] varchar(20))"); + ((ICatalogObject) this.getDTPDatabase()).refresh(); + + table = this.getDatabase().getCatalogNamed("column_lookup_test").getDefaultSchema().getTableNamed("test"); + assertNotNull(table.getColumnForIdentifier("[Id]")); + assertNotNull(table.getColumnForIdentifier("[Name]")); + + this.executeUpdate("drop table test"); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdate("drop database column_lookup_test"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + + public void testCrossSchemaReference() throws Exception { + this.connectionProfile.connect(); + TestConnectionListener listener = new TestConnectionListener(); + this.connectionProfile.addConnectionListener(listener); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdateIgnoreErrors("drop database xref_test2"); + this.executeUpdateIgnoreErrors("drop database xref_test1"); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdate("create database xref_test1"); + this.getJDBCConnection().setCatalog("xref_test1"); + this.executeUpdate("create table org (id integer primary key, name varchar(20))"); + + this.getJDBCConnection().setCatalog("master"); + this.executeUpdate("create database xref_test2"); + this.getJDBCConnection().setCatalog("xref_test2"); + this.executeUpdate("create table emp (id integer primary key, name varchar(20), " + + "org_id integer references xref_test1..org(id))"); + + ((ICatalogObject) this.getDTPDatabase()).refresh(); + Catalog catalog1 = this.getDatabase().getCatalogNamed("xref_test1"); + assertNotNull(catalog1); + Schema schema1 = catalog1.getSchemaNamed("dbo"); + assertNotNull(schema1); + Table orgTable = schema1.getTableNamed("org"); + assertNotNull(orgTable); + + Catalog catalog2 = this.getDatabase().getCatalogNamed("xref_test2"); + assertNotNull(catalog2); + Schema schema2 = catalog2.getSchemaNamed("dbo"); + assertNotNull(schema2); + Table empTable = schema2.getTableNamed("emp"); + assertNotNull(empTable); + assertEquals(1, empTable.getForeignKeysSize()); + ForeignKey fk = empTable.getForeignKeys().iterator().next(); + Table refTable = fk.getReferencedTable(); + assertNotNull(refTable); + assertEquals("org", refTable.getName()); + assertEquals(1, fk.getColumnPairsSize()); + ForeignKey.ColumnPair cp = fk.getColumnPairs().iterator().next(); + Column baseColumn = cp.getBaseColumn(); + assertEquals("org_id", baseColumn.getName()); + Column refColumn = cp.getReferencedColumn(); + assertEquals("id", refColumn.getName()); + + this.getJDBCConnection().setCatalog("xref_test2"); + this.executeUpdate("drop table emp"); + this.getJDBCConnection().setCatalog("xref_test1"); + this.executeUpdate("drop table org"); + this.getJDBCConnection().setCatalog("master"); + this.executeUpdate("drop database xref_test2"); + this.executeUpdate("drop database xref_test1"); + + this.connectionProfile.removeConnectionListener(listener); + this.connectionProfile.disconnect(); + } + +} |