Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 13681 invoked from network); 7 Dec 2005 07:26:47 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 7 Dec 2005 07:26:47 -0000 Received: (qmail 2775 invoked by uid 500); 7 Dec 2005 07:26:46 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 2528 invoked by uid 500); 7 Dec 2005 07:26:45 -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 2519 invoked by uid 99); 7 Dec 2005 07:26:45 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Dec 2005 23:26:45 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of msatoor@gmail.com designates 64.233.184.196 as permitted sender) Received: from [64.233.184.196] (HELO wproxy.gmail.com) (64.233.184.196) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Dec 2005 23:26:43 -0800 Received: by wproxy.gmail.com with SMTP id 36so588940wra for ; Tue, 06 Dec 2005 23:26:22 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=k/yza+zKDuf3S1vlLnIxQpVEj2BzQUa4WecTcHoK8oTsFzT0tQ8nHHz0t2AmvJ11hRskYYLp5y250aHrcoxfjLt3nL8+CBhdItou2bdSMLUL+1j7cDDX8cVMFzIB6lR74rnWhxg9eHnsi9d2ddBxEcJkrZnn1u2boZJCIxDdok0= Received: by 10.54.128.12 with SMTP id a12mr223131wrd; Tue, 06 Dec 2005 23:26:22 -0800 (PST) Received: by 10.54.133.2 with HTTP; Tue, 6 Dec 2005 23:26:22 -0800 (PST) Message-ID: Date: Tue, 6 Dec 2005 23:26:22 -0800 From: Mamta Satoor To: derby-dev@db.apache.org Subject: Re: [PATCH](DERBY-573) Provide support for optimizer overrides in Derby In-Reply-To: <4395DE9F.8050503@Sourcery.Org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18037_32123829.1133940382711" References: <4395D67D.7050809@Sourcery.Org> <4395DE9F.8050503@Sourcery.Org> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_18037_32123829.1133940382711 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Everyone, Here is the updated patch information which includes optimizer overrides support and database upgrade code. I have attached the patch to the Jira entry. The code for optimizer overrides support is same as for the earlier patch. But since the metadata.properties has changed between 10.1 and 10.2 (becaus= e of the new user visible optimizer overrides syntax), we need to have proper upgrade code to support this change. Following is a brief description of upgrade code 1)If a 10.1 db is getting run in soft upgrade mode with 10.2, the system tables would still have the old 10.1 optimizer overrides syntax which is no= t recognized by 10.2 The system tables of the 10.1 db can't be modified in soft upgrade mode to store the new 10.2 optimizer overrides syntax for metadata queries because in soft upgrade, system tables can't be modified i= n a backward incompatible way. To get around this, I have changed the code in EmbedDatabaseMetaData.java to see if db is getting run in soft upgrade mode= . If yes, then it should read the metadata queries from metadata.propertiesrather than system tables. This will work because metadata.properties for 10.2 release has been modified to use the new optimizer overrides syntax. 2)If a 10.1 db is getting run in hard upgrade mode with 10.2, then we can simply drop the stored queries for metadata calls from system tables and pu= t new queries from metadata.properties into the system table. This is acceptable in hard upgrade, because 10.2 db is not expected to run in 10.1release. This code is in DD_version.java 3)I have implemented the above 2 upgrade modes in a generic mode such that in future, with every new release, in soft upgrade mode, we will always rea= d the metadata queries from metadata.properties. And in hard uprgade mode, we will drop and recreate the stored metadata queries in the system tables wit= h the latest metadata queries from metadata.properties. This will take care o= f any future metadata.properties changes between the releases. A little description on changes made to some of the java files for the upgrade code 1)Added new methods in Statement.java and LanguageConnectionContext.javawhich will allow us to recognize the queries coming from metadata calls and let such queries use internal SQL syntax. These internal SQL syntaxes are not available to an end user but we have some queries in metadata.propertieswhich rely on them and hence we need to be able to run these queries with their internal syntax. This was not required in the past because metadata calls were always run from the system tables and hence the internal syntax was available to metadata calls at that point. Now, that in soft upgrade mode, we read the queries from metadata.properties file, we need to add these special methods to recognize metadata queries as special internal queries and let them use internal SQL syntax. 2)In DataDictionary.java, I have added 2 final static variables. One is added to reflect the 10.2 db version, DD_VERSION_DERBY_10_2. The other one is DD_VERSION_THIS_SOFTWARE_VERSION and with every release, it should be updated to the latest DD version final static number. This variable will be used by EmbeddedDatabaseMetaData to determine if we are in soft upgrade mode. Rather than hardcoding 10.2 DD version in EmbeddedDatabaseMetaData, I thought it would be better to use a generice variable DD_VERSION_THIS_SOFTWARE_VERSION. In order to test my upgrade changes, I have used derbyTesting/upgradeTests/phaseTester.java which was checked in by Dan sometime back. I have changed this test to be a subclass of metadata.javaand then I run the existing metadata tests within phaseTester after various stages of upgrade on a 10.1 db. phaseTester takes 4 parameters, the old derby major version, old derby minor version, old engine derby.jarclasspath, new engine derby.jar and derbyTesting.jar classpath. An eg run is as follows $ C:/p4clients/main/opensource/java/testing/org/apache/derbyTesting/upgradeTe= sts/runphases 10 1 c:/p4clients/code101/opensource/jars/sane c:/p4clients/main/opensource/jars/sane For the command above, phaseTester will 1)first create a 10.1 db and then will run metadata tests on it. 2)Then run the 10.1 db with 10.2 software in soft upgrade mode and run the metadata tests on it (this is where the metadata sql will be picked from metadata.properties rather than system table) 3)The run the soft upgraded 10.1 db back with 10.1 software and rerun the metadata tests 4)Now run the 10.1 db in hard upgrade mode with 10.2 software. The db will be upgraded to 10.2 version (which means that stored metadata sql from system tables will be dropped and recreated with sql from metadata.properties). Run the metadata tests on this hard upgraded db 5)Finally, try to use this upgraded 10.2 db with 10.1 software and that wil= l fail because we don't have backward compatibility. This phaseTester test unfortunately is not part of derbyall and has to be run manually. I think we have a beetle entry Derby-514 to make this test available as part of a suite rather than requiring manual runs. One another note on the tests, I have merged metadataJdbc20 and metadata.java into one single test by moving all the tests from metadataJdbc20 to metadata.java's super class metadata_test.java. In the past when we had support from jdk18, we had to have a separate test for jdbc20 but since jdk18 support has been phased out, these tests ca= n be merged. Also, for reference, I have included the comments for the actual optimizer overrides changes from the previous patch. Majority of the changes went into the sqlgrammar.jj because Derby engine already has support for them internally. It is the parser that needs to recognize these overrides and pass it on to through the query nodes. The parser now looks for character sequence -- DERBY-PROPERTIES (case insensitive and space between -- and D is optional) and once it finds that, it looks for propertyName=3Dvalue pairs on that same comment line in parser= 's propertyList method. The parser does the basic check to make sure that the same property is not used more than once for a given table. The remaining checks on the properties like checking the existence of user specified inde= x etc are done in the bind phase. I also changed the metadata.properties file to use --DERBY-PROPERTIES rathe= r than old PROPERTIES clause to supply optimizer overrides. In addition, adde= d \n at the end of the optimier override comment lines to make sure the comment line does not get concatenated with the next line of the sql. Import.java had to be changed to user --DERBY-PROPERTIES rather than PROPERTIES. Added a new test optimizerOverrides.sql which runs in both embedded and network server mode. Finally, I have run the derbyall suite and these changes didn't cause any new failures. Please review and send in your comments. Mamta On 12/6/05, Satheesh Bandaram wrote: > > Great! It will be interesting to see how you are handling soft and hard > upgrades. I will wait for the new patch to start the review. > > Satheesh > > Mamta Satoor wrote: > > Thanks, Satheesh, for the update. But I am very close to submitting a new > patch for this functionality which will also include soft and hard upgrad= e > to 10.2 So, give me a day or two to submit the new patch and then you > (and anyone else who might be interested) can review that patch. > > thanks, > Mamta > > > On 12/6/05, Satheesh Bandaram wrote: > > > > Hi Mamta, > > > > I will review with the goal of committing this patch this week. THANKS > > for being patient... I was held up trying to get my GrantRevoke Part I = patch > > out last few weeks. > > > > Satheesh > > > > Mamta Satoor wrote: > > > > Hi Everyone, > > > > I posted this patch about 20days back. Does anyone have any feedback on > > the patch? Is it good to go? > > > > thanks, > > Mamta > > > > > > On 11/8/05, Mamta Satoor wrote: > > > > > > Hi Everyone, > > > > > > I have the patch for optimizer overrides support in Derby. Alongwith > > > the patch, I have attached the updated functional spec to the JIRA > > > entry Derby-573. > > > > > > Majority of the changes went into the sqlgrammar.jj because Derby > > > engine already has support for them internally. It is the parser that= needs > > > to recognize these overrides and pass it on to through the query node= s. > > > The parser now looks for character sequence -- DERBY-PROPERTIES (case > > > insensitive and space between -- and D is optional) and once it finds= that, > > > it looks for propertyName=3Dvalue pairs on that same comment line in = parser's > > > propertyList method. The parser does the basic check to make sure tha= t the > > > same property is not used more than once for a given table. The remai= ning > > > checks on the properties like checking the existence of user specifie= d > > > index etc are done in the bind phase. > > > > > > I also changed the metadata.properties file to use --DERBY-PROPERTIES > > > rather than old PROPERTIES clause to supply optimizer overrides. In > > > addition, added \n at the end of the optimier override comment lines > > > to make sure the comment line does not get concatenated with the next= line > > > of the sql. > > > > > > Import.java had to be changed to user --DERBY-PROPERTIES rather than > > > PROPERTIES. > > > > > > Added a new test optimizerOverrides.sql which runs in both embedded > > > and network server mode. > > > > > > Rerunning all the tests after syncing the codeline to make sure > > > nothing has broken. An earlier run of the tests before the sync came = out > > > clean. > > > > > > I plan to next work on exposing these overrides through runtime > > > statistics so that user can verify that the optimizer overrides are > > > getting used. > > > > > > I haven't researched into upgrade much but will the changes in > > > metadata.properties require some upgrade path for existing databases? > > > Any pointers here will be very useful. > > > > > > svn stat > > > M java\engine\org\apache\derby\impl\load\Import.java > > > M java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj > > > M java\engine\org\apache\derby\impl\jdbc\metadata.properties > > > M java\engine\org\apache\derby\iapi\reference\SQLState.java > > > M java\engine\org\apache\derby\loc\messages_en.properties > > > M > > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\db2Comp= atibility.sql > > > M > > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\copyfil= es.ant > > > A > > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\optimiz= erOverrides.sql > > > M > > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\checkCo= nstraint.sql > > > M > > > java\testing\org\apache\derbyTesting\functionTests\tests\store\access= .sql > > > A > > > java\testing\org\apache\derbyTesting\functionTests\master\DerbyNet\op= timizerOverrides.out > > > M > > > java\testing\org\apache\derbyTesting\functionTests\master\db2Compatib= ility.out > > > A > > > java\testing\org\apache\derbyTesting\functionTests\master\optimizerOv= errides.out > > > > > > M > > > java\testing\org\apache\derbyTesting\functionTests\master\checkConstr= aint.out > > > M > > > java\testing\org\apache\derbyTesting\functionTests\master\access.out > > > M > > > java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.r= unall > > > M > > > java\testing\org\apache\derbyTesting\functionTests\suites\derbynetmat= s.runall > > > > > > Comments/questions on the patch? > > > thanks, > > > Mamta > > > > > > > > ------=_Part_18037_32123829.1133940382711 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Everyone,
 
