Debian Group and Project Distributions

What does this MR do?

This adds the Debian Group and Project Distribution models (see full Debian model diagram here). This was originally part of !44746 (closed) (that is where the model discussion took place), but that MR was growing too large. This is step 2.4.4 from #5835 (comment 414103286), cherry-picked from !44746 (closed).

Database

Migrations

Up Migration

== 20201204110700 CreatePackagesDebianProjectDistributions: migrating =========
-- table_exists?(:packages_debian_project_distributions)
   -> 0.0011s
-- create_table(:packages_debian_project_distributions, {})
   -> 0.0733s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_834dabadb6\nCHECK ( char_length(codename) <= 255 )\nNOT VALID;\n")
   -> 0.0021s
-- current_schema()
   -> 0.0005s
-- execute("SET statement_timeout TO 0")
   -> 0.0015s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_834dabadb6;")
   -> 0.0039s
-- execute("RESET ALL")
   -> 0.0014s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_a56ae58a17\nCHECK ( char_length(suite) <= 255 )\nNOT VALID;\n")
   -> 0.0019s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_a56ae58a17;")
   -> 0.0038s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_6177ccd4a6\nCHECK ( char_length(origin) <= 255 )\nNOT VALID;\n")
   -> 0.0019s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_6177ccd4a6;")
   -> 0.0039s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_6f6b55a4c4\nCHECK ( char_length(label) <= 255 )\nNOT VALID;\n")
   -> 0.0019s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_6f6b55a4c4;")
   -> 0.0039s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_96965792c2\nCHECK ( char_length(version) <= 255 )\nNOT VALID;\n")
   -> 0.0019s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_96965792c2;")
   -> 0.0045s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0008s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_b93154339f\nCHECK ( char_length(description) <= 255 )\nNOT VALID;\n")
   -> 0.0020s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_b93154339f;")
   -> 0.0046s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0008s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_c25603a25b\nCHECK ( char_length(encrypted_signing_keys) <= 2048 )\nNOT VALID;\n")
   -> 0.0022s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_c25603a25b;")
   -> 0.0041s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_d488f8cce3\nCHECK ( char_length(encrypted_signing_keys_iv) <= 255 )\nNOT VALID;\n")
   -> 0.0021s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_d488f8cce3;")
   -> 0.0044s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0007s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_cb4ac9599e\nCHECK ( char_length(file) <= 255 )\nNOT VALID;\n")
   -> 0.0021s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_cb4ac9599e;")
   -> 0.0089s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions\nADD CONSTRAINT check_a5a2ac6af2\nCHECK ( char_length(file_signature) <= 4096 )\nNOT VALID;\n")
   -> 0.0020s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_project_distributions VALIDATE CONSTRAINT check_a5a2ac6af2;")
   -> 0.0046s
== 20201204110700 CreatePackagesDebianProjectDistributions: migrated (0.3415s) 

== 20201204110800 CreatePackagesDebianGroupDistributions: migrating ===========
-- table_exists?(:packages_debian_group_distributions)
   -> 0.0011s
-- create_table(:packages_debian_group_distributions, {})
   -> 0.0765s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_590e18405a\nCHECK ( char_length(codename) <= 255 )\nNOT VALID;\n")
   -> 0.0021s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_590e18405a;")
   -> 0.0037s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_e7c928a24b\nCHECK ( char_length(suite) <= 255 )\nNOT VALID;\n")
   -> 0.0017s
-- current_schema()
   -> 0.0004s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_e7c928a24b;")
   -> 0.0036s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_b057cd840a\nCHECK ( char_length(origin) <= 255 )\nNOT VALID;\n")
   -> 0.0013s
-- current_schema()
   -> 0.0004s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_b057cd840a;")
   -> 0.0030s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0004s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_d3244bfc0b\nCHECK ( char_length(label) <= 255 )\nNOT VALID;\n")
   -> 0.0013s
