Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 547 invoked from network); 16 Nov 2005 19:07:21 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 16 Nov 2005 19:07:21 -0000 Received: (qmail 32078 invoked by uid 500); 16 Nov 2005 19:07:19 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 32056 invoked by uid 500); 16 Nov 2005 19:07:19 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 32047 invoked by uid 99); 16 Nov 2005 19:07:19 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 16 Nov 2005 11:07:19 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of msatoor@gmail.com designates 64.233.184.195 as permitted sender) Received: from [64.233.184.195] (HELO wproxy.gmail.com) (64.233.184.195) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 16 Nov 2005 11:08:53 -0800 Received: by wproxy.gmail.com with SMTP id i7so1757589wra for ; Wed, 16 Nov 2005 11:06:57 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=azOElslOZ0HOjReoq5iLJWezpyt1syHLi6TRbuAG2AnNTrqXCeMNer151OwVcpKsUnAT5kLCoh5HGnXNrc5JfRwrRam8Y6X5HG6Doxo90GGhIq3PKCUJ/krvMYB+BtxR/ds6kzfrkZi6JTq0CkAhW6y3QIdTxNBXA4HVUyfomQo= Received: by 10.54.146.11 with SMTP id t11mr4848030wrd; Wed, 16 Nov 2005 11:06:56 -0800 (PST) Received: by 10.54.133.2 with HTTP; Wed, 16 Nov 2005 11:06:56 -0800 (PST) Message-ID: Date: Wed, 16 Nov 2005 11:06:56 -0800 From: Mamta Satoor To: Derby Development Subject: [Derby-573]Upgrade code and metadata.properties MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_33081_29194925.1132168016792" X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_33081_29194925.1132168016792 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 (usin= g 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 =3D 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 =3D null; try { String queryText =3D getQueryDescriptions().getProperty("getPrimaryKeys"); PreparedStatement s =3D getEmbedConnection().prepareMetaDataStatement(queryText); s.setString(1, swapNull(catalog)); s.setString(2, swapNull(schema)); s.setString(3, swapNull(table)); rs =3D s.executeQuery(); } catch (Throwable t) { throw handleException(t); } finally { restoreContextStack(); } return rs; } } } The sql from getPrimaryKeys in metadata.properties looks as follows getPrimaryKeys=3D\ 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=3DFIXED \n \ SYS.SYSTABLES T --DERBY-PROPERTIES index=3D'SYSTABLES_INDEX1' \n\ , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=3DNESTEDLOOP, index =3D'SYSSCHEMAS_INDEX1' \n\ , SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStrategy=3DNESTEDLOOP, ind= ex =3D'SYSCONSTRAINTS_INDEX3' \n\ , SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=3DNESTEDLOOP, index =3D'SYSKEYS_INDEX1' \n\ , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=3DNESTEDLOO= P, index =3D 'SYSCONGLOMERATES_INDEX1' \n\ , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=3DNESTEDLOOP, index =3D'SYSCOLUMNS_INDEX1' \n\ WHERE ((1=3D1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME LI= KE ? AND \ T.SCHEMAID =3D S.SCHEMAID AND \ T.TABLEID =3D COLS.REFERENCEID AND T.TABLEID =3D CONGLOMS.TABLEID AND \ CONS.TABLEID =3D T.TABLEID AND CONS.TYPE =3D 'P' AND \ CONS.CONSTRAINTID =3D KEYS.CONSTRAINTID AND \ (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ 0 END) <> 0 AND \ KEYS.CONGLOMERATEID =3D 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 ------=_Part_33081_29194925.1132168016792 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi,
 
