Good morning everyone,
I started looking at what it will take to migrate data from FAS2 to FAS3.
Here are my findings.
First of all the DB schemas: FAS2: http://ambre.pingoured.fr/public/FAS2.png FAS3: http://ambre.pingoured.fr/public/FAS3.png
* Tables to delete in FAS2: - session - migration_version - visit - vistit_identity - configs - requests - samadhi_associations - samadhi_nonces - group_roles
* Tables of FAS2 I do not know what to do with: - Log We have some logs in the DB, we might be able to convert them but the amount of information missing for the new log table (people_activity_log) might not make it worth - bugzilla_queue There are a few entries in there, but I do not know what it is meant for nor used by
* Tables to migrate - person_roles -> group_membership in FAS3 - person_roles_fpca -> group_membership in FAS3 -> I guess created when we changed from CLA to FPCA so to be merged in the same one as above - groups -> group in FAS3 - people -> people in FAS3
* Fields that changed people username : FAS2 = varchar(32) -> FAS3 = varchar(255) fullname : FAS2 = human_name -> FAS3 = fullname avatar : FAS2 = blog_avatar? -> FAS3 = avatar password : FAS2 = varchar(127) -> FAS3 = text gpg_id : FAS2 = gpg_id -> FAS3 = gpg_keyid emailtoken: FAS2 = emailtoken -> FAS3 = email_token passwordtoken: FAS2 = passwordtoken -> FAS3 = password_token status : FAS2 = text -> FAS3 = int alias_enabled: FAS2 = alias_enabled -> FAS3 = email_alias last_seen : FAS2 = last_seen -> FAS3 = last_logged
group name : FAS2 = varchar(32) -> FAS3 = varchar(40) url : FAS2 = url -> FAS3 = web_link groupe_type: FAS2 = varchar(16) -> FAS3 = int (Foreign Key) creation : FAS2 = creation -> FAS3 = created joinmsg : FAS2 = joinmsg -> FAS3 = join_msg user_can_remove: FAS2 = user_can_remove -> FAS3 = self_removal
For this table I have a problem with these fields in FAS3: ``need_approval`` and ``requires_sponsorship``? What is the difference? Which corresponds to ``needs_sponsor``?
group_membership role_type: FAS2 = role_type (text) -> FAS3: role (int) role_status: FAS2 = role_status (text)-> FAS3: status (int) sponsor_id: FAS2 = sponsor_id -> FAS3: sponsor person_id: FAS2 = person_id -> FAS3: people_id creation: FAS2 = creation -> FAS3: creation_timestamp approval: FAS2 = approval -> FAS3: approval_timestamp
Xavier, could you confirm that this mapping is correct? Should we look into being a little closer to the FAS2 model? (For example in the group_membership table) Also for change such as the length of the password field, since we hash the password, does it make sense to use a text field there since they will all be of the same size?
Then there is the question of the integer-based status (in the `people` table and in the `group_membership` table). Is the mapping documented somewhere? Does it fit with the old status model?
Another question will be regarding the certificates, Xavier, will we be able to migrate certificates information to the new tables?
This is without checking the changes in unique constraints where we might have a few other surprises. The rest should be straight forward though: -> Delete the old tables -> Create the new ones -> Add the new fields -> Rename the fields we agree to rename -> Update data structure (varchar -> int) -> Adjust constraints (cf above)
Have a nice day,
Pierre
On Tue, Nov 17, 2015 at 11:04:20AM +0100, Pierre-Yves Chibon wrote:
This is without checking the changes in unique constraints where we might have a few other surprises.
After looking more into this, it seems the only fields where we will run into 'problems' are `ircnick` and `email_token` that FAS3 requires to be unique while FAS2 doesn't.
I run the following query on the DB: SELECT username, ircnick, status FROM people WHERE ircnick IS NOT NULL AND ircnick != '' AND status = 'active' AND ircnick IN ( SELECT ircnick FROM people WHERE status = 'active' GROUP BY ircnick HAVING COUNT(ircnick) > 1 ) ORDER BY ircnick, username;
This returned 70 rows, so at least 35 persons have at minimum 2 accounts, active, set with the same ircnick.
I propose that we contact them and kindly ask that they either remove the ircnick from one of the accounts or just deactivate it.
For the email_token, we have two different account with the same token, and a few with a token's whose value is ''. (Also: FAS3 requires email_token to be unique, but not password_token, should we make this consistent?)
Some more questions for the migration: - Do we migrate disabled accounts? - Do we migrate accounts who last_seen date == the creation date ?
Thanks,
Pierre
On Tue, Nov 17, 2015 at 11:04:20AM +0100, Pierre-Yves Chibon wrote:
- Tables of FAS2 I do not know what to do with:
- Log We have some logs in the DB, we might be able to convert them but the amount of information missing for the new log table (people_activity_log) might not make it worth
- bugzilla_queue There are a few entries in there, but I do not know what it is meant for nor used by
Might this have something to do with a FAS change that results in a request to Bugzilla admins for BZ group membership?
On Nov 18, 2015 1:32 PM, "Paul W. Frields" stickster@gmail.com wrote:
On Tue, Nov 17, 2015 at 11:04:20AM +0100, Pierre-Yves Chibon wrote:
- Tables of FAS2 I do not know what to do with:
- Log We have some logs in the DB, we might be able to convert them but
the amount
of information missing for the new log table (people_activity_log)
might not
make it worth
- bugzilla_queue There are a few entries in there, but I do not know what it is
meant for nor
used byMight this have something to do with a FAS change that results in a request to Bugzilla admins for BZ group membership?
Yeah, that's it, except that a script processes these rather than bugzilla admins. It should be in the infra con jobs somewhere.
On Wed, Nov 18, 2015 at 05:08:12PM -0800, Toshio Kuratomi wrote:
On Nov 18, 2015 1:32 PM, "Paul W. Frields" stickster@gmail.com wrote:
On Tue, Nov 17, 2015 at 11:04:20AM +0100, Pierre-Yves Chibon wrote:
- Tables of FAS2 I do not know what to do with:
  - Log    We have some logs in the DB, we might be able to convert them
but the amount
   of information missing for the new log table
(people_activity_log) might not
   make it worth   - bugzilla_queue    There are a few entries in there, but I do not know what it is
meant for nor
   used by
Might this have something to do with a FAS change that results in a request to Bugzilla admins for BZ group membership?
Yeah, that's it, except that a script processes these rather than bugzilla admins. It should be in the infra con jobs somewhere.
Do you have an example of these cases? Is this something that FAS3 keeps?
I see some data in the DB right now, is it supposed to get cleaned up by the cron ?
Thanks, Pierre
infrastructure@lists.fedoraproject.org
-
Paul W. Frields -
Pierre-Yves Chibon -
Toshio Kuratomi