Yes, Satheesh, you are right. I have been primarily focusing on getPrimaryKeys as an example and I see that it is being saved in sys.sysstatements and that's how I started to think about upgrade.
Army, thanks for your time on describing the codepath. I was under the impression that all the metadata queries get saved in the database, but looks like not.
Now, that we are back to the upgrade issue, I guess I need to figure out what should be done for soft upgrade. With my optimizer override patch, there is no support for old overrides syntax (in 10.2) which is what the metadata queries would have during soft upgrade. I am not sure at this point, how would I handle those old format metadata queries because w/o the new syntax,
10.2 would raise errors for metadata calls because the 10.2 parser doesn't recognize the old format anymore.
thanks,
Mamta
On 11/11/05, Satheesh Bandaram <[EMAIL PROTECTED]> wrote:
Thanks Army for the good description... But I do think there are SOME metadata calls that are stored in the database...
Try this:
ij> select stmtname, text from sys.sysstatements where text like '%PROPERTIES%';
STMTNAME
|TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------
getPrimaryKeys
|SELECT CAST ('' AS VARCHAR(128)
) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, COLS.COL
UMNNAME AS COLUMN_NAME, CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMB
ER) AS KEY_SEQ, CONS.CONSTRAINTNAME AS PK_NAME FROM PROPERTIES joinOrder=FIXED S
YS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', SYS.SYSSCHEMAS S PROPERTIES
joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1', SYS.SYSCONSTRAINTS CONS PR
OPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3', SYS.SYSKEYS K
EYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1', SYS.SYSCONGLOME
RATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_IND
EX1', SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS
_INDEX1' 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.TABLE
ID = CONGLOMS.TABLEID AND CONS.TABLEID = T.TABLEID AND CONS.TYPE = 'P' AND CONS.
CONSTRAIN&
getCrossReference
|SELECT CAST ('' AS VARCHAR(128)
) AS PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, CAST ('' AS VARCHA
R(128)) AS FKTABLE_CAT, S2.SCHEMANAME AS FKTABLE_SCHEM, T2.TABLENAME AS FKTABLE_
NAME, COLS2.COLUMNNAME AS FKCOLUMN_NAME, CAST (CONGLOMS2.DESCRIPTOR.getKeyColumn
Position( COLS2.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, CAST ((CASE WHEN F2.UPDAT
ERULE='S' THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE (CASE WHEN F
2.UPDATERULE='R' THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE java.s
ql.DatabaseMetaData::importedKeyNoAction END) END) AS SMALLINT) AS UPDATE_RULE,
CAST ((CASE WHEN F2.DELETERULE='S' THEN java.sql.DatabaseMetaData::importedKeyR
estrict ELSE (CASE WHEN F2.DELETERULE='R' THEN java.sql.DatabaseMetaData::impor
tedKeyNoAction ELSE (CASE WHEN F2.DELETERULE='C' THEN java.sql.DatabaseMetaData:
:importedKeyCascade ELSE (CASE WHEN F2.DELETERULE='U' THEN java.sql.DatabaseMeta
Data::importedKeySetNull ELSE java.sql.DatabaseMetaData::importedKeyNoAction END
)END)ENd&
odbc_getPrimaryKeys
|SELECT JDBC_SUBQUERY.TABLE_CAT
AS TABLE_CAT, JDBC_SUBQUERY.TABLE_SCHEM AS TABLE_SCHEM, JDBC_SUBQUERY.TABLE_NAME
AS TABLE_NAME, JDBC_SUBQUERY.COLUMN_NAME AS COLUMN_NAME, CAST (JDBC_SUBQUERY.KE
Y_SEQ AS SMALLINT) AS KEY_SEQ, JDBC_SUBQUERY.PK_NAME AS PK_NAME FROM ( SELECT CA
ST ('' AS VARCHAR(128)) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME A
S TABLE_NAME, COLS.COLUMNNAME AS COLUMN_NAME, CONGLOMS.DESCRIPTOR.getKeyColumnPo
sition(COLS.COLUMNNUMBER) AS KEY_SEQ, CONS.CONSTRAINTNAME AS PK_NAME FROM PROPER
TIES joinOrder=FIXED SYS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', SYS.SY
SSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1', SYS.
SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_I
NDEX3', SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_IN
DEX1', SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSCONGLOMERATES_INDEX1', SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLO
OP, inde&
getImportedKeys
|SELECT CAST ('' AS VARCHAR(128)
) AS PKTABLE_CAT, S.SCHEMANAME AS PKTABLE_SCHEM, TABLENAME AS PKTABLE_NAME, COLS
.COLUMNNAME AS PKCOLUMN_NAME, CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, FKTABLE_
SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPositi
on( COLS.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, CAST ((CASE WHEN FK_UPDATERULE='
S' THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE (CASE WHEN FK_UPDAT
ERULE='R' THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE java.sql.Data
baseMetaData::importedKeyNoAction END) END) AS SMALLINT) AS UPDATE_RULE, CAST (
(CASE WHEN FK_DELETERULE='S' THEN java.sql.DatabaseMetaData::importedKeyRestrict
ELSE (CASE WHEN FK_DELETERULE='R' THEN java.sql.DatabaseMetaData::importedKeyN
oAction ELSE (CASE WHEN FK_DELETERULE='C' THEN java.sql.DatabaseMetaData::import
edKeyCascade ELSE (CASE WHEN FK_DELETERULE='U' THEN java.sql.DatabaseMetaData::i
mportedKeySetNull ELSE java.sql.DatabaseMetaData::importedKeyNoAction END) END)
END) END&
4 rows selected
ij>
Army wrote:
Mamta Satoor wrote:
Hi,
The patch for optimizer overrides includes changes in metadata.properties.
I think this change in metadata.properties is going to require some form of
upgrade code so that the sql text stored in the systems tables can be
replaced with new sql text which uses this new form of optimizer overrides.
If I'm following the metadata codepath correctly, I don't _think_ the SQL text for metadata.properties is actually stored in the system tables. Rather, Derby reads the " metadata.properties" file (which is part of the classes directory and is included in the build jars) directly at run time.
To explain why I think this is the case, take "getProcedures()" (which is part of the Java DatabaseMetaData class) as an example. In the EmbedDatabaseMetaData class, the getProcedures() call ends up being:
PreparedStatement s = getPreparedQuery(queryName);
s.setString(1, swapNull(catalog));
s.setString(2, swapNull(schemaPattern));
s.setString(3, swapNull(procedureNamePattern));
return s.executeQuery();
"getPreparedQuery()" includes the following line:
String queryText = getQueryDescriptions().getProperty(nameKey);
Then "getQueryDescriptions()" calls "loadQueryDescriptions()", which does some security checking and ultimately calls "PBloadQueryDescriptions()", which (finally) calls:
InputStream is = getClass().getResourceAsStream("metadata.properties");
p.load(is);
As a quick (but not by any means exhaustive) check of this, I went and renamed the "metadata.properties " file in my classes directory to something else, then tried to call "getProcedures" from a Derby DatabaseMetaData instance after connecting to (an already created) database. The call failed with an NPE because the InputStream was not able to find metadata.properties. I then put the metadata.properties file back and deleted the "getProcedures" statement, to see what would happen if the file was there but didn't have the query in it. That failed, too, with an error saying:
Feature not implemented: getProcedures.
So that leads me to believe that the queries are not actually stored in the database.
As for the stored procedures that Kathey mentioned, those are used for client-server communication. The client calls these stored procedures, which are then mapped to the appropriate methods in EmbedDatabaseMetaData (the mapping occurs in SystemProcedures.java, which is where the stored procedures are implemented), and thus the same codepath shown above is used.
I wondered if anyone can give me some pointers on how to get started with
upgrade code to achieve this. In the mean time, I will start looking into it
too.
All of that said, I'm not entirely sure what that means for your upgrade question. But here's my theory:
Even if metadata.properties is different from one version to another, the engine will only read the metadata.properties file that it has in its build. So as long as your changes don't modify the stored procedure signatures in any way (they only change the metadata.properties file), I don't _think_ upgrade is an issue because nothing in the system table has actually changed.
As for soft upgrade...umm, I tend to lean toward this being a non-issue, as well, because nothing in the database is actually _changed_ when a metadata procedure is called. If you soft upgrade to a later version, execute a bunch of metadata queries, then revert back to an older version and execute a bunch more metadata queries, everything should still be fine...I think.
But all of that is based on my examination of the metadata codepath; if I've overlooked or oversimplified, people should feel free to correct me.
Hopefully that's more helpful than confusing,
Army
