Use explicit through table for media/decision many-to-many field #4310

AetherUnbound · 2024-05-10T20:05:12Z

Fixes

Description

This PR changes the many-to-many relationship between media records and media decisions to reference media by the identifier rather than the Django default ID. This required creating a separate, explicit model and using the through property of ManyToManyField.

Warning
The migration for this would be quite complex if we wanted to preserve data because Django doesn't support altering through on M2M fields after creation. Fortunately, we don't have any data in these tables currently in production and no way to add it yet unless it's done explicitly through each model. Thus, I feel it's safe to take the migration approach set out here.

This produces the following SQL for migrations:

BEGIN;
--
-- Create model ImageDecisionThrough
--
CREATE TABLE "api_imagedecisionthrough" ("id" integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "decision_id" integer NOT NULL, "identifier" uuid NOT NULL);
--
-- Create model AudioDecisionThrough
--
CREATE TABLE "api_audiodecisionthrough" ("id" integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "decision_id" integer NOT NULL, "identifier" uuid NOT NULL);
--
-- Remove field media_objs from audiodecision
--
DROP TABLE "api_audiodecision_media_objs" CASCADE;
--
-- Add field media_objs to audiodecision
--
-- (no-op)
--
-- Remove field media_objs from imagedecision
--
DROP TABLE "api_imagedecision_media_objs" CASCADE;
--
-- Add field media_objs to imagedecision
--
-- (no-op)
ALTER TABLE "api_imagedecisionthrough" ADD CONSTRAINT "api_imagedecisionthr_decision_id_f589ea97_fk_api_image" FOREIGN KEY ("decision_id") REFERENCES "api_imagedecision" ("id") DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "api_imagedecisionthrough" ADD CONSTRAINT "api_imagedecisionthr_identifier_8f4f8995_fk_image_ide" FOREIGN KEY ("identifier") REFERENCES "image" ("identifier") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "api_imagedecisionthrough_decision_id_f589ea97" ON "api_imagedecisionthrough" ("decision_id");
CREATE INDEX "api_imagedecisionthrough_identifier_8f4f8995" ON "api_imagedecisionthrough" ("identifier");
ALTER TABLE "api_audiodecisionthrough" ADD CONSTRAINT "api_audiodecisionthr_decision_id_cc8242d3_fk_api_audio" FOREIGN KEY ("decision_id") REFERENCES "api_audiodecision" ("id") DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "api_audiodecisionthrough" ADD CONSTRAINT "api_audiodecisionthr_identifier_16367ed5_fk_audio_ide" FOREIGN KEY ("identifier") REFERENCES "audio" ("identifier") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "api_audiodecisionthrough_decision_id_cc8242d3" ON "api_audiodecisionthrough" ("decision_id");
CREATE INDEX "api_audiodecisionthrough_identifier_16367ed5" ON "api_audiodecisionthrough" ("identifier");
COMMIT;

Testing Instructions

Run just down -v && just api/init. Then open a connection to the database with just api/dbshell and run:

COPY public.api_imagedecision (id, created_on, updated_on, notes, action, moderator_id) FROM stdin;
1	2024-05-01 23:27:09.884471+00	2024-05-01 23:27:09.884487+00		rejected_reports	3
2	2024-05-01 23:32:51.386486+00	2024-05-01 23:32:51.386501+00		marked_sensitive	3
\.


COPY public.api_imagedecisionthrough (id, decision_id, identifier) FROM stdin;
1	1	2e22c7e7-58b7-486c-9df8-16e84c27478f
2	2	052d2cfc-fdcb-40de-994c-40eadef753fc
\.