Here is the updated patch information which includes optimizer overrid= es support and database upgrade code. I have attached the patch to the Jira= entry.

The code for optimizer overrides support is same as for the earlier patc= h. But since the metadata.properties has changed between 10.1 and 10.2 (bec= ause of the new user visible optimizer overrides syntax), we need to have p= roper upgrade code to support this change.

Following is a brief description of upgrade code
1)If a 10.1 db is ge= tting run in soft upgrade mode with 10.2, the system tables would still hav= e the old 10.1 optimizer overrides syntax which is not recognized by 10.2 The system tables of the 10.1 db can't be modified in soft upgrade mode to= store the new 10.2 optimizer overrides syntax for metadata queries because= in soft upgrade, system tables can't be modified in a backward incompatibl= e way. To get around this, I have changed the code in=20 EmbedDatabaseMetaData.java to see if db is getting run in soft upgrade mode= . If yes, then it should read the metadata queries from metadata.properties= rather than system tables. This will work because metadata.properties for= =20 10.2 release has been modified to use the new optimizer overrides syntax.

2)If a 10.1 db is getting run in hard upgrade mode with 10.2, then we ca= n simply drop the stored queries for metadata calls from system tables and = put new queries from metadata.properties into the system table. This is acc= eptable in hard upgrade, because=20 10.2 db is not expected to run in 10.1 release. This code is in DD_version.= java

