Appion

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. 

*Insert users that are project owners and do not have login info and do not have a dbem user name.*

Set the passwords to the username.

Add the following project owners to dbemdata.UserData:

nramm_mbevans

nramm_erica

nramm_erwright

nramm_mgfinn

nramm_pucadyil

nramm_abaudoux

nramm_kuzman

nramm_my3r

nramm_liguo.wang

nramm_bbartholomew

nramm_cciferri

nramm_galushin

nramm_nachury

nramm_mfisher1

nramm_nicoles

nramm_gokhan_tolun

nramm_rkirchdo

<pre>

INSERT INTO dbemdata.UserData (username, firstname, lastname, email, password)

SELECT projectdata.users.username,projectdata.users.firstname, projectdata.users.lastname, 

projectdata.users.email, projectdata.users.username

FROM projectdata.users

WHERE projectdata.users.username IN ("nramm_mbevans", "nramm_erica", "nramm_erwright", "nramm_mgfinn", 

"nramm_pucadyil", "nramm_abaudoux", "nramm_kuzman", "nramm_my3r", "nramm_liguo.wang", "nramm_bbartholomew", 

"nramm_cciferri", "nramm_galushin", "nramm_nachury", "nramm_mfisher1", "nramm_nicoles", "nramm_gokhan_tolun", 

"nramm_rkirchdo")

</pre>

*Add their details into the userdetails table*

<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.username IN ( "nramm_mbevans", "nramm_erica", "nramm_erwright", 

"nramm_mgfinn", "nramm_pucadyil", "nramm_abaudoux", "nramm_kuzman", "nramm_my3r", "nramm_liguo.wang", 

"nramm_bbartholomew", "nramm_cciferri", "nramm_galushin", "nramm_nachury", "nramm_mfisher1", "nramm_nicoles", 

"nramm_gokhan_tolun", "nramm_rkirchdo")

</pre>

*Update the pis table with the correct usernames.* 

The correct usernames are the ones that the users actually use to login to the system.

They have been found by manual inspection.

<pre>

UPDATE projectdata.pis

SET projectdata.pis.username="chappie"

WHERE projectdata.pis.username="nramm_chappie"

UPDATE projectdata.pis

SET projectdata.pis.username="carthur"

WHERE projectdata.pis.username="nramm_Christopher.Arthur"

UPDATE projectdata.pis

SET projectdata.pis.username="cpotter"

WHERE projectdata.pis.username="nramm_cpotter"

UPDATE projectdata.pis

SET projectdata.pis.username="craigyk"

WHERE projectdata.pis.username="nramm_craigyk"

UPDATE projectdata.pis

SET projectdata.pis.username="dfellman"

WHERE projectdata.pis.username="nramm_dfellman"

UPDATE projectdata.pis

SET projectdata.pis.username="dlyumkis"

WHERE projectdata.pis.username="nramm_dlyumkis"

UPDATE projectdata.pis

SET projectdata.pis.username="southworth"

WHERE projectdata.pis.username="nramm_dsouthwo"

UPDATE projectdata.pis

SET projectdata.pis.username="fapalida"

WHERE projectdata.pis.username="nramm_fapalida"

UPDATE projectdata.pis

SET projectdata.pis.username="feisun"

WHERE projectdata.pis.username="nramm_feisun"

UPDATE projectdata.pis

SET projectdata.pis.username="glander"

WHERE projectdata.pis.username="nramm_glander"

UPDATE projectdata.pis

SET projectdata.pis.username="haoyan"

WHERE projectdata.pis.username="nramm_hao.yan"

UPDATE projectdata.pis

SET projectdata.pis.username="jaeger"

WHERE projectdata.pis.username="nramm_jaeger"

UPDATE projectdata.pis

SET projectdata.pis.username="koehn"

WHERE projectdata.pis.username="nramm_koehn"

UPDATE projectdata.pis

SET projectdata.pis.username="mmatho"

WHERE projectdata.pis.username="nramm_mmatho"

UPDATE projectdata.pis

SET projectdata.pis.username="moeller"

WHERE projectdata.pis.username="nramm_moeller"

UPDATE projectdata.pis

SET projectdata.pis.username="muldera"

WHERE projectdata.pis.username="nramm_mulderam"

UPDATE projectdata.pis

SET projectdata.pis.username="paventer"

WHERE projectdata.pis.username="nramm_paventer"

UPDATE projectdata.pis

SET projectdata.pis.username="rharshey"

WHERE projectdata.pis.username="nramm_rasika"

UPDATE projectdata.pis

SET projectdata.pis.username="nramm_langlois"

WHERE projectdata.pis.username="nramm_rl2528"

UPDATE projectdata.pis

SET projectdata.pis.username="rmglaeser"

WHERE projectdata.pis.username="nramm_rmglaeser"

UPDATE projectdata.pis

SET projectdata.pis.username="rtaurog"

WHERE projectdata.pis.username="nramm_rtaurog"

UPDATE projectdata.pis

SET projectdata.pis.username="sstagg"

WHERE projectdata.pis.username="nramm_sstagg"

UPDATE projectdata.pis

SET projectdata.pis.username="tgonen"

WHERE projectdata.pis.username="nramm_tgonen"

UPDATE projectdata.pis

SET projectdata.pis.username="vossman"

WHERE projectdata.pis.username="nramm_vossman"

UPDATE projectdata.pis

SET projectdata.pis.username="ychaban"

WHERE projectdata.pis.username="nramm_ychaban"

</pre>

*Add project co-owners (the people who actually access the project).* 

Many of the project owners do not actually access the data. Add the users who actually work with the project. 

<pre>

INSERT INTO projectdata.pis (projectId, username)

VALUES (200,"nramm_fazam"), (230,"glander"), (190,"jlee"), (231,"glander"), (203,"Ranjan"), (181,"kubalek"), (84,"strable"), (222,"nramm_barbie"), (199,"joelq")

</pre>

*Insert rows into projectowners.*

All project owners now have usernames in dbemdata.UserData and all projects have an active owner in project.pis. 

<pre>

INSERT INTO projectdata.projectowners (`REF|projects|project`, `REF|leginondata|UserData|user`)

SELECT projectdata.pis.projectId, dbemdata.UserData.DEF_id

FROM dbemdata.UserData, projectdata.pis

WHERE dbemdata.UserData.username = projectdata.pis.username

</pre>

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