db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade
Date Fri, 11 Nov 2005 20:01:59 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks Army for the good description... But I do think there are SOME
metadata calls that are stored in the database...<br>
<br>
Try this:<br>
<br>
ij&gt; <b>select stmtname, text from sys.sysstatements where text like
'%PROPERTIES%';<br>
</b><br>
STMTNAME<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|TEXT<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
--------------------------------------------------------------------------------<br>
----------------------------------------------------------------<br>
getPrimaryKeys<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|SELECT CAST ('' AS
VARCHAR(128)<br>
) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME,
COLS.COL<br>
UMNNAME AS COLUMN_NAME,
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMB<br>
ER) AS KEY_SEQ, CONS.CONSTRAINTNAME AS PK_NAME FROM PROPERTIES
joinOrder=FIXED S<br>
YS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', SYS.SYSSCHEMAS S
PROPERTIES<br>
joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1',&nbsp;
SYS.SYSCONSTRAINTS CONS PR<br>
OPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3',&nbsp;
SYS.SYSKEYS K<br>
EYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1',
SYS.SYSCONGLOME<br>
RATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSCONGLOMERATES_IND<br>
EX1', SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSCOLUMNS<br>
_INDEX1' WHERE ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND
T.TABLENAME<br>
LIKE ? AND T.SCHEMAID = S.SCHEMAID AND&nbsp; T.TABLEID = COLS.REFERENCEID
AND T.TABLE<br>
ID = CONGLOMS.TABLEID AND CONS.TABLEID = T.TABLEID AND CONS.TYPE = 'P'
AND CONS.<br>
CONSTRAIN&amp;<br>
getCrossReference<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|SELECT CAST ('' AS
VARCHAR(128)<br>
) AS PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, CAST (''
AS VARCHA<br>
R(128)) AS FKTABLE_CAT, S2.SCHEMANAME AS FKTABLE_SCHEM, T2.TABLENAME AS
FKTABLE_<br>
NAME, COLS2.COLUMNNAME AS FKCOLUMN_NAME, CAST
(CONGLOMS2.DESCRIPTOR.getKeyColumn<br>
Position( COLS2.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, CAST ((CASE WHEN
F2.UPDAT<br>
ERULE='S' THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE&nbsp;
(CASE WHEN F<br>
2.UPDATERULE='R' THEN java.sql.DatabaseMetaData::importedKeyNoAction
ELSE java.s<br>
ql.DatabaseMetaData::importedKeyNoAction END) END)&nbsp; AS SMALLINT) AS
UPDATE_RULE,<br>
&nbsp;CAST ((CASE WHEN F2.DELETERULE='S' THEN
java.sql.DatabaseMetaData::importedKeyR<br>
estrict ELSE&nbsp; (CASE WHEN F2.DELETERULE='R' THEN
java.sql.DatabaseMetaData::impor<br>
tedKeyNoAction ELSE (CASE WHEN F2.DELETERULE='C' THEN
java.sql.DatabaseMetaData:<br>
:importedKeyCascade ELSE (CASE WHEN F2.DELETERULE='U' THEN
java.sql.DatabaseMeta<br>
<a class="moz-txt-link-freetext" href="Data::importedKeySetNull">Data::importedKeySetNull</a>
ELSE
java.sql.DatabaseMetaData::importedKeyNoAction END<br>
)END)ENd&amp;<br>
odbc_getPrimaryKeys<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|SELECT
JDBC_SUBQUERY.TABLE_CAT<br>
AS TABLE_CAT, JDBC_SUBQUERY.TABLE_SCHEM AS TABLE_SCHEM,
JDBC_SUBQUERY.TABLE_NAME<br>
&nbsp;AS TABLE_NAME, JDBC_SUBQUERY.COLUMN_NAME AS COLUMN_NAME, CAST
(JDBC_SUBQUERY.KE<br>
Y_SEQ AS SMALLINT) AS KEY_SEQ, JDBC_SUBQUERY.PK_NAME AS PK_NAME FROM (
SELECT CA<br>
ST ('' AS VARCHAR(128)) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM,
T.TABLENAME A<br>
S TABLE_NAME, COLS.COLUMNNAME AS COLUMN_NAME,
CONGLOMS.DESCRIPTOR.getKeyColumnPo<br>
sition(COLS.COLUMNNUMBER) AS KEY_SEQ, CONS.CONSTRAINTNAME AS PK_NAME
FROM PROPER<br>
TIES joinOrder=FIXED SYS.SYSTABLES T PROPERTIES
index='SYSTABLES_INDEX1', SYS.SY<br>
SSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSSCHEMAS_INDEX1',&nbsp; SYS.<br>
SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSCONSTRAINTS_I<br>
NDEX3',&nbsp; SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index
='SYSKEYS_IN<br>
DEX1', SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES
joinStrategy=NESTEDLOOP, index =<br>
&nbsp;'SYSCONGLOMERATES_INDEX1', SYS.SYSCOLUMNS COLS PROPERTIES
joinStrategy=NESTEDLO<br>
OP, inde&amp;<br>
getImportedKeys<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|SELECT CAST ('' AS
VARCHAR(128)<br>
) AS PKTABLE_CAT, S.SCHEMANAME AS PKTABLE_SCHEM, TABLENAME AS
PKTABLE_NAME, COLS<br>
.COLUMNNAME AS PKCOLUMN_NAME, CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT,
FKTABLE_<br>
SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, CAST
(CONGLOMS.DESCRIPTOR.getKeyColumnPositi<br>
on( COLS.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, CAST ((CASE WHEN
FK_UPDATERULE='<br>
S' THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE&nbsp; (CASE WHEN
FK_UPDAT<br>
ERULE='R' THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE
java.sql.Data<br>
baseMetaData::importedKeyNoAction END) END)&nbsp; AS SMALLINT) AS
UPDATE_RULE, CAST (<br>
(CASE WHEN FK_DELETERULE='S' THEN
java.sql.DatabaseMetaData::importedKeyRestrict<br>
&nbsp;ELSE&nbsp; (CASE WHEN FK_DELETERULE='R' THEN
java.sql.DatabaseMetaData::importedKeyN<br>
oAction ELSE (CASE WHEN FK_DELETERULE='C' THEN
java.sql.DatabaseMetaData::import<br>
edKeyCascade ELSE (CASE WHEN FK_DELETERULE='U' THEN
java.sql.DatabaseMetaData::i<br>
mportedKeySetNull ELSE java.sql.DatabaseMetaData::importedKeyNoAction
END) END)<br>
END) END&amp;<br>
<br>
4 rows selected<br>
ij&gt;<br>
<br>
Army wrote:<br>
<blockquote cite="mid4374F4DD.6090409@sbcglobal.net" type="cite">
  <blockquote type="cite">Mamta Satoor wrote:
    <br>
    <br>
    <blockquote type="cite">Hi,
      <br>
The patch for optimizer overrides includes changes in
metadata.properties.
      <br>
I think this change in metadata.properties is going to require some
form of
      <br>
upgrade code so that the sql text stored in the systems tables can be
      <br>
replaced with new sql text which uses this new form of optimizer
overrides.
      <br>
    </blockquote>
  </blockquote>
  <br>
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.&nbsp; 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.
  <br>
  <br>
To explain why I think this is the case, take "getProcedures()" (which
is part of the Java DatabaseMetaData class) as an example.&nbsp; In the
EmbedDatabaseMetaData class, the getProcedures() call ends up being:
  <br>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;PreparedStatement s = getPreparedQuery(queryName);
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;s.setString(1, swapNull(catalog));
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;s.setString(2, swapNull(schemaPattern));
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;s.setString(3, swapNull(procedureNamePattern));
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;return s.executeQuery();
  <br>
  <br>
"getPreparedQuery()" includes the following line:
  <br>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;String queryText = getQueryDescriptions().getProperty(nameKey);
  <br>
  <br>
Then "getQueryDescriptions()" calls "loadQueryDescriptions()", which
does some security checking and ultimately calls
"PBloadQueryDescriptions()", which (finally) calls:
  <br>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;InputStream is =
getClass().getResourceAsStream("metadata.properties");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>
&nbsp;&nbsp;&nbsp;&nbsp;p.load(is);
  <br>
  <br>
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.&nbsp; The call failed with an NPE because the InputStream was not
able to find metadata.properties.&nbsp; 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:
  <br>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;Feature not implemented: getProcedures.
  <br>
  <br>
So that leads me to believe that the queries are not actually stored in
the database.
  <br>
  <br>
As for the stored procedures that Kathey mentioned, those are used for
client-server communication.&nbsp; 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.
  <br>
  <br>
  <blockquote type="cite">
    <blockquote type="cite">I wondered if anyone can give me some
pointers on how to get started with
      <br>
upgrade code to achieve this. In the mean time, I will start looking
into it
      <br>
too.
      <br>
    </blockquote>
  </blockquote>
  <br>
All of that said, I'm not entirely sure what that means for your
upgrade question.&nbsp; But here's my theory:
  <br>
  <br>
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.&nbsp; 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.
  <br>
  <br>
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.&nbsp; 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.
  <br>
  <br>
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.
  <br>
  <br>
Hopefully that's more helpful than confusing,
  <br>
Army
  <br>
  <br>
  <br>
  <br>
</blockquote>
</body>
</html>


Mime
View raw message