Skip to content
This repository has been archived by the owner on Nov 1, 2022. It is now read-only.

Horizon: Rewrite database structure and migrations #92

Open
Studio384 opened this issue Dec 22, 2020 · 0 comments
Open

Horizon: Rewrite database structure and migrations #92

Studio384 opened this issue Dec 22, 2020 · 0 comments
Assignees
Labels
enhancement Change to an existing feature
Milestone

Comments

@Studio384
Copy link
Member

Studio384 commented Dec 22, 2020

God, the database structure is a mess. Our relations are broken, etc.

Who's idea was it to use the codename of milestones as IDs?

Ow...

This issue isn't only about cleaning up our database structure and migrations. We'll also provide factories for all relevant models and seeders for anyone who wants to set up a local copy of ChangeWindows.

The new rules

For this project, a few rules have been set up. Some of these are common sense, some of these are Laravel guidelines and some of these are unique to ChangeWindows itself. These are just the rules that the current database wouldn't pass.

  • Every database table that isn't a pivot table has a unique numerical auto-incrimenting id, no exceptions. A pivot table uses a combination off the 2 or more foreign keys that make each line unique.
  • Every table has Laravel's auto-generated created_at and updated_at fields.
  • Any relation-field has the _id-suffix.
  • Pivot tables are named in a parent_child-pattern, not alphabetically as is enforced by Laravel. E.g. a platform has channels, thus its pivot table is named platform_channels and not channel_platforms as Laravel defaults towards.
  • All relations are defined with their constraints.
  • No fields should be added behind Laravel's created_at and updated_at fields, with the exception of Laravel's deleted_at field in case support for soft deleting is required.
  • Any table that has url-facing data has a slug field whom's value is set as the route key name to generate readable urls. E.g. platforms should have a slug, releases however shouldn't.

The only exception to these rules is the Buildfeed-table.

What this issue fixes

Right from the start, this means that a lot of things need to be fixed. The items that start with a (6) are new since version 6. Here is a list of all the fixes that Horizon applies:

  • Renames the ability_roles table to role_abilities.
  • Adds proper constraint from role_abilities to roles.
  • Adds proper constraint from role_abilities to abilities.
  • Renames abilities.label to abilities.description to properly reflect the function of the field.
  • Renames roles.label to roles.description to properly reflect the function of the field.
  • Removes the roles.rank-field which is a leftover of ChangeWindows 5.1.
  • Reorganizes the entire users-table field order.
  • Adds a users.slug field to store the slug for a user account based on name.
  • Adds proper constraint from roles to users.
  • (6) Renames the channel_platforms table to platform_channels.
  • Adds milestones.canonical_version which contains an XXYY-formatted version number for any milestone.
  • Renames milestones.osname to milestones.product_name to improve clearity of what the fields contains.
  • Renames milestones.preview to milestones.start_preview to better reflect the order of this field.
  • Renames milestones.public to milestones.start_public to better reflect the order of this field.
  • Renames milestones.mainEol to milestones.start_extended to better reflect the order of this field.
  • Renames milestones.mainXol to milestones.start_lts to better reflect the order of this field.
  • Renames milestones.ltsEol to milestones.end_lts to better reflect the order of this field.
  • Adds a milestones.slug field to store the slug for a milestone based on codename.
  • Renames releases-table to flights.
  • Renames releases.milestone to flights.milestone_id and changes type to bigint(20) to follow foreign key convention.
  • Renames releases.ring to flights.channel_id to follow foreign key convention.
  • Renames releases.platform to flights.platform_id to follow foreign key convention.
  • Adds flights.user_id to track user.
  • Removes the role_user-table.
  • Removes the patreons-table.
  • Removes the changelogs-table.
  • Removes the vnext-table.
@Studio384 Studio384 added the enhancement Change to an existing feature label Dec 22, 2020
@Studio384 Studio384 added this to the Version 6.0 milestone Dec 22, 2020
@Studio384 Studio384 self-assigned this Dec 22, 2020
@Studio384 Studio384 changed the title Rewrite database structure and migrations Horizon: Rewrite database structure and migrations Dec 23, 2020
Studio384 added a commit that referenced this issue Jan 29, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
enhancement Change to an existing feature
Projects
None yet
Development

No branches or pull requests

1 participant