Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Mon, 22 Mar 2010 08:54:39 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Upgrade work when a new column is added to an existing system
 table...
In-reply-to: <d9619e4a1003191453j72380e60k4fe3722be5ec57@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: derby-dev@db.apache.org
Message-id: <4BA792BF.8070205@sun.com>
References: <d9619e4a1003191453j72380e60k4fe3722be5ec57@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Macintosh/20100228)

Hi Mamta,

This is a very interesting problem. I am not aware that we have added a 
column to a system table since Derby was open-sourced. Whole tables have 
been added and the contents of individual columns have been changed. But 
I can't remember our adding a column to an existing table.

I do think your problem is solvable--albeit in a tricky way. First let 
me describe a possible user experience for your proposal:

1) In soft-upgraded databases, triggers behave as they did in 10.5.

2) In addition, even in hard-upgraded databases, old triggers behave as 
they did in 10.5.

3) However, in new 10.6 databases and in hard-upgraded databases, new 
triggers on LOB-bearing tables may perform significantly better.

4) So, in order to enjoy the advantages of your work on DERBY-1482, 
legacy databases must be hard-upgraded to 10.6 and triggers on 
LOB-bearing tables must be dropped and recreated.

If that's the contract, I think that the following solution will work:

A) We change the meaning of SYSTRIGGERS.REFERENCEDCOLUMNS. This column 
will carry additional metadata for new triggers created in databases 
which are at level 10.6.

B) We do this by changing the implementation of 
ReferencedColumnsDescriptorImpl, the object stored in that column. This 
object will now carry both the old list of columns in the trigger's 
update list as well as a new, optional list of columns mentioned in the 
trigger action. Let us call these two lists UpdateColumnList and 
TriggerActionColumnList. Note that it is OK to change the implementation 
of this class because the Reference Guide explicitly states that 
REFERENCEDCOLUMNS is not part of Derby's public api.

C) When creating a new trigger, if the database is at level 10.5 or 
earlier, then ReferencedColumnsDescriptorImpl is created only with an 
UpdateColumnList, and TriggerActionColumnList is left null. However, if 
the database level is 10.6 or later, then the new 
ReferencedColumnsDescriptorImpl is created with both an UpdateColumnList 
and a TriggerActionColumnList.

D) Now for the tricky part. When serializing/deserializing a 
ReferencedColumnsDescriptorImpl, we overload the meaning of the first 
integer written/read by the writeExternal()/readExternal() methods. Let 
us call this integer VersionNumber.

i) If ReferencedColumnsDescriptorImpl was created with a null 
TriggerActionColumnList, then VersionNumber continues to be what it was 
in 10.5 and earlier, viz., the length of the UpdateColumnList array. 
(De)serialization is the same as it was in 10.5.

ii) However, if ReferencedColumnsDescriptorImpl was created with a 
non-null TriggerActionColumnList, then VersionNumber is -1. In this 
case, we (de)serialize both UpdateColumnList and TriggerActionColumnList.

E) At query execution time, if TriggerActionColumnList is null, then the 
user sees the 10.5 behavior. However, if TriggerActionColumnList is not 
null, then the user sees the performance improvement you are introducing.

Hope this makes sense and seems useful,
-Rick


Mamta Satoor wrote:
> Hi,
>
> I was looking for some guidence regarding upgrade work involved when a
> new column needs to be added to an existing system table and that
> column needs to be initialized to different value depending on the row
> in the table.
>
> As part of DERBY-1482, I need to add a new column to SYSTRIGGERS. That
> column is going to be of the type
> org.apache.derby.catalog.ReferencedColumns. The purpose of this column
> is to keep track of all the columns (from the trigger table) which are
> referenced in the trigger action through the REFERENCE clause of
> CREATE TRIGGER. eg
> create trigger tr1 after update of id on t1 referencing old as oldt
> 	for each row insert into t2(id2) values (oldt.j);
> For the trigger above, we will note in SYSTRIGGERS that column j from
> the trigger table t1 is getting referenced in the trigger action. We
> will use this information to decide which columns need to be read in
> when the user executes an UPDATE t1(updating column id) SQL which will
> fire the trigger tr1. This will be especially useful if the table t1
> has LOB columns.
>
> My question is did we need to add a new column to an existing system
> table in the past? If yes, I would love to reuse that code if possible
> rather than reinventing the wheel. I do see following method in
> DataDictionaryImpl but do not see it getting called from anywhere
> 	/**
> 	  * Upgrade an existing catalog by adding columns.
> 	  *
> 	  * @param	rowFactory				Associated with this catalog.
> 	  * @param	newColumnIDs			Array of 1-based column ids.
> 	  * @param	tc						Transaction controller
> 	  *
> 	  * @exception StandardException Standard Derby error policy
> 	  */
> 	public void	upgrade_addColumns( CatalogRowFactory rowFactory, int[]
> newColumnIDs,TransactionController tc)
> 					throws StandardException
>
>
> If we do not have an existing infrastructure for adding a new column,
> I will explore the work needed. If anyone has any pointers/thoughts,
> would greatly appreciate that.
>
> Another needed step for upgrade for my specific situation is to
> initialize the new column correctly so the existing triggers don't
> break.
>
> thanks,
> Mamta
>