Schema Validations: Identify table columns that do not match schema

Overview

This is the second MR after splitting MR !115066 (closed).

Part 1: !117013 (merged)

What does this MR do and why?

Adds table columns support to Gitlab::Database::SchemaValidation framework

It adds the three new validators:

• DifferentDefinitionTables: Checks for diffs between tables there are present in the db and in the structure.sql file;
• ExtraTableColumns: Checks if table columns are present in the db, but not in the structure.sql file;
• MissingTableColumns: Checks if tables columns 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

Testing DifferentDefinitionTables (Diffs between db and db/structure.sql)

1. Change some existent table definition in structure.sql, like CREATE TABLE lfs_objects (id bigint NOT NULL, ...)
2. Check if the lfs_objects table is present in DifferentDefinitionTables 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::DifferentDefinitionTables.new(structure_sql, database).execute

Check the output

The table lfs_objects has a different column statement between structure.sql and database
Diff:
-CREATE TABLE lfs_objects (id integer NOT NULL)
+CREATE TABLE lfs_objects (id bigint NOT NULL)

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

1. Remove some columns from any table definition in structure.sql, like CREATE TABLE lfs_objects (id integer NOT NULL, ...)
2. Check if the lfs_objects table and the removed columns are present in ExtraTableColumns 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::ExtraTableColumns.new(structure_sql, database).execute

Check the output

The table lfs_objects has columns present in the database, but not in the structure.sql file
Diff:
+CREATE TABLE lfs_objects (id integer NOT NULL, oid character varying NOT NULL, size bigint NOT NULL)

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

1. Change some columns from lfs_objects table, in the database: ALTER TABLE lfs_objects DROP COLUMN created_at;, ALTER TABLE lfs_objects RENAME COLUMN updated_at TO new_updated_at;
2. Check if the lfs_objects table is present in MissingTableColumns 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::MissingTableColumns.new(structure_sql, database).execute

Check the output

The table lfs_objects has columns missing from the database
Diff:
+CREATE TABLE lfs_objects (created_at timestamp without time zone, updated_at timestamp without time zone)

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 #388013 (closed)