3)I have implemented the above 2 upgrade modes in a generic mode such th= at in future, with every new release, in soft upgrade mode, we will always = read the metadata queries from metadata.properties. And in hard uprgade mod= e, we will drop and recreate the stored metadata queries in the system tabl= es with the latest metadata queries from=20 metadata.properties. This will take care of any future metadata.properties = changes between the releases.

A little description on changes made to some of the java files for = the upgrade code
1)Added new methods in Statement.java and LanguageConne= ctionContext.java which will allow us to recognize the queries coming from = metadata calls and let such queries use internal SQL syntax. These internal= SQL syntaxes are not available to an end user but we have some queries in= =20 metadata.properties which rely on them and hence we need to be able to run = these queries with their internal syntax. This was not required in the past= because metadata calls were always run from the system tables and hence th= e internal syntax was available to metadata calls at that point. Now, that = in soft upgrade mode, we read the queries from=20 metadata.properties file, we need to add these special methods to recognize= metadata queries as special internal queries and let them use interna= l SQL syntax.
2)In DataDictionary.java, I have added 2 final static vari= ables. One is added to reflect the=20 10.2 db version, DD_VERSION_DERBY_10_2. The other one is DD_VERSION_THIS_SO= FTWARE_VERSION and with every release, it should be updated to the latest D= D version final static number. This variable will be used by EmbeddedDataba= seMetaData to determine if we are in soft upgrade mode. Rather than hardcod= ing=20 10.2 DD version in EmbeddedDatabaseMetaData, I thought it would be better t= o use a generice variable DD_VERSION_THIS_SOFTWARE_VERSION.

