We are in the process of wiping out our database and creating new layouts for our users. We would however like to save their addressbook (emails, email groups). The problem we are facing is that a user's profile is linked to the addressbook by userid and not username. What would be a good approach to have these new layouts while at the same time save the address book?
Update the addressbook data store to use the new userid key wherever it contains a current userid?
That is, by creating the new layouts, you'll be assigning each user_name a new user_id. So, then you'll have an old database with user_name to user_id pairs and a new database with user_name to user_id pairs.
For each user_id in your current adressbook data store, lookup the current user_name. In the new instance of the addressbook data store, use the new user_id for that user_name.
Should work so long as user_names don't change simultaneously with the user_id changes across your db upgrade.
This should be feasible with a SQL update.
Comments (1)
Aug 17, 2005
Arun Baliga says:
At Illinois State University we employed the following approach to allow users t...At Illinois State University we employed the following approach to allow users to successfully restore their addressbook.
#1 Create duplicates of tables, upc_wm_addrs, upc_wm_addr_grp and upc_wm_groups with an additional column, user_name
#2 Run the following 3 queries
insert into upc_wm_addrs_1 SELECT upc_wm_addrs.*, up_user.user_name FROM up_user INNER JOIN upc_wm_addrs ON up_user.user_id = upc_wm_addrs.user_id;
insert into upc_wm_addr_grp_1 SELECT upc_wm_addr_grp.*, up_user.user_name FROM up_user INNER JOIN upc_wm_addr_grp ON up_user.user_id = upc_wm_addr_grp.user_id;
insert into upc_wm_groups_1 SELECT upc_wm_groups.*, up_user.user_name FROM up_user INNER JOIN upc_wm_groups ON up_user.user_id = upc_wm_groups.user_id;
#3 Users can call the following java code to restore their address book
IPerson person = PersonManagerFactory.getPersonManagerInstance().getPerson(request);
String the_person = (String)person.getAttribute(person.USERNAME);
int id = person.getID();
String Insert_1 = "Insert into upc_wm_addrs ( id,user_id,first_name,last_name,nickname,email,ldap_id,ldap_source) select id,"id",first_name,last_name,nickname,email,ldap_id,ldap_source from upc_wm_addrs_1 where user_name='" + the_person + "'";
String Insert_2 = "Insert into upc_wm_addr_grp (user_id,address_id,group_id) select "id",address_id,group_id from
upc_wm_addr_grp_1 where user_name='" + the_person + "'";
String Insert_3 = "Insert into upc_wm_groups (user_id,group_id,groupname) select "id",group_id,groupname from
upc_wm_groups_1 where user_name='" + the_person + "'";
Hope this helps others.