[RFC] Migrating database of portail V1 to new version

[alexianelaude]

• Feature Name: database_migration
• Start Date: 2022-04-06

Summary

This document details a migration plan to transfer part of the old portail's database to the new version.
In particular, we will explicit which tables should be migrated, and how we intend on proceeding. For some tables, a discussion on whether or not we should migrate is required.

Motivation

The old version of the 'portail' has been running since ~2010, and we do not wish to wipe out all the history of this website when deploying the new version. In particular, it is necessary that all users keep their account, along with the information associated with their profile, so as not to exclude any 'mineur' from this students' website.
It would also be useful to migrate the current associations and their members (current and past), which would spare all associations from recreating their page from scratch.
Finally, we should consider migrating media, polls, past events, chat messages, etc, to which some users are emotionally attached. This is important if we want all users to turn towards the new version without regretting the past one.

Guide-level explanation

In this section, we will discuss which tables should be migrated, which tables might require a migration and which tables shouldn't be migrated.

To be migrated:

• Users and their profile: auth_user and trombi_userprofile
• Profile questions: trombi_question
• Profile answers: trombi_reponse and trombi_userprofile_reponses
• Minesparents: trombi_userprofile_parrains
• Roommates: trombi_userprofile_co
• Associations: association_association
• Current members of associations: association_adhesion
• Past members of associations: trombi_historique_assoc
• Octo and biero balances (from trombi_userprofile): they could be transferred by creating Funding objects inside the V2 database (but we would have negative fundings, which is not authorised by the backend code...).

Could be migrated:

• Association pages (association_page): there is a similar table in the new portail, but the structure is quite different (a link towards an external page in the old version vs. a text zone in the new version), thus much of the information would in any case be lost. Furthermore, a lot of these pages are outdated or should be substituted by the new marketplace/library system.
• Poles (sondages_sondage and sondages_vote): they should probably be migrated, in order to keep the statistics.
• Media (videos and images from bde, bds, bda, mediamines, but also vendome, abatage): The way media is dealt with in the old and new portail is very different, so this should be an issue of its own. Ultimately, it is important that some media is transferred (vendome, PR clips, palum...) but probably not all (mediamines pictures, old posters...)

Should not be migrated

• Permission groups and users (auth_group, auth_group_permissions, auth_user_groups and auth_user_user_permissions): the new version rather uses association roles to determine if someone is authorised to perform an action. Furthermore, the old version gave rights to past association members, which is not desirable...
• Events, news and chats: it is probable that no one cares about this past data.
• Elections and votes: it is best to delete past votes which in any case can only be accessed by superusers.

More anecdotic tables

• VP Katas (bapteme_vpkatas): they are singled out in the old portail, but also members of the Bapteme association, which doesn't really make sense...?
• Musical instruments (bda_instrument and bda_maitrise): does not exist in the new version, but perhaps something to add in the future

Reference-level explanation

The method we have chosen to run the migration is to generate SQL dumps of the v1 portail database and run them on the database of the new portail, once deployed.
We will not be creating tables/ sequences/ ... but only inserting values inside the v2 database. We will proceed table by table as listed above.

Because model structures between the old and new version differ in some ways, we will first run SQL queries on the v1 tables in order to:

• reorder and select fields when needed.
• join tables: in particular, numeric ids were used for users and associations in v1, but pseudos are now used as primary keys in v2. Thus any table containing the fields user_id and/or association_id will require a join with the table auth_user and/or association_association to obtain the pseudos for the migration towards a v2 table.
• Add default values when needed. For example, set marketplace and library to NULL for all associations, give current association members all permissions for their role (administration, election, event.... should be set to TRUE) but no permission to past association members (everything set to FALSE).

Some tables might require more complex manipulations, which can be run using a Python script. The script could either directly modify the .sql file, or we could export the table in .csv format instead, which might be easier to manipulate in Python.
This will especially be needed to populate the current_academic_year and student_type fields of the authentication_user table from v2. Indeed, the value of current_academic_year can be deduced by computing today.YEAR - year_of_entry, but must then be converted into a string based on 5 different cases ('1A', '2A', 'cesurien', '3A', 'diplomé') (NB: we will have to assume all students have done a gap year, because we are missing the information). The student_type can be deduced using the boolean values est_AST and est_isupfere from table V1.

Users should also simultaneously be created in the SSO server database. Because it also uses a sql database, the same method can be applied.

Drawbacks, rationale and alternatives

There is no reason not to migrate users and their profile information.

For the rest, a lot of data from the old portail is outdated, including a lot of media, events, etc but even some associations. The deployment of the new version could be an opportunity to filter out useless data which tends to pollute the website. In this case an automatic migration is perhaps not the best solution, and we could instead ask association members to choose for their own association which data they wish to keep.

Unresolved questions

See "Guide-level-explanation" for details on tables whose migration is open to debate. This RFC can in any case be run in separate phases (each table can be migrated independently from the others, provided there is no foreign key link).