I am working on writing the upgrade code for metadata.properties chang= es because of optimizer overrides syntax change. Also, I am trying to see i= f 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.doFul= lUpgrade(), I can drop the stored prepared statements (using the method&nbs= p; 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 over= ride syntax which is not recognized by 10.2 but because we are in soft upgr= ade 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 th= e system table so the=20 pre-10.2 database can be used by pre-10.2 derby release. I decided to tackl= e this issue by avoiding going to sysstatements table for few of these Data= baseMetaData calls(which use the optimizer overrides in their sql) and= instead, just read the sql from=20 metadata.properties and execute the sql directly. But that does not work ve= ry well because some of the metadata.properties sql uses syntax that is ava= ilable to Derby engine internally only. And when I try to run that sql by d= irectly reading from=20 metadata.properties, I get syntax error.
 
Here is the code snippet for getPrimaryKeys in EmbeddedDatab= aseMetadata after my changes
 public ResultSet getPrimaryKeys(String catalog, String schema,    String table) throws SQLException {
 &nbs= p;//check if the dictionary is at 10.2 revision. If not, then that means  //stored prepared statements for metadata calls are using the = old=20
  //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 tab= les in an backward
  //incompatible way, I am going to try to = read the metadata sql from
  //metadata.properties file rather than rely on system table= s.
  boolean newOptimizerOverridesSyntaxSupported;
 &n= bsp;try {
   newOptimizerOverridesSyntaxSupported =3D
=     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 1= 0.2
  //and hence is using new optimizer overrides syntax.
  if (newOptimizerOverridesSyntaxSupported)
  &n= bsp;return doGetPrimaryKeys(catalog, schema, table, "getPrimaryKeys&qu= ot;);
  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 (getConnectionS= ynchronization()) {
        &nbs= p;       setupContextStack();
  =             &nb= sp; ResultSet rs =3D null;
            &nb= sp;   try { 
     String queryTe= xt =3D getQueryDescriptions().getProperty("getPrimaryKeys");
&= nbsp;    PreparedStatement s =3D getEmbedConnection().p= repareMetaDataStatement(queryText);

     s.setString(1, swapNull(catalog));
&nbs= p;    s.setString(2, swapNull(schema));
  =    s.setString(3, swapNull(table));

     rs =3D s.executeQuery();
  &n= bsp;            = ;  } catch (Throwable t) {
      &nbs= p;            &= nbsp; throw handleException(t);
      &nbs= p;       } finally {
   &nb= sp;            =    restoreContextStack();
      =      }

            =      return rs;
      }
=   }
 }

The sql from getPrimaryKeys in metadata.properties looks as follows

getPrimaryKeys=3D\
 SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CA= T, \
     S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME A= S TABLE_NAME, \
     COLS.COLUMNNAME AS COLUMN_NAME,= \
     CONGLOMS.DESCRIPTOR.getKeyColumnPosition (COLS.COLUMNNUMBER) AS KEY_SEQ, \
     CONS.CONSTRAI= NTNAME AS PK_NAME \
 FROM --DERBY-PROPERTIES joinOrder=3DFIXED \n \=
   SYS.SYSTABLES T --DERBY-PROPERTIES index=3D'SYSTABLES= _INDEX1' \n\
   , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joi= nStrategy=3DNESTEDLOOP, index =3D'SYSSCHEMAS_INDEX1'  \n\
   , SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStra= tegy=3DNESTEDLOOP, index =3D'SYSCONSTRAINTS_INDEX3'  \n\
 &nbs= p; , SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=3DNESTEDLOOP, in= dex =3D'SYSKEYS_INDEX1' \n\
   , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=3DNESTEDLOOP, index =3D 'SYSCONGL= OMERATES_INDEX1' \n\
   , SYS.SYSCOLUMNS COLS --DERBY-PRO= PERTIES joinStrategy=3DNESTEDLOOP, index =3D'SYSCOLUMNS_INDEX1' \n\
&nbs= p;WHERE ((1=3D1) OR ? IS NOT NULL) AND=20 S.SCHEMANAME LIKE ? AND T.TABLENAME LIKE ? AND \
    T.SC= HEMAID =3D S.SCHEMAID AND \
    T.TABLEID =3D COLS.R= EFERENCEID AND T.TABLEID =3D CONGLOMS.TABLEID AND \
    C= ONS.TABLEID =3D T.TABLEID AND CONS.TYPE =3D 'P' AND \
    CONS.CONSTRAINTID =3D KEYS.CONSTRAINTID AND \
 &= nbsp;  (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \
 &nbs= p;  CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) E= LSE \
    0 END) <> 0 AND \
  &nb= sp; KEYS.CONGLOMERATEID =3D 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 state= ment. 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=20 10.2 will 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 m= ight 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, getImpo= rtedKeys, getPrimaryKeys.

thanks,
Mamta
------=_Part_33081_29194925.1132168016792--