DB Migration Process » History » Revision 17
Revision 16 (Amber Herold, 02/05/2010 11:00 AM) → Revision 17/29 (Amber Herold, 02/05/2010 11:11 AM)
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 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>
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>
h3. Set all null groups to 4 (users)
h3. set all group privelges that are null to 3
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.
h3. Copy users from dbemdata.UserData to the project.userdetails table. inserts 188 rows
<pre>
INSERT INTO projectdata.userdetails
(`REF|leginondata|UserData|user`,
title,
institution,
dept,
address,
city,
statecountry,
zip,
phone,
fax,
url)
SELECT dbemdata.UserData.DEF_id, projectdata.users.title, projectdata.users.institution,
projectdata.users.dept, projectdata.users.address, projectdata.users.city,
projectdata.users.statecountry, projectdata.users.zip, projectdata.users.phone,
projectdata.users.fax, projectdata.users.url
FROM dbemdata.UserData, projectdata.users
WHERE dbemdata.UserData.username = projectdata.users.username
AND projectdata.users.userId NOT IN ( 216, 224, 107, 204, 219, 241, 261 )
</pre>
ignore:
project.users.userId username
216 nramm_hetzer (dup w/less data)
224 nramm_hjing
107 nramm_jlanman
204 nramm_rkhayat
219 nramm_rkhayat
241 nramm_vinzenz.unger
261 nramm_vinzenz.unger