[Derby-573]Upgrade code and metadata.properties

[Mamta Satoor <ms...@gmail.com>]

Hi,
 I am working on writing the upgrade code for metadata.properties changes
because of optimizer overrides syntax change. Also, I am trying to see if I
can use org.apache.derbyTesting.upgradeTests.phaseTester (checked in by Dan
couple months back) to test this particular upgrade scenario.
 The changes for hard upgrade is not so bad because in
DD_version.doFullUpgrade(), I can drop the stored prepared statements (using
the method dropJDBCMetadataSPSes) and then recreate them.
 I am still struggling with soft upgrade, though. In soft upgrade mode, the
sysstatements table will get metadata queries using old optimizer override
syntax which is not recognized by 10.2 but because we are in soft upgrade
mode, I can't update the sysstatements table to use the new syntax. So,
somehow, I need to use the queries with the new syntax but w/o updating the
system table so the pre-10.2 database can be used by pre-10.2 derby release.
I decided to tackle this issue by avoiding going to sysstatements table for
few of these DatabaseMetaData calls(which use the optimizer overrides in
their sql) and instead, just read the sql from metadata.properties and
execute the sql directly. But that does not work very well because some of
the metadata.properties sql uses syntax that is available to Derby engine
internally only. And when I try to run that sql by directly reading from
metadata.properties, I get syntax error.
 Here is the code snippet for getPrimaryKeys in EmbeddedDatabaseMetadata
after my changes
 public ResultSet getPrimaryKeys(String catalog, String schema,
String table) throws SQLException {
//check if the dictionary is at 10.2 revision. If not, then that means
//stored prepared statements for metadata calls are using the old
//optimizer override syntax which is not recognized by 10.2 egnine.
//This can happen if we are in soft upgrade mode. Since in soft
//upgrade mode, we can't change the system tables in an backward
//incompatible way, I am going to try to read the metadata sql from
//metadata.properties file rather than rely on system tables.
boolean newOptimizerOverridesSyntaxSupported;
try {
newOptimizerOverridesSyntaxSupported =
getLanguageConnectionContext().getDataDictionary().checkVersion(
DataDictionary.DD_VERSION_DERBY_10_2,null);
} catch (Throwable t) {
throw handleException(t);
}

//We can safely goto system table since data dictionary is at 10.2
//and hence is using new optimizer overrides syntax.
if (newOptimizerOverridesSyntaxSupported)
return doGetPrimaryKeys(catalog, schema, table, "getPrimaryKeys");
else
{
//Can't use stored prepared statements because they don't use the new
//new optimizer override syntax. Need to read the sql from
metadata.properties
synchronized (getConnectionSynchronization()) {
setupContextStack();
ResultSet rs = null;
try {
String queryText = getQueryDescriptions().getProperty("getPrimaryKeys");
PreparedStatement s =
getEmbedConnection().prepareMetaDataStatement(queryText);

s.setString(1, swapNull(catalog));
s.setString(2, swapNull(schema));
s.setString(3, swapNull(table));

rs = s.executeQuery();
} catch (Throwable t) {
throw handleException(t);
} finally {
restoreContextStack();
}

return rs;
}
}
}

The sql from getPrimaryKeys in metadata.properties looks as follows

getPrimaryKeys=\
SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \
S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, \
COLS.COLUMNNAME AS COLUMN_NAME, \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS KEY_SEQ, \
CONS.CONSTRAINTNAME AS PK_NAME \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n \
SYS.SYSTABLES T --DERBY-PROPERTIES index='SYSTABLES_INDEX1' \n\
, SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSSCHEMAS_INDEX1' \n\
, SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSCONSTRAINTS_INDEX3' \n\
, SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP,
index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSCOLUMNS_INDEX1' \n\
WHERE ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME LIKE
? AND \
T.SCHEMAID = S.SCHEMAID AND \
T.TABLEID = COLS.REFERENCEID AND T.TABLEID = CONGLOMS.TABLEID AND \
CONS.TABLEID = T.TABLEID AND CONS.TYPE = 'P' AND \
CONS.CONSTRAINTID = KEYS.CONSTRAINTID AND \
(CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \
0 END) <> 0 AND \
KEYS.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \
ORDER BY COLUMN_NAME

The line number 4 of the sql is CONGLOMS.DESCRIPTOR.getKeyColumnPosition(
COLS.COLUMNNUMBER) AS KEY_SEQ which gives syntax error when I am trying to
run metadata query as prepared statement rather than stored prepared
statement. Any help in getting over this hump will be great. One drawback I
see with this possible solution is that every call to getPrimaryKeys
in 10.2will now check if the database is in soft upgrade mode and 99%
of the time,
that will not be the case. So, there is this additional check which might
have some small performance issue. But the plus point is that not all the
metadata calls have to do this check, only the ones that use optimizer
overrides in their sql. Those metadata calls are getCrossReference,
getImportedKeys, getPrimaryKeys.
thanks,
Mamta

Re: [Derby-573]Upgrade code and metadata.properties

[Daniel John Debrunner <dj...@debrunners.com>]

Mamta Satoor wrote:

> Hi,
>  
> I am working on writing the upgrade code for metadata.properties changes
> because of optimizer overrides syntax change. Also, I am trying to see
> if I can use org.apache.derbyTesting.upgradeTests.phaseTester (checked
> in by Dan couple months back) to test this particular upgrade scenario.
>  
> The changes for hard upgrade is not so bad because in
> DD_version.doFullUpgrade(), I can drop the stored prepared statements
> (using the method  dropJDBCMetadataSPSes) and then recreate them.
>  
> I am still struggling with soft upgrade, though. In soft upgrade mode,
> the sysstatements table will get metadata queries using old optimizer
> override syntax which is not recognized by 10.2 but because we are in
> soft upgrade mode, I can't update the sysstatements table to use the new
> syntax. So, somehow, I need to use the queries with the new syntax but
> w/o updating the system table so the pre-10.2 database can be used by
> pre-10.2 derby release. I decided to tackle this issue by avoiding going
> to sysstatements table for few of these DatabaseMetaData calls(which use
> the optimizer overrides in their sql) and instead, just read the sql
> from metadata.properties and execute the sql directly.

Assuming you can fix the "internal SQL" problem, a more generic solution
would be to always use the new sql from metadata.properties when in
soft-upgrade mode. That would help out future developers by removing
soft-upgrade as an issue for all metadata.

Dan.