-- current_schema()
   -> 0.0004s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_d3244bfc0b;")
   -> 0.0031s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0004s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_3fdadf4a0c\nCHECK ( char_length(version) <= 255 )\nNOT VALID;\n")
   -> 0.0014s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_3fdadf4a0c;")
   -> 0.0037s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_310ac457b8\nCHECK ( char_length(description) <= 255 )\nNOT VALID;\n")
   -> 0.0020s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_310ac457b8;")
   -> 0.0092s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0008s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_b811ec1218\nCHECK ( char_length(encrypted_signing_keys) <= 2048 )\nNOT VALID;\n")
   -> 0.0021s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_b811ec1218;")
   -> 0.0038s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_9b90bc0f07\nCHECK ( char_length(encrypted_signing_keys_iv) <= 255 )\nNOT VALID;\n")
   -> 0.0020s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_9b90bc0f07;")
   -> 0.0043s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0008s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_be5ed8d307\nCHECK ( char_length(file) <= 255 )\nNOT VALID;\n")
   -> 0.0021s
-- current_schema()
   -> 0.0005s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_be5ed8d307;")
   -> 0.0044s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0009s
-- execute("ALTER TABLE packages_debian_group_distributions\nADD CONSTRAINT check_3d6f87fc31\nCHECK ( char_length(file_signature) <= 4096 )\nNOT VALID;\n")
   -> 0.0024s
-- current_schema()
   -> 0.0006s
-- execute("ALTER TABLE packages_debian_group_distributions VALIDATE CONSTRAINT check_3d6f87fc31;")
   -> 0.0047s
== 20201204110800 CreatePackagesDebianGroupDistributions: migrated (0.3722s) ==

Down Migration

== 20201124100200 CreatePackagesDebianGroupDistributions: reverting ===========
-- drop_table(:packages_debian_group_distributions)
   -> 0.0127s
== 20201124100200 CreatePackagesDebianGroupDistributions: reverted (0.0128s) ==

== 20201124095300 CreatePackagesDebianProjectDistributions: reverting =========
-- drop_table(:packages_debian_project_distributions)
   -> 0.0183s
== 20201124095300 CreatePackagesDebianProjectDistributions: reverted (0.0185s) 

SQL Queries

There are a few new scopes introduced, they use the indexes included in the new tables, such as:

EXPLAIN ANALYZE SELECT "packages_debian_group_distributions".* 
FROM "packages_debian_group_distributions" 
WHERE "packages_debian_group_distributions"."group_id" = 1 
AND "packages_debian_group_distributions"."suite" = 'my-suite';

                                                                                          QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Index Scan using uniq_pkgs_debian_group_distributions_group_id_and_suite on packages_debian_group_distributions  (cost=0.14..2.16 rows=1 width=368) (actual time=0.039..0.039 rows=0 loops=1)
   Index Cond: ((group_id = 1) AND (suite = 'my-suite'::text))
 Planning Time: 0.106 ms
 Execution Time: 0.062 ms
(4 rows)

EXPLAIN ANALYZE SELECT "packages_debian_group_distributions".* 
FROM "packages_debian_group_distributions" 
WHERE "packages_debian_group_distributions"."group_id" = 1 
AND "packages_debian_group_distributions"."codename" = 'my-codename';

                                                                                          QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Index Scan using uniq_pkgs_debian_group_distributions_group_id_and_suite on packages_debian_group_distributions  (cost=0.14..2.17 rows=1 width=368) (actual time=0.005..0.005 rows=0 loops=1)
   Index Cond: (group_id = 1)
   Filter: (codename = 'my-codename'::text)
 Planning Time: 0.114 ms
 Execution Time: 0.027 ms
(5 rows)

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• [-] Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• [-] Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• [-] Tested in all supported browsers
• [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• [-] Label as security and @ mention @gitlab-com/gl-security/appsec
• [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• [-] Security reports checked/validated by a reviewer from the AppSec team