| Extending tables looks up in wrong schema / catalog [message #1849150] |
Wed, 05 January 2022 14:54 |
 Thorsten Hülß
Messages: 1
Registered: January 2022 |
Junior Member |
|
|
I have the following problem with accessing a MySQL database via JPA / EclipseLink:
My MySQL server has multiple schemas, each representing an instance of the same database structure (i.e. each schema has the same tables - the schemas are just for different customers).
In my Eclipse preferences I configure which schema to use at runtime and start my application. So far, this worked well.
Now I added some fields to one of my entities and want JPA to reflect this change by executing some "ALTER TABLE ADD ..." command on the respective table in the current database schema. This still worked well for the first schema, the new columns can be seen in the database and can be accessed.
However, after I changed to the second schema and restarted the application, the new columns are NOT created in the table of the second schema. I wondered why, and tracked the problem down to the function "extendTables" in org.eclipse.persistence.tools.schemaframework.TableCreator. This function boils down to the following (already shortened):
/**
* This creates/extends the tables on the database.
*/
public void extendTables(DatabaseSession session, SchemaManager schemaManager, boolean build) {
for (TableDefinition table : getTableDefinitions()) {
AbstractSession abstractSession = (AbstractSession) session;
...
if (alreadyExists) {
//Assume the table exists, so lookup the column info
String tableName = table.getTable()==null? table.getName(): table.getTable().getName();
List<DatabaseRecord> columnInfo = null;
// The following line looks up the column infos of ALL schemas!
columnInfo = abstractSession.getAccessor().getColumnInfo(null, null, tableName, null, abstractSession);
// hash the existing columns by name
Map<DatabaseField, DatabaseRecord> columns = new HashMap(columnInfo.size());
DatabaseField columnNameLookupField = new DatabaseField("COLUMN_NAME");
DatabaseField schemaLookupField = new DatabaseField("TABLE_SCHEM");
boolean schemaMatchFound = false;
// Determine the probably schema for the table, this is a heuristic, so should not cause issues if wrong.
String qualifier = table.getQualifier();
if ((qualifier == null) || (qualifier.length() == 0)) {
qualifier = session.getDatasourcePlatform().getTableQualifier();
}
boolean checkSchema = (qualifier != null) && (qualifier.length() > 0);
for (DatabaseRecord record : columnInfo) {
String fieldName = (String)record.get(columnNameLookupField);
if (fieldName != null && fieldName.length() > 0) {
DatabaseField column = new DatabaseField(fieldName);
String schema = (String)record.get(schemaLookupField);
// Check the schema as well. Ignore columns for other schema if a schema match is found.
if (schemaMatchFound) {
if (qualifier.equalsIgnoreCase(schema)) {
columns.put(column, record);
}
} else {
if (checkSchema) {
if (qualifier.equalsIgnoreCase(schema)) {
schemaMatchFound = true;
// Remove unmatched columns from other schemas.
columns.clear();
}
}
// If none of the schemas match what is expected, assume what is expected is wrong, and use all columns.
columns.put(column, record);
}
}
}
//Go through each field we need to have in the table to see if it already exists
for (FieldDefinition fieldDef : table.getFields()){
DatabaseField dbField = fieldDef.getDatabaseField();
if (columns.get(dbField)== null) {
//field does not exist so add it to the table
table.addFieldOnDatabase(abstractSession, fieldDef);
}
}
}
}
}
As it seems, the function indeed looks up the columns in ALL schemas. Then, it tries to filter out those that belong to the wrong schemas, comparing the desired schema to the information from "TABLE_SCHEM".
This looks like just what I want - except that the MySQL "schema" is translated to a "catalog" by JDBC, i.e. the information is stored in "TABLE_CAT" while "TABLE_SCHEM" is null. And "TABLE_CAT" is nowhere evaluated.
Can somebody see what I am getting wrong here and what would be a proper solution? Thank you in advance!
(Note: I asked the same question on stackoverflow, but did not yet get an answer: stackoverflow.com/questions/70589402/extending-tables-of-specific-mysql-schema-chosen-at-runtime-with-jpa ).
|
|
|
Powered by
FUDForum. Page generated in 0.04213 seconds
] 
Search
Help
Register
Login
Home
