Schema Validations: Identify foreign keys that do not match schema

Overview

Schema Validations is a framework that compares production DB and db/structure.sql to find inconsistencies between the two.

See bundle exec rake gitlab:db:schema_checker:run

See the issues generated using Schema Validations, tagged with database-inconsistency-report label

What does this MR do and why?

Adds foreign key support to Gitlab::Database::SchemaValidation framework

It adds the three new validators:

• DifferentDefinitionForeignKeys: Checks for diffs between fks there are present in the db and in the structure.sql file;
• ExtraForeignKeys: Checks if fks are present in the db, but not in the structure.sql file;
• MissingForeignKeys: Checks if fks are present in the structure.sql file, but not in the db;

⚠ Code is not DRY. We're still validating the framework. We're planing a refactoring soon as we validate the idea.

See: !113317 (comment 1304807919)

How to set up and validate locally

1. Testing DifferentDefinitionForeignKeys (Diffs between db and db/structure.sql)

1. Change some existent table definition in db/structure.sql, like ALTER TABLE ONLY user_follow_users ADD CONSTRAINT user_follow_users_follower_id_fkey ... ON UPDATE CASCADE ON DELETE CASCADE
2. Check if the public.user_follow_users_follower_id_fkey fk is present in DifferentDefinitionForeignKeys output:

structure_sql = Gitlab::Database::SchemaValidation::StructureSql.new(Rails.root.join('db/structure.sql'), 'public')
database = Gitlab::Database::SchemaValidation::Database.new(ActiveRecord::Base.connection)
Gitlab::Database::SchemaValidation::Validators::DifferentDefinitionForeignKeys.new(structure_sql, database).execute

Check the output

The public.user_follow_users_follower_id_fkey foreign key has a different statement between structure.sql and database
Diff:
-FOREIGN KEY (follower_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
+FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE)

2. Testing ExtraForeignKeys (that are present in the Database and not in the structure.sql)

1. Remove some fks from structure.sql, like ALTER TABLE ONLY user_follow_users ADD CONSTRAINT user_follow_users_follower_id_fkey ...
2. Check if the user_follow_users_follower_id_fkey fk is present in ExtraForeignKeys output:

structure_sql = Gitlab::Database::SchemaValidation::StructureSql.new(Rails.root.join('db/structure.sql'), 'public')
database = Gitlab::Database::SchemaValidation::Database.new(ActiveRecord::Base.connection)
Gitlab::Database::SchemaValidation::Validators::ExtraForeignKeys.new(structure_sql, database).execute

Check the output

The foreign key public.user_follow_users_follower_id_fkey is present in the database, but not in the structure.sql file
Diff:
+FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE

3. Testing MissingForeignKeys (that are present in the structure.sql and not in the Database)

1. Drop any FK from the database, like: ALTER TABLE timelogs DROP CONSTRAINT fk_timelogs_note_id;
2. Check if the fk_timelogs_note_id fk is present in MissingForeignKeys output:

structure_sql = Gitlab::Database::SchemaValidation::StructureSql.new(Rails.root.join('db/structure.sql'), 'public')
database = Gitlab::Database::SchemaValidation::Database.new(ActiveRecord::Base.connection)
Gitlab::Database::SchemaValidation::Validators::MissingForeignKeys.new(structure_sql, database).execute

Check the output

The foreign key public.fk_timelogs_note_id is missing from the database
Diff:
-FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE SET NULL

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #388016 (closed)