In order to test my upgrade changes, I have used derbyTesting/upgradeTes= ts/phaseTester.java which was checked in by Dan sometime back. I have chang= ed this test to be a subclass of metadata.java and then I run the existing = metadata tests within phaseTester after various stages of upgrade on a = ; 10.1 db. phaseTester takes 4 parameters, the old derby major version, old d= erby minor version, old engine derby.jar classpath, new engine derby.jar an= d derbyTesting.jar classpath. An eg run is as follows
$ C:/p4clients/mai= n/opensource/java/testing/org/apache/derbyTesting/upgradeTests/runphases 10= 1 c:/p4clients/code101/opensource/jars/sane c:/p4clients/main/opensource/j= ars/sane
For the command above, phaseTester will
1)first create a 10.1 db an= d then will run metadata tests on it.
2)Then run the 10.1 db with 10.2 s= oftware in soft upgrade mode and run the metadata tests on it (this is wher= e the metadata sql will be picked from=20 metadata.properties rather than system table)
3)The run the soft upgrade= d 10.1 db back with 10.1 software and rerun the metadata tests
4)Now run= the 10.1 db in hard upgrade mode with 10.2 software. The db will be upgrad= ed to=20 10.2 version (which means that stored metadata sql from system tables will = be dropped and recreated with sql from metadata.properties). Run the metada= ta tests on this hard upgraded db
5)Finally, try to use this upgraded=20 10.2 db with 10.1 software and that will fail because we don't have backwar= d compatibility.

This phaseTester test unfortunately is not part of derbyall and has to b= e run manually. I think we have a beetle entry Derby-514 to make this test = available as part of a suite rather than requiring manual runs.

One another note on the tests, I have merged metadataJdbc20 and metadata= .java into one single test by moving all the tests from metadataJdbc20 to m= etadata.java's super class metadata_test.java. In the past when we had supp= ort from jdk18, we had to have a separate
test for jdbc20 but since jdk18 support has been phased out, these test= s can be merged.

Also, for reference, I have included the comments for the actual optimiz= er overrides changes from the previous patch.
Majority of the changes we= nt into the sqlgrammar.jj because Derby engine already has support for them= internally. It is the parser that needs to recognize these overrides and p= ass it on to through the query nodes. The parser now looks for character se= quence -- DERBY-PROPERTIES (case insensitive and space between -- and D is = optional) and once it finds that, it looks for propertyName=3Dvalue pairs o= n that same comment line in parser's propertyList method. The parser does t= he basic check to make sure that the same property is not used more than on= ce for a given table. The remaining checks on the properties like checking = the existence of user specified index etc are done in the bind phase.=20
 
I also changed the metadata.properties file to use --DERBY-PR= OPERTIES rather than old PROPERTIES clause to supply optimizer overrides. I= n addition, added \n at the end of the optimier override comment lines to m= ake sure the comment line does not get concatenated with the next line of t= he sql.=20
 
Import.java had to be changed to user --DERBY-PROPERTIES rath= er than PROPERTIES.
 
Added a new test optimizerOverrides.sql wh= ich runs in both embedded and network server mode.

Finally, I have run the derbyall suite and these changes didn't cause an= y new failures.

Please review and send in your comments.
Mamta

