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/[EMAIL PROTECTED]&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/[EMAIL PROTECTED]&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/[EMAIL PROTECTED]&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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
