Appion

DB Migration Process » History » Revision 13

Revision 12 (Amber Herold, 02/03/2010 02:56 PM) → Revision 13/29 (Amber Herold, 02/03/2010 02:56 PM)

h1. DB Migration Process 

 The tables that will be affected are in the dbemdata database and the project database.  
 Migrate the user data from project to dbemdata because dbemdata is already in Sinedon format.  


 +dbemdata+ 

 * GroupData 
 * UserData (81 rows) 

 +project+ 

 * users (233 rows) 
 * login (187 rows) 
 * pis (145 rows) 
 * userdetails 
 * projectowner 

 Future: 
 Eventually, we would like to have 3 databases, appion, leginon and project. The user related tables in dbemdata would be moved to project. 
 All the tables in project still need to be converted to Sinedon format. 

 h2. 1 Add new columns to UserData 

 Add: 

 * username 
 * fullname 
 * firstname 
 * lastname 
 * password 
 * email 

 Leave the existing columns as is. Use of "name" and "full name" (with a space) will be phased out. 

 h2. 2 Copy data to the UserData table 

 From users, copy username, firstname, lastname to UserData. 

 h3. Update existing dbemdata.UserData entries with information from project.users when the names match. 

 <pre> 
 UPDATE @UPDATE UserData, project.users, project.login 
     SET UserData.username=project.users.username, 
         UserData.firstname=project.users.firstname, 
         UserData.lastname=project.users.lastname, 
         UserData.email=project.users.email  
     WHERE UserData.`full name` like concat(project.users.firstname, ' ',project.users.lastname)  
           and project.login.userId = project.users.userId  
           and project.users.userId not in(63,211) 
           and UserData.DEF_id != 54 
 </pre> 54@ 

 h3. Some names did not match exactly. Update these seperatly. 


 <pre> 
 //Palida? 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 42 
           AND UserData.DEF_id = 25 

 //Gabe? 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 65 
           AND UserData.DEF_id = 29 

 //Edward Bridgnole 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 78 
           AND UserData.DEF_id = 41 

 //Pickwei 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 122 
           AND UserData.DEF_id = 57 

 //Mark Daniels 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 199 
           AND UserData.DEF_id = 65 

 //Chris Arthur 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 35 
           AND UserData.DEF_id = 67 

 //Fei Sun 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 233 
           AND UserData.DEF_id = 76 

 //Chi-yu Fu 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 245 
           AND UserData.DEF_id = 78 

 //Otomo Takanori 	 uId=79 	 puId=252 
 UPDATE UserData, projectdata.users 
     SET UserData.username=projectdata.users.username, 
         UserData.firstname=projectdata.users.firstname, 
         UserData.lastname=projectdata.users.lastname, 
         UserData.email=projectdata.users.email  
     WHERE projectdata.users.userId = 252 
           AND UserData.DEF_id = 79 
 </pre> 

 h3. Insert the rest of the project.users entries into the dbemdata.UserData table. 

 This inserts users that have a corresponding project.login entry and have not already been merged into existing dbemdata.UserData entries.  
 NRAMM usernames with no login entry are not transferred. 

 <pre> 
 INSERT INTO dbemdata.UserData (username, firstname, lastname, email) 
 SELECT projectdata.users.username,projectdata.users.firstname, projectdata.users.lastname,projectdata.users.email 
 FROM projectdata.users 
 WHERE projectdata.users.userId IN (SELECT projectdata.login.userId FROM projectdata.login) 
 AND (projectdata.users.userId NOT IN  
 ( 
     SELECT projectdata.users.userId userId 
     FROM dbemdata.UserData, projectdata.users, projectdata.login 
     WHERE dbemdata.UserData.`full name` LIKE concat( projectdata.users.firstname, ' ', projectdata.users.lastname ) 
     AND projectdata.login.userId = projectdata.users.userId 
 ) 
 AND projectdata.users.userId NOT IN ( 42, 65, 78, 122, 199, 35, 233, 245, 252, 63, 211 )) 
 </pre> 


 h3. From project.login, copy user password to dbemdata.UserData. 


 <pre> 
 UPDATE dbemdata.UserData, projectdata.login 
 SET dbemdata.UserData.password=projectdata.login.password 
 WHERE dbemdata.UserData.username = projectdata.login.username 
 </pre> 

 h2. 3 Create projectowner table 

 Move the data from pis table to a new projectowner table in the project database. This table will refer to users in the UserData table.  
 We will phase out use of the pis table.  

 h2. 4 Modify userdetails table 

 Remove the email column from the userdetails table. 
 From users, copy all needed fields.