Possible Issue with multiple schemas on a single database instance

[priyapravas <pr...@infosys.com>]

Guys,
 We ran a sample test for inserting a row each in tables with the same name (LOCALIZED_ADDRESSES) but existing in two different schemas (with different set of fields) and ran into a possible issue. We cannot have schemas with similar named tables running on the same instance of a database. 


It turns out that If you insert a Localized Adddress in say one schema, and then try to insert another Localized address in the other schema: it fails indicating mis-match in the columns(which is not true). 

After debugging it all boiled down to 4 classes BasePeer (torque) and 3 Village classes: Record/Schema/TableDataset. The following piece of code is for reference:

#BasePeer.java.doInsert(Criteria,Connection)
TableDataSet tds = new TableDataSet(con,tableName);
Record record = tds.addRecord();

Now, a TableDataSet internally creates the Schema for the tableName. This Schema maintains a 'static' Hashtable of already created Schemas for given tableName and database. The key created is a combination of connection.getMetaData().getURL() + tableName. This, however, is not unique for schemas on the same database instance. Because of this the second insert will get the LocalizedAddresses schema of the previous schema instead of the latter and thus the error. 

Do you think it's OK to have con.getURL() as a unique identifier or should it be more specific like connection.getMetaData().getUserName() for unique identification?


Any suggestions?