Mailing-List: contact general-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: general@incubator.apache.org
Received-SPF: unknown (hermes.apache.org: error in processing during lookup of
 jta@bristowhill.com)
Message-ID: <41D59377.6020909@bristowhill.com>
Date: Fri, 31 Dec 2004 09:59:19 -0800
From: "Jean T. Anderson" <jta@bristowhill.com>
User-Agent: Mozilla Thunderbird 0.7.3 (X11/20040819)
MIME-Version: 1.0
To: Derby PPMC <derby-ppmc@incubator.apache.org>
CC: pmc@db.apache.org, general@incubator.apache.org,
 infrastructure@apache.org,
        dlr@apache.org
Subject: Re: Please remove ALL REFERENCES to nagoya
References: <NBBBJGEAGJAKLIDBKJOPEEENPAAB.noel@devtech.com>
In-Reply-To: <NBBBJGEAGJAKLIDBKJOPEEENPAAB.noel@devtech.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Noel J. Bergman wrote:

> <>...
> At the moment, I'm the one working on the webapp migrations, and, yes, we
> need to get it done pretty soon, but if you have time to take a look and
> come up with a better strategy, that's fine with me.

I assume the database has to be rebuilt and the ids will change.

We could provide mapping tables between old and new ids, which people
could use with their favorite tool (perl, awk, python, whatever) to
update references in web pages. This would be more accurate (and less
frustrating) than trying to manually reset ids.

If you already considered this strategy and eliminated it, stop reading
now!  :-) Otherwise, here are more assumptions ....

1) The index info is stored in a MySQL database.
And I'm assuming we can't freely alter those tables to add columns of
our own.

2) Looking at the SQL schema, these are the ids that get autogenerated
and need to be migrated:

   eb_list.listId
   eb_ListFile.fileId
   eb_Author.authorId

   eb_Subject.subjectId
   eb_Thread.threadId
   eb_Message.msgId

The ids that are the most tedious to update and most likely to break web
pages are the last three: eb_Subject.subjectId, eb_Thread.threadId, and
eb_Message.msgId.

For eb_Thread.threadId, I assume the 950240 below corresponds to the
threadId:
http://nagoya.apache.org/eyebrowse/BrowseList?listName=derby-user@db.apache.org&by=thread&from=950240&to=950240&first=1&count=1

For eb_Subject.subjectId, I assume the 632288 below corresponds to
subjectId:
http://nagoya.apache.org/eyebrowse/BrowseList?listName=derby-user@db.apache.org&by=subject&from=632288&to=632288&first=1&count=2

For eb_Message.msgId, that table also has a column called msgNo that is
not autogenerated by the database. How does it get filled in? And, given
the URI below:

http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-user@db.apache.org&msgNo=446

Does the 446 match eb_Message.msgId or eb_Message.msgNo ?

3) Remapping id's

Each table with a changing id would have a corresponding "old" table,
such as eb_Message_old, and a "migrate" table, such as eb_Message_mig.

The "old" table would match the schema of the production table exactly
and would preserve the contents of the table before the database is rebuilt.

The migration table would map old and new ids. These are 'pseudo' sql
statement because I haven't worked with MySQL (but I'm happy to do so if
this interests anyone):

CREATE TABLE eb_Message_mig (
       msgIdOld                INTEGER NOT NULL,
       msgIdNew               INTEGER NOT NULL);

initialize mapping table:

   insert into eb_Message_mig (msgIdOld)
       select msgId from eb_Message_old
      
Filling in the new ids requires information that is static for both old
and new. If the msgNo is taken from the email header, that would be
ideal and the mapping table could be updated with a statement something
like this:

   update eb_Message_mig mig
   set mig.msgIdNew =
        (select new.msgId from eb_Message new, eb_Message_old old
         where old.msgId=mig.msgIdOld
         and new.msgDate = old.msgDate
         and new.msgNo = old.msgNo
       )

If msgNo isn't a good candidate then we'd simply need to find a good
candidate, perhaps with joins to other tables so we can uniquely
identify a record.


Given those mapping tables, we could accurately update web pages to
reference the new ids. There would be a time lag, of course, during the
migration when ids would be broken. But since they'll break anyhow ... :-)

What do you think? too tangled?

 -jean


 -

---------------------------------------------------------------------
To unsubscribe, e-mail: general-unsubscribe@incubator.apache.org
For additional commands, e-mail: general-help@incubator.apache.org