COPY public.nsfw_reports (id, identifier, reason, description, status, created_at, decision_id) FROM stdin;
1	2e22c7e7-58b7-486c-9df8-16e84c27478f	mature		pending_review	2024-04-26 20:44:31.271278+00	\N
2	2e22c7e7-58b7-486c-9df8-16e84c27478f	dmca		pending_review	2024-04-26 20:44:36.178746+00	\N
4	2e22c7e7-58b7-486c-9df8-16e84c27478f	mature		pending_review	2024-04-26 20:44:46.869982+00	\N
5	c296b69b-c8d7-4fc3-b820-a83de6138e0e	dmca		pending_review	2024-04-26 20:44:52.034395+00	\N
6	de37812d-2daa-49f6-91ab-e87e780ca653	dmca	asdfsadfsadfsadfsadfsadf	pending_review	2024-04-26 20:45:31.272964+00	\N
7	de37812d-2daa-49f6-91ab-e87e780ca653	dmca		pending_review	2024-05-01 21:12:53.177529+00	\N
8	c296b69b-c8d7-4fc3-b820-a83de6138e0e	dmca		pending_review	2024-05-01 21:21:10.442789+00	\N
9	ba711b67-2cbe-487a-9feb-19d70accf912	dmca		pending_review	2024-05-01 21:28:45.869087+00	\N
3	2e22c7e7-58b7-486c-9df8-16e84c27478f	other		pending_review	2024-04-26 20:44:42.274724+00	1
10	052d2cfc-fdcb-40de-994c-40eadef753fc	dmca		pending_review	2024-05-01 21:31:04.020163+00	2
\.

This is similar to the testing described in #4254, but note the difference in the intermediary table. Visit http://localhost:50280/admin/api/image/ locally to see the reports & decisions.

Checklist

My pull request has a descriptive title (not a vague title likeUpdate index.md).
My pull request targets the default branch of the repository (main) or a parent feature branch.
My commit messages follow best practices.
My code follows the established code style of the repository.
I added or updated tests for the changes I made (if applicable).
I added or updated documentation (if applicable).
I tried running the project locally and verified that there are no visible errors.
I ran the DAG documentation generator (just catalog/generate-docs for catalog
PRs) or the media properties generator (just catalog/generate-docs media-props
for the catalog or just api/generate-docs for the API) where applicable.

Developer Certificate of Origin

Developer Certificate of Origin
Version 1.1

Copyright (C) 2004, 2006 The Linux Foundation and its contributors.
1 Letterman Drive
Suite D4700
San Francisco, CA, 94129

Everyone is permitted to copy and distribute verbatim copies of this
license document, but changing it is not allowed.


Developer's Certificate of Origin 1.1

By making a contribution to this project, I certify that:

(a) The contribution was created in whole or in part by me and I
    have the right to submit it under the open source license
    indicated in the file; or

(b) The contribution is based upon previous work that, to the best
    of my knowledge, is covered under an appropriate open source
    license and I have the right under that license to submit that
    work with modifications, whether created in whole or in part
    by me, under the same open source license (unless I am
    permitted to submit under a different license), as indicated
    in the file; or

(c) The contribution was provided directly to me by some other
    person who certified (a), (b) or (c) and I have not modified
    it.

(d) I understand and agree that this project and the contribution
    are public and that a record of the contribution (including all
    personal information I submit with it, including my sign-off) is
    maintained indefinitely and may be redistributed consistent with
    this project or the open source license(s) involved.

github-actions · 2024-05-10T20:16:07Z

Full-stack documentation: https://docs.openverse.org/_preview/4310

Please note that GitHub pages takes a little time to deploy newly pushed code, if the links above don't work or you see old versions, wait 5 minutes and try again.

You can check the GitHub pages deployment action list to see the current status of the deployments.

Changed files 🔄:

https://docs.openverse.org/_preview/4310/meta/media_properties/api.html

dhruvkb

LGTM! I'm not opposed to having it but the base model is kind of redundant here because all of its fields have to be overridden in the subclasses.

AetherUnbound · 2024-05-13T15:18:21Z

LGTM! I'm not opposed to having it but the base model is kind of redundant here because all of its fields have to be overridden in the subclasses.

I wasn't sure how best to handle that case, since we have some other abstract models in a similar vein! Happy to remove it though 😄

