[Derby-573]Optimizer overrides and metadata.properties files

[Mamta Satoor]

Hi,

 

Derby has an internal support for optimizer overrides which is non-standard and I am working on replacing it with more portable syntax. The new syntax (as discussed in the JIRA entry and email thread titled Optimizer overrides - functional spec) will be

-- DERBY-PROPERTIES propertyName = value [, propertyName = value]*

This is an extension of existing comments support and this syntax assumes that everything defined on that line after -- DERBY-PROPERTIES is a bunch of key=values pairs. If that rule is not followed, a syntax error will be thrown. This rule causes problems with existing metadata.properties file.

 

The existing internal, non-standard optimizer overrides support is used in metadata.properties which allows overrides to co-exist with the rest of the sql and metadata.properties relies on that support.

Part of one example query in metadata.properties is 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 PROPERTIES joinOrder=FIXED \
   SYS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', \
   SYS.SYSSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1',  \
   SYS.SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3',  \
   SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1', \
   SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \
   SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1'
 

When Derby reads these queries into the engine as java.util.Properties as Properties.load() method, the \ at the end is ignored and all the lines in metadata.properties for getPrimaryKeys get concatenated as big string value. Part of the above sql after concatenation looks as follows

... FROM PROPERTIES joinOrder=FIXED SYS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', SYS.SYSSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1',  SYS.SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3',   SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1', SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1'
 

As you can see, after PROPERTIES key=value, there is non-properties related sql, then more PROPERTIES and so on. And this is fine with the old syntax

 

I tried modifying metadata.properties to use the new optimizer override syntax (by replacing the PROPERTIES with --DERBY-PROPERTIES). But just replacing the old PROPERTIES with --DERBY-PROPERTIES is not enough. The concatenation of lines has caused key=value be followed by regular sql followed by more properties causes new optimizer override to throw syntax errors. eg

... FROM --DERBY-PROPERTIES joinOrder=FIXED SYS.SYSTABLES T --DERBY-PROPERTIES index='SYSTABLES_INDEX1', SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1',  SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3',   SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1', SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1'
 

I am planning on fixing this by adding ~ before \ in the metadata.properties on the lines where PROPERTIES are getting defined, as shown in the eg sql below

 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 PROPERTIES joinOrder=FIXED ~\
   SYS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', ~\
   SYS.SYSSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1',  ~\
   SYS.SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3',  ~\
   SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1', ~\
   SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', ~\
   SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1' 
And then, later on, when this gets read into a java.util.Properties object, I go through the value for the key and replace all ~ with a new line. This will make sure that there is a new line after key=value pairs which is what the new overrides syntax is looking for.

 

If anyone has ideas about a better way of resolving this, please let me know.

 

thanks,

Mamta