On 12/6/05, = Satheesh Bandaram <satheesh= @sourcery.org> wrote:
Great! It will be interesting to= see how you are handling soft and hard upgrades. I will wait for the new p= atch to start the review.

Satheesh=20


Mamta Satoor wro= te:
Thanks, Satheesh, for the update. But I am very close to submitting a = new patch for this functionality which will also include soft and hard upgr= ade to 10.2 So, give me a day or two to submit the new patch and then you (= and anyone else who might be interested) can review that patch.= =20
 
thanks,
Mamta

 
On 12/6/05, = Satheesh Bandaram <satheesh= @sourcery.org > wrote:=20
Hi Mamta,

I w= ill review with the goal of committing this patch this week. THANKS for bei= ng patient... I was held up trying to get my GrantRevoke Part I patch out l= ast few weeks.=20

Satheesh=20


Mamta Satoor wrote:=20
Hi Everyone,
 
I posted this patch about 20days back. Does anyone have any feedback o= n the patch? Is it good to go?
 
thanks,
Mamta

 
On 11/8/05, = Mamta Satoor <msatoor@gmail.com= > wrote:=20
Hi Everyone,
 
I have the patch for optimi= zer overrides support in Derby. Alongwith the patc= h, I have attached the updated functional spec to the JIRA= entry Derby-573.=20
 
Majority of the changes went into the sqlgrammar.jj because Derby engi= ne already has support for them internally. It is the parser that needs to = recognize these overrides and pass it on to through the query nodes. The&nb= sp;parser now looks for character sequence -- DERBY-PROPERTIES (case insens= itive and space between -- and D is optional) and once it finds that, it lo= oks for propertyName=3Dvalue pairs on that same comment line in parser's pr= opertyList method. The parser does the basic check to make sure that the sa= me property is not used more than once for a given table. The remaining che= cks on the properties like checking the existence of user specified in= dex etc are done in the bind phase.=20
 
I also changed the metadata.properties file to use --DERBY-PROPERTIES = rather than old PROPERTIES clause to supply optimizer overrides. In addition, added \n at the end of the optimier override comment lines to make sure the comment line does not get co= ncatenated with the next line of the sql.
 
Import.java had to be changed to user --DERBY-PROPERTIES rather than P= ROPERTIES.
 
Added a new test optimizerOverrides.sql which runs in both embedded an= d network server mode.
 
Rerunning all the tests after syncing the codeline to make sure nothin= g has broken. An earlier run of the tests before the sync came out clean.
 
I plan to next work on exposing these overrides through runtime statis= tics so that user can verify that the optimizer ov= errides are getting used.
 
I haven't researched into upgrade much but will the changes in me= tadata.properties require some upgrade path for existing databases? Any poi= nters here will be very useful.
 
svn stat
M      java\engine\org\apache\derby\impl\load= \Import.java
M      java\engine\org\apache\derb= y\impl\sql\compile\sqlgrammar.jj
M      java\en= gine\org\apache\derby\impl\jdbc\metadata.properties
M   &= nbsp;  java\engine\org\apache\derby\iapi\reference\SQLState.java=20
M      java\engine\org\apache\derby\loc\messag= es_en.properties
M      java\testing\org\apache= \derbyTesting\functionTests\tests\lang\db2Compatibility.sql
M  = ;    java\testing\org\apache\derbyTesting\functionTests\test= s\lang\copyfiles.ant=20
A      java\testing\org\apache\derbyTesting\fu= nctionTests\tests\lang\optimizerOverrides.sql
M    &= nbsp; java\testing\org\apache\derbyTesting\functionTests\tests\lang\checkCo= nstraint.sql
M      java\testing\org\apache\der= byTesting\functionTests\tests\store\access.sql=20
A      java\testing\org\apache\derbyTesting\f= unctionTests\master\DerbyNet\optimizerOverrides.out
M   &= nbsp;  java\testing\org\apache\derbyTesting\functionTests\master\db2Co= mpatibility.out
A      java\testing\org\apache\= derbyTesting\functionTests\master\optimizerOverrides.out=20
M      java\testing\org\apache\derbyTesting\fu= nctionTests\master\checkConstraint.out
M      j= ava\testing\org\apache\derbyTesting\functionTests\master\access.out
M&nb= sp;     java\testing\org\apache\derbyTesting\functionTe= sts\suites\derbylang.runall=20
M      java\testing\org\apache\derbyTesting\f= unctionTests\suites\derbynetmats.runall
 
Comments/questions on the patch?
thanks,
Mamta



------=_Part_18037_32123829.1133940382711--