Edit: Since it's referenced by another abstract model, I'd rather keep it currently.

openverse-bot · 2024-05-15T00:00:07Z

Based on the high urgency of this PR, the following reviewers are being gently reminded to review this PR:

@obulat
This reminder is being automatically generated due to the urgency configuration.

Excluding weekend¹ days, this PR was ready for review 2 day(s) ago. PRs labelled with high urgency are expected to be reviewed within 2 weekday(s)².

@AetherUnbound, if this PR is not ready for a review, please draft it to prevent reviewers from getting further unnecessary pings.

Specifically, Saturday and Sunday. ↩
For the purpose of these reminders we treat Monday - Friday as weekdays. Please note that the operation that generates these reminders runs at midnight UTC on Monday - Friday. This means that depending on your timezone, you may be pinged outside of the expected range. ↩

obulat

I tested this locally, and it all worked well.

I'm not sure that this is a problem with this PR, or should be fixed separately, but the way the decision is displayed in the Image Report view does not provide any information:

I think it should show what the decision actually was?

AetherUnbound · 2024-05-17T17:41:43Z

I'm not sure that this is a problem with this PR, or should be fixed separately, but the way the decision is displayed in the Image Report view does not provide any information.
I think it should show what the decision actually was?

This is just the Django Admin default, and it's temporary as well while we build out the rest of the features!

openverse/api/api/admin/__init__.py

Lines 57 to 59 in 0a7d31b

    
           # Temporary addition of model admin for decisions while this view gets built 
        
           admin.site.register(ImageDecision, admin.ModelAdmin) 
        
           admin.site.register(AudioDecision, admin.ModelAdmin)

github-actions · 2024-05-17T17:47:30Z

This PR has migrations. Please rebase it before merging to ensure that conflicting migrations are not introduced.

AetherUnbound requested review from a team as code owners May 10, 2024 20:05

AetherUnbound requested review from obulat and dhruvkb May 10, 2024 20:05

github-actions bot added 🧱 stack: api Related to the Django API 🧱 stack: documentation Related to Sphinx documentation labels May 10, 2024

openverse-bot added 🟧 priority: high Stalls work on the project or its dependents 🛠 goal: fix Bug fix 💻 aspect: code Concerns the software code in the repository labels May 10, 2024

github-actions bot added the migrations Modifications to Django migrations label May 10, 2024

dhruvkb approved these changes May 13, 2024

View reviewed changes

obulat approved these changes May 15, 2024

View reviewed changes

Use explicit through table for media/decision many-to-many field

55a88c5

AetherUnbound force-pushed the fix/many-to-many-identifier branch from 355fe47 to 55a88c5 Compare May 17, 2024 17:47

WordPress deleted a comment from github-actions bot May 17, 2024

AetherUnbound merged commit f2836e0 into main May 17, 2024
46 checks passed

AetherUnbound deleted the fix/many-to-many-identifier branch May 17, 2024 19:55

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Use explicit through table for media/decision many-to-many field #4310

Use explicit through table for media/decision many-to-many field #4310

AetherUnbound commented May 10, 2024

github-actions bot commented May 10, 2024

dhruvkb left a comment

AetherUnbound commented May 13, 2024 •

edited

openverse-bot commented May 15, 2024

obulat left a comment

AetherUnbound commented May 17, 2024

github-actions bot commented May 17, 2024

Use explicit through table for media/decision many-to-many field #4310

Use explicit through table for media/decision many-to-many field #4310

Conversation

AetherUnbound commented May 10, 2024

Fixes

Description

Testing Instructions

Checklist

Developer Certificate of Origin

github-actions bot commented May 10, 2024

dhruvkb left a comment

Choose a reason for hiding this comment

AetherUnbound commented May 13, 2024 • edited

openverse-bot commented May 15, 2024

Footnotes

obulat left a comment

Choose a reason for hiding this comment

AetherUnbound commented May 17, 2024

github-actions bot commented May 17, 2024

AetherUnbound commented May 13, 2024 •

edited