Add migration to limit Design filename
What does this MR do?
This MR adds two post-migrations that enforce a character limit for design_management_designs.filename of 255.
A corresponding MR to add a model validation has been merged !33564 (merged).
First post-migration: Database migration
The database migration adds the limit to the filename column.
As per the Add a text limit constraint to an existing column guidelines it specifies validate: false. A follow-up issue to set the validation in a later release #220420 (closed).
Second post-migration: Data migration
This data migration modifies any design filenames larger than 255 characters to the string "gitlab-modified-{id}.jpg".
This will ensure the filename remains valid (by having a valid image extension .jpg, regardless of the original extension, and being unique).
Any records modified by this data migration will appear as a broken image when viewed within the GitLab app, as filenames are used to look up the design file from the design git repository.
However, this is an intentional consequence given the 255 character limit was selected because that is the largest filename that most filesystems support, and also because GitLab.com has 6 records with filenames larger than this but they are all abuse (see https://gitlab.com/gitlab-org/gitlab/-/issues/209025#note_299196486). As such, we do not believe it is worth attempting to modify commits within the design git repository to match the updated filename.
SQL:
UPDATE "design_management_designs"
SET "filename" = CONCAT('gitlab-modified-', "design_management_designs"."id", '.jpg')
WHERE CHAR_LENGTH("design_management_designs"."filename") > 255;#database-lab EXPLAIN:
ModifyTable on public.design_management_designs (cost=0.00..555.24 rows=3437 width=54) (actual time=71.216..71.216 rows=0 loops=1)
Buffers: shared hit=69 read=117 dirtied=10
I/O Timings: read=60.656
-> Seq Scan on public.design_management_designs (cost=0.00..555.24 rows=3437 width=54) (actual time=8.279..35.075 rows=6 loops=1)
Filter: (char_length((design_management_designs.filename)::text) > 255)
Rows Removed by Filter: 10348
Buffers: shared hit=4 read=97
I/O Timings: read=27.483Time: 71.363 ms
- planning: 0.090 ms
- execution: 71.273 ms
- I/O read: 60.656 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 69 (~552.00 KiB) from the buffer pool
- reads: 117 (~936.00 KiB) from the OS file cache, including disk I/O
- dirtied: 10 (~80.00 KiB)
- writes: 0Migrations
Up
Post-migration database migration:
== 20200602013900 AddLimitToDesignsFilename: migrating ========================
-- transaction_open?()
-> 0.0000s
-- execute("ALTER TABLE design_management_designs\nADD CONSTRAINT check_07155e2715\nCHECK ( char_length(filename) <= 255 )\nNOT VALID;\n")
-> 0.0030s
== 20200602013900 AddLimitToDesignsFilename: migrated (0.0146s) ===============Post-migration data migration:
== 20200602013901 CapDesignsFilenameLengthToNewLimit: migrating ===============
== 20200602013901 CapDesignsFilenameLengthToNewLimit: migrated (0.0034s) ======Down
Post-migration data migration (note, the down is a no-op):
== 20200602013901 CapDesignsFilenameLengthToNewLimit: reverting ===============
== 20200602013901 CapDesignsFilenameLengthToNewLimit: reverted (0.0000s) ======Post-migration database migration:
== 20200602013900 AddLimitToDesignsFilename: reverting ========================
-- execute("ALTER TABLE design_management_designs\nDROP CONSTRAINT IF EXISTS check_07155e2715\n")
-> 0.0010s
== 20200602013900 AddLimitToDesignsFilename: reverted (0.0072s) ===============Rollback
Note, the data migration can be rolled back, but the data modification won't be reversed.
be rake db:migrate:down VERSION=20200602013901
be rake db:migrate:down VERSION=20200602013900