(General) Content versioning #65

New Issue

2024-02-23T18:05:23Z

boiii commented

2024-02-23 18:05:23 +00:00

This is essentially a more detailed overview of the prerequisite for #47.

Content needs versioning, so users can submit and update content. Currently, one release is just the current version.

I think there need to be 2 tables:

The current releases table (let's call it OldReleases) becomes the table for all versions of all releases, let's call it ReleaseVersions
- A version may be a draft or a part of a release's version history
- Version needs an author (foreign key to User table)
There needs to be a new table to store the version history of each release, let's call it ReleaseVersionHistory
- Each record here points to its version
- Has a timestamp to corrently order the history, because the version's creation date cannot be used

Every item in OldReleases is migrated to:

a version (in ReleaseVersions) with a hard-coded "traxxx" user as author
a history entry that points to the version

Run down of typical actions

User creates a new version of a release, this essentially forks a version (ReleaseVersions) into a new version (ReleaseVersions)
Version can be submitted to become the new current version (this would need a new table, too)
- Create a new history item (ReleaseVersionHistory) to append to the history, with creation date = NOW()
The version history of a release can be queried by just querying by ReleaseVersionHistory.release_id, ideally it's a descending index on release_id
A user can view their own versions (possibly drafts?) by querying ReleaseVersions.author, ideally it's a descending index on author_id
To get the latest version of a release, just query by release_id DESC LIMIT 1, join on the history entry's version_id (foreign key)

All this stuff would need to be done for Movies and Actors (and Studios?) too, so you would have

ReleaseVersions, ActorVersions, MovieVersions, .....................

Important

A version's author should be NULLABLE. If a user account is deleted, the version shouldn't vanish, the author just becomes ???.

Diagram

This is essentially a more detailed overview of the prerequisite for https://gitea.unknown.name/DebaucheryLibrarian/traxxx/issues/47. --- Content needs versioning, so users can submit and update content. Currently, one release is just the current version. I think there need to be 2 tables: - The current releases table (let's call it `OldReleases`) becomes the table for all versions of all releases, let's call it `ReleaseVersions` - A version may be a draft or a part of a release's version history - Version needs an author (foreign key to `User` table) - There needs to be a new table to store the version history of each release, let's call it `ReleaseVersionHistory` - Each record here points to its version - Has a timestamp to corrently order the history, because the version's creation date cannot be used Every item in `OldReleases` is migrated to: - a version (in `ReleaseVersions`) with a hard-coded "traxxx" user as author - a history entry that points to the version ## Run down of typical actions - User creates a new version of a release, this essentially forks a version (`ReleaseVersions`) into a new version (`ReleaseVersions`) - Version can be submitted to become the new current version (this would need a new table, too) - Create a new history item (`ReleaseVersionHistory`) to append to the history, with creation date = NOW() - The version history of a release can be queried by just querying by ReleaseVersionHistory.release_id, ideally it's a descending index on `release_id` - A user can view their own versions (possibly drafts?) by querying ReleaseVersions.author, ideally it's a descending index on `author_id` - To get the latest version of a release, just query by `release_id` DESC LIMIT 1, join on the history entry's `version_id` (foreign key) All this stuff would need to be done for Movies and Actors (and Studios?) too, so you would have - ReleaseVersions, ActorVersions, MovieVersions, ..................... ## Important A version's author should be NULLABLE. If a user account is deleted, the version shouldn't vanish, the author just becomes ???. ## Diagram ![image](/attachments/b57f0416-ed3c-4f54-ab5b-7b0715532b2a)

image.png

71 KiB

boiii referenced this issue

2024-02-23 18:05:28 +00:00

(General) User submitted content #47

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: DebaucheryLibrarian/traxxx#65