Fix remaining missing foreign key declarations

[asmecher]

This issue is a continuation of #6093.

The remaining missing foreign key constraints will require some code changes to move away from the use of 0 as a reserved case.

• lib/pkp/classes/migration/install/CommonMigration.php:
  • site.redirect (renamed to redirect_context_id)
  • plugin_settings.context_id (uses 0 for site-wide)
  • notifications.context_id (uses 0 for site-wide)
  • notifications.user_id (uses 0 for "anyone" IIRC)
  • notification_subscription_settings (just needs the field to be renamed from context to context_id)
• lib/pkp/classes/migration/install/LogMigration.php:
  • email_log.sender_id (uses 0 for "nobody in particular")
• lib/pkp/classes/migration/install/MetadataMigration.php:
  • filters.context_id (uses 0 for site-wide)
  • filters.parent_filter_id (uses 0 for none)
• lib/pkp/classes/migration/install/NavigationMenusMigration.php:
  • navigation_menus.context_id (0 used for site-wide)
  • navigation_menu_items.context_id (0 used for site-wide)
  • navigation_menu_items_assignments.parent_id (0 apparently used for none, even though the field appears nullable)
• lib/pkp/classes/migration/install/classes/RolesAndUserGroupsMigration.php:
  • user_groups.context_id (0 used for side-wide)
    notification_subscription_settings

Some of these areas are slated for rewriting, so this work may be a lower priority than the rewrite.

It makes sense to keep the email_log even when a sender (user) is deleted, but it's probably needed to review GDPR (e.g. right to be forgotten), as the email might keep personal data.

[jonasraoni]

I've updated the issue description (added the missing user_groups.context_id and marked others as done), and created initial PRs from detached commits of another issue.

[asmecher]

@jonasraoni, a tweak is required on this one: you're using ALTER TABLE t RENAME INDEX old_name TO new_name, which is only available in MariaDB as of version 10.5.2. My Ubuntu ships with 10.3.39 and I think there will probably be lots of ISPs using older versions too. Could you check if there's a workaround that'll support older MariaDBs?

[asmecher]

This changeset renamed the notification_subscription_settings_context index to notification_subscription_settings_context_id -- just an index rename, no functional change. However...

• The old index name remained in CommonMigration.php, and
• Index renaming is not supported in MariaDB 10.x or older but supported versions of MySQL.

I've backed out the change. If we do decide to rename it, let's wait until we can set a baseline MariaDB/MySQL requirement that'll allow us to do it cleanly (e.g. without deleting and re-creating it, which will disturb foreign key constrants). It's just cosmetic.

[jonasraoni]

I'll close this as the renameIndex() was dropped, if there are differences (regarding a new/upgraded installation) it might be handled better in another issue (e.g. #9250).