db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject [Derby-573]Optimizer overrides and metadata.properties files
Date Fri, 28 Oct 2005 07:52:55 GMT
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.SYSCOLUMNSCOLS 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.SYSCONGLOMERATESCONGLOMS --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

Mime
View raw message